文章目录
一、基本概念
DB:database数据库，存储一系列有组织数据的容器。
DBMS:Database Management System数据库管理系统，负责管理和维护DB。
SQL：Structure Query Language结构化查询语言，程序员用于与DBMS沟通的语言。
二、MySQL优点
- 体积小、安装方便
- 重点开源，不要钱
- 性能高，稳定性好
- 兼容性好
MySQL基本语法
DDL
1.库和表的管理
库的管理：
创建库 create database 库名；
删除库 drop database 库名；
表的管理：
create table if not exists stuinfo(
stuid int,
stuName varchar(15),
gender char,
bornDate datetime
);
desc stuinfo;//查看表结构
修改表
语法：alter table 表名 add|modify|drop|change column 字段名 【字段类型】;
①修改字段名
alter table stuinfo change colunm name（原名称)gender（修改后名称）sex char;
②修改表名
alter table stuinfo rename to studentinfo;
③修改字段类型
alter table stuinfo modify column bornDate Date;
④添加字段
alter table stuinfo add colunm email varchar(20) ;
⑤删除字段
alter table stuinfo drop colunm email;
删除表
drop table if exists stuinfo;
2.常见数据类型介绍
主要包括以下五大类：
整数类型：BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
浮点数类型：FLOAT、DOUBLE、DECIMAL（更精准，一般用于金额）
字符串类型：CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
日期类型：Date、DateTime、TimeStamp、Time、Year
其他数据类型：BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection等
3.常见约束
①NOT NULL ：非空，用于保证该字段的值不能为空。例如学生表的学生姓名及学号等等。
②DEFAULT：默认值，用于保证该字段有默认值。例如学生表的学生性别
③PRIMARY KEY：主键，用于保证该字段的值具有唯一性并且非空。例如学生表的学生学号等。
④UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空。例如注册用户的手机号，身份证号等。
⑤CHECK：检查约束（MySql不支持），检查字段的值是否为指定的值。
⑥FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某些的值。例如学生表的专业编号
DML
1.插入数据
insert into 表名(字段名，…) values(值1,…);
特点：
①字段类型和值类型一致或兼容，而且一一对应
②可以为空的字段，可以不用插入值，或用null填充
③不可以为空的字段，必须插入值
④字段个数和值的个数必须一致
⑤字段可以省略；默认所有字段，并且顺序和表中的存储顺序一致
2.修改数据
修改单表：
update 表名 set 字段=新值，字段=新值 where 条件；
修改多表：
update 表1 别名1，表2 别名2 set 字段=新值，字段=新值 where 连接条件 and 筛选条件
3.删除数据
方式一：delete
单表删除：
delete from 表名 where 筛选条件
多表删除
delete 别名1，别名2 from 表1 别名1，表2 别名2 where 连接条件 and 筛选条件；
方式二：truncate
truncate table 表名；
delete和truncate的区别？
1.truncate不能加where条件，而delete可以加where条件
2.truncate的效率高一些
3.truncate 删除带自增长的列的表数据后，如果再插入数据，数据从1开始；
delete删除带自增长列的表后，如果再插入数据，数据从上一次的断点处开始
4.truncate删除不能回滚，delete删除可以回滚
##DQL
####1.基本查询
语法： select 查询列表 from 表名；
特点：
①查询结果集是一个虚拟表
②查询列表可以是单个字段、多个字段、常量、表达式、函数，也可以是以上的任意组合
引申1：起别名
select 字段名 as “别名” from 表名；
select 字段名 ”别名“ from 表名；
引申2：+的作用
作用：加法运算
- 如果两个操作数都是数值型，则直接进行加法运算
- 如果其中一个为非数值型，则将强制装换成数值型，如果转换失败，则当做0处理
’123‘+4===》127
‘abc’+4===》4 - 如果其中一个为NULL，则结构直接为NULL
引申3：去重
select distinct department_id from employees;
引申4：内置函数
select version(); 查看数据库版本
select databese();查询当前所使用的数据库
select user();//查询当前挡路用户名
select ifnull(字段名，表达式)； //SELECT IFNULL(NULL,“11”); -> 11 SELECT IFNULL(“00”,“11”); -> 00
select concat(字符1，字符2，字符3)；//拼接多个列
select length(字符、字段)；获取字节长度
2.条件查询
语法：
select 查询列表 from 表名 where 筛选条件；
筛选条件分类：
①按条件表达式筛选：
关系运算符：>= < <= > <> =
②按逻辑表达式筛选
逻辑运算符：and or not
③模糊查询
like:一般和通配符搭配使用
_ 任意单个字符
% 任意多个字符
between and：一般用于判断某字段是否在指定的区间
a between 10 and 100
in: 一般用于判断某字段是否在指定的列表
a in(10,30,50)
is null:判断是否null值
3.排序查询
语法：
select 查询列表 from 表名 where 筛选条件 order by 排序列表；
特点：
①排序列表可以是 单个字段、多个字段、函数、表达式、别名、列的索引，以及以上的组合
②升序，通过asc；降序，通过desc
4.常见函数
SQL中的函数分为单行函数和分组函数
语法： select 函数名(实参列表);
- 字符函数
concat(str1,str2,…);拼接字符
substr(str,pos)；截取从pos开始的所有字符，起始索引从1开始
substr(str,pos,len);截取len个从pos开始的字符，起始索引从1开始
length(str)；获取字节个数
char_length(str);获取字符个数
upper(str);变大写
lower(str);变小写
trim(【substr from】str);去除指定字符，默认是空格
left(str,len);从左边截取制定len个数的字符
right(str,len);从右边截取制定len个数的字符
lpad(str,substr,len);左填充
rpad(str,substr,len);右填充
strcmp(str1，str2);比较两个字符的大小
instr(str,substr);获取substr在str中第一次出现的索引
2.数学函数
ceil(x);向上取整
floor(x);向下取整
round(x,d);四舍五入，四舍五入返回D位小数
mod(x,y);取模/取余
truncate(x,d);截断，保留小数点后d位
abs(x);求绝对值
3.日期函数
now();获取当前日期–时间
curtime();获取时间
curdate();获取日期
date_format(date,格式)；格式日期为字符
str_to_date(str,格式)；将字符转成日期
datediff(date1,date2);获取两个日期之间的天数差
year(date);
month(date);
…
4.流程控制函数
①if(条件，表达式1，表达式2)：如果条件成立，返回表达式1，否则返回表达式2
②case 表达式
when 值1 then 结果1
when 值2 then 结果2
…
else 结果n
end
③case
when 条件1 then 结果1
when 条件2 then 结果2
…
else 结果n
end
5.分组函数
sum求和
avg平均
max最大
min最小
count个数
特点：
- 实参的字段的类型，sum和avg只支持数值型，其他三个可以支持任意类型
- 这五个函数都忽略null值
- count可以支持一下参数
count(字段)：查询该字段非空值的个数
count(*):查询结果集的行数
count(1):查询结果集的行数 - 分组函数可以和distinct搭配使用，实现去重的统计
select count(distinct 字段) from 表名；
6.分组查询
语法：
select 分组函数，分组的字段 from 表名 where 分组前的筛选条件 group by 分组列表 having 分组后的筛选条件 order by 排序列表；
特点：
①分组列表可以是单个字段
②筛选条件分为两类：
| 筛选的基表 | 使用的关键字 | 位置 | |
|---|---|---|---|
| 分组前筛选 | 原始表 | where | group by之前 |
| 分组后筛选 | 分组后的结果集 | having | group by之后 |
7.连接查询
当查询中设计到多个字段，则需要通过多表连接
笛卡尔乘积：
出现原因：没有有效的链接条件
解决办法：添加有效的链接条件
--------------------------SQL92语法------------------------
语法：
select 查询列表 ①
from 表1 别名，表2 别名，… ②
where 连接条件 ③
and 筛选条件 ④
group by 分组列表 ⑤
having 分组后筛选 ⑥
order by 排序列表；⑦
执行顺序：②③④⑤⑥①⑦
--------------------------SQL99语法------------------------
1.内连接
语法：
select 查询列表 ①
from 表1 别名 ②
【inner】join 表2 别名 on 连接条件 ③
【inner】join 表2 别名 on 连接条件
where 筛选条件④
goup by 分组列表⑤
having 分组后的筛选⑥
order by 排序列表⑦
执行顺序：②③④⑤⑥①⑦
8.子查询
一条查询语句中又嵌套了另一条完整的select语句，其中被嵌套的select语句，称为子查询或内查询；在外面的查询语句，称为主查询或外查询
1、子查询都放在小括号内
2、子查询可以放在from后面、select后面、where后面、having后面，但一般放在条件的右侧
3、子查询优先于主查询执行，主查询使用了子查询的执行结果
4、子查询根据查询结果的行数不同分为以下两类：
① 单行子查询
结果集只有一行
一般搭配单行操作符使用：> < = <> >= <=
非法使用子查询的情况：
a、子查询的结果为一组值
b、子查询的结果为空
② 多行子查询
结果集有多行
一般搭配多行操作符使用：any、all、in、not in
in： 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
9.分页查询
语法：
select 字段 from 表名 where 条件 group by 分组字段 having 条件 order by 排序字段 limit 【起始的条目索引，】条目数；
特点：
①起始条目索引从0开始
②limit子句放在查询语句之后
③select * from 表 limit （page-1）*sizePerPage,sizePerPage
假如:每页显示条目数sizePerPage，要显示的页数 page
10.union联合查询
语法：
select 字段|常量|表达式|函数 from 表 【where 条件】 union 【all】
select 字段|常量|表达式|函数 from 表 【where 条件】 union 【all】
select 字段|常量|表达式|函数 from 表 【where 条件】 union 【all】
…
select 字段|常量|表达式|函数 from 表 【where 条件】
特点：
①多条查询语句的查询的列数必须是一致的
②多条查询语句的查询的列的类型几乎相同
③union代表去重，union all代表不去重
数据库事务
**定义：**数据库事务是构成单一逻辑工作单元的操作集合。
特点：
①原子性：要么都执行，要么都回滚
②一致性：保证数据的状态操作前和操作后保持一致
③隔离性：多个事务同时操作相同数据库的同一个数据时，一个事务的执行不受另外一个事务的干扰
④持久性：一个事务一旦提交，则数据将持久化到本地，除非其他事务对其进行修改
相关步骤：
1、开启事务
2、编写事务的一组逻辑操作单元（多条sql语句）
3、提交事务或回滚事务
事务分类：
隐式事务，没有明显的开启和结束事务的标志，比如
insert、update、delete语句本身就是一个事务
显式事务，具有明显的开启和结束事务的标志
set autocommit=0;
start transaction;
commit;
rollback;
事务的隔离级别
隔离级别一共有四种：读未提交、读已提交、可重复读、串行化。这四种隔离级别分别可以解决的不同的问题，下面先描述一下不使用隔离级别可能会出现的几种问题。
（1）丢失修改
A和B两个事物同事修改同一个数据，A修改的提交在B提交之后，导致B好像没有修改，丢失修改。
（2）脏读
B事务修改了一个数据并未提交，A事物读取了这个数据，然后B事务回滚了，最后A又读取了一次，两次读取的数据不一致，称为脏读。
（3）不可重复读
A事务读取了一个数据后，B事务修改了这个数据，A事务又读取了这个数据，两次读取的数据也不一致，称为不可重复读。
（4）幻读
A事务更新了某个字段（范围是整个数据表的）（以id=1为条件的），B事务又插入了一条新的记录，导致A事务认为自己没有完全更新过来，就像出现幻觉一样。
针对这几种错误分别设置不同的隔离级别来解决：
第一种丢失修改一般使用加锁锁来解决，因此串行化可以解决，并且串行化可以解决上面出现的所有问题。
第二种问题脏读是因为读取其他事物未提交的数据，因为设置读已提交隔离级别可以解决这个问题。但不可解决不可重复读和幻读的问题。
第三种问题不可能重复读，是因为B事物的修改影响了A事务的读取数据，设置可重复读隔离级别，使得B事务修改数据和A事务读取数据互不影响，隔离开来，从而解决这个问题，同时解决 脏读问题。
第四种幻读问题，是因为A事务更新完数据后，B事务又插入了新的数据，设置串行化隔离级别可解决，并且这种隔离级别解决上面所有的问题。除了串行化，多版本并发控制（MVCC，Multiversion Concurrency Control）机制也可以解决该问题。
扫一扫
463
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
