操作Mysql數據庫的基本流程

本文主要給大家簡單講講操作Mysql數據庫的基本流程，相關專業術語大家可以上網查查或者找一些相關書籍補充一下，這里就不涉獵了，直奔主題，希望可以給大家帶來一些實際幫助。

一、MySql數據庫表操作

1、操作數據庫

創建數據庫：create database 數據庫名;

刪除數據庫：drop database 數據庫名;

修改數據庫編碼格式：alter database 數據庫名 character set 要修改的字符集名稱;

查詢數據庫：show databases; 查看所有數據庫名稱

選擇數據庫：use 數據庫名;

2、操作表

創建表：create table 表名(

列名1 數據類型1,

列名2 數據類型2,...

);

常用列的數據類型：int double char varchar(字節長度) date:yyyy-MM-dd time: hh:mm:ss datetime: yyyy-MM-dd hh:mm:ss

刪除表：drop table 表名;

修改表：

添加列：alter table 表名 add 列名 數據類型;

修改列數據類型：alter table 表名 modify 列名 數據類型;

修改列名：alter table 表名 change 舊表名 新表名 數據類型;

刪除列：alter table 表名 drop 列名;

修改表名稱：alter table 舊表名 rename to 新表名;

查詢表：

show tables; 查詢數據庫下所有的表的名稱

desc 表名; 查詢該表的表結構

3、操作表中數據

增加數據：

insert into 表名(列名1,列名2,...) values(值1,值2,....);

insert into 表名 values(按順序給出所有列對應的值);

刪除數據：

delete from 表名 where 限定條件;

delete from 表名; -- 刪除全部無法刪除自增長約束，可以回滾,而truncate table不可以回滾

truncate table 表名; -- 刪除全部重置自增長約束，只能刪除全表數據,不能刪除單個數據

修改數據：

update 表名 set 列名1 = 值1 , 列名2 = 值2... where 限定條件;

基本查詢數據：

select * from 表名; 查看所有列

select 列名1,列名2,...from 表名; 查看所需列

select 列名1 as '別名1',列名2 as '別名2',...from 表名; 查看所需列并給列起別名

select (列名1 + 列名2) as '別名' from 表名; 

-- 和并列的時候,兩個列的字段要類型一致;null參與的運算，結果都為null;ifnull 字段名稱 會將null替換為0

4、表數據查詢

1)where限制條件查詢

=、!=、<>(不等于)、<、<=、>、>=;

between...and 在什么范圍之間

IN(set)；

is null 為空

is not null 不為空

and 并且

or 或者

not 非

2)模糊查詢 like

通配符

_ 匹配單個任意字符

% 匹配對個任意字符

select * from 表名 where 列名 like '通配符組合'; 

3)去除重復記錄 distinct

select distinct 列名 from 表名;

4)排序 order by ASC 默認升序排列 DESC 降序排列

select * from 表名 order by 列名 asc;

select * from 表名 order by 列名1 desc,列名2 desc;

5)聚合函數 ———— 縱向運算

count(): select count(* 或者不含null值的列名) from 表名;

max(): select max(列名) as '最大值' from 表名;

min(): select min(列名) as '最小值' from 表名;

sum(): select sum(列名) as '和' from 表名;

avg(): select avg(列名) as '平均值' from 表名;

6)分組查詢 ———— 配合聚合函數使用 查出的數據才有意義

select 列名1 聚合函數 from 表名 where 限定條件 group by 列名1; 按列名1分組

select 列名1 聚合函數 from 表名 group by 列名1 having 限定條件;

where：在分組之前對條件進行限定 不滿足條件，就不會參與分組

having：在分組之后，對結果集的篩選

7)分頁查詢

select * from 表名 limit 開始的記錄索引,每一頁顯示的條數;

開始的記錄索引 = (頁碼-1)*每一頁顯示的條數

* oracle：rownum 分頁方言

* sqlserver：top 分頁方言

5、約束

1)主鍵約束(primary key)

被修飾的字段唯一且非空;一張表只能有一個主鍵,這個主鍵可以包含多個字段

格式一：

create table 表名(

字段名稱 字段類型 primary key

);

格式二：

create table 表名(

字段名稱 字段類型;

primary key(字段名稱)

);

格式三：

alter table 表名 add primary key(字段名1,字段名2..);

2)唯一約束(unique) ———— 被修飾過的字段唯一,對null不起作用

3)非空約束(not null) ———— 被修飾過的字段非空

4)外鍵約束(foreign key)

1)自增長約束 auto_increment

1.被修飾的字段類型支持自增. 一般int

2.被修飾的字段必須是一個key 一般是primary key

2)在多表的一方添加外鍵約束

alter table 外表名稱 add foreign key(外鍵字段名稱) references 主表名稱(主鍵字段名稱);

6、多表查詢

1)內連接 a表，b表的連接

格式1:顯式的內連接

select a.*,b.* from a [inner] join b on ab的連接條件;

格式2:隱式的內連接

select a.*,b.* from a,b where ab的連接條件;

2)外連接

格式1:左外連接

select a.*,b.* from a left [outer] join b on 連接條件;

先展示join左邊的(a)表的所有數據,根據條件關聯查詢 join右邊的表(b),符合條件則展示出來,不符合以null值展示.

格式2:右外連接

select a.*,b.* from b right [outer] join a on 連接條件;

先展示jion右邊的表(a)表的所有數據,根據條件關聯查詢join左邊的表(b),符合條件則展示出來,不符合以null值展示.

3)子查詢 ———— 一個查詢依賴另一個查詢

例：SELECT emp_fname,emp_lname FROM employee WHERE emp_no = (SELECT emp_no FROM works_on WHERE enter_date = '1998-01-04 00:00:00');

select 列名 from 表名 where 列名 = (select 列名 from 表名 where 限定條件);

二、儲存過程 ———— 帶有邏輯的sql語句

1、特點

1)執行效率非常快，儲存過程是在數據庫的服務器端執行的

2)移植性很差，不同數據庫的儲存過程是不能移植的

2、語法

創建儲存過程

delimiter $

create procedure test(in 輸入參數名 數據類型，out 返回值名 數據類型) 

-- 也可以沒有參數和返回值

begin

-- 多條需要執行的sql語句

if 輸入參數名 = 值1 then

set 返回值名 = '返回值1';

if 輸入參數名 = 值2 then

set 返回值名 = '返回值2';

else

set 返回值名 = '返回值3';

end if;

end $

調用存儲過程：

call tset(輸入參數值,@返回值名);

select @返回值名;

3、MySQL中的變量

1)全局變量(內置變量)：作用域整個鏈接

-- 查看所有全局變量；show variables;

-- 查看某個全局變量：select @@變量名;

-- 修改全局變量：set 

set @@character_set_client=gbk; 設置服務器端口接收的編碼

set @@character_set_results=utf8; 設置顯示的編碼

2)會話變量：只存在當前一次連接中 如果這次鏈接斷掉 會話變量就消失了

-- 定義會話變量：set @變量 = 值;

-- 查看會話變量：select @變量;

3)局部變量：比如存儲過程中的變量 作用域在 begin和end之間

4、begin end間的邏輯sql語句

1)條件判斷

if ..then ..set..;

else set..;

end if;

2)循環

whlie 變量名 判斷符 值 do

set..;

set 變量名=變量名+1; -- 注意這塊沒有i++的說法

end whlie;

5、刪除儲存過程

drop procedure 儲存過程名;

三、觸發器 ———— 當一個操作發生時會觸發另一個行為

創建觸發器

create trigger 起個觸發器名字 after增/改/刪 on要增改刪的表名 for each row

insert into 要記錄的表名(字段名)values(‘增加了一條記錄’);

//after insert on 添加

//after update on 修改

//after delete on 刪除

例：

delimiter $

create trigger banji after insert on user for each row

begin

insert into loger values(NULL,'你增加了一條記錄',NULL);

end $

insert into user values(NULL,'張三');

四、MySql中的函數

1、系統函數

直接調用即可.任何函數都有返回值,函數的調用是通過select調用.因為有返回值,所以只能用select 調用

1)截取字符串

substring(原字符串,起始位置,截取長度); mysql 中下標從1開始算

2)字符字節長度

set @username='你好世界';

char_length('@username') 字符長度  4 個

length('@username')  字節長度 12個

3)查找字符所在字符串的索引，沒有返回0

select instr(@username,'好'); 返回2

4)按照指定長度填充指定字符串

lpad (@username,10,'要填充的字符') 左填充

例如: select lpad(@username,10,'歡迎'); 注意10個長度算上原來'你好世界'的這4個長度,也就是說歡迎填充了6個

rpad 右填充

5)insert 替換字符串

例如: select insert(@username,2,2,'哈哈'); 從第二個字符開始替換兩個字符 替換成哈哈

6)strcmp(字符串1,字符串2) 比較兩個字符串大小 按照字典順序去比較

2、自定義函數

1)創建自定義函數

create function 函數名(參數列表可有可無) returns 數據類型  --規定要返回的數據類型

begin

函數體

返回值:return 返回類型(我們指定的類型);

end

2)使用函數

create function show() returns int

return 100;

-- 調用函數

select show();

-- 查看所有函數 show functions status;

-- 查看創建函數  show create function test;

-- 刪除函數  drop function test;

3)例：

delimiter $

create function delById(uid int) returns int

begin

delete form user where id = uid;

return (select count(*) from user);

end $

select deById(7);

五、視圖

1、概述：有結構的虛擬表，虛擬表的結構來源不是自己定義,而是從對應的基表中產生

視圖可以更好的進行權限控制 比如對外隱藏我的一些基表的名稱

2、創建視圖

create view 視圖名稱 as select語句;

create view my_v1 as select * from student;

create view my_v2 as select a.字段名,b.字段名 from a,b where a.id=b.id;

3、查看視圖

關于查詢表的語句對于視圖都是可以用的 除過show create view my_v1;

視圖的執行:其實本質就是執行封裝的select 語句

4、刪除視圖：drop view 視圖名稱

5、修改視圖，其實是對原表的修改 alter view 視圖名字 as 新的select語句

6、視圖數據的操作

視圖插入數據:

(1)多表視圖不能插入數據

(2)單表視圖中可以插入數據(如果視圖中字段沒有基表中不能為空的字段且沒有默認值的字段,是插入不成功的)

(3)視圖是可以向基表中插入數據的 (視圖的操作是影響基表的)

視圖刪除數據

(1):多表視圖不能刪除數據

(2):單表視圖可以刪除數據,也會影響到基表

視圖更新數據

單表視圖,多表視圖都可以更新數據

    更新限制:with check option

    例如:create view my_v1 as select * from student where age>30 with check option;

    表示視圖數據的來源都是年齡大于30的,with check option 決定通過視圖更新的時候,不能將已得到

    數據age>30的學生 改成age<30 的.

    那么:update  my_v1 set age=20 where id=1; 就會報錯 不允許改 因為做了限制

操作Mysql數據庫的基本流程就先給大家講到這里，對于其它相關問題大家想要了解的可以持續關注我們的行業資訊。我們的板塊內容每天都會捕捉一些行業新聞及專業知識分享給大家的。