【MySQL】マルチテーブルクエリー&トランザクション&権限管理

13261 ワード
複数テーブルクエリー:
  • クエリー構文:
    select
	    
from
	    
where....
    • 例:
  • 部門表
    • を作成する
    CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20)
);
INSERT INTO dept (NAME) VALUES ('   '),('   '),('   ');
  • 社員表
    • を作成する
    CREATE TABLE emp (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	gender CHAR(1), --   
	salary DOUBLE, --   
	join_date DATE, --     
	dept_id INT,
	FOREIGN KEY (dept_id) REFERENCES dept(id) --   ，     (      )
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('   ',' ',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('   ',' ',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('  ',' ',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('   ',' ',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('   ',' ',4500,'2011-03-14',1);

    デカルト積:
  • には2つの集合A,Bがある.この2つの集合のすべての構成状況を取ります.
  • マルチテーブルクエリを完了するには、不要なデータを削除する必要があります
    • マルチテーブルクエリの分類:
    内部接続クエリー:
  • どのテーブルからデータを問合せますか
  • 条件は何ですか
  • クエリーのフィールド
    • 暗黙的な内部接続:where条件を使用して不要なデータを除去する
    --                 
SELECT * FROM emp,dept WHERE emp.`dept_id` = dept.`id`;

--         ，  。      
SELECT emp.name,emp.gender,dept.name FROM emp,dept WHERE emp.`dept_id` = dept.`id`;
--      
SELECT 
	t1.name, --       
	t1.gender,--       
	t2.name --       
FROM
	emp t1,
	dept t2
WHERE 
	t1.`dept_id` = t2.`id`;

    明示的な内部ジョイン:
  • 構文:selectフィールドリストfromテーブル名1[inner]joinテーブル名2 on条件
  • 例えば
    SELECT * FROM emp INNER JOIN dept ON emp.`dept_id` = dept.`id`;	

SELECT * FROM emp JOIN dept ON emp.`dept_id` = dept.`id`;
    • 外部リンククエリー:
    左外部ジョイン:
  • 構文:selectフィールドリストfrom表1 left[outer]join表2 on条件;
  • は、左のテーブルのすべてのデータとその交差部分をクエリーします.
  • 例:
    --         ，       ，       ，    ，        
SELECT 	t1.*,t2.`name` FROM emp t1 LEFT JOIN dept t2 ON t1.`dept_id` = t2.`id`;
    • 右外部ジョイン:
  • 構文:selectフィールドリストfromテーブル1 right[outer]joinテーブル2 on条件;
  • は、右のテーブルのすべてのデータとその交差部分をクエリーします.
  • 例:
    SELECT 	* FROM dept t2 RIGHT JOIN emp t1 ON t1.`dept_id` = t2.`id`;
    • サブクエリ:
  • コンセプト:ネストされたクエリをサブクエリと呼びます.
    --            
-- 1            9000
SELECT MAX(salary) FROM emp;

-- 2       ，      9000 
SELECT * FROM emp WHERE emp.`salary` = 9000;

--   sql       。   
SELECT * FROM emp WHERE emp.`salary` = (SELECT MAX(salary) FROM emp);

    • サブクエリの違い
  • サブクエリの結果は、1行1列です.
  • サブクエリは、条件として演算子を使用して判断することができる.演算子:>>=<<==
    • --               
SELECT * FROM emp WHERE emp.salary < (SELECT AVG(salary) FROM emp);
  • サブクエリの結果は、複数行の単一列です.
  • サブクエリは、条件として、演算子inを使用して
    • を判断することができる.
    --   '   ' '   '       
SELECT id FROM dept WHERE NAME = '   ' OR NAME = '   ';
SELECT * FROM emp WHERE dept_id = 3 OR dept_id = 2;

--      
SELECT * FROM emp WHERE dept_id IN (SELECT id FROM dept WHERE NAME = '   ' OR NAME = '   ');
  • サブクエリの結果は、複数行の複数列です.
  • サブクエリは、仮想テーブルとしてクエリ
    • に参加することができる.
    --          2011-11-11             
--    
SELECT * FROM dept t1 ,(SELECT * FROM emp WHERE emp.`join_date` > '2011-11-11') t2 WHERE t1.id = t2.dept_id;

--      
SELECT * FROM emp t1,dept t2 WHERE t1.`dept_id` = t2.`id` AND t1.`join_date` >  '2011-11-11'

    マルチテーブルクエリーの練習
    表を作る
  • 部門表
    • --    
CREATE TABLE dept (
	id INT PRIMARY KEY PRIMARY KEY, --   id
	dname VARCHAR(50), --     
	loc VARCHAR(50) --      
);
--   4   
INSERT INTO dept(id,dname,loc) VALUES 
	(10,'   ','  '),
	(20,'   ','  '),
	(30,'   ','  '),
	(40,'   ','  ');
  • 職務表
    • --    ，    ，    
CREATE TABLE job (
	id INT PRIMARY KEY,
	jname VARCHAR(20),
	description VARCHAR(50)
);
--   4   
INSERT INTO job (id, jname, description) VALUES
	(1, '   ', '      ，  '),
	(2, '  ', '      '),
	(3, '   ', '       '),
	(4, '  ', '      ');
  • 従業員表
    • --    
CREATE TABLE emp (
  id INT PRIMARY KEY, --   id
  ename VARCHAR(50), --     
  job_id INT, --   id
  mgr INT , --     
  joindate DATE, --     
  salary DECIMAL(7,2), --   
  bonus DECIMAL(7,2), --   
  dept_id INT, --       
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);
--     
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
    (1001,'   ',4,1004,'2000-12-17','8000.00',NULL,20),
    (1002,'   ',3,1006,'2001-02-20','16000.00','3000.00',30),
    (1003,'  ',3,1006,'2001-02-22','12500.00','5000.00',30),
    (1004,'  ',2,1009,'2001-04-02','29750.00',NULL,20),
    (1005,'  ',4,1006,'2001-09-28','12500.00','14000.00',30),
    (1006,'  ',2,1009,'2001-05-01','28500.00',NULL,30),
    (1007,'  ',2,1009,'2001-09-01','24500.00',NULL,10),
    (1008,'   ',4,1004,'2007-04-19','30000.00',NULL,20),
    (1009,'   ',1,NULL,'2001-11-17','50000.00',NULL,10),
    (1010,'  ',3,1006,'2001-09-08','15000.00','0.00',30),
    (1011,'  ',4,1004,'2007-05-23','11000.00',NULL,20),
    (1012,'  ',4,1006,'2001-12-03','9500.00',NULL,30),
    (1013,'   ',4,1004,'2001-12-03','30000.00',NULL,20),
    (1014,'  ',4,1007,'2002-01-23','13000.00',NULL,10);
  • 給与登録表
    • --      
CREATE TABLE salarygrade (
	grade INT PRIMARY KEY,   --   
	losalary INT,  --     
	hisalary INT --     
);
--   5     
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
	(1,7000,12000),
	(2,12010,14000),
	(3,14010,20000),
	(4,20010,30000),
	(5,30010,99990);

    需要とsql
  • は、すべての従業員情報を照会します.従業員番号、従業員名、給与、役職名、役職説明
    • を問い合せます.
    /*
    ：
	1.    ，    ，  ，    emp       ，         job 
	2.     emp.job_id = job.id
*/
SELECT 
	t1.`id`, --     
	t1.`ename`, --     
	t1.`salary`,--   
	t2.`jname`, --     
	t2.`description` --     
FROM 
	emp t1, job t2
WHERE 
	t1.`job_id` = t2.`id`;
  • 従業員番号、従業員名、給与、役職名、役職説明、部門名、部門位置
    • /*
	  ：
		1.     ，    ，   emp      ，     job      ，     dept
		2.   ： emp.job_id = job.id and emp.dept_id = dept.id
*/

SELECT 
	t1.`id`, --     
	t1.`ename`, --     
	t1.`salary`,--   
	t2.`jname`, --     
	t2.`description`, --     
	t3.`dname`, --     
	t3.`loc` --     
FROM 
	emp t1, job t2,dept t3
WHERE 
	t1.`job_id` = t2.`id` AND t1.`dept_id` = t3.`id`;
  • 従業員の名前、給与、給与等級
    • /*
	  ：
		1.    ，   emp       salarygrade
		2.   emp.salary >= salarygrade.losalary and emp.salary <= salarygrade.hisalary
			emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
*/
SELECT 
	t1.ename ,
	t1.`salary`,
	t2.*
FROM emp t1, salarygrade t2
WHERE t1.`salary` BETWEEN t2.`losalary` AND t2.`hisalary`;
  • 従業員の名前、給与、役職名、職務説明、部門名、部門位置、給与等級
    • を照会する
    	/*
	  ：
		1.     ，   emp ，     ，     job     ，    ，dept       salarygrade
		2.   ： emp.job_id = job.id and emp.dept_id = dept.id and emp.salary BETWEEN salarygrade.losalary and salarygrade.hisalary
	*/
SELECT 
	t1.`ename`,
	t1.`salary`,
	t2.`jname`,
	t2.`description`,
	t3.`dname`,
	t3.`loc`,
	t4.`grade`
FROM 
	emp t1,job t2,dept t3,salarygrade t4
WHERE 
	t1.`job_id` = t2.`id` 
	AND t1.`dept_id` = t3.`id`
	AND t1.`salary` BETWEEN t4.`losalary` AND t4.`hisalary`;
  • 部門番号、部門名、部門位置、部門人数
    • を検索する.
    /*
	  ：
		1.    、    、     dept  。      emp 
		2.      。  emp.dept_id    ，  count(id)
		3.       2       dept       
		
*/
SELECT 
	t1.`id`,t1.`dname`,t1.`loc` , t2.total
FROM 
	dept t1,
	(SELECT
		dept_id,COUNT(id) total
	FROM 
		emp
	GROUP BY dept_id) t2
WHERE t1.`id` = t2.dept_id;
  • すべての従業員の名前と直接の上司の名前を照会し、リーダーのいない従業員も
    • を照会する必要があります.
    /*
	  ：
		1.   emp，         emp
			* emp  id   mgr     
		2.   emp.id = emp.mgr
		3.         ，      
			*         
*/
/*
select
	t1.ename,
	t1.mgr,
	t2.`id`,
	t2.ename
from emp t1, emp t2
where t1.mgr = t2.`id`;
*/

SELECT 
	t1.ename,
	t1.mgr,
	t2.`id`,
	t2.`ename`
FROM emp t1
LEFT JOIN emp t2
ON t1.`mgr` = t2.`id`;

    取引
    事務の基本紹介
    コンセプト:
  • 複数のステップを含むビジネス・オペレーションがトランザクションによって管理される場合、これらのオペレーションは同時に成功するか、同時に失敗するかのいずれかです.

    • 操作:
  • オープントランザクション:start transaction;
  • ロールバック:rollback;
  • 提出:commit;

    • 例:
    CREATE TABLE account (
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(10),
	balance DOUBLE
);
--     
INSERT INTO account (NAME, balance) VALUES ('zhangsan', 1000), ('lisi', 1000);
SELECT * FROM account;
UPDATE account SET balance = 1000;

--         500  
-- 0.     
START TRANSACTION;

-- 1.      -500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan';

-- 2.      +500
--    ...
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi';

--         ，    
COMMIT;

--       ，    
ROLLBACK;

    MySQLデータベースでのトランザクションのデフォルト自動コミット
  • トランザクションのコミットの2つの方法:
  • 自動コミット:
  • mysqlは自動提出の
    • です
  • DML(追加削除)文は、トランザクションを自動的にコミットします.

  • 手動コミット:
  • Oracleデータベースのデフォルトは、手動でトランザクションをコミットする
    • です.
  • トランザクションを開始してから
    • にコミットする必要があります.

  • トランザクションのデフォルトのコミット方法を変更します.
  • トランザクションのデフォルトコミットの表示:SELECT @@autocommit;–1は自動コミット0を表し、手動コミット
    • を表します.
  • デフォルトコミットの変更:set @@autocommit = 0;

    • トランザクションの4つの特徴:
  • 原子性:分割不可能な最小操作単位であり、同時に成功するか、同時に失敗するか.
  • 永続性:トランザクションがコミットまたはロールバックされると、データベースはデータを永続的に保存します.
  • 独立性:複数のトランザクション間.互いに独立する.
  • 一貫性:トランザクションの前後で、データの合計は
    • に変わりません.
    トランザクションの独立性レベル(了解)
  • コンセプト:複数のトランザクション間で隔離され、互いに独立しています.しかし、複数のトランザクションが同じバッチのデータを操作すると、いくつかの問題が発生し、異なる独立性レベルを設定すると、これらの問題を解決できます.
  • に問題があります.
  • ダーティリード:あるトランザクション、別のトランザクションにコミットされていないデータ
    • を読み取る
  • 繰り返し不可(虚読):同じトランザクションで2回読み込まれたデータが異なります.
  • 幻読:1つのトランザクション・オペレーション(DML)データ・テーブルにすべてのレコードが追加され、もう1つのトランザクションにデータが追加されると、最初のトランザクション・クエリは自分の変更を行わない.

  • 独立性レベル:
  • read uncommitted:読み取り未送信
  • で発生した問題:汚れ読み、繰り返し不可読み、幻読み
  • read committed:リードコミット(Oracle)
  • で発生した問題:繰り返し不可、幻読
  • repeatable read:繰り返し可能(MySQLデフォルト)
  • で発生した問題:幻読
  • serializable:シリアル化
  • はすべての問題を解決することができる
  • 注意:独立性レベルは小さいから大きいまでセキュリティはますます高くなりますが、効率はますます低くなります.
  • データベースクエリー独立性レベル:select @@tx_isolation;
  • データベース独立性レベルの設定:set global transaction isolation level ;
  • デモ:
    • set global transaction isolation level read uncommitted;
start transaction;
--     
update account set balance = balance - 500 where id = 1;
update account set balance = balance + 500 where id = 2;

    DCL:
    SQL分類:
  • DDL:操作データベースと表
  • DML:添削改表中のデータ
  • DQL:クエリー・テーブルのデータ
  • DCL:ユーザーの管理、ライセンス
    • DCL:ユーザーの管理、認可
  • 管理ユーザ
  • ユーザーの追加:構文:CREATE USER ' '@' ' IDENTIFIED BY ' ';
  • 削除ユーザ:構文:DROP USER ' '@' ';
  • ユーザーパスワードの変更:
    • -- UPDATE USER SET PASSWORD = PASSWORD('   ') WHERE USER = '   ';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';

-- SET PASSWORD FOR '   '@'   ' = PASSWORD('   ');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');

    mysqlでrootユーザーのパスワードを忘れましたか?
  • cmd->net stop mysql mysql mysqlサービスを停止します.このcmd 2を管理者が実行する必要がある.認証なしでmysqlサービスを開始する:mysqld--skip-grant-tables 3.新しいcmdウィンドウを開き、mysqlコマンドを直接入力し、車をノックします.ログイン成功4.use mysql; 5.update user set password=password(‘あなたの新しいパスワード’)where user=‘root’;6.2つのウィンドウを閉じる.タスクマネージャを開き、mysqldを手動で終了します.exeのプロセス8.mysqlサービス9を起動します.新しいパスワードでログインします.

    • ユーザーの問合せ:
    -- 1.    mysql   
USE myql;

-- 2.   user 
SELECT * FROM USER;
  • ワイルドカード:%は、任意のホストでユーザ登録データベース
    • を使用できることを示す.
    権限管理:
  • クエリー権限:
    • --     
SHOW GRANTS FOR '   '@'   ';
SHOW GRANTS FOR 'lisi'@'%';
  • 権限付与:
    • --     
grant      on     .   to '   '@'   ';

--            ，          
GRANT ALL ON *.* TO 'zhangsan'@'localhost';
  • 取り消し権限:
    • --     ：
revoke      on     .   from '   '@'   ';
REVOKE UPDATE ON db3.`account` FROM 'lisi'@'%';
    [MYSQL]文字分割、文字を配列に分割
    mysql実装パケットクエリー各クラスの上位3名