首页 > 其他分享 >07-数据定义

07-数据定义

时间：2023-07-30 22:15:00浏览次数：27  
标签：key 10 07 classes student table 数据 id 定义

07-数据定义

课程目标

掌握创建表、增加和删除和修改表结构、添加和修改和删除表中数据、创建表并加入约束。

7.1创建表结构

1.语法格式

create table tableName(

columnName dataType(length),

………………..

columnName dataType(length)

);

set character_set_results='gbk';

show variables like '%char%';

创建表的时候，表中有字段，每一个字段有：

* 字段名

* 字段数据类型

* 字段长度限制

* 字段约束

2. MySQL常用数据类型，如下表7-1所示：

表7-1

类型

描述

char(长度)

定长字符串，存储空间大小固定，适合作为主键或外键

varchar(长度)

变长字符串，存储空间等于实际数据空间

double(有效数字位数，小数位)

数值型

float(有效数字位数，小数位)

数值型

int( 长度)

整型

bigint(长度)

长整型

date

日期型

BLOB

Binary Large OBject（二进制大对象）

CLOB

Character Large OBject（字符大对象）

其它…………………

3.建立学生信息表，字段包括：学号、姓名、性别、出生日期、email、班级标识，如下图7-1所示：

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2),

birthday date,

email varchar(30),

classes_id int(3)

);

图7-1

4.向t_student表中加入数据,（必须使用客户端软件，我们的cmd默认是GBK编码,数据中设置的编码是UTF-8），如下图7-2所示：

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', '[email protected]', 10);

图7-2

5.向t_student表中加入数据（使用默认值），如下图7-3所示：

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2) default 'm',

birthday date,

email varchar(30),

classes_id int(3)

);

insert into t_student(student_id, student_name, birthday, email, classes_id)

values

(1002, 'zhangsan', '1988-01-01', '[email protected]', 10);

图7-3

7.2 增加/删除/修改表结构

采用alter table来增加/删除/修改表结构，不影响表中的数据

1.添加字段

如：需求发生改变，需要向t_student中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40) ，如下图7-4所示：

alter table t_student add contact_tel varchar(40);

图7-4

2.修改字段

如：student_name无法满足需求，长度需要更改为100，如下图7-5所示：

alter table t_student modify student_name varchar(100) ;

图7-5

如sex字段名称感觉不好，想用gender那么就需要改列的名称，如下图7-6所示：

alter table t_student change sex gender char(2) not null;

图7-6

3.删除字段

如：删除联系电话字段，如下图7-7所示：

alter table t_student drop contact_tel;

图7-7

7.3 添加、修改和删除表中数据

1.insert

添加、修改和删出都属于DML，主要包含的语句：insert、update、delete

Insert语法格式

Insert into 表名(字段名1，字段名2，…) values(字段值1, 字段值2,…);

省略字段的插入，如下图7-8和7-9所示：

insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

图7-8

图7-9

不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到insert语句

指定字段的插入(建议使用此种方式) ，如下图7-10所示：

insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10);

图7-10

出现了主键重复的错误，主键表示了记录的唯一性，不能重复，如下图7-11所示：

图7-11

如何插入日期：

第一种方法，插入的日期格式和显示的日期格式一致，如下图7-12所示：

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10) ;

图7-12

第二种方法，采用str_to_date，如下图7-13所示：

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);

图7-13

第三种方法，添加系统日期now()，如下图7-14和7-15所示：

insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);

图7-14

图7-15

表复制，如下图7-16和7-17所示：

create table emp_bak as select * from emp;

图7-16

图7-17

以上方式，会自动创建表，将符合查询条件的数据自动复制到创建的表中

如何将查询的数据直接放到已经存在的表中，可以使用条件，如下图7-18所示：

insert into emp_bak select * from emp where sal=3000;

图7-18

2.update

可以修改数据，可以根据条件修改数据

语法格式：

update 表名 set 字段名称1=需要修改的值1, 字段名称2=需要修改的值2 where ……

将job为manager的员工的工资上涨10%

update emp set sal=sal+sal*0.1 where job='MANAGER';

3.delete

可以删除数据，可以根据条件删除数据

语法格式：

delete from表名 where ……

删除津贴为500的员工

delete from emp where comm=500;

删除津贴为null的员工

delete from emp where comm is null;

7.4 创建表加入约束

常见的约束：

    1. 非空约束，not null
    2. 唯一约束，unique
    3. 主键约束，primary key
    4. 外键约束，foreign key
    5. 自定义检查约束，check（不建议使用）(在MySQL中现在还不支持)

1.非空约束，not null

非空约束，针对某个字段设置其值不为空，如：学生的姓名不能为空

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30),

classes_id int(3)

);

insert into t_student(student_id, birthday, email, classes_id)

values(1002, '1988-01-01', '[email protected]', 10);

图7-19

以上错误为加入的学生姓名为空，如上图7-19所示。

2.唯一约束，unique

唯一性约束，它可以使某个字段的值不能重复，如：email不能重复：

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30) unique,

classes_id int(3)

);

insert into t_student(student_id, student_name , sex, birthday, email, classes_id)

values

(1001,'zhangsan','m', '1988-01-01', '[email protected]', 10);

图7-20

以上插入了重复的email，所以出现了“违反唯一约束错误”，所以unique起作用了

同样可以为唯一约束起个约束名，如上图7-20所示。

我们可以查看一下约束，如下图7-21所示：

MySQL> use information_schema;

MySQL> select * from table_constraints where table_name = 't_student';

图7-21

关于约束名称可以到table_constraints中查询

以上约束的名称我们也可以自定义。

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30) ,

classes_id int(3) ,

constraint email_unique unique(email)/*表级约束*/

);

3.主键约束，primary key

每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的

drop table if exists t_student;

create table t_student(

student_id int(10) primary key,/*列级约束*/

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30),

classes_id int(3)

);

insert into t_student(student_id, student_name, sex, birthday, email, classes_id)

values

(1001,'zhangsan','m', '1988-01-01', '[email protected]', 10);

向以上表中加入学号为1001的两条记录，出现如下错误，因为加入了主键约束，如下图7-22所示：

图7-22

我们也可以通过表级约束为约束起个名称：

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20) not null,

sex char(2) default 'm',

birthday date,

email varchar(30) ,

classes_id int(3),

constraint p_id primary key(student_id)

);

insert into t_student(student_id, student_name , sex, birthday, email, classes_id)

values

(1001,'zhangsan','m', '1988-01-01', '[email protected]', 10);

4.外键约束 foreign key

外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。

建立学生和班级表之间的连接

首先建立班级表t_classes

drop table if exists t_classes;

create table t_classes(

classes_id int(3),

classes_name varchar(40),

constraint pk_classes_id primary key(classes_id)

);

在t_student中加入外键约束

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2),

birthday date,

email varchar(30),

classes_id int(3),

constraint student_id_pk primary key(student_id),

constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)

);

向t_student中加入数据，如下图7-23所示：

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', '[email protected]', 10)；

图7-23

出现错误，因为在班级表中不存在班级编号为10班级，外键约束起到了作用

存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表，如下图7-24所示：

图7-24

以上成功的插入了学生信息，当时classes_id没有值，这样会影响参照完整性，所以我们建议将外键字段设置为非空

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(20),

sex char(2),

birthday date,

email varchar(30),

classes_id int (3) not null,

constraint student_id_pk primary key(student_id),

constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)

);

insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', '[email protected]', null);

再次插入班级编号为null的数据，如下图7-25所示：

图7-25

添加数据到班级表，添加数据到学生表，删除班级数据，将会出现如下错误，如下图7-26所示：

insert into t_classes (classes_id,classes_name) values (10,'366');

insert into t_student(

student_id, student_name, sex, birthday, email, classes_id

) values(

1001, 'zhangsan', 'm', '1988-01-01', '[email protected]', 10

);

MySQL> update t_classes set classes_id = 20 where classes_name = '366';

图7-26

因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除子表中的引用记录，再修改父表中的数据。

我们也可以采取以下措施 级联更新，如下图7-27所示：

MySQL> delete from t_classes where classes_id = 10;

图7-27

因为子表（t_student）存在一个外键classes_id，它参照了父表（t_classes）中的主键，所以先删除父表，那么将会影响子表的参照完整性，所以正确的做法是，先删除子表中的数据，再删除父表中的数据，采用drop table也不行，必须先drop子表，再drop父表

我们也可以采取以下措施 级联删除。

5.级联更新与级联删除

(1).on update cascade;

MySQL对有些约束的修改比较麻烦，所以我们可以先删除，再添加，如下图7-28所示：

alter table t_student drop foreign key fk_classes_id;

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id) on update cascade;

图7-28

我们只修改了父表中的数据，但是子表中的数据也会跟着变动。

(2).on delete cascade;

MySQL对有些约束的修改时不支持的，所以我们可以先删除，再添加，如下图7-29所示：

alter table t_student drop foreign key fk_classes_id_1;

alter table t_student add constraint fk_classes_id_2 foreign key(classes_id) references t_classes(classes_id) on delete cascade;

delete from t_classes where classes_id = 20;

图7-29

我们只删除了父表中的数据，但是子表也会中的数据也会删除。

6. t_student和t_classes完整示例

drop table if exists t_classes;

create table t_classes(

classes_id int (3),

classes_name varchar(30) not null,

constraint pk_classes_id primary key(classes_id)

);

drop table if exists t_student;

create table t_student(

student_id int(10),

student_name varchar(50) not null,

sex char(2) not null,

birthday date not null,

email varchar(30) unique,

classes_id int (3) not null,

constraint pk_student_id primary key(student_id),

constraint fk_classes_id foreign key(classes_id) references t_classes(classes_id)

);

7. 增加/删除/修改表约束

1.删除约束

将t_student中的约束删除

删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;

alter table t_student drop foreign key fk_classes_id;

删除主键约束：alter table 表名 drop primary key ;

alter table t_student drop primary key;

删除唯一约束：alter table 表名 drop key 约束名称 ;

alter table t drop key uk;

2.添加约束

向t_student中的添加约束

添加外键约束：alter table 从表 add constraint 约束名称 foreign key 从表(外键字段) references 主表(主键字段);

alter table t_student add constraint fk_classes_id_1 foreign key(classes_id) references t_classes(classes_id);

添加主键约束：alter table 表 add constraint 约束名称 primary key 表(主键字段);

alter table t_student add constraint pk primary key(student_id);

添加唯一性约束：alter table 表 add constraint 约束名称 unique 表(字段);

alter table t_student add constraint uk unique(email);

3.修改约束，其实就是修改字段

alter table t_student modify student_name varchar(30) unique;

MySQL对有些约束的修改时不支持，所以我们可以先删除，再添加

7.5 本章小结

本章主要阐述了SQL语句中的数据定义。

标签：key,10,07,classes,student,table,数据,id,定义
From： https://www.cnblogs.com/zxbin/p/17592156.html

相关文章

  • 【ACM专项练习#02】整行字符串、输入vector、打印图形、处理n组数据以及链表操作等
    输入整行字符串平均绩点题目描述每门课的成绩分为A、B、C、D、F五个等级，为了计算平均绩点，规定A、B、C、D、F分别代表4分、3分、2分、1分、0分。输入有多组测试样例。每组输入数据占一行，由一个或多个大写字母组成，字母之间由空格分隔。输出每组输出结果占一行。如果输入的大......
  • js的面向对象：类的定义、对象的创建、属性、方法
    //js当中定义一个类functionUser(a,b){//属性this.usercode=athis.username=b//方法（实例方法，通过对象调用this.doSome=function(){console.log(this.username+"......
  • Mysql数据库常用操作命令
    //创建数据库>create databasedatabase_name;eg;createdatabasesorder;>useorder;//选择数据库>showtables;//显示数据库order创建的所有数据表>dropdatabaseorder;//删除数据库order,删除数据库后，数据表中的数据会全部被删除，所以删除前一定要做好数据......
  • 【个人模板封装】树套树、高维数据结构
    前言这是我个人使用的一些模板封装，限于个人能力，可能存在诸多不足与漏洞，在未加测试直接使用前请务必小心谨慎。更新可能会滞后于我本地的文档，如有疑问或者催更之类的可以在评论区留言。全文模板测试均基于以下版本信息，请留意版本兼容问题。Windows,64bitG++(ISOC++20)stack......
  • 0730小马拉松 题解
    T358782阶乘数学。测试点\(1\sim3\)：longlong暴力阶乘。预期30分。测试点\(4\sim5\)：暴力试除，找出因子\(5\)的个数。预期50分。测试点\(6\sim7\)：考虑这样一个程序：while(x)x/=5,cnt+=x;即求出有多少个数是\(5\)的倍数，\(25\)的倍数，\(125\)的倍数......加起来......
  • python数据分析师入门-学习笔记（爬虫-序言）
    爬虫到底是什么概括爬虫是批量化自动获取既有数据批量化自动既有数据通常获取既有数据特殊批量注册一批账号批量去领取优惠券批量自动下单购物自动做任务（签到）实际应用企业中：竞品调研数据采集办公自动化个人：比如看小说有的网站收费有的网站不收费......
  • 清除 Electron (原子壳)中的缓存数据
    最佳答案 Electron将其缓存存储在以下文件夹中:window:C:\Users\<user>\AppData\Roaming\<yourAppName>\CacheLinux:/home/<user>/.config/<yourAppName>/Cache操作系统:/Users/<user>/Library/ApplicationSupport/<yourAppName>/Cache因此，删除这......
  • Manacher模板，支持自定义不同字符的相等关系
    #include<bits/stdc++.h>usingnamespacestd;structManacher{  structChar{    charch;    Char(){}    Char(charch):ch(ch){}    Char&operator=(constchar&r){      ch=r;      ret......
  • MySQL使用binlog恢复数据
    1.什么是BinlogMySQL的二进制日志（Binlog）是一种事务日志，用于记录对数据库的更改操作。Binlog主要用于MySQL复制和恢复:复制:从库通过拉取主库的binlog实现主从数据一致恢复:通过重放binlog恢复数据丢失或误操作情况1.1.Binlog的工作原理在MySQL中，每个事务都会在提交后......
  • AJAX--基于json格式的数据交换
    前端代码<!DOCTYPEhtml><htmllang="en"><head><metacharset="UTF-8"><metaname="viewport"content="width=device-width,initial-scale=1.0"><title>获取信息列表</title>......

赞助商

阅读排行

网站主页关于我们联系我们网站地图

本网站内容转载自其他媒体，侵权联系[admin##ips99.com]。

Copyright © 2020-2023 IPS99 版权所有 IPS99