MySql的复习(七)：表的使用
创建表
- 语法格式
create table tableName(
columnName dataType(length),
………………..
columnName dataType(length)
);
set character_set_results='gbk';
show variables like '%char%';
-- 创建表的时候，表中有字段，每一个字段有：
-- * 字段名
-- * 字段数据类型
-- * 字段长度限制
-- * 字段约束
- 常用数据类型
| 类型 | 描述 |
|---|---|
| Char(长度) | 定长字符串，存储空间大小固定，适合作为主键或外键 |
| Varchar(长度) | 变长字符串，存储空间等于实际数据空间 |
| double(有效数字位数，小数位) | 数值型 |
| Float(有效数字位数，小数位) | 数值型 |
| Int( 长度) | 整型 |
| bigint(长度) | 长整型 |
| Date | 日期型 年月日 |
| DateTime | 日期型 年月日 时分秒 毫秒 |
| time | 日期型 时分秒 |
| BLOB | Binary Large OBject（二进制大对象） |
| CLOB | Character Large OBject（字符大对象） |
| 其它………………… |
- 创建学生表
建表语句的语法格式：
create table 表名(
字段名1 数据类型,
字段名2 数据类型,
字段名3 数据类型,
....
);
create table t_student(
number bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
表结构的操作
利用alter table来操作，不影响表中的数据
添加字段 Add
- 需要向学生表中加入联系电话字段，字段名称为：contatct_tel 类型为varchar(40)
alter table t_student add contact_tel varchar(40);
修改字段 Modify
- 修改字段的长度：学生名字无法满足需求，长度需要更改为100
alter table t_student modify student_name varchar(100) ;
- 修改字段的名称：sex字段感觉不好用gender替换
alter table t_student change sex gender char(2) not null ;
删除字段 Drop
- 删除联系电话字段
alter table t_student drop contact_tel;
表数据的操作（DML）
添加 Insert
-
语法格式
语法格式：
insert into 表名(字段名1,字段名2,字段名3,…) values(值1,值2,值3,…)
要求：字段的数量和值的数量相同，并且数据类型要对应相同。 -
省略字段的插入
不建议使用此种方式，因为当数据库表中的字段位置发生改变的时候会影响到insert语句
insert into emp values(9999,'zhangsan','MANAGER', null, null,3000, 500, 10); -
指定字段的插入(建议使用)
insert into t_student(name) values('wangwu'); -- 除name字段之外，剩下的所有字段自动插入NULL。 -
一次插入多行数据
insert into t_student (no,name,sex,classno,birth) values (3,'rose','1','gaosi2ban','1952-12-14'),(4,'laotie','1','gaosi2ban','1955-12-14');
注意：
当一条insert语句执行成功之后，表格当中必然会多一行记录。
? 即使多的这一行记录当中某些字段是NULL，后期也没有办法在执行
? insert语句插入数据了，只能使用update进行更新。
插入日期
- 插入的日期格式和显示的日期格式一致
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9997,'zhangsan','MANAGER', null, '1981-06-12',3000, 500, 10);
- 采用str_to_date
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) valu
es(9996,'zhangsan','MANAGER',null,str_to_date('1981-06-12','%Y-%m-%d'),3000, 500, 10);
- 添加系统日期(now())
insert into emp(empno, ename, job, mgr, hiredate, sal, comm, deptno) values(9995,'zhangsan','MANAGER',null,now() ,3000, 500, 10);
表的复制
-
语法格式
create table 表名 as select语句;
将查询结果当做表创建出来。
create table emp_bak as select empno,ename,sal from emp;
将查询结果插入到一张表中
insert into emp_bak select * from emp where sal=3000;
修改 Update
可以修改数据，可以根据条件修改数据
-
语法格式
update 表名 set 字段名1=值1,字段名2=值2… where 条件;
如果缺少条件，整个表都会被更新！！
- 将job为manager的员工工资上涨10元
update emp set sal = sal+sal*0.1 where job = 'MANAGE '
- 将部门10的LOC修改为SHANGHAI，将部门的名称修改为RENSHIBU
update dept1 set loc = 'SHANGHAI',dname = 'RENSHIBU' where deptno = 10;
删除 Delete
可以删除数据，可以根据条件删除数据
-
语法格式：
delete from 表名 where 条件;
-
删除津贴为500的员工
delete from emp where comm=500;
-
删除津贴为空的员工
delete from emp where comm is null;
怎么删除大表？
- truncate table 表名; // 表被截断，不可回滚。永久丢失。
表的约束
什么是表的约束？
在创建表的时候，可以给表的字段添加相应的约束，添加约束的目的是为了保证表中数据的
合法性、有效性、完整性。
常见的约束有？
- 非空约束(not null)：约束的字段不能为NULL
- 唯一约束(unique)：约束的字段不能重复
- 主键约束(primary key)：约束的字段既不能为NULL，也不能重复（简称PK）
- 外键约束(foreign key)：…（简称FK）
- 检查约束(check)：注意Oracle数据库有check约束，但是mysql没有，目前mysql不支持该约束。
非空约束 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', 'qqq@163.com', 10)
唯一约束，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)
)
-- 插入了重复的email，导致出现错误
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
主键约束 Primary key
每个表应该具有主键，主键可以标识记录的唯一性，主键分为单一主键和复合（联合）主键，单一主键是由一个字段构成的，复合（联合）主键是由多个字段构成的
rop 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)
)
-- 向以上表中加入学号为1001的两条记录，出现如下错误，因为加入了主键约束
insert into t_student(student_id, student_name , sex, birthday, email, classes_id)
values
(1001,'zhangsan','m', '1988-01-01', 'qqq@163.com', 10)
- 可以通过表级约束为约束起个名字
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)
)
外键约束 Foreign key
外键主要是维护表之间的关系的，主要是为了保证参照完整性，如果表中的某个字段为外键字段，那么该字段的值必须来源于参照的表的主键，如：emp中的deptno值必须来源于dept表中的deptno字段值。
- 建立学生和班级表之间的联系
- 首先建立班级表
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中加入数据
insert into t_student(student_id, student_name, sex, birthday, email, classes_id) values(1001, 'zhangsan', 'm', '1988-01-01', 'qqq@163.com', 10)
-- 因为在班级表中不存在班级编号为10班级，外键约束起到了作用
-- 存在外键的表就是子表，参照的表就是父表，所以存在一个父子关系，也就是主从关系，主表就是班级表，从表就是学生表
-
外键值可以为NULL？
外键可以为null。 -
外键字段引用其他表的某个字段的时候，被引用的字段必须是主键吗？
注意：被引用的字段不一定是主键，但至少是具有unique约束，具有唯一性，不可重复！
级联更新和级联删除
on update cascade
- mysql对有些约束的修改比较麻烦，所以我们可以先删除，再添加
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;
我们只修改了父表中的数据，子表中的数据也会跟着变动
on delete cascade;
mysql对有些约束的修改时不支持的，所以我们可以先删除，再添加
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 delete cascade;
delete from t_classes where classes_id = 20;
我们只删除了父表中的数据，但是子表也会中的数据也会删除。