SQL 基础语句整理

SQL教程

 

SELECT 语句

SELECT * FROM 表名称

DISTINCT 语句

SELECT DISTINCT 列名称 FROM 表名称

SELECT LastName,FirstName FROM Persons

关键词 DISTINCT 用于返回唯一不同的值。

WHERE 子句

SELECT 列名称 FROM 表名称 WHERE 列 运算符 值

SELECT * FROM Persons WHERE City='Beijing'

AND 和 OR 运算符

SELECT * FROM Persons WHERE FirstName='Thomas' AND LastName='Carter'

SELECT * FROM Persons WHERE firstname='Thomas' OR lastname='Carter'

ORDER BY 语句用于对结果集进行排序。

SELECT Company, OrderNumber FROM Orders ORDER BY Company

SELECT Company, OrderNumber FROM Orders ORDER BY Company, OrderNumber

SELECT Company, OrderNumber FROM Orders ORDER BY Company DESC

ORDER BY Company DESC, OrderNumber ASC

INSERT INTO 语句

INSERT INTO Persons VALUES ('Gates', 'Bill', 'Xuanwumen 10', 'Beijing')

INSERT INTO Persons (LastName, Address) VALUES ('Wilson', 'Champs-Elysees')

Update 语句

UPDATE Person SET FirstName = 'Fred' WHERE LastName = 'Wilson'

UPDATE Person SET Address = 'Zhongshan 23', City = 'Nanjing' WHERE LastName = 'Wilson'

DELETE 语句

DELETE FROM Person WHERE LastName = 'Wilson' 删除某行

DELETE * FROM table_name 删除所有行 表结构还在

TOP 子句

TOP 子句用于规定要返回的记录的数目。并非所有的数据库系统都支持 TOP 子句。

SQL Server

SELECT TOP number|percent column_name(s) FROM table_name

MySQL 和 Oracle 中的 SQL SELECT TOP 是等价的

MySQL 语法

SELECT * FROM Persons LIMIT 5

Oracle 语法

SELECT * FROM Persons WHERE ROWNUM <= 5

 

LIKE 操作符

SELECT * FROM Persons WHERE City LIKE 'N%'

通过使用 NOT 关键字，我们可以从 "Persons" 表中选取居住在不包含 "lon" 的城市里的人

SELECT * FROM Persons WHERE City NOT LIKE '%lon%'

SQL 通配符

% 替代一个或多个字符

_ 仅替代一个字符

[charlist] 字符列中的任何单一字符

[^charlist]或者[!charlist] 不在字符列中的任何单一字符

SELECT * FROM Persons WHERE City LIKE '%lond%'

SELECT * FROM Persons WHERE LastName LIKE 'C_r_er'

SELECT * FROM Persons WHERE City LIKE '[ALN]%' 以 "A" 或 "L" 或 "N" 开头的

LIKE '[!ALN]%' 不以 "A" 或 "L" 或 "N" 开头的

IN 操作符

SELECT * FROM Persons WHERE LastName IN ('Adams','Carter')

BETWEEN 操作符

SELECT * FROM Persons WHERE LastName BETWEEN 'Adams' AND 'Carter'

以字母顺序显示介于 "Adams"（包括）和 "Carter"（不包括）之间的人

（重要事项：不同的数据库对 BETWEEN...AND 操作符的处理方式是有差异的 包不包括两头不一致）

SELECT * FROM Persons WHERE LastName NOT BETWEEN 'Adams' AND 'Carter'

Alias 别名

SELECT LastName AS Family, FirstName AS Name FROM Persons

Join 

SELECT * FROM Persons, Orders WHERE Persons.Id_P = Orders.Id_P

SELECT * FROM Persons INNER JOIN Orders ON Persons.Id_P = Orders.Id_P

ORDER BY Persons.LastName

• JOIN: 如果表中有至少一个匹配，则返回行 INNER JOIN 与 JOIN 是相同的。
• LEFT JOIN: 即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN: 即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN: 只要其中一个表中存在匹配，就返回行

 UNION 操作符

UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

SELECT E_Name FROM Employees_China UNION SELECT E_Name FROM Employees_USA

注释：默认地，UNION 操作符选取不同的值。如果允许重复的值，请使用 UNION ALL。

SELECT INTO 语句 用于创建表的备份复件

SELECT INTO 语句从一个表中选取数据，然后把数据插入另一个表中。

SELECT INTO 语句常用于创建表的备份复件或者用于对记录进行存档。

SELECT * INTO new_table_name [IN externaldatabase] FROM old_tablename

SELECT Persons.LastName,Orders.OrderNo INTO Persons_Order_Backup FROM Persons INNER JOIN Orders ON Persons.Id_P=Orders.Id_P

CREATE DATABASE 语句

CREATE DATABASE 用于创建数据库。

CREATE DATABASE my_db

CREATE TABLE 语句

CREATE TABLE Persons ( Id_P int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )

约束

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

NOT NULL 约束

NOT NULL 约束强制列不接受 NULL 值。

NOT NULL 约束强制字段始终包含值。

这意味着，如果不向字段添加值，就无法插入新记录或者更新记录。

Id_P int NOT NULL,

 

UNIQUE 约束

唯一 标识数据库表中的每条记录

每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束。

MySQL: UNIQUE (Id_P)

QL Server / Oracle / MS Access: Id_P int NOT NULL UNIQUE,

CONSTRAINT uc_PersonID UNIQUE (Id_P,LastName)

当表已被创建时，如需在 "Id_P" 列创建 UNIQUE 约束，请使用下列 SQL：

ALTER TABLE Persons ADD UNIQUE (Id_P)

PRIMARY KEY 约束

主键必须包含唯一的值。

主键列不能包含 NULL 值。

PRIMARY KEY (Id_P)

CONSTRAINT pk_PersonID PRIMARY KEY (Id_P,LastName)

ALTER TABLE Persons ADD PRIMARY KEY (Id_P)

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

ALTER TABLE Persons DROP PRIMARY KEY

FOREIGN KEY 约束

FOREIGN KEY 约束用于预防破坏表之间连接的动作。

FOREIGN KEY 约束也能防止非法数据插入外键列，因为它必须是它指向的那个表中的值之一。

FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

CONSTRAINT fk_PerOrders FOREIGN KEY (Id_P) REFERENCES Persons(Id_P)

CHECK 约束

CHECK 约束用于限制列中的值的范围。

CHECK (Id_P>0)

DEFAULT 约束

DEFAULT 约束用于向列中插入默认值。

如果没有规定其他的值，那么会将默认值添加到所有的新记录。

 

CREATE INDEX 索引

CREATE INDEX index_name ON table_name (column_name)

CREATE UNIQUE INDEX index_name ON table_name (column_name) 唯一索引

CREATE INDEX PersonIndex ON Person (LastName)

PersonIndex 索引名 Person 表名 LastName字段名

CREATE INDEX PersonIndex ON Person (LastName DESC) 降序索引

CREATE INDEX PersonIndex ON Person (LastName, FirstName)

DROP 语句，可以轻松地删除索引、表和数据库

DROP TABLE 表名称 表的结构、属性以及索引也会被删除

TRUNCATE TABLE 表名称 仅仅删除表格中的数据

DROP DATABASE 数据库名称

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 修改列

AUTO INCREMENT 字段

P_Id int NOT NULL AUTO_INCREMENT, 建表时

SQL CREATE VIEW 语句

CREATE VIEW [Current Product List] AS SELECT ProductID,ProductName FROM Products WHERE Discontinued=No 创建视图

 Date 函数

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

NULL 值

注释：无法比较 NULL 和 0；它们是不等价的。

NULL 值的处理方式与其他值不同。

NULL 用作未知的或不适用的值的占位符。

IS NULL IS NOT NULL

ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数

IFNULL(UnitsOnOrder,0) 空值就替换

 

SQL 函数

AVG()  平均值

SELECT AVG(OrderPrice) AS OrderAverage FROM Orders

COUNT() 函数返回匹配指定条件的行数

SELECT COUNT(*) FROM table_name

COUNT(*) 函数返回表中的记录数：

FIRST() 函数 返回指定的字段中第一个记录的值

SELECT FIRST(column_name) FROM table_name

LAST() 函数 返回指定的字段中最后一个记录的值。

SELECT LAST(OrderPrice) AS LastOrderPrice FROM Orders

MAX() 函数 返回一列中的最大值。NULL 值不包括在计算中。

SELECT MAX(OrderPrice) AS LargestOrderPrice FROM Orders

MIN() 函数 返回一列中的最小值。NULL 值不包括在计算中。

SELECT MIN(OrderPrice) AS SmallestOrderPrice FROM Orders

SUM() 函数 返回数值列的总数（总额）

SELECT SUM(OrderPrice) AS OrderTotal FROM Orders

GROUP BY 语句

用于结合合计函数，根据一个或多个列对结果集进行分组。

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

HAVING 子句

在 SQL 中增加 HAVING 子句原因是，WHERE 关键字无法与合计函数一起使用。

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

HAVING SUM(OrderPrice)<2000

UCASE() 函数

把字段的值转换为大写。

SELECT UCASE(column_name) FROM table_name

LCASE() 函数

LCASE 函数把字段的值转换为小写。

SELECT LCASE(column_name) FROM table_name

MID() 函数

MID 函数用于从文本字段中提取字符。

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

LEN() 函数

LEN 函数返回文本字段中值的长度。

SELECT LEN(column_name) FROM table_name

ROUND() 函数

ROUND 函数用于把数值字段舍入为指定的小数位数。

SELECT ROUND(column_name,decimals) FROM table_name

NOW() 函数

NOW 函数返回当前的日期和时间。

Sql Server 数据库，请使用 getdate() 函数来获得当前的日期时间。

FORMAT() 函数

FORMAT 函数用于对字段的显示进行格式化。

SELECT ProductName, UnitPrice, FORMAT(Now(),'YYYY-MM-DD') as PerDate FROM Products

CONVERT()函数 CAST()函数

语法：用来获取一个类型的值，并转成另一个类型的值

CAST(value as type); CONVERT(value, type);

select sid,CONVERT(AVG(score),decimal(18,2)) '平均成绩' from sc GROUP BY Sid

select sid,CAST(AVG(score) as decimal(18,2)) '平均成绩' from sc GROUP BY Sid

 

decimal

decimal(18,4)总长18位，包括1位小数点和4为小数，也就是说18-1-4=13整数位只有13位

decimal(19,1)总长19位，17位整数，1位小数

 

case when end 用法

CREATE TABLE `lee` (

`id` int(10) NOT NULL AUTO_INCREMENT, 

`name` char(20) DEFAULT NULL, 

`birthday` datetime DEFAULT NULL, 

PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into lee(name,birthday) values ('sam','1990-01-01');

insert into lee(name,birthday) values ('lee','1980-01-01');

insert into lee(name,birthday) values ('john','1985-01-01');

 

第一种： 判断大于的

SELECT name,

CASE

WHEN birthday < '1981' THEN 'old' 如果birthday小于1981显示old

WHEN birthday > '1988' THEN 'yong' 如果birthday大于1981显示old

ELSE 'ok' 否则显示ok

END YORN

FROM lee

（YORN 查出后的列的名称）

 

第二种用法： 只能判断等于

SELECT NAME,

CASE name

WHEN 'sam' THEN 'yong' name=sam 显示yong

WHEN 'lee' THEN 'handsome'

ELSE 'good'

END as oldname

FROM lee

 

复合查询

select name, birthday,

case 

when birthday > '1983' then 'yong'

when name='lee' then 'handsome'

else 'just so so' end

from lee;

 

create table penalties

(

paymentno INTEGER not NULL,

payment_date DATE not null,

amount DECIMAL(7,2) not null,

primary key(paymentno)

)

insert into penalties values(1,'2008-01-01',3.45);

insert into penalties values(2,'2009-01-01',50.45);

insert into penalties values(3,'2008-07-01',80.45);

第一题：对罚款登记分为三类，第一类low,包括大于0小于等于40的罚款，第二类moderate大于40到80之间的罚款，第三类high包含所有大于80的罚款

select payment_date, amount,

case 

when amount >= 0 AND amount < 40 then 'low'

when amount >=40 AND amount < 80 then 'moderate'

when amount >=80 then 'high' 

else 'null' END

FROM penalties

 

第二题:统计出属于low的罚款编号

select * from 

( select paymentno, amount,

case 

when amount >= 0 AND amount < 40 then 'low'

when amount >=40 AND amount < 80 then 'moderate'

when amount >=80 then 'high' 

else 'incorrect' end lvl

from penalties) as p

where p.lvl = 'low'