对数据库进行简单的操作
1）增删改数据
-- 1)增加数据
INSERT INTO student VALUES(1,'lucy',12,'男');
INSERT INTO student VALUES(2,'jason',24,'男',89,98);
INSERT INTO student VALUES(3,'lucy',20,'',54,61);
INSERT INTO student(sid, sname) VALUES(3,'zhangsan');
-- 2)查询数据
SELECT * FROM student;
-- 3)修改数据
UPDATE student SET sgender='男' WHERE sid=3 -- 修改单个字段
UPDATE student SET sname='sher' ,sage=22 WHERE sid=3 -- 修改多个字段
-- 4)删除数据
DELETE FROM student; -- 删除表全部内容
DELETE FROM student WHERE sid = 3; -- 带条件的删除
2）查询数据（重点）
-- 1）查询所有列
SELECT * FROM student;
-- 2）查询指定列
SELECT sid,sname FROM student;
-- 3）查询时添加常量列
SELECT *,'java就业班'AS '班级' FROM student; -- 举例：在查询表时添加一个班级列
-- 4）查询时合并列
-- 先给student表加入两个成绩列并赋值
ALTER TABLE student ADD servlet INT, ADD jsp INT;
DESC student;
UPDATE student SET servlet=70,jsp=87 WHERE sid=1;
UPDATE student SET servlet=56,jsp=98 WHERE sid=2;
SELECT *,(servlet+jsp) AS '总成绩' FROM student;
-- 5）查询时去除重复记录
SELECT DISTINCT sgender FROM student;
-- 6）条件查询
SELECT * FROM student WHERE sname='jason' AND sgender='男'; -- 查询两个条件都符合的数据
SELECT * FROM student WHERE servlet>=70; -- 查询单个条件符合的数据
SELECT * FROM student WHERE jsp BETWEEN 90 AND 100; -- 包含90，包含100
SELECT * FROM student WHERE saddress IS NULL OR saddress=''; -- 判断数据是否为空会为null
SELECT * FROM student WHERE sname LIKE '张%'; -- 模糊匹配：姓’张‘的数据，%表示任意字符
SELECT * FROM student WHERE sname LIKE '张_'; -- _表示一个字符,如果三个字再加一个下划线
-- 7）聚合查询(使用聚合函数的查询)
SELECT SUM(servlet) AS 'total grades' FROM student;
SELECT AVG(servlet) AS 'average grades' FROM student;
SELECT MAX(servlet) AS 'max grades' FROM student;
SELECT MIN(servlet) AS 'min grades' FROM student;
SELECT COUNT(*) FROM student; -- 也可用某个字段代替*，显示的是该字段的数目（会排除null），*会统计所有字段的最大值
-- 8）分页查询
SELECT * FROM student LIMIT 0,2; -- limit第一个参数是起始行（从0开始计数），第二个参数是查询几行
-- 9）查询排序(默认显示顺序：插入顺序)
SELECT * FROM student ORDER BY sage ASC; -- asc:正序,desc:反序
SELECT * FROM student ORDER BY sage ASC, servlet DESC; -- 多个排序条件
-- 10）分组查询
SELECT sgender,COUNT(*) FROM student GROUP BY sgender; -- 先分组，再统计每一组的人数
-- 11）分组查询后筛选
SELECT sgender,COUNT(*) FROM student GROUP BY sgender HAVING COUNT(*)>1; -- 先查询人数，再用having筛选(分组之后不用where)
