MYSQL命令(二)
查看表结构和alter 增加、修改、删除字段
16.查看表结构
(1)desc 表名;
1 2 3 4 5 6 7 8 9 10 | 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表名;
1 2 3 4 5 6 7 8 9 10 | 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 表名;
1 2 3 4 5 6 7 8 9 10 | 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 表名;
1 2 3 4 5 6 7 8 9 10 11 12 | 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 表名;
1 2 3 4 5 6 7 8 9 10 | 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 字段 类型;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | 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 字段;
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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 字段 类型;
1 2 3 4 5 6 7 8 9 10 | 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 );
1 2 3 4 | 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]
1 2 3 4 5 6 7 8 9 | 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 更新
1 2 3 4 5 6 7 8 9 10 | 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]
1 2 3 4 5 6 7 8 9 10 11 | 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'
1 2 3 4 5 6 7 8 | 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;
1 2 3 4 5 6 7 8 | 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…)。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | 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]]
1 2 3 4 5 6 7 8 9 10 | 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值处理:
1 2 3 4 5 6 7 8 9 10 | 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值:
1 2 3 4 5 6 7 8 9 10 | 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.正则表达式:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | 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) |
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux glibc自带哈希表的用例及性能测试
· 深入理解 Mybatis 分库分表执行原理
· 如何打造一个高并发系统?
· .NET Core GC压缩(compact_phase)底层原理浅谈
· 现代计算机视觉入门之:什么是图片特征编码
· 手把手教你在本地部署DeepSeek R1,搭建web-ui ,建议收藏!
· Spring AI + Ollama 实现 deepseek-r1 的API服务和调用
· 数据库服务器 SQL Server 版本升级公告
· C#/.NET/.NET Core技术前沿周刊 | 第 23 期(2025年1.20-1.26)
· 程序员常用高效实用工具推荐,办公效率提升利器!