开发网站需要哪些技术人员,零售电商,网站验证码调用,核工业南京建设集团网站目录 数据库约束
约束的定义
约束类型
null约束
unique:唯一约束
default:默认值约束
primary key:主键约束(重要)
foreign key:外键约束(描述两个表之间的关联)
表的设计
一般思路
三大范式
一对一
一对多
编辑
多对多
编辑
新增
查询
聚合查询
聚合函…目录 数据库约束
约束的定义
约束类型
null约束
unique:唯一约束
default:默认值约束
primary key:主键约束(重要)
foreign key:外键约束(描述两个表之间的关联)
表的设计
一般思路
三大范式
一对一
一对多
编辑
多对多
编辑
新增
查询
聚合查询
聚合函数
group by字句
having
联合查询(也称多表查询)
内连接
外连接
自连接
子查询
合并查询 数据库约束
约束的定义 定义:创建表时,给这个表制定一些规则,后续插入/修改/删除都要保证数据能够遵守这些规则. 引入约束的规则,是为了更强的数据检查/校验.数据一定要保证正确性.(如果后续插入/修改的数据不符合要求,就会报错) 执行效率(代码在机器上跑的快不快) 开发效率(程序员写代码所花费的时间) 我们讲约束也是牺牲了执行效率,换来了开发效率.因为开发效率更重要:因为人力成本高于机器硬件成本. 约束类型
共分为not null, unique, default, primary key, check这几种
null约束
定义:指示某列不能存储null值
在创建表时,就是指定某列不为空,当指定列插入null会报错 -- 重新设置学生表结构 DROP TABLE IF EXISTS student; CREATE TABLE student ( id INT NOT NULL , sn INT , name VARCHAR ( 20 ), qq_mail VARCHAR ( 20 ) ); unique:唯一约束
定义:保证某列的每行必须有唯一的值.
指定sn为唯一的,不重复的(即在这一列都不会出现): -- 重新设置学生表结构 DROP TABLE IF EXISTS student; CREATE TABLE student ( id INT NOT NULL , sn INT UNIQUE, name VARCHAR ( 20 ), qq_mail VARCHAR ( 20 ) ); default:默认值约束
定义:规定没有给列赋值时赋默认值.(默认情况下的默认值设置为null)
指定插入数据时,name列为空,默认值为unknown -- 重新设置学生表结构 DROP TABLE IF EXISTS student; CREATE TABLE student ( id INT NOT NULL , sn INT UNIQUE, name VARCHAR ( 20 ) DEFAULT unkown , qq_mail VARCHAR ( 20 ) ); primary key:主键约束(重要)
定义:not null和unique的结合.确保某列(或两个列,多个列的组合)有唯一标识(就是一个记录的身份标识),有助于更容易更快速地找到表中的一个特定记录.(通常用xxx id作为主键,通常一个表中只有一个主键).
指定id列为主键: -- 重新设置学生表结构 DROP TABLE IF EXISTS student; CREATE TABLE student ( id INT NOT NULL PRIMARY KEY, sn INT UNIQUE, name VARCHAR ( 20 ) DEFAULT unkown , qq_mail VARCHAR ( 20 ) ); 自增主键的介绍:auto_increment(对id(主键)的1操作) 不需要用户自己指定(可以放个null),可交给数据库进行分配(1,2,3...) 其实,程序员可以自行设定id亦可 每次使用null方式插入自增主键时,都是数据库根据这一列的最大值,在这个基础上,继续递增,不会重复利用之前上述的自增主键,只能在当前单个数据库下生效 如果数据库是由多个mysql服务器构成集群时,自增主键就无效了 在实际业务当中,引入的主键是不希望重复的. id INT PRTMARY KEY auto_increment, foreign key:外键约束(描述两个表之间的关联) 定义:保证一个表中的数据匹配另一个表中的值的参照完整性. 外键用于关联其他表的主键或者唯一键,语法: foreign key (字段名) references 主表(列) -- 字段名是指本表的哪个列 -- 主表是指被关联的表 -- 列就是被关联的表的哪个列 注:外键约束是写到最后,把前面的定义好之后,在最后通过foreign key创建外键约束. 案例: 创建班级表classes,id为主键: -- 创建班级表有使用 MySQL 关键字作为字段时需要使用 来标识 DROP TABLE IF EXISTS classes; CREATE TABLE classes ( id INT PRIMARY KEY auto_increment, name VARCHAR ( 20 ), desc VARCHAR ( 100 ) ); 设计要求:学生表中的class_id都在班级表中存在 创建学生表student,一个学生对应一个班级,一个班级对应多少学生.使用id为主键,classes_id为外键,关联班级表id -- 重新设置学生表结构 DROP TABLE IF EXISTS student; CREATE TABLE student ( id INT PRIMARY KEY auto_increment, sn INT UNIQUE, name VARCHAR ( 20 ) DEFAULT unkown , qq_mail VARCHAR ( 20 ), classes_id int , FOREIGN KEY (classes_id) REFERENCES classes(id) ); 1.把约束别的表的表称为父表. 把被约束的表,称为子表. 2.外键约束,也是双向的,要想删除父表的记录,就必须先删除子表中的相关数据,以确保子表中没有数据. 3.使用外键约束时,操作子表,要查询父表;操作父表,也要查询子表.子表和父表中被引用的列都要带有索引.表里有了主键之后,就会自动创建出索引,加快查询速度 4.扩展知识:对电脑上的文件进行删除,也只是逻辑上的删除,不是真的删除.把文件删除掉,其实也是在系统中,把硬盘对应的盘块数据标记为无效了(所以不是真正意义上的删除) 表的设计
一般思路 1.先根据需求,找到实体(一些关键性质的对象),梳理清需求,提取出关键字的名字,一般来说,每个实体,都得安排个表. 2.梳理实体间的关系(不同关系下,有不同设计表的方式),造句,向里面套,能套上哪个,就用哪种方式创建数据表(定式) 三大范式
一对一 方案一:搞一个大表,把这些信息都放在一起(前提是这两个表都很简单(即列很少,可以考虑合并)) 方案二:分两个表,使用id引用过来,建立联系(可用于表很复杂的情况) 一对多 多对多 创建课程表 -- 创建课程表 DROP TABLE IF EXISTS course; CREATE TABLE course ( id INT PRIMARY KEY auto_increment, name VARCHAR ( 20 ) ); 创建学生课程中间表,考试成绩表 -- 创建课程学生中间表考试成绩表 DROP TABLE IF EXISTS score; CREATE TABLE score ( id INT PRIMARY KEY auto_increment, score DECIMAL ( 3 , 1 ), student_id int , course_id int , FOREIGN KEY (student_id) REFERENCES student(id), FOREIGN KEY (course_id) REFERENCES course(id) ); 新增
插入查询结果
语法(将插入语句和查询语句结合到一起了): insert into table_name [(column [, column...])] select ... -- 查询结果的集合就代替了values 案例:创建一张用户表,设计有name姓名,email邮箱,sex性别,mobile手机号字段.需要把已有的学生数据复制进来,可以复制的字段有name, qq_mail. -- 创建用户表 DROP TABLE IF EXISTS test_user; CREATE TABLE test_user ( id INT primary key auto_increment, name VARCHAR ( 20 ) comment 姓名 , age INT comment 年龄 , email VARCHAR ( 20 ) comment 邮箱 , sex varchar ( 1 ) comment 性别 , mobile varchar ( 20 ) comment 手机号 ); -- 将学生表中的所有数据复制到用户表 insert into test_user(name, email) select name, qq_mail from student; 查询
聚合查询
定义:就是行和行之间的运算,但此处行之间的运算具有一定限制,不像表达式查询(表达式查询是操作列).
聚合函数 使用聚合函数时,列和列之间,已经被打散了,如果查询中包含聚合函数,和非聚合的列,各自是各自的.大部分情况,聚合的列和非聚合的列是不能混用的,一种情况除外(group by(后面讲)) 函数说明count([distinct] expr)返回查询到数据的数量sum([distinct] expr)返回查询到的数据的总和,不是数字则没有意义avg([distinct] expr)返回查询到数据的平均值,不是数字则没有意义max([distinct] expr)返回查询到数据的最大值,不是数字则没有意义min([distinct] expr) 返回查询到数据的最小值,不是数字则没有意义
案例:
count: -- 统计班级共有多少同学 SELECT COUNT (*) FROM student; SELECT COUNT ( 0 ) FROM student; -- 统计班级收集的 qq_mail 有多少个 qq_mail 为 NULL 的数据不会计入结果 SELECT COUNT (qq_mail) FROM student; 注意:当使用count(*) 时,即使是全为null的行,也会被记录次数 当使用count(列名)时,如果中间有为null的行,则不会被记录次数 sum: -- 统计数学成绩总分 SELECT SUM (math) FROM exam_result; -- 不及格 60 的总分没有结果返回 NULL SELECT SUM (math) FROM exam_result WHERE math 60 ; 注意: 1.sum函数会将null自动忽略 2.sql很多时候会将字符串当作数字进行算术运算,都会将字符串转为数字 (eg.允许100转换为100) avg: -- 统计平均总分 SELECT AVG (chinese math english) 平均总分 FROM exam_result; max: -- 返回英语最高分 SELECT MAX (english) FROM exam_result; min: -- 返回 70 分以上的数学最低分 SELECT MIN (math) FROM exam_result WHERE math 70 ; group by字句
在select中使用group by可以对指定列进行分组查询.需要满足:使用group by进行分组查询时,select指定的字段必须是分组依据字段, 其它字段如想出现在select中,必须包含在聚合函数中.
实际效果就是把这个指定的列,值相同的记录划分到一组,针对这些组就可以分别聚合查询了,分组操作,往往是和聚合配合使用的. select column1, sum(column2), .. from table group by column1,column3; -- column1是分组依据 案例:
准备测试表及数据:职员表,有id(主键), name(姓名), role(角色), salary(薪水) create table emp( id int primary key auto_increment, name varchar ( 20 ) not null , role varchar ( 20 ) not null , salary numeric ( 11 , 2 ) ); insert into emp(name, role, salary) values ( 马云 , 服务员 , 1000.20 ), ( 马化腾 , 游戏陪玩 , 2000.99 ), ( 孙悟空 , 游戏角色 , 999.11 ), ( 猪无能 , 游戏角色 , 333.5 ), ( 沙和尚 , 游戏角色 , 700.33 ), ( 隔壁老王 , 董事长 , 12000.66 ); 查询每个角色的最高工资,最低工资和平均工资 select role,max(salary),min(salary),avg(salary) from emp group by role; having
group字句进行分组之后,需要对分组结果再进行条件过滤时,不能使用where语句,而需要用having语句.(即分组之前的条件,使用where表示;分组之后的条件,使用having表示)
显示平均工资低于1500的角色和它的平均工资 select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary) 1500 ; 联合查询(也称多表查询)
所谓多表联合查询,核心操作为笛卡尔积,然后指定一些条件之类的,来实现需求中的一些查询结果.
实际开发中往往数据来自不同的表,所以需要多表联合查询.多表查询是对多张表的数据取笛卡尔积: 笛卡尔积列数为之前列数之和,行数为两个表的行数之积.
但实际上,笛卡尔积得到的结果,绝大部分是无效数据. 注意:关联查询可以对关联表使用别名.
让我们来看一下下面的案例(可以尝试自己敲一下): insert into classes(name, desc) values ( 计算机系 2019 级 1 班 , 学习了计算机原理、 C 和 Java 语言、数据结构和算法 ), ( 中文系 2019 级 3 班 , 学习了中国传统文学 ), ( 自动化 2019 级 5 班 , 学习了机械自动化 ); insert into student(sn, name, qq_mail, classes_id) values ( 09982 , 黑旋风李逵 , xuanfengqq.com , 1 ), ( 00835 , 菩提老祖 , null , 1 ), ( 00391 , 白素贞 , null , 1 ), ( 00031 , 许仙 , xuxianqq.com , 1 ), ( 00054 , 不想毕业 , null , 1 ), ( 51234 , 好好说话 , sayqq.com , 2 ), ( 83223 , tellme , null , 2 ), ( 09527 , 老外学中文 , foreignerqq.com , 2 ); ); insert into course(name) values ( Java ),( 中国传统文化 ),( 计算机原理 ),( 语文 ),( 高阶数学 ),( 英文 ); 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 ), -- tellme ( 80 , 7 , 2 ),( 92 , 7 , 6 ); 注意:在进行多表查询时,始终贯穿着这四部曲(由繁入简),筛选出符合条件的数据: 1.笛卡尔积 2.连接条件 3.根据需求指定其他条件(筛选行) 4.针对列进行精简(筛选列) 内连接
定义:是内连接查询中一种特殊的等值连接所谓的自连接就是指表与其自己当前表进行连接。自己和自己做连接。
语法: select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件; select 字段 from 表1 别名1,表2 别名2 where 连接条件 and 其他条件;(个人最推荐第二种,嘎嘎好用) 案例:
(1)查询许仙同学的成绩 select sco .score from student stu inner join score sco on stu .id sco .student_id and stu .name 许仙 ; -- 或者 select sco .score from student stu, score sco where stu .id sco .student_id and stu .name 许仙 ; (2)查询所有同学的总成绩,以及同学的个人信息 -- 成绩表对学生表是多对 1 关系查询总成绩是根据成绩表的同学 id 来进行分组的 SELECT stu .sn , stu .NAME , stu .qq_mail , sum( sco .score ) FROM student stu JOIN score sco ON stu .id sco .student_id GROUP BY sco .student_id ; (3)查询所有同学的成绩,以及同学的个人信息: -- 查询出来的都是有成绩的同学 “ 老外学中文 ” 同学 没有显示 select * from student stu join score sco on stu .id sco .student_id ; -- 学生表、成绩表、课程表 3 张表关联查询 SELECT stu .id , stu .sn , stu .NAME , stu .qq_mail , sco .score , sco .course_id , cou .NAME FROM student stu JOIN score sco ON stu .id sco .student_id JOIN course cou ON sco .course_id cou .id ORDER BY stu.id; 外连接
外连接分为左外连接和右外连接.如果联合查询,左侧的表完全显示我们就说是左外连接;右侧的表完全显示我们就说是右外连接. 外连接也是多表查询的一种表现形式,一般使用比较少,属于特殊情况特殊处理.
语法: -- 左外连接,表一完全显示 select 字段名 from 表名1 left join 表名2 on 连接条件; -- 右外连接,表二完全显示 select 字段名 from 表名1 right join 表名2 on 连接条件; 为了形象展示内连接,左外连接和右外连接的区别,下面我们来看这一组图: 案例:查询所有学生的成绩,及同学的个人信息,如果该同学没有成绩,也需要显示 -- “ 老外学中文 ” 同学 没有考试成绩也显示出来了 select * from student stu left join score sco on stu .id sco .student_id ; -- 对应的右外连接为 select * from score sco right join student stu on stu .id sco .student_id ; -- 学生表、成绩表、课程表 3 张表关联查询 SELECT stu .id , stu .sn , stu .NAME , stu .qq_mail , sco .score , sco .course_id , cou .NAME FROM student stu LEFT JOIN score sco ON stu .id sco .student_id LEFT JOIN course cou ON sco .course_id cou .id ORDER BY stu .id ; 自连接
顾名思义,自连接就是指在同一张表中进行查询.
案例:
显示所有计算机原理成绩比Java成绩高的成绩信息(在这个例子中,要想完成不同科目的比较,就需要比较行之间的大小,因为sql是无法实现这个功能的,所以只能将行转为列) -- 先查询 “ 计算机原理 ” 和 “Java” 课程的 id select id,name from course where name Java or name 计算机原理 ; -- 让我们使用四部曲来写一下 (1)将所有内容进行笛卡尔积 select * from score as s1, score as s2; (2)连接条件:两张表student_id相同 select * from score as s1, score as s2 where s1.student_id s2.student_id; (3)只筛选出要比较的课程id的数据 select * from score as s1, score as s2 where s1.student_id s2.student_id and s1.course_id 3 and s2.course_id 1; (4)找出结果 select * from score as s1, score as s2 where s1.student_id s2.student_id and s1.course_id 3 and s2.course_id 1 and s1.score s2.score; 子查询
子查询是指嵌入在其它sql语句中的select语句,也叫嵌套查询(这个查询方法虽然很装逼但不建议用,所以了解这个即可,自己写的时候尽量不要这样写)
举个例子:查询与不想毕业同班的同学: select * from student where classes_id( select classes_id from student where name 不想毕业 ); 合并查询
在实际应用中,为了合并多个select的执行结果,可以使用集合操作符union, union all.使用union和union all时,前后查询结果集中,字段需要一致.(合并查询其实用的很少,了解即可)
案例:查询id 小于3,或者名字为英文的课程 select * from course where id 3 union select * from course where name 英文 ; -- 或者使用 or 来实现 select * from course where id 3 or name 英文 ;
