SQL的学习

SQL的学习之路，主要参考资料是：

1. SQL必知必会
2. W3school的SQL简介

操作使用的数据库为Access及MySQL。

数据采用的是SQL必知必会中的数据（最下方可以下载数据）。其有5个表，表的关系如下：

# 这里补充下，MySQL创建该数据库（2017.5.17）,复制到MySQL创建执行创建即可（数据来源于《SQL必知必会》）；

-- ----------------------
-- Create Customers table
-- ----------------------
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);

# 插入数据
-- ------------------------
-- Populate Customers table
-- ------------------------
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000001', 'Village Toys', '200 Maple Lane', 'Detroit', 'MI', '44444', 'USA', 'John Smith', 'sales@villagetoys.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000002', 'Kids Place', '333 South Lake Drive', 'Columbus', 'OH', '43333', 'USA', 'Michelle Green');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000003', 'Fun4All', '1 Sunny Place', 'Muncie', 'IN', '42222', 'USA', 'Jim Jones', 'jjones@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact, cust_email)
VALUES('1000000004', 'Fun4All', '829 Riverside Drive', 'Phoenix', 'AZ', '88888', 'USA', 'Denise L. Stephens', 'dstephens@fun4all.com');
INSERT INTO Customers(cust_id, cust_name, cust_address, cust_city, cust_state, cust_zip, cust_country, cust_contact)
VALUES('1000000005', 'The Toy Store', '4545 53rd Street', 'Chicago', 'IL', '54545', 'USA', 'Kim Howard');

-- ----------------------
-- Populate Vendors table
-- ----------------------
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRS01','Bears R Us','123 Main Street','Bear Town','MI','44444', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('BRE02','Bear Emporium','500 Park Street','Anytown','OH','44333', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('DLL01','Doll House Inc.','555 High Street','Dollsville','CA','99999', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FRB01','Furball Inc.','1000 5th Avenue','New York','NY','11111', 'USA');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('FNG01','Fun and Games','42 Galaxy Road','London', NULL,'N16 6PS', 'England');
INSERT INTO Vendors(vend_id, vend_name, vend_address, vend_city, vend_state, vend_zip, vend_country)
VALUES('JTS01','Jouets et ours','1 Rue Amusement','Paris', NULL,'45678', 'France');

-- -----------------------
-- Populate Products table
-- -----------------------
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR01', 'BRS01', '8 inch teddy bear', 5.99, '8 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR02', 'BRS01', '12 inch teddy bear', 8.99, '12 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BR03', 'BRS01', '18 inch teddy bear', 11.99, '18 inch teddy bear, comes with cap and jacket');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG01', 'DLL01', 'Fish bean bag toy', 3.49, 'Fish bean bag toy, complete with bean bag worms with which to feed it');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG02', 'DLL01', 'Bird bean bag toy', 3.49, 'Bird bean bag toy, eggs are not included');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('BNBG03', 'DLL01', 'Rabbit bean bag toy', 3.49, 'Rabbit bean bag toy, comes with bean bag carrots');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RGAN01', 'DLL01', 'Raggedy Ann', 4.99, '18 inch Raggedy Ann doll');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL01', 'FNG01', 'King doll', 9.49, '12 inch king doll with royal garments and crown');
INSERT INTO Products(prod_id, vend_id, prod_name, prod_price, prod_desc)
VALUES('RYL02', 'FNG01', 'Queen doll', 9.49, '12 inch queen doll with royal garments and crown');

-- ---------------------
-- Populate Orders table
-- ---------------------
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20005, '2012-05-01', '1000000001');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20006, '2012-01-12', '1000000003');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20007, '2012-01-30', '1000000004');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20008, '2012-02-03', '1000000005');
INSERT INTO Orders(order_num, order_date, cust_id)
VALUES(20009, '2012-02-08', '1000000001');

-- -------------------------
-- Populate OrderItems table
-- -------------------------
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 1, 'BR01', 100, 5.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20005, 2, 'BR03', 100, 10.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 1, 'BR01', 20, 5.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 2, 'BR02', 10, 8.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20006, 3, 'BR03', 10, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 1, 'BR03', 50, 11.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 2, 'BNBG01', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 3, 'BNBG02', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 4, 'BNBG03', 100, 2.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20007, 5, 'RGAN01', 50, 4.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 1, 'RGAN01', 5, 4.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 2, 'BR03', 5, 11.99);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 3, 'BNBG01', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 4, 'BNBG02', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20008, 5, 'BNBG03', 10, 3.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 1, 'BNBG01', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 2, 'BNBG02', 250, 2.49);
INSERT INTO OrderItems(order_num, order_item, prod_id, quantity, item_price)
VALUES(20009, 3, 'BNBG03', 250, 2.49);

看不清的图片：右击新标签页打开即可。

---

1.SQL基础

2.检索数据

 

#SQL对大小写不敏感
#检索单个列
SELECT prod_name
FROM products;
#检索多个列
SELECT prod_name,prod_id,prod_price
FROM products;
#检索多个列
SELECT *
FROM products;
#检索不同的值
SELECT DISTINCT vend_id
FROM products;
#限制结果,需要好好理解
SELECT prod_name
FROM products
LIMIT 5;
SELECT prod_name
FROM products
LIMIT 4 OFFSET 5;#4是检索的行数，5是从那儿开始
SELECT prod_name
FROM products
LIMIT 5,2;#逗号前，从哪儿开始，逗号后，检索行数

3.排序检索数据

 

#排序检索
SELECT prod_name
FROM products
ORDER BY prod_name;
#多个列
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price,prod_name;
#按照列的相对位置
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY 2,3;
#混合使用
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY 2,vend_id;
#降序
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC;
#多个列降序
SELECT prod_id,prod_price,prod_name
FROM products
ORDER BY prod_price DESC,prod_name DESC;

 

 

4.过滤数据

 

#where子句过滤数据
SELECT prod_name,prod_price
FROM products
WHERE prod_price = 3.49;
SELECT prod_name,prod_price
FROM products
WHERE prod_price > 3.49
ORDER BY prod_price DESC;
#不匹配检查
SELECT vend_id,prod_name
FROM products
WHERE vend_id != 'DLL01';
#范围值
SELECT prod_name,prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;
#空值检查
SELECT cust_name
FROM customers
WHERE cust_email IS NULL;

 

 

 

5.高级过滤

#组合where子句
SELECT prod_id,prod_price,prod_name
FROM products
WHERE vend_id = "DLL01" AND prod_price <= 4
ORDER BY prod_name;

SELECT prod_name,prod_price
FROM products
WHERE vend_id = "DLL01" OR vend_id = "BRS01";
#优先级
SELECT prod_name,prod_price
FROM products
WHERE vend_id = "DLL01" OR vend_id = "BRS01" AND prod_price >=10;

SELECT prod_name,prod_price
FROM products
WHERE (vend_id = "DLL01" OR vend_id = "BRS01") AND prod_price >=10;
#in
SELECT prod_name,prod_price
FROM products
WHERE vend_id IN ('DLL01','BRS01')
ORDER BY prod_name;

SELECT prod_name,prod_price
FROM products
WHERE vend_id = 'DLL01' OR vend_id = 'BRS01';

SELECT prod_name,prod_price
FROM products
WHERE vend_id IN ('DLL01','BRS01') AND prod_price >= 10;
#not
SELECT prod_name,prod_price
FROM products
WHERE NOT vend_id ="DLL01"
ORDER BY prod_name;

SELECT prod_name,prod_price
FROM products
WHERE vend_id <> 'DLL01'
ORDER BY prod_name;

SELECT prod_name,prod_price
FROM products
WHERE NOT vend_id IN ('DLL01','BRS01') AND prod_price >= 5;

6.通配符过滤

 

#不区分大小写，但是区分空格
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'Fish%';

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE "%bean bag%";

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE 'F%y';

SELECT cust_email
FROM customers
WHERE cust_email LIKE '%@fun4all.com';
#%可以匹配空格，但是不能匹配NULL
#x下划线
SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '__ inch teddy bear';

SELECT prod_id,prod_name
FROM products
WHERE prod_name LIKE '% inch teddy bear';

 

 

 

7.创建计算字段

8.函数特性

9.函数

 

10.汇总数据

11.分组数据

 

12.子查询

这里说白了就是select语句嵌套，如select * from （select cust_name from customers）

13.联结 

笛卡尔积

 所谓的表的联结，其实就是将几个表的信息综合到一起，进而成为一个表。而这种综合其实是通过这几个表相同的主键，或者列，来进行选择。

14.高级联结

 

如果3个以上的表，那怎样联结？

 

1. 3个

 

use test;
select cust_name,cust_contact
from (customers 
inner join orders
on customers.cust_id = orders.cust_id)
inner join orderitems 
on orderitems.order_num = orders.order_num;

 

语法可以概括为：

 

FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号

 

2. 同理，4个表可以概括为：

 

FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号) INNER JOIN 表4 ON Member.字段号=表4.字段号

 

15.组合查询

关于规则中列顺序，前后必须一致。否则会出现如下情况：

use test;
select cust_name ,cust_email,cust_contact
from customers
where cust_state in ('IL','IN','MI')
union all
select cust_name,cust_contact,cust_email
from customers
where cust_name = 'Fun4All'

返回结果为：

cust_name	cust_email	cust_contact
Village Toys	sales@villagetoys.com	John Smith
Fun4All	jjones@fun4all.com	Jim Jones
The Toy Store		Kim Howard
Fun4All	Jim Jones	jjones@fun4all.com
Fun4All	Denise L. Stephens	dstephens@fun4all.com

列的顺序一致，才会返回正确结果

use test;
select cust_name ,cust_email,cust_contact
from customers
where cust_state in ('IL','IN','MI')
union all
select cust_name,cust_email,cust_contact
from customers
where cust_name = 'Fun4All'

结果：

cust_name	cust_email	cust_contact
Village Toys	sales@villagetoys.com	John Smith
Fun4All	jjones@fun4all.com	Jim Jones
The Toy Store		Kim Howard
Fun4All	jjones@fun4all.com	Jim Jones
Fun4All	dstephens@fun4all.com	Denise L. Stephens

16.插入数据

INSERT SELECT:

它可以将SELCET语句结果插入表中，在某种意义上可以完成表的复制。

如：

USE test;#使用数据库test
CREATE TABLE CustNew
(
  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 
);#创新新表
INSERT INTO CustNew
(
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
)
SELECT 
cust_id,
cust_name,
cust_address,
cust_city,
cust_state,
cust_zip,
cust_country,
cust_contact,
cust_email
FROM customers;#将customers表的数据插入新表alter
SELECT * FROM CustNew;

返回结果如下：

 

 而如果反过来用，大概就是：

insert into
customers
（列1，列2，......）
 select 
列1，列2，.....
 from custnew

即可完成将表custnew中的数据全部插入customers，这也完成了一条insert插入多条数据。

通常，一条INSERT 语句，只能插入一行数据，要插入多行，就必须执行多个INSERT，但是， INSERT SELECT 是个例外。

以上方法的复制，实际上是重新在数据库中创建了一个新表。

SELECT INTO：

这种方法，可以在SQL语句运行中创建一个表，并将一个表复制到这个全新的表。

我们可以在实验新的SQL语句前，用其进行复制，这样就不影响到实际数据。

如：

USE test;#使用数据库
CREATE TABLE Cucopy as
SELECT * FROM customers;#复制
SELECT * FROM Cucopy;

 返回结果，与上表一致。

 17.更新和删除

18.创建和操纵表

这里中点说一下创建表时，指定默认值的问题。一般来说，创建表时，我们需要指定列的数据类型、是否NULL列。关于NULL值具体说明请看下图。

默认值要是用的好，可以省却我们很多时间。

因其经常用于指定默认日期，这里以日期为例。

首先，在MySQL中内建的日期函数：

函数	描述
NOW()	返回当前的日期和时间
CURDATE()	返回当前的日期
CURTIME()	返回当前的时间
DATE()	提取日期或日期/时间表达式的日期部分
EXTRACT()	返回日期/时间按的单独部分
DATE_ADD()	给日期添加指定的时间间隔
DATE_SUB()	从日期减去指定的时间间隔
DATEDIFF()	返回两个日期之间的天数
DATE_FORMAT()	用不同的格式显示日期/时间

创建数据库t2,插入表。如下：

CREATE DATABASE t2;
USE t2;
CREATE TABLE test
(id int(5) NOT NULL,
noedate timestamp NOT NULL DEFAULT current_timestamp()
);

调用如下：

USE t2;
INSERT INTO test(id)
values(1);
SELET * FROM test;

返回结果：

id	nowdate
1	2017-05-08 22:09:45

以下SQL不合法：

--time_d time NOT NULL DEFAULT CURTIME(), 
--date_e date NOT NULL DEFAULT CURDATE(), 
--datetime_f datetime NOT NULL DEFAULT NOW(), 

DATETIME类型：NOW()函数以'YYYY-MM-DD HH:MM:SS'返回当前的日期时间，可以直接存到DATETIME字段中。不支持使用系统默认值。 

DATE类型：CURDATE()以'YYYY-MM-DD'的格式返回今天的日期，可以直接存到DATE字段中。不支持使用系统默认值。 

TIME类型：CURTIME()以'HH:MM:SS'的格式返回当前的时间，可以直接存到TIME字段中。不支持使用系统默认值。 

常见错误：

CREATE TABLE dnt_forums
( 
aa int NOT NULL DEFAULT (''), 
bb date NOT NULL DEFAULT (getdate()), 
cc char(50) NOT NULL DEFAULT (null) 
）；

aa 是 int 类型，默认值也得是整型，并且default后边不要（）括号 。
bb date类型不支持使用系统默认值，改成timestamp，能过now()取系统时间 。
cc 已经不允许为空（not null）所以不能默认为 null ，可以改成空字符串 。

修改：

CREATE TABLE dnt_forums( 
aa int NOT NULL DEFAULT 2, 
bb timestamp NOT NULL DEFAULT now(), 
cc char(50) NOT NULL DEFAULT '' 
); 

这个日期问题，依然有很多不太明白的地方，以后学习过程碰到继续解决。

主要参考：

脚本之家、博客

重命名表：

RENAME TABLE  cucopy1 TO hi;

 删除表：

DROP TABLE hi;