MySQL进阶学习（约束、关系模型、多表查询）

MySQL进阶学习

文章目录

MySQL备份与恢复

1.数据库导出sql脚本（备份数据库内容，并不是备份数据库）
mysqldump -uroot -pcql666 mydb>D:/a.sql

-- 在D盘下生成一个a.sql脚本保存文件。
2.恢复数据

  • 登陆前恢复

    create database mydb;    -- 先恢复数据库

mysql -uroot -pcql666 mydb<D:/a.sql    -- 再恢复数据库内容

  • source D:/a.sql; -- 登陆后恢复

约束

主键约束

主键：唯一标识

  • 非空
  • 唯一
  • 被引用
创建表时加主键约束
CREATE TABLE emp(

empno INT PRIMARY KEY,

ename VARCHAR(50)

);

  • CREATE TABLE emp(

empno INT,

ename VARCHAR(50),

PRIMARY KEY(empno)

);
修改表时加主键约束

ALTER TABLE emp ADD PRIMARY KEY(empno);

删除主键

ALTER TABLE emp DROP PRIMARY KEY;

主键自增长

必须加在整数列的后面

CREATE TABLE emp(

sid INT PRIMARY KEY AUTO_INCREMENT, 	

sname VARCHAR(50)

);
非空约束
CREATE TABLE emp(

sid INT NOT NULL，

sname VARCHAR(50)

);
唯一约束
CREATE TABLE emp(

sid INT NOT NULL UNIQUE，

sname VARCHAR(50)

);
外键约束
  • 外键必须是另一张表/自身的主键（外键要引用主键）；
  • 外键可以重复；
  • 外键可以为空；
  • 一张表中可以有多个外键（对应不同的表）
创建表时设置
CREATE TABLE dept(

deptno INT PRIMARY KEY AUTO_INCREMENT,

dname VARCHAR(50)

);

CREATE TABLE emp(

sid INT PRIMARY KEY AUTO_INCREMENT, 	

sname VARCHAR(50),

dno INT,

CONSTRAINT fk_emp_dept FOREIGN KEY (dno) REFERENCES dept(deptno)

);
修改表时添加外键
ALTER TABLE emp 

ADD CONSTRAINT fk_emp_dept FOREIGN KEY (dno) REFERENCES dept(deptno);

关系模型

1对1

从表的主键作为外键

CREATE TABLE hasbend(

hid INT PRIMARY KEY AUTO_INCREMENT,

sname VARCHAR(50)

); 

CREATE TABLE wife(

wid INT PRIMRAY KEY AUTO_INCREMENT,

sname VARCHAR(50),

CANSTRAINT fk_wife_hasbend FOREIGN KEY(wid) REFERENCES hasbend(hid)

);
1对多

在从表中创建新列作为外键，连接主表主键

CREATE TABLE class( cid INT PRIMARY KEY AUTO_INCREMENT, sname VARCHAR(15) NOT NULL 
); 
INSERT INTO class VALUES(NULL,'六年级一班'); 
INSERT INTO class VALUES(NULL,'六年级二班');
SELECT * FROM class; 

/*在sql语句末端添加*/ 
CREATE TABLE student( 
sid INT AUTO_INCREMENT, 
sname VARCHAR(10), 
s_cid INT, PRIMARY KEY(sid), 
FOREIGN KEY (s_cid) REFERENCES class(cid) 
);
INSERT INTO student VALUES(NULL,'王大锤',2); 
INSERT INTO student VALUES(NULL,'胡汉三',3);/*约束生效　　插入失败*/ 
SELECT * FROM student;
多对多

创建关系表

/*创建学生表*/
CREATE TABLE student(
  sid INT PRIMARY KEY AUTO_INCREMENT,
  sname VARCHAR(50));

/*创建教师表*/
CREATE TABLE teacher(
  tid INT PRIMARY KEY AUTO_INCREMENT,
  tname VARCHAR(50));

/*创建关联表*/
CREATE TABLE stu_tea(
  sid INT,
  tid INT,
  CONSTRAINT fk_student FOREIGN KEY(sid) REFERENCES student(sid),
  CONSTRAINT fk_teacher FOREIGN KEY(tid) REFERENCES teacher(tid)
);
/*往2张表插记录*/
INSERT INTO student VALUES(NULL,'刘德华');
INSERT INTO student VALUES(NULL,'梁朝伟');
INSERT INTO student VALUES(NULL,'谢霆锋');
INSERT INTO student VALUES(NULL,'张含韵');
INSERT INTO student VALUES(NULL,'小虎乐队');

INSERT INTO teacher VALUES(NULL,'李某');
INSERT INTO teacher VALUES(NULL,'童某');
INSERT INTO teacher VALUES(NULL,'张某');

/*分别查出来*/
SELECT * FROM student;
SELECT * FROM teacher;

/*建立关系*/
INSERT INTO stu_tea VALUES(1,1);
INSERT INTO stu_tea VALUES(2,1);
INSERT INTO stu_tea VALUES(3,1);
INSERT INTO stu_tea VALUES(4,1);
INSERT INTO stu_tea VALUES(5,1);

INSERT INTO stu_tea VALUES(2,2);
INSERT INTO stu_tea VALUES(3,2);
INSERT INTO stu_tea VALUES(4,2);

INSERT INTO stu_tea VALUES(3,3);
INSERT INTO stu_tea VALUES(4,3);
INSERT INTO stu_tea VALUES(5,3);

/*查关系表*/
SELECT * FROM stu_tea;

多表查询

合并结果集
  • 要求被合并的表中，列的类型和列数相同
/*--------课时11 合并结果集--------*/ 
/*创建2个表结构一样的表*/ 
CREATE TABLE ab (a INT , b VARCHAR(50));
CREATE TABLE cd (c INT , d VARCHAR(50));
/*插入记录*/ 
INSERT INTO ab VALUES(1,'1');
INSERT INTO ab VALUES(2,'2');
INSERT INTO ab VALUES(3,'3');

INSERT INTO cd VALUES(3,'3');
INSERT INTO cd VALUES(4,'4');
INSERT INTO cd VALUES(5,'5');

/*链接2个表结构一样的表(合并结果集))*/
SELECT * FROM ab
UNION ALL
SELECT * FROM cd

/*链接2个表结构一样的表(合并结果集))*/
SELECT * FROM ab
UNION /*完全相同的行会被去除*/
SELECT * FROM cd
连接查询
内连接

笛卡尔积：{a, b, c}{d, e}->{a1, a2, a3. a4, a5,a6}

  • 方言：

SELECT * FROM 表1 别名1 表2 别名2 WHERE 别名1.xx=别名2.xx

SELECT emp.ename,emp.sal, dept.dname
FROM emp, dept
WHERE emp.deptno=dept.deptno;     -- 去除笛卡尔积

SELECT e.ename,e.sal, d.dname
FROM emp e, dept d  -- 起别名
WHERE e.deptno=d.deptno;     -- 去除笛卡尔积
  • 标准：

SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx

  • 自然：

SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2

外连接

左外连接

SELECT e.ename,e.sal,IFNULL(d.dname,'无部门')
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno;

右外连接

SELECT e.ename,e.sal,IFNULL(d.dname,'无部门')
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno;

全外连接（MySQL不支持）

SELECT e.ename,e.sal,d.dname
FROM emp e LEFT OUTER JOIN dept d
ON e.deptno = d.deptno
UNION -- 合并结果集
SELECT e.ename,e.sal,d.dname
FROM emp e RIGHT OUTER JOIN dept d
ON e.deptno = d.deptno
子连接

  • 出现的位置

    • where后作为条件出现

      -- 查询本公司工资最高的员工的详细信息
SELECT * FROM emp WHERE sal=(SELECT MAX(sal) FROM emp);

    • from后作为表存在

      SELECT e.ename FROM (SELECT * FROM emp) e;
posted @ 2020-08-05 10:56  ccql  阅读(3)  评论(0)  编辑  收藏  举报  来源