Create database語句
? Create database語句是在MySQL實例上創(chuàng)建一個指定名稱的數(shù)據(jù)庫，create schema語句的語義和create database是一樣的
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...
carete_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_name
? 當創(chuàng)建的數(shù)據(jù)庫本身存在而且沒有寫明if not exists子句時，則創(chuàng)建數(shù)據(jù)庫的語句會報錯
? create_specification子句指明創(chuàng)建的數(shù)據(jù)庫的屬性，并存儲在db.opt文件中
? Character set屬性指明此數(shù)據(jù)庫的默認字符集
? Collate屬性指明此數(shù)據(jù)庫的默認排序規(guī)則
? 創(chuàng)建后的數(shù)據(jù)庫在數(shù)據(jù)文件所在目錄會創(chuàng)建一個自己的文件目錄，用來包含后續(xù)創(chuàng)建的表文件
? 當然，也可以直接通過mkdir的操作系統(tǒng)命令在數(shù)據(jù)目錄創(chuàng)建文件夾，則MySQL會識別為一個數(shù)據(jù)庫，并在執(zhí)行show databases命令時可以看到
[root@vmware1 data]# mkdir test4
[root@vmware1 data]# chown mysql:mysql –R test4
mysql> show create database test4;
+----------+------------------------------------------------------------------+
| Database | Create Database |
+----------+------------------------------------------------------------------+
| test4 | CREATE DATABASE `test4` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+------------------------------------------------------------------+
mysql> create database test3;
Query OK, 1 row affected (0.00 sec)
mysql> create database test3;
ERROR 1007 (HY000): Can't create database 'test3'; database exists
mysql> create database if not exists test3;
Query OK, 1 row affected, 1 warning (0.00 sec)
Mysql> use test3;
Create table語句
? Create table語句是在數(shù)據(jù)庫中創(chuàng)建表
? tbl_name表示被創(chuàng)建的表名，默認在當前數(shù)據(jù)庫下創(chuàng)建此表，當然也可以指定在某個數(shù)據(jù)庫下創(chuàng)建表
?
If not exists表示當相同的表名存在時，則不執(zhí)行此創(chuàng)建語句，避免語句執(zhí)行錯誤?
create table students2(sid int,sname varchar(10));?
create table test3.students2(sid int,sname varchar(10)); 在test3這個數(shù)據(jù)庫下創(chuàng)建students2表?
create table if not exists students2(sid int,sname varchar(10));? temporary關(guān)鍵詞表示創(chuàng)建的是臨時表，臨時表僅對本鏈接可見，另外的數(shù)據(jù)庫鏈接不可見，當本鏈接斷開時，臨時表也自動被drop掉
mysql> create temporary table temp1(sid int,sname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into temp1 values(1,'a');
Query OK, 1 row affected (0.00 sec)
mysql> select * from temp1;
+------+-------+
| sid | sname |
+------+-------+
| 1 | a |
+------+-------+
1 row in set (0.00 sec)
? 另一個數(shù)據(jù)庫鏈接執(zhí)行相同的查詢語句查不到數(shù)據(jù)
mysql> select * from temp1;
ERROR 1146 (42S02): Table 'test.temp1' doesn't exist
? 本數(shù)據(jù)庫鏈接斷開后再鏈接，臨時表也不存在
mysql> select * from temp1;
ERROR 1146 (42S02): Table 'test.temp1' doesn't exist
? Like關(guān)鍵詞表示基于另外一個表的定義復(fù)制一個新的空表，空表上的字段屬性和索引都和原表相同
mysql> create table students_copy like students;
Query OK, 0 rows affected (0.01 sec)
mysql> show create table students_copy;
+---------------+---------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
+
| Table | Create Table
|
+---------------+---------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------
+
| students_copy | CREATE TABLE `students_copy` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(20) DEFAULT NULL,
`sex` int(11) DEFAULT NULL,
UNIQUE KEY `idx_st_sid` (`sid`),
KEY `idx_st_union` (`sname`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
? Create table … as select語句表示創(chuàng)建表的同時將select的查詢結(jié)果數(shù)據(jù)插入到表中，但索引和主外鍵信息都不會同步過來
mysql> create table students_copy2 as select * from students;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc students_copy2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
| sex | int(11) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
? ignore和replace表示在插入數(shù)據(jù)的過程中如果新表中碰到違反唯一約束的情況下怎么處理，ignore表示不插入，replace表示替換已有的數(shù)據(jù)，默認兩個關(guān)鍵詞都不寫則碰到違反的情況會報錯
? data_type表示定義的字段類型
? not null/null表示字段是否允許為空，默認為null表示允許為空，not null表示需要對此字段明確數(shù)值，或者要有默認值，否則報錯
mysql> create table students2(sid int not null,sname varchar(10));
Query OK, 0 rows affected (0.00 sec)
mysql> insert into students2(sname) values('eee');
ERROR 1364 (HY000): Field 'sid' doesn't have a default value
? default表示設(shè)置字段的默認值
create table students3(sid int,sname varchar(10),gender int default 0);
insert into students3 values(1,'a',default);
insert into students3(sid,sname) values(2,'b');
mysql> select * from students3;
+------+-------+--------+
| sid | sname | gender |
+------+-------+--------+
| 1 | a | 0 |
| 2 | b | 0 |
+------+-------+--------+
? Auto_increment表示字段為整數(shù)或者浮點數(shù)類型的value+1遞增數(shù)值，value為當前表中該字段最大的值，默認是從1開始遞增；一個表中只容許有一個自增字段，且該字段必須有key屬性，不能含有default屬性，且插入負值會被當成很大的正數(shù)
mysql> create table students4(sid int auto_increment,sname varchar(10));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table students4(sid int primary key auto_increment,sname varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into students4(sname) values('a');
Query OK, 1 row affected (0.00 sec)
mysql> insert into students4(sid,sname) values(3,'b');
Query OK, 1 row affected (0.01 sec)
mysql> insert into students4(sname) values('c');
Query OK, 1 row affected (0.00 sec)
mysql> select * from students4;
+-----+-------+
| sid | sname |
+-----+-------+
| 1 | a |
| 3 | b |
| 4 | c |
+-----+-------+
? column_format目前僅在innodb存儲引擎的表上有用，表示該字段的存儲類型是fixed, dynamic或者是default
? storage目前也僅在innodb存儲引擎的表上有用
? constraint表示為主鍵、唯一鍵、外鍵等約束條件命名，如果沒有命名則MySQL會默認給一個
? primary key表示該字段為主鍵，主鍵字段必須唯一，必須非空，一個表中只能有一個主鍵，主鍵可以包含一個或多個字段
? key/index表示索引字段
? unique表示該字段為唯一屬性字段，且允許包含多個null值
? foreign key表示該字段為外鍵字段
CREATE TABLE `gender` (
gender_id int(11) NOT NULL,
name varchar(10) DEFAULT NULL,
PRIMARY KEY (gender_id)
)
create table students5(
sid int not null primary key auto_increment,
sname varchar(10) unique,
gender int,
constraint for_1 foreign key (gender) references gender(gender_id)
);
create table students5如果報錯：Cannot add foreign key constraint,從以下兩個方面來檢查：
- 設(shè)置的外鍵字段不能是主鍵；2. 所關(guān)聯(lián)字段的數(shù)據(jù)類型長度需要一樣
Insert語句
? Insert語句用于插入數(shù)據(jù)到表中，其基本語法有以下三種：
? Insert語句三種寫法的常規(guī)用法：
mysql> desc students;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| sid | int(11) | YES | | NULL | |
| sname | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
Insert into students values(1,'aaa');
Insert into students set sid=2,sname='bbb';
Insert into students select * from students_bak;
mysql> select * from students;
+------+-------+
| sid | sname |
+------+-------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
+------+-------+
? 其中insert…values和insert…set兩種語句都是將指定的數(shù)據(jù)插入到現(xiàn)成的表中，而insert…select語句是將另外表中數(shù)據(jù)查出來并插入到現(xiàn)成的表中
? partition子句代表可以將數(shù)據(jù)插入到指定的表分區(qū)中
? tbl_name代表將數(shù)據(jù)插入到的目標表
? col_name代表要插入指定數(shù)據(jù)的目標表列，如果是多列則用逗號隔開，如果目標表中的某些列沒有在Insert語句中指定，則這些列會插入默認值，當然可以使用default顯視指定插入默認值
? Values中除了可以指定確定的數(shù)值之外，還可以使用表達式expr
INSERT INTO tbl_name (col1,col2) VALUES(15,col1*2); ##正確
INSERT INTO tbl_name (col1,col2) VALUES(col2*2,15); ##錯誤
mysql> insert into students(sid,sname) values(4,'ddd');
mysql> insert into students(sid) values(5);
mysql> insert into students(sname) values('eee');
mysql> insert into students value(2*3,'fff');
mysql> select * from students;
+------+-------+
| sid | sname |
+------+-------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
| 4 | ddd |
| 5 | NULL |
| NULL | eee |
| 6 | fff |
+------+-------+
? Insert…values語句不光可以插入一條數(shù)據(jù)，也可以插入多條數(shù)據(jù)
INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);
Insert into students values(7,'abc'),(8,'bcd');
? Insert…values和insert…select語句的執(zhí)行結(jié)果如下
? Records: 100 Duplicates: 0 Warnings: 0
? Records代表此語句操作了多少行數(shù)據(jù)，但不一定是多少行被插入的數(shù)據(jù)，因為如果存
在相同的行數(shù)據(jù)且違反了某個唯一性，則duplicates會顯示非0數(shù)值，warning代表語句執(zhí)
行過程中的一些警告信息
? low_priority關(guān)鍵詞代表如果有其他鏈接正在讀取目標表數(shù)據(jù)，則此insert語句需要等待讀取完成
? low_priority和high_priority關(guān)鍵詞僅在MyISAM, MEMORY, and MERGE三種存儲引擎下才生效
? Ignore關(guān)鍵詞代表insert語句如果違反主鍵和唯一鍵的約束條件，則不報錯而只產(chǎn)生警告信息，違反的行被丟棄，而不是整個語句回退；在數(shù)據(jù)類型轉(zhuǎn)換有問題時如果有ignore則只產(chǎn)生警告信息，而不是語句回退
CREATE TABLE `students` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(20) DEFAULT NULL,
`gender` int(11) DEFAULT NULL,
UNIQUE KEY `idx_st_sid` (`sid`),
KEY `idx_st_union` (`sname`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin
mysql> select * from students;
+------+-------+--------+
| sid | sname | gender |
+------+-------+--------+
| 1 | abc | 1 |
| 2 | abc | 1 |
| 3 | abc | 1 |
mysql> insert into students values(1,'bbb',0);
ERROR 1062 (23000): Duplicate entry '1' for key 'idx_st_sid'
mysql> insert ignore into students values(1,'bbb',0);
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> show warnings;
+---------+------+------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------+
| Warning | 1062 | Duplicate entry '1' for key 'idx_st_sid' |
mysql> insert ignore into students values(1,'aa',0),(5,'bb',1);
Query OK, 1 row affected, 1 warning (0.03 sec)
Records: 2 Duplicates: 1 Warnings: 1
mysql> select * from students3;
+------+-------+--------+
| sid | sname | gender |
+------+-------+--------+
| 1 | a | 0 |
| 2 | b | 0 |
| 4 | c | 1 |
mysql> insert ignore into students select * from students3;
Query OK, 1 row affected, 2 warnings (0.01 sec)
Records: 3 Duplicates: 2 Warnings: 2
Insert…select語句
? 用于從另外的表中查出記錄并插入到目標表中
INSERT INTO tbl_temp2 (fld_id)
SELECT tbl_temp1.fld_order_id
FROM tbl_temp1 WHERE tbl_temp1.fld_order_id > 100;
? 當目標表和select語句中的表相同時，則會先將select語句的結(jié)果存放在臨時表中，再插入到目標表中(注意執(zhí)行順序)
insert into students select * from students;
Insert delayed語句
? 在5.6.6版本之前，用來表示此插入語句當碰到其他鏈接正在使用目標表時就等待，直到目標表沒被用時再插入數(shù)據(jù)
? 在5.7版本時，delayed關(guān)鍵詞就不再支持，但語句執(zhí)行時不會報錯，只會產(chǎn)生一個警告信息，后續(xù)版本會去掉此關(guān)鍵詞
mysql> insert delayed into students select * from students;
Query OK, 18 rows affected, 1 warning (0.00 sec)
Records: 18 Duplicates: 0 Warnings: 1
Insert on duplicate key update語句
? 當insert語句中使用on duplicate key update子句時，如果碰到當前插入的數(shù)據(jù)違反主鍵或唯一鍵的唯一性約束，則Insert會轉(zhuǎn)變成update語句修改對應(yīng)的已經(jīng)存在表中的這條數(shù)據(jù)。比如如果a字段有唯一性約束且已經(jīng)含有1這條記錄，則以下兩條語句的執(zhí)行結(jié)果相同：
INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;
UPDATE table SET c=c+1 WHERE a=1;
? On duplicate key update子句后面可以跟多個修改，用逗號隔開
? 上述例子中如果b字段也有唯一性約束，則與此語句的執(zhí)行結(jié)果相同，但一般應(yīng)該避免出現(xiàn)對應(yīng)多條的情況
UPDATE table SET c=c+1 WHERE a=1 OR b=2 LIMIT 1;
mysql> create table students2(sid int primary key,sname varchar(20),sex int);
mysql> Insert into students2 values(1,’aaa’,1); ##插入成功
mysql> Insert into students2 values(1,’bbb’,0); ##插入失敗
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
insert into students2 values(1,'bbb',0) on duplicate key update sname='bbb';##執(zhí)行成功
insert into students2 values(1,'ccc',0) on duplicate key update sname='ccc',sex=0; ##執(zhí)行成功
Update語句
? update語句用于修改表中已經(jīng)存在的數(shù)據(jù)
? 單表修改語句結(jié)構(gòu)
? 多表修改語句結(jié)構(gòu)
? update語句的常規(guī)用法
update students set sname='abcd',gender='1' where sid=1;
update students,students2 set students.sname=students2.sname,students.gender=students2.gender where students.sid=students2.sid;
? 單表修改是指修改指定單個表中的已經(jīng)存在數(shù)據(jù)的一個或多個列的數(shù)值；set短語后面跟要修改的列和值；
? where子句表示限定要修改表中的哪些行數(shù)據(jù)，如果沒有where子句則表示所有行都要修改；order by子句表示update數(shù)據(jù)按照指定的順序進行；limit子句表示限定修改數(shù)據(jù)的行數(shù)
? 多表修改是指修改table_references指定的多個表中滿足條件的行數(shù)據(jù)，多表修改不允許使用order by和limit子句
? 執(zhí)行update語句需要修改表的權(quán)限
? low_priority關(guān)鍵詞表示修改語句需要等待其他鏈接的讀此表操作結(jié)束后再執(zhí)行，只作用在MyISAM, MEMORY, and MERGE存儲引擎
? ignore關(guān)鍵詞表示當修改語句碰到違反唯一性約束條件等情況時，語句不會報錯回退而是報警告信息
update students set sname=‘a(chǎn)bc’ limit 2; ##只修改首先找到的兩行記錄
update students set sid=1 where sid=2; ##執(zhí)行失敗，違反唯一性約束
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
update ignore students set sid=1 where sid=2; ##執(zhí)行成功但數(shù)據(jù)不會修改
? 以下語句的col1只會比原值增加1
UPDATE t1 SET col1 = col1 + 1;
? 以下語句的col2和col1的結(jié)果是一樣的
UPDATE t1 SET col1 = col1 + 1, col2 = col1;
? Order by指定update數(shù)據(jù)的順序，在某些情況下可以避免錯誤的發(fā)生，比如t表中的id字段是有唯一約束的，則以下第一個語句執(zhí)行會報錯，而第二個語句執(zhí)行則正常
UPDATE t SET id = id + 1;
UPDATE t SET id = id + 1 ORDER BY id DESC;
mysql> update students2 set sid=sid+1; ##執(zhí)行報錯
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
mysql> update students2 set sid=sid+1 order by sid desc; ##執(zhí)行成功
Query OK, 2 rows affected (0.04 sec)
Rows matched: 2 Changed: 2 Warnings: 0
? 多表修改舉例（表之間通過where條件進行join操作）
UPDATE items,month SET items.price=month.price WHERE items.id=month.id;
delete語句
? delete語句用于刪除表中已經(jīng)存在的整行數(shù)據(jù)
? tbl_name關(guān)鍵詞代表刪除數(shù)據(jù)的目標表
? where 子句代表被刪除數(shù)據(jù)的滿足條件，如果沒有where子句則代表所有表數(shù)據(jù)都刪除
? order by子句代表刪除數(shù)據(jù)的順序
? limit子句代表被刪除數(shù)據(jù)的行數(shù)限制
? delete單表刪除舉例
delete from students;##刪除所有的行
delete from students where sid=1;
delete from students order by sid;
delete from students limit 1; ##只刪除先找到的一行
? 多表刪除語句語法有以下兩種
? 同樣，被刪除的表不能出現(xiàn)在查詢子句的子查詢中
? low_priority關(guān)鍵詞表示刪除語句需要等待其他鏈接的讀此表操作結(jié)束后再執(zhí)行，只作用在MyISAM, MEMORY, and MERGE存儲引擎
? quick關(guān)鍵詞是在使用myisam存儲引擎時，刪除操作不會合并刪除表的索引葉節(jié)點，這樣會在一定程度上加快刪除的速度
? ignore關(guān)鍵詞代表會忽略刪除過程中的一些錯誤
? Delete語句中的order by子句決定了刪除數(shù)據(jù)的順序，配合limit子句后在某些情況下也非常有用，比如刪除最老的一條記錄：
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;
? 多表刪除是指從一個或多個表中刪除滿足條件的數(shù)據(jù)，其中的table_references代表了多個表的join操作，例如以下兩個例子代表從t1和t2表中刪除滿足條件的數(shù)據(jù)
DELETE t1, t2 FROM t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
DELETE FROM t1, t2 USING t1 INNER JOIN t2 INNER JOIN t3 WHERE t1.id=t2.id AND t2.id=t3.id;
? 對多表刪除語句而言，如果想對表使用別名，則只能在table_references子句中使用，否則會報錯
DELETE a1, a2 FROM t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; ##正確
DELETE FROM a1, a2 USING t1 AS a1 INNER JOIN t2 AS a2 WHERE a1.id=a2.id; ##正確
DELETE t1 AS a1, t2 AS a2 FROM t1 INNER JOIN t2 WHERE a1.id=a2.id; ##錯誤
DELETE FROM t1 AS a1, t2 AS a2 USING t1 INNER JOIN t2 WHERE a1.id=a2.id; ##錯誤
select語句
? Select語句是指從現(xiàn)存的一個或多個表中查看滿足條件的數(shù)據(jù)
? Select語句常規(guī)用法：
Select * from students; ##查看表中所有數(shù)據(jù)
Select sid,sname from students; ##查看所有的sid和sname
Select sid,sname from students where sid=1; ##查看符合條件的數(shù)據(jù)
Select * from students order by sid; ##查看排序后的數(shù)據(jù)
Select sex,count(*) from students group by sex having count(*)>=2; ##查看分組的數(shù)據(jù)
Select * from students a inner join students2 b on a.sid=b.sid; ##查看兩個表鏈接后的數(shù)據(jù)
Select sid as a,sname as b from students; ##字段使用別名的方法1
Select sid a,sname b from students; ##字段使用別名的方法2
? select_expr關(guān)鍵詞代表要查詢的字段，至少要有一個select_expr，或者如果是查詢所有的字段，則用*號代替
? table_references關(guān)鍵詞代表查詢數(shù)據(jù)來自的一個或多個表
? Where子句代表只查詢滿足條件的表數(shù)據(jù)，如果沒有where子句則代表查詢表中所有的數(shù)據(jù)
? 查詢t1和t2表中所有的字段
SELECT * FROM t1 INNER JOIN t2 ...
SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ...
? select_expr也可以使用MySQL內(nèi)部的函數(shù)，另外字段也可以使用別名
SELECT CONCAT(last_name,', ',first_name) AS full_name FROM mytable ORDER BY full_name;
SELECT CONCAT(last_name,', ',first_name) full_name FROM mytable ORDER BY full_name;
? where條件中不能使用select_expr中定義的字段別名，因為語句執(zhí)行順序是where在select之前，所以where在執(zhí)行時字段別名未知
Select sid a,sname b from students where a>1; ##執(zhí)行錯誤
ERROR 1054 (42S22): Unknown column 'a' in 'where clause'
Select sid a,sname b from students where sid>1; ##正確的寫法
+------+------+
| a | b |
+------+------+
| 2 | ccc |
+------+------+
? from table_references子句中指定表名，tbl_name也可以指定別名，當涉及的表不在當前的數(shù)據(jù)庫時，需要使用db_name.tbl_name來指定表和所在的數(shù)據(jù)庫名
SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name;
SELECT t1.name, t2.salary FROM employee t1, info t2 WHERE t1.name = t2.name;
? 當多個表中有相同的字段名，且需要查詢出來時，需要在select_expr中使用tbl_name.column_name來顯視指定要查詢哪個表的字段
? 查看另外的數(shù)據(jù)庫的表數(shù)據(jù) Select * from test2.students;
? 兩個數(shù)據(jù)庫里的表關(guān)聯(lián)查詢
Select * from students a inner join test2.students b on a.sid=b.sid;
+-----+------+------+-----+-------+
| sid | sname | sex | sid | sname |
+-----+------+------+-----+-------+
| 1 | aaa | 0 | 1 | abc |
| 2 | ccc | 1 | 2 | bcd |
+-----+------+------+-----+-------+
? 有相同字段名時要指定表名，可以用表的別名(注意執(zhí)行順序)
mysql> select sname from students a inner join test2.students b on a.sid=b.sid;
ERROR 1052 (23000): Column 'sname' in field list is ambiguous
mysql> select a.sname from students a inner join test2.students b on a.sid=b.sid;
+-------+
| sname |
+-------+
| aaa |
| ccc |
+-------+
? Group by子句代表分組，通常和聚合函數(shù)配合使用，如最大值max, 最小值min, 平均值avg, 個數(shù)count，求和sum
insert into scores values(1,'english',88),(1,'chinese',86),(1,'math',90),(2,'english',95),(2,'chinese',84);
select sum(score) from scores;
select sid,count(*),max(score),min(score),avg(score),sum(score) from scores group by sid;
+------+----------+------------+------------+------------+------------+
| sid | count(*) | max(score) | min(score) | avg(score) | sum(score) |
+------+----------+------------+------------+------------+------------+
| 1 | 3 | 90 | 86 | 88.0000 | 264 |
| 2 | 2 | 95 | 84 | 89.5000 | 179 |
+------+----------+------------+------------+------------+------------+
? order by和group by子句可以引用select_expr中的列，通過以下三種方式：
SELECT college, region, seed FROM tournament ORDER BY region, seed;
SELECT college, region AS r, seed AS s FROM tournament ORDER BY r, s;
SELECT college, region, seed FROM tournament ORDER BY 2, 3;
? order by子句表示查詢結(jié)果按照順序排列，默認是升序排列，可以指定DESC表明按照降序排列
? having子句一般是跟在group by子句之后，代表限制分組之后的結(jié)果
SELECT user, MAX(salary) FROM users GROUP BY user HAVING MAX(salary) > 10;
? limit子句用來限制查詢結(jié)果的條數(shù)，其后可以帶兩位>0的整數(shù)，第一位代表 offset，第二位代表取多少行
SELECT * FROM tbl LIMIT 5; # Retrieve first 5 rows，等同于select * from tbl limit 0,5
SELECT * FROM tbl LIMIT 5,10; # Retrieve rows 6-15
select sid,avg(score) from scores group by sid having avg(score)>88
+------+------------+
| sid | avg(score) |
+------+------------+
| 2 | 89.5000 |
+------+------------+
select * from scores order by sid desc,score;
+------+---------+-------+
| sid | class | score |
+------+---------+-------+
| 2 | chinese | 84 |
| 2 | english | 95 |
| 1 | chinese | 86 |
| 1 | english | 88 |
| 1 | math | 90 |
+------+---------+-------+
? Select … into語句代表將查詢結(jié)果寫入文件中或者定義的參數(shù)變量中
? for update關(guān)鍵詞代表將查詢的數(shù)據(jù)行加上寫鎖，直到本事務(wù)提交為止
? lock in share mode關(guān)鍵詞代表將查詢的數(shù)據(jù)行加上讀鎖，則其他的鏈接可以讀相同的數(shù)據(jù)但無法修改加鎖的數(shù)據(jù)
? ALL/Distinct關(guān)鍵詞代表是否將查詢結(jié)果中完全重復(fù)的行都查詢出來，ALL是默認值代表都查詢出來，指定distinct代表重復(fù)行只顯示一次
? HIGH_PRIORITY代表賦予讀操作較高的操作優(yōu)先級
? max_statement_time=N子句代表設(shè)置語句執(zhí)行超時時間（毫秒）
? straight_join關(guān)鍵詞代表強制優(yōu)化器在表連接操作時按照語句中from子句中的表的順序執(zhí)行
? sql_big_result/sql_small_result通常是和group by/distinct一起使用，其作用是事先告訴優(yōu)化器查詢結(jié)果是大還是小，以便優(yōu)化器事先準備好將查詢結(jié)果存放在磁盤臨時表或者快速臨時表中以便后續(xù)操作
? sql_buffer_result強制將查詢結(jié)果存入臨時表中
? sql_calc_found_rows關(guān)鍵詞代表要求查詢結(jié)果的同時計算結(jié)果的行數(shù)，以便后續(xù)通過SELECT FOUND_ROWS()直接獲取行數(shù)
? sql_cache/sql_no_cache代表是否直接從query cache中獲取查詢結(jié)果
mysql> select count(*),count(all sid),count(distinct sid) from scores;
+----------+----------------+---------------------+
| count(*) | count(all sid) | count(distinct sid) |
+----------+----------------+---------------------+
| 5 | 5 | 2 |
+----------+----------------+---------------------+
Select…into語句
? Select … into語句代表將查詢結(jié)果存入定義的變量或者文件
SELECT ... INTO var_list將查詢結(jié)果存入定義的變量
SELECT ... INTO OUTFILE將查詢結(jié)果按照一定的格式寫入到文件中
SELECT ... INTO DUMPFILE將查詢結(jié)果以一行的格式寫入到文件中，且只能寫入一行
? 當使用存入變量方法時，需要保證查詢結(jié)果返回一行，如果不返回數(shù)據(jù)則報no data錯誤，如果返回多行則報Result consisted of more than one row錯誤，當返回行數(shù)不確定時，可以用limit 1強制只返回一行
SELECT id, data INTO @x, @y FROM test.t1 LIMIT 1;
? 使用Select … into outfile ‘file_name’時，文件會創(chuàng)建在本地服務(wù)器上，所以要確保你的用戶能創(chuàng)建文件，而且此file_name不能已經(jīng)存在在服務(wù)器上以免覆蓋其他文件
SELECT sid,sname,sex INTO OUTFILE '/tmp/students.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM students;
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement
My.ini配置文件中添加secure_file_priv=/tmp/后重啟再執(zhí)行，成功
mysql> select * into dumpfile '/tmp/students3.txt' from students;
mysql> select * into dumpfile '/tmp/students4.txt' from students limit 1;
Query OK, 1 row affected (0.00 sec)
Select語句中的表連接
? 當select語句中涉及到多表查詢結(jié)果時，就會用到表連接操作
? 在MySQL中，join/inner join/cross join三者的意思是一樣的
? Join語句中表別名的用法
SELECT t1.name, t2.salary FROM employee AS t1 INNER JOIN info AS t2 ON t1.name = t2.name;
SELECT t1.name, t2.salary FROM employee t1 INNER JOIN info t2 ON t1.name = t2.name;
? from子句后面還可以跟子查詢，但子查詢必須帶別名
SELECT * FROM (SELECT 1, 2, 3) AS t1;
? 當inner join或者表之間用逗號隔開，且沒有表之間的關(guān)聯(lián)字段，則代表結(jié)果是兩者的笛卡爾積
? conditional_expr子句一般代表指定兩個表之間的關(guān)聯(lián)條件，而where條件中指定查詢結(jié)果的篩選條件
? STRAIGHT_JOIN和Join的用法大致相同，唯一不同是確保左表是先被讀取的，以保證優(yōu)化器的讀取順序
SELECT * FROM students, scores; ##沒有關(guān)聯(lián)條件，表示笛卡爾積
SELECT * FROM students INNER JOIN scores ON students.sid=scores.sid;
SELECT * FROM students LEFT JOIN scores ON students.sid=scores.sid;
SELECT * FROM students LEFT JOIN scores USING (sid);
SELECT * FROM students LEFT JOIN students2 ON students.sid=students2.sid LEFT JOIN scores ON students2.sid=scores.sid;
? Union用來將多個select語句的執(zhí)行結(jié)果合并成一個結(jié)果
? 第一個select語句的column_name會被當做最后查詢結(jié)果的列名，接下來的每個select語句所一一對應(yīng)的列應(yīng)該和第一個語句的列的數(shù)據(jù)類型最好保持一致
? 默認情況下union語句會把最終結(jié)果中的重復(fù)行去掉，這和增加distinct這個關(guān)鍵詞的作用一樣，如果使用union all則代表最終結(jié)果中的重復(fù)行保留
mysql> select sid,sname from students union select sid,gender from students2;
+-----+-------+
| sid | sname |
+-----+-------+
| 3 | a |
| 4 | a |
| 5 | a |
| 1 | abc |
| 2 | abc |
| 1 | -2 |
| 2 | -1 |
+-----+-------+
mysql> select sid,sname from students union all select sid,sname from students2;
+------+-------+
| sid | sname |
+------+-------+
| 1 | aaa |
| 2 | ccc |
| 1 | aaa |
| 3 | ccc |
+------+-------+
4 rows in set (0.00 sec)
mysql> select sid,sname from students union select sid,sname from students2;
+------+-------+
| sid | sname |
+------+-------+
| 1 | aaa |
| 2 | ccc |
| 3 | ccc |
+------+-------+
3 rows in set (0.00 sec)
? 如果相對union語句的最后結(jié)果做排序或者limit限制，則需要將每個select語句用括號括起來，把order by或limit語句放在最后
mysql> (Select sid,sname from students) Union (Select sid,sname from students2) order by sid limit 2;
+------+-------+
| sid | sname |
+------+-------+
| 1 | aaa |
| 2 | ccc |
+------+-------+
2 rows in set (0.00 sec)
Create view語句
? Create view語句是指將某個查詢數(shù)據(jù)的定義保留下來，以便隨時調(diào)用
? view本身不存儲查詢結(jié)果，只是一個定義
? Or replace關(guān)鍵詞表示當創(chuàng)建的視圖已經(jīng)存在時，執(zhí)行替換命令
? Select_statement子句則是創(chuàng)建視圖的select語句，可以是從表中查詢數(shù)據(jù)，也可以從其他視圖中查詢數(shù)據(jù)
? *當視圖被創(chuàng)建之后，則其定義就已經(jīng)固定不會再改變，比如一個視圖是由select 創(chuàng)建的，則后續(xù)對表增加的字段不會成為視圖的一部分，而后續(xù)對表刪除字段則會導(dǎo)致查詢視圖失敗
? 創(chuàng)建的視圖默認情況下是屬于當前數(shù)據(jù)庫的，當要創(chuàng)建到另外的數(shù)據(jù)庫時則需要在視圖名前面加上數(shù)據(jù)庫名
CREATE VIEW test.v AS SELECT * FROM test2.t;
CREATE VIEW v_today (today) AS SELECT CURRENT_DATE; ##指定字段名
mysql> create view v_students_male as select sid,sname from students where sex=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_students_male
+------+-------+
| sid | sname |
+------+-------+
| 1 | aaa |
+------+-------+
mysql> create view v_students_female as select sid,sname from students where sex=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_students_female where sid>1;
+------+-------+
| sid | sname |
+------+-------+
| 2 | ccc |
+------+-------+
##創(chuàng)建相同名稱的視圖會報錯
mysql> create view v_students_male as select sid,sname,sex from students where sex=0;
ERROR 1050 (42S01): Table 'v_students_male' already exists
##添加or replace參數(shù)后，原視圖被替換
mysql> create or replace view v_students_male as select sid,sname,sex from students where sex=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from v_students_male;
+------+-------+------+
| sid | sname | sex |
+------+-------+------+
| 1 | aaa | 0 |
+------+-------+------+
mysql> create view v_test as select * from students;
mysql> alter table students add test int; ##表增加一個字段
mysql> select * from v_test; ##查詢視圖依然是之前的三個字段結(jié)果
+------+-------+------+
| sid | sname | sex |
+------+-------+------+
| 1 | aaa | 0 |
| 2 | ccc | 1 |
+------+-------+------+
mysql> alter table students drop column sex; ##表刪除原來的一個字段
mysql> select * from v_test; ##查詢視圖失敗
ERROR 1356 (HY000): View 'test.v_test' references invalid table(s) or column(s) orfunction(s) or
definer/invoker of view lack rights to use them
? Order by子句在創(chuàng)建視圖過程中是允許的，但當后續(xù)的查詢視圖的語句中有自己的order by子句時則會被忽略掉
? 視圖在滿足特定條件時是可以執(zhí)行insert/update/delete語句的，條件就是視圖中的每一行和視圖對應(yīng)的表中的每行數(shù)據(jù)都能一一對應(yīng)起來
mysql> insert into v_students_male values(3,'ddd',0);
Query OK, 1 row affected (0.00 sec)
mysql> update v_students_male set sname='eee' where sid=3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> delete from v_students_male where sid=3;
Query OK, 1 row affected (0.01 sec)
Create index語句
? Create index語句用來在表中創(chuàng)建索引
? Index_col_name可以包含一個字段，也可以包含多個字段（逗號隔開），如果包含多個字段，則表明此索引是復(fù)合索引
? Unique index代表索引中的值不能有重復(fù)
? Fulltext index只能創(chuàng)建在innodb和myisam存儲引擎的char,varchar和text字段上
? Index可以創(chuàng)建在包含NULL值的字段上
? Key_block_size=value是在myisam存儲引擎的表上指定索引鍵的block大小
create index idx_st_sname on students(sname); ##創(chuàng)建普通索引
create index idx_st_union on students(sname,sex); ##創(chuàng)建復(fù)合索引
create unique index idx_st_sid on students(sid); ##創(chuàng)建唯一索引
mysql> insert into students values(1,‘eee’,0); ##插入重復(fù)數(shù)據(jù)失敗
ERROR 1062 (23000): Duplicate entry '1' for key 'idx_st_sid'
? Index_type代表創(chuàng)建索引的類型
? comment 'string'代表可以為索引添加最長1024的注釋
CREATE TABLE t1 (id INT);
CREATE INDEX id_index ON t1 (id) COMMENT 'MERGE_THRESHOLD=40';
Alter database語句
? Alter database語句用來修改數(shù)據(jù)庫的屬性
? db_name可以不指定，如果不指定說明是修改當前數(shù)據(jù)庫的屬性
? character set代表修改數(shù)據(jù)庫的默認字符集
? collate代表修改數(shù)據(jù)庫的默認排序規(guī)則
? 如果修改了數(shù)據(jù)庫的默認字符集或排序規(guī)則，那數(shù)據(jù)庫中的所有存儲過程和函數(shù)都需要重新創(chuàng)建一遍
? alter view語句用來修改視圖的定義，本身的語法結(jié)構(gòu)和createview相同，語句所起到的作用和create or replace view語句相同
mysql> alter view v_students_male as select sid,sname from students where sex=0;
mysql> select * from v_students_male;
+------+-------+
| sid | sname |
+------+-------+
| 1 | aaa |
+------+-------+
Drop database語句
? drop database語句用來刪除數(shù)據(jù)庫操作，既刪除了數(shù)據(jù)庫也刪除了庫里的所有表
? 語法為DROP {DATABASE | SCHEMA} [IF EXISTS] db_name
? 刪除數(shù)據(jù)庫的操作將刪除該數(shù)據(jù)庫所在文件夾下如下格式的文件以及 db.opt文件
mysql> drop database test2;
Query OK, 1 row affected (0.07 sec)
mysql> drop database if exists test4;
Query OK, 0 rows affected (0.00 sec)
Drop index語句
? Drop index語句用來刪除索引操作
? 刪除tbl_name表中的名字叫index_name的索引
mysql> drop index idx_st_sname on students;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
Drop table語句
? Drop table語句用來刪除一個或多個表操作，當然也可以刪除臨時表
? Restrict/cascade兩個關(guān)鍵詞在5.7版本中沒用
mysql> drop table students2;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table if exists students2;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Drop view語句
? Drop view語句用來刪除一個或多個視圖
? 同樣，restrict/cascade兩個關(guān)鍵詞也是無效
mysql> drop view v_students_male;
Query OK, 0 rows affected (0.00 sec)
mysql> drop view if exists v_students_male;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rename table語句
? Rename table語句用來重命名一個或多個表名
RENAME TABLE old_table TO new_table;? 當想讓兩個表名相互調(diào)換時，可以執(zhí)行語句
RENAME TABLE old_table TO tmp_table,
new_table TO old_table,
tmp_table TO new_table;
? Rename table能將表中的數(shù)據(jù)，索引，主鍵定義都自動轉(zhuǎn)換到新表下，但視圖和對原表分配的權(quán)限不能自動轉(zhuǎn)換到新表，需要手動執(zhí)行
mysql> rename table students to students_test;
Query OK, 0 rows affected (0.03 sec)
mysql> show create table students_test;
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------+
| Table | Create Table
|
+---------------+-------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------+
| students_test | CREATE TABLE `students_test` (
`sid` int(11) DEFAULT NULL,
`sname` varchar(20) DEFAULT NULL,
`sex` int(11) DEFAULT NULL,
UNIQUE KEY `idx_st_sid` (`sid`),
KEY `idx_st_union` (`sname`,`sex`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
mysql> select * from v_students_female; ##原有視圖查詢失敗
ERROR 1356 (HY000): View 'test.v_students_female' references invalid table(s)
or column(s)
or function(s)
or definer/invoker
of view lack rights to use them
truncate table語句
? Truncate table語句用來刪除/截斷表里的所有數(shù)據(jù)
? 和delete刪除所有表數(shù)據(jù)在邏輯上含義相同，但性能更快
? 類似執(zhí)行了drop table和create table兩個語句
mysql> truncate table students_test;
Query OK, 0 rows affected (0.01 sec)
mysql> select * from students_test; ##查詢結(jié)果為空
Empty set (0.00 sec)
alter table語句
Alter table … add [column_name]
Alter table … add constraint [name] unique [index/key] [name]
Alter table … add constraint [name] foreign key (column_name) references table_name(column_name)
Alter table … drop column [column_name]
Alter table … drop [index/key] [index_name]
