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操作命令简单练习就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。