MySQL數(shù)據(jù)庫常用命令有哪些及如何使用

本篇內(nèi)容主要講解“MySQL數(shù)據(jù)庫常用命令有哪些及如何使用”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習(xí)“MySQL數(shù)據(jù)庫常用命令有哪些及如何使用”吧!

1.對數(shù)據(jù)庫常用命令

1.連接數(shù)據(jù)庫
mysql -u用戶名 -p密碼
2.顯示已有數(shù)據(jù)庫
show databases;
3.創(chuàng)建數(shù)據(jù)庫
create database sqlname;
4.選擇數(shù)據(jù)庫
use database sqlname;
5.顯示數(shù)據(jù)庫中的表（先選擇數(shù)據(jù)庫）
show tables;
6.顯示當(dāng)前數(shù)據(jù)庫的版本信息以及連接用戶名
select version(),user();
7.刪除數(shù)據(jù)庫(刪除時沒有提示直接刪除)
drop database sqlname;

2.數(shù)據(jù)庫中對表的命令

1.創(chuàng)建表
(1)語法：
create table tablename(
字段1 數(shù)據(jù)類型 字段屬性
…
字段n
);
(2)注意：
1.創(chuàng)建表時為了防止與保留字沖突，用’'括起來
2.單行注釋：#…
多行注釋：/…/
3.創(chuàng)建表時多字段中間用英文逗號隔開，最后一行不用逗號。
(3) 字段約束和屬性
1.非空約束 not null(字段不允許為空)
2.默認約束 default(設(shè)置默認值)
3.唯一約束 unique key(uk)(設(shè)置字段的值是唯一的，可為空，但只能有一個空值)
4.主鍵約束 primary key(pk)(作為表記錄的唯一標識)
5.外鍵約束 foreign key(fk)(用于兩個表之間建立關(guān)系，需要指定引用主表的哪一字段。在數(shù)據(jù)庫的存儲引擎中InnoDB支持外鍵，MyISAM不支持外鍵。
作為外鍵的字段要求是主表中的主鍵（單字段主鍵）)

添加外鍵約束：
CONSTRAINT FK_外鍵名 FOREIGN KEY(字表中外鍵字段)REFERENCES 關(guān)聯(lián)表名 (關(guān)聯(lián)字段)。
grandid作為字表的外鍵

1.設(shè)置自增auto_increment=n,從n開始。
2.設(shè)置自增set @@ auto_increment_increment=m,步長為m。

3.多字段設(shè)置主鍵：primary key(字段1，字段2…字段n)
4.表中的注釋/說明性文字：)comment=“說明文字”;
5.設(shè)置字符集：)charset=“字符集”;
6.查看表的結(jié)構(gòu):describe’表名’/desc 表名
7.查看數(shù)據(jù)庫定義：show create database sqlname;
8.查看數(shù)據(jù)表定義：show create table tablename;
9.查看默認存儲引擎：show variables like’storage_engine%’;
11.指定表的存儲引擎：)engine=存儲引擎;
10.刪除表：drop table ‘tablename’;
11.獲取當(dāng)前日期：now();
12.修改表:
(1)修改表名：alter table 舊表名 rename 新表名；
(2)添加字段：alter table 表名 add 字段名 數(shù)據(jù)類型…;(添加新的字段)
(3)修改字段：alter table 表名 change 原字段名 新字段名 數(shù)據(jù)類型…;
(4)刪除字段：alter table 表名 drop 字段名;
(5)在創(chuàng)建完表以后添加主鍵約束：
alter table 表名 add constraint 主鍵名 primary key 表名(主鍵字段);
(6)創(chuàng)建完表以后添加外鍵約束(作為外鍵的字段要求是主表中的主鍵（單字段主鍵)）:
alter table 表名 add constraint 外鍵名 foreign key(外鍵字段) references 關(guān)聯(lián)表名 (關(guān)聯(lián)字段);

插入數(shù)據(jù)

1.插入單行數(shù)據(jù)：
insert into 表名 (字段名列表(逗號隔開)) values(值列表(逗號隔開));
2.插入多行數(shù)據(jù) ：
insert into 表名(字段名列表) values (值列表1), … ,(值列表n);
3.將查詢結(jié)果插入到新表中：
create table 新表(select 字段1, … ,from 原表);

查詢student表中的id，name，sex，phone數(shù)據(jù)插入到newstudent表中：
CREATE TABLE newstudent(SELECT id,`name`,sex,phone FROM student);

3.更新數(shù)據(jù)（修改數(shù)據(jù)）：
update 表名 set 列名=更新值 where 更新條件;

修改newstudent表中id=1001的數(shù)據(jù)名字為tom：
UPDATE newstudent SET `name`='tom' WHERE id=1001;

4.刪除數(shù)據(jù)
(1)delete from 表名 where 刪除條件;
delete 刪除的是整條數(shù)據(jù)，不會只刪除單個列。

刪除newstudent表中名字為tom的數(shù)據(jù)：
DELETE FROM newstudent WHERE `name`='tom';

(2)truncate table 刪除數(shù)據(jù)：
truncate table 刪除的是表中所有的行，但表的結(jié)構(gòu)，列，約束，索引等不會改變。不能用于有外鍵約束的表。刪除數(shù)據(jù)不能恢復(fù)。
truncate table 表名 where 刪除條件;

數(shù)據(jù)查詢

1.使用select查詢
select 列名/表達式/函數(shù)/常量 from 表名 where 查詢條件 order by 排序的列名asc/desc;
(1)查詢所有的數(shù)據(jù)行和列：
select * from 表名；
(2)查詢部分行和列：
select 列名… from 表名 where 查詢條件；
(3)在查詢中使用列的別名：
select 列名 AS 新列名 form 表名 where 查詢條件；
計算，合并得到新的列名：
select 列名1+’.’+列名2 AS 新列名 from 表名；
(4)查詢空值：
通過is null 或者 is not null 判斷列值是否為空

查詢student表中Email為空的學(xué)生姓名：
SELECT `name` FROM student WHERE Email IS NULL;

2.分組查詢

#查詢不同課程的平均分，最低分，最高分,并查詢出平均分大于80分的課程
SELECT r.subjectno,sub.`SubjectName` 課程名稱,AVG(StudentResult) 平均分,
MAX(StudentResult) 最高分,MIN(StudentResult) 最低分
FROM result r INNER JOIN `subject` sub
ON r.`SubjectNo`=sub.`SubjectNo` 
GROUP BY r.subjectno
#where AVG(StudentResult)>=80出現(xiàn)錯誤，
#分組查詢group by 在where語句后，
#group by 約束條件使用having語句
HAVING AVG(StudentResult)>=80;

常用函數(shù)

1.聚合函數(shù)：
(1）AVG (平均值):select avg(列名)from 表名
假設(shè)列名為成績 則查詢到的是表中所有成績的平均值。
（2）count 返回某字段的行數(shù)
（3）max 返回某字段的最大數(shù)
（4）min 返回某字段的最小值
（5）sum 返回某字段的和。
2.字符串函數(shù)：
（1）concat() 連接字符串s1,s2…sn為一個完整的字符串。
（2）insert(s1,p1,n,news)將字符串s1從p1位置開始，n個字符長的字串替換為字符串news。
（3）lower(s)將字符串s中的所有字符改為小寫。
（4）upper(s)將字符串s中的所有字符改為大寫。
（5）substring(s,num,len)返回字符串s的第num個位置開始長度為len的子字符串。
3.時間日期函數(shù)：
（1）獲取當(dāng)前日期：curdate();
（2）獲取當(dāng)前時間：curtime();
（3）獲取當(dāng)前日期和時間：now();
（4）返回日期date為一年中的第幾周：week(date);
（5）返回日期date的年份：year(date);
（6）返回時間time的小時值：hour(time);
（7）返回時間time的分鐘值：minute(time);
（8）返回日期參數(shù)（date1和date2之間相隔的天數(shù)）:datediff(date1,date2);
（9）計算日期參數(shù)date加上n天后的日期：adddate(date,n);
4.數(shù)學(xué)函數(shù)
（1）返回大于或等于數(shù)值x的最小整數(shù)：ceil(x);
（2）返回小于或等于數(shù)值x的最大整數(shù)：floor(x)；
（3）返回0~1之間的隨機數(shù)：rand();
order by 子句
order by子句按照一定的順序排列查詢結(jié)果，asc升序排列，desc降序排列。
limit子句
顯示指定位置指定行數(shù)的記錄。
select 字段名列表 form 表名 where 約束條件 group by分組的字段名 order by 排序列名 limit 位置偏移量,行數(shù);
#查詢學(xué)生信息里gid=1按學(xué)號升序排列前四條記錄

#查詢學(xué)生信息里gid=1按學(xué)號升序排列前四條記錄（步長）
SELECT id,`name` FROM `student1` WHERE gid=1 ORDER BY id LIMIT 4;
（查詢表里全部信息中g(shù)id=1的前四個學(xué)生）

查詢結(jié)果：

#查詢學(xué)生信息里gid=1按學(xué)號升序排列前四條記錄（位置偏移量，步長）
SELECT id,`name` FROM `student1` WHERE gid=1 ORDER BY id LIMIT 4,4;
（查詢表中全部信息gid=1前四條以后的全部信息中的前四條學(xué)生信息）

查詢結(jié)果：

模糊查詢

in子查詢******not in 子查詢
使用in關(guān)鍵字可以使父查詢匹配子查詢返回的多個單字段值。
解決使用比較運算符（=,>等），子查詢返回值不唯一錯誤信息。
like模糊查詢
LIKE語句語法格式：select * from 表名 where 字段名 like 對應(yīng)值（子串）。

它主要是針對字符型字段的，它的作用是在一個字符型字段列中檢索包含對應(yīng)子串的。

A:% 包含零個或多個字符的任意字符串： 1、LIKE’Mc%’ 將搜索以字母 Mc 開頭的所有字符串（如 McBadden）。
　　2、LIKE’%inger’ 將搜索以字母 inger 結(jié)尾的所有字符串（如 Ringer、Stringer）。
　　3、LIKE’%en%’ 將搜索在任何位置包含字母 en 的所有字符串（如 Bennet、Green、McBadden）。
　　B:_（下劃線） 任何單個字符：LIKE’_heryl’ 將搜索以字母 heryl 結(jié)尾的所有六個字母的名稱（如 Cheryl、Sheryl）。
　　C：[ ] 指定范圍 ([a-f]) 或集合 ([abcdef]) 中的任何單個字符：、
　　1，LIKE’[CK]ars[eo]n’ 將搜索下列字符串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
　　2、LIKE’[M-Z]inger’ 將搜索以字符串 inger 結(jié)尾、以從 M 到 Z 的任何單個字母開頭的所有名稱（如 Ringer）
　　***D：[^] 不屬于指定范圍 ([a-f]) 或集合 ([abcdef]) 的任何單個字符：LIKE’M[^c]%’ 將搜索以字母 M 開頭，并且第二個字母不是 c 的所有名稱（如MacFeather）。
　　E： 它同于DOS命令中的通配符，代表多個字符：cc代表cc,cBc,cbc,cabdfec等多個字符。
　　F：？同于DOS命令中的？通配符，代表單個字符 :b?b代表brb,bFb等
　　G：# 大致同上，不同的是代只能代表單個數(shù)字。k#k代表k1k,k8k,k0k 。
　　F：[!] 排除 它只代表單個字符
　　下面我們來舉例說明一下：
　　例1，查詢name字段中包含有“明”字的。
　　select * from table1 where name like ‘%明%’
　　例2，查詢name字段中以“李”字開頭。
　　select * from table1 where name like '李’
　　例3，查詢name字段中含有數(shù)字的。
　　select * from table1 where name like ‘%[0-9]%’
　　例4，查詢name字段中含有小寫字母的。
　　select * from table1 where name like ‘%[a-z]%’
　　例5，查詢name字段中不含有數(shù)字的。
　　select * from table1 where name like ‘%[!0-9]%’
　　可以自定義轉(zhuǎn)移符----》escape’自定義轉(zhuǎn)移符’
　　distinct------》去除重復(fù)項
　   between*and模糊查詢
　操作符 BETWEEN … AND 會選取介于兩個值之間的數(shù)據(jù)范圍。這些值可以是數(shù)值、文本或者日期。
　null ，not null查詢

-- 查詢手機號不為null的用戶數(shù)據(jù)
SELECT * from user where phone is not null;
 
-- 查詢手機號為null的用戶數(shù)據(jù)
SELECT * from user where phone is null;

exists 子查詢 not exists子查詢
exists子查詢用來確認后邊的查詢是否繼續(xù)進行
drop table if exists test—>判斷是否存在表test，如果存在就刪除。
not exists實現(xiàn)取反操作。對不存在對應(yīng)查詢條件的記錄。

多表連接查詢

多表連接查詢是通過各個表之間共同列的關(guān)聯(lián)性來查詢數(shù)據(jù)。
1.內(nèi)連接查詢
內(nèi)連接查詢根據(jù)表中共同的列進行匹配。取兩個的表的交集。兩個表存在主外鍵關(guān)系是通常使用內(nèi)連接查詢。
內(nèi)連接使用inner join…on 關(guān)鍵字或者where子句來進行表之間的關(guān)聯(lián)。
inner 可省略 on 用來設(shè)置條件。
（1）在where子句中指定連接條件
（2）在from中使用inner join…on關(guān)鍵字

#查詢學(xué)生姓名和成績
SELECT studentname,studentresult FROM student s,result r
WHERE s.`StudentNo`=r.`StudentNo`

#在from中使用inner join....on關(guān)鍵字
SELECT s.`StudentName`,r.`StudentResult` ,r.`SubjectNo`FROM student s
INNER JOIN result r ON s.`StudentNo`=r.`StudentNo`

兩種方法查詢結(jié)果相同。
2.外連接查詢
外連接查詢中參與連接的表有主從之分，已主表的每行數(shù)據(jù)匹配從表的數(shù)據(jù)列，將符合連接條件的數(shù)據(jù)直接返回到結(jié)果集中，對不符合連接條件的列，將被填上null值再返回到結(jié)果集中。
（1）左外連接查詢
left join…on 或者left outer join…on關(guān)鍵字進行表之間的關(guān)聯(lián)。

SELECT s.`StudentName`,r.`StudentResult` ,r.`SubjectNo`FROM student s
LEFT JOIN result r ON s.`StudentNo`=r.`StudentNo`

將沒有成績的學(xué)生成績查出。
（2）右外連接查詢
右外連接包含右表中所有的匹配行，右表中有的項在左表中沒有對應(yīng)的項將以null值填充。
right join…on 或right outer join…on關(guān)鍵字進行表之間的關(guān)聯(lián)。
（3）自連接
把一個表作為兩個表使用。

#創(chuàng)建一個表
CREATE TABLE book(
id INT(10),
sort INT(10),
books VARCHAR(10) NOT NULL
);
#插入數(shù)據(jù)
INSERT INTO book VALUES (2,1,'古文書'),
(3,1,'現(xiàn)代書'),
(4,2,'《三字經(jīng)》'),
(5,2,'《唐詩三百首》'),
(6,3,'《我與地壇》'),
(7,2,'《游大林寺》'),
(8,2,'《王右軍年減十歲時》'),
(9,3,'《致橡樹》');

#查詢結(jié)果為：
#書籍類型       書籍名
#古文書         三字經(jīng)....
#現(xiàn)代書         我與地壇....

SELECT a.books 書籍類型, b.books 書籍名  
FROM book a,book b
WHERE a.id=b.sort;

自連接查詢結(jié)果：

MySQL的事務(wù)，視圖，索引，備份和恢復(fù)

1.事務(wù)
事務(wù)是指將一系列數(shù)據(jù)操作捆綁成為一個整體進行統(tǒng)一管理。
把所有的命令作為一個整體一起向系統(tǒng)提交或者撤銷造組偶請求。
事務(wù)屬性：原子性，一致性，隔離性，持久性。
myISA存儲引擎不支持事務(wù)。

關(guān)閉事務(wù)自動提交：set autocommit=0；
（1）開始事務(wù)：begin/start transaction；
（2）提交事務(wù)：commit；
（3）回滾/撤銷事務(wù)：rollback；
恢復(fù)自動提交：set autocommit=1；
設(shè)置結(jié)果集以？？編碼格式顯示：set names ？？；
2.視圖
視圖是一種查看數(shù)據(jù)庫中一個或多個表中數(shù)據(jù)的方法。視圖是一種虛擬表，作為來自一個或多個表的行或列的子集創(chuàng)建的。視圖充當(dāng)查詢中的表篩選器的角色。
（1）創(chuàng)建視圖：create view 視圖名 as <select語句>
（2）刪除視圖：drop view 視圖名；
（3）查看視圖數(shù)據(jù)：select 。。。。。from 視圖名；
3.索引
索引類似于書的目錄，使用索引可以將數(shù)據(jù)庫程序無須對整個表掃描就可以在其中找到所需數(shù)據(jù)。
（1）普通數(shù)據(jù)：允許重復(fù)和空值。
（2）唯一索引：不允許出現(xiàn)重復(fù)?？梢杂卸鄠€唯一索引。
（3）主鍵索引：非空，唯一。刪除時drop primary key；
（4）復(fù)合索引：將多個列組合作為索引。？
（5）全文索引：可重復(fù)和空值，在char，varchar，text創(chuàng)建。
where match（列名）against （&lsquo;查找內(nèi)容&rsquo;）；
（6）空間索引：對空間數(shù)據(jù)類型的列建立的索引。

創(chuàng)建索引：
create 【索引類型】index 索引名 on 表名 （創(chuàng)建索引的列）；
或者創(chuàng)建表時之間在列后面加上索引類型。
或者修改表alter table 表名 add index 索引名 （索引列）；
刪除索引：drop index 索引名；
查看索引：show index from 表名；
4.數(shù)據(jù)庫備份和恢復(fù)
1.使用mysqldump命令備份數(shù)據(jù)庫
mysqldump -u -p 數(shù)據(jù)庫名>備份數(shù)據(jù)庫位置及名字；
表數(shù)據(jù)導(dǎo)出到文本文件
select *from 表名 where 查詢條件 into outfile 備份數(shù)據(jù)庫位置及名字；

2.使用mysql命令恢復(fù)數(shù)據(jù)庫（先創(chuàng)建新的數(shù)據(jù)庫）
mysql -u -p 新創(chuàng)建數(shù)據(jù)庫名<所要恢復(fù)數(shù)據(jù)庫位置及文件名；
source命令恢復(fù)數(shù)據(jù)庫
source 數(shù)據(jù)庫備份文件；

新建用戶

#創(chuàng)建本地用戶
CREATE USER `user`@`localhost` IDENTIFIED BY '123123';
#用戶可登陸任何遠程主機，使用通配符%
CREATE USER `user2`@`123%` IDENTIFIED BY '123123';
#對用戶進行全部權(quán)限授權(quán)
GRANT ALL ON mysql.`user` TO `user2`@`123%`;
#對已創(chuàng)建的用戶授權(quán)
GRANT SELECT,INSERT ON mysql.`user` TO `user2`@`123%`; 
#創(chuàng)建用戶時授權(quán)
GRANT SELECT,INSERT ON mysql.`user` TO `user_2`@`123%` IDENTIFIED BY '123123';
#刪除用戶user2(使用刪除語句時必須擁有數(shù)據(jù)庫全局權(quán)限或select權(quán)限)
DROP USER `user2`@`123%`;
DROP USER `user_2`@`123%`;
DROP USER `user`@`localhost`;
#mysqladmin修改超級用戶user2賬戶密碼(mysqladmin命令在cmd中使用，只能修改超級用戶密碼)
mysqladmin -u root -p PASSWORD "123456";
#修改當(dāng)前登錄用戶密碼
SET PASSWORD =PASSWORD("123456");
#修改其他用戶密碼
SET PASSWORD FOR `user2`@`123%`=PASSWORD("123456");

到此，相信大家對“MySQL數(shù)據(jù)庫常用命令有哪些及如何使用”有了更深的了解，不妨來實際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！