WEB——06.MySQL(2)

WEB——06.MySQL(2)

SQL分类

• DDL：数据定义语言，包括：数据库项和表相关的SQL
• DML：数据操作语言，包括，增删改查
• DQL：数据查询语言，只包括查询select
• TCL：事务控制语言，只包括和事务相关的内容
• DCL：数据控制语言，包含用户管理，权限分配相关SQL

*.sql批处理文件

在F盘根目录中->source 路径; source F:/emp.sql;

desc emp;

Field	Type	Null	Key	Default	Extra
id	int(4)	NO	PRI	NULL	auto_increment
----	------	----	----	----	--------------
name	varchar(10)	NO		NULL
----	-----------	----	----	----	----
job	varchar(9)	YES		NULL
----	----------	----	----	----
manager	int(4)	YES		NULL
-------	------	----	----	----	----
hiredate	date	YES		NULL
--------	----	----	----	----	----
sal	double(7,2)	YES		NULL
----	-----------	----	----	----	----
comm	double(7,2)	YES		NULL
----	-----------	----	----	----	----
dept_id	int(4)	YES		NULL
-------	------	----	----	----	----
select * from emp;

id	name	job	manager	hiredate	sal	comm	dept_id
1	孙悟空	销售	4	1980-12-17	800.00	NULL	1
----	------	----	----	----------	------	----	----
2	猪八戒	销售	4	1981-02-20	1600.00	300.00	1
----	------	----	----	----------	-------	------	----
3	沙僧	销售	4	1981-02-22	1250.00	500.00	1
----	----	----	----	----------	-------	------	----
4	唐僧	销售经理	8	1981-04-02	2975.00	NULL	1
----	----	--------	----	----------	-------	----	----
5	刘备	项目经理	NULL	1981-09-28	1250.00	1400.00	3
----	----	--------	----	----------	-------	-------	----
6	关羽	程序员	5	1981-05-01	2850.00	NULL	3
----	----	------	----	----------	-------	----	----
7	张飞	程序员	5	1981-06-09	2450.00	NULL	3
----	----	------	----	----------	-------	----	----
8	观音	CEO	NULL	1981-11-17	5000.00	NULL	1
----	----	----	----	----------	-------	----	----
9	白骨精	人事	8	1981-09-08	1500.00	0.00	2
----	------	----	----	----------	-------	----	----
10	蜘蛛精	人事	8	1981-12-03	950.00	NULL	2
----	------	----	----	----------	-------	----	----
11	黑熊怪	市场	8	1981-12-03	3000.00	NULL	2
desc dept;

Field	Type	Null	Key	Default	Extra

id	int(4)	NO	PRI	NULL	auto_increment
name	varchar(14)	NO	UNI	NULL
----	-----------	----	----	----	----
loc	varchar(13)	YES		NULL
----	-----------	----	----	----

select * from dept;

​ id name loc

1	神仙	天庭
2	妖怪	盘丝洞
----	----	------
3	普通人	北京
----	------	------
4	赛亚人	外星球

数据类型

• 整数：int(m)和bigint(m)，bigint相当于java中的long，m代表显示长度，m=5,存18，得到00018，需要结合zerifill关键字使用。

• 举例：

  • create table t1(age int(5) zerofill);
  • insert into values(18);
  • select * from t1;

• 浮点数：double(m,d) m代表总长度，代表小数长度 m=5,d=3 23.123

• 举例：

  • create table t2(price double(5,3));
  • insert into values(54.321);

• 字符串:

• char(m)：固定长度，m=5,存“abc”占5个字符长度，执行效率略高（0-255）

• varchar(m)：可变长度，m=5,存“abc”占3个字符长度，节省空间（0-65535），长度在255以内得建议使用。

• text(m)：可变长度（0-65535）

• 日期：

• date:年月日

• time:时分秒

• datatime:年月日时分秒

• timestamp(时间戳):

• 举例：

  • create table t3(t1 date ,t2 time,t3 datatime,t4 timestamp);
  • insert into t3 values(“2021-11-22”,“10:38:20”,null,null);
  • insert into t3 values(null,null,“2021-11-22 10:38:20”,null);

主键约束

• 主键：表示数据唯一性得字段成为主键
• 约束：创建表时，给表字段添加得限制条件
• 主键约束：限制主键得值 唯一且非空。
• 举例：
  • create table t4(id int primary key ,name varchar(10));
  • insert into t4 values(1,“张三”);
  • insert into t4 values(1,“李四”); 报错 重复数据
  • insert intot4 values(null,“李四”); 报错 不能为空

主键约束+自增

**自增规则：**从最大值开始

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

insert into t5 values(null,“王五”);

insert into t5 values(null,“小明”);

insert into t5 values(10,“小明”);

insert into t5 values(null,“小明”);

delete from t5 where id>10; 删除数据不会影响计数器

insert into t5 values(null,“小明”);

truncate table t5; 把表删除 并重新创建 此时计数器会清0

去重distinct

• 查询员工表中有几种不同得工作

​ select distinct job from emp;

• 查询员工表中有几个不同得id

  select distinct id from emp;

is null 和is not null

• 查询没有上级领导得员工姓名

​ select name from emp where manager is null;

and 和 or

• 多个条件同时满足使用and

• 多个条件满足一个就可以使用or

• 查询1号部门工资高于2000得员工信息

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

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

• 查询不是程序员得姓名和工资

  ​ select name ,sal from emp where job!=“程序员”;

两者之间between x and y

• 查询工资在2000到3000之间得员工信息

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

in关键字

• 查询工资为3000，1500，5000的员工信息

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

模糊查询like

• %:代表0或多个未知字符。
• _:代表1个未知字符

排序order by 字段名

• 格式：order by 字段名 asc(默认升序)/desc降序；
• 查询员工姓名和工资，按照工资升序

​ select name,sal from emp order by sal;

• 查询员工姓名和工资，按照工资降序

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

分页查询 limit

• 格式：limit 跳过的条数，请求的条数(每页的条数)

• 举例： 跳过的条数 = (请求页数-1)*每页页数

  • 第一页的5条数据 limit 0,5
  • 第一页的10条 limit 0,10
  • 第5页的10条 limit 40,10
  • 第8页的5条 limit 70,5

• 查询工资最高的前三个人的信息

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

别名

• select name as “名字” from emp;
• select name “名字” from emp;
• select name 名字 from emp;

数值计算

• 查询每个员工的姓名，工资和年终奖(3个月的工资)

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

• 给3号部门的员工没人涨薪5块钱

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

聚合函数

• 可以对查询的多条数据进行统计查询，方式包括:平均值，最大值，最小值，求和，计数

• 平均值avg(字段名)

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

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

• 最大值max(字段名)、最小值min(字段名)

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

• 求和sum(字段名)

​ select sum(sal) from emp where dept_id=2;

• 计数count(字段名或*)

• 查询工资高于2000的员工人数

  select count(*) from emp where sal>2000;

分组查询group by

• 将某个字段相同值的数据划分为一组，然后以组为单位进行统计查询

• 查询每个部门的平均工资
  select dept_id,avg(sal) from emp group by dept_id;

• 查询每种工作的平均工资
  select job,avg(sal) from emp group by job;

• 查询每个部门的最高工资
  select dept_id,max(sal) from emp group by dept_id;

• 查询每种工作的人数
  select job,count from emp group by job;

• 查询每个部门工资高于2000的人数
  select dept_id,count(*) from emp where sal>2000 group by dept_id;

• 查询每个部门有领导的员工的人数
  select dept_id,count(*) from emp where manager is not null group by dept_id;

  having关键字

• where后面只能写普通字段的条件,不能写聚合函数函数.

• having关键字 作用和where类似都是用来添加条件的, 但是having后面专门写聚合函数条件,而且having要和group by分组查询结合使用, 写在分组关键字的后面

• 查询每个部门的平均工资要求平均工资大于2000
  select dept_id,avg(sal) from emp group by dept_id having avg(sal)>2000 ;

  别名用法

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

• 查询每种工作的人数,只查询人数大于1 的
  select job,count(*) c from emp group by job having c>1;

• 查询每个部门的工资总和,只查询有领导的员工, 并且要求工资总和大于5400
  select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;

• 查询每个部门的平均工资, 只查询工资在1000到3000之间的,并且过滤掉平均工资低于2000的
  select dept_id,avg(sal) a from emp where sal between 1000 and 3000 group by dept_id having a>=2000;

• 各个关键字的顺序
  资总和,只查询有领导的员工, 并且要求工资总和大于5400
  select dept_id,sum(sal) s from emp where manager is not null group by dept_id having s>5400;

• 查询每个部门的平均工资, 只查询工资在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 跳过条数,请求条数;