今天主要聊一聊有关表的一些操作。创建表，删除表，修改表，在表中插入数据，修改表中的数据，以及多表连接等。
表：由行和列组成
表空间：比表大，比数据库小的物理单位
创建表：
CREAT TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
[table_options]
[partition_options]
每个表必须属于一个数据库
如果在创建表时，如果没有指定数据库，在创建时就必须指定数据库。
mysql> CREAT TABLE user1(
-> ID INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> Uname CHAR(20) NOT NULL,
-> Uage INT(2) NOT NULL,
-> U*** ENUM('F','M') DEFAULT 'M',
-> UNIQUE(ID));
设定表将使用的引擎
ENGINE=engin_name在最后一行的括号外添加。例如：
mysql> CREAT TABLE user2(
-> ID INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
-> Uname CHAR(20) NOT NULL,
-> Uage INT(2) NOT NULL,
-> U*** ENUM('F','M') DEFAULT 'M',
-> UNIQUE(ID))
-> ENGINE = InnoDB;
查看当前数据库中表的信息，包含所修改的存储引擎
SHOW TABLE STATUS\G;
SHOW TABLE STATUS like 'user2'\G; COLLATE:为表指定专门的排序规则
AUTO_INCREMENT:自动加1，设置从1开始 -------------------------
CREAT TABLE [IF NOT EXISTS] tbl_name (create_definition,...)
[table_options]
[partition_options]
select_statement
select_statemnet 表示从一个表中检索的数据创建一个表 例如：
mysql> create table user3 select Uname from user; 我这里的user表是在写这篇博客前已经创建过
了，所以直接拿来用了，在后面的过程中会有
user表的创建的，这里你可以换成别的表。
mysql> desc user3;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| Uname | char(20) | NO | | NULL | |
+-------+----------+------+-----+---------+-------+
1 row in set (0.00 sec)
以user表为准创建表，只负责创建表结构，不会复制表中的数据。
create table user4 like user; 例如：
mysql> desc user4；
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| ID | int(10) | NO | PRI | NULL | auto_increment |
| Uname | char(20) | NO | | NULL | |
| Uage | int(2) | NO | | NULL | |
| U*** | char(2) | NO | | M | |
| Tutor | varchar(20) | YES | | NULL | |
| CourseID | int(10) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)
在哪些字段创建索引：
PRIMARY KEY，INDEX，CONSTRAINT，FULLTEXT
索引类型：
BTREE索引
HASH索引
使用using指定索引类型 创建索引：
例如：CREATE TABLE IF NOT EXSITS users(
-> ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(30) NOT NULL,
-> Age TINYINT UNSIGNED NOT NULL，
-> *** ENUM('F','M') NOT NULL DEFAULT 'M',
-> Score FLOAT );
CREATE TABLE IF NOT EXSITS users2(
-> ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(30) NOT NULL,
-> Age TINYINT UNSIGNED NOT NULL)
-> *** ENUM('F','M') NOT NULL DEFAULT 'M',
-> Score FLOAT,
-> INDEX index_score(Score));
SHOW INDEXES FROM users2;显示users2表的索引 例：加一个约束，name上要求必须唯一
CREATE TABLE IF NOT EXSITS users3(
-> ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Name VARCHAR(30) NOT NULL,
-> Age TINYINT UNSIGNED NOT NULL)
-> *** ENUM('F','M') NOT NULL DEFAULT 'M',
-> Score FLOAT,
-> INDEX index_score(Score),
-> UNIQUE(Name));
-------------------------------------------
设置默认存储引擎 1.在启动mysqld 时
--default-storage-engin = MYISAM
#vim /etc/my.cnf
在[mysqld]范围内添加：--default-storage-engin = MYISAM
2.SET SESSION storage_engin = InnoDB
SET GLOABLE storage_engin = InnoDB ---------------------------------------------
查询：
SELECT 字段 from 表名；
SELECT * from 表名；
where 条件 条件包括： >,<,=,>=,<=,<>,!=,LIKE,BETWEEN .. AND ..，IN在特定集合中，IS NULL,IS NOT NULL,REGEXP(正则表达式)（RLIKE）
匹配多个条件:AND(&&),OR(||),NOT(!)
LIKE :
% 任意长度任意字符
_ 任意一个
SELECT * FROM user WHERE ID IN(1,2,4);
SELECT * FROM user WHERE ID+1 > 3;
排序：ORDER by 条件 ASC|DESC (默认是升序) SELECT * FROM user ORDER BY ID; 升序
SELECT * FROM user ORDER BY ID DESC; 降序
LIMIT n:表示显示前n行 SELECT * FROM user LIMIT 2;
SELECT * FROM user LIMIT 2，1;从第二行开始显示一行。 消除重复值：DISTINCT(以下仅是例子，后面会提供相应的练习)
>SELECT DISTINCT zone FROM zones LIMIT 100;
>CREATE TABLE test SELECT * FROM zones LIMIT 100;
>SELECT * FROM test;
>SELECT DISTINCT FROM test;
GROUP BY 把相同的分为一组,HAVING 对结果做过滤 >SELECT * FROM mytb;
>SELECT zone,COUNT(*) FROM mytb;
>SELECT zone,COUNT(*) FROM mytb GROUP BY zone;
>SELECT zone,COUNT(*) FROM mytb GROUP BY zone HAVING COUNT（*）>= 10;
别名：AS SELECT zone AS domain FROM mytb;
设置变量； >SET @num=10
>SELECT zone,COUNT(*) FROM mytb GROUP BY zone HAVING COUNT（*）>= @num;
------------------------------------------------------------------
导入数据； create database mydb;
use mydb;
show tables;
mysql > LOAD DATA INFILE '数据文件路径' INTO table 表名 mysql练习1：
1.创建表结构：
mysql> CREATE TABLE www (
-> ID BIGINT(20) NOT NULL DEFAULT '0',
-> UserID BIGINT(20) NOT NULL,
-> ZoneID BIGINT(20) NOT NULL,
-> Zone VARCHAR(255) NOT NULL,
-> Host VARCHAR(255) NOT NULL,
-> Type ENUM('A','SOA','NS','MX','CNAME','PTR','TXT','AAAA') NOT NULL,
-> Mx_priority INT(10),
-> Data VARCHAR(255) NOT NULL,
-> TTL INT(10) NOT NULL DEFAULT '600',
-> View VARCHAR(20) NOT NULL DEFAULT 'DEF',
-> Active ENUM('Y','N') NOT NULL DEFAULT 'Y',
-> DomainLevel INT(11) NOT NULL DEFAULT '1',
-> Standby VARCHAR(255) NOT NULL,
-> CheckHostID BIGINT(20) NOT NULL DEFAULT '0',
-> IsFensheng TINYINT(1) NOT NULL DEFAULT '0');
2、将table.sql(我提供的附件中)中的数据导入至此表，将table.sql放到/tmp下。使用如下命令
mysql> LOAD DATA INFILE '/tmp/table.sql' INTO TABLE mytb;练习2：
1、显示www表中Host为www的所有行；select * from www where Host='www';
2、显示www表中Zone、Host、Type和Data字段；mysql> select Zone,Host,Type,Data from www;
3、将第2题中的Data字段显示时的Data改为显示Value；mysql> select Zone,Host,Type,Data AS 'Value' from www LIMIT 100;
4、显示www表中Host为ftp的所有行的Zone、Host、Type和Data字段；mysql> select Zone,Host,Type,Data from www where Host='ftp';
5、显示www表中Zone为以.org结尾或以.net结尾的所有行；mysql> select * from www where Zone LIKE '%.org' or Zone LIKE '%.net' ;
6、显示www表中Host字段的值为mail,@,*或oa的所有行；mysql> select * from www where Host IN ('mail','@','*','oa');
7、以Host字段为准对www表中的数据进行分组，并对分组后的结果进行行数统计；而后显示统计结果中行数大于10的组；mysql> select Host,count(*) from www GROUP BY Host HAVING count(*) > 10;
8、显示www表中Mx_priority不为空的行，并对其结果以zone字段为准进行降序排序；mysql> select * from www where Mx_priority IS NOT NULL ORDER BY Zone DESC;
9、将第1题中的结果限定为只显示第50行后100行；mysql> select * from www where Host='www' LIMIT 50,100;
10、显示www表中存在的所有域名；mysql> select DISTINCT Zone from www;
11、显示www表中存在的所有记录类型；mysql> select DISTINCT Type from www;
+-------+
| Type |
+-------+
| A |
| CNAME |
| MX |
| TXT |
+-------+
--------------------------------------------------
修改表结构：
ALTER TABLE tbl_name 操作
操作：
添加：ADD （默认添加字段）
ADD col_name col_def 默认添加在最后
AFTER col_name 在某一个字段后面添加
FIRST 添加到第一个
mysql> CREAT TABLE user(
-> UID INT(10) UNSIGNED AUTO_INCREMENT NOT NULL UNIQUE,
-> Uname CHAR(20) NOT NULL,
-> Uage INT(2) NOT NULL,
-> U*** CHAR(2),
-> CourseID TINYINT);
mysql> DESC user;
添加：
ALTER TABLE user ADD Master TINYINT UNSIGNED ;
修改：CHANGE (不仅可以改字段，还可以改名字),MODIFY(只改字段定义，不改字段名字)
CHANGE old_name new_name col_defination
MODIFY
mysql> ALTER TABLE user MODIFY U*** ENUM('F','M') NOT NULL DEFAULT 'M';
mysql> ALTER TABLE user CHANGE Master Tutor TINYINT UNSIGNED AFTER Uage;
添加索引
ADD {INDEX|KEY} [index_name] [index_type](col1[,...])
mysql> ALTER TABLE user ADD INDEX (Uage);不指定索引类型，默认则为BTREE索引类型。
删除：
删除字段：
DROP col_name
删除键：
DROP PRIMARY KEY
DROP {INDEX|KEY} index_name
为表重命名：
ALTER TABLE user RENAME TO users;
RENAME TABLE users TO user;
SHOW TABLES;
---------------
删除表：
DROP TABLE [IF EXISTS] table_name
往表中插入数据：
(1)INSERT INTO table_name (col1,col2,..) VALUES (value1,value2,...)
mysql> INSTERT INTO user (Uname) VALUES ('Yang Guo');
(2)INSERT INTO table_name SET col1=value1,col2=value2....
如果你把第二行删除，那么重新插入数据时，ID会从3开始，此时，可以修改LAST_INSERT_ID的值 LAST_INSERT_ID:记录上一次插入行的ID号，
ALTER TABLE user AUTO_INCREMENT=2; (3)如果不写字段名，那就得从头到尾为每个字段赋值
INSSERT INTO table_name VALUES (val1,val2,val3,val4)
(4)一个字段中插入多个值:
INSERT INTO user (uname) VALUES ('Qiao Feng'),('Duan Yu'),('Xu Zhu'),('Hong xigong'),('lingxiaosu'),('xiaoling');
修改表中数据：
UPDATE table_name SET col1=val1[,...] [WHERE 条件];
UPDATE user SET Uage=23;如果不指定条件，则所有的行的Uage都会被修改。
UPDATE user SET Uage=23 LIMIT=2；只设置前两行
删除表中的数据：
DELETE FROM table_name WHERE 条件
DELETE FROM user WHERE UID=5
清空一个表，并重置为初始状态，TRUNCATE TABLE tbl_name;
----------------------------------
多表查询：连接：join
首先，创建两个表。user表之前已经创建过了，只需要再稍作修改即可。再创建一个juexue表。
mysql> ALTER TABLE user ADD Tutor VARCHAR(20);
mysql> ALTER TABLE user ADD CourseID INT(10);
mysql> UPDATE user SET CourseID=2 WHERE ID=1;
mysql> UPDATE user SET CourseID=4 WHERE ID=2;
mysql> UPDATE user SET CourseID=9 WHERE ID=3;
mysql> UPDATE user SET CourseID=10 WHERE ID=4;
Uage属性可以自己随意设置，方法和上面的语句类似。我这里就不演示了。
mysql> SELECT * FROM user;
+----+-------------+------+------+-------+----------+
| ID | Uname | Uage | U*** | Tutor | CourseID |
+----+-------------+------+------+-------+----------+
| 1 | Yang Guo | 25 | M | NULL | 2 |
| 2 | xiao feng | 35 | M | NULL | 4 |
| 3 | Duan Yu | 20 | M | NULL | 9 |
| 4 | Xu Zhu | 22 | M | NULL | 10 |
| 5 | Hong xigong | 70 | M | NULL | NULL |
| 6 | lingxiaosu | 0 | M | NULL | NULL |
| 7 | xiaoling | 0 | M | NULL | NULL |
+----+-------------+------+------+-------+----------+
mysql> CREATE TABLE juexue (
-> CID TINYINT(10) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> Cname VARCHAR(50) NOT NULL,
-> Author TINYINT(4));
mysql> INSERT INTO juexue (Cname) Values ('qiankuangdanoyi'),('anranxiaohunzhang'),('tianweifeixian'),('xianglongshibazhang'),('hamagong'),('dagoubangfa'),('tiebushan'),('jinzhongzhao'),('liumaishenjian'),('beimingshengong');
mysql> UPDATE juexue SET Author=10 WHERE CID=1;
mysql> UPDATE juexue SET Author=1 WHERE CID=2;
mysql> UPDATE juexue SET Author=12 WHERE CID=3;
mysql> UPDATE juexue SET Author=2 WHERE CID=4;
mysql> UPDATE juexue SET Author=1 WHERE CID=5;
mysql> UPDATE juexue SET Author=14 WHERE CID=7;
mysql> UPDATE juexue SET Author=15 WHERE CID=8;
mysql> UPDATE juexue SET Author=3 WHERE CID=9;
mysql> UPDATE juexue SET Author=4 WHERE CID=10;
mysql> select * from juexue;
+-----+---------------------+--------+
| CID | Cname | Author |
+-----+---------------------+--------+
| 1 | qiankuangdanoyi | 10 |
| 2 | anranxiaohunzhang | 1 |
| 3 | tianweifeixian | 12 |
| 4 | xianglongshibazhang | 2 |
| 5 | hamagong | 1 |
| 6 | dagoubangfa | NULL |
| 7 | tiebushan | 14 |
| 8 | jinzhongzhao | 15 |
| 9 | liumaishenjian | 3 |
| 10 | beimingshengong | 4 |
+-----+---------------------+--------+
10 rows in set (0.00 sec)
多表连接的类型：
交叉连接（笛卡尔乘积）第一个表的第一行和第二个表的每一行连接，依此类推。因此会产生一个非常庞大的表。
内连接：对称连接，通常是等值条件
外连接 左外连接
右外连接
全外连接
自连接 UNION（1） 交叉连接：
SELECT * FROM user,juexue
（2）内连接：（等值连接）当a表中的条件与b表中的条件匹配时，才会显示
mysql> SELECT * FROM user,juexue WHERE user.CourseID = juexue.CID;
+----+-----------+------+------+-------+----------+-----+---------------------+--------+
| ID | Uname | Uage | U*** | Tutor | CourseID | CID | Cname | Author |
+----+-----------+------+------+-------+----------+-----+---------------------+--------+
| 1 | Yang Guo | 25 | 2 | NULL | 2 | 2 | anranxiaohunzhang | 1 |
| 2 | xiao feng | 35 | 2 | NULL | 4 | 4 | xianglongshibazhang | 2 |
| 3 | Duan Yu | 20 | 2 | NULL | 9 | 9 | liumaishenjian | 3 |
| 4 | Xu Zhu | 22 | 2 | NULL | 10 | 10 | beimingshengong | 4 |
+----+-----------+------+------+-------+----------+-----+---------------------+--------+
4 rows in set (0.00 sec)
mysql> SELECT user.Uname,juexue.Cname FROM user,juexue WHERE user.CourseID = juexue.CID;
+-----------+---------------------+
| Uname | Cname |
+-----------+---------------------+
| Yang Guo | anranxiaohunzhang |
| xiao feng | xianglongshibazhang |
| Duan Yu | liumaishenjian |
| Xu Zhu | beimingshengong |
+-----------+---------------------+
4 rows in set (0.00 sec)
mysql> SELECT Cname,Uname AS Author FROM juexue,user WHERE juexue.Author = user.ID;
+---------------------+-----------+
| Cname | Author |
+---------------------+-----------+
| anranxiaohunzhang | Yang Guo |
| xianglongshibazhang | xiao feng |
| hamagong | Yang Guo |
| liumaishenjian | Duan Yu |
| beimingshengong | Xu Zhu |
+---------------------+-----------+
5 rows in set (0.00 sec)
(3)外连接（非对称）
1）左外连接：左表中的用户选了某门课，但该门课在右表中不存在，则显示为空
mysql> SELECT user.Uname,juexue.Cname From user LEFT JOIN juexue ON user.CourseID = juexue.CID;
+-------------+---------------------+
| Uname | Cname |
+-------------+---------------------+
| Yang Guo | anranxiaohunzhang |
| xiao feng | xianglongshibazhang |
| Duan Yu | liumaishenjian |
| Xu Zhu | beimingshengong |
| Hong xigong | NULL |
| lingxiaosu | NULL |
| xiaoling | NULL |
+-------------+---------------------+
7 rows in set (0.24 sec)
2）右外连接
mysql> SELECT j.Cname,u.Uname FROM user AS u RIGHT JOIN juexue AS j ON j.Author = u.ID;
+---------------------+-----------+
| Cname | Uname |
+---------------------+-----------+
| qiankuangdanoyi | NULL |
| anranxiaohunzhang | Yang Guo |
| tianweifeixian | NULL |
| xianglongshibazhang | xiao feng |
| hamagong | Yang Guo |
| dagoubangfa | NULL |
| tiebushan | NULL |
| jinzhongzhao | NULL |
| liumaishenjian | Duan Yu |
| beimingshengong | Xu Zhu |
+---------------------+-----------+
10 rows in set (0.00 sec)
（4）自连接 ，简言之就是自己连接自己。
mysql> UPDATE user SET Tutor=5 WHERE UID=1;
mysql> SELECT u1.Uname,u2.Uname from user As u1,user AS u2 WHERE u1.Tutor = u2.ID;
+----------+-------------+
| Uname | Uname |
+----------+-------------+
| Yang Guo | Hong xigong |
+----------+-------------+
1 row in set (0.02 sec)
(5) UNION,将两张表的查询结果合成一张
mysql> SELECT ID AS ID,Uname AS NAME FROM user UNION SELECT CID AS ID,CNAME AS NAME FROM juexue;
+----+---------------------+
| ID | NAME |
+----+---------------------+
| 1 | Yang Guo |
| 2 | xiao feng |
| 3 | Duan Yu |
| 4 | Xu Zhu |
| 5 | Hong xigong |
| 6 | lingxiaosu |
| 7 | xiaoling |
| 1 | qiankuangdanoyi |
| 2 | anranxiaohunzhang |
| 3 | tianweifeixian |
| 4 | xianglongshibazhang |
| 5 | hamagong |
| 6 | dagoubangfa |
| 7 | tiebushan |
| 8 | jinzhongzhao |
| 9 | liumaishenjian |
| 10 | beimingshengong |
+----+---------------------+
17 rows in set (0.02 sec)
