对数据库进行简单的操作

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)

2018年10月30日