MySQL SQL Tutorial

MySQL - SQL tutorial Frank Shen 申建忠

MySQL Architecture Connection Thread Pool Query Cache Parser Optimizer client 1 client 2 client n Cache&Buffer Memory Storage Engines MySQL Server InnoDB MyISAM File System Memory Data Node2 NDB Data Node Data Node Data Node Network Data Node Authentication

MySQL Clients mysql-執⾏行行Query/DML/DDL的操作介⾯面 mysqladmin-管理理MySQL Server的操作介⾯面 mysqlcheck-檢查MySQL Table的完整性 mysqldump-產⽣生MySQL Table邏輯備份的⼯工具 mysqlimport-匯入⽂文字檔案內容到MySQL Table的⼯工具 mysqlshow-顯⽰示資料庫/表格/欄欄位資訊的⼯工具 mysqlslap-模擬客⼾戶端⼯工作負擔的⼯工具

Connection Layer Connection Thread Pool Authentication Query Cache

Communication Protocol Protocol Connection Type Supported OS TCP/IP Local/Remote ALL UNIX Socket File Local UNIX Shared Memory Local Windows Named Pipes Local Windos

SQL Layer Parser Syntax Check Semantic Check Query Transformation Optimizer Statistics Optimal Execution Plan

Storage Engines Disk Base InnoDB(Default) MyISAM Memory Memory Network NDB

Installation Type http://www.mysql.com/downloads Binary RPM TarBall Source Compile

RPM Installation [root@MySQL1 ~]# rpm -i MySQL-client-5.5.30-1.rhel5.x86_64.rpm [root@MySQL1 ~]# rpm -i MySQL-devel-5.5.30-1.rhel5.x86_64.rpm [root@MySQL1 ~]# rpm -i MySQL-shared-5.5.30-1.rhel5.x86_64.rpm [root@MySQL1 ~]# rpm -i MySQL-test-5.5.30-1.rhel5.x86_64.rpm [root@MySQL1 ~]# /etc/init.d/mysql start [root@MySQL1 ~]# /usr/bin/mysql_secure_installation

[root@MySQL1 ~}# groupadd mysql [root@MySQL1 ~}# useradd -g mysql mysql [root@MySQL1 ~}# passwd mysql [root@MySQL1 ~]# mv /stage/mysql-5.5.8-linux2.6-x86_64.tar.gz /usr/local [root@MySQL1 ~]# cd /usr/local [root@MySQL1 local]# tar -zxvf mysql-5.5.8-linux2.6-x86_64.tar.gz [root@MySQL1 local]# ln -s /usr/local/mysql-5.5.8-linux2.6-x86_64 mysql [root@MySQL1 local]# cd mysql [root@MySQL1 mysql]# chown -R mysql . [root@MySQL1 mysql]# chgrp -R mysql . [root@MySQL1 mysql]# scripts/mysql_install_db —user=mysql [root@MySQL1 mysql]# chown -R root . [root@MySQL1 mysql]# chown -R mysql data [root@MySQL1 mysql]# cp support-files/my-medium.cnf /etc/my.cnf [root@MySQL1 mysql]# bin/mysqld_safe --user=mysql & [root@MySQL1 mysql]# cp support-files/mysql.server /etc/init.d/mysql

Database/Table and Directory/datafile

Create Database [root@MySQL1 ~]# mysql -uroot -poracle mysql> show variables like 'datadir'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | datadir | /usr/local/mysql/data/ | +---------------+------------------------+ 1 row in set (0.00 sec) mysql> create database world_innodb; Query OK, 1 row affected (0.00 sec) mysql> use world_innodb; Database changed mysql> show tables; Empty set (0.00 sec) [root@MySQL1 ~]# ll /usr/local/mysql/data/ drwx------. 2 mysql mysql 4096 2014-06-29 21:49 world_innodb

Database and Directory mysql> show variables like 'datadir'; +---------------+------------------------+ | Variable_name | Value | +---------------+------------------------+ | datadir | /usr/local/mysql/data/ | +---------------+------------------------+ 1 row in set (0.00 sec) mysql> use world_innodb; Database changed mysql> show tables; Empty set (0.00 sec) [root@MySQL1 ~]# ll /usr/local/mysql/data/ drwx------. 2 mysql mysql 4096 2014-06-29 21:49 world_innodb

Create Table mysql> show tables; +------------------------+ | Tables_in_world_innodb | +------------------------+ | City | | Country | | CountryLanguage | +------------------------+ 3 rows in set (0.01 sec) mysql> show create table City; +-------+-----------------------------------------------------------------+ | City | CREATE TABLE `City` ( `ID` int(11) NOT NULL AUTO_INCREMENT, `Name` char(35) NOT NULL DEFAULT '', `CountryCode` char(3) NOT NULL DEFAULT '', `District` char(20) NOT NULL DEFAULT '', `Population` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`ID`), KEY `CountryCode` (`CountryCode`), CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`) ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1 |

mysql> select count(*) from City; +----------+ | count(*) | +----------+ | 4079 | +----------+ 1 row in set (0.06 sec) mysql> select count(*) from city; ERROR 1146 (42S02): Table 'world_innodb.city' doesn't exist mysql> select count(id) from City; +-----------+ | count(id) | +-----------+ | 4079 | +-----------+ 1 row in set (0.01 sec)

Table and Files [root@MySQL1 ~]# ll /usr/local/mysql/data/world_innodb/ 總計 888 -rw-rw----. 1 mysql mysql 8710 2014-06-29 21:49 City.frm -rw-rw----. 1 mysql mysql 589824 2014-06-29 21:50 City.ibd -rw-rw----. 1 mysql mysql 9172 2014-06-29 21:49 Country.frm -rw-rw----. 1 mysql mysql 163840 2014-06-29 21:50 Country.ibd -rw-rw----. 1 mysql mysql 8702 2014-06-29 21:49 CountryLanguage.frm -rw-rw----. 1 mysql mysql 114688 2014-06-29 21:50 CountryLanguage.ibd -rw-rw----. 1 mysql mysql 65 2014-06-29 21:48 db.opt

mysql> select * from t1; +------+------------+ | id | first_name | +------+------------+ | 1 | ABC | | -1 | DEF | +------+------------+ 2 rows in set (0.00 sec) mysql> select * from t2; +------+------------+ | id | first_name | +------+------------+ | 1 | ABC | | 0 | DEF | +------+------------+ 2 rows in set (0.00 sec)

INT Type Storage Min (Singed/UnSigned) Max (Singed/UnSigned) TinyINT 1 -128/ 0 127/ 255 SmallINT 2 -32768/ 0 3277/ 65535 MediumINT 3 -8388608/ 0 8388607/ 16777215 INT 4 -214783648/ 0 2147483647/ 4294967295 BigINT 8 -9223372036854775808/ 0 9223372036854775807/ 18446744073709551615

DECIMAL Precision: The number of significant digits Scale: The number of digits to the right of the decimal point DECIMAL(5,2) -> 5=Precision 2=Scale digit=0,1,2,3,4,5,6,7,8,9 DECIMAL(5,2)的最⼤大值為999.99,最⼩小值為-999.99 DECIMAL(5)=DECIMAL(5,0) DECIMAL=DECIMAL(10,0)

mysql> create table t3 (id1 int,id2 decimal(5,2)); Query OK, 0 rows affected (0.01 sec) mysql> insert into t3 values(1234.5,1234.5); Query OK, 1 row affected, 1 warning (0.00 sec) mysql> select * from t3; +------+--------+ | id1 | id2 | +------+--------+ | 1235 | 999.99 | --INT⾃自動4捨5入,DECIMAL超過最⼤大值時,將以最⼤大值儲存 +------+--------+ 1 row in set (0.00 sec)

mysql> insert into t3 values(123.45,123.45); Query OK, 1 row affected (0.00 sec) mysql> select * from t3; +------+--------+ | id1 | id2 | +------+--------+ | 1235 | 999.99 | | 123 |123.45 | +------+--------+ 2 rows in set (0.00 sec) mysql> insert into t3 values(12.345,12.345); Query OK, 1 row affected, 1 warning (0.01 sec) mysql> select * from t3; +------+--------+ | id1 | id2 | +------+--------+ | 1235 | 999.99 | | 123 | 123.45 | | 12 |12.35 | --DECIMAL也對超過scale限制部分進⾏行行4捨5入 +------+--------+ 3 rows in set (0.00 sec)

CHARACTER CharacterSet-字符集 ascii latin-1 utf-8 big5 Collation-⼤大⼩小順序 CI(Case InSensitive)

字符集 character-set- default 預設值 character-set- client 客⼾端請求字符集 character-set- results 請求結果字符集 character-set- server character-set- database character-set- table

mysql> show collation like 'big5%'; +-----------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +-----------------+---------+----+---------+----------+---------+ | big5_chinese_ci | big5 | 1 | Yes | Yes | 1 | | big5_bin | big5 | 84 | | Yes | 1 | mysql> show collation like 'ascii%'; +------------------+---------+----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +------------------+---------+----+---------+----------+---------+ | ascii_general_ci | ascii | 11 | Yes | Yes | 1 | | ascii_bin | ascii | 65 | | Yes | 1 | +------------------+---------+----+---------+----------+---------+ mysql> show collation like 'utf32%'; +---------------------+---------+-----+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +---------------------+---------+-----+---------+----------+---------+ | utf32_general_ci | utf32 | 60 | Yes | Yes | 1 | | utf32_bin | utf32 | 61 | | Yes | 1 | | utf32_unicode_ci | utf32 | 160 | | Yes | 8 |

CHAR Value CHAR(4) Storage Required VARCHAR(4) Storage Required ‘ ’ ‘ ‘ 4 bytes ‘ ‘ 1 bytes ‘ab’ ‘ab ‘ 4 bytes ‘ab’ 3 bytes ‘abcd’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes ‘abcdefgh’ ‘abcd’ 4 bytes ‘abcd’ 5 bytes

DATE DATE(YYYY-MM-DD) 1000-01-01~9999-12-31 DATETIME(YYYY-MM-DD HH:MI:SS) 1000-01-01 00:00:00~9999-12-31 23:59:59 TIMESTAMP(YYYY-MM-DD HH:MI:SS) 1970-01-01 00:00:01~2038-01-19 03:14:07 UTC MySQL interprets two-digit year values using these rules: Year values in the range 00-69 are converted to 2000-2069. Year values in the range 70-99 are converted to 1970-1999.

MyISAM Features Storage limits 256TB Transactions No Locking Granularrity Table MVCC No Geosptial data type support Yes Geosptial indexing support Yes B-tree indexes Yes T-tree indexes No Hash indexes No Full-text search indexes Yes Clustered indexes No Data cache No Index caches Yes Compressed data Yes Encrypted data Yes Cluster database support No Replication support Yes Foreign key support No Backup/Point- in-time recovery Yes Query cache Support Yes Update statistics for data dictionary Yes

mysql> show variables like 'datadir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | datadir | /usr/local/mysql/data/| +---------------+-----------------------+ 1 row in set (0.00 sec) --在datadir⽬目錄下,將⾃自動產⽣生⼀一個⼦子⽬目錄myisam_db mysql> create database myisam_db; Query OK, 1 row affected (0.00 sec) --建立⼀一個新myisam表格,在相對的資料庫⽬目錄下,⾃自動產⽣生3個file(*.MYD,*.MYI,*.frm) mysql> create table myisam_db.t1(id int,name char(10)) > engine=myisam; --明確要求此表格為MyISAM形態 Query OK, 0 rows affected (0.01 sec) mysql> insert into myisam_db.t1 > select id,substr(name,1,10) from world_innodb.City; Query OK, 4079 rows affected (0.01 sec) Records: 4079 Duplicates: 0 Warnings: 0 [root@MySQL1 ~]# ls -l /usr/local/mysql/data/myisam_db --資料庫 total 84 -rw-rw---- 1 mysql mysql 65 Mar 20 15:48 db.opt -rw-rw---- 1 mysql mysql 8586 Mar 20 15:48 t1.frm --表格定義 -rw-rw---- 1 mysql mysql 61185 Mar 20 15:48 t1.MYD --表格資料 -rw-rw---- 1 mysql mysql 1024 Mar 20 15:48 t1.MYI --資料索引

InnoDB Features Storage limits 64TB Transactions Yes Locking Granularrity Row MVCC Yes Geosptial data type support Yes Geosptial indexing support No B-tree indexes Yes T-tree indexes Yes Adaptive Hash indexes No Full-text search indexes Yes Clustered indexes No Data cache Yes Index caches Yes Compressed data Yes Encrypted data Yes Cluster database support No Replication support Yes Foreign key support Yes Backup/Point- in-time recovery Yes Query cache Support Yes Update statistics for data dictionary Yes Multiple buffer pools Yes Performance Schema Yes Automatic Crash Recovery Yes

儲存⽅方式類似Oracle的Index Organized Table MSSQL的Clustered Table 每個InnoDB的table都需要⼀一個Primary Key,當作索引欄欄位如何選擇Primary Key 明確指定Primary Key Unique Constraint+NOT NULL限制的欄欄位⾃自動產⽣生⼀一個隱藏欄欄位(6 Bytes)當作Primary Key

mysql> show variables like 'datadir'; +---------------+-----------------------+ | Variable_name | Value | +---------------+-----------------------+ | datadir | /usr/local/mysql/data/| +---------------+-----------------------+ 1 row in set (0.00 sec) --在datadir⽬目錄下,將⾃自動產⽣生⼀一個⼦子⽬目錄innodb_db mysql> create database innodb_db; Query OK, 1 row affected (0.00 sec) --建立⼀一個新InnoDB表格,在相對的資料庫⽬目錄下,⾃自動產⽣生1個file(*.frm) --除非設定innodb_file_per_table=TRUE,不然所有Innodb表格的資料都集中儲存在ibdata1 mysql> create table innodb_db.t1(id int,name char(10)) > engine=InnoDB; --明確要求此表格為InnoDB形態 Query OK, 0 rows affected (0.01 sec) mysql> insert into innodb_db.t1 > select id,substr(name,1,10) from world_innodb.City; Query OK, 4079 rows affected (0.01 sec) Records: 4079 Duplicates: 0 Warnings: 0 [root@MySQL1 ~]# ls -l /usr/local/mysql/data/innodb_db --資料庫 total 84 -rw-rw---- 1 mysql mysql 65 Mar 20 15:48 db.opt -rw-rw---- 1 mysql mysql 8586 Mar 20 15:48 t1.frm --表格定義 [root@MySQL1 ~]# ls -l /usr/local/mysql/data/ibdata1 -rw-rw---- 1 mysql mysql 18874368 Mar 20 15:48 /var/lib/mysql/ibdata1 --表格資料

mysql> set @@global.innodb_file_per_table=on; Query OK, 0 rows affected (0.00 sec) mysql> create table innodb_db.t2(id int primary key,name varchar(10)) engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert into innodb_db.t2 values(1,'A'),(2,'B'),(3,'C'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> select * from innodb_db.t2; +----+------+ | id | name | +----+------+ | 1 | A | | 2 | B | | 3 | C | +----+------+ 3 rows in set (0.00 sec) [root@MySQL1 ~]# ls -l /usr/local/mysql/data/ib* -rw-rw---- 1 mysql mysql 18874368 Mar 20 14:16 /usr/local/mysql/data/ibdata1 -rw-rw---- 1 mysql mysql 5242880 Mar 20 14:16 /usr/local/mysql/data/ib_logfile0 -rw-rw---- 1 mysql mysql 5242880 Mar 18 14:35 /usr/local/mysql/data/ib_logfile1 [root@MySQL1 ~]# ls -l /usr/local/mysql/data/innodb_db/t2.* -rw-rw---- 1 mysql mysql 8586 Mar 20 14:16 /usr/local/mysql/data/innodb_db/t2.frm --雖然t2的table data/index獨立儲存在t2.idb裡.但是其使⽤用的undo data還是在共⽤用tablespace裡　 -rw-rw---- 1 mysql mysql 98304 Mar 20 14:16 /usr/local/mysql/data/innodb_db/t2.ibd

Transaction Atomicity A transaction’s changes to the state are atomic: either all happen or none happen. These changes include database changes, messages, and actions on transducers. Consistency A transaction is a correct transformation of the state. The actions taken as a group do not violate any of the integrity constraints associated with the state. Isolation Even though transactions execute concurrently, it appears to each transaction T, that others executed either before T or after T, but not both. Durability Once a transaction completes successfully (commits), its changes to the state survive failures. source:http://en.wikipedia.org/wiki/Transaction_processing#ACID_criteria

mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ mysql> select engine,support > from information_schema.engines > where transactions='YES'; +--------+---------+ | engine | support | +--------+---------+ | InnoDB | DEFAULT | +--------+---------+ 1 row in set (0.00 sec) Storage Engines and Transaction

Transaction Control Language START TRANSACTION COMMIT ROLLBACK SAVEPOINT savepoint_name ROLLBACK TO savepoint_name RELEASE SAVEPOINT savepoint_name

mysql> show variables like 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+ 1 row in set (0.01 sec) mysql> create table world_innodb.t1(a int primary key) engine=innodb; Query OK, 0 rows affected (0.02 sec) mysql> create table world_innodb.t2(a int primary key) engine=myisam; Query OK, 0 rows affected (0.00 sec) mysql> insert into world_innodb.t1 values(10); Query OK, 1 row affected (0.01 sec) mysql> insert into world_innodb.t2 values(10); Query OK, 1 row affected (0.00 sec) mysql> rollback; mysql> select * from world_innodb.t1; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec) mysql> select * from world_innodb.t2; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec)

mysql> set autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(20); Query OK, 1 row affected (0.00 sec) mysql> insert into t2 values(20); Query OK, 1 row affected (0.01 sec) mysql> rollback; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> show warnings; +---------+------+---------------------------------------------------------------+ | Level | Code | Message | +---------+------+---------------------------------------------------------------+ | Warning | 1196 | Some non-transactional changed tables couldn't be rolled back | +---------+------+---------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> select * from world_innodb.t1; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec) mysql> select * from world_innodb.t2; +----+ | a | +----+ | 10 | | 20 | +----+ 1 row in set (0.00 sec)

mysql> set autocommit=1; Query OK, 0 rows affected (0.00 sec) mysql> start transaction; Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values(30); Query OK, 1 row affected (0.00 sec) mysql> insert into t1 values(40); Query OK, 1 row affected (0.00 sec) mysql> select * from t1; +----+ | a | +----+ | 10 | | 20 | | 30 | +----+ 3 rows in set (0.00 sec) mysql> rollback; Query OK, 0 rows affected (0.01 sec) mysql> select * from t1; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec)

Isolation Problems Dirty read A dirty read (aka uncommitted dependency) occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed. Non-Repeatable read A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads. Phantom read A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first. source:http://en.wikipedia.org/wiki/Isolation_(database_systems)

Isolation Mode Read UnCommitted Dirty Read Read Committed Non-Repeatable Read/Phantom Read Repeatable Read Non-Repeatable Read/Phantom Read(InnoDB沒問題) Serializable Non-Repeatable Read/Phantom Read

mysql> select @@global.tx_isolation,@@session.tx_isolation; +-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | REPEATABLE-READ | +-----------------------+------------------------+ 1 row in set (0.00 sec) mysql> set session transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql> select @@global.tx_isolation,@@session.tx_isolation; +-----------------------+------------------------+ | @@global.tx_isolation | @@session.tx_isolation | +-----------------------+------------------------+ | REPEATABLE-READ | READ-COMMITTED | +-----------------------+------------------------+ 1 row in set (0.00 sec)

Lock Get Lock Explicit TABLE Level mysql> LOCK TABLES t1 READ; mysql> LOCK TABLES t2 WRITE; ROW Level mysql> SELECT * FROM City WHERE id=123 LOCK IN SHARE MODE; mysql> SELECT * FROM City WHERE id=123 FOR UPDATE; Implicit Lock table or row(InnoDB) based on the SQL commands issued and Storage Engine being used. MODE Shared Exclusive

Implicit Locks InnoDB MyISAM SELECT No Lock Table-level Shared Lock UPDAT/DELETE Row-level Exclusive Lock Table-level Exclusive lock ALTER TABLE Table-level Shared lock Table-level Shared lock

Interaction of Table Locking and Transactions LOCK TABLES is not transaction-safe and implicitly commits any active transaction before attempting to lock the tables. UNLOCK TABLES implicitly commits any active transaction, but only if LOCK TABLES has been used to acquire table locks. FLUSH TABLES WITH READ LOCK acquires a global read lock and not table locks, so it is not subject to the same behavior as LOCK TABLES and UNLOCK TABLES with respect to table locking and implicit commits.

mysql> LOCK TABLES t1 READ; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec) mysql> select * from t2; ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLES mysql> LOCK TABLES t2 WRITE; Query OK, 0 rows affected (0.01 sec) mysql> select * from t2; +----+ | a | +----+ | 10 | | 20 | | 30 | +----+ 3 rows in set (0.01 sec) mysql> UNLOCK TABLES; Query OK, 0 rows affected (0.00 sec)

mysql> flush table t2,t1 with read lock; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec) mysql> select * from t2; +----+ | a | +----+ | 10 | | 20 | | 30 | +----+ 3 rows in set (0.00 sec) mysql> unlock tables; Query OK, 0 rows affected (0.00 sec) mysql> flush tables t2 with read lock; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; ERROR 1100 (HY000): Table 't1' was not locked with LOCK TABLES mysql> flush tables t1 with read lock; ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction mysql> lock tables t1 read; Query OK, 0 rows affected (0.00 sec) mysql> select * from t1; +----+ | a | +----+ | 10 | +----+ 1 row in set (0.00 sec)

3A Authentication Who are you ? Access Control,Stage 1 implement on Connection Layer Authorization What can you do ? Access Control,Stage 2 implement on Connection Layer Audit What was you done ?

mysql> desc mysql.user; +------------------------+-----------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------------+-----------------------------------+------+-----+---------+-------+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum('N','Y') | NO | | N | | | Insert_priv | enum('N','Y') | NO | | N | | | Update_priv | enum('N','Y') | NO | | N | | | Delete_priv | enum('N','Y') | NO | | N | | | Create_priv | enum('N','Y') | NO | | N | | | Drop_priv | enum('N','Y') | NO | | N | | | Reload_priv | enum('N','Y') | NO | | N | | | Shutdown_priv | enum('N','Y') | NO | | N | | | Process_priv | enum('N','Y') | NO | | N | | | File_priv | enum('N','Y') | NO | | N | | | Grant_priv | enum('N','Y') | NO | | N | | | References_priv | enum('N','Y') | NO | | N | | | Index_priv | enum('N','Y') | NO | | N | | | Alter_priv | enum('N','Y') | NO | | N | | | Show_db_priv | enum('N','Y') | NO | | N | | | Super_priv | enum('N','Y') | NO | | N | | | Create_tmp_table_priv | enum('N','Y') | NO | | N | | | Lock_tables_priv | enum('N','Y') | NO | | N | | | Execute_priv | enum('N','Y') | NO | | N | | | Repl_slave_priv | enum('N','Y') | NO | | N | | | Repl_client_priv | enum('N','Y') | NO | | N | | | Create_view_priv | enum('N','Y') | NO | | N | | | Show_view_priv | enum('N','Y') | NO | | N | | | Create_routine_priv | enum('N','Y') | NO | | N | | | Alter_routine_priv | enum('N','Y') | NO | | N | | | Create_user_priv | enum('N','Y') | NO | | N | | | Event_priv | enum('N','Y') | NO | | N | | | Trigger_priv | enum('N','Y') | NO | | N | | | Create_tablespace_priv | enum('N','Y') | NO | | N | | | ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | | | ssl_cipher | blob | NO | | NULL | | | x509_issuer | blob | NO | | NULL | | | x509_subject | blob | NO | | NULL | | | max_questions | int(11) unsigned | NO | | 0 | | | max_updates | int(11) unsigned | NO | | 0 | | | max_connections | int(11) unsigned | NO | | 0 | | | max_user_connections | int(11) unsigned | NO | | 0 | | | plugin | char(64) | YES | | | | | authentication_string | text | YES | | NULL | | +------------------------+-----------------------------------+------+-----+---------+-------+ 42 rows in set (0.00 sec)

With InnoDB tables, OPTIMIZE TABLE re-creates the table. CHECK TABLE options that are applicable to MyISAM only: QUICK: Do not scan the rows to check for incorrect links. FAST: Check only tables that were not closed properly. CHANGED: Check only tables that changed since the last check or were not closed properly. MEDIUM: Scan rows to verify that deleted links are valid. EXTENDED: Do a full key lookup for all keys for each row. Some mysqlcheck maintenance options: --analyze: Perform an ANALYZE TABLE. --check: Perform a CHECK TABLE (default). --optimize: Perform an OPTIMIZE TABLE. --repair: Perform a REPAIR TABLE

Exportmysql> create database demodb; Query OK, 1 row affected (0.00 sec) mysql> use demodb; Database changed mysql> create table t1(a int primary key,b varchar(10)); Query OK, 0 rows affected (0.00 sec) mysql> insert into t1 values (1,'A'),(2,'B'),(3,'C'),(4,'D'); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from t1; +---+------+ | a | b | +---+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | +---+------+ 4 rows in set (0.00 sec) mysql> flush tables with read lock; --只允許讀取,不允許異異動 [root@MySQL1 ~]# mysqldump --master-data demodb -uroot -poracle > demodb.sql mysql> unlock tables;

Import [root@MySQL ~]# mysql -uroot -poracle mysql> create database new_demodb; Query OK, 1 row affected (0.00 sec) mysql> use new_demodb; Database changed mysql> source /root/demodb.sql mysql> select * from new_demodb.t1; +---+------+ | a | b | +---+------+ | 1 | A | | 2 | B | | 3 | C | | 4 | D | +---+------+ 4 rows in set (0.00 sec)

MySQL SQL Tutorial

More Related Content

What's hot

Similar to MySQL SQL Tutorial

More from Chien Chung Shen

Recently uploaded

MySQL SQL Tutorial