MySQL数据库学习笔记--查询
- MySQL数据库学习笔记--查询
- 备份与恢复
- 主键
- 主键约束(唯一标识)
- 主键自增长
- 非空与唯一约束
- 对象模型与关系模型
- 外键约束
- 一对一关系与多对多关系
- 合并结果集
- 连接查询
- 内连接
- 外连接
- 子查询
- 关键字集合
MySQL数据库学习笔记–查询
备份与恢复
- 备份,关键字:mysqldump 与 >
注意:生成的脚步不包含create database语句,所以恢复是需要先有数据库
在命令窗口(非数据库命令行)执行如下命令:
mysqldump -u用户名 -p密码 数据库名>生成的脚本文件路径mysqldump -uroot -p1234 my_db>D:\my_db.sql
- 恢复,关键字:<
注意:需要先删除存在的数据库并重建。
- 第一种方式
在命令窗口(非数据库命令行)执行如下命令:
mysql -u用户名 -p密码 数据库<脚本文件路径
mysql -uroot -p1234 my_db<D\my_db.sql
- 第二种方式,关键字:source
登录mysql,在mysq命令行执行如下命令:
source 脚本路径
source D:\my_db.sql
主键
主键约束(唯一标识)
- 三要素:
- 非空 :不为空
- 唯一 :不可重复
- 被引用 :可被其他列引用
- 添加主键约束:
指定sid列为主键列,即为sid列添加主键约束,关键字:PRIMARY KEY。
// 创建表并指定主键列
CREATE TABLE stuent(
sid INT PRIMARY KEY,
sname VARCHAR(20),
age INT
);
CREATE TABLE student(
sid INT,
sname VARCHAR(20),
age INT,
PRIMARY KEY(sid)
);
// 将已存在的表指定主键列
ALTER TABLE student ADD PRIMARY KEY(sid);
- 插入记录:
插入记录主键值不可重复。
INSERT INTO stuent VALUES(100, 'ZhangSan',20);
主键自增长
指定sid列为主键列,添加主键自增长约束,关键字:AUTO_INCREMENT。
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20),
age INT
);
插入数据:
INSERT INTO student VALUES(NULL, 'ZhangSan', 10);
执行DELETE指令后再次插入数据不会重新计数。
非空与唯一约束
- 添加非空约束,关键字:NOT NULL
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL,
age INT
);
- 添加唯一约束,关键字: UNIQUE
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(20) NOT NULL UNIQUE,
age INT
);
对象模型与关系模型
概念模型
- 对象模型:在java中是domain。例如:Student类
is Student (类型)
has Name (属性,关联关系,1对1;1对多;多对多)
use Mouth (方法) - 关系模型:在数据库中是表。
1对1/1对多/多对多。
外键约束
- 添加外键约束,关键字:CONSTRAINT 名称 FOREIGN KEY(外键列名) REFERENCES 表名(列名)
// 创建表时添加外键约束
CREATE TABLE department(
deptno INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(50),
);
CREATE TABLE employee(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(50),
deptno INT,
CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES department(deptno)
);
// 在已存在的表中添加外键约束
ALTER TABLE employee
ADD CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES department(deptno);
一对一关系与多对多关系
- 一对一关系,关键字:从表的主键即是外键
CREATE TABLE hasband(
hid INT PRIMARY KEY AUTO_INCREMENT,
hname VARCHAR(50)
);
CREATE TABLE wife(
wid INT PRIMARY KEY AUTO_INCREMENT,
wname VARCHAR(50),
CONSTRAINT fk_wife_hasband FOREIGN KEY(wid) REFERENCES hasband(hid)
);
- 多对多关系,关键字:通过中间表处理
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)
);
合并结果集
关键字:UNION去除重复行;UNION ALL不去除重复行
注意:需要查询的结果集列数与列类型相同
CREATE TABLE department(
deptno INT PRIMARY KEY AUTO_INCREMENT,
dname VARCHAR(50),
);
CREATE TABLE employee(
empno INT PRIMARY KEY AUTO_INCREMENT,
ename VARCHAR(50),
deptno INT,
CONSTRAINT fk_emp_dept FOREIGN KEY(deptno) REFERENCES department(deptno)
);
// 报错
SELECT * FROM employee UNION ALL SELECT * FROM department;
// 报错
SELECT * FROM employee UNION SELECT * FROM department;
// 正确,不包含重复记录
SELECT deptno FROM employee UNION SELECT * FROM department;
// 正确,包含重复记录
SELECT deptno FROM employee UNION ALL SELECT * FROM department;
连接查询
内连接
内连接查询出的所有记录都满足条件,关键字:INNER JOIN
- 方言:SELECT * FROM 表1 别名1, 表2 别名2 WHERE 别名1.xx=别名2.xx;
如果不加条件会出现大量重复数据,通过条件可以去笛卡尔积
// 如下查询会得到两个表记录数乘积的结果数。比如:emp有10条记录,dept有5条记录;查询结果会得到50条记录。这个结果叫“笛卡尔积”
SELECT * FROM employee, department;
// 笛卡尔积可以通过条件过滤无用的记录
SELECT * FROM employee, department WHERE employee.deptno=department.deptno;
- 标准:SELECT * FROM 表1 别名1 INNER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
SELECT * FROM employee e INNER JOIN department d ON e.deptno=d.deptno;
- 自然:SELECT * FROM 表1 别名1 NATURAL JOIN 表2 别名2;
这个会自动匹配同名且同类型的列,相当于自动加WHERE条件。
SELECT * FROM employee e NATURAL JOIN department d;
外连接
外连接分主次,主表中无论满不满足条件都打印,不满足的记录此表信息显示NULL。
- 左外连接:SELECT * FROM 表1 别名1 LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
左表记录无论是否满足条件都会查询出来,而右表只有满足条件才能查询出来。左表中不满足条件的记录右表部分的信息都为NULL
SELECT * FROM employee e LEFT OUTER JOIN department d ON e.deptno=d.deptno;
- 左外自然:SELECT * FROM 表1 别名1 NATURAL LEFT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
- 右外连接:SELECT * FROM 表1 别名1 RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
右表记录无论是否满足条件都会查询出来,而左表只有满足条件才能查询出来。右表中不满足条件的记录左表部分的信息都为NULL
SELECT * FROM employee e RIGHT OUTER JOIN department d ON e.deptno=d.deptno;
- 右外自然:SELECT * FROM 表1 别名1 NATURAL RIGHT OUTER JOIN 表2 别名2 ON 别名1.xx=别名2.xx;
- 全外连接(MySQL不支持):可以使用UNION来完成全连接
SELECT e.ename, e.sal, d.dname FROM employee e LEFT OUTER JOIN department d ON e.deptno=d.deptno
UNION
SELECT e.ename, e.sal, d.dname FROM employee e RIGHT OUTER JOIN department d ON e.deptno=d.deptno;
子查询
查询中包含查询(有多个SELECT)
// 查询雇员表中工资最高的员工
SELECT * FROM employee WHERE sal=(SELECT MAX(sal) FROM employee);
关键字集合
mysqldumpsourcePRIMARY KEYAUTO_INCREMENTNOT NULLUNIQUECONSTRAINT 名称 FOREIGN KEY(外键列名) REFERENCES 表名(列名)UNIONUNION ALLINNER JOIN