数据库基础（四）－－结构化查询语言

SQL可以分为两类：数据操作语言（Data Manipulation Language，DML）和数据定义语言（Data Definition Language，DDL）。

DML由查询和更新命令组成：

• SELECT：从数据库中取出数据。
• UPDATE：更新数据库的数据。
• DELETE：从数据库中删除数据。
• INSERT INTO：向数据库插入新数据。

DDL创建和删除数据库、创建和删除表、定义索引（关键字）、指定表之间的联系、定义表之间的约束。SQL里最重要的DDL的语句有：

• CREATE DATABASE：创建一个新的数据库
• ALTER DATABASE：修改一个数据库。
• CREATE TABLE ：创建一张新的表。
• ALTER TABLE ：修改一张表。
• DROP TABLE ：删除一张表。
• CREATE INDEX ：创建一个索引。
• DROP INDEX ：删除一个索引。

用SELECT进行查询时，可以会有重复的记录。使用DISTINCT语句可以消除重复数据。比如：select distinct name from persons。使用WHERE可以指定查询的目标，比如：select * from persons where age >= 18。

WHERE 子句里允许的操作符有：

Operator	Description
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
BETWEEN	Between an inclusive range
LIKE	Search for a pattern
IN	To specify multiple possible values for a column

Note: In some versions of SQL the <> operator may be written as !=

IN的语法是

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...)

BETWEEN的语法是：

SELECT column_name(s)
FROM table_name
WHERE column_name
BETWEEN value1 AND value2

在对字符串比较时，需要用单引号包围字符串常量（多数据数据库系统也接受双引号），数值不应该使用绰号。

LIKE比较时可以使用的通配符有：

Wildcard	Description
%	A substitute for zero or more characters
_	A substitute for exactly one character
[charlist]	Any single character in charlist
[^charlist] or [!charlist]	Any single character not in charlist

在进行条件判断时，可以使用AND和OR来连接逻辑表达式。

可以使用ORDER BY对查询的结果进行排序，它的语法为

SELECT column_name(s)

FROM table_name

ORDER BY column_name(s) ASC|DESC

在mysql里，可以使用LIMIT子句来定义查询结果的最大数量。 比如：select * from persons order by age limit 1。会看到年纪最小的人。在SQL Server里等价的语句是

SELECT TOP number|percent column_name(s)
FROM table_name

注意limit和top都不是SQL标准。

INSERT用来插入数据，语法为：

INSERT INTO table_name
VALUES (value1, value2, value3,...)

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3,...)
VALUES (value1, value2, value3,...)

UPDATE更新数据，语法为：

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

DELETE删除表示的行，语法为：

DELETE FROM table_name
WHERE some_column=some_value

AS可以为表起别名：

SELECT column_name(s)
FROM table_name
AS alias_name

SQL里的连接

• JOIN：自然连接；
  
• LEFT JOIN：左外连接；
• RIGHT JOIN：右外连接；
• FULL JOIN：全外连接。
  

INNER JOIN 语法

SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

LEFT JOIN 语法

SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

RIGHT JOIN 语法

SELECT column_name(s)
FROM table_name1
RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

FULL JOIN 语法

SELECT column_name(s)
FROM table_name1
FULL JOIN table_name2
ON table_name1.column_name=table_name2.column_name

UNION 求并集，语法为：

SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

UNION ALL保留重复的记录：

SELECT column_name(s) FROM table_name1
UNION ALL
SELECT column_name(s) FROM table_name2

SELECT INTO 可以把查询结果写入一张表里

We can select all columns into the new table:

SELECT *
INTO new_table_name [IN externaldatabase]
FROM old_tablename

Or we can select only the columns we want into the new table:

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

CREATE DATABASE 语法

CREATE DATABASE database_name

CREATE TABLE 语法

CREATE TABLE table_name
(
column_name1 data_type,
column_name2 data_type,
column_name3 data_type,
....
)

SQL约束

约束限定可以插入表的数据的类型。它可以在表创建（CREATE TABLE）时指定，也可以在表创建手（用ALTER TABLE）指定。常用的约束有：

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

NOT NULL约束强制某一列不接受NULL值。比如:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

UNIQUE约束保证某列没有重复记录。在mysql中可以在创建表时有两种方法指定：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

或：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

注意第二种方式可以同时指定多列，只有在这些列上都相等的记录才视为重复。

在表创建后加入UNIQUE约束的方法是：

ALTER TABLE Persons
ADD UNIQUE (P_Id)

或

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

注意：如果该列中已经有重复的记录，那么加入约束会失败

删除UNIQUE约束：

ALTER TABLE Persons
DROP INDEX uc_PersonID

创建PRIMARY KEY约束：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

修改表来加入PRIMARY KEY约束：

ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
或

ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)

删除PRIMARY KEY约束：

ALTER TABLE Persons
DROP PRIMARY KEY

创建外键 FOREIGN KEY:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

修改表以创建FOREIGN KEY ：

ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

或
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

删除FOREIGN KEY约束：

ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders

创建 CHECK约束：

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)

或

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)

修改表以创建CHECK约束：

ALTER TABLE Persons
ADD CHECK (P_Id>0)

或

ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

删除CHECK约束：

ALTER TABLE Persons
DROP CONSTRAINT chk_Person

或

ALTER TABLE Persons
DROP CHECK chk_Person

创建DEFAULT约束：

My SQL / SQL Server / Oracle / MS Access:

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

修改表以创建DEFAULT约束:

ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'

或

ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'

删除DEFAULT约束：

ALTER TABLE Persons
ALTER City DROP DEFAULT

创建AUTO INCREMENT约束，它只能作用在主键上：

CREATE TABLE Persons
(
P_Id int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

我们可以设置自增的起始值：

ALTER TABLE Persons AUTO_INCREMENT=100

Indexes

索引用来提高数据查找的效率，用户看不到索引的存在。注意：使用索引会使更新表的速度变慢，因为数据更新的同时还要更新索引。

在表上创建一个允许重复值的索引：

CREATE INDEX index_name
ON table_name (column_name)

在表上创建唯一的索引：

CREATE UNIQUE INDEX index_name
ON table_name (column_name)

删除INDEX：

ALTER TABLE table_name DROP INDEX index_name

一个表上的约束可以通过SHOW INDEX FROM tbl_name来查看。mysql上可以通过修改一个列来增加约束：

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;但是要删除约束只能使用前面所述的drop ...的方式。

DROP TABLE 删除一张表：

DROP TABLE table_name

DROP DATABASE 删除一个数据库：

DROP DATABASE database_name

TRUNCATE TABLE可以删除一张表的所有数据而不删除该表：

TRUNCATE TABLE table_name

ALTER TABLE 可以修改一张表:

增加一列：
ALTER TABLE table_name
ADD column_name datatype

删除一列：

ALTER TABLE table_name
DROP COLUMN column_name

修改一列：

ALTER TABLE table_name ALTER COLUMN column_name datatype
ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;

视图是基于一个SQL语句的结果集的一张虚拟表。视图的优点有：

提供了逻辑数据独立性。当数据的逻辑结构发生改变时，原有的应用程序不用修改。

简化了用户观点。用户只需用到数据库中的一部分，视图适应了用户需要。

数据的安全保护功能。针对不同用户定义不同视图。

创建视图：

CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

更新视图：

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition

删除视图：

DROP VIEW view_name

MySQL的日期函数：

Function	Description
	Returns the current date and time
CURDATE()	Returns the current date
CURTIME()	Returns the current time
	Extracts the date part of a date or date/time expression
	Returns a single part of a date/time
	Adds a specified time interval to a date
	Subtracts a specified time interval from a date
	Returns the number of days between two dates
	Displays date/time data in different formats

MySQL日期数据类型

• DATE - format YYYY-MM-DD
• DATETIME - format: YYYY-MM-DD HH:MM:SS
• TIMESTAMP - format: YYYY-MM-DD HH:MM:SS
• YEAR - format YYYY or YY

IS NULL 操作符判断空值：

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

IS NOT NULL 操作符判断非空值：

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL

MySQL没有ISNULL函数，所以它使用IFNULL：

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

或COALESCE()函数：

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

MySQL数据类型

MySQL有三种主要类型：text, number和Date/Time。

Text types:

Data type	Description
CHAR(size)	Holds a fixed length string (can contain letters, numbers, and special characters). The fixed size is specified in parenthesis. Can store up to 255 characters
VARCHAR(size)	Holds a variable length string (can contain letters, numbers, and special characters). The maximum size is specified in parenthesis. Can store up to 255 characters.Note: If you put a greater value than 255 it will be converted to a TEXT type
TINYTEXT	Holds a string with a maximum length of 255 characters
TEXT	Holds a string with a maximum length of 65,535 characters
BLOB	For BLOBs (Binary Large OBjects). Holds up to 65,535 bytes of data
MEDIUMTEXT	Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB	For BLOBs (Binary Large OBjects). Holds up to 16,777,215 bytes of data
LONGTEXT	Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB	For BLOBs (Binary Large OBjects). Holds up to 4,294,967,295 bytes of data
ENUM(x,y,z,etc.)	Let you enter a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted. Note: The values are sorted in the order you enter them. You enter the possible values in this format:ENUM('X','Y','Z')
SET	Similar to ENUM except that SET may contain up to 64 list items and can store more than one choice

Number types:

Data type	Description
TINYINT(size)	-128 to 127 normal. 0 to 255 UNSIGNED*. The maximum number of digits may be specified in parenthesis
SMALLINT(size)	-32768 to 32767 normal. 0 to 65535 UNSIGNED*. The maximum number of digits may be specified in parenthesis
MEDIUMINT(size)	-8388608 to 8388607 normal. 0 to 16777215 UNSIGNED*. The maximum number of digits may be specified in parenthesis
INT(size)	-2147483648 to 2147483647 normal. 0 to 4294967295 UNSIGNED*. The maximum number of digits may be specified in parenthesis
BIGINT(size)	-9223372036854775808 to 9223372036854775807 normal. 0 to 18446744073709551615 UNSIGNED*. The maximum number of digits may be specified in parenthesis
FLOAT(size,d)	A small number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DOUBLE(size,d)	A large number with a floating decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter
DECIMAL(size,d)	A DOUBLE stored as a string , allowing for a fixed decimal point. The maximum number of digits may be specified in the size parameter. The maximum number of digits to the right of the decimal point is specified in the d parameter

*The integer types have an extra option called UNSIGNED. Normally, the integer goes from an negative to positive value. Adding the UNSIGNED attribute will move that range up so it starts at zero instead of a negative number.

Date types:

Data type	Description
DATE()	A date. Format: YYYY-MM-DD Note: The supported range is from '1000-01-01' to '9999-12-31'
DATETIME()	*A date and time combination. Format: YYYY-MM-DD HH:MM:SS Note: The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'
TIMESTAMP()	*A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD HH:MM:SS Note: The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC
TIME()	A time. Format: HH:MM:SS Note: The supported range is from '-838:59:59' to '838:59:59'
YEAR()	A year in two-digit or four-digit format. Note: Values allowed in four-digit format: 1901 to 2155. Values allowed in two-digit format: 70 to 69, representing years from 1970 to 2069

SQL聚集函数计算某列的各值，返回单一值。

有用的集体函数有：

• AVG() -平均数
• COUNT() -计数
• FIRST() -第一个值
• LAST() -最后一个值
• MAX() -最大值
• MIN() -最小值
• SUM() -求和

这些函数会计算重复值，可以使用DISTINCT来消除重复，比如：select sum(distinct age) from persons。

结果也可以用AS来起别名，比如select sum(age) as MySum from persons。

在MySql里没有first和last，所以

SELECT FIRST(OrderPrice) AS FirstOrderPrice FROM Orders

等价于：

SELECT OrderPrice FROM Orders ORDER BY O_Id LIMIT 1

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

等价于：

SELECT OrderPrice FROM Orders ORDER BY O_Id DESC LIMIT 1

GROUP BY可以根据记录在某列上的值把表的记录分组，然后分别计算各组的聚集函数的值。它也能指定多个列比如：

SELECT Customer,OrderDate,SUM(OrderPrice) FROM Orders
GROUP BY Customer,OrderDate

HAVING可以在查询时使用一个聚集函数来指定条件，它之前必须可以有group by修饰：

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value

Having与WHERE的区别是：

where决定哪些元组被选择参加运算，作用于关系中的元组；

Having决定哪些分组符合要求，作用于分组；

聚集函数的条件关系必须用Having，Where不应该出现聚集函数。

SQL标量函数基于输入值返回单个值。有用的标量函数有：

• UCASE() -转换成大写。
• LCASE() - 转换成小写。
• MID() - 抽取子字符串。
• LEN() - 得到字符串的长度。
• ROUND() - 舍取一个小数。
• NOW() - 返回当前日期和时间。
• FORMAT() - 格式化显示。

SQL UCASE() 语法

SELECT UCASE(column_name) FROM table_name

SQL MID() 语法

SELECT MID(column_name,start[,length]) FROM table_name

SQL ROUND() 语法

SELECT ROUND(column_name,decimals) FROM table_name

SQL FORMAT() 语法

SELECT FORMAT(column_name,format) FROM table_name

Parameter	Description
column_name	Required. The field to be formatted.
format	Required. Specifies the format.

参考：