一.SQL结构化语言
1.1 DQL(Data Query Language):数据查询语言,用来查询数据库中表的记录(数据)
1.1.1 基本查询
语法:select 查询列表 from 表名;(查询列表换为*的时候是查询所有字段)
起别名:使用AS或空格(注意:有的别名需要用单引号或者双引号引用)
格式:SELECT 字段名 AS xx,字段名 AS xx From 表名
1.1.2 常见函数
1.1.2.1单行函数
字符函数
LENGTH():用于获取参数值的字节个数
CONCAT():拼接函数
UPPER():转换为大写
LOWER():转换为小写
SUBSTR/SUBSTRING(‘指定字符串’,n,m):后接out_put输出
INSTR():返回子串第一次出现的索引,找不到返回0
TRIM():去除前后指定的的字符,比如:SELECT TRIM(‘a’ FROM ‘aaaaa宏aaaaa’) AS OUT_PUT
LPAD():用指定的字符左填充指定长度(如果截断,就从右边截断),比如:SELECT LPAD(‘放到赛’,10,‘$’) AS out_put
RPAD():用指定的字符右填充指定长度(如果截断,就从左边截断)
REPLACE()替换,SELECT REPLACE(str,from_str,to_str);第一个定义一个字符串,第二个从这个里面找到需要替换的内容,第三个是设置替换的结果
数学函数
round():四舍五入(如果需要设置小数个数,则在需要四舍五入的数后面加’,'后接位数)
ceil():向上取整
floor():向下取整
truncate(X,D):截断(X为需要截断的小数,D为截断的小数位数)
MOD(n,m):取余(n为被除数,m为除数)
日期函数
NOW():返回当前系统日期和时间
curdate():返回日期,不包含时间
curtime():返回时间,不包含日期
获取指定部分的日期,对于年月日以及时分秒:每个对应的用每个的英文加NAME,就比如年:YEARNAME(NOW()) 年; 以此类推
SELECT STR_TO_DATE(str,format)
DATE_FORMAT(date,format)
流程控制函数
IF语句:SELECT IF(expr1,expr2,expr3):如果expr1成立,执行expr2,不成立执行expr3
case语句(类似于switch语句(等值判断))
多重if在mysql中的使用(判断区间),相较于java,在mysql中使用case。只是和平常的case不一样,case后不加任何东西,直接写when,后和以上的case一样
1.1.2.2 分组函数(统计函数,聚合函数,组函数)做统计使用(传进去一组值,返回一个值)
分类:sum()求和,avg()平均值,max()最大值,min()最小值,count()计算个数
支持类型:sum,avg一般处理数值型,其他三种可以处理任何类型,且都忽略null值,分组函数都可以和distinct(去重函数)搭配使用
1.1.3 进阶查询
1.1.3.1 模糊查询
like: 格式:SELECT 查询列表 FROM 表名 where 字段 like ‘包含的字符’;
特点:一般和通配符搭配使用,%多个字符(在’包含字段的字符’里使用),_ 单个字符。
between and:格式:SELECT 查询列表 FROM 表名 where 字段 BETWEEN 条件 AND 条件 提高语句的简介性,包含临界值
in:前接字段后接需要查询的具体字段,如果有多个字段,必须使用括号,它相较于like来说,IN查的是一个大的字段,而LIKE查的是某一截,另外LIKE不用括号
is unll:格式:字段 IS NULL,查询没有特定条件的字段,相反,要查询存在特点条件的语句就是IS NOT NULL
1.1.3.2 排序查询
格式:SELECT 查询列表 FROM 表名 WHERE order by 排序列表名 asc(升序)/desc(降序)
如果不写关键字,默认是升序,order by后支持字段(单个/多个),别名,表达式,函数;一般放在查询语句的最后面(limit子句例外)。
1.1.3.3 分组查询
在分组前筛选,然后再需要分组的字段where后添加grouo by 关键字,grouo by后接需要分组的字段,可以有多个字段,中间必须使用,隔开,但必须放在order by前 ,from 表后,一般搭配count使用。
在分组后筛选,搭配having使用,由于用的数据源是之前筛选好的
where 和 having 的区别?
- where 在分组之前进行限定,如果不满足条件,则不参与分组。having在分组之后进行限定,如果不满足结果,则不会被查询出来
- where 后不可以跟聚合函数,having可以进行聚合函数的判断。
1.1.3.4 连接查询
为什么要使用该方法;表和表之间存在连接,如果在查询的时候就会发生错误,就必须要设置相应的条件来防止输出错误
内连接
等值连接:利用引用“.”来确定连接条件(一般放在WHERE后面),相应的可以为表取别名,当然如果需要加筛选条件,WHERE的附加条件后面加AND,再写筛选条件,多表连接条件:多表连接的结果为多表的交集部分,n表连接,至少需要n-1个条件,多表的顺序没有要求,一般需要为表取别名,可以搭配前面所有的查询子句非等值连接:只是在where条件语句中的等于条件换成其他符合条件的判断语句
自连接:对于连接条件在同一个表内,相当于查询了两次,只是第一次查的内容和第二次查的内容不一样
外连接
特点:如果表中有和他匹配的条件语句,则显示匹配的值;如果没有则显示null;外连接查询结果=内连接查询结果+主表中有而从表中没有的记录
左外连接
* 语法:
select 字段列表
from 表1
left [outer] join 表2 on 条件
…
* 注意:
左外连接查询的是左表所有数据以及其交集部分右外连接
* 语法:
select 字段列表
from 表1
right [outer] join 表2 on 条件
…
* 注意:
右外连接查询的是右表所有数据以及其交集部分全外连接
cross join 类似于笛卡尔积
1.1.3.5 子查询
查询中嵌套查询,称嵌套查询为子查询或内查询;外部的查询语句,称为主查询或外查询
分类
标量 子查询:结果只有一行一列
列 子查询:结果集只有一列多行
行 子查询:结果集有一行多列或者多行多列
表 子查询:结果集一般为多行多列其按照位置所在查询中放位置:
select后面只支持标量 子查询
from 后支持表 子查询,将子查询结果充当一个表,必须起别名
where或having 后支持标量,列,行 子查询
exitsts后支持 相关子查询(表子查询),查询该表是否有值,固它的禅熏结果只有0或1
特点(放在where或者having后):
子查询放在小括号内,子查询一般放在条件的右侧
单行子查询
一般搭配这单行操作符使用;
结果集只有一行
一般搭配单行操作符使用:> < = <> >= <=
非法使用子查询的情况:
a、子查询的结果为一组值
b、子查询的结果为空
多行子查询
列子查询,一般搭配这多行操作符使用
结果集有多行
一般搭配多行操作符使用:any、all、in、not in
in: 属于子查询结果中的任意一个就行
any和all往往可以用其他查询代替
子查询的执行都优先于主查询,主查询进行条件判断时都用到了子查询的结果
补充:多行操作符
1.1.3.6 分页查询
语法:
select 字段|表达式,…
from 表
where 条件
group by 分组字段
having 条件
order by 排序的字段
limit 起始的条目索引,条目数;
特点:limit放在查询语句的最后,位置和执行顺序都是在最后,在最后追加limit offset(要显示条目的起始索引)(起始索引从0开始),size(需要显示的条目个数),如果是从0开始,0可以省略,只需要写显示的条目个数。
公式:select * from 表 limit (page-1)*sizePerPage,sizePerPage(每页显示条目数sizePerPage,要显示的页数 page)
1.1.3.7 联合查询
使用关键字UNION去连接查询的语句,如果下面还有连接的语句,则还是需要写UNION
注意:
将多条查询的语句的结果合并为一个结果,且多个表没有直接联系,但查询的信息一般一样,
查询的语句的列数必须一样,其中多条查询语句的每一列的类型和顺序最好一致,
union关键字默认去重,如果需要不去重,则需要加union all
1.2 DML(Data Manipulate Language):数据操纵语言,用于添加、删除、修改数据库记录,并检查数据完整性
1.2.1插入语句(insert)
语法一:insert into 表名(列名,…) values(值1,…);
语法二:insert into 表名 select 列名=值,UNION …
注意:
插入的值的类型要与列的类型一致或者兼容;
如果在列中没有添加的属性,再添加的时候的可以不写,要么写null。
1.2.2修改语句(update)
修改单表:update 表名 set 列名1 = 值1, 列名2 = 值2,… [where 条件]
修改多表: update 表1 别名1,表2 别名2 set 字段=新值,字段=新值 where 连接条件 and 筛选条件
1.2.3删除语句(DELETE)
方式一:delete
单表删除语法:delete from 表名 where 筛选条件(按行删)
删除多表(补充)
方式二:truncate
单表语法:truncate table 表名
删除多表(补充):该语句中不允许加where(条件语句 ),删除整个表
两种方式的对比:
truncate不能加where条件,而delete可以加where条件;
truncate的效率高
truncate删除带自增长的列的表后,如果再插入数据,数据从1开始
delete 删除带自增长列的表后,如果再插入数据,数据从上一次的断点处开始
truncate删除不能回滚,delete删除可以回滚
1.3 DDL(Data Define Languge):数据定义语言,用于库和表的创建、修改、删除(alter关键字 操纵)
1.3.1 库的管理
创建库:creat database (if not exits) 库名 ,其中 if not exits判断库是否存在
修改库名:rename database 库名 to 新库名,但是这句话已近舍弃,现在改不了了,只能修改库的字符集:alter database 库名 character 需要更改的字符集名称
库的删除:drop database (if exits) 库名,其中 if exits判断库是否存在
查询当前正在使用的数据库名称:select database();
使用数据库:use 数据库名称;
查询所有数据库的名称:show databases;
查询某个数据库的字符集:查询某个数据库的创建语句:show create database 数据库名称;
1.3.2 表的管理
表的创建:
create table 表名(
列名1 数据类型1 【字段约束】,
列名2 数据类型2 【字段约束】,
…
列名n 数据类型n 【字段约束】
);
* 注意:最后一列,不需要加逗号(,)
表的修改:
语法模板:
例子说明操作:
(1)修改列名 ALTER(操纵) COLUMN(列),COLUM可以省略
ALTER TABLE book CHANGE COLUMN publishdate pubDate DATETIME;
(2)修改列的类型或约束 MODIFY(修改) ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;
(3)添加新列 ADD(添加) ALTER TABLE book ADD COLUMN number INT;
(4)删除列 DROP(删除) ALTER TABLE book DROP COLUMN number;
(5)修改表名 RENAME TO(换成 表名) ALTER TABLE book RENAME TO books;
表的删除: IF EXISTS(判断存在才删除) DROP TABLE IF EXISTS 表名;
表的复制:
(1)复制结构和数据,在创建的同时,用了一个子查询,将数据全出查询到再复制到新表中。
CREATE TABLE 目标表名 SELECT * FROM 复制源表名;
(2)复制表的结构,没有数据
CREATE TABLE 目标表名LIKE 复制源表名;
1.3.3 MySQL常用数据类型
整型:int
小数:
浮点型(较短的文本)float(M,D);double(M,D)
定点型(较长的文本)dec(M,D);decimal(M,D)
原则:所选择的类型越简单越好,能保存的数值类型越小越好
特点:
字符型:较短的文本使用char或者varchar
区别:
日期型:
datetime和timestamp的区别:
1.3.3 约束
含义:用于限制表中的数据,为了保证的数据的准确性和可靠性
六个约束:
NOT NULL:非空
DEFAULT:默认
UNIQUE:唯一,和主键的约束相似,只是值可以为空(只允许一个值为空)
CHECK:检查约束【mysql中不支持】
PRIMARY KEY:主键,用于保证该字段的值具有唯一性,并且非空(简单通俗理解,不能有重复的值),如果加上auto_increment就代表自增长,注意:非空且唯一;一张表只能有一个字段为主键;主键就是表中记录的唯一标识
(1)创建表时,添加主键约束
(2)删除主键
ALTER TABLE stu DROP PRIMARY KEY;
(3)创建完表后,添加主键
ALTER TABLE stu MODIFY id INT PRIMARY KEY;
FOREIGN KEY:外键,用于限制两个表的关系,用于保障该字段的值通过对应关系来自于主表的关联的值(一个值—>主表中对应另外一个表相关联的值)
(1)在创建表时,可以添加外键
(2)删除外键
ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;
(3)创建表之后,添加外键
ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);
(4)级联操作
级联删除:在添加外键的时候:alter tables 表名 add constraint 外键名称 foreign key(外键字段名称) refences 主表名称(主表列名称) ON DELETE CASCADE;
1.4TCL(Data Control Language):数据事务语言
(1)事务介绍:一个或一组sql语句组成一个执行单元,这个执行单元要么全部执行,要么全部不执行。在这个单元中,每个sql语句是相互依赖的,而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影
响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
回滚:把刚才执行的操作撤销,回到做出的状态
(2)事务的ACID特征
原子性:指事务是不可分割的工作单位,事务中的操作要么都发生,要么都不发生
一致性:事务必须是数据库从一个一致性状态变换为另一个一致性状态
隔离性:指一个事务的执行不能被其他事务干扰,即一个事务内部的操作以及使用的数据对并发的其他事务是隔离的,并发执行额各个事务之间不能互相干扰
持久性:指一个事务一旦被提交,它对数据库中数据的改变就是永久性,接下来的其他操作和数据库故障不应该对其有任何影响
(3)事务的创建
显示事务:步骤一:开启事务:set autocommit = 0;start trransaction;(可选)
步骤二:编写事务中的sql语句()
步骤三:结束事务,commit(提交事务);rollback(回滚事务)
隐式事务:没有明显的开启和结束事务的标志(insert、update、delete语句本身就是一个事务)
(4)事务并发问题:
脏读:在未更新的数据读取(未被提交)
不可重复读:读取更新后同一个数据,数据不相同
幻读:插入数据前后表的结构不一致
隔离性解释,数据库系统必须具有隔离并发运行各个事务的能力,使他们不会相互影响,避免各种并发问题,隔离级别越高,数据一致性就越好,并发性越弱
(5)设置隔离级别
数据库提供的 4 种事务隔离级别:
设置隔离级别模板:set session|global transaction isolation level 隔离级别名;
查看隔离级别:select @@tx_isolation;
二.视图
(1)简介:通过创建一个虚拟表,来延用它的功能
(2)好处:sql语句提高重用性,效率高,和表实现了分离,提高了使用的安全性,简化复杂的sql操作,不必知道它的查询细节.
(3)应用场景;多个地方用到同样的查询结果,该查询结果使用的sql语句较复杂
(4)视图和表的区别:视图仅仅值保存了sql的逻辑,不占用物理空间;而表占用,使用的方式和表一样,没有区别。
(5)使用:
创建:create view 表名 as 查询语句
修改:UPDATE 视图名 SET 视图字段名=值 WHERE 条件
删除:drop view 视图名,视图名,…;
查看:desc 视图名;show create view 视图名
插入:INSERT INTO 视图名(视图字段)VALUES(值)
更新:视图的可更新性和视图中查询的定义有关系,以下类型的视图是不能更新的
包含以下关键字的sql语句:分组函数、distinct、group by、having、union或者union all
常量视图
Select中包含子查询
join
from一个不能更新的视图
where子句的子查询引用了from子句中的表。
三. 存储过程(类似于java中的方法)和函数
3.1 存储过程
3.1.1含义以及好处
一组预先编译好的sql语句的集合,理解成批处理语句
好处:提高代码的复用性,简化操作,减少编译次数并且减少和数据库服务器的链接次数,提高效率
3.1.2 创建
(1)creat procedure 存储过程名(参数列表) begin 存储过程体(一组sql语句) end (如果存储过程体仅仅一句话,begin end 可以省略)
(2)参数列表包含三部分:参数模式 参数名 参数类型
参数模式:in(作为输入,说明改参数需要调用传入值) out(作为输出,该参数可以作为返回值) inout(作为输入和输出,可以传入值可以返回值)
(3)运行:这里一般在命令提示窗里运行,使用delimiter 结束标记来结束,不用’;',一般写在开头,最后的end 后要书写其结束符号,结束符号可以自己定义。
3.1.3 使用
(1)分类:无返回无参;仅仅带in类型,无返回有参;仅仅带out类型,有返回无参;既带in又带out,有返回有参;带inout,有返回有参
注意:in、out、inout都可以在一个存储过程中带多个
(2)使用带in 的存储过程,语法:命名时 creat procedure 存储过程名(IN 你取得字段名 类型(长度))后跟begin 方法体(在这个里面需要用到into 取得的字段名 end结束符),调用使用:CALL 存储过程名(‘您想要查的数据’)结束符号 ;
(3)使用带OUT 的存储过程,语法:和in大致语法的一样,只是在 存储过程名(IN 你取得字段名 类型(长度),OUT 想要的返回值名(随便取)类型(长度)),在方法体的查询中要使用到into 想要的返回值名,然后调用的时候,可以设置用户变量名,也可以不设置然后call 存储过程名(‘传入参数名’,@随便取) 结束符号
(4)inout和in,out的一样
3.1.4 调用
call 存储过程名(实参列表)结束符号
3.1.5 删除
drop procedure 存储过程名(只能一个一个的删除)
3.1.6 查看
show create procedure 存储过程名;
3.2 函数
3.1.1 含义以及好处
含义:经过编译并存储在数据库中的一段sql语句的集合
好处:提高代码的复用性,简化操作,减少编译次数并且减少和数据库服务器的链接次数,提高效率
3.1.2 创建
(1)create function 函数名(参数列表) returns 返回类型 后和存储过程的一样
(2)注意:参数列表包含两部分(参数名 参数类型),函数体中肯定有return语句,没有会报错;如果return没有在最后面,不会报错,但不建议
3.1.3 调用
select 函数名(参数列表) 结束符号
3.1.4 查看
show create function 函数名;
3.1.5 删除
drop function 函数名;
3.3 区别
(1)存储过程可以有0个返回值,也可以有多个返回值,适合做批量的插入和更新;函数有且仅有一个返回值,适合做处理数据后返回一个的结果
(2)
(3)sql语句(DML或SELECT)中不可用调用存储过程,而函数可以。
四.流程控制语句
4.1 分支语句
if结构:IF(表达式1,表达式2,表达式3):表达式1成立执行表达式2,不成立执行表达式三,
这个只是在平常语句中其实现多重分支语句(引用begin end),if 条件 then;elseif 条件2 then 语句2;…end if;
case判断值使用,最后结尾要加一个case end,和平常case语句中不一样
特点:可嵌套在其他语句中使用,也可以单独使用;执行when的条件或者值,执行then后面的语句,如果不满足往下执行,如果都不满足,执行else,如果没有else,则返回null
4.2 循环语句
分类:while loop repeat
while语法(先判断再循环): 标签名:【 while 循环条件 do 循环体 end while 标签】;
loop语法:标签 loop 循环体;end loop【标签】;(可以模拟简单的死循环 )
repeat语法(先执行后判断):【标签名】repeat 循环体;until(结束循环的条件) end repeatp[标签];
循环控制(跳出):iterate 类似于continue,结束本次循环;leave类似于break,直接跳出当前循环