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> useintrepid_detectives;Databasechanged
mysql>show tables;
Emptyset (0.00 sec)
mysql> usemysqlDatabasechanged
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 (,)
mysql> useintrepid_detectives;Databasechanged
mysql> create tableinvestigations (-> title varchar(100),-> detective varchar(30),-> daysToSolve integer);
Query OK,0 rows affected (0.04sec)
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 * frominvestigations;+------------------+-----------------+-------------+
| 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 tabledetectives (-> id int not nullauto_increment,-> name varchar(100),-> phone_number varchar(10),->certificationDate date,-> constraint detectives_pk primary key(id));
Query OK,0 rows affected (0.05sec)
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 intodetectives(name,phone_number,certificationDate)-> values("zhangsan",12333,"2002-01-03");
Query OK,1 row affected (0.00sec)
mysql> select * fromdetectives;+----+----------+--------------+-------------------+
| 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 tableinvestigations rename cases;
Query OK,0 rows affected (0.01sec)
mysql>show tables;+-------------------------------+
| Tables_in_intrepid_detectives |
+-------------------------------+
| cases |
| detectives |
+-------------------------------+
2 rows in set (0.00sec)
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.04sec)
Records:4 Duplicates: 0 Warnings: 0mysql> select * fromcases;+------------------+-----------------+-------------+----------+
| 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 tabledetectives change certificationDate certification_date date;
Query OK,0 rows affected (0.03sec)
Records:0 Duplicates: 0 Warnings: 0mysql>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 * fromcases;+------------------+-----------------+-------------+----+
| 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 * fromcases;+------------------+-----------------+-------------+----+
| 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 * fromcases;+------------------+-----------------+-------------+----+
| 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 * fromcriminals->;+-----------+
| name |
+-----------+
| crimianl1 |
| crimianl2 |
| crimianl3 |
+-----------+
3 rows in set (0.00sec)
mysql> delete fromcriminals;
Query OK,3 rows affected (0.00sec)
mysql> select * fromcriminals;
Emptyset (0.00 sec)
20. drop table criminals; // 删除criminals表
21. update
22. updat casesset hours_to_solve = hours_to_solve*24; // 我们将hours_to_solve这一列的值从天变换为小时,每一行都重新计算
注意的是有一行由于value为null,所以不会更改:
mysql> select * fromcases;+------------------+-----------------+----------------+----+
| 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)-> FROMstudent-> GROUP BY student_name;
44. count(*)
mysql> SELECT student.student_name,COUNT(*)-> FROMstudent,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);-> 3mysql> SELECT IF(1<2,'yes','no');-> 'yes'mysql> SELECT IF(STRCMP('test','test1'),'no','yes');-> 'no'
46. RAND()函数
mysql> SELECT i, RAND() FROMt;+------+------------------+
| 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)-> FROMstudent-> GROUP BY student_name;
mysql> SELECT year, SUM(profit) FROM sales GROUP BY year WITHROLLUP;+------+-------------+
| year | SUM(profit) |
+------+-------------+
| 2000 | 4525 |
| 2001 | 3010 |
| NULL | 7535 |
+------+-------------+
mysql> SELECT year, country, product, SUM(profit)-> FROMsales-> GROUP BY year, country, product WITHROLLUP;+------+---------+------------+-------------+
| 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) FROMshop;
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一个时产生的一系列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 TABLEbuildings (
building_noint(11) NOT NULLAUTO_INCREMENT,
building_namevarchar(255) NOT NULL,
addressvarchar(355) NOT NULL,PRIMARY KEY(building_no)
) ENGINE=InnoDB;CREATE TABLErooms (
room_noint(11) NOT NULLAUTO_INCREMENT,
room_namevarchar(255) NOT NULL,
building_noint(11) NOT NULL,PRIMARY KEY(room_no),KEYbuilding_no (building_no),CONSTRAINTrooms_ibfk_1FOREIGN KEY(building_no)REFERENCESbuildings (building_no)ON DELETE CASCADE) ENGINE=InnoDB;INSERT INTObuildings(building_name,address)VALUES('ACME Headquaters','3950 North 1st Street CA 95134'),
('ACME Sales','5000 North 1st Street CA 95134')SELECT * FROMbuildings;INSERT INTOrooms(room_name,building_no)VALUES('Amazon',1),
('War Room',1),
('Office of CEO',1),
('Marketing',2),
('Showroom',2)SELECT * FROMroomsDELETE FROMbuildingsWHERE building_no = 2
SELECT * FROM rooms //这时会将rooms表中对应building_no为2的记录全部删除
http://www.mysqltutorial.org/mysql-on-delete-cascade/
50. 如何找到on delete cascade对应会影响的表信息?
USEinformation_schema;SELECTtable_nameFROMreferential_constraintsWHERE constraint_schema = 'classicmodels' ANDreferenced_table_name= 'buildings' ANDdelete_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 fromfolder
)
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) asanswer_count,user_id from homeworkuseranswers where homework_id=51 group by user_id) p2on 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 *
FROMtarget_tableWHERE 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’
即可使用新密码登陆数据库
如果不想让密码过期,可以使用以下方式:
针对全局,可以修改配置文件,修改
1
2
[mysqld]
default_password_lifetime=0
针对单个用户,可是在修改密码的时候加入PASSWORD EXPIRE NEVER;参数,形如:
1
ALTER USER'jeffrey'@'localhost'PASSWORD EXPIRE NEVER;
以上。
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.*
fromTable1 t1where t1.id in(selectt2.idfromTable2 t2where t2.value =t1.value
)
63. \ 等特殊符号在query中出现的处理办法
比如 where x_str = 'contains\\\\one back slash'
上面的字符串等价于contains\one back slash
64. 如何从值数组中临时形成一个表作为join的一方?
select * from (SELECT 1176 AS homeworkid, 117 ASuidUNION ALL
SELECT 1175, 117
UNION ALL
SELECT 1174, 117) as temp1