Mysql 语法豆知识
https://www.cnblogs.com/chentianwei/p/8093748.html
mysql增加了大量语法，以前没有接触过。比如
create function
begin..end
limit的写法：
查看help select 发现，[LIMIT {[offset,] row_count | row_count OFFSET offset}] 有2种写法，前面的是简化版的。
⚠️被检索的第一个行其实是第0行。所以limit 1 offset 1表示返回第2行记录。
begin..end
mysql> mysql> help begin end Name: 'BEGIN END' Description: Syntax: [begin_label:] BEGIN [statement_list] END [end_label]
用于组合声明。可嵌套。
declare variable
mysql> help DECLARE VARIABLE Name: 'DECLARE VARIABLE' Description: Syntax: DECLARE var_name [, var_name] ... type [DEFAULT value]
声明本地变量。
default 子句，是提供默认值。如果不写，则值为null
例子：DECLARE P INT;
set
分配值给变量。
mysql> help set Name: 'SET' Description: Syntax: SET variable = expr [, variable = expr] ... variable: { user_var_name | param_name | local_var_name | {GLOBAL | @@GLOBAL.} system_var_name | {PERSIST | @@PERSIST.} system_var_name | {PERSIST_ONLY | @@PERSIST_ONLY.} system_var_name | [SESSION | @@SESSION. | @@] system_var_name }
通配符号和like操作符号
like操作符
expr LIKE pat [ESCAPE 'escape_char']
mysql> SELECT 'David!' LIKE 'David_'; -> 1 mysql> SELECT 'David!' LIKE '%D%v%'; -> 1
%, 匹配任意个数量的字符，包括0个。但不匹配null。
_, 匹配任意一个字符。
[] 通配符，代表一个字符集合，匹配字符集合内的一个字符。
此通配符可以用前缀字符^(脱字号)来否定。
函数
数据库管理系统dbms支持函数。
但每个语言的内建函数都有区别。
Mysql:
使用具体看help functions后列出的list,看string Functions。里面有所有的内建函数。
也可以看网页上的文档，help <item>信息的最后给出了url地址。
trim()函数 去掉左右的空格， 还有ltrim, rtrim。
substring(str, pos) 提取从pos位置开始的字符串。str是从1开始计算，和ruby, python不一样。
left(str, len):返回左边的len个字符。
length(str): 返回长度。
lower, upper, 改变大小写
soundex()函数：
（wiki）Soundex是一种语音算法，利用英文字的读音计算近似值，值由四个字符构成，第一个字符为英文字母，后三个为数字。在拼音文字中有时会有会念但不能拼出正确字的情形，可用Soundex做类似模糊匹配的效果。例如Knuth和Kant二个字符串，它们的Soundex值都是“K530”。
curdate()
abs(), cos(), sin(),
是否使用自定义函数，看需求。
聚合函数
count
count(*)对表中行的数据进行计算，包括null。
count(column)计算特点行，忽略null
mysql> SELECT student.student_name,COUNT(*) FROM student,course WHERE student.student_id=course.student_id GROUP BY student_name;
组合聚合函数
这条select语句执行了4个聚合计算，返回4个值。
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;
分组group by
select cust_id count(*) as orders from Orders group by cust_id having count(*) >= 2
使用 GROUP BY {col_name | expr | position}， 进行分组。
having类似where, 用于添加一些对分组后数据的筛选规则。
- where的筛选是针对行的。所以不能用聚合函数。
- having是配合group by进行分组的。可以用聚合函数。
⚠️：group by 分组后的数据如果需要再排序还要用 order by.
select 子句有固定的顺序：select from where group by having order by
MYsql支持多层嵌套查询。
但是：
- 子查询语句只能查询单个列。
- 使用子查询，对大数据的查询效能较低。
子查询的方式有2种：
- 用于过滤，在where子句中使用。
- 用于计算字段：
SELECT cust_name, cust_state, (SELECT COUNT(*) FROM Orders WHERE Orders.cust_id = Customers.cust_id) AS orders FROM Customers ORDER BY cust_name;
orders是一个计算字段，本例子该计算字段中的子查询执行的次数是外部select查询得到的记录数。
⚠️注意：这种方法并不高效，其实可以使用连接表的方式代替。
连接表
表和表之间的关联使用主key，外key来连接的。把一堆数据根据其数据特性或类型及相关关系分为多个表格，并用key进行连接，是一种数据库的 设计模式，叫做关系型数据库。
连接表：
- from子句中加上要连接的表的名字用逗号隔开，然后在where子句中用 key进行连接。⚠️这种连接，叫做内连接。
- 另一种是使用join..on关键字。包括inner join, left join. right join。后2者也叫外连接
select * from Vendors inner join Products on Vendors.vend_id = Products.vend_id
注意：⚠️可以进行多表连接。使用where。
组合查询 union
http://www.mysqltutorial.org/sql-union-mysql.aspx
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...] UNION is used to combine the result from multiple SELECT statements into a single result set. URL: https://dev.mysql.com/doc/refman/8.0/en/union.html
union把2个select语句的查询结果联合起来：
- column的数量和顺序，所有的select声明必须一样。
- column的数据类型，必须一样或兼容。
（⚠️以上两点注意，可以见链接的实例costumer表和employee表的union，最后返回的表的表头的列名使用第一个select声明的列的名字）
- 一般用于较复杂的过滤条件，或从多个表中检索数据。
- UNION默认使用 distinct，过滤掉重复的记录。
- order by 只能出现在整个语句的最后，即只出现一次。
UNiON VS join
union是垂直连接行，join是水平连接行。
数据的操作insert, update, delete
insert
- 插入完整的行 insert [into] 表名 values(字段值1，字段值2, ...) ⚠️values的参数数量必须和表的列一一对应。
- 插入行的一部分 insert [into] 表名(字段名,...) values(字段值1, 字段值2, ...)
- 插入select的结果。
- 还可以多行插入：insert [into] 表名(字段名,...) values(字段值1, 字段值2, ...), (字段值1, 字段值2, ...)
⚠️注意如果一个字段有限制：auto_increment，那么字段值可以输入0，null或default.
select * from 表名 into 外部文件|变量
update
update 表名 set 列名=value where 条件。
delete from 表名 where子句。
删除数据前，做表的完全备份。或者先查询要删除的数据，然后存入一个新的表。然后再在原表删除。最好是双人检查制度。
另外，最好配合使用transaction来进行删除操作。
这是物理删除，彻底从数据库删除数据。
还有一种是逻辑删除，并不真的删除一条记录，只是用一个字段的值表示一条记录不再被使用，以便未来会恢复这条数据。使用s数据类型bit
create view创建一个视图
视图就是一条SELECT语句执行后返回的结果集。所以我们在创建视图的时候，主要的工作就落在创建这条SQL查询语句上。
说白了就是保存一条select语句。类似于一个方法/函数。
优点：
- 提高了重用性，就像一个函数。
- 对数据库重构，却不影响程序的运行。
- 提高了安全性能，可以对不同的用户。无需把原表的权限给某个用户，只给这个用户查看视图的权限。
- 这是因为视图只是一条语句。无法修改原表。
- 让数据更加清晰
- 创建：create view 名字 as select语句
- 查看视图： show tables; select * from 视图名字；
- 删除视图: drop view 视图名字
管理事物处理
批量处理，发生意外恢复到处理前的状态。
特点：
- 原子性(Atomicity) 一个事务就是一个最小单元，不可再分割，这个事务的内部要不就全部完成，要不就保持最初的状态。
- 一致性(Consistency) 内部语句必须完全执行，如果中途出问题，就回退到最开始的状态。
- 隔离性(Isolation) 一个事务提交前，其他用户不能修改事务正在处理的表，即事务锁定了当前操作的表。
- 持久性(Durability) 事务被提交后，对数据库永久改变。
语法关键字：
- transaction
- rollback: 可以回退insert, update, delete。
- commit
START TRANSACTION [transaction_characteristic [, transaction_characteristic] ...] transaction_characteristic: { WITH CONSISTENT SNAPSHOT | READ WRITE | READ ONLY } BEGIN [WORK] COMMIT [WORK] [AND [NO] CHAIN] [[NO] RELEASE] ROLLBACK [WORK] [AND [NO] CHAIN] [[NO] RELEASE] SET autocommit = {0 | 1} 这些声明提供对transaction的控制流程的支持： o START TRANSACTION or BEGIN start a new transaction. o COMMIT commits the current transaction, making its changes permanent. 永久改变 o ROLLBACK rolls back the current transaction, canceling its changes. 可以撤销之前的commit o SET autocommit disables or enables the default autocommit mode for the current session.
开始事务:
start transaction或begin
提交：
commit
回滚：
rollback
⚠️：
1. Mysql有很多引擎，但只有innodb引擎支持事务处理。
2.Python操作数据库，默认开启transaction.
3.Python对数据库进行增删改的时候，需要手动commit。
4.使用终端操作数据库（也就是mysql的客户端）的时候，也默认开启事务。回车时自动commit。
储存过程(简介)stored procedure
http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx
Mysql5后的版本才支持。包括储存过程和储存函数
这是一个很大的模块。我目前的理解就是，面向过程的编程方法。
Section 1. Stored procedure basics
- 包括如何声明变量：使用declare关键字
Section 2. Conditional Statements
- if和case声明
Section 3. Loops
Section 4. Error Handling
Section 5. Cursors
Section 6. Stored Functions
Section 7. Stored Program Security
执行存储过程：
EXECUTE stmt_name
[USING @var_name [, @var_name] ...]
创建存储过程
CREATE [DEFINER = user] PROCEDURE sp_name ([proc_parameter[,...]]) [characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
out代表从procedure返回一个数据，而不是传递一个值给储存过程。
例子：
mysql> CREATE PROCEDURE simpleproc (OUT param1 INT) -> BEGIN -> SELECT COUNT(*) INTO param1 FROM t; -> END// Query OK, 0 rows affected (0.00 sec) mysql> delimiter ; mysql> CALL simpleproc(@a); Query OK, 0 rows affected (0.00 sec) mysql> SELECT @a; +------+ | @a | +------+ | 3 | +------+ 1 row in set (0.00 sec)
常用数据库命令
- select version(); #显示版本
- select now(); #显示当前系统时间
- select database(); #显示当前数据库
- show databases #查看所有数据库名称。
-
use 数据库名字 #选中一个数据库
- create database 名称
- drop database 名称
show命令
SHOW has many forms that provide information about databases, tables, columns, or status information about the server.
例如：
show databases #显示所有的数据库
show create database 数据库名字 会显示创建数据库的详细信息。
show tables #显示当前数据库的所有的表。
show create table 表名
创建表格
mysql> create table student(id int unsigned primary key auto_increment not null, -> name varchar(20) not null); mysql> show tables;+-----------------+ | Tables_in_test1 | +-----------------+ | student | +-----------------+
解释：
- 数据类型int， unsigned无符号的，这里表示是正整数。
- primary key 作为主键
- auto_increment 自动增长, 只支持整数浮点类。
- not null 不能为空。
- 数据类型 varchar(m)
- a variable-length string。m代表列的值的最大长度
- 数据类型 char(m)
- a fixed-length string。
var和varchar的区别再也储存和取回的方式，以及最大长度和是否空格补位：
- char(m)， m确定了最大字符长度，如果实际字符长度小于m, 储存时会使用空格右边补位。但取出时，会移除头尾空格。0<=m<=255
- varchar(m), m<=65535, 不会使用空格补位。
具体区别和2个例子见文档：https://dev.mysql.com/doc/refman/8.0/en/char.html
mysql> desc student -> ; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL |
- desc关键字，显示表格结构。
删除表格： drop table [库名（如果没进入数据库）] 表名
给现存表格添加字段： alter table 表名 add(col_name, col_definition, ...)
mysql> alter table student add(age int unsigned, -> high decimal(5,2), -> gender enum('man', 'woman') -> );
解释：
- 数据类型decimal(5,2)。 5代表有5个数字，2代表小数点后有2位。
- 数据类型 enum('value1', 'value2' ...)。 它是一个字符串对象, 枚举。
对现存表格的字段定义进行修改：alter table 表名 MODIFY col_name column_definition
mysql> alter table student modify gender enum('man', 'woman', 'middle', 'secret') default 'secret';
mysql> alter table student add(cls_id int unsigned);
解释：
- 使用modify关键字，重新编辑gender字段，⚠️必须全部重新编辑。
- 可以使用default关键字设置一个默认值。
- 使用desc student可以查看新的表结构。
- 使用show create table student显示，创建这个表的句法，可以直接复制后用到其他数据库建表。
- 也可以使用change关键字修改，只不过语法上它是为重起一个新的字段名而设计的：change 旧名 新名 新列定义
删除表的字段 ： alter table 表名字 drop 字段名字;
primary和foreign key
https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html
例子：
create table scores( id int unsigned auto_increment primary key not null, score int, stuid int unsigned, subid int unsigned, foreign key(stuid) references students(id), foreign key(subid) references subjects(id) );
foreign key(本表的列名) references 关联表(其列名)
foreign key的简介
为2个表建立1对多的关系。
有foreign key的表对主表的增加数据和删除数据有了约束。具体键文档
isnull(exp);
cast(exp as type);
left(str, len)
这个网站，特别适合学习mysql，对于常用功能，比官网有更多的例子和更详细的解释，🌿的是还有代码执行区域进行尝试。👍。
null
null是一个特殊的符号，不能使用 != , = 和null进行比较, null被count等聚合函数忽略计算。需要使用：
- is null
- is not null
mysql> select "hello" is null; +-----------------+ | "hello" is null | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec) mysql> select "hello" is not null; +---------------------+ | "hello" is not null | +---------------------+ | 1 | +---------------------+
返回0,代表false。返回1, 代表true。
详解：
http://www.mysqltutorial.org/mysql-is-null/和https://dev.mysql.com/doc/refman/8.0/en/numeric-type-syntax.html
- Mysql并没有boolean类型。作为替代，使用tinyint(M)类型中的1代表true，0代表false。
- tinyint(M)是一个小的有符号的整数集，从-128到127。
- the values
TRUEandFALSEare merely aliases for1and0。 true,false就是别名。
mysql> select if(0 = False, "is true", "is false"); +--------------------------------------+ | if(0 = False, "is true", "is false") | +--------------------------------------+ | is true
in操作符号
http://www.mysqltutorial.org/sql-in.aspx
SELECT column1,column2,... FROM table_name WHERE (expr|column_1) IN ('value1','value2',...);
MySQL Variables
http://www.mysqltutorial.org/mysql-variables/
Mysql有2种类型的变量，用户自定义变量，和储存过程变量。本章讲解自定义变量。
需求：
传递一个value, 从一个声明到另一个声明。使用自定义变量即可以完成这个需求。
创建格式：@variable_name
⚠️
- 类似Ruby的类定义种的实例变量。
- 大小写不敏感:@id等同@ID
- 变量的数据类型：比如integer, floating point, decimal, string , null
分配变量
set @variable := "hello world";
⚠️也可以使用 =号，不带冒号。一样的。
另一种方法
select @variable3 := "hi!";
在声明后，就可以在一个表达式中使用， where， insert, update等等。
⚠️：只能使用:=
使用例子：
select @id := Student.S from Student where Sname = '孙风';
变量@id得到了返回的记录结果。
select * from SC where S = @id;
+------+------+-------+
| S | C | score |
+------+------+-------+
| 03 | 01 | 80.0 |
| 03 | 02 | 80.0 |
| 03 | 03 | 80.0 |
+------+------+-------+
然后就可以使用@id在另一个声明中了。
⚠️注意：
- 自定义变量只能接受一行记录。如果分配多行，那么只储存最后一行记录。
- 一个@variable只能接受一个列的数据。如果一行记录有多个列，则使用select into句法.
select into
如果返回多行，会报告❌，因此最好加上limit 1。
SELECT
c1, c2, c3, ...
INTO
@v1, @v2, @v3,...
FROM
table_name
WHERE
condition;
分组和聚合函数rollup
http://www.mysqltutorial.org/mysql-rollup/
with rollup的作用是：在最后新增一行，来记录当前列里所有记录的总和。
mysql> select gender, count(*) from students group by gender with rollup; +---------+----------+ | gender | count(*) | +---------+----------+ | male | 5 | | female | 7 | | neutral | 1 | | secret | 1 | | NULL | 14 | +---------+----------+ 5 rows in set (0.04 sec)
rollup是聚合函数，在没有它之前，只能使用union all来连接表：
mysql> select gender, count(*) from students group by gender
union all
select null, count(*) from students; +---------+----------+ | gender | count(*) | +---------+----------+ | female | 7 | | male | 5 | | secret | 1 | | neutral | 1 | | NULL | 14 | +---------+----------+ 5 rows in set (0.30 sec)
结果是一样的。但是使用union all有2个缺陷：
- 2个select的列的数量要一致，所以第2个select语句使用了null作为一列。
- 会执行2次查询，然后连接表，这明显降低了效率。
⚠️另外with rollup支持多重分组统计。具体见👆连接的文档。
索引
1思考
一般的应用系统对比数据库的读写比例在10:1左右(即有10次查询操作时有1次写的操作)，遇到最多、最容易出问题还是一些复杂的查询操作，所以查询语句的优化显然是重中之重。
2解决办法
当数据库中数据量很大时，查找数据会变得很慢
优化方案：索引
3.索引是什么
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
4使用
- 查看索引 ：
show index from 表名;
- 创建索引
- 如果指定字段是字符串，需要指定长度，建议长度与定义字段时的长度一致
- 字段类型如果不是字符串，可以不填写长度部分
create index 索引名称 on 表名(字段名称(长度))
- 删除索引：
drop index 索引名称 on 表名;
开启运行时间检测： set profiling=1;
查看执行的时间：show profiles;
5 优缺点
-
当数据量大时，索引才有效。
- 索引数据需要单独维护，占用磁盘空间，降低对alter into ,update,delete from的效率, 所以不是越多越好。
控制台的提示符号
https://dev.mysql.com/doc/refman/8.0/en/entering-queries.html
| Prompt | Meaning |
|---|---|
mysql> |
Ready for new query |
-> |
Waiting for next line of multiple-line query |
'> |
Waiting for next line, waiting for completion of a string that began with a single quote (') |
"> |
Waiting for next line, waiting for completion of a string that began with a double quote (") |
`> |
Waiting for next line, waiting for completion of an identifier that began with a backtick (`) |
/*> |
Waiting for next line, waiting for completion of a comment that began with /* |
另外，末尾加上“\c”不会运行查询语句
mysql> select current_date -> \c mysql>
replace 字符串函数
https://www.mysqltutorial.org/mysql-string-replace-function.aspx
REPLACE(str,old_string,new_string);
替换str中的old_string 为new_string。
- ⚠️ old_string和new_string区分大小写字母
- ⚠️Mysql有一个replace statement, 用在insert和update数据上的，不要弄混淆。
语法例子：
UPDATE tbl_name SET field_name = REPLACE(field_name, string_to_find, string_to_replace) WHERE conditions;
⚠️：
- 第一个参数是列名，不要加上引号""，否则会引发意外数据丢失。
- replace()不支持正则表达式。
date_format()和str_to_date()
date_format(date, format)
mysql> SELECT STR_TO_DATE('11','%h'); +------------------------+ | STR_TO_DATE('11','%h') | +------------------------+ | 11:00:00 | +------------------------+ mysql> SELECT STR_TO_DATE('21,5,2013','%d,%m,%Y'); +-------------------------------------+ | STR_TO_DATE('21,5,2013','%d,%m,%Y') | +-------------------------------------+ | 2013-05-21 | +--------------------
MYsql 控制流程的函数
- case
- if
- ifnull
The control flow functions allow you to add if-then-else logic to SQL queries without using the procedural code.
用于sql的查询。
if()函数
IF(expr,if_true_expr,if_false_expr)
例子：
SELECT IF(1 = 1,' true','false'); -- true
可以和聚合函数一起使用：
SELECT SUM(IF(status = 'Shipped', 1, 0)) AS Shipped, SUM(IF(status = 'Cancelled', 1, 0)) AS Cancelled FROM orders;
和count()函数一起使用时，注意⚠️count()会忽略计算值为null的数据：
SELECT COUNT(IF(status = 'Cancelled', 1, NULL)) Cancelled, COUNT(IF(status = 'Disputed', 1, NULL)) Disputed, FROM orders;
因此，第三个参数可以设置为null。
上面的代码同样可以使用group by替代：
SELECT status, COUNT(STATUS) FROM orders GROUP BY status
但结果一个是横向表示，一个是纵向
case表达式
CASE value WHEN value1 THEN result1 WHEN value2 THEN result2 … [ELSE else_result] END
CASE WHEN expression1 THEN result1 WHEN expression2 THEN result2 … [ELSE else_result] END
case可以用在：
- select子句
- order by子句
- 聚合函数
ifnull()函数
用在select子句居多。
IFNULL(expression_1,expression_2);
- 如果expression_1不是null, 则返回它的结果
- 否则返回expression_2的结果
例子：
mysql> select ifnull(null, 'hello'); +-----------------------+ | ifnull(null, 'hello') | +-----------------------+ | hello | +-----------------------+ 1 row in set (0.00 sec)
