mysql基础

# mysql基础

# 参考资料：

菜鸟教程：https://www.runoob.com/mysql/mysql-select-database.html
博客园：https://www.cnblogs.com/wupeiqi/articles/5713315.html
C语言中文网：http://c.biancheng.net/view/7233.html

1
2
3

MYSQL[一]：https://www.cnblogs.com/wupeiqi/articles/5713315.html
MYSQL[二]：https://www.cnblogs.com/wupeiqi/articles/5713323.html

1
2

https://www.cnblogs.com/wupeiqi/articles/5713330.html (opens new window)

# 一、数据库操作

# 1、显示数据库

show databases;
SHOW DATABASES;

1
2

默认数据库：　　mysql - 用户权限相关数据　　test - 用于用户测试数据　　information_schema - MySQL本身架构相关数据

# 2、创建、删除数据库

# utf-8
CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

# gbk
CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

# utf8mb4
# 这样就可以插入中文
create database  dvadmin_warehouse charset utf8mb4;
create database  dvadmin_warehouse default charset utf-8;

1
2
3
4
5
6
7
8
9
10

drop databases 数据库名称;

drop databases db1;

1
2
3

# 3、使用数据库

use 数据库名
use db_name

1
2

显示当前使用的数据库中所有表：SHOW TABLES;

# 4、用户管理

把ip换成别人电脑的ip这样别人只能在那台电脑上登录mysql

创建用户
    create user '用户名'@'IP地址' identified by '密码';
删除用户
    drop user '用户名'@'IP地址';
修改用户
    rename user '用户名'@'IP地址' to '新用户名'@'IP地址';
修改密码
    set password for '用户名'@'IP地址' = Password('新密码');

1
2
3
4
5
6
7
8

create user 'alex'@'192.168.1.1' identified by '123456';  # 创建用户：alex密码：123456（只能在192.168.1.1这个IP才可以访问）
create user 'alex'@'192.168.1.%' identified by '123456';  # 创建用户：alex密码：123456（IP等于前面三个192.168.1才可以访问）
create user 'alex'@'%' identified by '123456';  # 创建用户：alex密码：123456（所有网络都可以访问）

rename user 'alex'@'%' to 'alex1'@'1234567';  # 修改用户

1
2
3
4
5
6

PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）

可以在mysql表中的user查看用户

# 5、用户授权管理

show grants for '用户'@'IP地址'                  -- 查看权限
grant  权限 on 数据库.表 to   '用户'@'IP地址'      -- 授权
revoke 权限 on 数据库.表 from '用户'@'IP地址'      -- 取消权限

1
2
3

grant all privileges on db1.tb1 TO '用户名'@'IP'   # 给用户除grant外的所有权限，只让它访问db1数据库的tb1表

grant select on db1.* TO '用户名'@'IP'  # 给用户select仅查权限,让它可以访问db2数据库下的所有表

grant select,insert on *.* TO '用户名'@'IP'   # 给用户查和插入权限,让它可以访问所有数据库下的所有表

revoke select on db1.tb1 from '用户名'@'IP'  # 给用户select仅查权限,让它可以访问db2数据库下的tb1表权限

grant all privileges on db1.* to 'alex'@'%';
revoke select on db1.* from 'alex'@'%'; # 删除用户的查看权限

1
2
3
4
5
6
7
8
9
10

权限

all privileges          除grant外的所有权限
select                  仅查权限
select,insert           查和插入权限
...
usage                   无访问权限
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke  all privileges
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和存储过程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
select                  使用select
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(关闭MySQL)
super                   􏱂􏰈使用change master、kill、logs、purge、master和set global。还允许mysqladmin􏵗􏵘􏲊􏲋调试登陆
replication client      服务器位置的访问
replication slave       由复制从属使用

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

数据库.表

对于目标数据库以及内部其他：
            数据库名.*           数据库中的所有
            数据库名.表          指定数据库中的某张表
            数据库名.存储过程     指定数据库中的存储过程
            *.*                所有数据库

1
2
3
4
5

用户名@IP地址

用户只能在改IP下才能访问
            用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%表示任意)
            用户名@%             用户可以再任意IP下访问(默认IP地址为%)

1
2
3

特殊的：（立即生效）

flush privileges，将数据读取到内存中，从而立即生效。

# 6、忘记密码

# 启动免授权服务端
mysqld --skip-grant-tables

# 客户端
mysql -u root -p

# 修改用户名密码
update mysql.user set authentication_string=password('666') where user='root';
flush privileges;

1
2
3
4
5
6
7
8
9

# 二、数据库基本操作

# 1、创建表

create table 表名(
    列名  类型  是否可以为空 默认值，
    列名  类型  是否可以为空 自增 主键
)ENGINE=InnoDB DEFAULT CHARSET=utf8

innodb # 支持事物，几个同时进行操作一个出错回滚到开始(原子性操作)
myisam # 不支持事物

1
2
3
4
5
6
7

# 2.1 基本创建

create table t1(
    id int,
    name char(10)
) engine=innodb default charset='utf8mb4';

1
2
3
4

# 2.2 是否可空

是否可空，null表示空，非字符串（默认可以为空）

not null    - 不可空
null        - 可空

create table t2(
    id int not null,  # 必须要添加数据了
    name char(10)
) engine=innodb default charset='utf8mb4';

1
2
3
4
5
6
7

# 2.3 默认值

默认值，创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值

defalut 值

create table t3(
    id int not null default 2,  # 添加默认值
    name char(10)
) engine=innodb default charset='utf8mb4';

1
2
3
4
5
6

# 2.4 自增

自增，如果为某列设置自增列，插入数据时无需设置此列，默认将自增（！表中只能有一个自增列）

auto_increment
auto_increment primary key

二种方式，因为自增必须加索引所有有二种方式
# 方式一：
create table t4(
    id int not null auto_increment primary key,
    name char(10)
) engine=innodb default charset='utf8mb4';

# 方式二：
create table tb1(
    id int not null auto_increment,
    name int null,
    index(nid)
)engine=innodb default charset='utf8mb4';

create table t4(
    id int not null auto_increment primary key,
    name char(10)
) engine=innodb default AUTE_INCREMENT=20 charset='utf8mb4';# 自增起始值

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

**注意**
1、对于自增列，必须是索引（含主键）。
2、对于自增可以设置步长和起始值
# 基于会话级别：
show session variables like 'auto_inc%';  # 查看全局变量
set session auto_increment_increment=2;  # 设置步长
set session auto_increment_offset=10;  # 起始值
# 基于全局级别：
show global  variables like 'auto_inc%';
set global auto_increment_increment=2;
set global auto_increment_offset=10;

# 也可以直接修改自增的起始值
alter table t1 AUTE_INCREMENT=20  # 修改t1表中自增的值起始值

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 2.5 主键

主键，一种特殊的唯一索引，不允许有空值，如果主键使用单个列，则它的值必须唯一，如果是多列，则其组合必须唯一。

主键：表示约束（不能重复不能有空值）；加速查找

primary key

create table tb1(
    nid int not null auto_increment primary key,
    num int null
)
或
create table tb1(
     nid int not null,
     num int not null,
     primary key(nid,num)	# 可以有多个主键
)

1
2
3
4
5
6
7
8
9
10
11
12

# 2.6 外键

外键，一个特殊的索引，只能是指定内容

! 外键名称是唯一的，外键可以有多个字段但必须是主键和不能为空

constraint 外键名称 foreign key (一对多字段) references 关联表(关联表字段)

! 外键名称是唯一的，外键可以有多个字段但必须是主键和不能为空

constraint fk_class_student foreign key (class_id，class_name) references class(id,name)


# 班级表(一个班级有多个学生)
creat table class(
    id int not null primary key,
    name char(16) not null
)

# 学生表
create table student(
    id int not null primary key,
    name char(32) null ,
    class_id int not null,
    constraint fk_class_student foreign key (class_id) references class(id)
)

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

# 2.7 一对一

# 用户信息
creat table userinfo(
    id int not null auto_increment primary key,
    name char(16) not null,
)engine=innodb default charset=utf8;

# 用户密码
create table admin(
    id int not null primary key,
    username varchar(32) not null ,
    password varchar(32) not null ,
    user_id int not null,
    unique uq_u1 (user_id)
    constraint fk_admin_userinfo foreign key (user_id) references userinfo(id)
)engine=innodb default charset=utf8;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 2.8 一对多

一个班级有多个学生，

# 班级表(一个班级有多个学生)
creat table class(
    id int not null primary key,
    caption char(16) not null
)

# 学生表
create table student(
    sid int not null primary key,
    sname char(32) null ,
    gender ENUM('男','女'),
    class_id int not null,
    constraint fk_class_student foreign key (class_id) references class(id)
)

# 老师表
creat table teacher(
    tid int not null primary key,
    tname char(16) not null
)

# 课程表表
creat table course(
    cid int not null primary key,
    cname char(16) not nullk,
    tearch_id int not null,
    constraint fk_teacher_course foreign key (tearch_id) references teacher(tid)
)

# 成绩表
creat table score(
    sid int not null primary key,
    student_id int not null,
    corse_id int not null,
    number int,
    constraint fk_student_score foreign key (student_id) references student(sid),
    constraint fk_student_course foreign key (corse_id) references course(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

# 2.9 多对多

一个用户可以访问多个服务器，一个服务器可以被多个人访问。

# 用户信息
creat table userinfo(
    id int auto_increment primary key,
    name char(16) not null,
)engine=innodb default charset=utf8;

# 服务器
creat table host(
    id int auto_increment primary key,
    hostname char(16) not null,
)engine=innodb default charset=utf8;

# 用户密码
create table user_host(
    id int not null primary key,
	userid int not null,
    hostid int not null,
    unique uq_u1 (userid,hostid),
    constraint fk_1 foreign key (userid) references userinfo(id),
     constraint fk_2 foreign key (hostid) references host(id)
)engine=innodb default charset=utf8;

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

# 2、删除表

drop table 表名

drop table t1

1
2
3

# 3、清空表

delete from 表名
truncate table 表名	# 清空表中的数据

1
2

# 4、修改表

添加列：alter table 表名 add 列名 类型

删除列：alter table 表名 drop column 列名

修改列：
        alter table 表名 modify column 列名 类型;  -- 类型
        alter table 表名 change 原列名 新列名 类型; -- 列名，类型

添加主键：
        alter table 表名 add primary key(列名);

删除主键：
        alter table 表名 drop primary key;
        alter table 表名  modify  列名 int, drop primary key;

添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);

删除外键：alter table 表名 drop foreign key 外键名称

修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;

删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

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

# 5、基本数据类型

MySQL的数据类型大致分为：数值、时间和字符串

更多参考：
http://www.runoob.com/mysql/mysql-data-types.html
http://dev.mysql.com/doc/refman/5.7/en/data-type-overview.html
https://www.cnblogs.com/wupeiqi/articles/5713315.html

1
2
3
4

 		enum
            枚举类型，
            An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
            示例：
                CREATE TABLE shirts (
                    name VARCHAR(40),
                    size ENUM('x-small', 'small', 'medium', 'large', 'x-large')
                );
                INSERT INTO shirts (name, size) VALUES ('dress shirt','large'), ('t-shirt','medium'),('polo shirt','small');

        set
            集合类型
            A SET column can have a maximum of 64 distinct members.
            示例：
                CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
                INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 6、数据库导入导出

导出现有数据库数据：

mysqldump -u用户名 -p密码数据库名称 >导出文件路径 # 结构+数据
mysqldump -u用户名 -p密码 -d 数据库名称 >导出文件路径 # 结构

mysqldump -u root -p  db1 > db1.sql
mysqldump -u root -p  -d db1 > db1.sql

1
2

导入现有数据库数据：

mysqldump -u root -p密码数据库名称 < 文件路径

mysqldump -u root -p db2 < db1.sql

# 三、表内容操作

# 1、增

insert into 表 (列名,列名...) values (值,值,值...);
insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);
insert into 表 (列名,列名...) select (列名,列名...) from 表;

1
2
3

insert into t1(id,name) values(1,"你");  # 添加一条数据
insert into t1(id,name) values(1,"你"),(2,"我")..;  # 添加多条数据

1
2

# 1.1 将t1表数据插入t2表

insert into 表 (列名,列名...) select (列名,列名...) from 表;


create table t6(id int,name varchar(10))engine=innodb default charset=utf8;
create table t7(id int,name varchar(10))engine=innodb default charset=utf8;  # 创建表
insert into t6(id,name) values(1,"a"),(2,"b");  # 插入数据
insert into t7(id,name) select id,name from t6;

1
2
3
4
5
6
7

# 2、删

delete from 表;
delete from t1;	# t1表中的数据都删除了

delete from 表 where 条件;
delete from 表 where id＝1 and name＝'alex';  # 删除id=1并name=alex的数据
delete from 表 where id＝1 or name＝'alex';  # 删除id=1并name=alex的数据

1
2
3
4
5
6

# 3、改

update 表 set 修改内容
update t1 set age=18;  # 把表中的所有数据都改成10

update 表 set 修改内容 where 条件
update 表 set name ＝ 'alex' where id>1  # 修改id大于1的name='alex'
update 表 set name ＝ 'alex',age=18 where id>1 and name='alex1'  # 修改id大于1name='alex1'，修改为name=alex,age=18

1
2
3
4
5
6

# 4、查

# 4.1 普通条件查询

not in

betwwen and

and

select from 表

select * from t1;	# 查询所有
select id,name from t2;	 # 指定查询手段

1
2

select from 表 where 条件

select nid,name,gender from 表 where id > 1;	# 查询id大于1

别名（as）

select id,name as n from t2;

区间（between and）：select from 表 where id between 开始 and 结束;

select * from t1 where id between 5 and 16;  # 查找t1表中id[5,16] 5到16的数据

在，不在（in，not in）

select * from t1 where id in (11,22,33);  # 找id在()中的
select * from t1 where id not in (11,22,33)  # 不在
select * from t1 where id in (select nid from 表)  # 嵌套跨表

id != 1
id > 1
id >= 1

1
2
3
4
5
6
7

# 4.2 通配符

%：多个字符串

select * from 表 where name like 'ale%'  - ale开头的所有（多个字符串）
select * from 表 where name like '%ale'  - ale结尾的所有（多个字符串）

1
2

_：一个字符

select * from 表 where name like 'ale_'  - ale开头的所有（一个字符）

select * from 表 where name like '_ale'  - ale结尾的所有（一个字符）

select * from 表 where name like '___ale'  - ale结尾的所有（可以使用多个_，一个_表示一个字符）

1
2
3
4
5

# 4.3 限制

limit

select * from 表 limit 5;            - 前5行
select * from 表 limit 1,5;          - 从第1行开始取后5行

1
2

limit offset

select * from 表 limit 5 offset 1    - 从第1行开始取后5行

# 4.4 排序

order_by 列 desc

select * from 表 order by 列 asc              - 根据 “列” 从小到大排列

select * from t7 order by id asc;

1
2
3

order_by 列 asc

select * from 表 order by 列 desc             - 根据 “列” 从大到小排列

select * from t7 order by id desc;

1
2
3

可以合并在一起（解决有重复的可以添加字段）

select * from 表 order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序

select * from t7 order by id desc,sid asc;

1
2
3

# 4.5 分组

group by

select num from 表 group by num
select num,nid from 表 group by num,nid
select num,nid from 表  where nid > 10 group by num,nid order nid desc

1
2
3

group by 搭配聚合函数：count,max,min,sum,avg

select num,nid,count(*),sum(score),max(score),min(score) from 表 group by num,nid

select count(id),name from t7 group by id;	# 以id进行分组，并统计id的个数
select count(id),name from t7 group by id order by id desc; # 并排序
select count(id),name from t7 where id>2 group by id order by id desc;	# id>2

1
2
3
4
5

!使用聚合函数当条件筛选是要加having关键字

select num from 表 group by num having max(id) > 10

select count(id),max(id),name from t7 group by id having max(id)>2  order by id desc;  # 聚合关键字max(id)>2

select count(id) from t1;
select student_id,avg(num) as achievement from score  group by student_id having achievement>60;

1
2
3
4
5
6

注意：group by 必须在where之后，order by之前

# 4.6 连表

create table t34(
    id int auto_increment primary key ,
    name char(10) not null,
    parent_id int,
    constraint fkkb_d foreign key (parent_id) references t33(sid)
) engine=innodb default charset='utf8mb4';

create table t33(
    sid int auto_increment primary key,
    name char(10)
) engine=innodb default charset='utf8mb4';

insert into t33(name) values("老师"),("校长"),("秘书");
insert into t34(name,parent_id) values("YYS",1),("aab",2),("ccc",3);

1
2
3
4
5
6
7
8
9
10
11
12
13
14

注意：连接的表有重名的话使用类名.字段

连表方式一：

select A.num, A.name, B.name
from A,B
Where A.nid = B.nid

select id,t34.name,parent_id,sid,t33.name
from t33,t34
where t34.parent_id=t33.sid;

1
2
3
4
5
6
7

连表方式二：inner join

select A.num, A.name, B.name
from A inner join B
on A.nid = B.nid

select id,t34.name,parent_id,sid,t33.name
from t33 inner join t34
where t34.parent_id=t33.sid;

1
2
3
4
5
6
7

连表方式三：left join 左边表全显示

A表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A left join B
on A.nid = B.nid

select id,t34.name,parent_id,sid,t33.name
from t33 left join t34
on t34.parent_id=t33.sid;

1
2
3
4
5
6
7
8

连表方式四：right join 右边表全显示

B表所有显示，如果B中无对应关系，则值为null
select A.num, A.name, B.name
from A right join B on A.nid = B.nid

select id,t34.name,parent_id,sid,t33.name
from t33 right join t34 on t34.parent_id=t33.sid;

# 可以连接很多表
select score.sid,student.sid
from score
left join student on score.student_id=student.sid
left join course on score.course_id=course.cid
left join class on student.class_id=class.cid
left join teacher on course.teacher_id=teacher.tid

1
2
3
4
5
6
7
8
9
10
11
12
13
14

union:自动去重,union all：不去重

select sid,name from db1
union
select sid,name from db2
-- 如果字段一样就去重

1
2
3
4

# 4.7 distinct去重

select distinct tid from course left join teacher on course.teacher_id=teacher.tid where tname like "李平老师";

给distinct tid字段去重，效率不高，用分组好点group by

1
2
3

# 4.8 子查询

    select sc.student_id,
        (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
        (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
        (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
        count(sc.course_id),
        avg(sc.num)
    from score as sc
    group by student_id desc

 select
	s1.student_id,
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 0,1) as num1,
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 1,1) as num2,
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 2,1) as num3
from score as s1;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 4.9 查询添加条件判断语句case

case when 条件 then 条件成立 else 条件不成立 END

select  course_id,max(num),min(num),case when min(num) < 10 then 0 else min(num) END from score group by course_id ;

-- 按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT
	course_id,
	avg( num ),
	sum( CASE WHEN num < 60 THEN 0 ELSE 1 END ) / sum( 1 ) AS jgl
FROM
	score
GROUP BY
	course_id
ORDER BY
	avg( num ) ASC,
	jgl DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

# 4.10 if 三元运算

if(isnull(条件),条件成功,条件失败)

SELECT
	tname,
	cname,
	avg( if(isnull(score.num),0,score.num) )
FROM
	teacher,
	course,
	score
WHERE
	teacher.tid = course.teacher_id
	AND score.course_id = course.cid
GROUP BY
	course_id
ORDER BY
	avg( num ) DESC;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 4.11 临时表

临时表就是把放到内存里面

(select from score where number>60)：就是把这个当成一张表用

临时表：(select * from score where number>60) 这里有几列，select 就可以选择几个

select sid,number from (select * from score where number>60) as B;

1
2
3

# 5、查看表详细

desc 表名;
desc t1;

1
2

# 6、查看表的创建命令

show create table 表名;
show create table t1;

show create table t1 \G;  # 更规范

1
2
3
4

# 7、唯一索引

unique uq1(num)	 # [唯一索引]
unique uq1(num,sname)  # [联合索引]

create table t1(
	id int not null auto_increment primary key,
	num int,
	sname char(10),
	# unique uq1(num)	 # 约束num字段的值不能重复（可以为空） [唯一索引]
    unique uq1(num,sname)  # 新的数据num和sname不能重复（可以为空）[联合索引]
)

# num=1 sname=1 (1,1)
# num=1 sname=2 (1,2) # 这样就可以 二个加起来不一样就欧克
ps:
	唯一索引：
		约束不能重复（可以为空）
		主键，不能重复（不可以为空）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

# 四、MYSQL练习题

题目：https://www.cnblogs.com/wupeiqi/articles/5729934.html (opens new window)

参考答案：https://www.cnblogs.com/wupeiqi/p/5748496.html (opens new window)

# 1、表关系

# 班级表(一个班级有多个学生)
create table class(
    id int auto_increment primary key,
    caption char(16) not null
)engine=innodb default charset='utf8mb4';

# 学生表
create table student(
    sid int auto_increment primary key,
    sname char(32) null ,
    gender ENUM('男','女'),
    class_id int not null,
    constraint fk_class_student foreign key (class_id) references class(id)
)engine=innodb default charset='utf8mb4';

# 老师表
create table teacher(
    tid int auto_increment primary key,
    tname char(16) not null
)engine=innodb default charset='utf8mb4';

# 课程表表
create table course(
    cid int auto_increment primary key,
    cname char(16) not null,
    tearch_id int not null,
    constraint fk_teacher_course foreign key (tearch_id) references teacher(tid)
)engine=innodb default charset='utf8mb4';

# 成绩表
create table score(
    sid int auto_increment primary key,
    student_id int not null,
    corse_id int not null,
    number int,
    unique uq1(student_id,corse_id),
    constraint fk_student_score foreign key (student_id) references student(sid),
    constraint fk_student_course foreign key (corse_id) references course(cid)
)engine=innodb default charset='utf8mb4';

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

# 2、操作表

- 男女生的数量 -- select gender,count("id") from student group by gender;
- 每个班级所有的学生 -- select caption,count(sname) from student left join class on student.class_id=class.id group by caption;

1、自行创建测试数据
insert into class(caption) values("三年二班"),("一年三班"),("三年一班");
insert into student(sname,gender,class_id) values("钢弹","女",1),("铁锤","女",1),("山炮","男",2);
insert into teacher(tname) values("波多"),("苍空"),("饭岛");
insert into course(cname,tearch_id) values("生物",1),("体育",1),("物理",2);
insert into score(student_id,corse_id,number) values(1,1,60),(1,2,59),(2,2,100);

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号；
-- 查询各自生物和物理的数据，进行连表
SELECT
	A.student_id,
	A.num AS sw_cj,
	A.cname AS sw_cname,
	B.num AS wl_cj,
	B.cname AS wl_cname
FROM
	( SELECT * FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname LIKE "生物" ) AS A
	LEFT JOIN ( SELECT * FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname LIKE "物理" ) AS B ON A.student_id = B.student_id
WHERE
	A.num > B.num;

3、查询平均成绩大于60分的同学的学号和平均成绩；
-- 1.查找所有同学成绩;2.用户group by分组聚合出所有平均成绩;3.在使用where筛选大于60分的使用成绩（！用了聚合函数要记住用having）
SELECT
	student_id,
	avg( num ) AS achievement
FROM
	score
GROUP BY
	student_id
HAVING
	achievement > 60;

4、查询所有同学的学号、姓名、选课数、总成绩；
# 方法一：
SELECT
	student.sid,
	sname,
	A.count1,
	A.sum1
FROM
	student
	RIGHT JOIN (
SELECT
	sid,
	student_id,
	course_id,
	num,
	count( student_id ) AS count1,
	sum( num ) AS sum1
FROM
	score
GROUP BY
	student_id
	) AS A ON student.sid = A.student_id;

# 方法二：
SELECT
	student.sid,
	sname,
	count( score.student_id ),
	sum( num )
FROM
	score
	LEFT JOIN student ON student.sid = score.student_id
GROUP BY
	student_id;

5、查询姓“李”的老师的个数；
SELECT
	count( tid )
FROM
	teacher
WHERE
	tname LIKE "李%";

6、查询没学过“叶平”老师课的同学的学号、姓名；
-- 1.获取李平老师授课的id;2.在得到成绩表中course_id是李平的授课id的，所有学生id;3.查询所有学生表id不在里面的
SELECT
	student.sid,
	sname
FROM
	student
WHERE
	student.sid NOT IN (
SELECT DISTINCT
	student_id
FROM
	score
WHERE
	score.course_id IN ( SELECT cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE tname LIKE "李平老师" )
	);

7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
SELECT
	*
FROM
	( SELECT student_id, course_id FROM score WHERE course_id = 1 OR course_id = 2 ) AS A
	LEFT JOIN student ON A.student_id = student.sid
GROUP BY
	student_id
HAVING
	count( student_id ) = 2;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
-- 1.拿到老师的所有授课id；2.查找score表所有是老师id的所有数据，通过student_id分组，在通过数量=授课数量进行筛选
SELECT
	student.sid,
	sname
FROM
	student
	RIGHT JOIN (
SELECT
	student_id
FROM
	score
WHERE
	course_id IN ( SELECT cid FROM course LEFT JOIN teacher ON teacher.tid = course.teacher_id WHERE tname LIKE "李平老师" )
GROUP BY
	student_id
HAVING
	count( course_id ) = ( SELECT count( cid ) FROM course LEFT JOIN teacher ON teacher.tid = course.teacher_id WHERE tname LIKE "李平老师" )
	) AS A ON student.sid = A.student_id;

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
-- 分别查询所有002，001对应所有的成绩；然后在进行连表筛选
SELECT
	sid,
	sname
FROM
	student
	RIGHT JOIN (
SELECT
	A.student_id,
	A.num AS cj2,
	B.num AS cj1
FROM
	( SELECT * FROM score WHERE course_id = 2 ) AS A
	LEFT JOIN ( SELECT * FROM score WHERE course_id = 1 ) AS B ON A.student_id = B.student_id
WHERE
	B.num < A.num
	) AS C ON C.student_id = student.sid;

10、查询有课程成绩小于60分的同学的学号、姓名；
SELECT
	student.sid,
	sname
FROM
	student,
	( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id ) AS A
WHERE
	A.student_id = student.sid;

11、查询没有学全所有课的同学的学号、姓名；
-- 先查询所有课程的数量，在查询成绩表然后进行分组和聚合判断数量是不是等于课程的数量
SELECT
	*
FROM
	score,
	student
WHERE
	student.sid = score.student_id
GROUP BY
	student_id
HAVING
	count( student_id ) < ( SELECT count( 1 ) FROM course );

12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名；
--     思路：
--         获取 001 同学选择的所有课程
--         获取课程在其中的所有人以及所有课程
--         根据学生筛选，获取所有学生信息
--         再与学生表连接，获取姓名
SELECT
	student.sid,
	sname,
	count1
FROM
	student,
	(
SELECT
	student_id,
	count( course_id ) AS count1
FROM
	score
WHERE
	student_id != 1
	AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 )
GROUP BY
	student_id
	) AS A
WHERE
	A.student_id = student.sid

13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名；
-- 先找到和001的学过的所有人
-- 然后个数 ＝ 001所有学科     ＝＝》 其他人可能选择的更多
SELECT
	student.sid,
	sname,
	count( course_id )
FROM
	score,
	student
WHERE
	student.sid = score.student_id
	AND student_id != 1
	AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 )
GROUP BY
	student_id
HAVING
	count( course_id ) = ( SELECT count( course_id ) FROM score WHERE student_id = 1 );

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名；
--  个数相同
-- 002学过的也学过
SELECT
	student_id,
	sname
FROM
	score
	LEFT JOIN student ON score.student_id = student.sid
WHERE
	student_id IN (
SELECT
	student_id
FROM
	score
WHERE
	student_id != 1
GROUP BY
	student_id
HAVING
	count( course_id ) = ( SELECT count( 1 ) FROM score WHERE student_id = 1 )
	)
	AND course_id IN ( SELECT course_id FROM score WHERE student_id = 1 )
GROUP BY
	student_id
HAVING
	count( course_id ) = ( SELECT count( 1 ) FROM score WHERE student_id = 1 )

15、删除学习“叶平”老师课的SC表记录；
DELETE
FROM
	score
WHERE
	course_id IN ( SELECT cid FROM course, teacher WHERE course.teacher_id = teacher.tid AND teacher.tname LIKE '李平老师2' );

16、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“002”课程的同学学号；②插入“002”号课程的平均成绩；
--     思路：
--         由于insert 支持
--                 inset into tb1(xx,xx) select x1,x2 from tb2;
--         所有，获取所有没上过002课的所有人，获取002的平均成绩
INSERT INTO score ( student_id, course_id, num ) SELECT
sid,
2,
( SELECT avg( num ) FROM score WHERE course_id = 2 )
FROM
	student
WHERE
	sid NOT IN ( SELECT student_id FROM score WHERE course_id = 2 )

17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分；
    select sc.student_id,
        (select num from score left join course on score.course_id = course.cid where course.cname = "生物" and score.student_id=sc.student_id) as sy,
        (select num from score left join course on score.course_id = course.cid where course.cname = "物理" and score.student_id=sc.student_id) as wl,
        (select num from score left join course on score.course_id = course.cid where course.cname = "体育" and score.student_id=sc.student_id) as ty,
        count(sc.course_id),
        avg(sc.num)
    from score as sc
    group by student_id desc

18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select  course_id,max(num),min(num),case when min(num) < 10 then 0 else min(num) END from score group by course_id ;

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
SELECT
	course_id,
	avg( num ),
	sum( CASE WHEN num < 60 THEN 0 ELSE 1 END ) / sum( 1 ) AS jgl
FROM
	score
GROUP BY
	course_id
ORDER BY
	avg( num ) ASC,
	jgl DESC;

20、课程平均分从高到低显示（现实任课老师）；
SELECT
	tname,
	cname,
	avg( if(isnull(score.num),0,score.num) )
FROM
	teacher,
	course,
	score
WHERE
	teacher.tid = course.teacher_id
	AND score.course_id = course.cid
GROUP BY
	course_id
ORDER BY
	avg( num ) DESC;

21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
select
	s1.student_id,
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 0,1) as num1,
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 1,1) as num2,
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 2,1) as num3
from score as s1;

22、查询每门课程被选修的学生数；
select count(course_id) from score group by course_id;

23、查询出只选修了一门课程的全部学生的学号和姓名；
select student.sid,sname from score left join student on score.student_id=student.sid group by student_id having count(course_id)=1;

24、查询男生、女生的人数；
select gender,count(1) from student group by gender;

25、查询姓“张”的学生名单；
select * from student where sname like "张%";

26、查询同名同姓学生名单，并统计同名人数；
select sname,count(1) from student group by sname;

27、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select avg(if(isnull(num),0,num)) as cj from score group by course_id order by cj desc,course_id asc;

28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩；
select student.sid,sname,avg(if(isnull(num),0,num)) as cj from student left join score on student.sid=score.student_id group by student_id having cj>85;

29、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select sname,num from student left join score on score.student_id=student.sid left join course on score.course_id=course.cid where cname like "物理" and num<60;

30、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；
select student.sid,sname from score left join student on student.sid=score.student_id where course_id=3 and num>80;

31、求选了课程的学生人数
select count(1) from (select count(student_id) from score group by student_id) as A;

32、查询选修“杨艳”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select A.student_id,sname,num from student right join (
select student_id,num from score left join course on course.cid=score.course_id left join teacher on teacher.tid=course.teacher_id where tname like "张磊老师" order by num desc limit 0,1) as A
on A.student_id=student.sid;

33、查询各个课程及相应的选修人数；
select cname,count(1) from score right join course on course.cid=score.course_id group by course_id;

34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select DISTINCT s1.course_id,s2.course_id,s1.num,s2.num  from score as s1,score as s2 where s1.num=s2.num and s1.course_id!=s2.course_id;

35、查询每门课程成绩最好的前两名；
select * from (
select
course_id,
student_id,
	num,
	-- 因为遍历s1,要得到s1遍历的每门课程的id
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 0,1) as first_num,
	(select num from score as s2 where s2.course_id=s1.course_id order by num desc limit 1,1) as last_num
from score as s1
) as C
where C.num>=C.last_num and C.num<=C.first_num;

36、检索至少选修两门课程的学生学号；
select student_id from score group by student_id having count(1)>1;

37、查询全部学生都选修的课程的课程号和课程名；
    select course_id,count(1) from score group by course_id having count(1) = (select count(1) from student);

38、查询没学过“叶平”老师讲授的任一门课程的学生姓名；
select sname from student where sid not in(select student_id from score where course_id in (select cid from course left join teacher on teacher.tid=course.teacher_id where tname like "刘海燕老师"));

39、查询两门以上不及格课程的同学的学号及其平均成绩；
    select student_id,count(1),avg(num) from score where num < 60 group by student_id having count(1) > 2

40、检索“004”课程分数小于60，按分数降序排列的同学学号；
select student_id from score where course_id=4 and num<60 order by student_id asc;

41、删除“002”同学的“001”课程的成绩；
    delete from score where course_id = 1 and student_id = 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
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
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385

# 五、视图

更多资料：http://c.biancheng.net/view/2584.html

视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，用户使用时只需使用【名称】即可获取结果集，并可以将其当作表来使用。

其实就是把sql语句放到一个函数中，用的时候直接调用即可

语法格式如下：

CREATE VIEW <视图名> AS <SELECT语句>

# 1、创建视图

# 1.1 单表创建

--格式：CREATE VIEW 视图名称 AS  SQL语句

create view v1 as select * from student where sid > 10;

-- 选择创建的字段
create view v2 (sid,sname) as  select sid,sname from student where sid > 10;

1
2
3
4
5
6

# 1.2 多表创建

1
2

# 2、删除视图

--格式：DROP VIEW 视图名称

DROP VIEW v1

1
2
3

# 3、修改视图

-- 格式：ALTER VIEW 视图名称 AS SQL语句

alter view v1 as select * from student where sid > 15;

1
2
3

# 4、使用视图

使用视图时，将其当作表进行操作即可，由于视图是虚拟表，所以无法使用其对真实表进行创建、更新和删除操作，仅能做查询用。

select * from v1

# 六、触发器

对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。

# 1、创建基本语法

# 插入前
CREATE TRIGGER 触发器名称 BEFORE INSERT ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 插入后
CREATE TRIGGER 触发器名称 AFTER INSERT ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 删除前
CREATE TRIGGER 触发器名称 BEFORE DELETE ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 删除后
CREATE TRIGGER 触发器名称 AFTER DELETE ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 更新前
CREATE TRIGGER 触发器名称 BEFORE UPDATE ON 表名 FOR EACH ROW
BEGIN
    ...
END

# 更新后
CREATE TRIGGER 触发器名称 AFTER UPDATE ON 表名 FOR EACH ROW
BEGIN
    ...
END

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

-- 创建触发器
delimiter // -- 修改结束符号为//，因为里面语句有;号。后面的end就不会执行，只有修改结束符号然后在改回来
CREATE TRIGGER cfq2 BEFORE INSERT ON student FOR EACH ROW
BEGIN
	insert into teacher(tname) values(NEW.SNAME); -- NEW新数据 OLD老数据
END //

delimiter ;

-- 添加student数据就会触发触发器，然后在teacher表添加之前添加数据
insert into student(gender,class_id,sname) values("男",1,"aabb");

-- NEW.SNAME获取新数据行的sname

1
2
3
4
5
6
7
8
9
10
11
12
13

特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。delimiter //：修改语句结束符号为//

# 2、删除触发器

DROP TRIGGER 触发器名称;

# 3、使用触发器

触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。

insert into tb1(num) values(666)

# 七、函数

# 1、内置函数

MySQL中提供了许多内置函数，例如：

https://www.cnblogs.com/wupeiqi/articles/5713323.html (opens new window)

https://dev.mysql.com/doc/refman/5.7/en/functions.html (opens new window)

sum,
lower
upper
..

1
2
3
4

# 2、自定义函数

# 2.1 创建自定义函数

delimiter \\
-- 定义函数传递参数
create function f1(
    i1 int,
    i2 int)
returns int
BEGIN
    declare num int;  -- 定义变量 int num
    set num = i1 + i2;
    return(num);
END \\
delimiter ;

1
2
3
4
5
6
7
8
9
10
11
12

# 2.2 删除函数

drop function 函数名;

# 2.3 执行函数

select f1(11,2) ,name from tb2;

select f1(11,2);

1
2
3

# 八、存储过程

详细资料：http://c.biancheng.net/mysql/85/ (opens new window)

https://www.cnblogs.com/wupeiqi/articles/5713323.html (opens new window)

存储过程是一个SQL语句集合，当主动去调用存储过程时，其中内部的SQL语句会按照逻辑执行。（就是像python函数一样,里面可以放很多条sql语句）

# 1、创建存储过程

# 1.1 无参数存储过程

-- 创建p1
delimiter //
create procedure p1()
begin
	select * from score;
	select * from teacher;
end //
delimiter ;

-- 调用创建的存储过程
call p1()

1
2
3
4
5
6
7
8
9
10
11

在python中调用

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql', db='db1')
cursor = conn.cursor()

effect_row = cursor.callproc("p1")  # 存储过程名称
conn.commit()
data = cursor.fetchall()  # 查看得到的所有结果
print(data,1)
cursor.close()
conn.close()

1
2
3
4
5
6
7
8
9
10
11

# 1.2 接收参数存储过程in,out,inout

对于存储过程，可以接收参数，其参数有三类：

in 仅用于传入参数用
out 仅用于返回值用
inout 既可以传入又可以当作返回值

-- 创建p3成绩大于i3并小于i1从存储过程

delimiter //
create procedure p3(
	in i1 int,  -- 仅用于传入参数用
	out i2 int,  -- 仅用于返回值用存储到session中
	inout i3 int  -- 既可以传入又可以当作返回值存储到session中
)
begin
	set i2 = 123321; -- 可以在里面赋值，这个要在里面赋值才可以在python拿到
	-- set i3 = 100;
	select * from score where num>i3 and num < i1;
end //
delimiter ;

-- 调用
set @v1 = 20;
set @v2 = 0;
call p6(3,@v1,@v2)

-- 查看创建的全局变量
select @v1;
select @v2;

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

在python中使用

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql', db='db1')
cursor = conn.cursor()

effect_row = cursor.callproc("p7", (3, 20, 0))  # 存储过程名称
conn.commit()
data = cursor.fetchall()  # 查看得到的所有结果
print("获取查询得到的结果：", data)
cursor.execute("select @_p7_0,@_p7_1,@_p7_2")
data2 = cursor.fetchall()
print("获取传递存储过程的参数：", data2)
cursor.close()
conn.close()

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 1.3 事务

要在MySQL存储过程中执行ROLLBACK，我们必须在存储过程中声明退出处理程序。在MySQL存储过程中有两种类型的处理程序。

sqlexception
sqlwarning

SQLException都将被执行时，有查询执行和过程中发生任何错误的SQLWarning将执行时的任何警告在MySQL存储过程发生。我们来看看如何在“存储过程”中使用这些块。

-- 创建事物

delimiter \\
create PROCEDURE p11(
		OUT p_return_code tinyint -- out只返回
)
BEGIN
	DECLARE exit handler for sqlexception -- 发生错误了执行下面的，返回1
	BEGIN
		-- ERROR
		set p_return_code = 1;
		rollback;
	END;

	DECLARE exit handler for sqlwarning -- 警告，返回2
	BEGIN
		-- WARNING
		set p_return_code = 2;
		rollback;
	END;

	START TRANSACTION; -- 开始执行事务
		DELETE from tb1;
		insert into tb2(name)values('seven');
	COMMIT;

	-- SUCCESS
	set p_return_code = 0; -- 执行成功，返回0
	END\\
delimiter ;

-- 执行
set @v1 = 0;
CALL p11(@v1);

-- 得到out v1的结果
select @v1;

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

在python中使用

import pymysql

conn = pymysql.connect(host='localhost', port=3306, user='root', passwd='mysql', db='db1')
cursor = conn.cursor()

effect_row = cursor.callproc("p11", (9,))  # 存储过程名称
conn.commit()
data = cursor.fetchall()  # 查看得到的所有结果
print("获取查询得到的结果：", data)
cursor.execute("select @_p11_0")
data2 = cursor.fetchall()
print("获取传递存储过程的参数：", data2)
cursor.close()
conn.close()

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 1.4 游标

详细资料：http://c.biancheng.net/view/7823.html (opens new window)

游标按我的理解就是用在sql编程中对查询结果集的解析，类比jdbc中的resultset对象。FETCH 一行游标指针就往下面移动一行，直到所有行被遍历完成。

游标的使用分为4步： 1、定义游标，指定游标名和查询sql语句 2、打开游标 3、fetch 获取数据，赋值给变量 4、关闭游标

-- 创建游标

delimiter //
create procedure p22()
begin
	declare ssid int; -- 自定义变量1
	declare ssname varchar(50); -- 自定义变量2
	DECLARE done INT DEFAULT FALSE;  -- done = false


	DECLARE my_cursor CURSOR FOR select sid,sname from student;  -- 创建游标
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  -- 没有数据的话变成True

	open my_cursor;  -- 打卡创建的游标
			xxoo: LOOP  -- 进行循环
					fetch my_cursor into ssid,ssname; -- fetch循环拿到sid,sname并赋值给ssid,ssname
					if done then -- 等于True的话就离开loop循环
							leave xxoo;
					END IF;  -- 插入数据
					insert into teacher(tname) values(ssname);
			end loop xxoo;
	close my_cursor;  -- 关闭游标
end  //
delimiter ;

-- 执行游标
CALL p23();

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

# 1.5 动态执行sql(防止sql注入)

我们可以通过传递不同的参数得到我们想要的值，还可以防止sql注入

-- 创建

delimiter \\
CREATE PROCEDURE p29 (
		in nid int
)
BEGIN
		PREPARE prod FROM 'select * from score where num > ?';	 -- 1.预检测某个东西 校验sql语句的合法性
		EXECUTE prod USING @nid;  -- SQL = 格式化 prod + @nid
		DEALLOCATE prepare prod;  -- 执行SQL语句
END\\
delimiter ;

-- 执行
call p29(5);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

# 2、删除存储过程

drop procedure proc_name;

# 3、执行存储过程

-- 无参数
call proc_name()

-- 有参数，全in
call proc_name(1,2)

-- 有参数，有in，out，inout
set @t1=0;
set @t2=3;
call proc_name(1,2,@t1,@t2)

1
2
3
4
5
6
7
8
9
10

# 九、索引

索引补充：https://www.cnblogs.com/wupeiqi/articles/5716963.html (opens new window)

索引，是数据库中专门用于帮助用户快速查询数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置，然后直接获取即可。

作用：

约束
加速查找

MySQL中常见索引有：

普通索引：加速查找
主键索引：加速查找 + 不能为空 + 不能重复
唯一索引：加速查找 + 不能重复
联合索引（多列，上面的都是单个字段）：
联合主键索引
联合唯一索引
联合普通索引
名词：
覆盖索引：在索引文件中直接获取数据

select id from student where id=3;  -- id已经建立索引

索引合并：把多个单列索引合并使用

select * from student where sname="abc" and email="c";  -- sname,email已经建立索引

有无索引的区别：

无索引：从前到后依次查找
有索引：

id			创建额外文件（某种格式存储）
name		创建额外文件（某种格式存储）
email		创建额外文件（某种格式存储）
name email  创建额外文件（某种格式存储）

1
2
3
4

索引种类（某种格式存储）：

hash索引
单值快
范围
btree索引
用了二叉树
连续的时候很快

建立索引：

a. 额外的文件保存特殊的数据结构
b. 查询快；插入更新删除慢
命中索引

select * from student where sname = "aabbcc";  -- 快

select * from student where sname like "aabbcc";  -- 慢，因为不支持like

1
2
3

# 1、普通索引

普通索引仅有一个功能：加速查询

# 1.1 创建表+索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)  -- 索引名称
)

1
2
3
4
5
6
7

# 1.2 创建索引

create index 索引名称 on 表名(字段名);

create index index_name on table_name(column_name);

1
2
3

# 1.3 删除索引

drop index 创建索引名称 on 表名;

drop index index_sname on student;

1
2
3

# 1.4 查看索引

show index from 表名;

show index from student;

1
2
3

注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。

create index ix_extra on in1(extra(32));

# 2、唯一索引

唯一索引有两个功能：加速查询和唯一约束（可含null）

# 2.1 创建表+索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    unique ix_name (name)
)

1
2
3
4
5
6
7

# 2.2 创建索引

create unique index 索引名 on 表名(列名)

create unique index index_sname on student(sid); -- 创建的时候字段中的值不能重复

1
2
3

# 2.3 删除索引

drop unique index 索引名 on 表名

# 3、主键索引

主键有两个功能：加速查询和唯一约束（不可含null）

# 3.1 创建表+索引

create table in1(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    index ix_name (name)
)

OR

create table in1(
    nid int not null auto_increment,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text,
    primary key(ni1),
    index ix_name (name)
)

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

# 3.2 创建索引

alter table 表名 add primary key(列名);

# 3.3 删除索引

alter table 表名 drop primary key;
alter table 表名  modify  列名 int, drop primary key;

1
2

# 4、组合索引(最左前缀匹配)

组合索引是将n个列组合成一个索引

其应用场景为：频繁的同时使用n列来进行查询，如：where n1 = 'alex' and n2 = 666。

# 3.1 创建表+索引

create table in3(
    nid int not null auto_increment primary key,
    name varchar(32) not null,
    email varchar(64) not null,
    extra text
)

1
2
3
4
5
6

# 3.2 创建组合索引

create index ix_name_email on in3(name,email);

最左前缀匹配：-也就是说只有从最左边开始，创建的索引才可以生效

-- 就是我创建了create index ix_name_email on in3(name,email);有name和email

-- 我查询的时候
	生效：
        select * from from in3 where name="a";
        select * from from in3 where name="a" and email="b";
    不生效
    	select * from from in3 where email="b";

-- 也就是说只有从最左边开始，创建的索引才可以生效
-- 在比如	in3(1,2,3)
	生效：（1，2，3），（1，2），（1，3）
	不生效：（2，3），（2），（3）

1
2
3
4
5
6
7
8
9
10
11
12
13

速度组合索引效率 > 索引合并

如上创建组合索引之后，查询：

name and email -- 使用索引
name -- 使用索引
email -- 不使用索引

注意：对于同时搜索n个条件时，组合索引的性能好于多个单一索引合并。

# 5、命中索引(正确使用索引)

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。即使建立索引，索引也不会生效：

- like '%xx'
    select * from tb1 where name like '%cn';
- 使用函数
    select * from tb1 where reverse(name) = 'wupeiqi';
- or
    select * from tb1 where nid = 1 or email = 'seven@live.com';
    特别的：当or条件中有未建立索引的列才失效，以下会走索引
            select * from tb1 where nid = 1 or name = 'seven';
            select * from tb1 where nid = 1 or email = 'seven@live.com' and name = 'alex'
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where name = 999;
- !=
    select * from tb1 where name != 'alex'
    特别的：如果是主键，则还是会走索引
        select * from tb1 where nid != 123
- >
    select * from tb1 where name > 'alex'
    特别的：如果是主键或索引是整数类型，则还是会走索引
        select * from tb1 where nid > 123
        select * from tb1 where num > 123
- order by
    select email from tb1 order by name desc;
    当根据索引排序时候，选择的映射如果不是索引，则不走索引
    特别的：如果对主键排序，则还是走索引：
        select * from tb1 order by nid desc;

- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 使用索引
    name                 -- 使用索引
    email                -- 不使用索引

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

# 6、其他注意事项

- 避免使用select *	!
- count(1)或count(列) 代替 count(*)  !
- 创建表时尽量时 char 代替 varchar	!
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合  !

1
2
3
4
5
6
7
8
9

# 7、！分页

无论是否有索引，limit分页是一个值得关注的问题

-- 方法一： 原始查询，速度慢
select * from student limit 20000,10;

-- 方法二：覆盖索引（和上面的差不多，快一点点）
select * from student where sid in (select * from student limit 20000,10)

-- 方法三：！记录当前页最大或最小ID

-- 1、页面只有上一页和下一页
-- max_id min_id，（拿到该页面最大和最小的id，因为只有上一页和下一页。所有点击下一页只用拿最大id的后10条，或者最小id的前10条）
-- 下一页：
select * from student where id > max_id limit 10;  --
-- 上一页
select * from student where id < min_id order by id desc limit 10;

-- 2、有页码 上一页 192 193 194 [195] 196 197 198 下一页
-- max_id min_id，（拿到选择附件的所有数据条数）
-- 上一页：
    select *
    from tb1
    where
        nid < (select nid from (select nid from tb1 where nid < 当前页最小值 order by nid desc limit 每页数据 *【页码-当前页】) A order by A.nid asc limit 1)
    order by nid desc limit 10;

    select *
    from tb1
    where
        nid < (select nid from (select nid from tb1 where nid < 970  order by nid desc limit 40) A order by A.nid asc limit 1)
    order by nid desc limit 10;

-- 下一页：
    select *
    from tb1
    where
        nid < (select nid from (select nid from tb1 where nid > 当前页最大值 order by nid asc limit 每页数据 *【当前页-页码】) A order by A.nid asc limit 1)
    order by nid desc limit 10;

    select *
    from tb1
    where
        nid < (select nid from (select nid from tb1 where nid > 980 order by nid asc limit 20) A order by A.nid desc limit 1)
    order by nid desc limit 10;

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

# 十、执行时间

详细资料：https://www.cnblogs.com/wupeiqi/articles/5716963.html (opens new window)

explain + 查询SQL - 用于显示SQL执行信息参数，根据参考信息可以进行SQL优化

explain select * from (select nid,name from tb1 where nid < 10) as B;

# 十一、慢日志查询

慢查询配置步骤：https://www.cnblogs.com/huangguowen/p/10234043.html (opens new window)

a、配置MySQL自动记录慢日志

slow_query_log = OFF 是否开启慢日志记录 long_query_time = 2 时间限制，超过此时间，则记录 slow_query_log_file = /usr/slow.log 日志文件 log_queries_not_using_indexes = OFF 为使用索引的搜索是否记录

show variables like '%query%';	-- 查看当前mysql配置参数
set global long_query_time=2;  -- 配置超时时间为2秒
set global slow_query_log_file="C:/DESKTOP-GGSJFOA-slow.log";  -- 日志文件放在哪里

show variables like 'long_query_time';  -- 值查询超时时间

1
2
3
4
5

注：查看当前配置信息： 　　 _show variables like '%query%'_修改当前配置：　　　　set global 变量名 = 值

b、查看MySQL慢日志

mysqldumpslow -s at -a  /usr/local/var/mysql/MacBook-Pro-3-slow.log


--verbose    版本
--debug      调试
--help       帮助

-v           版本
-d           调试模式
-s ORDER     排序方式
             what to sort by (al, at, ar, c, l, r, t), 'at' is default
              al: average lock time
              ar: average rows sent
              at: average query time
               c: count
               l: lock time
               r: rows sent
               t: query time
-r           反转顺序，默认文件倒序拍。reverse the sort order (largest last instead of first)
-t NUM       显示前N条just show the top n queries
-a           不要将SQL中数字转换成N，字符串转换成S。don't abstract all numbers to N and strings to 'S'
-n NUM       abstract numbers with at least n digits within names
-g PATTERN   正则匹配；grep: only consider stmts that include this string
-h HOSTNAME  mysql机器名或者IP；hostname of db server for *-slow.log filename (can be wildcard),
             default is '*', i.e. match all
-i NAME      name of server instance (if using mysql.server startup script)
-l           总时间中不减去锁定时间；don't subtract lock time from total time

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

# 十、Python操作mysql

# 1、pymysql

参考资料：https://www.cnblogs.com/wupeiqi/articles/5713330.html (opens new window)

菜鸟教程：https://www.runoob.com/python3/python3-mysql.html (opens new window)

增删改查的操作

# 1.1 下载

pip3 install pymysql



# 用户表
create table userinfo(
    id int auto_increment primary key,
    u_name char(16) not null
)engine=innodb default charset='utf8mb4';

# 权限表
create table perssion(
    id int auto_increment primary key,
    p_name char(16) not null
)engine=innodb default charset='utf8mb4';

# 用户权限关系表
create table user_persstion(
    id int auto_increment primary key,
    userinfo_id int not null,
    perssion_id int not null,
		unique(userinfo_id,perssion_id),
    constraint fk1 foreign key (userinfo_id) references userinfo(id),
    constraint fk2 foreign key (perssion_id) references perssion(id)
)engine=innodb default charset='utf8mb4';


select p_name,u_name
from user_persstion
	left join userinfo on userinfo.id=user_persstion.perssion_id
	left join perssion on perssion.id=user_persstion.userinfo_id
where u_name like "B";

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

编辑

← MySQL基本查询 sql语句面试50题→