MYSQL

2020-08-22

MYSQL

语法

使用MYSQL

SHOW DATABASES; //显示有哪些数据库
USE database_example; //使用某各数据库
SHOW TABLES; //显示有哪些表

//以下二者等价
SHOW COLUMNS FROM customers; //显示customers表结构，字段、类型等
DESCRIBE customers;

SHOW STATUS; //显示广泛的服务器状态信息
SHOW CREATE DATABASE xxx; //显示创建特定数据库或表
SHOW CREATE TABLE xxx;

SHOW GRANTS; //显示授予用户（所有用户或特定用户）的安全权限
SHOW ERRORS; //显示服务器错误或警告信息
SHOW WARNINGS;

检索数据

SELECT prod_name FROM products; //检索一列
SELECT prod_id, prod_name, prod_price FROM products; //检索多列

SELECT * FROM products; //检索所有列
SELECT DISTINCT vend_id FROM products; //检索不同值

SELECT prod_name FROM products LIMIT 5; //检索前五行
SELECT prod_name FROM products LIMIT 5,5; //指定检索开始行和要检索的行数，行索引从0开始，如果没有足够的行，检索的行数只返回能够返回的那么多行

//以下二者等价
SELECT prod_name FROM products LIMIT 3, 4;
SELECT prod_name FROM products LIMIT 4 OFFSET 3;

//使用完全限定的列名或表名
SELECT products.prod_name FROM products; //限定列名
SELECT products.prod_name FROM crachcourse.products; //限定表名

排序检索数据

//按单列排序
//注意ORDER BY子句后的字段可以是未选择的字段，即可以通过未选择的字段进行排序！！
SELECT prod_name FROM products ORDER BY prod_name; //以字段prod_name升序排列，默认字典升序

//按多列排序
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name; //首先按prod_price，而如果有多行相同的prod_price才按prod_name进行排序！！

//指定排序方向
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC; //降序

//多列排序，不同列排序顺序不同
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price DESC, prod_name(ASC); //按照prod_price降序，prod_name升序

//注意，在字典排序顺序中，大多数数据库管理系统默认A与a相同，如果需要改变这种排序顺序，ORDER BY子句做不到。必须请求数据库管理员的帮助。

//ORDER BY 与 LIMIT 组合
SELECT prod_price
FROM products
ORDER BY prod_price DESC
LIMIT 1;

过滤数据

SELECT prod_name, prod_price From products WHERE prod_price = 2.50;

//次序
//ORDER BY在FROM之后，LIMIT之前，WHERE之后

//MYSQL默认不区分大小写，所以Fuses与fuses匹配
SELECT prod_name, prod_price FROM products WHERE prod_name = 'fuses';
SELECT prod_name, prod_price FROM products WHERE prod_price < 10;
SELECT prod_name, prod_price FROM products WHERE prod_price <= 10;
SELECT vend_id, prod_name FROM products WHERE vend_id <> 1003; //与下一行等价
SELECT vend_id, prod_name FROM products WHERE vend_id != 1003;
//范围匹配，包括两端点
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;
//空值检查
SELECT prod_name FROM products WHERE prod_price IS NULL;

数据过滤

//AND
SELECT prod_id, prod_price, prod_name FROM products WHERE vend_id = 1003 AND prod_price <= 10;
//OR
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003;

//优先级AND比OR高
SELECT prod_name, prod_price FROM products WHERE vend_id = 1002 OR vend_id = 1003 AND prod_price >= 10;

//()
SELECT prod_name, prod_price FROM products WHERE (vend_id = 1002 OR vend_id = 1003) AND prod_price >= 10;

//IN 可选若干离散值
SELECT prod_name, prod_price
FROM products
WHERE vend_id IN (1002, 1003)
ORDER BY prod_name

//区间范围值，包括两端点
SELECT prod_name, prod_price
FROM products
WHERE prod_price BETWEEN 100 AND 200
ORDER BY prod_name

//NOT 否定选择的条件
SELECT prod_name, prod_price
FROM products
WHERE vend_id NOT IN (1002, 1003)
ORDER BY prod_name;

通配符过滤

//LIKE操作符
//百分号%通配符，匹配0个，1个或多个字符，不能匹配NULL
SELECT prod_id, prod_name
FROM products
WHERE prod_name, LIKE 'jet%'; //找出所有以jet开头的，与等号不同，这里区分大小写

SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '%anvil%'; //包含文本anvil，不论其之前或之后出现什么字符

SELECT prod_name
FROM products
WHERE prod_name LIKE 's%e';//中间匹配0个，1个或多个字符

//下划线_通配符, 只能匹配单个字符
SELECT prod_id, prod_name
FROM products
WHERE prod_name LIKE '_ ton anvil';

正则表达式

包含表达式的内容即可，例如prod_name REGEXP '1000'可以匹配出‘jet 1000’,’jets 1000’等

//基本字符匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000' //匹配字符1000
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name LIKE '1000' //不返回任何数据，因为LIKE匹配整个列，必须与通配符搭配使用，而REGEXP在值内进行匹配的，这是二者的差别
ORDER BY prod_name;

//匹配单个字符
SELECT prod_name
FROM products
WHERE prod_name REGEXP '.000' //匹配包含1000,2000,...,9000，例如'jet 1000'
ORDER BY prod_name

//v3.23.4后不区分大小写，为了区分大小写，可以加入BINARY关键字
SELECT prod_name
FROM produdcts
WHERE prod_name REGEXP BINARY 'JetPack .000';

//OR匹配
SELECT prod_name
FROM products
WHERE prod_name REGEXP '1000|2000'
ORDER BY prod_name;

//匹配几个字符之一, [xxx]
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[123] Ton' # ' Ton'前只匹配1或2或3
ORDER BY prod_name;

SELECT prod_name
FROM products
WHERE prod_name REGEXP '[^ 123] Ton' # 匹配除123之外的任何东西
ORDER BY prod_name;

//匹配范围，[1-5]，[a-z]
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[1-5] Ton'
ORDER BY prod_name;

//匹配特殊字符.,-,[],\等
SELECT vend_name
FROM vendors
WHERE vend_name REGEXP '\\.'
ORDER BY vend_name;

//匹配包含‘(1 stick)’、'TNT (2 sticks)'这种的字符串
//'\\( \\)'匹配括号，'[0-9]'匹配一次数字，'sticks?'问号匹配前一个字符'?'0或1次！
SELECT prod_name
FROM products
WHERE prod_name REGEXP '\\([0-9] sticks?\\)'
ORDER BY prod_name;

//匹配包含任意连续四位数字的字符串，例如‘JetPack 1000’
//'[:digit:]'匹配任意数字，'{4}'匹配前面的字符重复匹配4次，所以匹配前面的四个任意数字
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[[:digit:]]{4}'
ORDER BY prod_name;

//同上
SELECT prod_name
FROM products
WHERE prod_name REGEXP '[0-9][0-9][0-9][0-9]'
ORDER BY prod_name;

定位符

目前为止的所有例子都是匹配一个串中任意位置的文本。为了匹配特定位置的文本，需要定位符！

//从文本开始匹配以小数点或数字开头的产品
SELECT prod_name
FROM products
WHERE prod_name REGEXP '^[0-9\\.]'
ORDER BY prod_name;

简单的正则表达式测试

可以再不适用数据库表的情况下用SELECT来测试正则表达式。REGEXP检查总是返回0或1，表示不匹配或匹配。

1	SELECT 'hello' REGEXP '[0-9]'; //返回0，因为“hello”中没有数字

计算字段

拼接(concatenate)

1
2
3

SELECT Concat(vend_name, ' (', vend_country， ‘)’) # 拼接成"name(country)"格式
FROM vendors
ORDER BY vend_name;

LTrim()/RTrim()

1
2
3

SELECT Concat(LTrim(vend_name), ' (', RTrim(vend_country), ')') # RTrim()函数去掉值右边的所有空格,LTrim()函数去掉值左边的所有空格
FROM vendors
ORDER BY vend_name;

使用别名

1
2
3

SELECT Concat(LTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title # 将该新计算列的名字
FROM vendors
ORDER BY vend_name;

执行算数计算

SELECT prod_id, quantity, item_price
FROM orderitems
WHERE order_num = 20005; # 检索订单号2005中的所有物品

SELECT prod_id,
       quantity,
       item_price,
       quantity*item_price AS expanded_price # 用价格和数量计算总价
FROM orderitems
WHERE order_num = 20005;

数据处理函数

函数没有SQL的可移植性强：即不同的DBMS的实现都支持其他实现不支持的函数，而且有时差异还很大。

文本处理函数

1
2
3

SELECT vend_name, Upper(vend_name) AS vend_name_upcase  # Upper()将文本转成大写
FROM vendors
ORDER BY vend_name;

SOUNDEX()是一个将任何文本串转换为描述其语音表示的字母数字模式的算法。SOUNDEX()考虑了类似的发音字符和音节，使得能对串进行发音比较而不是字母比较。

1
2
3

SELECT cust_name, cust_contact
FROM customers
WHERE Soundex(cust_contack) = Soundex('Y Lie');

日期和时间处理函数

日期和时间采用相应的数据类型和特殊的格式存储，以便能快速和有效地排序或过滤，并且节省物理存储空间。一般，应用程序不使用用来存储日期和时间的格式，因此日期和时间函数总是被用来读取、统计和处理这些值。

首先注意MySQL使用的日期格式：yyyy-mm-dd

SELECT cust_id, order_num
FROM orders
WHERE order_date = '2005-09-01'; # 这可能对的，但是日期可能包含时间'2005-09-01 11:30:05' 这种情况就无法匹配成功

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) = '2005-09-01'; # 将日期与列中的日期部分进行比较，而不是将给出的日期与整个列值进行比较。

SELECT cust_id, order_num
FROM orders
WHERE Date(order_date) BETWEEN '2005-09-01' AND '2005-09-30'; # 匹配日期范围

SELECT cust_id, order_num
FROM orders
WHERE Year(order_date) = 2005 AND Month(order_date) = 9;

数值处理函数

汇总数据

聚集函数

AVG()函数

1 2	SELECT AVG(prod_price) AS avg_price # 计算prod_price列的均值 FROM products;

COUNT()函数

SELECT COUNT(*) AS num_cust  # 计算所有行数
FROM customers;

SELECT COUNT(cust_email) AS num_cust  # 只对具有电子邮件地址的客户计数
FROM customers;

MAX()函数

1 2	SELECT MAX(prod_price) AS max_price FROM products;

MIN()函数

1 2	SELECT MIN(prod_price) AS min_price FROM products;

SUM()函数

1
2
3

SELECT SUM(quantity) AS items_ordered  # 统计订单号为20005的quantity总和
FROM orderitems
WHERE order_num = 20005;

聚集不同值

（1）对所有的行执行计算，指定ALL参数或不给参数（因为ALL是默认行为）；
（2）只包含不同的值，指定DISTINCT参数。

1
2
3

SELECT AVG(DISTINCT prod_price) AS avg_price  # 统计产品价格平均值，只考虑不同价格
FROM products
WHERE vend_id = 1003;

组合聚集函数

SELECT COUNT(*) AS num_items,
       MIN(prod_price) AS price_min,
       MAX(prod_price) AS price_max,
       AVG(prod_price) AS price_avg
FROM products;

分组数据

数据分组

1
2
3

SELECT COUNT(*) AS num_prods  # 计算vend_id为1003的数量
FROM products
WHERE vend_id = 1003;

创建分组

GROUP BY的一些规定：
（1）GROUP BY子句可以包含任意数目的列。这使得能对分组进行嵌套，为数据分组提供更细致的控制；
（2）如果在GROUP BY子句中嵌套了分组，数据将在最后规定的分组上进行汇总。即，在建立分组时，指定的所有列都一起计算（所以不能从个别的列取回数据）；
（3）GROUP BY子句列出的每个列都必须是检索列或有效的表达式（但不能是聚集函数）。如果在SELECT中使用表达式，则必须在GROUP BY子句中指定相同的表达式。不能使用别名；
（4）除聚集计算语句外，SELECT语句中的每个列都必须在GROUP BY子句中给出；
（5）如果分组列中具有NULL值，则NULL将作为一个分组返回。如果列中有多行NULL值，它们将分为一组；
（6）GROUP BY子句必须出现在WHERE子句之后，ORDER BY子句之前。

1
2
3

SELECT vend_id, COUNT(*) AS num_prods # GROUP BY对vend_id分组，然后COUNT计算每个分组内行的数量
FROM products
GROUP BY vend_id;

过滤分组

SELECT cust_id, COUNT(*) AS orders
FROM orders
GROUP BY cust_id
HAVING COUNT(*) >= 2;   # 过滤分组

SELECT vend_id, COUNT(*) AS num_prods
FROM products
WHERE prod_price >= 10  # 先筛选出所有prod_price >=10的行，然后对vend_id进行分组，最后用HAVING对分组进行过滤
GROUP BY vend_id
HAVING COUNT(*) >= 2;

分组和排序

SELECT order_num, SUM(quantity*item_price) AS ordertotal
FROM orderitems
GROUP BY order_num
HAVING SUM(quantity*item_price) >= 50
ORDER BY ordertotal; # 先分组过滤后再排序

SELECT子句顺序

子查询

利用子查询进行过滤

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2'; # 从表orderitems中找出prod_id为‘TNT2’的所有订单编号

SELECT cust_id
FROM orders
WHERE order_num IN (20005, 20007);  # 从表orders中找出订单编号为20005，,20007的所有项

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');


SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN(SELECT cust_id
                 FROM orders
                 WHERE order_num IN(SELECT order_num
                                    FROM orderitems
                                    WHERE prod_id = 'TNT2'));

作为计算字段使用子查询

SELECT cust_name,
       cust_state,
      (SELECT COUNT(*)
       FROM orders
       WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;  # 从customers找出信息，且从orders找出对应id的订单数量

联结表

创建联结

# 等值联结
SELECT vend_name, prod_name, prod_price
FROM vendors, products
WHERE vendors.vend_id = products.vend_id  # 如果没有，则会导致笛卡尔积，最终结果行数目为第一个表的行数目与第二个表的行数目的乘积
ORDER BY vend_name, prod_name;

内部联结

# 内部联结，等同上一个例子
SELECT vend_name, prod_name, prod_price
FROM vendors INNER JOIN products
  ON vendors.vend_id = products.vend_id;

联结多个表

SELECT prod_name, vend_name, prod_price, quantity
FROM orderitems, products, vendors
WHERE products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 20005;

SELECT cust_name, cust_contact
FROM customers, orders, orderitems
WHERE customers.cust_id = orders.cust_id
  AND orderitems.order_num = orders.order_num
  AND prod_id = 'TNT2';

创建高级联结

使用表别名

SELECT Concat(RTrim(vend_name), ' (', RTrim(vend_country), ')') AS vend_title
FROM vendors
ORDER BY vend_name;

SELECT cust_name, cust_contact
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'TNT2';

使用不同类型的联结

自联结

# 子查询
SELECT prod_id, prod_name
FROM products
WHERE vend_id = (SELECT vend_id
                 FROM products
                 WHERE prod_id = 'DTNTR');

# 自联结
SELECT p1.prod_id, p1.prod_name
FROM products AS p1, products AS p2
WHERE p1.vend_id = p2.vend_id
  AND p2.prod_id = 'DTNTR';

自然联结

内部联结在联结时会产生重复的列。
自然联结，只能选择哪些唯一的列。

# 通配符只对第一个表使用。所有其他列明确列出，所以没有重复的列被检索出来
SELECT c.*, o.order_num, o.order_date,
       oi.prod_id, oi.quantity, OI.item_price
FROM customers AS c, orders AS o, orderitems AS oi
WHERE c.cust_id = o.cust_id
  AND oi.order_num = o.order_num
  AND prod_id = 'FB';

外部联结

# 只能检索出有订单的客户
SELECT customers.cust_id, orders.order_num
FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id;

# 可以检索出没有订单的客户
SELECT customers.cust_id, orders.order_num
FROM customers LEFT OUTER JOIN orders
  ON customers.cust_id = orders.cust_id;

SELECT customers.cust_id, orders.order_num
FROM customers RIGHT OUTER JOIN orders
  ON orders.cust_id = customers.cust_id;

使用带聚集函数的联结

# 检索所有客户及每个客户所下的订单数
SELECT customers.cust_name,
       customers.cust_id,
       COUNT(orders.order_num) AS num_ord
FROM customers INNER JOIN orders
  ON customers.cust_id = orders.cust_id
GROUP BY customers.cust_id;

组合查询

概念

多数SQL查询只包含从一个或多个表中返回数据的单条SELECT语句。MySQL也允许执行多个查询（多条SELECT语句），并将结果作为单个查询结果集返回。这些组合查询通常称为并或负荷查询：

在单个查询中从不同的表返回类似结构的数据；
对单个表执行多个查询，按单个查询返回数据。
创建组合查询
使用UNION
UNION必须由两条或两条以上的SELECT语句组成，语句之间用关键字UNION分割（因此，如果组合4条SELECT语句，将要使用3个UNION关键字）；
UNION中的每个查询必须包含相同的列、表达式或聚集函数（不过各个列不需要以相同的次序列出）；

列数据类型必须兼容：类型不必完全相同，但必须是DBMS可以隐含地转换的类型（例如，不同的数值类型或不同的日期类型）

SELECT vend_id, prod_id, prod_price
FROM produtcs
WHERE prod_price <= 5;

SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

包含或取消重复的行

UNION从查询结果中自动去除重复的行，这是默认行为，但是如果需要，可以改变它。事实上，如果想返回所有匹配行，可使用UNION ALL而不是UNION。

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION ALL
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002);

对组合查询结果排序

SELECT vend_id, prod_id, prod_price
FROM products
WHERE prod_price <= 5
UNION
SELECT vend_id, prod_id, prod_price
FROM products
WHERE vend_id IN (1001, 1002)
ORDER BY vend_id, prod_price;  # UNION组合查询时，只能使用一条ORDER BY子句，不存在对一部分排序，对另一部分另一种排序的方式

全文本搜索

使用全文本搜索

为了进行全文本搜索，必须索引被搜索的列，而且要随着数据的改变不断地重新索引。在对表列进行适当设计后，MySQL会自动进行所有的索引和重新索引。
在索引之后，SELECT可与Match()和Against()一起使用以实际执行搜索。

Match()：指定被搜索的列，Against()指定要使用的搜索表达式；

启用全文本搜索支持

CREATE TABLE productnotes
(
  note_id     int        NOT NULL AUTO_INCREATEMENT,
  prod_id,    char(10)   NOT NULL,
  note_date   datetime   NOT NULL,
  note_text   text       NULL,
  PRIMARY KEY(note_id),
  FULLTEXT(note_text)
)ENGINE=MyISAM;

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit');

# 同上
SELECT note_text
FROM productnotes
WHERE note_text LIKE '%rabbit%';

SELECT note_text
       Match(note_text), Against('rabbit') AS rank
FROM productnotes;

使用查询扩展

1
2
3

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('anvils' WITH QUERY EXPANSION);

布尔文本搜索

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy' IN BOOLEAN MODE);  # 未指定布尔操作符，结果与没有指定布尔方式相同

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('heavy -rope*' IN BOOLEAN MODE);  # 排除开头为rope的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+rabbit +bait' IN BOOLEAN MODE);  # 匹配包含词rabbit和bait的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('rabbit bait' IN BOOLEAN MODE);  # 匹配包含rabbit和bait中的至少一个词的行

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('"rabbit bait"' IN BOOLEAN MODE);  # 匹配短语‘rabbit bait’

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('>rabbit < carrot' IN BOOLEAN MODE);  # 匹配rabbit和carrot，增加前者的等级，降低后者的等级

SELECT note_text
FROM productnotes
WHERE Match(note_text) Against('+safe +(<combination)' IN BOOLEAN MODE);  # 匹配词safe和combination，降低后者的等级。

插入数据

插入完整的行

如果表的定义允许，则可以在INSERT操作中省略某些列。省略的列必须满足以下某个条件：
1.该列定义为允许NULL值（无值或空值）；
2.在表定义中给出默认值。这表示如果不给出值，将使用默认值。

INSERT INTO Customers
VALUES(NULL,
  'Pep E. LaPew',
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA',
  NULL,
  NULL);  # 简单但不安全，高度依赖表中列的定义次序，并且还依赖与其次序容易获得的信息

INSERT INTO customers(cust_name,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country,
  cust_contact,
  cust_email)
VALUES('Pep E. LaPew',
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA',
  NULL,
  NULL);

INSERT INTO customers(cust_name,
  cust_contact,
  cust_email,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country)
VALUES('Pep E. LaPew',
  NULL,
  NULL,
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA');  # 列名的次序不同，但插入结果正确

插入多个行

INSERT INTO customers(cust_name,
  cust_address,
  cust_city,
  cust_state,
  cust_zip,
  cust_country)
VALUES('Pep E. LaPew',
  '100 Main Street',
  'Los Angeles',
  'CA',
  '90046',
  'USA'),
  ('M. Martian',
  '42 Galaxy Way',
  'New York',
  'NY',
  '11213',
  'USA');

插入检索出的数据

INSERT INTO customers(cust_id,
    cust_contact,
    cust_email,
    cust_name,
    cust_address,
    cust_city,
    cust_state,
    cust_zip,
    cust_country)
SELECT cust_id,
       cust_contact,
       cust_email,
       cust_name,
       cust_address,
       cust_city,
       cust_state,
       cust_zip,
       cust_country
FROM custnew;

更新和删除数据

更新数据

UPDATE customers
SET cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

SELECT customers
SET cust_name = 'The Fudds',
    cust_email = 'elmer@fudd.com'
WHERE cust_id = 10005;

UPDATE customers
SET cust_email = NULL
WHERE cust_id = 10005;

删除数据

1 2	DELETE FROM customers WHERE cust_id = 10006;

创建和操纵表

创建表

CREATE TABLE customers(
  cust_id      int        NOT NULL, AUTO_INCREATEMENT,
  cust_name    char(50)   NOT NULL,
  cust_address char(50)   NULL,
  cust_city    char(50)   NULL,
  cust_state   char(50)   NULL,
  cust_zip     char(50)   NULL,
  cust_country char(50)   NULL,
  cust_contact char(50)   NULL,
  cust_email   char(50)   NULL,
  PRIMARY KEY(cust_id)
)ENGINE=InnoDB;

主键

CREATE TABLE orderitems(
  order_num   int          NOT NULL,
  order_item  int          NOT NULL,
  prod_id     char(10)     NOT NULL,
  quantity    int          NOT NULL,
  item_price decimal(8,2)  NOT NULL,
  PRIMARY KEY (order_num, order_item)   # order_num和order_item组合唯一，适合作为主键
)ENGINE=InnoDB;

AUTO_INCREATEMENT

覆盖AUTO_INCREMENT，如果一个列被指定为AUTO_INCREMENT，可以简单地在INSERT语句中指定一个值，只要它是唯一的（至今尚未使用过）即可，该值将被用来替代自动生成的值。

确定AUTO_INCREMENT值，在插入之前获得AUTO_INCREMENT列的值，用last_insert_id()函数获得这个值：SELECT 用last_insert_id()

指定默认值

CREATE TABLE orderitems(
    order_num     int           NOT NULL,
    order_item    int           NOT NULL,
    prod_id       char(10)      NOT NULL,
    quantity      int           NOT NULL  DEFAULT 1,
    item_price    decimal(8,2)  NOT NULL,
    PRIMARY KEY (order_num, order_item)
)ENGINE=InnoDB;

引擎类型

InnoDB是一个可靠的事务处理引擎，它不支持全文搜索；
MEMORY在功能等同于MyISAM，但由于数据存储在内存（不是磁盘）中，速度很快（特别适合于临时表）；
MyISAM是一个性能极高的引擎，它支持全文本搜索，但不支持事务处理。
引擎可以混用，即不同表使用不同的引擎；

缺陷：外检不能跨引擎。混用引擎类型有一个大缺陷。外键（用于强制实施引用完整性）不能跨引擎，即使用一个引擎的表不能引用具有使用不同引擎的表的外键。

更新表

ALTER TABLE vendors
ADD vend_phone CHAR(20);  # 增加一列

ALTER TABLE vendors
DROP COLUMN vend_phone;  # 删除一列

定义外键

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_orders
FOREIGN KEY(order_num) REFERENCES orders(order_num);

ALTER TABLE orderitems
ADD CONSTRAINT fk_orderitems_products FOREIGN KEY (prod_id)
REFERENCES products(prod_id);

ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (cust_id)
REFERENCES customers (cust_id);

ALTER TABLE products
ADD CONSTRAINT fk_products_vendors
FOREIGN KEY (vend_id) REFERENCES vendors(vend_id);

复杂的表结构更改一般需要手动删除过程，它涉及以下步骤：

用新的列布局创建一个新表；
使用INSERT SELECT语句从旧表赋值数据到新表。如果有必要，可使用转换函数和计算字段；
检验包含所需数据的新表；
重命名旧表（如果确定，可以删除它）
用旧表原来的名字重命名新表

根据需要，重新创建触发器、存储过程、索引和外键。

删除表以及重命名表

DROP TABLE customers2;

RENAME TABLE customers2 TO customers;

RENAME TABLE backup_customers TO customers,
             backup_vendors TO vendors,
             backup_products TO products;

LeetCode题库

176. 第二高的薪水 (Easy)

Description
编写一个 SQL 查询，获取 Employee 表中第二高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，SQL查询应该返回 200 作为第二高的薪水。如果不存在第二高的薪水，那么查询应返回 null。

+---------------------+
| SecondHighestSalary |
+---------------------+
| 200                 |
+---------------------+

Program

# Write your MySQL query statement below
SELECT(
    SELECT DISTINCT Salary
    FROM Employee
    ORDER BY Salary DESC
    LIMIT 1,1
) as SecondHighestSalary;

# Write your MySQL query statement below
SELECT(
    SELECT Salary
    FROM Employee
    GROUP BY Salary //类似于DISTINCT 现将相同Salary的组合在一起，排序就是组合后的Salary排序了。
    ORDER BY Salary DESC
    LIMIT 1,1
) as SecondHighestSalary;

177. 第N高的薪水 (Medium)

Description
编写一个 SQL 查询，获取 Employee 表中第 n 高的薪水（Salary）。

+----+--------+
| Id | Salary |
+----+--------+
| 1  | 100    |
| 2  | 200    |
| 3  | 300    |
+----+--------+

例如上述 Employee 表，n = 2 时，应返回第二高的薪水 200。如果不存在第 n 高的薪水，那么查询应返回 null。

+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200                    |
+------------------------+

Program

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
    -- 设置 N
    SET N := N-1;
  RETURN (
      # Write your MySQL query statement below.
      SELECT Salary
      FROM Employee
      GROUP BY Salary  # 同排名不跳级！
      ORDER BY Salary DESC
      LIMIT N,1
  );
END

MYSQL

语法

使用MYSQL

检索数据

排序检索数据

过滤数据

数据过滤

通配符过滤

正则表达式

定位符

简单的正则表达式测试

计算字段

拼接(concatenate)

LTrim()/RTrim()

使用别名

执行算数计算

数据处理函数

文本处理函数

日期和时间处理函数

数值处理函数

汇总数据

聚集函数

AVG()函数

COUNT()函数

MAX()函数

MIN()函数

SUM()函数

聚集不同值

组合聚集函数

分组数据

数据分组

创建分组

过滤分组

分组和排序

SELECT子句顺序

子查询

利用子查询进行过滤

作为计算字段使用子查询

联结表

创建联结

内部联结

联结多个表

创建高级联结

使用表别名

使用不同类型的联结

自联结

自然联结

外部联结

使用带聚集函数的联结

组合查询

概念

创建组合查询

使用UNION

包含或取消重复的行

对组合查询结果排序

全文本搜索

使用全文本搜索

启用全文本搜索支持

使用查询扩展

布尔文本搜索

插入数据

插入完整的行

插入多个行

插入检索出的数据

更新和删除数据

更新数据

删除数据

创建和操纵表

创建表

主键

AUTO_INCREATEMENT

指定默认值

引擎类型

更新表

定义外键

删除表以及重命名表

LeetCode题库

176. 第二高的薪水 (Easy)

177. 第N高的薪水 (Medium)