常用SQL语句

原文：https://blog.csdn.net/u013679744/article/details/76087429 

       主要内容包括，增删改查（CURD），重点在查询（子查询，模糊查询，分组查询等）。 
       在开始之前，要注意：SQL 是不区分大小写的，但是为了代码容易阅读与调试，一般将SQL关键字大写，对所有的表名和列名使用小写。多条 SQL 语句需要用分号（;）分隔，单条可以不加，但为了不出现错误，一般都加上，使用 mysql 命令行必须加分号来结束。另外 SQL 语句可以写在一行，但为了方便阅读与调试，一般会将 SQL 语句分成多行来写。mysql 作为是传统的关系型数据库，以表来组织数据。关于关系型数据库和非关系型数据库，可查看文章关系型数据库和非关系型数据库。 

查看语句：

-- Windows 启动数据库
net start mysql;

-- 查看数据库版本
mysql --version;
-- 查看当前数据库支持的引擎和默认的数据库引擎
-- 5.5.8之后 innodb为默认存储引擎
SHOW ENGINES;

-- 创建数据库
CREATE DATABASE database_name;
-- 查看数据库
SHOW DATABASES;
-- 查看表
SHOW TABLES;
-- 查看表状态
SHOW CREATE TABLE table_name;

创建和操纵表 

创建表和修改表：

-- 创建表
CREATE TABLE customers
(
  cust_id INT NOT NULL AUTO_INCREMENT,
  cust_name CHAR(50) NOT NULL,
  cust_email CHAR(255) NULL,
  PRIMARY KEY (cust_id),   -- 主建
  INDEX name_key (cust_name)        -- 建索引 索引名和列名 如果不指定索引名，默认列名
) ENGINE=InnoDB CHAR SET=utf8;   -- 存储引擎和编码

-- 删除表
DROP TABLE customers;

-- 添加列 默认可为空null
ALTER TABLE customers
ADD cust_phone CHAR(20);

-- 删除列
ALTER TABLE customers
DROP COLUMN cust_phone;

-- 重命名表
RENAME TABLE customers TO customers2;

插入数据

       数据插入，是利用 INSERT 插入行到数据库表中，可有几种方式：插入完整的行，插入行的一部分，插入多行以及插入某些查询的结果。

INSERT INTO customers
VALUES ( '1',
  'Tom',
  NULL,
  NULL
);

       注意：这里必须为表中的每个列都提供一个值，没有值并且可以为 null 的也必须制定 null 。且值需要用单引号’ ’ 表示。这种方式不安全，应避免使用，因为当表结构变化时，这种方式将出错。

INSERT INTO customers(cust_id,
        cust_name,
        cust_email,
        cust_phone)
VALUES ( '1',
         'Tom',
         NULL,
         NULL
);

       上边这种方式更安全，它是前面的列与后边的值一一对应。所以，不一定要按照各个列在实际表中出现的顺序，而且后边为null 的列，可以去掉，也就是?：

INSERT INTO customers(cust_id,
       cust_name)
VALUES ( '1',
         'Tom'
);

-- 插入多行
INSERT INTO customers(cust_id,
        cust_name)
VALUES (
         '1',
         'Tom'
),
(
         '2',
         'Kim'
);

更新和删除数据

更新操作：

       更新表中数据操作，用 UPDATE 、SET 语句，可分为：更新表中特定行，更新表中所有行。要注意WHERE 子句的应用，防止一不小心就更新了表中所有数据。

UPDATE customers
SET cust_email = 'edada@163.com'
WHERE cust_id = 1;

-- 更新多行
UPDATE customers
SET cust_email = 'edada@163.com',
    cust_phone = '1231'
WHERE cust_id = 1;

       更新操作。分别是：要更新的表名，列名和要更新值，确定要更新行的过滤条件。 

       注意：如果用 UPDATE 语句更新多行时，如果有一行出现错误，则整个UPDATE操作都被取消。如果，你想出现错误也要继续更新，可使用 IGNORE 关键字。即：UPDATE IGNORE。

删除操作

       在一个表中删除数据，使用 DELETE 语句，删除特定行、删除多有行。要注意WHERE 过滤条件的使用，安全性。

DELETE FROM customers
WHERE cust_name = 1;

检索数据

       本篇文章的重点是检索查询数据，在实际开发中用的也是最多的。这里，将详细解释其应用。

简单检索：LIMIT、ORDER BY

-- 检索多个列
SELECT cust_id, cust_name, cust_email
FROM customers;

-- 检索所有的列
SELECT cust_id, cust_name, cust_email, cust_phone
FROM customers;
-- 或
SELECT *
FROM customers;

       注意：如果不是需要表中的每个列，不要使用SELECT * 通配符操作，会降低检索性能。 
       若某行的值有重复，要检索出不同的行的 SQL 语句：

-- 检索不同的行
SELECT DISTINCT cust_name
FROM customers;

       限制结果，返回特定的行和行数：

-- 检索限制结果
-- LIMIT 第一个数为开始的行数，第二个数为要检索的行数

-- 从第0行（代表第一行）开始
SELECT cust_name
FROM customers
LIMIT 5;

-- 从第5行（实际表中的第六行）开始，显示5行
SELECT cust_name
FROM customers
LIMIT 5, 5;

       注意：上边检索出来的数据并不与实际表中的顺序一一对应，我们可认为检索出来的数据的顺序是无意义的。 

       为了更好的说明应用，假设有一个 products，有 id，价格，名称字段。

-- 默认 ASC 升序
SELECT prod_name
FROM products
ORDER BY prod_price;

-- DESC 降序
SELECT prod_name
FROM products
ORDER BY prod_price DESC;

-- 按多个列排序 先按价格, 再按名称
SELECT prod_name
FROM products
ORDER BY prod_price, prod_name;

-- 按多个列排序, DESC 只对它前面的列有作用, 即先按价格降序, 再按名称升序
SELECT prod_name
FROM products
ORDER BY prod_price DESC, prod_name;

-- DESC 降序, 然后再取第0行（0, 1 → 0 行开始取 1 行）
SELECT prod_name
FROM products
ORDER BY prod_price DESC
LIMIT 1;

       注意：ORDER BY 子句，要保证它位于 FROM 子句之后，在 WHERE 之后，如果使用 LIMIT，它必须位于 ORDER BY 之后。

数据过滤 条件/逻辑运算符

       最简单的数据过滤就是使用 WHERE 子句。WHERE 子句的条件操作符有：= 等于，!= 不等于，< 小于，<= 小于等于，>大于，>= 大于等于，BETWEEN AND 介于指定的两个值之间，IS NULL 空值检查。

-- WHERE 子句
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 5 AND 10;

       WHERE 的逻辑运算符有AND、OR、NOT IN。其具体用法为：

-- 检索出由 1003 供应商生成且价格 <= 10 的商品信息
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 AND prod_price <= 10;

-- 检索出由 1003 或 1002 供应商生成的商品
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id = 1003 OR vend_id = 1002;

       注意：AND 的操作符优先级要高于OR，在AND 和 OR 同时出现时，会优先处理AND的逻辑，所以为了避免歧义，要尽量使用括号( )。 

       IN和NOT IN 的用法：

-- 检索出由 1003 或 1002 供应商生成的商品 并排序
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name;

-- 检索出除了 1003 或 1002 供应商生成的商品 并排序
SELECT prod_id, prod_price, prod_name
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

利用通配符进行数据过滤

       LIKE + 通配符来进行过滤。必须使用 LIKE 操作符，否则就是直接相等匹配了。

-- 通配符 % 表示匹配 0 个或任意个字符 但不可以匹配 null

-- 匹配以 jet 为开头的任意字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 'jet%';

-- 匹配包含 jet 的任意字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%jet%';

-- 匹配以 s 开头 e 结尾的字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE 's%e';

-- 通配符 _ 表示匹配单个字符

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_jet';

       通配符来匹配时一般效率较低。 使用正则表达式进行匹配：

SELECT prod_id, prod_name
FROM products
WHERE prod_name REGEXP '.000';

       具体的规则可以查看正则表达式表。

汇合数据

       使用聚集函数来对数据进行汇总。MySQL提供了5种的聚集函数：

       AVG( ) 返回某列的平均值；
       COUNT( ) 返回某列的行数；
       MAX( ) 返回某列的最大值；
       MIN( ) 返回某列的最小值；
       SUM( ) 返回某列值之和。

-- AVG()
SELECT AVG(prod_price) AS avg_price
FROM products;

-- COUNT() 返回行数
-- COUNT(*) 对所有的行计数。不管各列中有什么值, 不忽略 null
SELECT COUNT(*)
FROM products;

-- COUNT(列名)  忽略null
SELECT COUNT(cust_email)
FROM customers;

       AS 起别名。其他的几个函数用法类似，不再举例。

数据分组

       对数据进行分组的理解是：假设一个供应商 A，它生产了很多产品，我们可以按照供应商来进行分组，并且使用聚集函数来统计分组信息?。

-- 按 vend_id 分组，并统计每组的行数
SELECT vend_id, count(*) 
FROM products
GROUP BY vend_id;

       过滤分组，利用 HAVING：

-- 过滤分组, 返回行数大于等于 2 的分组 并按行数排序
SELECT vend_id, count(*) AS odertotal
FROM products
GROUP BY vend_id
HAVING count(*) >= 2
ORDER BY odertotal;

使用子查询

       关系型数据库的核心就是表，假设有两个表，存储不同的信息，利用子查询。子查询就是把一条 SELECT 语句返回的结果用于另一条SELECT的WHERE子句。 子查询一般是与IN操作符结合使用的?。

-- 子查询。从 oderitems 表中查询 prod_id 的订单号, 然后返回此订单号的客户号
SELECT cust_id 
FROM orders
WHERE order_num IN (SELECT order_num
                     FROM oderitems
                     WHERE prod_id = 'TTT');

       另外，子查询可填充计算列。

联结表

       关系表的设计就是要保证把信息分解成多个表，一类数据一个表。主键：唯一标识，外键：外键为某个表中的某一列（是另一表的主键）。

-- 联结
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.prod_id

-- 用 INNER JOIN 明确表示联结
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
ON vendors.vend_id = products.prod_id

       其实，这个联结可以用子查询来实现。

组合查询

       利用 UNION 操作符，将多条 SELECT 语句组合成一个结果集。这里不详细解释了。

常见的Sql面试题

       1、查询每门功课都大于80的同学的名字：

       思路是：利用 NOT IN 排除所有小于 80 的同学。！然后利用 DISTINCT 关键字筛选出相应的不重复的名字！

SELECT DISTINCT name 
FROM student 
WHERE name NOT IN(SELCT name 
                  FROM student 
                  WHERE score < 80); 

       2、删除 除 id 不同其他别的列都相同的冗余数据：

        思路还是利用 NOT IN，先找出唯一的数据，再根据id删除。

DELETE FROM student 
WHERE id NOT IN (SELECT MIN(id)
              FROM student
              GROUP BY name, class, score);

       在 mysql 中，这样写会报错：You can’t specify target table ‘student’ for update in FROM clause，就是说：MySQL不能指定更新的目标表在 FROM 子句。其他数据库不会出现此问题。 
       解决方法：先把要删除的目标放到一张临时表中，再把要删除的条件指定到这张临时表即可。

-- 将中间结果放到临时表 a 中
DELETE FROM student 
WHERE id NOT IN (SELECT * FROM （SELECT MIN(id)
                                FROM student
                                GROUP BY name, class, score）
                 AS a);