MySQL Manual

MyFreeIT

已于 2025-05-30 09:51:30 修改

阅读量3.4k

点赞数 1

分类专栏：数据库文章标签： sql mysql

于 2021-05-31 10:30:35 首次发布

创作源自于MyFreeIT

本文链接：https://blog.csdn.net/MyFreeIT/article/details/117371877

版权

数据库专栏收录该内容

57 篇文章

订阅专栏

MySQL Command

1. DDL，Data Definition Language，即数据定义语言。
2. DML，Data Manipulation Language，即数据操作语句
3. DCL，Data Control Language，权限控制
4. TCL，Transaction Control Language，即事务控制语言
5. OS & MySQL 管理
事务管理
- MySQL autocommit 是否自动提交

SQL命令分为四种，分別是DDL，DML，DCL and TCL。

SQL的执行顺序如下：
1.FROM
2.ON
3.JOIN
4.WHERE
5.GROUP By
6.CUBE|ROllUP
7.HAVING
8.SELECT
9.DISTINCT
10.ORDER BY
11.LIMIT
最先执行的是FROM操作，最后执行的是LIMIT操作。每个操作都会产生一个虚拟表，该虚拟表作为一个处理的输入

1. DDL，Data Definition Language，即数据定义语言。

1.1 create db & table

新建表，索引，视图，存储过程等

字符集

utf8_general_ci 是 case insensitive collation 不區分大小寫，utf8_bin是區分大小寫。

在實際查詢時也可以使用binary對字段單獨設置。

1.1.1 SHOW DATABASES 查看數據庫

mysql> SHOW DATABASES;

if you want to review the created database, you can use the SHOW CREATE DATABASE command，MySQL returns the database name and the character set and collation of the database.
显示创建该数据库的脚本。

SHOW CREATE DATABASE testdb;
'testdb', 'CREATE DATABASE `testdb` /*!40100 
DEFAULT CHARACTER SET utf8mb4 
COLLATE utf8mb4_0900_ai_ci */ /*!80016 
DEFAULT ENCRYPTION=\'N\' */'

在这里插入图片描述

1.1.2 create db

1.1.2.1by command

1.CREATE DATABASE testdb;

2.CREATE SCHEMA `mytest` DEFAULT CHARACTER SET utf8 ;

3.CREATE DATABASE slave_test CHARACTER SET utf8 COLLATE utf8_general_ci;

1.1.2.2 by guide 向导

charset : utf8
collation:
utf8_general_ci 一个遗留的校对规则，不支持扩展。它仅能够在字符之间进行逐个比较,比较速度很快。
utf8-unicode_ci 支持扩展，即当把一个字母看作与其它字母组合相等时。例如，在德语和一些其它语言中‘ß’等于‘ss’。
在这里插入图片描述

1.1.2.3 mysqladmin 远程创建和复制数据库

1.创建数据库

mysqladmin -h 127.0.0.1 -uroot -proot create mybook2

2.复制数据库
从mybook 复制数据到mybook2

mysqldump -h 127.0.0.1 -uroot -proot mybook|
mysql -h 127.0.0.1 -uroot -proot mybook2

通过压缩从低速网络从远程计算机复制数据库，参考
mysqldump -h ‘other_hostname’ --compress db_name | mysql db_name

1.1.3 USE testdb

mysql> USE testdb;
Database changed

1.1.4 exit db command

mysql> exit
Bye

1.1.5 create table

CREATE TABLE bookorder(
id INT(10) NOT NULL AUTO_INCREMENT,
order_name VARCHAR(32) NOT NULL,
order_des VARCHAR(32),
create_time DATETIME NOT NULL 
     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY(id)
)
COMMENT='Order list'
ENGINE=InnoDB;

1.1.6 create table 复制table

CREATE TABLE  t_ti_mt  SELECT * FROM t_ti_xb;

1.1.7 DateTime 字段

默認值

日期和時間使用CURRENT_TIMESTAMP，參考
日期使用 CURRENT_DATE
在这里插入图片描述

1.2 alter，修改有数据库的结构(包括: 修改索引，修改字段类型，删除索引)

Reference here.

Rename MySQL Column with the RENAME Statement

ALTER TABLE bookorder RENAME COLUMN create_date TO create_time;

2.Rename MySQL Column with the CHANGE Statement

ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type;

1.2.1 修改ID AUTO_INCREMENT,從1開始

SET @num := 0;
UPDATE biz_customer SET id = @num := (@num+1);
ALTER TABLE biz_customer AUTO_INCREMENT =1;

1.3 truncate，删除表中所有记录，并删除已分配的空间。

TRUNCATE [TABLE] tbl_name

TRUNCATE TABLE bookorder3

1.4 comment，添加注释

表以及字段上添加注释。

1.5 rename，重命名

包括索引，表名等。

1.6 全库查询字段和表名information_schema

information_schema存储全数据库的结构信息，包括字段、表名称、权限、类型等。

全数据查询字段名称

SELECT * from information_schema.columns where COLUMN_NAME='name'

指定数据库查询字段名称

SELECT * from information_schema.columns where TABLE_SCHEMA='abc' and COLUMN_NAME='name';

查询表名

SELECT * from information_schema.columns where table_name='myconfig'

1.7 备份&恢复

1.7.1 source 导入数据库

mysql> create database mydb;      # 创建数据库
mysql> use mydb;                  # 使用已创建的数据库 
mysql> set names utf8;           # 设置编码
mysql> source c:/app/mydb.sql  # 导入备份数据库

1.7.2 mysql < 导入数据库

# mysql -uroot -p123456 dbname < mydb.sql

1.7.3 mysqldump导出table

導出整張表

mysqldump -uroot -proot --databases db1 --tables a1 a2  >/tmp/db1.sql

按條件導出表
多个表的条件相同可以一次性导出多个表

mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where="id='a'"  >/tmp/a1.sql

1.7.4 mysqldump 导出数据库

导出包括系统数据库在内的所有数据库

mysqldump -uroot -proot --all-databases >/tmp/all.sql

导出db1、db2两个数据库的所有数据

mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql

mysqldump --single-transaction -uroot -p123456 dbname > file_name

3.只导出表结构不导出数据，–no-data

mysqldump -uroot -proot --no-data --databases db1 >/tmp/db1.sql

-C启用压缩传递，mysqldump --host=192.168.80.137 -uroot -proot -C --databases test |mysql --host=192.168.80.133 -uroot -proot test
在这里插入图片描述

4.跨服务器导出导入数据
将h1服务器中的db1数据库的所有数据导入到h2中的db2数据库中，db2的数据库必须存在否则会报错

mysqldump --host=h1 -uroot -proot --databases db1 |mysql --host=h2 -uroot -proot db2

1.7.5 select … into outfile 备份&恢复

select … into outfile 是一种逻辑备份方法，它的恢复速度非常快，比 insert 的插入速度要快12倍。

1.7.5.1 备份

select col1,col2… from table_name into outfile ‘/path/备份文件名称’

select * from user into outfile '/var/lib/mysql/tt.sql';

1.7.5.2 恢复

mysql> LOAD DATA INFILE '/var/lib/mysql-files/tt.sql' INTO TABLE test.user;

1.7.6 mysqldump命令说明

mysqldump 是支持单线程工作的工具，只能逐个表导出。

# 备份数据库
mysqldump [arguments] > file_name

# 其中 [arguments] 如下：
--host(-h)				# 服务器IP地址
--port(-p)				# 服务器端口号
--user(-u)				# MySQL 用户名
--pasword(-p)			# MySQL 密码
--databases			# 指定要备份的数据库
--all-databases		# 备份所有数据库
--databases d1 d2	# 备份指定数据库
--compact			# 压缩模式，产生更少的输出
--comments			# 添加注释信息
--single-transaction test 	# 对 test 数据库备份前,执行 start transaction 保证备份一致性
--lock-tables		# 备份前，锁定所有数据库表
--add-locks			# 备份数据库表时锁定数据库表
--force				# 当出现错误时仍然继续备份操作
--default-character-set		# 指定默认字符集

1.7.7 mydumper

mydumper 是一个高性能多线程的备份工具，备份速度远远高于 mysqldump。

1.7.7 mysqlimport

mysqlimport 是 MySQL 提供的一个命令行程序，通过参数 --user-thread 参数实现并发的导入不同的文件。

1.7.7 myloader

数据还原时使用 myloader 工具，俗称“小钢炮”。

1.8 VARIABLES

1.8.1 查询数据存放路径

show VARIABLES LIKE 'datadir'

在这里插入图片描述

1.8.2 查看日志位置

show variables like 'general_log_file';

1.8.3 查看错误日志文件路径

show variables like 'log_error';

1.8.4 慢查询日志文件路径

show variables like 'slow_query_log_file';

1.8.5 查看表事务是否自动提交

查询语句后，数据库并没有数据

show variables like '%autocommit%';

show global variables like 'autocommit';

±--------------±------+
| Variable_name | Value |
±--------------±------+
| autocommit | ON |
±--------------±------+

关闭自动提交

mysql> set autocommit=0;
set global autocommit=0;

2. DML，Data Manipulation Language，即数据操作语句

通過group_concat把group by 子表的內容鏈接起來

group_concat(ca.ca_country)

2.1 select，检索数据

2.1.1函数

2.1.1.1 unix_timestamp

unix_timestamp() 返回1970_01_01_00_00_00到现在的时间戳。


select unix_timestamp() time_1970,unix_timestamp("2021-8-25 18:00:00") time_special from dual;

在这里插入图片描述

2.1.1.2 from_unixtime


select from_unixtime("1629876983") time_1629876983,from_unixtime("1629885600") time_1629885600 from dual;

在这里插入图片描述

IFNULL 和COALESCE，NULL轉換

SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products

SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products

2.1.1.3 limit

SQLSERVER
SELECT top 3 * FROM inv_hdr
MySQL
SELECT  * FROM inv_hdr LIMIT 3
Oracle
SELECT  * FROM inv_hdr ROW_NUMBER <3

2.1.1 查询包括空格的数据

通过trim 前后对比，确定是否包括空格

SELECT be_email_address FROM `biz_email`  WHERE TRIM(be_email_address)<> be_email_address

2.2 INSERT table

Reference here.

insert into slave_test.user values(1,"brother"),(2,"sister"),(3,"consin");

INSERT INTO bookorder(order_name,order_des) VALUES('奔驰','this is a car');

通過select 批量插入

INSERT temp_biz_cust_gr(cust_id,gr_id) SELECT a.id,91 FROM biz_cust a,temp_cust_mas b WHERE a.cust_code = b.cust_no

插入不重复的数据

INSERT biz_email(be_company_id,be_email_address,be_carbon_action)
SELECT DISTINCT cust_no,email,carbon FROM  temp_biz_email2

2.3 UPDATE table

UPDATE table_name SET field1=new-value1, field2=new-value2
[WHERE Clause]

update user set host='%' where host='127.0.0.1';

UPDATE bookorder SET order_name ='TSL01',order_des='this is a new car' WHERE id=1;

2.4 DELETE table

DELETE FROM bookorder WHERE id=4;

2.5 explain，查看语句的执行计划

2.6 lock table，锁表

2.7 關聯查詢

幾個外鍵就關聯幾次，目的是一次關聯只能查出一個name

2.7.1 INNER JOIN

最小化查詢

	    select

        tab1.id,tab1.customer_code,tab1.customer_name,tab1.customer_address,
    
		tab1.invoice_template_state, 
		tab1.invoice_template,tab2.template_name AS invoice_template_name,
		tab1.invoice_email_template,tab3.template_name AS invoice_email_template_name,
		tab1.statement_template,tab4.template_name AS statement_template_name,
		tab1.statement_email_template,tab5.template_name AS statement_email_template_name,
    
		tab1.customer_des,tab1.create_by,tab1.create_time,tab1.update_by,tab1.update_time,
		tab2.template_name
		
		from biz_customer AS tab1 
		
		INNER JOIN biz_template AS tab2 ON tab1.invoice_template = tab2.id
		INNER JOIN biz_template AS tab3 ON tab1.invoice_email_template = tab3.id
		INNER JOIN biz_template AS tab4 ON tab1.statement_template = tab4.id
		INNER JOIN biz_template AS tab5 ON tab1.statement_email_template = tab5.id

在这里插入图片描述

2.7.2 LEFT JOIN

以左邊的表為標準，去匹配關聯表，保留左邊表格最大集合

	  select

    tab1.id,tab1.customer_code,tab1.customer_name,tab1.customer_address,
    
		tab1.invoice_template_state, 
		tab1.invoice_template,tab2.template_name AS invoice_template_name,
		tab1.invoice_email_template,tab3.template_name AS invoice_email_template_name,
		tab1.statement_template,tab4.template_name AS statement_template_name,
		tab1.statement_email_template,tab5.template_name AS statement_email_template_name,
    
		tab1.customer_des,tab1.create_by,tab1.create_time,tab1.update_by,tab1.update_time,
		tab2.template_name
		
		from biz_customer AS tab1 
		
		LEFT JOIN biz_template AS tab2 ON tab1.invoice_template = tab2.id
		LEFT JOIN biz_template AS tab3 ON tab1.invoice_email_template = tab3.id
		LEFT JOIN biz_template AS tab4 ON tab1.statement_template = tab4.id
		LEFT JOIN biz_template AS tab5 ON tab1.statement_email_template = tab5.id

3. DCL，Data Control Language，权限控制

3.1创建用户以及远程访问

CREATE USER 'username'@'host' IDENTIFIED BY 'password';

username：你将创建的用户名
host：指定该用户在哪个主机上可以登陆，如果是本地用户可用localhost，如果想让该用户可以从任意远程主机登陆，可以使用通配符%
password：该用户的登陆密码，密码可以为空，如果为空则该用户可以不需要密码登陆服务器

CREATE USER 'repl'@'192.168.1.100' IDENTIFIED BY 'root';

或者

UPDATE mysql.user set host='%' WHERE user = 'erp'

查询验证：

SELECT  host,user,repl_slave_priv,repl_client_priv from mysql.user ;

在这里插入图片描述
参考示例：

CREATE USER ‘dog’@‘localhost’ IDENTIFIED BY ‘123456’;
CREATE USER ‘pig’@‘192.168.1.101_’ IDENDIFIED BY ‘123456’;
CREATE USER ‘pig’@‘%’ IDENTIFIED BY ‘123456’;
CREATE USER ‘pig’@‘%’ IDENTIFIED BY ‘’;
CREATE USER ‘pig’@‘%’;

在这里插入图片描述

3.2 grant，访问数据库的权限

3.2.1 查看用户的权限

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

USAGE ON 没有任何权限
授权后应该如：
在这里插入图片描述

3.2.2 授权

GRANT privileges ON databasename.tablename TO 'username'@'host'

GRANT ALL PRIVILEGES ON *.* TO 'erp'@'%' WITH GRANT OPTION

privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL
databasename：数据库名
tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用*表示，如 .

    mysql> grant all privileges on *.* to root@localhost identified by 'password' with grant option;
    Query OK, 0 rows affected (0.01 sec)*
    mysql> flush privileges;
    Query OK, 0 rows affected (0.00 sec)

GRANT SELECT, INSERT ON test.user TO 'pig'@'%';
GRANT ALL ON *.* TO 'pig'@'%';

如果用该用户可以继续授权

GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

3.3 修改权限

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'root' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.01 sec)

3.4 revoke，撤销访问数据库的权限

REVOKE privilege ON databasename.tablename FROM 'username'@'host';
例如：
REVOKE SELECT ON *.* FROM 'root'@'%';

3.5 设置&更改密码

SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');

当前登录的用户：

SET PASSWORD = PASSWORD("newpassword");

远程登录的用户

SET PASSWORD FOR 'pig'@'%' = PASSWORD("newpassword");

3.6 删除用户

DROP USER 'username'@'host';

4. TCL，Transaction Control Language，即事务控制语言

4.1 commit，提交事务

只有start transaction;没有 COMMIT;
事务会在下一个SQL语句执行时，才会提交。
最后一个SQL 永远不会提交，参考4.4 创建初始化数据示例。
但是MYSQL的客户端可以select出这些数据，通过程序获取不到。

4.2 rollback ，回滚事务

4.3 set trasaction，设置事务隔离级别

4.4 存储过程及事务处理

对一个表无限制的初始化数据

drop PROCEDURE  IF EXISTS data_sample;
DELIMITER #
CREATE PROCEDURE data_sample(in data_count int,isClear boolean)
BEGIN
	declare  v_max int default 1000;
	declare v_counter int default 0;
    
    IF data_count>0 then
		Set v_max = data_count;
    End IF;
    
    IF isClear Then
      truncate sample;
    End IF;
    
	start transaction;
	while v_counter <v_max do
		INSERT INTO `mybook`.`sample` (`code`, `name`) VALUES (CONCAT('test',v_counter), CONCAT('sample',v_counter));
        set v_counter = v_counter+1;
	end while;
    COMMIT;
END #
DELIMITER ;

5. OS & MySQL 管理

5.1 打开3306

设置进站端口，远程可以Telnent
在这里插入图片描述

打开Ping权限

在这里插入图片描述

不能在安全模式下修改字段

MySQL error code: 1175 during UPDATE in MySQL Workbench
Follow the following steps before executing the UPDATE command: In MySQL Workbench

Go to Edit --> Preferences
Click "SQL Editor" tab and uncheck "Safe Updates" check box
Query --> Reconnect to Server // logout and then login
Now execute your SQL query

在这里插入图片描述

事务管理

MySQL autocommit 是否自动提交

如果用户在当前情况下（autocommit=1）下执行 start transaction 命令，对数据库进行操作，系统则默认用户对数据库的每一个操作为一个孤立的事务，也就是说用户每进行一次操作系都会即时提交或者即时回滚


show global variables like 'autocommit'

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.04 sec)

mysql>

将自动提交功能置为ON ，set autocommit=1;

[^1] MySQL mysqldump数据导出详解
[^2] MySQL教學
[^3] MySQL创建用户与授权