MySQL必知必会笔记总结

最新推荐文章于 2024-10-01 17:33:05 发布

qj134206

最新推荐文章于 2024-10-01 17:33:05 发布

阅读量133

点赞数 1

文章标签： mysql 笔记数据库

本文链接：https://blog.csdn.net/qj134206/article/details/134929851

版权

SQL

mysql登录

mysql -uroot -p
******

1.了解SQL

主键

数据库表应该总是定义主键，主键通常定义在表的一列上，但这并不是必需的，也可以一起使用多个列作为主键。没有主键，更新或删除表中特定行很困难

任意两行都不具有相同的主键值；
每个行都必须具有一个主键值（主键列不允许NULL值）

3.使用数据库

CREATE DATABASE database_name; -- 创建数据库
use database_name; -- 使用数据库
show databases;-- 展示全部数据库
show tables;-- 展示表
show tables from table_name;-- 展示table_name表中表列名

4.检索数据

-- col
SELECT col1,col2(*) FROM tables_;
-- DISTINCT
SELECT DISTINCT col FROM tables_; -- DISTINCT不重复关键字
-- LIMIT
SELECT col FROM tables_ LIMIT 3,5; -- 从第3行开始的5行

5.排序数据

SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price, prod_name;
-- 以商品价格从小到大排序，价格相等则以名称大小排序
-- 等价
SELECT prod_id, prod_price, prod_name FROM products ORDER BY prod_price ASC, prod_name ASC; 

-- DESC 则是从大到小
-- 选取最大值
SELECT col FROM tables_ ORDER BY col1 DESC LIMIT 1;

6.过滤数据

SELECT prod_name, prod_price FROM products WHERE prod_price = 2.5;
-- 价格在5-10之间
SELECT prod_name, prod_price FROM products WHERE prod_price BETWEEN 5 AND 10;

7.数据过滤

-- and
SELECT prod_id, prod_name, prod_price 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;
-- 合并， SQL中AND优先级更高	
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) AND prod_price >= 10;
-- NOT
SELECT prod_name, prod_price FROM products WHERE vend_id NOT IN (1002,1003) AND prod_price >= 10;

8.用通配符进行过滤

-- LIKE
-- '%'匹配任意值任意次数
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE  'jet%';
'''
+--------------+------------+
| prod_name    | prod_price |
+--------------+------------+
| JetPack 1000 |      35.00 |
| JetPack 2000 |      55.00 |
+--------------+------------+
'''
-- '_'单个字符
SELECT prod_name, prod_price FROM products WHERE prod_name LIKE '_ ton%';
'''+-------------+------------+
| prod_name   | prod_price |
+-------------+------------+
| 1 ton anvil |       9.99 |
| 2 ton anvil |      14.99 |
+-------------+------------+
'''

9.正则搜索

-- 选取包含产品名字1000的信息
SELECT prod_name FROM products WHERE prod_name REGEXP '1000';
'''
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
+--------------+
'''

-- ‘.’任意一个字符
SELECT prod_name FROM products WHERE prod_name REGEXP '.000';
'''
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
''' 
-- ‘|’或
SELECT prod_name FROM products WHERE prod_name REGEXP '1000|2000';

'''
+--------------+
| prod_name    |
+--------------+
| JetPack 1000 |
| JetPack 2000 |
+--------------+
''' 

-- ‘[]’匹配[]中的任意一个字符
SELECT prod_name FROM products WHERE prod_name REGEXP '[123] Ton';
''' 
+-------------+
| prod_name   |
+-------------+
| 1 ton anvil |
| 2 ton anvil |
+-------------+
''' 

-- ‘[a-z]’ ‘[1-9]’ ‘[a-zA-Z0-9]’匹配任意小写英文，匹配任意数字，匹配任意字符同‘.’
SELECT prod_name FROM products WHERE prod_name REGEXP '[1-5] Ton';
''' 
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
'''

匹配特殊字符

匹配数量

匹配位置

-- 匹配以小数点开头或数字开头的名字
SELECT prod_name FROM products WHERE prod_name REGEXP '^[0-9//.]';

"""
+--------------+
| prod_name    |
+--------------+
| .5 ton anvil |
| 1 ton anvil  |
| 2 ton anvil  |
+--------------+
"""

10.创建计算字段

-- Concat 拼接
SELECT Concat(vend_name,'(',vend_country,')') FROM vendors;

'''+----------------------------------------+
| Concat(vend_name,'(',vend_country,')') |
+----------------------------------------+
| Anvils R Us(USA)                       |
| LT Supplies(USA)                       |
| ACME(USA)                              |
| Furball Inc.(USA)                      |
| Jet Set(England)                       |
| Jouets Et Ours(France)                 |
+----------------------------------------+
'''

-- Trim 去空格（LTrim，RTrim（去左右空格））
SELECT Concat(Trim(vend_name),'(',vend_country,')') FROM vendors;

-- 算术操作 + 加 - 减 * 乘 / 除
SELECT prod_id, quantity, item_price, item_price*quantity AS prices FROM orderitems LIMIT 5;
'''
+---------+----------+------------+--------+
| prod_id | quantity | item_price | prices |
+---------+----------+------------+--------+
| ANV01   |       10 |       5.99 |  59.90 |
| ANV02   |        3 |       9.99 |  29.97 |
| TNT2    |        5 |      10.00 |  50.00 |
| FB      |        1 |      10.00 |  10.00 |
| JP2000  |        1 |      55.00 |  55.00 |
+---------+----------+------------+--------+
'''

11.函数

文本处理函数

Left() -- 返回串左边的字符
Length() -- 返回串的长度
Locate() -- 找出串的一个子串
Lower() -- 将串转换为小写
LTrim() -- 去掉串左边的空格
Right() -- 返回串右边的字符
RTrim() -- 去掉串右边的空格
Soundex() -- 返回串的SOUNDEX值
SubString() -- 返回子串的字符
Upper() -- 将串转换为大写

日期时间处理函数

AddDate() -- 增加一个日期（天、周等）
AddTime() -- 增加一个时间（时、分等）
CurDate() -- 返回当前日期
CurTime() -- 返回当前时间
Date() -- 返回日期时间的日期部分
DateDiff() -- 计算两个日期之差
Date_Add() -- 高度灵活的日期运算函数
Date_Format() -- 返回一个格式化的日期或时间串
Day() -- -- 返回一个日期的天数部分
DayOfWeek() -- 对于一个日期，返回对应的星期几
Hour() -- 返回一个时间的小时部分
Minute() -- 返回一个时间的分钟部分
Month() -- 返回一个日期的月份部分
Now() -- 返回当前日期和时间
Second() -- 返回一个时间的秒部分
Time() -- 返回一个日期时间的时间部分
Year() -- 返回一个日期的年份部分

SELECT order_date FROM orders;
+---------------------+
| order_date          |
+---------------------+
| 2005-09-01 00:00:00 |
| 2005-09-12 00:00:00 |
| 2005-09-30 00:00:00 |
| 2005-10-03 00:00:00 |
| 2005-10-08 00:00:00 |
+---------------------+
-- 查找日期请使用Date()
SELECT cust_id,order_num FROM orders WHERE Date(order_date) = '2005-09-01';
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
+---------+-----------+

数值处理

Abs() -- 返回一个数的绝对值
Cos() -- 返回一个角度的余弦
Exp() -- 返回一个数的指数值
Mod() -- 返回除操作的余数
Pi() -- 返回圆周率
Rand() -- 返回一个随机数
Sin() -- 返回一个角度的正弦
Sqrt() -- 返回一个数的平方根
Tan() -- 返回一个角度的正切

12.汇总数据

聚集函数

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

SELECT AVG(prod_price) AS avg_price FROM products;
'''
+-----------+
| avg_price |
+-----------+
| 16.133571 |
+-----------+
'''

SELECT COUNT(*) FROM customers;
'''
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
'''

SELECT COUNT(*) AS nums, AVG(prod_price) AS avg_p, MAX(prod_price) AS max_p, MIN(prod_price) AS min_p FROM products;
'''
+------+-----------+-------+-------+
| nums | avg_p     | max_p | min_p |
+------+-----------+-------+-------+
|   14 | 16.133571 | 55.00 |  2.50 |
+------+-----------+-------+-------+
'''

13.数据分组

SELECT vend_id, COUNT(*) AS nums FROM products GROUP BY vend_id;
+---------+------+
| vend_id | nums |
+---------+------+
|    1001 |    3 |
|    1002 |    2 |
|    1003 |    7 |
|    1005 |    2 |
+---------+------+

-- 过滤 having处理分组后的数据，where是分组前的数据
SELECT vend_id, COUNT(*) AS nums FROM products GROUP BY vend_id HAVING COUNT(*) > 2;
+---------+------+
| vend_id | nums |
+---------+------+
|    1001 |    3 |
|    1003 |    7 |
+---------+------+

SELECT vend_id, COUNT(*) AS nums FROM products  WHERE prod_price >=10  GROUP BY vend_id HAVING COUNT(*) > 2;
+---------+------+
| vend_id | nums |
+---------+------+
|    1003 |    4 |
+---------+------+

-- 排序
SELECT order_num, SUM(quantity*item_price) AS totalPrices FROM orderitems GROUP BY order_num HAVING totalPrices >= 50;
+-----------+-------------+
| order_num | totalPrices |
+-----------+-------------+
|     20005 |      149.87 |
|     20006 |       55.00 |
|     20007 |     1000.00 |
|     20008 |      125.00 |
+-----------+-------------+

SELECT order_num, SUM(quantity*item_price) AS totalPrices FROM orderitems GROUP BY order_num HAVING totalPrices >= 50 ORDER BY totalPrices;
+-----------+-------------+
| order_num | totalPrices |
+-----------+-------------+
|     20006 |       55.00 |
|     20008 |      125.00 |
|     20005 |      149.87 |
|     20007 |     1000.00 |
+-----------+-------------+

SELECT子句顺序

14.使用子查询

-- (1) 检索包含物品TNT2的所有订单的编号。(2) 检索具有前一步骤列出的订单编号的所有客户的ID
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;
+----------------+------------+--------+
| cust_name      | cust_state | orders |
+----------------+------------+--------+
| Coyote Inc.    | MI         |      2 |
| Mouse House    | OH         |      0 |
| Wascals        | IN         |      1 |
| Yosemite Place | AZ         |      1 |
| E Fudd         | IL         |      1 |
+----------------+------------+--------+

15.联结表

外键：外键为某个表中的一列，它包含另一个表的主键值，定义了两个表之间的关系

-- 相同名称的列要标明表名
SELECT prod_name, vend_name, vendors.vend_id, products.vend_id FROM products, vendors WHERE products.vend_id = vendors.vend_id;
+----------------+-------------+---------+---------+
| prod_name      | vend_name   | vend_id | vend_id |
+----------------+-------------+---------+---------+
| .5 ton anvil   | Anvils R Us |    1001 |    1001 |
| 1 ton anvil    | Anvils R Us |    1001 |    1001 |
| 2 ton anvil    | Anvils R Us |    1001 |    1001 |
| Fuses          | LT Supplies |    1002 |    1002 |
| Oil can        | LT Supplies |    1002 |    1002 |
| Detonator      | ACME        |    1003 |    1003 |
| Bird seed      | ACME        |    1003 |    1003 |
| Carrots        | ACME        |    1003 |    1003 |
| Safe           | ACME        |    1003 |    1003 |
| Sling          | ACME        |    1003 |    1003 |
| TNT (1 stick)  | ACME        |    1003 |    1003 |
| TNT (5 sticks) | ACME        |    1003 |    1003 |
| JetPack 1000   | Jet Set     |    1005 |    1005 |
| JetPack 2000   | Jet Set     |    1005 |    1005 |
+----------------+-------------+---------+---------+
-- 等效上面
-- INNER JOIN tables ON 条件
SELECT prod_name, vend_name, vendors.vend_id, products.vend_id FROM products INNER JOIN vendors ON products.vend_id = vendors.vend_id;

16.高级联结

表别名

SELECT cust_name, cust_contact FROM customers AS c, orders AS o, orderitems AS os WHERE c.cust_id = o.cust_id AND os.order_num = o.order_num AND prod_id = 'TNT2';
+----------------+--------------+
| cust_name      | cust_contact |
+----------------+--------------+
| Coyote Inc.    | Y Lee        |
| Yosemite Place | Y Sam        |
+----------------+--------------+

自联结

-- 发现某物品（其ID为DTNTR）存在问题，因此想查询生产该物品的供应商生产的其他物品
SELECT prod_id, prod_name FROM products WHERE vend_id = (SELECT vend_id FROM products WHERE prod_id = 'DTNTR');
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+
-- 同上，p1作搜索表， p2作索引表
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';
+---------+----------------+
| prod_id | prod_name      |
+---------+----------------+
| DTNTR   | Detonator      |
| FB      | Bird seed      |
| FC      | Carrots        |
| SAFE    | Safe           |
| SLING   | Sling          |
| TNT1    | TNT (1 stick)  |
| TNT2    | TNT (5 sticks) |
+---------+----------------+

外部联结

外部联结包括没有关联行的行。在使用OUTER JOIN语法时，必须使用RIGHT或LEFT关键字指定包括其所有行的表（RIGHT指出的是OUTER JOIN右边的表，而LEFT 指出的是OUTER JOIN左边的表）

-- 内部联结
SELECT customers.cust_id, orders.order_num FROM customers INNER JOIN orders on customers.cust_id = orders.cust_id;
+---------+-----------+
| cust_id | order_num |
+---------+-----------+
|   10001 |     20005 |
|   10001 |     20009 |
|   10003 |     20006 |
|   10004 |     20007 |
|   10005 |     20008 |
+---------+-----------+

-- 外部联结
SELECT customers.cust_id, orders.order_num FROM customers LEFT OUTER JOIN orders on customers.cust_id = orders.cust_id;

17.组合查询

-- union关键字

SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price <= 5;
+---------+---------------+------------+
| prod_id | prod_name     | prod_price |
+---------+---------------+------------+
| FC      | Carrots       |       2.50 |
| FU1     | Fuses         |       3.42 |
| SLING   | Sling         |       4.49 |
| TNT1    | TNT (1 stick) |       2.50 |
+---------+---------------+------------+

 SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id in (1001,1002);
+---------+--------------+------------+
| prod_id | prod_name    | prod_price |
+---------+--------------+------------+
| ANV01   | .5 ton anvil |       5.99 |
| ANV02   | 1 ton anvil  |       9.99 |
| ANV03   | 2 ton anvil  |      14.99 |
| FU1     | Fuses        |       3.42 |
| OL1     | Oil can      |       8.99 |
+---------+--------------+------------+

SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id in (1001,1002)
    -> UNION
    -> SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price <= 5;
+---------+---------------+------------+
| prod_id | prod_name     | prod_price |
+---------+---------------+------------+
| ANV01   | .5 ton anvil  |       5.99 |
| ANV02   | 1 ton anvil   |       9.99 |
| ANV03   | 2 ton anvil   |      14.99 |
| FU1     | Fuses         |       3.42 |
| OL1     | Oil can       |       8.99 |
| FC      | Carrots       |       2.50 |
| SLING   | Sling         |       4.49 |
| TNT1    | TNT (1 stick) |       2.50 |
+---------+---------------+------------+
-- UNION ALL使用UNION ALL，MySQL不取消重复的行


-- 排序 order对全体select有效
 SELECT prod_id, prod_name, prod_price FROM products WHERE vend_id in (1001,1002)
    -> UNION
    -> SELECT prod_id, prod_name, prod_price FROM products WHERE prod_price <= 5
    -> ORDER BY prod_price;
+---------+---------------+------------+
| prod_id | prod_name     | prod_price |
+---------+---------------+------------+
| FC      | Carrots       |       2.50 |
| TNT1    | TNT (1 stick) |       2.50 |
| FU1     | Fuses         |       3.42 |
| SLING   | Sling         |       4.49 |
| ANV01   | .5 ton anvil  |       5.99 |
| OL1     | Oil can       |       8.99 |
| ANV02   | 1 ton anvil   |       9.99 |
| ANV03   | 2 ton anvil   |      14.99 |
+---------+---------------+------------+

18.全文本搜索

使用两个函数Match()和Against()执行全文本搜索，其中Match()指定被搜索的列，Against()指定要使用的搜索表达式

SELECT note_text FROM productnotes WHERE Match(note_text) Against('rabbit');
+------------------------------------------------------------------------------------------+
| note_text                                                                                                            |
+------------------------------------------------------------------------------------------+
| Customer complaint: rabbit has been able to detect trap, food apparently less effective now.                         |
| Quantity varies, sold by the sack load.
All guaranteed to be bright and orange, and suitable for use as rabbit bait. |
+------------------------------------------------------------------------------------------+
-- Match(note_text)指示MySQL针对指定的列进行搜索，Against('rabbit')指定词rabbit作为搜索文本。由于有两行包含词rabbit，这两个行被返回

-- like关键字匹配，等同
SELECT note_text FROM productnoteS WHERE note_text LIKE '%RABBIT%';

Match() Against() 返回的是匹配等级指数，为零则说明不存在，会按照指数大小对返回内容排序

-- 扩展
-- with query expansion 若返回值很少则返回一些相关的
Against('str', with query expansion);
-- IN BOOLEAN MODE 不需要FULLTEX索引，但速度相对慢

19.数据插入

对每个列必须提供一个值。如果某个列没有值（如上面的cust_contact和cust_email列），应该使用NULL 值（假定表允许对该列指定空值）

INSERT INTO customers VALUES(NULL, 'Pep E. LaPew','100 Main Street', 'Los Angeles', 'CA', '90046', 'USA',NULL,NULL); -- 易失误

INSERT INTO customers(col1_name, col2_name,....) VALUES(col1_value, col2_value, ......);
-- 列名和列值对应；可以调换列的顺序，可以省略列

-- 插入多条列
INSERT INTO customers(col1_name, col2_name,....) VALUES(col1_value1, col2_value1, ......),
(col1_value2, col2_value2, ......),......;

-- 插入搜索
INSERT INTO customers(col1_name, col2_name,....) SELECTcol1_name, col2_name,.... FROM new_table;

20.更新与删除数据

更新

UPDATE语句

要更新的表 update
列名和它们的新值 set
确定要更新行的过滤条件 where

UPDATE customers SET cust_email = 'elme@fudd.com' WHERE cust_id = 10005;
 
 -- 更新多个列
UPDATE customers SET cust_email = 'elme@fudd.com', cust_name = 'Tommer' WHERE cust_id = 10005;

IGNORE关键字如果用UPDATE语句更新多行，并且在更新这些行中的一行或多行时出一个现错误，则整个UPDATE操作被取消（错误发生前更新的所有行被恢复到它们原来的值）。为即使是发生错误，也继续进行更新，可使用IGNORE关键字

删除

删除语句

要删除的表 delete from
确定要更新行的过滤条件 WHERE

DELETE FROM customers WHERE cust_id = 10006;

如果想从表中删除所有行，不要使用DELETE。可使用TRUNCATE TABLE语句，它完成相同的工作，但速度更快

21.创建和操纵表

创建

新表的名字，在关键字CREATE TABLE之后给出；
表列的名字和定义，用逗号分隔。

表的主键可以在创建表时用 PRIMARY KEY关键字指定
如果你仅想在一个表不存在时创建它，应该在表名后给出IF NOT EXISTS。
每个表列或者是NULL列，或者是NOT NULL列，这种状态在创建时由表的定义规定。

NOT NULL表示不允许插入空值

主键定义

设定默认值

引擎类型

MySQL具有多种引擎。它打包多个引擎，这些引擎都隐藏在MySQL服务器内，全都能执行CREATE TABLE和SELECT 等命令，ENGINE=InnoDB可以指定使用InnoDB引擎

更新表

-- 添加列
ALTER TABLE vendors ADD vend_phone CHAR(20);

-- 删除列
ALTER TABLE vendors DROP vend_phone;

-- 定义外键
ALTER TABLE 子表
ADD FOREIGN KEY (外键列) REFERENCES 关联表名(关联表主键列);

删除表

DROP TABLE table1;

重命名

RENAME TABLE table1 TO table2;

22.视图

介绍

作为视图，它不包含表中应该有的任何列或数据，它包含的是一个SQL查询

使用视图

视图用CREATE VIEW语句来创建。
使用SHOW CREATE VIEW viewname；来查看创建视图的语句。
用DROP删除视图，其语法为DROP VIEW viewname;。
更新视图时，可以先用DROP再用CREATE，也可以直接用CREATE OR REPLACE VIEW。如果要更新的视图不存在，则第2条更新语句会创建一个视图；如果要更新的视图存在，则第2条更新语句会替换原有视图。

CREATE VIEW productcustomers AS SELECT c.cust_id, prod_id   ,c.cust_contact       FROM customers AS c, orders AS o, orderitems AS os WHERE c.cust_id = o.cust_id AND os.order_num = o.order_num;
SELECT * FROM productcustomers;
+---------+---------+--------------+
| cust_id | prod_id | cust_contact |
+---------+---------+--------------+
|   10001 | ANV01   | Y Lee        |
|   10001 | ANV02   | Y Lee        |
|   10001 | TNT2    | Y Lee        |
|   10001 | FB      | Y Lee        |
|   10001 | FB      | Y Lee        |
|   10001 | OL1     | Y Lee        |
|   10001 | SLING   | Y Lee        |
|   10001 | ANV03   | Y Lee        |
|   10003 | JP2000  | Jim Jones    |
|   10004 | TNT2    | Y Sam        |
|   10005 | FC      | E Fudd       |
+---------+---------+--------------+
-- --------------
 SELECT cust_id, cust_contact FROM productcustomers WHERE prod_id = 'TNT2';
+---------+--------------+
| cust_id | cust_contact |
+---------+--------------+
|   10001 | Y Lee        |
|   10004 | Y Sam        |
+---------+--------------+

更新视图中的数据：

默认情况下，视图是可更新的，但是有一些条件需要满足：

视图中包含所有要更新的列。
视图中的 SELECT 语句不包含以下元素：聚合函数、DISTINCT、GROUP BY、HAVING、UNION 或子查询。

sqlCopy code-- 更新视图中的数据
UPDATE 视图名
SET 列1 = 值1, 列2 = 值2
WHERE 条件;

23.存储过程

存储过程（Stored Procedure）是一组预编译的 SQL 语句的集合，它被存储在数据库中，并可以通过一个单独的调用来执行。在 MySQL 中，存储过程允许你封装一系列 SQL 语句并通过一个名称来调用它们，提高了数据库的性能、可维护性和安全性。

创建存储过程：

DELIMITER //  -- 告诉mysql以 // 作结束符号

CREATE PROCEDURE 存储过程名(IN 参数1 数据类型, IN 参数2 数据类型, ...)
BEGIN
    -- 存储过程的 SQL 语句
END //

DELIMITER ; -- 换;作结束符号

存储过程的参数：

IN 参数：用于传递输入值。
OUT 参数：用于存储输出值。
INOUT 参数：同时用于输入和输出。

CREATE PROCEDURE ExampleProcedure(IN input_param INT, OUT output_param INT)
BEGIN
    -- 存储过程的 SQL 语句
END;
-- IN参数传值/变量名， OUT参数传@+变量名
CALL ExampleProcedure(params, @params);

24.游标

在 MySQL 存储过程和函数中，可以使用光标（Cursor）来处理查询结果集。光标是一个指向查询结果的游标，它允许你逐行处理查询结果，类似于迭代器的概念。

DECLARE cursor_name CURSOR FOR SELECT 列1, 列2, ... FROM 表 WHERE 条件;

-- open
OPEN cursor_name;

-- close
OPEN employee_cursor;

-- FETCH返回游标所在的行并迭代到下一行
FETCH cursor_name INTO 变量1, 变量2, ...;

25.触发器

触发器（Trigger）是一种数据库对象，它在特定的数据库事件（例如插入、更新或删除）发生时自动执行一系列的 SQL 语句

CREATE TRIGGER 触发器名
[BEFORE | AFTER] 触发事件  --  [BEFORE | AFTER] 触发事件前|后
ON 表名
FOR EACH ROW  -- 触发器对每一行记录都会执行，即每次触发事件都会对受影响的行执行触发器中的 SQL 语句。
触发器的 SQL 语句;

触发事件：

BEFORE INSERT：在插入操作之前触发。
AFTER INSERT：在插入操作之后触发。
BEFORE UPDATE：在更新操作之前触发。
AFTER UPDATE：在更新操作之后触发。
BEFORE DELETE：在删除操作之前触发。
AFTER DELETE：在删除操作之后触发。

NEW 和 OLD：

在触发器中，可以使用 NEW 和 OLD 来引用新插入或更新的行以及旧的行。

INSERT事件可以用NEW来表示插入的数据；
DELETE事件可以用OLD来表示删除的数据；
UPDATE事件用可以NEW和OLD表示更新的数据和原来的数据；

例如，在 BEFORE INSERT 触发器中，使用 NEW 表示新插入的行。

sqlCopy codeCREATE TRIGGER before_insert_example
BEFORE INSERT
ON example_table
FOR EACH ROW
SET NEW.column_name = UPPER(NEW.column_name);

删除触发器：

sqlCopy code
DROP TRIGGER [IF EXISTS] 触发器名;

例如：

sqlCopy code
DROP TRIGGER IF EXISTS before_insert_employee;

26.管理事务处理

事务处理（transaction processing）可以用来维护数据库的完整性，它保证成批的MySQL操作要么完全执行，要么完全不执行。类似锁机制

术语

在使用事务和事务处理时，有几个关键词汇反复出现。下面是关于事务处理需要知道的几个术语：

事务（transaction）指一组SQL语句；
回退（rollback）指撤销指定SQL语句的过程；
提交（commit）指将未存储的SQL语句结果写入数据库表；
保留点（savepoint）指事务处理中设置的临时占位符（placeholder），你可以对它发布回退（与回退整个事务处理不同）。

ROLLBACK

SELECT * FROM table1; -- table1不为空
START TRANSACTION; -- 开始事务
DELETE FROM table1; -- 删除table1
SELECT * FROM table1; -- table1为空
ROLLBACK; -- 返回到开始事务
SELECT * FROM table1; -- table1为空

SAVEPOINT

START TRANSACTION; 
-- ....
SAVEPOINT point_name;
-- ....
ROLLBACK TO point_name; -- 返回到point_name

事务处理用来管理INSERT、UPDATE和 DELETE语句。你不能回退SELECT语句。（这样做也没有什么意义。）你不能回退CREATE或DROP操作。事务处理块中可以使用这两条语句，但如果你执行回退，它们不会被撤销。

COMMIT

一般的MySQL语句都是直接针对数据库表执行和编写的。这就是所谓的隐含提交（implicit commit），即提交（写或保存）操作是自动进行的。但是，在事务处理块中，提交不会隐含地进行。为进行明确的提交，使用COMMIT语句

START TRANSACTION; -- 开始事务
DELETE FROM table1 WHERE y_id = 2023; -- 删除表1中id为2023的
DELETE FROM table2 WHERE y_id = 2023; -- 删除表2中id为2023的
COMMIT; -- 上面两个删除语句都之前执行才提交删除语句

在数据库中，字符集（Character Set）和校对顺序（Collation）是两个重要的概念，它们影响着存储和比较字符串的方式。以下是关于字符集和校对顺序的一些基本知识点：

27.全球化和本地化

字符集（Character Set）：
- 定义： 字符集是一组字符的集合，用于表示文字和符号。每个字符集都有一个唯一的名称，例如，UTF-8、GBK、Latin1 等。
- UTF-8： 是一种支持全球范围内的字符集，最常用于存储和传输文本数据。
- GBK： 是中文编码字符集，支持简体中文、繁体中文等。
- Latin1： 是一个较老的字符集，主要用于欧洲语言。
校对顺序（Collation）：
- 定义： 校对顺序规定了在排序和比较字符串时所使用的规则，包括字符的顺序、大小写敏感性等。
- 案例敏感和不敏感： 校对顺序可以是大小写敏感（Case-Sensitive）或大小写不敏感（Case-Insensitive）的，即是否区分大小写。
- 排序规则： 不同的校对顺序可以定义不同的字符排序规则，影响字符串的比较和排序结果。

MySQL 中的字符集和校对顺序：

设置数据库字符集：

CREATE DATABASE database_name CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

设置表的字符集和校对顺序：

CREATE TABLE table_name1 (
    column_name VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci
);

修改表的字符集和校对顺序：

ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

查看数据库的字符集和校对顺序：

SHOW CREATE DATABASE database_name;

SHOW CREATE TABLE table_name;

字符集和校对顺序的选择：
- 全球化应用： 对于支持多语言的应用，通常选择 UTF-8 字符集，因为它能够覆盖世界上大多数语言的字符。
- 性能考虑： 不同的字符集和校对顺序可能会对查询性能产生影响，需要根据具体应用场景选择。
- 大小写敏感性： 根据应用需求，选择大小写敏感或不敏感的校对顺序。
- 存储空间： 不同的字符集和校对顺序可能占用不同的存储空间，需要根据数据量和存储需求选择。

字符集和校对顺序的选择需要根据具体的应用需求和国际化考虑。正确设置字符集和校对顺序可以确保数据存储的正确性和应用的正常运行。在设计数据库时，了解和理解字符集和校对顺序的概念非常重要。

28.安全管理

管理用户

-- 查看用户
USE mysql；
SELECT user from user;
'''
+------------------+
| user             |
+------------------+
| mysql.infoschema |
| mysql.session    |
| mysql.sys        |
| root             |
+------------------+
'''
-- 创建用户
CREATE USER  root1 IDENTIFIED BY '123456';
-- 更改用户名
REMOVE USER root1 TO root2;
-- 删除用户
DROP USER root2;
-- 展示用户权限
SHOW GRANTS FOR root1;
'''
+-----------------------------------+
| Grants for root1@%                |
+-----------------------------------+
| GRANT USAGE ON *.* TO `root1`@`%` |
+-----------------------------------+
'''
-- 添加root1查询example表的权限
GRANTS SELECT ON example.* TO root1;
SHOW GRANTS FOR root1;
'''
+--------------------------------------------+
| Grants for root1@%                         |
+--------------------------------------------+
| GRANT USAGE ON *.* TO `root1`@`%`          |
| GRANT SELECT ON `example`.* TO `root1`@`%` |
+--------------------------------------------+
'''
-- 更改密码
SET PASSWORD FOR root1 = Password('new password');

GRANT和REVOKE可在几个层次上控制访问权限：

整个服务器，使用GRANT ALL和REVOKE ALL；
整个数据库，使用ON database.*；
特定的表，使用ON database.table；
特定的列；
特定的存储过程

30.改善性能

首先，MySQL（与所有DBMS一样）具有特定的硬件建议。在学习和研究MySQL时，使用任何旧的计算机作为服务器都可以。但对用于生产的服务器来说，应该坚持遵循这些硬件建议。  一般来说，关键的生产DBMS应该运行在自己的专用服务器上。
MySQL是用一系列的默认设置预先配置的，从这些设置开始通常是很好的。但过一段时间后你可能需要调整内存分配、缓冲区大小等。（为查看当前设置，可使用SHOW VARIABLES;和SHOW STATUS;。）
MySQL一个多用户多线程的DBMS，换言之，它经常同时执行多个任务。如果这些任务中的某一个执行缓慢，则所有请求都会执行缓慢。如果你遇到显著的性能不良，可使用SHOW PROCESSLIST 显示所有活动进程（以及它们的线程ID和执行时间）。你还可以用KILL命令终结某个特定的进程（使用这个命令需要作为管理员登录）。
总是有不止一种方法编写同一条SELECT语句。应该试验联结、并、子查询等，找出最佳的方法。  使用EXPLAIN语句让MySQL解释它将如何执行一条SELECT语句。
一般来说，存储过程执行得比一条一条地执行其中的各条MySQL 语句快。
应该总是使用正确的数据类型。
决不要检索比需求还要多的数据。换言之，不要用SELECT *（除非你真正需要每个列）。
有的操作（包括INSERT）支持一个可选的DELAYED关键字，如果使用它，将把控制立即返回给调用程序，并且一旦有可能就实际执行该操作。
在导入数据时，应该关闭自动提交。你可能还想删除索引（包括 FULLTEXT索引），然后在导入完成后再重建它们。
必须索引数据库表以改善数据检索的性能。确定索引什么不是一件微不足道的任务，需要分析使用的SELECT语句以找出重复的 WHERE和ORDER BY子句。如果一个简单的WHERE子句返回结果所花的时间太长，则可以断定其中使用的列（或几个列）就是需要索引的对象。
你的SELECT语句中有一系列复杂的OR条件吗？通过使用多条 SELECT语句和连接它们的UNION语句，你能看到极大的性能改进。
索引改善数据检索的性能，但损害数据插入、删除和更新的性能。如果你有一些表，它们收集数据且不经常被搜索，则在有必要之前不要索引它们。（索引可根据需要添加和删除。
LIKE很慢。一般来说，最好是使用FULLTEXT而不是LIKE。
数据库是不断变化的实体。一组优化良好的表一会儿后可能就面目全非了。由于表的使用和内容的更改，理想的优化和配置也会改变。
最重要的规则就是，每条规则在某些条件下都会被打破。

附录：MySQL数据类型

D.1 串数据类型

有两种基本的串类型，分别为定长串和变长串

CHAR -- 1～255个字符的定长串。它的长度必须在创建时指定，否则MySQL假定为CHAR(1)
ENUM -- 接受最多64 K个串组成的一个预定义集合的某个串
LONGTEXT -- 与TEXT相同，但最大长度为4 GB 
MEDIUMTEXT -- 与TEXT相同，但最大长度为16 K 
SET -- 接受最多64个串组成的一个预定义集合的零个或多个串
TEXT -- 最大长度为64 K的变长文本
TINYTEXT -- 与TEXT相同，但最大长度为255字节
VARCHAR -- 长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）

D.2 数值数据类型

所有数值数据类型（除BIT和BOOLEAN外）都可以有符号或无符号。有符号数值列可以存储正或负的数值，无符号数值列只能存储正数。默认情况为有符号，但如果你知道自己不需要存储负值，可以使用UNSIGNED关键字，这样做将允许你存储两倍大小的值

BIT -- 位字段，1～64位。（在MySQL 5之前，BIT在功能上等价于 TINYINT
BIGINT -- 整数值，支持9223372036854775808～9223372036854775807（如果是UNSIGNED，为0～18446744073709551615）的数
BOOLEAN -- （或BOOL） 布尔标志，或者为0或者为1，主要用于开/关（on/off）标志
DECIMAL-- （或DEC） 精度可变的浮点值
DOUBLE  -- 双精度浮点值
FLOAT -- 单精度浮点值
INT -- （或INTEGER） 整数值，支持2147483648～2147483647（如果是UNSIGNED，为0～4294967295）的数
MEDIUMINT -- 整数值，支持8388608～8388607（如果是UNSIGNED，为0～16777215）的数
REAL -- 4字节的浮点值
SMALLINT -- 整数值，支持32768～32767（如果是UNSIGNED，为0～65535）的数
TINYINT -- 整数值，支持128～127（如果为UNSIGNED，为0～255）的数

D.3 日期和时间数据类型

DATE  -- 表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD
DATETIME  -- DATE和TIME的组合
TIMESTAMP  -- 功能和DATETIME相同（但范围较小）
TIME  -- 格式为HH:MM:SS
YEAR  -- 用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年

D.4 二进制数据类型

BLOB Blob最大长度为64 KB 
MEDIUMBLOB Blob最大长度为16 MB 
LONGBLOB Blob最大长度为4 GB 
TINYBLOB Blob最大长度为255字节

– 长度可变，最多不超过255字节。如果在创建时指定为VARCHAR(n)，则可存储0到n个字符的变长串（其中n≤255）


### D.2 数值数据类型

所有数值数据类型（除BIT和BOOLEAN外） 都可以有符号或无符号。有符号数值列可以存储正或负的数 值，无符号数值列只能存储正数。默认情况为有符号，但如 果你知道自己不需要存储负值，可以使用UNSIGNED关键字， 这样做将允许你存储两倍大小的值

```mysql
BIT -- 位字段，1～64位。（在MySQL 5之前，BIT在功能上等价于 TINYINT
BIGINT -- 整数值，支持9223372036854775808～9223372036854775807（如果是UNSIGNED，为0～18446744073709551615）的数
BOOLEAN -- （或BOOL） 布尔标志，或者为0或者为1，主要用于开/关（on/off）标志
DECIMAL-- （或DEC） 精度可变的浮点值
DOUBLE  -- 双精度浮点值
FLOAT -- 单精度浮点值
INT -- （或INTEGER） 整数值，支持2147483648～2147483647（如果是UNSIGNED，为0～4294967295）的数
MEDIUMINT -- 整数值，支持8388608～8388607（如果是UNSIGNED，为0～16777215）的数
REAL -- 4字节的浮点值
SMALLINT -- 整数值，支持32768～32767（如果是UNSIGNED，为0～65535）的数
TINYINT -- 整数值，支持128～127（如果为UNSIGNED，为0～255）的数

D.3 日期和时间数据类型

DATE  -- 表示1000-01-01～9999-12-31的日期，格式为YYYY-MM-DD
DATETIME  -- DATE和TIME的组合
TIMESTAMP  -- 功能和DATETIME相同（但范围较小）
TIME  -- 格式为HH:MM:SS
YEAR  -- 用2位数字表示，范围是70（1970年）～69（2069年），用4位数字表示，范围是1901年～2155年

D.4 二进制数据类型

BLOB Blob最大长度为64 KB 
MEDIUMBLOB Blob最大长度为16 MB 
LONGBLOB Blob最大长度为4 GB 
TINYBLOB Blob最大长度为255字节

qj134206

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫

MySQL必知必会笔记总结

SQL

mysql登录

1.了解SQL

主键

3.使用数据库

4.检索数据

5.排序数据

6.过滤数据

7.数据过滤

8.用通配符进行过滤

9.正则搜索

10.创建计算字段

11.函数

文本处理函数

日期时间处理函数

数值处理

12.汇总数据

聚集函数

13.数据分组

SELECT子句顺序

14.使用子查询

15.联结表

16.高级联结

表别名

自联结

外部联结

17.组合查询

18.全文本搜索

19.数据插入

20.更新与删除数据

更新

UPDATE语句

删除

删除语句

21.创建和操纵表

创建

主键定义

设定默认值

引擎类型

更新表

删除表

重命名

22.视图

介绍

使用视图

更新视图中的数据：

23.存储过程

24.游标

25.触发器

触发事件：

NEW 和 OLD：

删除触发器：

26.管理事务处理

术语

ROLLBACK

SAVEPOINT

COMMIT

27.全球化和本地化

28.安全管理

管理用户

30.改 善 性 能

附录：MySQL数据类型

D.1 串数据类型

D.2 数值数据类型

D.3 日期和时间数据类型

D.4 二进制数据类型

D.3 日期和时间数据类型

D.4 二进制数据类型

30.改善性能