SQL学习——基础篇_sql学习开发

我躺在时间的碎片，斟酌着我的曾经。

0. 数据库的概念

0.1 数据库设计的基本步骤

1. 需求分析
   需求分析是数据库设计的第一步，是最困难、最耗费时间的一步，也是整个设计过程的基础。
   需求分析是否做的充分和准确，直接决定了在其上构建数据库大厦的速度与质量。需求分析做的不好，会导致整个数据库设计返工重做。

2. 概要设计
   概要设计是数据库设计的关键，通过综合、归纳与抽象用户需求，形成一个具体 DBMS 的概念模型，也就是绘制数据库的 E-R 图。

3. 逻辑结构设计
   将 E-R 图转换为多张表，进行逻辑设计，确认各表的主外键，并应用数据库设计的三大范式进行审核，对其优化。
   在这阶段，E-R 图非常重要。要会根据各个实体定义的属性来画出总体的 E-R 图。
   数据库三范式链接

4. 物理设计阶段
   经项目组开会讨论确定 E-R 图后，根据项目的技术实现，团队开发能力及项目的成本预算，选择具体的数据库（如 MySQL 或 Oracle 等）进行物理实现。

5. 数据库实施阶段
   运用 DBMS 提供的数据语言（例如 SQL）、工具及宿主语言（例如 Java），根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行。

6. 数据库运行和维护阶段
   数据库应用系统经过试运行后即可投入正式运行。在运行过程中必须不断地对其进行评价、调整与修改。

1. SQL的语法特点

1. SQL语句可以单行或多行书写，以分号结尾
2. 可用空格和缩进增强语句的可读性
3. MySQL的SQL语句不区分大小写，建议使用大写，例如：SeLeCt * FRoM user。
4. MYSQL不区分大小，但是数据库名和表名是区分大小写的

2. SQL数据类型

2.1 常用类型

2.2 text类型

2.3 number类型

2.4 date类型

2.5 二进制类型

3. 数据库操作

# 创建数据库(字符集为UTF-8)
create database zimo character set utf8;
create database zimo charset utf8;
# 判断不为空后创建数据库
create database if not exists zimo charset utf8;

# 查看所有数据库
show databases;
# 查看某数据库定义信息
show create database zimo;
# 删除数据库
drop database zimo;
# 切换、使用数据库
use zimo;
# 查看正在使用的数据库
select database();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

4. 表的操作

4.1 表的基础操作

use zimo;
# 创建表
create table if not exists zimo_1(
    name varchar(20),
    age tinyint,
    id int primary key unique auto_increment not null
);

# 删除表
drop table zimo_1;
# 修改表名字
rename table zimoa to zimo_1;
# 修改表的字符集
alter table zimo_1 character set gbk;
# 删除主键
alter table zimo_1 drop primary key;
# 查看表属性
describe zimo_1;
# 查看当前数据库中有哪些表
show tables;

/* 修改表的结构格式 */
# 添加列
alter table zimo_1 add qq int unsigned;
# 修改列字段属性
alter table zimo_1 modify name varchar(50) not NULL;
# 修改列名(字段名)
alter table zimo_1 change qq qqid int;
# 删除分类
alter table zimo_1 drop age;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

4.2 插入表记录

在 MySQL 中，用单条 INSERT 语句处理多个插入要比使用多条 INSERT 语句更快。

use zimo;

# 插入表记录
insert into zimo_1 values ("张三",100,11111111);
insert into zimo_1(id, qqid,name) values (null,22222222,"李四");
# 从表A复制数据到表B
insert into zimo_1 select name,id+100,qqid from zimo_1;

# 更新表记录
update zimo_1 set qqid =12345678;   # 执行所有行
update zimo_1 set qqid =11111111 where id=101;

# 删除表记录
delete from zimo_1 where id=102;
delete from zimo_1;     # 并不删除auto_increment(自增）的记录数
truncate table zimo_1;  # 删除auto_increment(自增）的记录数，将其置为零，从新开始

# 查看所有mysql的编码
show variables like 'character%';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5. 基础查询

5.1 建表

use zimo;
drop table zimo_1;
create table if not exists zimo_1(
    name varchar(20),
    id int primary key unique auto_increment not null,
    qqid int not null
);
insert into zimo_1 values ('天一',1,100);
insert into zimo_1 values ('王二',null,200);
insert into zimo_1 values ('张三',null,200);
insert into zimo_1 values ('李四',null,400),('徐五',null,500),
                          ('老六',null,600),('唐七',null,700),
                          ('王八',null,800);
                          
# 插入时检索主键列表，如存在相同主键记录，不更改原纪录，只插入新的记录
insert ignore zimo_1 values ('老六',1006,12345),('刘九',null,900);
# 如上 王八 的新信息不会被传入数据库

# 插入时如发现主键已存在，则替换原记录，即先删除原记录，后insert新记录
replace into zimo_1 values('王八',8,999),('周十',null,1000);
# 这时 王八 的新信息会被更新并传入数据库！

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

5.2 简单查询

# 查询字段
select name,id from zimo_1;
select * from zimo_1;   # 查询所有字段，实际开发中，不推荐使用
select distinct qqid from zimo_1;   # 去重(distinct)查询

# 取别名
select * from zimo_1 as zm;
select * from zimo_1 zm;
select name as n from zimo_1;
select qqid q from zimo_1;

# 对列元素进行操作
select qqid+1000 q from zimo_1;
1
2
3
4
5
6
7
8
9
10
11
12
13

5.3 条件查询

# 条件查询
select * from zimo_1 where id<=5 and qqid>200;
# select * from zimo_1 where id<=5 && qqid>200;
select name,qqid from zimo_1 where qqid between 400 and 600;    # [400,600]左右都包
select name,qqid from zimo_1 where qqid in (400,600);    
select * from zimo_1 where qqid = 200 or qqid = 600 or id > 8;
# select * from zimo_1 where qqid = 200 || qqid = 600 || id > 8;

# 简单like语句
select * from zimo_1 where name like '十_';
select * from zimo_1 where name like '%一%';
select * from zimo_1 where name like '___';
select * from zimo_1 where qqid is null;
1
2
3
4
5
6
7
8
9
10
11
12
13

6. 约束

约束是指数据在数据类型限定的基础上额外增加的要求
关于随着数据update 而更改datetime为当前时间的方法连接（ update_at timestamp null default current_timestamp on update current_timestamp comment ‘更新时间’,）

6.1 主键约束

主键是表的一个特殊字段，该字段能唯一标识该表中的每条信息。例如上述表中的id号是唯一的。
使用主键应注意以下几点：

1. 每个表只能定义一个主键。
2. 主键值必须唯一标识表中的每一行，且不能为 NULL，即表中不存在有相同主键值的数据。这是唯一性原则。
3. 一个字段名只能在联合主键字段表中出现一次。
4. 联合主键不包含不必要的多余字段，当把联合主键的某一字段删除后，如果剩下的字段构成的主键仍然满足唯一性原则，那么这个联合主键是不正确的，这是最小化原则。

唯一主键：

drop table zimo_3;
describe zimo_3;
create table zimo_3(
    name varchar(50) unique,
    # 自增长主键
    id tinyint not null primary key auto_increment,
    qqid int unique
)auto_increment=100;    # 初始化自增长值，如果未初始化，则第一条数据的值为自增长初始值

# 自增字段值不连续
insert into zimo_3 values ('老1',null,100);
/*
    报错：Duplicate entry '100' for key 'zimo_3.qqid'
    因为qqid是唯一的
    但是在这时候自增字段仍然加1了，所以老3的id=102而不是101
 */
insert into zimo_3 values ('老2',null,100);
insert into zimo_3 values ('老3',null,300);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

id为主键

联合主键：

create table zimo_2(
    name varchar(10),
    age tinyint,
    department varchar(20),
    money int,
    sex enum('男','女','中性人') not null,
    primary key(name,department)
);
describe zimo_2;
1
2
3
4
5
6
7
8
9

添加/删除主键约束：

# 删除主键
alter table zimo_2 drop primary key;
# 增加主键
alter table zimo_2 add primary key(name,department);
1
2
3
4

6.2 外键约束

外键约束经常和主键约束一起使用，用来确保数据的一致性。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。
在一对多的中，那个“一”是主表，那个“多”是从表。
主表（父表）删除某条记录时，从表（子表）中与之对应的记录也必须有相应的改变。一个表可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。
定义、使用外键应遵守以下几点：

1. 主表必须已经存在于数据库中。若是当前正在创建的表，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
2. 主表有主键。
3. 主键不能含空值，但允许外键有空值。也就是说，只要外键的每个非空值均出现在指定的主键中，这个外键的内容就是正确的。
4. 在主表的表名后面指定列名或列名的组合。这个列或列的组合必须是主表的主键或候选键。
5. 外键中列的数目必须和主表的主键中列的数目相同。
6. 外键中列的数据类型必须和主表主键中对应列的数据类型相同。

如图：左边为主表 (zimo_main)，右边为从表 (zimo_out)。

drop table zimo_main;
create table zimo_main(
    deptid int(10) primary key ,
    deptname varchar(20)
);
insert into zimo_main values (101,'top'),(102,'jug'),
                             (103,'mid'),(104,'sup'),
                             (105,'adc');
drop table zimo_out;     # 必须先删除主表才能删除从表
create table zimo_out(
    id int(8) primary key unique auto_increment,
    name varchar(10) unique,
    address varchar(20),
    deptid int(10),
    constraint outkey foreign key (deptId) references zimo_main(deptid)
);
insert into zimo_out values (001,'ClearLove','EDG',102),
                            (002,'Kid','IG',105),
                            (003,'Zz1tai','IG',101),
                            (006,'Uzi','RNG',105),
                            (014,'Godv','LGD',103),
                            (015,'Crisp','FPX',104),
                            (016,'Smlz',null,105),
                            (017,'xiye','WE',103);

# 查看table创建语句
show create table zimo_out;
/*  删除外键约束
 外键一旦删除，就会解除主表和从表间的关联关系。
 这里只能删除约束，但还有索引关联
 */
alter table zimo_out drop foreign key outkey;

/*  添加外键约束
 前提是：从表中外键列中的数据必须与主表中主键列中的数据一致或者是没有数据
 */
alter table  zimo_out add constraint outkey
    foreign key(deptid) references zimo_main (deptid);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38

6.3 唯一约束

drop table zimo_unique;
create table zimo_unique(
    id int unique,
    name varchar(10)
);
show create table zimo_unique;
describe zimo_unique;
# 删除唯一约束[ alter table <表名> drop index <唯一约束名> ]
alter table zimo_unique drop index id;
# 添加唯一约束[ alter table <数据表名> add constraint <唯一约束名> unique(<列名>) ]
alter table zimo_unique add constraint unique(id);
1
2
3
4
5
6
7
8
9
10
11

6.4 非空约束

指字段的值不能为空。对于使用了非空约束的字段，如果用户在添加数据时没有指定值，数据库系统就会报错。

drop table zimo_notnull;
truncate table zimo_notnull;
create table zimo_notnull(
    id int(5) primary key,
    name varchar(10) not null
);
describe zimo_notnull;
show create table zimo_notnull;

# 删除非空约束
alter table zimo_notnull change column name name varchar(10) null;
# 添加非空约束
alter table zimo_notnull change column name name varchar(10) not null;
1
2
3
4
5
6
7
8
9
10
11
12
13

6.5 默认值约束

默认值（Default）的完整称呼是“默认值约束，用来指定某列的默认值。在表中插入一条新记录时，如果没有为某个字段赋值，即赋值为（default）。则系统就会自动为这个字段插入默认值。

drop table zimo_default;
truncate table zimo_default;
create table zimo_default(
    id int(5) primary key,
    name varchar(10) default 'zimo'
);
describe zimo_default;
show create table zimo_default;


insert into zimo_default values (6,null);
# 插入默认值
insert into zimo_default values (12,default);

# 删除默认值约束
alter table zimo_default change column name name varchar(10) default null;

# 添加默认值约束
alter table zimo_default change column name name varchar(10) default 'zimo';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

6.6 检查约束

将 CHECK 约束子句置于表中某个列的定义之后，则这种约束也称为基于列的 CHECK 约束。在更新表数据的时候，系统会检查更新后的数据行是否满足 CHECK 约束中的限定条件。

drop table zimo_check;
truncate table zimo_check;
create table zimo_check(
    id int(5) primary key ,
    name varchar(10) unique,
    constraint idcheck check ( id>100 and id<200 )
);
describe zimo_check;
show create table zimo_check;
insert into zimo_check values (200,'zimo');     # 报错

# 删除检测约束
alter table zimo_check drop constraint idcheck;
# 添加检查约束
alter table zimo_check add constraint  idcheck check ( id>100 and id<200 );
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

7. 进阶查询

SELECT
{* | <字段列名>}
[
FROM <表 1>, <表 2>…
[WHERE <表达式>
[GROUP BY <group by definition>
[HAVING <expression> [{<operator> <expression>}…]]
[ORDER BY <order by definition>]
[LIMIT[<offset>,] <row count>]
]

7.1 limit 分页查询

drop table zmfind;
truncate table zmfind;
create table zmfind(
    id smallint primary key auto_increment,
    name varchar(20) not null,
    age tinyint not null ,
    address varchar(50) default '福建省福州市',
    deptid smallint not null
)auto_increment=101;
describe zmfind;
show create table zmfind;
insert into zmfind values (null,'王一',23,default,101),
                          (null,'王二',22,'大连',104),
                          (null,'王三',28,'上海',102),
                          (null,'王四',23,'西安',101),
                          (null,'王五',25,'杭州',102),
                          (null,'王六',25,'深圳',101),
                          (null,'王七',26,'芜湖',103),
                          (null,'王八',28,'重庆',104);

# limit a,b  表示从位置a+1开始，往下查b条数据
select name from zmfind limit 2,6;
# limit b  表示从位置0开始，往下查b条数据
select name,age from zmfind limit 5;
# limit a offset b   表示从位置b+1开始，往下查a条数据
select * from zmfind limit 5 offset 2;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

7.2 order by 排序查询

drop table zmfind;
truncate table zmfind;
create table zmfind(
    id smallint primary key auto_increment,
    name varchar(20) not null,
    age tinyint not null ,
    address varchar(50) default '福建省福州市',
    deptid smallint not null
)auto_increment=101;
describe zmfind;
show create table zmfind;
insert into zmfind values (null,'王一',23,default,101),
                          (null,'王二',22,'大连',104),
                          (null,'王三',28,'上海',102),
                          (null,'王四',23,'西安',101),
                          (null,'王五',25,'杭州',102),
                          (null,'王六',25,'深圳',101),
                          (null,'王七',26,'芜湖',103),
                          (null,'王八',28,'重庆',104);

# order by 排序查询（DESC降序排序，ASC为默认值为序排序）
select * from zmfind order by deptid;
select * from zmfind order by age desc;

# 表示先按deptid升序排序,deptid相同时按age降序排序
select * from zmfind order by deptid,age desc;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

7.3 group by 分组查询

drop table zmfind;
truncate table zmfind;
create table zmfind(
    id smallint primary key auto_increment,
    name varchar(20) not null,
    age tinyint not null ,
    address varchar(50) default '福建省福州市',
    deptid smallint not null
)auto_increment=101;
describe zmfind;
show create table zmfind;
insert into zmfind values (null,'王一',23,default,101),
                          (null,'王二',22,'大连',104),
                          (null,'王三',28,'上海',102),
                          (null,'王四',23,'西安',101),
                          (null,'王五',25,'杭州',102),
                          (null,'王六',25,'深圳',101),
                          (null,'王七',26,'芜湖',103),
                          (null,'王八',28,'重庆',104);

# 单独使用 GROUP BY，查询结果只显示每个分组的第一条记录（但很多数据库未开启该设置）
# group_concat() 函数会把每个分组的字段值都显示出来。
select deptid,group_concat(name,age) from zmfind group by deptid;

# 多重分组
select deptid,age,group_concat(name) from zmfind group by deptid, age;

# 聚合函数 COUNT(),SUM(),AVG(),MAX(),MIN()
select deptid,avg(age),count(id) from zmfind group by deptid;
select deptid,max(age),min(age) from zmfind group by deptid;

# with rollup 是对上面所有记录的运算
select deptid,avg(age),max(age),count(age) from zmfind group by deptid with rollup;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

7.4 having过滤分组

having和 where都用来过滤数据，且 having支持 where关键字中所有的操作符和语法。
where和 HAVING的差异：

1. 一般情况下，where用于过滤数据行，而 having用于过滤分组。
2. where查询条件中不可以使用聚合函数，而 having查询条件中可以使用聚合函数。
3. where在数据分组前进行过滤，而 having 在数据分组后进行过滤 。
4. where针对数据库文件进行过滤，而 having针对查询结果进行过滤。也就是说，where根据数据表中的字段直接进行过滤，而 having是根据前面已经查询出的字段进行过滤。
5. where查询条件中不可以使用字段别名，而 having查询条件中可以使用字段别名。

drop table zmfind;
truncate table zmfind;
create table zmfind(
    id smallint primary key auto_increment,
    name varchar(20) not null,
    age tinyint not null ,
    address varchar(50) default '福建省福州市',
    deptid smallint not null
)auto_increment=101;
describe zmfind;
show create table zmfind;
insert into zmfind values (null,'王一',23,default,101),
                          (null,'王二',22,'大连',104),
                          (null,'王三',28,'上海',102),
                          (null,'王四',23,'西安',101),
                          (null,'王五',25,'杭州',102),
                          (null,'王六',25,'深圳',101),
                          (null,'王七',26,'芜湖',103),
                          (null,'王八',28,'重庆',104);

# 在没有聚合的情况下不推荐使用having，导致效率低下
select name,address,age from zmfind having age>=25;

# where与having的区别
# 1. having的条件中必须是select后跟着的属性
select name,address from zmfind where age>=25;
select name,address from zmfind having age>=25;  # 报错，没找到age
# 2. where语句不能处理分组后的数据
select deptid,avg(age) agev from zmfind group by deptid having agev>25;
select deptid,avg(age) agev from zmfind group by deptid where agev>25;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30

7.5 子查询

1. in | not in
   当表达式与子查询返回的结果集中的某个值相等，则返回 true，否则返回 false。若使用关键字 not，则返回值正好相反。
2. exists | not exists
   用于判断子查询的结果集是否为空，若子查询的结果集不为空，返回 true，否则返回 false。若使用关键字 not，则返回的值正好相反。

子查询的注意点：

• 子查询语句可以嵌套在 SQL 语句中任何表达式出现的位置
• 只出现在子查询中而没有出现在父查询中的表不能包含在输出列中

select * from (select * from result); 			# 错误语句
select * from (select * from result) as temp;	# 正确写法
1
2

drop table zimo_son;
truncate table zimo_son;
create table zimo_son(
    id smallint primary key auto_increment,
    name varchar(20) not null,
    age tinyint not null ,
    sex enum ('男','女'),
    money smallint not null
)auto_increment=101;
describe zimo_son;
show create table zimo_son;
insert into zimo_son values (null,'王一',23,'男',3000),
                          (null,'王二',22,'女',5000),
                          (null,'王三',28,'男',6000),
                          (null,'王四',23,'女',7000),
                          (null,'王五',25,'男',11000),
                          (null,'王六',25,'女',8000),
                          (null,'王七',26,'女',6000),
                          (null,'王八',28,'男',5000);

# 子查询

# 年龄大于25的，但工资低于平均工资单人
select * from zimo_son where age>25
        and money < (select avg(money) from zimo_son);
# 工资高于男生平均工资的女生数目
select count(id) from zimo_son where money >
    (select avg(money) from zimo_son group by sex having sex='男')
    and sex='女'
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29

7.6 正则查询

drop table zimo_re;
truncate table zimo_re;
create table zimo_re(
    id smallint primary key auto_increment,
    name varchar(20) not null,
    age tinyint not null ,
    sex enum ('男','女'),
    weibo text
)auto_increment=101;
describe zimo_re;
show create table zimo_re;
insert into zimo_re values 
(null,'王一',23,'男','第一次握手：客户端给服务器发送一个SYN段(在 TCP 标头中 SYN 位字段为 1 的
 TCP/IP 数据包), 该段中也包含客户端的初始序列号(Sequence number = J)。'),
(null,'王二',22,'女','第二次握手：服务器返回客户端 SYN +ACK 段(在 TCP 标头中SYN和ACK位字段都为 1 的
 TCP/IP 数据包)， 该段中包含服务器的初始序列号(Sequence number = K)；同时使 
 Acknowledgment number = J + 1来表示确认已收到客户端的 SYN段(Sequence number = J)。'),
(null,'王三',28,'男','第三次握手：客户端给服务器响应一个ACK段(在 TCP 标头中 ACK 位字段为 1 的 TCP/IP
 数据包), 该段中使 Acknowledgment number = K + 1来表示确认已收到服务器的 SYN段(Sequence number = K)'),

(null,'王四',23,'女','第一次挥手：A数据传输完毕需要断开连接，A的应用进程向其TCP发出连接释放报文段
（FIN = 1,序号seq = u）,并停止再发送数据，主动关闭TCP连接，进入FIN-WAIT-1状态，等待B的确认。'),
(null,'王五',25,'男','第二次挥手：B收到连接释放报文段后即发出确认报文段（ACK=1，确认号ack=u+1,序号seq=v）
,B进入CLOSE-WAIT关闭等待状态,此时的TCP处于半关闭状态，A到B的连接释放。而A收到B的确认后，
进入FIN-WAIT-2状态，等待B发出的连接释放报文段。'),
(null,'王六',25,'女','第三次挥手：当B数据传输完毕后，B发出连接释放报文段（FIN = 1，ACK = 1，序号seq = w
,确认号ack=u+1）,B进入LAST-ACK（最后确认）状态，等待A 的最后确认。'),
(null,'王七',26,'女','第四次挥手：A收到B的连接释放报文段后，对此发出确认报文段（ACK = 1，seq=u+1，
ack=w+1）,A进入TIME-WAIT（时间等待）状态。此时TCP未释放掉，需要经过时间等待计时器设置的时间2MSL后，
A才进入CLOSE状态。'),
(null,'王八',28,'男',null);

select weibo from zimo_re where weibo regexp '第一次.*?';

select weibo from zimo_re where weibo regexp '.三次.手.*';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35