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;
/*注意 在判断值的时候,null是不能用'='来判断,应该用'is' */
SELECT * FROM sstud;
/* delete from 它是记录日志的删除 */
DELETE FROM sstud WHERE sname='Jack2';
/* TRUNCATE TABLE sstud 它是不记录日志的删除 */
创建视图
语法如下:
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第二天课堂记录
/*正式开始进入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;
/*复杂查询*/
/*where子句--between子句是两端都包含*/
SELECT * FROM sstud WHERE age BETWEEN 23 AND 25;
/*in 子句*/
SELECT * FROM sstud WHERE age IN(23,25,32);
/*like模糊查询*/
/* '%' 匹配所有 */
SELECT * FROM sstud WHERE sname LIKE '张%';
/* '_'匹配单字符 */
SELECT * FROM sstud WHERE sname LIKE '张_';
/*注意:通配符必须和like共同使用 */
UPDATE sstud SET age=20 WHERE age IS NULL;
/*注意 在判断值的时候,null是不能用'='来判断,应该用'is' */
SELECT * FROM sstud;
/* delete from 它是记录日志的删除 */
DELETE FROM sstud WHERE sname='Jack2';
/* TRUNCATE TABLE sstud 它是不记录日志的删除 */
/*创建视图*/
CREATE VIEW aview AS SELECT * FROM sstud WHERE age >25;
SELECT * FROM aview;
/*视图view是一个虚表,也可以进行操作*/
/*聚合函数*/
/* COUNT计数,AVG平均,ROUND四舍五入,SUM求和,MAX最大值,MIN最小值 */
SELECT COUNT(*) AS num FROM sstud;/*一般要专门给一个别名*/
SELECT COUNT(1) AS num FROM sstud;
/*以上两条语句结果一样*/
SELECT COUNT(age) AS num FROM sstud;
/*字符串为""也算有数据,只有为null时,才算是无数据 */
/*DISTINCT 数据相同时,只显示第一条,去除重复数据*/
SELECT DISTINCT saddress FROM sstud;
/*排序*/
/* order by ASC:升序 DESC:降序 */
SELECT * FROM sstud ORDER BY age ASC;
SELECT * FROM sstud ORDER BY age DESC;
/*ORDER BY 子句要放在最后*/
/*Exists函数 判断是否存在 不会一一过滤,相当与java的if */
SELECT * FROM sstud WHERE EXISTS (SELECT saddress FROM sstud WHERE age=20);
/*一条有bug的SQL语句 EXISTS语句只是用来判断的 返回true和false 非空就是true*/
/*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 */