Some basic MySQL operations

recently started to learn MySQL, mainly through books, and see Yan 18 teacher's video, and then through the blog to record their learning process. log in to database

View all current databases

Mysql> show databases, #查看当前全部数据库 +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema |+--------------------+3 rows in Set (0.00 sec)

Create a database

mysql> CREATE database Ceshi; #创建数据库ceshiQuery OK, 1 row Affected (0.00 sec) mysql> show databases; #查看当前全部数据库, you'll see more now. A ceshi+--------------------+| Database |+--------------------+| Information_schema | | Ceshi | | MySQL | | Performance_schema |+--------------------+4 rows in Set (0.00 sec)

view the definition of a database

Mysql> Show Create Database Ceshi, #查看数据库ceshi的定义 +----------+--------------------------------------------------- ---------------+| Database | Create Database |+----------+-------------------------------------------- ----------------------+| Ceshi | CREATE DATABASE ' Ceshi '/*!40100 DEFAULT CHARACTER SET latin1 */|+----------+----------------------------------------- -------------------------+1 Row in Set (0.00 sec)

Deleting a database

mysql> drop database Ceshi; #删除数据库ceshiQuery OK, 0 rows affected (0.07 sec) mysql> show databases; #查看当前全部数据库, now you'll find CES Hi no longer exists, because has deleted +--------------------+| Database |+--------------------+| Information_schema | | MySQL | | Performance_schema |+--------------------+3 rows in Set (0.00 sec

Working with Databases

mysql> CREATE database Ceshi_ku; #先建立一个库, etc to build table query OK, 1 row Affected (0.00 sec) mysql> show databases; #ceshi_ku已经成 The establishment of the work +--------------------+| Database           |+--------------------+| information_schema | | ceshi_ku           | | mysql              | | performance_schema |+------ --------------+4 rows in Set (0.00 sec) mysql> use Ceshi_ku; #建立表之前, first select a library database changed

View all tables below the current library

Mysql> show tables; #查看当前库以下的全部表, now what table is not empty set (0.00 sec)

Create a table

Mysql> CREATE TABLE Ceshi_biao    ---    ID int (one),    salary float    ); #建立ceshi_ Biao Table Query OK, 0 rows affected (0.10 sec) mysql> show tables; #查看当前库以下的全部表, Ceshi_biao table has been successfully established +--------------------+| Tables_in_ceshi_ku |+--------------------+| Ceshi_biao         |+--------------------+1 row in Set (0.00 sec)

View the table's basic structure

Mysql> desc Ceshi_biao, #查看ceshi_biao表基本结构 +--------+---------+------+-----+---------+-------+| Field  | Type    | Null | Key | Default | Extra |+--------+---------+------+-----+---------+-------+| ID     | int (11) | YES  |     | NULL    |       | | | salary | float   | YES  |     | NULL    |       | +--------+---------+------+-----+---------+-------+2 rows in Set (0.04 sec)

view table-specific structure

Mysql> Show CREATE Table Ceshi_biao, #查看ceshi_biao表具体结构 +------------+------------------------------------------- ------------------------------------------------------------------------------------+| Table      | Create Table                                                                                                                  |+------------+------------------------------------------------------------------------------- ------------------------------------------------+| Ceshi_biao | CREATE TABLE ' Ceshi_biao ' (  ' id ' int (one) default null,  ' salary ' float default null) ENGINE=INNODB default charset= Latin1 |+------------+------------------------------------------------------------------------------------------ -------------------------------------+1 Row in Set (0.00 sec)

Change table name

mysql> ALTER TABLE Ceshi_biao Rename to ceshi_new; #把ceshi_biao表的名字换成ceshi_newQuery OK, 0 rows affected (0.05 sec) mysql& Gt Show tables; #查看当前库以下的全部表, the name of the Ceshi_biao table has been successfully changed to ceshi_new+--------------------+| Tables_in_ceshi_ku |+--------------------+| Ceshi_new          |+--------------------+1 row in Set (0.00 sec) ########## #2种方法 ############### #mysql > Rename table Ceshi_biao to Ceshi_new; #把ceshi_biao表的名字换成ceshi_newQuery OK, 0 rows affected (0.03 sec) mysql> show tables;# To view all tables below the current library, the name of the Ceshi_biao table has been successfully changed to ceshi_new+--------------------+| Tables_in_ceshi_ku |+--------------------+| Ceshi_new |+--------------------+1 row in Set (0.00 sec)

inserting data into a table

mysql> INSERT into ceshi_new values---(11,400.56), (12,600.07), (13,800.45); Query OK, 3 rows affected (0.03 sec) records:3 duplicates:0 warnings:0mysql> select * from Ceshi_new; #这个表就是员工号码以及员工 Salary, just inserted 3 +------+--------+| ID |   Salary |+------+--------+| 11 |   400.56 | | 12 |   600.07 | | 13 | 800.45 |+------+--------+3 rows in Set (0.00 sec

Clear Table

mysql> truncate ceshi_new; #清空表, not delete table query OK, 0 rows affected (0.04 sec) mysql> select * from ceshi_new; #查看内容Empty SE T (0.00 sec)

Delete a table

mysql> drop table ceshi_new; #删除ceshi_new表Query OK, 0 rows affected (0.05 sec) mysql> show tables; #查看当前库以下的全部表, Ceshi_ The new table has successfully deleted the empty set (0.01 sec)

Change Code

mysql> set names UTF8; #改动编码为utf8Query OK, 0 rows Affected (0.00 sec)

Some basic MySQL operations