Mysql常用命令操作小結

標籤：

Mysql操作大全

一、概述

    SQL（Structured Query Language）語言的全稱是結構化查詢語言 (SQL)。資料庫管理系統通過SQL語言來管理資料庫中的資料。

    SQL語言分為三個部分：資料定義語言 (Data Definition Language)（Data DefinitionLanguage，簡稱為DDL）、資料操作語言（DataManipulation Language，簡稱為DML）和資料控制語言（Data Control Language，簡稱為DCL）。分別如下:

DDL語句：CREATE、ALTER、DROP

DML語句：update、insert、delete、select

DCL語句：是資料庫控制功能。是用來設定或更改資料庫使用者或角色許可權的語句，包括（grant,deny,revoke等）語句

    MySQL是一個關係型資料庫管理系統，由於其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，許多互連網公司選擇了MySQL作為後端資料庫。

       MySQL資料庫的優點：

       1、多語言支援：Mysql為C、C++、Python、Java、Perl、PHP、Ruby等多種程式設計語言提供了API，訪問和使用方便。

       2、可以移植性好：MySQL是跨平台的。

       3、免費開源。

       4、高效：MySql的核心程式採用完全的多線程編程。

       5、支援大量資料查詢和儲存：Mysql可以承受大量的並發訪問。

註：mysql的命令及相關操作，跟sql都極為相似。

二、MySQL程式常用命令

1、常用程式命令：

顯示所有資料庫：show databases;

選定預設資料庫：use dbname;

顯示預設資料庫中所有表：show tables;

2、建立新據庫

文法： create database 資料庫名

例：CREATE DATABASE Students CHARACTER set utf8;

3、刪除資料庫

drop database 資料庫名

例：Drop database 資料庫名

4、建立表

文法：create table 表名（

列名1 列類型 [<列的完整性條件約束>],

列名2 列類型 [<列的完整性條件約束>],

 ... ... );

例：建立students表

use Students;

CREATE TABLE Students(

id INT(10) PRIMARY key auto_increment,

name VARCHAR(20) NOT NULL,

sex VARCHAR(4),

age INT(10),

class VARCHAR(20),

addr VARCHAR(50)

);

列資料表條件約束：

• PRIMARY KEY 主碼約束(主鍵)

• UNIQUE 唯一性限制式

• NOT NULL 非空值約束

• AUTO_INCREMENT 用於整數列預設自增1

• UNSIGNED 不帶正負號的整數

• DEFAULT default_value 預設值約束

• DEFAULT cur_timestamp 建立新記錄時預設儲存目前時間

（僅適用timestamp資料列）

• ON UPDATE cur_timestamp 修改記錄時預設儲存目前時間

（僅適用timestamp資料列）

• CHARACTER SET name 指定字元集（僅適用字串）

5、修改表操作

改表名：

ALTER TABLE 舊錶名 RENAME [TO] 新表名 ;

Alter table school rename school2;

改表欄位類型：

ALTER TABLE 表名 MODIFY 屬性名稱 資料類型 ;

Alter table school modify school _name char(20);

加欄位：

ALTER TABLE 表名 ADD 屬性名稱1 資料類型 [完整性條件約束條件]

[FIRST | AFTER 屬性名稱2] ;

Alter table school add addr varchar(50) not null first;

加外鍵：

alter table 表名 add constraint FK_ID foreign key(

你的外鍵欄位名) REFERENCES 外表表名(對應的表

的主鍵欄位名);

alter table xiaodi add constraint FK_1 foreign

key(dage_id) REFERENCES dage(id);

刪除外鍵約束：

ALTER TABLE 表名 DROP FOREIGN KEY 外鍵別名 ;

alter table xiaodi drop foreign key FK_1;

刪欄位：

ALTER TABLE 表名 DROP 欄位名 ;

Alter table school drop addr;

清空表內容：

Truncate table;

Truncate students;

6、刪除表

DROP TABLE 表名;

drop table school;

7、插入表內容

第一種方式是不指定具體的欄位名。第二種方式是列出表

的所有欄位。

1．INSERT語句中不指定具體的欄位名

2．INSERT語句中列出所有欄位

例1：

insert into teacher values(‘001‘,‘張三‘,‘11000000000‘);

insert into teacher values(‘002’,‘李四‘,‘12000000000‘);

insert into teacher values(‘003‘,‘王五‘,‘13000000000‘);

INSERT INTO 表名(屬性1, 屬性2, … , 屬性m)

VALUES(值1,值2, …, 值m);

例2：

insert into classes(class_no,class_name,department_name) values(null,‘一年級‘, ‘aaa

‘);

insert into classes(class_no,class_name,department_name) values(null,‘一年級‘, ‘aaa‘);

insert into classes(class_no,class_name,department_name) values(null,‘二年級‘, ‘ccc‘);

8、查詢操作：

一般查詢：

1．列出表的所有欄位

2．使用“*”查詢所有欄位

select * from students;

多表查詢：

多表串連可以通過join關鍵字來串連，也可以直接用關聯表中相同的id來進行關聯；

Join：

Left join：左串連， 串連兩張表，以左邊表的資料匹配右邊表中的資料，如果左邊

表中的資料在右邊表中沒有，會顯示左邊表中的資料。

Right join：右串連，串連兩張表，以右邊表的資料匹配左邊表中的資料，如果左邊

表中的資料在左邊邊表中沒有，會顯示右邊表中的資料。

Inner join：內串連，串連兩張表，匹配兩張表中的資料，和前面兩個不同的是只

顯示匹配的資料。

select a.name 學生姓名,b.score 學產生績 from students a left join score b on

a.id=b.student_id;

select a.name 學生姓名,b.score 學產生績 from students a right join score b on

a.id=b.student_id;

select a.name 學生姓名,b.score 學產生績 from students a INNER join score b on

a.id=b.student_id;

select a.name 學生姓名,b.score 學產生績 from students a,score b where a.id=b.student_id;

帶IN關鍵字的查詢：

[ NOT ] IN ( 元素1, 元素2, …, 元素n )

select * from users where id in (1 ,2);

select * from users where id not in (1 ,2);

帶OR的多條件查詢：

條件運算式1 OR 條件運算式2 [ …OR 條件運算式n ]

其中，OR可以用來串連兩個條件運算式。而且，可以

同時使用多個OR關鍵字，這樣可以串連更多的條件表達。

select * from student where id=1 or id=2;

select * from student where id!=1 or id!=2;

帶AND的多條件查詢:

條件運算式1 AND 條件運算式2 [ … AND 條件運算式n ]

Select * from users where id >100 and sex = 2;

Select * from users where id >100 and sex = 2 and addr not null;

distinct來剃重：

select distinct phone from classes;

limit關鍵字限制條數:

Select * from users limit 5;

Select * from users limit 10,20;

BETWEEN AND的範圍查詢:

[ NOT ] BETWEEN 取值1 AND 取值2

Select * from students where score between 60 and 100;

LIKE的字元匹配查詢:

[ NOT ] LIKE ‘字串‘

select * from student where name like ‘張_‘;

 

查詢空值:

IS [ NOT ] NULL

Select * from users where addr is null;

Select * from users where sex is not null;

表結果排序：

ORDER BY 屬性名稱 [ ASC | DESC ]

Select * from students where sex = ‘女‘ order by score;

彙總函式查詢

COUNT()函數統計記錄的條數：

SELECT COUNT(*) FROM employee ;

select count(*) 學生人數 from student;

SUM()函數求和函數

select sum(score) 總成績 from choose;

select student.name 學生名稱,sum(score.score) 學生總成績 from student,score where student.id = score.student_id;

AVG()函數是平均值的函數

select student.name 學生名稱,avg(score.score) 學生總成績from student,score where student.id = score.student_id;

MAX()函數求最大值的函數

select student.name 學生名稱,max(score.score) 學生總成績 from student,score where student.id = score.student_id;

MIN()函數求最小值的函數

select max(score) 最高分,min(score) 最低分 from score;

group by子句：

GROUP BY關鍵字可以將查詢結果按某個欄位或多個字

段進行分組。欄位中值相等的為一組。其文法規則如下：

GROUP BY 屬性名稱 [ HAVING 條件運算式 ]

1．單獨使用GROUP BY關鍵字來分組

2．GROUP BY關鍵字與集合函數一起使用

3．GROUP BY關鍵與HAVING一起使用

4．按多個欄位進行分組

註：一般與彙總函式一起用

例如把學生表中的男生和女生分成兩組。

select * from students GROUP BY sex;

having子句：

having子句用於設定分組或彙總函式的過濾篩選條件，having子句通常與group by子句一起使用。having子句文法格式與where子句文法格式類似，having子句文法格式如下。

Having 條件運算式

其中條件運算式是一個邏輯運算式，用於指定分組後的篩選條件。

例如查詢1班男女學生的人數。

SELECT a.sex,count(a.id),b.class_name from students a ,class b

where a.id=b.student_id GROUP BY a.sex HAVING b.class_name =‘一班‘;

union合并結果集：

文法：select 欄位列表1 from table1 union [all] select 欄位列表2 from table2...

union 與 union all 的區別：

當使用union時，MySQL 會篩選掉select結果集中重複的記

錄（在結果集合并後會對新產生的結果集進行排序運算，

效率稍低）。而使用union all時，MySQL會直接合并兩個

結果集，效率高於union。如果可以確定合并前的兩個結

果集中不包含重複的記錄，建議使用union all。

select name 姓名,sex 性別,phone 電話 from students UNION select

teacher_name,sex,mobile from teacher;

比較子：

通過這些比較子，可以判斷表中的哪些記錄是合格。

1．運算子“=”

2．運算子“<>”和“!=”

3．運算子“>”

4．運算子“>=”

5．運算子“<”

6．運算子“<=”

三、索引

索引是建立在表上的，是對資料庫表中一列或多列的值進行排序的一種結

構。索引可以提高查詢的速度。

建立索引的文法格式：

– 建立普通索引：

create index 索引名稱 on 表名(列)

alter table 表名 add index 索引名稱 (列)

– 建立唯一索引：

create unique index 索引名稱 on 表名(列名）

alter table 表名 add unique index 索引名稱 (列)

例子：

– 給students 表的 phone加上唯一索引

– Create unqiue index st_phone on students(phone);

– 給students表的name加上普通索引

– Create index st_name on students(name);

– 給訂單表中的訂單狀態和使用者id加上複合式索引

– Create index status_user on orders(status,user_id)

刪除索引的文法格式：

DROP INDEX 索引名 ON 表名 ;

drop index complex_index on book;

四、視圖

視圖是從一個或多個表中匯出來的表，是一種虛擬存在的表。

建立視圖的文法格式

Create view 視圖名稱（視圖列1，視圖列2） as select 語句 建立一個視圖，只顯示bug表中的bugid，bug標題，bug狀態，單表 視圖

create view bug_view (id,title,status) as select id,title,bug_status from

bf_bug_info;

建立一個視圖，顯示bug表中的bugid，bug標題，bug狀態，bug創

建者，歸屬產品，多表視圖

create view bug_view_new (bug_id,title,tester,bug_status,product_name) as select

a.id,a.title,b.realname,a.bug_status,c.name from bf_bug_info a,bf_test_user

b,bf_product c where a.created_by=b.id and a.product_id=c.id;

修改視圖:

CREATE OR REPLACE VIEW 語句修改

– 文法格式：

• CREATE OR REPLACE VIEW 視圖名稱 (列1,列2) as select語句

– 樣本，把上面建立的視圖修改成只顯示10條的bug標題：

• create or replace view bug_view(title) as select title from

bf_bug_info limit 10;

• Alter view 語句修改

– 文法格式

• Alter view 視圖名稱(列1,列2) as select語句

– 樣本，把上面建立的視圖修改成只顯示15條的bugid和標題 ：

• alter view bug_view(id,title) as select id,title from

bf_bug_info limit 15;

刪除視圖：

DROP VIEW [ IF EXISTS] 視圖名列表

舉例，刪除上面建立的兩個視圖

DROP VIEW IF EXISTS bug_view,bug_view_new;

五、預存程序和函數

建立預存程序和函數是指將經常使用的一組SQL語句的組合在一起，並將這些SQL語句當作一個整體儲存在MySQL伺服器中。

建立預存程序的基本形式如下：

delimiter $$;

CREATE PROCEDURE 名稱(參數列表)

BEGIN

SQL語句塊

End

$$;

delimiter;

例子：

delimiter $$;

CREATE PROCEDURE test_p()

begin

Select * from bf_bug_info limit 10;

End

$$;

Delimiter;

– Call test_p;

建立函數文法：

定義函數的格式如下：

create function 函數名( 變數1，變數2.....)

returns 資料類型

begin

......執行的程式碼

return 資料;

end;

例子：

delimiter $$;

create FUNCTION get_stuid(s_name varchar(20))

RETURNS int

begin

declare num int;

select id from students where name=s_name into num;

return num;

end

$$;

delimiter ;

調用 select get_stuid(‘aaa‘);

六、觸發器

   觸發器（TRIGGER）是由事件來觸發某個操作。這些事件包括INSERT語句、UPDATE語句和DELETE語句。當資料庫系統執行這些事件時，就會啟用觸發器執行相應的操作。

建立觸發器：

MySQL中，觸發器觸發的執行語句可能有多個。建立有多個執行語句的觸發器的基本形式如下：

CREATE TRIGGER 觸發器名 BEFORE | AFTER 觸發

事件

ON 表名 FOR EACH ROW

BEGIN

執行語句列表

END;

例子：在students表中刪除一條資料後，從score表中也把它的成績刪除

delimiter $$;

create trigger del_score after delete

on students for each row

begin

delete from socre where student_id=old.id;

end;

$$;

delimiter ;

Delet from studens where id =1;

查看觸發器：

show triggers語句

– show triggers;

 show create 語句

– show create trigger 觸發器名;

從information_schema查看

– SELECT * FROM information_schema.triggers where

TRIGGER_NAME=‘預存程序名稱‘;

刪除觸發器：

DROP TRIGGER 觸發器名;

七、事件

   事件（event），它類似與定時任務（crontab），但內部機制卻完全不同。你可以建立事件，它會在某個特定時間或時間間隔執行一次預先寫好的SQL代碼。通常的方式就是將複雜的SQL語句封裝到一個預存程序中，然後調用一下即可。

建立事件：

文法格式

create event 事件名稱 on schedule 執行頻率

Starts 開始時間

ends 停止時間

do

Sql語句

執行個體：有一張暫存資料表stu_tmp,寫一個事件實現每30分鐘刪一下這個表裡的資料

• delimiter $$;

• create event del_tmp on schedule every 30

MINUTE

• do

• delete from stu_tmp;

• $$;

• delimiter ;

查看事件：

show event語句

– show triggers;

• show create 語句

– show create event 事件名;

• 從information_schema查看

– select * from information_schema.EVENTS;

刪除事件：

Drop event 事件名稱;

 

Mysql常用命令操作小結