目录
1.数据库约束
1.1NOT NULL:非空约束
mysql> create table student (id int, name varchar(20) not null);
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | NO | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into student values (null,null);
ERROR 1048 (23000): Column 'name' cannot be null //id可以为空但是name不能为空
1.2UNIQUE:唯一值约束
不允许存在两行数据,在这个指定列上重复。
mysql> create table student(id int unique,name varchar(20));
Query OK, 0 rows affected (0.03 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | UNI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into student values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(1,'wangwu');
ERROR 1062 (23000): Duplicate entry '1' for key 'id'//重复条目
1.3DEFAULT:默认值约束
可自定义默认值
mysql> create table student (id int,name varchar(20) default '未命名');
Query OK, 0 rows affected (0.05 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | 未命名 | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into student (id) values (1);
Query OK, 1 row affected (0.04 sec)
mysql> select * from student;
+------+--------+
| id | name |
+------+--------+
| 1 | 未命名 |
+------+--------+
1 row in set (0.00 sec)
1.4PRIMARY KEY:主键约束
主键就是一条数据的身份标识,是not null 和 unique 的合体;
在数据库中,通过主键约束来指定某个列作为主键;
一个表中只能有一个主键
mysql> create table student (id int primary key,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> desc student;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into student values(1,'zhangsan');
Query OK, 1 row affected (0.00 sec)
mysql> insert into student values(null,'zhangsan');
ERROR 1048 (23000): Column 'id' cannot be null
mysql> insert into student values(1,'zhangsan');
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
自增主键:auto_increment,允许客户端在插入数据时,不手动指定主键的值,即可以是null,而是交给MySQL自行分配,确保分配出来的这个主键的值是和之前不重复的,也可以手动指定值。
mysql> create table student (id int primary key auto_increment,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> insert into student values(null,'zhangsan');
Query OK, 1 row affected (0.04 sec)
mysql> insert into student values(null,'wangwu');
Query OK, 1 row affected (0.04 sec)
mysql> insert into student values(8,'zhaoliu');
Query OK, 1 row affected (0.04 sec)
mysql> insert into student values(null,'sunquan');
Query OK, 1 row affected (0.04 sec)
mysql> select * from student;
+----+----------+
| id | name |
+----+----------+
| 1 | zhangsan |
| 2 | lisi |
| 8 | zhaoliu |
| 9 | sunquan |
+----+----------+
4 rows in set (0.00 sec)
1.5FOREIGN KEY:外键约束
格式:foreign key(子表中受到约束的列)reference 父表 (父表中起到约束效果的列);
创建外键时,修改的是子表的代码,父表代码不受影响。
mysql> create table class (classId int primary key,className varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> insert into class values (1,'class602'),
-> (2,'class709'),
-> (3,'class303');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> create table student (studentId int primary key auto_increment, name varchar(20),classId int,
-> foreign key(classId) references class(classId));
Query OK, 0 rows affected (0.03 sec)
注意:
1> 插入/修改子表中受约束的这一列的数据,要保证结果在父表中存在;
2> 删除/修改父表中的数据,要看看这个数据是否在子表中被使用了,如果被使用了,则不能进行删除/修改;
3> 删表时要先删子表再删父表,否则删不掉
mysql> insert into student values(1,'zhangsan',8);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`javasql`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
mysql> insert into student values(1,'zhangsan',3);
Query OK, 1 row affected (0.01 sec)
mysql> delete from class where classId = 2;
Query OK, 1 row affected (0.01 sec)
mysql> delete from class where classId = 3;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`javasql`.`student`, CONSTRAINT `student_ibfk_1` FOREIGN KEY (`classId`) REFERENCES `class` (`classId`))
mysql> drop table class;
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> drop table student;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table class;
Query OK, 0 rows affected (0.01 sec)
1.6CHECK约束
写一个具体的条件表达式,符合条件则可以插入/修改,不符合条件就失败;
对MySQL5来说,并不支持check,写上不会报错也没啥作用。
2.表的设计
关系是实体和实体之间的关联关系,数据库中有四种关系:
一对一,一对多,多对多,没关系
2.1一对一
举例:学生与账号
1> student ( id, name, age, classId……)
account ( accountId, username, password, studentId……)
2> student ( id, name, age, classId, accountId……)
account ( accountId, username, password……)
3> student_account ( id, name, classId, username, password……) 两张表放一起
2.2一对多
举例:学生与班级
class (classId, className……)
student (studentId, name, classId……)
2.3多对多
表示多对多关系时需要引入一个关联表
举例:学生与课程
student (id, name……)
course (id, name……)
student_course ( studentId, courseId) --- 关联表
3.新增
格式:insert into 表名 select ……
将 插入 insert 和 查询 select 操作合并在一起,即将查询出来的结果直接插入另一个表中,注意:两个表列的数目,类型,约束等要一一对应。
mysql> create table student (id int,name varchar(20));
Query OK, 0 rows affected (0.05 sec)
mysql> create table student2 (id int,name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student values (1,'zhangsan'),(2,'lisi'),(3,'wangwu');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into student2 select * from student;
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from student2;
+------+----------+
| id | name |
+------+----------+
| 1 | zhangsan |
| 2 | lisi |
| 3 | wangwu |
+------+----------+
3 rows in set (0.00 sec)
mysql> insert into student2 select name from student;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> insert into student2 select name,id from student;
ERROR 1366 (HY000): Incorrect integer value: 'zhangsan' for column 'id' at row 1
4.查询
4.1聚合查询
4.1.1聚合查询
函数 | 说明 |
---|---|
COUNT( [ DISTINCT ] expr) | 返回查询到的数据的 数量 |
SUM( [ DISTINCT ] expr) | 返回查询到的数据的 总和,不是数字没有意义 |
AVG( [ DISTINCT ] expr) | 返回查询到的数据的 平均值,不是数字没有意义 |
MAX( [ DISTINCT ] expr) | 返回查询到的数据的 最大值,不是数字没有意义 |
MIN( [ DISTINCT ] expr) | 返回查询到的数据的 最小值,不是数字没有意义 |
COUNT:
计算数据的数量时,有时会不计入空值的个数;
空格不能乱敲,count () 会报错,正确为count()。
mysql> insert into examresult values (null,null,null,null,null);
Query OK, 1 row affected (0.00 sec)
mysql> select * from examresult;
+------+----------+---------+------+---------+
| id | name | Chinese | Math | English |
+------+----------+---------+------+---------+
| 1 | zhangsan | 67.0 | 98.0 | 56.0 |
| 2 | lisi | 87.0 | 58.0 | 98.0 |
| 3 | wangwu | 88.0 | 66.0 | 90.0 |
| 5 | 孙权 | 67.0 | 89.0 | 72.0 |
| 6 | 孙尚香 | 80.0 | 98.0 | 90.0 |
| 7 | 孙悟空 | NULL | NULL | NULL |
| NULL | NULL | NULL | NULL | NULL |
+------+----------+---------+------+---------+
7 rows in set (0.00 sec)
mysql> select count(*) from examresult;
+----------+
| count(*) |
+----------+
| 7 |
+----------+
1 row in set (0.00 sec)
mysql> select count(name) from examresult;
+-------------+
| count(name) |
+-------------+
| 6 |
+-------------+
1 row in set (0.01 sec)
SUM:
将每一列的这一行先转化成double,再按照double的方式进行累加;
若无法相加,也能编译成功,但会报警告。
mysql> select sum(Chinese) from examresult;
+--------------+
| sum(Chinese) |
+--------------+
| 389.0 |
+--------------+
1 row in set (0.01 sec)
mysql> select sum(name) from examresult;
+-----------+
| sum(name) |
+-----------+
| 0 |
+-----------+
1 row in set, 6 warnings (0.00 sec)
mysql> show warnings;
+---------+------+----------------------------------------------+
| Level | Code | Message |
+---------+------+----------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'zhangsan' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'lisi' |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'wangwu' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙权' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙尚香' |
| Warning | 1292 | Truncated incorrect DOUBLE value: '孙悟空' |
+---------+------+----------------------------------------------+
6 rows in set (0.00 sec)
AVG、MAX、MIN:
mysql> select avg(Chinese + Math + English) from examresult;
+-------------------------------+
| avg(Chinese + Math + English) |
+-------------------------------+
| 240.80000 |
+-------------------------------+
1 row in set (0.01 sec)
mysql> select max(Chinese) from examresult;
+--------------+
| max(Chinese) |
+--------------+
| 88.0 |
+--------------+
1 row in set (0.01 sec)
mysql> select min(English) from examresult;
+--------------+
| min(English) |
+--------------+
| 56.0 |
+--------------+
1 row in set (0.00 sec)
4.1.2GROUP BY
指定这一列,按照这一列进行分组,这一列中,数值相同的行会被放到同一组;
注意:查询时select中指定的列必须是当前group by 指定的列,如果想用其他的列,其他的列必须放到聚合函数中,否则查询的结果无意义。
mysql> select * from emp;
+----+--------+--------+--------+
| id | name | role | salary |
+----+--------+--------+--------+
| 1 | 张三 | 讲师 | 10000 |
| 2 | 李四 | 讲师 | 11000 |
| 3 | 王五 | 讲师 | 12000 |
| 4 | 赵六 | 学管师 | 9000 |
| 5 | 田七 | 学管师 | 8500 |
| 6 | 龙傲天 | 老板 | 100000 |
+----+--------+--------+--------+
6 rows in set (0.00 sec)
mysql> select role,avg(salary) from emp group by role;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 学管师 | 8750.0000 |
| 老板 | 100000.0000 |
| 讲师 | 11000.0000 |
+--------+-------------+
3 rows in set (0.01 sec)
mysql> select role,name ,avg(salary) from emp group by role; //无意义
+--------+--------+-------------+
| role | name | avg(salary) |
+--------+--------+-------------+
| 学管师 | 赵六 | 8750.0000 |
| 老板 | 龙傲天 | 100000.0000 |
| 讲师 | 张三 | 11000.0000 |
+--------+--------+-------------+
3 rows in set (0.00 sec)
分组前的条件语句:where
分组后的条件语句:having
mysql> select role,avg(salary) from emp where name != '张三' group by role;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 学管师 | 8750.0000 |
| 老板 | 100000.0000 |
| 讲师 | 11500.0000 |
+--------+-------------+
3 rows in set (0.01 sec)
mysql> select role,avg(salary) from emp group by role having avg(salary) < 20000;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 学管师 | 8750.0000 |
| 讲师 | 11000.0000 |
+--------+-------------+
2 rows in set (0.01 sec)
mysql> select role,avg(salary) from emp where name != '张三' group by role having avg(salary) < 20000;
+--------+-------------+
| role | avg(salary) |
+--------+-------------+
| 学管师 | 8750.0000 |
| 讲师 | 11500.0000 |
+--------+-------------+
2 rows in set (0.00 sec)
4.2联合查询
把所有表结合再筛选,效率十分低下。
笛卡尔积:任意两张表都可以算,但是如果两张表没有关系,计算的结果无意义。
格式:select * from 表名, 表名……
连接条件:筛选掉无意义的数据。
建表阶段:
mysql> create table classes(id int primary key auto_increment, name varchar(20), `desc` varchar(100));
Query OK, 0 rows affected (0.02 sec)
mysql> create table student(id int primary key auto_increment, sn varchar(20), name varchar(20), qq_mail varchar(20), class_id int);
Query OK, 0 rows affected (0.02 sec)
mysql> create table course(id int primary key auto_increment, name varchar(20));
Query OK, 0 rows affected (0.02 sec)
mysql> create table score(score decimal(3,1), student_id int, course_id int);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into classes(name, `desc`) values
-> ('计算机系2022级16班', '计算机原理、C语言、Java语言'),
-> ('中文系2022级3班','中国传统文学、古代文学鉴赏'),
-> ('自动化2022级9班','机械自动化');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> insert into student(sn, name, qq_mail, class_id) values
-> ('09982','张三','[email protected]',1),
-> ('00835','李四',null,1),
-> ('00391','王五',null,1),
-> ('00031','赵六','[email protected]',1),
-> ('00054','田七',null,1),
-> ('51234','钱八','[email protected]',2),
-> ('83223','孙九',null,2),
-> ('09527','吴十','[email protected]',2);
Query OK, 8 rows affected (0.01 sec)
Records: 8 Duplicates: 0 Warnings: 0
mysql> insert into course(name) values
-> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
Query OK, 6 rows affected (0.00 sec)
Records: 6 Duplicates: 0 Warnings: 0
mysql> insert into score(score, student_id, course_id) values
-> (70.5, 1, 1),(98.5, 1, 3),(33, 1, 5),(98, 1, 6),
-> (60, 2, 1),(59.5, 2, 5),
-> (33, 3, 1),(68, 3, 3),(99, 3, 5),
-> (67, 4, 1),(23, 4, 3),(56, 4, 5),(72, 4, 6),
-> (81, 5, 1),(37, 5, 5),
-> (56, 6, 2),(43, 6, 4),(79, 6, 6),
-> (80, 7, 2),(92, 7, 6);
Query OK, 20 rows affected (0.01 sec)
Records: 20 Duplicates: 0 Warnings: 0
提问:查询赵六同学的成绩;
查询每个班有多少人;
查询每个同学的总成绩;
查询每个同学每个课程的成绩
单表查询条件中直接写列名即可,多表查询条件中最好写作 “表名.列名”
4.2.1内连接
mysql> select student.name, score.score from student ,score where student.id = score.student_id and student.name = '赵六';
+------+-------+
| name | score |
+------+-------+
| 赵六 | 67.0 |
| 赵六 | 23.0 |
| 赵六 | 56.0 |
| 赵六 | 72.0 |
+------+-------+
4 rows in set (0.01 sec)
mysql> select classes.name, count(student.class_id) from student, classes where student.class_id = classes.id group by classes.name;
+--------------------+-------------------------+
| name | count(student.class_id) |
+--------------------+-------------------------+
| 中文系2022级3班 | 3 |
| 计算机系2022级16班 | 5 |
+--------------------+-------------------------+
2 rows in set (0.00 sec)
mysql> select student.name, sum(score.score) as summary from student, score where student.id = score.student_id group by student.name;
+------+---------+
| name | summary |
+------+---------+
| 孙九 | 172.0 |
| 张三 | 300.0 |
| 李四 | 119.5 |
| 王五 | 200.0 |
| 田七 | 118.0 |
| 赵六 | 218.0 |
| 钱八 | 178.0 |
+------+---------+
7 rows in set (0.00 sec)
mysql> select student.name, course.name as object, score.score from student, score, course where student.id = score.student_id and score.course_id = course.id;
+------+--------------+-------+
| name | object | score |
+------+--------------+-------+
| 张三 | Java | 70.5 |
| 张三 | 计算机原理 | 98.5 |
| 张三 | 高阶数学 | 33.0 |
| 张三 | 英文 | 98.0 |
| 李四 | Java | 60.0 |
| 李四 | 高阶数学 | 59.5 |
| 王五 | Java | 33.0 |
| 王五 | 计算机原理 | 68.0 |
| 王五 | 高阶数学 | 99.0 |
| 赵六 | Java | 67.0 |
| 赵六 | 计算机原理 | 23.0 |
| 赵六 | 高阶数学 | 56.0 |
| 赵六 | 英文 | 72.0 |
| 田七 | Java | 81.0 |
| 田七 | 高阶数学 | 37.0 |
| 钱八 | 中国传统文化 | 56.0 |
| 钱八 | 语文 | 43.0 |
| 钱八 | 英文 | 79.0 |
| 孙九 | 中国传统文化 | 80.0 |
| 孙九 | 英文 | 92.0 |
+------+--------------+-------+
20 rows in set (0.00 sec)
4.2.2外连接
格式:select 列名 from 表名 left/right join 表名 on 条件;
分类:左外连接:left join 以左侧表为主,左侧表中的数据在结果中一定存在,若左表存在右表不存在,会将对应的列填为空值;
右外连接:right join 类上;
全外连接:outer join ,MySQL 不支持,Oracle支持。
mysql> select student.name, score.score from student join score on student.id = score.student_id and student.name = '赵 六';
+------+-------+
| name | score |
+------+-------+
| 赵六 | 67.0 |
| 赵六 | 23.0 |
| 赵六 | 56.0 |
| 赵六 | 72.0 |
+------+-------+
4 rows in set (0.00 sec)
mysql> select * from student;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+------+
3 rows in set (0.01 sec)
mysql> select * from score;
+------+-------+
| id | score |
+------+-------+
| 1 | 90 |
| 2 | 80 |
| 4 | 70 |
+------+-------+
3 rows in set (0.00 sec)
mysql> select * from student join score on student.id = score.id;
+------+------+------+-------+
| id | name | id | score |
+------+------+------+-------+
| 1 | 张三 | 1 | 90 |
| 2 | 李四 | 2 | 80 |
+------+------+------+-------+
2 rows in set (0.00 sec)
mysql> select * from student left join score on student.id = score.id;
+------+------+------+-------+
| id | name | id | score |
+------+------+------+-------+
| 1 | 张三 | 1 | 90 |
| 2 | 李四 | 2 | 80 |
| 3 | 王五 | NULL | NULL |
+------+------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from student right join score on student.id = score.id;
+------+------+------+-------+
| id | name | id | score |
+------+------+------+-------+
| 1 | 张三 | 1 | 90 |
| 2 | 李四 | 2 | 80 |
| NULL | NULL | 4 | 70 |
+------+------+------+-------+
3 rows in set (0.00 sec)
4.2.3自连接
自己和自己做笛卡尔积,把行之间的关系转换为列之间的关系;
为避免重命名,名字要使用别名。
格式:select * from 表名 as 别名1, 表名 as 别名2;
mysql> select * from student1;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+------+
3 rows in set (0.00 sec)
mysql> select * from student1 as s1, student1 as s2;
+------+------+------+------+
| id | name | id | name |
+------+------+------+------+
| 1 | 张三 | 1 | 张三 |
| 2 | 李四 | 1 | 张三 |
| 3 | 王五 | 1 | 张三 |
| 1 | 张三 | 2 | 李四 |
| 2 | 李四 | 2 | 李四 |
| 3 | 王五 | 2 | 李四 |
| 1 | 张三 | 3 | 王五 |
| 2 | 李四 | 3 | 王五 |
| 3 | 王五 | 3 | 王五 |
+------+------+------+------+
9 rows in set (0.00 sec)
4.2.4子查询
嵌在其他SQL语句中的select语句,也叫嵌套查询。
4.2.5合并查询
将两个查询表合并成一个表。
格式:select * from 表1 union (all) select * from 表2;
合并前提:两表参数类型需一一对应,名字可以不同。
mysql> select * from student1;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
+------+------+
2 rows in set (0.04 sec)
mysql> select * from student2;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 3 | 王五 |
+------+------+
2 rows in set (0.00 sec)
mysql> select * from student1 union select * from student2;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+------+------+
3 rows in set (0.04 sec)
mysql> select * from student1 union all select * from student2;
+------+------+
| id | name |
+------+------+
| 1 | 张三 |
| 2 | 李四 |
| 1 | 张三 |
| 3 | 王五 |
+------+------+
4 rows in set (0.01 sec)
关于表的内容就over啦!
文章评论