下文所講解的資料庫技巧是許多人在大量的資料庫分析與設計實踐中,逐步總結出來的。對於這些經驗的運用,讀者不能生幫硬套,死記硬背,而要消化理解,實事求是,靈活掌握。並逐步做到:在應用中發展,在發展中應用。和yjbys一起來學習吧!
常用資料操作語言DML筆記(select insert update delete)
select 語句
高階的查詢功能,見下面的詳細內容
12345 | select 列名1,列名2 ... from 表名1,表名2... [ where 條件] like [ group by ...] [ having ...] [ order by ...] 以特定的順序顯示 例: order by name asc ;以名字顯示,為降序排列 |
insert 語句
MySQL 當記錄不存在時插入 insert if not exists.在 MySQL 中,插入(insert)一條記錄很簡單,但是一些特殊應用,在插入記錄前,需要檢查這條記錄是否已經存在,只有當記錄不存在時才執行插入操作,本文介紹的就是這個問題的解決方案.
1 | insert into 表名(列名1,列名2,... ) values (值1,值2,...) |
update 語句
1 | update 表名 set 列名1=值1,列名2=值2 ... [ where 條件] |
delete 語句
1 | delete from 表名 [ where 條件] 注:條件時會刪除整個表名 |
truncate 語句
12 | truncate tables 表名 # 注:相當刪除整個表名,在重建.非常快 |
從一個檔案載入資料到 MYSQL
1 | load data infile '檔案' into tables 表名 [fields terminated by '字元' ] |
匯出 MYSQL 資料成一個檔案
1 | select * from 表名 into outfile '檔案' |
DDL資料定義語言(vreate tabe,drop table,altre table)
MYSQL 的字元型別要注意 CHAR VARCHAR TEXT 不分大小寫,只能存字元 ,BLOB可以存二進位制的內容,如果聲音,檔案.
CHAR主要是定長,VARCHAR是變長,所以VARCHAR更加節約空間.
查看錶結構欄位
1 | desc tablesname; |
新增新的表字段
1234 | alter table test add name varchar (20); #新增一個 name 的欄位 alter table test add f_name varchar (20) after id; #在id之後新增一個 name 的'欄位 alter table test add sid int first ; #在最前面加入一個欄位 alter table test add primary key (id); #新增一個 primary 主鍵 |
刪除表中的欄位
12 | alter table test drop name ; #刪除 alter table test add primary key ; #刪除主鍵 |
設定表中欄位的預設值
1 | alter table test alter name set default '不知道' ; |
修改欄位名
1 | alter table test change name l_anme varchar (10); #修改欄位名,資料型別要寫上 |
資料庫插入
123 | insert #表名 values insert into tmp values ( default , 'kate' ,0,20);#可以使用 default . insert into tmp( name ,sex,age) values ( 'katess' ,0,20); |
表的匯入匯出
1 | insert tmp2 select name ,sex from tmp where id<4; #可以給 select 中得到的內容放到tmp2的新表中 |
更新 MYSQL 表中的欄位
12 | UPDATE <表名> SET <列名 = 更新值> [ WHERE <更新條件>] update tmp set address= '北京' where id between 4 and 5 |
刪除表中特定的內容
12 | DELETE FROM tmp WHERE id >5 #刪除id大於5的內容 DELETE FROM ss1,ss2 using ss1,ss2 where = and ss1. name = 'alex' #二張表ss1和ss2相同的id中內容有alex的內容刪除 |
高階查詢
常用查詢函式
concat(字元連線)
distinct(去掉重複資料)
as(別名)
limit 3,2 (控制顯示數,如前現示從第三個起,拿二個數據)
集函式
count(統計)
sun
age
查詢(條件內容查詢)
12 | select 'abc' like 'abc%' ; # %任意字元 _單個字元最少要出現一次 |
MYSQL 中使用正則來查詢資料
1 | select 'abc' regexp '^a.*c$' ; |
排序查詢結果
使用排序功能,和使用別名功能
123 | SELECT * FROM student ORDER BY sage DESC ; #降排序 order by 要放在最後< SELECT * FROM student ORDER BY sdept in ( '資訊系' , '美術系' ) #同時二個條件 SELECT sno AS '學號' , sname AS '姓名' FROM student; |
分組
12 | select cid, avg (grad) from sc group by cid; # 在分組中 group by 要放在最後,要是要加條件的話,集函式要使用 having 來換 where .放在 group by 後 |
多表查詢
1234 | select e,e form sc as g, student as s where e= '扶%' and =; select e,e from sc as g inner join student as s on =; # 內連線,同連線顯示相同的內容,要加 inner 給whereis 改成 on # 如果有的資料沒有,可以換 left ,和 right 以第一個表為基礎來排資料, inner join 是二個表都有的內容. |
子查詢
12 | select sname,sage from student where sno not in ( select sid from sc) ; select sname from student where sno in ( select ) |
在 MYSQL 中查詢,要先轉義
#name=mysql_escape_string($name); mysql_escape_string
FAQ:
1. 檢視和修改設定 MYSQL 預設編碼
123 | SHOW CHARACTER SET;檢視支援的所有字 show variables like 'character_set_%' ; SHOW VARIABLES LIKE 'collation_%' ; |
讓 MYSQL 重起也可以使用utf8
1234 | [mysqld] default-character-set=utf [mysql] default-character-set=utf8 |
2. 怎麼樣進行 MYSQL 備份
MYSQL 的備份可用命令mysqldump ,使用方法很簡單,
1 | $ mysqldump -u 使用者名稱 -p (密碼) -h 主機名 資料庫名 >路徑/備份名 |
同時也可以是用mysqldump備份資料結構()和資料()
1 | $ mysqldump -u 使用者名稱 -p (密碼) -h 主機名 資料庫名 tablename1 tablename2 > |
mysqldump -u 使用者名稱 -p (密碼) -h 主機名 資料庫名 –tab 路徑 –opt 資料庫名.
EXA:
1 | $ mysqldump -u xxxx -p xxxxt databases > ./ |
3. 改變mysql管理員的密碼
方法1: 在/usr/local/mysql/bin/下:
1 | $ mysqladmin -u root password 'new_password' |
一般安裝時用此方法設定.
方法2:
在mysql狀態下:
12 | mysql>UPDATE user SET password=PASSWORD( 'new_password' ) WHERE user= 'root' ; mysql>FLUSH PRIVILEGES; |
Method 3:
mysql>SET PASSWORD FOR root=PASSWORD('new_password');
4. 什麼情況下會導致 MYSQL 資料庫損壞和怎麼修復 MYSQL
mysql 正在執行的時候,伺服器突然斷電或者直接按reset鍵重啟,硬碟空間不夠,導致資料寫不進去,也很有可能導致資料表損壞,物理硬碟有損壞.主要是這幾個原因,mysql修復方法如下
可以把mysql停掉,用mysql的命令myisamchk來修復,這種修復方法是最好的.
具體命令如下:
123 | $ myisamchk -r database/* $ myisamchk -o database/* $ myisamchk -f database/* |
引數 -r, -o , -f 是遞進關係,一般首先用-r修復,然後-o , -f
5. mysql的匯出匯入
12 | $ mysqldump -uroot -p mysql > $ mysql -uroot -p pcti > |
使用source命令,後面引數為指令碼檔案(如這裡用到的)
1 | mysql>source d:backup_ |
6. 設定進入時的預設編碼
1 | mysql -uroot -p --default-character-set=utf8 |
問題:我建立了一個表來存放客戶資訊,我知道可以用 insert 語句插入資訊到表中,但是怎麼樣才能保證不會插入重複的記錄呢?
答案:可以通過使用 EXISTS 條件句防止插入重複記錄.
示例一:插入多條記錄
假設有一個主鍵為 client_id 的 clients 表,可以使用下面的語句:
Code:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, ‘advertising’
FROM suppliers
WHERE not exists (select * from clients
where nt_id = lier_id);
示例一:插入單條記錄
Code:
INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, ‘IBM’, ‘advertising’
FROM dual
WHERE not exists (select * from clients
where nt_id = 10345);
使用 dual 做表名可以讓你在 select 語句後面直接跟上要插入欄位的值,即使這些值還不存在當前表中.
MySQL 連線的狀態資訊
我們常常需要看一些連線的資訊,如下可以顯示相關的資訊:
010203040506070809101112 | mysql> show status like '%onnect%' ; + --------------------------+-------+ | Variable_name | Value | + --------------------------+-------+ | Aborted_connects | 8960 | | Connections | 31530 | | Max_used_connections | 111 | | Ssl_client_connects | 0 | | Ssl_connect_renegotiates | 0 | | Ssl_finished_connects | 0 | | Threads_connected | 73 | + --------------------------+-------+ |
Aborted_connects 嘗試已經失敗的MySQL伺服器的連線的次數。
Connections 試圖連線MySQL伺服器的次數。
Threads_connected 當前開啟的連線的數量.
設定的連線數可以通過下面這個檢視
1 | show variables like 'max_connections' ; |
如果想修改的話,修改 /etc/ 找到max_connections一行,修改為(如果沒有,則自己新增)
1 | max_connections = 1000 |
臨時修改此引數的值, 注意大小寫
1 | set GLOBAL max_connections=1000; |
查詢表的格式
1 | SHOW TABLE STATUS WHERE ROW_FORMAT LIKE 'Compact' |
查詢 binlog 轉換成可讀
mysqlbinlog mysql-bin.000002 -vvvv –base64-output=DECODE-ROWS