Summary of basic commands of mysql Library
1 Log in to mysql database
[root@db02--52] # mysql-uroot-p
Enter password:
Welcome to the MySQL monitor. Commands end with; or\ g.
Your MySQL connection id is 9
Server version: 5.6.34-log Source distribution
Copyright (c) 2000, 2016, 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 >
2 to view the currently logged-in users:
Mysql > select user ()
+-+
| | user () |
+-+
| | root@localhost |
+-+
1 row in set (0.20 sec)
Mysql >
3 create the database and view the complete statement of the built database
Mysql > create database ; create database
Query OK, 1 row affected (0.00 sec)
Mysql > show create database ; to view robust databases
+-+
| | Database | Create Database |
+-+
| | | CREATE DATABASE `` / *! 40100 DEFAULT CHARACTER SET utf8 * / | |
+-+
1 row in set (0.00 sec)
Mysql > show databases; view all databases
+-+
| | Database |
+-+
| | information_schema |
| | |
| | mysql |
| | oldboy |
| | oldgril |
| | performance_schema |
| | xinpan |
| | xu |
+-+
8 rows in set (0.00 sec)
Mysql >
4 create user hehe so that it can manage database
Mysql > create user hehe@'localhost' identified by 'oldboy123'
->; create a local user hehe authorization database password
Query OK, 0 rows affected (0.06 sec)
Mysql > grant all on .* to hehe@'localhost'; is the database, hehe is the user
Query OK, 0 rows affected (0.00 sec)
5 to see what permissions the created user hehe has
Mysql > show grants for hehe@'localhost'; to see what permissions the created user hehe has
+-+
| | Grants for hehe@localhost |
+-+
| | GRANT USAGE ON *. * TO 'hehe'@'localhost' IDENTIFIED BY PASSWORD' * FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515' |
| | GRANT ALL PRIVILEGES ON `oldboy`.* TO 'hehe'@'localhost' |
| | GRANT ALL PRIVILEGES ON `hehe`.* TO 'hehe'@'localhost' |
| | GRANT ALL PRIVILEGES ON ``.* TO 'hehe'@'localhost' |
+-+
4 rows in set (0.03 sec)
6 check which users are in the current database.
Mysql >
Mysql > select user,host from mysql.user
+-+ +
| | user | host |
+-+ +
| | keke |% | |
| | oldboy |% | |
| | xinjia |% | |
| | mha | 10.0.0.% | |
| | rep | 10.0.0.% | |
| | root | 127.0.0.1 | |
| | bbs | 172.16.1% | |
| | wordpress | 172.16.1% | |
| | root | db02--52 |
| | | localhost |
| | hehe | localhost |
| | oldboy | localhost |
| | root | localhost |
| | system | localhost |
+-+ +
14 rows in set (0.00 sec)
Mysql >
6 access to oldboy database
Mysql > use oldboy
Database changed
Mysql >
7 View the current database
Mysql > select database ()
+-+
| | database () |
+-+
| | oldboy |
+-+
1 row in set (0.00 sec)
Mysql >
8 create a table xiaoke fields id and name varchar (16)
Mysql > create table xiaoke (id int (4) not null, name varchar (16) not null)
Query OK, 0 rows affected (0.63 sec)
Mysql >
9 View the table structure and the sql statement of the table structure
Mysql > desc xiaoke to view the structure of the table
->
+-+ +
| | Field | Type | Null | Key | Default | Extra | |
+-+ +
| | id | int (4) | NO | | NULL |
| | name | varchar (16) | NO | | NULL |
+-+ +
2 rows in set (0.03 sec)
View the sql statement of the table structure
Mysql > show full columns from xiaoke; to view the sql statement of the table structure
+-+
| | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment |
+-+
| | id | int (4) | NULL | NO | | NULL | | select,insert,update,references | |
| | name | varchar (16) | utf8_general_ci | NO | | NULL | | select,insert,update,references | |
+-+
2 rows in set (0.00 sec)
Mysql >
10 insert a piece of data "1BI aini"
Mysql > insert into test (id,name) values
Query OK, 1 row affected (0.02 sec)
Mysql > select * from test
+-- +
| | id | age | name | |
+-- +
| | 1 | NULL | oldboy |
| | 1 | NULL | oldboy |
| | 1 | NULL | aini |
+-- +
3 rows in set (0.02 sec)
Mysql >
11 insert 2 rows of data "2Magnexiaoxu" and "3Magnexiaoxin" in batch
Mysql > insert into test (id,name) values (2meme Xaioke), (3meme Xiaoxin')
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
Mysql > select * from test
+-- +
| | id | age | name | |
+-- +
| | 1 | NULL | oldboy |
| | 1 | NULL | oldboy |
| | 1 | NULL | aini |
| | 2 | NULL | xaioke |
| | 3 | NULL | xiaoxin |
+-- +
5 rows in set (0.00 sec)
12 query the record named oldboy
Mysql > select * from test where name='oldboy'
+-- +
| | id | age | name | |
+-- +
| | 1 | NULL | oldboy |
| | 1 | NULL | oldboy |
+-- +
2 rows in set (0.00 sec)
Mysql >
13 change the name of database id equal to 1 oldboy to oldgril
Mysql > select * from test where name='oldboy'
+-- +
| | id | age | name | |
+-- +
| | 1 | NULL | oldboy |
| | 1 | NULL | oldboy |
+-- +
2 rows in set (0.00 sec)
Mysql > updata test set name='oldgirl' where id='1'
Mysql > select * from test
+-- +
| | id | age | name | |
+-- +
| | 1 | NULL | oldgirl |
| | 1 | NULL | oldgirl |
| | 1 | NULL | oldgirl |
| | 2 | NULL | xaioke |
| | 3 | NULL | xiaoxin |
+-- +
5 rows in set (0.00 sec)
Mysql >
To be continued.
