——数据库进阶（实操篇）

导入批处理文件*.sql

• 下载emp.zip 解压得到emp.sql 文件 把文件放到某个磁盘的根目录,比如F盘（文件内容在https://blog.csdn.net/CNShork/article/details/120564772）

• 在终端中执行 source 路径; 即可把emp.sql中的数据库/表/数据 导入到自己的mysql里面

• 测试:

  show databases; //会显示多了一个 empdb

  use empdb; //使用数据库

  show tables; // 里面有emp和dept两个表

数据类型

1. 整数: int(m)和bigint bigint相当于Java中的long , m代表显示长度, m=10 存 18 查询时显示 0000000018 , 需要和zerofill关键字结合使用

   举例:

   create table t1(age int(10) zerofill);
   
   insert into t1 values(18);
   
   select * from t1; (在终端中测试)

2. 浮点数: double(m,d) m代表总长度,d代表小数长度 , 35.342 m=5 d=3 ,超高精度浮点数 decimal(m,d) ,当涉及到超高精度运算时使用 

   create table t2(money double(5,3));
   
   insert into t2 values(55.234); //正常
   
   insert into t2 values(55.2345678); //四舍五入
   
   insert into t2 values(555.235); //规则不符 出异常

3. 字符串

   • char(m): 固定长度, m=10 存abc 所占长度为10, 执行效率略高于可变长度, 当需求中长度是固定的值时使用. 最大长度为255
   • varchar(m):可变长度, m=10存abc所占长度为3, 更节省空间, 最大长度65535 ,规范保存255以内的数据 超过255的建议使用text
   • text(m): 可变长度, 最大长度65535

4. 时间

   • date: 只能保存年月日
   • time: 只能保存时分秒
   • datetime: 保存年月日时分秒 , 默认值为null, 最大值9999-12-31
   • timestamp(时间戳距离1970年1月1日毫秒数): 保存年月日时分秒,默认值为当前系统时间, 最大值2038-1-19 

   举例:

   create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
   
   insert into t_date values("2021-9-29",null,null,null);
   
   insert into t_date values(null,"14:48:22","1985-11-22 10:20:30",null);
   
   select * from t_date;

主键约束 primary key

create table t(id int primary key,name varchar(20));

insert into t values(1,"张三");

insert into t values(2,"李四");

insert into t values(1,"王五"); //报错

ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY' 主键值重复错误

insert into t values(null,"赵六"); //报错

ERROR 1048 (23000): Column 'id' cannot be null

主键约束+自增 auto_increment

create table t3(id int primary key auto_increment,name varchar(20));

insert into t3 values(null,"aaa");

insert into t3 values(null,"bbb");

insert into t3 values(10,"ccc");

insert into t3 values(null,"ddd");

delete from t3 where id>=10;

insert into t3 values(null,"eee");

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

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

   select * from emp where sal<=3000;

2. 查询1号部门的员工姓名和工作

   select name,job from emp where dept_id=1;

3. 查询不是程序员的员工姓名 工资和工作(两种写法)

   select name,sal,job from emp where job!="程序员";
   
   select name,sal,job from emp where job<>"程序员";

4. 查询有奖金的员工姓名和奖金 

   select name,comm from emp where comm>0;

与and或or非not

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

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

2. 查询是程序员或者工资等于5000的员工信息

   select * from emp where job="程序员" or sal=5000;

3. 查询出CEO和项目经理的名字

   select name from emp where job="CEO" or job="项目经理";

4. 查询奖金是500的销售信息 

   select * from emp where comm=500 and job="销售"

between x and y

• 查询两者之间 包含x和y

1. 查询工资在2000到3000之间的员工信息

   select * from emp where sal>=2000 and sal<=3000;
   
   select * from emp where sal between 2000 and 3000;

2. 查询工资在2000到3000之外的员工信息

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

in

• 当查询某个自动的值为多个值的时候使用

1. 查询工资等于1500,3000和5000的员工信息

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

模糊查询like

• _: 代表1个未知字符
• %: 代表0或多个未知字符
• 举例:
  • 以x开头 x%
  • 以x结尾 %x
  • 包含x %x%
  • 第二个是x _x%
  • 以x开头倒数第3个是y x%y__

1. 查询姓孙的员工信息

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

2. 查询工作中第二个字是售的员工信息

   select * from emp where job like "_售%";

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

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

4. 查询名字中包含僧的员工并且工资高于2000的员工信息

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

5. 查询1号和2号部门中工作以市开头的员工信息

   select * from emp where dept_id in(1,2) and job like "市%";

6. 查询有领导的员工中是经理的员工姓名

   select name from emp where manager is not null and job like "%经理%";

排序 order by

• 格式: order by 排序字段名 asc(默认升序)/ desc(降序)

1. 查询每个员工的姓名和工资 按照工资升序排序

   select name,sal from emp order by sal;
   
   select name,sal from emp order by sal desc; 降序

2. 查询1号部门的姓名和工资 并按照工资降序排序

   select name,sal from emp where dept_id=1 order by sal desc;

3. 查询工资高于1000的员工姓名,工资和部门编号,按照部门编号升序排序,如果部门编号一致则按照工资降序排序 (多字段排序)

   select name,sal,dept_id from emp where sal>1000 order by dept_id,sal desc;

分页查询 limit

• 格式: limit 跳过的条数(跳过的页数*每页的条数),请求的条数(每页的条数)
• 举例: 
  • limit 0,5 请求第1页的5条数据
  • limit 5,5 请求第2页的5条数据
  • limit 70,10 请求第8页的10条数据
  • limit 40,8 请求第6页的8条数据

1. 查询员工姓名和工资,按照工资升序排序, 查询第一页的5条数据

   select name,sal from emp order by sal limit 0,5;

2. 查询员工姓名和工资,按照工资升序排序, 查询第2页的5条数据

   select name,sal from emp order by sal limit 5,5;

3. 查询员工姓名和工资,按照工资升序排序, 查询第3页的3条数据

   select name,sal from emp order by sal limit 6,3;

4. 查询1号部门工资最高的员工姓名和工资

   select name,sal from emp where dept_id=1 order by sal desc limit 0,1;

别名

select name as "名字" from emp;

select name "名字" from emp;

select name 名字 from emp;

数值计算 + - * / %

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

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

2. 让1号部门的员工涨薪5块钱 

   update emp set sal=sal+5 where dept_id=1;

聚合函数

• 聚合函数可以对查询的多条数据进行统计查询
• 统计方式: 平均值, 最大值 , 最小值 , 求和, 计数 

1. 平均值avg(字段名)

   • 查询1号部门的平均工资

     select avg(sal) from emp where dept_id=1;

2. 最大值max(字段名)

   • 查询1号部门的最高工资

     select max(sal) from emp where dept_id=1;

3. 最小值min(字段名)

   • 查询1号部门的最低工资

     select min(sal) from emp where dept_id=1;

4. 求和sum(字段名)

   • 查询1号部门的工资总和

     select sum(sal) from emp where dept_id=1;