AskeyNil

SQL 的分类

SQL： Structrued Query Language 结构化的查询语言

  1. DDL ：数据定义语言，定义数据库、表结构
    1. create 创建
    2. drop 删除
    3. alter 修改
    4. truncate 截断
  2. DML： 数据库操纵语言，操作的对象是数据
    1. insert 插入
    2. update 更新
    3. delete 删除
  3. DCL ：数据库控制语言，控制的是权限
    1. grant 授权
    2. revoke 取消授权
  4. DQL ：数据库查询语言，查询数据
    1. select 查询

常用命令

DDL（数据定义语言）

创建数据库

1
2
 
# 用 xxx 为数据库名称创建一个数据库
create database xxx;

删除数据库

1
2
 
# 删除名称为 xxx 的数据库
drop database xxx;

修改数据库

1
2
3
4
5
 
# 修改数据库编码
# 修改 xxx 数据库的编码为 gbk
alter database xxx character set gbk;

# 一般很少修改数据库，最常用的为修改数据库编码，虽然这也非常少用到。

显示数据库

1
2
3
4
5
 
# 显示所有数据库
show databases;

# 显示 xxx 数据库创建的语句和编码
show create database xxx;

创建数据表

1
2
3
4
5
6
7
8
9
10
11
12
13
 
/* 
语法：
create table 表名（
                            字段名  类型(长度) [约束]，
                            字段名  类型(长度) [约束]，
                            ...）;
*/

# 🌰
create table aa (
                        id int primary key auto_increment,
                        username varchar(20));
# 创建表 aa，aa 中有两个字段分别是 int 类型的主键 id 和 varchar 类型的 username

删除数据表

1
2
3
4
5
6
7
8
 
/*
语法：
drop table 表名
*/

# 🌰
drop table aa;
# 删除表 aa

修改数据表

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
 
/*
语法
1. 在表中添加列
alter table 表名 add 列名 类型(长度) [约束];
2. 修改表中存在列的类型长度
alter table 表名 modify 列名 类型(长度) [约束];
3. 修改列名
alter table 表名 change 旧列名 新列名 类型(长度) [约束];
4. 删除列
alter table 表名 drop 列名;
5. 修改表名（比较少用）
rename table 表名 to 新表名;
*/

# 🌰
# 1. 在 aa 表中添加一列名为 age
alter table aa add age int;
# 2. 将 aa 表中的 username 的长度修改为 30
alter table aa modify username varchar(30);
# 3. 修改 aa 表中 username 的名称为 nickname
alter table aa change username nickname varchar(30);
# 4. 删除 aa 表中的 age 列
alter table aa drop age;
# 5. 修改 aa 的表名为 bb
rename table aa to bb;

显示数据表

1
2
3
4
5
6
7
8
 
# 显示当前数据库中 所有的数据表
show tables;

# 显示 xxx 表中的具体结构 
desc xxx;

# 显示 xxx 表的建表结构
show create table xxx;

DML（数据库操纵语言）

增加数据

1
2
3
4
5
6
7
8
9
10
11
12
13
 
/*
语法：
1. 添加一条记录，对具体的列进行赋值，若是字符或者日期类型需要加 '' 引号
insert into 表名 (列 1, 列 2, 列 3) values(值 1, 值 2, 值 3);
2. 添加一条记录，对表中所有的列进行赋值，按建表语句中的顺序
insert into 表名 values(值 1, 值 2, 值 3)
*/

# 🌰
# 1. 将 username 为 'zhangsan' 数据添加到 aa 表中
insert into aa (username) values('zhangsan');
# 2. 将 null, lisi 添加到 aa 数据库中, 因为 id 是 primary key auto_increment 传递 null 给它，其会自增
insert into aa values(null, 'lisi');

删除数据

1
2
3
4
5
6
7
8
9
10
11
 
/*
语法：
delete from 表名 [where 条件]
将满足条件的所有表删除，如果不加条件，即为所有都满足，删除所有表记录
*/

# 🌰
# 1. 将表中 username 为 lisi 的记录删除
delete from aa where username='lisi'
# 2. 逐一删除 aa 表中所有记录
delete from aa;

修改数据

1
2
3
4
5
6
7
8
9
10
11
 
/*
语法：
update 表名 set 字段名1=值1，字段名2=值2 [where 条件];
将满足条件的数据中的 字段名1 改为 值1 ，字段名2 改为 值2
*/

# 🌰
# 1. 将 aa 数据库中 username 为 zhangsan 的记录的 age 改为 20
update aa set age = 20 where username = 'zhangsan'
# 2. 不带 where, 表中所有记录的 age 都改为 20
update aa set age = 20;

DCL（数据库控制语言）

MySQL账户体系，根据账户所具有的权限的不同，MySQL 的账户可以分为以下几种

  1. 服务实例级账号：启动一个 mysqld，即为一个数据库的实例，如果某用户如 root，拥有服务实例级分配的权限，那么该账号就可以删除所有的数据库，连同这些库中的表
  2. 数据库级别账号：对特定数据库进行增删改查的操作
  3. 数据表级别账号：对特定表执行增删改查的操作
  4. 字段级别的账号：对某些表的特定字段进行操作
  5. 存储程序级别的账号：对存储程序进行增删改查的操作

！注意：进行账户操作时，需要使用 root 账户登录，这个账户拥有最高的实例权限。账户的操作主要包括创建账户、删除账户、修改密码、授权权限等。

1
2
3
4
 
# 常用的权限主要有
create、 alter、 drop、 insert、 update、 delete、 select
# 如果分配所有权限 可以使用
all privileges

创建账户、授权

1
2
3
4
5
6
7
8
9
10
11
 
# 创建账户
create user '用户名'@'主机名' identified by '密码';

# 授权
grant 权限 on 数据库.表名 to '用户名'@'主机名';

# 🌰
# 创建 'zhangsan'@'localhost' 账号
create user 'zhangsan'@'localhost' identified by '123456';
# 将查询 demo 数据库中 user 表的权限授予 'zhangsan'@'localhost' 账号
grant select on demo.user to 'zhangsan'@'localhost';
  1. 可以操控demo数据库的 user 表，方式为 demo.user
  2. 访问主机通常使用 % 表示此账户可以用任何 ip 的主机登录访问此数据库
  3. 访问主机可以设计为 localhost 或具体的 ip 表示只允许本机和特定的 ip 才可以访问

查看用户权限

1
 
show grants for 'zhangsan'@'localhost';

删除权限

1
2
3
4
5
6
7
 
revoke 权限名称 on 数据库.表名 from '用户名'@'主机名';

# 🌰
# 删除 zhangsan 对 demo 数据中 user 表查询的权限
revoke select on demo.user from 'zhangsan'@'localhost';
# 刷新权限
flush privileges

修改密码

1
2
3
4
5
 
set password for '用户名'@'主机名' = password('新密码');

# 🌰
# 将 'zhangsan'@'localhost' 用户密码设置为 123456
set password for 'zhangsan'@'localhost' = password('123456');

删除账户

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
/*
语法 1：
drop user '用户名'@'主机名';
语法 2：
delete from user where user='用户名';

推荐使用语法 1 的方式删除用户，如果语法 1 删除不成功，再使用语法 2 的方式。
*/

# 🌰
# 方式 1
drop user 'zhangsan'@'localhost';
# 方式 2
delete from user where user='zhangsan';

DQL（数据库查询语言）

准备工作

为了后面的 🌰 方便，准备了一份数据

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
 
# 准备一张商品分类表(分类ID,分类名称,分类描述)
create table category(
    cid int primary key auto_increment,
      cname varchar(10),
      cdesc varchar(50)
);

# 准备一张商品表(商品编号,商品名称,商品价格,商品描述,商品分类编号)
create table product(
    pid int primary key auto_increment,
      pname varchar(10),
      price double,
      pdesc varchar(50),
      cno   int
);

# 数据的准备
insert into category values(null,'手机数码','小手机');
insert into category values(null,'鞋靴箱包','包包');
insert into category values(null,'香烟酒水','黄鹤楼,茅台');
insert into category values(null,'馋嘴零食','卫龙辣条,周黑鸭');

insert into product values(null,'锤子',2999,'可以砸榴莲的手机',1);
insert into product values(null,'小米',1599,'为发烧而生',1);
insert into product values(null,'李宁',99,'不走寻常路',2);
insert into product values(null,'耐克',399,'just do it',2);
insert into product values(null,'黄鹤楼',20,'饭后一根烟,胜做活神仙',3);
insert into product values(null,'卫龙辣条',5,'卫龙辣条加料不加价',4);
insert into product values(null,'老干妈辣椒酱',9,'永远不变的味道',4);
insert into product values(null,'老干爹辣椒酱',19,'永远不变的味道',4);
insert into product values(null,'黄鹤楼',20,'饭后一根烟,胜做活神仙',3);
 
1
2
3
4
 
/*
基本语法：
select [distinct]*[列名1, 列名2] from 表 [where 条件]
*/

简单查询

1
2
3
4
5
6
7
8
9
10
 
# 🌰
# 1. 查询所有商品
select * from product;
# 2. 查询商品名和商品价格
select pname, price from product;
# 3. 别名查询，使用关键字 as，as 可以省略
select * from product as p;                # 表别名
select pname as pn from product;    # 列别名
# 4. 去重复值
select distinct price from product;

条件查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
/*
where 后的条件写法：
1. 比较运算符：>(大于), <(小于), =(等于), >=(大于等于), <=(小于等于), !=(不等于), <>(不等于，老的写法)
2. 逻辑运算符：or(或), and(与)
3. is null        ： 专门用来判断是否为空
4. is not null： 专门用来判断是否非空
5. like 模糊查询：
                        _    ：代表一个字符
                        % ：代表任意个字符
6. in 在某个特定数据中取值
7. between 在给定范围内取值
*/
# 1. 查询商品名称为十三香的商品的所有信息
select * from product where pname = '十三香';
# 2. 查询商品价格大于 60 元的所有商品信息
select * from product where price > 60;
# 3. 查询商品价格大于 60 元且分类为 2 的所有商品信息
select * from product where price > 60 and cno = 2;
# 4. 查询商品名称中带有 新 字的商品信息
select * from product where pname like '%新%';
# 5. 查询商品pid 为2,5,8的数据
select * from product where pid in (2, 5, 8)
# 6. 查询商品pid 在 2 与 8 之间的数据
select * from product where pid between 2 and 8;

排序查询

1
2
3
4
5
6
7
8
9
 
# asc 升序， desc 降序
# order by 列 [asc|desc]

# 🌰
# 1. 查询所有商品，按价格进行排序，默认升序
select * from product order by price;
# 2. 查询名称含有 新 的商品，且按价格降序排序
select * from product where pname like '%新%' order by price desc;

聚合查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
/*
sum        ： 和
avg        ： 平均值
max        ：    最大值
min        ：    最小值
count ： 个数
*/

# 🌰
# 1. 获取所有商品的价格总和
select sum(price) from product;
# 2. 获取所有商品的平均价格
select avg(price) from product;
# 3. 获取所有商品的个数
select count(*) from product;

分组查询

1
2
3
4
5
6
7
8
 
# group by 列
# having 关键字可以让我们筛选分组后的各种数据

# 🌰
# 1. 根据 cno 字段分组，分组后统计各组的个数
select cno, count(*) from product group by cno;
# 2. 根据 cno 字段分组，分组后统计各组的平均价格，且平均价格大于 60
select cno, avg(price) from product group by cno having avg(price) > 60;

分页查询

1
2
3
4
5
6
 
# limit ?, ?
# 第一个 ？表示跳过前面的多少条，第二个 ？表示返回多少条

# 🌰
# 查询从第二条开始的两条商品信息，即跳过第一条，返回两条
select * from product limit 1, 2;

多表查询

准备工作

首先，介绍一下，表之间的关系，约束如何表示

1
2
3
4
5
6
7
8
9
10
11
12
 
/*
一共 5 大约束：
    1. 主键约束：primary key 非空且唯一
    2. 唯一约束：unique             唯一可以为空
    3. 非空约束：not null         不能为空
    4. 检查约束：check(条件)     mysql中没有设置检查约束，可以直接忽略
    5. 外键约束：foreign key 约束从表中的记录必须参考主表
    
    1-3 很好理解，4我们不做考虑，简要描述 5 的写法和用法
*/
# 下面的建表语句中 该表的 cno 值必须依赖 category 表中的 cid 值
foreign key(cno) references category(cid)

为了后面的 🌰 方便，准备了一份数据

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
 
# 用户表 (用户的ID,用户名,密码,手机)
create table user(
      uid int primary key auto_increment,
    name varchar(10),
    password varchar(20),
    mobile varchar(11)
  );
  
# 订单表 (订单编号,总价,订单时间 外键-->用户的ID)
create table orders(
      oid int primary key auto_increment,
    total double,
    otime timestamp,
    uno int,
    foreign key(uno) references user(uid)
);
  
# 商品分类表(分类ID,分类名称,分类描述)
create table category(
     cid int primary key auto_increment,
    cname varchar(20),
    cdesc varchar(50)
);

# 商品表 (商品ID, 商品名称,商品价格,商品描述,外键cno)
create table product(
    pid int primary key auto_increment,
    pname varchar(10),
    price double,
    pdesc varchar(50),
     cno int,
     foreign key(cno) references category(cid)
);

# 订单项: 中间表(订单ID,商品ID,商品数量,订单项总价)
  create table orderitem(
      ono int,
    pno int,
    num int,
    subtotal double,
  foreign key(ono) references orders(oid),
    foreign key(pno) references product(pid)
  );
  
# 准备数据: 假设张三用户注册
insert into user values(1,'zs','123','110');

# 准备数据: 插入分类表的数据
insert into category values(1,'手机数码','小手机');
insert into category values(2,'鞋靴箱包','包包');
insert into category values(3,'香烟酒水','黄鹤楼,茅台');
insert into category values(4,'馋嘴零食','卫龙辣条,周黑鸭');
insert into category values(5,'电脑办公','联想电脑外星人');

# 准备数据 : 插入商品表的数据
insert into product values(1,'锤子',2999,'可以砸榴莲的手机',1);
insert into product values(2,'小米',1599,'为发烧而生',1);
insert into product values(3,'李宁',99,'不走寻常路',2);
insert into product values(4,'耐克',399,'just do it',2);
insert into product values(5,'黄鹤楼',20,'饭后一根烟',3);
insert into product values(6,'黄鹤楼',20,'饭后一根烟',3);
insert into product values(7,'卫龙辣条',5,'卫龙辣条加料不加价',4);
insert into product values(8,'老干妈辣椒酱',9,'永远不变的味道',4);
insert into product values(9,'老干娘辣椒酱',null,'永远不变的味道',4);
insert into product values(10,'热干面',10,'大武汉热干面',null);

# 准备数据: 假设张三在我们的商城下两个订单
 # zs买东西: 
 # 1号订单:
      # 锤子: 1  10  29990
      # 李宁: 3  2    198
        # 向 订单表 插入1号订单信息(订单编号,订单总价,订单时间,用户编号)      
insert into orders values(1,30000,null,1);
        # 向 订单项表 插入1号订单包含的商品信息(订单编号,商品编号,商品数量,商品小计)
insert into orderitem values(1,1,10,29990);
insert into orderitem values(1,3,2,198);

 # 2号订单:
      # 卫龙辣条: 7  10 50
      # 老干妈辣椒酱:8 2 18
      # 李宁: 3  1    99 
        # 向 订单表 插入2号订单信息(订单编号,订单总价,订单时间,用户编号)            
insert into orders values(2,150,null,1);
        # 向 订单项表 插入2号订单所包含的商品信息(订单编号,商品编号,商品数量,商品小计)
insert into orderitem values(2,7,10,50);
insert into orderitem values(2,8,2,18);
insert into orderitem values(2,3,1,99);

交叉连接查询

笛卡尔积：两张表的乘积，查询的结果没有什么实际意义

1
2
 
# product 表和 category 的笛卡尔积
select * from product, category;

内连接查询

隐式内连接
1
2
 
# product 表中 cno 等于 cateory表中 cid 的数据
select * from product p, category c where p.cno = c.cid;
显式内连接
1
2
 
# inner join 关键字 内连接
select * from product p inner join category c on p.cno = c.cod;

外连接查询

左外连接

以左表为基础，将左表中所有的记录都查询出来，如果没有对应的记录，就用 null 填充

1
 
select * from product p left outer join category c on p.cno = c.cid;
右外连接

以右表为基础，将右表中所有的记录都查询出来，如果没有对应的记录，就用 null 填充

1
 
select * from product p right outer join category c on p.cno = c.cid;

子查询

为了后面的 🌰 方便，准备了一份数据

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
39
 
# 员工信息表
create table emp(
    empno int comment '员工编号',
    ename varchar(50) comment '员工姓名',
    job varchar(50) comment '员工工作',
    mgr int comment '员工经理编号',
    hiredate date comment '员工入职日期',
    sal decimal(7,2) comment '员工工资', 
    comm decimal(7,2) comment '员工奖金',
    deptno int comment '员工所处部门'
);

insert into emp values(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20);
insert into emp values(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
insert into emp values(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
insert into emp values(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
insert into emp values(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20);
insert into emp values(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
insert into emp values(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20);
insert into emp values(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
insert into emp values(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
insert into emp values(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10);
insert into emp values(7981,'MILLER2','CLERK',7788,'1992-01-23',2600,500,20);

# 部门信息表
create table dept(
    deptno int comment '部门编号',
    dname varchar(14) comment '部门的名称',     
    loc varchar(13) comment '部门所处的位置'
);

insert into dept values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
insert into dept values(40, 'OPERATIONS', 'BOSTON');

单行子查询

1
2
 
# 查询出高于 10 号部门的平均工资的员工信息
select * from emp where sal > (select avg(sal) from emp where deptno = 10);

多行子查询

1
2
 
# 查询出比 20 号部门任何员工工资高的员工信息 in, not in, any, all
select * from emp where sal > all(select sal from emp where deptno = 20);

视图（虚表）

  1. 视图其实就是将一条复杂且常用的 SQL 查询语句封装成一张虚表
  2. 视图本身不存储任何的数据，所有的数据都存放在原来的表中，所以可以把视图看成是一张逻辑上的表
  3. 当原始表数据发生变化的时候，视图中的数据也发生变化
  4. 视图这张虚表也可以进行数据更新操作

创建视图

1
2
3
 
create view 视图名称 as 查询语句;
# 1. 视图中的字段名称不能有重复的
# 2. 为了方便区分，建议用 v_ 开头表示视图

查看视图

1
2
 
# 该方法不仅可以看到表，也可以看到视图
show tables;

使用视图

视图的使用方法与表的使用方法一致

删除视图

1
 
drop view 视图的名称;

事务

事务其实就是一系列的 SQL 操作，要么都成功，要么都失败，不允许部分执行成功，部分执行失败的情况发生。

事务的特性 ACID

  1. 原子性(Atomicity)
    • 一个事务必须被视为一个不可分割的最小单元。
  2. 一致性(Consistency)
    • 数据库总是从一个一致性的状态转换到另一个一致性的状态。
  3. 隔离性(Isolation)
    • 一个事务的执行，不受其他事务的影响。
  4. 持久性(Durability)
    • 事务一旦提交，则其所做的修改会永远保存到数据库中（此时即使系统崩溃，修改的数据也不会丢失）。

事务的使用

1
2
3
4
5
6
 
# 开启事务
begin;
# 提交事务
commit;
# 回滚事务
rollback;

索引

索引是一种排好序的快速查找的数据结构，它帮助数据库高效的查询数据

优点：

  1. 提高数据查询的效率，降低数据库的 IO 成本
  2. 通过索引对数据进行排序，降低数据排序成本，降低 CPU 的消耗

缺点：

  1. 索引本身也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也要占用空间
  2. 虽然索引大大提高了查询的速度，同时反向影响了增删改的操作效率，因为表中数据变化之后，会导致索引内容不准，所以需要更新索引表信息，增加数据库的工作量。
  3. 随着业务的不断变化，之前建立的索引可能不满足我们的查询需求，需要消耗我们的时间去更新索引

索引的使用

查看索引

1
 
show index from 表名;

创建索引

1
2
3
 
# 如果指定字段是字符串，需要指定长度，长度最好与定义字段时一致
# 字段类型如果不是字符串，可以不填长度
create index 索引名称 on 表名(字段名(长度));

删除索引

1
 
drop index 索引名称 on 表名;

博客内容遵循 署名-非商业性使用-相同方式共享 4.0 国际 (CC BY-NC-SA 4.0) 协议

本文永久链接是：https://www.askeynil.com/2019/Basics-of-MySQL.html

评论