DML
#先创建测试数据库
CREATE DATABASE test_dml2;
USE test_dml2;
#学生表
CREATE TABLE t_stu(
id INT(10) PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(20) NOT NULL,
course_id INT(10) UNIQUE
);
#课程表
CREATE TABLE t_course(
id INT(10) PRIMARY KEY,
course_name VARCHAR(20) NOT NULL UNIQUE
);
#插入:
INSERT INTO t_stu(`name`,course_id) VALUES("Tom",1),("Jane",2),
("Lucy",3),("Junny",2),("Keng",2),("Quiey",3),("Oinca",1),("Houdb",5);
INSERT INTO t_course VALUES(1,"Java"),(2,"Python"),(3,"C++"),(5,"C#");
INSERT INTO t_stu(`name`) VALUES ("没课上的人");
#查询:
SELECT id,`name`,course_id FROM t_stu; #关键字需要用飘号标注
SELECT id,course_name FROM t_course;
#修改:
UPDATE t_stu SET course_id=6 WHERE `name`="Houdb";
UPDATE t_course SET id=6 WHERE course_name="C#";
#删除:
DELETE FROM t_stu WHERE id=8;
#AS(可以省略):给列起别名(只是在本次CRUD中起别名)
SELECT id AS "学号", `name` AS "名字" , course_id "课程号"
FROM t_stu WHERE course_id=2;
#DISTINCT(筛选去重)
SELECT DISTINCT course_id "所有课程编号" FROM t_stu;
#查询一个集合或者一个范围(使用between min and max
#或者in(..,..,..,...),not in(..,..,..,...))
SELECT * FROM t_stu WHERE course_id IN(1,2);
SELECT * FROM t_stu WHERE course_id BETWEEN 1 AND 2;
#空值的判断null :只能使用 is null 或者 is not null 来判断
SELECT * FROM t_stu WHERE course_id IS NULL;
SELECT * FROM t_stu WHERE course_id IS NOT NULL;
#null值计算处理使用 ifnull(字段, 默认值): 如果此字段不为null则使用原值,否则使用默认值
SELECT `name` , IFNULL(course_id,999) AS "course_id" FROM t_stu;
#模糊查询:like %:代表0~n个任意字符 _:代表1个任意一个字符
SELECT * FROM t_stu WHERE `name` LIKE "%a";
SELECT * FROM t_stu WHERE `name` LIKE "%a%";
SELECT * FROM t_stu WHERE `name` LIKE "_u%";
#group by:分组查询
SELECT course_id, COUNT(course_id) "本课程出现的次数" FROM t_stu GROUP BY course_id;
#having: 在分组查询后的条件
SELECT course_id, COUNT(course_id) "本课程出现的次数"
FROM t_stu
GROUP BY course_id
HAVING course_id IS NOT NULL;
#order by : 顺序排列(默认是升序asc ,降序desc)
SELECT * FROM t_stu WHERE course_id IS NOT NULL ORDER BY course_id;
SELECT * FROM t_stu WHERE course_id IS NOT NULL ORDER BY course_id DESC;
#limit : 分页查询 limit 起始索引值,要查询的条数
SELECT * FROM t_stu LIMIT 0 , 3;