Data Base学习记录：SQL_数据定义、修改、查询语言

最新推荐文章于 2022-06-08 16:36:13 发布

@Lucia1314

最新推荐文章于 2022-06-08 16:36:13 发布

阅读量173

点赞数

分类专栏：数据库文章标签： sql

本文链接：https://blog.csdn.net/m0_49564079/article/details/115486704

版权

数据库专栏收录该内容

12 篇文章 1 订阅

订阅专栏

数据定义语言

数据库

数据库的建立

数据库的删除

DROP DATABASE UserTest

数据库的使用

USE UserTest
则以下的对表进行操作的SQL语句均在UserTest 中。如下语句能够正确执行吗？

CREATE DATABASE UserTest
USE UserTest
出错，因为一批SQL语句中间没有GO的话，会认为一起提交。改正：
CREATE DATABASE UserTest

GO

USE UserTest

正在使用的数据库能够被删除吗？

表

表的建立

建立表：

CREATE TABLE	表名(
列名1	数据类型1,列名2	数据类型2,主键说明,外部键说明)

基本构件：关键字、表名、列名、数据类型。表名在整个数据库中要唯一
主键的定义：
- CONSTRAINT 主键约束的名字 PRIMARY KEY [CLUSTERED | NONCLUSTERED] (作为主键的列名)
- 主键不能定义在可以为空的列上
- 每个表只能创建一个 PRIMARY KEY 约束
外部键的定义：
- CONSTRAINT 外部键约束的名字 FOREIGN KEY (本表的列名) REFERENCES 另一个表2 (表2中对应的列名)
- 表2中对应的列名必须为表2的主键
- 约束名在整个数据库中要唯一

CREATE TABLE Students(
st_id integer NOT Null UNIQUE, st_name varchar(50) NOT Null,
pwd varchar(20) Null DEFAULT	'0000', birthday datetime Null
CONSTRAINT cc_birth CHECK(Year(birthday)>1985),
st_dept integer NOT Null ,
CONSTRAINT pk_Students PRIMARY KEY
NONCLUSTERED(st_id),
CONSTRAINT fk_Students FOREIGN KEY(st_dept)
REFERENCES st_Dept(deptno)
)
CREATE TABLE st_Dept( deptno	integer NOT Null
CONSTRAINT pk_st_Dept PRIMARY KEY,
deptname varchar(50) NOT Null CONSTRAINT upper_name
CHECK(deptname=UPPER(deptname)), fdtime datetime DEFAULT GETDATE()
)

表的修改

ALTER TABLE table
[ ALTER COLUMN 列名 数据类型等属性 ]
| ADD	[列的定义|约束的定义]
| DROP {[CONSTRAINT] 约束名|COLUMN 列名 }
|{CHECK | NOCHECK} CONSTRAINT {ALL|约束名}
|{ENABLE | DISABLE} TRIGGER {ALL|触发器名}

表的修改例

不检查出生年份是否>1985，则

ALTER TABLE Students NOCHECK CONSTRAINT cc_birth

ALTER TABLE Students ALTER COLUMN st_name integer Null
不能改已经对它建立了约束的列的属性，包括主键、外部键、UNIQUE等。
ALTER TABLE Students ADD CONSTRAINT
pk2_Students PRIMARY KEY NONCLUSTERED (st_name)正确吗？
ALTER TABLE Students DROP pk_Students

表的删除

DROP TABLE 表名
DROP TABLE Students
不能删除正由一个 FOREIGN KEY 约束引用的表。必须先删除引用的 FOREIGN KEY 约束或引用的表
DROP TABLE Students GO

DROP TABLE st_Dept

索引

索引的建立

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
INDEX 索引名
ON {表名|视图名} ( 列名 [ ASC | DESC ] [ ,...n ] )
--升序或降序。默认为 ASC

CREATE UNIQUE NONCLUSTERED INDEX Idx_st_Dept
ON st_Dept(deptname)
在表st_Dept的deptname列上建立唯一非簇类索引
Idx_st_Dept

索引名在表或视图中要唯一
注意：CREATE INDEX Idx_Test ON Test (colm1,colm2)与CREATE INDEX Idx_Test ON Test (colm2,colm1)二者不同。前者以colm1为主顺序，后者以colm2为主顺序

索引的删除

DROP INDEX '表名.索引名|视图名.索引名' [ ,...n ]
DROP INDEX st_Dept. Idx_st_Dept

视图

视图的建立

CREATE VIEW [<数据库名>.] [<用户名>.]视图名 [(列名[ ,...n])] 
–若不指定列名，则与select保持一致
[ WITH < view_attribute > [ ,...n ] ]
AS select_statement	[ WITH CHECK OPTION ]
--强制视图上执行的所有数据修改语句都必须符合由select_statement 设置的准则。

CREATE VIEW创建一个虚拟表，该表以另一种方式表示一个或多个表中的数据
CREATE VIEW必须是查询批处理中的第一条语句

CREATE VIEW TestView1(col1,col2,col3,col4,col5) AS
SELECT * FROM Students WHERE Year(birthday)>1988 GO
CREATE VIEW TestView2(col1,col2,col3) AS
SELECT a.st_name,b.deptno,b.deptname FROM Students a,st_Dept b WHERE a.st_dept=b.deptno

给视图指定列名个数必须与SELECT语句列数相同
视图只有满足下列条件才可更新：
- select_statement 在选择列表中没有聚合函数(聚合函数对一组值执行计算并返回单一的值，如 AVG, COUNT,SUM,MIN,MAX)，也不包含 TOP、GROUP BY、UNION或 DISTINCT 子句。在 FROM 子句的子查询中允许使用聚合函数。
- select_statement 的选择列表中没有派生列。派生列是由任何非简单列表达式(使用函数或运算符等)所构成的结果集列。
- select_statement 中的 FROM 子句至少引用一个表。
以下视图是不可更新的：

CREATE VIEW TestView AS
SELECT GETDATE() AS CurrentDate, @@LANGUAGE AS CurrentLanguage, CURRENT_USER AS CurrentUser

视图的删除

DROP VIEW { 视图名 } [ ,...n ]
DROP VIEW TestView

数据修改语言

INSERT

INSERT将新行添加到表或视图

INSERT [ INTO]{ 表名 |视图名 }	{ [ ( 列名列表 ) ]
{ VALUES({DEFAULT|NULL|表达式})
| derived_table}

DEFAULT强制该列值为为该列定义的默认值。如果对于某列并不存在默认值，并且该列允许 NULL，则插入 NULL
derived_table任何有效的SELECT语句
```
Students(st_id,st_name,pwd,birthday,st_dept) 
st_Dept(deptno,deptname,fdtime)
```
- INSERT st_Dept VALUES(184, ‘AUTOMATIC CONTROL’, DEFAULT)
  INSERT Students VALUES(2000184013, ‘王菲’, DEFAULT, ‘1976-01-01’, 184)
  - 上面两句之间用不用加上GO语句？
  - 如果要在有外部键的表中插入数据，则对应外部键的列值必须在被参照表中存在。
- INSERT Students
  SELECT st_id+10,Left(st_name,1)+‘燕’,pwd,‘4-3-1985’,st_dept FROM Students WHERE Left(st_name,1) =‘王’
INSERT语句注意事项：
- 主键列不能为空和重复
- 不允许为空的列必须给予赋值
- 赋的值要满足约束条件

UPDATE

UPDATE更改表中的现有数据

UPDATE	{ 表名 | 视图名}
SET
列名 = { expression | DEFAULT | NULL }
[ FROM { < table_source > }]	--指定条件时要用到的表[ WHERE < search_condition > ]

FROM 子句中指定的表的别名不能作为 SET column_name 子句中要修改的列名的限定符使用

st_score(st_id,Eng_score,Math_score,AC_score) 
students(st_id,st_name,pwd,birthday,st_dept) 
courses(course_id,course_name,type,percents,teacher_id)

将系编号为‘184’的学生的英语成绩乘以课程难度系数。假设‘0001’为英语课代码，percents为课程难度系数

UPDATE st_score
SET t.Eng_score = t.Eng_score * s.percents FROM st_score t, courses s
WHERE s.course_id = '0001' AND t.st_id IN (SELECT students.st_id FROM students WHERE students.st_dept = '184')

上面的内容是否有错？
改正：SET Eng_score = t.Eng_score * s.percents

DELETE

DELETE从表中删除行

DELETE	[ FROM ]
{表名 | 视图名}
[ FROM { < table_source > } [ ,...n ] ] --指定条件的表
[ WHERE { < search_condition > ]

DELETE authors
删除表authors？
不带参数使用 DELETE将删除表中所有行

 titles(title_id,title,pub_id,type) 
titleauthor(title_id,au_id) 
authors(au_id,name,address)

二者等价

DELETE FROM titleauthor
WHERE title_id IN (SELECT title_id FROM titles WHERE title LIKE '%computers%')

DELETE titleauthor
FROM titleauthor AS a	INNER JOIN titles AS b ON a.title_id=b.title_id
WHERE titles.title LIKE '%computers%'

DELETE authors
FROM (SELECT TOP 10 * FROM authors ) AS t1
WHERE authors.au_id = t1.au_id

数据查询语言

SELECT基本语法

SELECT select_list --要查询的内容
[ INTO new_table ] --将结果存放到一个新表
FROM table_source --指定所使用的表、视图
[ WHERE search_condition ] --查询的条件
[ GROUP BY group_by_expression ] --分组
[ ORDER BY order_expression [ ASC | DESC ] ] --排序

SELECT子句

SELECT[ALL|DISTINCT][TOP n [PERCENT]]<select_list>
- 缺省为ALL。空值认为相等
- TOP n [PERCENT]只从查询结果中输出前n行或前n%行
- 带 PERCENT 时，n 必须是介于 0 和 100 之间的整数
- 如果查询包含 ORDER BY 子句，将输出由 ORDER BY 子句排序的前 n 行(或前百分之 n 行)
- SELECT DISTINCT TOP 10 Eng_score FROM st_scoreORDER BY Eng_score ASC输出最低的10种英语成绩的数值

AS子句

AS 子句可用于为结果集列指定不同的名称或别名。
SELECT EmpSSN AS "Employee Social Security Number" FROM EmpTable
st_score(st_id,Eng_score,Math_score,AC_score)查询各个年级的英语平均成绩

SELECT Left(st_id,4) AS grade# , Sum(Eng_score)/Count(Eng_score) AS Avg_Eng FROM st_score
GROUP BY Left(st_id,4) ORDER BY grade# ASC
--GROUP BY子句不能利用grade#ASC

UNION子句

可以在多个查询之间使用 UNION 运算符，以将查询的结果组合成单个结果集，
- 该结果集显示的列名是前一个SELECT的列名
- 该结果集包含联合查询中的所有查询的全部行
- 如果UNION连接的两个查询结果列不一致，则会将后一个查询结果转换成前一个查询结果的列的数据类型
如果两表列数不同，则不能用UNION

SELECT * FROM TestStu2 UNION SELECT 
FROM TestStu1
和
SELECT * FROM TestStu1 UNION SELECT * FROM
TestStu2
不同

SELECT示例

Customers(Cust_Name,Country,City,Phone) 
SouthAmericanCustomers (Cust_Name,Country,City,Phone)

SELECT Cust_Name,Country,City,Phone INTO #CustomerResults
	FROM Customers
	WHERE Country IN ('USA','Canada') --北美记录UNION
	SELECT Cust_Name,Country,City,Phone FROM SouthAmericanCustomers
	INTO子句指定名为 CustomerResults 的表包含由Customers 和 SouthAmericanCustomers 表中指定列的并集组成的最终结果集。

st_score(st_id,Eng_score,Math_score,AC_score) students(st_id,st_name,pwd,birthday,st_dept)查询最高英语成绩的学生学号和英语成绩

SELECT st_id,Eng_score FROM st_score
WHERE Eng_score = (SELECT Max(Eng_score) FROM st_score )
ORDER BY st_id

titles(title_id,ytd_sales ) 
titleauthor(title_id,au_id ) 
authors(au_id,au_fname,au_lname )

SELECT ytd_sales AS Sales, authors.au_fname + ' ' + authors.au_lname AS Author
INTO #Tmp_count
FROM titles INNER JOIN titleauthor ON titles.title_id = titleauthor.title_id INNER JOIN authors ON titleauthor.au_id = authors.au_id
ORDER BY Sales DESC, Author ASC

 st_score(st_id,Eng_score,Math_score,AC_score) 
students(st_id,st_name,pwd,birthday,st_dept)

 查询英语成绩为100的学生的名字并存放在临时表highscore中。
SELECT DISTINCT st_name INTO #highscore FROM students
WHERE 100 IN (SELECT Eng_score FROM st_score WHERE st_score.st_id = students.st_id)
	* SELECT DISTINCT st_name INTO #highscore FROM students
WHERE st_id IN (SELECT st_id FROM st_score WHERE st_score.Eng_score = 100)
	* SELECT DISTINCT st_name INTO #highscore FROM students a,st_score b	WHERE a.st_id = b.st_id AND b.Eng_score = 100
* customers(cust_name,address,postcode,acc_id) accouts(acc_id,type,balance,fdtime)

查询存款类型存在check类型的银行客户名称。
SELECT DISTINCT cust_name FROM customers
WHERE EXISTS (SELECT * FROM accouts WHERE
acc_id = customers.acc_id AND type = 'check')

SELECT DISTINCT cust_name FROM customers
WHERE acc_id IN (SELECT acc_id FROM accouts WHERE type = 'check')

SELECT DISTINCT cust_name FROM customers AS a
INNER JOIN accouts AS b ON a. acc_id = b. acc_id WHERE b. type = 'check'

SELECT步骤总结

分析所要查询的内容和条件分布在哪些表中
在SELECT子句中写要查询的内容
在FROM子句中写所涉及的表
在WHERE子句中写所有的条件
- 注意：利用“表名.”引用该表的列名
- 若要分组查询则加入GROUP BY子句
- 若要排序则利用ORDER BY子句
- 若要将查询结果保存在一个临时表中则利用INTO子句

SELECT示例

st_score(st_id,Eng_score,Math_score,AC_score) 
st_highscore(st_id,score,course_id) 
courses(course_id,course_name,type,percents,teacher_id)

查询各科成绩最高的学生的学号并将相关信息保存在st_highscore表中
INSERT st_highscore (st_id,score,course_id) SELECT a.st_id,a.Eng_score,b.course_id
FROM st_score a, courses b
WHERE Eng_score = (SELECT Max(Eng_score) FROM st_score ) AND b.course_name = '英语'
类似将其他课程的进行处理。

若为st_score(st_id,course_id,score)该如何处理？

三表联合查询
st_highscore(st_id,score,course_id) students(st_id,st_name,pwd,birthday,st_dept)
courses(course_id,course_name,type,percents,teacher_id)

查询在‘import’类型的课程中考过最高分的学生名字。
SELECT st_name
FROM students a, st_highscore b, courses c
WHERE a.st_id = b.st_id AND b.course_id = c.course_id AND c.type = 'import'
SELECT st_name FROM students
WHERE st_id IN (SELECT st_id FROM st_highscore WHERE course_id IN (SELECT course_id FROM courses WHERE type
= 'import'))