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']endfrom 表名
六、存储过程
存储过程创建的格式为：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