本篇內(nèi)容主要講解“MySQL管理數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)的基本操作是什么”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL管理數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)的基本操作是什么”吧!
成都創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比東光網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式東光網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋東光地區(qū)。費(fèi)用合理售后完善，10余年實(shí)體公司更值得信賴。
注意：MySQL數(shù)據(jù)庫命令不區(qū)分大小寫，但在Mac端如果想使用tab鍵自動補(bǔ)全命令，就必須使用大寫。
1、數(shù)據(jù)庫管理
1.1 create 創(chuàng)建數(shù)據(jù)庫
mysql> create database test; Query OK, 1 row affected (0.50 sec)
1.2 show 查看所有數(shù)據(jù)庫
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | | test | +--------------------+ 5 rows in set (0.32 sec)
1.3 alter 修改數(shù)據(jù)庫
alter命令修改數(shù)據(jù)庫編碼：
一般我們設(shè)置的字符集使用的都是UTF8，若發(fā)現(xiàn)數(shù)據(jù)庫編碼不是UTF8，可使用該語句更改數(shù)據(jù)庫編碼：
mysql> alter database test character set utf8; Query OK, 1 row affected (0.01 sec)
1.4 use 使用數(shù)據(jù)庫
mysql> use test; Database changed
1.5 查看當(dāng)前使用的數(shù)據(jù)庫
mysql> select database(); +------------+ | database() | +------------+ | test | +------------+ 1 row in set (0.00 sec)
1.6 drop 刪除數(shù)據(jù)庫
mysql> drop database test; Query OK, 0 rows affected (0.00 sec)
2、數(shù)據(jù)庫表管理
我們先創(chuàng)建一個數(shù)據(jù)庫，以供我們后面使用：
mysql> create database test; Query OK, 1 row affected (0.06 sec)
創(chuàng)建后，使用use命令進(jìn)入數(shù)據(jù)庫：
mysql> use test; Database changed
2.1 create 創(chuàng)建表
mysql> create table test(id int auto_increment primary key, -> name varchar(20) not null, -> age int not null, -> birthday datetime); Query OK, 0 rows affected (0.14 sec)
2.2 show 顯示表
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | test | +----------------+ 1 row in set (0.00 sec)
2.3 desc 查看表結(jié)構(gòu)
mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.01 sec)
2.4 alter 修改表結(jié)構(gòu)（增、刪、改）
2.4.1 add 在表中添加字段（列）
mysql> alter table test add star bool; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0
注意：在MySQL中布爾類型會自動轉(zhuǎn)變?yōu)閠inyint(1)類型
用desc查看可得到：
mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | | star | tinyint(1) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
2.4.2 modify 修改表字段（列）
mysql> alter table test modify star int; Query OK, 0 rows affected (0.43 sec) Records: 0 Duplicates: 0 Warnings: 0
注意：int可以指定長度，也可以不指定，不指定時默認(rèn)長度為int(11)，修改后再次用desc查看：
mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | | star | int(11) | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
2.4.3 drop 刪除表字段（列）
mysql> alter table test drop column star; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0
查看表結(jié)構(gòu)可以看到?jīng)]有star字段了：
mysql> desc test; +----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | | birthday | datetime | YES | | NULL | | +----------+-------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
2.4.4 rename 重命名表名
mysql> rename table test to people; Query OK, 0 rows affected (0.01 sec)
再次查看表格，發(fā)現(xiàn)表名已改變：
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | people | +----------------+ 1 row in set (0.00 sec)
2.5 create 利用已有數(shù)據(jù)創(chuàng)建新表
mysql> create table newpeople select * from people; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0
查看數(shù)據(jù)庫存在的表發(fā)現(xiàn)已經(jīng)有了：
mysql> show tables; +----------------+ | Tables_in_test | +----------------+ | newpeople | | people | +----------------+ 2 rows in set (0.00 sec)
3、數(shù)據(jù)的操作及管理
3.1 增加數(shù)據(jù)（增）
mysql> insert into people values(null,'Anny',22,'1990-09-09'); Query OK, 1 row affected (0.00 sec)
用select查詢：
mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 1 | Anny | 22 | 1990-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec)
3.2 刪除數(shù)據(jù)（刪）
mysql> delete from people where name='Anny'; Query OK, 1 row affected (0.00 sec)
再用select查詢，發(fā)現(xiàn)沒有刪除的數(shù)據(jù)了：
mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Lisa | 20 | 1992-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec)
3.3 修改數(shù)據(jù)（改）
mysql> update people set name='Anny' where name='Lisa'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0
再查數(shù)據(jù)可以看到Lisa名字變?yōu)閍nny了：
mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec)
3.4 查詢數(shù)據(jù)（查）
最簡單的就是下面的語句：
mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | +----+------+-----+---------------------+ 1 row in set (0.00 sec)
* 表示所有字段，也可以指定字段查詢，如下：
mysql> select name from people; +------+ | name | +------+ | Anny | +------+ 1 row in set (0.00 sec)
4、管理視圖
4.1 創(chuàng)建視圖
mysql> create view people_view(name,age) as select name,age from people; Query OK, 0 rows affected (0.00 sec)
創(chuàng)建成功后可以查看視圖：
mysql> select * from people_view; +------+-----+ | name | age | +------+-----+ | Anny | 20 | +------+-----+ 1 row in set (0.00 sec)
可以用desc 查看視圖字段：
mysql> desc people_view; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | NO | | NULL | | | age | int(11) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
4.2 替換視圖
創(chuàng)建或替換原有視圖：
mysql> create or replace view people_view (id,name,age) as select id,name,age from people; Query OK, 0 rows affected (0.00 sec)
查看現(xiàn)有視圖：
mysql> select * from people_view; +----+------+-----+ | id | name | age | +----+------+-----+ | 2 | Anny | 20 | | 3 | Lisa | 22 | +----+------+-----+ 2 rows in set (0.00 sec)
4.3 操作視圖
當(dāng)視圖有變化時，對應(yīng)的表格也會跟著改變，即操作視圖就是操作表格，我們在視圖中插入一條數(shù)據(jù)：
重慶分公司
重慶分公司
mysql> insert into people_view values('Lisa',22);
Query OK, 1 row affected (0.08 sec)插入成功后，我們查看視圖數(shù)據(jù)：
mysql> select * from people_view; +------+-----+ | name | age | +------+-----+ | Anny | 20 | | Lisa | 22 | +------+-----+ 2 rows in set (0.00 sec)
可以看到視圖中已經(jīng)有了，再查看我們表格數(shù)據(jù)：
mysql> select * from people; +----+------+-----+---------------------+ | id | name | age | birthday | +----+------+-----+---------------------+ | 2 | Anny | 20 | 1992-09-09 00:00:00 | | 3 | Lisa | 22 | NULL | +----+------+-----+---------------------+ 2 rows in set (0.00 sec)
發(fā)現(xiàn)也有了對應(yīng)的數(shù)據(jù)
4.4 刪除視圖：
mysql> drop view people_view; Query OK, 0 rows affected (0.00 sec)
到此，相信大家對“MySQL管理數(shù)據(jù)庫、數(shù)據(jù)表、數(shù)據(jù)的基本操作是什么”有了更深的了解，不妨來實(shí)際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！
