MySQL必知必会

文章目录

SQL TutorialSelectSelect DistinctwhereAnd、Or、NotOrder ByInsert IntoNull ValueUpdateDeleteSelect TopMin和Max函数Count、Avg、SumLikeWildCardsInBetweenAliasJoinUnionGroup ByHavingExistsAny、AllSelect IntoInsert Into SelectCaseNULL FunctionsStored ProceduresCommentsOperators SQL DataBaseCreate DBDrop DBBackup DBCreate TableDrop TableAlter TableConstraintsAuto IncrementDatesViewsInjectionData TypesViewsInjectionData Types

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 rangeLIKESearch for a patternINTo 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 : numberMySQL: LimitOracle：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_IdProductNameUnitPriceUnitsInStockUnitsOnOrder1Jarlsberg10.4516152Mascarpone32.56233Gorgonzola15.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-DDDATETIME - format: YYYY-MM-DD HH:MI:SSTIMESTAMP - format: YYYY-MM-DD HH:MI:SSYEAR - format YYYY or YY

​ SQL Server：

DATE - format YYYY-MM-DDDATETIME - format: YYYY-MM-DD HH:MI:SSSMALLDATETIME - format: YYYY-MM-DD HH:MI:SSTIMESTAMP - 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:SSYEAR - format YYYY or YY

​ SQL Server：

DATE - format YYYY-MM-DDDATETIME - format: YYYY-MM-DD HH:MI:SSSMALLDATETIME - format: YYYY-MM-DD HH:MI:SSTIMESTAMP - 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有多种数据类型，一眼而言，主要分三类：字符串、数字、日期。具体参看官网

​