MySql常用语法整理

列出服务器管理的数据库

show DATABASES

 

 

用于显示当前选定的 MySQL 数据库中的所有表

SHOW TABLES

 

 

显示有关给定表中的列的信息

SHOW COLUMNS

 

SHOW COLUMNS FROM orders;

 

 

DISTINCT 关键字

与 SELECT 结合使用，以去掉所有重复的记录，仅返回唯一的记录。

基本语法

SELECT DISTINCT column_name1, column_name2 FROM table_name;

eg:

select DISTINCT firstname,lastname from CUSTOMERS;

 

 

LIMIT 关键字

语法如下：

SELECT column list

FROM table_name

LIMIT [number of records];

也可以从特定的偏移量中选取一组记录。

在下面的例子中，我们从第三个位置开始选取四条记录：

#MySQL 是从零开始计数的，意味着第一行的偏移量是 0，而不是 1

SELECT ID, FirstName, LastName, City

FROM customers LIMIT 1, 2;

 

 

完全限定名称

在 SQL 中，你可以在列名之前提供表名，通过用点分隔。

以下语句是等效的：

SELECT City FROM customers;

SELECT customers.City FROM customers;

当使用多个可能共享相同列名的表时，这种写法是特别有用的。

 

 

排序

SELECT * FROM customers

ORDER BY FirstName;

 

SELECT * FROM customers

ORDER BY FirstName DESC;

多列排序

将对多个列使用 ORDER BY 时，请使用逗号分隔列的列表以跟随 ORDER BY,

它将按列出的第一列排序，然后按第二列排序，依此类推

SELECT * FROM customers ORDER BY lastname,age;

 

 

BETWEEN 运算符

BETWEEN 操作符选取介于两个值之间的数据范围内的值。这些值可以是数值、文本或者日期。

BETWEEN子句的语法如下：

SELECT column_name(s)

FROM table_name

WHERE column_name BETWEEN value1 AND value2;

 

select * from customers where age between 18 and 19;

 

 

and 与 or 结合

select * from customers where lastname = 'ho' and (age =18 or age =19);

 

 

IN运算符

select * from customers where lastname in ('ho','hu');

NOT IN 运算符允许你从结果集中排除特定值的列表。

select * from customers where lastname not in ('ho','hu');

 

 

CONCAT() 函数

CONCAT() 函数用于连接两个或多个文本值，并返回连接字符串。

select CONCAT(firstname,' ',lastname) from customers;

 

 

AS 关键字

为结果列分配自定义名称，指定新列名

一个连接导致一个新的列。默认列名称将是 CONCAT 函数(等函数)。

可以使用 AS 关键字为结果列分配自定义名称：

select CONCAT(firstname,' ',lastname) as name from customers;

 

 

算术运算符

算术运算符对数值操作数执行算术运算。算术运算符包括加法（+），减法（-），乘法（*）和除法（/）

select ID, FirstName, LastName, age+10 as age from customers;

 

 

UPPER() 函数与LOWER() 函数

前者将指定字符串中的所有字母转换为大写，后者将字符串转换为小写

如果字符串中的字符不是字母，则此函数对它们没有影响

select firstname, UPPER(lastname) from customers;

select firstname, UPPER(lastname) as lastname from customers;

 

 

SQRT() 和 AVG() 函数

#SQRT() 函数返回参数中给定值的平方根

#AVG() 函数返回数值列的平均值：

select age,SQRT(age) from customers;

select age,AVG(age) from customers;

 

 

SUM() 函数

#SUM() 函数返回数值列的总数。

select SUM(age) from customers;

 

 

子查询

#当一个查询是另一个查询的条件时，称之为 子查询。

select * from customers where age > (select AVG(age) FROM customers) ORDER BY age desc;

#将子查询括在括号中。另外，子查询结尾处没有分号，因为它是单个查询的一部分。

 

 

LIKE 操作符

# LIKE 操作符用于在 WHERE 子句中搜索列中的指定模式。

# SQL 的模式匹配允许你使用 “_” 匹配任何单个字符，而使用 “%” 匹配任意数目字符（包括零个字符）

select * from customers where firstname like '%o%';

 

 

MIN() 函数

返回指定列的最小值

select MIN(age) from customers;

select firstname,MIN(age) from customers where firstname like '%o%' and age in(18,19,20);

 

 

表连接

#“表连接” 表示组合来自两个或多个表的数据。表连接创建一个临时表，显示连接表中的数据。

-- 要连接这两个表，在 FROM 子句中将它们指定为逗号分隔列表

select guest.id,guest.name,orders.name,orders.amount

from guest,orders

where guest.id = orders.guest_id

order by guest.id;

 

使用【AS】为连接列或表指定别名

select guest.id,guest.name as guest_name,orders.name as goods_name,orders.amount

from guest,orders

where guest.id = orders.guest_id

order by guest.id;

 

 

连接类型

-- 以下是可以在 SQL 中使用的连接类型：

-- 内连接（INNER JOIN） 等同于JOIN

-- 左连接（LEFT JOIN）

-- 右连接 （RIGHT JOIN）

-- ON 关键字用于指定内部连接条件。

--  语法：

# 1.内连接（INNER JOIN）

SELECT column_name(s) 

FROM table1 INNER JOIN table2

ON table1.column_name = table2.column_name;

 

# 2.左连接（LEFT JOIN）

-- LEFT JOIN 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。

-- 如果右表中没有匹配，则列结果为 NULL。

-- 语法

SELECT table1.column1, table2.column2...

FROM table1 LEFT OUTER JOIN table2

ON table1.column_name = table2.column_name;

SELECT guest.*,orders.amount

FROM guest LEFT JOIN orders

ON guest.name = orders.name;

 

# 3.右连接（RIGHT JOIN）

-- RIGHT JOIN 关键字从右表（table2）返回所有的行，即使左表（table1）中没有匹配。

-- 如果左表中没有匹配，则结果为 NULL。

-- 基本语法如下：

SELECT table1.column1, table2.column2...

FROM table1 RIGHT OUTER JOIN table2

ON table1.column_name = table2.column_name;

SELECT orders.*,guest.name as guest_name

FROM guest RIGHT JOIN orders

ON guest.name = orders.name;

 

 

UNION 操作符

-- 有时候，你可能需要将多个表的数据合并为一个综合数据集。这可能是在相同数据库中具有相似数据的表，或者可能需要在数据库、甚至跨服务器上合并相似的数据。

-- 要完成此操作，请使用 UNION 和 UNION ALL 操作符。

-- UNION 将多个数据集合并到单个数据集中，并删除任何现有的重复项。

-- UNION ALL 将多个数据集合并到一个数据集中，但不会删除重复的行。

-- UNION ALL 比 UNION 快，因为它不会对数据集执行重复删除操作。

-- UNION 操作符用于合并两个或多个 SELECT 语句的结果集。

-- 请注意，UNION 内部的每个 SELECT 语句必须拥有相同数量的列。列也必须拥有相似的数据类型。同时，每个 SELECT 语句中的列的顺序必须相同。

-- SQL UNION 语法：

SELECT column_name(s) FROM table1

UNION

SELECT column_name(s) FROM table2;

 

-- 提示：如果你的列在所有查询中不完全匹配，您可以使用NULL（或任何其他）值。

-- SQL 语句将如下所示：

SELECT FirstName, LastName, Company FROM table3

UNION

SELECT FirstName, LastName, NULL FROM table4;

SELECT id,age,FirstName FROM customers

UNION

SELECT id,age,NULL FROM guest;

 

 

SQL 约束

-- 在 SQL 中，我们有如下约束：

-- NOT NULL - 指示某列不能存储 NULL 值。

-- UNIQUE - 保证某列的每行必须有唯一的值。

-- PRIMARY KEY - NOT NULL 和 UNIQUE 的结合。确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速地找到表中的一个特定的记录。

-- FOREIGN KEY - 保证一个表中的数据匹配另一个表中的值的参照完整性。

-- CHECK - 保证列中的值符合指定的条件。

-- DEFAULT - 规定没有给列赋值时的默认值。

-- 约束可以在创建表时规定（通过 CREATE TABLE 语句），或者在表创建之后规定（通过 ALTER TABLE 语句）。

 

ALTER TABLE

-- ALTER TABLE 语句用于在已有的表中添加、删除或修改列。

-- 还可以使用 ALTER TABLE 命令在现有表上添加和删除各种约束。

 

-- 添加列： ALTER TABLE TABLE_NAME ADD COLUMN_NAME DATA_TYPE ;

-- 删除列： ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

-- 重命名列: ALTER TABLE TABLE_NAME CHANGE OLD_COLUMN_NAME NEW_COLUMN_NAME DATA_TYPE;

 

-- 删除整表：DROP TABLE TABLE_NAME;

-- 重命名表：RENAME TABLE OLD_TABLE_NAME TO NEW_TABLE_NAME;

 

 

视图

-- 在 SQL 中，视图是基于 SQL 语句的结果集的可视化的表。

-- 视图包含行和列，就像一个真实的表。视图中的字段就是来自一个或多个数据库中的真实的表中的字段。

-- 你可以向视图添加 SQL 函数、WHERE 以及 JOIN 语句，也可以呈现数据，就像这些数据来自于某个单一的表一样。

-- 视图总是显示最新的数据！每当用户查询视图时，数据库引擎通过使用视图的 SQL 语句重建数据

-- SQL CREATE VIEW 语法：

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition;