MySQL数据库的高级SQL语句！

MySQL进阶查询select

按关键字排序

• 使用order by语句来实现排序，排序可针对一个或多个字段
• ASC：升序（默认排序方式）
• DESC：降序

##order by 语法结构
select 字段1，字段2，字段3…… from 表名 where 条件表达式 order by 字段1，字段2 asc或desc;
查看某表的字段1，2，3，并根据字段1，2进行排序

mysql> select * from whd;
+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    1 | zhangshan |   18 |
|    2 | lisi      |   20 |
|    3 | wangwu    |   25 |
|    4 | wangmazi  |   22 |
|    5 | xuyi      |   24 |
+------+-----------+------+
5 rows in set (0.00 sec)
mysql> select * from whd order by age asc;		
##查看whd表，根据age字段进行升序排序，后面不加asc或者desc的话默认也为升序

+------+-----------+------+
| id   | name      | age  |
+------+-----------+------+
|    1 | zhangshan |   18 |
|    2 | lisi      |   20 |
|    4 | wangmazi  |   22 |
|    5 | xuyi      |   24 |
|    3 | wangwu    |   25 |
+------+-----------+------+
5 rows in set (0.00 sec)
mysql> select * from whd where age>=22 order by age asc;	#加入条件判断
+------+----------+------+
| id   | name     | age  |
+------+----------+------+
|    4 | wangmazi |   22 |
|    5 | xuyi     |   24 |
|    3 | wangwu   |   25 |
+------+----------+------+
3 rows in set (0.00 sec)

or/and 或、且

mysql> select * from whd;
+------+-----------+------+-------+
| id   | name      | age  | score |
+------+-----------+------+-------+
|    1 | zhangshan |   18 |    80 |
|    2 | lisi      |   20 |    60 |
|    3 | wangwu    |   25 |    60 |
|    4 | wangmazi  |   22 |    90 |
|    5 | xuyi      |   24 |    90 |
+------+-----------+------+-------+
5 rows in set (0.00 sec)
mysql> select * from whd where score >70 and score <=90;
+------+-----------+------+-------+
| id   | name      | age  | score |
+------+-----------+------+-------+
|    1 | zhangshan |   18 |    80 |
|    4 | wangmazi  |   22 |    90 |
|    5 | xuyi      |   24 |    90 |
+------+-----------+------+-------+
3 rows in set (0.00 sec)

• 嵌套查询

mysql> select * from whd where id in (select id from test);
+------+-----------+------+-------+
| id   | name      | age  | score |
+------+-----------+------+-------+
|    1 | zhangshan |   18 |    80 |
|    2 | lisi      |   20 |    60 |
|    3 | wangwu    |   25 |    60 |
+------+-----------+------+-------+
3 rows in set (0.00 sec)
mysql> select distinct score from whd;			##select后加distinct可以查看不重复的，即过滤重复的字段
+-------+，
| score |
+-------+
|    80 |
|    60 |
|    90 |
+-------+
3 rows in set (0.00 sec)

对结果进行分组

• group by 分组

select 字段名 from 表名 where 条件表达式 group by 字段名;

mysql> select count(name),age from whd group by score;
+-------------+------+
| count(name) | age  |
+-------------+------+
|           2 |   20 |
|           1 |   18 |
|           2 |   22 |
+-------------+------+

• 现在结果条目查询

mysql> select * from whd order by score desc limit 2,4;
+------+-----------+------+-------+
| id   | name      | age  | score |
+------+-----------+------+-------+
|    1 | zhangshan |   18 |    80 |
|    2 | lisi      |   20 |    60 |
|    3 | wangwu    |   25 |    60 |
+------+-----------+------+-------+
3 rows in set (0.00 sec

设置别名

在MySQL查询时，有的表名或字段名过长，为了方便书写或查询，可以对字段或表设置别名，增强可读性

select 字段名 as '别名' from 表名;	#对字段设置别名
select 字段名 as '别名'，字段名2 as '别名' from 表名 as '别名';

mysql> select name as '姓名',age as '年龄' from whd;
+-----------+--------+
| 姓名      | 年龄   |
+-----------+--------+
| zhangshan |     18 |
| lisi      |     20 |
| wangwu    |     25 |
| wangmazi  |     22 |
| xuyi      |     24 |
+-----------+--------+
5 rows in set (0.00 sec)
mysql> create table text as select * from whd;		#克隆表，不会复制主键
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from text;
+------+-----------+------+-------+
| id   | name      | age  | score |
+------+-----------+------+-------+
|    1 | zhangshan |   18 |    80 |
|    2 | lisi      |   20 |    60 |
|    3 | wangwu    |   25 |    60 |
|    4 | wangmazi  |   22 |    90 |
|    5 | xuyi      |   24 |    90 |
+------+-----------+------+-------+
5 rows in set (0.00 sec)

通配符

select * from 表名 where 条件表达式 like ‘字符串通配符’;  #like表示模糊查询
 - % :表示零个、一个或多个字符
 - _	：下划线表示单个字符

mysql> select * from whd where name like 'l_s_';
+------+------+------+-------+
| id   | name | age  | score |
+------+------+------+-------+
|    2 | lisi |   20 |    60 |
+------+------+------+-------+
1 row in set (0.00 sec)

mysql> select * from whd where name like 'w%';
+------+----------+------+-------+
| id   | name     | age  | score |
+------+----------+------+-------+
|    3 | wangwu   |   25 |    60 |
|    4 | wangmazi |   22 |    90 |
+------+----------+------+-------+
2 rows in set (0.00 sec)

子查询

子查询也叫内查询或者嵌套查询，指在一个查询语句中嵌套另一个查询语句，并且子查询语句是先于主语句被执行的，子查询的结果作为主查询的查询过滤

mysql> select id,name from whd where id in (select id from whd where score <80);
##如果是not in则表示取反

+------+--------+
| id   | name   |
+------+--------+
|    2 | lisi   |
|    3 | wangwu |
+------+--------+
2 rows in set (0.00 sec)

• 子查询-exists：在子查询前面加exists是为了判断子查询结果是否成立，如果成立，则返回true值正常执行主语据的匹配，如果不成立则返回不成立，则不会执行主语据的查询

mysql> select id,name from whd where exists (select id from whd where score <80);
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshan |
|    2 | lisi      |
|    3 | wangwu    |
|    4 | wangmazi  |
|    5 | xuyi      |
+------+-----------+
5 rows in set (0.00 sec)

mysql> select id,name from whd where exists (select id from whd where score >100);
Empty set (0.00 sec)

视图

数据库中的虚拟表，该表中不包含真是数据，只是做了映射，视图简化查询结果集、灵活查询、可以针对不同用户呈现不同结果集、有较高的安全性

##创建视图
create view 视图名 as select * from 表名 where 条件表达式

##创建多表视图
create view v_zuhe(id,name,score,age) as select test1.id,test1.name,test1.score,test2.age from test1,test2 where test1.name=test2.name;

mysql> create view v_socre as select * from whd where id>3;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from v_socre;			#查看视图
+------+----------+------+-------+
| id   | name     | age  | score |
+------+----------+------+-------+
|    4 | wangmazi |   22 |    90 |
|    5 | xuyi     |   24 |    90 |
+------+----------+------+-------+
2 rows in set (0.01 sec)
mysql> update v_socre set age=18 where id=5;		
##修改原表数据也会对视图产生影响,但是并不允许修改视图来修改原表，因为修改视图中的某些函数计算出的字段不会修改原表

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from whd;
+------+-----------+------+-------+
| id   | name      | age  | score |
+------+-----------+------+-------+
|    1 | zhangshan |   18 |    80 |
|    2 | lisi      |   20 |    60 |
|    3 | wangwu    |   25 |    60 |
|    4 | wangmazi  |   22 |    90 |
|    5 | xuyi      |   18 |    90 |
+------+-----------+------+-------+
5 rows in set (0.00 sec)

• 视图和表的区别
  （1）视图是已经编译好的sql语句，而表不是
  （2）视图没有实际的物理记录，而表有
  （3）表占有物理空间而表不占用物理空间
  （4）视图时查看数据表的一种方法，只是一些SQL语句的集合
  （5）视图的建立和删除只影响视图本身，不影响其对应的表

MySQL中的正则表达式

^ :匹配以什么字符为开头
$ :匹配以什么字符为结尾
. :表示任意单个字符
* :表示零个或多个字符
+ :表示前面的字符出现1次或多次
| :或
? :表示前面的字符零次或一次
[..] :匹配字符集合中的任意一个字符
[^..] :匹配不在括号中的内容
{n} :匹配前面字符串出现n次
{n,m} :匹配前面的字符串至少n次，最多m次

mysql> select id,name from whd where name regexp '^w';
+------+----------+
| id   | name     |
+------+----------+
|    3 | wangwu   |
|    4 | wangmazi |
+------+----------+
2 rows in set (0.00 sec)

mysql> select id,name from whd where name regexp 'an|si';
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshan |
|    2 | lisi      |
|    3 | wangwu    |
|    4 | wangmazi  |
+------+-----------+
4 rows in set (0.00 sec)
mysql> select id,name from whd where name regexp '^[^w]';		#表示不匹配以W开头的
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangshan |
|    2 | lisi      |
|    5 | xuyi      |
+------+-----------+
3 rows in set (0.01 sec)

NULL值

数据库中NULL值表示缺少的值，表示在表中该字段是没有值的

null值和空值的区别： 空值长度为0，不占空间，NULL值的长度为null，占用空间，而当count（）计算时，NULL值会被忽略，而空值会加入计算

mysql> alter table whd add addr varchar(10);		#添加一个字段
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from whd;							#查看表内容
+------+-----------+------+-------+------+
| id   | name      | age  | score | addr |
+------+-----------+------+-------+------+
|    1 | zhangshan |   18 |    80 | NULL |
|    2 | lisi      |   20 |    60 | NULL |
|    3 | wangwu    |   25 |    60 | NULL |
|    4 | wangmazi  |   22 |    90 | NULL |
|    5 | xuyi      |   18 |    90 | NULL |
+------+-----------+------+-------+------+
5 rows in set (0.00 sec)

mysql> update whd set addr='ai' where id>2;			#修改表记录
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> select * from whd;							#查看修改结果
+------+-----------+------+-------+------+
| id   | name      | age  | score | addr |
+------+-----------+------+-------+------+
|    1 | zhangshan |   18 |    80 | NULL |
|    2 | lisi      |   20 |    60 | NULL |
|    3 | wangwu    |   25 |    60 | ai   |
|    4 | wangmazi  |   22 |    90 | ai   |
|    5 | xuyi      |   18 |    90 | ai   |
+------+-----------+------+-------+------+
5 rows in set (0.00 sec)

mysql> update whd set addr='' where id=2;			#添加空值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from whd;
+------+-----------+------+-------+------+
| id   | name      | age  | score | addr |
+------+-----------+------+-------+------+
|    1 | zhangshan |   18 |    80 | NULL |
|    2 | lisi      |   20 |    60 |      |
|    3 | wangwu    |   25 |    60 | ai   |
|    4 | wangmazi  |   22 |    90 | ai   |
|    5 | xuyi      |   18 |    90 | ai   |
+------+-----------+------+-------+------+
5 rows in set (0.00 sec)

mysql> select count(addr) from whd;					#计算addr字段的记录行数
+-------------+
| count(addr) |
+-------------+
|           4 |
+-------------+
1 row in set (0.00 sec)

mysql> update whd set addr='' where id=1;			#将最后一个NULL值修改为空值
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select count(addr) from whd;					#再次查看addr字段行数对比发现，空值会计算在内，而NULL在count（）计算中不会计算在内
+-------------+
| count(addr) |
+-------------+
|           5 |
+-------------+
1 row in set (0.00 sec)

算数运算

• 用select进行计算

mysql> select 1+2,2-1,3*2,4/2,5%2;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*2 | 4/2    | 5%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |   6 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)
mysql> create table ww as select 1+2,2-1,3*2,4/2,5%2;		#创建一个表，将计算放入表中
Query OK, 1 row affected (0.00 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from ww;
+-----+-----+-----+--------+------+
| 1+2 | 2-1 | 3*2 | 4/2    | 5%2  |
+-----+-----+-----+--------+------+
|   3 |   1 |   6 | 2.0000 |    1 |
+-----+-----+-----+--------+------+
1 row in set (0.00 sec)

• 比较运算符

!=或<> :不等于
is null :判断是否给null
is not null :判断是否为非null
betweed and :两者之间
in :判断是否在集合中
like :通配符匹配
greatest :两个或多个参数时返回最大值
least :两个或多个参数时返回最小值
regexp :正则表达式

mysql> select 2=4,2=2,2='2','e'='e','r'=null;		#等号判断值是否相等，结果0为错误，1为正确，如果结果有一个null，其结果就是null
+-----+-----+-------+---------+----------+
| 2=4 | 2=2 | 2='2' | 'e'='e' | 'r'=null |
+-----+-----+-------+---------+----------+
|   0 |   1 |     1 |       1 |     NULL |
+-----+-----+-------+---------+----------+
1 row in set (0.00 sec)
mysql> select least(3,4,5),greatest(1,2,3);		#取最大值和最小值
+--------------+-----------------+
| least(3,4,5) | greatest(1,2,3) |
+--------------+-----------------+
|            3 |               3 |
+--------------+-----------------+
1 row in set (0.00 sec)
mysql> select least(1,'a','b'),greatest(1,'a','b');	#有数字和字母对比时，以数字为优先，如果结果显示字母则表示为0
+------------------+---------------------+
| least(1,'a','b') | greatest(1,'a','b') |
+------------------+---------------------+
| 0                | 1                   |
+------------------+---------------------+
1 row in set, 4 warnings (0.00 sec)

mysql> select 2 in (2,3,4);		#in判断数值是否在集合中
+--------------+
| 2 in (2,3,4) |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)
mysql> select 'abc' like 'ab%','abc' like '_bc','abc' not like 'a%'
    -> ;
+------------------+------------------+---------------------+
| 'abc' like 'ab%' | 'abc' like '_bc' | 'abc' not like 'a%' |
+------------------+------------------+---------------------+
|                1 |                1 |                   0 |
+------------------+------------------+---------------------+
1 row in set (0.00 sec)

• 逻辑运算符

not 或 ！：非，当操作值为0时取反为1，当操作值为1时取反为0，当操作值为null时所得结果也为null
and 或者 && ；与，当所有操作数都为非0值且不为null时，返回值为1，否则为0
or 或 || ：或，当操作值任意一个为非零值且不是null时，返回为1否则为0，有一个操作值为null，另一个操作值为非0时，返回为1，非洲为null
xor ：异或，只有两个操作值都为0或者都是非0时，才返回0，一个非0一个为0则返回1，有一个null值时，则返回为null

mysql> select not 3,!3,not 0;		#2，3表示非0值，所以结果为0，
+-------+----+-------+
| not 3 | !3 | not 0 |
+-------+----+-------+
|     0 |  0 |     1 |
+-------+----+-------+
1 row in set (0.00 sec)
mysql> select 2 and 3,4 && 0,0 && null,1 and null;
+---------+--------+-----------+------------+
| 2 and 3 | 4 && 0 | 0 && null | 1 and null |
+---------+--------+-----------+------------+
|       1 |      0 |         0 |       NULL |
+---------+--------+-----------+------------+
1 row in set (0.00 sec)
mysql> select 1 or 3,0 or null;		 
+--------+-----------+
| 1 or 3 | 0 or null |
+--------+-----------+
|      1 |      NULL |
+--------+-----------+
1 row in set (0.00 sec)
mysql> select 2 or 3,2 or 0,2 or null,0 or 0,null or null
    -> ;
+--------+--------+-----------+--------+--------------+
| 2 or 3 | 2 or 0 | 2 or null | 0 or 0 | null or null |
+--------+--------+-----------+--------+--------------+
|      1 |      1 |         1 |      0 |         NULL |
+--------+--------+-----------+--------+--------------+
1 row in set (0.00 sec)

连接查询

表示将两个或者多个表记录之间的共同字段进行数据的拼接来使表的记录行结合，使用较多的连接查询有；内连接、左连接、右连接

• 内连接 :MySQL中的内连接就是两张或多张表（为了更好的性能，最好不要超过三张表）中同时符合某种条件的数据记录的组合，通常用inner join来连接多张报，并使用on设置连接条件

select column_name(s) from table1 inner join table2 on table1.column_name = table2.column_name;

mysql> create table whds(id int (11),name varchar(20),age int(3));
Query OK, 0 rows affected (0.00 sec)

mysql> insert into whds values(1,'lisi',21),(2,'wangwu',24),(3,'xuyi',19);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
 
mysql> select * from whd;
+------+-----------+------+-------+------+------+
| id   | name      | age  | score | addr | sss  |
+------+-----------+------+-------+------+------+
|    1 | zhangshan |   18 |    80 | null | NULL |
|    2 | lisi      |   20 |    60 |      | NULL |
|    3 | wangwu    |   25 |    60 | ai   | NULL |
|    4 | wangmazi  |   22 |    90 | ai   | NULL |
|    5 | xuyi      |   18 |    90 | ai   | NULL |
+------+-----------+------+-------+------+------+
5 rows in set (0.00 sec)

mysql> select * from whds;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | lisi   |   21 |
|    2 | wangwu |   24 |
|    3 | xuyi   |   19 |
+------+--------+------+
3 rows in set (0.00 sec)
mysql> select whd.name,whd.age from whd inner join whds on whd.name=whds.name;
##查看whd表跟whds表中name相同的数据
+--------+------+
| name   | age  |
+--------+------+
| lisi   |   20 |
| wangwu |   25 |
| xuyi   |   18 |
+--------+------+
3 rows in set (0.00 sec)

• 左连接：以左侧表作为基础表，接受左表所有行，并将与右表相同的部分输出出来，右表没有的用null值表示

mysql> select * from whd left join whds on whd.name=whds.name;
+------+-----------+------+-------+------+------+------+--------+------+
| id   | name      | age  | score | addr | sss  | id   | name   | age  |
+------+-----------+------+-------+------+------+------+--------+------+
|    2 | lisi      |   20 |    60 |      | NULL |    1 | lisi   |   21 |
|    3 | wangwu    |   25 |    60 | ai   | NULL |    2 | wangwu |   24 |
|    5 | xuyi      |   18 |    90 | ai   | NULL |    3 | xuyi   |   19 |
|    1 | zhangshan |   18 |    80 | null | NULL | NULL | NULL   | NULL |
|    4 | wangmazi  |   22 |    90 | ai   | NULL | NULL | NULL   | NULL |
+------+-----------+------+-------+------+------+------+--------+------+
5 rows in set (0.00 sec)	

• 右连接：右连接跟左连接正好相反，它是以右表为基础表，用于接收右表中的所有行，并用这些记录与左表中的行进行匹配，将相同的行输出，不同的为null值

mysql> select * from whd right join whds on whd.name=whds.name;
+------+--------+------+-------+------+------+------+--------+------+
| id   | name   | age  | score | addr | sss  | id   | name   | age  |
+------+--------+------+-------+------+------+------+--------+------+
|    2 | lisi   |   20 |    60 |      | NULL |    1 | lisi   |   21 |
|    3 | wangwu |   25 |    60 | ai   | NULL |    2 | wangwu |   24 |
|    5 | xuyi   |   18 |    90 | ai   | NULL |    3 | xuyi   |   19 |
+------+--------+------+-------+------+------+------+--------+------+
3 rows in set (0.00 sec)