练习：
| <code>CREATE TABLE student2( id INT, NAME VARCHAR(20), chinese FLOAT, english FLOAT, math FLOAT ); </code> INSERT INTO student2(id,NAME,chinese,english,math) VALUES(1,'张小明',89,78,90); INSERT INTO student2(id,NAME,chinese,english,math) VALUES(2,'李进',67,53,95); INSERT INTO student2(id,NAME,chinese,english,math) VALUES(3,'王五',87,78,77); INSERT INTO student2(id,NAME,chinese,english,math) VALUES(4,'李一',88,98,92); INSERT INTO student2(id,NAME,chinese,english,math) VALUES(5,'李来财',82,84,67); INSERT INTO student2(id,NAME,chinese,english,math) VALUES(6,'张进宝',55,85,45); INSERT INTO student2(id,NAME,chinese,english,math) VALUES(7,'黄蓉',75,65,30); – 查询表中所有学生的信息。 SELECT * FROM student2; – 查询表中所有学生的姓名和对应的英语成绩。 SELECT NAME,english FROM student2; – 过滤表中英语成绩的重复数据 SELECT DISTINCT(english) FROM student2; – 使用别名表示学生分数。 SELECT NAME AS ‘姓名',chinese AS ‘语文',english AS ‘英语',math AS ‘数学' FROM student2; – 查询姓名为李一的学生成绩 SELECT * FROM student2 WHERE NAME='李一'; – 查询英语成绩大于等于90分的同学 SELECT * FROM student2 WHERE english>=90; – 查询总分大于200分的所有同学 SELECT * FROM student2 WHERE (chinese+english+math)>200; – 查询所有姓李的学生英语成绩。 SELECT NAME,english FROM student2 WHERE NAME LIKE ‘李%'; – 查询英语>80或者总分>200的同学 SELECT * FROM student2 WHERE english>80 OR (chinese+english+math)>200 – 统计每个学生的总分。 SELECT id,NAME,(chinese+english+math) AS ‘总成绩' FROM student2; – 在所有学生总分数上加10分特长分。 SELECT id,NAME,(chinese+english+math+10) AS ‘总成绩' FROM student2; |
数据约束
| <code>-- 1.1 默认值 CREATE TABLE student( id INT, NAME VARCHAR(20), address VARCHAR(20) DEFAULT '广州天河' -- 默认值 ) -- 当字段没有插入值的时候，mysql自动给该字段分配默认值 INSERT INTO student(id,NAME) VALUES(1,'张三'); -- 注意：默认值的字段允许为null INSERT INTO student(id,NAME,address) VALUE(2,'李四',NULL); INSERT INTO student(id,NAME,address) VALUE(3,'王五','广州番禺'); -- 1.2 非空 -- 需求： gender字段必须有值（不为null） CREATE TABLE student( id INT, NAME VARCHAR(20), gender VARCHAR(2) NOT NULL -- 非空 ) -- 非空字段必须赋值 INSERT INTO student(id,NAME) VALUES(1,'李四'); -- 非空字符不能插入null INSERT INTO student(id,NAME,gender) VALUES(1,'李四',NULL); -- 1.3 唯一 CREATE TABLE student( id INT UNIQUE, -- 唯一 NAME VARCHAR(20) ) INSERT INTO student(id,NAME) VALUES(1,'zs'); INSERT INTO student(id,NAME) VALUES(1,'lisi'); -- ERROR 1062 (23000): Duplicate entry '1' for key 'id' -- 1.4 主键（非空+唯一） CREATE TABLE student( id INT PRIMARY KEY, -- 主键 NAME VARCHAR(20) ) INSERT INTO student(id,NAME) VALUES(1,'张三'); INSERT INTO student(id,NAME) VALUES(2,'张三'); -- INSERT INTO student(id,NAME) VALUES(1,'李四'); -- 违反唯一约束： Duplicate entry '1' for key 'PRIMARY' -- insert into student(name) value('李四'); -- 违反非空约束： ERROR 1048 (23000): Column 'id' cannot be null -- 1.5 自增长 CREATE TABLE student( id INT(4) ZEROFILL PRIMARY KEY AUTO_INCREMENT, -- 自增长，从0开始 ZEROFILL 零填充 NAME VARCHAR(20) ) -- 自增长字段可以不赋值，自动递增 INSERT INTO student(NAME) VALUES('张三'); -- 不能影响自增长约束 DELETE FROM student; -- 可以影响自增长约束 TRUNCATE TABLE student; -- 1.6 外键约束 -- 员工表 CREATE TABLE employee( id INT PRIMARY KEY, empName VARCHAR(20), deptName VARCHAR(20) -- 部门名称 ) INSERT INTO employee VALUES(1,'张三','软件开发部'); INSERT INTO employee VALUES(2,'李四','软件开发部'); INSERT INTO employee VALUES(3,'王五','应用维护部'); -- 解决数据冗余高的问题：给冗余的字段放到一张独立表中 -- 独立设计一张部门表 CREATE TABLE dept( id INT PRIMARY KEY, deptName VARCHAR(20) ) -- 修改员工表 CREATE TABLE employee( id INT PRIMARY KEY, empName VARCHAR(20), deptId INT,-- 把部门名称改为部门ID -- 声明一个外键约束 CONSTRAINT emlyee_dept_fk FOREIGN KEY(deptId) REFERENCES dept(id) ON UPDATE CASCADE ON DELETE CASCADE -- ON CASCADE UPDATE ：级联修改 ) -- 问题: 该记录业务上不合法，员工插入了一个不存在的部门数据 INSERT INTO employee VALUES(5,'陈六',4); -- 违反外键约束： Cannot add or update a child row: a foreign key constraint fails (`day16`.`employee`, REFERENCES `dept` (`id`)) -- 1）当有了外键约束，添加数据的顺序： 先添加主表，再添加副表数据 -- 2）当有了外键约束，修改数据的顺序： 先修改副表，再修改主表数据 -- 3）当有了外键约束，删除数据的顺序： 先删除副表，再删除主表数据 -- 修改部门(不能直接修改主表) UPDATE dept SET id=4 WHERE id=3; -- 先修改员工表 UPDATE employee SET deptId=2 WHERE id=4; -- 删除部门 DELETE FROM dept WHERE id=2; -- 先删除员工表 DELETE FROM employee WHERE deptId=2; -- 级联修改（修改） -- 直接修改部门 UPDATE dept SET id=5 WHERE id=4; -- 级联删除 -- 直接删除部门 DELETE FROM dept WHERE id=1; </code> |
