MySQL 8学習

13186 ワード
Mysql
1.ユーザー操作
  • ユーザ
    create user 'username'@'hostname' identified with mysql_native_password by 'password';//MySQL8  （  mysql8         ，          ，           ）  
 create user 'username'@'hostname' identified by 'password';//MySQL8
    • を作成する.
  • ユーザパスワード
    alter user 'username'@'hostname' identified with mysql_native_password by 'password';  //mysql8    
 set password for 'mysqltest'@'localhost' = password('mysqltest');  //mysql8
    • を変更する.
  • ユーザ
    drop user 'username'@'hostname'；
    • を削除する.
  • リモートログイン/付与権限
    alter user set user.host = '%' where name = 'root';//mysql8      
 grant all privileges on *.* to 'username'@'hostname';//mysql8          
 grant all privileges on *.* to 'username'@'hostname' identified by 'password' with grant option;//mysql8              
 grant select/insert/update/delete on *.* to 'username'@'hostname';//        
 grant select(id,salary) on database_name.table_name to 'username'@'hostname';//        
 grant grant option on *.* to 'username'@'hostname';//
    • を許可する.
  • クエリー権限
    show grants for 'username'@'hostname';//
  • 取り消し権限
    REVOKE privilege ON database_name.table_name FROM 'username'@'host';
  • ロール
    create role 'role1','role2';//      
 grant select on database_name.table_name to role1;//        
 grant role1 to user_name;//
    • を使用
  • リモートログイン
    mysql --host=hostname --port=3306 --user=username --password<=password>  
 mysql -h hostname -P 3306 -u username -p   
 -P：    
 -p：
  • ユーザ
    alter user 'username'@'hostname' account lock/unlock;
    • をロック/ロック解除する.
  • 期限切れパスワード
    create user 'username'@'hostname' identified with mysql_native_password by 'password'  password expire;  
 ALTER user 'username'@'hostname' identified with mysql_native_password by 'password'  password expire [interval 90 day];
    • を設定
    2.DML操作
  • クエリーデータベース
    show databases；
  • データベース
    use 'database_name'；
    • を使用
  • クエリー現在使用されているデータベース
    select database();
  • データベース
    create database database_name；
    • を作成
  • クエリー表
    show tables；
  • 記述テーブル情報
    desc tablename；
  • テーブル構造
    create table if not exists database_name.table_name(  
 	'id' int AUTO_INCREAMENT PRIMARY KEY,   
 	'NAME' varchar(20)  
 ) engine=InnoDB;
    • を作成する.
  • クローンテーブル構造
    create table tablename like source_tablename;
  • 削除テーブル
    drop table tablename;//      ，   ，     
 truncate table tablename;//    ，   ，
  • 削除列
    ALTER TABLE 【   】 DROP 【   】；
  • 列増加
    ALTER TABLE 【   】 ADD 【   】 INT NOT NULL  COMMENT '    '；
  • 列の名前変更/変更のタイプ情報
    ALTER TABLE 【   】 CHANGE 【   】【    】 BIGINT NOT NULL  COMMENT '    '；
  • 名前変更表
    ALTER TABLE 【   】 RENAME 【    】;
  • インデックス
        （PRIMARY KEY，INDEX，UNIQUE）：
mysql>ALTER TABLE tbl_name ADD INDEX index_name (column list);
mysql>ALTER TABLE tbl_name ADD UNIQUE index_name (column list);
mysql>ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column list);

    （PRIMARY KEY，INDEX，UNIQUE）：
mysql>ALTER TABLE tbl_name DROP INDEX index_name (column list);
mysql>ALTER TABLE tbl_name DROP UNIQUE index_name (column list);
mysql>ALTER TABLE tbl_name DROP PRIMARY KEY index_name (column list);

    查看某个数据表的索引：
    
mysql> SHOW INDEX FROM tbl_name;

    • 3. DDL操作

    1. 将数据加载到表中

      create table table_name as select ...//      （   as）  
 insert into table_name select ...	//
    2. 挿入データ(処理重複)
      //           
 ①insert into tablename(col1,col2...) values(value1,value2...);//    。       id  ，    ，   。  
 ②insert ignore into tablename(col1,col2...) values(value1,value2...);//       id  ，    ，         。  
 ③replace into tablename(col1,col2...) values(value1,value2...);//  。       id  ，    ，   。     ，   。   
 
 //              （         unique）  
 ④INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+values(c);//    unique  primary key  a，        a    ，   。    ，   ON DUPLICATE KEY UPDATE    。（values(c)       c  ）
    3. 更新データ
      update tablename set col1=value1,col2=value2... where ...;
    4. 削除データ
      delete from table_name where ...;
    5. select操作
      between ... and ...  
 in (...)  
 exists ...
 not (...)  
 is null
 like '_abc%' //——      ，%  0       
 rlike/regexp '^?abc?' //       
 limit start,length // start+1  ，   length
    6. 集約関数
      group by ... having ... // ...  ，having         
 distinct 'sex' //         
 count(*)  
 avg(salary)  
 sum(salary)  
 ...  
  ：  
 ①      ，group by distinct         ，      。 distinct ，distinct   col                ，       hash  ，key col  ，    hash       key      。   ，             。        。  
 ② group by      col  。      group    sort   ，       col    。         ，     nlogn，   1.，             。         ，          ，       。
    7. エクスポートファイル
      //    secure_file_priv    NULL  
 select * from table_name into outfile '/root/reslut'   
 fields terminated by ','   
 optionally enclosed by '"'   
 lines terminated by '
      '
    8. インポートファイル
      load data infile '/root/result.csv' [replace|ignore] into table table_name   
 fields terminated by ','   
 optionally enclosed by '"'   
 lines terminated by '
      '
    9. ストアド・プロシージャ(ユーザーにはexecute権限が必要)の役割:MySQLで一連の特定の文を実行し、戻り値はありません.
      mysql> delimiter $$　　#           ;      $$(      )  
 mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)  
 	-> BEGIN  
 	-> 　　DECLARE name varchar(20) DEFAULT 'Yancy Chang'; //      
 	-> 　　DELETE FROM MATCHES WHERE playerno = p_playerno;  
 	-> 　　SET name = 'My Name'； //     
 	-> 　　CASE //case    
 	-> 　　 　　WHEN 'Yancy Chang' THEN  
 	-> 　　 　　SELECT  name;  
 	-> 　　 　　WHEN 'My Name' THEN  
 	-> 　　 　　ELSE  
 	-> 　　 　　SELECT  name;  
 	-> 　　END CASE 
 	-> END  
 	-> $$ 
 Query OK, 0 rows affected (0.01 sec)  
 mysql> delimiter ;　　#             
 mysql> call delete_matches(24)　　#
      その他のコマンド:すべてのストレージコマンドを表示します:show procedure status;ストレージコマンド定義の表示:show create procedure'procedure_name’; 良い参考:http://www.runoob.com/w3cnote/mysql-stored-procedure.html
    10. 関数の役割:MySQLで一連の特定の文を実行し、戻り値があります.
      mysql> delimiter $$　　#           ;      $$(      )  
mysql> CREATE FUNCTION delete_matches(p_playerno INTEGER)  
	-> RETURN varchar(20)  
	-> DETERMINISTIC     
	-> BEGIN  
	-> 　　DECLARE name varchar(20) DEFAULT 'Yancy Chang'; //      
	-> 　　DELETE FROM MATCHES WHERE playerno = p_playerno;  
	-> 　　SET name = 'My Name'； //     
	-> 　　CASE //case    
	-> 　　 　　WHEN 'Yancy Chang' THEN  
	-> 　　 　　 　　RETURN (name);  
	-> 　　 　　WHEN 'My Name' THEN  
	-> 　　 　　 　　RETURN (name);  
	-> 　　 　　ELSE  
	-> 　　 　　 　　RETURN (name);  
	-> 　　END CASE 
	-> END
	-> $$  
Query OK, 0 rows affected (0.01 sec)  
mysql> delimiter;　　#               

      注：在函数创建中给出DETERMINISTIC 关键字非常重要。如果一个例程对于相同的输入参数总是产生相同的结果,则认为该函数为DETERMINISTIC，否则为NOT DETERMINISTIC。
      
如果在例程定义中既未给出DETERMINISTIC，也未给出NOTDETERMINISTIC，则默认是NOT DETERMINISTIC。 如果要声明一个函数是确定性的，则必须明确指定DETERMINISTIC。
      
如果将一个NON DETERMINISTIC例程声明为DETERMINISTIC,可能导致意想不到的结果，因为它会导致优化器选择不正确的执行计划。
      
将DETERMINISTIC例程声明为NON DETERMINISTIC可能会导致可用的优化未被使用，降低性能。

      类似于前面的存储过程。

      其他命令：
      展示所有函数命令：show function status;
      展示某一函数命令定义：show create function ‘function_name’;
      详细介绍：https://www.cnblogs.com/zhangminghui/p/4113160.html

    11. 触发器
      作用：用于在触发事件之前或之后激活某些内容。

      CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt

      trigger_name：触发器的名称
      
tirgger_time：触发时机，为BEFORE或者AFTER
      
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
      
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
      
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
      
所以可以说MySQL创建以下六种触发器：
      
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
      
AFTER INSERT,AFTER DELETE,AFTER UPDATE
      
FOR EACH ROW表示任何一条记录上的操作满足触发事件都会触发该触发器
      触发器类型 NEW和OLD的使用
      insert NEW()表示新增的数据
      update OLD()表示被删除的数据；NEW()表示新增的数据
      delete OLD()表示被删除的数据 
      NEW()：        
OLD()：        
NEW.columnname：        
OLD.columnname：
      その他のコマンド:すべてのトリガコマンドを表示します:show triggers;ストレージコマンド定義の表示:show create trigger'trigger_name'; トリガの詳細:[https://www.cnblogs.com/phpper/p/7587031.html](https://www.cnblogs.com/phpper/p/7587031.html)
    12. ビュー機能:ビューは、SELECT文が実行された後に返される結果セットです.
      CREATE [OR REPLACE] 
[ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
[DEFINER = `root`@`localhost`]
[SQL SECURITY DEFINER]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

      说明：
      
ALGORITHM=UNDEFINED：指定视图的处理算法；
      
DEFINER=root@localhost：指定视图创建者；
      
SQL SECURITY DEFINER：指定视图查询数据时的安全验证方式；
      

      WITH [CASCADED | LOCAL] CHECK OPTION：表示视图在更新时保证在视图的权限范围之内
      
cascade是默认值，表示更新视图的时候，要满足视图和表的相关条件
      
local表示更新视图的时候，要满足该视图定义的一个条件即可

      视图的更改：（不能一句SQL更改多个表的数据）
      ①视图与表是一对一关系情况：如果没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行增删改数据操作；
      ②视图与表是一对多关系情况：如果只修改一张表的数据，且没有其它约束（如视图中没有的字段，在基本表中是必填字段情况），是可以进行改数据操作。
      其他命令：
      展示所有视图命令：SHOW FULL TABLES WHERE TABLE_TYPE LIKE ‘VIEW’;
      展示某一视图命令定义：show create view ‘view_name’;
      视图详解：
      https://www.cnblogs.com/geaozhang/p/6792369.html |
      https://www.cnblogs.com/chenpi/p/5133648.html

    13. 事件
      和linux的cron一样，定时操作。不想写了。

    14. 数据库信息
      information_schema数据库中：
      tables 是描述各个数据库表的信息。
      columns 是描述各个数据库表的各个列的信息。
      files 是描述.ibd文件的信息。
      processlist 是描述当前正在查询语句的信息。（还可以用show processlist显示）

    4. 进阶使用

    1. JSON解析 
      select id,detail->'$.address' from mytable;#         
 select id,detail->>'$.address' from mytable;#          
 JSON_SET(json_doc, path, val[, path, val] ...) #             
 #  ：SELECT JSON_SET(detail, '$.address', 'myaddress') FROM student WHERE id= 12;
 JSON_INSERT(json_doc, path, val[, path, val] ...) #      
 JSON_REPLACE(json_doc, path, val[, path, val] ...) #      
 JSON_REMOVE(json_doc, path[, path] ...) #    
 JSON_KEYS(json_doc[, path]) #   
 JSON_LENGTH(json_doc[, path]) #
    2. 生成列
      
   ：
    ：column_name data_type [GENERATED ALWAYS] AS (expression) [VIRTUAL | STORED] [UNIQUE [KEY]];
    ：alter table table_name add column_name column_type as (expression) [VIRTUAL | STORED];
 VIRTUAL：      。
 STORED：        。
 
   ：
 CREATE TABLE contacts (
     id INT AUTO_INCREMENT PRIMARY KEY,
     first_name VARCHAR(50) NOT NULL,
     last_name VARCHAR(50) NOT NULL,
     fullname varchar(101) [GENERATED ALWAYS] AS (CONCAT(first_name,' ',last_name))[VIRTUAL | STORED],
     email VARCHAR(100) NOT NULL
 );
    3. ウィンドウ関数は見たくないので、後で話します.
    4. バイナリ・ファイル・リカバリを使用する
      SHOW VARIABLES LIKE '%log_bin%';#  logbin         
 ./mysqlbinlog --no-defaults \MysqlBinLog\binlog.000001	 #          ，       
 #    
 ./mysqlbinlog --no-defaults \MysqlBinLog\binlog.000001 \Mysql57BinLog\binlog.000002 | mysql -u root -p
 ./mysqlbinlog --no-defaults --start-position="4" --stop-position="1285" \MysqlBinLog\binlog.000006  | mysql -u root -p
      その他のアクション:
      SET SQL_LOG_BIN = 0; #          ，0  ，1  
 show binary logs; #          
 purge binary logs before '';#
      参照:https://www.cnblogs.com/suixinpeng/p/mysqlbinlog.html
    5. データベースのバックアップ/リカバリ
      ./mysqldump -u root -p --all-databases --routines --events > /root/dump.sql  #  
 mysql -u root -p < /root/dump.sql   #
    sql時間変換レコード
    SQLは身分証明書によって、ユーザーの省を統計する