8. DDL、DCL、DML、DQL¶

1. DDL：数据定义语言¶

1.1 库定义¶

1.创建库

help create database;
create database db1 charset utf8;

2.开发规范

1.库名不能出现大写（表名库名大小写敏感，列名大小写不敏感）
2.库名不能以数字开头
3.库名要和业务功能相关
4.建库要加字符集

3.删除库（危险）

drop database db1;

4.修改库

alter database db1 utf8mb4;

5.查看库

#查看所有库
show databases;
#查看建库语句
show create database db1;

1.2 表定义¶

1.2.1 建表¶

create table if not exists tb1(id int)charset utf8;

1.数据类型

数字：tinyint、int
时间：datetime、timestamp
字符串：char、varchar、enum

2.建表练习

create table student(
sno int not null primary key auto_increment comment '学号',
sname varchar(20) not null comment '姓名',
sage tinyint unsigned not null comment '年龄',
ssex enum('f','m') not null default 'm' comment '性别'
)engine=innodb charset=utf8;

3.创建表结构一模一样的表

create table stu like student;

4.创建表结构和内容一模一样的表（但没有约束）

create table stu1  select * from student;     #有没有as？

1.2.2 查看表¶

#查看库中所有表
use oldboy;
show tables;
#查看表信息
desc stu;
#查看建表语句
show create table stu;

1.2.3 删除表¶

drop table tb1;

#从物理上删除表（效率高）
truncate table tb1;
#一行一行删除表（效率低）
delete * from tb1;

1.2.4 修改表¶

1.改表名

alter table stu rename student;
desc stu;

2.添加单个列(在末尾)

alter table stu add age int;

3.添加多个列

alter table stu add gender varchar(20),add qq int;

4.在sno列后加入sid列

alter table stu add sid char(18) not null unique comment '身份证' after sno;

5.在sno之前添加suser列

alter table stu add suser varchar(10) not null unique comment '用户名' first;

6.修改列类型

alter table stu modify birthday datetime;

7.修改列名列类型

alter table stu change birthday birth timestamp;

8.删除列

alter table stu drop age;

2. DCL：数据控制语言¶

grant   授权
revoke  回收权限

grant all on wordpress.* to wordpress@'%' identified by '123456';
授予  所有权限 关于 wp数据库  给 wp用户 任意地方登录           密码123456

3. DML：数据操作语言¶

3.1 insert¶

desc teacher;
insert into teacher(tno,tname) values
(101,'oldboy1'),
(102,'oldboy2'),
(103,'oldguo');

3.2 update¶

desc stu;
update student set sname='ls' where sno=2;

不加where

update student set sname='ls'; #修改所有行

3.3 delete¶

delete from sc where sno=1;

不加where

delete from sc;  #删除所有数据行

3.4 伪删除¶

1.企业中一般不会删除数据行，而是使用状态列，实现伪删除(1显示，0删除)

alter table sc add state enum('1','0') not null default '1';

2.使用update替代delete

update sc set state='0' where sno=1;

3.修改应用查询语句

select * from sc where state='1';

4. DQL：数据查询语言¶

4.1 普通查询¶

select * from city;

4.2 where条件查询¶

#等值查询=（效率高）
select name from city where countrycode='chn';

#不等值查询>< (尽量指定上下限，效率高)（尽量不使用<>，效率很低）
select * from city where population>500000;

4.3 like模糊查询¶

#尽量%不在开头，性能差
select countrycode,name from city where countrycode like 'ch%';

4.4 and，or，in，between¶

#and
select * from city where population>500000 and population <600000;

#between and
select * from city where population between 500000 and 600000;

#or（尽量不要使用，all查询，效率低）
select * from city where countrycode='chn' or countrycode='usa';

#in
select * from city where countrycode in ('chn','usa');

#union all(尽量改成这种，效率高)
select * from city where countrycode='chn'
union 
select * from city where countrycode='usa';

4.5 group by + 聚合函数¶

1.统计各个国家城市个数

select countrycode,count(name) from city group by countrycode;

2.统计中国各省人口总和

select district,sum(population)
from city
where countrycode='chn'
group by district;

4.6 order by¶

1.按人口从大到小，查询中国所有城市信息

select * from city
where countrycode='chn'
order by population desc;

2.统计各个国家城市个数

select countrycode,count(name) as c_name
from city
group by countrycode
order by c_name desc;

4.7 limit¶

1.一般放在order by语句之后，做输出限制

统计各个国家城市个数，只显示前10行

select countrycode,count(name) as c_name
from city
group by countrycode
order by c_name desc;
limit 10;

统计各个国家城市个数，显示15-25行

select countrycode,count(name) as c_name
from city
group by countrycode
order by c_name desc;
limit 15,10;            #待验证

select countrycode,count(name) as c_name
from city
group by countrycode
order by c_name desc;
limit 10 offset 15;

4.8 多表连接查询¶

1.传统连接

#查询人口数量小于100的城市名，国家名，人口数
select ci.name,co.name,ci.population
from city as ci,country as co
where ci.countrycode=co.code
and ci.population<100;

2.join内连接

#语法
select * from A join B on A.xx=B.xx where ...

#两表连接查询
select s.sname,count(sc.cno)
from student as s join sc
on s.sno=sc.sno
where s.sname='z3'
group by s.sname;

#四表连接查询 (好好研究一下)
select t.tname,s.sname
from teacher as t join course as c
on t.tno=c.tno
join sc
on c.sno=sc.cno
join student as s
on sc.sno=s.sno
where t.tname='oldguo';

3.外连接（略）

A left join B on A.id=B.id

select t.tname,c.cname 
from teacher as t
left join course as c
on t.tno=c.tno
and t.tname='oldguo';

4.9 子查询（尽量少用，效率很低）¶

select name from country where code=(select countrycode from city where population<100);

尽量改成多表连接查询，性能较高

select co.name 
from country as co 
join city as ci
on co.code=ci.countrycode
where ci.population<100;

最后更新: 2022-02-25 03:53:42