Mysql常用sql语句

目录

1 SQL 语法基础

2 创建数据库

3 删除数据库

4 MySQL 数据类型

5 数值类型

6 字符串类型

7 日期和时间类型

8 创建表

9 查询表

10 删除数据表

11 添加，删除，更新字段

12 查询 & 条件查询

13 简单的操作符

14 插入数据

15 更新数据

16 删除数据

17 LIKE 操作符

18 UNION 操作符

19 ORDER BY 子句

20 GROUP BY 语句

21 链接的使用

---

1 SQL 语法基础

MySQL 使用的是标准的 SQL 语法，在使用时有以下注意事项：

1. 语句中的关键字推荐全大写：
   虽然使用小写也可正常运行，但为了避免关键字与自定义的数据库名、表名、字段名等重复，建议使用全大写的语法。
2. 当定义数据库名、表名有多个单词时，使用 "_" 进行拼接，不要使用驼峰命名：
   数据库名、表名对大小写不敏感，大写字母会自动转换为小写，与预期的会有偏差，因此建议使用 "_" 的形式代替驼峰命名，如：user_info（正确），userInfo（错误）。

2 创建数据库

我们可以在登陆 MySQL 服务后，使用 create 命令创建数据库，语法如下:  

CREATE DATABASE 数据库名;

3 删除数据库

语法：

DROP DATABASE <数据库名>;

4 MySQL 数据类型

在 MySQL中定义字段的数据类型对数据库的优化是非常重要的。

MySQL支持多种类型，大致可以分为三类：数值、日期/时间和字符串(字符)类型。

5 数值类型

MySQL支持所有标准SQL数值数据类型

这些类型包括以下两类：

• 严格数值数据类型          
  • INTEGER，SMALLINT，DECIMAL，NUMERIC
• 近似数值数据类型
  • FLOAT，REAL，DOUBLE ，PRECISION

         关键字INT是INTEGER的同义词，关键字DEC是DECIMAL的同义词。BIT数据类型保存位字段值，并且支持MyISAM、MEMORY、InnoDB和BDB表。作为SQL标准的扩展，MySQL也支持整数类型TINYINT、MEDIUMINT和BIGINT。下面的表显示了需要的每个整数类型的存储和范围。

6 字符串类型

字符串类型指CHAR、VARCHAR、BINARY、VARBINARY、BLOB、TEXT、ENUM和SET。该节描述了这些类型如何工作以及如何在查询中使用这些类型。

CHAR 和 VARCHAR 类型类似，但它们保存和检索的方式不同。它们的最大长度和是否尾部空格被保留等方面也不同。在存储或检索过程中不进行大小写转换。

BINARY 和 VARBINARY 类似于 CHAR 和 VARCHAR，不同的是它们包含二进制字符串而不要非二进制字符串。也就是说，它们包含字节字符串而不是字符字符串。这说明它们没有字符集，并且排序和比较基于列值字节的数值值。

BLOB 是一个二进制大对象，可以容纳可变数量的数据。有 4 种 BLOB 类型：TINYBLOB、BLOB、MEDIUMBLOB 和 LONGBLOB。它们区别在于可容纳存储范围不同。

有 4 种 TEXT 类型：TINYTEXT、TEXT、MEDIUMTEXT 和 LONGTEXT。对应的这 4 种 BLOB 类型，可存储的最大长度不同，可根据实际情况选择。

8 创建表

语法

CREATE TABLE IF NOT EXISTS  table_name (
    column_name1 column_type1,
    ... ...
    column_nameN column_typeN
);

实例解析：

• 如果你不想字段为 NULL 可以设置字段的属性为 NOT NULL， 在操作数据库时如果输入该字段的数据为NULL ，就会报错。
• UNSIGNED 用于设置数据的值不重复。
• VARCHAR(100) 设置字段类型为 VARCHAR，并且字节长度不能超过100。
• AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。
• PRIMARY KEY 关键字用于定义列为主键。 您可以使用多列来定义主键，列间以逗号分隔。
• ENGINE 设置存储引擎，DEFAULT CHARSET 设置编码。
• IF NOT EXISTS  表不存在则创建

9 查询表

• 查询数据库中存在的所有表

语法：

SHOW TABLES;

查询数据库中某张表的设计信息

语法：

SHOW COLUMNS FROM table_name;

10 删除数据表

语法：

DROP TABLE table_name;

示例：

11 添加，删除，更新字段

添加字段

ALTER TABLE table_name ADD field_name field_type;

修改原字段名称及类型

ALTER TABLE table_name CHANGE old_field_name new_field_name new_field_type;

删除字段

ALTER TABLE table_name DROP field_name;

参数说明：

• table_name表名
• field_name字段名
• field_type 字段数据类型
• old_field_name旧的字段名
• new_field_name 新的字段名

12 查询 & 条件查询

基础查询

SELECT field1, ..., fieldN FROM table_name1, ..., table_nameN;
SELECT * FROM table_name1, ..., table_nameN;

条件查询

SELECT field1, ..., fieldN FROM table_name1, ..., table_nameN WHERE condition1 [AND | OR] condition2 ...

查询时对表进行重命名

SELECT newName.field FROM table_name (AS) newName;

解析：

• 查询语句中你可以使用若干个表，表之间使用逗号分割。
• 查询语句中你可以查询指定的若干个字段（field），也可使用星号（*）查询出所有字段。
• 当想限制查询条件时，可以使用 WHERE 子句指定任何条件。
• 可以使用 AND 或者 OR 指定一个或多个条件。
• 如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。
• 如果我们想在 MySQL 数据表中读取指定的数据，WHERE 子句是非常有用的。
• 使用主键来作为 WHERE 子句的条件查询是非常快速的。
• WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。
• WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据

13 简单的操作符

14 插入数据

语法：

INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

Tip：如果数据是字符型，必须使用单引号或者双引号，如 "value"。

15 更新数据

语法：

UPDATE table_name SET field1 = new_val1, ..., fieldN = new_valN [WHERE condition]; 

说明：

• field 为字段名，new_val 为更新后的值。
• 你可以同时更新若干个字段的数据，每个字段之间用逗号分隔即可。
• 更新语句通常都会结合 WHERE 子句来使用，可用来更新数据表中指定行的数据；若没有指定 WHERE 子句，数据表中的 field 都会被改为 new_val 值。

16 删除数据

语法：

DELETE FROM table_name [WHERE condition];

说明：若删除数据时没有指定 WHERE 子句， table_name 表中的所有记录都将被删除。

17 LIKE 操作符

我们知道在 MySQL 中可以使用 SELECT 命令来查询读取数据， 并且可以结合 WHERE 子句来获取指定记录：

在 WHERE 子句中使用等号（=）来设定获取数据的条件，如 "name=w3cschool"。

但是有时候我们需要获取 name 字段含有 "w3c" 字符的记录，这时就需要在 WHERE 子句中使用 LIKE 操作符，实现模糊查找。

SQL LIKE 子句中使用百分号 % 字符来表示任意字符，类似于 UNIX 或 正则表达式 中的星号 *。如果没有使用百分号（%），LIKE 操作符与等号（=）的效果是一样的。

语法：

SELECT field1, field2, ...fieldN FROM table_name
WHERE field1 LIKE "somevalue" [AND | OR] filed2 = "somevalue";

• 可以使用 LIKE 操作符代替等号 =。
• LIKE 通常与 % 一同使用，类似于一个元字符的搜索。
• 同样，你也可以使用 AND 或者 OR 指定一个或多个条件。
• 在 DELETE 或 UPDATE 命令中也可以使用 WHERE ... LIKE 子句来指定条件。

18 UNION 操作符

MySQL UNION 操作符用于合并两个以上的 SELECT 语句的结果、组合到一个结果集合中。

语法：

SELECT field(s) FROM table [WHERE conditions]
UNION [ALL | DISTINCT]
SELECT field(s) FROM table [WHERE conditions];

UNION 可选的修饰符：

• ALL：返回所有结果集，包含重复数据
• DISTINCT：默认值，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没有什么影响。

注意：

• UNION 内部每个 SELECT 语句必须拥有相同的列，即语法中的 s 必须相等，否则会报错；
• 每个 SELECT 语句的列最好是相同的字段名、数据类型，否则默认会合并成第一个 SELECT 语句的字段名。

19 ORDER BY 子句

 如果我们需要对 SELECT 查询的数据进行排序时，可以使用 MySQL 的 ORDER BY 子句来设定：希望按哪个字段的哪种方式进行排序，再返回搜索结果。

语法：

SELECT field1, ..., fieldN FROM tableName
ORDER BY field1 [ASC | DESC] [, ..., fieldN [ASC | DESC]];

• 可以使用任何字段来作为排序的条件，从而返回排序后的查询结果。
• 可以设定多个字段来排序。
• 可以使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下会使用 ASC 即升序排列。

20 GROUP BY 语句

 GROUP BY 语句可根据一个或多个列对结果集进行分组，在分组的列上可以使用 COUNT、SUM、AVG 等聚合函数。

语法：

SELECT [* | field], function(field) FROM tableName GROUP BY field;

21 链接的使用

在前几章节中，我们已经学会了如何在一张表中读取数据，这是相对简单的，但是在真正的应用中经常需要从多个数据表中读取数据。本节我们将向大家介绍如何使用 MySQL 的 JOIN 联合两个或多个表中查询数据，实现联表查询。

你可以在 SELECT、UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询。

JOIN 按照功能大致分为如下三类：

• INNER JOIN：内链接，或称为等值连接，用于获取两个表中字段匹配关系的记录；
• LEFT JOIN：左链接，用于获取左表的所有记录，即使右表没有对应匹配的记录；
• RIGHT JOIN：右链接，与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

mysql分页原理和高效率的mysql分页查询语句

这篇文章主要介绍了mysql分页原理和高效率的mysql分页查询语句,大家参考使用吧

以前我在mysql中分页都是用的 limit 100000,20这样的方式，我相信你也是吧，但是要提高效率，让分页的代码效率更高一些，更快一些,那我们又该怎么做呢?

第一部分：看一下分页的基本原理：

复制代码代码如下:

mysql explain SELECT * FROM message ORDER BY id DESC LIMIT 10000, 20 ***** 1. row ****** id: 1 select_type: SIMPLE table: message type: index possible_keys: NULL key: PRIMARY key_len: 4 ref: NULL rows: 10020 Extra: 1 row in set (0.00 sec)

对上面的mysql语句说明：limit 10000,20的意思扫描满足条件的10020行，扔掉前面的10000行，返回最后的20行，问题就在这里，如果是limit 100000,100，需要扫描100100行，在一个高并发的应用里，每次查询需要扫描超过10W行，性能肯定大打折扣。文中还提到limit n性能是没问题的，因为只扫描n行。

第二部分：根据雅虎的几位工程师带来了一篇Efficient Pagination Using MySQL的报告内容扩展：在文中提到一种clue的做法，给翻页提供一些线索，比如还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是1020，最小的是1000，如果我们只提供上一页、下一页这样的跳转（不提供到第N页的跳转），那么在处理上一页的时候SQL语句可以是：

复制代码代码如下:

SELECT * FROM message WHERE id>1020 ORDER BY id ASC LIMIT 20;//下一页

处理下一页的时候SQL语句可以是：

复制代码代码如下:

SELECT * FROM message WHERE id<1000 ORDER BY id DESC LIMIT 20;//上一页

不管翻多少页，每次查询只扫描20行。

缺点是只能提供上一页、下一页的链接形式，但是我们的产品经理非常喜欢“上一页 1 2 3 4 5 6 7 8 9 下一页”这样的链接方式，怎么办呢？

如果LIMIT m,n不可避免的话，要优化效率，只有尽可能的让m小一下，我们扩展前面的clue做法，还是SELECT * FROM message ORDER BY id DESC，按id降序分页，每页20条，当前是第10页，当前页条目id最大的是2519，最小的是2500;

当是第10页的SQL如下：

复制代码代码如下:

SELECT * FROM tb_goods_info WHERE auto_id >=2500 ORDER BY auto_id ASC LIMIT 0,20

比如要跳到第9页，SQL语句可以这样写：

复制代码代码如下:

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 0,20

比如要跳到第8页，SQL语句可以这样写：

复制代码代码如下:

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 20,20

比如要跳到第7页，SQL语句可以这样写：

复制代码代码如下:

SELECT * FROM tb_goods_info WHERE auto_id <2500 ORDER BY auto_id desc LIMIT 40,20

跳转到第11页：

复制代码代码如下:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 0,20

跳转到第12页：

复制代码代码如下:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 20,20

跳转到第13页：

复制代码代码如下:

SELECT * FROM tb_goods_info WHERE auto_id >2519 ORDER BY auto_id asc LIMIT 40,20

原理还是一样，记录住当前页id的最大值和最小值，计算跳转页面和当前页相对偏移，由于页面相近，这个偏移量不会很大，这样的话m值相对较小，大大减少扫描的行数。其实传统的limit m,n，相对的偏移一直是第一页，这样的话越翻到后面，效率越差，而上面给出的方法就没有这样的问题。

注意SQL语句里面的ASC和DESC，如果是ASC取出来的结果，显示的时候记得倒置一下。

已在60W数据总量的表中测试，效果非常明显