MySql数据库，SQL语句小结

青旅半醒 2023-06-10 10:26 20阅读 0赞

#

    • 备份数据库、恢复数据库
    • SQL基础
    • SQL基础查询
    • SQL模糊查询
    • SQL分组查询、聚合函数、排序查询
    • 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服务器中所有数据库

  1. show databases;

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

  1. use mysql;

– 查看已进入的库

  1. select database();

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

  1. show tables;

– 04.删除mydb1库

  1. drop database mydb1;

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

  1. drop database if exists mydb1;

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

  1. create database mydb1 charset utf8;

– 如果不存在则创建mydb1;

  1. create database if not exists mydb1 charset utf8;

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

  1. show create database mydb1;

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

  1. drop table if exists stu;

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

  1. use mydb1;
  2. drop table if exists stu;
  3. create table stu(
  4.     id int primary key auto_increment, -- id主键自增
  5.     name varchar(20), 
  6.     gender varchar(10),
  7.     birthday date,
  8.     score double
  9. );

– 09.查看stu学生表结构

  1. desc stu;

– 查看建表时的语句

  1. show create table stu;

– 10.插入一条记录

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

– 设置字段编码

  1. set names gbk;

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

  1. select * from stu;

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

  1. update stu set score=score+10;

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

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

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

  1. delete from stu;

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

  1. truncate table stu;

SQL基础查询

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

  1. select name,sal,bonus from emp;

– distinct 用于剔除重复的记录

  1. select distinct dept,job from emp;

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

  1. select name,sal+ifnull(bonus,0) from emp
  2. where sal+ifnull(bonus,0) > 3500;
  3. -- ---------------------设置别名
  4. select name as 姓名,sal+ifnull(bonus,0) as 总薪资 from emp
  5. where sal+ifnull(bonus,0) > 3500;
  6. -- --------------------- as可以省略
  7. select name 姓名,sal+ifnull(bonus,0) 总薪资 from emp
  8. where sal+ifnull(bonus,0) > 3500;
  10. -- ifnull(列, 值)函数: 判断指定的列是否包含null值, 如果有null值, 用第二个值替换null值
  11. -- 注意查看上面查询结果中的表头，如何将表头中的 sal+bonus 修改为 "总薪资"
  12. -- where子句中不能使用列别名

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

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

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

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

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

  1. select name,sal from emp
  2. where sal!=1400 and sal!=1600 and sal!=1800;
  3. select name,sal from emp
  4. where not(sal=1400 or sal=1600 or sal=1800);
  5. select name,sal from emp
  6. where sal not in(1400,1600,1800);

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

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

SQL模糊查询

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  1. select name,sal from emp order by sal asc;

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

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

SQL关联查询（重点）

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

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

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

  1. select * from emp left join dept
  2. on emp.dept_id=dept.id;
  4. select * from dept right join emp
  5. on emp.dept_id=dept.id;
  7. -- union将两条SQL语句查询的结果合并在一起,并剔除重复记录
  8. select * from dept left join emp
  9. on emp.dept_id=dept.id
  10. union
  11. select * from dept right join emp
  12. on emp.dept_id=dept.id;

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

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

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

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

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

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

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

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

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

  1. select e1.id ,e1.name ,d.name 
  2. from emp e1 ,emp e2 ,dept d
  3. where e1.topid =e2.id 
  4. and e1.dept_id=d.id 
  5. and e1.hdate<e2.hdate;

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

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

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

  1. select job,min(sal)
  2. from emp
  3. group by job
  4. having min(sal)>1500;

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

  1. /* emp e1 员工表 emp e2 上级表
  2. 显示的列: e1.name, e2.id, e2.name
  3. 查询的表: emp e1, emp e2
  4. 关联条件: e1.topid=e2.id
  5.  */
  6. select e1.name, e2.id, e2.name
  7. from emp e1, emp e2
  8. 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即可。

发表评论

表情：
评论列表 （有 0 条评论，20人围观）

还没有评论，来说两句吧...

相关阅读