MySQL-5.5操作命令简单练习
发表于：2025-11-11 作者：千家信息网编辑
千家信息网最后更新 2025年11月11日，本文主要给大家简单讲讲MySQL-5.5操作命令简单练习，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望MySQL-5.5操作命令简单练习这篇文章可以给
千家信息网最后更新 2025年11月11日MySQL-5.5操作命令简单练习
本文主要给大家简单讲讲MySQL-5.5操作命令简单练习，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望MySQL-5.5操作命令简单练习这篇文章可以给大家带来一些实际帮助。
1. 数据定义语句 DDL
create （database | table | index）
drop （database | table | index）
alter （database | table）
rename （table）
1.1 create
mysql> create database school;Query OK, 1 row affected (0.00 sec)mysql> show create database school;+----------+-----------------------------------------------------------------+| Database | Create Database |+----------+-----------------------------------------------------------------+| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+-----------------------------------------------------------------+1 row in set (0.00 sec)mysql> use school;Database changedmysql> mysql> create table student( -> `id` int(5) not null auto_increment, -> `name` char(20) not null, -> `sex` char(5) not null, -> `age` tinyint(2) not null default '0', -> primary key(id), -> key index_name(name));Query OK, 0 rows affected (0.02 sec)mysql> desc student;+-------+------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+----------------+| id | int(5) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || sex | char(5) | NO | | NULL | || age | tinyint(2) | NO | | NULL | |+-------+------------+------+-----+---------+----------------+4 rows in set (0.00 sec)mysql> show create table student\G*************************** 1. row *************************** Table: studentCreate Table: CREATE TABLE `student` ( `id` int(5) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `sex` char(5) NOT NULL, `age` tinyint(2) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), KEY `index_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> create index index_age on student(age);Query OK, 0 rows affected (0.12 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc student; +-------+------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+----------------+| id | int(5) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || sex | char(5) | NO | | NULL | || age | tinyint(2) | NO | MUL | NULL | |+-------+------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
1.2 drop
mysql> use school;Database changedmysql> mysql> show tables;+------------------+| Tables_in_school |+------------------+| student || test01 || test02 |+------------------+3 rows in set (0.00 sec)mysql> mysql> drop table test01;Query OK, 0 rows affected (0.00 sec)mysql> show tables; +------------------+| Tables_in_school |+------------------+| student || test02 |+------------------+2 rows in set (0.00 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || school || test |+--------------------+5 rows in set (0.00 sec)mysql> drop database test;Query OK, 0 rows affected (0.12 sec)mysql> show databases; +--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || school |+--------------------+4 rows in set (0.00 sec)mysql> use school;Database changedmysql> mysql> desc student;+-------+------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+----------------+| id | int(5) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || sex | char(5) | NO | | NULL | || age | tinyint(2) | NO | MUL | NULL | |+-------+------------+------+-----+---------+----------------+4 rows in set (0.00 sec)mysql> drop index index_age on student;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc student; +-------+------------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+------------+------+-----+---------+----------------+| id | int(5) | NO | PRI | NULL | auto_increment || name | char(20) | NO | MUL | NULL | || sex | char(5) | NO | | NULL | || age | tinyint(2) | NO | | NULL | |+-------+------------+------+-----+---------+----------------+4 rows in set (0.00 sec)
1.3 alter
mysql> show create database test;+----------+---------------------------------------------------------------+| Database | Create Database |+----------+---------------------------------------------------------------+| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+---------------------------------------------------------------+1 row in set (0.00 sec)mysql> alter database test CHARACTER SET GBK COLLATE gbk_chinese_ci; # 改库的字符集Query OK, 1 row affected (0.00 sec) mysql> show create database test; +----------+--------------------------------------------------------------+| Database | Create Database |+----------+--------------------------------------------------------------+| test | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET gbk */ |+----------+--------------------------------------------------------------+1 row in set (0.00 sec)mysql> show character set;+----------+-----------------------------+---------------------+--------+| Charset | Description | Default collation | Maxlen |+----------+-----------------------------+---------------------+--------+| big5 | Big5 Traditional Chinese | big5_chinese_ci | 2 || dec8 | DEC West European | dec8_swedish_ci | 1 || cp850 | DOS West European | cp850_general_ci | 1 || hp8 | HP West European | hp8_english_ci | 1 || koi8r | KOI8-R Relcom Russian | koi8r_general_ci | 1 || latin1 | cp1252 West European | latin1_swedish_ci | 1 || latin2 | ISO 8859-2 Central European | latin2_general_ci | 1 || gbk | GBK Simplified Chinese | gbk_chinese_ci | 2 || latin5 | ISO 8859-9 Turkish | latin5_turkish_ci | 1 || armscii8 | ARMSCII-8 Armenian | armscii8_general_ci | 1 || utf8 | UTF-8 Unicode | utf8_general_ci | 3 |mysql> desc test01;+-------+--------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| id | int(4) | NO | PRI | 0 | || fit | int(5) | YES | MUL | NULL | |+-------+--------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table test01 drop primary key;Query OK, 0 rows affected (0.17 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc test01;+-------+--------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| id | int(4) | NO | | 0 | || fit | int(5) | YES | MUL | NULL | |+-------+--------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> alter table test01 drop index index_fit;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> desc test01;+-------+--------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+--------+------+-----+---------+-------+| id | int(4) | NO | | 0 | || fit | int(5) | YES | | NULL | |+-------+--------+------+-----+---------+-------+2 rows in set (0.00 sec)mysql> select * from student;+----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 13 || 2 | 小南 | 女 | 13 || 3 | 小北 | 男 | 13 || 4 | 小西 | 女 | 13 |+----+--------+-----+-----+4 rows in set (0.10 sec)mysql> alter table student drop age;Query OK, 4 rows affected (0.16 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> mysql> select * from student; +----+--------+-----+| id | name | sex |+----+--------+-----+| 1 | 小东 | 男 || 2 | 小南 | 女 || 3 | 小北 | 男 || 4 | 小西 | 女 |+----+--------+-----+4 rows in set (0.00 sec)mysql> alter table student add age tinyint(2) not null;Query OK, 4 rows affected (0.10 sec)Records: 4 Duplicates: 0 Warnings: 0mysql> select * from student; +----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 2 | 小南 | 女 | 0 || 3 | 小北 | 男 | 0 || 4 | 小西 | 女 | 0 |+----+--------+-----+-----+4 rows in set (0.00 sec)
1.4 rename
mysql> show tables;+------------------+| Tables_in_school |+------------------+| student |+------------------+1 row in set (0.00 sec)mysql> rename table student to boy;Query OK, 0 rows affected (0.00 sec)mysql> show tables; +------------------+| Tables_in_school |+------------------+| boy |+------------------+1 row in set (0.00 sec)
2. 数据操作语句 DML
insert
select
update
delete
2.1 insert
mysql> insert into student(name,sex,age) values('小东','男','13'),('小南','女','13');Query OK, 2 rows affected (0.02 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> mysql> select * from student;+----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 13 || 2 | 小南 | 女 | 13 |+----+--------+-----+-----+2 rows in set (0.00 sec)mysql> insert into student values(3,'小北','男','13'),(4,'小西','女','13'); Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from student;+----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 13 || 2 | 小南 | 女 | 13 || 3 | 小北 | 男 | 13 || 4 | 小西 | 女 | 13 |+----+--------+-----+-----+4 rows in set (0.00 sec)2.2 select
mysql> select * from student; +----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 2 | 小南 | 女 | 0 || 3 | 小北 | 女 | 12 || 4 | 小西 | 女 | 13 |+----+--------+-----+-----+4 rows in set (0.00 sec)mysql> select * from student where name='小北';+----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 3 | 小北 | 女 | 12 |+----+--------+-----+-----+1 row in set (0.00 sec)mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | 127.0.0.1 || root | localhost |+------+-----------+2 rows in set (0.00 sec)mysql> select user,host from mysql.user where user='root' and host='localhost';+------+-----------+| user | host |+------+-----------+| root | localhost |+------+-----------+1 row in set (0.00 sec)
2.3 update
mysql> update student set age=13 where id=4;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from student;+----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 2 | 小南 | 女 | 0 || 3 | 小北 | 男 | 0 || 4 | 小西 | 女 | 13 |+----+--------+-----+-----+4 rows in set (0.00 sec)mysql> update student set sex='女',age=12 where id=3;Query OK, 1 row affected (0.00 sec)Rows matched: 1 Changed: 1 Warnings: 0mysql> select * from student; +----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 2 | 小南 | 女 | 0 || 3 | 小北 | 女 | 12 || 4 | 小西 | 女 | 13 |+----+--------+-----+-----+4 rows in set (0.00 sec)
2.4 delete
mysql> select * from student; +----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 2 | 小南 | 女 | 0 || 3 | 小北 | 女 | 12 || 4 | 小西 | 女 | 13 |+----+--------+-----+-----+4 rows in set (0.00 sec)mysql> delete from student where id=4;Query OK, 1 row affected (0.00 sec)mysql> select * from student; +----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 2 | 小南 | 女 | 0 || 3 | 小北 | 女 | 12 |+----+--------+-----+-----+3 rows in set (0.00 sec)mysql> delete from student where name='小南';Query OK, 1 row affected (0.00 sec)mysql> select * from student; +----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 3 | 小北 | 女 | 12 |+----+--------+-----+-----+2 rows in set (0.00 sec)
3. 数据库管理语句
show
create user
grant
revoke
3.1 show
mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || school |+--------------------+4 rows in set (0.00 sec)mysql> use school;Database changedmysql> show tables;+------------------+| Tables_in_school |+------------------+| student |+------------------+1 row in set (0.00 sec)mysql> show create database school;+----------+-----------------------------------------------------------------+| Database | Create Database |+----------+-----------------------------------------------------------------+| school | CREATE DATABASE `school` /*!40100 DEFAULT CHARACTER SET utf8 */ |+----------+-----------------------------------------------------------------+1 row in set (0.00 sec)mysql> show create table student\G*************************** 1. row *************************** Table: studentCreate Table: CREATE TABLE `student` ( `id` int(5) NOT NULL AUTO_INCREMENT, `name` char(20) NOT NULL, `sex` char(5) NOT NULL, `age` tinyint(2) NOT NULL, PRIMARY KEY (`id`), KEY `index_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> select user,host from mysql.user;+------+-----------+| user | host |+------+-----------+| root | 127.0.0.1 || root | localhost |+------+-----------+2 rows in set (0.00 sec)mysql> show grants for root@localhost; +----------------------------------------------------------------------------------------------------------------------------------------+| Grants for root@localhost |+----------------------------------------------------------------------------------------------------------------------------------------+| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' WITH GRANT OPTION || GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION |+----------------------------------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)
3.2 create user
mysql> select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host | password |+------+-----------+-------------------------------------------+| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || root | 127.0.0.1 | |+------+-----------+-------------------------------------------+2 rows in set (0.00 sec)mysql> mysql> create user logen@'192.168.0.%' identified by '123';Query OK, 0 rows affected (0.00 sec)mysql> select user,host,password from mysql.user; +-------+-------------+-------------------------------------------+| user | host | password |+-------+-------------+-------------------------------------------+| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || root | 127.0.0.1 | || logen | 192.168.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |+-------+-------------+-------------------------------------------+3 rows in set (0.00 sec)mysql> select user,host,password from mysql.user;+-------+-------------+-------------------------------------------+| user | host | password |+-------+-------------+-------------------------------------------+| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || root | 127.0.0.1 | || logen | 192.168.0.% | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 |+-------+-------------+-------------------------------------------+3 rows in set (0.00 sec)mysql> drop user logen@'192.168.0.%'; Query OK, 0 rows affected (0.00 sec)mysql> select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host | password |+------+-----------+-------------------------------------------+| root | localhost | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 || root | 127.0.0.1 | |+------+-----------+-------------------------------------------+2 rows in set (0.00 sec)
3.3 grant
mysql> grant insert,delete,update,select on school.student to logen@'192.168.0.%' identified by '123';Query OK, 0 rows affected (0.00 sec)mysql> show grants for logen@'192.168.0.%'; +----------------------------------------------------------------------------------------------------------------+| Grants for logen@192.168.0.% |+----------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'logen'@'192.168.0.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' || GRANT SELECT, INSERT, UPDATE, DELETE ON `school`.`student` TO 'logen'@'192.168.0.%' |+----------------------------------------------------------------------------------------------------------------+2 rows in set (0.00 sec)[root@SQL-S1 ~]# mysql -h292.168.0.88 -ulogen -pEnter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 3Server version: 5.5.55-log MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || school |+--------------------+2 rows in set (0.00 sec)mysql> use school;Database changedmysql> mysql> show tables;+------------------+| Tables_in_school |+------------------+| student |+------------------+1 row in set (0.00 sec)mysql> mysql> select * from student;+----+--------+-----+-----+| id | name | sex | age |+----+--------+-----+-----+| 1 | 小东 | 男 | 0 || 3 | 小北 | 女 | 12 |+----+--------+-----+-----+2 rows in set (0.00 sec)
3.4 revoke
mysql> revoke all on school.student from logen@'192.168.0.%';Query OK, 0 rows affected (0.00 sec)mysql> show grants for logen@'192.168.0.%'; +----------------------------------------------------------------------------------------------------------------+| Grants for logen@192.168.0.% |+----------------------------------------------------------------------------------------------------------------+| GRANT USAGE ON *.* TO 'logen'@'192.168.0.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |+----------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)[root@SQL-S1 ~]# mysql -h292.168.0.88 -ulogen -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.5.55-log MySQL Community Server (GPL)Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> show databases;+--------------------+| Database |+--------------------+| information_schema |+--------------------+1 row in set (0.00 sec)
MySQL-5.5操作命令简单练习就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。
小东
小西
命令
数据
语句
专业
行业
专业知识
主题
书籍
内容
字符
字符集
实际
数据库
新闻
术语
板块
知识
篇文章
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
Excel数据库输入数据
成都网络安全知识答题
网络安全岗位安全目标
车辆网络安全兼职
世界地区数据库
成都公司中标云南软件开发
平航服务器远程勘验设备
云课堂服务器账号密码
员工最容易犯的网络安全问题
网络安全议论文素材初中
软件开发涉及行业司法
idc服务器需要同相电吗
服务器 返回json
网络安全产品管理方案
怀化市网络安全攻防应急演练
华为5g网络技术专题
阳城网络安全周
网络安全文明上网论文
惠普服务器阵列管理页面
plc编程是软件开发吗
饥荒服务器mac
sql发布向导是数据库吗
播出服务器
市公安局网络安全管理支队
数据库高性能概念
企业网络技术咨询怎么样
楚雄服务器云存储经销商
客户机服务器模式架构
汕尾软件开发就业
和平精英国际服亚服服务器视频
