MySQL简单使用

OS: ubuntu 16.04

SQL

• 根据数据字典对数据表拼串

-- 遍历WordPress数据表

SELECT 
    CONCAT('select * from ', table_name, ';')
FROM
    information_schema.tables
WHERE
    table_name LIKE 'wp_%';

CLI

• root@virtualUnbuntu:/home/ben# mysql -u root -p

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.17-0ubuntu0.16.04.1 (Ubuntu)

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> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

• mysql> create database test;

Query OK, 1 row affected (0.00 sec)

• mysql> use test;

Database changed

• mysql> show tables;

Empty set (0.00 sec)

• mysql> create table test (id int,name varchar(255));

Query OK, 0 rows affected (0.55 sec)

• mysql> insert into test (id,name) values (1,’ana’);

Query OK, 1 row affected (0.07 sec)

• mysql> insert into test (id,name) values (2,’jack’);

Query OK, 1 row affected (0.07 sec)

• mysql> insert into test (id,name) values (3,’marry’);

Query OK, 1 row affected (0.08 sec)

• mysql> select * from test ;

+------+-------+
| id   | name  |
+------+-------+
|    1 | ana   |
|    2 | jack  |
|    3 | marry |
+------+-------+
3 rows in set (0.00 sec)

• mysql> update test set name = ‘marry3’ where id =3;

Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

• mysql> delete from test where id = 2;

Query OK, 1 row affected (0.07 sec)

• mysql> select * from test;

+------+--------+
| id   | name   |
+------+--------+
|    1 | ana    |
|    3 | marry3 |
+------+--------+
2 rows in set (0.00 sec)

Query database, table, user

• mysql> show databases;
• mysql> show tables;
• mysql> select user, password from mysql.user;