当前位置：首页 > news >正文

国内建筑网站网络宣传推广

news 2025/8/11 20:02:15

国内建筑网站,网络宣传推广,相亲网站建设方案,做私活的网站目录 1.数据库约束 1.1NOT NULL：非空约束 1.2UNIQUE：唯一值约束 1.3DEFAULT：默认值约束 1.4PRIMARY KEY：主键约束 1.5FOREIGN KEY：外键约束 1.6CHECK约束 2.表的设计 2.1一对一 2.2一对多 2.3多对多 3.新增…

1.数据库约束

1.1NOT NULL：非空约束

1.2UNIQUE：唯一值约束

1.3DEFAULT：默认值约束

1.4PRIMARY KEY：主键约束

1.5FOREIGN KEY：外键约束

1.6CHECK约束

2.表的设计

2.1一对一

2.2一对多

2.3多对多

3.新增

4.查询

4.1聚合查询

4.1.1聚合查询

4.1.2GROUP BY

4.2联合查询

4.2.1内连接

4.2.2外连接

4.2.3自连接

4.2.4子查询

4.2.5合并查询

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: 0mysql> 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: 0mysql> insert into student2 select * from student;
Query OK, 3 rows affected (0.04 sec)
Records: 3  Duplicates: 0  Warnings: 0mysql> 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: 0mysql> insert into student(sn, name, qq_mail, class_id) values-> ('09982','张三','zhangsan@qq.com',1),-> ('00835','李四',null,1),-> ('00391','王五',null,1),-> ('00031','赵六','zhaoliu@qq.com',1),-> ('00054','田七',null,1),-> ('51234','钱八','qianba@qq.com',2),-> ('83223','孙九',null,2),-> ('09527','吴十','wushi@qq.com',2);
Query OK, 8 rows affected (0.01 sec)
Records: 8  Duplicates: 0  Warnings: 0mysql> insert into course(name) values-> ('Java'),('中国传统文化'),('计算机原理'),('语文'),('高阶数学'),('英文');
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0mysql> 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啦！