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;
symboldescribe
=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 !=
BETWEENBetween a certain range
LIKESearch for a pattern
INTo 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如下:

  1. Inner Join:返回表的交集部分

在这里插入图片描述

```sql
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
```
  1. Left Join:返回左表全部和匹配的右表部分

    在这里插入图片描述

    SELECT column_name(s)
    FROM table1
    LEFT JOIN table2
    ON table1.column_name = table2.column_name;
    
  2. Right Join:返回整个右表以及和左表匹配的部分

在这里插入图片描述

```sql
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
```
  1. Full Join:返回两张表的所有匹配信息

Full Join

```sql
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
```
  1. 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_IdProductNameUnitPriceUnitsInStockUnitsOnOrder
1Jarlsberg10.451615
2Mascarpone32.5623
3Gorgonzola15.67920
--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有多种数据类型,一眼而言,主要分三类:字符串、数字、日期。具体参看官网

posted @ 2020-10-05 17:34  pony.ma  阅读(40)  评论(0编辑  收藏  举报