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  阅读(38)  评论(0)  编辑  收藏  举报