07_mysql Common SQL statements

First, database-related

1. Create a database:

mysql> CREATE DATABASE test default character set UTF8 collate utf8_general_ci;
Query OK, 1 row Affected (0.00 sec)

2. Query data:

Query all databases:

Mysql> show databases;
+--------------------+
| Database |
+--------------------+
| Information_schema |
| MySQL |
| Performance_schema |
| Taotao |
| Test |
| Xmall |
+--------------------+
6 rows in Set (0.00 sec)

Query the database that is currently in use:

mysql> Select Database ();
+------------+
| Database () |
+------------+
| Taotao |
+------------+
1 row in Set (0.00 sec)

3. Change Database information:

1. Change the name:

has abandoned rename.

1. If all the tables in the library are MyISAM engines, the names of the folders corresponding to the library can be changed.
(There is little likelihood of this, and the method requires downtime, not quite practical.)
To shut down MySQL Server:

sudo /etc/init.d/mysql stop

Change Directory Name:

cd /data/mysql; mv old_db new_db;

Turn on MySQL Server

sudo /etc/init.d/mysql start

2. Rename all tables below the old library

create databases new_db;rename table old_db.table1 to new_db.table1,old_db.table2 to new_db.table2,...;drop database old_db;

3. Build a new library, use the Mysqldump tool to export the old library data and import the new library

Mysqldump-uuser-ppass-hhost-pport--single-transaction old_db > Old_db.sqlmysql -uuser - Ppass -hhost -pport -e Span class= "hljs-string" > "CREATE Database new_db" MySQL -uuser -ppass -hhost -Pport new_ DB < old_db.sqlmysql -uuser Span class= "Hljs-attribute" >-ppass -hhost -Pport -e  "drop database old_db"       

In the case of the table in method 2, it is too painful to write these names, which can be done by shell scripts or stored procedures.

#!/bin/bashmysqlconn=" mysql -uuser -ppass  -hhost -Pport ”old_db=”old_db ”new_db=”new_db ”$mysqlconn -e “CREATE DATABASE $new_db”params=$($mysqlconn -N -e “SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE table_schema=’$old_db’”)for name in $params; do$mysqlconn -e “RENAME TABLE $old_db.$name to $new_db.$name”;done;$mysqlconn -e “DROP DATABASE $old_db”

07_mysql Common SQL statements