MySQL查询技巧精讲-优快云博客

一、查询语法

mysql>select 字段名称1，字段名称2 from 表名条件

二、简单查询

mysql> select * from t3;

mysql>select name ,salary薪水，dep_id from employee5;

mysql>select name as mingzi, salary薪水 as moeny ，dep_id from employee5; ------as给字段起别名

mysql>select name as mingzi ,salary+dep_id as num from employee5; 两个字段之间进行加法运算

注意：as 可以省略不写

三、避免重复distinct

mysql>select post from employee5;

mysql>select distinct post from employee5; ----distinct去重

注意：不能部分使用distinct ，通常仅用于某一字段

四、通过四则运算查询

运算：

mysql>select math*china-50 from t1;;

mysql>select 547/6;

mysql>select 5>3; ---(true 为1)

针对表的运算：

mysql>select name ,salay,salary*14 from employee5;

mysql>select anme ,salary,salary*14 as annual_salary from employee5; --------------annual_salary年新

五、定义显示格式

concat( ) 函数用于连接字符串（连接在一起）

ariaDB [company]> select concat(name,'    annual_salary:',salary*14) as annul_salary from employee5;
+-------------------------------------+
| annul_salary                        |
+-------------------------------------+
| jack    annual_salary:70000.00      |
| tom    annual_salary:77000.00       |
| robin    annual_salary:112000.00    |
| alice    annual_salary:100800.00    |
| tianyun    annual_salary:8400.00    |
| harry    annual_salary:84000.00     |
| emma    annual_salary:280000.00     |
| christine    annual_salary:30800.00 |
| zhuzhu    annual_salary:30800.00    |
| gougou    annual_salary:30800.00    |
+-------------------------------------+
10 rows in set (0.00 sec)

六、单条件查询

mysql>select salary from t3 where name="bijz"; -----查询t3表中name为bijz的salary

mysql>select salary from t3 where not name="bijz"; ---查询t3表中name除bijz这个名字的salary

七、多条件查询

1.关键字where

mysql>select math from db1.t1 where math>50 and math<60 ; -----and也可以是&&

mysql>select math from db1.t1 where not math>50;

2.关键字between and

mysql>select name,salary from employee5 where salary between 5000 and 15000;

mysql>select name,salary from employee5 where salary not between 5000 and 15000;

3.关键字 is null is not null

SELECT name,job_description FROM employee5 WHERE job_description IS NULL;

SELECT name,job_description FROM employee5 WHERE job_description IS NOT NULL;

SELECT name,job_description FROM employee5 WHERE job_description='';

NULL说明：
1、等价于没有任何值、是未知数。
2、NULL与0、空字符串、空格都不同,NULL没有分配存储空间。
3、对空值做加、减、乘、除等运算操作，结果仍为空。
4、比较时使用关键字用“is null”和“is not null”。
5、排序时比其他数据都小（索引默认是降序排列，小→大），所以NULL值总是排在最前。

4、关键字 IN集合查询

SELECT name, salary FROM employee5
WHERE salary=4000 OR salary=5000 OR salary=6000 OR salary=9000 ;

SELECT name, salary FROM employee5
WHERE salary IN (4000,5000,6000,9000) ;

SELECT name, salary FROM employee
WHERE salary NOT IN (4000,5000,6000,9000) ;

八.排序查询 order by

mysql> select china from t1 order by china;
mysql> select china from t1 order by china desc;                      -----降序
mysql> select china from t1 order by china desc limit 3;           -----降序前三名
mysql> select china from t1 order by china desc limit 1,3;        -----从第2行开始取三个

注：
ascending 美音 /ə'sɛndɪŋ/ 升序（默认)
descending 美音 /dɪ'sɛndɪŋ/ 降序

按多列排序:

入职时间相同的人薪水不同

先按入值时间，再按薪水排序

SELECT * FROM employee5
ORDER BY hire_date DESC,
salary ASC;

九.分组查询

（1）group by 和 group_concat( )函数一起使用（把不同组的记录放到一行）

搜索字段dep_id和name，把名字按照dep_id分组。---------统计每个部门都有谁

搜索字段sex和name，把名字按照sex分组。--------统计男生和女生各有谁

（2）group by 和集合函数sum（salary）一起使用

统计每个部门一共发多少薪水

搜索dep_id和把每个部门的薪水按照求和并按照dep_ip排列。

MariaDB [company]> select dep_id,sum(salary) from employee5 group by dep_id;

+--------+-------------+
| dep_id | sum(salary) |
+--------+-------------+
|    100 |    25700.00 |
|    101 |     6600.00 |
|    102 |    26600.00 |
+--------+-------------+
3 rows in set (0.02 sec)

十、模糊查询（通配符）

_ 下划线任意单个字符

% 所有字符

mysql>select * from t1 where china='1_';

mysql>select * from t1 where china like '%0%';

小结：对字符串匹配的方式

WHERE name = 'tom';
WHERE name LIKE 'to%';
WHERE name REGEXP 'lice$';

十一、正则查询

MariaDB [company]> select * from employee5 where name regexp '^ali';
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| id | name | sex | hire_date | post | job_description | salary | office | dep_id |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+
| 4 | alice | female | 2018-02-02 | instructor | teach | 7200.00 | 501 | 100 |
+----+-------+--------+------------+------------+-----------------+---------+--------+--------+

十二、子查询

搜索表中工资最高的人的全部信息

十三、函数

count()
max()
min()
avg()
database()
user()
now()
sum()

SELECT COUNT(*) FROM employee5;     --------统计表中总共有多少条记录
SELECT COUNT(*) FROM employee5 WHERE dep_id=101;   ---------统计101部门总共有多少条记录
SELECT MAX(salary) FROM employee5;     ----------查找表中的最高工资
SELECT MIN(salary) FROM employee5;      ----------查找表中的最低工资
SELECT AVG(salary) FROM employee5;     ----------计算表中工资的平均值
SELECT SUM(salary) FROM employee5;     ---------对表中的所有工资求和
SELECT SUM(salary) FROM employee5 WHERE dep_id=101;    -------对101部门的所有工资求和

MariaDB [company]> select password(5);
+-------------------------------------------+
| password(5) |
+-------------------------------------------+
| *7534F9EAEE5B69A586D1E9C1ACE3E3F9F6FCC446 |
+-------------------------------------------+

MariaDB [company]> select md5(5);
+----------------------------------+
| md5(5) |
+----------------------------------+
| e4da3b7fbbce2345d7772b0674a318d5 |
+----------------------------------+
1 row in set (0.00 sec)

MariaDB [company]> select sha1(5);
+------------------------------------------+
| sha1(5) |
+------------------------------------------+
| ac3478d69a3c81fa62e60f5c3696165a4e5e6ac4 |
+------------------------------------------+
1 row in set (0.00 sec)