一 概述
mysql以分号；为语句结束符
mysql不区分大小写
分两大类： 关系型：如sqllite，db2，oracle，access，sql server，MySQL，注意：sql语句通用 非关系型：mongodb，redis，memcache 总结两句话： 关系型数据库需要有表结构 非关系型数据库是key-value存储的，没有表结构 RDBMS即关系数据库管理系统(Relational Database Management System)的术语： 冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。 复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。 索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。
二 用户及权限
1. 用户管理
1.1 查看所有用户
用户相关数据保存在mysql数据库的user表中
select user,host from mysql.user；
user表中host列的值的意义
% 匹配所有主机 localhost localhost不会被解析成IP地址，直接通过UNIXsocket连接 127.0.0.1 会通过TCP/IP协议连接，并且只能在本机访问； ::1 ::1就是兼容支持ipv6的，表示同ipv4的127.0.0.1
1.2 用户的增删改查
增删改查时注意写全登录地址，即host属性
创建用户 create user '用户名'@'IP地址' identified by '密码';
删除用户 drop user '用户名'@'IP地址';
修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';
修改密码 set password for '用户名'@'IP地址' = Password('新密码')
2. 权限管理
DBA会设置好权限，普通用户一般只有对几个数据库增删改查的权限
show grants for '用户'@'IP地址' -- 查看权限 grant 权限 on 数据库.表 to '用户'@'IP地址' -- 授权 revoke 权限 on 数据库.表 from '用户'@'IP地址' -- 取消权限
授权要写到具体表上
grant 权限 on 数据库.* to '用户'@'IP地址' 所有表，即整个库
部分权限为：
all privileges 除grant外的所有权限
select 仅查权限
select,insert 查和插入权限
...
create 使用create table
create temporary tables 使用create temporary tables
delete 使用delete
drop 使用drop table
index 使用index
insert 使用insert
process 使用show full processlist
select 使用select
show databases 使用show databases
show view 使用show view
update 使用update
reload 使用flush
flush privileges，将数据读取到内存中，从而立即生效
三 库、表操作
1. database
# 增 create database db1; create database db1 charset utf8;
# 查看数据库 show create database db1 G; # 查看所有的数据库 show databases;
# 改 alter database db1 charset gbk;
# 删 drop database db1;
2. table
2.1 创建表
create table table_name (column_name1 column_type null others, column_name2 column_type not null default 2 others，
constraint) engine=innodb default charset=utf8;
- 逗号表示间断
- default，不为空时的默认值
- null/not null规定该列某个数据能否为空。
- engine 设置引擎，包括innodb mysisam，其中innodb支持事务，原子性操作。
- charset 设置编码。
- others包括：auto_increment自增，只能有一列, primary key主键，不能重复且不能为空，两者一般会一起
实例：
mysql> CREATE TABLE runoob_tbl( -> runoob_id INT NOT NULL AUTO_INCREMENT, -> runoob_title VARCHAR(100) NOT NULL, -> runoob_author VARCHAR(40) NOT NULL, -> submission_date DATE, -> PRIMARY KEY ( runoob_id ) -> )ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.2 查看及删除
#切换文件夹 use db1; #查看当前所在文件夹 select database(); #查看t1表创建时的命令 show create table t1 G; (G为选装90度) # 查看所有的表 show tables; # 查看表的详细信息 desc t1; # 清空表 delete from table_name; #如果有自增id，新增的数据，以删除前的最后一样作为起始 truncate table table_name; #数据量大，删除速度比上一条快，且直接从零开始 # 删除表 drop table table_name
修改表名：alter table t_book rename to bbb; 添加列：alter table 表名 add column 列名 varchar(30); 删除列：alter table 表名 drop column 列名; 修改列名： alter table bbb change nnnnn hh int; 修改列属性：alter table t_book modify name varchar(22);
3. 数据类型
MySQL的数据类型大致分为：数值、时间和字符串
#1. 数字： 整型：tinyint int bigint 小数： float[(m,d)] ：m表示总位数，d表示小数点之后的位数；小数越多，越不精准 double[(m,d)] ：精度会比float准确，但也不是绝对精准 0.000001230123123123 存成：0.000001230000 decimal[(m[,d])]：（如果用小数，则用推荐使用decimal） 精准,内部原理是以字符串形式去存 #2. 字符串： char（10）：简单粗暴，浪费空间，存取速度快，最大为255字符 root存成root000000 varchar(10)：精准，节省空间，存取速度慢，最大为255字符 自己进行的sql优化：创建表时，定长的类型往前放，变长的往后放 比如性别 比如地址或描述信息 大于255字符时，只把径存放到数据库中。比如大文件，图片，视频等会有文件服务器。 #3. 时间类型： 最常用：datetime
（YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59 Y）
#4. 枚举类型与集合类型 enum 和set
枚举实例：只能取其中一个
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');
集合实例：只能取由其组成的任意组合
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. 基本方法
insert into，delete from ，select from ，update set 都是表方法，不需要加table关键字；
drop, create 是全局方法，需要加table 或者database 关键字
# 增 有value和select两种方法，两者都是在表的下端继续加入行 insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) #增加多行数据
insert into 表1 (列名,列名...) select 列名,列名 from 表2 #表2列数据以行的形式增加到表1，在查询的基础上增加insert into 表1（列名，列名）
insert into class(caption) values ('三年级二班'),('一年级三班'),('三年级一班');
注意：字符串需要加引号
# 删 delete from 表 #清空整个表 delete from 表 where id=1 #清空整行，相当于省略了select * # 改 update 表 set name＝'matt'，age='26' #修改整列 update 表 set name＝'matt' where id=1 #修改元素 # 查 select * from 表 #查看整张表，不推荐 select nid,name,gender from 表 #查看多列
2. 语法
一、单表查询的语法 SELECT 字段1,字段2... FROM 表名 WHERE 条件 GROUP BY field HAVING 筛选 ORDER BY field LIMIT 限制条数 二、关键字的执行优先级（重点） from where group by having select distinct order by limit 1.找到表:from 2.拿着where指定的约束条件，去文件/表中取出一条条记录 3.将取出的一条条记录进行分组group by，如果没有group by，则整体作为一组 4.将分组的结果进行having过滤 5.执行select 6.去重 7.将结果按条件排序：order by 8.限制结果的显示条数
3. where
select * from 表 where id > 1 and name != 'alex' and num = 12; 比较运算符：>, <, >=, <=, != select * from 表 where id between 5 and 16; 闭区间，区别于limit，有时候id不连续 select * from 表 where id in (11,22,33) select * from 表 where id not in (11,22,33) select * from 表 where id in (select nid from 表)
4. 通配符
select * from 表 where name like 'ale%' - ale开头的所有（多个字符串） select * from 表 where name like 'ale_' - ale开头的所有（一个字符）
5. limit
limit表示的为行数，不同于id，分页时注意区分
select * from 表 limit 5; - 前5行 select * from 表 limit 4,5; - 从第4行开始的5行 select * from 表 limit 5 offset 4 - 从第4行开始的5行
6. order
select * from 表 order by 列 asc - 根据 “列” 从小到大排列 select * from 表 order by 列 desc - 根据 “列” 从大到小排列 select * from 表 order by 列1 desc,列2 asc - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
7. group by、having、聚类函数
分类必带“每”字
select num from 表 group by num select num from 表 where nid > 10 group by num order nid desc select num,count(*),sum(score),max(score),min(score) from 表 group by num
注意：select中只能使用分组字段和聚合函数
select count（id） from 表；查询表内数据的个数，因为未分组前整表默认是一组。
select num from 表 where nid > 10 group by num having max(id) > 10 注意：group by 必须在where之后，order by之前
having与where不同点：
- 优先级从高到低：where > group by > having
- Where 发生在分组group by之前，因而Where中可以有任意字段，但不能使用聚合函数。
- Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数
聚类函数包括：
- max()求最大值
- min()求最小值
- avg()求平均值
- sum() 求和
- count() 求总个数
8. 连表查询
select 字段列表 from 表1 inner|left|right join 表2 on 表1.字段 = 表2.字段;
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), ('nvshen','male',18,200), ('xiaomage','female',18,204) ; # 查看表结构和数据 mysql> desc department; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | name | varchar(20) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ rows in set (0.19 sec) mysql> desc employee; +--------+-----------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+-----------------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | YES | | NULL | | | sex | enum('male','female') | NO | | male | | | age | int(11) | YES | | NULL | | | dep_id | int(11) | YES | | NULL | | +--------+-----------------------+------+-----+---------+----------------+ rows in set (0.01 sec) mysql> select * from department; +------+--------------+ | id | name | +------+--------------+ | 200 | 技术 | | 201 | 人力资源 | | 202 | 销售 | | 203 | 运营 | +------+--------------+ rows in set (0.02 sec) 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 | nvshen | male | 18 | 200 | | 6 | xiaomage | female | 18 | 204 | +----+----------+--------+------+--------+ rows in set (0.00 sec)
8.1 笛卡尔积
mysql> select * from employee,department; +----+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +----+----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 1 | egon | male | 18 | 200 | 201 | 人力资源 | | 1 | egon | male | 18 | 200 | 202 | 销售 | | 1 | egon | male | 18 | 200 | 203 | 运营 | | 2 | alex | female | 48 | 201 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 2 | alex | female | 48 | 201 | 202 | 销售 | | 2 | alex | female | 48 | 201 | 203 | 运营 | | 3 | wupeiqi | male | 38 | 201 | 200 | 技术 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 202 | 销售 | | 3 | wupeiqi | male | 38 | 201 | 203 | 运营 | | 4 | yuanhao | female | 28 | 202 | 200 | 技术 | | 4 | yuanhao | female | 28 | 202 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 4 | yuanhao | female | 28 | 202 | 203 | 运营 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 201 | 人力资源 | | 5 | nvshen | male | 18 | 200 | 202 | 销售 | | 5 | nvshen | male | 18 | 200 | 203 | 运营 | | 6 | xiaomage | female | 18 | 204 | 200 | 技术 | | 6 | xiaomage | female | 18 | 204 | 201 | 人力资源 | | 6 | xiaomage | female | 18 | 204 | 202 | 销售 | | 6 | xiaomage | female | 18 | 204 | 203 | 运营 |
8.2 内连接
只连接笛卡尔积匹配的行
mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee inner join department on employee.dep_id=department.id; +----+---------+------+--------+--------------+ | id | name | age | sex | name | +----+---------+------+--------+--------------+ | 1 | egon | 18 | male | 技术 | | 2 | alex | 48 | female | 人力资源 | | 3 | wupeiqi | 38 | male | 人力资源 | | 4 | yuanhao | 28 | female | 销售 | | 5 | nvshen | 18 | male | 技术 | +----+---------+------+--------+--------------+#上述sql等同于 mysql> select employee.id,employee.name,employee.age,employee.sex,department.name from employee,department where employee.dep_id=department.id;
8.3 外左连接
优先显示左边全部记录
mysql> select employee.id,employee.name,department.name as depart_name from employee left join department on employee.dep_id=department.id; +----+----------+--------------+ | id | name | depart_name | +----+----------+--------------+ | 1 | egon | 技术 | | 5 | nvshen | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 6 | xiaomage | NULL | +----+----------+--------------+
8.4 外右连接
优先显示右表全部记录
mysql> select employee.id,employee.name,department.name as depart_name from employee right join department on employee.dep_id=department.id; +------+---------+--------------+ | id | name | depart_name | +------+---------+--------------+ | 1 | egon | 技术 | | 2 | alex | 人力资源 | | 3 | wupeiqi | 人力资源 | | 4 | yuanhao | 销售 | | 5 | nvshen | 技术 | | NULL | NULL | 运营 | +------+---------+--------------+
8.5 全外连接
mysql> 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 ; +------+----------+--------+------+--------+------+--------------+ | id | name | sex | age | dep_id | id | name | +------+----------+--------+------+--------+------+--------------+ | 1 | egon | male | 18 | 200 | 200 | 技术 | | 5 | nvshen | male | 18 | 200 | 200 | 技术 | | 2 | alex | female | 48 | 201 | 201 | 人力资源 | | 3 | wupeiqi | male | 38 | 201 | 201 | 人力资源 | | 4 | yuanhao | female | 28 | 202 | 202 | 销售 | | 6 | xiaomage | female | 18 | 204 | NULL | NULL | | NULL | NULL | NULL | NULL | NULL | 203 | 运营 | +------+----------+--------+------+--------+------+--------------+ 7 rows in set (0.01 sec) #注意 union与union all的区别：union会去掉相同的纪录
union 的用法：把多列连成一列，表头以第一表为准：
select cid ,caption FROM class
UNION
SELECT sid, sname FROM student
实例练习：
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;
8.6 n表连接
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
9. 子查询
子查询是将一个查询语句嵌套在另一个查询语句中，内层查询语句的查询结果，可以为外层查询语句提供查询条件。
子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字，还可以包含比较运算符：= 、 !=、> 、<等
9.1 带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人的部门名 select name from department where id not in (select dep_id from employee group by dep_id);
9.2 带比较运算符的查询
#比较运算符：=、!=、>、>=、<、<=、<> #查询大于所有人平均年龄的员工名与年龄 mysql> select name,age from employee where age > (select avg(age) from employee); +---------+------+ | name | age | +---------+------+ | alex | 48 | | wupeiqi | 38 | +---------+------+ #查询大于部门内平均年龄的员工名、年龄 mysql> select t1.name,t1.age from employee as t1 inner join (select dep_id,avg(age) as avg_age from employee group by dep_id) as t2 on t1.dep_id = t2.dep_id where t1.age > t2.avg_age; +------+------+ | name | age | +------+------+ | alex | 48 |
9.3 带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 | nvshen | male | 18 | 200 | | 6 | xiaomage | female | 18 | 204 | +----+----------+--------+------+--------+ mysql> select * from employee where exists (select id from department where id=204); Empty set (0.00 sec)
10. 其它
mysql中： 1.isnull(exper) 判断exper是否为空，是则返回1，否则返回0 2.ifnull(exper1,exper2)判断exper1是否为空，是则用exper2代替 3.nullif(exper1,exper2)如果expr1= expr2 成立，那么返回值为NULL，否则返回值为 expr1。
if(expr1,expr2,expr3)，如果expr1的值为true，则返回expr2的值，如果expr1的值为false，
临时表：(select * from tb where id>10) as A
指定映射：
select id,name,111,sum(x)/count()
条件：
case when id>8 then 0 else 1 end
五 约束性条件
约束条件都是可选参数
primary key #标识该字段为该表的主键，可以唯一的标识记录 foreign key #标识该字段为该表的外键 not null #标识该字段不能为空，默认为null unique #标识该字段的值是唯一的 auto_increment #标识该字段的值自动增长（整数类型，而且为主键） default #为该字段设置默认值 unsigned #无符号 zerofill #使用0填充
1 查看表的字段信息：desc 表名; 2 查看表的所有信息：show create table 表名; 3 添加主键约束：alter table 表名 add constraint 主键 （形如：PK_表名） primary key 表名(主键字段); 4 添加外键约束：alter table 从表 add constraint 外键（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段); 5 删除主键约束：alter table 表名 drop primary key; 6 删除外键约束：alter table 表名 drop foreign key 外键（区分大小写）;
1. unique
# 单列唯一 create table department( id int unique, name char(10) unique ); # 联合唯一，多列数据只有一列不相同即可 create table department( id int, name char(10) , unique unique_name (id，name) );
2. primary key
等价于 not null unique, 字段的值不为空且唯一
存储引擎默认是（innodb），对于innodb存储引擎来说，一张表必须有一个主键。
# 单列主键 create table t14( id int primary key, #primary key 与not null unique完全等效 name char(16) ); create table t15( id int not null unique, name char(16) ); # 复合主键 create table t16( ip char(15), port int, primary key(ip,port) );
3. foreign key
create table userinfo( uid int auto_increment primary key, name varchar(32), department_id int, constraint fk_user_depar foreign key (department_id) references department(id) )engine=innodb default charset=utf8; create table department( id bigint auto_increment primary key, title char(15) )engine=innodb default charset=utf8;
解析：
- construction，foreign key，references：关键字；
- fk_user_depar：外键名称，不能重复；
- department_id为：内表的外键关联列
- department（id）：外表关联列
4. foreign key变形
4.1 1对1
create table user( id int primary key auto_increment, name varchar(20) ); # 设置外键的为内表 create table blog( id int primary key auto_increment, url varchar(100), user_id int unique, constraint fk_user foreign key(user_id) references user(id) on delete cascade #同步删除：外表删除，关联的内表一起删除 on update cascade #同步修改：外表修改，内表一起修改 );
4.2 1对多
create table press( id int primary key auto_increment, name varchar(20) ); create table book( id int primary key auto_increment, name varchar(20), press_id int not null, constraint fk_book_press foreign key(press_id) references press(id) on delete cascade on update cascade );
4.3 多对多
create table author( id int primary key auto_increment, name varchar(20) );
create table book(
id int primary key auto_increment;
name varchar(30)
create table author2book( id int not null primary key auto_increment, author_id int not null, book_id int not null, constraint fk_author foreign key(author_id) references author(id) on delete cascade on update cascade, constraint fk_book foreign key(book_id) references book(id) on delete cascade on update cascade, unique unique_name (author_id,book_id) #联合唯一，不能重复，unique_name为唯一索引名称 );
5. auto_increment
create table student( id int primary key auto_increment, name varchar(20), sex enum('male','female') default 'male' );
无论是在中间加入（前id=4，加入的设置为id=10）还是删除造成id不连续，再次插入一条不指定id的记录时会在之前的最后一条记录继续增长。
# 更改自增值 show create table table_name;可查看auto_increment的具体值 alter table table_name auto_increment = 20
# 实例：
mysql> show create table firstapp_book G;
*************************** 1. row ***************************
Table: firstapp_book
Create Table: CREATE TABLE `firstapp_book` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(32) NOT NULL,
`press_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4
设置步长及起始值
# 基于会话级别，关闭cmd对话框即消失 show session variables like 'auto_inc%'; set session auto_increment_increment=2; #设置会话步长 set session auto_increment_offset=10; #设置会话起始值 # 基于全局级别 shwo global variables like 'auto_inc%'; set global auto_increment_increment=2; #设置全局步长 set global auto_increment_offset=10; #设置全局起始值
mysql> show variables like'auto_inc%'; +--------------------------+-------+ | Variable_name | Value | +--------------------------+-------+ | auto_increment_increment | 5 | | auto_increment_offset | 3 | +--------------------------+-------+
# 下次插入的时候，从起始位置3开始，每次插入记录id+5
六 Navicate
在生产环境中操作MySQL数据库还是推荐使用命令行工具mysql，但在我们自己开发测试时，可以使用可视化工具Navicat。
Navicate主要用于：
- 查询
- 导入数据
转储SQL文件：
- 数据和结构，mysqldump -rname -ppwd dabatase_name > file_name
- 仅结构，mysqldump -rname -ppwd -d dabatase_name > file_name
运行SQL文件：
- 首先在mysql内创建数据库database_name
- mysqldump -rname -ppwd dabatase_name < file_name
七 练习
题目：https://www.cnblogs.com/wupeiqi/articles/5729934.html
答案：https://www.cnblogs.com/wupeiqi/p/5748496.html
2. SELECT A.student_id FROM ( SELECT student_id, num AS bio_sco FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname = '生物' ) AS A LEFT JOIN ( SELECT student_id, num AS phi_sco FROM score LEFT JOIN course ON score.course_id = course.cid WHERE cname = '物理' ) AS B ON A.student_id = B.student_id WHERE bio_sco > ifnull( phi_sco, 0 ) 3. select student_id,avg(num) from score group by student_id HAVING avg(num) > 60 4. select sid, count_course, sum_num,sname from (SELECT student_id,COUNT(course_id) as count_course,sum(num) as sum_num FROM score GROUP BY student_id)as A LEFT JOIN student on A.student_id = sid 5. select COUNT(tid) from (SELECT * FROM teacher WHERE tname LIKE '李%' ) as B 6. select sid , sname from student WHERE sid not in (select student_id from score INNER JOIN (SELECT cid from course LEFT JOIN teacher on course.teacher_id= teacher.tid WHERE tname='李平老师') as B on score.course_id = B.cid GROUP BY student_id) 7. SELECT sid , sname FROM student RIGHT JOIN( SELECT student_id from score WHERE course_id in (1,2) GROUP BY student_id HAVING COUNT(course_id) = 2) as B on student.sid = B.student_id 8. SELECT sid , sname FROM student RIGHT JOIN( SELECT student_id from score WHERE course_id in (SELECT cid from course LEFT JOIN teacher on course.teacher_id= teacher.tid WHERE tname='李平老师') GROUP BY student_id HAVING COUNT(course_id) = ((SELECT COUNT(1) from course LEFT JOIN teacher on course.teacher_id= teacher.tid WHERE tname='李平老师'))) as B on student.sid = B.student_id 9. SELECT student.sid,student.sname FROM ( SELECT id_b FROM ( SELECT student_id AS id_a,num AS num_a FROM score WHERE course_id=2) AS A RIGHT JOIN ( SELECT student_id AS id_b,num AS num_b FROM score WHERE course_id=1) AS B ON A.id_a=B.id_b WHERE num_b> ifnull(num_a,0)) AS D LEFT JOIN student ON D.id_b=student.sid 10. SELECT student.sid,student.sname FROM ( SELECT student_id FROM score WHERE num < 60 GROUP BY student_id ) AS D LEFT JOIN student ON D.student_id=student.sid 11. SELECT student.sid,student.sname FROM ( SELECT sid FROM student WHERE student.sid NOT IN ( SELECT student_id FROM score GROUP BY student_id HAVING count(course_id)=4)) AS D LEFT JOIN student ON D.sid=student.sid 12. SELECT student.sid,student.sname FROM ( SELECT student_id FROM score WHERE course_id IN ( SELECT course_id FROM score WHERE student_id=1) GROUP BY student_id) AS D LEFT JOIN student ON D.student_id=student.sid 13. 14. SELECT student.sid,student.sname FROM ( SELECT student_id FROM score WHERE course_id IN ( SELECT course_id FROM score WHERE student_id=2) GROUP BY student_id HAVING COUNT(course_id)= 3 ) AS D LEFT JOIN student ON D.student_id=student.sid 15.DELETE FROM score WHERE course_id in (SELECT cid FROM course WHERE teacher_id = (SELECT tid from teacher WHERE tname = '李平老师') ) 16.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. SELECT DISTINCT student_id,( SELECT num FROM score AS B WHERE B.student_id=A.student_id AND course_id=1) AS '语文',( SELECT num FROM score AS B WHERE B.student_id=A.student_id AND course_id=2) AS '数学',( SELECT num FROM score AS B WHERE B.student_id=A.student_id AND course_id=3) AS '英语',(SELECT AVG(num) FROM score )as avg_num FROM score AS A 18. SELECT course_id,MAX(num),MIN(num) FROM score GROUP BY course_id 19. SELECT course_id,AVG(num),SUM(CASE WHEN num < 60 THEN 0 ELSE 1 END) ,sum(1) FROM score GROUP BY course_id ORDER BY AVG(num) asc 20. SELECT course_id,avg_num,tname FROM ( SELECT course_id,AVG(num) AS avg_num FROM score GROUP BY course_id) AS A LEFT JOIN course ON A.course_id=course.cid LEFT JOIN teacher ON course.teacher_id=teacher.tid 21. SELECT DISTINCT A.course_id,( SELECT B.num FROM score AS B WHERE B.course_id=A.course_id GROUP BY B.num ORDER BY B.num DESC LIMIT 1) AS one,( SELECT C.num FROM score AS C WHERE C.course_id=A.course_id GROUP BY C.num ORDER BY C.num DESC LIMIT 1,1) AS two FROM score AS A 22. SELECT course_id, COUNT(1) FROM score GROUP BY course_id 23. SELECT * FROM score GROUP BY course_id HAVING COUNT(course_id)=1 24. SELECT COUNT(gender) FROM student GROUP BY gender 25. SELECT sname FROM student WHERE sname LIKE '张%' 26. SELECT sname FROM student GROUP BY sname HAVING COUNT(1 ) > 1 27. SELECT course_id, AVG(IFNULL(num,0)) as aa FROM score GROUP BY course_id ORDER BY aa 28. SELECT student_id, AVG(num) FROM score GROUP BY student_id HAVING AVG(num) > 85 29. SELECT student_id , num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname = '生物' and num > 60 30. SELECT student_id,num FROM score WHERE course_id = 3 and num > 80 31. select count(distinct student_id) from score SELECT count(c) FROM (SELECT MIN(num) AS c FROM score GROUP BY student_id) AS A
八 pymysql
1. pymysql使用
# 实现：使用Python实现用户登录，如果用户存在则登录成功（假设该用户已在数据库中） import pymysql user = input('请输入用户名：') pwd = input('请输入密码：') # 1.连接 conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', password='', db='db8', charset='utf8') # 2.创建游标 cursor = conn.cursor() #注意%s需要加引号 sql = "select * from userinfo where username='%s' and pwd='%s'" %(user, pwd) print(sql) # 3.执行sql语句 cursor.execute(sql)
# result = cursor.fetchone() #获取第一条数据 result=cursor.execute(sql) #执行sql语句，返回sql查询成功的记录数目 print(result) # 关闭连接，游标和连接都要关闭 cursor.close() conn.close()
if result: print('登陆成功') else: print('登录失败')
2. sql注入及解决方法
#1、sql注入之：用户存在，绕过密码 mjj' -- 任意字符 #2、sql注入之：用户不存在，绕过用户与密码 xxx' or 1=1 -- 任意字符
#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为%s加引号了） #列表形式
sql="select * from userinfo where name=%s and password=%s" #！！！注意%s需要去掉引号，因为pymysql会自动为我们加上 result=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。
#字典形式
sql="select * from userinfo where name=%(u)s and password=%(p)s"
result=cursor.execute(sql,{'u':user,'p':pwd})
3. 增删改
import pymysql username = input('请输入用户名：') pwd = input('请输入密码：') # 1.连接 conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.创建游标 cursor = conn.cursor()
# 增 # sql = "insert into userinfo(username,pwd) values (%s,%s)" # effect_row = cursor.execute(sql,(username,pwd)) #同时插入多条数据 #cursor.executemany(sql,[('李四'，'110'),（'王五'，'119'）])
# print(effect_row) #返回值为受影响的行数 # 改 # sql = "update userinfo set username = %s where id = 2" # effect_row = cursor.execute(sql,username) # print(effect_row) # 删 sql = "delete from userinfo where id = 2" effect_row = cursor.execute(sql) print(effect_row) #最后一定记得commit conn.commit() # 4.关闭游标 cursor.close() # 5.关闭连接 conn.close()
4. 查
- fetchone():获取下一行数据，第一次为首行；
- fetchall():获取所有行数据源
- fetchmany(4):获取4行数据
import pymysql # 1.连接 conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.创建游标 cursor = conn.cursor() sql = 'select * from userinfo' cursor.execute(sql) # 获取所有的数据 rows = cursor.fetchall() print(rows)
# 4.关闭游标 cursor.close() # 5.关闭连接 conn.close() #运行结果 ((1, 'mjj', '123'), (3, '张三', '110'), (4, '李四', '119'))
- cursor.scroll(1,mode='relative') 相对当前位置移动
- cursor.scroll(2,mode='absolute') 相对绝对位置移动
第一个值为移动的行数，整数为向下移动，负数为向上移动，mode指定了是相对当前位置移动，还是相对于首行移动
# 1.Python实现用户登录 # 2.Mysql保存数据 import pymysql # 1.连接 conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.创建游标 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = 'select * from userinfo' cursor.execute(sql) # 查询第一行的数据 row = cursor.fetchone() print(row) # (1, 'mjj', '123') # 查询第二行数据 row = cursor.fetchone() # (3, '张三', '110') print(row) cursor.scroll(-1,mode='relative') #设置之后，光标相对于当前位置往前移动了一行，所以打印的结果为第二行的数据 row = cursor.fetchone() print(row) cursor.scroll(0,mode='absolute') #设置之后，光标相对于首行没有任何变化，所以打印的结果为第一行数据 row = cursor.fetchone() print(row) # 4.关闭游标 cursor.close() # 5.关闭连接 conn.close() #结果如下 {'id': 1, 'username': 'mjj', 'pwd': '123'} {'id': 3, 'username': '张三', 'pwd': '110'} {'id': 3, 'username': '张三', 'pwd': '110'} {'id': 1, 'username': 'mjj', 'pwd': '123'}
默认情况下，我们获取到的返回值是元组，只能看到每行的数据，却不知道每一列代表的是什么，这个时候可以使用以下方式来返回字典，每一行的数据都会生成一个字典：
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #在实例化的时候，将属性cursor设置为pymysql.cursors.DictCursor
import pymysql # 1.连接 conn = pymysql.connect(host='localhost', port=3306, user='root', password='', db='db8', charset='utf8') # 2.创建游标 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) sql = 'select * from userinfo' cursor.execute(sql) # 获取2条数据 rows = cursor.fetchmany(2) print(rows) # 4.关闭游标 # rows = cursor.fetchall() # print(rows) cursor.close() # 5.关闭连接 conn.close() #结果如下： [{'id': 1, 'username': 'mjj', 'pwd': '123'}, {'id': 3, 'username': '张三', 'pwd': '110'}]
获取自增ID
new_id = cursor.lastrowid
九 view视图
视图类似于临时表，能够持久化；视图与原表的更新是同步的，不能自己更改数据。
--增 格式：CREATE VIEW 视图名称 AS SQL语句 CREATE VIEW v1 AS SELECT nid,NAME FROM A WHERE nid> 4 --删 格式： DROP VIEW 视图名称 DROP VIEW v1 -- 改 格式：ALTER VIEW 视图名称 AS SQL语句 ALTER VIEW v1 AS SELECT A.nid,B.NAME FROM A LEFT JOIN B ON A.id=B.nid LEFT JOIN C ON A.id=C.nid WHERE A.id> 2 AND C.nid< 5 -- 使用 select * from v1
十 trigger 触发器
对某个表进行【增/删/改】操作的前后如果希望触发某个特定的行为时，可以使用触发器，触发器用于定制用户对表的行进行【增/删/改】前后的行为。多用于多表的联动。
# 插入前 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
插入前后触发器
delimiter // --更改分隔符 CREATE TRIGGER tri_before_insert_tb1 BEFORE INSERT ON tb1 FOR EACH ROW BEGIN IF NEW.NAME == 'alex' THEN INSERT INTO tb2 ( NAME )VALUES( 'aa' ) END --注意此处没有if和分号 END // delimiter;
delimiter // CREATE TRIGGER tri_after_insert_tb1 AFTER INSERT ON tb1 FOR EACH ROW BEGIN IF NEW.num = 666 THEN --new代表新插入的整行数据，在inset中使用，old代表删除的行数据，在delete中使用，两者都能在update中使用 INSERT INTO tb2 ( NAME )VALUES( '666' ),( '666' ); ELSEIF NEW.num = 555 THEN INSERT INTO tb2 ( NAME )VALUES( '555' ),( '555' ); END IF; --注意此处有if和分号 END // delimiter;
删除触发器
DROP TRIGGER tri_after_insert_tb1;
触发器无法由用户直接调用，而知由于对表的【增/删/改】操作被动引发的。
十一 function 函数
函数会使索引受限制，导致性能变低。
CHAR_LENGTH(str) 返回值为字符串str 的长度，长度的单位为字符。一个多字节字符算作一个单字符。 对于一个包含五个二字节字符集, LENGTH()返回值为 10, 而CHAR_LENGTH()的返回值为5。 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) 变大写 LTRIM(str) 返回字符串 str ，其引导空格字符被删除。 RTRIM(str) 返回字符串 str ，结尾空格字符被删去。 SUBSTRING(str,pos,len) 获取字符串子序列 LOCATE(substr,str,pos) 获取子序列索引位置 REPEAT(str,count) 返回一个由重复的字符串str 组成的字符串，字符串str的数目等于count 。 若 count <= 0,则返回一个空字符串。 若str 或 count 为 NULL，则返回 NULL 。 REPLACE(str,from_str,to_str) 返回字符串str 以及所有被字符串to_str替代的字符串from_str 。 REVERSE(str) 返回字符串 str ，顺序和字符顺序相反。 RIGHT(str,len) 从字符串str 开始，返回从后边开始len个字符组成的子序列 SPACE(N) 返回一个由N空格组成的字符串。 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' TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str) 返回字符串 str ， 其中所有remstr 前缀和/或后缀都已被删除。若分类符BOTH、LEADIN或TRAILING中没有一个是给定的,则假设为BOTH 。 remstr 为可选项，在未指定情况下，可删除空格。 mysql> SELECT TRIM(' bar '); -> 'bar' mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); -> 'barxxx' mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); -> 'bar' mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); -> 'barx' 部分内置函数
官网函数：
https://dev.mysql.com/doc/refman/8.0/en/functions.html
自定义函数
delimiter \ CREATE FUNCTION f1 ( i1 INT, i2 INT ) RETURNS INT BEGIN DECLARE num INT; SET num = i1 + i2; RETURN ( num ); END \
delimiter; --删除函数 drop function func_name; --调用函数 select f1(11,nid) ,name from tb2;
函数有返回值，函数内部不能使用select语句
十二 存储过程
存储过程就是封装了一串mysql语句，直接调用即可，没有返回值，只有伪造参数，能够使用select语句
1. 无参数存储
-- 创建存储过程 delimiter // create procedure p1() BEGIN select * from t1; END// delimiter ; -- 执行存储过程 call p1()
--删除存储
drop procedure proc_name;
2. 有参数存储
对于存储过程，可以接收参数，其参数有三类：
- in 仅用于传入参数用
- out 仅用于返回值用，传不进去
- inout 既可以传入又可以当作返回值
输出的变量实际是伪造的返回值，相当于一全局变量。其作用为：设置存储过程的执行结果。
-- 创建存储过程 delimiter \ create procedure p1( in i1 int, in i2 int, inout i3 int, out r1 int ) BEGIN DECLARE temp1 int; DECLARE temp2 int default 0; --declare表示声明变量 set temp1 = 1; --set为赋值符号 set r1 = i1 + i2 + temp1 + temp2; set i3 = i3 + 100;
select * from score --设置结果集 end\ delimiter ; -- 执行存储过程 set @t1 =4; --@表示是session级别的变量 set @t2 = 0; CALL p1 (1, 2 ,@t1, @t2); --查询结果集 SELECT @t1,@t2; --查询输出变量
3. 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', charset='utf8') cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 执行存储过程 cursor.callproc('p1', args=(1, 22, 3, 4))
#获取结果集
result1 = cursor.fetchall()
# 获取存储参数 cursor.execute("select @_p1_0,@_p1_1,@_p1_2,@_p1_3") #in类型参数怎么进去怎么出来 result2 = cursor.fetchall() conn.commit() cursor.close() conn.close()
print(result1，result2)
4. 事务
事务用于将某些操作的多个SQL作为原子性操作，一旦有某一个出现错误，即可回滚到原来的状态，从而保证数据库数据完整性。
delimiter \ create PROCEDURE p1(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 tb2(name)values('seven'); COMMIT; -- SUCCESS set p_return_code = 0; END\ delimiter ；
5. if语句
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 ；
6. 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 ;
7. 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 ;
8. 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
参考，佩奇博客对存储的讲解：
https://www.cnblogs.com/wupeiqi/articles/5713323.html
十三 索引
索引的作用就是加速查找，优化mysql的作用。
无索引： 从前往后一条一条查询 有索引：创建索引的本质，就是创建额外的文件（某种格式存储，查询的时候，先去格外的文件找，定好位置，然后再去原始表中直接查询。但是创建索引越多，会对硬盘也是有损耗。 建立索引的目的： a.额外的文件保存特殊的数据结构 b.查询快，但是插入更新删除依然慢 c.创建索引之后，必须命中索引才能有效 无索引和有索引的区别以及建立索引的目的
索引内部结构分为： hash索引和BTree索引 （1）hash类型的索引：查询单条快，范围查询慢，应用范围不大 （2）btree类型的索引：b+树，层数越多，数据量指数级增长（innodb默认支持它），应用较广
1. 索引的分类：
- 普通索引
- 唯一索引
- 主键索引
- 联合索引（多列）
- 联合主键索引
- 联合唯一索引
- 联合普通索引
1.1 普通索引
--创建索引 create table userinfo( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, index ix_name(name) ); --创建索引 create index 索引的名字 on 表名(列名) --删除索引 drop index 索引的名字 on 表名 --查看索引 show index from 表名
注意：对于创建索引时如果是BLOB 和 TEXT 类型，必须指定length。
create index ix_extra on in1(text1(32));
1.2 唯一索引
--创建索引 create table userinfo( id int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, unique index ix_name(name) ); --创建索引 create unique index 索引名 on 表名(列名) --删除索引 drop index 索引名 on 表名;
1.3 主键索引
--创建主键索引 create table userinfo( id int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null ) or create table userinfo( id int not null auto_increment, name varchar(32) not null, email varchar(64) not null, primary key(nid) ) --创建主键索引 alter table 表名 add primary key(列名); --删除索引 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
1.4 组合索引
其应用场景为：频繁的同时使用n列来进行查询，如：where name = 'matt' and email = 'matt@qq.com'。
create index ix_name_email on in3(name,email);
最左前缀匹配
create index ix_name_email on userinfo(name,email); select * from userinfo where name = 'alex'; select * from userinfo where name = 'alex' and email='alex@oldBody'; select * from userinfo where email='alex@oldBody'; 如果使用组合索引如上，name和email组合索引之后，查询 （1）name和email ---使用索引 （2）name ---使用索引 （3）email ---不适用索引 对于同时搜索n个条件时，组合索引的性能好于多个单列索引 ******组合索引的性能>索引合并的性能*********
2. 索引名词
#覆盖索引：在索引文件中直接获取数据 例如： select name from userinfo where name = 'alex50000'; #索引合并：把多个单列索引合并成使用 例如： select * from userinfo where name = 'alex13131' and id = 13131;
3. 命中索引
#1. 准备300w数据表 create table userinfo( id int, name varchar(20), gender char(6), email varchar(50) ); #2. 创建存储过程，实现批量插入记录 delimiter $$ #声明存储过程的结束符号为$$ create procedure auto_insert1() BEGIN declare i int default 1; while(i<3000000)do insert into userinfo values(i,concat('alex',i),'male',concat('egon',i,'@oldboy')); set i=i+1; end while; END$$ #$$结束 delimiter ; #重新声明分号为结束符号 #3. 查看存储过程 show create procedure auto_insert1G #4. 调用存储过程 call auto_insert1();
不能命中索引的情况：
-- 此例子中id，name建立索引，email未建立索引，一般主键都有特殊情况 -- 避免使用迷糊查找 like '%xx'，实际中很少使用，一般适应第三方工具 select * from userinfo where name like '%al'; -- 避免使用函数 select * from userinfo where reverse(name) = 'alex333'; -- 避免使用 or select * from userinfo where id = 1 or email = 'alex122@oldbody'; -- id有索引，而email没有索引，总体上为没有索引 -- 特别的：以下会走索引 select * from userinfo where id = 1 or name = 'alex1222'; select * from userinfo where id = 1 or email = 'alex122@oldbody' and name = 'alex112' -- 避免类型不一致 -- 如果列是字符串类型，传入条件是必须用引号引起来 select * from userinfo where name = 999; -- 避免使用判断符 !=， >等 select count(*) from userinfo where name != 'alex' -- 特别的：如果是主键，则还是会走索引 select count(*) from userinfo where id != 123 select * from userinfo where name > 'alex' -- 特别的：如果是主键或索引是整数类型，则还是会走索引 select * from userinfo where id > 123 select * from userinfo where num > 123 -- 避免使用order by select email from userinfo order by name desc; -- 当根据索引排序时候，选择的映射如果不是索引，则不走索引 -- 特别的：如果对主键排序，则还是走索引： select * from userinfo order by nid desc; -- 组合索引最左前缀 -- 如果组合索引为：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引
4. 注意事项
- 避免使用select * - count(1)或count(列) 代替 count(*) - 创建表时尽量时 char 代替 varchar，定长代替不定长 - 表的字段顺序固定长度的字段优先 - 组合索引代替多个单列索引（经常使用多个条件查询时） - 尽量使用短索引 create index index_name on info(name(4))，其中4表示name列的前4个字符 - 使用连接（JOIN）来代替子查询(Sub-Queries) - 连表时注意条件类型需一致 - 索引散列值（重复少）不适合建索引，例：性别不适合
5. 执行计划
explain + 查询SQL语句，比如 explain select * from info，返回查询方式
mysql> explain select * from userinfo; +----+-------------+----------+------+---------------+------+---------+------+---------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+----------+------+---------------+------+---------+------+---------+-------+ | 1 | SIMPLE | userinfo | ALL | NULL | NULL | NULL | NULL | 2973016 | NULL | +----+-------------+----------+------+---------------+------+---------+------+---------+-------+
select_type： 查询类型 SIMPLE 简单查询 PRIMARY 最外层查询 SUBQUERY 映射为子查询 DERIVED 子查询 UNION 联合 UNION RESULT 使用联合的结果 table： 正在访问的表名 type： 查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const ALL 全表扫描，对于数据表从头到尾找一遍 select * from userinfo; 特别的：如果有limit限制，则找到之后就不在继续向下扫描 select * from userinfo where email = 'alex112@oldboy' select * from userinfo where email = 'alex112@oldboy' limit 1; 虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。 INDEX ： 全索引扫描，对索引从头到尾找一遍 select nid from userinfo; RANGE： 对索引列进行范围查找 select * from userinfo where name < 'alex'; PS: between and in > >= < <= 操作 注意：!= 和 > 符号 INDEX_MERGE： 合并索引，使用多个单列索引搜索 select * from userinfo where name = 'alex' or nid in (11,22,33); REF： 根据索引查找一个或多个值 select * from userinfo where name = 'alex112'; EQ_REF： 连接时使用primary key 或 unique类型 select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id; CONST：常量 表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。 select id from userinfo where id = 2 ; SYSTEM：系统 表仅有一行(=系统表)。这是const联接类型的一个特例。 select * from (select id from userinfo where id = 1) as A; possible_keys：可能使用的索引 key：真实使用的 key_len： MySQL中使用索引字节长度 rows： mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值 extra： 该列包含MySQL解决查询的详细信息 “Using index” 此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。 “Using where” 这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。 “Using temporary” 这意味着mysql在对查询结果排序时会使用一个临时表。 “Using filesort” 这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。 “Range checked for each record(index map: N)” 这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的
6. 慢日志
配置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%'
修改当前配置：
set global 变量名 = 值
十四 分页
第1页： select * from userinfo limit 0,10; 第2页： select * from userinfo limit 10,10; 第3页： select * from userinfo limit 20,10; 第4页： select * from userinfo limit 30,10; ...... 第2000010页 select * from userinfo limit 2000000,10; 越往后查询，需要的时间约长，是因为越往后查，全文扫描查询，会去数据表中扫描查询。
采用覆盖索引优化效率也不高：
select * from（select id from limit 2000000,10）
最优解决方案：
记录当前页的最大和最小id，截断查询
（1）只有上一页和下一页 做一个记录：记录当前页的最大id或最小id 下一页： select * from userinfo where id > max_id limit 10; 上一页： select * from userinfo where id < min_id order by id desc limit 10; (2) 中间有页码的情况
下n页： select * from userinfo where id in( select id from (select id from userinfo where id > cur_max_id limit (tar_pag - cur_pag)*10) as A order by A.id desc limit 10 );
存疑？？？？？，in的效率高吗