爆肝整理MySQL資料庫指令合集（總結分享）

這篇文章為大家帶來了關於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 關聯表名(關聯欄位)。
爆肝整理MySQL資料庫指令合集（總結分享）作為字表的外鍵


# 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
（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%`; 

#建立使用者時授權

#刪除使用者user2(使用刪除語句時必須擁有資料庫全域權限或select權限)

以上是爆肝整理MySQL資料庫指令合集（總結分享）的詳細內容。更多資訊請關注PHP中文網其他相關文章！