SQL比知必会笔记

创建相关表

CREATE TABLE Customers(  cust_id      char(10)  NOT NULL ,  cust_name    char(50)  NOT NULL ,  cust_address char(50)  NULL ,  cust_city    char(50)  NULL ,  cust_state   char(5)   NULL ,  cust_zip     char(10)  NULL ,  cust_country char(50)  NULL ,  cust_contact char(50)  NULL ,  cust_email   char(255) NULL );-- ------------------------- Create OrderItems table-- -----------------------CREATE TABLE OrderItems(  order_num  int          NOT NULL ,  order_item int          NOT NULL ,  prod_id    char(10)     NOT NULL ,  quantity   int          NOT NULL ,  item_price decimal(8,2) NOT NULL );-- --------------------- Create Orders table-- -------------------CREATE TABLE Orders(  order_num  int      NOT NULL ,  order_date datetime NOT NULL ,  cust_id    char(10) NOT NULL );-- ----------------------- Create Products table-- ---------------------CREATE TABLE Products(  prod_id    char(10)      NOT NULL ,  vend_id    char(10)      NOT NULL ,  prod_name  char(255)     NOT NULL ,  prod_price decimal(8,2)  NOT NULL ,  prod_desc  text          NULL );-- ---------------------- Create Vendors table-- --------------------CREATE TABLE Vendors(  vend_id      char(10) NOT NULL ,  vend_name    char(50) NOT NULL ,  vend_address char(50) NULL ,  vend_city    char(50) NULL ,  vend_state   char(5)  NULL ,  vend_zip     char(10) NULL ,  vend_country char(50) NULL );-- --------------------- Define primary keys-- -------------------ALTER TABLE Customers ADD PRIMARY KEY (cust_id);ALTER TABLE OrderItems ADD PRIMARY KEY (order_num, order_item);ALTER TABLE Orders ADD PRIMARY KEY (order_num);ALTER TABLE Products ADD PRIMARY KEY (prod_id);ALTER TABLE Vendors ADD PRIMARY KEY (vend_id);-- --------------------- Define foreign keys-- -------------------ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Orders FOREIGN KEY (order_num) REFERENCES Orders (order_num);ALTER TABLE OrderItems ADD CONSTRAINT FK_OrderItems_Products FOREIGN KEY (prod_id) REFERENCES Products (prod_id);ALTER TABLE Orders ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY (cust_id) REFERENCES Customers (cust_id);ALTER TABLE Products ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY (vend_id) REFERENCES Vendors (vend_id);

了解SQL

1. 表由列组成，列也称为字段，表的行称为一个记录

2. 表名是唯一的，应该总是定义主键，SQL不区分大小写。

3. 所有的空格都被忽略，所以SQL语句可以写成多行。

4. 查询基本用法：

   1. 选择单列(返回的数据没有特定的顺序)：
      SELECT prod_name FROM Products;

   2. 选择多列：
      SELECT prod_id, prod_name, prod_price FROM Products

   3. 检索所有的列(建议少用)
      SELECT * FORM Products

   4. 检索不同的值：
      下面的检索会有重复
      SELECT vend_id FROM Products
      去除重复（DISTINCT关键字作用域所有列）：
      SELECT DISTINCT vend_id FROM Products;

   5. 限制结果(第5行起的5行数据，行数从0算):

      SELECT prod_name FORM Products LIMIT 5 OFFSET 5

5. 注释

   行内注释：– 或 #

   多行注释：/*commemt*/

排序检索数据

1. 排序

   指定一条order by语句时，应该保证它是select语句的最后一条子句，同时用非检索（不是查出来的）的列排序数据是完全合法的。

   select prod_name form Products order by prod_name

2. 按多个列排序(prod_price相同时，按照prod_name排序)

   select prod_id, prod_price, prod_name from Products order by prod_price, prod_name

3. 按列位置排序(先按第二列排序，再按第三列排序，可以混用实际列名和相对位置)

   select prod_id, prod_price, prod_name from Products order by 2, 3

4. 指定排序方向(DESC指定降序)

   在字典排序顺序中，A被视为与a相同

   select prod_id, prod_price, prod_name from Products order by prod_price descselect prod_id, prod_price, prod_name from Products order by prod_price DESC, prod_name

过滤数据

1. 使用where子句(order by位于where子句后面)

   select prod_name, prod_price from Products where prod_price=3

2. where子句操作符（有冗余的）

   操作符 说明 操作符 说明 = 等于 > 大于 <> 不等于 >= 大于等于 != 不等于 !> 不大于 < 小于 BETWEEN 在指定的两个值之间 <= 小于等于 IS NULL 为NULL值 !< 不小于

3. 检查单个值

   select prod_name, prod_price from Products where prod_price < 10

4. 不匹配检查

   单引号用来限定字符串，如果将值与字符串类型的列进行比较，就需要限定引号，用来与数值列进行比较的值不用引号

   select vend_id, prod_name from Products where vend_id <> 'DLL01'

5. 范围值检查

   select prod_name, prod_price from Products where prod_price between 5 and 10

6. 空值检查

   select prod_name from Porducts where prod_prices IS NULL

高级数据过滤

1. 组合where子句(可以多个and, or进行连接)

   select porod_id, prod_price, prod_name from Products where vend_id = 'DLL01' and prod_price <= 4

2. 注意and结合优先级大于or

   select prod_name, prod_price from Products where (vend_id = 'DLL01' or vend_id = 'BRS01') and prod_price >= 10

3. IN操作符(与or功能相同，IN操作符的语法更清楚)

   select prod_name, prod_price from Products where vend_id IN ('DLL01', 'BRS01') order by prod_name

4. NOT操作符，否定后面的条件（在复杂语句中使用有优势）

   select prod_name from Products where NOT vend_id = 'DLL01' order by prod_name也可以：select prod_name from Products where vend_id <> 'DLL01' order by prod_name

通配符进行过滤

1. %号通配符(接受Fish之后的任意字符，0个或多个，一条语句可以写多个%)

   select prod_id, prod_name from Products where prod_name LIKE 'Fish%'

2. 有些数据库会用空格来填充剩下的字段空间，这时候注意：

   LIKE 'f%y' 可能匹配不上f开头，y结尾的内容，要用：LIKE 'f%y%'

3. %不能匹配NULL

4. 下划线匹配单个字符

   select prod_id, prod_name from Products where prod_name LIKE '_ inch teddy bear'

5. 方括号[]指定一个字符集，必须配置指定的位置（只有微软的支持）

   select cust_contact from customers where cust_contact LIKE '[JM]%' order by cust_contact匹配JM中任意一个字符，也是只能匹配单个字符可以用[^JM]来表示否定

6. 创建计算字段(使用+号拼接结果，+号也可以用||替换)，下面查询的结果将会显示：name空格…(country空格…）

   select vend_name + '(' + vend_country + ')' from Vendors ordery by vend_name

   Mysql或MariaDB则需要使用以下语句:

   select Contact(vend_name, '(', vend_country,')') from Vendors order by vend_name

   许多数据库保存填充为列宽的文本值(即会填充空格)，mysql中括号中的空格可以这样去掉：

   select rtrim(vend_name) + '(' + RTRIM(vend_country) + ')' from vendors order by vend_name

   使用别名(别名既可以是一个单词，也可以是一个字符串，如果是后者，要放在括号中)：

   select RTRIM(vend_name) + ' (' + RTRIM(vend_country) + ')' AS vend_title from Vendors order by vend_name

   执行算术操作：

   select prod_id, quantity, item_price,quantity*item_price AS expand_pricefrom OrderItems where order_num = 20008

使用函数

1. 文本处理函数

   select vend_name, UPPER(vend_name) as vend_name_upcase from Vendors order by vend_name

   常见的文本处理函数：

   LEFT():返回字符串左边的字符

   LENGTH():返回字符串的长度

   LOWER():将字符串转换为小写

   LTRIM():去掉字符串左边的字符

   RIGHT():返回字符串右边的字符

   SOUNDEX()：返回字符串的SOUNDEX值

   UPPER()：将字符串转为大写

   select cust_name, cust_contact from Customers where cust_contact='Michael Green'//匹配所有发音类似的联系名select cust_name, cust_contact from Customers where SOUNDEX(cust_contact)=SOUNDEX('Michael Green')

2. 日期和时间处理函数

   //SQLServer中检索2012年的所有订单，第一个参数是返回值select order_num from Orders where DATEPART(yy, order_date)=2012//Mysqlselect order_num from orders where YEAR(order_date)=2012

3. 数值处理函数

   ABS():返回绝对值COS():余弦EXP():指数值PI():圆周率SIN():正弦SQRT():数的平方根TAN():正切

汇总数据

1. 聚集函数

   AVG(): 返回某列的平均值

   COUNT():返回行数

   MAX():最大值

   MIN():最小值

   SUM():返回某列值之和、

   //AVG()只能用于单个列，会忽略列值为NULL的行select AVG(prod_price) as avg_price from Products//计算所有行，不会忽略值为NULL的行select COUNT(*) as num_cust from Customers//只对单个列计数，忽略NULL的行select COUNT(cust_email) as num_cust from Customers//返回指定列的最大值，用于文本的话返回该列排序后的最后一行，忽略NULLselect MAX(prod_price) AS max_price FROM Products//同上，只不过意思相反select MIN(prod_price) AS min_price FROM Products//忽略NULL值select sum(quantity*quantity) as total_price from OrderItems where order_num=20005//DISTINCT针对值不同的列操作，不能用于COUNT(*)，与DISTINCE相反的参数是ALL，但是是默认的select AVG(DISTINCT prod_price) AS avg_price from Products where vend_id='DLL01'//组合聚集函数select COUNT(*) AS num_items,MIN(prod_price) AS price_min,MAX(prod_price) AS price_max,AVG(prod_price) AS pirce_avgFROM Products;

分组数据

1. 数据分组

   order by子句必须出现在where子句之后，order by 子句之前

   select COUNT(*) AS num_prods FROM Products group by vend_id;

2. 过滤分组(HAVING),在数据分组后进行过滤(WHERE在分组前过滤)

   select cust_id, COUNT(*) AS orders from orders GROUP BY cust_id HAVING COUNT(*) >= 2select vend_id, COUNT(*) AS num_prods FROM Products where prod_price>=4 group by vend_idhaving count(*)>=2select order_num, COUNT(*) AS items FROM OrderItems GROUP BY order_num HAVING COUNT(*)>=3ORDER BY items, order_num

使用子查询（推荐使用连接查询）

1. 使用子查询

   select cust_id from orders where order_num IN(20007,20008)//子查询select cust_id from orders where order_num IN (select order_num from orderitemswhere prod_id='RGA001')//使用了表的限定名select cust_name,cust_state,(SELECT COUNT(*)FROM OrdersWHERE Orders.cust_id = Customers.cust_id) AS ordersfrom Customersorder by cust_name

连接表

1. 创建连接

   //从两个表中选择字段，没有where子句将会返回笛卡尔积,称为等值连接select vend_name, prod_name, prod_price from Vendors, Products where Vendors.vend_id=Products.vend_id//内连接（上面其实也是内连接，只不过没采用标明的方式）select vend_name, prod_name, prod_price from Vendors INNER JOIN Products ON Vendors.vend_id=Porducts.vend_id

2. 连接多个表(连接的表越多，性能下降越厉害)

   select prod_name, vend_name, prod_price, quantity from OrderItems, Products, Vendors where Porducts.vend_id=Vendors.vend_idAND OrderItems.prod_id=Products.prod_idAND order_num=20007

创建高级连接查询

1. 使用表别名(常用于where语句)

   select RTRIM(vend_name)+' ('+RTRIM(vend_country)+')' AS vend_title from VendorsORDER BY vend_name;

2. 其他一些类型的连接

   //自连接select c1.cust_id, c1.cust_name, c1.cust_contact from Customers AS c1, Customers AS c2where c1.cust_name = c2.cust_nameAND c2.cust_contact='Jim Jones'; //自然连接，去掉重复的列(两张表中拿来比较的列)select C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity,OI.item_pricefrom Customers AS C, Orders AS O, OrderItems AS OIWHERE C.cust_id=O.cust_id    AND OI.order_num=O.order_num    AND prod_id='RGAN01'//外连接，左外连接，以左边为主，选择左边所有的行select Customers.cust_id, Orders.order_numfrom Customers LEFT OUTER JOIN Orders    On Customers.cust_id=Orders.cust_id//还有一种是 FULL OUTER JOIN，全外连接，左右都要全选

3. 使用带聚集函数的连接

   select Customers.cust_id,COUNT(Orders.order_num) AS num_ordFROM Customers INNER JOIN Orders ON Customers.cust_id=Orders.cust_idGROUP BY Customers.cust_id

组合查询

1. 使用UNION操作符组合数条SQL查询,组合成一个查询集，必须是查询相同的列

   //这个例子用OR也能实现，对于复杂问题用UNION比较方便SELECT cust_name, cust_contact, cust_emailFROM CustomersWHERE cust_state IN('IL', 'IN', 'MI')UNIONSELECT cust_name, cust_contact, cust_eamilFROM CustomersWHERE cust_name='Fun4All'会自动去除重复的行,如果不想去除使用UNION ALLUNION支持在最后放一个ORDER BY排序语句，对整个结果起作用

插入数据

1. 数据插入,每一列都给出值，并且依赖顺序

   select into Customers values('..','..',NULL,NULL)

2. 插入指定列

   insert into Customers(cust_id,cust_name...) values('..','..')

3. 省略某些列(该列可以被定义为NULL值)

4. 插入检索出的数据

   insert into Customers(cust_id,...)select cust_id,....from CustNew

5. 从一个表复制到另一个表

   select * into CustCopy from Customers或create table CustCopy as select * from Customers

   ​

更新和删除数据

1. 更新(一个或多个列)

   update Customers set cust_email='...' where cust_id='...'

2. 删除数据

   delete from Customers where cust_id='...'

创建和操作表

1. 表创建

   create table Products(prod_id  char(10) not null,...);

   使用NULL：

   NULL值就是没有值，允许NULL也允许在插入的时候不给出该列的值

   每个表要么是NULL列，要么是NOT NULL列，不指出，默认为NULL

   指定默认值：

   quantity INTEGER NOT NULL DEFAULT 1

2. 增加、删除列：

   ALTER TABLE VendorsADD vend_phone CHAR(20)ALTER TABLE VendorsDROP COLUMN vend_phone;

3. 删除列

   DROP TABLE CustCopy

使用视图

1. 视图：视图是虚拟的表,方便重复使用以及格式化结果

2. 创建视图：

   CREATE VIEW ProductCustomers ASSELECT cust_name, cust_contact, prod_idFROM Customers, Orders, OrderItemsWHERE Customers.cust_id=Orders.cust_idAND OrderItems.order_num=Orders.order_num

   使用：

   SELECT cust_name, cust_contactFROM ProductCustomersWHERE prod_id='RGAN01'

   格式化结果的案例：

   CREATE VIEW VendorLocations ASSELECT RTRIM(vend_name) + '('+RTRIM(vend_country)+')'AS vend_titleFROM Vendors

   使用：

   SELECT * FROM VendorLocations

存储过程

1. 存储过程

   @开头的为局部变量，此存储过程没有参数

   CREATE PROCEDURE MailingListCountASDECLARE @cnt INTEGERSELECT @cnt = COUNT(*)FROM CustomersWHERE NOT cust_email IS NULL;RETURN @cnt;

   调用:

   DECLARE @ReturnValue INTEXECUTE @ReturnValue=MailingListCount;SELECT @ReturnValue #显示返回值

   有参数的存储过程(创建一个新的订单，传入顾客的订单号，GETDATE返回日期)：

   CREATE PROCEDURE NewOrder @cust_id CHAR(10)DECLARE @order_num INTEGERSELECT @order_num=MAX(order_num) FROM OrdersSELECT @order_num=@order_num+1INSERT INTO Orders(order_num,order_date,cust_id)VALUES(@order_num,GETDATE(),@cust_id)

   @@IDENTITY获取自动生成的自增长的标识字段

   CREATE PROCEDURE NewOrder @cust_id CHAR(10)ASINSERT INTO Orders(cust_id)VALUES(@cust_id)SELECT order_num=@@IDENTITY

事务管理

1. 一般SQL语句都是针对数据库表直接执行和编写的，即隐式提交，在事务中提交不会隐式进行

   BEGIN TRANSACTIONDELETE OrderItems WHERE order_num = 12345DELETE Orders WHERE order_num = 12345COMMIT TRANSACTION

   部分回退，可以在事务执行的过程中插入一个占位符：

   SAVEPOINT delete1

   使用该占位符：

   ROLLBACK TO delete1

游标

1. 创建游标：

   DECLARE CustCursor CURSORFOR SELECT * FROM CustomersWHERE cust_email IS NULL

   使用游标：

   OPEN CURSOR CustCursor

   关闭游标：

   CLOSE CustCursor

高级特性

1. 主键不为空

   vend_id CHAR(10) NOT NULL PRIMARY KEY

   或者添加主键约束

   ALTER TABLE VendorsADD CONSTRAINT PRIMARY KEY (vend_id);

2. 外键

   cust_id CHAR(10) NOT NULL REFERENCES Customers(cust_id)

   或者添加约束

   ALTER TABLE OrdersADD CONSTRAINTFOREIGN KEY (cust_id) REFERENCES Customers (cust_id)

3. 检查约束

   quantity INTEGER NOT NULL CHECK (quantity > 0 )

4. 索引：

   类似于排序的原理，加快检索的过程（主键一般都是排序的）

   CREATE INDEX prod_name_indON Products (prod_name)

5. 触发器：

   下面是一个触发器的例子，对于所有INSERT和UPDATE操作，将cust_state转为大写

   CREATE TRIGGER customer_stateON CustomersFOR INSERT, UPDATEASUPDATE CustomersSET cust_state = Upper(cust_state)WHERE Customers.cust_id = inserted.cust_id

   ​

   ​