SQL入门

数据库特点：

并发性，完整性，安全性，海量数据

 

数据库三范式（依次递进）：

列不可分割

唯一标识

在引用其他表的数据时，要有其他表的唯一标识

 

脚本操作：

对库进行的操作：

create, alter, drop(创建、修改、删除)

对表进行的操作：

insert, delete, update, select(增、删、改、查)

字段类型：

int, bit, datetime, decimal, char/varchar/nvarchar

demical(4, 1) 表示四位，一位小数

 

创建库：

--创建数据库
create database dbtest
on primary
(
    name = '%%%%',
    filename = '文件路径\名称.mdf'，
    size = 10mb,
    maxsize = 100mb,
    filegrowth = 10mb
)
log on
(
    name = '%%%%',
    filename = '文件路径\名称.ldf'，
    size = 2mb,
    filegrowth = 1mb
)

--修改数据库
alter database dbtest

--删除数据库
drop database dbtest

创建表：

--创建表
use dbtest

create table ClassInfo
(
    cId int not null primary key identity(1,1),
    cTitle nvarchar(10)
)

 数据的相关操作：

--关闭自增列
set identity_insert stus off

--插入操作
insert into 表名(列名, 列名, ...)
values(值, 值, ...)

--批量插入
insert into 表名(列名, 列名, ...)
values(值, 值, ...),(值, 值, ...), ...

--修改操作
update 表名 set 列名1 = 值1, 列名2 = 值2, ... where

--删除操作
delete from 表名 where
--在执行删除操作后，再次添加数据时，表中被设定为自加1的列会保存之前的数值，使得值不连续

--清空操作(保留表，数据全删除，自加1的列重置)，被外键引用的话不能清空
truncate table 表名

常用辅助命令及快捷键：

--set statistics time on/off:在消息栏显示详细执行时间
--ctrl+e:执行
--ctrl+r:隐藏消息栏
--ctrl+l:计划任务，对SQL语句进行分析

查询相关操作：

--为表、列起别名: as
--表示查看前n行: top n 列名
--表示查看前百分之几的数据: top n percent 列名
--排序: order by 列名1 asc|desc(升序|降序), 列名2 asc|desc(升序|降序), ...
--消除重复行: distinct(直接放在select后, 相应列之前)
--条件查询: 写在where后面
    --比较运算符
    --在一个连续的范围内: between 值1 and 值2
    --在一个费连续的范围内: in (值1, 值2, ...)
    --逻辑运算符: and, or, not
    --模糊查询:
        --查询相似的值: like
        --对未知值的个数不确定: %
        --对未知值的个数确定(一个字符): _
        --在一个连续的范围可以使用: []
        --写在[]内部的开头，表示不使用内部的任何字符: ^
    --null的判断: is null | is not null
    --优先级(降序): 小括号, not, 比较运算符, 逻辑运算符

连接查询：

select *
from 表1
--内连接(取两表相交的数据,去除两表特有的数据)
inner join 表2 on 表1.列名 = 表2.列名
--可同时连接多个表中的数据,被连接的表只要与已连接过后的任何一个表有对应关系即可
inner join 表3 on 表2.列名 = 表3.列名 

--left join 表2 on 表1.列名 = 表2.列名 左连接(取两表相交的数据,保留表1特有的数据)
--right join 表2 on 表1.列名 = 表2.列名 右连接(取两表相交的数据,保留表2特有的数据)
--full join 表2 on 表1.列名 = 表2.列名 全连接(取两表相交的数据,同时保留两表特有的数据)

聚合函数：

--对行数据进行合并,遇到null时不做操作，即忽视该数据
--sum(求和), avg(平均), count(计数), max(最大值), min(最小值)

开窗函数：

--将统计出来的数据分布到原来的每一行中，结合聚合函数、排名函数使用
select 表名.*, avg(列名) over()
from 表名

分组：

--根据相应列分组
select *
from 表名
group by 列名1, 列名2, ...
--对分组后的数据进行筛选
having 条件

联合查询：

--将多个查询的结果集合并成一个结果集
--合并数据、并删除重复行: union
--合并数据、并保留重复行: union all
--保留多个结果集中都包含的结果(交集): intersect
--保留结果集中前一个的特有结果(差集): except

数据备份：

--向未有表备份,会新建表,表的结构完成一致,但是不包含约束,想要不包含数据加一个不可能的条件即可
select 列名 into 备份表名 from 源表名
--向已有表备份, 插入部分数据
insert into 备份表名 select 列名 from 源表名

内置函数：

--类型转换函数
CAST(值 as data_type[(length)])
CONVERT(data_type[(length)], 值)

--字符串函数
    --求单个字符的ascii值
    ascii
    --根据ascii转到字符
    char
    --字符串截取, 从1开始, 截几个自己决定(分别为自左向右, 自右向左, 任意位置开始, )
    left, right, substring
    --返回字符串的长度
    len
    --返回字符串的长度(包含可显示字符后面的空格)
    datalength
    --英文转小写、大写
    lower, upper
    --去空格(左侧、右侧)
    ltrim, rtrim

--日期函数
    --获取当前日期时间
    getDate
    --日期的加运算
    dateADD
    --日期的差运算
    dateDiff
    --取日期的某部分
    datePart
获取年份示例: select datePart("Dayofyear", GETDATE())

 

视图：将一个复杂的select语句进行封装，以更方便使用，

注意：视图中存储的是select语句，而不是结果集数据

视图语法：

创建视图：create view 名称 as select ...

修改视图：alter view 名称 as select ...

删除视图：drop view 名称

建议：可将视图名称当做一个表名，进行各种查询操作，不建议通过视图进行增加、修改和删除操作 

 

子查询：将一个或多个查询语句嵌入另一个查询语句中

条件部分出现的运算符：=/exists/in

 

数据的行列转换：case语句

 

t-sql编程

变量：

-》声明：declare 变量名 类型--变量名要求以@开头

-》设置：set/select 变量名 = 值

-》输出：print/select 变量名

-》全局变量：使用双@符号

特殊语句：

@@version --数据库版本

@@identity --进行插入后调用，返回标示值

@@servername --服务器名称

@@error --返回执行的上一个Transact-SQL语句的错误号

@@rowcount --返回受上一语句影响的行数

选择语句：

--选择语句
if 条件
begin 
    --满足条件时，执行如下代码
    print 'ok'
end
else
begin
    --不满足条件时，执行如下代码
    print 'no'
end

--循环语句
while
begin
    --满足条件时，执行如下代码
end

--异常处理语句
begin try
    --具体操作
end try
begin catch
    --上述操作有误时，执行如下代码
end catch

 

事务：一个最小的不可分割的单元，能够保证一个业务的完整性

特性(ACID)：

A:原子性(事务是最小的单位，不可分割)

C:一致性(同一事务中的多条SQL语句，必须保证同时成功或者同时失败)

I:隔离性(不同事务之间互不影响)

D:持久性(一旦结束便不可撤销)

比如银行转账： 

--  a -> -100
    update user set money = money - 100 where name = 'a';
--  b -> +100
    update user set money = money + 100 where name = 'b';

实际的程序中，如果只有一条程序执行成功了，另外一条没有执行成功，结果不堪设想 

多条SQL语句，可能有同时成功的要求，不然就同时失败

 

MySQL默认开启事务

mysql> select @@autocommit
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

默认事务开启的作用：

当相关人员执行一个SQL语句时，语句会立即生效且不能回滚

回滚：rollback语句

开启事务的方法：

1.自动开启： set autocommit 1/0; (直接完成事务，不可回滚)

2.手动开启：commit; (直接完成事务，不可回滚)

3.手动开启：begin; (仅开始事务，可手动完成事务，亦可回滚) 

4.手动开启：start transaction; (仅开始事务，可手动完成事务，亦可回滚)

 

事务的隔离性：

1.read uncommitted;     读未提交的

2.read commited;          读已经提交的

3.repeatable read;         可以重复读

4.serializable;                串行化

 

查看数据库的隔离级别：

mySQL 8.0

--系统级别
select @@global.trancaction_isolation;
--会话级别
select @@trancaction_isolation;

更改数据库的隔离级别：

mySQL 8.0

--系统级别
set global trasaction isolation level 相关级别;
--会话级别
set trasaction isolation level 相关级别;

1.read uncommitted;

如果有事务a，和事务b

a事务对数据进行操作，在操作的过程中，事务没有被提交，但是b可以看见a操作的结果

脏读：一个事务读到了另外一个事务没有提交的数据(需要该数据进行相关操作)

2.read commited;

如果有事务a，和事务b

a事务对数据进行操作(读取)，同时b对数据进行操作，b在操作的过程中，事务被提交，a可以看见b操作的结果

不可重复读：一个事务没读到另外一个事务提交的数据(进行相关操作时数据增加)

3.repeatable read;

如果有事务a，和事务b

a事务对数据进行操作(读取)，同时b对数据进行操作，b在操作的过程中，事务被提交，a不可以看见b操作的结果

幻读：一个事务没读到另外一个事务提交的数据(进行相关操作时数据冲突)（同时向表中加入一样的value）

4.serializable;

如果有事务a，和事务b

a事务对数据进行操作(读取)，同时b对数据进行操作，b在操作的过程中，事务被提交，a可以看见b操作的结果

b继续对数据进行操作，只要a操作未完成(commit)，b的相关操作会卡住，直到a操作完成

阻塞：一个事务未操作完成时，另外一个事务无法完成操作

 

性能：

read uncommitted > read committed > repeatable read > serializable

隔离级别越高，性能越差

默认级别为：repeatable read

 

创建存储过程：

--创建去掉两边空字符存储过程trim
create proc trim
    @str1 varchar(10)
as
begin
    select LTRIM(RTRIM(@str1))
end


--执行上面创建好的存储过程
exec trim '  abc  '