文章目录
一、基本概念
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)机制也可以解决该问题。