AS 别名

SELECT sno AS 编号 FROM sstud;

结果显示的表头为‘编号’，但表数据还是用英文，不要用中文。

如图所示：

结果显示图：

表数据图：

复杂查询

where 子句 between and 子句和 in子句

between and子句

SELECT * FROM sstud WHERE age BETWEEN 23 AND 25;

执行语句结果如下：

in子句

SELECT * FROM sstud WHERE age IN (23,25,32);

执行语句结果如下：

like模糊查询

知识点1：’%’ 匹配所有,’_’匹配单字符，通配符必须和like共同使用

‘%’ 匹配所有

SELECT * FROM sstud WHERE sname LIKE '张%';

执行结果如图：

‘_’匹配单字符

SELECT * FROM sstud WHERE sname LIKE '张_';

执行结果如图：

知识点2

1、在判断值的时候，null是不能用’=’来判断，应该用’is’

2、delete from 它是记录日志的删除

3、TRUNCATE TABLE sstud 它是不记录日志的删除

UPDATE sstud SET age=20 WHERE age IS NULL;

SELECT * FROM sstud;

DELETE FROM sstud WHERE sname='Jack2';

创建视图

语法如下：

CREATE VIEW aview AS SELECT * FROM sstud WHERE age >25;

视图view是一个虚表，也可以进行操作

如：

SELECT * FROM aview;

聚合函数

COUNT计数,AVG平均,ROUND四舍五入,SUM求和,MAX最大值,MIN最小值等

SELECT COUNT(*) AS num FROM sstud;

SELECT COUNT(1) AS num FROM sstud;

上面两条语句执行结果一样，如图：

字符串为”“也算有数据，只有为null时，才算是无数据

SELECT COUNT(age) AS num FROM sstud;

执行结果：

DISTINCT 数据相同时，只显示第一条，去除重复数据：

SELECT DISTINCT saddress FROM sstud;

执行结果如图：

排序：

order by

ASC:升序

SELECT * FROM sstud ORDER BY age ASC;

执行结果如图：

DESC:降序

SELECT * FROM sstud ORDER BY age DESC;

执行结果如图：

注意

ORDER BY 子句要放在最后

Exists函数

判断是否存在 不会一一过滤，相当与java的if

GROUP BY:分组

使用语法：

SELECT saddress ,AVG(age) AS 平均年龄 FROM sstud GROUP BY saddress;

执行结果如图：

固定搭配 group by … having 条件

SELECT saddress , AVG(age) AS 平均年龄 FROM sstud GROUP BY saddress HAVING AVG(age)>24;

执行结果如图：

注意：

group by 子句中的条件用的是HAVING 而不是WHERE

mysql第二天课堂记录

CREATE DATAbase aa CHARACTER SET utf8;CREATE TABLE sstud(  sno VARCHAR(15) NOT NULL PRIMARY KEY,  sname VARCHAR(15) NOT NULL,  age INT,  saddress VARCHAR(30));INSERT INTO sstud VALUES('1005','张三',22,'益阳');INSERT INTO sstud VALUES('1006','张四',23,'益阳');INSERT INTO sstud VALUES('1007','李四',22,'益阳');INSERT INTO sstud VALUES('1008','刘备',24,'北京');SELECT * FROM sstud;SELECT sno AS 编号 FROM sstud;SELECT * FROM sstud WHERE age BETWEEN 23 AND 25;SELECT * FROM sstud WHERE age IN(23,25,32);SELECT * FROM sstud WHERE sname LIKE '张%';SELECT * FROM sstud WHERE sname LIKE '张_';UPDATE sstud SET age=20 WHERE age IS NULL;SELECT * FROM sstud;DELETE FROM sstud WHERE sname='Jack2';CREATE VIEW aview AS SELECT * FROM sstud WHERE age >25;SELECT * FROM aview;SELECT  COUNT(*) AS num FROM sstud;SELECT COUNT(1) AS num FROM sstud;SELECT COUNT(age) AS num FROM sstud;SELECT DISTINCT saddress FROM sstud;SELECT * FROM sstud ORDER BY age ASC;SELECT * FROM sstud ORDER BY age DESC;SELECT * FROM sstud WHERE  EXISTS (SELECT saddress FROM sstud WHERE age=20);SELECT saddress ,AVG(age) AS 平均年龄 FROM sstud GROUP BY saddress;SELECT saddress , AVG(age) AS 平均年龄 FROM sstud GROUP BY saddress HAVING AVG(age)>24;