#先把表的结构定义出来，再向表中插入记录
CREATE Table Student
(Sno Char(3) not null primary key,#学号
Sname Char(8) not null,#学生姓名
Ssex Char(2) not null,#学生性别
Sbirthday datetime,#学生出生年月
Class Char(5),#学生所在班级
);
Create table Course
(
Cno Char(5) not null primary key, #课程号
Cname Varchar(10) not null, #课程名称
Tno Char(3) not null #教工编号
);
Create table Score
(
Sno Char(3) not null references Student(Sno) , #学号
Cno Char(5) not null references Course(Cno), #课程号
Degree Decimal(4,1) #成绩
);
create table Teacher
(
Tno Char(3) not null primary key, #教工编号
Tname Char(4) not null, #教工姓名
Tsex Char(2) not null, #教工性别
Tbirthday datetime, # 教工出生年月
Prof Char(6), #职称
Depart Varchar(10) not null #教工所在部门
);
#对于表中的记录要一条一条的插入
#记住语句：记录是插入到表中的insert into
insert into Student values('108','曾华','男','1977-09-01','95033');
insert into Student values('105','匡明','男','1975-10-02','95031');
insert into Student values('107','王丽','女','1976-01-23','95033');
insert into Student values('101','李军','男','1976-02-20','95033');
insert into Student values('109','王芳','女','1975-02-10','95031');
insert into Student values('103','陆君','男','1974-06-03','95031');
insert into Course values('3-105','计算机导论','825');
insert into Course values('3-245','操作系统','804');
insert into Course values('6-166','数字电路','856');
insert into Course values('9-888','高等数学','831');
insert into Score values('103','3-245','86');
insert into Score values('105','3-245','75');
insert into Score values('109','3-245','68');
insert into Score values('103','3-105','92');
insert into Score values('105','3-105','88');
insert into Score values('109','3-105','76');
insert into Score values('101','3-105','64');
insert into Score values('107','3-105','91');
insert into Score values('108','3-105','78');
insert into Score values('101','6-166','85');
insert into Score values('107','6-166','79');
insert into Score values('108','6-166','81');
insert into Teacher values('804','李诚','男','1958-12-02','副教授','计算机系');
insert into Teacher values('856','张旭','男','1969-03-12','讲师','电子工程系');
insert into Teacher values('825','王萍','女','1972-05-05','助教','计算机系');
insert into Teacher values('831','刘冰','女','1977-08-14','助教','电子工程系');
#1、查询Student表中的所有记录的Sname、Ssex和Class列。
select Sname,Ssex,Class from Student
#2、 查询教师所有的单位即不重复的Depart列。
select distinct Depart from Teacher
#3、 查询Student表的所有记录。
select * from Student
#4、 查询Score表中成绩在60到80之间的所有记录。
#SQL中没有if条件关键字，与之对应的关键字是where
select * from Score where Degree between 60 and 80
#5、 查询Score表中成绩为85，86或88的记录。
#SQL中的逻辑运算符：and，or
select * from Score where Degree=85 or Degree=86 or Degree=88
#SQL中也有像python中一样含义的关键字：in
select * from Score where Degree in (85,86,88)
#6、 查询Student表中“95031”班或性别为“女”的同学记录。
select * from Student where class='95031' or Ssex='女'
#7、 以Class降序查询Student表的所有记录。
#order by说的是选择出来的记录的展示方式
select * from student order by class desc
#8、 以Cno升序、Degree降序查询Score表的所有记录。
#order by默认是升序，如需降序则需要使用关键字desc
select * from Score order by Cno, Degree desc
#9、 查询“95031”班的学生人数。
#count计数值，计算记录的条数
select count(Sno) from Student where class='95031'
select count(*) from Student where class='95031'
#10、?查询Score表中的最高分的学生学号和课程号。（子查询或者排序）
select Sno,Cno from Score where Degree=(select max(Degree) from Score)
#11、 查询每门课的平均成绩。
select avg(Degree) from Score where Cno='3-245';
select avg(Degree) from Score where Cno='3-105';
select avg(Degree) from Score where Cno='6-166';
#这里的group by和order by 类似，都是展示结果的方式
select avg(Degree) from Score group by Cno;
#12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。
select avg(Degree) from Score group by Cno having count(Cno)>=5 and Cno like '3%'
select avg(Degree) from Score group by Cno having count(*)>=5 and Cno like '3%'
#Cno课程号
select avg(Degree) from Score where Cno like '3%' and Cno in (select Cno from Score group by Cno having count(*)>=5)
#13、查询分数大于70，小于90的Sno列。
select Sno from Score where Degree between 70 and 90
#14、查询所有学生的Sname、Cno和Degree列。
select Student.Sname,Score.Cno,Score.Degree from Student,Score where Student.Sno=Score.Sno
#默认内连接，关键字on指定连接的键值
select sname,cno,degree from student join score on Student.Sno=Score.Sno
#15、查询所有学生的Sno、Cname和Degree列。
select Score.Sno,Course.Cname,Score.Degree from Score,Course where Course.Cno=Score.Cno
select Sno,Cname,Degree from Score join Course on Course.Cno=Score.Cno
#16、查询所有学生的Sname、Cname和Degree列。
select Student.Sname,Course.Cname,Score.Degree from Student,Course,Score where Student.Sno=Score.Sno and Course.Cno=Score.Cno
select Sname,Cname,Degree from Course join Score on Course.Cno=Score.Cno join Student on Student.Sno=Score.Sno
#17、 查询“95033”班学生的平均分。
select avg(Degree) from Score,Student where Student.Sno=Score.Sno and class='95033'
select avg(Degree) from Score where Sno in (select Sno from Student where class='95033')
#18、 假设使用如下命令建立了一个grade(评分等级)表：
create table grade
(
low int(3),upp int(3),rank char(1)
);
insert into grade values(90,100,'A');
insert into grade values(80,89,'B');
insert into grade values(70,79,'C');
insert into grade values(60,69,'D');
insert into grade values(0,59,'E');
#现查询所有同学的Sno、Cno和rank列
select Score.Sno,Score.Cno,grade.rank from score,grade where degree between low and upp #order by rank;
select Sno,Cno,rank from score join grade on degree between low and upp
#19、 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
select * from Student,Score where Student.Sno=Score.Sno and Cno='3-105' and Degree>76
select distinct a.Cno,a.Sno,a.Degree from Score a,Score b where a.Cno='3-105' and a.Degree>b.Degree and b.Sno='109' and b.Cno='3-105'
select * from Score a where a.Cno='3-105' and a.degree>(select degree b from Score where Sno='109' and Cno='3-105')
select * from Score where Cno='3-105' and degree>(select degree from Score where Sno='109' and Cno='3-105')
#20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
select Sno from Score group by Sno having count(*)>1;#having似乎是针对group by操作的结果的
#和python一样，SQL也有not in 关键字，含义也一致
select * from Score where Degree not in (select max(Degree) from Score group by cno) #非课程最高分
#学号
select * from Score a where Sno in(select Sno from Score group by Sno having count(*)>1) and
#在满足第一个条件的表里继续找满足条件的
a.Degree not in (select max(Degree) from Score b where b.cno=a.cno) #非课程最高分
#两个条件单独做，再综合
select * from Score where Sno in(select Sno from Score group by Sno having count(*)>1) and
Degree not in (select max(Degree) from Score group by sno)#非学号最高分
select * from Score where
Degree not in (select max(Degree) from Score group by sno having count(*)>1) and
Sno in(select Sno from Score group by Sno having count(*)>1)#非学号最高分
#21、 查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
select * from Score where degree>(select degree from Score where Sno='109' and Cno='3-105')
#22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。
select Sno,Sname,Sbirthday from Student where Sbirthday=(select Sbirthday from Student where Sno='108') #年月日全部匹配
select Sno,Sname,Sbirthday from Student where Year(Sbirthday)=(select Year(Sbirthday) from Student where Sno='108') #只匹配年
#23、查询“张旭“教师任课的学生成绩。
select Degree from Score,Course,Teacher where Teacher.Tname='张旭'and Teacher.Tno=Course.Tno and Course.Cno=Score.Cno
select Degree from Score where Cno=(select Cno from Course where Tno=(select Tno from Teacher where Tname='张旭'))#多级where方式
#24、查询选修某课程的同学人数多于5人的教师姓名。
select Tname from Teacher where Tno in (select Tno from Course,Score group by Score.Cno having count(Score.Cno)>5)
select Tname from Teacher where tno in(select tno from course where cno in (select cno from score group by cno having count(cno)>5))
#25、查询95033班和95031班全体学生的记录。
select * from Student where class in('95033','95031')
#26、查询存在有85分以上成绩的课程Cno.
select Cno from Score where Degree in (select Degree from Score group by Cno having Degree>85)
select distinct cno from score where degree>85
#27、查询出“计算机系“教师所教课程的成绩表。
select distinct * from Score where Cno in (select Cno from Course where Tno in(select Tno from Teacher where Depart='计算机系'))
select distinct Score.Sno,Score.Cno,Score.Degree from Score,Course,Teacher where Score.Cno=Course.Cno and Course.Tno=Teacher.Tno and Teacher.Depart='计算机系'
#28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
select Tname,Prof from Teacher where Depart='计算机系' and prof not in (select Prof from Teacher where Depart='电子工程系')
#只查出了计算机系中的记录
#这里的and操作为集合的交集
select Tname,Prof from Teacher where Prof not in (select Prof from Teacher where Depart='计算机系' and prof in (select Prof from Teacher where Depart='电子工程系'))
#查出了计算机系和电子工程系的记录
#29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno和Degree,并按Degree从高到低次序排序。
select Cno,Sno,Degree from Score where Cno='3-105' and Degree>any (select Degree from Score where Cno='3-245')order by Degree desc;
# any 任何一个值
#30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
select Cno,Sno,Degree from Score where Cno='3-105' and Degree>(select max(Degree) from Score where Cno='3-245') order by Degree desc
select Cno,Sno,Degree from Score where Cno='3-105' and Degree>all(select Degree from Score where Cno='3-245') #all 所有值
#31、 查询所有教师和同学的name、sex和birthday.
select Tname as name,Tsex as sex,Tbirthday as birthday from Teacher union select Sname,Ssex,Sbirthday from Student
#32、查询所有“女”教师和“女”同学的name、sex和birthday.
#关键字union将查询结果并在一起
select Tname,Tsex,Tbirthday from Teacher where Tsex='女' union select Sname,Ssex,Sbirthday from Student where Ssex='女'
#33、 查询成绩比该课程平均成绩低的同学的成绩表。
select * from Score a where a.Degree<(select avg(Degree) from Score b where b.Cno=a.Cno)
#34、 查询所有任课教师的Tname和Depart.
select Tname,Depart from Teacher
select Tname,Depart from Teacher where Tno in(select Tno from Course where Teacher.Tno=Course.Tno) #理解为有课就有任课
select Tname,Depart from Teacher where exists(select Tno from Course where Teacher.Tno=Course.Tno) #通常用exists里面select后面跟*
select Tname,Depart from Teacher where Tno in(select tno from course where cno in (select cno from score where cno !=0))#理解为有课并有学生选课才是有任课
select Tname,Depart from Teacher where Tno in(select tno from course where cno in (select cno from score where course.cno=score.cno))#理解为有课并有学生选课才是有任课
#35 、 查询所有未讲课的教师的Tname和Depart.
select Tname,Depart from Teacher where Tno not in(select Tno from Course)
select Tname,Depart from Teacher where not exists(select Tno from Course where Teacher.Tno=Course.Tno) #通常用exists里面select后面跟*
select Tname,Depart from Teacher where Tno not in(select tno from course where cno in (select cno from score where cno !=0))
select Tname,Depart from Teacher where Tno not in(select tno from course where cno in (select cno from score))
#36、查询至少有2名男生的班号。
select class from Student where Ssex='男' group by class having count(Student.Ssex)>=2
select class from Student where Ssex='男' group by class having count(*)>=2
#37、查询Student表中不姓“王”的同学记录。
select * from Student where Sname not like '王%'
#38、查询Student表中每个学生的姓名和年龄。
select Sname as 姓名,year(now())-year(Sbirthday) as 年龄 from Student
#now sysdate 取现在的时间，年月日点
#39、查询Student表中最大和最小的Sbirthday日期值。
select max(Sbirthday),min(Sbirthday) from Student
#40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
select * from Student order by class desc,Sbirthday
#41、查询“男”教师及其所上的课程。
select Teacher.Tname,Teacher.Tsex,Course.Cname from Teacher,Course where Teacher.Tsex='男' and Teacher.Tno=Course.Tno
select Cname from Course where Tno in (select Tno from Teacher where Tsex='男')
#42、查询最高分同学的Sno、Cno和Degree列。
select * from Score where Degree=(select max(Degree) from Score)
#43、查询和“李军”同性别的所有同学的Sname.
select Sname from Student where Ssex=(select Ssex from Student where Sname='李军')
#44、查询和“李军”同性别并同班的同学Sname.
select Sname from Student where Ssex=(select Ssex from Student where Sname='李军') and class=(select class from Student where Sname='李军')
#45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
select Cno from Course where Cname='计算机导论'#fen
select Sno from Student where Ssex='男' #fen
select * from Score
where Cno in (select Cno from Course where Cname='计算机导论') and Sno in (select Sno from Student where Ssex='男')
#满足一个条件可以用‘=’，满足多个条件用‘in’，所以最好都用‘in’
#在指定的列中插入数据
INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')
#更新某一行中的一个列
UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'
#更新某一行中的若干列
UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'
#DELETE 语句用于删除表中的行。
DELETE FROM Person WHERE LastName = 'Wilson'
#可以在不删除表的情况下删除所有的行。这意味着表的结构、属性和索引都是完整的：
DELETE FROM table_name
#或者
DELETE * FROM table_name
SELECT * FROM Persons LIMIT 5
#LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。
SELECT * FROM Persons WHERE City NOT LIKE '%lon%'
#SQL 通配符必须与 LIKE 运算符一起使用。
'''
% 除了一个或多个字符外，%还能匹配0个字符。%代表搜索模式中给定位置的0个、1个或者多个字符。
_ 仅替代一个字符
[charlist] 字符列中的任何单一字符
[^charlist]
或者 不在字符列中的任何单一字符
[!charlist]
==注意==：通配符不能匹配NULL
'''
#IN 操作符允许我们在 WHERE 子句中规定多个值。
SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')
#通过使用 SQL，可以为列名称和表名称指定别名（Alias）。
#使用表名称别名
SELECT po.OrderID, p.LastName, p.FirstName
FROM Persons AS p, Product_Orders AS po
WHERE p.LastName='Adams' AND p.FirstName='John'
#使用一个列名别名
SELECT LastName AS Family, FirstName AS Name
FROM Persons
#有时为了得到完整的结果，我们需要从两个或更多的表中获取结果。我们就需要执行 join。
'''
数据库中的表可通过键将彼此联系起来。主键（Primary Key）是一个列，
在这个列中的每一行的值都是唯一的。在表中，每个主键的值都是唯一的。
这样做的目的是在不重复每个表中的所有数据的情况下，把表间的数据交叉捆绑在一起。
'''
#我们可以通过引用两个表的方式，从两个表中获取数据：
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P
#除了上面的方法，我们也可以使用关键词 JOIN 来从两个表中获取数据。
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName
'''
JOIN: 如果表中有至少一个匹配，则返回行(INNER JOIN 与 JOIN 是相同的。)
LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
FULL JOIN: 只要其中一个表中存在匹配，就返回行
'''
#LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行，即使在右表 (table_name2) 中没有匹配的行。
#RIGHT JOIN 关键字会右表 (table_name2) 那里返回所有的行，即使在左表 (table_name1) 中没有匹配的行。
#FULL JOIN 关键字会从左表 (Persons) 和右表 (Orders) 那里返回所有的行。
#如果 "Persons" 中的行在表 "Orders" 中没有匹配，
#或者如果 "Orders" 中的行在表 "Persons" 中没有匹配，这些行同样会列出。
'''
UNION 操作符用于合并两个或多个 SELECT 语句的结果集。
请注意，UNION 内部的 SELECT 语句必须拥有相同数量的列。
列也必须拥有相似的数据类型。同时，
每条 SELECT 语句中的列的顺序必须相同。
另外，UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。
'''
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
SELECT E_Name FROM Employees_China
UNION ALL
SELECT E_Name FROM Employees_USA
'''
SQL SELECT INTO 语句可用于创建表的备份复件。
SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。
SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。
'''
#制作备份复件
SELECT * INTO Persons_backup FROM Persons
#IN 子句可用于向另一个数据库中拷贝表：
SELECT * INTO Persons IN 'Backup.mdb' FROM Persons
#CREATE DATABASE 用于创建数据库。
CREATE DATABASE my_db
#可以通过 CREATE TABLE 来添加数据库表。
#CREATE TABLE 语句用于创建数据库中的表。
#可使用 INSERT INTO 语句向空表写入数据。
CREATE TABLE Persons
(
Id_P int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
'''
SQL 约束
约束用于限制加入表的数据的类型。
可以在创建表时规定约束（通过 CREATE TABLE 语句），
或者在表创建之后也可以（通过 ALTER TABLE 语句）。
我们将主要探讨以下几种约束：
NOT NULL
UNIQUE
PRIMARY KEY
FOREIGN KEY
CHECK
DEFAULT
'''
'''
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。
'''
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
'''
SQL UNIQUE 约束
UNIQUE 约束唯一标识数据库表中的每条记录。
UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。
PRIMARY KEY 拥有自动定义的 UNIQUE 约束(也就是说如果列是主键约束，那么它自动拥有UNIQUE约束)。
请注意，每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。
'''
#在 "Persons" 表创建时在 "Id_P" 列创建 UNIQUE 约束：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (Id_P)
)
#如果需要命名 UNIQUE 约束，以及为多个列定义 UNIQUE 约束，请使用下面的 SQL 语法：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
)
#当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束，请使用下列 SQL：
ALTER TABLE Persons ADD UNIQUE (Id_P)
#如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：
ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)
#如需撤销 UNIQUE 约束，请使用下面的 SQL：
ALTER TABLE Persons DROP INDEX uc_PersonID
'''
SQL PRIMARY KEY 约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。
'''
#SQL PRIMARY KEY Constraint on CREATE TABLE
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (Id_P)#估计MySQL认为PRIMARY KEY是关键字，加这样的约束时需要单独列出，NOT NULL不构成独立的名词关键字
)
#如果需要命名 PRIMARY KEY 约束，以及为多个列定义 PRIMARY KEY 约束，请使用下面的 SQL 语法：
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
)
#SQL PRIMARY KEY Constraint on ALTER TABLE
ALTER TABLE Persons ADD PRIMARY KEY (Id_P)
ALTER TABLE Persons ADD CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)
ALTER TABLE Persons DROP PRIMARY KEY
'''
SQL FOREIGN KEY 约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的动作。
FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。
'''
#SQL FOREIGN KEY Constraint on CREATE TABLE
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)
)
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
PRIMARY KEY (Id_O),
CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
)
#SQL FOREIGN KEY Constraint on ALTER TABLE
ALTER TABLE Orders
ADD FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (Id_P)
REFERENCES Persons(Id_P)
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
'''
SQL CHECK 约束
CHECK 约束用于限制列中的值的范围。
如果对单个列定义 CHECK 约束，那么该列只允许特定的值。
如果对一个表定义 CHECK 约束，那么此约束会在特定的列中对值进行限制。
'''
#SQL CHECK Constraint on CREATE TABLE
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)
)
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
)
#SQL CHECK Constraint on ALTER TABLE
ALTER TABLE Persons
ADD CHECK (Id_P>0)
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (Id_P>0 AND City='Sandnes')
ALTER TABLE Persons
DROP CHECK chk_Person
'''
SQL DEFAULT 约束
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值，那么会将默认值添加到所有的新记录。
'''
#SQL DEFAULT Constraint on CREATE TABLE
CREATE TABLE Persons
(
Id_P int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
#通过使用类似 GETDATE() 这样的函数，DEFAULT 约束也可以用于插入系统值：
CREATE TABLE Orders
(
Id_O int NOT NULL,
OrderNo int NOT NULL,
Id_P int,
OrderDate date DEFAULT GETDATE()
)
#SQL DEFAULT Constraint on ALTER TABLE
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
ALTER TABLE Persons
ALTER City DROP DEFAULT
'''
CREATE INDEX 语句用于在表中创建索引。
在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。
索引
您可以在表中创建索引，以便更加快速高效地查询数据。
用户无法看到索引，它们只能被用来加速搜索/查询。
注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，
这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。
'''
#在表上创建一个简单的索引。允许使用重复的值：
CREATE INDEX PersonIndex ON Person (LastName)
#如果您希望以降序索引某个列中的值，您可以在列名称之后添加保留字 DESC：
CREATE INDEX PersonIndex ON Person (LastName DESC)
#假如您希望索引不止一个列，您可以在括号中列出这些列的名称，用逗号隔开：
CREATE INDEX PersonIndex ON Person (LastName, FirstName)
#在表上创建一个唯一的索引。唯一的索引意味着两个行不能拥有相同的索引值。
CREATE UNIQUE INDEX index_name ON table_name (column_name)
#通过使用 DROP 语句，可以轻松地删除索引、表和数据库。
#我们可以使用 DROP INDEX 命令删除表格中的索引。
ALTER TABLE table_name DROP INDEX index_name
#DROP TABLE 语句用于删除表（表的结构、属性以及索引也会被删除）：
DROP TABLE 表名称
#DROP DATABASE 语句用于删除数据库：
DROP DATABASE 数据库名称
#如果我们仅仅需要除去表内的数据，但并不删除表本身，
#请使用 TRUNCATE TABLE 命令（仅仅删除表格中的数据）：
TRUNCATE TABLE 表名称
#ALTER TABLE 语句用于在已有的表中添加、修改或删除列。
#在表 "Persons" 中添加一个名为 "Birthday" 的新列
ALTER TABLE Persons ADD Birthday date
#改变数据类型实例
ALTER TABLE Persons ALTER COLUMN Birthday year
#DROP COLUMN 实例
ALTER TABLE Person DROP COLUMN Birthday
'''
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)
)
#默认地，AUTO_INCREMENT 的开始值是 1，每条新记录递增 1。
#要让 AUTO_INCREMENT 序列以其他的值起始，请使用下列 SQL 语法：
ALTER TABLE Persons AUTO_INCREMENT=100
#视图是可视化的表。
#如何创建、更新和删除视图。
'''
什么是视图？
在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。
视图包含行和列，就像一个真实的表。
视图中的字段就是来自一个或多个数据库中的真实的表中的字段。
我们可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，
我们也可以提交数据，就像这些来自于某个单一的表。
注释：数据库的设计和结构不会受到视图中的函数、where 或 join 语句的影响。
'''
#注释：视图总是显示最近的数据。每当用户查询视图时，数据库引擎通过使用 SQL 语句来重建数据。
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
#我们可以查询上面这个视图：
SELECT * FROM [Current Product List]
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
SELECT * FROM [Products Above Average Price]
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
SELECT * FROM [Category Sales For 1997]
#我们也可以向查询添加条件。现在，我们仅仅需要查看 "Beverages" 类的全部销量：
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
#SQL 更新视图
#SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
#SQL 撤销视图
DROP VIEW view_name
'''
SQL 日期
当我们处理日期时，最难的任务恐怕是确保所插入的日期的格式，
与数据库中日期列的格式相匹配。
只要数据包含的只是日期部分，运行查询就不会出问题。
但是，如果涉及时间，情况就有点复杂了。
在讨论日期查询的复杂性之前，我们先来看看最重要的内建日期处理函数。
'''
# MySQL 中最重要的内建日期函数：
'''
函数 描述
NOW() 返回当前的日期和时间
CURDATE() 返回当前的日期
CURTIME() 返回当前的时间
DATE() 提取日期或日期/时间表达式的日期部分
EXTRACT() 返回日期/时间的单独部分,比如年、月、日、小时、分钟等等。
语法:
EXTRACT(unit FROM date)
SELECT EXTRACT(YEAR FROM OrderDate) AS OrderYear,
EXTRACT(MONTH FROM OrderDate) AS OrderMonth,
EXTRACT(DAY FROM OrderDate) AS OrderDay
FROM Orders
WHERE OrderId=1
DATE_ADD() 给日期添加指定的时间间隔
语法:
DATE_ADD(date,INTERVAL expr type)
SELECT OrderId,DATE_ADD(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
DATE_SUB() 从日期减去指定的时间间隔
语法:
DATE_SUB(date,INTERVAL expr type)
SELECT OrderId,DATE_SUB(OrderDate,INTERVAL 2 DAY) AS OrderPayDate
FROM Orders
DATEDIFF() 返回两个日期之间的天数
语法:
DATEDIFF(date1,date2)
注释：只有值的日期部分参与计算。
SELECT DATEDIFF('2008-12-30','2008-12-29') AS DiffDate
DATE_FORMAT() 用不同的格式显示日期/时间
语法:
DATE_FORMAT(date,format)
'''
#SQL Date 数据类型
'''
MySQL 使用下列数据类型在数据库中存储日期或日期/时间值：
DATE - 格式 YYYY-MM-DD
DATETIME - 格式: YYYY-MM-DD HH:MM:SS
TIMESTAMP - 格式: YYYY-MM-DD HH:MM:SS
YEAR - 格式 YYYY 或 YY
'''
'''
NULL 值的处理方式与其他值不同。
NULL 用作未知的或不适用的值的占位符。
注释：无法比较 NULL 和 0；它们是不等价的。
无法使用比较运算符来测试 NULL 值，比如 =, <, 或者 <>。
我们必须使用 IS NULL 和 IS NOT NULL 操作符。
'''
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL
#提示：请始终使用 IS NULL 来查找 NULL 值。
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
#MySQL 也拥有类似 ISNULL() 的函数。
#不过它的工作方式与微软的 ISNULL() 函数有点不同。
#在 MySQL 中，我们可以使用 IFNULL() 函数，就像这样：
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
'''
MySQL 数据类型
在 MySQL 中，有三种主要的类型：文本、数字和日期/时间类型。
Text 类型：
数据类型 描述
CHAR(size) 保存固定长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size)
保存可变长度的字符串（可包含字母、数字以及特殊字符）。在括号中指定字符串的最大长度。最多 255 个字符。
注释：如果值的长度大于 255，则被转换为 TEXT 类型。
TINYTEXT 存放最大长度为 255 个字符的字符串。
TEXT 存放最大长度为 65,535 个字符的字符串。
BLOB 用于 BLOBs (Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.)
允许你输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值，则插入空值。
注释：这些值是按照你输入的顺序存储的。
可以按照此格式输入可能的值：ENUM('X','Y','Z')
SET 与 ENUM 类似，SET 最多只能包含 64 个列表项，不过 SET 可存储一个以上的值。
Number 类型：
数据类型 描述
TINYINT(size) -128 到 127 常规。0 到 255 无符号*。在括号中规定最大位数。
SMALLINT(size) -32768 到 32767 常规。0 到 65535 无符号*。在括号中规定最大位数。
MEDIUMINT(size) -8388608 到 8388607 普通。0 to 16777215 无符号*。在括号中规定最大位数。
INT(size) -2147483648 到 2147483647 常规。0 到 4294967295 无符号*。在括号中规定最大位数。
BIGINT(size) -9223372036854775808 到 9223372036854775807 常规。0 到 18446744073709551615 无符号*。在括号中规定最大位数。
FLOAT(size,d) 带有浮动小数点的小数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在括号中规定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型，允许固定的小数点。
* 这些整数类型拥有额外的选项 UNSIGNED。通常，整数可以是负数或正数。如果添加 UNSIGNED 属性，那么范围将从 0 开始，而不是某个负数。
Date 类型：
数据类型 描述
DATE()
日期。格式：YYYY-MM-DD
注释：支持的范围是从 '1000-01-01' 到 '9999-12-31'
DATETIME()
*日期和时间的组合。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 '1000-01-01 00:00:00' 到 '9999-12-31 23:59:59'
TIMESTAMP()
*时间戳。TIMESTAMP 值使用 Unix 纪元('1970-01-01 00:00:00' UTC) 至今的描述来存储。格式：YYYY-MM-DD HH:MM:SS
注释：支持的范围是从 '1970-01-01 00:00:01' UTC 到 '2038-01-09 03:14:07' UTC
TIME() 时间。格式：HH:MM:SS 注释：支持的范围是从 '-838:59:59' 到 '838:59:59'
YEAR()
2 位或 4 位格式的年。
注释：4 位格式所允许的值：1901 到 2155。2 位格式所允许的值：70 到 69，表示从 1970 到 2069。
* 即便 DATETIME 和 TIMESTAMP 返回相同的格式，它们的工作方式很不同。在 INSERT 或 UPDATE 查询中，TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式，比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。
'''
'''
现代的 SQL 服务器构建在 RDBMS 之上。
DBMS - 数据库管理系统（Database Management System）
数据库管理系统是一种可以访问数据库中数据的计算机程序。
DBMS 使我们有能力在数据库中提取、修改或者存贮信息。
不同的 DBMS 提供不同的函数供查询、提交以及修改数据。
关系数据库管理系统 (RDBMS) 也是一种数据库管理系统，
其数据库是根据数据间的关系来组织和访问数据的。
RDBMS 是 SQL 的基础，也是所有现代数据库系统诸如
Oracle、SQL Server、IBM DB2、Sybase、MySQL 以及 Microsoft Access 的基础。
'''
#内建 SQL 函数的语法是：
SELECT function(列) FROM 表
'''
在 SQL 中，基本的函数类型和种类有若干种。函数的基本类型是：
Aggregate 函数
Scalar 函数
Aggregate 函数的操作面向一系列的值，并返回一个单一的值。
注释：如果在 SELECT 语句的项目列表中的众多其它表达式中使用 SELECT 语句，
则这个 SELECT 必须使用 GROUP BY 语句！
Scalar 函数的操作面向某个单一的值，并返回基于输入值的一个单一的值。
'''
#SQL AVG 函数
SELECT AVG(OrderPrice) AS OrderAverage FROM Orders
SELECT Customer FROM Orders
WHERE OrderPrice>(SELECT AVG(OrderPrice) FROM Orders)
#SQL COUNT() 函数
#COUNT() 函数返回匹配指定条件的行数。
#COUNT(*) 函数返回表中的记录数：
SELECT COUNT(*) FROM table_name
#COUNT(DISTINCT column_name) 函数返回指定列的不同值的数目：
SELECT COUNT(DISTINCT column_name) FROM table_name
#注释：COUNT(DISTINCT) 适用于 ORACLE 和 Microsoft SQL Server，但是无法用于 Microsoft Access。
SELECT COUNT(Customer) AS CustomerNilsen FROM Orders
WHERE Customer='Carter'
SELECT COUNT(DISTINCT Customer) AS NumberOfCustomers FROM Orders
#SQL FIRST() 函数
'''
FIRST() 函数返回指定的字段中第一个记录的值。
提示：可使用 ORDER BY 语句对记录进行排序。
'''
SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders
#SQL LAST() 函数
'''
LAST() 函数返回指定的字段中最后一个记录的值。
提示：可使用 ORDER BY 语句对记录进行排序
'''
SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders
#SQL MAX() 函数
#MAX 函数返回一列中的最大值。NULL 值不包括在计算中。
#注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。
SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders
#SQL MIN() 函数
#MIN 函数返回一列中的最小值。NULL 值不包括在计算中。
#注释：MIN 和 MAX 也可用于文本列，以获得按字母顺序排列的最高或最低值。
SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders
#SQL SUM() 函数
SELECT SUM(OrderPrice) AS OrderTotal FROM Orders
#SQL GROUP BY 语句
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate
#在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。
SELECT Customer,SUM(OrderPrice) FROM Orders
GROUP BY Customer
HAVING SUM(OrderPrice)<2000
SELECT Customer,SUM(OrderPrice) FROM Orders
WHERE Customer='Bush' OR Customer='Adams'
GROUP BY Customer
HAVING SUM(OrderPrice)>1500
#SQL UCASE() 函数
#UCASE 函数把字段的值转换为大写。
SELECT UCASE(LastName) as LastName,FirstName FROM Persons
#SQL LCASE() 函数
#LCASE 函数把字段的值转换为小写。
SELECT LCASE(LastName) as LastName,FirstName FROM Persons
#SQL MID() 函数
#MID 函数用于从文本字段中提取字符。
#SQL MID() 语法
#SELECT MID(column_name,start[,length]) FROM table_name
'''
参数 描述
column_name 必需。要提取字符的字段。
start 必需。规定开始位置（起始值是 1）。
length 可选。要返回的字符数。如果省略，则 MID() 函数返回剩余文本。
'''
SELECT MID(City,1,3) as SmallCity FROM Persons
#SQL LEN() 函数
#LEN 函数返回文本字段中值的长度。
SELECT LEN(City) as LengthOfCity FROM Persons
#SQL ROUND() 函数
#ROUND 函数用于把数值字段舍入为指定的小数位数。
'''
SQL ROUND() 语法:
SELECT ROUND(column_name,decimals) FROM table_name
参数 描述
column_name 必需。要舍入的字段。
decimals 必需。规定要返回的小数位数。
'''
SELECT ProductName, ROUND(UnitPrice,0) as UnitPrice FROM Products
#SQL NOW() 函数
#NOW 函数返回当前的日期和时间。
SELECT ProductName, UnitPrice, Now() as PerDate FROM Products
#SQL FORMAT() 函数
#FORMAT 函数用于对字段的显示进行格式化。
#SQL FORMAT() 语法
SELECT FORMAT(column_name,format) FROM table_name
'''
参数 描述
column_name 必需。要格式化的字段。
format 必需。规定格式。
'''
SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate
FROM Products
