mysql_basic

2020-01-07 Knowledge Knowledge, MySQL Comments

[!NOTE]
在MySQL中,schema和database是同义词.
CREATE SCHEMA和CREATE DATABASE是等效的.
但是其他的数据库产品(几乎所有数据库)有所不同.在oracle数据库产品中,schema是database的一部分.
表示the tables and other objects owned by a single user.

[!NOTE]
https://dev.mysql.com/doc/
https://pymysql.readthedocs.io/en/latest/

mysql is case-insensitive

可用反引号（`）为标识符（库名、表名、字段名、索引、别名）包裹，以避免与关键字重名！中文也可以作为标识符！
每个库目录存在一个保存当前数据库的选项文件db.opt。
注释：
单行注释 # 注释内容
多行注释 /* 注释内容 */
单行注释 – 注释内容 (标准SQL注释风格，要求双破折号后加一空格符（空格、TAB、换行等）)
模式通配符：
_ 任意单个字符
% 任意多个字符，甚至包括零字符
单引号需要进行转义 ’
CMD命令行内的语句结束符可以为 “;”, “\G”, “\g”，仅影响显示结果。其他地方还是用分号结束。delimiter 可修改当前对话的语句结束符。
SQL对大小写不敏感
清除已有语句：\c

-- connection over TCP/IP
mysql -h server_ip -Pport -uroot -p
-- connection over UNIX Socket
mysql -S /data/mysql/mysql.sock -uroot -p

mysqld --verbose --help
mysqladmin version status proc
mysqladmin variables extended-status processlist
mysql -e "check table db_name.tb_name quick"
mysqlshow
mysqladmin -u root password "new_password"
mysqladmin -u USERNAME -h HOSTNAME password 'NEW_PASS' -p
mysqldumpslow  -s  c  -t  20  233SS1-slow.log

\s;
status;

-- engine-related
show engine ENGINE_NAME {logs|status};


-- variable-related
-- 系统变量
set @@global.validate_password_length=1,@@global.validate_password_low_case=0;
set names charset_name;
select @@global.sql_mode;

-- 用户自定义变量
set @var_name:=value;
select @var_name;





-- 时间函数
mysql> select unix_timestamp() into @didi;
Query OK, 1 row affected (0.01 sec)

mysql> select from_unixtime(@didi);
+----------------------+
| from_unixtime(@didi) |
+----------------------+
| 2019-12-20 15:23:35  |
+----------------------+
1 row in set (0.00 sec)


-- user-related

> 用户权限相关数据保存在mysql数据库的usertb_name中，所以也可以直接对其进行操作

-- 创建用户
create USER 'username'@'ip' IDETIFIED BY 'password';
    - 必须拥有mysql数据库的全局CREATE USER权限，或拥有INSERT权限。
    - 只能创建用户，不能赋予权限。
    - 用户名，注意引号：如 'user_name'@'192.168.1.1'
    - 密码也需引号，纯数字密码也要加引号
alter user user_name@'%' identified by password ACCOUNT LOCK/UNLOCK;

-- 设置密码
SET PASSWORD = PASSWORD('密码')  -- 为当前用户设置密码
SET PASSWORD FOR 用户名 = PASSWORD('密码') -- 为指定用户设置密码

-- 重命名用户
rename user user_name TO new_user_name;
rename user user_name@ip_addr TO new_username@new_ip_addr;

-- 删除用户
DROP USER 'username'@'ip';
delete from mysql.user where Host='192.168.1.4';

-- 修改用户
RENAME USER 'username'@'ip' to 'new_username'@'ip';

-- 修改密码
SET password FOR 'username'@'ip' = Password('new_password');
# mysql5.7之前
mysql> update mysql.user set password=password('123456') where user='root';
# mysql5.7之后
mysql> update mysql.user set authentication_string=password('123456') where user='root';
mysql> flush privileges;

Alter user UserName@ip_addr identified by 'password';
select Host,User,Grant_priv from mysql.user;]

> [Privileges Provided by MySQL](https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html)

-- 查看权限
show grants;
show GRANTS FOR 'usernmae'@'ip';
show grants for current_user();
show grants for "user"@"host";

-- 授权
GRANT 权限 ON db_name.tb_name TO 'username'@'ip';
GRANT ALL PRIVILEGES ON db_name.* TO 'username'@'host' IDENTIFIED BY 'testuser_pass' WITH GRANT OPTION;

* 取消授权
REVOKE 权限 FROM db_name.tb_name FROM 'username'@'ip'
revoke all on *.* from "username"@"localhost";
revoke select  on *.* from "username"@"localhost";

-- 修改用户密码
alter user 'username'@'ip_addr' identified by 'new_password';
update mysql.user set password=password('新密码') where User="username" and Host="ip_addr";
set password for user_name = new_password;
set password for user_name = password('new_password');

-- 口令失效
alter user user_name@ip_addr password expire;

-- 权限列表
ALL [PRIVILEGES]    -- 设置除GRANT OPTION之外的所有简单权限
ALTER   -- 允许使用ALTER TABLE
ALTER ROUTINE   -- 更改或取消已存储的子程序
CREATE  -- 允许使用CREATE TABLE
CREATE ROUTINE  -- 创建已存储的子程序
CREATE TEMPORARY TABLES     -- 允许使用CREATE TEMPORARY TABLE
CREATE USER     -- 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW     -- 允许使用CREATE VIEW
DELETE  -- 允许使用DELETE
DROP    -- 允许使用DROP TABLE
EXECUTE     -- 允许用户运行已存储的子程序
FILE    -- 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX   -- 允许使用CREATE INDEX和DROP INDEX
INSERT  -- 允许使用INSERT
LOCK TABLES     -- 允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS     -- 允许使用SHOW FULL PROCESSLIST
REFERENCES  -- 未被实施
RELOAD  -- 允许使用FLUSH
REPLICATION CLIENT  -- 允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE   -- 用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT  -- 允许使用SELECT
SHOW DATABASES  -- 显示所有数据库
SHOW VIEW   -- 允许使用SHOW CREATE VIEW
SHUTDOWN    -- 允许使用mysqladmin shutdown
SUPER   -- 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE  -- 允许使用UPDATE
USAGE   -- “无权限”的同义词
GRANT OPTION    -- 允许授予权限

-- PLUGIN-related

-- install plugins
INSTALL PLUGIN plugin_nbame SONAME 'plugin_name';
-- uninstall plugin
uninstall plugin plugin_name;


-- show-related
show warnings/variablels/status/engines/collation;
show create procedure/function/table/database;
show procedure status;
show create procedure proc_name;
show variables like 'lower_case_table_names';
show tables like 'regx';

show create table tb_name;
desc tb_name;
describe tb_name;
show COLUMNS FROM tb_name;
show full fields from tb_name;
show INDEX FROM tb_name;

show TABLE STATUS FROM db_name;
show TABLE STATUS FROM db_name LIKE 'pattern%';

-- SELECT-related
SELECT routine_schema, routine_name, sql_mode from information_schema.routines;
SELECT event_object_schema, event_object_table, trigger_name, sql_mode from nformation_schema.triggers;
select user()/version()/database()/CURRENT_DATE/CURRENT_DATE()/CURDATE()/CURRENT_TIME/CURRENT_TIME()/CURTIME()/unix_timestamp()/last_insert_id()/connection_id()/uuid();
select 4+5,ifnull(3,2/0);


SELECT [ALL|DISTINCT] select_expr FROM -> WHERE -> GROUP BY [合计函数] -> HAVING -> ORDER BY -> LIMIT
a. select_expr
    -- 可以用 * 表示所有字段。
        select * from tb;
    -- 可以使用表达式（计算公式、函数调用、字段也是个表达式）
        select stu, 29+25, now() from tb;
    -- 可以为每个列使用别名。适用于简化列标识，避免多个列标识符重复。
        - 使用 as 关键字，也可省略 as.
        select stu+10 as add10 from tb;
b. FROM 子句
    用于标识查询来源。
    -- 可以为表起别名。使用as关键字。
        SELECT * FROM tb1 AS tt, tb2 AS bb;
    -- from子句后，可以同时出现多个表。
        -- 多个表会横向叠加到一起，而数据会形成一个笛卡尔积。
        SELECT * FROM tb1, tb2;
    -- 向优化符提示如何选择索引
        USE INDEX、IGNORE INDEX、FORCE INDEX
        SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3;
        SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
c. WHERE 子句
    -- 从from获得的数据源中进行筛选。
    -- 整型1表示真，0表示假。
    -- 表达式由运算符和运算数组成。
        -- 运算数：变量（字段）、值、函数返回值
        -- 运算符：
            =, <=>, <>, !=, <=, <, >=, >, !, &&, ||,
            in (not) null, (not) like, (not) in, (not) between and, is (not), and, or, not, xor
            is/is not 加上ture/false/unknown，检验某个值的真假
            <=>与<>功能相同，<=>可用于null比较
d. GROUP BY 子句, 分组子句
    GROUP BY 字段/别名 [排序方式]
    分组后会进行排序。升序：ASC，降序：DESC
    以下[合计函数]需配合 GROUP BY 使用：
    count 返回不同的非NULL值数目  count(*)、count(字段)
    sum 求和
    max 求最大值
    min 求最小值
    avg 求平均值
    group_concat 返回带有来自一个组的连接的非NULL值的字符串结果。组内字符串连接。
e. HAVING 子句，条件子句
    与 where 功能、用法相同，执行时机不同。
    where 在开始时执行检测数据，对原数据进行过滤。
    having 对筛选出的结果再次进行过滤。
    having 字段必须是查询出来的，where 字段必须是数据表存在的。
    where 不可以使用字段的别名，having 可以。因为执行WHERE代码时，可能尚未确定列值。
    where 不可以使用合计函数。一般需用合计函数才会用 having
    SQL标准要求HAVING必须引用GROUP BY子句中的列或用于合计函数中的列。
f. ORDER BY 子句，排序子句
    order by 排序字段/别名 排序方式 [,排序字段/别名 排序方式]...
    升序：ASC，降序：DESC
    支持多个字段的排序。
g. LIMIT 子句，限制结果数量子句
    仅对处理好的结果进行数量限制。将处理好的结果的看作是一个集合，按照记录出现的顺序，索引从0开始。
    limit 起始位置, 获取条数
    省略第一个参数，表示从索引0开始。limit 获取条数
h. DISTINCT, ALL 选项
    distinct 去除重复记录
    默认为 all, 全部记录


-- table-related
create table tb_1( id int , name varchar(5) );
show index from tb_1;
alter table tb_1 add primary key(id);
create index ind_name on tb_1(id);
show table status like 'tb_1';
SHOW TABLES [ LIKE 'pattern'];
drop index ind_name on tb_1;
drop table tb_name;
repair table tb_name quick;
analyze table tb_name;
SHOW TABLES FROM db_name;
SHOW CREATE TABLE 表名;
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']

-- 新建表
create TABLE IF NOT EXISTS `tb_name`(
    `field1` int primary key AUTO_INCREMENT not null,
    `field2` varchar(20) COLLATE utf8mb4_general_ci,
    `fieldLast`
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- 表锁定只用于防止其它客户端进行不正当地读取和写入
-- MyISAM 支持表锁，InnoDB 支持行锁
-- 锁定
    LOCK TABLES tbl_name [AS alias]
-- 解锁
    UNLOCK TABLES

-- 其他建tb_name方式：  
create TABLE new_tb_name LIKE tb_name;(复制tb_name结构)  
INSERT INTO new_tb_name select * FROM tb_name;(tb_name结构一致的前提下，插入所选数据)
create TABLE tb_name select * from tb_name;
create TABLE tb_name AS select * FROM tb_name;

-- 清空表
DELETE FROM tb_name;(清空数据，保留tb_name结构)  
TRUNCATE TABLE tb_name;(清空数据，保留tb_name结构) 
-- 区别
- truncate 是删除表再创建，delete 是逐条删除
- truncate 重置auto_increment的值。而delete不会
- truncate 不知道删除了几条，而delete知道
- 当被用于带分区的表时，truncate 会保留分区
DROP TABLE tb_name;(清空数据，删除tb_name)  

DELETE FROM tbl_name [WHERE where_definition] [ORDER BY ...] [LIMIT row_count]
- 按照条件删除。where
- 指定删除的最多记录数。limit
- 可以通过排序条件删除。order by + limit
- 支持多表删除，使用类似连接语法。
- delete from 需要删除数据多表1，表2 using 表连接操作 条件。

-- 更改表名
ALTER TABLE old_tb_name RENAME TO new_tb_name;
RENAME TABLE old_tb_name TO new_tb_name;
RENAME TABLE 原表名 TO 库名.表名 （可将表移动到另一个数据库）

-- 插入信息
INSERT INTO tb_name VALUES(字段1，字段2，'str', ...)；  
INSERT INTO tb_name VALUES(字段1(如果该字段自增，即使指定数字也无用，可写NULL代替)，字段2，'str', ...);  
INSERT INTO tb_name VALUES( 1, 'zhao' ),( 2, 'qian' ),( 3, 'sun' );(插入多条数据)

-- select语句获得的数据可以用insert插入
可以省略对列的指定，要求 values () 括号内，提供给了按照列顺序出现的所有字段的值。
    或者使用set语法。
    INSERT INTO tbl_name SET field=value,...；
可以一次性使用多个值，采用(), (), ();的形式。
    INSERT INTO tbl_name VALUES (), (), ();
可以在列值指定时，使用表达式。
    INSERT INTO tbl_name VALUES (field_value, 10+10, now());
可以使用一个特殊值 DEFAULT，表示该列使用默认值。
    INSERT INTO tbl_name VALUES (field_value, DEFAULT);
可以通过一个查询的结果，作为需要插入的值。
    INSERT INTO tbl_name SELECT ...;
可以指定在插入的值出现主键（或唯一索引）冲突时，更新其他非主键列的信息。
    INSERT INTO tbl_name VALUES/SET/SELECT ON DUPLICATE KEY UPDATE 字段=值, …;


-- 更新信息
UPDATE tb_name SET `fieldN` = 'new_value' WHERE Clause;

-- 删除字段
ALTER TABLE tb_name DROP `target_column_name`;

-- 添加字段
ALTER TABLE tb_name ADD `new_column_name` column_type_and_option;

-- 更改字段名
ALTER TABLE tb_name CHANGE `target_col_name` `new_col_name` new_col_def;
ALTER TABLE tb_name CHANGE COLUMN　`target_col_name` `new_col_name` new_col_def;
alter table tb_name modify `col_name` new_col_def;
ALTER TABLE tb_name MODIFY COLUMN `target_col_name` new_def;

-- db-related
-- 新建数据库
create DATABASE IF NOT EXISTS `db_name` DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;
CREATE DATABASE `dada` /*!40100 DEFAULT CHARACTER SET utf8mb4 */;
create database zeze /*!default character set utf8*/;

# alter-related
alter table tb_name change old_col_name new_col_name col_definition;
alter table didi engine=innodb;
alter table tb_name drop col_name;
alter table tb_name add new_col_name col_definition;
alter table tb_name add new_col_name First;
alter table tb_name add new_col_name after existed_col_name;


# trim()
select trim(BOTH ',' FROM ",,haha,,");
select ... into outfile '/tmp/db_xx_tb_yy';

# COMMENT / change
alter table didi change id id int comment 'column:id';
# modify
alter table didi modify id int comment 'column:id';
select column_name, data_type, column_comment from information_schema.columns where table_name='didi' and table_schema='haha' and column_name like 'name%';
create table zeze (id int, name varchar(20)) COMMENT 'table zeze';
create table zeze (id int, name varchar(20)) COMMENT='table zeze';
show table status from haha like 'didi'\G;
show table status where name='didi'\G;

# log-related
show BINARY LOGS;
show master logs;
show MASTER STATUS;
show BINLOG EVENTS IN '二进制日志文件' [FROM 'position'];
PURGE BINARY LOGS TO '日志文件';
FLUSH LOGS;
purge binary logs before now()- interval 7 day;
purge binary logs to 'bin_log_name';

show function status [like pattern];
show create function 函数名字;
create function fun_name ...;
drop function fun_name;
# 使用函数
select 函数名(实参列表);

# perfromance
# performance-related
show status like 'innodb_row_%';
show variables like 'query%';
show variables like 'innodb_doublewrite';
show variables like 'innodb_data_file%';
show variables like 'innodb_file%';
show variables like 'innodb_temp%';

# 回收undo段的线程
show variables like '%purge%%';
show variables like 'max_heap_table_size';
show variables like 'tmp_table_size';
show variables like '%tmpdir%';
show status like 'created_tmp%';

# 添加列
alter table tb_name名 add 列名 类型
# 删除列
alter table tb_name名 drop column 列名
# 修改列
alter table tb_name名 modify column 列名 类型;  -- 类型
alter table tb_name名 change 原列名 新列名 类型; -- 列名，类型
  
# 添加主键
alter table tb_name名 add primary key(列名);
# 删除主键
alter table tb_name名 drop primary key;
alter table tb_name名  modify  列名 int, drop primary key;

# 添加外键
alter table 从tb_name add constraint 外键名称（形如
FK_从tb_name_主tb_name） foreign key 从tb_name(外键字段) references 主tb_name(主键字段);
# 删除外键
alter table tb_name名 drop foreign key 外键名称
  
# 修改默认值
ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;
# 删除默认值
ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;

1、条件
 select * from tb_name where id > 1 and name != 'alex' and num = 12;
 select * from tb_name where id between 5 and 16;
 select * from tb_name where id in (11,22,33)
 select * from tb_name where id not in (11,22,33)
 select * from tb_name where id in (select nid from tb_name)

2、通配符
select * from tb_name where name like 'ale%'  - ale开头的所有（多个字符串）
select * from tb_name where name like 'ale_'  - ale开头的所有（一个字符）

3、限制
select * from tb_name limit 5;            - 前5行
select * from tb_name limit 4,5;          - 从第4行开始的5行
select * from tb_name limit 5 offset 4    - 从第4行开始的5行
 
4、排序
select * from tb_name order by 列 asc              - 根据 “列” 从小到大排列
select * from tb_name order by 列 desc             - 根据 “列” 从大到小排列
select * from tb_name order by 列1 desc,列2 asc    - 根据 “列1” 从大到小排列，如果相同则按列2从小到大排序
 
5、分组
    select num from tb_name group by num
    select num,nid from tb_name group by num,nid
    select num,nid from tb_name  where nid > 10 group by num,nid order nid desc
    select num,nid,count(*),sum(score),max(score),min(score) from tb_name group by num,nid
 
    select num from tb_name group by num having max(id) > 10

# > 特别的
group by 必须在where之后，order by之前
 
6、连tb_name
    无对应关系则不显示
    select A.num, A.name, B.name
    from A,B
    Where A.nid = B.nid
 
    无对应关系则不显示
    select A.num, A.name, B.name
    from A inner join B
    on A.nid = B.nid
 
    Atb_name所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A left join B
    on A.nid = B.nid
 
    Btb_name所有显示，如果B中无对应关系，则值为null
    select A.num, A.name, B.name
    from A right join B
    on A.nid = B.nid
 
7、组合
    组合，自动处理重合
    select nickname
    from A
    union
    select name
    from B
 
    组合，不处理重合
    select nickname
    from A
    union all
    select name
    from B

日志

错误日志：
- log_error
- log_warnings
一般查询日志：
- general_log
- general_log_file
- log
- log_output
慢查询日志：
- log_output {TABLE|FILE|NONE}
- long_query_time
- log_slow_queries
- slow_query_log
- slow_query_log_file
二进制日志:
- 复制、即时点恢复
- 重放
- 二进制日志事件：
  - 基于语句：statement
  - 基于行：row
  - 混合方式：mixed

通配符

通配符的分类:
%百分号: tb_name示任何字符出现任意次数(可以是0次)。
下划线: tb_name示只能匹配单个字符,不能多也不能少,就是一个字符。

like操作符:
LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较。

%通配符可以匹配任意字符,但是不能匹配NULL
如果在使用like操作符时,后面的没有使用通用匹配符效果是和"="一致的。

原则：

不要过度使用通配符。如果其他操作符能达到相同的目的，应该使用其他操作符。
在确实需要使用通配符时，除非绝对有必要，否则不要把它们用在搜索模式的开始处。把通配符置于搜索模式的开始处，搜索起来是最慢的。
仔细注意通配符的位置。如果放错地方，可能不会返回想要的数.

查看字符集

show CHARSET;
show CHARACTER SET;
show COLLATION；

UTF－8：一个汉字＝3个字节
GBK：一个汉字＝2个字节
从mysql 8.0开始，mysql默认的CHARSET已经不再是Latin1了，改为了utf8mb4（参考链接），并且默认的COLLATE也改为了utf8mb4_0900_ai_ci

设置COLLATE可以在示例级别、库级别、tb_name级别、列级别、以及SQL指定。实例级别的COLLATE设置就是mysql配置文件或启动指令中的collation_connection系统变量。

假设全都显示设置，那么优先级顺序是 SQL语句 > 列级别设置 > tb_name级别设置 > 库级别设置 > 实例级别设置。也就是说列上所指定的COLLATE可以覆盖tb_name上指定的COLLATE，tb_name上指定的COLLATE可以覆盖库级别的COLLATE。如果没有指定，则继承下一级的设置。即列上面没有指定COLLATE，则该列的COLLATE和tb_name上设置的一样。

数据类型

数值类型

– a. 整型 ----------
类型字节范围（有符号位）
tinyint 1字节 -128 ~ 127 无符号位：0 ~ 255
smallint 2字节 -32768 ~ 32767
mediumint 3字节 -8388608 ~ 8388607
int 4字节
bigint 8字节
int(M) M表示总位数
- 默认存在符号位，unsigned 属性修改
- 显示宽度，如果某个数不够定义字段时设置的位数，则前面以0补填，zerofill 属性修改
例：int(5) 插入一个数’123’，补填后为’00123’
- 在满足要求的情况下，越小越好。
- 1表示bool值真，0表示bool值假。MySQL没有布尔类型，通过整型0和1表示。常用tinyint(1)表示布尔型。

– b. 浮点型 ----------
类型字节范围
float(单精度) 4字节
double(双精度) 8字节
浮点型既支持符号位 unsigned 属性，也支持显示宽度 zerofill 属性。
不同于整型，前后均会补填0.
定义浮点型时，需指定总位数和小数位数。
float(M, D) double(M, D)
M表示总位数，D表示小数位数。
M和D的大小会决定浮点数的范围。不同于整型的固定范围。
M既表示总位数（不包括小数点和正负号），也表示显示宽度（所有显示符号均包括）。
支持科学计数法表示。
浮点数表示近似值。

– c. 定点数 ----------
decimal – 可变长度
decimal(M, D) M也表示总位数，D表示小数位数。
保存一个精确的数值，不会发生数据的改变，不同于浮点数的四舍五入。
将浮点数转换为字符串来保存，每9位数字保存为4个字节。

字符串类型

– a. char, varchar ----------
char 定长字符串，速度快，但浪费空间
varchar 变长字符串，速度慢，但节省空间
M表示能存储的最大长度，此长度是字符数，非字节数。
不同的编码，所占用的空间不同。
char,最多255个字符，与编码无关。
varchar,最多65535字符，与编码有关。
一条有效记录最大不能超过65535个字节。
utf8 最大为21844个字符，gbk 最大为32766个字符，latin1 最大为65532个字符
varchar 是变长的，需要利用存储空间保存 varchar 的长度，如果数据小于255个字节，则采用一个字节来保存长度，反之需要两个字节来保存。
varchar 的最大有效长度由最大行大小和使用的字符集确定。
最大有效长度是65532字节，因为在varchar存字符串时，第一个字节是空的，不存在任何数据，然后还需两个字节来存放字符串的长度，所以有效长度是64432-1-2=65532字节。
例：若一个表定义为 CREATE TABLE tb(c1 int, c2 char(30), c3 varchar(N)) charset=utf8; 问N的最大值是多少？答：(65535-1-2-4-30*3)/3

– b. blob, text ----------
blob 二进制字符串（字节字符串）
tinyblob, blob, mediumblob, longblob
text 非二进制字符串（字符字符串）
tinytext, text, mediumtext, longtext
text 在定义时，不需要定义长度，也不会计算总长度。
text 类型在定义时，不可给default值

– c. binary, varbinary ----------
类似于char和varchar，用于保存二进制字符串，也就是保存字节字符串而非字符字符串。
char, varchar, text 对应 binary, varbinary, blob.

日期时间类型
一般用整型保存时间戳，因为PHP可以很方便的将时间戳进行格式化。
datetime 8字节日期及时间 1000-01-01 00:00:00 到 9999-12-31 23:59:59
date 3字节日期 1000-01-01 到 9999-12-31
timestamp 4字节时间戳 19700101000000 到 2038-01-19 03:14:07
time 3字节时间 -838:59:59 到 838:59:59
year 1字节年份 1901 - 2155
datetime YYYY-MM-DD hh:mm:ss
timestamp YY-MM-DD hh:mm:ss
YYYYMMDDhhmmss
YYMMDDhhmmss
YYYYMMDDhhmmss
YYMMDDhhmmss
date YYYY-MM-DD
YY-MM-DD
YYYYMMDD
YYMMDD
YYYYMMDD
YYMMDD
time hh:mm:ss
hhmmss
hhmmss
year YYYY
YY
YYYY
YY
枚举和集合

– 枚举(enum) ----------
enum(val1, val2, val3…)
在已知的值中进行单选。最大数量为65535.
枚举值在保存时，以2个字节的整型(smallint)保存。每个枚举值，按保存的位置顺序，从1开始逐一递增。
表现为字符串类型，存储却是整型。
NULL值的索引是NULL。
空字符串错误值的索引值是0。

– 集合（set） ----------
set(val1, val2, val3…)
create table tab ( gender set(‘男’, ‘女’, ‘无’) );
insert into tab values (‘男, 女’);
最多可以有64个不同的成员。以bigint存储，共8个字节。采取位运算的形式。
当创建表时，SET成员值的尾部空格将自动被删除。

约束

PRIMARY 主键
- 能唯一标识记录的字段，可以作为主键。
- 一个表只能有一个主键。
- 主键具有唯一性。
- 声明字段时，用 primary key 标识。
  也可以在字段列表之后声明
  例：create table tab ( id int, stu varchar(10), primary key (id));
- 主键字段的值不能为null。
- 主键可以由多个字段共同组成。此时需要在字段列表后声明的方法。
  例：create table tab ( id int, stu varchar(10), age int, primary key (stu, age));
UNIQUE 唯一索引（唯一约束）
使得某字段的值也不能重复。
NULL 约束
null不是数据类型，是列的一个属性。
表示当前列是否可以为null，表示什么都没有。
null, 允许为空。默认。
not null, 不允许为空。
insert into tab values (null, ‘val’);
– 此时表示将第一个字段的值设为null, 取决于该字段是否允许为null
DEFAULT 默认值属性
当前字段的默认值。
insert into tab values (default, ‘val’); – 此时表示强制使用默认值。
create table tab ( add_time timestamp default current_timestamp );
– 表示将当前时间的时间戳设为默认值。
current_date, current_time
AUTO_INCREMENT 自动增长约束
自动增长必须为索引（主键或unique）
只能存在一个字段为自动增长。
默认为1开始自动增长。可以通过表属性 auto_increment = x进行设置，或 alter table tbl auto_increment = x;
COMMENT 注释
例：create table tab ( id int ) comment ‘注释内容’;
FOREIGN KEY 外键约束
用于限制主表与从表数据完整性。
alter table t1 add constraint t1_t2_fk foreign key (t1_id) references t2(id);
– 将表t1的t1_id外键关联到表t2的id字段。
– 每个外键都有一个名字，可以通过 constraint 指定
存在外键的表，称之为从表（子表），外键指向的表，称之为主表（父表）。
作用：保持数据一致性，完整性，主要目的是控制存储在外键表（从表）中的数据。
MySQL中，可以对InnoDB引擎使用外键约束：
语法：
foreign key (外键字段） references 主表名 (关联字段) [主表记录删除时的动作] [主表记录更新时的动作]
此时需要检测一个从表的外键需要约束为主表的已存在的值。外键在没有关联的情况下，可以设置为null.前提是该外键列，没有not null。
可以不指定主表记录更改或更新时的动作，那么此时主表的操作被拒绝。
如果指定了 on update 或 on delete：在删除或更新时，有如下几个操作可以选择：
1. cascade，级联操作。主表数据被更新（主键值更新），从表也被更新（外键值更新）。主表记录被删除，从表相关记录也被删除。
2. set null，设置为null。主表数据被更新（主键值更新），从表的外键被设置为null。主表记录被删除，从表相关记录外键被设置成null。但注意，要求该外键列，没有not null属性约束。
3. restrict，拒绝父表删除和更新。
  注意，外键只被InnoDB存储引擎所支持。其他引擎是不支持的。

授权相关

all privileges  除grant外的所有权限
select          仅查权限
select,insert   查和插入权限
usage                   无访问权限
alter                   使用alter table
alter routine           使用alter procedure和drop procedure
create                  使用create table
create routine          使用create procedure
create temporary tables 使用create temporary tables
create user             使用create user、drop user、rename user和revoke
create view             使用create view
delete                  使用delete
drop                    使用drop table
execute                 使用call和存储过程
file                    使用select into outfile 和 load data infile
grant option            使用grant 和 revoke
index                   使用index
insert                  使用insert
lock tables             使用lock table
process                 使用show full processlist
select                  使用select
show databases          使用show databases
show view               使用show view
update                  使用update
reload                  使用flush
shutdown                使用mysqladmin shutdown(关闭MySQL)
super
replication client      服务器位置的访问
replication slave       由复制从属使用


对于目标数据库以及内部其他：
    数据库名.*           数据库中的所有
    数据库名.tb_name     指定数据库中的某张tb_name
    数据库名.存储过程     指定数据库中的存储过程
    *.*                  所有数据库
    用户名@IP地址        用户只能在改IP下才能访问
    用户名@192.168.1.%   用户只能在改IP段下才能访问(通配符%tb_name示任意)
    用户名@%             用户可以再任意IP下访问(默认IP地址为%)

查询

JOIN 按照功能大致分为如下三类：
INNER JOIN（内连接,或等值连接）：获取两个tb_name中字段匹配关系的记录。
LEFT JOIN（左连接）：获取左tb_name所有记录，即使右tb_name没有对应匹配的记录。
RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右tb_name所有记录，即使左tb_name没有对应匹配的记录。

select tb1.field1, tb1.field2,…tb2.fieldX FROM tb1 tb1_alias JOIN tb2 tb2_alias on tb1.special_field=tb2.special_field

MySQL 同样也支持其他正则tb_name达式的匹配， MySQL中使用 REGEXP 操作符来进行正则tb_name达式匹配

select field FROM tb_name WHERE condition REGEXP ‘pattern’;

select DISTINCT field FROM tb_name;

UPDATE tb_name SET field=new_value WHERE field=old_Value

单tb_name查询
多tb_name查询
子查询
自然连接
内连接
外连接
自连接
左连接
右连接

多tb_name查询：
连接：
交叉连接：笛卡尔乘积
自然连接(内连接)：条件WHERE
外连接：
左外连接：… LEFT JOIN … ON …
右外连接: … RIGHT JOIN … ON …
自连接：条件WHERE

子查询：
比较操作中使用子查询：子查询只能返回单个值；
IN(): 使用子查询；
在FROM中使用子查询；

联合查询：
UNION

关系运算：
投影：只输出指定属性
选择：只输出符合条件的行
自然连接：具有相同名字的属性上所有取值相同的行；
笛卡尔积(交叉连接)：
(a+b)*(c+d)=ac+ad+bc+bd
外连接
内连接
完全连接
并：集合运算

视图：
存储下来的select语句；
基于基tb_name的查询结果；

物化视图：select 将select的结果保存下来，但是基tb_name更新了，物化视图就凉了

mysql视图上不能创建索引

用户管理

用户

创建用户的三种方式:

create user user_name identified by ‘password’;
grant usage on . to username@‘hostname’;
insert into mysql.user set password=password(“new password”) where user=‘user_name’;

权限

重置数据库 root 密码

启动mysqld_safe时传递两个参数：
–skip-grant-tables
–skip-networking

通过更新授权tb_name方式(insert into mysql.user set password=password() where user='root')直接修改其密码，而后移除此两个选项重启服务器。

建表规范

– Normal Format, NF
- 每个表保存一个实体信息
- 每个具有一个ID字段作为主键
- ID主键 + 原子表
– 1NF, 第一范式
字段不能再分，就满足第一范式。
– 2NF, 第二范式
满足第一范式的前提下，不能出现部分依赖。
消除符合主键就可以避免部分依赖。增加单列关键字。
– 3NF, 第三范式
满足第二范式的前提下，不能出现传递依赖。
某个字段依赖于主键，而有其他字段依赖于该字段。这就是传递依赖。
将一个实体信息的数据放在一个表内实现。

表维护

# 分析和存储表的关键字分布
ANALYZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE 表名 ...
# 检查一个或多个表是否有错误
CHECK TABLE tbl_name [, tbl_name] ... [option] ...
option = {QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
# 整理数据文件的碎片
OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [, tbl_name] ...

视图

视图是一个虚拟表，其内容由查询定义。同真实的表一样，视图包含一系列带有名称的列和行数据。但是，视图并不在数据库中以存储的数据值集形式存在。行和列数据来自由定义视图的查询所引用的表，并且在引用视图时动态生成。
视图具有表结构文件，但不存在数据文件。
对其中所引用的基础表来说，视图的作用类似于筛选。定义视图的筛选可以来自当前或其它数据库的一个或多个表，或者其它视图。通过视图进行查询没有任何限制，通过它们进行数据修改时的限制也很少。
视图是存储在数据库中的查询的sql语句，它主要出于两种原因：安全原因，视图可以隐藏一些数据，如：社会保险基金表，可以用视图只显示姓名，地址，而不显示社会保险号和工资数等，另一原因是可使复杂的查询易于理解和使用。

-- 创建视图
CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] VIEW view_name [(column_list)] AS select_statement
    - 视图名必须唯一，同时不能与表重名。
    - 视图可以使用select语句查询到的列名，也可以自己指定相应的列名。
    - 可以指定视图执行的算法，通过ALGORITHM指定。
    - column_list如果存在，则数目必须等于SELECT语句检索的列数
-- 查看结构
    SHOW CREATE VIEW view_name
-- 删除视图
    - 删除视图后，数据依然存在。
    - 可同时删除多个视图。
    DROP VIEW [IF EXISTS] view_name ...
-- 修改视图结构
    - 一般不修改视图，因为不是所有的更新视图都会映射到表上。
    ALTER VIEW view_name [(column_list)] AS select_statement
-- 视图作用
    1. 简化业务逻辑
    2. 对客户端隐藏真实的表结构
-- 视图算法(ALGORITHM)
    MERGE       合并
        将视图的查询语句，与外部查询需要先合并再执行！
    TEMPTABLE   临时表
        将视图执行完毕后，形成临时表，再做外层查询！
    UNDEFINED   未定义(默认)，指的是MySQL自主去选择相应的算法。

事务

事务是指逻辑上的一组操作，组成这组操作的各个单元，要不全成功要不全失败。
- 支持连续SQL的集体成功或集体撤销。
- 事务是数据库在数据晚自习方面的一个功能。
- 需要利用 InnoDB 或 BDB 存储引擎，对自动提交的特性支持完成。
- InnoDB被称为事务安全型引擎。
– 事务开启
START TRANSACTION; 或者 BEGIN;
开启事务后，所有被执行的SQL语句均被认作当前事务内的SQL语句。
– 事务提交
COMMIT;
– 事务回滚
ROLLBACK;
如果部分操作发生问题，映射到事务开启前。
– 事务的特性
1. 原子性（Atomicity）
事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。
2. 一致性（Consistency）
事务前后数据的完整性必须保持一致。
- 事务开始和结束时，外部数据一致
- 在整个事务过程中，操作是连续的
3. 隔离性（Isolation）
多个用户并发访问数据库时，一个用户的事务不能被其它用户的事物所干扰，多个并发事务之间的数据要相互隔离。
4. 持久性（Durability）
一个事务一旦被提交，它对数据库中的数据改变就是永久性的。
– 事务的实现
1. 要求是事务支持的表类型
2. 执行一组相关的操作前开启事务
3. 整组操作完成后，都成功，则提交；如果存在失败，选择回滚，则会回到事务开始的备份点。
– 事务的原理
利用InnoDB的自动提交(autocommit)特性完成。
普通的MySQL执行语句后，当前的数据提交操作均可被其他客户端可见。
而事务是暂时关闭“自动提交”机制，需要commit提交持久化数据操作。
– 注意
1. 数据定义语言（DDL）语句不能被回滚，比如创建或取消数据库的语句，和创建、取消或更改表或存储的子程序的语句。
2. 事务不能被嵌套
– 保存点
SAVEPOINT 保存点名称 – 设置一个事务保存点
ROLLBACK TO SAVEPOINT 保存点名称 – 回滚到保存点
RELEASE SAVEPOINT 保存点名称 – 删除保存点
– InnoDB自动提交特性设置
SET autocommit = 0|1; 0表示关闭自动提交，1表示开启自动提交。
- 如果关闭了，那普通操作的结果对其他客户端也不可见，需要commit提交后才能持久化数据操作。
- 也可以关闭自动提交来开启事务。但与START TRANSACTION不同的是，
SET autocommit是永久改变服务器的设置，直到下次再次修改该设置。(针对当前连接)
而START TRANSACTION记录开启前的状态，而一旦事务提交或回滚后就需要再次开启事务。(针对当前事务)

备份

MySQL的备份和还原

备份：副本
RAID1，RAID10：保证硬件损坏而不会业务中止；
    DROP TABLE mydb.tb1;

备份类型：
    热备份、温备份和冷备份
        热备份：读、写不受影响；
        温备份：仅可以执行读操作；
        冷备份：离线备份；读、写操作均中止；

    物理备份和逻辑备份
        物理备份：复制数据文件；
        逻辑备份：将数据导出至文本文件中；

    完全备份、增量备份和差异备份；
        完全备份：备份全部数据；
        增量备份：仅备份上次完全备份或增量备份以后变化的数据；
        差异备份：仅备份上次完全备份以来变化的数据；

在线：物理完全备份


备份，将数据的结构与表内数据保存起来。
利用 mysqldump 指令完成。
# 导出
mysqldump [options] db_name [tables]
mysqldump [options] ---database DB1 [DB2 DB3...]
mysqldump [options] --all--database
1. 导出一张表
　　mysqldump -u用户名 -p密码 库名 表名 > 文件名(D:/a.sql)
2. 导出多张表
　　mysqldump -u用户名 -p密码 库名 表1 表2 表3 > 文件名(D:/a.sql)
3. 导出所有表
　　mysqldump -u用户名 -p密码 库名 > 文件名(D:/a.sql)
4. 导出一个库
　　mysqldump -u用户名 -p密码 --lock-all-tables --database 库名 > 文件名(D:/a.sql)
可以-w携带WHERE条件
-- 导入
1. 在登录mysql的情况下：
　　source  备份文件
2. 在不登录的情况下
　　mysql -u用户名 -p密码 库名 < 备份文件



select * into outfile 文件地址 [控制格式] from 表名;   -- 导出表数据

load data [local] infile 文件地址 [replace|ignore] into table 表名 [控制格式]; -- 导入数据
    生成的数据默认的分隔符是制表符
    local未指定，则数据文件必须在服务器上
    replace 和 ignore 关键词控制对现有的唯一键记录的重复的处理
-- 控制格式
fields  控制字段格式
默认：fields terminated by '\t' enclosed by '' escaped by '\\'
    terminated by 'string'  -- 终止
    enclosed by 'char'      -- 包裹
    escaped by 'char'       -- 转义
    -- 示例：
        SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        FROM test_table;
lines   控制行格式
默认：lines terminated by '\n'
    terminated by 'string'  -- 终止

SQL编程


--// 局部变量 ----------
-- 变量声明
    declare var_name[,...] type [default value]
    这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个default子句。值可以被指定为一个表达式，不需要为一个常数。如果没有default子句，初始值为null。
-- 赋值
    使用 set 和 select into 语句为变量赋值。
    - 注意：在函数内是可以使用全局变量（用户自定义的变量）


--// 全局变量 ----------
-- 定义、赋值
set 语句可以定义并为变量赋值。
set @var = value;
也可以使用select into语句为变量初始化并赋值。这样要求select语句只能返回一行，但是可以是多个字段，就意味着同时为多个变量进行赋值，变量的数量需要与查询的列数一致。
还可以把赋值语句看作一个表达式，通过select执行完成。此时为了避免=被当作关系运算符看待，使用:=代替。（set语句可以使用= 和 :=）。
select @var:=20;
select @v1:=id, @v2=name from t1 limit 1;
select * from tbl_name where @var:=30;
select into 可以将表中查询获得的数据赋给变量。
    -| select max(height) into @max_height from tb;
-- 自定义变量名
为了避免select语句中，用户自定义的变量与系统标识符（通常是字段名）冲突，用户自定义变量在变量名前使用@作为开始符号。
@var=10;
    - 变量被定义后，在整个会话周期都有效（登录到退出）



--// 控制结构 ----------
-- if语句
if search_condition then
    statement_list   
[elseif search_condition then
    statement_list]
...
[else
    statement_list]
end if;
-- case语句
CASE value WHEN [compare-value] THEN result
[WHEN [compare-value] THEN result ...]
[ELSE result]
END
-- while循环
[begin_label:] while search_condition do
    statement_list
end while [end_label];
- 如果需要在循环内提前终止 while循环，则需要使用标签；标签需要成对出现。
    -- 退出循环
        退出整个循环 leave
        退出当前循环 iterate
        通过退出的标签决定退出哪个循环



--// 内置函数 ----------
-- 数值函数
abs(x)          -- 绝对值 abs(-10.9) = 10
format(x, d)    -- 格式化千分位数值 format(1234567.456, 2) = 1,234,567.46
ceil(x)         -- 向上取整 ceil(10.1) = 11
floor(x)        -- 向下取整 floor (10.1) = 10
round(x)        -- 四舍五入去整
mod(m, n)       -- m%n m mod n 求余 10%3=1
pi()            -- 获得圆周率
pow(m, n)       -- m^n
sqrt(x)         -- 算术平方根
rand()          -- 随机数
truncate(x, d)  -- 截取d位小数
-- 时间日期函数
now(), current_timestamp();     -- 当前日期时间
current_date();                 -- 当前日期
current_time();                 -- 当前时间
date('yyyy-mm-dd hh:ii:ss');    -- 获取日期部分
time('yyyy-mm-dd hh:ii:ss');    -- 获取时间部分
date_format('yyyy-mm-dd hh:ii:ss', '%d %y %a %d %m %b %j'); -- 格式化时间
unix_timestamp();               -- 获得unix时间戳
from_unixtime();                -- 从时间戳获得时间
-- 字符串函数
length(string)          -- string长度，字节
char_length(string)     -- string的字符个数
substring(str, position [,length])      -- 从str的position开始,取length个字符
replace(str ,search_str ,replace_str)   -- 在str中用replace_str替换search_str
instr(string ,substring)    -- 返回substring首次在string中出现的位置
concat(string [,...])   -- 连接字串
charset(str)            -- 返回字串字符集
lcase(string)           -- 转换成小写
left(string, length)    -- 从string2中的左边起取length个字符
load_file(file_name)    -- 从文件读取内容
locate(substring, string [,start_position]) -- 同instr,但可指定开始位置
lpad(string, length, pad)   -- 重复用pad加在string开头,直到字串长度为length
ltrim(string)           -- 去除前端空格
repeat(string, count)   -- 重复count次
rpad(string, length, pad)   --在str后用pad补充,直到长度为length
rtrim(string)           -- 去除后端空格
strcmp(string1 ,string2)    -- 逐字符比较两字串大小
-- 流程函数
case when [condition] then result [when [condition] then result ...] [else result] end   多分支
if(expr1,expr2,expr3)  双分支。
-- 聚合函数
count()
sum();
max();
min();
avg();
group_concat()
-- 其他常用函数
md5();
default();
-- 字符连接函数
concat(str1,str2,...])
concat_ws(separator,str1,str2,...)

-- 分支语句
if 条件 then
    执行语句
elseif 条件 then
    执行语句
else
    执行语句
end if;

-- 修改最外层语句结束符
delimiter 自定义结束符号
    SQL语句
自定义结束符号
delimiter ;     -- 修改回原来的分号

-- 语句块包裹
begin
    语句块
end

--// 存储函数，自定义函数 ----------
-- 新建
    CREATE FUNCTION function_name (参数列表) RETURNS 返回值类型
        函数体
    - 函数名，应该合法的标识符，并且不应该与已有的关键字冲突。
    - 一个函数应该属于某个数据库，可以使用db_name.funciton_name的形式执行当前函数所属数据库，否则为当前数据库。
    - 参数部分，由"参数名"和"参数类型"组成。多个参数用逗号隔开。
    - 函数体由多条可用的mysql语句，流程控制，变量声明等语句构成。
    - 多条语句应该使用 begin...end 语句块包含。
    - 一定要有 return 返回值语句。
-- 删除
    DROP FUNCTION [IF EXISTS] function_name;
-- 查看
    SHOW FUNCTION STATUS LIKE 'partten'
    SHOW CREATE FUNCTION function_name;
-- 修改
    ALTER FUNCTION function_name 函数选项

--// 存储过程，自定义功能 ----------
-- 定义
存储存储过程 是一段代码（过程），存储在数据库中的sql组成。
一个存储过程通常用于完成一段业务逻辑，例如报名，交班费，订单入库等。
而一个函数通常专注与某个功能，视为其他程序服务的，需要在其他语句中调用函数才可以，而存储过程不能被其他调用，是自己执行 通过call执行。
-- 创建
CREATE PROCEDURE sp_name (参数列表)
    过程体
参数列表：不同于函数的参数列表，需要指明参数类型
IN，表示输入型
OUT，表示输出型
INOUT，表示混合型
注意，没有返回值。


/* 存储过程 */ ------------------
存储过程是一段可执行性代码的集合。相比函数，更偏向于业务逻辑。
调用：CALL 过程名
-- 注意
- 没有返回值。
- 只能单独调用，不可夹杂在其他语句中
-- 参数
IN|OUT|INOUT 参数名 数据类型
IN      输入：在调用过程中，将数据输入到过程体内部的参数
OUT     输出：在调用过程中，将过程体处理完的结果返回到客户端
INOUT   输入输出：既可输入，也可输出
-- 语法
CREATE PROCEDURE 过程名 (参数列表)
BEGIN
    过程体
END

本文链接： http://jsunnyr.github.io/2020/01/07/db/mysql/mysql-basic/
版权声明： 本博客所有文章除特别声明外，均采用 CC BY 4.0 CN协议许可协议。转载请注明出处！

素丶素丶丶wodetian

忍一时越想越气，退一步越想越亏。