sql整理

• use RUNOOB; 命令用于选择数据库。
• set names utf8; 命令用于设置使用的字符集。

• SELECT * FROM Websites; 读取数据表的信息
• SQL 对大小写不敏感：SELECT 与 select 是相同的

一些最重要的 SQL 命令

• SELECT - 从数据库中提取数据
• UPDATE - 更新数据库中的数据
• DELETE - 从数据库中删除数据
• INSERT INTO - 向数据库中插入新数据
• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

选取特定列

+----+--------------+---------------------------+-------+---------+
| id | name         | url                       | alexa | country |
+----+--------------+---------------------------+-------+---------+
| 1  | Google       | https://www.google.cm/    | 1     | USA     |
| 2  | 淘宝          | https://www.taobao.com/   | 13    | CN      |
| 3  | 菜鸟教程      | http://www.runoob.com/    | 4689  | CN      |
| 4  | 微博          | http://weibo.com/         | 20    | CN      |
| 5  | Facebook     | https://www.facebook.com/ | 3     | USA     |
+----+--------------+---------------------------+-------+---------+

选取特定列

SELECT name,country FROM Websites;

去掉重复的列

SELECT DISTINCT country FROM Websites;

SELECT * FROM Websites WHERE country='CN';

文本字段 vs. 数值字段

SQL 使用单引号来环绕文本值（大部分数据库系统也接受双引号）。

在上个实例中 'CN' 文本字段使用了单引号。

如果是数值字段，请不要使用引号。

WHERE 子句中的运算符

下面的运算符可以在 WHERE 子句中使用：

运算符	描述
=	等于
<>	不等于。注释：在 SQL 的一些版本中，该操作符可被写成 !=
>	大于
<	小于
>=	大于等于
<=	小于等于
BETWEEN	在某个范围内
LIKE	搜索某种模式
IN	指定针对某个列的多个可能值

3.In

Select * from emp where sal in (5000,3000,1500);

查询 EMP 表 SAL 列中等于 5000，3000，1500 的值。

4.like

Like模糊查询

Select * from emp where ename like 'M%';

查询 EMP 表中 Ename 列中有 M 的值，M 为要查询内容中的模糊信息。

•  % 表示多个字值，_ 下划线表示一个字符；
•  M% : 为能配符，正则表达式，表示的意思为模糊查询信息为 M 开头的。
•  %M% : 表示查询包含M的所有内容。
•  %M_ : 表示查询以M在倒数第二位的所有内容。

排序

SELECT * FROM Websites ORDER BY country , alexa ;

ORDER BY 多列的时候，先按照第一个column name排序，在按照第二个column name排序；如上述教程最后一个例子：

•  1）、先将country值这一列排序，同为CN的排前面，同属USA的排后面；
•  2）、然后在同属CN的这些多行数据中，再根据alexa值的大小排列。
•  3）、ORDER BY 排列时，不写明ASC DESC的时候，默认是ASC。

插入新数据

INSERT INTO table_name (column1,column2,column3,...)

VALUES (value1,value2,value3,...);

INSERT INTO USER(uid,username,address) VALUES (22,'阿飞哇','日本');

修改一些数据

UPDATE Websites SET alexa='5000', country='USA' WHERE name='菜鸟教程';

DELETE FROM WebsitesWHERE name='百度' AND country='CN';

DROP:

DROP test;

删除表test，并释放空间，将test删除的一干二净。

TRUNCATE:

TRUNCATE test;

删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在。

DELETE:

1、删除指定数据

删除表test中年龄等于30的且国家为US的数据

DELETE FROM test WHERE age=30 AND country='US';

2、删除整个表

仅删除表test内的所有内容，保留表的定义，不释放空间。

DELETE FROM test 或者 DELETE FROM test;
DELETE * FROM test 或者 DELETE * FROM test;

SELECT * FROM Websites LIMIT 2;

SELECT * FROM USER ORDER BY uid DESC LIMIT 3;

SELECT TOP 50 PERCENT * FROM Websites;

下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：

实例

SELECT * FROM Websites
WHERE name REGEXP '^[GFs]';

下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站：

实例

SELECT * FROM Websites
WHERE name REGEXP '^[A-H]';

下面的 SQL 语句选取 name 不以 A 到 H 字母开头的网站：

实例

SELECT * FROM Websites
WHERE name REGEXP '^[^A-H]';

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

下面的 SQL 语句选取alexa介于 1 和 20 之间但 country 不为 USA 和 IND 的所有网站：

实例

SELECT * FROM Websites
WHERE (alexa BETWEEN 1 AND 20)
AND NOT country IN ('USA', 'IND');

SELECT * FROM USER WHERE (uid BETWEEN 23 AND 110) AND NOT address IN ('东台','浙江');

SELECT username AS '用户名',address AS '地址' FROM USER;

把后面几个信息合起来了，注意的是第一列后面有逗号，后面也有引号夹逗号

SELECT uid,username,address,content FROM USER INNER JOIN dosome ON user.uid = dosome.id;

SELECT uid, CONCAT(username, ', ', address) AS site_info FROM USER;

SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
INNER JOIN access_log

ON Websites.id=access_log.site_id;

注意点，要多少数据就写清要的哪几列，关键字from inner join on X.=Y.

INNER JOIN 与 JOIN 是相同的。注释：在某些数据库中，LEFT JOIN 称为 LEFT OUTER JOIN。

SELECT country FROM Websites UNION
SELECT country FROM apps

ORDER BY country; 把你需要的列连在一起

SELECT country FROM Websites
UNION ALL
SELECT country FROM apps

ORDER BY country; UNION ALL可以选取重复的值哈哈

SELECT country, name FROM Websites
WHERE country='CN'
UNION ALL
SELECT country, app_name FROM apps
WHERE country='CN'

ORDER BY country;

使用UNION命令时需要注意，只能在最后使用一个ORDER BY命令，是将两个查询结果合在一起之后，再进行排序！绝对不能写两个ORDER BY命令。

另外，在使用ORDER BY排序时，注意两个结果的别名保持一致，使用别名排序很方便。当然也可以使用列数。

CREATE DATABASE my_db;创建数据库

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

);创建表

在 SQL 中，我们有如下约束：

• NOT NULL - 指示某列不能存储 NULL 值。
• UNIQUE - 保证某列的每行必须有唯一的值。
• PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
• FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。
• CHECK - 保证列中的值符合指定的条件。
• DEFAULT - 规定没有给列赋值时的默认值。

创建表，里面内容用括号括起来，且最后一个列后面没符号，且中间用逗号隔开

PRIMARY KEY 约束拥有自动定义的 UNIQUE 约束。

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

CREATE TABLE 时的 SQL UNIQUE 约束

下面的 SQL 在 "Persons" 表创建时在 "P_Id" 列上创建 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)
)

SQL Server / Oracle / MS Access：

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

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

MySQL / 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),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)

---

ALTER TABLE 时的 SQL UNIQUE 约束

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

MySQL / SQL Server / Oracle / MS Access：

ALTER TABLE Persons
ADD UNIQUE (P_Id)

如需命名 UNIQUE 约束，并定义多个列的 UNIQUE 约束，请使用下面的 SQL 语法：

MySQL / SQL Server / Oracle / MS Access：

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

---

撤销 UNIQUE 约束

如需撤销 UNIQUE 约束，请使用下面的 SQL：

MySQL：

ALTER TABLE Persons
DROP INDEX uc_PersonID

SQL Server / Oracle / MS Access：

ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID

--给tb3表添加主键约束，主键名为：pk_id
alter table tb3 add constraint pk_id primary key (tb3_id);

一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY。

CREATE TABLE consumer(
cid INT NOT NULL,
username VARCHAR(255) UNIQUE,
uid INT NOT NULL,
PRIMARY KEY(cid),
FOREIGN KEY(uid) REFERENCES USER(uid)

) 写在最后主键和外键，主键是(),外键是(),参考的哪个表()什么参数

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) DEFAULT 'Sandnes'

)

平均数

SELECT AVG(count) AS CountAverage FROM access_log;

SELECT site_id, count FROM access_log WHERE count > (SELECT AVG(count) FROM access_log);

统计数目

SELECT COUNT(*) AS nums FROM access_log;

下面的 SQL 语句计算 "access_log" 表中不同 site_id 的记录数：

实例

SELECT COUNT(DISTINCT site_id) AS nums FROM access_log;

SELECT name AS FirstSite FROM Websites LIMIT 1;

SELECT name FROM Websites
ORDER BY id DESC

LIMIT 1;

最大值

下面的 SQL 语句从 "Websites" 表的 "alexa" 列获取最大值：

实例

SELECT MAX(alexa) AS max_alexa FROM Websites;

SELECT MIN(alexa) AS min_alexa FROM Websites;

统计和

SELECT SUM(count) AS nums FROM access_log;

局部统计，聚合函数

SELECT site_id, SUM(access_log.count) AS nums

FROM access_log GROUP BY site_id;

SELECT SUM(cid)AS nums,uid FROM consumer GROUP BY uid;

Having语句

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

HAVING 子句可以让我们筛选分组后的各组数据。

SQL HAVING 语法

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;

现在我们想要查找总访问量大于 200 的网站。

我们使用下面的 SQL 语句：

实例

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log
INNER JOIN Websites
ON access_log.site_id=Websites.id)
GROUP BY Websites.name
HAVING SUM(access_log.count) > 200;

SELECT UCASE(name) AS site_title, url

FROM Websites;

SELECT LCASE(name) AS site_title, url

FROM Websites;

下面的 SQL 语句从 "Websites" 表的 "name" 列中提取前 4 个字符：

实例

SELECT MID(name,1,4) AS ShortTitle
FROM Websites;

计算文本长度

SELECT name, LENGTH(url) as LengthOfURL FROM Websites;

和表无关SELECT ROUND(3.9);

SELECT name, url, Now() AS date

FROM Websites; 记住Now后面有括号，函数都这样

下面的 SQL 语句从 "Websites" 表中选取 name, url 以及格式化为 YYYY-MM-DD 的日期：

实例

SELECT name, url, DATE_FORMAT(Now(),'%Y-%m-%d') AS date
FROM Websites;

SQL ALTER TABLE 语法

如需在表中添加列，请使用下面的语法:

ALTER TABLE table_name
ADD column_name datatype

如需删除表中的列，请使用下面的语法（请注意，某些数据库系统不允许这种在数据库表中删除列的方式）：

ALTER TABLE table_name
DROP COLUMN column_name

要改变表中列的数据类型，请使用下面的语法：

SQL Server / MS Access：

ALTER TABLE table_name
ALTER COLUMN column_name datatype

My SQL / Oracle：

ALTER TABLE table_name
MODIFY COLUMN column_name datatype

Oracle 10G 之后版本:

ALTER TABLE table_name
MODIFY column_name datatype;

用于 MySQL 的语法

下面的 SQL 语句把 "Persons" 表中的 "ID" 列定义为 auto-increment 主键字段：

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