海南做网站,2021重庆互联网公司排名,做网站法人拍照背景,怎样开网店卖别人的东西目录
1.数据库约束
1.1NOT NULL#xff1a;非空约束
1.2UNIQUE#xff1a;唯一值约束
1.3DEFAULT#xff1a;默认值约束
1.4PRIMARY KEY#xff1a;主键约束
1.5FOREIGN KEY#xff1a;外键约束
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 doesnt 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,张三,zhangsanqq.com,1),- (00835,李四,null,1),- (00391,王五,null,1),- (00031,赵六,zhaoliuqq.com,1),- (00054,田七,null,1),- (51234,钱八,qianbaqq.com,2),- (83223,孙九,null,2),- (09527,吴十,wushiqq.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啦
