sql小知识

2022-11-14 12:18:07

1：查询某一段落内的几条数据，按时间降序。 LIMIT 5,10; //检索记录行6-15

select * from FtpPublicFile order by date_created DESC limit #{page},10

2：创建视图，查询出某些类别的数据，保存在视图中。 || 的优先级高于and

create view newpoint as select * from newslist where (pid=2 and id=1) || (pid=2 and id=2);

3: 查询出某些字段的数据。

select * from tablec where cat in {'c1','c2'};

4：查询出表中的前几条数据，top在mysql中不支持，可以用limit代替。

select top 2 * from newpoint order by date_created desc;

5：查询某些类别之后，union进行合并. 查询的两个表相加。

(select * from newpoint where pid=2 and id=1) union (select * from newpoint where pid=2 and id=2);

6：查询pid和id个数大于2的分组，然后在每一个分组中找到最大的htmlid.

表newslist:

mysql> select * from newslist;

+---------------+-----+----+----------+--------------+------------+----------+

| htmlid        | pid | id | title    | date_created | titleImage | type     |

+---------------+-----+----+----------+--------------+------------+----------+

| 1533107342607 | 6   | 1  | 国内交流 | 2018-08-01   | NULL       | 本站原创 |

| 1533107362998 | 2   | 2  | 教学简报 | 2018-08-01   | NULL       | 本站原创 |

| 1533390471987 | 2   | 1  | test     | 2018-08-04   | NULL       | 本站原创 |

| 1533627497794 | 6   | 1  | 再次交流 | 2018-08-07   | NULL       | 本站原创 |

| 1533042165245 | 2   | 1  | dsfg     | 2018-07-31   | NULL       | 本站原创 |

| 1533092125456 | 2   | 2  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533092137401 | 2   | 2  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533092154982 | 2   | 3  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533092173217 | 2   | 4  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533092187850 | 2   | 1  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533107323029 | 2   | 5  | 学子风采 | 2018-08-01   | NULL       | 本站原创 |

+---------------+-----+----+----------+--------------+------------+----------+

11 rows in set

6.1首先创建临时表tempA，找出pid和id个数都大于2的分组。

order by htmlid desc可以不要

create temporary table tempA select distinct *  from (select * from newslist where pid in (select pid from newslist group by pid having count(pid)>2) and id in (select id from newslist group by id having count(id)>2) order by htmlid desc) tablea;

mysql> select * from tempA;

+---------------+-----+----+----------+--------------+------------+----------+

| htmlid        | pid | id | title    | date_created | titleImage | type     |

+---------------+-----+----+----------+--------------+------------+----------+

| 1533390471987 | 2   | 1  | test     | 2018-08-04   | NULL       | 本站原创 |

| 1533107362998 | 2   | 2  | 教学简报 | 2018-08-01   | NULL       | 本站原创 |

| 1533092187850 | 2   | 1  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533092137401 | 2   | 2  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533092125456 | 2   | 2  | 2        | 2018-08-01   | NULL       | 本站原创 |

| 1533042165245 | 2   | 1  | dsfg     | 2018-07-31   | NULL       | 本站原创 |

+---------------+-----+----+----------+--------------+------------+----------+

6 rows in set

6.2在临时表中查询每组的最大htmlid

mysql> select max(htmlid) from tempA group by pid,id;

+---------------+

| max(htmlid)   |

+---------------+

| 1533390471987 |

| 1533107362998 |

+---------------+

2 rows in set

6.3查询tempA*几个pid,id分组

mysql> select distinct pid,id from tempA;

+-----+----+

| pid | id |

+-----+----+

| 2   | 1  |

| 2   | 2  |

+-----+----+

2 rows in set

6.4将1,2步骤合在一起

mysql> select max(htmlid) from (select distinct *  from (select * from newslist where pid in (select pid from newslist group by pid having count(pid)>2) and id in (select id from newslist group by id having count(id)>2)) tablea) tableb group by pid,id;

+---------------+

| max(htmlid)   |

+---------------+

| 1533390471987 |

| 1533107362998 |

+---------------+

2 rows in set

7 创建数据库employees https://www.nowcoder.com/practice/ec1ca44c62c14ceb990c3c40def1ec6c?tpId=82&tqId=29754&tPage=1&rp=&ru=%2Fta%2Fsql&qru=%2Fta%2Fsql%2Fquestion-ranking

查找最晚入职员工的所有信息

CREATE TABLE `employees` (

`emp_no` int(11) NOT NULL,

`birth_date` date NOT NULL,

`first_name` varchar(14) NOT NULL,

`last_name` varchar(16) NOT NULL,

`gender` char(1) NOT NULL,

`hire_date` date NOT NULL,

PRIMARY KEY (`emp_no`));

mysql> insert into employees values(1,2018-01-03,'li','yafei','male','2018-01-02');

mysql> insert into employees values(2,2018-01-03,'li','yafei','male','2018-01-03');

mysql> insert into employees values(3,2018-01-03,'li','yafei','male','2018-01-04');

mysql> insert into employees values(5,2018-01-03,'li','yafei','male','2018-01-06');

mysql> insert into employees values(6,2018-01-03,'li','yafei','male','2018-01-02');

7.1 :查询最晚入职的所有员工，max函数可以用于字符串和日期

select * from employees where hire_date=(select max(hire_date) from employees);

7.2 :查找入职员工时间排名倒数第三的员工所有信息

select * from employees where hire_date=(select hire_date from (select distinct hire_date from employees order by hire_date desc) tableA limit 2,1);

7.3: 部门领导和薪水表，数据.

CREATE TABLE `dept_manager` (

`dept_no` char(4) NOT NULL,

`emp_no` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `salaries` (

`emp_no` int(11) NOT NULL,

`salary` int(11) NOT NULL,

`from_date` date NOT NULL,

`to_date` date NOT NULL,

PRIMARY KEY (`emp_no`,`from_date`));

INSERT INTO dept_manager VALUES('d001',10002,'1996-08-03','9999-01-01');

INSERT INTO dept_manager VALUES('d002',10006,'1990-08-05','9999-01-01');

INSERT INTO dept_manager VALUES('d003',10005,'1989-09-12','9999-01-01');

INSERT INTO dept_manager VALUES('d004',10004,'1986-12-01','9999-01-01');

INSERT INTO dept_manager VALUES('d005',10010,'1996-11-24','2000-06-26');

INSERT INTO dept_manager VALUES('d006',10010,'2000-06-26','9999-01-01');

INSERT INTO salaries VALUES(10001,60117,'1986-06-26','1987-06-26');

INSERT INTO salaries VALUES(10001,62102,'1987-06-26','1988-06-25');

INSERT INTO salaries VALUES(10001,66074,'1988-06-25','1989-06-25');

INSERT INTO salaries VALUES(10001,66596,'1989-06-25','1990-06-25');

INSERT INTO salaries VALUES(10001,66961,'1990-06-25','1991-06-25');

INSERT INTO salaries VALUES(10001,71046,'1991-06-25','1992-06-24');

INSERT INTO salaries VALUES(10001,74333,'1992-06-24','1993-06-24');

INSERT INTO salaries VALUES(10001,75286,'1993-06-24','1994-06-24');

查找各个部门当前(to_date='9999-01-01')领导当前薪水详情以及其对应部门编号dept_no

sql

select distinct salaries.emp_no,salary,salaries.from_date,salaries.to_date,dept_no from dept_manager,salaries  where salaries.emp_no=dept_manager.emp_no and salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01';

7.4:查找所有员工的last_name和first_name以及对应部门编号dept_no，也包括展示没有分配具体部门的员工

select e.last_name,e.first_name,d.dept_no from employees e left outer join dept_emp d on e.emp_no=d.emp_no;

8:concat 的用法，连接字符串

mysql> select concat('','','');

    -> //

+------------------------+

| concat('','','') |

+------------------------+

| 112233                 |

+------------------------+

1 row in set

9:各种数据类型的转换：cast(intNum as char);

https://www.cnblogs.com/yangchunze/p/6667502.html

10:sql中 find_in_set（str1,str2）用法，找到字符串str1在str2中的位置。https://www.cnblogs.com/mytzq/p/7090197.html

mysql> SELECT FIND_IN_SET('b','a,b,c,d');

+----------------------------+

| FIND_IN_SET('b','a,b,c,d') |

+----------------------------+

|                          2 |

+----------------------------+

1 row in set

11： group_concat ，将某一分组的某个字段连接起来。

https://baijiahao.baidu.com/s?id=1595349117525189591&wfr=spider&for=pc

mysql> select * from salarie;

+----------+--------+----+

| name     | salary | id |

+----------+--------+----+

| zhangsan |   2100 |  1 |

| lisi     |   2600 |  2 |

| wangwu   |   3000 |  3 |

| wangwu   |    200 |  4 |

| wangwu   |    200 |  5 |

| wangwu   |    200 |  6 |

| wangwu   |    200 |  7 |

| wangwu   |    200 |  8 |

| wangwu   |    200 |  9 |

| wangwu   |    200 | 10 |

| wangwu   |    200 | 11 |

| wangwu   |    200 | 12 |

| wangwu   |    200 | 13 |

| lisi     |    200 | 14 |

| lisi     |    200 | 15 |

| lisi     |    200 | 16 |

| lisi     |    200 | 17 |

| lisi     |    200 | 18 |

| lisi     |    200 | 19 |

| zhangsan |    400 | 20 |

| zhangsan |    400 | 21 |

| zhangsan |    400 | 22 |

| zhangsan |    400 | 23 |

| zhangsan |    400 | 24 |

| zhangsan |    400 | 25 |

| zhangsan |    400 | 26 |

| zhaoliu  |   1300 | 28 |

+----------+--------+----+

27 rows in set

-- find_in_set()用法
mysql> select * from salarie where find_in_set('wangwu',name);

+--------+--------+----+

| name   | salary | id |

+--------+--------+----+

| wangwu |   3000 |  3 |

| wangwu |    200 |  4 |

| wangwu |    200 |  5 |

| wangwu |    200 |  6 |

| wangwu |    200 |  7 |

| wangwu |    200 |  8 |

| wangwu |    200 |  9 |

| wangwu |    200 | 10 |

| wangwu |    200 | 11 |

| wangwu |    200 | 12 |

| wangwu |    200 | 13 |

+--------+--------+----+

11 rows in set

group_concat()

mysql> select name,salary,group_concat(id) from salarie group by(name);

+----------+--------+---------------------------+

| name     | salary | group_concat(id)          |

+----------+--------+---------------------------+

| lisi     |    200 | 14,15,16,17,18,19,2       |

| wangwu   |    200 | 13,12,11,10,3,4,5,6,7,8,9 |

| zhangsan |    400 | 26,25,24,23,22,21,20,1    |

| zhaoliu  |   1300 | 28                        |

+----------+--------+---------------------------+

4 rows in set

group_concat()

mysql> select name,salary,group_concat(id) from salarie group by(salary);

+----------+--------+-------------------------------------------+

| name     | salary | group_concat(id)                          |

+----------+--------+-------------------------------------------+

| lisi     |    200 | 14,19,18,17,16,15,13,12,11,10,9,4,5,6,7,8 |

| zhangsan |    400 | 24,25,26,20,21,22,23                      |

| zhaoliu  |   1300 | 28                                        |

| zhangsan |   2100 | 1                                         |

| lisi     |   2600 | 2                                         |

| wangwu   |   3000 | 3                                         |

+----------+--------+-------------------------------------------+

6 rows in set

12：mysql实现rank函数，mysql自带的没有rank函数，所以需要自己实现该函数。 https://blog.csdn.net/qq686867/article/details/79355760

A=B

A表中的薪水取出，从B表中查询出比该薪水大的个数，就是该薪水的排名。

select id,salary,name,(1+(select count(*) from salarie B where B.salary>A.salary)) as s_rank from salarie A order by s_rank;

mysql> select id,salary,name,(1+(select count(*) from salarie B where B.salary>A.salary)) as s_rank from salarie A order by s_rank;

+----+--------+----------+--------+

| id | salary | name     | s_rank |

+----+--------+----------+--------+

|  3 |   3000 | wangwu   |      1 |

|  2 |   2600 | lisi     |      2 |

|  1 |   2100 | zhangsan |      3 |

| 28 |   1300 | zhaoliu  |      4 |

| 22 |    400 | zhangsan |      5 |

| 26 |    400 | zhangsan |      5 |

| 25 |    400 | zhangsan |      5 |

| 24 |    400 | zhangsan |      5 |

| 23 |    400 | zhangsan |      5 |

| 21 |    400 | zhangsan |      5 |

| 20 |    400 | zhangsan |      5 |

| 18 |    200 | lisi     |     12 |

| 19 |    200 | lisi     |     12 |

|  5 |    200 | wangwu   |     12 |

| 17 |    200 | lisi     |     12 |

| 16 |    200 | lisi     |     12 |

| 15 |    200 | lisi     |     12 |

|  6 |    200 | wangwu   |     12 |

|  7 |    200 | wangwu   |     12 |

|  8 |    200 | wangwu   |     12 |

|  9 |    200 | wangwu   |     12 |

| 10 |    200 | wangwu   |     12 |

| 11 |    200 | wangwu   |     12 |

| 12 |    200 | wangwu   |     12 |

| 13 |    200 | wangwu   |     12 |

|  4 |    200 | wangwu   |     12 |

| 14 |    200 | lisi     |     12 |

+----+--------+----------+--------+

27 rows in set

排名，按顺序。 @salary_rank:=@salary_rank+1 ，从上往下查，每查询一个salary_rank就加1

mysql> set @salary_rank:=0;

select s.*,@salary_rank:=@salary_rank+1 from salarie s order by salary;

Query OK, 0 rows affected

+----------+--------+----+------------------------------+

| name     | salary | id | @salary_rank:=@salary_rank+1 |

+----------+--------+----+------------------------------+

| lisi     |    200 | 14 |                            1 |

| lisi     |    200 | 19 |                            2 |

| lisi     |    200 | 18 |                            3 |

| lisi     |    200 | 17 |                            4 |

| lisi     |    200 | 16 |                            5 |

| lisi     |    200 | 15 |                            6 |

| wangwu   |    200 | 13 |                            7 |

| wangwu   |    200 | 12 |                            8 |

| wangwu   |    200 | 11 |                            9 |

| wangwu   |    200 | 10 |                           10 |

| wangwu   |    200 |  9 |                           11 |

| wangwu   |    200 |  4 |                           12 |

| wangwu   |    200 |  5 |                           13 |

| wangwu   |    200 |  6 |                           14 |

| wangwu   |    200 |  7 |                           15 |

| wangwu   |    200 |  8 |                           16 |

| zhangsan |    400 | 24 |                           17 |

| zhangsan |    400 | 25 |                           18 |

| zhangsan |    400 | 26 |                           19 |

| zhangsan |    400 | 20 |                           20 |

| zhangsan |    400 | 21 |                           21 |

| zhangsan |    400 | 22 |                           22 |

| zhangsan |    400 | 23 |                           23 |

| zhaoliu  |   1300 | 28 |                           24 |

| zhangsan |   2100 |  1 |                           25 |

| lisi     |   2600 |  2 |                           26 |

| wangwu   |   3000 |  3 |                           27 |

+----------+--------+----+------------------------------+

27 rows in set

13: mysql中:= 与=的区别， select中使用=需要先set

:=与=都是赋值语句，但是=只有在set以及select语句中才为赋值语句，还有逻辑比较的功能

14: mysql实现row_number 的功能

select @rd := @rd+1 as rownum, id from (select @rd:=0, id from salarie) b;

15： mysql中的各种查询

https://blog.csdn.net/evankaka/article/details/46954751

16：mysql中实现tile

mysql> set @num=1;

select id,round((@num:=@num+1)/((select count(*) from salarie where name='wangwu')/3)) as tile from salarie where name='wangwu';

Query OK, 0 rows affected

+----+------+

| id | tile |

+----+------+

|  3 | 1    |

|  4 | 1    |

|  5 | 1    |

|  6 | 1    |

|  7 | 2    |

|  8 | 2    |

|  9 | 2    |

| 10 | 2    |

| 11 | 3    |

| 12 | 3    |

| 13 | 3    |

+----+------+

11 rows in set

17：mysql中函数大全

https://www.cnblogs.com/xuyulin/p/5468102.html

18: enum的用法，ENUM中为每个数据按照整数形式存储。

mysql> create table enum_table(

    -> e ENUM('fish','apple','dog') not null);

Query OK, 0 rows affected

mysql> insert into enum_table values('fish'),('dog'),('apple');

Query OK, 3 rows affected

Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from enum_table;

+-------+

| e     |

+-------+

| fish  |

| dog   |

| apple |

+-------+

3 rows in set

mysql> select e+0 from enum_table;

+-----+

| e+0 |

+-----+

|   1 |

|   3 |

|   2 |

+-----+

3 rows in set

高效Mysql

19：inet_aton() 将IP地址和整数的相互转化，可以将IP地址转化为整数存储，效率较高，因为整数比较比字符串比较算法不同，更快。

20：为某个字段(url)计算索引值，作为改字段存储的索引值。

mysql> select crc32('http://www.mysql.com');

+-------------------------------+

| crc32('http://www.mysql.com') |

+-------------------------------+

|                    1560514994 |

+-------------------------------+

1 row in set

创建一个函数，为某个子段计算索引值，可以使用触发器。

mysql> drop table if exists pseudoHash;

create table pseudoHash(

 id int not null auto_increment,

 url varchar(255) not null,

 crc32 int unsigned not null default 0,

 primary key(id)

);

delimiter//

drop trigger if exists insertCrc32ForUrl;//

create trigger insertCrc32ForUrl before insert on pseudoHash

    for each row

        begin

           set new.crc32=crc32(new.url);

        end;//

delimiter;

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

mysql> insert into pseudoHash(url) values('http://www.mysql.com');

Query OK, 1 row affected

mysql> select * from pseudoHash;

+----+----------------------+------------+

| id | url                  | crc32      |

+----+----------------------+------------+

|  1 | http://www.mysql.com | 1560514994 |

+----+----------------------+------------+

1 row in set

查询的时候要用字段和crc32的值同时查询，因为不同的字段可能crc32值相同,然后需要根据内容判断。

mysql> select * from pseudoHash where url='http://www.mysql.com' and crc32=crc32('http://www.mysql.com');

+----+----------------------+------------+

| id | url                  | crc32      |

+----+----------------------+------------+

|  1 | http://www.mysql.com | 1560514994 |

+----+----------------------+------------+

1 row in set

21: 计算每次sql执行时间 https://blog.csdn.net/lin443514407lin/article/details/54911120?locationNum=10&fps=1

22: 用户定义变量

set @var1:=1;     --  定义变量

set @var2:='abc';

select @var1:=@var1+1 as variable1 from t1;

set @var1:=select count(*) from t1;  -- 使用变量

23: 让应用直接访问多个数据源，绝对是个糟糕的设计，因为这会增加许多复杂的代码。一个好办法是，把这些代码都放在一个抽象层里，这个抽象层要完成以下这些任务。

a: 连接到正确的数据分块上，并查询数据。

b:分布式一致性校验。

c: 跨分块查询后的数据聚合。

d:跨分块连接。

e:锁和事务管理

f:创建新数据分块（至少是在运行时找到新的数据分块），然后重新平衡所有分块（如果你有时间实现它）。

数据分块的工具例如：mysql-proxy, 美团的meituan-proxy就是给数据库抽象层，提供这种作用。mysql-proxy, 美团的meituan-proxy 也可以做负载平衡。

24: 负载平衡算法

a: 随机，b:轮询 c: 最小连接优先d:最快响应优先e:散列化f:权重。

25:mysql通信协议：a:tcp/ip，b:socket c: 共享内存， d:信号量

26：备份工具

26.1 :mysqldump

将表从一台服务器复制到另一台

26.2 innodb引擎的 ibbackup 实现热备份。

26.3 perl脚本 mysqlhotcopy

26.4 ZRM 使用最广泛的

26: 权限

grant [privileges] on [objects] to [user];

grant select on test.salary to 'liyafei'@'127.0.0.1';  --  授予某个服务器，某个用户对salary表的查询权限

grant [privileges] on [objects] to [user] identified by [password];

revoke [privileges] on [objects] from [user];  -- 收回权限

grant all privileges on *.* to 'root'@'localhost' identified by [password] with grant option;  -- 赋予超级用于权限

27: mysql中的并发控制

27.1：多版本机制

a: 多版本时间戳排序，每当write(data)时，就创建一个data的版本。形成一个版本序列，当发出read(data）操作时，就立即返回其中一个版本的数据。

b:当事务Ti发出write(data)，如果事务Ti的开始时间TS(Ti) 小于（早于）data（Qk版本）的读取时间R-timestamp(Qk)。说明该数据已经被读取了，该事物不能写入了。如果TS(Ti)<R-timestamp(Qk) ，则系统回滚事务Ti。如果TS(Ti)>R-timestamp(Qk) ，创建data的一个新版本。

27.2 快照隔离

a: 快照隔离在事务开始执行时给它数据库的一份快照（snapshot）。然后，事务在该快照上操作，和其它并发事务完全隔离开。

b: 快照隔离的更新操作，采取先更新者获胜。如果两个事务T1和T2对同一数据项进行更新，那么当T1要更新时，会获得该数据项的更新锁，T2操作发现被其它事务跟新了，T2将中止。

c: 快照隔离不能保证可串行化

d: 快照隔离中，在查询语句之后，添加for update可以防止快照的并发修改。

select  * from t1 where id=2222 for update;

28: MySql中DECLARE CONTINUE HANDLER FOR NOT FOUND 解释https://blog.csdn.net/wanglha/article/details/51434148

1.解释：

在MySQL的存储过程中经常会看到这句话：DECLARE CONTINUE HANDLER FOR NOT FOUND。

它的含义是：若没有数据返回,程序继续,并将变量IS_FOUND设为0 ，这种情况是出现在select XX into XXX from tablename的时候发生的。

sql中处理异常 https://www.cnblogs.com/datoubaba/archive/2012/06/20/2556428.html

29: exists 的使用，exists只做判断不做筛选

mysql> select * from t1;

+------+-------+

| name | grade |

+------+-------+

| 3    |    64 |

| 3    |    59 |

| 5    |    80 |

| 5    |    64 |

+------+-------+

4 rows in set (0.02 sec)

mysql> select * from t1 where name=5 and exists(select * from t1 where grade=80);

+------+-------+

| name | grade |

+------+-------+

| 5    |    80 |

| 5    |    64 |

+------+-------+

2 rows in set (0.03 sec)

mysql>

子查询

mysql> select * from (select avg(grade) as avg_grade from t1 group by name)t where t.avg_grade>60;

+-----------+

| avg_grade |

+-----------+

| 61.5000   |

| 72.0000   |

+-----------+

2 rows in set (0.04 sec)

mysql> select * from (select avg(grade) as avg_grade from t1 group by name)t where t.avg_grade>62;

+-----------+

| avg_grade |

+-----------+

| 72.0000   |

+-----------+

1 row in set (0.04 sec)

码农公寓

相关文章