运算符
- / % * + - && || > < = !=
查询
- 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;