MySql例题

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 字段名 字段类型 约束