MySQL (十) DDL之语法 学习回顾记录

DDL（data definition language） 针对数据库、表的创建、删除、修改等操作

一、创建数据库

------------------------------------创建数据库库--------------------------------------

语法：
create database 数据库名 default character set 字符集 collate 排序规则；

# 删除数据库
drop database 数据库名；

# 创建数据库

create database db_lzy default character set utf8mb4 collate utf8mb4_general_ci;

# 删除数据库
drop database db_lzy;

二、创建表

创建表

create table 表名(
				字段名 类型（长度） 是否为null 约束条件 注释,
				字段名 类型（长度） 是否为null default 默认值 [auto_increment 主键自动递增] comment '注释'，
				字段名 类型（不写使用默认长度） 是否为null default默认值 comment '注释',
				。。。
				PRIMARY KEY(主键)
)；

# 创建一个表
CREATE TABLE t_user(
				id int(11) not null auto_increment comment '主键',
				name VARCHAR(20) DEFAULT null comment '姓名',
				sex TINYINT(1) DEFAULT 1 comment '性别1男0女',
				create_time datetime default now() comment '创建时间',
				modify_time datetime DEFAULT null comment '修改时间',
				PRIMARY key(id)
				);

# 查看表信息
desc t_user;

三、复制表信息

#复制表（先选择数据库）

复制表不会复制约束条件，需要自己重新添加
create table 表名 as select 字段列表 from 已有表; #会将数据一起复制过来

create table 表名 as select 字段列表 from 已有表 where 1！=1;
create table 表名 as select 字段列表 from 已有表 where 1=0; # 只复制表结构,没有数据

复制表以及约束条件但是不复制数据
create table 表名 like 已有表;

# 先给 t_uer 添加数据
insert into t_user(name,sex,create_time) values('张三',1,now()); #主键自增情况
insert into t_user(id,name,sex,create_time) values(2,'李四',0,now()); #主键不是自增情况

#不会复制约束条件，但是有数据
create table t_user_copy as select * from t_user;

#只会复制表结构，没有数据
create table t_user_copy2 as select * from t_user where 1=0;

# 复制表以及约束条件但是不复制数据
CREATE TABLE t_user_copy3 like t_user;

desc t_user_copy;
desc t_user_copy2;
desc t_user_copy3;

四、删除表

drop table 表名;	#删除表
truncate table 表名;	# 清空表

1/ 清空表，只清空数据会保留一些状态（自增的状态，再次添加 是接着以前的自增插入）
delete from t_user;

2/ 清空表，清空数据以及状态（相当于恢复出场状态）
truncate table t_user;

五、修改表结构

######################################修改表结构#################

# 修改表名：rename table 原表名 to 新表名;
# 修改列名：alter table 表名 change 原字段名 新字段名 字段类型 约束条件;
# 修改类型：alter table 表名 modify 字段名称 字段类型 [完整性约束条件];
# 添加列	 ：alter table 表名 add 字段 类型 约束;
# 删除列	 ：alter table 表名 drop column 字段;

# 修改表名
RENAME table t_user to t_user_original;

# 修改列名 将 sex 列修改为age int（3） 添加默认约束
alter table t_user_original change sex age int(3) DEFAULT 0 comment '年龄';

# 修改类型 将age 列类型修改为 varchar(长度3) 添加默认约束 非空约束
alter table t_user_original modify age varchar(3) DEFAULT '0' not null comment '注释';

# 添加列
alter table t_user_original add job datatime DEFAULT NULL;

# 删除列

alter table t_user_original drop column job;

六、综合练习

#练习 外键约束

# 1、先建立一个学生表

CREATE table t_student(

			s_id tinyint(11) not null auto_increment comment '主键',
			s_name VARCHAR(20) DEFAULT null comment '名字',
			s_sex  tinyint(1) DEFAULT null COMMENT '性别',
			create_time datetime DEFAULT now() comment '创建时间',
			is_valid TINYINT(1) DEFAULT 1 comment '是否有效',
			primary key(s_id)

);

# 创建老师表

CREATE table t_teacher(

			t_id tinyint(11) not null auto_increment comment '主键',
			t_name VARCHAR(20) DEFAULT null comment '名字',
			t_sex  tinyint(1) DEFAULT null COMMENT '性别',
			create_time datetime DEFAULT now() comment '创建时间',
			is_valid TINYINT(1) DEFAULT 1 comment '是否有效',
			primary key(t_id)

);

# 创建老师学生中间表

CREATE TABLE t_t_s(

t_id tinyint(1) not null comment '学生id',
s_id tinyint(1) not null comment '老师id',
PRIMARY key(t_id,s_id)
);

################## 添加外键约束##################
# 数据库层面有了关系

#中间表中的 s_id 和t_student中的s_id关联起来
alter table t_t_s add constraint fk_s_id foreign key(s_id) REFERENCES t_student(s_id);

#中间表中的 t_id 和 t_teacher 中的t_id 关联起来
alter table t_t_s add constraint fk_t_id foreign key(t_id) references t_teacher(t_id);


#################添加数据########################
insert into t_student(s_name,s_sex,create_time) 
VALUES('张三',1,now()),('李四',0,now()),('王五',1,now()),('赵六',0,now()),('田七',1,now());


insert into t_teacher(t_name,t_sex,create_time) 
VALUES('周老师',1,now()),('吴老师',0,now()),('郑老师',1,now()),('王老师',0,now()),('赵老师',1,now());

insert into t_t_s(s_id,t_id)
VALUES(1,1),(2,1),(3,1),(1,2),(3,2),(4,2),(1,3),(2,3),(3,3),(4,3);


##########################查询数据#################################
#查询周老师所带学生的姓名
#分析
####################使用子查询#########################
#1、周老师t_id
select t_id from t_teacher where t_name='周老师';

# 2、在 t_t_s表中查询 周老师id 所对应的 学生t_id
select t_id from t_t_s where s_id = (select t_id from t_teacher where t_name='周老师');

# 3、在t_student 表中查询 第二步中学生id 的姓名；
select s_name from t_student where s_id in (
select t_id from t_t_s where s_id = (
select t_id from t_teacher where t_name='周老师')
);

/*
张三
李四
王五
*/

###################使用inner join ###################

select s_name from t_student s 
inner join t_t_s tt on s.s_id = tt.s_id 
inner join t_teacher t on tt.t_id = t.t_id
where t.t_name = '周老师';  


###################使用left join #####################

select s_name from t_student s 
left join t_t_s tt on s.s_id = tt.s_id 
left join t_teacher t on tt.t_id = t.t_id
where t.t_name = '周老师';  

##################使用right join ###################

select * from t_student s 
right join t_t_s tt on s.s_id = tt.s_id 
right join t_teacher t on tt.t_id = t.t_id
where t.t_name = '周老师';