MySQL資料庫常用命令小結

1.對資料庫常用命令

1.連線資料庫
mysql -u使用者名稱 -p密碼
2.顯示已有資料庫
show databases;
3.建立資料庫
create database sqlname;
4.選擇資料庫
use database sqlname;
5.顯示資料庫中的表（先選擇資料庫）
show tables;
6.顯示當前資料庫的版本資訊以及連線使用者名稱
select version(),user();
7.刪除資料庫(刪除時沒有提示直接刪除)
drop database sqlname;

2.資料庫中對錶的命令

1.建立表
(1)語法：
create table tablename(
欄位1 資料型別 欄位屬性
…
欄位n
);
(2)注意：
1.建立表時為了防止與保留字衝突，用’'括起來
2.單行註釋：#…
多行註釋：/…/
3.建立表時多欄位中間用英文逗號隔開，最後一行不用逗號。
(3) 欄位約束和屬性
1.非空約束 not null(欄位不允許為空)
2.預設約束 default(設定預設值)
3.唯一約束 unique key(uk)(設定欄位的值是唯一的，可為空，但只能有一個空值)
4.主鍵約束 primary key(pk)(作為表記錄的唯一標識)
5.外來鍵約束 foreign key(fk)(用於兩個表之間建立關係，需要指定參照主表的哪一欄位。在資料庫的儲存引擎中InnoDB支援外來鍵，MyISAM不支援外來鍵。
作為外來鍵的欄位要求是主表中的主鍵（單欄位主鍵）)

新增外來鍵約束：
CONSTRAINT FK_外來鍵名 FOREIGN KEY(字表中外來鍵欄位)REFERENCES 關聯表名 (關聯欄位)。
grandid作為字表的外來鍵

1.設定自增auto_increment=n,從n開始。
2.設定自增set @@ auto_increment_increment=m,步長為m。

3.多欄位設定主鍵：primary key(欄位1，欄位2…欄位n)
4.表中的註釋/說明性文字：)comment=“說明文字”;
5.設定字元集：)charset=“字元集”;
6.檢視表的結構:describe’表名’/desc 表名
7.檢視資料庫定義：show create database sqlname;
8.檢視資料表定義：show create table tablename;
9.檢視預設儲存引擎：show variables like’storage_engine%’;
11.指定表的儲存引擎：)engine=儲存引擎;
10.刪除表：drop table ‘tablename’;
11.獲取當前日期：now();
12.修改表:
(1)修改表名：alter table 舊錶名 rename 新表名；
(2)新增欄位：alter table 表名 add 欄位名 資料型別…;(新增新的欄位)
(3)修改欄位：alter table 表名 change 原欄位名 新欄位名 資料型別…;
(4)刪除欄位：alter table 表名 drop 欄位名;
(5)在建立完表以後新增主鍵約束：
alter table 表名 add constraint 主鍵名 primary key 表名(主鍵欄位);
(6)建立完表以後新增外來鍵約束(作為外來鍵的欄位要求是主表中的主鍵（單欄位主鍵)）:
alter table 表名 add constraint 外來鍵名 foreign key(外來鍵欄位) references 關聯表名 (關聯欄位);

插入資料

1.插入單行資料：
insert into 表名 (欄位名列表(逗號隔開)) values(值列表(逗號隔開));
2.插入多行資料 ：
insert into 表名(欄位名列表) values (值列表1), … ,(值列表n);
3.將查詢結果插入到新表中：
create table 新表(select 欄位1, … ,from 原表);

查詢student表中的id，name，sex，phone資料插入到newstudent表中：
CREATE TABLE newstudent(SELECT id,`name`,sex,phone FROM student);

3.更新資料（修改資料）：
update 表名 set 列名=更新值 where 更新條件;

修改newstudent表中id=1001的資料名字為tom：
UPDATE newstudent SET `name`='tom' WHERE id=1001;

4.刪除資料
(1)delete from 表名 where 刪除條件;
delete 刪除的是整條資料，不會只刪除單個列。

刪除newstudent表中名字為tom的資料：
DELETE FROM newstudent WHERE `name`='tom';

(2)truncate table 刪除資料：
truncate table 刪除的是表中所有的行，但表的結構，列，約束，索引等不會改變。不能用於有外來鍵約束的表。刪除資料不能恢復。
truncate table 表名 where 刪除條件;

資料查詢

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

查詢student表中Email為空的學生姓名：
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出現錯誤，
#分組查詢group by 在where語句後，
#group by 約束條件使用having語句
HAVING AVG(StudentResult)>=80;

常用函數

1.聚合函數：
(1）AVG (平均值):select avg(列名)from 表名
假設列名為成績 則查詢到的是表中所有成績的平均值。
（2）count 返回某欄位的行數
（3）max 返回某欄位的最大數
（4）min 返回某欄位的最小值
（5）sum 返回某欄位的和。
2.字串函數：
（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.時間日期函數：
（1）獲取當前日期：curdate();
（2）獲取當前時間：curtime();
（3）獲取當前日期和時間：now();
（4）返回日期date為一年中的第幾周：week(date);
（5）返回日期date的年份：year(date);
（6）返回時間time的小時值：hour(time);
（7）返回時間time的分鐘值：minute(time);
（8）返回日期引數（date1和date2之間相隔的天數）:datediff(date1,date2);
（9）計算日期引數date加上n天后的日期：adddate(date,n);
4.數學函數
（1）返回大於或等於數值x的最小整數：ceil(x);
（2）返回小於或等於數值x的最大整數：floor(x)；
（3）返回0~1之間的亂數：rand();
order by 子句
order by子句按照一定的順序排列查詢結果，asc升序排列，desc降序排列。
limit子句
顯示指定位置指定行數的記錄。
select 欄位名列表 form 表名 where 約束條件 group by分組的欄位名 order by 排序列名 limit 位置偏移量,行數;
#查詢學生資訊裡gid=1按學號升序排列前四條記錄

#查詢學生資訊裡gid=1按學號升序排列前四條記錄（步長）
SELECT id,`name` FROM `student1` WHERE gid=1 ORDER BY id LIMIT 4;
（查詢表裡全部資訊中gid=1的前四個學生）

查詢結果：

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

查詢結果：

模糊查詢

in子查詢******not in 子查詢
使用in關鍵字可以使父查詢匹配子查詢返回的多個單欄位值。
解決使用比較運運算元（=,>等），子查詢返回值不唯一錯誤資訊。
like模糊查詢
LIKE語句語法格式：select * from 表名 where 欄位名 like 對應值（子串）。

它主要是針對字元型欄位的，它的作用是在一個字元型欄位列中檢索包含對應子串的。

A:% 包含零個或多個字元的任意字串： 1、LIKE’Mc%’ 將搜尋以字母 Mc 開頭的所有字串（如 McBadden）。
　　2、LIKE’%inger’ 將搜尋以字母 inger 結尾的所有字串（如 Ringer、Stringer）。
　　3、LIKE’%en%’ 將搜尋在任何位置包含字母 en 的所有字串（如 Bennet、Green、McBadden）。
　　B:_（下劃線） 任何單個字元：LIKE’_heryl’ 將搜尋以字母 heryl 結尾的所有六個字母的名稱（如 Cheryl、Sheryl）。
　　C：[ ] 指定範圍 ([a-f]) 或集合 ([abcdef]) 中的任何單個字元：、
　　1，LIKE’[CK]ars[eo]n’ 將搜尋下列字串：Carsen、Karsen、Carson 和 Karson（如 Carson）。
　　2、LIKE’[M-Z]inger’ 將搜尋以字串 inger 結尾、以從 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：# 大致同上，不同的是代只能代表單個數位。k#k代表k1k,k8k,k0k 。
　　F：[!] 排除 它只代表單個字元
　　下面我們來舉例說明一下：
　　例1，查詢name欄位中包含有“明”字的。
　　select * from table1 where name like ‘%明%’
　　例2，查詢name欄位中以“李”字開頭。
　　select * from table1 where name like '李’
　　例3，查詢name欄位中含有數位的。
　　select * from table1 where name like ‘%[0-9]%’
　　例4，查詢name欄位中含有小寫字母的。
　　select * from table1 where name like ‘%[a-z]%’
　　例5，查詢name欄位中不含有數位的。
　　select * from table1 where name like ‘%[!0-9]%’
　　可以自定義轉移符----》escape’自定義轉移符’
　　distinct------》去除重複項
　   between*and模糊查詢
　操作符 BETWEEN … AND 會選取介於兩個值之間的資料範圍。這些值可以是數值、文字或者日期。
　null ，not null查詢

-- 查詢手機號不為null的使用者資料
SELECT * from user where phone is not null;
 
-- 查詢手機號為null的使用者資料
SELECT * from user where phone is null;

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

多表連線查詢

多表連線查詢是通過各個表之間共同列的關聯性來查詢資料。
1.內連線查詢
內連線查詢根據表中共同的列進行匹配。取兩個的表的交集。兩個表存在主外來鍵關係是通常使用內連線查詢。
內連線使用inner join…on 關鍵字或者where子句來進行表之間的關聯。
inner 可省略 on 用來設定條件。
（1）在where子句中指定連線條件
（2）在from中使用inner join…on關鍵字

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

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

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

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

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

#建立一個表
CREATE TABLE book(
id INT(10),
sort INT(10),
books VARCHAR(10) NOT NULL
);
#插入資料
INSERT INTO book VALUES (2,1,'古文書'),
(3,1,'現代書'),
(4,2,'《三字經》'),
(5,2,'《唐詩三百首》'),
(6,3,'《我與地壇》'),
(7,2,'《遊大林寺》'),
(8,2,'《王右軍年減十歲時》'),
(9,3,'《致橡樹》');

#查詢結果為：
#書籍型別       書籍名
#古文書         三字經....
#現代書         我與地壇....

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

自連線查詢結果：

MySQL的事務，檢視，索引，備份和恢復

1.事務
事務是指將一系列資料操作捆綁成為一個整體進行統一管理。
把所有的命令作為一個整體一起向系統提交或者復原造組偶請求。
事務屬性：原子性，一致性，隔離性，永續性。
myISA儲存引擎不支援事務。

關閉事務自動提交：set autocommit=0；
（1）開始事務：begin/start transaction；
（2）提交事務：commit；
（3）回滾/復原事務：rollback；
恢復自動提交：set autocommit=1；
設定結果集以？？編碼格式顯示：set names ？？；
2.檢視
檢視是一種檢視資料庫中一個或多個表中資料的方法。檢視是一種虛擬表，作為來自一個或多個表的行或列的子集建立的。檢視充當查詢中的表篩選器的角色。
（1）建立檢視：create view 檢視名 as <select語句>
（2）刪除檢視：drop view 檢視名；
（3）檢視檢視資料：select 。。。。。from 檢視名；
3.索引
索引類似於書的目錄，使用索引可以將資料庫程式無須對整個表掃描就可以在其中找到所需資料。
（1）普通資料：允許重複和空值。
（2）唯一索引：不允許出現重複。可以有多個唯一索引。
（3）主鍵索引：非空，唯一。刪除時drop primary key；
（4）複合索引：將多個列組合作為索引。？
（5）全文索引：可重複和空值，在char，varchar，text建立。
where match（列名）against （‘查詢內容’）；
（6）空間索引：對空間資料型別的列建立的索引。

建立索引：
create 【索引型別】index 索引名 on 表名 （建立索引的列）；
或者建立表時之間在列後面加上索引型別。
或者修改表alter table 表名 add index 索引名 （索引列）；
刪除索引：drop index 索引名；
檢視索引：show index from 表名；
4.資料庫備份和恢復
1.使用mysqldump命令備份資料庫
mysqldump -u -p 資料庫名>備份資料庫位置及名字；
表資料匯出到文字檔案
select *from 表名 where 查詢條件 into outfile 備份資料庫位置及名字；

2.使用mysql命令恢復資料庫（先建立新的資料庫）
mysql -u -p 新建立資料庫名<所要恢復資料庫位置及檔名；
source命令恢復資料庫
source 資料庫備份檔案；

新建使用者

#建立本地使用者
CREATE USER `user`@`localhost` IDENTIFIED BY '123123';
#使用者可登陸任何遠端主機，使用萬用字元%
CREATE USER `user2`@`123%` IDENTIFIED BY '123123';
#對使用者進行全部許可權授權
GRANT ALL ON mysql.`user` TO `user2`@`123%`;
#對已建立的使用者授權
GRANT SELECT,INSERT ON mysql.`user` TO `user2`@`123%`; 
#建立使用者時授權
GRANT SELECT,INSERT ON mysql.`user` TO `user_2`@`123%` IDENTIFIED BY '123123';
#刪除使用者user2(使用刪除語句時必須擁有資料庫全域性許可權或select許可權)
DROP USER `user2`@`123%`;
DROP USER `user_2`@`123%`;
DROP USER `user`@`localhost`;
#mysqladmin修改超級使用者user2賬戶密碼(mysqladmin命令在cmd中使用，只能修改超級使用者密碼)
mysqladmin -u root -p PASSWORD "123456";
#修改當前登入使用者密碼
SET PASSWORD =PASSWORD("123456");
#修改其他使用者密碼
SET PASSWORD FOR `user2`@`123%`=PASSWORD("123456");

到此這篇關於MySQL資料庫命令的文章就介紹到這了,更多相關MySQL資料庫命令內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！