[科大讯飞] SQL数据库复习资料

1、Ø DATABASE语句创建数据库:CREATE DATABASE 数据库名称 删除数据库:DROP DATABASE 数据库名 称Ø TABLE语句创建表:CREATE TABLE 表名称 ( 列名称 1 数据类型 , 列名称 2 数据类型 , 列名称 3 数 据类型 , . 增加列:ALTER TABLE table_name ADD column_name datatype删除列:ALTER TABLE table_name DROP COLUMN column_name修改列:ALTER TABLE table_name ALTER COLUMN column_na

2、me datatype删除表:DROP TABLE 表名称删除表中数据:A 、 DELETE FROM表名称B 、 TRUNCATE TABLE 表名称Ø INSERT INTO语句INSERT INTO 表名称 V ALUES (值 1, 值 2,. 说明:默认所有列,所以 V ALUES 中值需 要对应列数和列顺序例:INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing'INSERT INTO table_name (列 1, 列

3、2,. VALUES (值 1, 值 2,.说明:列名和值一一对应 例:INSERT INTO Persons (LastName, Address VALUES ('Wilson', 'Champs-Elysees'Ø Update 语句UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值说明:修改多列时, 之间用逗号隔开, 另外通常加上 WHERE 子句, 否则修改表中所有行。 例 :UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing

4、' WHERE LastName = 'Wilson'Ø DELETE 语句DELETE FROM 表名称 WHERE 列名称 = 值 说明:通常加上 WHERE 子句,否则删除 所有的行。 例:DELETE FROM Person WHERE LastName = 'Wilson'Ø RDBMS 指的是关系型数据库管理系统。比如 MS SQL Server, IBM DB2, Oracle, MySQL 以及 Microsoft Access。Ø SELECT 语句:SELECT 列名称 FROM 表名称SELECT *

5、FROM 表名称Ø SELECT DISTINCT 语句:关键词 DISTINCT 用于返回唯一不同的值SELECT DISTINCT 列名称 FROM 表名称说明:列名称若有多个,多列数据必须全部一样,才认为是重复。Ø WHERE 子句SELECT 列名称 FROM 表名称 WHERE 列 运算符 值例:SELECT * FROM Persons WHERE City='Beijing' SQL使用单引号来环绕文本值(大部分数据库系统也接受双引号 。如果是数值,请不要使用引号。Ø AND和 OR 运算符例:SELECT * FROM Person

6、s WHERE FirstName='Thomas' AND LastName='Carter'例:SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'Ø ORDER BY语句升序:ASC (默认降序:DESC例:SELECT Company, OrderNumber FROM Orders ORDER BY Company例:SELECT Company, OrderNumber FROM Orders ORDER BY Company

7、DESC例 :SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC, OrderNumber ASC先按 Company 降序排列,遇到 Company 相同的列时,这些列再按 OrderNumber 升序排列, 若 Company 没有相同项时,则 OrderNumber 无意义。Ø TOP子句:用于规定要返回的记录的数目SELECT TOP number|percent column_name(s FROM table_name通常和 ORDER BY 一起使用Ø LIKE操作符例:SELECT *

8、FROM Persons WHERE City LIKE 'N%'例:SELECT * FROM Persons WHERE City LIKE '%lon%'Ø IN 操作符SELECT column_name(s FROM table_name WHERE column_name IN (value1,value2,. 例:SELECT * FROM Persons WHERE LastName IN ('Adams','Carter'例:SELECT * FROM Persons WHERE LastName IN

9、 (SELECT LastName FROM Users 说明:IN 后面的 SELECT 子句只能查询一列Ø BETWEEN 操作符例:SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'Ø INNER JOIN关键字SELECT column_name(s FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_nameØ LEFT JO

10、IN 关键字SELECT column_name(s FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_nameØ UNION 操作符:用于合并两个或多个 SELECT 语句的结果集。请注意, UNION 内部的 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据 类型。同时,每条 SELECT 语句中的列的顺序必须相同。另外, UNION 结果集中的列名总是等于 UNION 中第一个 SELECT 语句中的列名。 SELECT column_name(

11、s FROM table_name1 UNION SELECT column_name(s FROM table_name2Ø SQL 系统函数 :A VG 、 COUNT 、 MAX 、 MIN 、 SUMØ GROUP BY 语句SELECT column_name, aggregate_function(column_name FROM table_name WHERE column_name operator value GROUP BY column_nameØ 变量定义及赋值DECLARE 变量名 数据类型 如 DECLARE name varchar

12、(10 注意:不可缺少 符号A 、 SET name = feipeng B 、 SELECT name = Name FROM Users WHERE UserId = A101Ø 存储过程A 、不带参数:CREATE PROC sp_InsertUserBEGIN-SQL语句SELECT * FROM UserEND执行存储过程:EXEC sp_InsertUserB 、带参数:CREATE PROC sp_InsertUseruserId varchar(50,userName varchar(10BEGIN-SQL语句INSERT INTO User VALUES(userI

13、d,userNameEND执行存储过程:EXEC sp_InsertUser A101,feipengEXEC sp_InsertUser userId=A101,userName=feipengEXEC sp_InsertUser userName=feipeng, userId=A101C 、带输出参数:CREATE PROC sp_InsertUseruserId varchar(50,userName varchar(10,userCount OUTPUTBEGIN-SQL语句INSERT INTO User VALUES(userId,userNameSELECT userCount

14、 = COUNT(* FROM UserEND执行存储过程:DECLARE uc INTEXEC sp_InsertUser A101,feipeng,uc OUTPUTEXEC sp_InsertUseruserId=A101,userName=feipeng,userCount=uc OUTPUTEXEC sp_InsertUser userName=feipeng, userId=A101,userCount=uc OUTPUT优点:A 、模块化设计 像其他程序语言的函数一样,单独出来,可以调用它 n 次,并 且可以独立于源代码,单独修改储存过程。B 、更快执行 如果执行大量 SQL 代

15、码或重复执行,储存过程比 SQL 批代码执行 要快。C 、减少网络流量 一条 SQL 语句就可以执行上千条 SQL 代码。Ø 事务BEGIN TRANDECLARE SumError INTSET SumError = 0INSERT INTO User VALUES('A1001','feipeng'SET SumError = SumError + ERRORDELETE FROM Class WHERE ClassId = 'B110'SET SumError = SumError + ERRORIF SumError <&

16、gt; 0BEGINROLLBACK TRANENDELSEBEGINCOMMIT TRANENDØ触发器CREATE TRIGGER 触发器名称ON 表名称AFTER INSERT,DELETE,UPDATEASBEGIN- Insert statements for trigger hereEND两张逻辑表:inserted 、 deleted删除操作:deleted插入操作:inserted更新操作:deleted 、 insertedØ游标示例代码declare cur_Employees SCROLLcursor forselect EmployeeID,Birt

17、hDatefrom HumanResources.Employee where EmployeeID <6FOR READ ONLYopen cur_Employeesdeclare EmployeeID intdeclare BirthDate datetimefetch NEXT from cur_Employees into EmployeeID,BirthDatewhile fetch_status = 0beginprint convert(char(3,EmployeeID+' | '+convert(varchar,BirthDate fetch next

18、from cur_Employees into EmployeeID,BirthDateendclose cur_Employeesdeallocate cur_EmployeesØ 创建临时表CREATE TABLE #表名称 ( 列名称 1 数据类型 , 列名称 2 数据类型 , 列名称 3 数据类 型 , . 注意：表名称前加 # Ø 执行自定义的 SQL 语句 DECLARE sql VARCHAR(1000 SET sql = 'SELECT * FROM Class' SET sql = 'SELECT * FROM User WHERE Name like ''%' + name + '%''' EXEC (sql Ø 索引 微软的 SQL SERVER 提供了两种索引：聚集索引、非聚集索引。 注意：每个表只能有一个聚集索引 索引是对数据库表中一列或多列的值进行排序的一种结构， 使用索引可快速访问数据库表中 的特定信息。 大多数情况下，聚集索引的速度比非聚集索引要略快一些。因为聚集索引的 B 树叶子节点 直接存储数据，而非聚集索引还需要额外通过叶子节点的指针找到数据。 我最喜欢的一句话是”everything has