Mysql必知必会

程序员文章站 2024-01-12 14:58:16

...

1。建表：

CREATE DATABASE IF NOT EXISTS `mydb` 
USE `mydb`;
CREATE TABLE IF NOT EXISTS `websites` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `url` varchar(255) NOT NULL DEFAULT '',
  `alexa` int(11) NOT NULL DEFAULT '0' COMMENT 'Alexa 排名',
  `country` char(10) NOT NULL DEFAULT '' COMMENT '国家',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

INSERT INTO `websites` (`id`, `name`, `url`, `alexa`, `country`) VALUES
    (1, 'Google', 'https://www.google.cm/', 1, 'USA'),
    (2, '淘宝', 'https://www.taobao.com/', 13, 'CN'),
    (3, '菜鸟教程', 'http://www.runoob.com', 4689, 'CN'),
    (4, '微博', 'http://weibo.com/', 20, 'CN'),
    (5, 'Facebook', 'https://www.facebook.com/', 3, 'USA'),
    (6, '*', 'https://*.com/', 0, 'IND');
USE `mydb`;

-- 导出  表 mydb.access_log 结构
CREATE TABLE IF NOT EXISTS `access_log` (
  `aid` int(11) NOT NULL AUTO_INCREMENT,
  `site_id` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  `date` date NOT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1;

INSERT INTO `access_log` (`aid`, `site_id`, `count`, `date`) VALUES
    (1, 1, 45, '2016-05-10'),
    (2, 3, 100, '2016-05-13'),
    (3, 1, 230, '2016-05-14'),
    (4, 2, 10, '2016-05-14'),
    (5, 5, 205, '2016-05-14'),
    (6, 4, 13, '2016-05-15'),
    (7, 3, 220, '2016-05-15'),
    (8, 5, 545, '2016-05-16'),
    (9, 3, 201, '2016-05-17');

2。体会inner join, left join和right join的不同。

select * from (access_log inner join websites on access_log.site_id = websites.id)
select * from (access_log left join websites on access_log.site_id = websites.id)
select * from (access_log right join websites on access_log.site_id = websites.id)

3。现在我们想要查找总访问量大于 200 的网站。

select websites.name, websites.url, sum(access_log.count) as visite_number 
from 
(websites inner join access_log on access_log.site_id = websites.id)
group by websites.name
having number > 200

4。然后我们想要查找总访问量大于 200 的网站并且按照访问量降序显示。
方法一：

select websites.name, websites.url, sum(access_log.count) as visits_number 
from 
(websites inner join access_log on access_log.site_id = websites.id)
group by websites.name
having visits_number > 200
order by visits_number desc

方法二：

select websites.name, websites.url, sum(access_log.count) as visits_number 
from websites, access_log 
where access_log.site_id = websites.id
group by websites.name
having visits_number > 200
order by visits_number desc

5。解析

1。数据分组(group by ):
select 列a, 聚合函数（聚合函数规范）
from 表名
where 过滤条件
group by 列a
having 条件
order by 标识
2。group by 字句也和where条件语句结合在一起使用。当结合在一起时，where在前，group by 在后。
即先对select xx from xx的记录集合用where进行筛选，然后再使用group by 对筛选后的结果进行分组。
使用having字句对分组后的结果进行筛选，语法和where差不多:having 条件表达式
需要注意having和where的用法区别：
1.having只能用在group by之后，对分组后的结果进行筛选(即使用having的前提条件是分组)。
2.where肯定在group by 之前，即也在having之前。
3.where后的条件表达式里不允许使用聚合函数，而having可以。
3。当一个查询语句同时出现了where,group by,having,order by的时候，执行顺序和编写顺序是：
1.执行where xx对全表数据做筛选，返回第1个结果集。
2.针对第1个结果集使用group by分组，返回第2个结果集。
4.针对第2个结集执行having xx进行筛选，返回第3个结果集。
3.针对第3个结果集中的每1组数据执行select xx，有几组就执行几次，返回第4个结果集。
5.针对第4个结果集排序。
参考链接

**问题：查找最晚入职员工的所有信息**
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`));

建表并插入语句

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`));


insert into employees(emp_no, birth_date, first_name, last_name, gender, hire_date) values (10, '1990-09-01', 'wenhao', 'wang', '1', '2017-09-02');
insert into employees(emp_no, birth_date, first_name, last_name, gender, hire_date) values (11, '1990-09-01', 'wenhao', 'wang', '1', '2017-09-02');
INSERT INTO employees VALUES(10001,'1953-09-02','Georgi','Facello','M','1986-06-26');
INSERT INTO employees VALUES(10002,'1964-06-02','Bezalel','Simmel','F','1985-11-21');
INSERT INTO employees VALUES(10003,'1959-12-03','Parto','Bamford','M','1986-08-28');
INSERT INTO employees VALUES(10004,'1954-05-01','Chirstian','Koblick','M','1986-12-01');
INSERT INTO employees VALUES(10005,'1955-01-21','Kyoichi','Maliniak','M','1989-09-12');
INSERT INTO employees VALUES(10006,'1953-04-20','Anneke','Preusig','F','1989-06-02');
INSERT INTO employees VALUES(10007,'1957-05-23','Tzvetan','Zielinski','F','1989-02-10');
INSERT INTO employees VALUES(10008,'1958-02-19','Saniya','Kalloufi','M','1994-09-15');
INSERT INTO employees VALUES(10009,'1952-04-19','Sumant','Peac','F','1985-02-18');
INSERT INTO employees VALUES(10010,'1963-06-01','Duangkaew','Piveteau','F','1989-08-24');
INSERT INTO employees VALUES(10011,'1953-11-07','Mary','Sluis','F','1990-01-22');

查询语句：第一个查询是一个集合，第二个仅仅是一个结果。
limit m,n : 表示从第m+1条开始，取n条数据；
limit n ：表示从第0条开始，取n条数据，是limit(0,n)的缩写。
本题limit 0,1 表示从第（0+1）条数据开始，取一条数据，即取出最晚入职员工。

select * from employees where hire_date = (select max(hire_date) from employees);
SELECT * FROM employees ORDER BY hire_date DESC limit 0, 1;

问题：查找入职员工时间排名倒数第三的员工所有信息
思考：倒数第三的可能有多个，先把没有重复的元素从大到小排序，选出倒数第三个，然后等于这个值的都选择出来。

SELECT * 
FROM employees
WHERE hire_date = (SELECT DISTINCT hire_date FROM employees ORDER BY hire_date DESC LIMIT 2,1);

Mysql必知必会
解答：

insert into actor(actor_id,first_name,last_name,last_update)
values (1,'PENELOPE','GUINESS','2006-02-15 12:34:33'), (2,'NICK','WAHLBERG','2006-02-15 12:34:33');

insert into actor 
values(1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'), 
(2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

题目：
将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values (‘1’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘2’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘3’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘4’, ‘10004’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’),
(‘5’, ‘10001’, ‘Senior Engineer’, ‘1986-06-26’, ‘9999-01-01’),
(‘6’, ‘10002’, ‘Staff’, ‘1996-08-03’, ‘9999-01-01’),
(‘7’, ‘10003’, ‘Senior Engineer’, ‘1995-12-03’, ‘9999-01-01’);
答案：

update titles_test set to_date = NULL , from_date = '2001-01-01'
where to_date = '9999-01-01'

查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t
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 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');
INSERT INTO salaries VALUES(10001,75994,'1994-06-24','1995-06-24');
INSERT INTO salaries VALUES(10001,76884,'1995-06-24','1996-06-23');
INSERT INTO salaries VALUES(10001,80013,'1996-06-23','1997-06-23');
INSERT INTO salaries VALUES(10001,81025,'1997-06-23','1998-06-23');
INSERT INTO salaries VALUES(10001,81097,'1998-06-23','1999-06-23');
INSERT INTO salaries VALUES(10001,84917,'1999-06-23','2000-06-22');
INSERT INTO salaries VALUES(10001,85112,'2000-06-22','2001-06-22');
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10002,72527,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10002,72527,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10002,72527,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10002,72527,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,40006,'1995-12-03','1996-12-02');
INSERT INTO salaries VALUES(10003,43616,'1996-12-02','1997-12-02');
INSERT INTO salaries VALUES(10003,43466,'1997-12-02','1998-12-02');
INSERT INTO salaries VALUES(10003,43636,'1998-12-02','1999-12-02');
INSERT INTO salaries VALUES(10003,43478,'1999-12-02','2000-12-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,40054,'1986-12-01','1987-12-01');
INSERT INTO salaries VALUES(10004,42283,'1987-12-01','1988-11-30');
INSERT INTO salaries VALUES(10004,42542,'1988-11-30','1989-11-30');
INSERT INTO salaries VALUES(10004,46065,'1989-11-30','1990-11-30');
INSERT INTO salaries VALUES(10004,48271,'1990-11-30','1991-11-30');
INSERT INTO salaries VALUES(10004,50594,'1991-11-30','1992-11-29');
INSERT INTO salaries VALUES(10004,52119,'1992-11-29','1993-11-29');
INSERT INTO salaries VALUES(10004,54693,'1993-11-29','1994-11-29');
INSERT INTO salaries VALUES(10004,58326,'1994-11-29','1995-11-29');
INSERT INTO salaries VALUES(10004,60770,'1995-11-29','1996-11-28');
INSERT INTO salaries VALUES(10004,62566,'1996-11-28','1997-11-28');
INSERT INTO salaries VALUES(10004,64340,'1997-11-28','1998-11-28');
INSERT INTO salaries VALUES(10004,67096,'1998-11-28','1999-11-28');
INSERT INTO salaries VALUES(10004,69722,'1999-11-28','2000-11-27');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
INSERT INTO salaries VALUES(10005,78228,'1989-09-12','1990-09-12');
INSERT INTO salaries VALUES(10005,82621,'1990-09-12','1991-09-12');
INSERT INTO salaries VALUES(10005,83735,'1991-09-12','1992-09-11');
INSERT INTO salaries VALUES(10005,85572,'1992-09-11','1993-09-11');
INSERT INTO salaries VALUES(10005,85076,'1993-09-11','1994-09-11');
INSERT INTO salaries VALUES(10005,86050,'1994-09-11','1995-09-11');
INSERT INTO salaries VALUES(10005,88448,'1995-09-11','1996-09-10');
INSERT INTO salaries VALUES(10005,88063,'1996-09-10','1997-09-10');
INSERT INTO salaries VALUES(10005,89724,'1997-09-10','1998-09-10');
INSERT INTO salaries VALUES(10005,90392,'1998-09-10','1999-09-10');
INSERT INTO salaries VALUES(10005,90531,'1999-09-10','2000-09-09');
INSERT INTO salaries VALUES(10005,91453,'2000-09-09','2001-09-09');
INSERT INTO salaries VALUES(10005,94692,'2001-09-09','9999-01-01');
INSERT INTO salaries VALUES(10006,43311,'1990-08-05','1991-08-05');
INSERT INTO salaries VALUES(10006,43311,'1991-08-05','1992-08-04');
INSERT INTO salaries VALUES(10006,43311,'1992-08-04','1993-08-04');
INSERT INTO salaries VALUES(10006,43311,'1993-08-04','1994-08-04');
INSERT INTO salaries VALUES(10006,43311,'1994-08-04','1995-08-04');
INSERT INTO salaries VALUES(10006,43311,'1995-08-04','1996-08-03');
INSERT INTO salaries VALUES(10006,43311,'1996-08-03','1997-08-03');
INSERT INTO salaries VALUES(10006,43311,'1997-08-03','1998-08-03');
INSERT INTO salaries VALUES(10006,43311,'1998-08-03','1999-08-03');
INSERT INTO salaries VALUES(10006,43311,'1999-08-03','2000-08-02');
INSERT INTO salaries VALUES(10006,43311,'2000-08-02','2001-08-02');
INSERT INTO salaries VALUES(10006,43311,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10007,56724,'1989-02-10','1990-02-10');
INSERT INTO salaries VALUES(10007,60740,'1990-02-10','1991-02-10');
INSERT INTO salaries VALUES(10007,62745,'1991-02-10','1992-02-10');
INSERT INTO salaries VALUES(10007,63475,'1992-02-10','1993-02-09');
INSERT INTO salaries VALUES(10007,63208,'1993-02-09','1994-02-09');
INSERT INTO salaries VALUES(10007,64563,'1994-02-09','1995-02-09');
INSERT INTO salaries VALUES(10007,68833,'1995-02-09','1996-02-09');
INSERT INTO salaries VALUES(10007,70220,'1996-02-09','1997-02-08');
INSERT INTO salaries VALUES(10007,73362,'1997-02-08','1998-02-08');
INSERT INTO salaries VALUES(10007,75582,'1998-02-08','1999-02-08');
INSERT INTO salaries VALUES(10007,79513,'1999-02-08','2000-02-08');
INSERT INTO salaries VALUES(10007,80083,'2000-02-08','2001-02-07');
INSERT INTO salaries VALUES(10007,84456,'2001-02-07','2002-02-07');
INSERT INTO salaries VALUES(10007,88070,'2002-02-07','9999-01-01');
INSERT INTO salaries VALUES(10008,46671,'1998-03-11','1999-03-11');
INSERT INTO salaries VALUES(10008,48584,'1999-03-11','2000-03-10');
INSERT INTO salaries VALUES(10008,52668,'2000-03-10','2000-07-31');
INSERT INTO salaries VALUES(10009,60929,'1985-02-18','1986-02-18');
INSERT INTO salaries VALUES(10009,64604,'1986-02-18','1987-02-18');
INSERT INTO salaries VALUES(10009,64780,'1987-02-18','1988-02-18');
INSERT INTO salaries VALUES(10009,66302,'1988-02-18','1989-02-17');
INSERT INTO salaries VALUES(10009,69042,'1989-02-17','1990-02-17');
INSERT INTO salaries VALUES(10009,70889,'1990-02-17','1991-02-17');
INSERT INTO salaries VALUES(10009,71434,'1991-02-17','1992-02-17');
INSERT INTO salaries VALUES(10009,74612,'1992-02-17','1993-02-16');
INSERT INTO salaries VALUES(10009,76518,'1993-02-16','1994-02-16');
INSERT INTO salaries VALUES(10009,78335,'1994-02-16','1995-02-16');
INSERT INTO salaries VALUES(10009,80944,'1995-02-16','1996-02-16');
INSERT INTO salaries VALUES(10009,82507,'1996-02-16','1997-02-15');
INSERT INTO salaries VALUES(10009,85875,'1997-02-15','1998-02-15');
INSERT INTO salaries VALUES(10009,89324,'1998-02-15','1999-02-15');
INSERT INTO salaries VALUES(10009,90668,'1999-02-15','2000-02-15');
INSERT INTO salaries VALUES(10009,93507,'2000-02-15','2001-02-14');
INSERT INTO salaries VALUES(10009,94443,'2001-02-14','2002-02-14');

SQL：

SELECT emp_no,count(emp_no) as t from salaries group by emp_no having t>15;

题目描述
将titles_test表名修改为titles_2017。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

ALTER TABLE titles_test RENAME TO titles_2017

上一篇： guzz 1.2.9 英文版发布新闻，求好心人转发

下一篇：通过ssh tunnel访问内网Subversion

Mysql必知必会