13518219792
本文主要给大家简单讲讲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 changed mysql> 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: student Create 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=utf8 1 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: 0 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)
1.2 drop
mysql> use school; Database changed mysql> 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 changed mysql> 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: 0 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)
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: 0 mysql> 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: 0 mysql> 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: 0 mysql> 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: 0 mysql> 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: 0
mysql>
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: 0
mysql> 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: 0 mysql> 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: 0 mysql> 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 changed mysql> 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: student Create 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=utf8 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> 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 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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 changed mysql> 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 2 Server 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 its affiliates. Other names may be trademarks of their respective owners. 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操作命令简单练习就先给大家讲到这里，对于其它相关问题大家想要了解的可以持续关注我们的行业资讯。我们的板块内容每天都会捕捉一些行业新闻及专业知识分享给大家的。
