MySQL学习笔记——20170810

运算符

• / % * + - && || > < = !=

---

查询

• SELECT 字段名 FROM 表名
  • WHERE 条件
  • GROUP BY 字段 (HAVING 条件)
  • ORDER BY 字段 ASC/DESC

示例：

SELECT * FROM t_user ORDER BY age ASC;-- 升序
SELECT * FROM t_user ORDER BY age DESC;-- 降序
SELECT * FROM t_user ORDER BY sal DESC, empno ASC;

SELECT COUNT(*) FROM t_user WHERE sal>2500;

SELECT depton,SUM(empno) FROM t_user GROUP BY depton;
SELECT depton,SUM(sal) FROM t_user GROUP BY depton HAVING SUM(sal)>9000;

---

Like 模糊查询

• % 匹配所有字符
• _ 匹配一个字符

示例：

SELECT * FROM t_user WHERE name LIKE "___";
SELECT * FROM t_user WHERE name LIKE "__i";
SELECT * FROM t_user WHERE name LIKE "Z%";
SELECT * FROM t_user WHERE name LIKE "_i%";
SELECT * FROM t_user WHERE name LIKE "%a%";

---

其他

• 子查询

例：

SELECT name,address,TIMESTAMPDIFF(YEAR, birthday, '2017-08-10') 'age' 
FROM t_student2 
WHERE address IN (
	SELECT address FROM t_student2 GROUP BY address HAVING AVG(TIMESTAMPDIFF(YEAR,birthday,'2017-08-10'))>25
);

• 字段 IN () -> 子段只要等于()集合内的任何一个值，就返回true
• 字段 = SOME () -> 同上，但是可以有> < >=等
  > 字段 SOME ()
  字段 >= SOME ()
• 字段 ALL () -> 字段必须与()内的全部符合，才返回true
• EXISTS () -> 存在

---

练习

-- 4
SELECT sid,sname,age FROM t_student;

-- 5
SELECT * FROM t_student WHERE age=20 && sex=1;
SELECT * FROM t_student WHERE sid=17001 || sex!=1;
SELECT * FROM t_student WHERE age>20 && age<25;

-- 6
SELECT * FROM t_student WHERE sid=17002 || sid=17008 || sid=17010;
SELECT * FROM t_student WHERE age>20 && age<25;
SELECT * FROM t_student WHERE LEFT(sname,1)='马';
SELECT * FROM t_student WHERE age IS NOT NULL;
SELECT * FROM t_student ORDER BY age DESC;
SELECT sex,COUNT(*) FROM t_student GROUP BY sex;

-- 7
SELECT COUNT(*) FROM t_student;
SELECT COUNT(*) FROM t_student WHERE age=25;
SELECT AVG(age) FROM t_student;

-- 0810
CREATE TABLE t_student2(
	num int,
	name VARCHAR(20),
	gender char(1),
	birthday DATE,
	department VARCHAR(20),
	address VARCHAR(50)
);

INSERT INTO t_student2 
VALUES 	
	(101,'张三','男','1990-01-12','计算机','重庆沙坪坝'),
	(102,'李四','男','1992-05-11','金融','重庆南岸'),
	(103,'王五','男','1990-03-12','计算机',NULL),
	(104,'刘大华','男','1993-01-01','体育','上海'),
	(105,'张小友','男','1995-03-02','音乐','上海'),
	(106,'李明','男','1991-11-12','音乐','重庆渝北'),
	(107,'郭成','男','1997-11-30','计算机','重庆渝中'),
	(108,'林霞','女','1992-05-05','计算机','重庆江北'),
	(109,'张玉','女','1990-05-05','金融','重庆江北'),
	(110,'张敏','女','1992-06-12','计算机','重庆沙坪坝'),
	(111,'王贤','女','1990-07-28','计算机','重庆南岸'),
	(112,'朱莹','女','1998-06-30','影视','北京'),
	(113,'周星星','男','1990-05-08','影视','香港'),
	(114,'王阿阿','男','1990-05-08','影视','香港');
	
SELECT * FROM t_student2 WHERE LEFT(birthday,7)='1990-05';

SELECT * FROM t_student2 WHERE name LIKE '___';

SELECT * FROM t_student2 WHERE RIGHT(name,1) = LEFT(RIGHT(name,2),1);

SELECT name,TIMESTAMPDIFF(YEAR,birthday,'2017-08-10') 'age' 
FROM t_student2 
WHERE TIMESTAMPDIFF(YEAR,birthday,'2017-08-10')>22 ORDER BY age DESC;

SELECT name,address,TIMESTAMPDIFF(YEAR, birthday, '2017-08-10') 'age' 
FROM t_student2 
WHERE address IN (
	SELECT address FROM t_student2 GROUP BY address HAVING AVG(TIMESTAMPDIFF(YEAR,birthday,'2017-08-10'))>25
);

SELECT address,COUNT(num) 
FROM t_student2 
GROUP BY address 
ORDER BY COUNT(num) ASC 
LIMIT 1;

INSERT INTO t_student2 
VALUES (200,'老王汪','男','1990-01-01','NTR',NULL);

SELECT * FROM t_student2 
WHERE name LIKE '___' && CONVERT(RIGHT(name,1) USING gbk) = CONVERT(LEFT(RIGHT(name,2),1) USING gbk);

SELECT * FROM t_student2 
ORDER BY CONVERT(RIGHT(LEFT(name,2),1) USING gbk) ASC;