SQL基础

sql事务

sql事务的特性ACID

A：原子性 （数据操作的基本单位）

C：一致性 （确保数据库的完整性约束不能被破坏）

I：隔离性 （确保每个事务彼此独立，不受其他事务执行影响。在一个事务提交之前，对其他事务都是不可见的）

D：持久性（事务提交之后对数据的修改是持久性的）

注：原子性是基础，隔离性是手段，一致性是手段，持久性是目的

事务的控制

InnoDB（行锁）是支持事务的，MyISAM（表锁，不支持外键）存储引擎不支持事务，Oracle支持事务

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎

InnoDB 的特性主要有：

1. DML 操作(增、删、改)遵循 ACID(事务安全表) 模型，支持事务。

2. 支持外键（FOREIGN KEY）约束，保证数据的完整性和正确性。

3. 行级锁，提高并发访问性能。

   注：对事务的完整性有比较高的要求，在并发条件下要求数据一致性，数据操作除了插入和查询之外还有大量的删除更新操作则选用InnoDB存储引擎；若以读和插入操作为主则选用MyISAM存储引擎。

事务的控制语句：

1. START TRANSACTION 或者 BEGIN ，开启一个显式事务。（隐式事务自动提交）

2. COMMIT：提交事务。当提交事务后，对数据库的修改是永久性的。

3. ROLLBACK 或者 ROLLBACK TO [SAVEPOINT]，意为回滚事务。意思是撤销正在进行的所有没有提交的修改，或者将事务回滚到某个保存点。（回滚只能影响当前事务，已经提交的事务就无法进行影响）

4. SAVEPOINT：在事务中创建保存点，方便后续针对保存点进行回滚。一个事务中可以存在多个保存点。

5. RELEASE SAVEPOINT：删除某个保存点。

6. SET TRANSACTION，设置事务的隔离级别。

补充mysql的体系结构：连接层，服务层，引擎层，存储层

存储引擎是基于表的，而不是基于库的即存储引擎也可称成为表类型

事务的隔离级别

读未提交：允许读未能提交的数据，该情况下不会使用锁但可能产生脏读，幻读，不可重复读等

读已提交：只允许读已经提交的数据，能避免脏读若需要避免幻读和不可重复读则需要在sql查询语句上加上锁

可重复读：保证同一个事物在相同的查询条件下两次查询得到的数据结构都是一致的，可避免不可重复读和脏读但无法避免幻读

可串行化：将事务进行串行化，可解决事务读取的所有出现的异常情况但牺牲了系统的并发性

注：幻读是指查询某一个范围的数据行变多了或则变少了，重点在于insert；不可重复读是指同一条记录的内容被修改了，重点在于update和delete

sql server中的锁

锁的使用划分

共享锁：当需要读取对象时，会发生锁定类型。用于所有的只读数据操作。读取之后立即释放 READ 表 lock in share mode 数据集

对于表添加共享锁

begin；
select * from student（表名） where id=1 READ;
commit;

对于数据集添加共享锁

begin;
select * from studentinfo where id=1 lock in share mode;
commit;

对表解锁  unlock  table

排他锁（独占锁）;以防止其他事务修改或访问锁定对象。用于对数据进行写操作 WRITE 表

更新锁：和独占锁类似，差异：将更新操作划分为不同的阶段，读取阶段和写入阶段，在读取读取阶段不希望其他事务有权访问此对象以进行更改。用来预定要对资源施加排他锁，允许其他事务读但不允许再施加更新锁或排他锁。一直到事务结束时才能被释放

意图锁：当SQL Server获取页面或行上的锁时，表中需要设置意图锁

注：对于update insert delete语句，自动给相关数据加上排他锁；加上共享锁不能写但对于当前进行的事务加上共享锁，该事务可以写

锁的粒度划分

行级锁，页级锁，表级锁

锁与隔离级别的关系

1. 在 Read Uncommitted(读未提交) 级别下，读取数据不需要加共享锁，这样就不会跟被修改的数据上的排他锁冲突；

2. 在 Read Committed（读已提交） 级别下，读操作需要加共享锁，但是在语句执行完以后释放共享锁；

3. 在 Repeatable Read （可重复读）级别下，读操作需要加共享锁，但是在事务提交之前并不释放共享锁，也就是必须等待事务执行完毕以后才释放共享锁；

4. 在 SERIALIZABLE （可串行化）级别下，限制性最强，因为该级别锁定整个范围的键，并一直持有锁，直到事务完成。

sql中的join函数

join函数名称	用法
inner join	表和表之间的交集
left join	以左表为基础，对照右表返回左表全部记录
right join	以右表为基础，对照左表返回右表全部记录
full outer join	只要两个表中，某一个存在匹配则返回行

inner join（内链接，可以省略inner）：表与表之间的交集

 

left join (左连接)：以左边的表为基础，对照右边的表返回左表中所有满足条件的记录，不管右表中有没有关联的数据。

 

 right join（右连接):与左连接相反

full outer join:只要左表和右表其中一个表中存在匹配，则返回行

full join：只要其中某个表存在匹配，则返回行

注：某些表中full outer join和full join是一样的

sql中的where函数

多个条件则用“and”或“or“连接。交集（和）条件用”and“；并集（或）条件用”or“

where函数是一个约束声明，在数据库的结果返回之前对数据库中的查询条件进行约束，即在结果返回之前起作用，后面不能跟”聚合函数“

having函数是一个过滤声明，在查询数据库结果返回之后进行过滤，后面可以跟”聚合函数“

having是弥补where后面不能跟聚合函数的局限

注：聚合函数一般跟group by 在一起使用，聚合函数（sum,avg,max,min等）返回一组的唯一数值。使用聚合函数时注意所使用的条件如sum则需要在数字列使用。

sql分类

DDL（数据定义语言） ：

DML（数据操作语言）：insert,update,delete（删除能回滚）,truncate(删除不能回滚，删除时不会产生日志也不能有撤销操作需谨慎使用。)

DCL（数据控制语言） ：create,crant,revoke,show,drop

DQL（数据查询语言) ：

1. select 列名 ----> 要查询的列名称

2. from 表名 ----> 要查询的表名称

3. where 条件 ----> 行条件

4. group by 分组列 ----> 对结果分组

5. having 分组条件 ----> 分组后的行条件

6. order by 排序列 ----> 对结果分组（默认升序，asc升序，desc降序）

7. limit 起始行, 行数 ----> 结果限定（mysql方言）

sql函数

SQL Srever聚合函数

聚合函数	SQL srever定义	SQL srever语法
count	使用 COUNT(*) 能对整张表的内容（行）进行计数，不管内容（行）是有值还是空值;使用 COUNT(字段) 能对某一字段的内容（行）进行计数，但是会忽略 NULL 值	SELECT COUNT(*) FROM 表名 ；SELECT COUNT(字段名) FROM 表名
max	返回某一字段（列）的最大值	SELECT MAX(字段名) FROM 表名
min	返回某一字段（列）的最小值	SELECT MIN(字段名) FROM 表名
sum	返回某一字段的数值之和	SELECT SUM(字段名) FROM 表名
avg	AVG() 函数通过计算某字段（列）内容（行）的个数和它们的数值之和来返回某一字段的平均值。	SELECT AVG(column_name) FROM table_name
distinct	用于删除指定删除指定集中的重复值，并返回结果集	SELECT DISTINCT(字段名) FROM 表名

mysql聚合函数相同与sql srever聚合函数

PostgreSql聚合函数

聚合函数	PostgreSql定义	PostgreSql语法
array_agg(expression)	把表达式变成一个数组， 一般配合 array_to_string() 函数使用	SELECT 列名, ARRAY_AGG() FROM 表名
string_agg(expression, delimiter)	直接把一个表达式变成字符串	SELECT 列名 ,STRING_AGG() FROM 表名
avg(expression)	所有非空输入值的平均值（算术平均）	SELECT 列名 ,AVG() FROM 表名
bit_and(expression)	所有非空输入值的按位与，如果没有非空值则结果是空值
bit_or(expression)	所有非空输入值的按位或，如果没有非空值则结果是空值
bool_and(expression)	如果所有输入值为真则结果为真，否则为假
bool_or(expression)	至少一个输入值为真时结果为真，否则为假
count(*)	输入的行数
count(expression)	expression值非空的输入行的数目
every(expression)等价于	等价于bool_and
json_agg(expression)	将值，包含空值，聚集成一个 JSON 数组
max(expression)	所有非空输入值中expression的最大值
min(expression)	所有非空输入值中expression的最小值
sum(expression)	所有非空输入值的expression的和

聚合函数：返回汇总值

1. AVG(表达式) 返回表达式中所有的平均值。仅用于数字列并自动忽略NULL值。

2. COUNT(表达式) 返回表达式中非NULL值的数量。可用于数字和字符列。

3. COUNT(*) 返回表中的行数(包括有NULL值的列)。

4. MAX(表达式) 返回表达式中的最大值,忽略NULL值。可用于数字、字符和日期时间列。

   注：使用max时需要有特定的字段（列）与之配对，不能使用通配符*作为参数。

5. MIN(表达式) 返回表达式中的最小值,忽略NULL值。可用于数字、字符和日期时间列。

6. SUM(表达式) 返回表达式中所有的总和,忽略NULL值。仅用于数字列。

SQL Srever转化函数

转化函数	SQL Srever定义	SQL Srever语法
cast	一般用于小数转数值和字符型	SELECT CAST（数据 as 类型）FROM 表
convert	一般用于日期和时间类型以及小数之间转换	SELECT CONVERT（类型 ，数据）FROM 表

PostgreSql转化函数

转化函数	PostgreSql定义	PostgreSql实例
to_char(timestamp, text)	将时间戳转换为字符串	to_char(current_timestamp, ‘HH12:MI:SS’)
to_char(interval, text)	将时间间隔转换为字符串	to_char(interval ‘15h 2m 12s’, ‘HH24:MI:SS’)
to_char(int, text)	整型转换为字符串	to_char(125, ‘999’)
to_char(double precision, text)	双精度转换为字符串	to_char(125.8::real, ‘999D9’)
to_char(numeric, text)	数字转换为字符串	to_char(-125.8, ‘999D99S’)
to_date(text, text)	字符串转换为日期

转换函数：将一种数据类型转换成另一种

有convert和cast，convert和cast语法不同。在时间转化中一般用convert，convert比cast多了一个style

cast强制转换，convert显示转换

convert语句用法：

Select convert(varchar(10) ,orgid) as orgid,orgname from enterprise；

cast语句用法：

Select cast(orgid as varchar(10)) as orgid,orgname from enterprise；

MySQL日期函数

日期函数	MySQL定义	MySQL语法
GETDATE()	当前的系统日期	SELECT GETDATE()
DATEADD(日期部分,number,date)	返回带有指定数字(number)的日期(date),该数字添加到指定的日期部分(datepart)	select DATEADD(dd, 5, getdate())
DATEPART(日期部分,date)	返回日期中指定的日期部分的整数形式	select DATEPART(dw,GETDATE())
YEAR(date)	返回指定日期的年份数值	select YEAR(GETDATE())
DATEDIFF(日期部分,date1,date2)	返回两个日期中指定的日期部分之间的差值	select DATEDIFF(mm, '2010-1-1', '2010-3-1 00:00:00')
DATENAME(日期部分,date)	返回日期中日期部分的字符串形式	select DATENAME(dw,GETDATE())

PostgreSql日期函数

日期函数	描述	PostgreSql实例
timestamp	日期加减	SELECT now()::timestamp + '1 year' -当前时间加一年

日期函数：处理日期和时间

数学函数：执行算术运算

字符串函数：对字符串、二进制数据或表达式执行操作

系统函数：从数据库返回在SQLSERVER中的值、对象或设置的特殊信息

文本和图像函数：对文本和图像数据执行操作

MySQL字符串函数

函数	功能
concat(s1,s2,s3,...,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个长度的字符串

update tablename set workno=lpad(workno,5,'0') #更新tablename表中的workno字段将其不足五位数的工号用0补充成五位

MySQL数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回X/Y的模
rand()	返回0~1内的随机数
round(x,y)	求参数x的四舍五入的值，保留y位小数

select lpad(round(rand()*1000000,0),6,0) #通过数据库函数，生成一个六位数的随机验证码

流程函数

函数	功能
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,......否则返回defalut默认值

select id,name,
(case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end)'数学'
(case when english>=85 then '优秀' when english>=60 then '及格' else '不及格' end)'英语'
from corse