数据库_SQL的硬知识

SQL

SQL 概念

1、SQL是一种结构化查询语言
2、按照一定的数据结构来组织、存储、管理数据的仓库
3、RDBMS：关系数据库管理系统

数据库表

1、每个表包含列名、标识、属性

数据库语法

可以将SQL分为两个部分，即数据操作语言（DML）和数据定义语言（DDL）
DML语法：

1、SELECT从数据库表中获取数据
2、UPDATE 更新数据库表中的数据
3、DELETE从数据库表中删除数据
4、INSERT INTO向数据库表中插入数据

DDL语句

1、CREATE DATABASE  创建数据库
2、ALTER DATABASE 修改数据库
3、CREATE TABLE 创建表
4、ALTER TABLE 修改数据库表
5、DROP TABLE 删除表
6、CREATE INDEX 创建引索
7、DROP INDEX 删除引索

SQL SELECT语法

基本语法：SELECT 列名 FROM 表名

select * from a
//这里*号指代全部，a为表名

distinct

distinct可以除去重复值
语法:SELECT DISTINCT 列名 FROM 表名
实例： SELECT DISTINCT username FROM name

where

where用于有条件的从表中选取数据
语法：select 列名 from 表名 where 列运算符 值
实例：select username from user where username ="张三"

AND和OR

1、and类似于&，需要两个条件都成立，则and运算符显示一条记录
2、而or是两个条件只要一个成立就显示一条记录
一般结合where使用

order by

order by 是对指定列的值进行排序，默认排序是升序，可以使用desc进行降序排序
实例：select * from user order by id desc

insert into

insert into 用于向表中插入新的行（数据）
语法:insert into 表名 values (值1，值2，。。。。)
实例：

//向指定列插入数据
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

//不指定列的话需要将所有数据都写上
insert into persons(1,'zhang','shan','shanghai')

update

语法：update 表名 set 列名=新值 where 列名 =值

//更新某列中的值
update person set firstname='zhang' where lastname='lin'

delete

delete用于删除表中的行
语法：delete from 表名 where 列名=值
删除所有数据delete * from table_name

高级语法

limit

limit 相当于SQL的top，limit是mysql的语法，用于返回规定的记录数目
实例：

//返回头五条记录
select * from user limit 5

like

like 一般用于模糊查找

//查找“N”开头的城市
select * from persons where city like 'N%'

//查找以“g”结尾的城市
select * from persons where city like '%N'

//查找包含N的城市
select * from persons where city like '%N%'

//查找不包含N的城市
select * from persons where city not like '%N%'

通配符

%代替一个或多个
_代替一个
[charlist]字符列中的任何单一字符
[!charlist]不在字符列中的任何单一字符
实例：

//_代替一个
select * from persons where lastname like 'b_ij_n'

//[charlist]查找以A或B开头的人
SELECT * FROM Persons WHERE City LIKE '[AB]%'

//[!charlist]不查找以A或B开头的人
SELECT * FROM Persons WHERE City LIKE '[!AB]%'

IN

in可以让我们在where语句中规定多个值
语法：select column_name from table_name where column_name in (value1.value2)

//查找姓李和张的人
select * from user where username in ('李‘，’张’);

between

between是用于两个值之间范围选取
实例：

//显示字母顺序显示介于a和b的人
select * from user where username between 'a' and 'b'

//不显示字母顺序显示介于a和b的人
select * from user where username not between 'a' and 'b'

AS

可以为表名列名其别名，当查询语句长，列名复杂且重复使用时
实例 ：

//将列名分别为A， B，表名为C
select username as A ,password as B from user as C 

//将表名简化
select A.*,B.name from user as A inner join class as B

连接

JOIN: 如果表中有至少一个匹配，则返回行
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行

inner join

inner join 内连接
实例：

//从两个表中查询该学生的id和姓名以及成绩
select user.name ,user.id,class.scoure from user inner join scoure on user.id=class.userid

union和union all

用于合并多个select查询结果集
1、union默认不允许重复值，union all允许重复值
实例

select column from table 
union all
select column from table

into

select into 用于从一个表中选取数据，然后把数据插入另一个表中，常用于备份
实例：

select * into user from old_user

创建数据库

CREATE datebase 数据库名

创建数据库表

create table 表名（列名 数据类型，列名 数据类型，。。。。。）
实例：

CREATE TABLE user
(
Id int,
username varchar(255),
password varchar(255),
create_date varchar(255),
male varchar(5)
)

约束

1、not null 非空
2、unipue唯一
3、primary key 主键
4、foreign key 外键
5、check约束
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。

如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
实例：

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (Id_P>0)
)

6、default向列中插入默认值
实例：

CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

INDEX引索

可以在表中创建索引，以便更加快速高效地查询数据，用户无法查看引索，只用来加速查询
实例：

CREATE INDEX index_name
ON table_name (column_name)

drop index删除引索语法：

DROP INDEX index_name ON table_name

drop table 表名 删除表

//删除数据库
drop database 数据库名

//清除表数据
truncate table 表名

alter table

1、改变表中列的数据类型

ALTER TABLE table_name
ALTER COLUMN column_name datatype

2、删除列

ALTER TABLE table_name 
DROP COLUMN column_name

3、添加列

ALTER TABLE table_name
ADD column_name datatype

auto increment

auto increment 自动地创建主键字段值

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

SQL函数

avg（）

返回列的平均值
实例

select avg(age) as average from user

count()计数

first（）返回指定字段第一个记录值

last返回指定字段最后一个值

max（）返回一列中最大的值

min()返回一列中最小的值

sum（）相当于加法，返回一列的总数

group by 对结果集进行分组

范式

第一范式：列不能再分

第二范式：建立在第一范式基础上，无部分依赖

第三范式： 建立在第二范式基础上，无传递依赖。

事务

概念：事务(Transaction)，一般是指要做的或所做的事情。在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。在计算机术语中，事务通常就是指数据库事务。

1、特性

隔离性、一致性、原子性、持久性

2、释义：

原子性（Atomicity）：事务作为一个整体被执行，包含在其中的对数据库的操作要么全部被执行，要么都不执行。
一致性（Consistency）：事务应确保数据库的状态从一个一致状态转变为另一个一致状态。一致状态的含义是数据库中的数据应满足完整性约束。
隔离性（Isolation）：多个事务并发执行时，一个事务的执行不应影响其他事务的执行。
持久性（Durability）：一个事务一旦提交，他对数据库的修改应该永久保存在数据库中

锁

共享锁

S锁，也叫读锁，用于所有的只读数据操作。共享锁是非独占的，允许多个并发事务读取其锁定的资源。
性质

1. 多个事务可封锁同一个共享页；
2. 任何事务都不能修改该页；
3. 通常是该页被读取完毕，S锁立即被释放。

排他锁

X锁，也叫写锁，表示对数据进行写操作。如果一个事务对对象加了排他锁，其他事务就不能再给它加任何锁了。

1. 仅允许一个事务封锁此页；
2. 其他任何事务必须等到X锁被释放才能对该页进行访问；
3. X锁一直到事务结束才能被释放。

更新锁

U锁，在修改操作的初始化阶段用来锁定可能要被修改的资源，这样可以避免使用共享锁造成的死锁现象。
性质

1. 用来预定要对此页施加X锁，它允许其他事务读，但不允许再施加U锁或X锁；
2. 当被读取的页要被更新时，则升级为X锁；
3. U锁一直到事务结束时才能被释放。
   场景
   因为当使用共享锁时，修改数据的操作分为两步：
4. 首先获得一个共享锁，读取数据，
5. 然后将共享锁升级为排他锁，再执行修改操作。
   这样如果有两个或多个事务同时对一个事务申请了共享锁，在修改数据时，这些事务都要将共享锁升级为排他锁。这时，这些事务都不会释放共享锁，而是一直等待对方释放，这样就造成了死锁。
   如果一个数据在修改前直接申请更新锁，在数据修改时再升级为排他锁，就可以避免死锁。