Shulou(Shulou.com)06/01 Report--

This article mainly gives you a brief talk about the simple practice of MySQL-5.5 operation commands. You can check the relevant professional terms on the Internet or find some related books to supplement them. We will not dabble here. Let's go straight to the topic. I hope this article of simple practice of MySQL-5.5 operation commands can bring you some practical help.

1. Data definition statement 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 `room` / *! 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 '0mm,-> 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\ gateway * 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 '0mm, 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 # change the character set Query OK of the library, 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 | Xiaodong | male | 13 | 2 | Xiaonan | female | 13 | 3 | Xiaobei | male | 13 | 4 | Xiaoxi | female | 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 | Xiaodong | male | 2 | Xiaonan | female | 3 | Xiaobei | male | 4 | Xiaoxi | female | +-+ 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 | Xiaodong | male | 0 | 2 | Xiaonan | female | 0 | 3 | Xiaobei | male | 0 | 4 | Xiaoxi | female | 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)

two。 Data manipulation statement DML

Insert

Select

Update

Delete

2.1 insert

Mysql > insert into student (name,sex,age) values ('Xiaodong', 'male', '13'), (' Xiaonan', 'female', '13'); Query OK, 2 rows affected (0.02 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > mysql > select * from student +-+ | id | name | sex | age | +-+ | 1 | Xiaodong | male | 13 | 2 | Xiaonan | female | 13 | + 2 rows in set (0.00 sec) mysql > insert into student values (3Little North) 'male', '13'), (4' Xiaoxi', 'female', '13') Query OK, 2 rows affected (0.00 sec) Records: 2 Duplicates: 0 Warnings: 0mysql > select * from student +-+ | id | name | sex | age | +-+ | 1 | Xiaodong | male | 13 | 2 | Xiaonan | female | 13 | 3 | Xiaobei | male | 13 | 4 | Xiaoxi | female | 13 | + | -+-+ 4 rows in set (0.00 sec)

2.2 select

Mysql > select * from student +-+ | id | name | sex | age | +-- + | 1 | Xiaodong | male | 0 | 2 | Xiaonan | female | 0 | 3 | Xiaobei | female | 12 | 4 | Xiaoxi | female | 13 | + -+ 4 rows in set (0.00 sec) mysql > select * from student where name=' Xiaobei' +-+ | id | name | sex | age | +-+ | 3 | Xiaobei | female | 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 | Xiaodong | male | 0 | 2 | Xiaonan | female | 0 | 3 | Xiaobei | male | 0 | 4 | Xiaoxi | female | 13 | + -- + 4 rows in set (0.00 sec) mysql > update student set sex=' female' 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 | Xiaodong | male | 0 | 2 | Xiaonan | female | 0 | 3 | Xiaobei | female | 12 | 4 | Xiaoxi | female | 13 | + -+ 4 rows in set (0.00 sec)

2.4 delete

Mysql > select * from student +-+ | id | name | sex | age | +-- + | 1 | Xiaodong | male | 0 | 2 | Xiaonan | female | 0 | 3 | Xiaobei | female | 12 | 4 | Xiaoxi | female | 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 | Xiaodong | male | 0 | 2 | Xiaonan | female | 0 | | 3 | Xiaobei | female | 12 | +-+-- + -+-+ 3 rows in set (0.00 sec) mysql > delete from student where name=' Xiaonan' Query OK, 1 row affected (0.00 sec) mysql > select * from student +-+ | id | name | sex | age | +-+ | 1 | Xiaodong | male | 0 | 3 | Xiaobei | female | 12 | +-+ 2 rows in set (0.00 sec)

3. Database management statement

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 `room` / *! 40100 DEFAULT CHARACTER SET utf8 * / | +-- -- + 1 row in set (0.00 sec) mysql > show create table student\ row * 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.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.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 inquiry 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 `room`.`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 | Xiaodong | male | 0 | 3 | Xiaobei | female | 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 operation command simple exercise to tell you here, for other related issues you want to know can continue to pay attention to our industry information. Our section will capture some industry news and professional knowledge to share with you every day.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.