数据库基础篇

SQL

SQL通用语法

1. SQL语句可以单行或者多行书写，以分号结尾

2. SQL语句可以用空格/缩进来增强语句的可读性

3. MySQL数据库的SQL语句不区分大小写，关键字建议大写

4. 注释:单行注释用--或者#

5: 多行注释/*   */      

SQL语句的分类

DDL： 数据定义语言，定义数据库对象（数据库，表，字段

DML： 数据操作语言，对数据库表中的数据进行增删改

DQL： 数据查询语言，用来查询数据库中表的记录

DCL： 数据控制语言，用来创建数据库用户，控制数据库的访问权限

DDL

Show Databases 查询数据库

Select Database 查询当前数据库

Create Database[If not exists]数据库名 [Default Charset]字符集[Collate 排序规则] 创建数据库

Drop Database 删除数据库

USE 数据库名字 使用数据库

DML

Show Tables 查询所有表

Desc 表名 查询表结构

Show Create Table 表名 查询指定表的建表语句

Creat table表名（ 创建表

字段1 字段1类型 comment 含义，

字段2 字段2类型 comment 含义

）comment表注释

Alter table 表名 ADD 字段名 类型 添加字段

Alter table 表名 Modify 字段名 新数据类型 修改数据类型

Alter table 表名 Change 旧字段名 新字段名里 类型 修改字段名和类型

Alter table 表名 Drop 字段名 删除字段

Alter table 表名 Rename to 新表名 修改表名

Drop table [IF EXISTS]表名 删除表

添加数据

insert into 表名（字段1，字段2.。。）values（值1，值2.。）[(值1，值2..)(..)]

修改数据

update 表名 set 字段1=值1，字段2=值2[where 条件]

删除数据

delete from 表名 [where. 条件]

DQL

数据查询语言，用来查询数据库中表的记录，关键词：select

基本查询：

查询多个字段

select 字段1，字段2…from 表名

select from 表名

设置别名

select 字段1 [AS 别名]，字段2[AS 别名2]..from 表名；

去除重复记录

select distinct 字段列表 from 表名

条件查询：

1语法 select 字段列表 from 表名 where 条件列表

2条件 比较运算符： > < = >= <= != between..and:在某个范围之内

in（）在in之后的列表中的值的一个 //其实就是多个or的简写 like占位符 ：模糊匹配（_匹配单个字符，%匹配多个字符。

is null ： 是null

逻辑运算符： and &&:并且 or || 或者 not ！：非，不是

例子：select * from employ where age=88; 其中*表示表中所有的字段

select * from employ where idcard is null;

select * from employ where name like ‘__’ //查询姓名为两个字的员工信息。 一个_代表一个字符

select * from employ where idcard like ‘%X’ //查询身份证号最后一位为X的员工信息   %代表多个字符

聚合函数  作用于表的每一列，即表的字段

语法：select 聚合函数 （字段列表）from 表名

count 统计数量。 Max  最大值。min  最小值。 avg  平均值。 Sum 求和。

例子：select count (*) from employ    计算每一个字段里数据的数量 select avg（age） from employ  计算年龄字段数据平均值

所有的null值不参与计算

分组查询

1语法：select 字段列表 from 表名 where 条件 group by 分组字段名 having 分组后过滤条件

where和having的区别

执行时机不同：where是分组之前进行过滤，不满足条件就不参与分组。

而having是分组之后对结果进行过滤

判断条件不同：where不能对聚合函数进行判断，而having可以

例子：select  gender, count(*)  *from employ group by gender;  //根据性别和性别数量做字段分组

  select address， count(*) from employ where age<45 group by address having count（*）>3;

//查询年龄小于45的员工，并根据工作地址进行分组，最后获取员工数大于3的工作地址

注意：

执行顺序：where >聚合函数>having

分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段无如何意义

排序查询

1语法：select 字段列表 from 表名 order by 字段1 排序方式，字段2，排序方式。

2排序方式

ASC 升序（默认）

DESC 降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序

例子：select * from employ order by age asc;     //根据年龄对公司的员工升序排序

select *from employ order by age asc, entrydate desc ;

//根据年龄升序，如果你年龄相同，根据入职时间降序

分页查询

1.语法：select 字段列表 from 表名 limit 起始索引，查询记录数；

起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数

分页查询是数据库的方言，不同的数据库有不同的实现，MySQL中是LIMIT

如果查询的是第一页，起始索引可以省略，直接简写完为limit 10.

例子：select * from employ limit 1，10 //查询第二页的前十个数据

编写顺序 select  字段列表 from 表名 where 条件列表 group by 分组字段列表

having 分组条件列表 order by 排序字段列表 limit 分页参数

执行顺序 from表 ，where条件，group字段，select字段，having条件，order字段，limit分页参数

DCL

数据控制语言，用来管理数据库用户，控制数据库的访问权限

1.查询用户

use mysql

select from user

2.创建用户

create user ‘用户名’@‘主机名’ identified by ‘密码’

3. 修改用户密码

alter use ‘用户名’@‘主机名’ identified with mysql_native_password ‘新密码’

4. 删除用户

drop user ‘用户名’@‘主机名’；

注意：主机名可以用%号通配

这类SQL开发人员操作的比较少，注意是DBA（数据库管理员）使用

权限控制。all    所有权限 insect  添加权限 delete 删除权限    create   创建数据库/表

select  查询权限 update 修改权限 alter   修改表       Drop      删除数据库/表/视图

1查询权限：show     grants   for    ‘用户名’@‘主机名’；

2授予权限：grant    权限列表 on 数据库名.表名  to     ‘用户名’@‘主机名’；

3撤销权限：revoke 权限列表 on 数据库名.表名  from ‘用户名’@‘主机名’;

函数

函数是指一段可以直接被另一段程序调用的程序和代码

字符串函数：常用：select     concat（s1，s2..） 字符串拼接s1,s2,s3

select lower  (str) 将字符串转小写

select upper（str 将字符串转大写

lpad（str ,n, pad) 左填充，用pad对str的左边填充，达到n个字符长度

Rpad（str, n,  pad) 右填充，用pad对str右填充

trim  （str） 去掉字符串头部和尾部的空格

substring（str，start，len） 返回字符串str从start起len个长度的字符串

数值函数 ：常用： ceil(x) 向上取整

floor（x） 向下取整

mod（x，y） 返回x/y的模

rand（） 返回0-1的随机数

round（x，y） 求参数x的四舍五入的值，保留y位小数

日期函数 ：常用： curdate（） 返回当前日期

curtime（） 返回当前时间

now（） 返回当前日期和时间

year（date） 获取指定date的年份

month（date） 获取指定date的月份

day（date） 获取指定date的日期

date_add(date , interval expr type) 返回一个日期/时间值加上一个时间间隔expr后的时间值

datediff（date1，date2） 返回起始时间date1和date2之间的天数

流程函数 ： if(value,t,f) 如果value为true ，则返回t，否则返回f

ifnull（value1，value2） 如果value1不为空，返回value1，否则返回value2

case when value1 then res1…else default end 如果value1为true，返回res1….否则返回default默认值 case expr when val1 then res1…else default end 如果expr的值等于value1，返回res1，…否则返回default

例子： 从employ表中以姓名和工作地址为列，把员工的工作地址筛出，赋一线城市二线城市

select name,

（case workaddress when ‘北京’ then ‘一线城市’when‘上海’then ‘一线城市’else‘二线城市’end） as ‘工作地址’

from employer;

从score表中给数学成绩大于85的人赋以优秀，大于60赋以及格，其他赋不及格

select  id，name，

（case when math>=85then‘优秀’when math>=60 then ‘及格’else‘不及格’ end)’数学’

from score；

约束

约束是作用于表中字段上的规则，用于限制存储在表中的数据

目的：保证数据库中数据的正确性，有效性和完整性

常见分类：

  约束 描述                     关键字

非空约束 限制字段不能存null。                     not null

唯一约束 保证字段的所有数据都唯一不重复                     unique

主键约束 是一行数据的唯一标识，要求非空唯一                   primary key

默认约束 保存数据时，如果未指定改字段值，采用默认          default

8.0.16后 检查约束 保证字段值满足某一条件                     check

外键约束 让两张表的数据之间建立连接，保证数据一致完整    foreign key

例子：通过约束创建表

create table employ (

    id      int         primary key    auto_increment  comment '编号',

    name    varchar(10) not null       unique          comment '姓名',

    age     int         check(age>0 && age<=120)       comment '年龄',

    status  char(1)     default (1)                    comment '状态',

     gender  char(1)                                    comment '性别'

)comment '员工表';

外键：让两张表建立联系，让数据一致完整性得到保证

建立外键关联

语法：1:建表时添加

create 表名(

字段名 数据类型，

…

[constraint]外键名 froeign key 外键字段名 references 主表 (主表列名)

);

  2:直接添加

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表（主表列名）

删除外键

alter table 表名 drop foreign key 外键名称

外键约束 让两张表的数据之间建立连接，保证数据一致完整    foreign key

no action 当父表中删除或更新时，先检查该记录是否有对应外键，有则不允许删除或更新

restrict 当父表中删除或更新时，先检查改记录是否有对应外键，有则不允许删除或更新

cascade 当父表中删除或更新时，先检查改记录是否有对应外键，有则也删除或更新子表记录

set null 当父表中删除时，先检查改记录是否有对应外键，有则设置子表为null（此时要求外键允许取null

set default 当父表中删除或更新时，子表将外键列设置成一个默认的值（当前mysql不支持

例子：创建外键时可以添加外键约束，on update cascade on delete cascade

alter table 表名 add constraint 外键名 foreign (外键字段) references 主表名 （主表字段名） on update cascade on delete cascade

多表查询

多表关系：数据库表结构设计的时候，根据业务需求和业务模块之间的关系，让各个表结构有各种联系

分为三种：一对多（多对一），多对多，一对一

一对多：例，部门于员工的关系。

实现：在多的一方设置外键，只想一的一方的主键

多对多：例，学生于课程的关系，一个学生可以选多门课程，一个课程也可以让多个学生选择

实现：建立第三方中间表，中间表至少包含两个外键，分别关联两方主键

多表查询：从多张表中查询信息

分为：1.连接查询： 内连接： 查询A，B表交集部分

外连接： 左外连接：查询左表所有数据，以及两张表交集部分数据

右外连接：查询右表所有数据，以及两张表交集部分数据

自连接： 当前表与自身的连接查询，自连接不行使用表别名

内连接：内连接查询语法：

隐式内连接：select 字段列表 from 表1，表2 where 条件… 显式内连接：select 字段列表 from 表1[inner] join 表2 on 连接条件…

表结构：emp,dept

连接条件：emp.dept_id=dept.id

显式例子：select emp.name,dept.name  from emp,dept where emp.dept_id=dept.id;

select e.name,d.name from emp e,dept d where e.dept_id=d.id;

隐式例子：select e.name,d.name from emp e,dept d inner join dept d on e.dept_id=d.id;

左外连接：select 字段列表 from 表1 left outer join 表2 on 条件…

右外连接：select 字段列表 from 表1 right outer join 表2 on 条件 ….

左外例子：select * from emp left outer join dept on emp.dept_id=dept.id;

右外例子：select * from emp right outer join dept on emp.dept_id=dept.id;

自连接：即表内的字段之间的联系,（在联系的时候一定要用别名

例子：select  a.name,b.name from emp a,enp b where a.managerid=b.id;

2.联合查询

对于union查询，就是把多次查询的结果合并起来，形成一个新的查询结果集。

select 字段列表 from 表1 …      union  all     select 字段列表 from 表2…；

就是将两个表直接合并

select 字段列表 from 表1 …      union     select 字段列表 from 表2…；

如果把all去掉就会自动去重

但是当两个表的字段数量不同或者数据类型不同时，就无法用union合并

3.子查询 ：SQL语句中嵌套select语句，称为嵌套查询，也叫子查询

select * from t1 where column1=(seldect column1 from t2);

根据子查询的位置，分为where后，from后，select后

标量子查询：子查询结果为单个值

常用操作符：> < = >= <= in           not in

例子：select * from emp where dept_id =(select id from dept where name=‘销售部’)；

列子查询：子查询结果为一列

常用操作符. in not in any some         all

any:返回集合中满足一条即可（some 和any作用等同

all：返回集合中满足所有条件

例子：select* from emp where salary >

all(select salary from emp where dept_id =(select id from dept where name =‘财务部’));

行子查询：子查询结果为一行

常用操作符：> < = >= <=

例子：select * from emp where (salary ,managerid)

=(select salary,managerid from emp where name =‘张无忌’)

表子查询：子查询结果为多行多列

常用操作符为 in

select * from emp where (salary ,job )

in (select salary ,job from emp where name =‘鹿杖客’ or name =‘宋远桥’)；

事务

事务是一组操作的集合，是不可分割的工作单位，事务回把所有的操作作为一个整体

一起向系统提交或撤销操作请求，即这些操作要么同时成功，要么同时失败。

例如：银行转账

默认的mysql的事务是自动提交的，也就是说，当执行一条DML语句，MySQL回立即隐式的提交事务

方式一：

控制事务：

selsect @@autocommit ; 查看当前数据库的事务提交方式

set  @@autocommit=0; 设置事务提交方式。 1:自动提交。0:手动提交

提交事务：

commit； 改成手动提交之后的操作，如果不提交就无法操作数据库

回滚事务：

rollback; 如果事务出现了异常，就要还原之前修改的操作

方式二：

开启事务： start transaction 或者begin； 开启事务

提交事务： commit； 事务执行成功使用

回滚事务： rollback； 事务执行失败使用

事务的四大特性（ACID）

原子性： 事务是不可分割的最小操作单元，要么全部成功，要么全部失败

如转账操作

一致性：事务完成时，必须使所有的数据都保持一致状态

如转账操作中不会出现，A转出钱，但B没收到的情况

隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行

有若干操作对数据库同时操作，这个时候这些操作不会互相影响，相互独立

持久性：事务一旦提交或者回滚，它对数据库的改变是永久的

并发事务问题

脏读： 一个事务读到另一个事务还没有提交的数据。

例：事务A更新了数据还没提交，此时又有事务B来查询

不可重复读： 一个事务先后读取同一条记录，但两次读取到数据不同

例：事务A查询了数据，此时事务B更新了数据，这时事务A又有一条同样查询语句在查询

幻读： 一个事务按照条件查询数据时，没有对应数据行，但是在插入数据时，又发现这行数据

已经存在，好像出现了幻影

例：事务A查询数据库，此时事务B插入了新数据，此时事务A插入相同数据，

A再查询数据，数据不存在

事务的隔离级别：（解决并发事务问题

read uncommitted                  脏读 不可重复读         幻读 read committed                      不可重复读         幻读

repeatable  read（默认）                         幻读

serializable

以上事务隔离级别逐步上升，数据更加安全，但性能逐步下降

查看当前事务隔离级别： select @@transaction_isolation

设置事务隔离级别   ： set [session | global]  transaction isolation level {隔离级别}

这些是我看哔哩哔哩黑马的那个mySQL记录的

增删改查，一点不难，就是东西多，得慢慢记牢

第一次发文，支持下新人，点点赞呗😁

以后会持续产出的哦