MySQL基础操作

一、连接MySQL

shell

mysql -h 127.0.0.1 -P 3306 -uroot -p

备注

-h, --host：指定服务器IP或域名，如果是链接本机的可以省略
-P, --port：指定端口，如果是默认端口3306可以省略
-u, --user：指定用户
-p, --password：密码不要输入，手动敲

二、数据库操作

1.创建数据库

shell

create database xxx
# 指定字符集
CREATE DATABASE ufs DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

2.删库

shell

drop database xxx
# 使用IF EXIST
DROP DATABASE IF EXISTS ufs;

3.切换数据库

shell

use ufs;

4.查看数据库

shell

show database;

三、表的操作

1.查看当前库所有的表

shell

show tables;

2.创建一张表

sql

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`(
    `id`          INT AUTO_INCREMENT                COMMENT '主键ID',
    `nick_name`   VARCHAR(20)                       COMMENT '昵称',
    `birthday`    DATE                              COMMENT '生日',
    `e_mail`      VARCHAR(20)                       COMMENT '邮箱',
    `state`       TINYINT     NOT NULL DEFAULT 1    COMMENT '状态(0-异常，1-正常)',
    `update_time` DATETIME COMMENT '更新时间',
    `create_time` DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    PRIMARY KEY (`id`)
) COMMENT='用户信息表';

3.编辑表

alter table 表名 add(添加)|modify(修改)|drop(删除) 列名类型;

添加字段

sql

ALTER TABLE t_user ADD phone VARCHAR(20) COMMENT '创建时间';

修改字段

sql

ALTER TABLE t_user MODIFY update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间';

查看表结构

sql

desc tablename;

更改表名称

sql

rename table 原表名 to 新表名;

查看边的创建语句

sql

show create table 表名

四、用户和权限

4.1用户操作

1.查询用户

sql

mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user             | host      | authentication_string                                                  | plugin                |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| root             | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

host：

localhost：用户只能本地登录
%：用户既可以本地登录，也可以远程登录

2.创建用户

sql

mysql> create user 'ufs'@'%' identified by '123456';
Query OK, 0 rows affected (0.05 sec)

mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user             | host      | authentication_string                                                  | plugin                |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| root             | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
| ufs              | %         | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9                              | mysql_native_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
5 rows in set (0.00 sec)

3.修改用户密码

sql

mysql> alter user 'ufs'@'%' identified with mysql_native_password by 'ufs';
Query OK, 0 rows affected (0.01 sec)

4.删除用户

sql

mysql> drop user 'ufs'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> select user,host,authentication_string,plugin from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| user             | host      | authentication_string                                                  | plugin                |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
| root             | %         | *81F5E21E35407D884A6CD4A731AEBFB6AF209E1B                              | mysql_native_password |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+------------------+-----------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)

4.2 权限操作

1.查询用户权限

sql

mysql> show grants for 'ufs'@'%';
+---------------------------------+
| Grants for ufs@%                |
+---------------------------------+
| GRANT USAGE ON *.* TO `ufs`@`%` |
+---------------------------------+
1 row in set (0.00 sec)

2.给用户授权

ufs用户添加ufs库中的t_user和t_file两张表的权限

sql

mysql> grant all privileges on ufs.t_user to 'ufs'@'%';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on ufs.t_file to 'ufs'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'ufs'@'%';
+-----------------------------------------------------+
| Grants for ufs@%                                    |
+-----------------------------------------------------+
| GRANT USAGE ON *.* TO `ufs`@`%`                     |
| GRANT ALL PRIVILEGES ON `ufs`.`t_file` TO `ufs`@`%` |
| GRANT ALL PRIVILEGES ON `ufs`.`t_user` TO `ufs`@`%` |
+-----------------------------------------------------+
3 rows in set (0.00 sec)

授予用户所有库的所有表所有权限

sql

mysql> grant all privileges on *.* to 'ufs'@'%';
Query OK, 0 rows affected (0.01 sec)

授予用户查询权限

sql

mysql> GRANT SELECT ON *.* TO 'ufs'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for 'ufs'@'%';
+----------------------------------+
| Grants for ufs@%                 |
+----------------------------------+
| GRANT SELECT ON *.* TO `ufs`@`%` |
+----------------------------------+
1 row in set (0.00 sec)

3.撤销权限

一般不需要撤销，一般都是直接删用户，重新创建一个用户

sql

mysql> revoke all privileges on ufs.t_file from 'ufs'@'%';
ERROR 1227 (42000): Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
mysql>
mysql> grant system_user on *.* to 'root'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> revoke all privileges on ufs.t_file from 'ufs'@'%';
Query OK, 0 rows affected (0.00 sec)

五、索引操作

索引优化其实本质就是索引删除重建的操作

索引设计原则

针对于数据量较大，且查询比较频繁的表建立索引
针对于常作为查询条件(where)、排序（order by）、分组（group by）操作的字段建立索引
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度高，使用索引的效率越高
如果是字符串类型的字段，字段的长度较长，可以针对字段的特点，建立前缀索引
尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省储存空间，避免回表，提高查询效率
要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
如果索引列不能储存NULL值，请在创建表时使用NOT NULL约束它，当优化器知道每列是否包含NULL值时，它可以更好地确定那个索引最有效地用于查询。

1.创建索引

sql

CREATE INDEX t_user_idx_create_time ON `t_user` (create_time);

2.删除索引

sql

DROP INDEX t_user_idx_create_time ON `t_user`;

3.查看索引

sql

show index from t_user

六、外键操作 FK

外键约束：比如有学生表t_student和老师t_class两张表，class_id是学生属于哪个班的字段，对应班级表的id，这种关系就属于外键关系

shell

mysql> select * from t_class;
+----+-----------------+
| id | class_name      |
+----+-----------------+
|  1 | 三年级一班      |
|  2 | 三年级二班      |
+----+-----------------+
2 rows in set (0.00 sec)
mysql> select * from t_student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 李子明    |        1 |
|  2 | 李大明    |        1 |
|  3 | 王二蛋    |        2 |
+----+-----------+----------+
3 rows in set (0.00 sec)

1.添加外键

语法

alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 父表 (父表字段）on update 更新行为 on delete 删除行为

外键数据的更新和删除行为

项目	说明
no action	父表删除更新记录时，首先检查该记录是否有外键约束，有则不允许删除
restrict	和 no action 一致
cascade	有外键约束同步删除子表的数据，比如删除三年级一班，则学生也删除
set null	有外键则设置子表中该外键的值为null,这就要求该外键记录允许null
set default	父表有变更时，子表将外键列设置成一个默认的值(Innodb不支持)

添加外键约束：给t_student表中的class_id设置为t_class表的外键

sql

mysql> alter table t_student add constraint 'fk_class' foreign key(class_id) references t_class (id);
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

查看表结构：发现已经有了外键

sql

mysql> show create table t_student \G
*************************** 1. row ***************************
       Table: t_student
Create Table: CREATE TABLE `t_student` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `name` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '姓名',
  `class_id` int DEFAULT NULL COMMENT '班级ID',
  PRIMARY KEY (`id`),
  KEY `fk_class` (`class_id`),
  CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci COMMENT='学生表'
1 row in set (0.00 sec)

删除父表t_class的数据，报错了 外键一旦设置成功，将会保持子表和父表的数据一致性和完整性

sql

mysql> delete from t_class where id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`base`.`t_student`, CONSTRAINT `fk_class` FOREIGN KEY (`class_id`) REFERENCES `t_class` (`id`))

有外键引用的可以设置行为来支持删除，比如

dtd

mysql> alter table t_student add constraint fk_t_class foreign key (class_id) references t_class(id) on update set null on delete set null;
Query OK, 3 rows affected (0.03 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from t_student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 李子明    |        1 |
|  2 | 李大明    |        1 |
|  3 | 王二蛋    |        2 |
+----+-----------+----------+
3 rows in set (0.00 sec)

mysql> delete from t_class where id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
+----+-----------+----------+
| id | name      | class_id |
+----+-----------+----------+
|  1 | 李子明    |     NULL |
|  2 | 李大明    |     NULL |
|  3 | 王二蛋    |        2 |
+----+-----------+----------+
3 rows in set (0.00 sec)

2.删除外键

sql

mysql> alter table t_student drop foreign key fk_class;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

七、常用函数

A字符串类

concat

sql

mysql> select concat("hello","world");
+-------------------------+
| concat("hello","world") |
+-------------------------+
| helloworld              |
+-------------------------+
1 row in set (0.00 sec)

lower/upper

sql

mysql> select lower("Hello"),upper('World');
+----------------+----------------+
| lower("Hello") | upper('World') |
+----------------+----------------+
| hello          | WORLD          |
+----------------+----------------+
1 row in set (0.00 sec)

trim/replace 去掉开始和结尾的空格，中间的不会去

sql

mysql> select length(trim(" ab c   ")),replace('he、ca、da', '、', '/');
+--------------------------+-------------------------------------+
| length(trim(" ab c   ")) | replace('he、ca、da', '、', '/')    |
+--------------------------+-------------------------------------+
|                        4 | he/ca/da                            |
+--------------------------+-------------------------------------+
1 row in set (0.00 sec)

lpad/rpad

统一显示五位数，不足五位数的前面补0

sql

mysql> select * from t_stu;
+----+------+------+
| id | no   | name |
+----+------+------+
|  1 |    1 | tom  |
|  2 |    2 | tom2 |
|  3 |    3 | tom3 |
+----+------+------+
3 rows in set (0.00 sec)
mysql> update t_stu set no = lpad(no,5,'0');
Query OK, 0 rows affected (0.00 sec)
Rows matched: 3  Changed: 0  Warnings: 0

mysql> select * from t_stu;
+----+-------+------+
| id | no    | name |
+----+-------+------+
|  1 | 00001 | tom  |
|  2 | 00002 | tom2 |
|  3 | 00003 | tom3 |
+----+-------+------+
3 rows in set (0.00 sec)

B时间日期

时间日期函数

sql

mysql> select now(),year('2023-07-18 21:53:10'),month(now()),curdate(),curtime();
+---------------------+-----------------------------+--------------+------------+-----------+
| now()               | year('2023-07-18 21:53:10') | month(now()) | curdate()  | curtime() |
+---------------------+-----------------------------+--------------+------------+-----------+
| 2023-12-14 17:33:02 |                        2023 |           12 | 2023-12-14 | 17:33:02  |
+---------------------+-----------------------------+--------------+------------+-----------+
1 row in set (0.00 sec)

时间日期计算

sql

mysql> select date_add(now(), interval 2 day) 后天, date_add(now(), interval -1 MONTH) 一月前, datediff('1995-11-16',now()) 活了多少天;
+---------------------+---------------------+-----------------+
| 后天                | 一月前              | 活了多少天      |
+---------------------+---------------------+-----------------+
| 2023-12-16 17:37:28 | 2023-11-14 17:37:28 |          -10255 |
+---------------------+---------------------+-----------------+
1 row in set (0.00 sec)

时间和字符串互转

sql

mysql> select date_format(now(), '%Y-%m-%d') 时间转字符串, str_to_date('2023-12-12', '%Y-%m-%d') 字符串转时间;
+--------------------+--------------------+
| 时间转字符串       | 字符串转时间       |
+--------------------+--------------------+
| 2023-12-14         | 2023-12-12         |
+--------------------+--------------------+
1 row in set (0.00 sec)

C条件判断

if/case when/ifnull

sql

mysql> select if(1>2,"ok","no"), ifnull(null,'--');
+-------------------+-------------------+
| if(1>2,"ok","no") | ifnull(null,'--') |
+-------------------+-------------------+
| no                | --                |
+-------------------+-------------------+
1 row in set (0.00 sec)

D数字类

ceil/floor/round rand 四舍五入和随机数

ceil(x)：向上取整
floor(x)：向下取整
round(x,y)：求参数x的四舍五入的值，保留y位小数
rand()：返回0-1内的随机数

rpad(round(rand()*9998+1,0),4,'0') 取1-9999的随机数，四射五入保留0位小数，不足4位，后面补0

sql

mysql> select ceil(1.1), floor(1.9), round(3.1415926,2), rand(), rpad(round(rand()*9998+1,0),4,'0');
+-----------+------------+--------------------+----------------------+------------------------------------+
| ceil(1.1) | floor(1.9) | round(3.1415926,2) | rand()               | rpad(round(rand()*9998+1,0),4,'0') |
+-----------+------------+--------------------+----------------------+------------------------------------+
|         2 |          1 |               3.14 | 0.045669486788725004 | 6428                               |
+-----------+------------+--------------------+----------------------+------------------------------------+
1 row in set (0.00 sec)

E其他常用的

group_concat

sql

mysql> select group_concat(distinct name separator '/') from t_stu group by '1';
+----------------------------------+
| group_concat(name separator '/') |
+----------------------------------+
| tom/tom2/tom3                    |
+----------------------------------+
1 row in set (0.00 sec)

窗口函数

sql

ROW_NUMBER() OVER (PARTITION BY xxx, xxx ORDER BY xxx DESC) rn

正则

正则截取：regexp_substr(url, '[0-9]+')
正则匹配： regexp '\\s'

八、存储过程

存储过程是事先经过编译并存储在数据库中的一段sql语句的集合。封装，重用的思想，来提升效率

1.存储过程基本语句格式

sql

DELIMITER //
CREATE PROCEDURE [数据库名].存储过程名([IN 参数 类型, OUT 变量名 类型, ...]) 
BEGIN
	-- 存储过程sql语句块 SELECT * FROM table_name
END //
DELIMITER ;

DELIMITER //：指定SQL语句的结束符，默认是“;”，存储过程里改完“//”，存储过程结束，改回原来的

比如定义一个存储过程，返回t_user表里state=?的数据条数

sql

mysql> DELIMITER //
mysql> CREATE PROCEDURE sp_user_total(IN state INT)
    -> BEGIN
    ->     -- SQL SECURITY DEFINER
    ->     -- COMMENT '统计某个平台用户数量'
    ->     SELECT COUNT(*) FROM t_user t WHERE t.state = state;
    ->
    -> END //
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> call sp_user_total(0);
+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

2.删除存储过程

sql

mysql> DROP PROCEDURE IF EXISTS sp_user_total;
Query OK, 0 rows affected (0.00 sec)

3.删除存储过程

sql

mysql> show create procedure sp_user_total \G
*************************** 1. row ***************************
           Procedure: sp_user_total
            sql_mode: STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`%` PROCEDURE `sp_user_total`(IN state INT)
BEGIN


    SELECT COUNT(*) FROM t_user t WHERE t.state = state;

END
character_set_client: utf8mb4
collation_connection: utf8mb4_0900_ai_ci
  Database Collation: utf8mb4_general_ci
1 row in set (0.00 sec)

mysql>

4. 其他语法

if语句

sql

DELIMITER //
CREATE PROCEDURE ufs.sp_user_demo(IN score INT, OUT result VARCHAR(10)) 
BEGIN
	-- 存储过程语句块
	if score >=85 then set result := '优秀';
    elseif score >= 60 then set result := '及格';
    else set result := '不及格';
    end if;
END //
DELIMITER ;

调用

sql

mysql> set @score = 98;
Query OK, 0 rows affected (0.00 sec)

mysql> set @result = '';
Query OK, 0 rows affected (0.00 sec)

mysql> call sp_user_demo(@score,@result);
Query OK, 0 rows affected (0.00 sec)

mysql> select @result;
+---------+
| @result |
+---------+
| 优秀    |
+---------+
1 row in set (0.00 sec)

case when

sql

delimiter //
create procedure ufs.sp01(in month int) 
begin
    declare result varchar(10);
    case
        when month >= 1 and month <= 3  then
            set result := '第一季度';
        when month >= 4 and month <= 6  then
            set result := '第二季度';
        when month >= 7 and month <= 9  then
            set result := '第三季度';
        when month >= 10 and month <= 12  then
            set result := '第四季度';
        else
            set result := '非法参数';
    end case;
    select concat('您输入的月份是：', month, ', 所属的季度是：', result);
end //
delimiter ;

while

sql

delimiter //
create procedure ufs.sp02(in n int) 
begin
    declare total int default 0;
    while n>0 do
        set total := total + n;
    　　 set n := n - 1;
    end while;
    select total;
end //
delimiter ;

九、统计和备份

1.统计相关

查看当前数据库的insert，update，dalete,select的访问频次，七个_ 表示起个通配符

sql

mysql> show global status like 'Com_______';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_binlog    | 0     |
| Com_commit    | 0     |
| Com_delete    | 3     |
| Com_import    | 0     |
| Com_insert    | 3     |
| Com_repair    | 0     |
| Com_revoke    | 7     |
| Com_select    | 280   |
| Com_signal    | 0     |
| Com_update    | 1     |
| Com_xa_end    | 0     |
+---------------+-------+
11 rows in set (0.01 sec)

查看其他指标，比如Slow_queries、Connections、Uptime

sql

show global status like 'Slow_queries';  # 查询慢查询次数
show global status like 'Connections';   # 试图连接MySQL服务器的次数
show global status like 'Uptime';   # 服务器工作时间

查看MySQL当前进行的线程

sql

mysql> show processlist;
+----+-----------------+----------------+------+---------+--------+------------------------+------------------+
| Id | User            | Host           | db   | Command | Time   | State                  | Info             |
+----+-----------------+----------------+------+---------+--------+------------------------+------------------+
|  5 | event_scheduler | localhost      | NULL | Daemon  | 182143 | Waiting on empty queue | NULL             |
| 29 | root            | localhost:3949 | base | Sleep   |    185 |                        | NULL             |
| 30 | root            | localhost:3952 | base | Sleep   |   1169 |                        | NULL             |
| 31 | root            | localhost:3953 | base | Sleep   |   6061 |                        | NULL             |
| 32 | root            | localhost:5192 | base | Query   |      0 | init                   | show processlist |
+----+-----------------+----------------+------+---------+--------+------------------------+------------------+
5 rows in set (0.00 sec)

数据库大小

sql

mysql> select table_schema db_name,
    ->     concat(round(sum(data_length/1024/1024),2),'MB') `data`,
    ->     concat(round(sum(index_length/1024/1024),2),'MB') `index`,
    ->     concat(round(sum((data_length+index_length)/1024/1024),2),'MB')  total
    -> from information_schema.tables
    -> where table_schema in( 'ufs','testdb')
    -> group by db_name;
+---------+--------+--------+--------+
| db_name | data   | index  | total  |
+---------+--------+--------+--------+
| ufs     | 0.19MB | 0.00MB | 0.19MB |
+---------+--------+--------+--------+
1 row in set (0.00 sec)

查询表信息

sql

mysql> select table_schema db_name, table_name,table_rows,create_time,table_comment,
    ->     concat(round(sum(data_length/1024/1024),2),'MB') AS `dataSize`
    -> from information_schema.tables
    -> where table_schema = 'ufs'
    -> group by table_schema,table_name,table_rows,create_time,table_comment
    -> order by table_rows desc
    -> limit 100;
+---------+---------------------+------------+---------------------+-------------------------------+----------+
| db_name | TABLE_NAME          | TABLE_ROWS | CREATE_TIME         | TABLE_COMMENT                 | dataSize |
+---------+---------------------+------------+---------------------+-------------------------------+----------+
| ufs     | t_stu               |          3 | 2023-12-14 17:00:44 | 学生表                        | 0.02MB   |
| ufs     | t_file              |          0 | 2023-09-27 14:20:37 | 文件表                        | 0.02MB   |
| ufs     | t_sys_log           |          0 | 2023-09-08 15:14:19 | 系统日志表                    | 0.02MB   |
+---------+---------------------+------------+---------------------+-------------------------------+----------+
12 rows in set (0.00 sec)

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time 单位：秒，默认10秒)的所有SQL语句的日志，默认是没开启的；开启方式需要在/etc/my.cnf里配置开启

txt

# 开启慢查询
slow_query_log=1

# 设置慢查询的时间 10s
long_query_time=10

查询是否开启 OFF没有开

sql

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   |
+----------------+-------+
1 row in set, 1 warning (0.00 sec)

2.数据库备份

shell

# 单个数据库
mysqldump -h hostname -u username -p dbname > backup.sql
# 多个数据库
mysqldump -h hostname -u username -p --databases dbname1 dbname2 > backup.sql

MySQL基础操作 ​

一、连接MySQL ​

二、数据库操作 ​

1.创建数据库 ​

2.删库 ​

3.切换数据库 ​

4.查看数据库 ​

三、表的操作 ​

1.查看当前库所有的表 ​

2.创建一张表 ​

3.编辑表 ​

四、用户和权限 ​

4.1用户操作 ​

1.查询用户 ​

2.创建用户 ​

3.修改用户密码 ​

4.删除用户 ​

4.2 权限操作 ​

1.查询用户权限 ​

2.给用户授权 ​

3.撤销权限 ​

五、索引操作 ​

1.创建索引 ​

2.删除索引 ​

3.查看索引 ​

六、外键操作 FK ​

1.添加外键 ​

2.删除外键 ​

七、常用函数 ​

A字符串类 ​

B时间日期 ​

C条件判断 ​

D数字类 ​

E其他常用的 ​

八、存储过程 ​

1.存储过程基本语句格式 ​

2.删除存储过程 ​

3.删除存储过程 ​

4. 其他语法 ​

九、统计和备份 ​

1.统计相关 ​

2.数据库备份 ​

MySQL基础操作

一、连接MySQL

二、数据库操作

1.创建数据库

2.删库

3.切换数据库

4.查看数据库

三、表的操作

1.查看当前库所有的表

2.创建一张表

3.编辑表

四、用户和权限

4.1用户操作

1.查询用户

2.创建用户

3.修改用户密码

4.删除用户

4.2 权限操作

1.查询用户权限

2.给用户授权

3.撤销权限

五、索引操作

1.创建索引

2.删除索引

3.查看索引

六、外键操作 FK

1.添加外键

2.删除外键

七、常用函数

A字符串类

B时间日期

C条件判断

D数字类

E其他常用的

八、存储过程

1.存储过程基本语句格式

2.删除存储过程

3.删除存储过程

4. 其他语法

九、统计和备份

1.统计相关

2.数据库备份