mysql05

SELECT * FROM stu ORDER BY sex desc;
SELECT * FROM stu ORDER BY sex ASC;
SELECT * FROM stu ORDER BY id DESC;

SELECT * FROM stu WHERE sex=2 ORDER BY id DESC LIMIT 1;
UPDATE stu SET sex=1 WHERE id>2 AND id<8;
SELECT * FROM stu;
SELECT * FROM stu WHERE sex=1 ORDER BY id DESC LIMIT 1;
SELECT * FROM stu WHERE sex=2 ORDER BY sex DESC, birthday desc;
SELECT * FROM stu ORDER BY rand() DESC LIMIT 1;
SELECT sname,birthday,month(birthday) AS m FROM stu ORDER BY m DESC;

SELECT sname,birthday,mid(birthday,6,2) AS m FROM stu
WHERE birthday IS NOT NULL
ORDER BY m desc;

SELECT sname,left(birthday,4) AS y FROM stu ORDER BY y desc;
SELECT * FROM stu WHERE year(birthday)>=1990 ORDER BY birthday ASC LIMIT 1;

SELECT field(‘a’,‘f’,‘c’,‘b’);
SELECT sname, left(sname,1) AS s FROM stu ORDER BY FIELD(s,‘李’,‘郑’) DESC;

SELECT count() FROM stu;
SELECT count() FROM stu WHERE sex=‘man’;
SELECT count(*) FROM stu WHERE class_id IS NOT NULL;
SELECT count(class_id) FROM stu;

SELECT * FROM stu ORDER BY class_id DESC LIMIT 1;
SELECT * FROM stu WHERE class_id IS NOT NULL ORDER BY class_id DESC;
SELECT max(class_id) FROM stu;
SELECT min(class_id) FROM stu;
SELECT year(birthday) FROM stu WHERE age=(SELECT min(age) FROM stu);
SELECT max(YEAR(birthday)) FROM stu;
SELECT min(click) FROM artical;
SELECT * FROM artical WHERE click=(SELECT min(click) FROM stu);

SET sql_mode=(SELECT REPLACE(@@sql_mode,‘ONLY_FULL_GROUP_BY’,’’));
SELECT min(click),tile FROM artical;
SELECT sum(click) FROM artical;
SELECT AVG(click) FROM artical;
SELECT * FROM artical WHERE click<=(SELECT AVG(click) FROM artical);
SELECT sname,timestampdiff(YEAR, birthday, now()) FROM stu;
SELECT sname,AVG(timestampdiff(YEAR, birthday, now())) FROM stu;
SELECT ROUND(AVG(timestampdiff(YEAR, birthday, now()))) FROM stu;

DROP TABLE IF EXISTS shop;
DROP TABLE IF EXISTS attendance,shop;
DROP TABLE shop;
DROP DATABASE IF EXISTS attendance;
DROP DATABASE IF EXISTS test1;

SELECT class_id FROM stu;
SELECT DISTINCT class_id FROM stu;
SELECT DISTINCT class_id FROM stu WHERE class_id IS NOT NULL;
SELECT DISTINCT class_id,sname FROM stu; – 只有class_id和sname都是一样的时候才会被去掉
SELECT count(DISTINCT class_id,sname) FROM stu;
SELECT * FROM stu GROUP BY class_id ;
SELECT count(class_id),class_id FROM stu GROUP BY class_id;
SELECT count(class_id),class_id FROM stu WHERE class_id IS NOT NULL GROUP BY class_id;

SELECT count(class_id),class_id FROM stu
WHERE class_id IS NOT NULL AND sex=‘woman’
GROUP BY class_id;

SELECT count(class_id),class_id FROM stu
WHERE class_id IS NOT NULL AND sex=‘man’
GROUP BY class_id;

SELECT class_id,sname,min(birthday)AS b FROM stu GROUP BY class_id;
SELECT * FROM stu WHERE birthday IN (SELECT max(birthday) FROM stu GROUP BY class_id);
SELECT * FROM stu WHERE birthday IN (SELECT max(birthday) FROM stu);
SELECT max(birthday) FROM stu;
SELECT class_id,max(birthday) FROM stu GROUP BY class_id;

SELECT count(*),class_id FROM stu
WHERE class_id IS NOT NULL
GROUP BY class_id,sex
ORDER BY class_id;
– 人数超过两个的班级

SELECT class_id,count(*) AS c FROM stu
GROUP BY class_id
HAVING c>=2; – 这里不能使用where

CREATE TABLE attendance(id int PRIMARY KEY AUTO_INCREMENT);
SELECT date(date_add(now(), INTERVAL 0-WEEKDAY(now()) day));
ALTER TABLE attendance ADD stu_id int;
ALTER TABLE attendance ADD created_at datetime;
ALTER TABLE attendance MODIFY created_at datetime;
INSERT INTO attendance (stu_id, created_at) VALUES (1, ‘2020-02-02 10:10:10’), (2, ‘2020-02-08 07:10:10’),(3, ‘2020-01-31 08:00:00’);
INSERT INTO attendance (stu_id, created_at) VALUES (1, ‘2020-02-06 07:00:00’), (4, ‘2020-02-01 11:00:00’);

SELECT stu_id,count(stu_id) FROM attendance
WHERE date(created_at)>=date(date_add(now(), INTERVAL 0-weekday(now()) DAY))
GROUP BY stu_id;

SELECT count(*) AS c,stu_id FROM attendance
WHERE date(created_at)>=date(date_add(now(), INTERVAL 0-weekday(now()) DAY))
AND time(created_at)<=‘08:30:00’
GROUP BY stu_id
HAVING c>=2;

SELECT stu_id, count(*) AS c FROM attendance
WHERE date(created_at)>=date(date_add(now(), INTERVAL 0-weekday(now()) DAY))
AND time(created_at)<=‘08:30:00’
GROUP BY stu_id
ORDER BY c DESC LIMIT 1;

SELECT date(created_at),stu_id, count() AS c FROM attendance
WHERE date(created_at)>=date(date_add(now(), INTERVAL 0-WEEKDAY(now()) DAY))
AND time(created_at)>‘08:30:00’
GROUP BY date(created_at)
ORDER BY count()
LIMIT 1;

SELECT left(sname,1) AS s FROM stu
GROUP BY s
HAVING count()>=2
ORDER BY count() DESC
LIMIT 1;