04_mysql03

最新推荐文章于 2024-08-09 21:25:57 发布

Ayui.

最新推荐文章于 2024-08-09 21:25:57 发布

阅读量21

点赞数

分类专栏： 02_javaee 文章标签：数据库 sql mysql

本文链接：https://blog.csdn.net/m0_61569709/article/details/132477723

版权

02_javaee 专栏收录该内容

27 篇文章 0 订阅

订阅专栏

1 约束

constraints：约束，在定义表时 给表设置的检查规则  用于保证数据库的完整性

完整性：
  1 数据完整性：每行的数统一，列数一致
  2 域完整性： 对列的取值进行约束、列取值有效
  3 对象完整性：唯一约束的列值必须有值：统一的都有那些列/学号必须有值
  4 参照完整性：从表中外键列的值必须在主表的主键中存在，student表中的tid值 必须在teacher表中的tid列中存在

-- constraints：约束，在定义表时 给表设置的检查规则  用于保证数据库的完整性
DROP TABLE tab_1;
-- 非空约束：not null
CREATE TABLE tab_1(
   tid INT,
   tname VARCHAR(11) NOT NULL
);
INSERT INTO tab_1 VALUES(1,'韩梅梅');
INSERT INTO tab_1 VALUES(2,NULL);  -- error:Column 'tname' cannot be null
INSERT INTO tab_1(tid) VALUES(23); -- wain:Field 'tname' doesn't have a default value  使用默认值""
SELECT * FROM tab_1;
INSERT INTO tab_1(tid) VALUES(31);
SELECT * FROM tab_1 WHERE tname IS NULL;
SELECT * FROM tab_1 WHERE tname='';



-- 唯一约束: unique: 可以有多个null 但如果不是null 必须不能重复
CREATE TABLE tab_1(
 tid INT,
 tname VARCHAR(11) UNIQUE
);
DESC tab_1;
INSERT INTO tab_1 VALUES(1,'韩梅梅');
INSERT INTO tab_1 VALUES(0,NULL);
INSERT INTO tab_1 VALUES(11,NULL);
INSERT INTO tab_1 VALUES(2,'韩梅2');
INSERT INTO tab_1 VALUES(3,'韩梅2'); -- Error :Duplicate entry '韩梅2' for key 'tname'

-- 默认约束：default : 不给当前字段赋值时  值去默认值
CREATE TABLE tab_1(
 tid INT,
 tname VARCHAR(11) DEFAULT '无名'
);
INSERT INTO tab_1 VALUES(0,'韩梅梅');
INSERT INTO tab_1 VALUES(1,NULL);
INSERT INTO tab_1(tid) VALUES(2);
SELECT * FROM tab_1;

-- 检查约束(5.1不支持):
CREATE TABLE tab_1(
 tid INT,
 tage INT,
 CHECK(tage > 0 AND tage < 120)
);
INSERT INTO tab_1 VALUES(2,11);
INSERT INTO tab_1 VALUES(21,-11);
SELECT * FROM tab_1;

-- 主键约束：primary key
-- 当某列可以唯一标示本行记录时 此列为主键列
-- 特点：1：值必须唯一：
-- 特点：2：非空
-- 特点：3：可以被从表引用
DROP TABLE tab_1;
-- 创建表时1: 添加主键约束
CREATE TABLE tab_1(
 tid INT PRIMARY KEY,
 tname VARCHAR(10)
);
DESC tab_1;
-- 创建表时2: 添加主键约束
CREATE TABLE tab_1(
 tid INT,
 tname VARCHAR(10),
 PRIMARY KEY(tid)
);
INSERT INTO tab_1 VALUES(1,'韩梅梅');
INSERT INTO tab_1 VALUES(2,'韩梅梅2');
INSERT INTO tab_1 VALUES(1,'韩梅梅3'); -- Duplicate entry '1' for key 'PRIMARY'
INSERT INTO tab_1 VALUES(NULL,'韩梅梅4'); -- Column 'tid' cannot be null
DELETE  FROM tab_1;
SELECT * FROM tab_1;
-- 删除主键约束
ALTER TABLE tab_1 DROP PRIMARY KEY;
-- 创建表后 添加主键约束
ALTER TABLE tab_1 ADD PRIMARY KEY(tid);
ALTER TABLE tab_1 CHANGE tid tid INT PRIMARY KEY;
ALTER TABLE tab_1 MODIFY tid INT PRIMARY KEY;

-- 自增约束：auto_increment: 只限于整数类型主键
-- 数据库中定义一个变量 来记录主键列的历史最大值
CREATE TABLE tab_1(
 tid INT PRIMARY KEY AUTO_INCREMENT,
 tname VARCHAR(10)
);
INSERT INTO tab_1 VALUES(11,'韩梅梅');
INSERT INTO tab_1 VALUES(50,'韩梅梅5');
INSERT INTO tab_1 VALUES(33,'韩梅梅3');
INSERT INTO tab_1 VALUES(-11,'韩梅梅3');
INSERT INTO tab_1 VALUES(-12,'韩梅梅3');
INSERT INTO tab_1 VALUES(NULL,SUBSTRING(UUID(),1,9));
DELETE FROM tab_1 WHERE tid >20;
SELECT * FROM tab_1;

-- 外键约束：foreign key
-- 当a表中 要引用b表的记录时：在a表中定义一列 作为外键列 来引用b表的主键列
-- a表称之为从表,b表称之为主表
-- 外键特点：1 可以为null 2可以重复  3 如果有值 此值必须在主表的主键列中存在
--           4 一张从表 可以定义多个外键列
-- 创建表时 定义外键约束
CREATE TABLE tab_2(
   tid2 INT PRIMARY KEY,
   tname2 VARCHAR(10),
   tid1 INT,
   CONSTRAINT fk_1 FOREIGN KEY(tid1) REFERENCES tab_1(tid)
   /*fk_1 是外键约束名：用于删除使用*/
);
DROP TABLE tab_2;
DESC tab_2;
DELETE FROM tab_2;
INSERT INTO tab_2 VALUES(1,'同学1',NULL);
INSERT INTO tab_2 VALUES(2,'同学1',1);
INSERT INTO tab_2 VALUES(3,'同学3',1);
INSERT INTO tab_2 VALUES(4,'同学4',20);-- Cannot add or update a child row: a foreign key constraint fails (`db_1`.`tab_2`, CONSTRAINT `fk_1` FOREIGN KEY (`tid1`) REFERENCES `tab_1` (`tid`))

-- 删除外键约束
ALTER TABLE tab_2 DROP FOREIGN KEY fk_1;

-- 创建表后 添加外键约束
ALTER TABLE tab_2 ADD CONSTRAINT fk_1 FOREIGN KEY(tid1) REFERENCES tab_1(tid);

2 数据库三范式

数据库设计时 大部分情况下 需要准守的三种规则：1nf 2nf 3nf

1nf: 第一范式：

保证列的原子性：列数据不能再分割

错误示范

create table user(
  uid int primary key,
  uname varchar(10),
  uaddress varchar(200)
);
insert into user values(1,"张三","河南省郑州市二七区xx社区x楼x号");
insert into user values(2,"李四","河南省郑州市经开区xx社区x楼x号");

缺点：地址使用时 需要进行拆分：地址还必须满足指定格式
改进：把uaddress字段拆分为需要的多个小字段

正确写法

create table user(
  uid int primary key,
  uname varchar(10),
  uprovince varchar(10),
  ucity varchar(10),
  usheQu varchar(10)
);

2nf: 第二范式

保证所有列 直接依赖主键列 消除间接依赖

错误示范

create table school(
   sid int parimary key,
   sname varchar(100),
   stype varchar(100),
   sheader_name varchar(10),
   sheader_sex char(1),
   sheader_salary float(9,2),
   sheader_age int
);

缺点： sheader_name，sheader_sex，sheader_salary，sheader_age是学校的校长的数据 不是直接描述学校
      一张表既有描述学校的信息 还有描述学校校长的信息：：冗杂
改进： 在重新创建一个表描述校长 学校中定义一个外键来引用校长的主键列即可

正确写法

create table school_header(
   hid int parimary key,
   hname varchar(100),
   hsex char(1),
   hsalary float(9,2),
   hage int
);
create table school(
   sid int parimary key,
   sname varchar(100),
   stype varchar(100),
   shid int,
   constraint fk_22 foreign key (shid) references school_header(hid)
);

3nf: 第三范式

联合主键时：保证所有的列必须完全依赖主键 取消部分依赖

准备

学生表：
create student(
   sid int primary key,
   sname varchar(100),
   sex char(1),
   sclass varchar(10)
);
课程表
create ke_cheng(
    kid int primary key,
    kname varchar(100),
    kscore float(2,1)
);
选课表
create xuan_ke1(
    sid int,
    kid int,
    primary key(sid,kid)
);
insert into xuan_ke values(1,2);
insert into xuan_ke values(2,2);
insert into xuan_ke values(1,1);

create xuan_ke2(
    sid int,
    kid int,
    primary key(sid,kid),
    kao_shi_score float(4,1),
    ke_cheng_content varchar(100) ,/*课程描述*/
    student_age int/*学生年龄*/
);

xuan_ke2中的ke_cheng_content 只是依赖联合主键(sid+kid)中的kid 于sid无关 这叫部分依赖
xuan_ke2中的student_age 只是依赖联合主键(sid+kid)中的sid 于kid无关 这叫部分依赖
xuan_ke2不是完全描述选课的：内容冗余 不清晰

3 多表查询

3.0 准备

CREATE DATABASE db_1 CHARSET='utf8';
USE db_1;
CREATE TABLE teacher(
  tid INT PRIMARY KEY AUTO_INCREMENT,
  tname VARCHAR(10),
  tsex CHAR(1),
  tsalary FLOAT(8,2)
);
INSERT INTO teacher VALUES(NULL,"张1老师",'男',3001.1);
INSERT INTO teacher VALUES(NULL,"张2老师",'男',3020.1);
INSERT INTO teacher VALUES(NULL,"张3老师",'女',3300.1);
INSERT INTO teacher VALUES(NULL,"高1老师",'男',4000.1);
INSERT INTO teacher VALUES(NULL,"田老师",'女',3500.1);
INSERT INTO teacher VALUES(NULL,"高2老师",'男',5000.1);
INSERT INTO teacher VALUES(NULL,"王老师",'女',7000.1);
INSERT INTO teacher VALUES(NULL,"王三",'男',5000.1);
INSERT INTO teacher VALUES(NULL,"李四",'男',5200.1);
CREATE TABLE student(
  sid INT PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(10),
  sex CHAR(1),
  score FLOAT(8,2),
  sage INT,
  stid INT,
  CONSTRAINT fk_001 FOREIGN KEY(stid) REFERENCES teacher(tid)
);
ALTER TABLE student ADD sclass VARCHAR(100);
UPDATE student SET sclass=CONCAT("class_",MOD(sid,5)+1);
INSERT INTO student VALUES(
    NULL,
    SUBSTRING(REPLACE(UUID(),"-",""),1,8),
    IF(RAND()>0.5,"男","女"),
    TRUNCATE(RAND()*100,1),
    TRUNCATE(RAND()*10+15,0),
    TRUNCATE(RAND()*7+1,0)
);
INSERT INTO student VALUES(
    NULL,
    SUBSTRING(REPLACE(UUID(),"-",""),1,8),
    IF(RAND()>0.5,"男","女"),
    TRUNCATE(RAND()*100,1),
    TRUNCATE(RAND()*10+15,0),
    NULL
);
INSERT INTO student VALUES(
    NULL,
    "李四",
    '男',
    TRUNCATE(RAND()*100,1),
    TRUNCATE(RAND()*10+15,0),
    TRUNCATE(RAND()*7+1,0)
);
SELECT * FROM student;
SELECT * FROM teacher;

3.1 合并结果集

-- 多表查询：一次查询多张表
-- 情况1：合并结果集：把两个结果集通过 union /union all 合并为一个结果集
SELECT sname,sex FROM student;
SELECT tname,tsex FROM teacher;
    /*合并结果集：结果集的列数必须相同+对应列的列类型必须一致*/
    /*获取全校师生的名字和性别：去除了重复的行*/
SELECT sname,sex FROM student UNION
SELECT tname,tsex FROM teacher;
   /*获取全校师生的名字和性别：保留所有记录*/
SELECT sname 师生名字,sex 师生性别 FROM student UNION ALL
SELECT tname,tsex FROM teacher;

3.2 连接查询

-- 情况2：连接查询：同时查询两张表/from后面有两张表
--  笛卡尔积：：让两张表的所有记录见面
SELECT t.*,s.* FROM teacher t,student s;/*行数=9*47 列数=4+6*/
--  r=1+sind@
--  去除垃圾的笛卡尔积行：主表的主键值=从表的外键值

   /*获取学生及其老师的信息*/
--  方言格式的内连接
SELECT t.*,s.* FROM teacher t,student s WHERE t.tid=s.stid;
--  标准格式的内连接
SELECT t.*, s.* FROM teacher t INNER JOIN student s ON t.tid=s.stid;

-- 左外连接:保证左边表的完整性：：
    /*获取所有老师及其学生的信息*/
SELECT t.*,s.* FROM teacher t LEFT  JOIN student s ON t.tid=s.stid;
SELECT t.*,s.* FROM student s RIGHT  JOIN teacher t ON t.tid=s.stid;

-- 右外连接:保证右边表的完整性：：
    /*获取所有学生及其老师的信息*/
SELECT t.*,s.* FROM teacher t RIGHT  JOIN student s ON t.tid=s.stid;

3.3 子查询

-- 情况3：子查询：select嵌套select
-- 子select在where后面:作为条件
-- 可以使用的运算符：比较运算符：= > >= < <= != (子select获取的是一个值：单行单列)
--                   范围判断： in/not in  /> all /> any  /< all /< any 

  /*获取分数最高的学生的信息*/
SELECT * FROM student WHERE score=MAX(score); -- Invalid use of group function/where后面不能出现聚合函数
  -- 获取最高分
SELECT MAX(score) FROM student;
  -- 获取分数市最高分的学生的信息
SELECT * FROM student WHERE score=(SELECT MAX(score) FROM student);
  /*获取年龄和王三、李四、a3d22eeb相同的所有学生的信息*/
  -- 获取"王三","李四","王五"个学生的年龄
SELECT sage FROM student WHERE sname IN("王三","李四","王五"); 
  -- 获取年龄是这个范围内的所有学生的信息
SELECT * 
FROM student 
WHERE sage IN(SELECT sage 
              FROM student 
              WHERE sname IN("王三","李四","王五"));
  /*获取和年龄为21的所有学生分数不同的其他所有学生*/
SELECT score FROM student WHERE sage=21;  
SELECT * 
FROM student 
WHERE score NOT IN(    SELECT score 
			FROM student 
			WHERE sage=21);
  /*获取比class_2班的所有学生分数高的所有学生*/
SELECT MAX(score) FROM student WHERE sclass="class_2";
SELECT * FROM student WHERE score > (SELECT MAX(score) FROM student WHERE sclass="class_2");

SELECT score FROM student WHERE sclass="class_2";
SELECT * FROM student WHERE score > ALL(SELECT score FROM student WHERE sclass="class_2");
  
   /*获取比class_2班任意学生分数高的所有学生的信息*/
SELECT * FROM student WHERE sclass!='class_2' AND score > ANY(SELECT score FROM student WHERE sclass="class_2");
  
   /*获取和class_2班学生性别相同并且姓相同的所有学生*/
   /*获取class_2班学生性别相同并且姓*/
SELECT DISTINCT sex,SUBSTRING(sname,1,1) xing FROM student WHERE sclass="class_2"; 

SELECT * 
FROM student 
WHERE sclass!='class_2' AND (sex,SUBSTRING(sname,1,1)) IN (SELECT DISTINCT sex,SUBSTRING(sname,1,1) xing 
							   FROM student 
							   WHERE sclass="class_2");
   /*获取和张三相同性别 相同老师的所有学生的信息*/
SELECT sex,stid FROM student WHERE sname="王三";
SELECT * FROM student WHERE sname!='王三' AND (sex,stid) = (SELECT sex,stid FROM student WHERE sname="王三");	


-- 子select在from后面：作为表
/*获取女老师及其学生的信息*/
SELECT * FROM student s,teacher t WHERE t.tsex='女'	AND t.tid=s.stid;
SELECT * FROM student s,(SELECT * FROM teacher WHERE tsex='女') t WHERE s.stid=t.tid;

-- 子select在select后面：作为结果集的列
/*获取所有学生 及其与平均分之间的差值*/ 
SELECT *,score-AVG(score) FROM student;	 -- 集合函数只能是一个值
SELECT *,score-(SELECT AVG(score) FROM student) 差值 FROM student;

-- 获取所有学生的信息 并显示学生的最高分
SELECT *,(SELECT MAX(score) FROM student) 最高分 FROM student;
SELECT *,1  FROM student;	
SELECT *, MAX(score) FROM student;