Mysql 數(shù)據(jù)庫表如何增刪改查

這篇文章將為大家詳細(xì)講解有關(guān)Mysql 數(shù)據(jù)庫表如何增刪改查，文章內(nèi)容質(zhì)量較高，因此小編分享給大家做個(gè)參考，希望大家閱讀完這篇文章后對(duì)相關(guān)知識(shí)有一定的了解。

一、表操作

1、【增加】

create table DBname.table_name(id int, namevarchar(255))engine=MyISAM charset=gbk;

2、【刪除】

刪除表

drop table table_name;

3、【修改】

修改表選項(xiàng)

alter table table_name engine=myisamcharset=utf8;//修改字符集

rename table Old_tablename to new_tablename;//修改表名

4、【查詢】

show tables like '%_name'; //查看表

show create table table_name //查看建表語句

desc table_name //查看表結(jié)構(gòu) （describe）

show variables like 'character_set%';//展示以character_set開頭變量

二、表字段操作

1、【增加】

增加表字段

alter table table_name add column 字段定義 //增加字段

alter table table_name add column heightint after name;//增加一個(gè)字height字段在name之后

alter table table_name add column snvarchar(10) first;//在最形始增加sn字段、使用關(guān)鍵字first

2、【刪除】

刪除字段

alter table table_name drop columncolumn_name;

alter table table_name drop columnheight;//刪除表table_name中height字段

3、【修改】

 修改已有字段（修改字段定義）

alter table table_name modify columncolumn_name 新的定義;

alter table table_name modify column sn intafter name; //修改sn字段

修改字段

alter table table_name change column 原字段名   新字段名  新字段定義;

alter table table_name change column snnew_sn varchar(30) after age;//修改字段

4、【查詢】

查看表和表結(jié)構(gòu)

show tables like '%_name'; //查看表

show create table table_name //查看建表語句

desc table_name //查看表結(jié)構(gòu) （describe）

三、表數(shù)據(jù)操作

1、【增加】

insert into 表名（字段列表） values(與字段相對(duì)的值列表);

insert into table_name('name','age','height')values(ssw,22,180);

2、【刪除】

delete from 表名 where 條件;(刪除是不可逆的)

delete 時(shí)、支持order by  和limit來限制刪除數(shù)據(jù)記錄

delete from table_name where id>2;

delete from tb_student order  by height  desc  limit 3;

truncate tb_student;//清空表

3、【修改】

update 表名 set 字段=新值,字段n=新值n  where 條件;（也可用order by 和limit限制）

update table_name  set name='php' where id=3;

4、【查詢】

select [字段表達(dá)式列表]  from 表名 [where 子句] [group by子句] [having 子句] [order by子句] [limit 子句]

select 字段列表 from 表名 [where 條件表達(dá)式] (*表示所有字段)

select * from table_name where id>6;

select name from table_name where id>2;

select 加法邏輯關(guān)系別名(as)//select 運(yùn)算

select 1+1; select 10>20; select 1 or 0;select 1+1 as a;

select * from tb1,tb2;//多表查詢

select tb1.id as s_id, tb2.* from tb1,tb2;//字段別名多表查詢

select tb1.id  as  s_ id, s.class_name,  c.*  from tb1  as  s, tb2 as  c;//表別名

where子句、查詢條件子句：

關(guān)系：> >= < <= != =

Like:模糊查詢、like  ‘模式匹配符%和_’;(%任意字符，_表示一個(gè)字符)

Notlike: 是like取反

Betweennum1 and num2: 在某個(gè)區(qū)間、閉區(qū)間。

In(元素列表): 在某個(gè)集合之內(nèi)  

Notin(元素列表): 不在某個(gè)集合之內(nèi)

Null判斷、is null 或者is not null

事例：selsect * from tb1where class_name like ‘%22’;

selsect * from tb1 where class_name like ‘_22’;

select * from  tb1 where  id  between 11  and  40;

select * from  tb1 where  id >=11  and id<=40;

select * from  tb1 where  id  not  in(11,30);

select * from  tb1 where  id  in (11,20);

group  by 字段：分組查詢

對(duì)查詢結(jié)果（已經(jīng)通過where子句過濾之后的數(shù)據(jù)），按照某個(gè)字段，進(jìn)行分組！

合計(jì)函數(shù)：

count(): 統(tǒng)計(jì)記錄數(shù)、可以是*和字段名

sum(字段表達(dá)式): 統(tǒng)讓和、對(duì)某個(gè)字段求和、

avg(): 平均值

max(字段表達(dá)式)：最大值

min(字段表達(dá)式)：最小值

group_concat(字段表達(dá)式)：組內(nèi)連接字符串

select count(*),id  from  tb1 where   1  groub by   id;

select sum(money),class_id   from   tb1  groub  by   class_id;

select avg(money),class_id   from   tb1  groub  by   class_id;

select concat(‘It’ ,’is’,’test’);

分組排序：

ASC: 升序

DESC：降序

Select  count(*),class_id   from   tb1 group  by  class_id;

多字段分組：

Select   count(*),class_id,class_name  from   tb1   groub by  class_id,class_name;

Having子句：條件子句、功能和where類似

Select *  from  tb1 where  money>300;

Select *  from  tb1 having  money>300;

Having和where區(qū)別：having的結(jié)果一定是 where 已經(jīng)過濾之后的結(jié)果!having對(duì)結(jié)果進(jìn)行二次處理

Select avg(age),class_id  from  tb1 where  1  group by class_id  having  avg(age)>16;

Order by 排序子句

Order by  字段名 [asc|desc],[字段名[asc|desc],]//對(duì)結(jié)果進(jìn)行排序的語句！可對(duì)多個(gè)字段排序

Select * from  tb1 order  by  class_id desc,  age  asc;

原則是，先按照第一個(gè)字段進(jìn)行排序，如果字段值相同，則采用第二個(gè)，以此類推

Select class_id  from  tb1 group  by  class_id asc  order  by class_id  desc;

Limit 子句：限制結(jié)果記錄的子句、limit  start (起始位置), size(記錄數(shù));

Select *  from  tb1 limit  1,3;

執(zhí)行順序：字段表達(dá)式，from子句，where子句，group by子句，having子句，order by子句，limit子句

子查詢語句：

select max(height)  from  tb1;

select *  from  tb1 where  height=170;

select *  from  tb1 where  height=(select  max(height) from  tb1);

子查詢的兩種分類依據(jù)：

依據(jù)子查詢出現(xiàn)的位置！

where型子查詢， 出現(xiàn)在where子句內(nèi)！

from 型子查詢， 出現(xiàn)在from子句內(nèi)！

依據(jù)子查詢的返回?cái)?shù)據(jù)的格式！

標(biāo)量子查詢，返回值是一個(gè)數(shù)據(jù)，稱之為標(biāo)量子查詢！

列子查詢，返回一個(gè)列，

行子查詢，返回一個(gè)行，

表子查詢，返回的是一個(gè)二維表

Select * from tb1 where height=(selectmax(height) from tb1); //where型

需要，先用一個(gè)查詢，得到身高排序結(jié)果，再將該結(jié)果分組

Select* from (select * from tb1 order byheight desc) group by class_id;//from型

Select* from (select * from tb1 order byheight desc) as tmp  group by class_id;

列子查詢:

Select * from  tb1 where sex=’girl’  and  class_id in(select  class_id  from tb1  where sex=’body’ group byclass_id); //找到班級(jí)內(nèi)有女同學(xué)的男學(xué)生信息

Select * from  tb1 where (height,money)=(selectmax(height),max(money)  from tb1);

//找到，高富，最高并且最有錢！

exists型子查詢:

select * from tb1 where  exists(select * from tb2  where tb1.class_id = tb2.id);

連接查詢，join:

連接多個(gè)表記錄之間的連接！from 表名1  join        表名2  on 連接條件

Select  name,class_id,age  from  tb_xue join  tb_ban  on tb_xue.class_id=tb_ban.id;

需要不單從 學(xué)生表獲取數(shù)據(jù)，還需要從 班級(jí)表獲得數(shù)據(jù)

內(nèi)連接，inner  join

mysql默認(rèn)的連接就是 inner join

select stu_name,class_id,class_name fromselect_student inner join select_class onselect_student.class_id=select_class.id;(可省略inner)

外連接，left join，right join

Select stu_name,class_id,class_name from select_student left join select_classon select_student.class_id = select_class.id;

join關(guān)鍵字前面的（左邊的）左表，join關(guān)鍵字后邊的（右邊的）右表！

左外：如果出現(xiàn)左表記錄連接不上右表記錄的，左表記錄會(huì)出現(xiàn)正在最終的連接結(jié)果內(nèi)！而右表記錄相應(yīng)設(shè)置成NULL。

右外：如果出現(xiàn)右表記錄連接不上左表記錄的，右表記錄會(huì)出現(xiàn)正在最終的連接結(jié)果內(nèi)！而左表記錄相應(yīng)設(shè)置成NULL。

交叉連接，cross join: 相當(dāng)于是 沒有條件的內(nèi)連接

自然連接，natural join: mysql，自動(dòng)判斷連接條件，幫助我們完成連接！

Select stu_name,class_name  from  select_class natural  join  select_student;

而自然連接也分內(nèi)連接與外連接！

自然內(nèi)連接：natural join

自然左外：natural left join

自然右外：natual right join

總結(jié)：

最終的效果只有：內(nèi)，左外，右外！

交叉，特殊的內(nèi)!

自然，相當(dāng)于自動(dòng)判斷連接條件，完成內(nèi)，左外，右外！

連接條件，on，using:

on，后面使用一個(gè)連接條件表達(dá)式！

using(連接字段)，要求使用同名字段進(jìn)行連接！

Select class_name,stu_name  from  tb_class inner  join  tb_student on tb_class.class_id=tb_student.class_id;

Select class_name,stu_name  from  tb_class inner  join tb_student  using(class_id);

union查詢，聯(lián)合查詢:

將多個(gè)查詢的結(jié)果，并列到一個(gè)結(jié)果集合內(nèi)！

(select stu_name,height  from  tb_student where  sex=’girl’  order by height asc limit  1000) union(select  stu_name ,height  from tb_student  where  sex=’box’ order by height  desc  limit 1000 );

union 的連接的兩個(gè)子句，不要求實(shí)同表，只要求，列的數(shù)量相同！

union會(huì)在聯(lián)合時(shí)：主動(dòng)去掉相同的記錄：此時(shí)，可以使用 all關(guān)鍵字加以修正：

select 1=1 union  all select 2;

select語句的選項(xiàng):

distinct，取消相同的記錄

select class_id  from  tb_student;

select all  class_id  from tb_student;

select distinct  class_id  from tb_student;

四、編碼

1、建庫，建表，建字段 設(shè)置（數(shù)據(jù)庫中的數(shù)據(jù)的編碼）

2、PHP作為mysql服務(wù)器的客戶端，設(shè)置的客戶端編碼和連接編碼（set names gbk/utf8）

3、設(shè)置php返回給瀏覽器數(shù)據(jù)的編碼，（Content-Type,header(),<meta>）

4、PHP文件本身保存的編碼（文件編碼，通過文本編輯器設(shè)置）

五、視圖

視圖：就是通過一條查詢語句得到一個(gè)張?zhí)摂M表！因此，視圖就是 select語句的結(jié)果

作用：簡(jiǎn)化查詢的業(yè)務(wù)邏輯，隱藏真實(shí)的表結(jié)構(gòu)。

語法：create   view  視圖名字  as   查詢語句

Create view  view_name  as select  *  from tb_student  as  s left  join  tb_class using(class_id);

Select * from   view_name  where id=22;

//取得每個(gè)班級(jí)最高的學(xué)生信息

Create view  view_student  as select  *  from view_student  order  by height desc;

Select * from  view_student  group by  class_id;

六、事務(wù)

事務(wù)：一組 SQL 的集合，要不集體都執(zhí)行成功，要不集體都失敗，

語法：

開啟事務(wù)：start  transaction (可簡(jiǎn)寫begin)

提交：commit  (如果sql成功、則提交、將sql的執(zhí)行結(jié)果保存到數(shù)據(jù)庫里)

回滾：rollback (如果sql失敗、則回滾、將sql的執(zhí)行結(jié)果退到事務(wù)開始之前)

注：無論回滾還是提交，都會(huì)關(guān)閉該事務(wù)?。ㄐ枰俅伍_啟，才能使用）事務(wù)，只針對(duì)當(dāng)前的連接生效！

事例：

Start transaction;

Update tb_student  set  money=money+1000  where id=10;

Commit;

七、數(shù)據(jù)庫備份

1、備份單個(gè)表

select 字段列表into outfile文件地址 from 表名 where 其他的select子句

select *  into  outfile ‘d:/b.txt’ 

fields terminated  by ‘,’ 

lines terminated  by  ‘\r\n’

from tb_student;

還原:load  data  infile filename  into  tb_name;

2、備份一個(gè)庫

Mysqldump -h227.0.0.1  -P3306  -uroot -p  > d:/data_back.sql;//備份庫

Mysqldump -h227.0.0.1  -P3306 -uroot -p data_name  tb_studnet >d:/tb_back.sql;//備份表

3、還原數(shù)據(jù)庫

Mysql -h227.0.0.1  -P3306  -uroot -p  data_name <d:/data_back.sql;

Mysql>source  d:/data_back.sql;(登錄后還原)

3、忘記root密碼

mysqld服務(wù)器程序，有一個(gè)選項(xiàng)，跳過權(quán)限認(rèn)證選項(xiàng)！客戶端登陸不用密碼

my.ini里面要有這個(gè)選項(xiàng)、沒有就手動(dòng)填加 添加skip-grant-tables（改好密碼就刪除這段）

第一步：

重新開啟mysqld服務(wù)、直接mysql就能進(jìn)去了。

cmd>mysql

第二步：

更新mysql.user表root用戶的password字段

Update mysql.user  set  password=password(‘123456’)  where user=’root’; 這個(gè)不行就用下面這個(gè)

update mysql.user set authentication_string=password('**') where user='**';

第三步：

重啟mysqld

八、PHP操作mysql服務(wù)器

php作為mysql服務(wù)器的客服端

php--鏈接認(rèn)認(rèn)證--發(fā)送sql--執(zhí)行sql、生成結(jié)果--處理結(jié)果--關(guān)閉連接

1、【連接認(rèn)證】

$_host = 'localhost';

$_port = '3306';

$_user = 'root';

$_pass = 'rootpass';

if(!$_link = mysql_connect("$_host:$_port",$_user, $_pass)){

         die('連接失??！');

}

2、【向mysql服務(wù)器發(fā)送sql】

$_sql = 'show databases';

if(!$_result = mysql_query($_sql, $_link)){

         echo'sql執(zhí)行失敗';

}

3、【處理返回的數(shù)據(jù)】

$_row = mysql_fetch_assoc($_result);

var_dump($_row);

結(jié)果集取出來的函數(shù)：

mysql_fetch_assoc() 函數(shù)從結(jié)果集中取得一行作為關(guān)聯(lián)數(shù)組。

mysql_fetch_row() 函數(shù)從結(jié)果集中取得一行作為索引數(shù)組。

mysql_fetch_array() 函數(shù)從結(jié)果集中取得一行作為關(guān)聯(lián)數(shù)組，或數(shù)字?jǐn)?shù)組，或二者兼有

mysql_num_rows()函數(shù)返回結(jié)果集中行的數(shù)目。

注：任何有結(jié)果的sql操作，返回的都是結(jié)果集！結(jié)果集，就是一個(gè)二維表的結(jié)構(gòu)！是一行行的記錄組成！

4、【釋放釋源】

mysql_free_result($_result);結(jié)果集

mysql_close($_link);連接資源

關(guān)于Mysql 數(shù)據(jù)庫表如何增刪改查就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺得文章不錯(cuò)，可以把它分享出去讓更多的人看到。