mysql 语言学习

整理了一下 mysql 操作语言，不是很全，部分地方也许需要修改，先放上来，有时间再慢慢完善。

一、数据库操作

连接数据库

$ sudo mysql [-h ip] -u root -p [-P 3306] 

初始化数据库

$ mysql_secure_installation

备份数据库

# 备份一个或多个数据库
$ sudo mysqldump [options] --databases 数据库名,... > 备份文件

# 备份所有数据库
$ sudo mysqldump [options] --all-databases > 备份文件

# 备份一个或多个表
$ sudo mysqldump [options] 数据库名 [表名,...] > 备份文件

常用的 options：

• -u, --user= username ：数据库用户名。
• -p, --password[= password]：数据库密码。
• -P, --port= portnumber ：指定端口。
• -h, --host= hostname ：指定主机名。
• -r, --result-file= filename ：将导出结果保存到指定的文件中，等同于“>”。
• -t：只备份数据。
• -d：只备份表结构。

还原数据库

$ sudo mysql [-h 主机名] [-P 3306] -u 用户名 -p 数据库名 < 已备份文件

二、数据库系统变量操作

mysql 系统变量有全局变量和会话变量两类。

• 全局变量(global variables)的修改只在当前 mysql 服务有效，当 mysql 服务重启后变量将重新初始化；
• 会话变量(session variables)的修改只在当前会话中有效，关闭会话并重新打开会话后该变量设置失效。

-- 查看所有全局|会话变量，省略参数则默认为会话变量
mysql> show [global|session] variables;

-- 查看字符集变量
mysql> show variables like '%character%';    

-- 查看密码规则变量
mysql> show variables like 'validate_password%';

-- 修改密码策略(0 low,1 medium,2 strong)
mysql> set global validate_password.policy=0;

注意：用 set 修改全局变量，mysql重启失效；修改会话变量后，关闭会话失效。

三、数据库用户管理

mysql 的用户信息(用户名、允许访问的地址、密码验证策略、有关权限等等)存储在 mysql.user 表中。

-- 创建数据库用户
mysql> create user '用户名'@'主机名' identified [with 密码验证规则] by '密码';

主机名表示在哪台主机上可以登录 mysql，可以是 ip 地址，并且可以使用 % 通配符。其中：localhost 表示本机，% 通配符则表示任意 ip。
通过 create 创建的用户没有任何权限，需要通过 grant 来对用户授权。

-- 给数据库用户赋权
mysql> grant privileges on 数据库名.数据库对象名 to '用户名'@'主机名' [with grant option];

priveleges 表示用户的操作权限，如 select,insert,update 等，多个权限之间?逗号分开。如果要授予所有的权限则使用 all。
数据对象名主要为：表、视图、存储过程、存储函数等。*.*表示所有任意数据库的任意对象。
with grant option ：可选参数，表示该用户可以给将??拥有的权限授权给别?。

-- 刷新权限,赋权之后通常要刷新权限
mysql> flush privileges;

-- 查看用户权限
mysql> show grants [for '用户名'@'主机名'];

-- 撤销用户权限，参数与 grant 赋权的参数含义相同
mysql> revoke privileges on 数据库名.表名 from '用户名'@'主机名';

-- 修改数据库用户
mysql> alter user '用户名'@'主机名' identified [with 密码验证规则] by '密码';

-- 删除数据库用户
mysql> drop user [if exists] '用户名'@'主机名';

-- 因为数据库用户存储在mysql.user表中，因此可以通过表操作语言来删除，但记得要调用flush privileges 来刷新权限。
mysql> delete from mysql.user where user='用户名' and host='主机名';
mysql> flush privileges;

四、数据库管理

-- 创建数据库
mysql> create database [if not exists] 数据库名 [character set 字符集名];

-- 显示所有数据库
mysql> show databases;

-- 选择数据库
mysql> use 数据库名;

-- 修改数据库
mysql> alter database 库名 character set 字符集名;

-- 删除数据库
mysql> drop database [if exists] 库名;

五、表相关语法

-- 创建表
mysql> create table [if not exists] 表名(…字段名 字段类型 [约束],...);

-- 查看表结构
mysql> desc table_name;

-- 查看所有表
mysql> show tables;

-- 添加列
mysql> alter table 表名 add column 列名 类型 [first|after 字段名];

-- 修改列类型&约束
mysql> alter table 表名 modify column 列名 新类型 [新约束];

-- 修改列名称
mysql> alter table 表名 change column 旧列名 新列名 类型;

-- 删除列
mysql> alter table 表名 drop column 列名;

-- 修改表名
mysql> alter table 表名 rename [to] 新表名;

-- 删除表
mysql> drop table[if exists] 表名;

-- 复制表
mysql> create table 表名 like 旧表;

-- 复制表结构&数据
mysql> create table 表名 select 查询列表 from 旧表[where 筛选];

-- 创建索引
mysql> create [unique|fulltext|spatial] index index_name[using index_type] on table_name (index_col_name,...)

-- 删除索引
mysql> alter table table_name drop index index_name;

-- 查看索引
mysql> show index from table_name;

插入数据语法

-- 插入多行
mysql> insert into 表名(字段名,…) values(值,…);

-- 子查询插入
mysql> insert into 表名 (查询语句);

删除数据语法

mysql> delete from 表名 [where 筛选条件][limit 条目数];

修改数据语法

mysql> update 表名 set 字段=值,字段=值 [where 筛选条件];

查询数据语法

-- 综合
mysql> select 查询列表 from 表1 [别名 连接类型 join 表2 on 连接条件 where 筛选 group by 分组列表 having 筛选 order by排序列表 limit 起始条目索引，条目数];

-- 通配符
mysql> select 查询列表 from 表名 where 列名 like 'test%'

-- 子查询
mysql> select 查询列表 from 表名 where 列名 in（子查询）

-- 子查询为真才有结果
mysql> select 查询列表 form 表名 where exists （子查询）

表连接查询语法

-- 返回两个表中联结字段相等的行
mysql> select * from table1 A inner join table2 B on A.id=B.id;

-- 左表中的所有记录和右表中联结字段
mysql> select * from table1 A left join table2 B on A.id=B.id;相等的记录

-- 右表中的所有记录和左表中联结字段相等的记录
mysql> select * from table1 A right join table2 B on A.id=B.id;

视图相关语法

-- 创建视图
mysql> create view 视图名 as select 语句；

-- 删除视图
mysql> drop view 视图名

union集语法

-- 不重复并集
mysql> [SELECT 语句 1] UNION [SELECT 语句 2];

-- 重复并集
mysql> [SELECT 语句 1] UNION ALL [SELECT 语句 2];

case语法

select case （列名） 
      when '条件1' then '结果1' 
      when '条件2' then '结果2'
      [else '结果N']
      end
      from 表名

六、存储过程

存储过程创建的格式为：CREATE PROCEDURE 过程名 ([过程参数[,...]])[特性 ...] 过程体 ，其中过程体是以 BEGIN 作为开始标志，以 END 作为结束标志的，下面进行简单的演示：

创建存储过程

mysql> use mysql;                                       --选择 mysql 数据库  
mysql> DELIMITER $$                                     --声明分隔符为 $$ （默认分隔符是分号，声明 $$ 分隔符后再碰到分号，就不会立即执行语句了)
mysql> CREATE PROCEDURE Proc(IN p1 varchar(32))         --创建存储过程，过程名为Proc，带字符型输入参数 p1
    -> BEGIN                                            --过程体以关键字 BEGIN 开始
    -> select user,host from user where user=p1;        --过程体语句,查找用户名为 p1 的用户
    -> END                                              --过程体以关键字 END 结束
    -> $$                                               --输入 $$ 分隔符，执行上面的语句，也就是完成了存储过程的创建
mysql> DELIMITER ;                                      --将语句的结束符号恢复为分号

调用存储过程

mysql> SET @p1='root'                                   --因此上面的存储过程有一个输入参数，所以先定义一个变量 @p1
mysql> call Proc(@p1);                                  --调用存储过程
+------+-----------+
| user | host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

查看存储过程

mysql> show procedure status where db='mysql';     --查看 mysql 数据库中所有的存储过程状态信息
+-------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| Db    | Name | Type      | Definer        | Modified            | Created             | Security_type | Comment | character_set_client | collation_connection | Database Collation |
+-------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
| mysql | proc | PROCEDURE | root@localhost | 2023-12-09 22:03:36 | 2023-12-09 22:03:36 | DEFINER       |         | utf8mb4              | utf8mb4_0900_ai_ci   | utf8mb4_0900_ai_ci |
+-------+------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+
1 row in set (0.00 sec)

mysql> select routine_name from information_schema.routines where routine_schema='mysql';    --查看 mysql 数据库中所有的存储过程名
+--------------+
| ROUTINE_NAME |
+--------------+
| proc         |
+--------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE PROCEDURE mysql.Proc;           --查看某个存储过程的具体信息

删除存储过程

mysql> DROP PROCEDURE IF EXISTS Proc;            --仅当存在时删除，如果指定的过程不存在，则产生一个错误

有关函数

1）数学函数

SQRT(number)                        --求平方根
ABS(number)                         --绝对值
CEILING(number2)                    --向上取整
CONV(number2,from_base,to_base)     --进制转换
FLOOR(number2)                      --向下取整
FORMAT(number,decimal_places)       --保留小数位数
BIN(decimal_number)                 --十进制转二进制
HEX (DecimalNumber)                 --十进制转十六进制，HEX()中还传入字符串，返回值是其ASC-11码，如HEX('DEF')返回4142143
GREATEST(x1,x2…)                   --求最大值
LEAST(number , number2 [,..])       --求最小值
MOD(numerator ,denominator)         --求余
POWER(number ,power)                --求指数
RAND([seed])                        --随机数
ROUND(number,decimals)              --四舍五入,decimals 为小数位数

2）聚合函数

avg() --平均值
count() --个数
min() --最大值
max() --最小值
sum() --求和

3）字符串函数

SPACE(count)                 --生成count个空格
REVERSE(str)                 --字符串反转
CHARSET(str)                 --返回字串字符集
CONCAT(str [,... ])          --连接字串
LCASE(str )                  --转换成小写，也可用lower(str)
UCASE(str )                  --转换成大写，也可用UPPER(str )
LEFT (str ,length )          --从str中的左边起取length个字符
RIGHT(str ,length )          --从str中的右边起取length个字符
LENGTH (str )                --返回长度
LOAD_FILE (file_name )       --从文件读取内容
INSTR (str,substring )       --返回substring首次在str中出现的位置,不存在返回0
LOCATE (substring , string [,start_position ] ) --同INSTR,但可指定开始位置
LPAD (str ,length ,pad )     --重复用pad加在string开头,直到字串长度为length
LTRIM (str )                 --去除前端空格
REPEAT (str ,count )         --重复count次
REPLACE (str ,search_str ,replace_str )           --在str中用replace_str替换search_str
RPAD (str ,length ,pad)      --在str后用pad补充,直到长度为length
RTRIM (str )                 --去除后端空格
STRCMP (str1 ,str2 )         --逐字符比较两字串大小,返回-1,0,1
SUBSTRING (str , position [,length ])           --从str的position开始,取length个字符

4) 日期时间函数

ADDTIME (date2 ,time_interval )               --将time_interval加到date2
CONVERT_TZ (datetime2 ,fromTZ ,toTZ )         --转换时区
CURRENT_DATE ( )                              --当前日期
CURRENT_TIME ( )                              --当前时间
CURRENT_TIMESTAMP ( )                         --当前时间戳
DATE (datetime )                              --返回datetime的日期部分
DATE_ADD (date2 , INTERVAL d_value d_type )   --在date2中加上日期或时间
DATE_FORMAT (datetime ,FormatCodes )          --使用formatcodes格式显示datetime
DATE_SUB (date2 , INTERVAL d_value d_type )   --在date2上减去一个时间
DATEDIFF (date1 ,date2 )                      --两个日期差
DAY (date )                                   --返回日期的天
DAYNAME (date )                               --英文星期
DAYOFWEEK (date )                             --星期(1-7) ,1为星期天
DAYOFYEAR (date )                             --一年中的第几天
EXTRACT (interval_name FROM date )            --从date中提取日期的指定部分
MAKEDATE (year ,day )                         --给出年及年中的第几天,生成日期串
MAKETIME (hour ,minute ,second )              --生成时间串
MONTHNAME (date )                             --英文月份名
NOW ( )                                       --当前时间
SEC_TO_TIME (seconds )                        --秒数转成时间
STR_TO_DATE (string ,format )                 --字串转成时间,以format格式显示
TIMEDIFF (datetime1 ,datetime2 )              --两个时间差
TIME_TO_SEC (time )                           --时间转秒数]
WEEK (date_time [,start_of_week ])            --第几周
YEAR (datetime )                              --年份
DAYOFMONTH(datetime)                          --月的第几天
HOUR(datetime)                                --小时
LAST_DAY(date)                                --date的月的最后日期
MICROSECOND(datetime)                         --微秒
MONTH(datetime)                               --月
MINUTE(datetime)                              --分返回符号,正负或0