目录
一、常用查询
（增、删、改、查）
对 MySQL 数据库的查询，除了基本的查询外，有时候需要对查询的结果集进行处理。例如只取 10 条数据、对查询结果进行排序或分组等等
环境准备：
mysql> select * from test;
+------+----------+-------+
| id | name | level |
+------+----------+-------+
| 6 | heiba | 52.00 |
| 8 | jerry | 43.00 |
| 2 | lisi | 35.00 |
| 5 | tianqi | 43.00 |
| 7 | tom | 63.00 |
| 3 | wangwu | 55.00 |
| 1 | zhangsan | 46.00 |
| 4 | zhaoliu | 63.00 |
+------+----------+-------+
8 rows in set (0.00 sec)1.1 关键词排序
（1）语法
select 属性，属性1…… from 表名 order by 属性，属性1
ASC|DESC;
- ASC 是按照升序进行排序的，是默认的排序方式，即 ASC 可以省略。SELECT 语句中如果没有指定具体的排序方式，则默认按 ASC方式进行排序
- DESC 是按降序方式进行排列。当然 order by 前面也可以使用 where 子句对查询结果进一步过滤
distinct：查询不重复记录
语法：
select distinct 属性 from 表名；
（2）关键词排序
降序
mysql> select * from test order by level desc;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 4 | zhaoliu | 63 |
| 7 | tom | 63 |
| 3 | wangwu | 55 |
| 6 | heiba | 52 |
| 1 | zhangsan | 46 |
| 5 | tianqi | 43 |
| 8 | jerry | 43 |
| 2 | lisi | 35 |
+----+----------+-------+
8 rows in set (0.00 sec)条件降序（单字段）
mysql> select * from test where level >= 45 order by level desc;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 4 | zhaoliu | 63 |
| 7 | tom | 63 |
| 3 | wangwu | 55 |
| 6 | heiba | 52 |
| 1 | zhangsan | 46 |
+----+----------+-------+
5 rows in set (0.00 sec)（多字段排序）
mysql> select * from test where level>=45 order by level desc,id desc;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 7 | tom | 63 |
| 4 | zhaoliu | 63 |
| 3 | wangwu | 55 |
| 6 | heiba | 52 |
| 1 | zhangsan | 46 |
+----+----------+-------+
5 rows in set (0.00 sec)（多字段排序）：level降序排序，相同情况下，id降序排序
mysql> select * from test order by level desc,id desc;
+------+----------+-------+
| id | name | level |
+------+----------+-------+
| 7 | tom | 63.00 |
| 4 | zhaoliu | 63.00 |
| 3 | wangwu | 55.00 |
| 6 | heiba | 52.00 |
| 1 | zhangsan | 46.00 |
| 8 | jerry | 43.00 |
| 5 | tianqi | 43.00 |
| 2 | lisi | 35.00 |
+------+----------+-------+
8 rows in set (0.00 sec)区间判断及查询不重复记录：and/or 且/或
用or再加排序
mysql> select * from test where level >=60 or level <= 40 order by level desc,id;
+------+---------+-------+
| id | name | level |
+------+---------+-------+
| 4 | zhaoliu | 63.00 |
| 7 | tom | 63.00 |
| 2 | lisi | 35.00 |
+------+---------+-------+
3 rows in set (0.00 sec)查询不重复记录
mysql> select distinct level from test;
+-------+
| level |
+-------+
| 52.00 |
| 43.00 |
| 35.00 |
| 63.00 |
| 55.00 |
| 46.00 |
+-------+
6 rows in set (0.00 sec)
1.2 对结果分组
简单理解：
count（属性 ）+ 属性：这个属性代表可重复值（后面属性重复次数体现在这），根据后面所跟的属性进行对应，代表一个组
mysql> select count(name) 数量,level from test where level>=40 group by level;
+--------+-------+
| 数量 | level |
+--------+-------+
| 2 | 43 |
| 1 | 46 |
| 1 | 52 |
| 1 | 55 |
| 2 | 63 |
+--------+-------+
5 rows in set (0.00 sec)综合：
mysql> select count(name),level from test where level >= 40 group by level order by count(name) desc,level desc;
+-------------+-------+
| count(name) | level |
+-------------+-------+
| 2 | 63.00 |
| 2 | 43.00 |
| 1 | 55.00 |
| 1 | 52.00 |
| 1 | 46.00 |
+-------------+-------+
5 rows in set (0.00 sec)1.3 限制结果条目
mysql> select column1,column2,... from table_name limit [offset,] number;LIMIT限制结果条数
mysql> select * from test limit 3;
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 1 | zhangsan | 46 |
| 2 | lisi | 35 |
| 3 | wangwu | 55 |
+----+----------+-------+
3 rows in set (0.00 sec)不从第一条开始取值
mysql> select * from test limit 2,3; //从第三条记录开始显示之后的三条数据
+----+---------+-------+
| id | name | level |
+----+---------+-------+
| 3 | wangwu | 55 |
| 4 | zhaoliu | 63 |
| 5 | tianqi | 43 |
+----+---------+-------+
3 rows in set (0.00 sec)综合化
mysql> select * from info where score >60 order by id limit 2,2;
+------+----------+-------+----------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+----------+--------+
| 4 | tianqi | 99.00 | hangzhou | 5 |
| 5 | jiaoshou | 98.00 | laowo | 3 |
+------+----------+-------+----------+--------+
2 rows in set (0.00 sec)1.4 设置别名
mysql> select column_name AS alias_name from table_name; //列的别名
mysql> select column_name(s) from table_name AS alias_name; //表的别名as用法
mysql> select name AS 姓名 from test;
+----------+
| 姓名 |
+----------+
| zhangsan |
| lisi |
| wangwu |
| zhaoliu |
| tianqi |
| heiba |
| tom |
| jerry |
+----------+
8 rows in set (0.00 sec)mysql> select count(*)总数量 from info;
+-----------+
| 总数量 |
+-----------+
| 7 |
+-----------+
1 row in set (0.00 sec)as作为连接语句
mysql> create table tmp as select * from test;注意： 克隆表但不包含主键和唯一键，需要自己设置
1.5 通配符
%：百分号表示零个、一个或多个字符
_：下划线表示单个字符
mysql> select * from test where name like 'zh%';
+----+----------+-------+
| id | name | level |
+----+----------+-------+
| 1 | zhangsan | 46 |
| 4 | zhaoliu | 63 |
+----+----------+-------+
2 rows in set (0.01 sec)mysql> select * from test where name like 'to_';
+----+------+-------+
| id | name | level |
+----+------+-------+
| 7 | tom | 63 |
+----+------+-------+
1 row in set (0.00 sec)mysql> select * from info where name like '_i%';
+------+----------+-------+----------+--------+
| id | name | score | address | hobbid |
+------+----------+-------+----------+--------+
| 5 | jiaoshou | 98.00 | laowo | 3 |
| 7 | lilei | 11.00 | nanjing | 5 |
| 3 | lisi | 60.00 | shanghai | 4 |
| 1 | liuyi | 80.00 | beijing | 2 |
| 4 | tianqi | 99.00 | hangzhou | 5 |
+------+----------+-------+----------+--------+
5 rows in set (0.00 sec)1.6 子查询
（1）in/not in
简单理解
子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句
子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤
注：子语句可以与主语句所查询的表相同，也可以是不同表
表达式 in 子查询
举例：
select name,score from info where id in (select name from info where score >80);
主语句：select name,score from info where id
子语句(集合)： select id from info where score >80注意：
子语句中的sql语句是为了，最后过滤出一个结果集，用于主语句的判断条件
in: 将主表和子表关联/连接的语法
mysql> select name,level from test where id in (select id from test where level>=45);
+----------+-------+
| name | level |
+----------+-------+
| zhangsan | 46 |
| wangwu | 55 |
| zhaoliu | 63 |
| heiba | 52 |
| tom | 63 |
+----------+-------+
5 rows in set (0.01 sec)
mysql> insert into tmp set select * from player where id in (select id from player);
mysql> update tmp set level=level-7 where id in (select id from test);
mysql> delete from tmp where id in (select a.id from (select id from tmp where level=47) a);
mysql> select id,name,level from tmp where id = (select id from tmp where name='shirley');
mysql> select count(*) as number from tmp where exists (select id from tmp where name='shirley');子查询不仅可以在 SELECT 语句中使用，在 INERT、UPDATE、DELETE 中也同样适用。在嵌套的时候，子查询内部还可以再次嵌套新的子查询，也就是说可以多层嵌套
举例：
mysql> update info set score=50 where id in (select * from ky30 where id=2);
mysql> delete from info where id in (select id where score>80);补充：表达式 not in 子查询：作用和in相反
（2）exists
exists 这个关键字在子查询时，主要用于判断子查询的结果集是否为空。如果不为空， 则返回 TRUE；反之，则返回 FALSE
查询如果存在分数等于80的记录则计算info的字段数
mysql> select count(*) from info where exists(select id from info where score=80);
学校里面 （人员信息统计，只有当所有人全部签到之后，在人员信息统计表录入完成侯，我才需要进行统计）
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)（3）as别名
需求：从info表中的id和name字段的内容做为"内容" 输出id的部分
mysql> select id from (select id,name from info);
ERROR 1248 (42000): Every derived table must have its own alias
#此时会报错，原因为：
select * from 表名 此为标准格式，而以上的查询语句，"表名"的位置其实是一个完整结果集，mysql并不能直接识别，而此时给与结果集设置一个别名，以”select a.id from a“的方式查询将此结果集视为一张"表"，就可以正常查询数据了，如下：
select a.id from (select id,name from info) a;
相当于
select info.id,name from info;
select 表.字段，字段 from 表；二、MySQL视图
2.1 简单理解
视图：优化操作+安全方案
数据库中的虚拟表，这张虚拟表中不包含真实数据，只是做了真实数据的映射
视图可以理解为镜花水月/倒影，动态保存结果集（数据）
作用范围：
select * from info; #展示的部分是info表
select * from view_name; #展示的一张或多张表
功能：
简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性
本质而言视图是一种select(结果集的呈现)PS:视图适合于多表连接浏览时使用!不适合增、删、改
而存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率!
视图和表的区别和联系
区别：
- ①视图是已经编译好的sql语句。而表不是
- ②视图没有实际的物理记录。而表有
- show table status\G
- ③表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
- ④视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
- ⑤表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
- ⑥视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）
视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。
单表
#创建视图(单表)
mysql> create view v_score as select * from info where score>=80;
Query OK, 0 rows affected (0.01 sec)
#查看表状态
show table status\G
#查看视图
mysql> select * from v_score;
+------+-----------+--------+----------+--------+
| id | name | score | address | hobbid |
+------+-----------+--------+----------+--------+
| 1 | liuyi | 100.00 | beijing | 2 |
| 4 | tianqi | 100.00 | hangzhou | 5 |
| 5 | jiaoshou | 100.00 | laowo | 3 |
| 6 | hanmeimei | 100.00 | nanjing | 3 |
| 7 | lilei | 100.00 | nanjing | 5 |
+------+-----------+--------+----------+--------+
#查看视图与源表结构
mysql> desc v_score;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| hobbid | int(5) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
mysql> desc info;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(10) | NO | PRI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(20) | YES | | NULL | |
| hobbid | int(5) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)多表
#多表创建视图
创建test01表
create table test01 (id int,name varchar(10),age char(10));
insert into test01 values(1,'zhangsan',20);
insert into test01 values(2,'lisi',30);
insert into test01 values(3,'wangwu',29);
需求：需要创建一个视图，需要输出id、学生姓名、分数以及年龄
mysql> create view v_info(id,name,score,age) as select info.id,info.name,info.score,test01.age from info,test01 where info.name=test01.name;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_info;
+------+--------+-------+------+
| id | name | score | age |
+------+--------+-------+------+
| 3 | lisi | 60.00 | 30 |
| 2 | wangwu | 90.00 | 29 |
+------+--------+-------+------+
2 rows in set (0.00 sec)2.2 null值
- 表示缺失的值
- 与数字0或者空白是不同的
- 使用 IS NULL 或 IS NOT NULL 进行判断
- NULL值和空值的区别
- 空值长度为0，不占空间；NULL值的长度为NULL，占用空间
- IS NULL 无法判断空值
- 空值使用 “=” 或 “<>” 来处理（“<>”不等于）
- COUNT()计算，NULL会忽略，空值加入计算
mysql> desc info;
+---------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | UNI | NULL | |
| score | decimal(5,2) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
| hobbid | int(3) | YES | | NULL | |
+---------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
插入一条记录，分数字段输入null，显示出来就是null
#验证：
alter table info add column addr varchar(50);
update info set addr='nj' where score >=70;
#统计数量：检测null是否会加入统计中
select count(addr) from info;
#将info表中其中一条数据修改为空值''
update info set addr='' where name='wangwu';
#统计数量，检测空值是不会被添加到统计中
select count(addr) from info;
#查询null值
mysql> select * from info where addr is NULL;
+------+-----------+-------+---------+--------+------+
| id | name | score | address | hobbid | addr |
+------+-----------+-------+---------+--------+------+
| 6 | hanmeimei | 10.00 | nanjing | 3 | NULL |
| 7 | lilei | 11.00 | nanjing | 5 | NULL |
+------+-----------+-------+---------+--------+------+
2 rows in set (0.00 sec)
#查询不为空的值
ysql> select * from info where addr is not null;
+------+----------+-------+------------+--------+------+
| id | name | score | address | hobbid | addr |
+------+----------+-------+------------+--------+------+
| 1 | liuyi | 80.00 | beijing | 2 | nj |
| 2 | wangwu | 90.00 | shengzheng | 2 | nj |
| 3 | lisi | 60.00 | shanghai | 4 | |
| 4 | tianqi | 99.00 | hangzhou | 5 | nj |
| 5 | jiaoshou | 98.00 | laowo | 3 | nj |
| 1 | xiaoer | 80.00 | hangzhou | 3 | nj |
+------+----------+-------+------------+--------+------+
6 rows in set (0.00 sec)三、连接查询
- MySQL 的连接查询，通常都是将来自两个或多个表的记录行结合起来，基于这些表之间的共同字段，进行数据的拼接。
- 首先，要确定一个主表作为结果集，然后将其他表的行有选择 性的连接到选定的主表结果集上。
- 使用较多的连接查询包括：内连接、左连接和右连接
3.1 内连接
- MySQL 中的内连接就是两张或多 张表中同时符合某种条件的数据记录的组合。
- 通常在 FROM 子句中使用关键字 INNER JOIN 来连接多张表，并使用 ON 子句设置连接条件，内连接是系统默认的表连接，所以在 FROM 子句后可以省略 INNER 关键字，只使用 关键字 JOIN。
- 同时有多个表时，也可以连续使用 INNER JOIN 来实现多表的内连接，不过为了更好的性能，建议最好不要超过三个表
语法：
select 表名.属性 表名.属性1 from 表名 inner join 表名2 on 表名.属性=表名1.属性;
示例：
select info.id,info.name from info inner join infos on info.name=infos.name;
create table infos(name varchar(40),score decimal(4,2),address varchar(40));
insert into infos values('wangwu',80,'beijing'),('zhangsan',99,'shanghai'),('lisi',100,'nanjing');
mysql> select * from infos;
+----------+-------+----------+
| name | score | address |
+----------+-------+----------+
| wangwu | 80.00 | beijing |
| zhangsan | 99.00 | shanghai |
| lisi | 99.99 | nanjing |
+----------+-------+----------+
mysql> select info.id,info.name from info inner join infos on info.name=infos.name;
+------+--------+
| id | name |
+------+--------+
| 2 | wangwu |
| 3 | lisi |
+------+--------+
2 rows in set (0.00 sec)注：
- 内连查询：通过inner join 的方式将两张表指定的相同字段的记录行输出出来
- 内连查询：面试，直接了当的说 用inner join 就可以
3.2 左连接
- 左连接也可以被称为左外连接，在 FROM 子句中使用 left join 或者 left outer join 关键字来表示。
- 左连接以左侧表为基础表，接收左表的所有行，并用这些行与右侧参考表中的记录进行匹配，也就是说匹配左表中的所有行以及右表中符合条件的行
mysql> select * from info left join infos on info.name=infos.name;
+------+-----------+--------+------------+--------+------+--------+-------+---------+
| id | name | score | address | hobbid | addr | name | score | address |
+------+-----------+--------+------------+--------+------+--------+-------+---------+
| 2 | wangwu | 50.00 | shengzheng | 2 | nj | wangwu | 80.00 | beijing |
| 3 | lisi | 50.00 | shanghai | 4 | nj | lisi | 99.99 | nanjing |
| 1 | liuyi | 60.00 | beijing | 2 | nj | NULL | NULL | NULL |
| 4 | tianqi | 100.00 | hangzhou | 5 | | NULL | NULL | NULL |
| 5 | jiaoshou | 100.00 | laowo | 3 | NULL | NULL | NULL | NULL |
| 6 | hanmeimei | 100.00 | nanjing | 3 | NULL | NULL | NULL | NULL |
| 7 | lilei | 100.00 | nanjing | 5 | NULL | NULL | NULL | NULL |
| 7 | lilei | 100.00 | nanjing | 5 | NULL | NULL | NULL | NULL |
| 8 | abn | 81.00 | bj | 1 | nj | NULL | NULL | NULL |
+------+-----------+--------+------------+--------+------+--------+-------+---------+
左连接中左表的记录将会全部表示出来，而右表只会显示符合搜索条件的记录，右表记录不足的地方均为 NULL。3.3 右连接
- 右连接也被称为右外连接，在 FROM 子句中使用 right join 或者 RIGHT OUTER JOIN 关键字来表示。
- 右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配
mysql> select * from info right join infos on info.name=infos.name;
+------+--------+-------+------------+--------+------+----------+-------+----------+
| id | name | score | address | hobbid | addr | name | score | address |
+------+--------+-------+------------+--------+------+----------+-------+----------+
| 2 | wangwu | 50.00 | shengzheng | 2 | nj | wangwu | 80.00 | beijing |
| NULL | NULL | NULL | NULL | NULL | NULL | zhangsan | 99.00 | shanghai |
| 3 | lisi | 50.00 | shanghai | 4 | nj | lisi | 99.99 | nanjing |
+------+--------+-------+------------+--------+------+----------+-------+----------+
3 rows in set (0.00 sec)
在右连接的查询结果集中，除了符合匹配规则的行外，还包括右表中有但是左表中不匹 配的行，这些记录在左表中以 NULL 补足四、Mysql存储过程
4.1 什么是存储过程
- 存储过程是一组为了完成特定功能的SQL语句集合。
- 存储过程在使用过程中是将常用或者复杂的工作预先使用SQL语句写好并用一个指定的名称存储起来，这个过程经编译和优化后存储在数据库服务器中。当需要使用该存储过程时，只需要调用它即可。存储过程在执行上比传统SQL速度更快、执行效率更高。
4.2 存储过程优点
- 执行一次后，会将生成的二进制代码驻留缓冲区，提高执行效率。
- SQL语句加上控制语句的集合，灵活性高。
- 在服务器端存储，客户端调用时，降低网络负载。
- 可多次重复被调用，可随时修改，不影响客户端调用。
- 可完成所有的数据库操作，也可控制数据库的信息访问权限。
4.3 创建、调用和查看存储过程
1、创建存储过程
delimiter $$ #将语句的结束符号从分号；临时改为两个$$（可以自定义）
create procedure proc () #创建存储过程，过程名为proc，不带参数
-> begin #过程体以关键字begin开始
-> select * from store_info; #过程体语句
-> end $$ #过程体以关键字end结束
delimiter ; #将语句的结束符号恢复为分号实例
delimiter $$
create procedure proc01 ()
-> bengin
-> create table student(id int,name char(10),age int);
-> insert into student values(1,'zhangsan',18);
-> insert into student values(2,'lisi',18);
-> select * from student;
-> end $$
delimiter ;2、调用和查看存储过程
call proc; #调用存储过程
show create procedure [数据库.]存储过程名; #查看某个存储过程的具体信息
show create procedure proc;
show create procedure proc\G
show procedure status [like '%proc%'] \G
示例
call proc01;
show create procedure proc01;
show create procedure proc01\G #查看存储过程的具体信息
show procedure status like '%Proc01%'\G3、删除存储过程
#存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程。
drop procedure if exists proc;
#仅当存在时删除，不添加If EXISTS 时，如果指定的过程不存在，则产生一个错误。
示例
drop procedure if exists proc01;4.4 存储过程的参数
- IN 输入参数： 表示调用者向过程传入值。（传入值可以是字面量或变量）
- OUT 输出参数： 表示过程向调用者传出值。(可以返回多个值)（传出值只能是变量）
- INOUT 输入输出参数： 既表示调用者向过程传入值，又表示过程向调用者传出值。（值只能是变量）
注意：变量名不能含有下划线
delimiter $$
create procedure proc2(in stuname char(20)) #参数为stuname，数据类型一定要与下面的where语句后字段的数据类型相同
-> begin
-> select * from student where name = stuname;
-> end $$
delimiter ;
call proc2('zhangsan'); #调用存储过程，并传入参数‘zhangsan’4.5 修改存储过程
- ALTER PROCEDURE <过程名>[<特征>... ]
- ALTER PROCEDURE GetRole MODIFIES SQL DATA SQL SECURITY INVOKER;
- MODIFIES sQLDATA:表明子程序包含写数据的语句
- SECURITY:安全等级
- invoker:当定义为INVOKER时，只要执行者有执行权限，就可以成功执行。
4.6 删除存储过程
存储过程内容的修改方法是通过删除原有存储过程，之后再以相同的名称创建新的存储过程
drop procedure if exists proc(存储名称);
6239
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
