sql server数据库建表时添加备注 建表sql语句 并备注

1、 表定义

建表语句

create table 表名 （
属性 类型 注释 [默认值]，
属性 类型 注释 [默认值]，
…
）

drop table if exists student;
create table student(
	id int primary key auto_increment comment 'ID',
	name varchar(20) comment ‘学生姓名’,
	age int comment '学生年龄',
	check(age>0)
)

删除表语句

drop table 表名

drop table student

修改表语句

修改表名

表重命名
rename table oldTableName to newTableName;

修改表字段、字段类型、备注

增加表字段
alter table student add class varchar(20) comment'xxx';

修改表字段类型
alter table student modify class varchar(50) comment'xxx';

删除表字段
alter table student drop class

修改字段名
alter table student change studentName stu_name varchar(20) not null comment'学生姓名';

2、SQL数据查询和操作

SQL查询

SQL查询的基本结构

select A1,A2....
from r1,r2...
where P

A表示表属性
r表示表
P表示条件

select子句

1. 属性名中不能使用“-”，用下划线“_”替代

--例子：查出所有老师职务
select teacher_name,dept_name from teacher

2. SQL中允许关系或者SQL表达式结果出现重复的元祖（重复的记录）
   若是要去重，可以再select后面添加关键字distinct，（distinct只能对所选择的属性的合集进行去重）

select distinct teacher_name from teacher

3. 星号 “”*”在select子句后，用来表示选择全部的属性
4. 属性可以进行运算操作

where子句

1. where子句允许我们选出那些在from子句结果集中满足特定谓词的元祖
2. where子句中可以使用逻辑连词 and，or，not 和between范围查询，逻辑连词的运算对象可以包换比较运算符

from子句

form子句是一个查询求值需要访问的关系列表，通过from子句在一个子句中列出所有关系上的笛卡尔积

1. from子句中存在多个表时，在where子句后需要在属性前添加表前缀以示区分

更名运算

提供给属性和表重新命名机制，使用as子句：old-name as new-name,一般在多张表或者使用聚合函数的属性的时候用

字符串运算

1. like相关的模糊查询，“%”和“_”的区别
2. 查询特殊字符（%和_等），需要在like比较运算中使用escape关键字作为定义转义符，MySQL中使用‘\’时报错，使用其他都没有问题。

--匹配name属性含有%字符的所有元祖（记录）
select * from student where name like "%/%%" escape "/"

3. 字符串上的函数，串联、提取子串、计算字符串长度、大小写转换、去掉空格

排序元祖显示次序

SQL提供元祖排序控制，order by子句，以及升序asc,降序desc

-- 根据学生表按学生升高降序，以及名字升序排序
select * from student order student_height desc,name asc

集合运算

聚集函数

1. avg：平均
2. min：最小
3. max：最大
4. sum：求和
5. count：记录总数
6. group by (分组聚集)：分组

-- 例子：在老师表查询出所有系和平均薪资
select dept_name ,avg(salary) as avg_salary 
from teacher 
group by dept_name

注意：任何没有出现在group by子句中的属性，如果出现在select子句中的话，它只能出现在聚集函数内部，否则这样查询时会报错的。

select dept_name ,avg(salary) as avg_salary 
from teacher 
group by dept_name 
having avg(salary)>40000

注意：和select子句情况类似，任何出现在having子句中的，但是没有被聚集的属性必须出现在group by子句中，否则查询时错误的（having中的要么是聚集函数中的属性或者是group by 子句后的属性）

空值

1. 我们可以再谓词中用特殊关键字null测试空值

select name from teacher where name not is null

2. 聚集函数中只有count函数会将null也统计进去，其他的函数都是忽略null

嵌套子查询

集合成员资格

连接词in测试元祖是否是集合中的成员，相反的有 not in

-- 找出2019年秋季和2020年春季都开设的课程
select course_id 
from section 
where semester = "Fall" and year = 2019 and
course_id in (select course_id 
			  from section 
			  where semester = "Spring" and year = 2020)

集合比较

-- 找出满足下面条件多有老师名字，他们的工资至少比Biology系某一个教师工资高
--case one
select distinct t.name 
from teacher t,teacher t1 
where t.salary >t1.salary and t1.dept_name = 'Biology';

--case two (重点：至少比某一个要大)
select distinct name 
from teacher 
where salary > some (select salary 
					 from teacher 
					 where dept_name = 'biology')

-- 查找出下面条件的所有教师名字，他们的工资比Biology系的每一个老师的工资都要高
select name 
from teacher 
where salary > all (select salary 
					from teacher 
					where dept_name = 'Biology')

空关系测试

--找出在2019年秋季学期和2020春季学期开课的所有课程
-- case one
select course_id 
from section as S 
where semester = 'Fall' and year = 2019 and  
	exists (select * 
			from section as T 
			where semeter = 'Spring ' and year = 2020 and S.course_id = T.course_id)

首先外行的select选择出2019年秋季课程，接着会再exists的内部在检查一遍，如果满足也是2020年春季课程的话，则exists结果 为true，否则为false,结果为true则结果加入外行输出结果集

not exists 刚好和exists相反，如果内部检查不存在的话，满足要求返回true，否则为false

EXISTS与IN的使用效率的问题，通常情况下采用exists要比in效率高，因为IN不走索引，但要看实际情况具体使用：
IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。

重复元祖存在性测试

SQL中提供一个布尔函数，用来测试在一个子查询中是否存在重复元祖。如果子查询中没有存在重复的元祖unique结构返回true值。

-- 找出所有课程在2020年最多开设一次的课程
select T.course_id 
from course as T 
where unique (select R.course_id 
			  from section as R 
			  where T.course_id = R.course_id and R.year = 2020)

unique 相当于 1>= :只有不重复的情况下才能是true

not unique 相当于 1<:至少存在两个

from子句中的子查询

SQL允许在from子句中使用子查询。因为select-from-where返回结果都是关系，因此可以插到另一个select-from-where中任何关系可以出现的位置

--查询所有系中工资总额最大值的系
select max(totle_salary)
from (select dept_name,sum(salary) 
	  from instructor group by dept_name) 
	  as dept_totle(dept_name ,totle_salary)

with子句

with子句提供定义临时关系，这个定义只对包含with子句查询有效

3、SQL增删改操作

数据库修改——删除

-- r是表名 P是删除的条件
delete from r where P

数据库修改——插入

-- []中的可以省略
insert into r[(A1,A2,....)] 
values(e1,e2,....)

数据库修改——更新

update r 
set <A1 = e1, [A2 = e2,....]>
[where P]

SQL提供了case结构，避免更新次序引发的问题

case
when pred1 then result1
when pred1 then result2
...
when predn then resultn
else result0
end

--工资小于5000的涨5%，其他的涨3%
update teacher 
set salary = case
			 when salary<5000 then salary*1.05
			 else salary*1.03
end

3、SQL语句总结

SQL查询语句通用形式

-- A是表属性（字段）
select <[distinct] A1,A2,...>
from <r1,r2,...>
[where<condition>]
[group by <A1,A2,...> [having<cond2>]]
[order by <A1[desc],[A2[asc|desc]]>]

SQL的查询执行顺序

from -> where -> group -> having ->select -> order by

视图和索引

视图

视图是从一个或几个基本表(或视图)导出的表。它与基本表不同，是一个虚表。数据库中只存放视百图的定义，而不存放视图对应的数据，这些数据仍存放在度原来的基本表中。所以基本表中的数据发生变化，从视图中查询出的数据也就随之改变了。从这个内意义上讲，视图就像一个窗口，透过它可以看到数据库中自己感兴趣的数据及其变化。视图一经定义，就可以和基本表一样被查询、被删除。也可以在一个视图之上再定义新的视图，但对视图的更新（增、删、改）操容作则有一定得限制。

索引

为关系中的某些属性创建索引，它允许数据库系统高效的找到关系中那些在索引上给定的取值元祖，而不需要扫描所有的元祖

create index <i-name> on <table-name> (<attribute-list>)

--例子：普通索引
create index stu_index on student (ID) 

create index stu_ID_name_index on student (ID , name)

-- 唯一索引
create unique index stu_index on student (ID)

-- 删除索引
drop index <i-name>

4 、事务和完整性约束

事务

事务（transaction）由查询和更新语句的序列组成，SQL标准规定当一条SQL语言被执行，就隐式开启一个事务，下列SQL语句之一是结束一个事务

• Commit work：提交当前事务，也就是将数据持久化更新到数据库保存，在事务被提交之后，一个新的事务自动开启
• Rollback work：回滚当前事务，及撤销该事务所有SQL语句对数据库的更新，这样数据库会回到执行该事务第一条语句之前状态

事务的四个特性：

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

完整性约束

完整性约束保证授权用户对数据库所做的改变不会导致数据一致性的破坏

按约束对象区分

1. 域完整性
   在单列上的约束
2. 关系完整性
   再多列上或者表上的约束或者是表之间的约束

按约束来源区分

1. 结构完整性约束

1. 实体完整性（主键约束）：主键不能为空
2. 参照完整性（外键约束）：外键只能为空或者参考表中属性的集合

涉及的表存在参考完整性约束，在增删改的时候，都需要考虑约束情况

--建表是创建外键
--被参照关系也可以明确指出，但是必须是生命的主码或者候选码（正确）
[CONSTRAINT constraint_name]foreign key （dept_name） references department （dept_name)

SQL中的联机动作

create table course（
	foreign key (dept_name) reference department
		[on delete cascade]
		[on update cascade]
）

on delete cascade ,如果删除department中的元祖导致参照完整性约束违反，删除不会被系统拒绝，而是对course关系做级联删除。即删除被删除系的元祖。级联更新的话也是类似。
如果存在多个级联操作的话，那么参照完整性实在事务结束时检查。
其他级联操作选择

on delete set null 

on delete set default

1. 用户自定义完整性
   比如：老师的年龄在20—50之间等

1. 内容约束
   比如用户自定义约束

按状态区分——静态完整性约束

列约束

--MySQL-列约束（属性后面追加约束）
create table <table-name> (
	colname int [primary key] [auto_increment] [comment '注释'] [default <默认值>],
	colname varchar(30) [unique] [comment '注释'] [default <默认值>],
	colname int [comment '注释'] [default <默认值>],
	colname int [constraint <constraint-name>]check(colname>20) not null [comment '注释'] [default <默认值>],

	[constraint <constraint-name> foreign key(colname) references <table-name> (colname)],
)

--MySQL建表
create table dept(
	id int primary key auto_increment comment 'ID' ,
 	Dname varchar(30) not null comment '系名称'
 );

--case one 列约束
create table teacher(
 	id int primary key auto_increment comment ‘ID’,
	name varchar(30) not null  UNIQUE comment ‘老师姓名’,
	age int CONSTRAINT tea_age CHECK(age>20) comment ‘老师年龄’,
	dept_id int comment ‘老师所在系ID’,
	[constraint dept_id] foreign key(dept_id) references dept(id)
)

表约束

create table <table-name>(
	colname int auto_incremrnt [comment '注释'] [default <默认值>],
	colname varchar(30)  [comment '注释'] [default <默认值>],
	colname int [comment '注释'] [default <默认值>],
	colname int  not null [comment '注释'] [default <默认值>],
	
	[primary key(cloname)]
	[constraint <constraint-name> unique(colname)],
	[constraint <constraint-name> foreign key(colname) references <table-name> (colname)],
	[constraint <constraint-name> check(cloname>20)]
)
--case two 表约束
create table teacher(
	id int auto_increment,
	name varchar(30) not null,
	age int,
	dept_id int,
	primary key(id)
	constraint Tname unique(name),
	constraint Tage check(age>20),
	constraint dept_id foreign key(dept_id) references dept(id)
)

W3school约束创建、删除、修改，以及不同数据库的约束的区别

MySQL中删除约束

1、删除外键约束

alter table <table-name> drop foreign key <key-name>

--例子
alter table teacher3 drop foreign key dept_id

2、删除主键约束

如果主键时自增的话，直接删除的话会报错

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key              
#这说明此列是自动增长列，无法直接删除

列的属性还带有AUTO_INCREMENT，那么要先将这个列的自动增长属性去掉，才可以删除主键。

--先去掉自增
alter table <table-name> modify id int
--在删除主键约束
alter table <table-name> drop primary key

主键添加自增

alter table <table-name> modify colname dataType auto_increment

属性（ID）设置成主键且自增

alter table <table-name> modify colname dataType auto_increment primary key

3、删除唯一约束

alter table <table-name> drop index <constraint-name>

动态完整性约束

MySQL参考手册——触发器语法和示例