mysql workbench常用命令快捷鍵
ctrl+T ->建立新的sql query tab
ctrl+shift+enter->執行當前的sql命令
https://dev.mysql.com/doc/workbench/en/wb-keys.html
1. mysql -uroot -p
2. show databases;
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | ccpdev | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.00 sec)
3.exit;
4. create database intrepid_detectives; // 建立新的database
5. use intrepid_detectives; // 開始使用新創的那個資料庫
database stores their data in tables.資料庫將資料儲存在table中，一個database可以有多張表,就像一個execel spreadsheet可以有多個sheet一樣的概念。
6. show tables;
mysql> use intrepid_detectives; Database changed mysql> show tables; Empty set (0.00 sec)
mysql> use mysql Database changed mysql> show tables; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | engine_cost | | event | | func | | general_log | | gtid_executed | | help_category | | help_keyword | | help_relation | | help_topic | | innodb_index_stats | | innodb_table_stats | | ndb_binlog_index | | plugin | | proc | | procs_priv | | proxies_priv | | server_cost | | servers | | slave_master_info | | slave_relay_log_info | | slave_worker_info | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 31 rows in set (0.00 sec)
從上面可以看出mysql預設builtin database內建有很多table
Every table has a set of columns, 每一張表都有一系列的columns(列).每一列都有一個name和一個datatype(at least).
7. create table <name> (<col-name> <col-type>,<col-name> <col-type>)
mysql> use intrepid_detectives; Database changed mysql> create table investigations ( -> title varchar(100), -> detective varchar(30), -> daysToSolve integer); Query OK, 0 rows affected (0.04 sec) mysql> show tables; +-------------------------------+ | Tables_in_intrepid_detectives | +-------------------------------+ | investigations | +-------------------------------+ 1 row in set (0.00 sec)
8. explain investigations; // 可以列出table的結構來：
mysql> explain investigations; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | title | varchar(100) | YES | | NULL | | | detective | varchar(30) | YES | | NULL | | | daysToSolve | int(11) | YES | | NULL | | +-------------+--------------+------+-----+---------+-------+ 3 rows in set (0.00 sec)
9. insert into investigations values("Finnigan's Falut","Carlotta McOwen", 4); //插入一行
table中的每一個entry被稱為record. 一個table可以有多個records. intersection of a column and a record is a field.
10. insert into investigations(datasToSolve, title,detective) values(3, "The Missing Tree", "Finch Hosky"); // 插入一行資料時，指定欄位名稱，不和資料庫表中的欄位順序一致的辦法
mysql> insert into investigations values("Finnigan's Falut","Carlotta McOwen", 4); Query OK, 1 row affected (0.00 sec) mysql> insert into investigations(daysToSolve, title,detective) values(3, "The Missing Tree", "Finch Hosky"); Query OK, 1 row affected (0.00 sec)
11. select * from investigations; // 從investigations表中獲取所有資料
mysql> select * from investigations; +------------------+-----------------+-------------+ | title | detective | daysToSolve | +------------------+-----------------+-------------+ | Finnigan's Falut | Carlotta McOwen | 4 | | The Missing Tree | Finch Hosky | 3 | | ohter ing Tree | sssf | 3 | | ohter ing Tree | sssf | NULL | +------------------+-----------------+-------------+ 4 rows in set (0.00 sec)
NULL is the SQL value for "no value";任何一個欄位都可能是NULL，只要你不給他賦值！
12. select title, detective from investigations; // 只選擇部分欄位
13. data types:
- varchar(100) create table person(name varchar(100));
- text create table email(body text);
- Numbers int/integer: create table person(age integer unsigned); // 正整數ff
- unsigned int: 0-4294967295
- BIGINT
- SMALLINT
- MEDIUMINT
- TINYINT
- decimal(precision, scope)比如decimal(10,0)=>0123546789; decimal(5,2)=>123.45;decimal(9,7)=>89.1234567
- auto_increment: 如果不給這個欄位一個明確的值，則自動增1 create table student id integer auto_increment);
- date,time,datetime
- date: 'YYYY-MM-DD';
- time: '[H]HH:MM:SS'
- datetime: 'YYYY-MM-DD HH:MM:SS' create table order(order_date date);
- Booleans: bool/boolean: tinyint(1) create table order(fulfilled boolean); 0: false, no-0 is true
- default values:
create table order(coupon varchar(10) default "nodiscount", customer_id integer default null, datetime datetime default current_timestamp,fulfilled boolean NT NULL default 0);
每一個資料庫table都必須有一個primary key, a column that quniquely identifies each row. it can ndeve be null and must be set on record creation and never changed.
14. 建立constraint (主鍵)
mysql> create table detectives ( -> id int not null auto_increment, -> name varchar(100), -> phone_number varchar(10), -> certificationDate date, -> constraint detectives_pk primary key (id)); Query OK, 0 rows affected (0.05 sec) mysql> explain -> detectives; +-------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | phone_number | varchar(10) | YES | | NULL | | | certificationDate | date | YES | | NULL | | +-------------------+--------------+------+-----+---------+----------------+
mysql> insert into detectives(name,phone_number,certificationDate) -> values("zhangsan",12333,"2002-01-03"); Query OK, 1 row affected (0.00 sec) mysql> select * from detectives; +----+----------+--------------+-------------------+ | id | name | phone_number | certificationDate | +----+----------+--------------+-------------------+ | 1 | zhangsan | 12333 | 2002-01-03 | +----+----------+--------------+-------------------+ 1 row in set (0.00 sec)
15. alter table investigations rename cases; // 修改表格名稱
mysql> alter table investigations rename cases; Query OK, 0 rows affected (0.01 sec) mysql> show tables; +-------------------------------+ | Tables_in_intrepid_detectives | +-------------------------------+ | cases | | detectives | +-------------------------------+ 2 rows in set (0.00 sec) mysql>
16. alter table cases add criminal varchar(100) // 增加一列, drop criminal則刪除一列
mysql> alter table cases add criminal varchar(100); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> select * from cases; +------------------+-----------------+-------------+----------+ | title | detective | daysToSolve | criminal | +------------------+-----------------+-------------+----------+ | Finnigan's Falut | Carlotta McOwen | 4 | NULL | | The Missing Tree | Finch Hosky | 3 | NULL | | ohter ing Tree | sssf | 3 | NULL | | ohter ing Tree | sssf | NULL | NULL | +------------------+-----------------+-------------+----------+ 4 rows in set (0.00 sec)
17. alter table detectives change certificationDate certification_date date //更改 old certificationDate欄位名稱為new: certificate_date，type不變!!
mysql> alter table detectives change certificationDate certification_date date; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain detectives; +--------------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------------------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(100) | YES | | NULL | | | phone_number | varchar(10) | YES | | NULL | | | certification_date | date | YES | | NULL | | +--------------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
mysql> select * from cases; +------------------+-----------------+-------------+----+ | title | detective | daysToSolve | id | +------------------+-----------------+-------------+----+ | Finnigan's Falut | Carlotta McOwen | 4 | 1 | | The Missing Tree | Finch Hosky | 3 | 2 | | ohter ing Tree | sssf | 3 | 3 | | ohter ing Tree | sssf | NULL | 4 | | new case | zhangsan | 3 | 5 | +------------------+-----------------+-------------+----+ 5 rows in set (0.00 sec) mysql> alter table cases change daysToSolve hours_to_solve int; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from cases; +------------------+-----------------+----------------+----+ | title | detective | hours_to_solve | id | +------------------+-----------------+----------------+----+ | Finnigan's Falut | Carlotta McOwen | 4 | 1 | | The Missing Tree | Finch Hosky | 3 | 2 | | ohter ing Tree | sssf | 3 | 3 | | ohter ing Tree | sssf | NULL | 4 | | new case | zhangsan | 3 | 5 | +------------------+-----------------+----------------+----+ 5 rows in set (0.00 sec)
18. alter table cases add id int not null;
alter table cases add primary key (id); // 注意這時由於id預設為0，多個record都具有相同的id，因此這條命令會失敗！！！
mysql> select * from cases; +------------------+-----------------+-------------+----+ | title | detective | daysToSolve | id | +------------------+-----------------+-------------+----+ | Finnigan's Falut | Carlotta McOwen | 4 | 0 | | The Missing Tree | Finch Hosky | 3 | 0 | | ohter ing Tree | sssf | 3 | 0 | | ohter ing Tree | sssf | NULL | 0 | +------------------+-----------------+-------------+----+ 4 rows in set (0.00 sec) mysql> alter table cases add primary key (id); ERROR 1062 (23000): Duplicate entry '0' for key 'PRIMARY'
解決方案：需要手工或者程式將該列的值更改為不同的整數，隨後才能夠成功
下面是手工修正id為不同值後能夠正確執行add prmiary key(id)和change id id int not null auto_increment的過程
mysql> select * from cases; +------------------+-----------------+-------------+----+ | title | detective | daysToSolve | id | +------------------+-----------------+-------------+----+ | Finnigan's Falut | Carlotta McOwen | 4 | 1 | | The Missing Tree | Finch Hosky | 3 | 2 | | ohter ing Tree | sssf | 3 | 3 | | ohter ing Tree | sssf | NULL | 4 | +------------------+-----------------+-------------+----+ 4 rows in set (0.00 sec) mysql> alter table cases add primary key(id); Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain cases; +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | title | varchar(100) | YES | | NULL | | | detective | varchar(30) | YES | | NULL | | | daysToSolve | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | | +-------------+--------------+------+-----+---------+-------+ 4 rows in set (0.00 sec) mysql> alter table cases change id id INT NOT NULL AUTO_INCREMENT; Query OK, 4 rows affected (0.04 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> explain cases; +-------------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+----------------+ | title | varchar(100) | YES | | NULL | | | detective | varchar(30) | YES | | NULL | | | daysToSolve | int(11) | YES | | NULL | | | id | int(11) | NO | PRI | NULL | auto_increment | +-------------+--------------+------+-----+---------+----------------+ 4 rows in set (0.00 sec)
19. delete from criminals; // 刪除整個criminals表格的資料
mysql> select * from criminals -> ; +-----------+ | name | +-----------+ | crimianl1 | | crimianl2 | | crimianl3 | +-----------+ 3 rows in set (0.00 sec) mysql> delete from criminals; Query OK, 3 rows affected (0.00 sec) mysql> select * from criminals; Empty set (0.00 sec)
20. drop table criminals; // 刪除criminals表
21. update <table> set <col>=<val> <conditions> ; //更新資料表記錄
22. updat cases set hours_to_solve = hours_to_solve*24; // 我們將hours_to_solve這一列的值從天變換為小時,每一行都重新計算
注意的是有一行由於value為null,所以不會更改:
mysql> select * from cases; +------------------+-----------------+----------------+----+ | title | detective | hours_to_solve | id | +------------------+-----------------+----------------+----+ | Finnigan's Falut | Carlotta McOwen | 4 | 1 | | The Missing Tree | Finch Hosky | 3 | 2 | | ohter ing Tree | sssf | 3 | 3 | | ohter ing Tree | sssf | NULL | 4 | | new case | zhangsan | 3 | 5 | +------------------+-----------------+----------------+----+ 5 rows in set (0.00 sec) mysql> update cases set hours_to_solve = hours_to_solve*24; Query OK, 4 rows affected (0.00 sec) Rows matched: 5 Changed: 4 Warnings: 0 mysql> select * from cases; +------------------+-----------------+----------------+----+ | title | detective | hours_to_solve | id | +------------------+-----------------+----------------+----+ | Finnigan's Falut | Carlotta McOwen | 96 | 1 | | The Missing Tree | Finch Hosky | 72 | 2 | | ohter ing Tree | sssf | 72 | 3 | | ohter ing Tree | sssf | NULL | 4 | | new case | zhangsan | 72 | 5 | +------------------+-----------------+----------------+----+ 5 rows in set (0.00 sec)
23. select * from cases where title = "xxx" // where clause限定選中的records:
select * from cases where title = "Finnigan's Falut";
select * from cases where detective_id !=2 ; //列出所有不是id為2的偵探處理的case
select * from cases where detective_id <>2 ; //列出所有不是id為2的偵探處理的case
select * from cases where start_date < '2015-02-02'; //列出早於2015-02-02的cases
select * from cases where detective_id =2 and hours_to_solve<90;
select * from cases where detective_id =1 or detective_id =2;
select * from cases where detective_id IN (1,2);
select * from cases where hours_to_solve between 24 and 90;
select * from detectives where first_name like "zhang%"
select * from detectives where phone_number NOT like "134"
24. update cases set title="the man that wasnt" where title = "will be changed";
delete from cases where id = 4; 只刪除4th
25.select * from cases limit 5;
26. select * from cases where start_date>'2010-01-01' limit 2;
27. select * from cases where start_date>'2010-01-01' limit 2 offset 4; 把前面的4個結果集descoped，只取2個
28. select distinct detective_id from cases; // 列出去重之後的detective_id的值
29. select distinct detective_id from cases where hours_to_solve > 50; // 列出去重之後的detective_id的值
30. select * from cases order by start_date desc;
31. select * from cases order by criminal_id DESC, start_date ASC; // 多個欄位排序
32. select detective_id,AVG(hours_to_solve) from cases GROUP BY detective_id; //將結果集按照detective_id來group，並且對hours_to_solve欄位來取平均數
select detective_id,avg(hours_to_solve) as avg_hours from cases group by detective_id order by avg_hours; 帶orderby的groupby
33. select * from cases where detective_id = (select id from detectives where first_name="zhang");
select in select: 從detectives表中選擇first_name為zhang的id作為另外一個select的條件
選擇first_name為zhang的偵探他所有處理的case
34. select title from cases where detective_id IN (select id from detectives where phone_number like "8088%"); //選擇所有phone_number以8088開頭的偵探所處理的case的title
35. select first_name,last_name from detectives, cases where detectives.id = detective_id and criminial_id in (select id from criminals where first_name = "jim")
從detectives表中選中first/last_name，條件是detectives表的id欄位等於 cases表的detective_id欄位並且cases表的criminal_id欄位在criminals表中的first_name為jim的結果集中
36. SELECT column_name(s) FROM table_name1 UNION SELECT column_name(s) FROM table_name2
union將兩個select結果集合並(預設情況下去重),如果希望列出所有的則需要用UNION ALL關鍵字
37. select title, hours_to_solve as time from cases; // as關鍵字
38. select concat(first_name," ", last_name) as name from deteives // concat as
39. select d.first_name from detectives as d cross join criminals
foreign key is a column that links one table to another table by only accepting values that are already in a specified column of the second table: 外來鍵
40. create table parent (id int, constraint parent_pk primary key (id));
create table child (parent int, constraint parent_fk foreign key (parent) references parent(id));
41. select count(*) from detectives where phone_number like "31%";
42. select 317 as area_code, count(*) as count from detectives where phone_number like "317%"
43: 從student表中找到每一個學生：名稱，最小分數，最大分數（注意max,min實際上是通過test_score計算出來的，並不存在這一列）,並且groupby學生名
mysql> SELECT student_name, MIN(test_score), MAX(test_score) -> FROM student -> GROUP BY student_name;
44. count(*)
mysql> SELECT student.student_name,COUNT(*) -> FROM student,course -> WHERE student.student_id=course.student_id -> GROUP BY student_name;
45. IF(exp1,exp2,exp3): 如果exp1為true返回exp2,否則返回exp3.
mysql> SELECT IF(1>2,2,3); -> 3 mysql> SELECT IF(1<2,'yes','no'); -> 'yes' mysql> SELECT IF(STRCMP('test','test1'),'no','yes'); -> 'no'
46. RAND()函式
mysql> SELECT i, RAND() FROM t; +------+------------------+ | i | RAND() | +------+------------------+ | 1 | 0.61914388706828 | | 2 | 0.93845168309142 | | 3 | 0.83482678498591 | +------+------------------+
47.mysql中的聚集類函式:
AVG() :返回平均值；
COUNT():返回行數
COUNT(DISTINCT):返回不同值的個數
GROUP_CONCAT():返回一個連線後的字串
MAX()返回最大值；
MIN():返回最小值；
SUM():返回和值
注意上面的聚合類函式如果沒有GROUP BY子句，將對所有的row執行聚合
mysql> SELECT student_name, AVG(test_score) -> FROM student -> GROUP BY student_name;
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITH ROLLUP; +------+-------------+ | year | SUM(profit) | +------+-------------+ | 2000 | 4525 | | 2001 | 3010 | | NULL | 7535 | +------+-------------+
mysql> SELECT year, country, product, SUM(profit) -> FROM sales -> GROUP BY year, country, product WITH ROLLUP; +------+---------+------------+-------------+ | year | country | product | SUM(profit) | +------+---------+------------+-------------+ | 2000 | Finland | Computer | 1500 | | 2000 | Finland | Phone | 100 | | 2000 | Finland | NULL | 1600 | | 2000 | India | Calculator | 150 | | 2000 | India | Computer | 1200 | | 2000 | India | NULL | 1350 | | 2000 | USA | Calculator | 75 | | 2000 | USA | Computer | 1500 | | 2000 | USA | NULL | 1575 | | 2000 | NULL | NULL | 4525 | | 2001 | Finland | Phone | 10 | | 2001 | Finland | NULL | 10 | | 2001 | USA | Calculator | 50 | | 2001 | USA | Computer | 2700 | | 2001 | USA | TV | 250 | | 2001 | USA | NULL | 3000 | | 2001 | NULL | NULL | 3010 | | NULL | NULL | NULL | 7535 | +------+---------+------------+-------------+
48. User-defined variables:
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price; +---------+--------+-------+ | article | dealer | price | +---------+--------+-------+ | 0003 | D | 1.25 | | 0004 | D | 19.95 | +---------+--------+-------+
我們可以在一個statement中儲存一個user-defined variable,而在另外的statement中引用使用它。這允許你從一條查詢傳入value到另外一條查詢。
User variable這樣使用: @var_name, 不同使用中槓-，但是如果你希望使用-的話，可以用引號括起來@'my-var'
a)使用SET語句來建立user-defined variable(可以用=或者 := 作為賦值操作符)
SET @var_name = expr [, @var_name = expr] ... // 或者使用:=賦值
b)也使用不用SET語句，而直接用 := 來賦值建立user-defined variable:
mysql> SET @t1=1, @t2=2, @t3:=4; mysql> SELECT @t1, @t2, @t3, @t4 := @t1+@t2+@t3; +------+------+------+--------------------+ | @t1 | @t2 | @t3 | @t4 := @t1+@t2+@t3 | +------+------+------+--------------------+ | 1 | 2 | 4 | 7 | +------+------+------+--------------------+
48. group by子句:
一個grouped table是在evaluate一個<group by clause>時產生的一系列groups。一個group G就是一系列row的集合，這些row具有以下特徵： 每一個row的 grouping column GC,如果某行的GC欄位的值為GV，那麼這個group中的每一個GC欄位的值都是GV;而且如果R1是grouped table GT的group G1裡面的一個row的話， R2是GT裡面的一個ROW並且：對每一個grouping column GC欄位，R1的GC的值等於R2的GC的值的話，那麼R2就必然在G1中出現。GT表中每一個row都只能在一個group中. 一個group就可以被認為是一個table. Set函式操作在groups之上。
49. on delete 如何實現父表記錄一旦刪除子表對應記錄也能夠刪除
設想下面的場景： 一個buildings表，一個rooms表，一旦我們刪除了building表一個記錄，我們就應該刪除對應rooms表中該building的rooms
CREATE TABLE buildings ( building_no int(11) NOT NULL AUTO_INCREMENT, building_name varchar(255) NOT NULL, address varchar(355) NOT NULL, PRIMARY KEY (building_no) ) ENGINE=InnoDB; CREATE TABLE rooms ( room_no int(11) NOT NULL AUTO_INCREMENT, room_name varchar(255) NOT NULL, building_no int(11) NOT NULL, PRIMARY KEY (room_no), KEY building_no (building_no), CONSTRAINT rooms_ibfk_1 FOREIGN KEY (building_no) REFERENCES buildings (building_no) ON DELETE CASCADE ) ENGINE=InnoDB; INSERT INTO buildings(building_name,address) VALUES('ACME Headquaters','3950 North 1st Street CA 95134'), ('ACME Sales','5000 North 1st Street CA 95134') SELECT * FROM buildings; INSERT INTO rooms(room_name,building_no) VALUES('Amazon',1), ('War Room',1), ('Office of CEO',1), ('Marketing',2), ('Showroom',2) SELECT * FROM rooms DELETE FROM buildings WHERE building_no = 2 SELECT * FROM rooms //這時會將rooms表中對應building_no為2的記錄全部刪除
http://www.mysqltutorial.org/mysql-on-delete-cascade/
50. 如何找到on delete cascade對應會影響的表資訊？
USE information_schema; SELECT table_name FROM referential_constraints WHERE constraint_schema = 'classicmodels' AND referenced_table_name = 'buildings' AND delete_rule = 'CASCADE'
51. 如何查表中組合列出現的count次數？
SELECT id,difftag_id,difftaggable_id,difftaggable_type, count(*) as tags_count FROM difftaggables group by 3,4 order by tags_count
上面的sql程式碼中關鍵點為 group by 3,4 分別指difftaggable_id, difftaggable_type，這樣就列出所有被某資源打過的difftag數量
select `id`, `title`,`root`,`level`, count(*) as dcount from cates group by 2,3 order by dcount desc
http://stackoverflow.com/questions/11072036/select-combination-of-two-columns-and-count-occurrences-of-this-combination
52. 如何計數得出表中組合列出現的unique次數
select count(*) from ( select distinct folderid, userid from folder )
http://stackoverflow.com/questions/8519747/sql-how-to-count-unique-combination-of-columns
53. 若干不能建立外來鍵的錯誤解決辦法：
http://stackoverflow.com/questions/11907147/struggling-adding-this-mysql-foreign-key
http://stackoverflow.com/questions/19137519/trouble-adding-foreign-key
很大的原因是表已經有了資料，而這些資料可能又不滿足外來鍵的約束，所以建立外來鍵出錯！注意這時你即使set @@global.foreign_key_check=0貌似不報錯了，但是外來鍵依然未建立成功！唯一的解決辦法就是找出這些資料，手工刪除或者解決外來鍵不成功的問題。
SELECT * FROM `a` WHERE `a_id` not IN (SELECT id from b)
54. 查詢某個欄位為null
WHERE field IS NULL
55. Cannot drop index 'a_user_id_foreign': needed in a foreign key constraint解決辦法
這時你需要先手工把foreign key 刪除然後才能刪除index!
alter table a drop foreign key key_name_here
注意foreign key 就是這樣一堆東西: CONSTRAINT `ta_column_id_foreign` FOREIGN KEY (`column_id`) REFERENCES `b` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, 而index就是KEY `ta_column_id_foreign` (`column_id`)
56. 如何查詢到非unique的資料集
SELECT * from difftaggables WHERE difftaggable_id IN (SELECT difftaggable_id from (SELECT id,difftag_id,difftaggable_id,difftaggable_type, count(*) as tags_count FROM difftaggables group by 3,4 order by tags_count) AS hasduplicates WHERE tags_count > 1) ORDER by difftaggable_id
57. 關於key, primary key, foreign key，constrain等的概念
http://www.cnblogs.com/mydomain/archive/2011/11/10/2244233.html
http://www.cnblogs.com/Gile/p/MySQL.html
58. 複雜聚合函式以及子查詢等的查詢例子
select p1.* from homeworkuseranswers p1 inner join ( select max(created_at) as latest_date, count(user_id) as answer_count, user_id from homeworkuseranswers where homework_id=51 group by user_id ) p2 on p1.user_id = p2.user_id and p1.created_at = p2.latest_date
59. Where in (id1,id2,id5,id3,id4)如何也orderby這個陣列的value順序?
在mysql查詢中，我們可能有這樣的要求：我們給定id陣列列表，隨後希望獲取到按照在id陣列列表中出現的順序來獲取到對應的model資料，但是由於mysql會自動按照id的從小到大的順序來做排序，甚是煩人，總麼辦？
SELECT * FROM target_table WHERE id IN (4, 2, 6, 1) ORDER BY field(id, 4, 2, 6, 1);
如果你用laravel,則可以翻譯成:
$orderbyrawstring = 'field(id,' . implode(",", $ids).')'; YourModel::whereIn('id',$ids)->>orderByRaw($orderbyrawstring)->get();
60. SQLSTATE[HY000] [1862] Your password has expired. To log in you must change it using a client that supports expired passwords
mysql -uroot -p mysql> set password=PASSWORD("xxxx");
http://www.omgdba.com/fixed-your-password-has-expired-to-log-in-you-must-change-it-using-a-client-that-supports-expired-passwords-on-mysql-5-7.html
MySQL5.7 中增加了密碼過期機制，如果到期沒有修改密碼的話，會被資料庫拒絕連線或進入沙箱模式（可以連進去，卻無法操作）。但是總有些人會忘記定期修改密碼這回事，等到過期了無法登陸卻抓頭搔耳無所適從，本文記錄了一次“Your password has expired. To log in you must change it using a client that supports expired passwords”錯誤的修復過程，以供出現同樣狀況的道友參考。
- 使用mysql 5.7 安裝路徑中的mysql程式登陸資料庫，即~\mysql\bin\mysql.exe 檔案。
- 登陸後使用alter user ‘root’@’%’ identifity by ‘new-password’
- 即可使用新密碼登陸資料庫
- 如果不想讓密碼過期，可以使用以下方式：
- 針對全域性，可以修改配置檔案，修改
12[mysqld]default_password_lifetime=0 - 針對單個使用者，可是在修改密碼的時候加入PASSWORD EXPIRE NEVER;引數，形如：
1ALTER USER 'jeffrey'@'localhost' PASSWORD EXPIRE NEVER;
- 針對全域性，可以修改配置檔案，修改
- 以上。
更多詳情，請參閱Password Expiration Policy。
61. count if怎麼實現？
在sql查詢中，有時我們可能要聚合列出一個欄位，我們知道count函式是可以實現這個功能的，但是有的時候他不能完全滿足要求。比如，我們要判斷answer是正確的行數，這時簡單使用count就無法滿足了，這時我們可以使用SUM函式來曲線救國實現這個功能:
// 這條查詢語句返回一個使用者所有答對的習題聚合數目 select user_name,SUM(CASE isright WHEN 0 THEN 0 ELSE 1 END) AS right_count from answers
62. 什麼是corelated subquery?
a correlated subquery is a subquery that is executed once for each row
A correlated subquery returns results based on the column of the main query
select t1.* from Table1 t1 where t1.id in ( select t2.id from Table2 t2 where t2.value = t1.value )
63. \ 等特殊符號在query中出現的處理辦法
比如 where x_str = 'contains\\\\one back slash'
上面的字串等價於contains\one back slash