MySQL的一些基本操作

標籤：des   style   blog   io   使用   ar   strong   for   資料   

近期開始學習MySQL，主要是通過書籍，和看燕十八老師的視頻，然後通過部落格記錄自己的學習過程。登入資料庫

[email protected]:~$ mysql -uroot -pEnter password: 

查看當前全部資料庫

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

建立資料庫

mysql> create database ceshi;#建立資料庫ceshiQuery 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)

查看資料庫的定義

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)

刪除資料庫

mysql> drop database ceshi;#刪除資料庫ceshiQuery 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_newQuery 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)###########2種方法################mysql> rename table ceshi_biao to ceshi_new;#把ceshi_biao表的名字換成ceshi_newQuery OK, 0 rows affected (0.03 sec)mysql> show tables;#查看當前庫以下的全部表，ceshi_biao表的名字已經成功改為ceshi_new+--------------------+| Tables_in_ceshi_ku |+--------------------+| ceshi_new          |+--------------------+1 row in set (0.00 sec)

往表裡面插入資料

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;#這個表就是員工號碼以及員工的工資，剛才就僅僅插入了3條+------+--------+| id   | salary |+------+--------+|   11 | 400.56 ||   12 | 600.07 ||   13 | 800.45 |+------+--------+3 rows in set (0.00 sec

清空表

mysql> truncate ceshi_new;#清空表，不是刪除表Query OK, 0 rows affected (0.04 sec)mysql> select * from ceshi_new;#查看內容Empty 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;#改動編碼為utf8Query OK, 0 rows affected (0.00 sec)

MySQL的一些基本操作