学习自C语言中文网SQL教程笔记,该笔记为速查笔记,学习还是看原教程文章:http://c.biancheng.net/sql/
SQL命令
SQL 是关系型数据库的标准语言,SQL关键字不区分大小写
SQL语句分为以下三种类型:
DML: Data Manipulation Language 数据操纵语言
DDL: Data Definition Language 数据定义语言
DCL: Data Control Language 数据控制语言
1、数据定义语言
CREATE #创建表,视图或对象
ALTER #修改现有的数据库。比如表,记录
DROP #删除整个表,视图或数据库中其他对象
2、数据处理语言
SELECT #检索记录
INSERT #插入一条记录
UPDATE #修改记录
DELETE #删除记录
3、数据控制语句
GRANT #向用户分配权限
REVOKE #收回用户权限
SQL语法
1、SELECT 查数据
SELECT column1, column2,...,columnN FROM table_name;
2、UPDATE 更新数据库中数据
UPDATE table_name SET column1=value1, column2=value2,...,columnN=valueN [WHERE CONDITION]
3、DELETE 从数据库中删除数据
DELETE FROM table_name WHERE {CODITION};
4、CREATE TABLE 创建新的数据表
CREATE TABLE table_name(
column1 datatype,
column2 datatype,
...
columnN datatype,
PRIMARY KEY(one or more columns)
);
5、ALTER TABLE 修改数据表,修改数据表字段
ALTER TABLE table_name {ADD|DROP|MODIFY} column_name {data_type};
ALTER TABLE table_nae RENAME TO new_table_name;
6、DROP TABLE 删除数据表
DROP TABLE table_name;
7、CREATE DATABASE 创建新的数据库
CREATE DATABASE database_name;
8、INSERT INTO 向数据表中插入新的数据
INSERT INTO table_name(column1,column2,...,columnN) VALUES(value1,value2,...,valueN);
9、CREATE INDEX 创建索引
CREATE UNIQUE INDEX index_name ON table_name(column1,column2,...,columnN);
10、DROP INDEX 删除索引
ALTER TABLE table_name DROP INDEX index_name;
数据库操作
#创建数据库
CREATE DATABASE test;
#查看数据库
SHOW DATABASES;
#删除数据库
DROP DATABASE test;
#选择数据库
USE test;
数据表
# 创建数据表
CREATE TABLE website(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
url VARCHAR(30) DEFAULT '',
age TINYINT UNSIGNED NOT NULL,
uv FLOAT DEFAULT '0',
country CHAR(3) NOT NULL DEFAULT '',
PRIMARY KEY (`id`)
);
# 查看数据表结构
DESC website;
# 删除表
DROP TABLE website;
# 重命名表
ALTER TABLE old_table_name RENAME TO new_table_name;
# or
RENAME old_table_name TO new_table_name;
注意事项:
- 单引号:当字段的数据类型是 char,varchar,插入的value必须用单引号,这里的插入的其他数据,包括整型的数据,可以也可以用单引号
- 反引号:我们给表名起名字,可能会与sql 语法的关键词冲突因此,表名与字段/属性名字统一用反引号引起来
- 对于数据表的主键例如 id ,因为他是整型integer ,不用引起来
数据操作
INSERT INTO 插入
# 指定列插入数据
INSERT INTO table_name(column1,column2,column3,...,columnN) VALUES(value1,value2,value3,...,valueN);
#or
INSERT INTO table_name VALUES(value1,value2,...,valueN);# 需要全部字段的值
# 一个表填充另一个表(另一个表中必须有一组字段和当前表的字段是匹配的)
INSERT INTO first_table_name[(column1,...,columnN)] SELECT column1,...,columnN FROM second_table_name [WHERE condition]
SELECT 选取数据
SELECT column1,...,columnN FROM table_name WHERE conditions;
#WHERE子句是可选的
SELECT可以结合以下子句一起使用:
- WHERE:指明筛选条件
- ORDER BY:按某个字段对结果进行排序
- GROUP BY:结合聚合函数,根据一个或多个列对结果集进行分组
- HAVING:通常和GROUP BY 联合使用,用来过滤由GROUP BY子句返回的结果集
WHERE 指定查询条件
SELECT column1,...,columnN FROM table_name WHERE condition
condition条件中可用:
比较运算符:>, <, = 等
逻辑运算符:AND, OR等
模糊匹配:LIKE, NOT LIKE 等
# 例
SELECT id, name, url, uv FROM website WHERE uv>80 AND name LIKE '%o%'
UPDATE 修改数据
UPDATE table_name SET column1=value1,...,column=valueN WHERE [condition];
DELETE 删除数据
DELETE FROM table_name WHERE [condition]
# 例
DELETE FROM website WHERE id = 6;# 删除指定行
DELETE FROM website; #删除所有记录
LIKE 模糊匹配
SELECT FRMO table_name WHERE column LIKE 'pattern'
SELECT * FROM website WHERE uv LIKE '59%'; #以59开头的记录
TOP 限制返回数据数量
并不是所有的数据库都支持 TOP 子句,有些数据库使用其它的等价语句来替代,例如:
- MySQL 使用 LIMIT 子句获取指定数量的记录;
- Oracle 使用 ROWNUM 子句获取指定数量的记录
SELECT TOP number|percent column_name(s) FROM table_name WHERE [condition]
# 例
SELECT TOP 3 * FROM website;
ORDER BY 排序
默认情况下,大部分数据库将查询结果按照升序排序
SELECT column_list FROM table_name [WHERE condition] [ORDER BY column1,column2,...,columnN] [ASC | DESC];
# ASC 关键字表示升序,DESC 关键字表示降序
SELECT * FROM website ORDER BY age, uv;
GROUP BY 分组
SELECT column1,...,columnN FROM table_name WHERE [condition] GROUP BY column1,...,columnN ORDER BY column1,...,columnn
# 顺序不可颠倒
- SUM( ) 指定字段的值进行求和
- COUNT( ) 计算某个分组内数据的条数;
- AVG( )指定字段的值求平均数。
# 例
SELECT country, TRUNCATE(SUM(uv), 2) AS total FROM website GROUP BY country;
DISTINCT 删除重复记录
UNIQUE 和 DISTINCT 的效果是等价的,都用来消除结果集中的重复记录( UNIQUE 是一种老式的语法,Oracle 数据库曾经使用过)
SELECT DISTINCT column1,...,columnN FROM table_name WHERE [condition]
SQL约束
约束(Constraint)是指表的数据列必须强行遵守的规则,这些规则用于限制插入表中的数据类型
约束可以是列级别,也可以是表级别,常用约束有:NOT NULL, DEFAULT, UNIQUE, PRIMARY Key, FOREIGN Key, CHECK, INDEX
1、创建约束:
- CREATE TABLE 创建数据表时指定约束
- ALTER TABLE 创建数据表后增加约束
2、删除约束
# 删除EMPLOYEES 表中的主键
ALTER TABLE EMPLOYEES DROP CONSTRAINT EMPLOYEES_PK;
# Oracle 可以使用 DROP PRIMARY KEY 关键字删除主键约束
ALTER TABLE EMPLOYEES DROP PRIMARY KEY;
NULL
NOT NULL 关键字表示不允许该字段为空值, 创建表时,默认为运行NULL
不能使用 =、<、> 等比较运算符来检测 NULL 值,而必须使用 IS NULL 或者 IS NOT NULL 关键字来检测 NULL 值
# 筛选时
SELECT id, name, age, uv,country FROM website WHERE url IS NOT NULL;
# 已创建的表修改约束
ALTER TABLE website MODIFY url VARCHAR(30) NOT NULL
DEFAULT 默认约束
ALTER TABLE website MODIFY uv FLOAT DEFAULT '10.0';
# 删除默认约束
ALTER TABLE website ALTER COLUMN uv DROP DEFAULT;
UNIQUE 唯一约束
PRIMARY KEY = UNIQUE + NOT NULL
一张表可以包含多个 UNIQUE 字段,但是只能有一个主键。
# 将 UNIQUE 约束添加到 alexa 字段
ALTER TABLE website MODIFY alexa INT UNSIGNED NOT NULL UNIQUE;
# 多个字段添加 UNIQUE 约束
ALTER TABLE website ADD CONSTRAINT myUniqueConstraint UMIQUE(alexa, url);
# 删除UNIQUE约束
ALTER TABLE website DROP CONSTRAINT myUniqueConstraint;
# MYSQL 删除UNIQUE约束
ALTER TABLE website DROP INDEX myUniqueConstraint;
Primary Key
当主键包含多个字段时,又称为复合键(Composite Primary Key)
- SQL 规定,主键长度不能超过 900 个字节。Oracle 规定,主键不能超过 32 个字段。
# 未建表
CREATE TABLE website(
id INT NOT NULL AUTO_INCREMENT, # 定义为主键
name VARCHAR(20) NUT NULL,
url VARCHAR(30),
age INT UNSIGNED NOT NULL,
alexa INT UNSIGNED NOT NULL,
uv FLOAT DEFAULT '0',
country CHAR(3) NUT NULL,
PRIMARY KEY (id);
)
# 已建表
ALTER TABLE website ADD PRIMARY KEY(id);
# 主键包含多个字段
ALTER TABLE website ADD CONSTRAINT PK_CUSTID PRIMARY KEY (id, url);
# 删除主键
ALTER TABLE website DROP PRIMARY KEY;
Foreign Key
# 创建user表
CREATE TABLE user(
id INT NOT NULL AUTO_INCREMENT,
username VARCHAR(30) NOT NULL,
password VARCHAR(30) NOT NULL,
email VARCHAR(30) NOT NULL,
cellphone CHAR(11),
ip CHAR(15) NOT NULL,
PRIMARY KEY(id)
);
CREATE TABLE `order`(
oid INT NOT NULL AUTO_INCREMENT,
uid INT REFERENCES user(id),
order_no CHAR(11) NOT NULL,
price DECIMAL(6,1) DEFAULT '0.0',
creat_time DATETIME NOT NULL,
pay_time DATETIME NOT NULL,
PRIMARY KEY(oid)
);
# order是 SQL 中的关键字,用作表名时需要使用反引号
# 已创建表
ALTER TABLE `order` ADD FOREIGN KEY (uid) REFERENCES user (id);
# 删除外键
ALTER TABLE `order` DROP FOREIGN KEY;
CHECK 检测约束
用来限制字段的取值范围。可以在 CHECK 约束中添加限制条件,只有满足这些条件的值才允许进入该字段。
1、创建表
CREATE TABLE website(
id INT NOT NULL AUTO_INCREMENT,
...
age TINYINT UNSIGNED NOT NULL CHECK(age<=25);
...
)
CREATE TABLE website (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
url VARCHAR(30),
age TINYINT UNSIGNED NOT NULL,
alexa INT UNSIGNED NOT NULL,
uv FLOAT DEFAULT '0',
country CHAR(3) NOT NULL ,
PRIMARY KEY (id),
CONSTRAINT myCheck CHECK(age<=25 AND alexa<=10000)
);
# myCheck 为自定义的约束名字。
2、已创建表
# 单个字段
ALTER TABLE website MODIFY age TINYINT UNSIGNED NOT NULL CHECK(age<=25);
# 多个字段
ALTER TABLE website ADD CONSTRAINT myCheck CHECK(age<=25 AND alexa<=10000);
3、删除约束
ALTER TABLE website DROP CONSTRAINT myCheck;
INDEX 索引
CREATE INDEX index_name ON table_name(column1,...,columnN)
# 针对 name 字段创建索引,用以提高检索姓名时的效率
CREATE INDEX myIndex ON website(name);
# 删除索引
ALTER TABLE website DROP INDEX myIndex;
JOIN 联合表(合并列)
SQL JOIN 子句用于将两个或者多个表联合起来进行查询
SELECT table1.column1, table2.column2... FROM table1 JOIN table2 ON table1.common_column1 = table2.common_column2
# 只有满足table1.common_column1 = table2.common_column2的记录才会合并为一行
# 例
SELECT table1.id, table1.name, table2.amount, table2.date FROM `table1` JOIN `table2` ON table1.id = table2.customer_id WHERE amount>1515 ORDER BY amount;
# 先ON后WHERE
1、INNER JOIN(默认)
返回两个表都满足条件的记录
2、LEFT JOIN
返回左表满足条件的记录
3、RIGHT JOIN
返回右表满足条件的记录
4、FULL JOIN
返回其中一个表存在满足条件的记录
(有点数据库不支持,例如MySQL,可以使用UNION ALL将LEFT JOIN和RIGHT JOIN结合起来)
# mysql
SELECT id, name, amount, date FROM customers LEFT JOIN orders ON customers.id =orders.customer_id
UNION ALL
SELECT id, name, amount, date FROM customers RIGHT JOIN orders ON customers.id=orders.customers_id
5、SELF JOIN
将一个表连接到自身,就像该表是两个表一样,为了区分两个表,在SQL语句中需要至少重命名一个表。
SQL 并没有 SELF JOIN 关键字,而是使用 WHERE 子句来达到自连接的目的。
SELECT a.id, a.name, a.salary,b.name AS this_ame, b.salary AS this_salary FROM customer AS a, customer AS b WHERE a.salary<b.salary;
SELF JOIN 以右表为主,它先将左表中的每一行与右表中的第一行进行比较,然后再将左表中的每一行与右表中的第二行进行比较
6、CROSS JOIN
交叉连接,从两个或者多个连接表中返回记录集的笛卡尔积(左表的每一行与右表的每一行合并)
SELECT table1.column1, table2.column2... FROM table1 CROSS JOIN table2;
# or
SELECT table1.column1, table2.column2... FROM table1, table2;
UNION和UNION ALL (合并行)
UNION 用于合并两个或者多个 SELECT 语句的结果集,默认会过滤掉结果集中重复的记录,只保留一条,且按默认规则对结果集进行排序
UNION ALL 只是对结果集进行简单的合并,不会过滤重复的记录,也不会进行排序
SELECT column1, column2... FROM table1, table2 [WHERE condition1]
UNION/UNION ALL
SELECT column1,column2... FROM table1, table2 [WHERE condition2]
- 每个 SELECT 语句都必须拥有相同数量的字段;
- 不同 SELECT 语句的对应字段必须拥有相似的类型
- 不同 SELECT 语句的字段名不需要相同,SQL 会将第一个 SELECT 语句的字段名作为结果集的字段名
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 data_type;
# 添加 NOT NULL 约束
ALTER TABLE table_name MODIFY column_name datatype NOT NULL;
# 添加UNIQUE约束
ALTER TABLE table_name ADD CONSTRAINT myUniqueConstraint UNIQUE(column1, column2...);
# 使用ALTER TABLE 添加CHECK约束
ALTER TABLE table_name ADD CONSTRAINT MyUniqueConstraint CHECK(condition);
# 添加主键约束
ALTER TABLE table_name ADD CONSTRAINT MyPrimaryKey PRIMARY KEY(column1, column2...);
# 删除UNIQUE约束
ALTER TABLE table_name DROP CONSTRAINT MyUniqueConstraint;
## MySQL时使用以下语句
ALTER TABLE table_name DROP INDEX MyUniqueConstraint;
# 删除主键约束
ALTER TABLE table_name DROP CONSTRAINT MyPrimaryKey;
## MySQL时使用以下语句
ALTER TABLE table_name DROP PRIMARY KEY;
TRUNCATE TABLE 清空表
- DROP TABLE 用来删除表,包括删除该表的数据、结构、索引、触发器、约束等所有信息
- TRUNCATE TABLE 仅仅删除表的所有记录,后续仍然可以使用该表
- 不带 WHERE 子句的 DELETE FROM 语句同样可以达到清空表的效果,但是 TRUNCATE TABLE 使用的系统资源和日志资源更少,因此比 DELETE FROM 更加快速
- TRUNCATE TABLE 还能重置具有自动递增(AUTO_INCREMENT)属性的字段,而 DELETE FROM 却不具备该功能
TRUNCATE TABLE table_name;
视图
1、CREATE VIEW 创建
视图(View)是一个由 SELECT 查询所定义出来的虚拟表
CREATE VIEW view_name AS SELECT column1, column2... FROM table_name WHERE [condition];
创建视图其实就是给 SELECT 查询起了一个名字而已,并不会真的执行 SELECT 语句,只有当使用视图时,数据库引擎才会真正执行 SELECT 查询,产生一个结果集
2、修改
(INSERT、UODATE、DELETE)
为了使得修改操作在视图中同步呈现,需要在创建视图时增加WITH CHECK OPTION子句。
要求
- 通过视图修改的数据必须呈现到视图中
- 视图中的数据必须满足基础表的要求
使用INSERT语句向视图中插入的数据必须满足WHERE子句的设定条件(UODATE、DELETE类似)
# 创建视图
CREATE VIEW customers_view AS SELECT id, name, age, address, salary FROM customers WHERE salary>2000 WITH CHECK OPTION;
# 向视图插入数据
INSERT INTO customers_view(name, age, address, salary) VALUES('Tom', 30, 'MP', 3300.00);
3、删除
DROP VIEW view_name;
HAVING子句
通常与 GROUP BY 子句一起使用,用来过滤 GROUP BY 子句返回的分组结果集
GROUP BY 子句通常和聚合函数(COUNT()、SUM()、AVG()、MIN()、MAX() 等)一起使用来对结果进行分组,而 WHERE 子句不能和聚合函数一起使用,因此有了HAVING。
WHERE 子句在分组操作之前起作用,HAVING 子句在分组操作之后起作用。
SELECT column1, column2...
FROM table1, table2
WHERE [conditions]
GROUP BY column1, column2... HAVING [conditions]
ORDER BY column1, column2...
SQL事务
一条SQl语句称为一次基本操作,将若干条SQl语句“打包”在一起,共同执行一个完整的任务,就是事务。
一旦某个 SQL 语句执行失败,整个事务就失败了。事务失败后需要回滚所有的 SQL 语句。
事务属性:
- 原子性:所有语句,要么都成功要么都失败
- 一致性:数据必须完全符合所有预设规则
- 隔离性:数据库允许多个并发事务同时对其数据进行读写修改的能力。隔离分为不同级别,包括未提交、读提交、可重复读和串行化
- 持久性:事务处理结束后,对数据的修改就是永久的,即使系统故障也不会丢失
事务执行流程:
各种数据库对事务的支持细节不完全相同,以MySQL为例。
BEGIN或者START TRANSACTION:开始事务
COMMIT:提交事务
ROLLBACK:回滚事务
SAVEPOINT:在事务内部设置回滚标记点
RELEASE SAVEPOINT:删除回滚标记点
ROLLBACK TO:将事务回滚到标记点
事务控制命令仅能与 DML 类别的 SQL 命令一起使用,包括 INSERT、UPDATE、DELETE 和 SELECT,在创建或者删除表时不能使用事务,因为这些操作在数据库中是自动提交的。
# 开始事务
BEGIN;
# or
START TRANSACTION
# 提交事务
COMMIT;
# 提交事务意味着真正执行事务包含的 SQL 语句,并把对数据库的修改写入到磁盘上的物理数据库中。意味着事务结束且执行成功
BEGIN;
INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Chaitali', 25, 'Mumbai', 6500.00 );
INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES ('Hardik', 27, 'Bhopal', 8500.00 );
COMMIT;
# 回滚
BEGIN;
DELETE FROM CUSTOMERS WHERE ID=4;
DELETE FROM CUSTOMERS WHERE ID=5;
ROLLBACK;
# 设置标记点
SAVEPOINT point_name;
# 回滚到标记点
ROLLBACK TO point_name;
BEGIN;
DELETE FROM CUSTOMERS WHERE ID=4;
SAVEPOINT sp;
DELETE FROM CUSTOMERS WHERE ID=5;
ROLLBACK TO sp;
# 只有 ID 为 4 的用户被删除,ID 为 5 的用户依然留在数据库中
临时表的使用
临时表的概念由 SQL Server 引入,用来存储和处理中间结果
临时表在会话期间创建,会话结束后自动被删除,和普通表一样可以执行各种操作
CREATE TEMPORARY TABLE table_name(
column1 datatype;
column2 datatype;
...
columnN datatype;
PRIMARY KEY(one or more columns)
);
当使用 SHOW TABLES 命令查看数据库中的表时,临时表将不会被显示。
如果希望在会话期间删除临时表,则可以使用 DROP TABLE 命令:
DROP TABLE table_name;
克隆表(复制表)
步骤:
- 执行SHOW CREATE TABLE table_name:获取创建表的CREATE TABLE语句
- CREATE TABLE语句中的表名改成新表的名字,然后执行该语句
- 如果希望复制表中的数据,可以私用INSERT INTO和SELECT命令
# 旧表数据复制到新表
INSERT INTO new_table(column1, column2, column3, column4) SELECT column1, column2, column3, column4 FROM old_table;
SQL 子查询
将一个 SELECT 查询(子查询)的结果作为另一个 SQL 语句(主查询)的数据来源或者判断条件
子查询常用在 WHERE 子句和 FROM 子句后边
# WHERE子句后面
SELECT column_name FROM table1 WHERE column_name operator (SELECT column_name FROM table1/table2 [WHERE])
SELECT * FROM customers WHERE salary>(SELECT salary FROM customers WHERE name='komal');
SELECT * FROM customers WHERE id IN (SELECT id FROM customers WHERE salary>4500) AND age>25;
# FROM子句后面
SELECT column_name FROM (SELECT column_name FROM table1/table2 [WHERE] AS temp_table_name) WHERE condition;
AUTO_INCREMENT 自动增长序列
AUTO_INCREMENT 会在新记录插入表中时生成一个唯一的整数,这些整数是自动增长的,用户还可以指定增长的步长(默认为 1)
- 一个表中只能有一个字段使用 AUTO_INCREMENT 约束
- 在插入数据或者更新数据时,一般将 AUTO_INCREMENT 字段留空
# 获取AUTO_INCREMENT 最后的值(mysql)
SELECT LAST_INSERT_ID();
# 对现有序列重新编号
## 重新编号时必须十分小心,您应该检查您的表是否和另一个表关联
ALTER TABLE table_name DROP id;
ALTER TABLE table_name ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY(id);
# 从特定值开始增长(mysql)
CREATE TABLE table_name(
id INT UNSIGNED NOT NULL AUTO_INCREMENT = 100 PRIMARY KEY (id)
...
);
# or
ALTER TABLE table_name AUTO_INCREMENT = 100;
SQL注入
SQL 注入是一种代码渗透技术,是最常用的网络黑客技术之一,通过网页输入框将恶意 SQL 代码提交给服务器是最常见的 SQL 注入方式之一。
通常使用模式匹配(Pattern Matching),借助正则表达式来校验用户输入的数据,几乎每种编程语言都提供了模式匹配函数。