MySQL Manual
CREATE TABLE t_tick_mt4 SELECT * FROM t_tick_hbhb;
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 管理
- 5.1 打开3306
- 打开Ping权限
- 不能在安全模式下修改字段
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,修改有数据库的结构(包括: 修改索引,修改字段类型,删除索引)
- 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 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,即数据操作语句
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
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
[^1] MySQL mysqldump数据导出详解
[^2] MySQL教學
[^3] MySQL创建用户与授权
更多推荐
所有评论(0)