MySQL資料庫的一些基本操作

最近開始學習MySQL，主要是通過書籍，和看燕十八老師的視頻，然後通過博客記錄自己的學習過程。

登入資料庫

zhiniaobu@telunsu-K55VD:~$ mysql -uroot -p
Enter password: 

查看當前所有資料庫

mysql> show databases;#查看當前所有資料庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

創建資料庫ceshi

mysql> create database ceshi;#創建資料庫ceshi
Query OK, 1 row affected (0.00 sec)

mysql> show databases;#查看當前所有資料庫，會看到現在多瞭一個ceshi
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

查看資料庫ceshi的設定

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)

刪除資料庫ceshi

mysql> drop database ceshi;#刪除資料庫ceshi
Query OK, 0 rows affected (0.07 sec)

mysql> show databases;#查看當前所有資料庫，現在會發現ceshi已經不存在瞭，因為已經刪除瞭
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec

使用資料庫

mysql> create database ceshi_ku;#先建立一個庫，等下用來建表
Query OK, 1 row affected (0.00 sec)

mysql> show databases;#ceshi_ku已經成功的建立瞭
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi_ku           |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.00 sec)

mysql> use ceshi_ku;#建立表之前，首先要選擇一個庫
Database changed

查看當前庫下面的所有表

mysql> show tables;#查看當前庫下面的所有表，目前是什麼表也沒有
Empty set (0.00 sec)

創建表

mysql> create table ceshi_biao
    -> (
    -> id int(11),
    -> salary float
    -> );#建立ceshi_biao表
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;#查看當前庫下面的所有表，ceshi_biao表已經成功建立
+--------------------+
| Tables_in_ceshi_ku |
+--------------------+
| ceshi_biao         |
+--------------------+
1 row in set (0.00 sec)

查看表基本結構

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)

查看表詳細結構

mysql> show create table ceshi_biao;#查看ceshi_biao表詳細結構
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| Table      | Create Table                                                                                                                  |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
| ceshi_biao | CREATE TABLE `ceshi_biao` (
  `id` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

修改表名

mysql> alter table ceshi_biao rename to ceshi_new;#把ceshi_biao表的名字換成ceshi_new
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;#查看當前庫下面的所有表，ceshi_biao表的名字已經成功改為ceshi_new
+--------------------+
| Tables_in_ceshi_ku |
+--------------------+
| ceshi_new          |
+--------------------+
1 row in set (0.00 sec)

刪除表

mysql> drop table ceshi_new;#刪除ceshi_new表
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;#查看當前庫下面的所有表，ceshi_new表已經成功刪除
Empty set (0.01 sec)

修改編碼

mysql> set names utf8;#修改編碼為utf8
Query OK, 0 rows affected (0.00 sec)