MySQL-5.5操作命令简单练习
发表于：2025-01-23 作者：千家信息网编辑
千家信息网最后更新 2025年01月23日，本文主要给大家简单讲讲MySQL-5.5操作命令简单练习，相关专业术语大家可以上网查查或者找一些相关书籍补充一下，这里就不涉猎了，我们就直奔主题吧，希望MySQL-5.5操作命令简单练习这篇文章可以给
千家信息网最后更新 2025年01月23日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安全错误
数据库的锁怎样保障安全
网络技术应用 目录
网络安全中分段攻击的意思
新h3c服务器硬盘
大一上学期网络技术期末考试
数据库中视频如何存储
数据库关联表示语句
苹果手机服务器无法验证身份信息
银行网络技术人员演讲稿
消息软件的数据库
炫彩互联网科技
服务器机房散热
开源体系 软件开发
学生寒假学网络安全
鹰潭公司app软件开发多少钱
原神服务器什么能互通
java数据库对象列表转字符型
企业内部网络技术要求
安全风险数据库的目的
sql删除数据库用户
私人承包软件开发
网络技术公司招聘文案范文
电脑连接不上mysql数据库
网络安全的目的包括哪些
汽车车载网络技术的论文
数据库表 语句怎么写
工具贸易软件开发价格
为什么说网络安全是一个系统
网络安全大赛是敲代码吗
hpe高密度服务器价格
合肥软件开发驻场正规平台
