这里写目录标题
MySql例题:
DQL
基础查询
#查询员工号为176的员工的姓名和部门号和年薪(加上奖金率)
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
WHERE employee_id=176;
#查询没有奖金,且工资小于18000的salary,last_name(is null)
SELECT salary,last_name
FROM employees
WHERE salary<18000 AND commission_pct IS NULL;
#查询employees表中,job_id不为'IT'或工资为120000的员工信息(<>不等于)
SELECT *
FROM employees
WHERE salary=12000 OR job_id <> 'IT';
#查询部门departments表中涉及到了哪些位置编号(去重)
SELECT DISTINCT location_id
FROM departments;
#查询员工的姓名和部门号和年薪,按年薪降序 按姓名升序(desc,asc)
SELECT last_name,department_id,salary*12*(1+IFNULL(commission_pct,0)) 年薪
FROM employees
ORDER BY 年薪 DESC,last_name ASC;
#选择工资不在8000到17000的员工的姓名和工资,按工资降序
SELECT last_name,salary
FROM employees
WHERE salary<8000 OR salary>17000
ORDER BY salary DESC;
#查询邮箱中包含e的员工信息,并先按邮箱的字节数降序,再按部门号升序(模糊查询%e%)
SELECT *
FROM employees
WHERE email LIKE '%e%'
ORDER BY LENGTH(email) DESC,department_id ASC;
分组查询、分组函数的使用
#查询公司员工工资的最大值,最小值,平均值,总和
SELECT MAX(salary) mx_sal,MIN(salary) mi_sal,AVG(salary) ag_sal,SUM(salary) sm_sal
FROM employees;
#查询员工表的最大入职时间和最小入职时间的相差天数(DATEDIFF函数 大日期-小日期等相差的天数)
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) diffrence
FROM employees;
#查询各job_id的员工工资的最大值,最小值,平均值,总和,并按job_id升序(GROUP BY)
SELECT MAX(salary) mx_sal,MIN(salary) mi_sal,AVG(salary) ag_sal,SUM(salary) sm_sal
FROM employees
GROUP BY job_id
ORDER BY job_id;
#查询各个管理者手下员工的最低工资,其中最低工资不能低于6000,没有管理者的员工不计算在内(HAVING在分组后)
SELECT MIN(salary),manager_id
FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
HAVING MIN(salary)>=6000;
#查询所有部门的编号,员工数量和工资平均值,并按平均工资降序
SELECT department_id,COUNT(*),AVG(salary)
FROM employees
GROUP BY department_id
ORDER BY AVG(salary) DESC;
连接查询
#显示所有员工的姓名,部门号和部门名称
SELECT last_name,e.department_id,department_name
FROM employees e, departments d
WHERE d.`department_id`=e.`department_id`;
#查询每个工种,每个部门的部门名、工种名和最低工资(两个“每个”GROUP BY后加两个)
SELECT department_name,job_title,MIN(salary) 最低工资
FROM employees e, departments d,jobs j
WHERE d.`department_id`=e.`department_id`
AND e.`job_id`=j.`job_id`
GROUP BY department_name,job_title;
#查询每个国家下的部门个数大于2的国家编号(注意部门个数>2写在HAVING)
SELECT country_id,COUNT(*) 部门个数
FROM departments d,locations l
WHERE d.`location_id`=l.`location_id`
GROUP BY l.`country_id`
HAVING 部门个数>2;
#查询编号>3的女神的男朋友信息如果有则列出详情,没有则用null填充(左外连接,以左为主)
SELECT b.`id`,b.`name`,bo.*
FROM beauty b
LEFT OUTER JOIN boys bo
ON b.`boyfriend_id`=bo.`id`;
#查询那个城市没有部门(右外连接,城市在前面)
SELECT city
FROM departments d
RIGHT JOIN locations l
ON l.`location_id`=d.`location_id`
WHERE department_id IS NULL;
子查询
#查询和Zlotkey相同部门的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
WHERE last_name='Zlotkey'
);
#查询平均工资比公司平均工资要高的员工的员工号,姓名和工资
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
);
#查询各个部门中工资比部门平均工资高的员工的员工号,姓名和工资
SELECT employee_id,last_name,salary
FROM employees e
INNER JOIN(
SELECT AVG(salary) 平均工资,department_id
FROM employees
GROUP BY department_id
) ag_dep
ON e.`department_id`=ag_dep.department_id
WHERE salary>ag_dep.平均工资;
#查询和姓名中包含字母u的员工在相同部门的员工号和姓名(在范围内用in,并且DISTINCT去重)
SELECT employee_id,last_name
FROM employees
WHERE department_id IN(
SELECT DISTINCT department_id
FROM employees
WHERE last_name LIKE '%u%'
);
#查询在部门的location_id为1700的部门工资的员工的员工号
SELECT employee_id
FROM employees
WHERE department_id IN (
SELECT DISTINCT department_id
FROM departments
WHERE location_id=1700
);
#查询管理者是King的员工姓名和工资
SELECT last_name,salary
FROM employees
WHERE manager_id IN(
SELECT employee_id
FROM employees
WHERE last_name='K_ing'
);
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-OLoipiz1-1615728030120)(C:\Users\wan\AppData\Roaming\Typora\typora-user-images\image-20210314134032878.png)]
一
select substr(email,1,instr(email,'@')-1) 用户名
from stuinfo
二
SELECT COUNT(*)
FROM stuinfo
GROUP BY sex;
三
SELECT NAME,gradeName
FROM stuinfo s
INNER JOIN grade g
ON s.gradeId=g.id
WHERE age>18;
四
SELECT MIN(age),gradeId
FROM stuinfo
GROUP BY gradeId
HAVING MIN(age)>20;
五
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-UMLLQL5S-1615728030122)(C:\Users\wan\AppData\Roaming\Typora\typora-user-images\image-20210314134308786.png)]
联表查询
union
多条查询语句的查询列数是一致的
union关键字默认去重
DML(insert、delete、update)改变表里的数据
插入
insert into 表名(字段名,…) values(值,…);
- 支持一次加入多行,支持子查询
insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)#可以不写,默认为全部
values(13,'万','男','19990509','155',null,2);#列数和值数一致,没加的值默认为null
修改
update 表名 set 字段=新值,字段=新值 【where 筛选条件】;
update beauty set phone='8120'
where name like '万';
删除
truncate不可以添加筛选条件、删除后,如果再插入,标识列从1开始、效率较高、没有返回值、不可以回滚
delete可以添加筛选条件、删除后,如果再插入,标识列从断点开始、可以返回受影响的行数、可以回滚
delete from 表名 where 筛选条件
delete from beauty where phone like '%9';#删除9开头的整行
DDL(库和表的操作)改变表的结构
库的创建
creat database 库名;
create database books;
库的删除
drop database books;
创建表
create table 【if not exists】 表名(
字段名 字段类型 【约束】,
字段名 字段类型 【约束】,
。。。
字段名 字段类型 【约束】 )
create table book(
id int,
price double,
authorId int,
publishDate datetime;
);
修改表
1.添加列
alter table 表名 add column 列名 类型;
2.修改列的类型或约束
alter table 表名 modify column 列名 新类型;
3.修改列名
alter table 表名 change column 旧列名 新列名 类型;
4 .删除列
alter table 表名 drop column 列名;
5.修改表名
alter table 表名 rename 新表名;
删除表
drop table 表名;
复制表
1、复制表的结构
create table 表名 like 旧表;
2、复制表的结构+数据
create table 表名
select 查询列表 from 旧表【where 筛选】;
自增长列
特点:
1、不用手动插入值,可以自动提供序列值,默认从1开始,步长为1
auto_increment_increment
如果要更改起始值:手动插入值
如果要更改步长:更改系统变量
set auto_increment_increment=值;
2、一个表至多有一个自增长列
3、自增长列只能支持数值型
4、自增长列必须为一个key
一、创建表时设置自增长列
create table 表(
字段名 字段类型 约束 auto_increment
)
二、修改表时设置自增长列
alter table 表 modify column 字段名 字段类型 约束 auto_increment
三、删除自增长列
alter table 表 modify column 字段名 字段类型 约束