mysql 常用操作

標籤：mysql

1. mysql insert update on duplicate

如果更新 key 衝突，那麼按照指定的規則來更新資料，有時候這樣可以方便不少。還有一個是連表更新資料，這樣可以將 table_b 的 狀態同步到 table_a, 本質上和 多表查詢是類似的。

insert into myblog (id,title,ctime) values(123,‘hello‘,now())on duplicate key update title=values(title),ctime=values(ctime);update table_a a , table_b b set a.shop_status = b.group_status where a.shop_id = b.shop_id;

2. 插入，從另一張表選擇內容插入到新表

將 blog_bak表中的所有資料匯入到myblog 中，表 blog 和 blog_bak 應該有同樣的表結構

insert into myblog( blog,ctime) select * from blog_bak;

3. mysql 索引

給自己的表添加索引，可以給多個欄位添加索引,有下面兩種方式，對應的，刪除索引的方法也列在了下面。

create index index_name on table_name (column_list);alter table table_name add index index_name (column_list);drop index index_name on table;alter table table_name drop index index_name;

4. mysql show

有時候遇到了 warnnings，怎麼查看呢，可以用下面的命令,查看全表的資訊,可以查到自己表的注釋資訊是可以用最後一個命令full fields from。

show warnings;show errors;show tables like ‘%talname%‘;show full fields from your_table;

5. mysql 資料匯入和匯出

truncate table會清空表中的資料，包括auto_increment的欄位都會被重設。mysqldump 可以又這些選擇，匯出表，匯出庫，匯出表結構，匯出表中的資料。或者整體匯出，下面都有對應的shell命令。

mysqldump -h localhost -ppasswd  -uroot -d database > dump.sql ;#只匯出資料庫的結構mysqldump -h localhost -ppasswd  -uroot  database  > dump.sql ;#匯出資料庫的結構和所有的資料mysqldump -h localhost -ppasswd  -uroot -d database tablename > dump.sql ;#只匯出表結構mysqldump -h localhost -ppasswd  -uroot  database tablename > dump.sql ;#匯出表結構和表中的資料mysql -u root -p yourpasswd -h localhost yourdb < dump.sql#將dump.sql 匯出入到你的資料庫

6. mysql 幾個簡單的時間處理函數

請不要吧 day 寫成 days ，month , hour 同理，group by 多個欄位 從 col_a -> col_b -> col_c 優先順序依次降低。

select date_format(now(),"%Y-%m-%d %H:%i:%s") now;select date_sub(now(), interval 10 day) as yesterday; select * from test_table where status = 1 order by col_a desc, col_b desc, col_c asc limit 100;

7. mysql 變數

可以通過 select 給變數賦值，對，你沒有看錯,這兩種方式都可以給@a賦值。使用的時候記得用@啊，就像PHP裡面的$一樣，這個變數只在這個連結周期中有效。

set @a = 100;set @a:=100;select @a:=300;  

8. mysql 預存程序

下面是一個簡單的預存程序的例子，因為 mysql 預設的 終止符是; 而這個正好是預存程序的文法，所以在編寫預存程序之前，先將 delimiter 改成 $，在預存程序結束之後，應將 delimiter 改成預設的; 這樣符合我們的習慣, 最後是調用這個預存程序call p()。具體代碼如下：

delimiter $ create procedure p()beginselect * from ttt;end;$delimiter ;     call p();       

9. mysql 中文亂碼，設定成utf8.

下面是我的 centos 6.5 上的mysql 的設定檔，mysql是通過yum安裝的,貌似版本都是5.1的….我添加的內容是斜體部分，目的就是設定預設的字元集為utf8 ，如果有些部分沒有，請自行添加，比如[client]沒有的話，在我的設定檔中，我就是自己添加的，不知道那個版本的mysql配置為何那麼奇葩，以至於剛開啟都不太敢相信自己的眼睛。

[client]
default_character_set=utf8
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
character_set_server=utf8
init_connect=’SET NAMES utf8’
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
default-character-set=utf8

10. mysql 許可權管理

許可權管理的所有資訊都存放在mysql這個資料庫中。只有通過root使用者進去才可以進行對應的操作。前面一個是建立一個使用者，host 是表明這個使用者可以從什麼地方串連資料庫，設定成本地比較安全，注意其中的password()函數。flush privileges是讓修改生效。最後的grant是將一個資料庫t_sweet 授權給這個使用者。

use mysql;insert into mysql.user(Host,User,Password) values("localhost","phplamp",password("1234"));flush privilegesgrant all privileges on t_sweet.* to [email protected] identified by ‘yourpassword‘

設定好之後可以這樣看一下。

show variables like ‘character%‘

mysql 常用操作