SQL练习

2023-04-10  本文已影响0人  蛋上有皱纹

SQL

和数据库软件建立连接

  1. 通过命令行/终端和数据库软件建立连接
    • 首先检查MySQL/MariaDB服务是否开启 : 在我的电脑/此电脑上右键-->管理 , 参见下图
      [图片上传失败...(image-ea7904-1681195634436)]
    • 从开始菜单中找到MySQL/MariaDB-->MySQL Client 打开客户端 , 输入自己的密码显示Welcome ..... 说明建立好了连接
    • 退出指令 : exit
    • 建立连接指令 : mysql -uroot -p 回车后输入密码 , 再回车
  2. 通过三方的可视化工具和数据库软件建立连接

数据库相关

  1. 查询所有数据库

    show databases;

  2. 创建数据库

  1. 查询数据库信息
  1. 删除数据库
  1. 使用数据库

数据库相关练习题

  1. 创建 mydb1和mydb2 数据库 字符集分别为utf8和gbk

    create database mydb1 charset = utf8;
create database mydb2 charset = gbk;

  2. 查询所有数据库检查是否创建成功

    show databases;

  3. 检查两个数据库的字符集是否正确

    show create database mydb1;
show create database mydb2;

  4. 先使用mydb2 再使用 mydb1

    use mydb2;
use mydb1;

  5. 删除两个数据库

    drop database mydb1;
drop database mydb2;

表相关

  1. 创建表
  1. 查询所有表
show tables;
  1. 查询表信息
  1. 查询表字段
  1. 删除表
  1. 修改表名

表相关练习题

  1. 创建数据库mydb3 字符集gbk并使用

    create database mydb3 charset = gbk;
use mydb3;

  2. 创建t_hero英雄表 , 有名字和年龄字段 , 默认字符集

    create table t_hero(name varchar(50),age int);

  3. 修改表名为hero

    rename table t_hero to hero;

  4. 查看hero表的字符集

    show create table hero;

  5. 查询hero表字段

    desc hero;

  6. 删除表hero

    drop table hero;

  7. 删除数据库mydb3

    drop database mydb3;

表相关(续)

使用db1数据库 use db1;

  1. 添加表字段
  1. 删除表字段
  1. 修改表字段

表相关练习题:

  1. 创建数据库mydb4 , 字符集utf8并使用

    create database mydb4 charset = utf8;
use mydb4;

  2. 创建teacher表 , 具有姓名字段

    create table teacher (name varchar(50));

  3. 添加表字段 : 最后添加age , 最前面添加id , age前添加salary工资

    alter table teacher add age int;
alter table teacher add id int first;
alter table teacher add salary int after name;

  4. 删除age字段

    alter table teacher drop age;

  5. 修改表名为t

    rename table teacher to t;

  6. 删除表t

    drop table t;

  7. 删除数据库mydb4

    drop database mydb4;

数据相关

操作数据必须保证已经使用了某个数据库并且已经准备好了保存数据的表

create database mydb5 charset = utf8;
use mydb5;
create table person(name varchar(50),age int);
  1. 往表中插入数据
                          <img src="10第三阶段笔记/de5272b726a94a24ad1f90536fa9498c.png" alt="在这里插入图片描述" style="zoom:33%;" />
  1. 查询数据
  1. 修改数据
  1. 删除数据

综合练习题

  1. 创建数据库day1db , 字符集utf8并使用

    create database day1db charset = utf8;
use day1db;

  2. 创建t_hero表, 有name字段 , 字符集utf8

    create table t_hero (name varchar(50));

  3. 修改表名为hero

    rename table t_hero to hero;

  4. 最后面添加价格字段money, 最前面添加id字段, name后面添加age字段

    alter table hero add money int;
alter table hero add id int first;
alter table hero add age int after name;

  5. 表中添加以下数据 : 1,李白,50,6888 2,赵云,30,13888 3,刘备,25,6888

    insert into hero values(1,"李白",50,6888),(2,"赵云",30,13888),(3,"刘备",25,6888);

  6. 查询价格为6888的英雄名

    select name from hero where money = 6888;

  7. 修改刘备年龄为52岁

    update hero set age = 52 where name = "刘备";

  8. 修改年龄小于等于50岁的价格为5000

    update hero set money = 5000 where age <= 50;

  9. 删除价格为5000的信息

    delete from hero where money = 5000;

  10. 删除表hero, 删除数据库day1db

    drop table hero;
drop database day1db;

主键约束

主键约束+自增

SQL分类

数据类型

导入*.sql 批处理文件

去重distinct

  1. 查询员工表中有哪几种不同的工作?

    select job from emp;
select distinct job from emp;

  2. 查询员工表中有哪几个不同的部门id?

    select distinct dept_id from emp;

is null 和 is not null

  1. 查询没有上级领导的员工姓名

    select name from emp where manager is null;

  2. 查询有上级领导的员工姓名

    select name from emp where manager is not null;

and 和 or

  1. 查询1号部门工资高于2000的员工信息

    select * from emp where dept_id = 1 and sal > 2000;

  2. 查询3号部门的员工或者工资等于5000的员工信息

    select * from emp where dept_id = 3 or sal = 5000;

  3. 查询孙悟空和猪八戒的员工信息

    select * from emp where name = "孙悟空" or name = "猪八戒";

比较运算符 > < >= <= = !=和<>

  1. 查询工资大于等于3000的员工信息

    select * from emp where sal >= 3000;

  2. 查询工作不是程序员的员工信息(两种写法)

    select * from emp where job != "程序员";
select * from emp where job <> "程序员";

between x and y 两者之间

查询工资在2000到3000之间的员工信息
select * from emp where sal >= 2000 and sal <= 3000;
select * from emp where sal between 2000 and 3000;

不在2000到3000之间的员工信息
select * from emp where sal <2000 or sal > 3000;
select * from emp where sal not between 2000 and 3000;

in关键字

select * from emp where sal = 5000 or sal = 1500 or sal = 3000;
select * from emp where sal in (5000,1500,3000);

综合练习题

  1. 查询1号部门有哪几种不同的工作?

    select distinct job from emp where dept_id = 1;

  2. 查询1号部门中有上级领导的员工信息

    select * from emp where dept_id = 1 and manager is not null;

  3. 查询工作是程序员,销售和人事的员工信息

    select * from emp where job in ("程序员","销售","人事");

  4. 查询有奖金的员工信息

    select * from emp where comm > 0;

  5. 查询工资不在1000-2000之间的员工信息

    select * from emp where sal not between 1000 and 2000;

模糊查询 like

  1. 查询姓孙员工信息

    select * from emp where name like "孙%";

  2. 查询名字以精结尾的员工姓名

    select name from emp where name like "%精";

  3. 查询工作第二个字是售的员工姓名和工作

    select name,job from emp where job like "_售%";

  4. 查询名字中包含僧并且工资大于2000的员工姓名和工资

    select name,sal from emp where name like "%僧%" and sal > 2000;

排序 order by

  1. 查询员工姓名和工资,按照工资升序

    select name,sal from emp order by sal;

  2. 查询员工姓名和工资,按照工资降序

    select name,sal from emp order by sal desc;

  3. 查询每个员工的姓名,工资和部门id按照部门id升序排序,如果部门id相同则按照工资降序排序

    select name,sal,dept_id from emp order by dept_id,sal desc;

分页查询

  1. 查询按照工资升序排序的第一页的3条数据

    select * from emp order by sal limit 0,3;

  2. 按照入职日期(hiredate) 升序排序 查询第3页的3条数据

    select * from emp order by hiredate limit 6,3;

  3. 查询工资最高的员工信息

    select * from emp order by sal desc limit 0,1;
select * from emp order by sal desc limit 1;

  4. 查询按照工资降序第2页的5条数据

    select * from emp order by sal desc limit 5,5;

综合练习题

  1. 查询员工表中3号部门工资高于1500的员工信息

    select * from emp where dept_id = 3 and sal > 1500;

  2. 查询2号部门员工或者没有领导的员工信息

    select * from emp where dept_id = 2 or manager is null;

  3. 查询有领导的员工姓名,工资按照工资降序排序

    select name,sal from emp where manager is not null order by sal desc;

  4. 查询2号和3号部门的员工姓名和入职日期hiredate , 按照入职日期降序排序

    select name,hiredate from emp where dept_id in (2,3) order by hiredate desc;

  5. 查询名字中包含僧和包含精的员工姓名

    select name from emp where name like "%僧%" or name like "%精%";

  6. 查询工资高于2000的工作有哪几种?

    select distinct job from emp where sal > 2000;

  7. 查询工资升序第4页的2条数据

    select * from emp order by sal limit 6,2;

别名

给查询字段起别名

select name from emp;
select name as "姓名" from emp;
select name "姓名" from emp;
select name 姓名 from emp;

数值计算 + - * / %

  1. 查询每个员工的姓名 , 工资和年终奖(5个月的工资)

    select name,sal,5*sal 年终奖 from emp;

  2. 给3号部门的员工每人涨薪5块钱

    udpate emp set sal = sal + 5 where dept_id = 3;

聚合函数

  1. 平均值avg(字段名)
  1. 最大值max(字段名)
  1. 最小值min(字段名)
  1. 求和sum(字段名)
  1. 计数count(*)

综合练习

  1. 查询1号部门名字中包含僧的员工信息

    select * from emp where dept_id = 1 and name like "%僧%";

  2. 查询2号和3号部门中工资高于1500的员工人数

    select count(*) from emp where dept_id in (2,3) and sal > 1500;

  3. 查询名字里面包含精的最高工资

    select max(sal) from emp where name like "%精%";

  4. 查询程序员的平均工资

    select avg(sal) from emp where job = "程序员";

  5. 查询销售的最低工资

    select min(sal) from emp where job = "销售";

  6. 查询有领导的员工人数

    select count(*) from emp where manager is not null;

  7. 查询3号部门的人数和平均工资,起别名为平均工资

    select count(*) 人数,avg(sal) 平均工资 from emp where dept_id = 3;

分组查询group by

  1. 查询每个部门的平均工资

    select dept_id,avg(sal) from emp group by dept_id;

  2. 查询每种工作的最高工资

    select job,max(sal) from emp group by job;

  3. 查询每个部门的最高工资

    select dept_id,max(sal) from emp group by dept_id;

  4. 查询每种工作的人数

    select job,count(*) from emp group by job;

  5. 查询每个部门工资高于2000的人数

    select dept_id,count(*) from emp where sal > 2000 group by dept_id;

  6. 查询每个部门有领导的员工的人数

    select dept_id,count(*) from emp where manager is not null group by dept_id;

having关键字

  1. 查询每个部门的平均工资要求平均工资大于2000

    select dept_id,avg(sal) from emp group by dept_id having avg(sal) > 2000;

  2. 查询每种工作的人数,只查询人数大于1的

    select job,count(*) from emp group by job having count(*) > 1;
select job,count(*) c from emp group by job having c > 1;

  3. 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400

    select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s > 5400;

  4. 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的

    select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a >= 2000;

各个关键字的顺序

select * from 表名 where 普通字段条件 group by 分组字段名 having 聚合函数条件 order by 排序字段名 desc limit 起始下标,请求条数;

子查询(嵌套查询)

use empdb;

  1. 查询工资大于2号部门平均工资的员工信息

    select avg(sal) from emp where dept_id = 2;
select * from emp where sal > (select avg(sal) from emp where dept_id = 2);

  2. 查询工资高于程序员最高工资的员工信息

    select max(sal) from emp where job = "程序员";
select * from emp where sal > (select max(sal) from emp where job = "程序员");

  3. 查询工资最高的员工信息

    select * from emp where sal = (select max(sal) from emp);

  4. 查询和孙悟空相同工作的员工信息

    select * from emp where job = (select job from emp where name = "孙悟空") and name != "孙悟空";

  5. 查询拿最低工资员工的同事们的信息(同事指同一部门)

    select * from emp where dept_id = (select dept_id from emp where sal = (select min(sal) from emp)) and sal != (select min(sal) from emp);

关联关系

  1. 如果需要同时查询多张表的数据使用关联查询
  2. 关联查询包括 : 等值连接,内连接和外连接
  3. 等值连接和内连接查询的是两个表的交集数据, 推荐使用内连接
  4. 如果需要查询一张表的全部和另外一张表的交集时 , 使用外连接,只需要掌握左外即可,因为表的位置可以交换
上一篇 下一篇

猜你喜欢

热点阅读