数据库个人学习笔记

MySQL

1. 数据库：数据存储的仓库

2. 数据库管理系统：操作和管理数据库的大型软件

3. SQL：操作关系型数据库的编程语言，是一套标准

SQL--->数据库管理系统--->数据库

SQL分类：

DDL: 数据定义语言 包括：create，drop，alter，truncate(不支持事务)

DML:数据操作语言 包括：insert update delete （支持事务）

DQL :数据查询语言 包括：select

DCL：用来创建用户和用户的权限

1.DDL：对库、表、字段进行操作

show databases; 查询所有数据库

create database 数据库名; 创建数据库

use 数据库名; 选择数据库

create table 表名+( 创建数据表 字段名1 数据类型(字符长度)， 字段名2 数据类型(字符长度)， 字段名3数据类型(字符长度), .......);

字段名 = 列名 数据类型 = 单元格格式

查询所有数据表: show tables;

查看创建表格过程: show create table 表名;

查看创建表格以表格形式展现: describe/desc(一般缩写) 表名;

修改表格的结构: alter(修改)

修改数据表名称: alter table 原来表名 rename to 新表名

修改字段名称: alter table 表名 change 旧字段名 新字段名 新数据类型(字符长度) 注意:此SQL语句有两个作用还可以修改数据类型 如果只改名字不改数据类型 旧数据类型照抄上

修改字段数据类型: alter table 表名 modify 字段名 新数据类型(字符长度)

(修改)表中添加字段: alter table 表名 add 字段名 数据类型(长度);

将字段添加在字段2的后面 alter table 表名 add 字段 数据类型(长度) after 字段2;

将字段添加在首位 alter table 表名 add 字段 数据类型(长度) first;

(修改)删除表中字段:

delete:删除数据 drop:删除数据库,表,字段 alter table 表名 drop 字段名;

(修改)修改表中字段位置: 将字段1放在字段2的后面 alter table 表名 modify 字段1 数据类型(长度) after 字段2;

将字段1放在首位 alter table 表名 modify 字段1 数据类型(长度) first;

删除数据库: drop database 数据库名;

删除数据表: drop table 表名;

2.DML：对表中数据进行增、删、改操作

增加/插入/添加数据

1.为全部字段添加数据 insert into 表名 values (添加的数据,值) 按照原表字段顺序

2.为部分字段添加数据 insert into 表名 (字段名) values (添加的数据,值)

查询数据

1.查询所有字段数据 select * from 表名;

2.查询部分字段数据 slesct 字段名 from 表名 ;

修改数据(更新)

1.修改/更新部分字段数据 update 表名 set 字段名1=值1（更新后的值）,字段名2=值2...； where 条件表达式 过滤条件：（定位）

2.修改/更新全部字段数据 update 表名 set 字段名1=值1（更新后的值）,字段名2=值2...；

删除数据 delete

1.删除指定数据 delete from 表名 where 条件表达式；

2.删除全部数据 delete from 表名 ；

3.删除指定部分数据 delete from 表名 where 字段名 in（值1，值2...）；

4.删除范围数据(关系运算符) delete from 表名 where 字段名 运算符 对比的值；

5.删除全部数据(truncate) truncate table 表名；

truncate和delete的区别

a.使用delete删除全部数据时，如果再添加数据，自动添加字段的值为删除时该字段的最大值+1； 使用truncate删除全部数据时，如果在添加数据，自动增加的值从1开始；

b.delete删除语句，可以在后跟where过滤条件，来实现删除部分或指定数据； truncate，只仅仅是删除全部数据；

3.DQL ：对表中数据进行查询操作

查询 按条件查询 = > < >= <= !=

按照关键词查询 in （...）在...之中

between...and... 在...和...之间

is null 空值查询 通过关键字is null来判断字段的值为空值的数据 select* from 表名 where 字段名 is null

is not null 非空查询 通过关键字is not null来判断字段的值不为空值的数据 select* from 表名 where 字段名 is not null *

去除重复查询（去重查询） 去除查询结果中的重复数据， 关键字 :distinct select distinct 字段1，字段2...from 表名； 注：在去重时，去除所有字段组合在一起，来去除所有字段完全相同的数据；

模糊查询： 已知部分数据，查询相关的所有数据(数据中包含了已知数据) 关键词：like select * from 表名 where 字段名 like “字符串+通配符”； 注：not like(不包含) 通配符： %： 任意长度的字符串，包括空字符（只需要用一次） _(下划线): 任意单个字符（可以用多次）

关键字and查询 使用and关键字时，可以连接两个或多个查询条件，只有满足了所有条件才会返回结果 select * from 表名 where 表达式1 and 表达式2 and ...；

关键词or查询 使用or关键字时，只需要满足其中任意一个条件就会被查询出来 select * from 表名 where 表达式1 or 表达式二 or ...；

聚合函数（聚合查询） select 聚合函数（字段）from 表名 ；

最大值：MAX（） 最小值：MIN（） 平均值：AVG（） 总和：SUM（） 总数：COUNT（）

排序查询（对查询的结果进行排序） 关键词：order by select* from 表名 order by 字段； asc 升序 （默认） desc 降序 跟在字段后面 注:程序默认的是升序排列，所以使用升序排列时，asc关键字可以省略，但是降序desc一定要写

分组 关键词：group by 可以按照某个字段或者多个字段中的值进行分组，字段值相同的为一组

1）普通分组 select* from 表名 group by 字段；

2）group by 和聚合函数联用 select 聚合函数（字段）from 表名 group by 字段；

3）group by 与 having联用 having必须在 group by 之后 order by 之前 having关键字和where关键字的作用相同,都属于设置条件表达式对查询结果进行过滤; 两者区别在于:having关键字后可以跟聚合函数,而where关键字不能。having用于对分组后的结果进行过滤

语法结构: select 字段1，字段2，...from 表名 group by 字段1, 字段2,...having聚合函数表达式

1.按照成绩进行分组，并查出那一组的总成绩大于300分的 例如: select sum(score) from stu1 group by score having sum(score)>200;

2.按照成绩进行分组,并对分组结果进行降序排序 select count(score),score from stu1 group by score order by score desc;

关键字:LIMIT 限制查询结果的数量,可以指定查询结果从那一条记录开始，以及一共查询多少条数据 select * from 表名 limit 起始位置 记录数; 记录数:要查看的数据的数量

注:

1)查询结果并不是从起始位置开始的，而是从起始位置的下一条数据开始显示

2)在没有起始位置的这个参数时，将从第一条数据开始显示

例如: 1.在学生表中,从第8条数据开始，查询5条数据

select * from stu1 limit 8,5;

2.在学生表中，查询5条数据

select * from stu1 limit 0,5;

为字段或表格取别名 关键字: AS 有时为了让显示查询结果的字段名更加直观,可以为字取一个别名; select 字段1 as 新名称,字段2 as 新名称 ,字段3 as 新名称...from 表名; 可以省略关键字 as 但一般不省略

4.DCL：用来创建用户和用户的权限

1. 查询用户

   use mysql;

   select * from user;

2. 创建用户

   create user '用户名'@'主机名' identified by '密码';

3. 修改用户密码

   alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';

4. 删除用户

   drop user '用户名'@'主机名'

注意：

• 主机可以使用%通配。

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

权限控制

权限 说明

ALL/ALL PRIVILEGES 所有权限

SELECT 查询数据

INSERT 插入数据

UPDATE 修改数据

DELETE 删除数据

ALTER 修改表

DROP 删除数据库/表/视图

CREATE 创建数据库/表

1.查询具有的权限

show grants for '用户名'@'主机名';

2.授予权限

grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

3.撤销权限

revoke 权限列表 on 数据库名.表名 to '用户名'@'主机名';

注意：

• 多个权限之间，使用逗号分隔。

• 授权时，数据库名和表名可以使用 * 进行通配，代表所有。

函数

1.字符串函数

concat（s1,s2...sn）：字符串拼接，将s1，s2，...sn拼接成一个字符串

lower（str）：将字符串str全部转换为小写

upper（str）：将字符串str全部转换为大写

lpad（str，n，pad）：左填充，用字符串pad对str的左边进行填充，达到n个字符串长度

rpad（str，n，pad）：右填充，用字符串pad对str的右边进行填充，达到n个字符串长度

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

substring（str，start，len）：返回从字符串str从start位置起的len个长度的字符串（索引从1开始）

2.数值函数

ceil（x）：向上取整

floor（x）：向下取整

mod（x，y）：返回x/y的模（余数）

rand（）：返回0~1内的随机数

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

3.日期函数

curdate（）：返回当前日期

curtime（）：返回当前时间

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

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

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

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

date_add（date，interval expr type）:返回一个日期/时间加上一个时间间隔expr后的时间值

date_add（now()，interval 70 day）:当前时间往后加70天

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

4.流程函数

if（value，t，f）：如果value为true，则返回t，否则返回f

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

case when [val1] then [res1]...else [default] end：如果val1位true，返回res1，...否则返回default默认值

case [expr] when [val1] then [res1]...else [default] end：如果expr的值等于val1，返回res1，...否则返回default默认值

约束条件

1.主键约束

当字段设置了主键约束时，同时也会为其字段添加一个非空约束， 所以必须要为其字段添加数据 关键词: primary key 如果某一(字段)列添加了主键约束,那么这一列取值不能为空值,并且取值要唯一 主键:是用来唯一标识一行数据的;主键一般为编号

a.单字段约束 为某一个字段设置约束,且写在字段的后面;字段名 数据类型(长度) primary key 例如: id int (30) primary key

b.多字段约束 是多个字段组合而成的主键,必须满足多条字段的数据都不相同; 定义在所有字段的后面;primary key(字段1,字段2...)

2.外键约束

外键: 指引用另一个表格的一列或者多列,被引用的列应该具备主键约束或者唯一约束, 外键是用来连接和加强两个表格之间的连接.

添加外键约束 constraint:约束条件 alter table 从表名 add constraint 外建名 foreign key (外键字段) references 主表名 (主键字段) 为表格中的id字段添加一个外键约束并起名为(外建名),它引用了某个表格的id字段 删除外键约束 alter table 从表名 drop foreign key 外建名;

3.唯一性约束

保证数据表当中字段的唯一性，即表中字段的值不能重复 关键词: unique 写在字段的后面；字段名 数据类型（长度）unique

4.非空约束

限值某个字段的值不能为空值 当字段设置了非空约束时，必须要为其字段添加数据 关键词: not null 写在字段的后面；字段名 数据类型（长度） not null

5.默认值约束

为某数据表中的某个字段添加一个默认值 关键词: default 字段名 数据类型（长度） default 默认值。设置字段值自动增加 被其约束的字段可以是任何整数类型；该字段的值是从1开始自增（每次增加1）

注：

1.自动生成的数据一定是唯一的，并且不能为空

2.为了保证数据的唯一性和独立性，在使用此功能时，必须与主键约束一起使用 (primary key) 关键词：auto_increment 字段名 整数数据类型（长度）primary key auto_increment

多表查询

1.关联关系:

1.1.一对多

1.2.一对一

1.3.多对多

2.连接查询:

2.1.交叉连结

交叉连结返回的结果是被连接的两个表格中的所有数据行的 笛卡尔积 (也就是所有行的乘积) 也就是返回了第一个表中符合条件的数据行乘以第二个表中符合条件的数据行 关键词:cross join 语法结构: select * from 表1 cross join 表2 where 表1.相同字段名=表2.相同字段名;

2.2.内连接

内连接使用比较关系运算符对两个表中数据进行比较,比较出与连接条件匹配的数据 关键词:inner join 语法结构: select *(表名.字段名) from 表1 inner join 表2 注：inner 可以省略 只写join where 表1.相同字段名=表2.相同字段名;

2.3.外连接

2.3.1.左外连接

left join 返回的结果是包括左表中的所有数据和右边表中符合链接条件的数据

2.3.2.右外连接

right join 返回的结果是包括右表中的所有数据和左边表中符合链接条件的数据

语法格式: select 查询字段名 from 表1 left/right join 表2 on 表1.相同字段名 = 表2.相同字段名；

3.复合条件查询

就是在连接查询的过程中，通过添加过滤条件来限制查询结果，使查询结果更加准确 语法结构： （内外连接） （where，having） （group by ，order by） 关联查询 + 过滤条件 / 分组 / 排序

4.子查询

是指在一个查询语句中嵌套了另一个查询语句 内部的查询 它可以嵌套一个select,insert into等语句,在执行过程中,首先 执行子查询中的语句,然后将返回的查询结果作为外层的过滤条件

4.1.带有关键字 in 的子查询

select 字段 from 查询字段所在的表 where 相同字段名 in(子查询: select 相同字段名 from 表2 where 条件)

4.2.带有关键字exists的子查询

此关键字后面的参数可以是任意一个子查询,这个子查询的作用相当于测试它不会查询任何数据 只会返回true或false,当返回true时,外层查询才会执行,反之则不执行. select 字段 from 查询字段所在的表 where exists(子查询)

4.3.带有关键字any的子查询

只要满足任意一个条件，则被查询出来 select 字段 from 查询字段所在的表 where 条件表达式 any(子查询)

4.4.带有关键字all的子查询

满足全部条件，则被查询出来 select 字段 from 查询字段所在的表 where 条件表达式 all(子查询)

MYSQL搜索引擎

1.innodb：支持数据库高级操作，例如事务，外键

2.myisam：只支持数据基础的增删查改

set character_set_client = gbk; set character_set_results = gbk; set character_set_connection = gbk;

事务 什么是事务：是数据库中执行同一业务多条sql语句的工作单元，可以保证多条 sql全部执行成功或全部执行失败 事务指令：

事务 什么是事务：是数据库中执行同一业务多条sql语句的工作单元，可以保证多条 sql全部执行成功或全部执行失败 事务指令：

1.开始事务 begin

2.提交事务 commit 有事务保护的情况下,提交事务(先开启事务) 两条数据都在表面完成,如果想两次全部彻底改变,则需要执行提交 提交事务之后不能回滚

3.回滚 rollback 当执行结果中发现一条成功一条失败应该执行回滚操作 修改数据一定在开启事务与回滚之间,一定先开启,在修改

*事物的ACID特性:*面试题*

保证事务正确执行的四大基本要素

1.atomicity 原子性:最小不可拆分,保证全部执行成功或者全部失败

2.consistency 一致性:从一个一致状态到另一个一致状态

3.isolation 隔离性:多个事务之间互相隔离不影响

4.durability 持久性:当事务提交后数据保存到磁盘中持久生效 在没有开启事务的事务,系统将自动提交事务 保存回滚点: begin; sql语句; savepoint (保存点) s1 ; rollback s1; (回到保存点 s1)

修改现有表中的时间列默认值为自动获取

#修改表 t_user 中的 create_time 列 在插入新的数据时 如果值为空就设置为当前的系统时间 ALTER TABLE t_user MODIFY create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间';

#修改表 t_user 中的 update_time 列 在修改的数据时 如果值为空就设置为当前的系统时间 ALTER TABLE t_user MODIFY update_time timestamp not null default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP COMMENT '更新时间';