MySQL

MySQL总结

SQL语言主要用于存取数据、查询数据、更新数据和管理关系数据库系统,SQL语言由IBM开发。SQL语言分为3种类型：

#1、DDL语句    数据库定义语言： 数据库、表、视图、索引、存储过程，例如CREATE DROP ALTER
#2、DML语句    数据库操纵语言： 插入数据INSERT、删除数据DELETE、更新数据UPDATE、查询数据SELECT
#3、DCL语句    数据库控制语言： 例如控制用户的访问权限GRANT、REVOKE

关闭服务端 net stop MySQL
启动服务端 net start MySQL

基本操作

增 create database db1 charset utf8;
查 show databases;
   show create database db1;
改 alter database db1 charset gbk;
删 drop database db1

查看所在库 select database();
切换库 use db1;
增 creat table t1(id int, name char);
查 show tables;
   show create table t1;
   desc t1; 查看表结构
改 alter table t1 add sex char; 添加字段
   alter table t1 drop sex; 删除字段
   alter table t1 modify name char(8); 修改字段类型
   alter table t1 change name Name char(16); 修改原字段名及该字段类型
删 drop table t1;

记录

增 insert into db1.t1(id,name) values 
   (1,'xionger'),(2,'xiongda');
查 select id,name from db1.t1  查指定字段
   select * from db1.t1  查所有(不推荐使用)
改 update t1 set name='DD' where id=2;
删 delete from t1 where id=2; 删除指定字段记录
   truncate t1; 清空表中记录(重置整张表)

表相关操作

存储引擎

存储引擎即表类型，mysql根据不同的表类型会有不同的处理机制

Innodb : 默认的存储引擎,查询速度相对myisam慢,但是更安全特点是支持行锁,支持外键

innodb类型表有两个表文件
.frm : 表结构相关
.ibd : 数据相关 (innodb的索引就是用数据组织的,以主键为依据组织数据,用树型结构减少IO优化查询)

myisam : 老版本的存储引擎

myisam类型表有三个表文件
.frm ; 表结构相关
.MYD : 数据相关
.MYI : 索引文件

memory : 内存引擎,数据全部存在内存中,断电数据消失

.frm : 表结构相关

blackhole : 黑洞引擎,无论存什么数据,立马消失

.frm : 表结构相关

查看MySQL支持的存储引擎

show enginesG
查看正在使用的存储引擎
show variables like 'storage_engine%';
创建表时指定存储引擎
create table t1(id int)engine=innodb;

语法

创建表

create table 表名(
    字段名1 类型[(宽度) 约束条件],
    字段名2 类型[(宽度) 约束条件],
    字段名3 类型[(宽度) 约束条件]
)

1. 在同一张表中，字段名是不能相同
2. 宽度和约束条件可选
3. 字段名和类型是必须的

修改表

修改表名
ALTER TABLE 表名 RENAME 新表名;
增加字段
ALTER TABLE 表名 
                ADD 字段名  数据类型 [完整性约束条件…],
                ADD 字段名  数据类型 [完整性约束条件…];
ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  FIRST;
ALTER TABLE 表名 ADD 字段名  数据类型 [完整性约束条件…]  AFTER 字段名;
删除字段
ALTER TABLE 表名 DROP 字段名;
修改字段
ALTER TABLE 表名 MODIFY  字段名 数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 旧数据类型 [完整性约束条件…];
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 新数据类型 [完整性约束条件…];

复制表

复制表结构＋记录 （key不会复制: 主键、外键和索引）
create table t2 select * from t1;
只复制表结构
select * from service where 1=2;  条件为假，查不到任何记录
reate table t4 like employees;

类型

数值类型

整型 
TINYINT SMALLINT MEDIUMINT INT BIGINT
为该类型指定宽度时，仅仅只是指定查询结果的显示宽度，与存储范围无关

浮点型
float[(M,D)] [UNSIGNED] [ZEROFILL]
定义：m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
精确度：随着小数的增多，精度变得不准确

double[(M,D)] [UNSIGNED] [ZEROFILL]
定义：m是数字总个数，d是小数点后个数。m最大值为255，d最大值为30
精确度：随着小数的增多，精度比float要高，但也会变得不准确

decimal[(m[,d])] [unsigned] [zerofill]
定义： 准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。
精确度： 随着小数的增多，精度始终准确,对于精确数值计算时需要用此类型,decaimal能够存储精确值的原因在于其内部按照字符串存储。

日期类型
YEAR 年
DATE 年-月-日
TIME 时:分:秒
DATETIME 年-月-日 时:分:秒

字符串类型

char : 定长,数据超出预定长度报错,不够用空格补位
    - 特点: 查询速度快,单浪费空间
    - 检索:在检索或者说查询时，查出的结果会自动删除尾部的空格，除非我们打开pad_char_to_full_length SQL模式（SET sql_mode = 'PAD_CHAR_TO_FULL_LENGTH';）
    - 注意:精准查找时(select * from user where name='xionger';),char类型补充的空格会自动去掉进行匹配,但是用like模糊查找时(select * from user name like 'xionger';),char类型补充的空格也会进行匹配
varchar : 变长,数据超出预定长度报错,不够就是实际长度
    - 特点: 精准,节省空间,查询较慢
    - 检索：尾部有空格会保存下来，在检索或者说查询时，也会正常显示包含空格在内的内容

查看字段长度 : char_length(字段)
    select char_length(name) from user;

集合与枚举类型

枚举enum: 多选一
集合set: 多选多

约束条件

约束条件与数据类型的宽度一样，都是可选参数

作用：用于保证数据的完整性和一致性

PRIMARY KEY (PK)    标识该字段为该表的主键，可以唯一的标识记录
FOREIGN KEY (FK)    标识该字段为该表的外键
NOT NULL    标识该字段不能为空
UNIQUE KEY (UK)    标识该字段的值是唯一的
AUTO_INCREMENT    标识该字段的值自动增长（整数类型，而且为主键）
DEFAULT    为该字段设置默认值

UNSIGNED 无符号
ZEROFILL 使用0填充

not null / default

not null - 不可空
null - 可空
default 默认值
创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
create table user(
    id int,
    name char(16) not null,
    sex char(6) not null default "male"
);

unique

单列唯一
create table user(
    id int unique,
    name char(16)
);
联合唯一
create table server(
    id int unique,
    ip char(15),
    port int,
    ip port,unique(ip,port)
)

primary key

从约束角度看: 主键等于not null + unique
1.一张表中有且有一个主键
2.若没有设置主键则会由上到下检索非空且唯一的字段作为主键
3.若没有主键也没有非空且唯一的字段,就采用默认的隐藏字段作为主键(7个bytes),这样丧失了查询效率
innodb类要依据主键组织数据结构(idb文件)
索引的目的是一步步缩小查询范围,减少IO次数

auto_increment

1.auto_increment通常与primary key连用
2.auto_increment通常加给id字段
3.auto_increment只能给被定义成key(unique key,primary key)的字段加

表关系

所有数据在一张表中导致: 组织结构不清晰,扩展性差,浪费空间

foreign key 外键是一种约束,约束两张表的关系

约束1:创建表 先建立被关联表,才能建关联表
约束2:查数据 被关联表先插入数据,关联表才能插入数据
约束3:删数据 先删除关联表中的数据,才能删被关联表的数据
约束4:不能改关联字段

级联: 同步更新,同步删除

on update cascade # 同步更新
on delete cascade # 同步删除
注: 级联是绑定给外键的

多对一:

create table dep(
    id int primary key auto_increment,
    dep_name char(16),
    dep_comment char(64)
);
create table emp(
    id int primary key auto_increment,
    name char(16),
    gender enum('male','female') not null default 'male',
    dep_id int,
    foreign key(dep_id) references dep(id) 
    on update cascade # 同步更新 
    on delete cascade # 同步删除
);

多对多:

create table author(
    id int primary key auto_increment,
    name varchar(20)
);
create table book(
    id int primary key auto_increment,
    name char(16),
    price int
);
# 创建第三张表
create table author2book(
    id int not null unique auto_increment,
    author_id int not null,
    book_id int not null,
    fk_author foreign key(author_id) references author(id) on delete cascade on update cascade,
    fk_book foreign key(book_id) references book(id) on delete cascade on update cascade,
    primary key(author_id,book_id)
);

表查询

单表查询

create table emp(
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
);

#查看表结构
mysql> desc emp;
+--------------+-----------------------+------+-----+---------+----------------+
| Field        | Type                  | Null | Key | Default | Extra          |
+--------------+-----------------------+------+-----+---------+----------------+
| id           | int(11)               | NO   | PRI | NULL    | auto_increment |
| name         | varchar(20)           | NO   |     | NULL    |                |
| sex          | enum('male','female') | NO   |     | male    |                |
| age          | int(3) unsigned       | NO   |     | 28      |                |
| hire_date    | date                  | NO   |     | NULL    |                |
| post         | varchar(50)           | YES  |     | NULL    |                |
| post_comment | varchar(100)          | YES  |     | NULL    |                |
| salary       | double(15,2)          | YES  |     | NULL    |                |
| office       | int(11)               | YES  |     | NULL    |                |
| depart_id    | int(11)               | YES  |     | NULL    |                |
+--------------+-----------------------+------+-----+---------+----------------+

#插入记录
#三个部门：教学，销售，运营
insert into emp(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.13,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.33,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)
;

案例表信息

较完整的查询语句
select id,name from emp
    where id > 1 and name like %xx%
    group by city
    having 分组后的条件
    order by 排序依据
    limit 展示条数;

DISTINCT 去重查询

查出所有的岗位（去掉重复）
SELECT DISTINCT post FROM emp;

+--------------------+
| post               |
+--------------------+
| 张江第一帅形象代言 |
| teacher            |
| sale               |
| operation          |
+--------------------+

结果

四则运行查询

查年薪,重命名
SELECT name, salary*12 FROM emp;
SELECT name, salary*12 AS year_salary FROM emp;

+--------+-------------+
| name   | year_salary |
+--------+-------------+
| jason  |    87603.96 |
| egon   | 12000003.72 |
| kevin  |    99600.00 |
| tank   |    42000.00 |
| owen   |    25200.00 |
| jerry  |   108000.00 |
| nick   |   360000.00 |
| sean   |   120000.00 |
| 歪歪   |    36001.56 |
| 丫丫   |    24004.20 |
| 丁丁   |    12004.44 |
| 星星   |    36003.48 |
| 格格   |    48003.96 |
| 张野   |   120001.56 |
| 程咬金 |   240000.00 |
| 程咬银 |   228000.00 |
| 程咬铜 |   216000.00 |
| 程咬铁 |   204000.00 |
+--------+-------------+

结果

CONCAT 定义显式

查出所有员工的名字，薪资,格式为:
    <名字:xxx>    <薪资:666666>
SELECT CONCAT('姓名: ',name,'  年薪: ', salary*12)  AS year_salary FROM emp;

+-------------------------------+
| year_salary                   |
+-------------------------------+
| 姓名: jason  年薪: 87603.96   |
| 姓名: egon  年薪: 12000003.72 |
| 姓名: kevin  年薪: 99600.00   |
| 姓名: tank  年薪: 42000.00    |
| 姓名: owen  年薪: 25200.00    |
| 姓名: jerry  年薪: 108000.00  |
| 姓名: nick  年薪: 360000.00   |
| 姓名: sean  年薪: 120000.00   |
| 姓名: 歪歪  年薪: 36001.56    |
| 姓名: 丫丫  年薪: 24004.20    |
| 姓名: 丁丁  年薪: 12004.44    |
| 姓名: 星星  年薪: 36003.48    |
| 姓名: 格格  年薪: 48003.96    |
| 姓名: 张野  年薪: 120001.56   |
| 姓名: 程咬金  年薪: 240000.00 |
| 姓名: 程咬银  年薪: 228000.00 |
| 姓名: 程咬铜  年薪: 216000.00 |
| 姓名: 程咬铁  年薪: 204000.00 |
+-------------------------------+

结果

WHERE 约束

where字句中可以使用：

1. 比较运算符：> < >= <= <> !=
2. between 80 and 100 值在80到100之间
3. in(80,90,100) 值是10或20或30
4. like 'xxx%'
pattern可以是%或_，
%表示任意多字符
_表示一个字符
5. 逻辑运算符：多个条件直接可以使用逻辑运算符 and or not

1.查看岗位是teacher的员工姓名、年龄
select name,age from emp where post = 'teacher';

+-------+-----+
| name  | age |
+-------+-----+
| egon  |  78 |
| kevin |  81 |
| tank  |  73 |
| owen  |  28 |
| jerry |  18 |
| nick  |  18 |
| sean  |  48 |
+-------+-----+

结果

2.查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select name,age from emp where post='teacher' and age > 30;

+-------+-----+
| name  | age |
+-------+-----+
| egon  |  78 |
| kevin |  81 |
| tank  |  73 |
| sean  |  48 |
+-------+-----+

结果

3. 查看岗位是teacher且薪资在9000-1000范围内的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary between 9000 and 10000;

+-------+-----+----------+
| name  | age | salary   |
+-------+-----+----------+
| jerry |  18 |  9000.00 |
| sean  |  48 | 10000.00 |
+-------+-----+----------+

结果

4. 查看岗位描述不为NULL的员工信息
select * from emp where post_comment is not null;

Empty set (0.00 sec)

结果

5. 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary in (10000,9000,30000);

+-------+-----+----------+
| name  | age | salary   |
+-------+-----+----------+
| jerry |  18 |  9000.00 |
| nick  |  18 | 30000.00 |
| sean  |  48 | 10000.00 |
+-------+-----+----------+

结果

6. 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select name,age,salary from emp where post='teacher' and salary not in (10000,9000,30000);

+-------+-----+------------+
| name  | age | salary     |
+-------+-----+------------+
| egon  |  78 | 1000000.31 |
| kevin |  81 |    8300.00 |
| tank  |  73 |    3500.00 |
| owen  |  28 |    2100.00 |
+-------+-----+------------+

结果

7. 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select name,salary*12 from emp where post='teacher' and name like 'j%';

+-------+-----------+
| name  | salary*12 |
+-------+-----------+
| jerry | 108000.00 |
+-------+-----------+

结果

GROUP BY 分组查询

1、首先明确一点：分组发生在where之后，即分组是基于where之后得到的记录而进行的

2、分组指的是：将所有记录按照某个相同字段进行归类，比如针对员工信息表的职位分组，或者按照性别进行分组等

3、大前提：可以按照任意字段分组，但是分组完毕后，比如group by post，只能查看post字段，如果想查看组内信息，需要借助于聚合函数

#查看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 emp 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

mysql> use db1;
Database changed
mysql> select * from emp 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)

ONLY_FULL_GROUP_BY

单独使用GROUP BY关键字分组
    SELECT post FROM emp GROUP BY post;
    注意：我们按照post字段分组，那么select查询的字段只能是post，想要获取组内的其他相关信息，需要借助函数

GROUP BY关键字和GROUP_CONCAT()函数一起使用
    SELECT post,GROUP_CONCAT(name) FROM emp GROUP BY post;#按照岗位分组，并查看组内成员名
    SELECT post,GROUP_CONCAT(name) as emp_members FROM emp GROUP BY post;

GROUP BY与聚合函数一起使用
    select post,count(id) as count from emp group by post;#按照岗位分组，并查看每个组有多少人

如果我们用unique的字段作为分组的依据，则每一条记录自成一组，这种分组没有意义
多条记录之间的某个字段值相同，该字段通常用来作为分组的依据

聚合函数

#强调：聚合函数聚合的是组的内容，若是没有分组，则默认一组

示例：
    SELECT COUNT(*) FROM emp;
    SELECT COUNT(*) FROM empe WHERE depart_id=1;
    SELECT MAX(salary) FROM emp;
    SELECT MIN(salary) FROM emp;
    SELECT AVG(salary) FROM emp;
    SELECT SUM(salary) FROM emp;
    SELECT SUM(salary) FROM emp WHERE depart_id=3;

1. 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(name) from emp group by post;

+--------------------+--------------------------------------+
| post               | group_concat(name)                   |
+--------------------+--------------------------------------+
| operation          | 张野,程咬金,程咬银,程咬铜,程咬铁     |
| sale               | 歪歪,丫丫,丁丁,星星,格格             |
| teacher            | egon,kevin,tank,owen,jerry,nick,sean |
| 张江第一帅形象代言 | jason                                |
+--------------------+--------------------------------------+

结果

2. 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from emp group by post;

+--------------------+-----------+
| post               | count(id) |
+--------------------+-----------+
| operation          |         5 |
| sale               |         5 |
| teacher            |         7 |
| 张江第一帅形象代言 |         1 |
+--------------------+-----------+

结果

3.查询公司内男员工和女员工的个数
select gender,count(id) from emp group by gender;

+--------+-----------+
| gender | count(id) |
+--------+-----------+
| male   |        10 |
| female |         8 |
+--------+-----------+

结果

4. 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from emp group by post;

+--------------------+---------------+
| post               | avg(salary)   |
+--------------------+---------------+
| operation          |  16800.026000 |
| sale               |   2600.294000 |
| teacher            | 151842.901429 |
| 张江第一帅形象代言 |   7300.330000 |
+--------------------+---------------+

结果

5. 查询岗位名以及各岗位的最高薪资
select post,max(salary) from emp group by post;

+--------------------+-------------+
| post               | max(salary) |
+--------------------+-------------+
| operation          |    20000.00 |
| sale               |     4000.33 |
| teacher            |  1000000.31 |
| 张江第一帅形象代言 |     7300.33 |
+--------------------+-------------+

结果

6. 查询岗位名以及各岗位的最低薪资
select post,min(salary) from emp group by post;

+--------------------+-------------+
| post               | min(salary) |
+--------------------+-------------+
| operation          |    10000.13 |
| sale               |     1000.37 |
| teacher            |     2100.00 |
| 张江第一帅形象代言 |     7300.33 |

结果

7. 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select gender,avg(salary) from emp group by gender;

+--------+---------------+
| gender | avg(salary)   |
+--------+---------------+
| male   | 110920.077000 |
| female |   7250.183750 |
+--------+---------------+

结果

HAVING 分组后过滤

HAVING与WHERE不一样的地方在于!!!!!!

执行优先级从高到低：where > group by > having 
1. Where 发生在分组group by之前，因而Where中可以有任意字段，但是绝对不能使用聚合函数。
2. Having发生在分组group by之后，因而Having中可以使用分组的字段，无法直接取到其他字段,可以使用聚合函数

1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(name),count(id) from emp group by post having count(id) < 2;

+--------------------+--------------------+-----------+
| post               | group_concat(name) | count(id) |
+--------------------+--------------------+-----------+
| 张江第一帅形象代言 | jason              |         1 |
+--------------------+--------------------+-----------+

结果

2. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) > 10000;

+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+

结果

3. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from emp group by post having avg(salary) > 10000 and avg(salary) <20000;

+-----------+--------------+
| post      | avg(salary)  |
+-----------+--------------+
| operation | 16800.026000 |
+-----------+--------------+

结果

ORDER BY 排序

正序 ASC 默认

倒序 DESC

1. 查询所有员工信息，先按照age升序排序，如果age相同则按照hire_date降序排序
select * from emp ORDER BY age asc,hire_date desc;

+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
| id | name   | gender | age | hire_date  | post               | post_comment | salary     | office | depart_id |
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+
|  1 | jason  | male   |  18 | 2017-03-01 | 张江第一帅形象代言 | NULL         |    7300.33 |    401 |         1 |
| 12 | 星星   | female |  18 | 2016-05-13 | sale               | NULL         |    3000.29 |    402 |         2 |
| 17 | 程咬铜 | male   |  18 | 2015-04-11 | operation          | NULL         |   18000.00 |    403 |         3 |
| 18 | 程咬铁 | female |  18 | 2014-05-12 | operation          | NULL         |   17000.00 |    403 |         3 |
| 16 | 程咬银 | female |  18 | 2013-03-11 | operation          | NULL         |   19000.00 |    403 |         3 |
| 11 | 丁丁   | female |  18 | 2011-03-12 | sale               | NULL         |    1000.37 |    402 |         2 |
|  6 | jerry  | female |  18 | 2011-02-11 | teacher            | NULL         |    9000.00 |    401 |         1 |
| 15 | 程咬金 | male   |  18 | 1997-03-12 | operation          | NULL         |   20000.00 |    403 |         3 |
|  7 | nick   | male   |  18 | 1900-03-01 | teacher            | NULL         |   30000.00 |    401 |         1 |
| 13 | 格格   | female |  28 | 2017-01-27 | sale               | NULL         |    4000.33 |    402 |         2 |
| 14 | 张野   | male   |  28 | 2016-03-11 | operation          | NULL         |   10000.13 |    403 |         3 |
|  5 | owen   | male   |  28 | 2012-11-01 | teacher            | NULL         |    2100.00 |    401 |         1 |
| 10 | 丫丫   | female |  38 | 2010-11-01 | sale               | NULL         |    2000.35 |    402 |         2 |
|  9 | 歪歪   | female |  48 | 2015-03-11 | sale               | NULL         |    3000.13 |    402 |         2 |
|  8 | sean   | male   |  48 | 2010-11-11 | teacher            | NULL         |   10000.00 |    401 |         1 |
|  4 | tank   | male   |  73 | 2014-07-01 | teacher            | NULL         |    3500.00 |    401 |         1 |
|  2 | egon   | male   |  78 | 2015-03-02 | teacher            | NULL         | 1000000.31 |    401 |         1 |
|  3 | kevin  | male   |  81 | 2013-03-05 | teacher            | NULL         |    8300.00 |    401 |         1 |
+----+--------+--------+-----+------------+--------------------+--------------+------------+--------+-----------+

结果

2. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资升序排列
select post,avg(salary) from emp group by post having avg(salary) > 10000 order by avg(salary) asc;

+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| operation |  16800.026000 |
| teacher   | 151842.901429 |
+-----------+---------------+

结果

3. 查询各岗位平均薪资大于10000的岗位名、平均工资,结果按平均薪资降序排列
select post,avg(salary) from emp group by post having avg(salary) > 10000 order by avg(salary) desc;

+-----------+---------------+
| post      | avg(salary)   |
+-----------+---------------+
| teacher   | 151842.901429 |
| operation |  16800.026000 |
+-----------+---------------+

结果

LIMIT 限制查询记录的条数

示例：
    SELECT * FROM emp ORDER BY salary DESC 
        LIMIT 3;                    #默认初始位置为0 
    
    SELECT * FROM emp ORDER BY salary DESC
        LIMIT 0,5; #从第0开始，即先查询出第一条，然后包含这一条在内往后查5条

    SELECT * FROM emp ORDER BY salary DESC
        LIMIT 5,5; #从第5开始，即先查询出第6条，然后包含这一条在内往后查5条

单表查询关键字执行顺序

执行顺序:
from    # 确定是那张表
where   # 根据条件,筛选数据
group by    # 分组
having　　# 对分组后的表筛选 分组之后只能拿到分组的依据和聚合函数的结果
select  # 拿出筛选出来的数据中的某些字段
distinct　　# 去重

多表查询

create table dep(
id int,
name varchar(20) 
);

create table emp(
id int primary key auto_increment,
name varchar(20),
sex enum('male','female') not null default 'male',
age int,
dep_id int
);

#插入数据
insert into dep values
(200,'技术'),
(201,'人力资源'),
(202,'销售'),
(203,'运营');

insert into emp(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)
;

#查看表结构和数据
mysql> desc dep;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+

mysql> desc emp;
+--------+-----------------------+------+-----+---------+----------------+
| 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 | |
+--------+-----------------------+------+-----+---------+----------------+

mysql> select * from dep;
+------+--------------+
| id | name |
+------+--------------+
| 200 | 技术 |
| 201 | 人力资源 |
| 202 | 销售 |
| 203 | 运营 |
+------+--------------+

mysql> select * from emp;
+----+------------+--------+------+--------+
| 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 |
+----+------------+--------+------+--------+

案例表信息

笛卡尔积

左表的一条记录对应右表的每条记录,其中一条记录是正确的对应关系

select * from emp,dep;

+----+------------+--------+------+--------+------+--------------+
| 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 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  201 | 人力资源     |
|  5 | liwenzhou  | male   |   18 |    200 |  202 | 销售         |
|  5 | liwenzhou  | male   |   18 |    200 |  203 | 运营         |
|  6 | jingliyang | female |   18 |    204 |  200 | 技术         |
|  6 | jingliyang | female |   18 |    204 |  201 | 人力资源     |
|  6 | jingliyang | female |   18 |    204 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 |  203 | 运营         |
+----+------------+--------+------+--------+------+--------------+

结果

可以利用where条件选出正确的对应关系,正常不用where做链表的活

select * from emp,dep where emp.dep_id = dep.id;

+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

结果

内连接 inner join ... on ...

只取两张表有对应关系的记录,链接成一张虚拟表

select * from emp inner join dep on emp.dep_id = dep.id;

+----+-----------+--------+------+--------+------+--------------+
| id | name      | sex    | age  | dep_id | id   | name         |
+----+-----------+--------+------+--------+------+--------------+
|  1 | egon      | male   |   18 |    200 |  200 | 技术         |
|  2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
+----+-----------+--------+------+--------+------+--------------+

结果

查询"技术"部门员工的信息
select * from emp inner join dep on emp.dep_id = dep.id 
    where dep.name = "技术";

+----+-----------+------+------+--------+------+--------+
| id | name      | sex  | age  | dep_id | id   | name   |
+----+-----------+------+------+--------+------+--------+
|  1 | egon      | male |   18 |    200 |  200 | 技术   |
|  5 | liwenzhou | male |   18 |    200 |  200 | 技术   |
+----+-----------+------+------+--------+------+--------+

结果

左连接 left join ... on ...

内连接基础上,保留左表没有对应关系的记录

select * from emp left join dep on emp.dep_id = dep.id;

+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  6 | jingliyang | female |   18 |    204 | NULL | NULL         |
+----+------------+--------+------+--------+------+--------------+

结果

右链接 right join ... on ...

内连接基础上,保留右表没有对应关系的记录

select * from emp right join dep on emp.dep_id = dep.id;

+------+-----------+--------+------+--------+------+--------------+
| id   | name      | sex    | age  | dep_id | id   | name         |
+------+-----------+--------+------+--------+------+--------------+
|    1 | egon      | male   |   18 |    200 |  200 | 技术         |
|    2 | alex      | female |   48 |    201 |  201 | 人力资源     |
|    3 | wupeiqi   | male   |   38 |    201 |  201 | 人力资源     |
|    4 | yuanhao   | female |   28 |    202 |  202 | 销售         |
|    5 | liwenzhou | male   |   18 |    200 |  200 | 技术         |
| NULL | NULL      | NULL   | NULL |   NULL |  203 | 运营         |
+------+-----------+--------+------+--------+------+--------------+

结果

全连接 union

内连接基础上,保留左右两张表没有对应关系的记录

 select * from emp inner join dep on emp.dep_id = dep.id
     union
 select * from emp left join dep on emp.dep_id = dep.id;

+----+------------+--------+------+--------+------+--------------+
| id | name       | sex    | age  | dep_id | id   | name         |
+----+------------+--------+------+--------+------+--------------+
|  1 | egon       | male   |   18 |    200 |  200 | 技术         |
|  2 | alex       | female |   48 |    201 |  201 | 人力资源     |
|  3 | wupeiqi    | male   |   38 |    201 |  201 | 人力资源     |
|  4 | yuanhao    | female |   28 |    202 |  202 | 销售         |
|  5 | liwenzhou  | male   |   18 |    200 |  200 | 技术         |
|  6 | jingliyang | female |   18 |    204 | NULL | NULL         |
+----+------------+--------+------+--------+------+--------------+

结果

子查询

1：子查询是将一个查询语句嵌套在另一个查询语句中。

2：内层查询语句的查询结果，可以为外层查询语句提供查询条件。

3：子查询中可以包含：IN、NOT IN、ANY、ALL、EXISTS 和 NOT EXISTS等关键字

4：还可以包含比较运算符：= 、 !=、> 、<等

查询技术部门员工的姓名:
1.链表查询：
select emp.name 
    from emp inner join dep on emp.dep_id = dep.id 
    where dep.name = "技术";
2.子查询: 
select name from emp 
    where dep_id = (
        select id from dep where name = "技术"
    );

+-----------+
| name      |
+-----------+
| egon      |
| liwenzhou |
+-----------+

结果

查询平均年龄在25岁以上的部门名称
1.链表查询:
select dep.name from 
    emp inner join dep on emp.dep_id = dep.id 
    group by dep.name 
    having avg(age) > 25;
2.子查询:
select name from dep 
    where id in  (
        select dep_id from emp group by dep_id having avg(age) > 25
    );

+--------------+
| name         |
+--------------+
| 人力资源     |
| 销售         |
+--------------+

结果

带EXISTS关键字的子查询

EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
而是返回一个真假值。True或False
当返回True时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询

#dep表中存在dept_id=203，Ture
select * from emp
         where exists
             (select id from dep 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 |
+----+------------+--------+------+--------+

结果

#dep表中存在dept_id=205，False
select * from emp
        where exists
             (select id from dep where id=204);

Empty set (0.00 sec)

结果

PyMySQL

pymysql是一个socket客户端,使用它需要先建立连接

基本使用

1.连接数据库

import pymysql
conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '',
    database = 'db1',
    charset = 'utf8',
)

2.获取游标

cursor = conn.cursor()

cursor = conn.cursor(pymysql.cursors.DictCursor)  
# 获取游标时,通常用此方法获取,这样查询返回的数据是字典的形式,
　　1. key是字段,value是字段值
　　2. 每个字典就是一条记录

游标就是cmd里的:

3.向游标提交sql语句

rows = cursor.execute('show tables;')
print(rows)  # 2  得到的结果是响应的行数
补充: 当输入的sql语句的查询没有结果时,影响行数为0

我们在cmd里执行sql语句得到的是数据结果和行数,在python中执行的sql语句打印出来的只有行数,实际上数据结果也拿到了,此时在内存中,可以继续通过游标获取

4.获取数据

获取一条 : fetchone()
获取所有 : fetchall()
获取指定数量: fetchmany(size)
rows = cursor.execute('select * from dep')
print(rows)  # 4 
print(cursor.fetchone())  # {'id': 200, 'name': '技术'}
print(cursor.fetchmany(2))  # [{'id': 200, 'name': '技术'}, {'id': 201, 'name': '人力资源'}]
print(cursor.fetchall())  # [{'id':200, 'name': '技术'},{'id': 201, 'name': '人力资源'}, {'id': 202, 'name': '销售'}, {'id': 203, 'name': '运营'}]

5.关闭游标关闭socket连接通道

cursor.close()
conn.close()

游标控制

absolute 绝对移动

cursor.scroll(2, 'absolute') # 相对于起始位置移动2位
print(cursor.fetchone())  # {'id': 202, 'name': '销售'}

relative 相对移动

cursor.scroll(3,'relative')  # 相对当前光标所在位置移动3位
print(cursor.fetchone())  # {'id': 203, 'name': '运营'}

sql注入问题

# 创建user表
create table user(
    id int primary key auto_increment,
    name char(16) not null unique,
    password char(16) not null
);
# 插入数据
insert into user(id, name, password) values(1, 'xiongda', 123), (2, 'xionger', 456)

# select * from user;
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | xiongda | 123      |
|  2 | xionger | 456      |
+----+---------+----------+

准备案例表

sql注入是利用sql的注释,or 等一些特殊字符,来改变sql的执行逻辑,从而使sql语句正常执行,得到数据.

cursor = conn.cursor(pymysql.cursors.DictCursor)

username = input(">>>:").strip()
password = input(">>>:").strip()

sql = "select * from user where name = '%s' and password = '%s'" %(username, password)

rows = cursor.execute(sql)
if rows:
    print('登录成功')
else:
    print('登录失败')

cursor.close()
conn.close()

>>>:xiongda' -- 1564as3d13a1sd 
>>>:
登录成功
>>>:xiongda' or 1=1 -- 1as32d13a2s1d
>>>:
登录成功

解决方法

原来我是自己对sql语句进行字符串拼接,导致sql注入问题

改写为利用execute帮助我们字符串拼接,pymysql帮我们处理了特殊符号

sql = "select * from user where name = %s and password = %s"
rows = cursor.execute(sql, [username, password])

增、删、改：conn.commit()

增删改对于数据库来说都是敏感操作,提交完sql语句后都要加上conn.commit(),才能真正的对数据增删改

sql = "insert into user (name, password) values(%s, %s)"

rows = cursor.execute(sql, ['xxx', 789])
rows = cursor.executemany(sql, [('yyy',111), ('www', 321)])  # 一次插入多条数据
print(cursor.lastrowid)  # 当前记录是第几条
conn.commit()

也可以在连接数据库时配置autocommit = True,就不需要手动写conn.commit()

conn = pymysql.connect(
    host = '127.0.0.1',
    port = 3306,
    user = 'root',
    password = '',
    database = 'db1',
    charset = 'utf8',
    autocommit = True  # 这个参数配置后,增删改操作都不会需要手动加conn.commit了
)

视图

视图就是一张虚拟表,虚拟表都是通过查询得到的

使用视图我们可以把查询过程中的临时表摘出来，用视图去实现，这样以后再想操作该临时表的数据时就无需重写复杂的sql了，直接去视图中查找即可，但视图有明显地效率问题，并且视图是存放在数据库中的，如果我们程序中使用的sql过分依赖数据库中的视图，即强耦合，那就意味着扩展sql极为不便，因此并不推荐使用

create table course(
  cid int primary key auto_increment,
  cname char(20),
  teacher_id int
);
create table teacher(
  tid int primary key auto_increment,
  tname char(20)
);

insert into course(cname, teacher_id) values
    ('生物',1),
    ('物理',2),
    ('体育',3),
    ('美术',2);

insert into teacher(tname) values 
  ('张磊老师'),
  ('李平老师'),
  ('刘海燕老师'),
  ('朱云海老师'),
  ('李杰老师');

 select * from course;
+-----+--------+------------+
| cid | cname  | teacher_id |
+-----+--------+------------+
|   1 | 生物   |          1 |
|   2 | 物理   |          2 |
|   3 | 体育   |          3 |
|   4 | 美术   |          2 |
+-----+--------+------------+

select * from teacher;
+-----+-----------------+
| tid | tname           |
+-----+-----------------+
|   1 | 张磊老师        |
|   2 | 李平老师        |
|   3 | 刘海燕老师      |
|   4 | 朱云海老师      |
|   5 | 李杰老师        |
+-----+-----------------+

案例表

创建视图

创建一张老师与课程内连接的虚拟表
create view course_teacher as
    select * from course inner join teacher on course.teacher_id = teacher.tid;

select * from course_teacher;
+-----+--------+------------+-----+-----------------+
| cid | cname  | teacher_id | tid | tname           |
+-----+--------+------------+-----+-----------------+
|   1 | 生物   |          1 |   1 | 张磊老师        |
|   2 | 物理   |          2 |   2 | 李平老师        |
|   3 | 体育   |          3 |   3 | 刘海燕老师      |
|   4 | 美术   |          2 |   2 | 李平老师        |
+-----+--------+------------+-----+-----------------+

结果

注意:

1.在硬盘中虚拟表只有 .frm文件(表结构),没有 .idb文件(数据)

2.使用视图以后就无需每次都重写子查询的sql，但是这么效率并不高，还不如我们写子查询的效率高

3.视图通常用来辅助查询,不要修改视图中的数据!!!

修改视图

ALTER VIEW 视图名称 AS SQL语句

删除视图

DROP VIEW 视图名称

存储过程

存储过程包含了一系列可执行的sql语句,存储过程放于MySQL中,通过调用它的名字可以执行其内部的一堆sql(像在对象里封装了一堆方法,直接用对象调用方法即可)

优点

1.用于替代程序写的sql语句,实现程序与sql解耦

2.给予网络传输,传别名的数据量小,而直接传sql数据量大

缺点

1.程序员扩展功能不方便

补充

模型一:
应用程序:只需要开发应用程序的逻辑
mysql:编写好存储过程,以供应用程序调用
优点:开发效率执行效率都高
缺点:考虑到人为因素,跨部门沟通等问题,导致扩展性差

模型二:
应用程序:既要开发应用的逻辑,也要编写原生的sql
优点: 公司成本低
缺点: 开发效率执行效率不如方式一,编写原生sql过于复杂,还要考虑优化问题

模型三:
应用程序:只需要开发应用程序的逻辑,不需要编写原生sql,使用ORM框架

三种开发模式

创建存储过程 procedure

create procedure 名(参数)
begin
    sql语句
end

使用存储过程

无参数

delimiter $$
create procedure p1()
begin
    select * from user;
end $$

delimiter ;

#在mysql中调用
call p1()

 call p1();
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  1 | xiongda | 123      |
|  2 | xionger | 456      |
|  3 | xxx     | 789      |
+----+---------+----------+

结果

有参数

in 用于传入参数使用

out 用于返回值用

inout 既可以传入也可以当做返回值

delimiter $$
create procedure p2(
    in n int,
    in m int,
    out res int
)
begin
    select * from user where id > n and id < m;
    set res=0;
end $$

delimiter ;

#在mysql中调用
set @res=0; #0代表假（执行失败），1代表真（执行成功）
call p2(1, 3, @res);  # 注意:返回值只能接收变量,所以要先定义变量再传入
select @res;

 set @res=0; 

call p2(1, 3, @res);
+----+---------+----------+
| id | name    | password |
+----+---------+----------+
|  2 | xionger | 456      |
+----+---------+----------+

select @res;
+------+
| @res |
+------+
|    0 |
+------+

结果

python中调用存储过程 callproc

python中基于pymsql调用存储过程

游标.callproc(存储过程名, (参数们))

cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.callproc('p2',(1, 3, 1))  # @_p2_0=1,@_p2_1=3,@_p2_2=1,
print(cursor.fetchall())  # [{'id': 2, 'name': 'xionger', 'password': '456'}]

cursor.execute('select @_p2_2;')
print(cursor.fetchall())  # [{'@_p2_2': 0}]

查看存储过程

show create procedure 名;

删除存储过程

drop function 名;

索引

mysql中存在一种专门的数据结构,key,又叫索引,通过该数据结构可以不断的缩小查询范围从而减少IO次数,达到加速查询效率目的
- index key : 只加速查询效果,没有约束功能
- unique key : 不仅有加速查询效果,还附加了约束功能
- primary key : 不仅有加速查询效果,还附加了约束功能,并且innodb存储引擎会按照主键字段的值来组织表中所有的数据,所以一张innodb表中有且只有一个主键,innodb的索引跟数据都放在idb表数据文件中

索引的影响

当表中有数据再建索引,建索引的速度会慢,因为要扫描数据进行'归类'

当存在索引再插入数据,插数据的速度会慢,因为之前的索引结构需要重新编排.

所以索引可以加速查询,但会影响写入数据速度

聚集索引(primary key)

聚集索引其实指的就是表的主键

特点:叶子结点放的一条条完整的记录

辅助索引(unique,index)

只在辅助索引的叶子节点就已经找到了我们想要的数据

特点：叶子结点存放的是辅助索引字段对应的那条记录的主键的值(比如:按照name字段创建索引，那么叶子节点存放的是:{name对应的值:name所在的那条记录的主键值})

select name from user where name='xionger';

上述语句叫覆盖索引:只在辅助索引的叶子节点中就已经找到了所有我们想要的数据

select age from user where name='xionger';

上述语句叫非覆盖索引,虽然查询的时候命中了索引字段name，但是要查的是age字段，所以还需要利用主键才去查找

相关阅读:
查看文件方法、vim末行操作
 目录结构、文件管理命令
 计算机快捷键、常用命令、别名、
Redis 使用与优化
 Redis-Sentinel
Redis主从复制
 Redis持久化
 API的使用
 Redis安装和配置
 集群搭建（脑裂）
原文地址：https://www.cnblogs.com/waller/p/12039081.html

MySQL

MySQL总结

基本操作

库

表

记录

表相关操作

存储引擎

语法

创建表

修改表

复制表

类型

数值类型

字符串类型

集合与枚举类型

约束条件

not null / default

unique

primary key

auto_increment

表关系

多对一:

多对多:

表查询

单表查询

DISTINCT 去重查询

四则运行查询

CONCAT 定义显式

WHERE 约束

GROUP BY 分组查询

聚合函数

HAVING 分组后过滤

ORDER BY 排序

LIMIT 限制查询记录的条数

单表查询关键字执行顺序

多表查询

笛卡尔积

内连接 inner join ... on ...

左连接 left join ... on ...

右链接 right join ... on ...

全连接 union

子查询

PyMySQL

基本使用

1.连接数据库

2.获取游标

3.向游标提交sql语句

4.获取数据

5.关闭游标关闭socket连接通道

游标控制

sql注入问题

解决方法

增、删、改：conn.commit()

视图

创建视图

修改视图

删除视图

存储过程

优点

缺点

补充

创建存储过程 procedure

使用存储过程

无参数

有参数

python中调用存储过程 callproc

查看存储过程

删除存储过程

索引

索引的影响

聚集索引(primary key)

辅助索引(unique,index)