Mysql 數據庫表如何增刪改查

這篇文章將為大家詳細講解有關Mysql 數據庫表如何增刪改查，文章內容質量較高，因此小編分享給大家做個參考，希望大家閱讀完這篇文章后對相關知識有一定的了解。

一、表操作

1、【增加】

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

2、【刪除】

刪除表

drop table table_name;

3、【修改】

修改表選項

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 //查看表結構 （describe）

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

二、表字段操作

1、【增加】

增加表字段

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

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

alter table table_name add column snvarchar(10) first;//在最形始增加sn字段、使用關鍵字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、【查詢】

查看表和表結構

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

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

desc table_name //查看表結構 （describe）

三、表數據操作

1、【增加】

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

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

2、【刪除】

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

delete 時、支持order by  和limit來限制刪除數據記錄

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 [字段表達式列表]  from 表名 [where 子句] [group by子句] [having 子句] [order by子句] [limit 子句]

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

select * from table_name where id>6;

select name from table_name where id>2;

select 加法邏輯關系別名(as)//select 運算

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子句、查詢條件子句：

關系：> >= < <= != =

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

Notlike: 是like取反

Betweennum1 and num2: 在某個區間、閉區間。

In(元素列表): 在某個集合之內  

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

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 字段：分組查詢

對查詢結果（已經通過where子句過濾之后的數據），按照某個字段，進行分組！

合計函數：

count(): 統計記錄數、可以是*和字段名

sum(字段表達式): 統讓和、對某個字段求和、

avg(): 平均值

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

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

group_concat(字段表達式)：組內連接字符串

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區別：having的結果一定是 where 已經過濾之后的結果!having對結果進行二次處理

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],]//對結果進行排序的語句！可對多個字段排序

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

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

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

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

Select *  from  tb1 limit  1,3;

執行順序：字段表達式，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);

子查詢的兩種分類依據：

依據子查詢出現的位置！

where型子查詢， 出現在where子句內！

from 型子查詢， 出現在from子句內！

依據子查詢的返回數據的格式！

標量子查詢，返回值是一個數據，稱之為標量子查詢！

列子查詢，返回一個列，

行子查詢，返回一個行，

表子查詢，返回的是一個二維表

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

需要，先用一個查詢，得到身高排序結果，再將該結果分組

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); //找到班級內有女同學的男學生信息

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:

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

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

需要不單從 學生表獲取數據，還需要從 班級表獲得數據

內連接，inner  join

mysql默認的連接就是 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關鍵字前面的（左邊的）左表，join關鍵字后邊的（右邊的）右表！

左外：如果出現左表記錄連接不上右表記錄的，左表記錄會出現正在最終的連接結果內！而右表記錄相應設置成NULL。

右外：如果出現右表記錄連接不上左表記錄的，右表記錄會出現正在最終的連接結果內！而左表記錄相應設置成NULL。

交叉連接，cross join: 相當于是 沒有條件的內連接

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

Select stu_name,class_name  from  select_class natural  join  select_student;

而自然連接也分內連接與外連接！

自然內連接：natural join

自然左外：natural left join

自然右外：natual right join

總結：

最終的效果只有：內，左外，右外！

交叉，特殊的內!

自然，相當于自動判斷連接條件，完成內，左外，右外！

連接條件，on，using:

on，后面使用一個連接條件表達式！

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

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查詢，聯合查詢:

將多個查詢的結果，并列到一個結果集合內！

(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 的連接的兩個子句，不要求實同表，只要求，列的數量相同！

union會在聯合時：主動去掉相同的記錄：此時，可以使用 all關鍵字加以修正：

select 1=1 union  all select 2;

select語句的選項:

distinct，取消相同的記錄

select class_id  from  tb_student;

select all  class_id  from tb_student;

select distinct  class_id  from tb_student;

四、編碼

1、建庫，建表，建字段 設置（數據庫中的數據的編碼）

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

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

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

五、視圖

視圖：就是通過一條查詢語句得到一個張虛擬表！因此，視圖就是 select語句的結果

作用：簡化查詢的業務邏輯，隱藏真實的表結構。

語法：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;

//取得每個班級最高的學生信息

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

Select * from  view_student  group by  class_id;

六、事務

事務：一組 SQL 的集合，要不集體都執行成功，要不集體都失敗，

語法：

開啟事務：start  transaction (可簡寫begin)

提交：commit  (如果sql成功、則提交、將sql的執行結果保存到數據庫里)

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

注：無論回滾還是提交，都會關閉該事務！（需要再次開啟，才能使用）事務，只針對當前的連接生效！

事例：

Start transaction;

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

Commit;

七、數據庫備份

1、備份單個表

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、備份一個庫

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、還原數據庫

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

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

3、忘記root密碼

mysqld服務器程序，有一個選項，跳過權限認證選項！客戶端登陸不用密碼

my.ini里面要有這個選項、沒有就手動填加 添加skip-grant-tables（改好密碼就刪除這段）

第一步：

重新開啟mysqld服務、直接mysql就能進去了。

cmd>mysql

第二步：

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

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

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

第三步：

重啟mysqld

八、PHP操作mysql服務器

php作為mysql服務器的客服端

php--鏈接認認證--發送sql--執行sql、生成結果--處理結果--關閉連接

1、【連接認證】

$_host = 'localhost';

$_port = '3306';

$_user = 'root';

$_pass = 'rootpass';

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

         die('連接失敗！');

}

2、【向mysql服務器發送sql】

$_sql = 'show databases';

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

         echo'sql執行失敗';

}

3、【處理返回的數據】

$_row = mysql_fetch_assoc($_result);

var_dump($_row);

結果集取出來的函數：

mysql_fetch_assoc() 函數從結果集中取得一行作為關聯數組。

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

mysql_fetch_array() 函數從結果集中取得一行作為關聯數組，或數字數組，或二者兼有

mysql_num_rows()函數返回結果集中行的數目。

注：任何有結果的sql操作，返回的都是結果集！結果集，就是一個二維表的結構！是一行行的記錄組成！

4、【釋放釋源】

mysql_free_result($_result);結果集

mysql_close($_link);連接資源

關于Mysql 數據庫表如何增刪改查就分享到這里了，希望以上內容可以對大家有一定的幫助，可以學到更多知識。如果覺得文章不錯，可以把它分享出去讓更多的人看到。