MySql数据库，SQL语句小结

备份数据库、恢复数据库

1. 打开CMD窗口(不要登录)，通过命令备份mydb1数据库。备份命令: mysqldump -u用户名 -p 数据库名字 > 数据文件的位置
   例如: mysqldump -uroot -p mydb1 > d:/mydb1.sql
   输入密码, 如果没有提示错误, 即备份成功, 查询d盘的mydb1.sql文件
   (备份数据库只是备份数据库中的表, 不会备份数据库本身)
2. 备份数据库只是备份数据库中的表, 不会备份数据库本身
   drop database mydb1; – 删除mydb1库
   show databases; – 查询所有库, 是否还存在mydb1库
   由于备份时, 没有备份数据库本身, 所以在恢复库中的数据前, 需要先创建好要恢复的库
   create database mydb1 charset utf8; – 创建mydb1数据库
   (此时的mydb1库是空的, 没有任何表)
3. 在CMD窗口中(不要登录)，通过命令恢复mydb1数据库
   恢复命令: mysql -u用户名 -p 数据库名字 < 数据文件的位置
   例如: mysql -uroot -p mydb1 < d:/mydb1.sql
   输入密码, 如果没有提示错误, 即恢复成功, 下面进行验证
4. 在登录状态下, 选择mydb1库, 查询其中的表是否恢复了回来

SQL基础

– 01.查看mysql服务器中所有数据库

show databases; 

– 02.进入某一数据库（进入数据库后，才能操作库中的表和表记录）

use mysql; 

– 查看已进入的库

select database();

– 03.查看当前数据库中的所有表

show tables; 

– 04.删除mydb1库

drop database mydb1; 

– 当删除的表不存在时，如何避免错误产生?

drop database if exists mydb1; 

– 05.重新创建mydb1库，指定编码为utf8

create database mydb1 charset utf8; 

– 如果不存在则创建mydb1;

create database if not exists mydb1 charset utf8; 

– 06.查看建库时的语句（并验证数据库库使用的编码）

show create database mydb1; 

– 07.进入mydb1库，删除stu学生表(如果存在)

drop table if exists stu; 

– 08.创建stu学生表（编号[数值类型]、姓名、性别、出生年月、考试成绩[浮点型]）

use mydb1;
drop table if exists stu;
create table stu(
	id int primary key auto_increment, -- id主键自增
	name varchar(20), 
	gender varchar(10),
	birthday date,
	score double
);	

– 09.查看stu学生表结构

desc stu; 

– 查看建表时的语句

show create table stu; 

– 10.插入一条记录

insert into stu(id,name,gender,birthday,score) values(null,'tony','male','1988-1-1',78);

– 设置字段编码

set names gbk; 

– 11.查询stu表所有学生的信息

select * from stu; 

– 12.修改stu表中所有学生的成绩

update stu set score=score+10; 

– 13.修改stu表中tony的成绩

update stu set score=88 where name='tony'; 

– 14.删除stu表中所有的记录不会删除自增变量的值

delete from stu; 

– 仅删除符合条件的
delete from stu where id>=3;
– 清空表记录并重置表,会删除自增变量的值

truncate table stu; 

SQL基础查询

– 查询emp表中的所有员工，显示姓名，薪资，奖金
/* 使用 *的缺点：把不必要的列也查询出来了，而且效率不如直接指定列名 */

select name,sal,bonus from emp; 

– distinct 用于剔除重复的记录

select distinct dept,job from emp; 

– 查询emp表中总薪资(薪资+奖金)大于3500的所有员工，显示员工姓名、总薪资

select name,sal+ifnull(bonus,0) from emp
where sal+ifnull(bonus,0) > 3500;
-- ---------------------设置别名
select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp
where sal+ifnull(bonus,0) > 3500;
-- --------------------- as可以省略
select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp
where sal+ifnull(bonus,0) > 3500;

-- ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值
-- 注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 "总薪资"
-- where子句中不能使用列别名

– 查询emp表中薪资在3000和4500之间的员工，显示员工姓名和薪资
/* between…and… 是闭区间，[3000,4500] */

select name,sal from emp where sal between 3000 and 4500;

– 查询emp表中薪资为 1400、1600、1800的员工，显示员工姓名和薪资

select name,sal from emp
where sal=1400 or sal=1600 or sal=1800;
-- -------------------------
select name,sal from emp
where sal in(1400,1600,1800);
-- --------------------批量删除
delete from stu where id in(1,3,5,7,9);

– 查询薪资不为1400、1600、1800的员工

select name,sal from emp
where sal!=1400 and sal!=1600 and sal!=1800;
select name,sal from emp
where not(sal=1400 or sal=1600 or sal=1800);
select name,sal from emp
where sal not in(1400,1600,1800);

– 查询没有部门的员工（即部门列为null值）

select * from emp
where dept is null;
-- 如何查询有部门的员工（即部门列不为null值）
select * from emp
where dept is not null;

SQL模糊查询

/* like进行模糊查询，"%" 表示通配，表示0或多个任意的字符。 "_"表示一个任意的字符 */
– 查询emp表中姓名中以"刘"字开头的员工，显示员工姓名。

select name from emp where name like '刘%';

– 查询emp表中姓名中包含"涛"字的员工，显示员工姓名。

select name from emp where name like '%涛%';

– 查询emp表中姓名以"刘"开头，并且姓名为两个字的员工，显示员工姓名。

select name from emp where name like '刘_';

SQL分组查询、聚合函数、排序查询

– 对emp表按照部门对员工进行分组，查看分组后效果

/* 分组的语法: 
select 查询的列 from 表名 group by 列名
根据指定的列进行分组 */
select * from emp
group by dept;

– count(列|*) 统计行数
– 统计emp表中的所有员工的人数(按组统计,若没有分组,查询结果默认为一组)
– 对emp表按照部门进行分组, 求每个部门的最高薪资(不包含奖金)，显示部门名称和最高薪资

-- max(列名) 求当前列中的最大值
-- min(列名) 求当前列中的最小值
-- 求所有员工中的最高薪资/最低薪资
select max(sal) from emp;
select min(sal) from emp;
-- 求每个部门(每个组)中的最高薪资
select dept, max(sal) from emp group by dept;

– sum(column)对某列的值求和
– avg(column)对某列的值求平均值
– 查询本月过生日的所有员工

select name,birthday from emp
where month(birthday)=month(curdate())
-- 查询下个月过生日的所有员工
select name,birthday from emp
where month(birthday)=month(curdate())+1
/* 
curdate() 获取当前日期 年月日
curtime() 获取当前时间 时分秒
sysdate() 获取当前日期+时间 年月日 时分秒 */

/* order by 排序的列 asc 升序(从低到高)
order by 排序的列 desc 降序(从高到低)
默认就是升序，所以asc可以省略不写 */
– 对emp表中所有员工的薪资进行升序(从低到高)排序，显示员工姓名、薪资。

select name,sal from emp order by sal asc;

/* 在mysql中，通过limit进行分页查询：
limit (页码-1)*每页显示记录数, 每页显示记录数 */
– 查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 1 页。

select * from emp
limit 0, 3;
select * from emp
limit 3, 3;
select * from emp
limit 6, 3;
select * from emp
limit 9, 3;
-- 查询emp表中的所有记录，分页显示：每页显示3条记录，返回第 2 页。
select * from emp
limit 3, 3;

SQL关联查询（重点）

– 查询部门和部门对应的员工信息

select * from dept,emp;
-- 上面这种查询叫做"笛卡尔积查询":
如果同时查询两张表,其中一张表中有m条数据,
另外一张表中有n条数据,笛卡尔积查询的结果是 m*n条.
由于这个查询结果中存在大量错误的数据,所以我们一般不会直接使用这种查询。

– 查询部门和所有员工，如果员工没有所属部门，部门显示为null

select * from emp left join dept
on emp.dept_id=dept.id;

select * from dept right join emp
on emp.dept_id=dept.id;

-- union将两条SQL语句查询的结果合并在一起,并剔除重复记录
select * from dept left join emp
on emp.dept_id=dept.id
union
select * from dept right join emp
on emp.dept_id=dept.id;

– 列出薪资比’tony’薪资高的所有员工，显示姓名、薪资

select name,sal from emp
where sal > (select sal from emp where name='tony');

– 列出薪资比’大数据部’部门(已知部门编号为30)所有员工薪资都高的员工信息，显示员工姓名、薪资和部门名称。

select emp.name,sal,dept.name
from emp left join dept
on emp.dept_id=dept.id 
where sal > ( select max(sal) from emp where dept_id=30 );

– 列出在’大数据部’任职的员工，假定不知道’数据部’的部门编号，显示部门名称，员工名称。

select dept.name,emp.name
from dept,emp
where dept.id=emp.dept_id and 
	dept.name='大数据部';

– 列出每个部门薪资最高的员工信息，显示部门编号、员工姓名、薪资

select e.dept_id,e.name,t.maxsal
from emp e,(select dept_id,max(sal) maxsal from emp group by dept_id) t
where e.sal=t.maxsal 
	and e.dept_id=t.dept_id
union
select e.dept_id,e.name,max(sal)
from emp e where dept_id is null;
/*UNION 
	操作符用于合并两个或多个 SELECT 语句的结果集。
	请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。
	列也必须拥有相似的数据类型。
	同时，每个 SELECT 语句中的列的顺序必须相同。
*/

– 列出受雇日期早于直接上级的所有员工的编号、姓名、部门名称。

select e1.id ,e1.name ,d.name 
from emp e1 ,emp e2 ,dept d
where e1.topid =e2.id 
and e1.dept_id=d.id 
and e1.hdate<e2.hdate;  

– 查出至少有一个员工的部门。显示部门编号、部门名称、部门位置、部门人数。

-- 关联查询两张表(dept, emp)
-- 替换要显示的列和统计部门人数
select d.id,d.name ,d.loc ,count(*) 
from dept d , emp e 
where d.id=e.dept_id 	-- 如果没有这个条件就是笛卡尔查询
group by d.name;

– 列出最低薪资大于1500的各种职位，显示职位和该职位最低薪资

select job,min(sal)
from emp
group by job
having min(sal)>1500;

– (自查询)列出所有员工及其直接上级，显示员工姓名、上级编号，上级姓名

/* emp e1 员工表 emp e2 上级表
显示的列: e1.name, e2.id, e2.name
查询的表: emp e1, emp e2
关联条件: e1.topid=e2.id
 */
select e1.name, e2.id, e2.name
from emp e1, emp e2
where e1.topid=e2.id;

补充

• SQL语句的执行顺序：
  from… – 确定要查询的是哪张表 (定义表别名)
  where… – 从整张表的数据中进行筛选过滤
  select… – 确定要显示哪些列 (定义列别名)
  group by… – 根据指定的列进行分组
  order by… – 根据指定的列进行排序
• where和having都用于筛选过滤，但是：
  (1) where用于在分组之前进行筛选, having用于在分组之后进行筛选
  (2) 并且where中不能使用列别名, having中可以使用别名
  (3) where子句中不能使用列别名(可以使用表别名), 因为where子句比select先执行!!
• 左外连接和右外连接查询：
  (1) 左外连接查询：是将左边表中所有数据都查询出来, 如果在右边表中没有对应的记录, 右边表显示为null即可。
  (2) 右外连接查询：是将右边表中所有数据都查询出来, 如果在左边表中没有对应的记录, 左边表显示为null即可。