为了复习SQL,再次把SQL必知必会上的SQL敲了一遍,因此就把博客园写的博客搬过来,然后把以前没写进去的代码,这次全敲一遍。
SQL的学习之路,主要参考资料是:
- SQL必知必会
- W3school的SQL简介
操作使用的数据库为访问MySQL的的的的的及。
数据采用的是SQL必知必会中的数据(最下方可以下载数据)其有5个表,表的关系如下:
#这里补充下时,MySQL的的的创建该数据库(2017年8月24日),复制到的MySQL的的的创建执行创建即可(数据来源于“SQL必知必会”);
- ----------------------
- 创建客户表
- ----------------------
创建表客户
(
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
);
- -----------------------
- 创建OrderItems表
- -----------------------
CREATE TABLE OrderItems
(
order_num int NOT NULL,
order_item int NOT NULL,
prod_id char(10)NOT NULL,
数量int NOT NULL,
item_price十进制(8,2)NOT NULL
);
- -------------------
- 创建订单表
- -------------------
创建表订单
(
order_num int NOT NULL,
order_date datetime NOT NULL,
cust_id char(10)NOT NULL
);
- ---------------------
- 创建产品表
- ---------------------
创建表产品
(
prod_id char(10)NOT NULL,
vend_id char(10)NOT NULL,
prod_name char(255)NOT NULL,
prod_price十进制(8,2)NOT NULL,
prod_desc文本为空
);
- --------------------
- 创建供应商表
- --------------------
创建表供应商
(
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
);
- -------------------
- 定义主键
- -------------------
ALTER TABLE客户添加主键(cust_id);
ALTER TABLE OrderItems ADD PRIMARY KEY(order_num,order_item);
ALTER TABLE Orders ADD PRIMARY KEY(order_num);
ALTER TABLE产品ADD PRIMARY KEY(prod_id);
ALTER TABLE供应商ADD PRIMARY KEY(vend_id);
- -------------------
- 定义外键
- -------------------
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)参考产品(prod_id);
ALTER TABLE订单ADD CONSTRAINT FK_Orders_Customers FOREIGN KEY(cust_id)参考客户(cust_id);
ALTER TABLE产品ADD CONSTRAINT FK_Products_Vendors FOREIGN KEY(vend_id)参考供应商(vend_id);
#插入数据
------------------------
- 填充客户表
------------------------
INSERT INTO客户(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客户(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客户(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客户(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客户(cust_id,cust_name,cust_address,cust_city,cust_state,cust_zip,cust_country,cust_contact)
VALUES('1000000005'“玩具店”,“4545第53街”,“芝加哥”,“IL”,“54545”,“美国”,“金霍华德”);
- ----------------------
- 填充供应商表
- ----------------------
INSERT INTO Vendors(vend_id,vend_name,vend_address,vend_city,vend_state,vend_zip,vend_country)
价值('BRS01','熊R'''123大街'熊城''''44444''美国');
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)
价值('JTS01','Jouets et''1 Rue Amusement','Paris',NULL''45678','France');
- -----------------------
- 填充产品表
- -----------------------
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('BR01','BRS01','8寸泰迪熊',5.99''8寸泰迪熊,配上帽子和夹克');
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('BR02','BRS01','12寸泰迪熊',8.99''12寸玩具熊,配上帽子和夹克');
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('BR03','BRS01','18寸泰迪熊',11.99,'18寸泰迪熊,配上帽子和夹克');
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('BNBG01','DLL01','鱼豆包玩具',3.49'鱼豆袋玩具,配有豆袋蠕虫饲料)
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('BNBG02','DLL01','豆豆包玩具',3.49''鸟豆袋玩具,不包括鸡蛋');
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('BNBG03','DLL01','兔子豆袋玩具',3.49'兔子豆包玩具,随附豆袋胡萝卜');
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('RGAN01','DLL01','Raggedy Ann',4.99''18英寸Raggedy Ann doll');
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('RYL01','FNG01','国王娃娃',9.49'皇家服装和皇冠''12寸王妃');
INSERT INTO产品(prod_id,vend_id,prod_name,prod_price,prod_desc)
VALUES('RYL02','FNG01','Queen doll',9.49,'皇家服装和皇冠''12寸皇后娃娃);
- ---------------------
- 填充订单表
- ---------------------
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');
-------------------------
- 填充OrderItems表
-------------------------
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)
VALES(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产品;
#检索多个列
SELECT prod_name,prod_id,prod_price
FROM产品;
#检索多个列
选择*
FROM产品;
#检索不同的值
SELECT DISTINCT vend_id
FROM产品;
#限制结果,需要好好理解
SELECT prod_name
从产品
LIMIT 5;
SELECT prod_name
从产品
LIMIT 4 OFFSET 5;#4是检索的行数,5是从那儿开始
SELECT prod_name
从产品
LIMIT 5,2;#逗号前,从哪儿开始,逗号后,检索行数
3.排序检索数据
#排序检索
SELECT prod_name
从产品
ORDER BY prod_name;
#多个列
SELECT prod_id,prod_price,prod_name
从产品
ORDER BY prod_price,prod_name;
#按照列的相对位置
SELECT prod_id,prod_price,prod_name
从产品
ORDER BY 2,3;
#混合使用
SELECT prod_id,prod_price,prod_name
从产品
ORDER BY 2,vend_id;
#降序
SELECT prod_id,prod_price,prod_name
从产品
ORDER BY prod_price DESC;
#多个列降序
SELECT prod_id,prod_price,prod_name
从产品
ORDER BY prod_price DESC,prod_name DESC;
4.过滤数据
5.高级过滤
6.通配符过滤
7.创建计算字段
8.函数特性
9.函数
10.汇总数据
11.分组数据
以上测试均在访问数据库中进行,以下于的MySQL的的的的中进行,通过工作台(版本6.3)操作。
12.子查询
这里说白了就是选择语句嵌套,如select * from(从客户中选择cust_name)
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.字段号
同理,4个表可以概括为:
FROM((表1 INNER JOIN表2 ON表1.字段号=表2.字段号)INNER JOIN表3 ON表1.字段号=表3.字段号INNER JOIN表4 ON成员字段号=表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'
返回结果为:
列的顺序一致,才会返回正确结果
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'
结果:
16.插入数据
插入选择:
它可以将SELCET语句结果插入表中,在某种意义上可以完成表的复制。
如:
1 USE test;#使用数据库测试
2 CREATE TABLE CustNew
3 (
4 cust_id char(10) NOT NULL ,
5 cust_name char(50) NOT NULL ,
6 cust_address char(50) NULL ,
7 cust_city char(50) NULL ,
8 cust_state 烧焦(5) NULL ,
9 cust_zip char(10) NULL ,
10 cust_country char(50) NULL ,
11 cust_contact char(50) NULL ,
12 cust_email char(255)NULL
13 );#创新新表
14 INSERT INTO CustNew
15 (
16 cust_id,
17 cust_name,
18 cust_address,
19 cust客户
20, cust_state,
21 cust_zip,
22 cust_country,
23 cust_contact,
24 cust_email
25 )
26 SELECT
27 cust_id,
28 cust_name,
29 cust_address,
30 cust_city,
31 cust_state,
32 cust_zip,
33 cust_country,
34 cust_contact ,
35 CUST_EMAIL
36 来自客户; #将客户表的数据插入新表
更改37 SELECT * FROM CustNew;
返回结果如下:
而如果反过来用,大概就是:
1 insert into
2 customers
3 (列1,列2,......)
4 select
5 列1,列2,.....
6 from custnew
即可完成将表custnew中的数据全部插入客户,这也完成了一条插入插入多条数据。
通常,一条INSERT语句,只能插入一行数据,要插入多行,就必须执行多个INSERT,但是,INSERT SELECT是个例外。
以上方法的复制,实际上是重新在数据库中创建了一个新表。
选择INTO:
这种方法,可以在SQL语句运行中创建一个表,并将一个表复制到这个全新的表。
我们可以在实验新的SQL语句前,用其进行复制,这样就不影响到实际数据。
如:
USE test;#使用数据库
CREATE TABLE Cucopy 为
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;
返回结果:
idnowdate
12017-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(),
DATE类型:NOW()函数以'YYYY-MM-DD HH:MM:SS'返回当前的日期时间,可以直接存在DATETIME字段中。不支持使用系统默认值
DATE类型:CURDATE()以'YYYY -MM -DD '的格式返回今天的日期,可以直接存到DATE字段中不支持使用系统默认值。
。时间类型:CURTIME()以' HH:MM:SS'的格式返回当前的时间,可以直接存到时间字段中。不支持使用系统默认值。
常见错误:
CREATE TABLE dnt_forums
(
aa int NOT NULL DEFAULT (''),
bb date NOT NULL DEFAULT (getdate()),
cc char(50) NOT NULL DEFAULT (null)
);
AA是INT类型,默认值也得是整型,并且默认后边不要()括号的.bb
日期类型不支持使用系统默认值,改成时间戳,能过现在()取系统时间
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;
18.推荐练习
SQLZOO
数据库