mysql 管理命令
mysql> status #查看数据库的状态（\s）
mysql> exit quit # 退出数据库（\q）
mysql> \c #终止当前的SQL语句
mysql> help #查看帮助信息（\h、？、\?）
mysql> source #导入数据（\.）
mysql> use #切换数据库（\u）
mysql> tee /tmp/a.log #记录操作日志（\T）
mysql> show slave status\G #将结果展示成key:value的形式
mysqladmin管理命令
#设置密码 password
[root@db01 ~]# mysqladmin -uroot -p1 password '123'
#关闭实例 shutdown
[root@db01 ~]# mysqladmin -uroot -p1 shutdown
#创建数据库 create
[root@db01 ~]# mysqladmin -uroot -p123 create oldboy2
#删除数据库 drop
[root@db01 ~]# mysqladmin -uroot -p123 drop oldboy2
#查看MySQL的参数 variables
[root@db01 ~]# mysqladmin -uroot -p123 variables
#刷新binlog日志 flush-log
[root@db01 ~]# mysqladmin -uroot -p123 flush-log
#重载授权表 reload
[root@db01 ~]# mysqladmin -uroot -p123 reload
#判断MySQL是否存活
[root@db01 ~]# mysqladmin -uroot -p123 ping
什么是SQL语句？
结构化查询语言
1.SQL语句-DDL（data definition language）
针对库的DDL
# 增（create）
mysql> create database db;
mysql> create database db2 charset utf8 collate utf8_general_ci;
# 删（drop）
mysql> drop database db;
# 改（alter）
mysql> alter database zls charset gbk;
##DQL ，查看建库语句（字符集）
mysql> show create database zls;
+----------+----------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------+
| zls | CREATE DATABASE `zls` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
针对表的DDL
## 数据类型
整型
int -2^31 ~ 2^31-1
tinyint -128 ~ 127
浮点型
float (255,30)
double(255,30)
decimal(65,30)
字符串类型
char 定长 char(20)
varchar 变长 varchar(20)
枚举类型
enum 多选一
set 多选多
日期类型
date 年月日
time 时分秒
datetime 年月日+时分秒
year 年
timestamp 年月日+时分秒约束自动填充
##数据约束
1.非空： not null 不可为空（一般与 default 0 使用）
2.主键： primary key 不为空且唯一
3.自增： auto_increment
4.无符号： unsigned
5.默认值： default
6.0填充： zerofill
7.唯一： unique（not null unique 变成主键）
# 增
create table student(
id int primary key auto_increment,
name varchar(20) not null,
age tinyint unsigned not null,
gender enum('f','m'),
cometime datetime default NOW());
create table student(
id int primary key auto_increment comment '学生学号',
name varchar(20) not null comment '学生姓名',
age tinyint unsigned not null comment '学生年龄',
gender enum('f','m') comment '学生性别',
cometime datetime default NOW()) comment '入学时间';
mysql> desc student;
+----------+---------------------+------+-----+-------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+-------------------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(20) | NO | | NULL | |
| age | tinyint(3) unsigned | NO | | NULL | |
| gender | enum('f','m') | YES | | NULL | |
| cometime | datetime | YES | | CURRENT_TIMESTAMP | |
+----------+---------------------+------+-----+-------------------+----------------+
# 删
mysql> drop table student;
# 改
1.插入字段
mysql> alter table 表名 add 字段名 类型[(宽度) 约束];
alter table student add qiandao varchar(10);
2.在最前面插入字段
mysql> alter table 表名 add 字段名 类型[(宽度) 约束] first;
alter table student add qiandao varchar(10) first;
3.将字段插入到某个字段的后面
mysql> alter table 表名 add 字段名 类型[(宽度) 约束] after 旧字段名;
alter table student add y int after x;
4.删除字段
mysql> alter table 表名 drop 字段名;
alter table student drop y;
5.修改字段属性
mysql> alter table 表名 modify 字段名 类型[(宽度) 约束];
alter table student modify x int; # 模式不同, 涉及精度问题
6.修改字段名字，也可以修改属性
mysql> alter table 表名 change 旧字段名 新字段名 类型[(宽度) 约束];
alter table student change y c char(10) not null; # 模式不同, 涉及类型转换问题
7.修改表名
mysql> alter table 旧表名 rename 新表名;
8.修改表的引擎与字符编码
alter table 表名 engine="引擎名" charset="编码名";
9.复制表结构
create table student select host,user from mysql.user where 1 < 0;
10.复制表结构和数据
create table student select host,user from mysql.user;
多对一
'''
案例：员工employees表 | 部门department表
建表规则：
先建立主表，再建立从表，在从表中设置主表的唯一字段(通常为主键)作为外键
建表语法：
create table 主表(
id int primary key auto_increment,
...
);
create table dep(
id int primary key auto_increment,
name varchar(16),
work varchar(16)
);
create table 从表(
id int primary key auto_increment,
...
主表_id int, # 只是在从表中起了一个名字, 该名字和主表主键对应,所有起了个见名知义的名字
foreign key(主表_id) references 主表(唯一字段名id)
on update cascade
on delete cascade
);
create table emp(
id int primary key auto_increment,
name varchar(16),
salary float,
dep_id int,
foreign key(dep_id) references dep(id)
on update cascade # 设置级联
on delete cascade
);
'''
多对多
'''
案例：作者author表 | 书book表
建表规则：
新建第三张表，通过两个外键形成多对多关系
建表语法：
create table 表1(
id int primary key auto_increment,
...
);
create table book(
id int primary key auto_increment,
name varchar(16),
price int
);
create table 表2(
id int primary key auto_increment,
...
);
create table author(
id int primary key auto_increment,
name varchar(16)
);
create table 关系表(
id int primary key auto_increment,
表1_id int,
表2_id int,
foreign key(表1_id) references 表1(id)
on update cascade
on delete cascade,
foreign key(表2_id) references 表2(id)
on update cascade
on delete cascade
);
create table book_author(
id int primary key auto_increment,
book_id int,
author_id int,
foreign key(book_id) references book(id)
on update cascade
on delete cascade,
foreign key(author_id) references author(id)
on update cascade
on delete cascade
);
'''
一对一
'''
案例：丈夫husband表 | 妻子wife表
建表规则：
未存放外键的表被依赖，称之为左表；存放外键的表示依赖表，称之为右表；先操作左边再操作右表
建表语法：
create table 左表(
id int primary key auto_increment,
...
);
create table husband(
id int primary key auto_increment,
name varchar(16)
);
create table 右表(
id int primary key auto_increment,
...
左表_id int unique, # 一对一的外键需要唯一性
foreign key(左表_id) references 左表(id)
on update cascade
on delete cascade
);
create table wife(
id int primary key auto_increment,
name varchar(16),
husband_id int unique, # 一对一的外键需要唯一性
foreign key(husband_id) references husband(id)
on update cascade
on delete cascade
);
'''
用户操作
# 操作前提：登录root用户
1.重要字段
Host | User | Password
2.新建用户
create user 用户名@主机名 identified by '密码'; # 正确
create user zero@localhost identified by 'zero';
注：insert into mysql.user(Host,User,Password) values("主机名","用户名",password("密码")); # 错误
3.设置用户权限
grant 权限们 on 数据库名.表名 to 用户名@主机名 [with grant option];
grant create on db1.* to zero@localhost with grant option;
注：权限有select,delete,update,insert,drop..., all代表所有权限
注：数据库名，表名可以用*替换，代表所有
注：设置权限时如果没有当前用户，会自动创建用户，提倡使用
重点: grant all on db1.* to owen@localhost identified by 'owen'; # (创建用户)设置权限
4.撤销权限
revoke 权限名 on 数据库名.表名 from 用户名@主机名;
revoke delete on db1.* from owen@localhost;
5.修改密码
set password for 用户名@主机名 = password('新密码');
set password for owen@localhost = password('123');
6.删除用户
drop user 用户名@主机名;
2.SQL语句-DML
针对数据的DML
# 插入数据 insert
1.不规范
mysql> insert into student4 values(1,'qiandao',84,'m',NOW());
2.规范写法
mysql> insert into student4(name,age,gender) values('qiandao',84,'m');
3.插入多条数据
mysql> insert into student4(name,age,gender) values('qiandao',84,'m'),('qiudao',73,'f');
4.利用表数据插入表数据
mysql> insert into student select * from student4;
# 修改数据 update
1.不规范
mysql> update student set gender='m';
2.规范
mysql> update student set gender='m' where name='qiandao';
3.就算修改整列内容，也要加条件
mysql> update student set age=100 where 1=1;
# 删除数据 delete
1.不规范
mysql> delete from test.student;
2.规范
mysql> delete from student4 where id=1;
3.规范
mysql> delete from student4 where 1=1;
4.清空表
truncate 表名；
使用update代替delete做伪删除
# 1.添加状态列
mysql> alter table student4 add state enum('0','1') default '1';
# 2.使用update删除数据
mysql> update student4 set state='0' where id=7;
# 3.查询数据
mysql> select * from student4 where state='1';
3.SQL语句-DCL
数据控制语句
grant权限
grant all on *.* to root1@'%' identified by '123' with grant option;
grant all privileges on *.* to root@'%' identified by '123' with grant option;
### 其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量
给开发开权限
grant select() on ku.biao to dev1@'%' identified by '123' with max_queries_per_hour 1 max_updates_per_hour 1 max_connections_per_hour 1 max_user_connections 1;
revoke 撤销权限
mysql> revoke select on *.* from root1@'%';
4.SQL语句-DQL
select：基础用法 单表查询
单表查询语法
SELECT 字段1,字段2... FROM 表名
WHERE 条件
GROUP BY field
HAVING 筛选
ORDER BY field
LIMIT 限制条数
关键字的执行优先级(重点)
# 重点中的重点：关键字的执行优先级
1.找到表:from
2.拿着where指定的约束条件，去文件/表中取出一条条记录
3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组
4.将分组的结果进行having过滤
5.执行select
6.去重
7.将结果按条件排序：order by
8.限制结果的显示条数
from
where
group by
having
select
distinct
order by
limit
单表查询建表
#创建表
create table employee(
id int not null unique auto_increment,
name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int);
#插入记录
#三个部门：教学，销售，运营
insert into employee(name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),
('歪歪','female',48,'20150311','sale',3000.00,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.00,402,2),
('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;
#ps：如果在windows系统中，插入中文字符，select的结果为空白，可以将所有字符编码统一设置成gbk
简单查询
#简单查询
SELECT id,name,sex,age,hire_date,post,post_comment,salary,office,depart_id
FROM employee;
SELECT * FROM employee;
SELECT name,salary FROM employee;
#避免重复DISTINCT
SELECT DISTINCT post FROM employee;
#通过四则运算查询
SELECT name, salary*12 FROM employee;
SELECT name, salary*12 AS Annual_salary FROM employee;
SELECT name, salary*12 Annual_salary FROM employee;
#定义显示格式
CONCAT() 函数用于连接字符串
SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary
FROM employee;
CONCAT_WS() 第一个参数为分隔符
SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary
FROM employee;
结合CASE语句：
SELECT
(
CASE
WHEN NAME = 'egon' THEN
NAME
WHEN NAME = 'alex' THEN
CONCAT(name,'_BIGSB')
ELSE
concat(NAME, 'SB')
END
) as new_name
FROM
emp;
# 小练习
1 查出所有员工的名字，薪资,格式为
<名字:egon> <薪资:3000>
select concat('<名字:',name,'> ','<薪资:',salary,'>') from employee;
2 查出所有的岗位（去掉重复）
select distinct depart_id from employee;
3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
select name,salary*12 annual_salary from employee;
where 约束
where字句中可以使用：
1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在10到20之间
3. in(80,90,100) 值是10或20或30
4. like 'egon%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：在多个条件直接可以使用逻辑运算符 and or not
#1:单条件查询
SELECT name FROM employee
WHERE post='sale';
#2:多条件查询
SELECT name,salary FROM employee
WHERE post='teacher' AND salary>10000;
#3:关键字BETWEEN AND
SELECT name,salary FROM employee
WHERE salary BETWEEN 10000 AND 20000;
SELECT name,salary FROM employee
WHERE salary NOT BETWEEN 10000 AND 20000;
#4:关键字IS NULL(判断某个字段是否为NULL不能用等号，需要用IS)
SELECT name,post_comment FROM employee
WHERE post_comment IS NULL;
SELECT name,post_comment FROM employee
WHERE post_comment IS NOT NULL;
SELECT name,post_comment FROM employee
WHERE post_comment=''; 注意''是空字符串，不是null
ps：
执行
update employee set post_comment='' where id=2;
再用上条查看，就会有结果了
#5:关键字IN集合查询
SELECT name,salary FROM employee
WHERE salary=3000 OR salary=3500 OR salary=4000 OR salary=9000 ;
SELECT name,salary FROM employee
WHERE salary IN (3000,3500,4000,9000) ;
SELECT name,salary FROM employee
WHERE salary NOT IN (3000,3500,4000,9000) ;
#6:关键字LIKE模糊查询
通配符'%'
SELECT * FROM employee
WHERE name LIKE 'eg%';
通配符'_'
SELECT * FROM employee
WHERE name LIKE 'al__';
# 小练习
1. 查看岗位是teacher的员工姓名、年龄
select name,sex from employee where post='teacher';
2. 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,sex from employee where post='teacher' and age > 30;
3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
select name,sex,salary from employee where post='teacher' and salary between 9000 and 10000;
4. 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is not null;
5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,sex,salary from employee where post='teacher' and salary=9000 or salary=10000 or salary=30000;
6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,sex,salary from employee where post='teacher' and salary not in (10000,9000,30000);
7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary*12 from employee where post='teacher' and name like 'jin%';
范围查询> 、 < 、<>
mysql> select * from world.city where id<10;
mysql> select * from city where countrycode='CHN' or countrycode='USA';
mysql> select * from city where countrycode in ('CHN','USA');
联合查询
mysql> select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
group by 分组
#1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的
#2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等
#3、为何要分组呢？
取每个部门的最高工资
取每个部门的员工数
取男人数和女人数
小窍门：‘每’这个字后面的字段，就是我们分组的依据
#4、大前提：
可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数
ONLY_FULL_GROUP_BY
#查看MySQL 5.7默认的sql_mode如下：
mysql> select @@global.sql_mode;
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
#！！！注意
ONLY_FULL_GROUP_BY的语义就是确定select target list中的所有列的值都是明确语义，简单的说来，在ONLY_FULL_GROUP_BY模式下，target list中的值要么是来自于聚集函数的结果，要么是来自于group by list中的表达式的值。
#设置sql_mole如下操作(我们可以去掉ONLY_FULL_GROUP_BY模式)：
mysql> set global sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
mysql> select @@global.sql_mode;
+-------------------+
| @@global.sql_mode |
+-------------------+
| |
+-------------------+
1 row in set (0.00 sec)
mysql> select * from employee group by post;
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
| 14 | 张野 | male | 28 | 2016-03-11 | operation | NULL | 10000.13 | 403 | 3 |
| 9 | 歪歪 | female | 48 | 2015-03-11 | sale | NULL | 3000.13 | 402 | 2 |
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
| 1 | egon | male | 18 | 2017-03-01 | 外交大使 | NULL | 7300.33 | 401 | 1 |
+----+------+--------+-----+------------+----------------------------+--------------+------------+--------+-----------+
4 rows in set (0.00 sec)
#由于没有设置ONLY_FULL_GROUP_BY,于是也可以有结果，默认都是组内的第一条记录，但其实这是没有意义的
mysql> set global sql_mode='ONLY_FULL_GROUP_BY';
Query OK, 0 rows affected (0.00 sec)
mysql> quit #设置成功后，一定要退出，然后重新登录方可生效
Bye
ysql> use db1;
Database changed
mysql> select * from employee group by post; #报错
ERROR 1055 (42000): 'db1.emp.id' isn't in GROUP BY
mysql> select post,count(id) from emp group by post; #只能查看分组依据和使用聚合函数
+----------------------------+-----------+
| post | count(id) |
+----------------------------+-----------+
| operation | 5 |
| sale | 5 |
| teacher | 7 |
| 外交大使 | 1 |
+----------------------------+-----------+
4 rows in set (0.00 sec)
单独使用GROUP BY关键字分组
SELECT post FROM employee GROUP BY post;
注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数
GROUP BY关键字和GROUP_CONCAT()函数一起使用
SELECT post,GROUP_CONCAT(name) FROM employee GROUP BY post; #按照岗位分组，并查看组内成员名
SELECT post,GROUP_CONCAT(name) as emp_members FROM employee GROUP BY post;
GROUP BY与聚合函数一起使用
select post,count(id) as count from employee group by post; #按照岗位分组，并查看每个组有多少人
ps: 如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义,多条记录之间的某个字段值相同，该字段通常用来作为分组的依据
聚合函数
max() 最大
min() 最小
sum() 求和
avg() 平均值
count() 统计
distinct() 去重
CONCAT() 函数用于连接字符串
CONCAT_WS() 第一个参数为分隔符
1. 查询岗位名以及岗位包含的所有员工名字
mysql> select post,group_concat(name) from employee group by post;
2. 查询岗位名以及各岗位内包含的员工个数
mysql> select post,count(id) from employee group by post;
3. 查询公司内男员工和女员工的个数
mysql> select sex,count(id) from employee group by sex;
4. 查询岗位名以及各岗位的平均薪资
mysql> select post,avg(salary) from employee group by post;
5. 查询岗位名以及各岗位的最高薪资
mysql> select post,max(salary) from employee group by post;
6. 查询岗位名以及各岗位的最低薪资
mysql> select post,min(salary) from employee group by post;
7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
mysql> select sex,avg(salary) from employee group by sex;
函数使用方法
mysql> SELECT CONCAT('姓名: ',name,' 年薪: ', salary*12) AS Annual_salary FROM employee;
mysql> SELECT CONCAT_WS(':',name,salary*12) AS Annual_salary FROM employee;
having 过滤
#！！！执行优先级从高到低：where > group by > having
#1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
#2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
mysql> select @@sql_mode;
+--------------------+
| @@sql_mode |
+--------------------+
| ONLY_FULL_GROUP_BY |
+--------------------+
1 row in set (0.00 sec)
mysql> select * from employee where salary > 100000;
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| id | name | sex | age | hire_date | post | post_comment | salary | office | depart_id |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
| 2 | alex | male | 78 | 2015-03-02 | teacher | NULL | 1000000.31 | 401 | 1 |
+----+------+------+-----+------------+---------+--------------+------------+--------+-----------+
1 row in set (0.00 sec)
mysql> select * from employee having salary > 100000;
ERROR 1463 (42000): Non-grouping field 'salary' is used in HAVING clause
mysql> select post,group_concat(name) from employee group by post having salary > 10000;#错误，分组后无法直接取到salary字段
ERROR 1054 (42S22): Unknown column 'salary' in 'having clause'
mysql> select post,group_concat(name) from employee group by post having avg(salary) > 10000;
+-----------+-------------------------------------------------------+
| post | group_concat(name) |
+-----------+-------------------------------------------------------+
| operation | 程咬铁,程咬铜,程咬银,程咬金,张野 |
| teacher | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex |
+-----------+-------------------------------------------------------+
2 rows in set (0.00 sec)
1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
mysql> select post,group_concat(name),count(id) from employee group by post having count(name) < 2;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000;
3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
mysql> select post,avg(salary) from employee group by post having avg(salary) > 10000 and avg(salary) < 20000;
order by 排序
按单列排序
SELECT * FROM employee ORDER BY salary;
SELECT * FROM employee ORDER BY salary ASC;
SELECT * FROM employee ORDER BY salary DESC;
按多列排序:先按照age排序，如果年纪相同，则按照薪资排序
SELECT * from employee ORDER BY age,salary DESC;
1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
mysql> select * from employee ORDER BY age asc,hire_date desc;
2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary);
3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from employee group by post having avg(salary) > 10000 order by avg(salary) desc;
limit 分页查询
示例：
SELECT * FROM employee ORDER BY salary DESC
LIMIT 3; #默认初始位置为0
SELECT * FROM employee ORDER BY salary DESC
LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条
SELECT * FROM employee ORDER BY salary DESC
LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条
1.查询当前0到5的表数据
select * from employee limit 0,5;
2.查询当前5到后5条的表数据
select * from employee limit 5,5;
3.查询当前10到后5条表数据
select * from employee limit 10,5;
使用正则表达式查询
SELECT * FROM employee WHERE name REGEXP '^ale';
SELECT * FROM employee WHERE name REGEXP 'on$';
SELECT * FROM employee WHERE name REGEXP 'm{2}';
小结：对字符串匹配的方式
WHERE name = 'egon';
WHERE name LIKE 'yua%';
WHERE name REGEXP 'on$';
1.查看所有员工中名字是jin开头，n或者g结果的员工信息
select * from employee where name regexp '^jin.*[gn]$';
select 高级用法 链表查询
多表查询建表
create table department(
id int,
name varchar(20)
);
create table employee(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);
#插入数据
insert into department values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');
insert into employee(name,sex,age,dep_id) values
('egon','male',18,200),
('alex','female',48,201),
('wupeiqi','male',38,201),
('yuanhao','female',28,202),
('liwenzhou','male',18,200),
('jingliyang','female',18,204)
;
内链接：只保留有对应关系的记录
找两张表共有的部分，相当于利用条件从笛卡尔积结果中筛选出了正确的结果
department没有204这个部门，因而employee表中关于204这条员工信息没有匹配出来
# 不正规链表查询
select * from employee,department where employee.dep_id = department.id;
# 正规链表查询
select * from employee inner join department on employee.dep_id = department.id;
左链接：在内链接的基础上保留左表的记录
以左表为准，即找出所有员工信息，当然包括没有部门的员工
本质就是：在内连接的基础上增加左边有右边没有的结果
select * from employee left join department on employee.dep_id = department.id;
右链接：在内链接的基础上保留右表的记录
以右表为准，即找出所有部门信息，包括没有员工的部门
本质就是：在内连接的基础上增加右边有左边没有的结果
select * from employee right join department on employee.dep_id = department.id;
全外链接：full join，在内连接的基础上，左右两表没有对应关系的记录也留下来
select * from employee left join department on employee.dep_id = department.id
union
select * from employee right join department on employee.dep_id = department.id;
符合条件连接查询
#示例1：以内连接的方式查询employee和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee.name,department.name from employee inner join department
on employee.dep_id = department.id
where age > 25;
#示例2：以内连接的方式查询employee和department表，并且以age字段的升序方式显示
select employee.id,employee.name,employee.age,department.name from employee,department
where employee.dep_id = department.id
and age > 25
order by age asc;
子查询
1：子查询是将一个查询语句嵌套在另一个查询语句中。
2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。
3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字
4：还可以包含比较运算符：= 、 !=、> 、<等
带IN关键字的子查询
#查询平均年龄在25岁以上的部门名
select id,name from department
where id in
(select dep_id from employee group by dep_id having avg(age) > 25);
#查看技术部员工姓名
select name from employee
where dep_id in
(select id from department where name='技术');
#查看不足1人的部门名(子查询得到的是有人的部门id)
select name from department where id not in (select distinct dep_id from employee);
not in 无法处理null的值，即子查询中如果存在null的值，not in将无法处理，如下
mysql> select * from employee;
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
| 7 | xxx | male | 19 | NULL |
+----+------------+--------+------+--------+
6 rows in set (0.00 sec)
mysql> select * from department;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+
4 rows in set (0.00 sec)
# 子查询中存在null
mysql> select * from department where id not in (select distinct dep_id from employee);
Empty set (0.00 sec)
# 解决方案如下
mysql> select * from dep where id not in (select distinct dep_id from emp where dep_id is not null);
+------+--------+
| id | name |
+------+--------+
| 203 | 运营 |
+------+--------+
1 row in set (0.00 sec)
带ANY关键字的子查询
#在 SQL 中 ANY 和 SOME 是同义词，SOME 的用法和功能和 ANY 一模一样。
# ANY 和 IN 运算符不同之处1
ANY 必须和其他的比较运算符共同使用，而且ANY必须将比较运算符放在 ANY 关键字之前，所比较的值需要匹配子查询中的任意一个值，这也就是 ANY 在英文中所表示的意义
例如：使用 IN 和使用 ANY运算符得到的结果是一致的
select * from employee where salary = any (
select max(salary) from employee group by depart_id);
select * from employee where salary in (
select max(salary) from employee group by depart_id);
结论：也就是说“=ANY”等价于 IN 运算符，而“<>ANY”则等价于 NOT IN 运算符
# ANY和 IN 运算符不同之处2
ANY 运算符不能与固定的集合相匹配，比如下面的 SQL 语句是错误的
SELECT * FROM T_Book WHERE FYearPublished < ANY (2001, 2003, 2005)
带ALL关键字的子查询
# 在单表下的employee下查询
# all同any类似，只不过all表示的是所有，any表示任一
查询出那些薪资比所有部门的平均薪资都高的员工=》薪资在所有部门平均线以上的狗币资本家
select * from employee where salary > all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比所有部门的平均薪资都低的员工=》薪资在所有部门平均线以下的无产阶级劳苦大众
select * from employee where salary < all (
select avg(salary) from employee group by depart_id);
查询出那些薪资比任意一个部门的平均薪资低的员工=》薪资在任一部门平均线以下的员工
select * from employee where salary < any ( select avg(salary) from employee group by depart_id;
查询出那些薪资比任意一个部门的平均薪资高的员工=》薪资在任一部门平均线以上的员工
select * from employee where salary > any (
select avg(salary) from employee group by depart_id);
带比较运算符的子查询
#比较运算符：=、!=、>、>=、<、<=、<>
#查询大于所有人平均年龄的员工名与年龄
mysql> select name,age from employee where age > (select avg(age) from employee);
+---------+------+
| name | age |
+---------+------+
| alex | 48 |
| wupeiqi | 38 |
+---------+------+
2 rows in set (0.00 sec)
#查询大于部门内平均年龄的员工名、年龄
select t1.name,t1.age from employee t1 inner join (select dep_id,avg(age) avg_age from employee group by dep_id) t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age;
带EXISTS关键字的子查询
EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
#department表中存在dept_id=203，Ture
mysql> select * from employee where exists (select id from department where id=200);
+----+------------+--------+------+--------+
| id | name | sex | age | dep_id |
+----+------------+--------+------+--------+
| 1 | egon | male | 18 | 200 |
| 2 | alex | female | 48 | 201 |
| 3 | wupeiqi | male | 38 | 201 |
| 4 | yuanhao | female | 28 | 202 |
| 5 | liwenzhou | male | 18 | 200 |
| 6 | jingliyang | female | 18 | 204 |
+----+------------+--------+------+--------+
#department表中存在dept_id=205，False
mysql> select * from employee where exists (select id from department where id=204);
Empty set (0.00 sec)
in与exists
！！！！！！当in和exists在查询效率上比较时，in查询的效率快于exists的查询效率！！！！！！
==============================exists==============================
# exists
exists后面一般都是子查询，后面的子查询被称做相关子查询（即与主语句相关），当子查询返回行数时，exists条件返回true，
否则返回false，exists是不返回列表的值的，exists只在乎括号里的数据能不能查找出来，是否存在这样的记录。
# 例
查询出那些班级里有学生的班级
select * from class where exists (select * from stu where stu.cid=class.id)
# exists的执行原理为：
1、依次执行外部查询：即select * from class
2、然后为外部查询返回的每一行分别执行一次子查询：即(select * from stu where stu.cid=class.cid)
3、子查询如果返回行，则exists条件成立，条件成立则输出外部查询取出的那条记录
==============================in==============================
# in
in后跟的都是子查询，in()后面的子查询 是返回结果集的
# 例
查询和所有女生年龄相同的男生
select * from stu where sex='男' and age in(select age from stu where sex='女')
# in的执行原理为：
in()的执行次序和exists()不一样,in()的子查询会先产生结果集,
然后主查询再去结果集里去找符合要求的字段列表去.符合要求的输出,反之则不输出.
not in与 not exists
!!!!!!not exists查询的效率远远高与not in查询的效率。!!!!!!
==============================not in==============================
not in()子查询的执行顺序是：
为了证明not in成立，即找不到，需要一条一条地查询表，符合要求才返回子查询的结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完，只能查询全部记录才能证明，并没有用到索引。
==============================not exists==============================
not exists：
如果主查询表中记录少，子查询表中记录多，并有索引。
例如:查询那些班级中没有学生的班级
select * from class
where not exists
(select * from student where student.cid = class.cid)
not exists的执行顺序是：
在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。
create database db13;
use db13
create table student(
id int primary key auto_increment,
name varchar(16)
);
create table course(
id int primary key auto_increment,
name varchar(16),
comment varchar(20)
);
create table student2course(
id int primary key auto_increment,
sid int,
cid int,
foreign key(sid) references student(id),
foreign key(cid) references course(id)
);
insert into student(name) values
("egon"),
("lili"),
("jack"),
("tom");
insert into course(name,comment) values
("数据库","数据仓库"),
("数学","根本学不会"),
("英语","鸟语花香");
insert into student2course(sid,cid) values
(1,1),
(1,2),
(1,3),
(2,1),
(2,2),
(3,2);
# 1、查询选修了所有课程的学生id、name:（即该学生根本就不存在一门他没有选的课程。）
select * from student s where not exists
(select * from course c where not exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
select s.name from student as s
inner join student2course as sc
on s.id=sc.sid
group by s.name
having count(sc.id) = (select count(id) from course);
# 2、查询没有选择所有课程的学生，即没有全选的学生。（存在这样的一个学生，他至少有一门课没有选）
select * from student s where exists
(select * from course c where not exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
# 3、查询一门课也没有选的学生。（不存这样的一个学生，他至少选修一门课程）
select * from student s where not exists
(select * from course c where exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
# 4、查询至少选修了一门课程的学生。
select * from student s where exists
(select * from course c where exists
(select * from student2course sc where sc.sid=s.id and sc.cid=c.id));
练习
# 查询每个部门最新入职的那位员工
# 链表查询
mysql> select * from employee as t1 inner join
(select post,max(hire_date) max_date from employee group by post) as t2 on
t1.post = t2.post where t1.hire_date = t2.max_date;
# 子查询
mysql> select t3.name,t3.post,t3.hire_date from employee as t3 where id in (select (select id from employee as t2 where t2.post=t1.post order by hire_date desc limit 1) from employee as t1 group by post);
可以基于上述方法解决：比如某网站在全国各个市都有站点，每个站点一条数据，想取每个省下最新的那一条市的网站质量信息
综合练习语法
1、查询所有的课程的名称以及对应的任课老师姓名
SELECT
course.cname,
teacher.tname
FROM
course
INNER JOIN teacher ON course.teacher_id = teacher.tid;
2、查询学生表中男女生各有多少人
SELECT
gender 性别,
count(1) 人数
FROM
student
GROUP BY
gender;
3、查询物理成绩等于100的学生的姓名
SELECT
student.sname
FROM
student
WHERE
sid IN (
SELECT
student_id
FROM
score
INNER JOIN course ON score.course_id = course.cid
WHERE
course.cname = '物理'
AND score.num = 100
);
4、查询平均成绩大于八十分的同学的姓名和平均成绩
SELECT
student.sname,
t1.avg_num
FROM
student
INNER JOIN (
SELECT
student_id,
avg(num) AS avg_num
FROM
score
GROUP BY
student_id
HAVING
avg(num) > 80
) AS t1 ON student.sid = t1.student_id;
5、查询所有学生的学号，姓名，选课数，总成绩
SELECT
student.sid,
student.sname,
t1.course_num,
t1.total_num
FROM
student
LEFT JOIN (
SELECT
student_id,
COUNT(course_id) course_num,
sum(num) total_num
FROM
score
GROUP BY
student_id
) AS t1 ON student.sid = t1.student_id;
6、 查询姓李老师的个数
7、 查询没有报李平老师课的学生姓名
8、 查询物理课程比生物课程高的学生的学号
9、 查询没有同时选修物理课程和体育课程的学生姓名
10、查询挂科超过两门(包括两门)的学生姓名和班级
、查询选修了所有课程的学生姓名
12、查询李平老师教的课程的所有成绩记录
13、查询全部学生都选修了的课程号和课程名
14、查询每门课程被选修的次数
15、查询之选修了一门课程的学生姓名和学号
16、查询所有学生考出的成绩并按从高到低排序（成绩去重）
17、查询平均成绩大于85的学生姓名和平均成绩
18、查询生物成绩不及格的学生姓名和对应生物分数
19、查询在所有选修了李平老师课程的学生中，这些课程(李平老师的课程，不是所有课程)平均成绩最高的学生姓名
20、查询每门课程成绩最好的前两名学生姓名
21、查询不同课程但成绩相同的学号，课程号，成绩
22、查询没学过“叶平”老师课程的学生姓名以及选修的课程名称；
23、查询所有选修了学号为1的同学选修过的一门或者多门课程的同学学号和姓名；
24、任课最多的老师中学生单科成绩最高的学生姓名
视图
视图简介
视图是一个虚拟表（非真实存在），其本质是【根据SQL语句获取动态的数据集，并为其命名】，
用户使用时只需使用【名称】即可获取结果集，可以将该结果集当做表来使用。
使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可.
但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图.
即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用
创建视图
#语法：CREATE VIEW 视图名称 AS SQL语句
create view teacher_view as select tid from teacher where tname='李平老师';
#于是查询李平老师教授的课程名的sql可以改写为
mysql> select cname from course where teacher_id = (select tid from teacher_view);
+--------+
| cname |
+--------+
| 物理 |
| 美术 |
+--------+
2 rows in set (0.00 sec)
#！！！注意注意注意：
#1. 使用视图以后就无需每次都重写子查询的sql，但是这么做效率并不高，还不如我们写子查询的效率高
#2. 而且有一个致命的问题：视图是存放到数据库里的，如果我们程序中的sql过分依赖于数据库中存放的视图，那么意味着，一旦sql需要修改且涉及到视图的部分，则必须去数据库中进行修改，而通常在公司中数据库有专门的DBA负责，你要想完成修改，必须付出大量的沟通成本DBA可能才会帮你完成修改，极其地不方便。
使用视图
#修改视图，原始表也跟着改
mysql> select * from course;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+--------+------------+
4 rows in set (0.00 sec)
mysql> create view course_view as select * from course; #创建表course的视图
Query OK, 0 rows affected (0.52 sec)
mysql> select * from course_view;
+-----+--------+------------+
| cid | cname | teacher_id |
+-----+--------+------------+
| 1 | 生物 | 1 |
| 2 | 物理 | 2 |
| 3 | 体育 | 3 |
| 4 | 美术 | 2 |
+-----+--------+------------+
4 rows in set (0.00 sec)
mysql> update course_view set cname='xxx'; #更新视图中的数据
Query OK, 4 rows affected (0.04 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql> insert into course_view values(5,'yyy',2); #往视图中插入数据
Query OK, 1 row affected (0.03 sec)
mysql> select * from course; #发现原始表的记录也跟着修改了
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 1 | xxx | 1 |
| 2 | xxx | 2 |
| 3 | xxx | 3 |
| 4 | xxx | 2 |
| 5 | yyy | 2 |
+-----+-------+------------+
5 rows in set (0.00 sec)
修改视图
语法：ALTER VIEW 视图名称 AS SQL语句
mysql> alter view teacher_view as select * from course where cid>3;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from teacher_view;
+-----+-------+------------+
| cid | cname | teacher_id |
+-----+-------+------------+
| 4 | xxx | 2 |
| 5 | yyy | 2 |
+-----+-------+------------+
2 rows in set (0.00 sec)
删除视图
语法：DROP VIEW 视图名称
DROP VIEW teacher_view;
触发器
使用触发器可以定制用户对表进行【增、删、改】操作时前后的行为，注意：没有查询
一 创建触发器
# 插入前
CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 插入后
CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除前
CREATE TRIGGER tri_before_delete_tb1 BEFORE DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 删除后
CREATE TRIGGER tri_after_delete_tb1 AFTER DELETE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新前
CREATE TRIGGER tri_before_update_tb1 BEFORE UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
# 更新后
CREATE TRIGGER tri_after_update_tb1 AFTER UPDATE ON tb1 FOR EACH ROW
BEGIN
...
END
准备表
CREATE TABLE cmd (
id INT PRIMARY KEY auto_increment,
USER CHAR (32),
priv CHAR (10),
cmd CHAR (64),
sub_time datetime, #提交时间
success enum ('yes', 'no') #0代表执行失败
);
CREATE TABLE errlog (
id INT PRIMARY KEY auto_increment,
err_cmd CHAR (64),
err_time datetime
);
#创建触发器
delimiter //
CREATE TRIGGER tri_after_insert_cmd AFTER INSERT ON cmd FOR EACH ROW
BEGIN
IF NEW.success = 'no' THEN #等值判断只有一个等号
INSERT INTO errlog(err_cmd, err_time) VALUES(NEW.cmd, NEW.sub_time) ; #必须加分号
END IF ; #必须加分号
END//
delimiter ;
#往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志
INSERT INTO cmd (
USER,
priv,
cmd,
sub_time,
success
)
VALUES
('egon','0755','ls -l /etc',NOW(),'yes'),
('egon','0755','cat /etc/passwd',NOW(),'no'),
('egon','0755','useradd xxx',NOW(),'no'),
('egon','0755','ps aux',NOW(),'yes');
#查询错误日志，发现有两条
mysql> select * from errlog;
+----+-----------------+---------------------+
| id | err_cmd | err_time |
+----+-----------------+---------------------+
| 1 | cat /etc/passwd | 2017-09-14 22:18:48 |
| 2 | useradd xxx | 2017-09-14 22:18:48 |
+----+-----------------+---------------------+
2 rows in set (0.00 sec)
特别的：NEW表示即将插入的数据行，OLD表示即将删除的数据行。
二 使用触发器
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
三 删除触发器
drop trigger tri_after_insert_cmd;
存储过程
一 介绍
存储过程包含了一系列可执行的sql语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一堆sql
使用存储过程的优点：
1. 用于替代程序写的SQL语句，实现程序与sql解耦
2. 基于网络传输，传别名的数据量小，而直接传sql数据量大
程序与数据库结合使用的三种方式
#方式一：
MySQL：存储过程
程序：调用存储过程
#方式二：
MySQL：
程序：纯SQL语句
#方式三：
MySQL:
程序：类和对象，即ORM（本质还是纯SQL语句）
二 创建简单存储过程（无参）
delimiter //
create procedure p1()
BEGIN
select * from blog;
INSERT into blog(name,sub_time) values("xxx",now());
END //
delimiter ;
#在mysql中调用
call p1()
#在python中基于pymysql调用
cursor.callproc('p1')
print(cursor.fetchall())
三 创建存储过程（有参）
对于存储过程，可以接收参数，其参数有三类：
#in 仅用于传入参数用
#out 仅用于返回值用
#inout 既可以传入又可以当作返回值
int:传入参数
delimiter //
create procedure p2(
in n1 int,
in n2 int
)
BEGIN
select * from blog where id > n1;
END //
delimiter ;
#在mysql中调用
call p2(3,2)
#在python中基于pymysql调用
cursor.callproc('p2',(3,2))
print(cursor.fetchall())
cut:返回值
delimiter //
create procedure p3(
in n1 int,
out res int
)
BEGIN
select * from blog where id > n1;
set res = 1;
END //
delimiter ;
#在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
call p3(3,@res);
select @res;
#在python中基于pymysql调用
cursor.callproc('p3',(3,0)) #0相当于set @res=0
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p3_0,@_p3_1;') #@p3_0代表第一个参数，@p3_1代表第二个参数，即返回值
print(cursor.fetchall())
inout:既可以传入又可以返回
delimiter //
create procedure p4(
inout n1 int
)
BEGIN
select * from blog where id > n1;
set n1 = 1;
END //
delimiter ;
#在mysql中调用
set @x=3;
call p4(@x);
select @x;
#在python中基于pymysql调用
cursor.callproc('p4',(3,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p4_0;')
print(cursor.fetchall())
事务
#介绍
delimiter //
create procedure p4(
out status int
)
BEGIN
1. 声明如果出现异常则执行{
set status = 1;
rollback;
}
开始事务
-- 由秦兵账户减去100
-- 方少伟账户加90
-- 张根账户加10
commit;
结束
set status = 2;
END //
delimiter ;
#实现
delimiter //
create PROCEDURE p5(
OUT p_return_code tinyint
)
BEGIN
DECLARE exit handler for sqlexception
BEGIN
-- ERROR
set p_return_code = 1;
rollback;
END;
DECLARE exit handler for sqlwarning
BEGIN
-- WARNING
set p_return_code = 2;
rollback;
END;
START TRANSACTION;
DELETE from tb1; #执行失败
insert into blog(name,sub_time) values('yyy',now());
COMMIT;
-- SUCCESS
set p_return_code = 0; #0代表执行成功
END //
delimiter ;
#在mysql中调用存储过程
set @res=123;
call p5(@res);
select @res;
#在python中基于pymysql调用存储过程
cursor.callproc('p5',(123,))
print(cursor.fetchall()) #查询select的查询结果
cursor.execute('select @_p5_0;')
print(cursor.fetchall())
四 执行存储过程
在mysql中执行存储过程
-- 无参数
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)
执行存储过程
在python中基于pymysql执行存储过程
#!/usr/bin/env python
# -*- coding:utf-8 -*-
import pymysql
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123', db='t1')
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
# 执行存储过程
cursor.callproc('p1', args=(1, 22, 3, 4))
# 获取执行完存储的参数
cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3")
result = cursor.fetchall()
conn.commit()
cursor.close()
conn.close()
print(result)
五 删除存储过程
drop procedure proc_name;
函数
一、数学函数
ROUND(x,y)
返回参数x的四舍五入的有y位小数的值
RAND()
返回０到１内的随机值,可以通过提供一个参数(种子)使RAND()随机数生成器生成一个指定的值。
二、聚合函数(常用于GROUP BY从句的SELECT查询中)
AVG(col)返回指定列的平均值
COUNT(col)返回指定列中非NULL值的个数
MIN(col)返回指定列的最小值
MAX(col)返回指定列的最大值
SUM(col)返回指定列的所有值之和
GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
三、字符串函数
CHAR_LENGTH(str)
返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。
CONCAT(str1,str2,...)
字符串拼接
如有任何一个参数为NULL ，则返回值为 NULL。
CONCAT_WS(separator,str1,str2,...)
字符串拼接（自定义连接符）
CONCAT_WS()不会忽略任何空字符串。 (然而会忽略所有的 NULL）。
CONV(N,from_base,to_base)
进制转换
例如：
SELECT CONV('a',16,2); 表示将 a 由16进制转换为2进制字符串表示
FORMAT(X,D)
将数字X 的格式写为'#,###,###.##',以四舍五入的方式保留小数点后 D 位， 并将结果以字符串的形式返回。若 D 为 0, 则返回结果不带有小数点，或不含小数部分。
例如：
SELECT FORMAT(12332.1,4); 结果为： '12,332.1000'
INSERT(str,pos,len,newstr)
在str的指定位置插入字符串
pos：要替换位置其实位置
len：替换的长度
newstr：新字符串
特别的：
如果pos超过原字符串长度，则返回原字符串
如果len超过原字符串长度，则由新字符串完全替换
INSTR(str,substr)
返回字符串 str 中子字符串的第一个出现位置。
LEFT(str,len)
返回字符串str 从开始的len位置的子序列字符。
LOWER(str)
变小写
UPPER(str)
变大写
REVERSE(str)
返回字符串 str ，顺序和字符顺序相反。
SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
不带有len 参数的格式从字符串str返回一个子字符串，起始于位置 pos。带有len参数的格式从字符串str返回一个长度同len字符相同的子字符串，起始于位置 pos。 使用 FROM的格式为标准 SQL 语法。也可能对pos使用一个负值。假若这样，则子字符串的位置起始于字符串结尾的pos 字符，而不是字符串的开头位置。在以下格式的函数中可以对pos 使用一个负值。
mysql> SELECT SUBSTRING('Quadratically',5);
-> 'ratically'
mysql> SELECT SUBSTRING('foobarbar' FROM 4);
-> 'barbar'
mysql> SELECT SUBSTRING('Quadratically',5,6);
-> 'ratica'
mysql> SELECT SUBSTRING('Sakila', -3);
-> 'ila'
mysql> SELECT SUBSTRING('Sakila', -5, 3);
-> 'aki'
mysql> SELECT SUBSTRING('Sakila' FROM -4 FOR 2);
-> 'ki'
四、日期和时间函数
CURDATE()或CURRENT_DATE() 返回当前的日期
CURTIME()或CURRENT_TIME() 返回当前的时间
DAYOFWEEK(date) 返回date所代表的一星期中的第几天(1~7)
DAYOFMONTH(date) 返回date是一个月的第几天(1~31)
DAYOFYEAR(date) 返回date是一年的第几天(1~366)
DAYNAME(date) 返回date的星期名，如：SELECT DAYNAME(CURRENT_DATE);
FROM_UNIXTIME(ts,fmt) 根据指定的fmt格式，格式化UNIX时间戳ts
HOUR(time) 返回time的小时值(0~23)
MINUTE(time) 返回time的分钟值(0~59)
MONTH(date) 返回date的月份值(1~12)
MONTHNAME(date) 返回date的月份名，如：SELECT MONTHNAME(CURRENT_DATE);
NOW() 返回当前的日期和时间
QUARTER(date) 返回date在一年中的季度(1~4)，如SELECT QUARTER(CURRENT_DATE);
WEEK(date) 返回日期date为一年中第几周(0~53)
YEAR(date) 返回日期date的年份(1000~9999)
重点:
DATE_FORMAT(date,format) 根据format字符串格式化date值
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
五、加密函数
MD5()
计算字符串str的MD5校验和
PASSWORD(str)
返回字符串str的加密版本，这个加密过程是不可逆转的，和UNIX密码加密过程使用不同的算法。
六、控制流函数
CASE WHEN[test1] THEN [result1]...ELSE [default] END
如果testN是真，则返回resultN，否则返回default
CASE [test] WHEN[val1] THEN [result]...ELSE [default]END
如果test和valN相等，则返回resultN，否则返回default
IF(test,t,f)
如果test是真，返回t；否则返回f
IFNULL(arg1,arg2)
如果arg1不是空，返回arg1，否则返回arg2
NULLIF(arg1,arg2)
如果arg1=arg2返回NULL；否则返回arg1
七、控制流函数小练习
#7.1、准备表
/*
Navicat MySQL Data Transfer
Source Server : localhost_3306
Source Server Version : 50720
Source Host : localhost:3306
Source Database : student
Target Server Type : MYSQL
Target Server Version : 50720
File Encoding : 65001
Date: 2018-01-02 12:05:30
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
`c_id` int(11) NOT NULL,
`c_name` varchar(255) DEFAULT NULL,
`t_id` int(11) DEFAULT NULL,
PRIMARY KEY (`c_id`),
KEY `t_id` (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of course
-- ----------------------------
INSERT INTO `course` VALUES ('1', 'python', '1');
INSERT INTO `course` VALUES ('2', 'java', '2');
INSERT INTO `course` VALUES ('3', 'linux', '3');
INSERT INTO `course` VALUES ('4', 'web', '2');
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`s_id` int(10) DEFAULT NULL,
`c_id` int(11) DEFAULT NULL,
`num` double DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '1', '79');
INSERT INTO `score` VALUES ('2', '1', '2', '78');
INSERT INTO `score` VALUES ('3', '1', '3', '35');
INSERT INTO `score` VALUES ('4', '2', '2', '32');
INSERT INTO `score` VALUES ('5', '3', '1', '66');
INSERT INTO `score` VALUES ('6', '4', '2', '77');
INSERT INTO `score` VALUES ('7', '4', '1', '68');
INSERT INTO `score` VALUES ('8', '5', '1', '66');
INSERT INTO `score` VALUES ('9', '2', '1', '69');
INSERT INTO `score` VALUES ('10', '4', '4', '75');
INSERT INTO `score` VALUES ('11', '5', '4', '66.7');
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`s_id` varchar(20) NOT NULL,
`s_name` varchar(255) DEFAULT NULL,
`s_age` int(10) DEFAULT NULL,
`s_sex` char(1) DEFAULT NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES ('1', '鲁班', '12', '男');
INSERT INTO `student` VALUES ('2', '貂蝉', '20', '女');
INSERT INTO `student` VALUES ('3', '刘备', '35', '男');
INSERT INTO `student` VALUES ('4', '关羽', '34', '男');
INSERT INTO `student` VALUES ('5', '张飞', '33', '女');
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS `teacher`;
CREATE TABLE `teacher` (
`t_id` int(10) NOT NULL,
`t_name` varchar(50) DEFAULT NULL,
PRIMARY KEY (`t_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of teacher
-- ----------------------------
INSERT INTO `teacher` VALUES ('1', '大王');
INSERT INTO `teacher` VALUES ('2', 'alex');
INSERT INTO `teacher` VALUES ('3', 'egon');
INSERT INTO `teacher` VALUES ('4', 'peiqi');
#7.2、统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
select score.c_id,
course.c_name,
sum(CASE WHEN num BETWEEN 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
sum(CASE WHEN num BETWEEN 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
sum(CASE WHEN num BETWEEN 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
sum(CASE WHEN num < 60 THEN 1 ELSE 0 END) as '[ <60]'
from score,course where score.c_id=course.c_id GROUP BY score.c_id;
需要掌握的函数
#1 基本使用
mysql> SELECT DATE_FORMAT('2009-10-04 22:23:00', '%W %M %Y');
-> 'Sunday October 2009'
mysql> SELECT DATE_FORMAT('2007-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1900-10-04 22:23:00',
-> '%D %y %a %d %m %b %j');
-> '4th 00 Thu 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
-> '%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'
mysql> SELECT DATE_FORMAT('2006-06-00', '%d');
-> '00'
#2 准备表和记录
CREATE TABLE blog (
id INT PRIMARY KEY auto_increment,
NAME CHAR (32),
sub_time datetime
);
INSERT INTO blog (NAME, sub_time)
VALUES
('第1篇','2015-03-01 11:31:21'),
('第2篇','2015-03-11 16:31:21'),
('第3篇','2016-07-01 10:21:31'),
('第4篇','2016-07-22 09:23:21'),
('第5篇','2016-07-23 10:11:11'),
('第6篇','2016-07-25 11:21:31'),
('第7篇','2017-03-01 15:33:21'),
('第8篇','2017-03-01 17:32:21'),
('第9篇','2017-03-01 18:31:21');
#3. 提取sub_time字段的值，按照格式后的结果即"年月"来分组
SELECT DATE_FORMAT(sub_time,'%Y-%m'),COUNT(1) FROM blog GROUP BY DATE_FORMAT(sub_time,'%Y-%m');
#结果
+-------------------------------+----------+
| DATE_FORMAT(sub_time,'%Y-%m') | COUNT(1) |
+-------------------------------+----------+
| 2015-03 | 2 |
| 2016-07 | 4 |
| 2017-03 | 3 |
+-------------------------------+----------+
3 rows in set (0.00 sec)
一 自定义函数
#！！！注意！！！
#函数中不要写sql语句（否则会报错），函数仅仅只是一个功能，是一个在sql中被应用的功能
#若要想在begin...end...中写sql，请用存储过程
delimiter //
create function f1(
i1 int,
i2 int)
returns int
BEGIN
declare num int;
set num = i1 + i2;
return(num);
END //
delimiter ;
delimiter //
create function f5(
i int
)
returns int
begin
declare res int default 0;
if i = 10 then
set res=100;
elseif i = 20 then
set res=200;
elseif i = 30 then
set res=300;
else
set res=400;
end if;
return res;
end //
delimiter ;
二 删除函数
drop function func_name;
三 执行函数
# 获取返回值
select UPPER('egon') into @res;
SELECT @res;
# 在查询中使用
select f1(11,nid) ,name from tb2;
流程控制
一 条件语句
delimiter //
CREATE PROCEDURE proc_if ()
BEGIN
declare i int default 0;
if i = 1 THEN
SELECT 1;
ELSEIF i = 2 THEN
SELECT 2;
ELSE
SELECT 7;
END IF;
END //
delimiter ;
二 循环语句
while循环
delimiter //
CREATE PROCEDURE proc_while ()
BEGIN
DECLARE num INT ;
SET num = 0 ;
WHILE num < 10 DO
SELECT
num ;
SET num = num + 1 ;
END WHILE;
END //
delimiter ;
repeat循环
delimiter //
CREATE PROCEDURE proc_repeat ()
BEGIN
DECLARE i INT ;
SET i = 0 ;
repeat
select i;
set i = i + 1;
until i >= 5
end repeat;
END //
delimiter;
loop
BEGIN
declare i int default 0;
loop_label: loop
set i=i+1;
if i<8 then
iterate loop_label;
end if;
if i>=10 then
leave loop_label;
end if;
select i;
end loop loop_label;
END
网友评论