【个人笔记】SQL操作基础

1.主键
分为逻辑主键与业务主键。
业务主键：具有真实意义的，比如身份证、银行卡等，一旦变化，难以维护。
逻辑主键：没有任何实际含义，只为了标识当前列在当前数据表里的位置的唯一标识。
不可以手动编辑，特殊情况下除外。
·每一张表都推荐有标识列·
·推荐使用逻辑主键·
·每一张表都推荐有主键列，并设置标识·
·主键标识列，就算数据被删除了，也会按照原先的标识增长

命令方式创建数据库

create database MySchool –数据库名
on
(
name=’MySchool_data’, –数据库名称
filename=’d:\MySchool_data.mdf’, –物理文件名
size=5mb, –初始大小
maxsize=10mb, –最大文件大小
filegrowth=15% –主文件增长率
)
log on
(
name=’MySchool_log’, –日志文件名
filename=’MySchool_log.ldf, –日志物理文件名
size=2mb,
maxsize=4mb,
filegrowth=1mb
)
go

命令方式创建表 - 要先选择要 创建表的位置

格式：
列名 类型 标识 主键 是否允许为空
id INT IDENTITY(1,1)
create table Student
(
id INT IDENTITY(1,1) PRIMARY KEY,
name NVARCHAR(16) NOT NULL,
age INT NOT NULL
)

DML数据库操作语言

·增删查改
·增加数据
INSERT INTO 表名(…列名) VALUES(…要插入的数据)
· INSERT INTO 表名 VALUES(…要插入的数据) –完全插入
· INSERT INTO 表名(…要插入的列名) VALUES(…对应的数据) –选择性插入

    注意：
        1.插入数据的时候，bit字段要用"0/1"表示True/False
        2.时间字段要用单引号括起，单引号中的内容要遵循基本的时间格式，如‘2014-10-10’
        3.INSERT语句中列的数目要和VALUE后值的数目一致
        4.不能为标识列插入数据（特殊情况下可以，SET IDENTITY_INSERT 表名 ON)(同理  SET IDENTITY_INSERT 表名 OFF)
        5.直接拖拽‘列’可以快速插入列名
        6.除了标识列，其他所有字段的值都要写出来，而且还要按照顺序写出来
        7.数据表中的 NULL 不是空指针也不是空字符串，而是表示数据库不知道为何值
        8.在服务器上执行的代码中显示的 UNICODE字符串常量 必须以大写字母 N 为前缀，避免插入特殊字符时无法识别
        9.INSERT INTO 表名(列名) VALUES(DEFAULT)
            VALUES 后的值可以为 DEFAULT 值，即默认值
        10.INSERT INTO Student(Name) VALUES('张''三'）
            插入的数据中有单引号的时候，需要输入两个单引号表示一个单引号（转义字符）


·删除数据
    DELETE FROM Student    --删除表中的所有数据
    TRUNCATA TABLE Studeng  --重置表 （包括重置标识）
        ·以上两者效率有很大差别，如果用 DELETE 会产生很多的日志，而使用 TRUNCATE 只会产生一行日志。
    DROP TABLE Student   --直接从数据库中删除该表（慎用）

·修改语句
    UPDATE 表名 SET 列名1=数据1,列名2=数据2··· WHERE 列名1=数据1( AND 列名2=数据2 AND 列名3=数据3 ···)( OR 列名2=数据2 ···）（ AND 列名2=数据2 OR 列名3=数据3）
        UPDATE Student SET age=age+1
        UPDATE Student SET age+=1
        update Student set Phone='10010' where name='王五' and Gender=1 or name='王五6'
        UPDATE Student SET Address='未知' WHERE Address IS NULL
        WHERE Address IS NOT NULL
        WHERE Address=' '
        WHERE Address

    注意：
        1. AND 优先于 OR 执行
        2. SET 的时候可以取出原来的值
        3. WHERE 中可以使用的其他逻辑运算符：(||)OR 、 (&&)AND 、 (!)NOT 、 < 、 > 、 >= 、 <= 、 <>(同!=)（不等）等 

约束：
    非空约束：数据不能为空
    主键约束：唯一且不能为空
    唯一约束：数据唯一，允许为空，但只能出现一次
    默认约束:如果不给值，则为默认值
    检查约束：范围以及各式限制。在设计界面的列名上右键-CHECK约束
    外键约束：添加主外键关系，外键的值必须来自主键表

·查询语句
    select 列名1( as 重命名),列名2( as 重命名)··· from 表名 where 条件
    select 列名1( as '重命名'),列名2( as '重命名')··· from 表名 where 条件
    select 列名1( 重命名),列名2( 重命名)··· from 表名 where 条件

    select 1+1  --计算表达式值
    SELECT GETDATE()  --方便快捷的取到服务器系统的时间

    -TOP
        --在查询出的整个结果集中取前一部分数据
        select top 10 * from Student  --取出Student表中前10条的所有数据
        select top 10 Name,Gender,Age from Student  --取出Student表中前10条的部分数据
        select top 10 percent * from Student  --从Student表中取出前10%的数据，所有计算的结果是进位的，如 21/10=2.1=>3

    -ORDER BY   （效率较低，能不使用就不使用）
        --将当前查询出来的数据结果进行排序，排序方式按照后面指定的列排序
        --order by 列名 desc  : 按照降序排序数据
        --order by 列名 asc   : 按照升序排序数据
        select top 10 * from Student order by id desc

    -DISTINCT
        --针对查询之后的结果去除重复行。两行数据中只要有一个数据不同就不认为是重复行。
        select distinct Name,Gender,Age from Student  --将查询出的整个结果集进行重复处理。

·聚合函数
MAX(列名） –返回一行数据
MIN(列名) –返回一行数据
AVG(列名) –返回一行数据
SUM(列名) –返回一行数据
COUNT(列名) –返回筛选成功的数据量。建议使用 COUNT(ID)

注：1.聚合函数对NULL值不计算。如果一行的值都是NULL，COUNT(*) 包含对空值行、重复行的统计。
    2.对于查询表中的所有数据，【select 分别列出所有的列名 from 表名】要比【select * from Student】 运行效率高。

例：select max(age) from Student  --取出表中的最大年龄
    select min(age),max(age),avg(age),sum(age) from Student  --计算出各种值
    select count(*) from Student where age=10  --计算符合要求的行的数据量

·排序规则
-先后顺序为 数字、字母、汉子（拼音）

·WHERE
- < > <= >= <> != 等判断符号

- BETWEEN...AND... （该函数经过优化，在大数据中使用时，明显查询效率）
    -例如 BETWEEN 20 AND 30 其显示的结果中包含20与30 
    -SELECT * FROM STUDENT WHERE AGE BETWEEN 20 AND 30

- IN (数据1,2...) （查询表中列中的值为括号中的值的数据）
    -SELECT * FROM STUDENT WHERE CID IN (1，2) 同 SELECT * FROM STUDENT WHERE CID=1 OR CID=2
    -SELECT * FROM dbo.Student WHERE Gender IN ('男','女')

注：1.BETWEEN and 在数据库内部是做过特殊优化的，执行效率比> and < 等这种方式快

·模糊查询
-使用系统已经定义好的匹配符，按照定义的规则筛选数据
-匹配符： _ % [] ^
_ 代表一个任意字符
% 代表零个或多个任意字符
[] 代表一个字符的取值区间
-不区分大小写
^ 配合[]使用，表示不属于该区间
-该匹配符数据库不兼容，SQL SERVER可以使用，其他数据库要使用 NOT LIKE

注：1.使用匹配符筛选数据效率很低
    2.当要转义匹配符时一般使用 [] 进行转义，而'^'不用，因为当'^'在[]外时就代表一个普通的'^'符号

-例如：SELECT * FROM STUDENT WHERE NAME LIKE '%王%' --从表中筛选出名字中有'王'字的人
       SELECT * FROM STUDENT WHERE NAME LIKE '王%'  --从表中筛选出姓'王'的人
       SELECT * FROM STUDENT WHERE NAME LIKE '_A%'  --从表中筛选出名字第二个字是'A'的人
       SELECT * FROM STUDENT WHERE NAME LIKE '%[A-Z]%]  --从表中筛选出名字中有字母的人
       SELECT * FROM STUDENT SHERE NAME LIKE '%[0-9]%'  --查询表中名字中有数字的人
       SELECT * FROM STUDENT WHERE NAME LIKE '%[0-9(，)A-Z]%'  --
       SELECT * FROM STUDENT WHERE NAME LIKE '%[^0-9A-Z]%'  --查询名字中没有数字和字母的人
       SELECT * FROM STUDENT WHERE NAME NOT LIKE '%[0-9A-Z]%'  --同上，查询名字中没有数字和字母的人
       特殊：
           SELECT * FROM STUDENT WHERE NAME LIKE '%[%]%'  --查询名字中有一个'%'的人
           SELECT * FROM STUDENT WHERE NAME LIKE '%[_]%'  --查询名字中有一个'_'的人
           SELECT * FROM STUDENT WHERE NAME LIKE '%['']%'  --查询名字中有一个 单引号 的人
           SELECT * FROM STUDENT WHERE NAME LIKE '%['']%'  --同上
           SELECT * FROM STUDENT WHERE NAME LIKE '%^%'  --匹配出名字中有一个'^'的人

·空值判断
-SELECT * FROM STUDENT WHERE ADRESS IS NULL
-SELECT * FROM STUDENT WHERE ADRESS IS NOT NULL

·ISNULL() 函数
-如：SELECT NAME,ISNULL(AGE,符合AGE类型的其他值) FROM STUDENT WHERE … –当查询的结果中，AGE的值为’NULL’，则显示为要替换的值

·类型转换
-CAST(AGE AS NVARCHAR(10)) –将原本为INT类型的AGE转换成NVARCHAR(10)类型
-CONVERT(INT,’123’) –将字符串’123’转换成INT类型
-

·GROUP BY
CAST(EXPRESSION AS DATA_TYPE)
CONVERT(DATA_TYPE,EXPRESSION,[STYLE])
-根据某一列数据进行分组，返回的数据表格中一行代表一组，通常配合聚合函数使用。
-例如：SELECT GENDER,MAX(AGE),MIN(AGE),SUM(AGE) FROM STUDENT GROUP BY GENDER
SELECT * FROM STUDENT GROUP BY GENDER
-注：1.分组后只能获得作为分组依据的原表数据，其他数据需通过聚合函数获得

-HAVING
    -其作用是对分组后的信息进行过滤。可用的参数为GROUP BY子句中的参数和聚合函数
    -例如：SELECT AGE,COUNT(ID) FROM STUDENT GROUP BY AGE HAVING AGE>25
           SELECT AGE,COUNT(ID) FROM STUDENT GROUP BY AGE HAVING COUNT(ID)>1

·SQL语句的执行顺序
1.SELECT -> DISTINCT -> TOP
2.FROM 表
3.WHERE 条件 –形成结果集
4.GROUP BY 列
5.HAVING 筛选条件
6.ORDER BY 列

`UNION 联合查询
-1.两个结果集必须具有相同的列数
2.列具有相同的数据类型（至少能隐式转换）
3.最终输出的集合的列名由第一个结果集的列名确定

-SELECT * FROM STUDENT WHERE AGE>25
 UNION
 SELECT * FROM STUDENT WHERE AGE<30
 UNITON
 ...
 （可进行多结果集联合，但结果中不会出现重复数据）

 -SELECT * FROM STUDENT WHERE AGE>25
 UNION ALL
 SELECT * FROM STUDENT WHERE AGE<30
 UNITON ALL
 ...
 （可进行多结果集联合，结果中可能包含重复数据）

·批量插入 = 插入语句 + 结果集
-将结果集当成值插入数据库，结果集的列数量、类型都要一样
INSERT INTO STUDENT(STUNO,ENGLISH,MATH)
SELECT 1,80,100 UNION
SELECT 1,80,100 UNION
SELECT 3,50,59 UNION ALL
SELECT 4,66,89 UNION
SELECT 5,59,100

 -可以复制一张表，但是没有主键
    Select * into Score2 from Score where 1<>1

·字符串函数
-LEN(STRING) 计算字符串中的字符个数
-DATALENGTH(STRING) 计算字符串的字节长度
-LOWER(STRING) 将字符串转换成小写
-UPPER(STRING) 将字符串转换成大写
-LTRIM(STRING) 去除字符串左侧的空白字符
-RTRIM(STRING) 去除字符串右侧的空白字符
-LEFT(STRING,LENGTH) 取得字符串从左侧开始取的LENGTH长度的字符串
-RIGHT(STRING,LENGTH) 取得字符串从右侧开始取的LENGTH长度的字符串
-SUBSTRING(STRING,START_NUM,LENGTH) 从字符串中第START_NUM个字符开始取LENGTH个字符

·日期函数
-GETDATE() 取得当前日期
-DATEADD(PART_NAME,NUM,DATE) 在DATE的基础上增加NUM的时间
例如：SELECT DATEADD(YEAR,2,GETDATE())
SELECT DATEADD(MONTH,2,GETDATE())
SELECT DATEADD(DAY,2,GETDATE())
SELECT DATEADD(HOUR,2,GETDATE())
…
-DATEDIFF(DATE_NAME,START_DATE,END_DATE) 取得以DATE_NAME为时间单位的两个时间的差
例如：SELECT DATEDIFF(MINUTE,GETDATE(),DATEADD(YEAR,2,GATDATE()))
-DATEPART(DATE) 取得一个日期的特定部分
例如：SELECT DATEPART(YEAR,GETDATE())
SELECT DATEPART(MONTH,GETDATE())
SELECT DATEPART(HOUR,GETDATE())
-YEAR(DATE) 取得时间的年
-MONTH(DATE) 取得时间的月
-DAY(DATE) 取得时间的日

·取绝对值 ABS(数值)

【高级运用】
-INSERT
1.在 INSERT 语句的 VALUES 前加上 OUTPUT INSERTED.列名 ，这样就能取得新增数据的列名值，和普通的 INSERT 语句相比，当使用 OUTPUT 时将返回列名值，与 @@IDENTITY 类似。

-DELETE
1. DELETED.列名 ，在 WHERE 前使用，效果同 INSERTED.列名 。（若删除多行，则返回多个值）

为已存在的表的列添加默认值约束
ALTER TABLE [表名] ADD CONSTRAINT [约束名] DEFAULT([默认值]) FOR [列名]