成都比较好的网站设计公司,东台网站建设公司,36kr wordpress,wordpress 短链插件作者主页#xff1a;paper jie_博客 本文作者#xff1a;大家好#xff0c;我是paper jie#xff0c;感谢你阅读本文#xff0c;欢迎一建三连哦。 本文录入于《MySQL》专栏#xff0c;本专栏是针对于大学生#xff0c;编程小白精心打造的。笔者用重金(时间和精力)打造paper jie_博客 本文作者大家好我是paper jie感谢你阅读本文欢迎一建三连哦。 本文录入于《MySQL》专栏本专栏是针对于大学生编程小白精心打造的。笔者用重金(时间和精力)打造将MySQL基础知识一网打尽希望可以帮到读者们哦。 其他专栏《算法详解》《C语言》《javaSE》《数据结构》等 内容分享本期将会分享MySQL表的增删改查的一些奇淫巧技 目录
数据库的约束
约束类型
null约束
unique: 唯一约束
default: 默认值约束
primary key: 主键约束
foreign key: 外键约束
check约束(MySQL中不使用它)
表的设计
三大范式
一对一
一对多
多对多
新增
查询
聚合查询
聚合函数
group by 子句
having
联合查询
初始化数据
内连接
外连接
自连接
子查询
合并查询 数据库的约束
约束类型
not null: 表示某列不可以存放null
unique: 保证某列的每行必须是唯一值
default: 规定没有给列赋值时给的默认值
primary key: 主键, 保证某列有唯一的标记,且不能为null
foreign key: 外键, 保证一个表的数据匹配另一个表的值的参照完整性,与匹配表对应
check: 保证列中的值符合指定的条件,但MySQL数据库堆check子句进行分析的时候会忽略它
null约束 创建表的时候,指定列不能为空: create table student(id int is not null, name varchar(20));
unique: 唯一约束
创建表的时候,指定列是唯一的,不重复的: create table student(id int unique, name varchar(20));
default: 默认值约束
创建表的时候,指定列为空,则插入指定默认值:
create table student(id int, name varchar(20) default 无名氏);
primary key: 主键约束
指定列为主键:
create table student(id int primary key, mame varchar(20));
自增主键: auto_increment, 插入对应数据不给值时,使用最大值1 create table student(id int primary key auto_increment, mame varchar(20));
foreign key: 外键约束
外键用于关键其他的表的主键:
注意: 外键约束关键的表的对应列得有主键约束才能使用foreign key
create table student(id int primary key auto_increment, mame varchar(20));
create table score(id int, name varchar(20), student_id int, foreign key (student_id) references student(id));
check约束(MySQL中不使用它)
MySQL使用会忽略check:
create table student(id int, name varchar(30), check(id 1 or id 3));
表的设计
三大范式
一对一 一对多 多对多 新增
将一张表已有的数据复制到另一张表中,复制的字段类型需要在被复制表中有的类型
语法:
insert into 表名1(类型1,类型2....) select 类型1, 类型2.... from 表名2;
栗子:
insert into student1(id,name) select id, name from student2;
查询
聚合查询
聚合函数
一般常用的函数有sum, avg, max, min, count:
函数作用sum返回查询的行的数据之和,需要是数字avg返回查询的行的数据的平均值,需要是数字max返回查询的行的数据的最大值,需要是数字min返回查询的行的数据的最小值,需要是数字count返回查询的行数
栗子:
mysql select * from student;
---------------------------------------------------
| id | sn | name | qq_mail | class_id |
---------------------------------------------------
| 2 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 |
| 3 | 00835 | 菩提老祖 | NULL | 1 |
| 4 | 00391 | 白素贞 | NULL | 1 |
| 5 | 00031 | 许仙 | xuxianqq.com | 1 |
| 6 | 00054 | 不想毕业 | NULL | 1 |
| 7 | 51234 | 好好说话 | sayqq.com | 2 |
| 8 | 83223 | tellme | NULL | 2 |
| 9 | 09527 | 老外学中文 | foreignerqq.com | 2 |
---------------------------------------------------
sum:
mysql select sum(id) from student;
---------
| sum(id) |
---------
| 44 |
---------
avg:
mysql select avg(id) from student;
---------
| avg(id) |
---------
| 5.5000 |
---------
max:
mysql select max(id) from student;
---------
| max(id) |
---------
| 9 |
---------
min:
mysql select min(id) from student;
---------
| min(id) |
---------
| 2 |
---------
count:
mysql select count(*) from student;
----------
| count(*) |
----------
| 8 |
----------
注意: 这里*是查询全部的行数, 括号内可以指定查询字段,字符为null的不会记录
mysql select count(qq_mail) from student;
----------------
| count(qq_mail) |
----------------
| 4 |
----------------group by 子句
select使用group by 可以指定列分组查询. 需要满足的条件有: 使用group by 进行分组查询的时候,select 指定的字段得是分组依据的字段, 其他字段要是想出现在select中得包含在聚合函数中
下面栗子中需要查询的数据:
mysql select * from score;
------------------------------
| score | student_id | course_id |
------------------------------
| 70 | 1 | 1 |
| 98 | 1 | 3 |
| 33 | 1 | 5 |
| 98 | 1 | 6 |
| 60 | 2 | 1 |
| 59 | 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 |
------------------------------
栗子:
mysql select student_id, sum(score) from score group by student_id;
------------------------
| student_id | sum(score) |
------------------------
| 1 | 299 |
| 2 | 119 |
| 3 | 200 |
| 4 | 218 |
| 5 | 118 |
| 6 | 178 |
| 7 | 172 |
------------------------
having
分组后需要对结果进行筛选,不能使用where语句,需要使用having
栗子:
mysql select student_id, sum(score) from score group by student_id having sum(score) 200;
------------------------
| student_id | sum(score) |
------------------------
| 2 | 119 |
| 5 | 118 |
| 6 | 178 |
| 7 | 172 |
------------------------
联合查询
在开发中会有很多张表存放数据,这里我们就需要联合查询.联合查询就是对多张表进行笛卡尔积: 初始化数据
mysql select * from student;
---------------------------------------------------
| id | sn | name | qq_mail | class_id |
---------------------------------------------------
| 2 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 |
| 3 | 00835 | 菩提老祖 | NULL | 1 |
| 4 | 00391 | 白素贞 | NULL | 1 |
| 5 | 00031 | 许仙 | xuxianqq.com | 1 |
| 6 | 00054 | 不想毕业 | NULL | 1 |
| 7 | 51234 | 好好说话 | sayqq.com | 2 |
| 8 | 83223 | tellme | NULL | 2 |
| 9 | 09527 | 老外学中文 | foreignerqq.com | 2 |
---------------------------------------------------
mysql select * from classes;
---------------------------------------------------------------------------
| classID | name | desc |
---------------------------------------------------------------------------
| 1 | 计算机系2019级1班 | 学习了计算机原理、C和Java语言、数据结构和算法 |
| 2 | 中文系2019级3班 | 学习了中国传统文学 |
| 3 | 自动化2019级5班 | 学习了机械自动化 |
---------------------------------------------------------------------------
mysql select * from score;
------------------------------
| score | student_id | course_id |
------------------------------
| 70 | 1 | 1 |
| 98 | 1 | 3 |
| 33 | 1 | 5 |
| 98 | 1 | 6 |
| 60 | 2 | 1 |
| 59 | 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 |
------------------------------ mysql select * from course;
------------------
| id | name |
------------------
| 1 | Java |
| 2 | 中国传统文化 |
| 3 | 计算机原理 |
| 4 | 语文 |
| 5 | 高阶数学 |
| 6 | 英文 |
------------------
内连接
使用方式: select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件; mysql select * from student,score where student.id score.student_id and student.name 菩提老祖;
----------------------------------------------------------------------
| id | sn | name | qq_mail | class_id | score | student_id | course_id |
----------------------------------------------------------------------
| 3 | 00835 | 菩提老祖 | NULL | 1 | 33 | 3 | 1 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 68 | 3 | 3 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 99 | 3 | 5 |
---------------------------------------------------------------------- mysql select * from student join score on student.id score.student_id and student.name 菩提老祖;
----------------------------------------------------------------------
| id | sn | name | qq_mail | class_id | score | student_id | course_id |
----------------------------------------------------------------------
| 3 | 00835 | 菩提老祖 | NULL | 1 | 33 | 3 | 1 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 68 | 3 | 3 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 99 | 3 | 5 |
----------------------------------------------------------------------
外连接
外连接分为左外连接和右外连接.联合查询中左侧的表完全显示为左外连接,右侧完全显示为有外连接
使用方法: -- 左外连接表1完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件; -- 右外连接表2完全显示 select 字段 from 表名1 right join 表名2 on 连接条件; mysql select * from student left join score on student.id score.student_id;
---------------------------------------------------------------------------------
| id | sn | name | qq_mail | class_id | score | student_id | course_id |
---------------------------------------------------------------------------------
| 2 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 60 | 2 | 1 |
| 2 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 59 | 2 | 5 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 33 | 3 | 1 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 68 | 3 | 3 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 99 | 3 | 5 |
| 4 | 00391 | 白素贞 | NULL | 1 | 67 | 4 | 1 |
| 4 | 00391 | 白素贞 | NULL | 1 | 23 | 4 | 3 |
| 4 | 00391 | 白素贞 | NULL | 1 | 56 | 4 | 5 |
| 4 | 00391 | 白素贞 | NULL | 1 | 72 | 4 | 6 |
| 5 | 00031 | 许仙 | xuxianqq.com | 1 | 81 | 5 | 1 |
| 5 | 00031 | 许仙 | xuxianqq.com | 1 | 37 | 5 | 5 |
| 6 | 00054 | 不想毕业 | NULL | 1 | 56 | 6 | 2 |
| 6 | 00054 | 不想毕业 | NULL | 1 | 43 | 6 | 4 |
| 6 | 00054 | 不想毕业 | NULL | 1 | 79 | 6 | 6 |
| 7 | 51234 | 好好说话 | sayqq.com | 2 | 80 | 7 | 2 |
| 7 | 51234 | 好好说话 | sayqq.com | 2 | 92 | 7 | 6 |
| 8 | 83223 | tellme | NULL | 2 | NULL | NULL | NULL |
| 9 | 09527 | 老外学中文 | foreignerqq.com | 2 | NULL | NULL | NULL |
---------------------------------------------------------------------------------
mysql select * from student right join score on student.id score.student_id;
----------------------------------------------------------------------------------
| id | sn | name | qq_mail | class_id | score | student_id | course_id |
----------------------------------------------------------------------------------
| NULL | NULL | NULL | NULL | NULL | 70 | 1 | 1 |
| NULL | NULL | NULL | NULL | NULL | 98 | 1 | 3 |
| NULL | NULL | NULL | NULL | NULL | 33 | 1 | 5 |
| NULL | NULL | NULL | NULL | NULL | 98 | 1 | 6 |
| 2 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 60 | 2 | 1 |
| 2 | 09982 | 黑旋风李逵 | xuanfengqq.com | 1 | 59 | 2 | 5 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 33 | 3 | 1 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 68 | 3 | 3 |
| 3 | 00835 | 菩提老祖 | NULL | 1 | 99 | 3 | 5 |
| 4 | 00391 | 白素贞 | NULL | 1 | 67 | 4 | 1 |
| 4 | 00391 | 白素贞 | NULL | 1 | 23 | 4 | 3 |
| 4 | 00391 | 白素贞 | NULL | 1 | 56 | 4 | 5 |
| 4 | 00391 | 白素贞 | NULL | 1 | 72 | 4 | 6 |
| 5 | 00031 | 许仙 | xuxianqq.com | 1 | 81 | 5 | 1 |
| 5 | 00031 | 许仙 | xuxianqq.com | 1 | 37 | 5 | 5 |
| 6 | 00054 | 不想毕业 | NULL | 1 | 56 | 6 | 2 |
| 6 | 00054 | 不想毕业 | NULL | 1 | 43 | 6 | 4 |
| 6 | 00054 | 不想毕业 | NULL | 1 | 79 | 6 | 6 |
| 7 | 51234 | 好好说话 | sayqq.com | 2 | 80 | 7 | 2 |
| 7 | 51234 | 好好说话 | sayqq.com | 2 | 92 | 7 | 6 |
----------------------------------------------------------------------------------
自连接
自连接就是自己与自己连接查询.
mysql select * from course c1, course c2 where c1.name c2.name;
------------------------------------
| id | name | id | name |
------------------------------------
| 1 | Java | 1 | Java |
| 2 | 中国传统文化 | 2 | 中国传统文化 |
| 3 | 计算机原理 | 3 | 计算机原理 |
| 4 | 语文 | 4 | 语文 |
| 5 | 高阶数学 | 5 | 高阶数学 |
| 6 | 英文 | 6 | 英文 |
------------------------------------
子查询
子查询是指嵌入在其他sql语句中的select语句
mysql select * from student where class_id (select class_id from student where name tellme);
---------------------------------------------------
| id | sn | name | qq_mail | class_id |
---------------------------------------------------
| 7 | 51234 | 好好说话 | sayqq.com | 2 |
| 8 | 83223 | tellme | NULL | 2 |
| 9 | 09527 | 老外学中文 | foreignerqq.com | 2 |
---------------------------------------------------
合并查询
将多个类型一样的表合并
mysql select * from course where id 2 union select * from course where id 4;
----------
| id | name |
----------
| 1 | Java |
| 4 | 语文 |
----------
