SQL 基础

DBMS（DataBase Management System，数据库管理系统）和数据库。平时谈到“数据库”可能有两种含义：MSSQLServer、Oracle等某种DBMS；存放一堆数据表的一个分类（ Catalog ）。
不同品牌的DBMS：MYSQL（中型数据库，开源，免费，速度很快，适合对数据要求并不是十分严谨的地方，去掉了很多中小型企业中不常用的功能）、MSSQLServer（大中型数据库，与.net结合很好）、DB2（大型）、Oracle（大型）、Access（文件）等

主键:
　　唯一的标识一行数据
　　可以作为其它表的外键来引用
　　业务主键,使用有业务意义的字段做主键
　　逻辑主键,指业务无关,单纯的用来标识行
主外键:
　　减少数据冗余
　　维护方便
　　可以优化查询效率
两张表存在依赖数据时,就可以使用主外键来解决,其中将依赖列作为主键的就叫做为主键表,另一个就叫外键表,外键表的外键列数据取自主键表的主键
为了避免两张表的主外键数据出现不一致情况,需要建立主外键约束关系,当两张表数据修改时出现主外键不一致,则报错,拒绝修改

SQL语句中字符串用单引号,单等号,大小写不敏感
SQL语句中的优先级 not and or
char类型,当储存的数据小于长度时,会自动用空格来补充
Nchar类型,使用unicode编码,任意字符都占用两个字节
varchar类型,当储存的数据小于长度时,不会自动占用空格来补充
datetime类型,储存日期时间数据,如果要用代码来表示一个具体时间,需要加上单引号

Sql主要分为DLL(数据定义语言,建表,建库等)和DML(数据操作语言)和DCL(数据库控制语言)
--创建数据库
CREATE DATABASE 数据库名
ON [PRIMARY]
(
<数据文件参数> [，…n]
)
[LOG ON]
(
<日志文件参数> [，…n]
)

CREATE DATABASE Student
ON PRIMARY --默认就属于PRIMARY主文件组，可省略
(
NAME=‘Student‘, --主数据文件的逻辑名
FILENAME=‘E:\Student_data.mdf‘, --主数据文件的物理名
SIZE=3mb, --主数据文件初始大小
MAXSIZE=10mb, --主数据文件最大的值
FILEGROWTH=15% --主数据文件的增长率
)

LOG ON --日志文件
(
NAME=‘Student_log‘,
FILENAME=‘E:\HeiMaBlog8_log.ldf‘,
SIZE=3mb, --日志文件初始大小
MaxSize=20mb,
FILEGROWTH=1MB
)
USE HeiMaBlog--将当前数据库设置为HeiMaBlog
GO
CREATE TABLE Score
(
ScoreId INT IDENTITY(1,1),
SId INT NOT NULL ,
English INT NOT NULL,
Math INT NOT NULL
Name Varchar(50) not null
)

go关键字并不是SQL语法的规范,而是我们MSSQLSERVERl软件客户端的关键字,客户端遇到go时,会把go前面的代码先发到数据库服务去执行,然后再运行go下面的SQL代码语句
--修改表结构
　　增加一个列
　　　　Altertable tabname addcolumn col type
　　　　注：列增加后将不能删除。DB2中列加上后数据类型也不能改变，唯一能改变的是增加varchar类型的长度。
　　添加主键： Altertable tabname addprimarykey(col)
　　删除主键： Altertable tabname dropprimarykey(col)
创建索引：create[unique]index idxname on tabname(col….)
删除索引：dropindex idxname
　　注：索引是不可更改的，想更改必须删除重新建。
创建视图：createview viewname asselect statement
删除视图：dropview viewname
--插入语句
insert into表(列名1,列名2...)values(value1,value2...)
insert into Teacher (Name,Gender,Age,Salary,Birthday)
values(N‘杨老师‘,1,18,100000.121,‘1984-01-01‘)
为自增长字段手动插入标识
set Identity_insert Teacher ON --打开手动插入标识 默认为OFF
N前缀:N‘字符串‘,在服务器上执行代码中(例如在存储过程和触发器中)显示的Unicode字符串常量必须以大写字母N开头
当要向表中新增的数据中,包含所有的(自动增长的列除外)的值,可以不写出列名
--更新语句
update 表名 set 列名=值
update Teacher set Gender=0,Age = 20 where Name=‘杨老师‘
--删除语句
delete 表名 --自增列不会重置
--高效删除
truncate table 表名 --删除表中的所有数据,自增字段将重置
　　1.truncate语句非常高效。由于truncate操作采用按最小方式来记录日志，所以效率非常高。对于数百万条数据使用truncate删除只要几秒钟，而使用delete则可能耗费几小时。
　　2.truncate语句会把表中的自动编号重置为默认值。
　　3.truncate语句不触发delete触发器
--删除表
drop table 表名 --完全删除,包括表结构
--检索语句
select * form 表名
　　尽量不要使用*,因为这样会先到数据库系统表中查询所有的列,
--为结果集的列取别名
select Id as 编号,name as 姓名 form Teacher

约束-保证数据完整性
非空约束
主键约束(PK) primary key constraint 唯一且不为空
唯一约束(UQ) unique constraint 唯一,允许为空,但只能出现一次
默认约束(DF) default constraint 默认值
检查约束(CK) check constraint 范围及格式限制
外键约束(FK) foreign key constraint 表关系,保证外键值来源于主键
Top 用于获取前几条数据
　　select top 10 * from teacher --获取前10条记录
　　select top 10 percent * from teacher --前10%的记录(如果是小数 则向上取整ceiling)
distinct 取消重复,针对查询出的整个结果集
　　select distinct age from teacher
聚合函数 -- 聚合函数的结果集是单个值,没法与多个值的结果集结合
　　max() min() sum()
　　avg() --如果列是整数,平均值是小数,则向下取整-floor
　　count()--count会忽悠掉()中的null值,如果一行的数据都是null 而count(*)依然会记数
条件查询
　　where ...<>--表示不等于 < >
　　between ... and ... --取两者之间的数,包括边界
　　in(8,10,11,13) 当使用子查询配合in关键字时,子查询的结果集必须只有一个类,而且列的类型必须和条件列类型一不能致
模糊查询
　　like ‘ ‘
　　通配符 _(任意单个字符) %(任意长度字符) [](匹配括号中的任意单个字符) ^(表示取非,必须放到中括号中使用)
空值处理
　　null代表不知道,而不是表示没有
　　select * from teacher where name is null/is not null
空值处理函数：
　　ISNULL(expression,value) 如果expression不为空则返回expression,否则返回value
数据排序
　　order...by...asc/desc 要放到where语句之后
　　select * from teacher order by age desc,Id asc--多条件排序
数据分组
group...by...
　　select gender from teacher group by gender
　　分组的结果是一个集合的信息,与单行信息无关
　　group by子句必须放到where语句之后,与order by一样都是对筛选后的数据进行处理,而where是用来筛选数据的
　　没有出现在group by子句中的列是不能放到select中的(聚合函数除外)
　　select count(*),age,gender from teacher group by age,gender --多条件分组,只有当age和gender的值一样时,才分为一组
having语句
　　having用于对分组后的数据进行筛选,而where语句用于对表中的数据进行筛选
　　select count(*),gender from teacher group by gender having gender = 1
　　在Where中不能使用聚合函数，必须使用Having，Having要位于Group By之后
一次插入多条数据
　　insert into Score(studentId,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 newStudent from student（newStudent表在select查询的同时自动建立。）
复制表(只复制结构,源表名：a 新表名：b) (Access可用)
　　方法一：select * into b from a where 1<>1（仅用于SQlServer）
　　方法二：select top 0 * into b from a
拷贝表(拷贝数据,源表名：a 目标表名：b) (Access可用)
　　insert into b(a, b, c) select d,e,f from b;
复制表数据
　　insert into backupStudent select * from students(backupStudent表必须提前建好)
SQL语句的执行顺序:
1. Select > 选择列 > distinct > top > From表
2. Where条件 > Group by 列 > Having 筛选条件 > Order by 列
高级查询运算符:
联合结果集 union --可以合并多个,默认去除重复(所有列一样),可以使用union all查询所有结果(包括重复)
　　select Id,name,age from teacher
　　union
　　select Id,name age from student
　　要合并的结果集的的列数,对应列的类型要一致(兼容)
　　union因为要去除重复,所以效率低于union all
EXCEPT 运算符
　　EXCEPT 运算符通过包括所有在 TABLE1 中但不在 TABLE2 中的行并消除所有重复行而派生出一个结果表。当 ALL 随 EXCEPT 一起使用时 (EXCEPT ALL)，不消除重复行。
INTERSECT 运算符
　　INTERSECT 运算符通过只包括 TABLE1 和 TABLE2 中都有的行并消除所有重复行而派生出一个结果表。当 ALL 随 INTERSECT 一起使用时 (INTERSECT ALL)，不消除重复行。
类型转换函数
　　case(expression as date_type)
　　convert(data_type, expression)
字符串函数
　　len() --计算字符串的长度(字符的个数) 不包括右边的空格
　　datalength() --计算字符串的字节数,
　　lower() upper 转换为大小写
　　right() left() --用来切割字符串
　　rihgt(‘abcdefg‘,2) --fg 从右边数返回2个字符
　　ltrim() rtrim() --去除左或右侧空格
　　ltrim(rtirm()) --可以去除两边的空格
　　substring(string,start_positon,length) --截取子字符串,索引从1开始
数学函数：
　　ABS() 求绝对值 CEILING() 舍入到最大整数 FLOOR() 舍入到最小整数 ROUND() 四舍五入
日期函数
　　getdate() --取得当前日期时间
　　dateadd(datepart,number,date) --(修改日期的部分,增加数值,要增加的时间)
　　datediff(datepart,startdate,enddate) --计算两段日期的差
　　datepart(datepart,date) --获得一个日期的特定部分
　　year() month() day() --效果同上
CASE 函数：
　　用法1：CASE expression
　　WHEN value1 THEN returnvalue1
　　WHEN value2 THEN returnvalue2
　　....
　　ELSE defaultreturnvalue
　　END
　　用法2：CASE
　　WHEN condition1 THEN returnvalue1
　　WHEN condition2 THEN returnvalue2
　　...
　　ELSE defaultreturnvalue
　　END
使用外连接
A、left （outer） join：
　　左外连接（左连接）：结果集几包括连接表的匹配行，也包括左连接表的所有行。
　　SQL: select a.a, a.b, a.c, b.c, b.d, b.f from a LEFT OUT JOIN b ON a.a = b.c
B：right （outer） join:
　　右外连接(右连接)：结果集既包括连接表的匹配连接行，也包括右连接表的所有行。
C：full/cross （outer） join：
　　全外连接：不仅包括符号连接表的匹配行，还包括两个连接表中的所有记录

SQL 基础,古老的榕树,5-wow.com