首页 运维知识 常用sql commands以及mysql问题解决方案日志

常用sql commands以及mysql问题解决方案日志

mysql workbench常用命令快捷键 ctrl+T ->创建新的sql query tab ctrl+shift+enter->执行当前的sql命令 https…

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字段来取平均数

常用sql commands以及mysql问题解决方案日志插图

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常用sql commands以及mysql问题解决方案日志插图1

34. select title from cases where detective_id IN (select id from detectives where phone_number like “8088%”); //选择所有phone_number以8088开头的侦探所处理的case的title

常用sql commands以及mysql问题解决方案日志插图2

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: 外键

常用sql commands以及mysql问题解决方案日志插图3

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”错误的修复过程,以供出现同样状况的道友参考。

  1. 使用mysql 5.7 安装路径中的mysql程序登陆数据库,即~\mysql\bin\mysql.exe 文件。
  2. 登陆后使用alter user ‘root’@’%’  identifity by ‘new-password’
  3. 即可使用新密码登陆数据库
  4. 如果不想让密码过期,可以使用以下方式:
    • 针对全局,可以修改配置文件,修改
      1
      2
      ]
      0
    • 针对单个用户,可是在修改密码的时候加入PASSWORD EXPIRE NEVER;参数,形如:
      1
      ;

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
)

常用sql commands以及mysql问题解决方案日志插图4

63. \ 等特殊符号在query中出现的处理办法

比如 where x_str = ‘contains\\\\one back slash’

上面的字符串等价于contains\one back slash

64. 如何从值数组中临时形成一个表作为join的一方?

select * from (SELECT 1176 AS homeworkid, 117 AS uid
UNION ALL
SELECT 1175, 117
UNION ALL
SELECT 1174, 117) as temp1
免责声明:文章内容不代表本站立场,本站不对其内容的真实性、完整性、准确性给予任何担保、暗示和承诺,仅供读者参考,文章版权归原作者所有。如本文内容影响到您的合法权益(内容、图片等),请及时联系本站,我们会及时删除处理。

作者: 小小编

为您推荐

dell R710 更换raid卡后,raid卡信息没有了,处理方案

dell R710 更换raid卡后,raid卡信息没有了,处理方案

1.将一台服务器(A)的硬盘依次拔出,按相同顺序插入另一台同样配置的服务器(B) 2.启动服务器(B) 3.按提示键盘按...
PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

PL SQL Developer 13连接Oracle数据库并导出数据详细操作教程方法

下载 并安装 PL SQL Developer 13,默认支持中文语言 ========================...
关于一条sql语句在mysql中是如何执行的

关于一条sql语句在mysql中是如何执行的

最近开始在学习mysql相关知识,自己根据学到的知识点,根据自己的理解整理分享出来,本篇文章会分析下一个sql语句在my...
关于sql注入姿势总结(mysql)

关于sql注入姿势总结(mysql)

前言 学习了sql注入很长时间,但是仍然没有系统的了解过,这次总结一波,用作学习的资料。 从注入方法分:基于报错、基于布...
关于Oracle SQL外连接

关于Oracle SQL外连接

SQL提供了多种类型的连接方式,它们之间的区别在于:从相互交叠的不同数据集合中选择用于连接的行时所采用的方法不同。 连接...

发表回复

返回顶部