# 查看當(dāng)前的默認(rèn)存儲(chǔ)引擎:
mysql> show variables like "default_storage_engine";

# 查詢當(dāng)前數(shù)據(jù)庫(kù)支持的存儲(chǔ)引擎
mysql> show engines \G;

mysql> create table ai(id bigint(12),name varchar(200)) ENGINE=MyISAM; 
mysql> create table country(id int(4),cname varchar(50)) ENGINE=InnoDB;

# 也可以使用alter table語(yǔ)句，修改一個(gè)已經(jīng)存在的表的存儲(chǔ)引擎。
mysql> alter table ai engine = innodb;

# my.ini文件
[mysqld]
default-storage-engine=INNODB

mysql> desc staff_info;
+-------+-----------------------+------+-----+---------+-------+
| Field | Type                  | Null | Key | Default | Extra |
+-------+-----------------------+------+-----+---------+-------+
| id    | int(11)               | YES  |     | NULL    |       |
| name  | varchar(50)           | YES  |     | NULL    |       |
| age   | int(3)                | YES  |     | NULL    |       |
| sex   | enum('male','female') | YES  |     | NULL    |       |
| phone | bigint(11)            | YES  |     | NULL    |       |
| job   | varchar(11)           | YES  |     | NULL    |       |
+-------+-----------------------+------+-----+---------+-------+
rows in set (0.00 sec)

mysql> show create table staff_info\G;
*************************** 1. row ***************************
       Table: staff_info
Create Table: CREATE TABLE `staff_info` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(50) DEFAULT NULL,
  `age` int(3) DEFAULT NULL,
  `sex` enum('male','female') DEFAULT NULL,
  `phone` bigint(11) DEFAULT NULL,
  `job` varchar(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
row in set (0.01 sec)

ERROR: 
No query specified

--不指定id，則自動(dòng)增長(zhǎng)
create table student(id int primary key auto_increment,name varchar(20),sex enum('male','female') default 'male');

mysql> desc student;
+-------+-----------------------+------+-----+---------+----------------+
| Field | Type                  | Null | Key | Default | Extra          |
+-------+-----------------------+------+-----+---------+----------------+
| id    | int(11)               | NO   | PRI | NULL    | 
auto_increment  |
| name  | varchar(20)           | YES  |     | NULL    |                |
| sex   | enum('male','female') | YES  |     | male    |                |
+-------+-----------------------+------+-----+---------+----------------+
mysql> insert into student(name) values ('nick'),('tank') ;

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | nick | male |
|  2 | tank | male |
+----+------+------+


--也可以指定id
mysql> insert into student values(4,'asb','female');
Query OK, 1 row affected (0.00 sec)

mysql> insert into student values(7,'wsb','female');
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+------+--------+
| id | name | sex    |
+----+------+--------+
|  1 | nick | male   |
|  2 | tank | male   |
|  4 | asb  | female |
|  7 | wsb  | female |
+----+------+--------+


--對(duì)于自增的字段，在用delete刪除后，再插入值，該字段仍按照刪除前的位置繼續(xù)增長(zhǎng)
mysql> delete from student;
Query OK, 4 rows affected (0.00 sec)

mysql> select * from student;
Empty set (0.00 sec)

mysql> insert into student(name) values('ysb');
mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  8 | ysb  | male |
+----+------+------+

--應(yīng)該用truncate清空表，比起delete一條一條地刪除記錄，truncate是直接清空表，在刪除大表時(shí)用它
mysql> truncate student;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into student(name) values('nick');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+------+------+
| id | name | sex  |
+----+------+------+
|  1 | nick | male |
+----+------+------+
row in set (0.00 sec)

--在創(chuàng)建完表后，修改自增字段的起始值
mysql> create table student(id int primary key auto_increment, name varchar(20),sex enum('male','female') default 'male');
mysql> alter table student auto_increment=3 ;
mysql> show create table student;
.......
ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8

--也可以創(chuàng)建表時(shí)指定auto_increment的初始值，注意初始值的設(shè)置為表選項(xiàng)，應(yīng)該放到括號(hào)外
mysql> create table student(id int primary key auto_increment, name varchar(20),sex enum('male','female') default 'male' 
                           )auto_increment=3 ;

mysql> create table t10 (name char(20),gender enum('female','male') );
Query OK, 0 rows affected (0.01 sec)

-- 選擇enum('female','male')中的一項(xiàng)作為gender的值，可以正常插入
mysql> insert into t10 values ('nick','male');
Query OK, 1 row affected (0.00 sec)

-- 不能同時(shí)插入'male,female'兩個(gè)值，也不能插入不屬于'male,female'的值
mysql> insert into t10 values ('nick','male,female');
ERROR 1265 (01000): Data truncated for column 'gender' at row 1

mysql> create table t11 (name char(20),hobby set('抽煙','喝酒','燙頭','翻車(chē)') );
Query OK, 0 rows affected (0.01 sec)

-- 可以任意選擇set('抽煙','喝酒','燙頭','翻車(chē)')中的項(xiàng)，并自帶去重功能
mysql> insert into t11 values ('tank','燙頭,喝酒,燙頭');
Query OK, 1 row affected (0.01 sec)

mysql> select * from t11;
+------+---------------+
| name | hobby        |
+------+---------------+
| tank | 喝酒,燙頭     |
+------+---------------+
row in set (0.00 sec)

-- 不能選擇不屬于set('抽煙','喝酒','燙頭','翻車(chē)')中的項(xiàng)，
mysql> insert into t11 values ('jason','燙頭,翻車(chē),看妹子');
ERROR 1265 (01000): Data truncated for column 'hobby' at row 1

SELECT * FROM employee ORDER BY salary DESC 
    LIMIT 3;    --默認(rèn)初始位置為0 

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 0 , 5 ; --從第0開(kāi)始，即先出第一條，然后包含這一條在內(nèi)往后查5條

SELECT * FROM employee ORDER BY salary DESC
    LIMIT 5 , 5 ; --從第5開(kāi)始，即先出第6條，然后包含這一條在內(nèi)往后查5條

SELECT * FROM employee WHERE emp_name REGEXP '^jas';
SELECT * FROM employee WHERE emp_name REGEXP 'on$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';

--語(yǔ)法：
mysqldump -h 服務(wù)器 -u用戶名 -p密碼 數(shù)據(jù)庫(kù)名 > 備份文件.sql

--示例：
--單庫(kù)備份
mysqldump -uroot –p123  mysql > c:\db1.sql
mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql

--多庫(kù)備份
mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql

--備份所有庫(kù)
mysqldump -uroot -p123 --all-databases > all.sql

--方法一：
[root@nick backup]-- mysql -uroot -p123 < /backup/all.sql

--方法二：
mysql> use db1;
mysql> SET SQL_LOG_BIN=0;   --關(guān)閉二進(jìn)制日志，只對(duì)當(dāng)前session生效
mysql> source /root/db1.sql

begin;  -- 開(kāi)啟事務(wù)
   select * from emp where id = 1 for update;  -- 查詢id值，for update添加行鎖；
    update emp set salary=10000 where id = 1; -- 完成更新
commit; -- 提交事務(wù)

explain select * from news;

+--+-----------+-----+----+-------------+---+-------+---+----+-----+?
|id|select_type|table|type|possible_keys|key|key_len|ref|rows|Extra|?
+--+-----------+-----+----+-------------+---+-------+---+----+----—+