MySQL必知必会
文章目录
SQL Tutorial
Select
查询
SELECT column1, column2, ...
FROM table_name;
SELECT * FROM table_name;
Select Distinct
选择不同的项
SELECT DISTINCT column1, column2, ...
FROM table_name;
where
范围选择
SELECT column1, column2, ...
FROM table_name
WHERE condition;
symbol | describe |
---|---|
= | Equal |
> | Greater than |
< | Less than |
>= | Greater than or equal |
<= | Less than or equal |
<> | Not equal. Note: In some versions of SQL this operator may be written as != |
BETWEEN | Between a certain range |
LIKE | Search for a pattern |
IN | To specify multiple possible values for a column |
And、Or、Not
与或非选择
//与
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
//或
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
//非
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Order By
排序,默认升序,DESC:倒序
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Insert Into
插入数据
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Null Value
空值
//寻找null value
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
//寻找不为空
SELECT column_names
FROM table_name
WHERE column_name IS NULL;
Update
更新
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
Delete
删除
DELETE FROM table_name WHERE condition;
Select Top
选择指定数量的结果,不同数据库略有差异
- SQL Server/MS Access : number
- MySQL: Limit
- Oracle:Rownum
--SQL Server / MS Access Syntax:
SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;
--MySQL Syntax:
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;
--Oracle Syntax:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Min和Max函数
Min返回给定字段最小的一项数据,Max则相反
SELECT MIN(column_name)
FROM table_name
WHERE condition;
SELECT MAX(column_name)
FROM table_name
WHERE condition;
Count、Avg、Sum
Count统计,Avg平均数、Sum求和
--count
SELECT COUNT(column_name)
FROM table_name
WHERE condition;
--Avg
SELECT AVG(column_name)
FROM table_name
WHERE condition;
--Sum
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Like
模糊查询
- %:0个或多个字符
- _ :1个字符
SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;
WildCards
通配符
MS Access:
符号 | 描述 | 例子 |
---|---|---|
* | 0个或多个字符 | bl* 可以代表 bl, black, blue, and blob |
? | 代表一个字符 | h?t 可以代表hot, hat, and hit |
[] | 代表方括号里的任意单个字符 | h[oa]t 可以代表 hot and hat, 但不能代表 hit |
! | 代表不在方括号里的任意字符 | h[!oa]t 与上面一个相反 |
- | 代表一个范围 | c[a-b]t 代表 cat and cbt |
# | 代表任意一个数字 | 2#5 代表205, 215, 225, 235, 245, 255, 265, 275, 285, 295 |
SQL Server:
符号 | 描述 | 例子 |
---|---|---|
% | 0个或多个字符 | bl* 可以代表 bl, black, blue, and blob |
_ | 一个字符 | h_t 可以代表hot, hat, and hit |
[] | 方括号里的任意字符 | h[oa]t 可以代表 hot and hat, 但不能代表 hit |
^ | 不在方括号里的任意字符 | h[^ oa]t 代表 hit, 但不代表 hot 和 hat |
- | 代表一个范围 | c[a-b]t 代表 cat 和 cbt |
In
相当于多个Or
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
or
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Between
指定选择范围
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Alias
别名
--给字段取别名
SELECT column_name AS alias_name
FROM table_name;
--给表取别名
SELECT column_name(s)
FROM table_name AS alias_name;
Join
主要用于多表查询,不同的Join如下:
- Inner Join:返回表的交集部分
```sql
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
```
-
Left Join:返回左表全部和匹配的右表部分
SELECT column_name(s) FROM table1 LEFT JOIN table2 ON table1.column_name = table2.column_name;
-
Right Join:返回整个右表以及和左表匹配的部分
```sql
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
```
- Full Join:返回两张表的所有匹配信息
```sql
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
```
-
Self Join:表与自身的联接
SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
Union
用于组合两个或多个结果集,但要求结果集合要有相同的结构和数据类型
--默认不允许有重复
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
--允许有重复
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Group By
将结果指定字段进行分组,一般与聚合函数(COUNT, MAX, MIN, SUM, AVG)一起使用
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
Having
Having是为了解决Where无法使用聚合函数而引入的
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
Exists
Exists用于测试子查询是否有结果,如果有,返回true。
SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);
Any、All
Any、All主要用于Where和Having。当有任意子查询满足条件,Any返回true;All对应满足所有子查询。
SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
(SELECT column_name FROM table_name WHERE condition);
SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
(SELECT column_name FROM table_name WHERE condition);
Select Into
Select Into主要用于将数据从一个表中复制到另外一个表中。通常用于备份
--复制全部
SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
--复制部分
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;
Insert Into Select
Insert Into Select用于将一个表中的数据插入到另外一个表中去。注意要两个表要数据类型匹配
INSERT INTO target_table
SELECT * FROM source_table
WHERE condition;
INSERT INTO target_table (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM source_table
WHERE condition;
Case
Case用于多选择判断的情况
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
eg.
SELECT OrderID, Quantity,
CASE
WHEN Quantity > 30 THEN 'The quantity is greater than 30'
WHEN Quantity = 30 THEN 'The quantity is 30'
ELSE 'The quantity is under 30'
END AS QuantityText
FROM OrderDetails;
NULL Functions
用于判断是否为空,在某些时候需要将其设置成一个临时值
eg.
P_Id | ProductName | UnitPrice | UnitsInStock | UnitsOnOrder |
---|---|---|---|---|
1 | Jarlsberg | 10.45 | 16 | 15 |
2 | Mascarpone | 32.56 | 23 | |
3 | Gorgonzola | 15.67 | 9 | 20 |
--MySQL
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;
--SQL Server
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;
--MS Access
SELECT ProductName, UnitPrice * (UnitsInStock + IIF(IsNull(UnitsOnOrder), 0, UnitsOnOrder))
FROM Products;
--Oracle
SELECT ProductName, UnitPrice * (UnitsInStock + NVL(UnitsOnOrder, 0))
FROM Products;
Stored Procedures
Stored Procedures:存储过程也就是一段可编程函数,用于完成特定功能的SQL语句集和,使用存储过程主要有好处有
- 提高效率:将重复度非常高的一些操作通过存储过程反复调用
- 提高数据安全性:通过统一接口,从而提高数据的安全性
存储过程创建、查看、删除
delimiter $
:该语句将sql语句的分隔符改为$
(默认是;
)从而确保多条sql语句能被放到一个存储过程中
创建存储过程
CREATE PROCEDURE 名称()
BEGIN
语句
END$
调用存储过程
CALL 名称();
删除存储过程
DROP PROCEDURE `存储过程名称`;
Comments
单行注释:–
多行注释:/**/
Operators
运算符,SQL有各种各样的运算符,如算术运算符(±*/)、逻辑运算符(ALL AND OR)、位运算符(& | ^)等
SQL DataBase
Create DB
创建数据库
CREATE DATABASE databasename;
Drop DB
删除数据库
DROP DATABASE databasename;
Backup DB
备份数据库
--SQL Server
BACKUP DATABASE databasename
TO DISK = 'filepath';
Create Table
创建表
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
....
);
--使用其他表来创建表
CREATE TABLE new_table_name AS
SELECT column1, column2,...
FROM existing_table_name
WHERE ....;
Drop Table
删除表
--表本身也删除
DROP TABLE table_name;
--只删除表的数据,表自身不删除
TRUNCATE TABLE table_name;
Alter Table
修改表结构,如增加字段、删除字段、修改字段等
--添加字段
ALTER TABLE table_name
ADD column_name datatype;
--删除字段
ALTER TABLE table_name
ADD column_name datatype;
--修改字段
--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;
Constraints
表约束,一般在建表时给定相应约束
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
SQL常用约束:
-
NOT NULL:不为空
-
UNIQUE:值不同
-
PRIMARY KEY:主键约束
-
FORIGEN KEY:外键约束
-
CHECK:确保值满足特定条件
-
DEFAULT:给字段设置默认值
-
INDEX:用于快速从数据库创建和检索数据
eg:
--NOT NULL CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int ); --Unique --SQL Server / Oracle / MS Access CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int ); --MySQL CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID) ); --定义多个字段的组合约束unique CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, CONSTRAINT UC_Person UNIQUE (ID,LastName) );
Auto Increment
自动增长:主要用于主键
Dates
日期:
MySQL:
- DATE - format YYYY-MM-DD
- DATETIME - format: YYYY-MM-DD HH:MI:SS
- TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
- YEAR - format YYYY or YY
SQL Server:
- DATE - format YYYY-MM-DD
- DATETIME - format: YYYY-MM-DD HH:MI:SS
- SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
- TIMESTAMP - format: a unique number
Views
视图:基于结果集的虚拟表
创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
更新视图
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
删除视图
DROP VIEW view_name;
Injection
注入:一种代码注入技术,可以侵入/破坏数据库,可以通过参数来防控
Data Types
数据类型:SQL有多种数据类型,一眼而言,主要分三类:字符串、数字、日期。具体参看官网
MM-DD HH:MI:SS
- TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
- YEAR - format YYYY or YY
SQL Server:
- DATE - format YYYY-MM-DD
- DATETIME - format: YYYY-MM-DD HH:MI:SS
- SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
- TIMESTAMP - format: a unique number
Views
视图:基于结果集的虚拟表
创建视图
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
更新视图
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
删除视图
DROP VIEW view_name;
Injection
注入:一种代码注入技术,可以侵入/破坏数据库,可以通过参数来防控
Data Types
数据类型:SQL有多种数据类型,一眼而言,主要分三类:字符串、数字、日期。具体参看官网