SQL分类
用来定义数据库对象
查询
查询所有数据库
查询当前数据库
创建
删除
使用
查询当前数据库所有表
查询表结构
查询指定表的建表语句
1.3DDL表操作-创建
MySQL中的数据类型有很多,主要分为三类,数值类型,字符串类型,日期时间类型。
添加字段
修改数据类型
修改字段名和字段类型
删除字段
修改表名
删除表
删除指定表,并重新创建该表
注意:在删除表时,表中全部数据也会被删除。
DML用来对数据库中表的数据记录进行增删改查操作。
1.给指定字段添加数据
2.给全部字段添加数据
3.批量添加数据
注意:
插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期类型数据应该包含在引号中。
插入的数据大小,应该在字段的规定范围内。
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
注意:delete语句的条件可以有,也可以没有,如果没有条件,则会删除整张表的所有数据。
delete语句不能删除某一个字段的值(可以使用update)
数据查询语言,用来查询数据库中表的记录。
查询关键字:select
基本语法
基本查询
条件查询(where)
聚合函数(count,max,min,avg,sum)
分组查询(group by)
排序查询(order by)
分页查询(limit)
1,查询多个字段
select 字段1,字段2,字段3… from 表名;
select * from 表明;
2.设置别名
3.去除重复记录
注意:*号代表查询所有字段,在实际开发中尽量少用(不直观,影响效率)。
语法
条件
介绍
将一列数据作为整体,进行纵向计算。
常见聚合函数
语法
**注意:**null值不参与所有聚合函数运算。
语法
where与having区别
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组;而having是分组之后对结果进行过滤。
判断条件不同:where不能对聚合函数进行判断,而having可以。
**注意:**执行顺序:where>聚合函数>having.
分组之后,查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义。
语法
排序方式:
ASC:升序(默认值)
DESC:降序
**注意:**如果是多个字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
语法
注意:
起始索引从0开始,起始索引=(查询页码-1)*每页显示记录数。
分页查询是数据库的方言,不同的数据库有不同的实现,MySQL中是LIMIT.
如果查询的是第一页数据,起始索引可以省略,直接简写为limit 10;
1.查询年龄为20,21,22,23 岁的员工信息。
2.查询性别为"男",并且年龄在20-24岁(含)以内的性为为三个字的员工
3.统计员工表中,年龄小于60岁,男性员工和女性员工的人数。
4.查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按照入职时间降序排序
5.查询性别为男,且年龄在20-40岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄系统按入职时间升序排序。
查询用户
创建用户
修改用户密码
删除用户
注意:
主机名可以使用%通配。
这类SQL开发人员操作的比较少,主要是DBA(数据库管理员)使用。
查询权限
授予权限
撤销权限
注意:
多个权限之间,使用逗号分隔
授权时,数据库名和表名可以使用*进行通配,代表所有。
函数是值一段可以直接另一段程序调用的程序或代码。
MySQL中内置了很多字符串函数,常用的几个如下:
(1)CONCAT函数
(2)LOWER函数
(3)UPPER函数
(4)LPAD函数
(5)RPAD函数
(6)TRIM函数
(7)SUBSTRING函数
由于业务需求变更,企业员工的工号,统一为5位数,目前不足5位数的全部在前面补0,比如1号员工的工号应该为00001。
常见的数值函数如下:
(1)ceil函数
(2)FLOOR函数
(3)MOD函数
(4)RAND函数
(5)ROUND函数
案例:通过数据库函数,生成一个六位数的随机验证码。
(1)CURDATE
(2)CURTIME
(3)NOW
(4)YEAR
(5)MONTH
(6)DAY
(7)DATE_ADD
(8)DATEDIFF
案例:查询所有员工的入职天数,并根据入职天数倒叙排序。
流程控制函数也是很常用的一类函数,可以在SQL语句中实现条件的筛选,从而提高查询的效率。
1if
2ifnull
3case when then else end
需求:查询emp表的员工姓名和工作地址(北京|上海—》一线城市,其他–》二线城市)
– 案例: 统计班级各个学员的成绩,展示的规则如下: – >= 85,展示优秀 – >= 60,展示及格 – 否则,展示不及格
概述:约束是作用于表中字段上的规则,用于限制存储在表中的数据。
目的:保证数据库中数据的正确,有效性和完整性。
分类:
注意:约束是作用于表中字段上的,可以在创建表/修改表的时候添加约束。
案例:根据需求,完成表结构的创建
插入数据
概念:外键用来让两张表的数据之间建立连接,从而保证数据的一致性和完整性。
注意:目前上述的两张表,在数据库后面,并未建立外键关联,所以是无法保证数据的一致性和完整性的。
语法:
添加外键
删除外键
创建部门表
i添加数据
创建emp表
插入数据
添加外键
删除外键
删除/更新行为
– 外键的删除和更新行为
概述:项目开发中,在进行数据据表结构设机时,会根据业务需求及业务模块之间的关系,分析并设机表结构,由于业务之间相互关联,所以以各个表结构之间也存在着各种联系,基本上分为三种:
一对多(多对一)
案例:部门与员工的关系
关系:一个部门对应多个员工,一个员工对应一个部门
实现:在多的一方建立外键,指向一的一方的主键
多对多
案例:学生与课程的关系
关系:一个学生可以选修多门课程,一门课程也可以供多个学生选择
实现:建立第三张中间表,中间表至少包含两个外键,分别关联两方主键
一对一
案例:用户与用户详情的关系
关系:一对一关系,多用于单表拆分,将一张表的基础字段放在一张表中,其他详细字段放在另一张表中,以提升操作效率。
实现:在任意一方加入外键,关联另一方的主键,并且设置外键为唯一的(UNIQUE)
概述:指从多张表中查询数据
笛卡尔积:笛卡尔积是指在数序中,两个集合A集合B的所有组成情况。(在多表查询时,需要消除无效的笛卡尔积)
多表查询–笛卡尔积
连接查询
内连接:相当于查询A,B交集部分数据
外连接:
左外连接:查询左表所有数据,以及两张表交集部分数据
右外连接:查询右表所有数据,以及两张表交集部分数据
子连接:当前表与自身的连接查询,自连接必须使用表别名
子查询
7.2.1内连接查询语法
隐式内连接
–内连接演示
–1.查询每一个员工姓名,及关联的部门名称(隐式内连接实现)
–表结构:emp,dept
–连接条件:e.dept_id=d.id
显示内连接
内连接查询的时两张表交集的部分
2.查询每一个员工的姓名,及关联的部门的名称(显示内连接实现)–INNER JOIN …ON…
–表结构:emp,dept
–连接条件:emp.dept_id=dept.id
7.2.2外连接查询语法
左外连接
相当于查询表1(左表)的所有数据包含表1和表2交集部分的数据
–外连接演示
–1.查询emp表的所有数据,和对应的部门信息(左外连接)
–表结构:emp,dept
–连接条件:emp.dept_id=dept.id
右外连接
相当于查询表2(右表)的所有数据包含表1和表2交集部分数据。
查询dept表的所有数据,和对应的员工信息(右外连接)
7.2.3连接查询-自连接
自连接查询语法:
自连接查询,可以是内连接查询,也可以是外连接查询。
–查询员工,及其所属领导的名字
–表结构:emp
–2.查询所有员工emp,及其领导的名字emp,如果员工没有领导,也需要查询出来。
7.2.4联合查询-union,union all
对于union查询,就是把多次查询的结果合并起来,形成一个新的查询结果集。
对于联合查询的多张表的列数必须保持一致,字段类型也要保持一致。
union all会将全部的数据直接合并在一起,union会对合并之后的数据去重。
–union all,union
–1.将薪资低于5000的员工,和年龄大于50岁的员工全部查询出来。
不去重
去重
7.2.5子查询
概念:SQL语句中嵌套select语句,称为嵌套查询,又称子查询。
子查询外部的语句可以是insert/update/delete/select的任何一个。
根据子查询结果不同,分为:
(1)标量子查询(子查询结果为单个值)
子查询返回结果是单个值(数字,字符串,日期等),最简单的形式,这种子查询称为“标量子查询”
常用的操作符:= <> > >= < <=
–标量子查询 –1.查询"研发部"的所有员工信息 –a.查询"研发部"部门ID
–b.根据销售部部门id,查询员工信息
–2查询在"韦一笑"入职之后的员工信息 –a.查询"韦一笑"的入职日期
–b.查询指定日期之后入职的员工信息
(2)列子查询(子查询结果为一列)
子查询返回的结果是一列(可以是多行),这种子查询称为列子查询。
常用的操作符:IN,NOT IN,ANY,SOME,ALL
–列子查询 –1.查询"销售部"和"市场部"的所有员工信息。 –a.查询"销售部"和"市场部"的部门id
–b.根据部门id,查询员工信息
–2.查询比财务部门所有人工资都高的员工信息 –a.查询所有财务部人员工资
–b比财务部所有人工资都高的员工信息
–3查询比研发部其中任意一人工资高的员工信息 –a.查询研发部所有人工资
–b.比研发部其中任意一人工资高的员工信息。
(3)行子查询(子查询结果为一行)
子查询返回结果是一行(可以是多列),这种子查询称为“行子查询”。
常用的操作符:=,<>,IN,NOT IN
–行字查询 –1.查询与"张无忌"的薪资及直属领导相同的员工信息; –a.查询"张无忌"的薪资及直属领导
–b.查询与"张无忌"的薪资及直属领导相同的员工信息:
(4)表子查询(子查询结果为多行多列)
子查询返回的结果是多行多列,这种子查询称为“表子查询”。
常用的操作符:IN
根据子查询位置,分为:WHERe之后,FROM之后,SELECt之后。
–表子查询 –1.查询与"鹿杖客",“宋远桥"的职位和薪资相同的员工信息。 –a.查询"鹿杖壳”,"宋远桥"的职位和薪资 SELECT job,salary from emp where name=‘鹿杖客’ or name=‘宋远桥’;
–b.查询"鹿杖壳","宋远桥"的职位和薪资相同的员工信息 SELECt * from emp where (job,salary)in(SELECt job,salary from emp where name=‘鹿杖客’ or name=‘宋远桥’);
–2.查询入职日期是"2006-01-01"之后的员工信息,及其部门信息 –a.入职日期是:"2006-01-01"之后的员工信息 SELECt * from emp where entrydate>‘2000-01-01’;
–b.查询这部分员工,对应的部门信息; SELECt e.,d. from (SELECt * from emp where entrydate>‘2000-01-01’) e LEFT JOIN dept d on e.dept_id=d.id;
7.2.6根据需求,完成SQL语句的编写
1.查询员工的姓名,年龄,职位,部门信息。
2.查询年龄小于30岁的员工姓名,年龄,职位,部门信息。
3.查询拥有员工的ID,部门名称。
4.查询所有年龄大于40岁的员工,及其归属的部门名称,如果员工没有分配部门,也需要展示出来。
5.查询所有员工的工资等级。
6.查询“研发部”所有员工的信息集器工资等级。
7.查询“研发部”员工的平均工资。
8.查询工资比“灭绝”高的员工信息。
a. 查询 “灭绝” 的薪资
– b. 查询比她工资高的员工数据
9.查询比平均薪资高的员工信息。
– a. 查询员工的平均薪资
– b. 查询比平均薪资高的员工信息
10.查询低于本部门平均工资的员工信息。
– a. 查询指定部门平均薪资 1
– b. 查询低于本部门平均工资的员工信息
11.查询所有的部门信息,并统计部门的员工人数。
12.查询所有学生的选课情况,展示出学生名称,学号,课程名称.
1多表关系
一对多:在多的一方设置外键,关联一的一方的主键。
多对多:建立中间表,中间表包含两个外键,关联两张表的主键。
一对一:用于表结构拆分,在其中任何一方设置外键,关联另一方的主键。
2多表查询
内连接
隐式:select…from 表A,表B where 条件…
显示:select…from 表A inner join 表B ON 条件…
外连接
左外:select …from 表A left join 表B ON 条件…
右外:select…from 表A right join 表B ON 条件…
自连接:select…from 表A 别名1,表A 别名2 where 条件…
子查询:标量子查询,列子查询,行子查询,表子查询
事务简介:事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败。
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务。
转账操作(张三给李四转账1800)
查看/设置事务提交方式
提交事务
回滚事务
案例
事务操作
查看/设置事务提交方式
提交事务
回滚事务
原子性(Atomicity):事务是不可分隔的最小操作单元,要么全部成功,要么全部失败。
一致性(Consistency):事务完成时,必须使所有的数据都保持一致状态。
隔离性(Isolation):数据库系统提供隔离机制,保证事务在不受外部并发操作影响的独立环境下运行。