MYSQL命令（二）
查看表结构和alter 增加、修改、删除字段
16.查看表结构
(1)desc 表名;
mysql> desc runoob_tbl; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | runoob_title | varchar(100) | NO | | NULL | | | runoob_author | varchar(40) | NO | | NULL | | | submission_date | date | YES | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
(2)describe表名;
mysql> describe runoob_tbl; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | runoob_title | varchar(100) | NO | | NULL | | | runoob_author | varchar(40) | NO | | NULL | | | submission_date | date | YES | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
（3）show columns from 表名;
mysql> show columns from runoob_tbl; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | runoob_title | varchar(100) | NO | | NULL | | | runoob_author | varchar(40) | NO | | NULL | | | submission_date | date | YES | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
(4) show create table 表名;
mysql> show create table runoob_tbl; +------------+----------------------------------------------------------------------------------------+ | Table | Create Table | +------------+----------------------------------------------------------------------------------------+ | runoob_tbl | CREATE TABLE `runoob_tbl` ( `runoob_id` int(10) unsigned NOT NULL AUTO_INCREMENT, `runoob_title` varchar(100) NOT NULL, `runoob_author` varchar(40) NOT NULL, `submission_date` date DEFAULT NULL, PRIMARY KEY (`runoob_id`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 | +------------+----------------------------------------------------------------------------------------
(5) show fields from 表名;
mysql> show fields from runoob_tbl; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | runoob_title | varchar(100) | NO | | NULL | | | runoob_author | varchar(40) | NO | | NULL | | | submission_date | date | YES | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
17.alter命令
(1) 添加字段：alter table 表名 add 字段 类型;
mysql> alter table runoob_tbl add runoob_extra varchar(40); Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show columns from runoob_tbl; +-----------------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------------+------------------+------+-----+---------+----------------+ | runoob_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | runoob_title | varchar(100) | NO | | NULL | | | runoob_author | varchar(40) | NO | | NULL | | | submission_date | date | YES | | NULL | | | runoob_extra | varchar(40) | YES | | NULL | | +-----------------+------------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
(2) 删除字段：alter table表名 drop 字段;
mysql> alter table runoob_tbl drop runoob_extra; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show full fields from runoob_tbl; +-----------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-----------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ | runoob_id | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | | runoob_title | varchar(100) | utf8_general_ci | NO | | NULL | | select,insert,update,references | | | runoob_author | varchar(40) | utf8_general_ci | NO | | NULL | | select,insert,update,references | | | submission_date | date | NULL | YES | | NULL | | select,insert,update,references | | +-----------------+------------------+-----------------+------+-----+---------+----------------+---------------------------------+---------+ 4 rows in set (0.00 sec)
(3) 修改字段：alter table表名 modify 字段 类型;
mysql> alter table runoob_tbl modify runoob_title varchar(128); Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc runoob_tbl runoob_title; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | runoob_title | varchar(128) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
18.插入数据语法：INSERT INTO table_name ( field1, field2,...fieldN ) VALUES( value1, value2,...valueN );
mysql> INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("学习PHP", " runoob.com", NOW());
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> INSERT INTO runoob_tbl (runoob_title, runoob_author, submission_date) VALUES ("JAVA 教程", "RUNOOB.COM", '2016-05-06');
Query OK, 1 row affected (0.01 sec)
19.查询数据语法：SELECT column_name,column_nameFROM table_name [WHERE Clause] [LIMIT N][ OFFSET M]
mysql> select * from runoob_tbl; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习PHP | runoob.com | 2019-11-12 | | 2 | 学习 MySQL | runoob.com | 2019-11-12 | | 3 | JAVA 教程 | RUNOOB.COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 3 rows in set (0.04 sec)
20.UPDATE 更新
mysql> UPDATE runoob_tbl SET runoob_title='学习 C++' WHERE runoob_id=3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> select * from runoob_tbl where runoob_id=3; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 3 | 学习 C++ | RUNOOB.COM | 2016-05-06 | +-----------+--------------+---------------+-----------------+ 1 row in set (0.00 sec)
21.DELETE语法:DELETE FROM table_name [WHERE Clause]
mysql> DELETE FROM runoob_tbl WHERE runoob_id=3; Query OK, 1 row affected (0.01 sec) 22.Where子句 mysql> SELECT * from runoob_tbl WHERE runoob_author=' runoob.com'; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习PHP | runoob.com | 2019-11-12 | | 2 | 学习 MySQL | runoob.com | 2019-11-12 | +-----------+--------------+---------------+-----------------+ 2 rows in set (0.00 sec)
22.LIKE子句语法：SELECT field1, field2,...fieldN FROM table_name WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
mysql> SELECT * from runoob_tbl WHERE runoob_author LIKE '%COM'; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习PHP | runoob.com | 2019-11-12 | | 2 | 学习 MySQL | runoob.com | 2019-11-12 | +-----------+--------------+---------------+-----------------+ 2 rows in set (0.00 sec)
23.分组GROUP BY 语法：SELECT column_name, function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;
mysql> select count(*), runoob_author from runoob_tbl group by runoob_author; +----------+---------------+ | count(*) | runoob_author | +----------+---------------+ | 2 | runoob | | 2 | runoob.com | +----------+---------------+ 2 rows in set (0.00 sec)
补充：with rollup可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
mysql> select sum(runoob_id), runoob_author from runoob_tbl group by runoob_author with rollup; +----------------+---------------+ | sum(runoob_id) | runoob_author | +----------------+---------------+ | 29 | runoob | | 3 | runoob.com | | 32 | NULL | +----------------+---------------+ 3 rows in set (0.00 sec) mysql> select avg(runoob_id), runoob_author from runoob_tbl group by runoob_author with rollup; +----------------+---------------+ | avg(runoob_id) | runoob_author | +----------------+---------------+ | 14.5000 | runoob | | 1.5000 | runoob.com | | 8.0000 | NULL | +----------------+---------------+ 3 rows in set (0.00 sec) mysql> select count(runoob_id), runoob_author from runoob_tbl group by runoob_author with rollup; +------------------+---------------+ | count(runoob_id) | runoob_author | +------------------+---------------+ | 2 | runoob | | 2 | runoob.com | | 4 | NULL | +------------------+---------------+ 3 rows in set (0.00 sec)
24.ORDER BY子句查询数据语法：SELECT field1, field2,...fieldN FROM table_name1, table_name2...ORDER BY field1 [ASC [DESC][默认 ASC]], [field2...] [ASC [DESC][默认 ASC]]
mysql> select * from runoob_tbl order by runoob_id desc; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 18 | 学习JAVA | runoob | 2019-11-28 | | 11 | 学习JAVA | runoob | 2019-11-18 | | 2 | 学习 MySQL | runoob.com | 2019-11-12 | | 1 | 学习PHP | runoob.com | 2019-11-12 | +-----------+--------------+---------------+-----------------+ 4 rows in set (0.00 sec)
25.NULL值处理：
mysql> select * from runoob_tbl where runoob_title is not null; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习PHP | runoob.com | 2019-11-12 | | 2 | 学习 MySQL | runoob.com | 2019-11-12 | | 11 | 学习JAVA | runoob | 2019-11-18 | | 18 | 学习JAVA | runoob | 2019-11-28 | +-----------+--------------+---------------+-----------------+ 4 rows in set (0.00 sec)
查询null值：
mysql> insert runoob_tbl values(5,null,'RUNOOB','2019-11-29'); Query OK, 1 row affected (0.01 sec) mysql> select * from runoob_tbl where runoob_title is null; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 5 | NULL | RUNOOB | 2019-11-29 | +-----------+--------------+---------------+-----------------+ 1 row in set (0.01 sec)
26.正则表达式：
mysql> select * from runoob_tbl where runoob_author REGEXP '^runoob.'; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习PHP | runoob.com | 2019-11-12 | | 2 | 学习 MySQL | runoob.com | 2019-11-12 | +-----------+--------------+---------------+-----------------+ 2 rows in set (0.00 sec) mysql> mysql> select * from runoob_tbl where runoob_author REGEXP '^RUN'; +-----------+--------------+---------------+-----------------+ | runoob_id | runoob_title | runoob_author | submission_date | +-----------+--------------+---------------+-----------------+ | 1 | 学习PHP | runoob.com | 2019-11-12 | | 2 | 学习 MySQL | runoob.com | 2019-11-12 | | 5 | NULL | RUNOOB | 2019-11-29 | | 11 | 学习JAVA | runoob | 2019-11-18 | | 18 | 学习JAVA | runoob | 2019-11-28 | +-----------+--------------+---------------+-----------------+ 5 rows in set (0.00 sec)
