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