深入理解mysql语句（一）

一、查询

一、按关键字排序

1、使用ORDER BY语句来实现排序排序
2、可针对一个或多个字段
3、ASC:升序，默认排序方式，可以省略
4、DESC:降序

#创建实验表
mysql> create table wz (id int,name varchar(10) primary key not null ,score decimal(5,2),address varchar(20),hobbid int(5));
mysql> insert into wz values(1,'xiaoqiao',80,'beijing',2);
mysql> insert into wz values(2,'yuji',90,'shanghai',2);
mysql> insert into wz values(3,'jialuo',60,'hangzhou',4);
mysql> insert into wz values(4,'ganjiang',99,'hangzhou',4);
mysql> insert into wz values(5,'luban',98,'shengzheng',5);
mysql> insert into wz values(6,'zhangliang',22,'nanjing',3);
mysql> insert into wz values(7,'anjila',15,'nanjing',5);
mysql> select * from wz;
+------+------------+-------+------------+--------+
| id   | name       | score | address    | hobbid |
+------+------------+-------+------------+--------+
|    7 | anjila     | 15.00 | nanjing    |      5 |
|    4 | ganjiang   | 99.00 | hangzhou   |      4 |
|    3 | jialuo     | 60.00 | hangzhou   |      4 |
|    5 | luban      | 98.00 | shengzheng |      5 |
|    1 | xiaoqiao   | 80.00 | beijing    |      2 |
|    2 | yuji       | 90.00 | shanghai   |      2 |
|    6 | zhangliang | 22.00 | nanjing    |      3 |
+------+------------+-------+------------+--------+
7 rows in set (0.00 sec)

mysql> select id,name,score from wz order by score; #默认不指定是升序
+------+------------+-------+
| id   | name       | score |
+------+------------+-------+
|    7 | anjila     | 15.00 |
|    6 | zhangliang | 22.00 |
|    3 | jialuo     | 60.00 |
|    1 | xiaoqiao   | 80.00 |
|    2 | yuji       | 90.00 |
|    5 | luban      | 98.00 |
|    4 | ganjiang   | 99.00 |
+------+------------+-------+
7 rows in set (0.03 sec)

mysql> select id,name,score from wz order by score desc; #降序查看
+------+------------+-------+
| id   | name       | score |
+------+------------+-------+
|    4 | ganjiang   | 99.00 |
|    5 | luban      | 98.00 |
|    2 | yuji       | 90.00 |
|    1 | xiaoqiao   | 80.00 |
|    3 | jialuo     | 60.00 |
|    6 | zhangliang | 22.00 |
|    7 | anjila     | 15.00 |
+------+------------+-------+
7 rows in set (0.00 sec)

mysql> select id,name,score from wz where address='hangzhou' order by score desc; #使用where筛选出address='hangzhou'后并按scroe降序
+------+----------+-------+
| id   | name     | score |
+------+----------+-------+
|    4 | ganjiang | 99.00 |
|    3 | jialuo   | 60.00 |
+------+----------+-------+
2 rows in set (0.00 sec)

mysql> select id,name,hobbid from wz order by hobbid desc,id desc; #先执行hobbid的降序，在hobbid相同的数据中执行id的降序      
+------+------------+--------+
| id   | name       | hobbid |
+------+------------+--------+
|    7 | anjila     |      5 |
|    5 | luban      |      5 |
|    4 | ganjiang   |      4 |
|    3 | jialuo     |      4 |
|    6 | zhangliang |      3 |
|    2 | yuji       |      2 |
|    1 | xiaoqiao   |      2 |
+------+------------+--------+
7 rows in set (0.00 sec)

二、区间判断及查询不重复记录

① AND/OR ——且/或

mysql> select * from wz where score >70 and score <=90; #查看同时满足两个条件的数据
+------+----------+-------+----------+--------+
| id   | name     | score | address  | hobbid |
+------+----------+-------+----------+--------+
|    1 | xiaoqiao | 80.00 | beijing  |      2 |
|    2 | yuji     | 90.00 | shanghai |      2 |
+------+----------+-------+----------+--------+
2 rows in set (0.01 sec)

mysql> select * from wz where score >70 or score <=90; #查看两个条件满足一个即可
+------+------------+-------+------------+--------+
| id   | name       | score | address    | hobbid |
+------+------------+-------+------------+--------+
|    7 | anjila     | 15.00 | nanjing    |      5 |
|    4 | ganjiang   | 99.00 | hangzhou   |      4 |
|    3 | jialuo     | 60.00 | hangzhou   |      4 |
|    5 | luban      | 98.00 | shengzheng |      5 |
|    1 | xiaoqiao   | 80.00 | beijing    |      2 |
|    2 | yuji       | 90.00 | shanghai   |      2 |
|    6 | zhangliang | 22.00 | nanjing    |      3 |
+------+------------+-------+------------+--------+
7 rows in set (0.00 sec)

嵌套（多个条件）
mysql> select * from wz where score >70 or (score >75 and score <90); #查看score大于75的数据
+------+----------+-------+------------+--------+
| id   | name     | score | address    | hobbid |
+------+----------+-------+------------+--------+
|    4 | ganjiang | 99.00 | hangzhou   |      4 |
|    5 | luban    | 98.00 | shengzheng |      5 |
|    1 | xiaoqiao | 80.00 | beijing    |      2 |
|    2 | yuji     | 90.00 | shanghai   |      2 |
+------+----------+-------+------------+--------+
4 rows in set (0.00 sec)

三、distinct 查询不重复记录

mysql> select distinct hobbid from wz;
+--------+
| hobbid |
+--------+
|      5 |
|      4 |
|      2 |
|      3 |
+--------+
4 rows in set (0.00 sec)

四、对结果进行分组

通过 SQL 查询出来的结果，还可以对其进行分组，使用GROUP BY 语句来实现 ，GROUP BY 通常都是结合聚合函数一起使用的，常用的聚合函数包括：计数（COUNT）、 求和（SUM）、求平均数（AVG）、最大值（MAX）、最小值（MIN），GROUP BY 分组的时候可以按一个或多个字段对结果进行分组处理。

mysql> select count(name),hobbid from wz group by hobbid; #对hobbid组相同字段的数据进行计数
+-------------+--------+
| count(name) | hobbid |
+-------------+--------+
|           2 |      2 |
|           1 |      3 |
|           2 |      4 |
|           2 |      5 |
+-------------+--------+
4 rows in set (0.00 sec)

mysql> select count(name),hobbid from wz where score>=80 group by hobbid; #对对hobbid组相同字段且score字段>=80的数据进行计数
+-------------+--------+
| count(name) | hobbid |
+-------------+--------+
|           2 |      2 |
|           1 |      4 |
|           1 |      5 |
+-------------+--------+
3 rows in set (0.00 sec)

mysql> select count(name),score,hobbid from wz where score>=80 group by hobbid order by count(name)asc; #对对hobbid组相同字段且score字段>=80的数据进行计数,然后对count(name)升序排序
+-------------+-------+--------+
| count(name) | score | hobbid |
+-------------+-------+--------+
|           1 | 99.00 |      4 |
|           1 | 98.00 |      5 |
|           2 | 80.00 |      2 |
+-------------+-------+--------+
3 rows in set (0.00 sec)

五、限制结果条目(limit)

limit的第一个参数是位置偏移量（可选参数），是设置MySQL从哪一行开始显示。如果不设定第一个参数，将会从表中的第一条记录开始显示。需要注意的是，第一条记录的位置偏移量是0，第二条是1，以此类推。第二个参数是设置返回记录行的最大数目。

mysql> select * from wz limit 3;  #查看表的前三行数据                                   
+------+----------+-------+----------+--------+
| id   | name     | score | address  | hobbid |
+------+----------+-------+----------+--------+
|    7 | anjila   | 15.00 | nanjing  |      5 |
|    4 | ganjiang | 99.00 | hangzhou |      4 |
|    3 | jialuo   | 60.00 | hangzhou |      4 |
+------+----------+-------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from wz limit 3,3;#从第4行开始，往后显示3行内容
+------+----------+-------+------------+--------+
| id   | name     | score | address    | hobbid |
+------+----------+-------+------------+--------+
|    5 | luban    | 98.00 | shengzheng |      5 |
|    1 | xiaoqiao | 80.00 | beijing    |      2 |
|    2 | yuji     | 90.00 | shanghai   |      2 |
+------+----------+-------+------------+--------+
3 rows in set (0.00 sec)

mysql>  select id,name from wz order by id limit 3; #按id的大小升序排列显示前三行
+------+----------+
| id   | name     |
+------+----------+
|    1 | xiaoqiao |
|    2 | yuji     |
|    3 | jialuo   |
+------+----------+
3 rows in set (0.00 sec)

mysql> select id,name from wz order by id desc limit 3; #按id的大小降序排列显示前三行/显示最后三行
+------+------------+
| id   | name       |
+------+------------+
|    7 | anjila     |
|    6 | zhangliang |
|    5 | luban      |
+------+------------+
3 rows in set (0.00 sec)

六、设置别名(as)

在 MySQL 查询时，当表的名字比较长或者表内某些字段比较长时，为了方便书写或者 多次使用相同的表，可以给字段列或表设置别名。

mysql> select count(*) as number from wz;#查询wz表的字段数量，以number显示(as可以省略)
+--------+
| number |
+--------+
|      7 |
+--------+
1 row in set (0.01 sec)

#创建yx表，将wz表的查询记录全部插入yx表中
mysql> create table yx as select * from wz;
mysql> select * from yx;
+------+------------+-------+------------+--------+
| id   | name       | score | address    | hobbid |
+------+------------+-------+------------+--------+
|    7 | anjila     | 15.00 | nanjing    |      5 |
|    4 | ganjiang   | 99.00 | hangzhou   |      4 |
|    3 | jialuo     | 60.00 | hangzhou   |      4 |
|    5 | luban      | 98.00 | shengzheng |      5 |
|    1 | xiaoqiao   | 80.00 | beijing    |      2 |
|    2 | yuji       | 90.00 | shanghai   |      2 |
|    6 | zhangliang | 22.00 | nanjing    |      3 |
+------+------------+-------+------------+--------+
7 rows in set (0.00 sec)
但“约束”没有被完全“复制”过来，但是如果原表设置了主键，那么附表的：default字段会默认设置一个0

七、通配符

%：百分号表示零个或一个或多个字符
_：下划线表示单个字符

mysql> select id,name from wz where name like 'z%'; #查询name是z开头的记录
+------+------------+
| id   | name       |
+------+------------+
|    6 | zhangliang |
+------+------------+
1 row in set (0.00 sec)

mysql> select id,name from wz where name like '%g'; #查询name是g结尾的记录
+------+------------+
| id   | name       |
+------+------------+
|    4 | ganjiang   |
|    6 | zhangliang |
+------+------------+
2 rows in set (0.00 sec)

mysql> select id,name from wz where name like '%a%';#查询name含有a的记录
+------+------------+
| id   | name       |
+------+------------+
|    7 | anjila     |
|    4 | ganjiang   |
|    3 | jialuo     |
|    5 | luban      |
|    1 | xiaoqiao   |
|    6 | zhangliang |
+------+------------+
6 rows in set (0.00 sec)

mysql> select id,name from wz where name like 'yuj_'; #查询name中yuj后面跟任一字符的记录
+------+------+
| id   | name |
+------+------+
|    2 | yuji |
+------+------+
1 row in set (0.00 sec)

mysql> select id,name from wz where name like '%n_';#查询name中n后面含有一个字符记录
+------+------------+
| id   | name       |
+------+------------+
|    4 | ganjiang   |
|    6 | zhangliang |
+------+------------+
2 rows in set (0.00 sec)

二、子查询

子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一 步的查询过滤。

mysql> select name,score from wz where id in (select id from wz where score>80); #通过子查询score>80筛选出来的id号，来执行主主查询
+----------+-------+
| name     | score |
+----------+-------+
| ganjiang | 99.00 |
| luban    | 98.00 |
| yuji     | 90.00 |
+----------+-------+
3 rows in set (0.01 sec)

#删除表数据，通过子查询重新添加
mysql> delete from yx; #删除yx表中所有数据
mysql> insert into yx select * from wz where id in (select id from wz);
mysql> select * from yx;
+------+------------+-------+------------+--------+
| id   | name       | score | address    | hobbid |
+------+------------+-------+------------+--------+
|    7 | anjila     | 15.00 | nanjing    |      5 |
|    4 | ganjiang   | 99.00 | hangzhou   |      4 |
|    3 | jialuo     | 60.00 | hangzhou   |      4 |
|    5 | luban      | 98.00 | shengzheng |      5 |
|    1 | xiaoqiao   | 80.00 | beijing    |      2 |
|    2 | yuji       | 90.00 | shanghai   |      2 |
|    6 | zhangliang | 22.00 | nanjing    |      3 |
+------+------------+-------+------------+--------+
7 rows in set (0.00 sec)

#update语句也可以使用子查询
mysql> create table xh (id int);
mysql> insert into xh values(1),(2),(3);
mysql> update wz set score=50 where id in (select id from xh where id=2); #通过子查询结果将id=2的这行数据的score改为50
mysql> select * from wz;
+------+------------+-------+------------+--------+
| id   | name       | score | address    | hobbid |
+------+------------+-------+------------+--------+
|    7 | anjila     | 15.00 | nanjing    |      5 |
|    4 | ganjiang   | 99.00 | hangzhou   |      4 |
|    3 | jialuo     | 60.00 | hangzhou   |      4 |
|    5 | luban      | 98.00 | shengzheng |      5 |
|    1 | xiaoqiao   | 80.00 | beijing    |      2 |
|    2 | yuji       | 50.00 | shanghai   |      2 |
|    6 | zhangliang | 22.00 | nanjing    |      3 |
+------+------------+-------+------------+--------+
7 rows in set (0.00 sec)

#delete也适用于子语句
mysql> delete from yx where id in (select id where score>80); #删除分数大于80的记录
mysql> select * from yx;
+------+------------+-------+----------+--------+
| id   | name       | score | address  | hobbid |
+------+------------+-------+----------+--------+
|    7 | anjila     | 15.00 | nanjing  |      5 |
|    3 | jialuo     | 60.00 | hangzhou |      4 |
|    1 | xiaoqiao   | 80.00 | beijing  |      2 |
|    6 | zhangliang | 22.00 | nanjing  |      3 |
+------+------------+-------+----------+--------+
4 rows in set (0.00 sec)

#在in前面还可以添加not，其作用与not相反，表示否定
mysql> delete from yx where id not in (select id where score>=80); #删除分数不是大于等于80的记录
mysql> select * from yx;
+------+----------+-------+---------+--------+
| id   | name     | score | address | hobbid |
+------+----------+-------+---------+--------+
|    1 | xiaoqiao | 80.00 | beijing |      2 |
+------+----------+-------+---------+--------+
1 row in set (0.00 sec)

#exists这个关键字在子查询时,主要用于判断子查询的结果集是否为空。如果不为空,则返回ture;反之,则返回fales
mysql> select count(*) from wz where exists(select id from wz where score=80);#查询如果存在分数等于80的记录则计算wz的字段数
+----------+
| count(*) |
+----------+
|        7 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from wz where exists(select id from wz where score<10);#查询如果存在分数小于50的记录则计算wz的字段数，wz表没有小于50的，所以返回0
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

三、视图

1、简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、相对有更高的安全性，本质而言视图是一种select(结果集的呈现)
2、视图适合于多表连接浏览时使用，不适合增、删、改。
3、而存储过程适合于使用较频繁的SQL语句，这样可以提高执行效率。

一、视图和表的区别

1、视图是已经编译好的sql语句。而表不是
2、视图没有实际的物理记录。而表有。
3、表只用物理空间而视图不占用物理空间，视图只是逻辑概念的存在，表可以及时对它进行修改，但视图只能有创建的语句来修改
4、视图是查看数据表的一种方法，可以查询数据表中某些字段构成的数据，只是一些SQL语句的集合。从安全的角度说，视图可以不给用户接触数据表，从而不知道表结构。
5、表属于全局模式中的表，是实表；视图属于局部模式的表，是虚表。
6、视图的建立和删除只影响视图本身，不影响对应的基本表。（但是更新视图数据，是会影响到基本表的）

二、视图和表的联系

视图(view)是在基本表之上建立的表，它的结构(即所定义的列)和内容(即所有数据行)都来自基本表，它依据基本表存在而存在。一个视图可以对应一个基本表，也可以对应多个基本表。视图是基本表的抽象和在逻辑意义上建立的新关系。

三、单表

mysql> create view v_wz as select * from wz where score>=80; #创建视图
mysql> select * from v_wz; #查看视图
+----+----------+-------+------------+--------+
| id | name     | score | address    | hobbid |
+----+----------+-------+------------+--------+
|  1 | xiaoqiao | 80.00 | beijing    |      2 |
|  4 | ganjiang | 99.00 | hangzhou   |      4 |
|  5 | luban    | 98.00 | shengzheng |      5 |
+----+----------+-------+------------+--------+
3 rows in set (0.00 sec)
mysql> show table status\G; #查看表状态

视图中没有实际的物理记录

四、多表

mysql> create table test01 (id int,name varchar(10),age char(10));
mysql> insert into test01 values(1,'xiaoqiao',20);
mysql> insert into test01 values(2,'yuji',18);
mysql> insert into test01 values(3,'jialuo',22);

mysql> create view v_db(id,name,score,age) as select wz.id,wz.name,wz.score,test01.age from wz,test01 where wz.name=test01.name; #将表wz和表test01的需求字段组合

mysql> update wz set score='60' where name='yuji'; #修改原表

#查看视图的变化

mysql> update v_db set score='100' where name='jialuo'; #修改视图数据
mysql> select * from wz;#查看原表

一、null值

mysql> alter table wz add column aa varchar(50);#给wz表添加一个字段aa
mysql> update wz set aa='x' where score >=80;#修改score字段大于等于80，aa的数据为x
mysql> select * from wz;
+----+------------+--------+------------+--------+------+
| id | name       | score  | address    | hobbid | aa   |
+----+------------+--------+------------+--------+------+
|  1 | xiaoqiao   |  80.00 | beijing    |      2 | x    |
|  2 | yuji       |  60.00 | shanghai   |      2 | NULL |
|  3 | jialuo     | 100.00 | hangzhou   |      4 | x    |
|  4 | ganjiang   |  99.00 | hangzhou   |      4 | x    |
|  5 | luban      |  98.00 | shengzheng |      5 | x    |
|  6 | zhangliang |  60.00 | nanjing    |      3 | NULL |
|  7 | anjila     |  15.00 |            |      5 | NULL |
+----+------------+--------+------------+--------+------+
7 rows in set (0.00 sec)
mysql> select count(aa) from wz; #统计数量，发现null值不作为做计数

mysql> update wz set address='' where name='anjila';#表中其中一条数据修改为空值''
mysql> select count(address) from wz;#统计数量，检测空值会被添加到统计中

mysql> select * from wz where aa is NULL; #查看aa字段为null值的数据
+----+------------+-------+----------+--------+------+
| id | name       | score | address  | hobbid | aa   |
+----+------------+-------+----------+--------+------+
|  2 | yuji       | 60.00 | shanghai |      2 | NULL |
|  6 | zhangliang | 60.00 | nanjing  |      3 | NULL |
|  7 | anjila     | 15.00 |          |      5 | NULL |
+----+------------+-------+----------+--------+------+
3 rows in set (0.00 sec)

mysql> select * from wz where aa is not NULL;#查看aa字段不为null值的数据
+----+----------+--------+------------+--------+------+
| id | name     | score  | address    | hobbid | aa   |
+----+----------+--------+------------+--------+------+
|  1 | xiaoqiao |  80.00 | beijing    |      2 | x    |
|  3 | jialuo   | 100.00 | hangzhou   |      4 | x    |
|  4 | ganjiang |  99.00 | hangzhou   |      4 | x    |
|  5 | luban    |  98.00 | shengzheng |      5 | x    |
+----+----------+--------+------------+--------+------+
4 rows in set (0.00 sec)