溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
本文主要給大家簡單講講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操作命令簡單練習就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
