sql复习
参考：菜鸟教程
基本
创建表：
CREATE TABLE Websites(
id INT PRIMARY KEY,
name VARCHAR(255),
url VARCHAR(255),
alexa INT,
country VARCHAR(255)
);
INSERT INTO Websites(id, name, url, alexa, country)
VALUES
(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', 'http://www.facebook.com/', 3, 'USA');
CREATE TABLE access_log(
aid INT PRIMARY KEY,
site_id INT,
count INT,
date VARCHAR(255)
);
INSERT INTO access_log
VALUES
(1,1 ,45 , '2016-05-10'),
(2,3 ,100 , '2016-05-13'),
(3,1 ,230 , '2016-05-14');
# AND & OR
SELECT * FROM Websites
WHERE country='CN'
AND alexa > 50;
SELECT * FROM Websites
WHERE country='USA'
OR country='CN';
# DISTINCT 返回唯一的column1, column2, ...的组合
SELECT DISTINCT column1, column2, ...
FROM table_name;
# 将查询的结果使用某些列升序（默认）或降序排列
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
SELECT * FROM Websites
ORDER BY country,alexa DESC;
# 更新表中已存在的记录
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE Websites
SET alexa='5000', country='USA'
WHERE name='菜鸟教程';
# 删除行
DELETE FROM table_name
WHERE condition;
# 限制返回的记录的数目
SELECT column_name(s)
FROM table_name
LIMIT number;
select * from user limit 2;
# like
# 选取 name 不包含模式 "oo" 的
SELECT * FROM Websites
WHERE name NOT LIKE '%oo%';
# AS用于设置表或列的别名
SELECT name, CONCAT(url, ', ', alexa, ', ', country) AS site_info
FROM Websites;
SELECT w.name, w.url, a.count, a.date
FROM Websites AS w, access_log AS a
WHERE a.site_id=w.id and w.name="菜鸟教程";
# ALTER
ALTER TABLE 语句用于在已有的表中添加、删除或修改列。
## 添加列
ALTER TABLE table_name
ADD column_name datatype
## 删除列
ALTER TABLE table_name
DROP COLUMN column_name
## 修改类型
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
## 常见数据类型
VARCHAR(n)
INT
FLOAT(p)
DATE
TIME
其他就不摘录了，直接看菜鸟教程。
进阶
删除表的三种方式
drop table user; // 删除整个表，包括表的结构。
truncate table user; // 删除表中所有元素
delete from user where user_id = 1; // 删除表中某些元素
拼接多个表查询的结果：
基于表之间的共同字段，将查询结果拼接起来。
SELECT Websites.id, Websites.name, access_log.count, access_log.date
FROM Websites
LEFT JOIN access_log
ON Websites.id=access_log.count;
INNER JOIN：返回满足条件的行。
LEFT JOIN：返回满足条件的行，如果左表的某行没有和右表的任何行匹配，那么只返回左表的内容，右表的内容为NULL。
RIGHT JOIN：返回满足条件的行，如果右表的某行没有和左表的任何行匹配，那么只返回右表的内容，左表的内容为NULL。
FULL JOIN：返回满足条件的行，如果右表的某行没有和左表的任何行匹配，那么只返回右表的内容，左表的内容为NULL；如果左表的某行没有和右表的任何行匹配，那么只返回左表的内容，右表的内容为NULL。
sql注入
sql注入
sql注入：输入字符串将原有的sql语句拼接成非法的操作，如删库、休眠、始终合法（OR TRUE）等。
避免SQL注入：1.验证字符串合法性 2.不将参数的内容视为 SQL 语句的一部分来进行处理 3.限制用户操作数据库的权限
其他：
# 连接两个表的查询结果集 UNION ALL代表允许重复行 UNION代表不允许重复行
SELECT country FROM Websites
UNION ALL
SELECT country FROM apps
ORDER BY country;
# 只复 id=1 的数据到 "Websites" 中
INSERT INTO Websites (name, country)
SELECT app_name, country FROM apps
WHERE id=1;
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
# 如果UnitsOnOrder是null，那么IFNULL(UnitsOnOrder,0)为0
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
视图：相当于创建了一个虚的表。视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系。
# 选取 "Products" 表中所有单位价格高于平均单位价格的产品：
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
# 我们可以像这样查询上面这个视图：
SELECT * FROM [Products Above Average Price]
# 创建同名视图就可以覆盖原有视图，这就起到了修改视图的作用
# 删除视图
DROP VIEW view_name;
SQL 约束：
- NOT NULL - 指示某列不能存储 NULL 值。
- UNIQUE - 保证某列的每行必须有唯一的值。
- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。
- FOREIGN KEY- 保证一个表中的数据匹配另一个表中的值的参照完整性。一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。
- CHECK - 保证列中的值符合指定的条件。
- DEFAULT - 规定没有给列赋值时的默认值。
# 下面的 SQL 在 "Orders" 表创建时在 "P_Id" 列上创建 FOREIGN KEY 约束：
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id) # 约束名为fk_PerOrders
)
索引
用户无法看到索引，它们只能被用来加速搜索/查询。
CREATE INDEX index_name
ON table_name (column_name)
# 唯一索引
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
# 删除索引
ALTER TABLE table_name DROP INDEX index_name
