数据表、DML和DDL操作
数据表操作
完整性约束：
CREATE TABLE [IF NOT EXISTS] tbl_name(
字段名称 字段类型 [UNSIGNED|ZERIFUKK] [NOT NULL] [DEFAULT 默认值] [[PRIMARY] KEY] | UNIQUE [KEY]] [AUTO_INCREMENT])ENGINE=INNODB CHARSET=UTF8 AUTO_INCREMENT=100
AUTO_INCREMENT
（1）一个表中只能有一个自增长字段，且必须配合主键使用，且只对整数列、整数串有意义，对字符串没意义
（2）加入AUTO_INCREMENT属性
mysql> CREATE TABLE IF NOT EXISTS user5(
-> id SMALLINT KEY AUTO_INCREMENT,
-> username VARCHAR(20)
-> );
Query OK, 0 rows affected (0.34 sec)
mysql> DESC user5;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| id | smallint(6) | NO | PRI | NULL | auto_increment |
| username | varchar(20) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
2 rows in set (0.11 sec)
（3）若从输入新制定的id，则从新指定的id开始自增
mysql> INSERT user5 VALUES(111,'KING1');
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM user5;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | queen |
| 111 | KING1 |
+-----+----------+
3 rows in set (0.00 sec)
mysql> INSERT user5(username) VALUES('queen1');
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM user5;
+-----+----------+
| id | username |
+-----+----------+
| 1 | KING |
| 2 | queen |
| 111 | KING1 |
| 112 | queen1 |
+-----+----------+
4 rows in set (0.00 sec)
（4）指定自增从哪一位开始
mysql> CREATE TABLE IF NOT EXISTS user6(
-> id SMALLINT KEY AUTO_INCREMENT,
-> username VARCHAR(20)
-> )AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.30 sec)
mysql> SELECT * FROM user6;
+-----+----------+
| id | username |
+-----+----------+
| 100 | queen1 |
+-----+----------+
1 row in set (0.00 sec)
NOT NULL非空
（1）不能输入空值，也不能不输入值
mysql> CREATE TABLE IF NOT EXISTS user7(
-> id INT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password CHAR(32) NOT NULL,
-> age TINYINT UNSIGNED
-> );
Query OK, 0 rows affected (0.32 sec)
mysql> DESC user7;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
4 rows in set (0.03 sec)
（2）若有NOT NULL限制，输入NULL或不输入会报错
mysql> INSERT user7(username,password) VALUES(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null
DEFAULT 设置默认值
（1）常和NOT_NULL配合使用
mysql> INSERT user7(username,password) VALUES(NULL,NULL);
ERROR 1048 (23000): Column 'username' cannot be null
mysql> CREATE TABLE IF NOT EXISTS user8(
-> id INT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL,
-> password CHAR(32) NOT NULL,
-> age TINYINT UNSIGNED DEFAULT 18,
-> addr VARCHAR(50) NOT NULL DEFAULT '北京',
-> sex ENUM('男','女','保密') NOT NULL DEFAULT '男'
-> );
Query OK, 0 rows affected (0.36 sec)
mysql> DESC user8;
+----------+----------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| password | char(32) | NO | | NULL | |
| age | tinyint(3) unsigned | YES | | 18 | |
| addr | varchar(50) | NO | | 北京 | |
| sex | enum('男','女','保密') | NO | | 男 | |
+----------+----------------------------+------+-----+---------+----------------+
6 rows in set (0.07 sec)
mysql> INSERT user8(username,password) VALUES('KING','KING');
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM user8;
+----+----------+----------+------+--------+-----+
| id | username | password | age | addr | sex |
+----+----------+----------+------+--------+-----+
| 1 | KING | KING | 18 | 北京 | 男 |
+----+----------+----------+------+--------+-----+
1 row in set (0.00 sec)
唯一性约束 UNIQUE KEY （KEY可省略）
（主键PRIMARY KEY中PRIMARY可省略）
（1）一个表中只能有一个主键，但能有多个唯一，NULL之不算重复
mysql> CREATE TABLE IF NOT EXISTS user9(
-> id TINYINT UNSIGNED KEY AUTO_INCREMENT,
-> username VARCHAR(20) NOT NULL UNIQUE,
-> card CHAR(18) UNIQUE
-> );
Query OK, 0 rows affected (0.41 sec)
mysql> DESC user9;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| card | char(18) | YES | UNI | NULL | |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.05 sec)
mysql> SHOW CREATE TABLE user9;
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user9 | CREATE TABLE `user9` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`username`),
UNIQUE KEY `card` (`card`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
mysql> INSERT user9(username) VALUES('A');
Query OK, 1 row affected (0.06 sec)
mysql> INSERT user9(username) VALUES('A1');
Query OK, 1 row affected (0.04 sec)
mysql> SELECT * FROM user9;
+----+----------+------+
| id | username | card |
+----+----------+------+
| 1 | A | NULL |
| 2 | A1 | NULL |
+----+----------+------+
修改表结构的方式
修改表名：
（1）ALTER TABLE tbl_name RENAME [TO|AS] new_name
mysql> ALTER TABLE user10 RENAME TO user11;
Query OK, 0 rows affected (0.20 sec)
（2）RENAME TABLE tbl_name TO n
mysql> RENAME TABLE user11 TO user10;
Query OK, 0 rows affected (0.17 sec)
添加字段
（1）ALTER TABLE tbl_name ADD 字段名称 字段类型 [完整性约束条件] [FIRST|ALTER 字段名称]
默认添加在末尾
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.01 sec)
mysql> ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNQIUE;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'UNQIUE' at line 1
mysql> ALTER TABLE user10 ADD test1 VARCHAR(100) NOT NULL UNIQUE;
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
| test1 | varchar(100) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
（2）直接使用ADD
mysql> ALTER TABLE user10
-> ADD test4 INT NOT NULL DEFAULT 123 AFTER password,
-> ADD test5 FLOAT(6,2) FIRST,
-> ADD test6 SET('A','B','C');
Query OK, 0 rows affected (0.60 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test5 | float(6,2) | YES | | NULL | |
| test2 | varchar(20) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test3 | int(11) | NO | | 100 | |
| password | char(32) | NO | | NULL | |
| test4 | int(11) | NO | | 123 | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
| test1 | varchar(100) | NO | UNI | NULL | |
| test6 | set('A','B','C') | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
17 rows in set (0.01 sec)
删除字段
（1）ALTER TABLE tbl_name DROP字段名称
mysql> ALTER TABLE user10 DROP test6;
Query OK, 0 rows affected (0.72 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test5 | float(6,2) | YES | | NULL | |
| test2 | varchar(20) | NO | | NULL | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| test3 | int(11) | NO | | 100 | |
| password | char(32) | NO | | NULL | |
| test4 | int(11) | NO | | 123 | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
| test1 | varchar(100) | NO | UNI | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
16 rows in set (0.01 sec)
（2）直接用DROP
mysql> ALTER TABLE user10
->
-> DROP test2,
->
-> DROP test3,
->
-> DROP test4,
->
-> DROP test5;
Query OK, 0 rows affected (0.65 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| addr | varchar(200) | NO | | 北京 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
修改字段
（1）ALTER TABEL tbl_name MODIFY 字段名称 字段类型 [完整性约束条件] [FIRST|AFTER字段名称]
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test | int(10) unsigned | NO | | 10 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.00 sec)
mysql> ALTER TABLE user10 MODIFY email VARCHAR(200);
Query OK, 0 rows affected (0.75 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+-------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+-------------+----------------+
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(200) | YES | | NULL | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| test | int(10) unsigned | NO | | 10 | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
| card | char(18) | YES | | NULL | |
+----------+----------------------------+------+-----+-------------+----------------+
11 rows in set (0.04 sec)
（2）修改字段名称：ALTER TABLE tbl_name CHANGE 旧字段名称 新字段名称 字段类型 [完整性约束条件] [FIRST|ALTER字段名称]
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test | char(32) | NO | | 123 | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| card | char(18) | YES | | NULL | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.01 sec)
mysql> ALTER TABLE user10 CHANGE test test1 CHAR(32) NOT NULL DEFAULT '123';
Query OK, 0 rows affected (0.11 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user10;
+----------+----------------------------+------+-----+------------------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------------------------+------+-----+------------------+----------------+
| test1 | char(32) | NO | | 123 | |
| id | smallint(5) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| password | char(32) | NO | | NULL | |
| email | varchar(50) | NO | | 382771946@qq.com | |
| age | tinyint(3) unsigned | YES | | 18 | |
| sex | enum('男','女','保密') | YES | | 保密 | |
| card | char(18) | YES | | NULL | |
| salary | float(6,2) | YES | | NULL | |
| regTime | int(10) unsigned | YES | | NULL | |
| face | char(100) | NO | | default.jpg | |
+----------+----------------------------+------+-----+------------------+----------------+
11 rows in set (0.01 sec)
添加默认值：
（1）ALTER TABLE tbl_name ALTER字段名称 SET DEFAULT默认值
删除默认值：
（1）ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT
mysql> DESC user11;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.04 sec)
mysql> ALTER TABLE user11 ALTER age SET DEFAULT 18;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user11;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | UNI | NULL | |
| age | tinyint(3) unsigned | YES | | 18 | |
+----------+---------------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
添加主键
ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] PRIMARY KEY[index_type] (字段名称, …)
（1）添加单主键
mysql> DESC test12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> ALTER TABLE test12 ADD PRIMARY KEY(id);
Query OK, 0 rows affected (0.46 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC test12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.01 sec)
（2）添加复合主键
mysql> ALTER TABLE test13 ADD PRIMARY KEY(id,card);
Query OK, 0 rows affected (0.55 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC test13;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | 0 | |
| card | char(18) | NO | PRI | | |
| username | varchar(20) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
删除主键
（1）ALTER TABLE tbl_name DROP PRIMARY KEY
mysql> ALTER TABLE test12 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.70 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC test12;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id | int(11) | NO | | 0 | |
+-------+---------+------+-----+---------+-------+
1 row in set (0.02 sec)
（2）删除主键时如果主键时自增长的，则需要先去除自增长属性，再删除主键
mysql> DESC test14;
+-------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
+-------+------------------+------+-----+---------+----------------+
1 row in set (0.03 sec)
mysql> ALTER TABLE test14 DROP PRIMARY KEY ;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> ALTER TABLE test14 MODIFY id INT UNSIGNED;
Query OK, 0 rows affected (0.62 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC test14;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | PRI | 0 | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> ALTER TABLE test14 DROP PRIMARY KEY;
Query OK, 0 rows affected (0.64 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC test14;
+-------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | NO | | 0 | |
+-------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
添加唯一
ALTER TABLE tbl_name ADD [constraint [symbol]] PRIMARY KEY [index_type] [索引名称] (字段名称, …)
删除唯一
ALTER TABLE tbl_name DROP {index_name}
mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| card | char(18) | NO | UNI | NULL | |
| test | varchar(20) | NO | MUL | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
mysql> ALTER TABLE user12 DROP KEY uni_card;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql>
mysql> ALTER TABLE user12 DROP KEY mulUni_test_test1;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC user12;
+----------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+----------------+
| id | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| username | varchar(20) | NO | | NULL | |
| card | char(18) | NO | | NULL | |
| test | varchar(20) | NO | | NULL | |
| test1 | char(32) | NO | | NULL | |
+----------+---------------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)
修改表的存储引擎
ALTER TABLE tbl_name ENGINE=存储引擎名称
mysql> ALTER TABLE user12 ENGINE=MyISAM;
Query OK, 0 rows affected (0.32 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user12;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user12 | CREATE TABLE `user12` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) NOT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE user12 ENGINE=INNODB;
Query OK, 0 rows affected (0.30 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE user12;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user12 | CREATE TABLE `user12` (
`id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`card` char(18) NOT NULL,
`test` varchar(20) NOT NULL,
`test1` char(32) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
设置自增长的值
ALTER TABLE tbl_name AUTO_INCREMENT=值
mysql> ALTER TABLE user12 AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.08 sec)
删除数据表
DROP TABLE [IF EXISTS] tbl_name[, tbl_name…]
（需删除已经存在的表）
mysql> DROP TABLE user12;
Query OK, 0 rows affected (0.09 sec)
mysql> DROP TABLE user12;
ERROR 1051 (42S02): Unknown table 'maizi.user12'
DML
插入数据
不指定具体的字段名
【1】INSERT [INTO] tbl_name VALUES|VALUE(值)
mysql> INSERT INTO user VALUES(1,'KING','KING','KING@QQ.COM',20);
Query OK, 1 row affected (0.07 sec)
mysql> SELECT * FROM user;
+----+----------+----------+-------------+------+
| id | username | password | email | age |
+----+----------+----------+-------------+------+
| 1 | KING | KING | KING@QQ.COM | 20 |
+----+----------+----------+-------------+------+
1 row in set (0.00 sec)
【2】列出指定字段：INSERT[INTO] tbl_name(字段名称1,…) VALUES|VALUE(值1,…)
值和字段顺序一一对应
mysql> INSERT user(username,password) VALUES('A','AAA');
Query OK, 1 row affected (0.05 sec)
mysql>
mysql> INSERT user(password,username) VALUES('BBB','B');
Query OK, 1 row affected (0.03 sec)
mysql> SELECT * FROM user;
+----+----------+----------+------------------+------+
| id | username | password | email | age |
+----+----------+----------+------------------+------+
| 1 | KING | KING | KING@QQ.COM | 20 |
| 2 | QUEEN | QUEEN | QUEEN@QQ.COM | 30 |
| 3 | A | AAA | 382771946@qq.com | 18 |
| 4 | B | BBB | 382771946@qq.com | 18 |
+----+----------+----------+------------------+------+
4 rows in set (0.00 sec)
通过SET形式插入记录
INSERT [INTO] tbl_name SET 字段名称=值,…
mysql> INSERT user SET username='maizi',password='maizixueyuan' ;
Query OK, 1 row affected (0.08 sec)
将查询结果插入到表中
INSERT [INTO] tbl_name[(字段名称,…)] SELECT 字段名称 FROM tbl_name [WHERE 条件]
更新数据：
UPDATE tbl_name 字段名称=值,…[WHERE条件][ORDER BY字段名称][LIMIT限制条数]
mysql> SELECT * FROM user;
+----+----------+----------------+------------------+------+
| id | username | password | email | age |
+----+----------+----------------+------------------+------+
| 1 | KING | KING | KING@QQ.COM | 20 |
| 2 | QUEEN | QUEEN | QUEEN@QQ.COM | 30 |
| 3 | A | AAA | 382771946@qq.com | 18 |
| 4 | B | BBB | 382771946@qq.com | 18 |
| 6 | D | DDD | D@QQ.COM | 35 |
| 8 | E | EEE | E@QQ.COM | 9 |
| 18 | F | FFF | F@QQ.COM | 32 |
| 55 | C | CCC | CCC@QQ.COM | 18 |
| 98 | test | this is a test | 123@qq.com | 48 |
| 99 | maizi | maizixueyuan | 382771946@qq.com | 18 |
+----+----------+----------------+------------------+------+
10 rows in set (0.00 sec)
mysql> UPDATE user SET age=5;
Query OK, 10 rows affected (0.06 sec)
Rows matched: 10 Changed: 10 Warnings: 0
mysql> SELECT * FROM user;
+----+----------+----------------+------------------+------+
| id | username | password | email | age |
+----+----------+----------------+------------------+------+
| 1 | KING | KING | KING@QQ.COM | 5 |
| 2 | QUEEN | QUEEN | QUEEN@QQ.COM | 5 |
| 3 | A | AAA | 382771946@qq.com | 5 |
| 4 | B | BBB | 382771946@qq.com | 5 |
| 6 | D | DDD | D@QQ.COM | 5 |
| 8 | E | EEE | E@QQ.COM | 5 |
| 18 | F | FFF | F@QQ.COM | 5 |
| 55 | C | CCC | CCC@QQ.COM | 5 |
| 98 | test | this is a test | 123@qq.com | 5 |
| 99 | maizi | maizixueyuan | 382771946@qq.com | 5 |
+----+----------+----------------+------------------+------+
10 rows in set (0.00 sec)
删除数据
（1）不清空自增长的值：
DELETE tbl_name 字段名称=值,…[WHERE条件][ORDER BY字段名称][LIMIT限制条数]
mysql> DELETE FROM testUser ;
Query OK, 10 rows affected (0.07 sec)
（2）彻底清空数据表（清空自增长的值）：
TRUNCATE [TABLE] tbl_name
mysql> TRUNCATE TABLE user;
Query OK, 0 rows affected (0.32 sec)```
## DQL
#### 查询记录：
（1）SELECT select_expr [, select_expr ...]
[
FROM table_references
[WHERE 条件]
[GROUP BY {col_name | position} [ASC| DESC], ... 分组]
[HAVING 条件 对分组结果进行二次筛选]
[ORDER BY {col_name | position} [ASC| DESC], ...排序]
[LIMIT 限制显示条数]
]
（2）查询表方式
【1】每一个表达式表示想要的一列，必须至少有一列，多个列之间以逗号分隔
```sql
mysql> SELECT username,id,role FROM cms_admin;
+----------+----+-----------------+
| username | id | role |
+----------+----+-----------------+
| admin | 1 | 超级管理员 |
| king | 2 | 普通管理员 |
| 麦子 | 3 | 普通管理员 |
| queen | 4 | 普通管理员 |
| test | 5 | 普通管理员 |
+----------+----+-----------------+
5 rows in set (0.00 sec)
【2】* 表示所有列，tbl_name.*可以表示命名的所有列
mysql> SELECT cms_admin.* FROM cms_admin;
+----+----------+----------+--------------+-----------------+
| id | username | password | email | role |
+----+----------+----------+--------------+-----------------+
| 1 | admin | admin | admin@qq.com | 超级管理员 |
| 2 | king | king | admin@qq.com | 普通管理员 |
| 3 | 麦子 | maizi | admin@qq.com | 普通管理员 |
| 4 | queen | queen | admin@qq.com | 普通管理员 |
| 5 | test | test | admin@qq.com | 普通管理员 |
+----+----------+----------+--------------+-----------------+
5 rows in set (0.03 sec)
【3】查询表达式可以用 [AS]alias_name为其赋予别名
mysql> SELECT id,username FROM cms_admin AS a;
+----+----------+
| id | username |
+----+----------+
| 1 | admin |
| 2 | king |
| 4 | queen |
| 5 | test |
| 3 | 麦子 |
+----+----------+
5 rows in set (0.00 sec)
查询
查询范围：WHERE条件
模糊查询
（1）%表示0个或1个或多个任意字符
（2）_表示一个任意字符
GROUP BY分组
（1）单独使用，只显示组中第一条记录
mysql> SELECT * FROM cms_user GROUP BY proId;
+----+-----------+--------------+-------------+------------+----------+-------+------+
| id | username | password | email | regTime | face | proId | age |
+----+-----------+--------------+-------------+------------+----------+-------+------+
| 1 | 张三 | zhangsan | user@qq.com | 1419811708 | user.jpg | 1 | 18 |
| 2 | 张三丰 | zhangsanfeng | user@qq.com | 1419812708 | user.jpg | 2 | 18 |
| 3 | 章子怡 | zhangsan | user@qq.com | 1419813708 | user.jpg | 3 | 18 |
| 4 | long | long | user@qq.com | 1419814708 | user.jpg | 4 | 18 |
| 7 | king | king | user@qq.com | 1419817708 | user.jpg | 5 | 18 |
+----+-----------+--------------+-------------+------------+----------+-------+------+
5 rows in set (0.31 sec)
（1）配合GROUP_CONTACE()得到分组详情
mysql> SELECT id,sex,GROUP_CONCAT(username) FROM cms_user GROUP BY sex;
+----+--------+---------------------------------+
| id | sex | GROUP_CONCAT(username) |
+----+--------+---------------------------------+
| 1 | 男 | 张三,rose,king,ring,章子怡 |
| 4 | 女 | long,queen,blek,张三丰,lily |
| 11 | 保密 | john,test1 |
+----+--------+---------------------------------+
3 rows in set (0.07 sec)
（2）配合聚合函数
【1】COUNT()
mysql> SELECT id,sex,GROUP_CONCAT(username)AS users,COUNT(*) AS totalUsers FROM cms_user GROUP BY sex;
+----+--------+---------------------------------+------------+
| id | sex | users | totalUsers |
+----+--------+---------------------------------+------------+
| 1 | 男 | 张三,rose,king,ring,章子怡 | 5 |
| 4 | 女 | long,queen,blek,张三丰,lily | 5 |
| 11 | 保密 | john,test1 | 2 |
+----+--------+---------------------------------+------------+
3 rows in set (0.02 sec)
【2】MAX()
【3】MIN()
【4】AVG()
【5】SUM()
（3）配合WITH ROLLUP记录上面所有记录的综合
mysql> SELECT id,sex,GROUP_CONCAT(username),
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> MIN(age) AS min_age,
-> AVG(age) AS avg_age,
-> SUM(age) AS sum_age
-> FROM cms_user
-> GROUP BY sex WITH ROLLUP;
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+---------+---------+
| id | sex | GROUP_CONCAT(username) | totalUsers | max_age | min_age | avg_age | sum_age |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+---------+---------+
| 1 | 男 | 张三,rose,king,ring,章子怡 | 5 | 56 | 11 | 27.4000 | 137 |
| 4 | 女 | long,queen,blek,张三丰,lily | 5 | 88 | 21 | 52.4000 | 262 |
| 11 | 保密 | john,test1 | 2 | 65 | 65 | 65.0000 | 65 |
| 11 | NULL | 张三,rose,king,ring,章子怡,long,queen,blek,张三丰,lily,john,test1 | 12 | 88 | 11 | 42.1818 | 464 |
+----+--------+---------------------------------------------------------------------------+------------+---------+---------+---------+---------+
4 rows in set (0.00 sec)
（4）HAVING子句，对分组记录结果进行二次筛选（只能使用在分组之后）
mysql> SELECT sex,GROUP_CONCAT(username) AS users,
-> COUNT(*) AS totalUsers,
-> MAX(age) AS max_age,
-> SUM(age) AS sum_age
-> FROM cms_user
-> GROUP BY sex
-> HAVING COUNT(*)>2 AND MAX(age)>60;
+------+--------------------------------+------------+---------+---------+
| sex | users | totalUsers | max_age | sum_age |
+------+--------------------------------+------------+---------+---------+
| 女 | long,queen,blek,张三丰,lily | 5 | 88 | 262 |
+------+--------------------------------+------------+---------+---------+
1 row in set (0.00 sec)
947
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
