一、演示表介绍
1.employees员工表
| 字段名称 | 字段含义 |
|---|
| employee_id | 员工编号 | | first_name | 名 | | last_name | 姓 | | email | 邮箱 | | phone_number | 电话号码 | | job_id | 工种编号 | | salary | 工资 | | commission_pct | 奖金率 | | manager_id | 上级领导的编号 | | department_id | 部门编号 | | hiredate | 入职日期 |
2.departments部门表
| 字段名称 | 字段含义 |
|---|
| department_id | 部门编号 | | department_name | 部门名称 | | manager_id | 部门领导id | | location_id | 位置编号 |
3.location 位置表
| 字段名称 | 字段含义 |
|---|
| location_id | 位置编号 | | street_address | 街道 | | postal_code | 右边 | | city | 成是 | | state_province | 州/省 | | country_id | 国家编号 |
4.jobs工种表
| 字段名称 | 字段含义 |
|---|
| job_id | 工种编号 | | job_title | 工种名称 | | min_salary | 最低工资 | | max_salary | 最高工资 |
三、基础查询
语法：
select 查询列表 from 表名称;
1.查询所有字段
mysql> use myemployees;
mysql> show tables;
+
| Tables_in_myemployees |
+
| departments |
| employees |
| jobs |
| locations |
+
4 rows in set (0.00 sec)
mysql> select * from jobs;
+
| job_id | job_title | min_salary | max_salary |
+
| AC_ACCOUNT | Public Accountant | 4200 | 9000 |
| AC_MGR | Accounting Manager | 8200 | 16000 |
| AD_ASST | Administration Assistant | 3000 | 6000 |
| AD_PRES | President | 20000 | 40000 |
| AD_VP | Administration Vice President | 15000 | 30000 |
| FI_ACCOUNT | Accountant | 4200 | 9000 |
| FI_MGR | Finance Manager | 8200 | 16000 |
| HR_REP | Human Resources Representative | 4000 | 9000 |
| IT_PROG | Programmer | 4000 | 10000 |
| MK_MAN | Marketing Manager | 9000 | 15000 |
| MK_REP | Marketing Representative | 4000 | 9000 |
| PR_REP | Public Relations Representative | 4500 | 10500 |
| PU_CLERK | Purchasing Clerk | 2500 | 5500 |
| PU_MAN | Purchasing Manager | 8000 | 15000 |
| SA_MAN | Sales Manager | 10000 | 20000 |
| SA_REP | Sales Representative | 6000 | 12000 |
| SH_CLERK | Shipping Clerk | 2500 | 5500 |
| ST_CLERK | Stock Clerk | 2000 | 5000 |
| ST_MAN | Stock Manager | 5500 | 8500 |
+
19 rows in set (0.00 sec)
2.查询单个字段
mysql> select job_id from jobs;
+
| job_id |
+
| AC_ACCOUNT |
| AC_MGR |
| AD_ASST |
| AD_PRES |
| AD_VP |
| FI_ACCOUNT |
| FI_MGR |
| HR_REP |
| IT_PROG |
| MK_MAN |
| MK_REP |
| PR_REP |
| PU_CLERK |
| PU_MAN |
| SA_MAN |
| SA_REP |
| SH_CLERK |
| ST_CLERK |
| ST_MAN |
+
19 rows in set (0.00 sec)
3.查询多个字段
mysql> select job_id,job_title from jobs;
+
| job_id | job_title |
+
| AC_ACCOUNT | Public Accountant |
| AC_MGR | Accounting Manager |
| AD_ASST | Administration Assistant |
| AD_PRES | President |
| AD_VP | Administration Vice President |
| FI_ACCOUNT | Accountant |
| FI_MGR | Finance Manager |
| HR_REP | Human Resources Representative |
| IT_PROG | Programmer |
| MK_MAN | Marketing Manager |
| MK_REP | Marketing Representative |
| PR_REP | Public Relations Representative |
| PU_CLERK | Purchasing Clerk |
| PU_MAN | Purchasing Manager |
| SA_MAN | Sales Manager |
| SA_REP | Sales Representative |
| SH_CLERK | Shipping Clerk |
| ST_CLERK | Stock Clerk |
| ST_MAN | Stock Manager |
+
19 rows in set (0.00 sec)
4.查询常量
mysql> select 10010;
+
| 10010 |
+
| 10010 |
+
1 row in set (0.00 sec)
mysql> select "hello world";
+
| hello world |
+
| hello world |
+
1 row in set (0.00 sec)
mysql> select 'mysql very good';
+
| mysql very good |
+
| mysql very good |
+
1 row in set (0.00 sec)
5.查询表达式
mysql> select 100 / 3;
+
| 100 / 3 |
+
| 33.3333 |
+
1 row in set (0.00 sec)
6.查询函数
mysql> select version();
+
| version() |
+
| 5.6.36 |
+
1 row in set (0.00 sec)
mysql> select database();
+
| database() |
+
| myemployees |
+
1 row in set (0.00 sec)
mysql> select now();
+
| now() |
+
| 2020-12-20 11:14:42 |
+
1 row in set (0.00 sec)
7.起别名
mysql> select now() as 时间;
+
| 时间 |
+
| 2020-12-20 11:22:30 |
+
1 row in set (0.00 sec)
mysql> select version() as "版本";
+
| 版本 |
+
| 5.6.36 |
+
1 row in set (0.00 sec)
给表中的字段起别名，其中得as也可以省略
mysql> select min_salary as 最少工资,max_salary as 最多工资 from jobs;
+
| 最少工资 | 最多工资 |
+
| 4200 | 9000 |
| 8200 | 16000 |
| 3000 | 6000 |
mysql> select min_salary 最少工资,max_salary 最多工资 from jobs;
+
| 最少工资 | 最多工资 |
+
| 4200 | 9000 |
| 8200 | 16000 |
| 3000 | 6000 |
| 20000 | 40000 |
给表起别名,一般在多表查询的时候用的较多。
mysql> select * from employees as e;
8.去重distinct
查询员工表中涉及到的所有的部门编号.
mysql> select department_id from employees limit 15;
+
| department_id |
+
| NULL |
| 10 |
| 20 |
| 20 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 30 |
| 40 |
| 50 |
| 50 |
| 50 |
| 50 |
+
15 rows in set (0.00 sec)
这里查询到的数据是重复的，去重的方法就是在字段前面加上去重关键字distinct
mysql> select distinct department_id from employees limit 15;
+
| department_id |
+
| NULL |
| 10 |
| 20 |
| 30 |
| 40 |
| 50 |
| 60 |
| 70 |
| 80 |
| 90 |
| 100 |
| 110 |
+
12 rows in set (0.00 sec)
四、条件查询
语法：
select 查询列表 from 表名 where 筛选条件;
1.按条件表达式筛选
条件运算符号： > < = != <> >= <=
例子1：查询工资>12000的员工信息
mysql> mysql> select * from employees where salary > 12000;
例子2：查询部门编号不等于90号的员工和部门编号
select last_name,department_id from employees where department_id != 90;
或者
select last_name,department_id from employees where department_id <> 90;
2.按照逻辑表达式筛选
逻辑运算符: && || !
and or not
逻辑运算符作用:连接多个表达式
例子1：查询工资在10000到20000之间的员工名、工资以及奖金
select
last_name,salary,commission_pct
from
employees
where
salary >= 10000 and salary <= 20000;
3.模糊查询
(1)like
例子1：查询员工名称中包含字符a的员工信息
# "%"代表统配符，代表任意长度的任意字符。
# "_" 代表任意单个字符
select
*
from
employees
where
last_name like '%a%';
(2)between and
例子：查询员工编号在120到120之间的员工信息
select
*
from
employees
where
employee_id between 100 and 120;
(3)in
例子1：查询员工的工种编号是IT_PROG 、AD_VP、AD_PRES中的一个员工名和工种编号
select
last_name,
job_id
from
employees
where
job_id in('IT_PROG','AD_VP','AD_PRES');
(4)is null
例子1：查询没有奖金的员工名和奖金率
select
last_name,
commission_pct
from
employees
where
commission_pct is null;
反之
select
last_name,
commission_pct
from
employees
where
commission_pct is not null;
五、排序查询
语法：
select
*
from
表名
where
查询条件
order by 排序字段 [asc | desc];
例子1；查询员工得工资 按照低到高排序
mysql> select * from employees order by salary limit 10;
六、常见函数
1.字符函数
(1) length（）:统计字符得字节大小
? 如果是英文字符串得话，字符长度 = 字符所有占用空间大小
? 如果是中文得话，要看字符集设置，在UTF-8中一个汉字占用3个字节
mysql> select length('hello');
+
| length('hello') |
+
| 5 |
+
1 row in set (0.00 sec)
mysql> select length('中国你好');
+
| length('中国你好') |
+
| 12 |
+
1 row in set (0.00 sec)
(2) concat（）:拼接字符串
mysql> select concat('hello',' ','world') as '字符串拼接结果';
+
| 字符串拼接结果 |
+
| hello world |
+
1 row in set (0.00 sec)
拼接字段也是一样得
mysql> select concat(last_name,'_',first_name) from employees limit 3;
+
| concat(last_name,'_',first_name) |
+
| K_ing_Steven |
| Kochhar_Neena |
| De Haan_Lex |
+
3 rows in set (0.00 sec)
(3)upper（）:转换为大写
mysql> select upper('hello');
+
| upper('hello') |
+
| HELLO |
+
1 row in set (0.00 sec)
(4)lower（）:转换为小写
mysql> select lower('HELLO World');
+
| lower('HELLO World') |
+
| hello world |
+
1 row in set (0.00 sec)
(5)substr（）:字符串截取
? substr(数据，开始索引，向后截取得长度)
? 注意：索引是从1开始得
mysql> select phone_number from employees limit 3;
+
| phone_number |
+
| 515.123.4567 |
| 515.123.4568 |
| 515.123.4569 |
+
3 rows in set (0.00 sec)
mysql> select substr(phone_number,5) from employees limit 3;
+
| substr(phone_number,5) |
+
| 123.4567 |
| 123.4568 |
| 123.4569 |
+
3 rows in set (0.00 sec)
mysql> select substr(phone_number,5,3) from employees limit 3;
+
| substr(phone_number,5,3) |
+
| 123 |
| 123 |
| 123 |
+
3 rows in set (0.00 sec)
(6)instr()：取出字串得索引
? 如果找不到返回0
mysql> select instr('张无忌爱周芷若','张') as ret;
+
| ret |
+
| 1 |
+
1 row in set (0.00 sec)
mysql> select instr('张无忌爱周芷若','周芷若') as ret;
+
| ret |
+
| 5 |
+
1 row in set (0.00 sec)
mysql> select instr('张无忌爱周芷若','小') as ret;
+
| ret |
+
| 0 |
+
1 row in set (0.00 sec)
(7)trim()：去空格函数
mysql> select length(' hello ') as ret;
+
| ret |
+
| 11 |
+
1 row in set (0.00 sec)
mysql> select length(trim(' hello ')) as ret;
+
| ret |
+
| 5 |
+
1 row in set (0.00 sec)
(8)replace():字符替换
mysql> select replace('张无忌喜欢周芷若，周芷若也爱张无忌','周芷若','赵敏') as ret;
+
| ret |
+
| 张无忌喜欢赵敏，赵敏也爱张无忌 |
+
1 row in set (0.00 sec)
2.数学函数
(1)round()：四舍五入函数
mysql> select round(3.14);
+
| round(3.14) |
+
| 3 |
+
1 row in set (0.00 sec)
mysql> select round(3.64);
+
| round(3.64) |
+
| 4 |
+
1 row in set (0.00 sec)
mysql> select round(3.64,1);
+
| round(3.64,1) |
+
| 3.6 |
+
1 row in set (0.00 sec)
mysql> select round(3.66,1);
+
| round(3.66,1) |
+
| 3.7 |
+
1 row in set (0.00 sec)
(2)truncate()：直接保留小数点后边几位，不进行四舍五入
mysql> select truncate(3.1415,2);
+
| truncate(3.1415,2) |
+
| 3.14 |
+
1 row in set (0.00 sec)
mysql> select truncate(3.1415,3);
+
| truncate(3.1415,3) |
+
| 3.141 |
+
1 row in set (0.00 sec)
3.日期函数
(1)now():系统日期+时间
mysql> select now();
+
| now() |
+
| 2020-12-20 16:08:07 |
+
1 row in set (0.00 sec)
(2)curdate():只显示日期
mysql> select curdate();
+
| curdate() |
+
| 2020-12-20 |
+
1 row in set (0.00 sec)
(3)curtime()：只显示时间
mysql> select curtime();
+
| curtime() |
+
| 16:09:47 |
+
1 row in set (0.00 sec)
(4)str_to_date():字符串转日期函数
| 序号 | 格式符号 | 功能 |
|---|
| 1 | %Y | 四位的月份 | | 2 | %y | 两位的月份 | | 3 | %m | 月份（01，02…11,12） | | 4 | %c | 月份（1，2…11,12） | | 5 | %d | 日（01，02） | | 6 | %H | 小时（24小时制） | | 7 | %h | 小时（12小时制） | | 8 | %i | 分钟（00，01…59） | | 9 | %s | 秒(00,01…59) |
例子：在转化的时候，后边要转换的位置和前边的数据位置对应上，最后的结果系统自动会按照’年 月 日’的顺序显示出来
mysql> select str_to_date('6-7 1997','%m-%d %Y') as 'date';
+
| date |
+
| 1997-06-07 |
+
1 row in set (0.00 sec)
mysql> select str_to_date('3/1/2020','%m/%d/%Y') as 'date';
+
| date |
+
| 2020-03-01 |
+
1 row in set (0.00 sec)
(5)将日期转换成字符
mysql> select date_format(now(),'%Y年%m月%d日') as ret;
+
| ret |
+
| 2020年12月20日 |
+
1 row in set (0.01 sec)
mysql> select date_format(now(),'公元%Y年 %m月 %d日') as ret;
+
| ret |
+
| 公元2020年 12月 20日 |
+
1 row in set (0.01 sec)
4.其它函数
mysql> select version();
+
| version() |
+
| 5.6.36 |
+
1 row in set (0.00 sec)
mysql> select database();
+
| database() |
+
| myemployees |
+
1 row in set (0.00 sec)
mysql> select user();
+
| user() |
+
| root@localhost |
+
1 row in set (0.00 sec)
5.流程控制函数
七、分组函数
功能：用作统计使用，又称为聚合函数或者统计函数
1.sum():求和
mysql> select sum(salary) from employees;
+
| sum(salary) |
+
| 691400.00 |
+
1 row in set (0.00 sec)
2.avg():平均值
mysql> select avg(salary) from employees;
+
| avg(salary) |
+
| 6461.682243 |
+
1 row in set (0.00 sec)
3.max():最大值
mysql> select max(salary) from employees;
+
| max(salary) |
+
| 24000.00 |
+
1 row in set (0.00 sec)
4.min():最小值
mysql> select min(salary) from employees;
+
| min(salary) |
+
| 2100.00 |
+
1 row in set (0.00 sec)
5.count():计算个数
用法1： count(字段名) ：统计字段一共多少行
mysql> select count(salary) from employees;
+
| count(salary) |
+
| 107 |
+
1 row in set (0.00 sec)
用法2：count(*) : 每一行中只要一个字段有数据，就算一行
6.注意：
1.sum avg 一般只用来处理数值类型数据
2.max min count 可以处理任何类型的数据
3.以上所有函数都忽略null
八、分组查询
语法：
select
分组函数，字段名(这个字段名还要出现在group by的后边)
from
表名
where
筛选条件
group by 上边的字段名（也就是要分组的字段）
1.简单分组查询
例子1：查询每个工种的最高工资
mysql> select max(salary),job_id from employees group by job_id;
+-------------+------------+
| max(salary) | job_id |
+-------------+------------+
| 8300.00 | AC_ACCOUNT |
| 12000.00 | AC_MGR |
| 4400.00 | AD_ASST |
| 24000.00 | AD_PRES |
| 17000.00 | AD_VP |
| 9000.00 | FI_ACCOUNT |
| 12000.00 | FI_MGR |
| 6500.00 | HR_REP |
| 9000.00 | IT_PROG |
| 13000.00 | MK_MAN |
| 6000.00 | MK_REP |
| 10000.00 | PR_REP |
| 3100.00 | PU_CLERK |
| 11000.00 | PU_MAN |
| 14000.00 | SA_MAN |
| 11500.00 | SA_REP |
| 4200.00 | SH_CLERK |
| 3600.00 | ST_CLERK |
| 8200.00 | ST_MAN |
+-------------+------------+
19 rows in set (0.00 sec)
2.带判断的分组查询
例子1：查询邮箱中包含a字符的，每个部门的平均工资
mysql> select avg(salary),department_id,email from employees where email like '%a%' group by department_id;
+
| avg(salary) | department_id | email |
+
| 7000.000000 | NULL | KGRANT |
| 4400.000000 | 10 | JWHALEN |
| 9500.000000 | 20 | MHARTSTE |
| 4460.000000 | 30 | DRAPHEAL |
| 6500.000000 | 40 | SMAVRIS |
| 3496.153846 | 50 | AFRIPP |
| 6200.000000 | 60 | AHUNOLD |
| 10000.000000 | 70 | HBAER |
| 8535.294118 | 80 | KPARTNER |
| 17000.000000 | 90 | NKOCHHAR |
| 8166.666667 | 100 | DFAVIET |
+
11 rows in set (0.00 sec)
|