mysql基础复习
MYSQL
一、数据库概述
1.1数据管理技术的发展阶段
- 人工阶段 50年代中期以前（磁带、纸带，无磁盘 ）
- 文件 50年代后期到60年代中期（磁盘 ）
- 数据库系统 60年代后期（大容量磁盘，硬件价格下降 ）
1.2数据库
数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
数据库中的数据按一定的数据模型组织、描述和储存，具有较小的冗余度、较高的数据独立性和易扩展性，并可为各个用户共享。
数据库的作用？
存储大量数据，方便查询与操作（select）
保持数据信息的一致,完整（主键 primary key 外键 foreign key 约束 check）
共享和安全（不同的用户赋予不同的权限，共享如第三方登录）
通过组合分析,产生新的有用信息（根据实际的业务信息查找数据显示）
如：数据库中的记录
李明，男，21，1972，江苏，计算机系，1990
可翻译为：
李明是个大学生，1972年出生，男,江苏人，1990年考入计算机系
1.3数据库管理系统
Database Management System简称DBMS
是一种操纵和管理数据库的软件，用于建立、使用和维护数据库 它对数据库进行统一的管理和控制，以保证数据库的安全性和完整性。
常见数据库：
- Oracle是目前世界上使用最为广泛的数据库管理系统, 是甲骨文公司的一款关系数据库管理系统
- SqlServer是由Microsoft开发和推广的关系数据库管理系统
- DB2 是美国IBM公司开发的一套关系型数据库管理系统, 主要应用于大型应用系统
- MySql是一个小型的关系型数据库管理系统, 目前属于 Oracle 旗下产品. 与其他的大型数据库例如 Oracle、DB2、SQL Server等相比，MySQL 自有它的不足之处，但是这丝毫也没有减少它受欢迎的程度。对于一般的个人使用者和中小型企业来说，MySQL提供的功能已经绰绰有余，而且由于 MySQL是开放源码软件，因此可以大大降低总体拥有成本。
除了以上的关系数据库管理系统之外，还有Redis，MongoDB等非关系数据库，基于Key-Value存储的数据库，统称为NoSQL（Not only sql）。
关系数据库管理系统RDBMS(Relational Database Management System)：包括相互联系的数据集合 (数据库)和存取这些数据的一套程序 (数据库管理系统软件)。关系数据库管理系统就是管理关系数据库，并将数据组织为相关的行和列的系统。
二、Mysql安装
MySQL卸载
1.在控制面板\所有控制面板项\程序和功能中找到MySQL相关的应用程序,将应用程序卸载
2.把MySQL安装目录删除
D:\MySQL
3.把MySQL的数据文件删除
C:\ProgramData\MySQL
4.清除MySQL注册表
按win + R 输入 regedit 进入注册表
在注册表中找到 HKEY_LOCAL_MACHINE 项
右键点击 查找 勾选项 输入MySQL
删除以MySQL开头的文件夹
三、Navicat安装
Navicat是一套快速、可靠并价格相当便宜的数据库管理工具，专为简化数据库的管理及降低系统管理成本而设。它的设计符合数据库管理员、开发人员及中小企业的需要。Navicat 是以直觉化的图形用户界面而建的，让你可以以安全并且简单的方式创建、组织、访问并共用信息。
安装时按照安装步骤安装即可，推荐不要安装在C盘
如果没有Navicat，我们访问数据库如下方式，如果想通过此方式测试，需要配置mysql环境变量，这里不在讲解，实现仅作为了解参考。
C:\Users\ibf>mysql -uroot -p
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| beifeng |
| mysql |
| performance_schema |
| sakila |
| shclz |
+--------------------+
10 rows in set (0.55 sec)
mysql> use beifeng
Database changed
mysql> show tables;
+------------------------+
| Tables_in_beifeng |
+------------------------+
| messafe_info |
| role_anthority_info |
| role_info |
| staff_info |
| staff_salary |
+------------------------+
23 rows in set (0.06 sec)
mysql> select * from role_info
-> ;
+---------+------------+------------------------+------------+
| role_id | role_name | role_desc | role_state |
+---------+------------+------------------------+------------+
| 1 | 超级管理员 | 天天管学生 | 1 |
| 2 | 招生人员 | 找人培训 | 0 |
| 3 | 网大管 | 天天修电脑，天天接网线 | 1 |
+---------+------------+------------------------+------------+
3 rows in set (0.31 sec)
四、SQL概述
Structured Query Language(结构化查询语言)的缩写。SQL是专为数据库而建立的操作命令集，是一种功能齐全的数据库语言。在使用它时，只需要发出“做什么”的命令，“怎么做”是不用使用者考虑的。SQL功能强大、简单易学、使用方便，已经成为了数据库操作的基础。
特点：一条SQL语句一个执行结果
重点
select 选择，from 从...，insert插入 （into 到..），update 修改、更新，delete 删除，table 表，database 数据库，create创建，drop 丢弃， values 值（复数），primary key 主键，foreign key 外键，reference 引用，where 哪里，inner 内部的 join 连接 =》内连接，left join 左连接，right join 右连接，group（组，分组） by.. 根据..进行分组，having 有...，order by.. 根据..进行排序，desc（descending） 降序，asc（ascending）升序，auto_increment自增长，default 默认,null 空，not null非空，unique 唯一的，set 设置（值），distinct 确定的（去除重复数据），between ... and ... 在..和..之间，in 在..里面，like 像..一样(%,_)，limit 限制（分页时使用）(position,pageSize)，show 展示,use 使用,exists 存在,count统计，sum求和，avg平均值，max最大值，min最小值, describe描述
五、Mysql数据类型
5.1整形
| MySQL数据类型 | 含义（有符号） |
|---|---|
| tinyint | 1个字节 范围(-128~127) |
| smallint | 2个字节 范围(-32768~32767) |
| mediumint | 3个字节 范围(-8388608~8388607) |
| int | 4个字节 范围(-2147483648~2147483647) |
| bigint | 8个字节 范围(+-9.22*10的18次方) |
注意：应用int为主
5.2小数
| MySQL数据类型 | 含义 |
|---|---|
| float(m,d) | 单精度浮点型 8位精度(4字节) m总个数，d小数位 |
| double(m,d) | 双精度浮点型 16位精度(8字节) m总个数，d小数位 |
| decimal(m,d) | 参数m<65 是总个数，d<30且 d<m 是小数位 ( 将来可能会看到numberic，其余decimal类似 ) |
设一个字段定义为float(6,3)，如果插入一个数123.45678,实际数据库里存的是123.457，但总个数还以实际为准，即6位。整数部分最大是3位，如果插入数12.123456，存储的是12.1234，如果插入12.12，存储的是12.1200
decimal(10,2) 123.45678=> 123.45 12.123456=>12.12
5.3字符串
| MySQL数据类型 | 含义 |
|---|---|
| char(n) | 固定长度，最多255个字符 |
| varchar(n) | 固定长度，最多65535个字符 |
| tinytext | 可变长度，最多255个字符 |
| text | 可变长度，最多65535个字符 |
| mediumtext | 可变长度，最多2的24次方-1个字符 |
| longtext | 可变长度，最多2的32次方-1个字符 |
注意：应用以varchar为主
5.4日期
| MySQL数据类型 | 含义 |
|---|---|
| date | 日期 '2008-12-2' |
| time | 时间 '12:25:36' |
| datetime | 日期时间 '2008-12-2 22:06:44' |
| timestamp | 自动存储记录修改时间戳 |
六、数据库创建删除
查看所有的数据库
show databases; # sql语句 ；表示一条sql语句结束了
CREATE DATABASE [IF NOT EXISTS] 数据库名;
create database myschool;
create database if not exists myschool;
DROP DATABASE [IF EXISTS] 数据库名;
drop database abcd;
drop database if exists abcd;
使用(切换)数据库
USE 数据库名;
use train；
查看某个库中的所有的表(注意:在操作表之前必须先使用数据库)
show tables;
七、创建表
CREATE TABLE 表名(
字段1 字段类型1,
字段2 字段类型2,
....
字段n 字段类型n
);
CREATE TABLE Student
( Sno varchar(9) ，
Sname varchar(20) ，
Ssex varchar(2)，
Sage INT，
Sdept varchar(20)
)；
八、插入数据
INSERT INTO <表名> [(<属性列1>[，<属性列2 >…)]
VALUES (<常量1> [，<常量2>] … )
| 学 号 Sno | 姓 名 Sname | 性 别 Ssex | 年 龄 Sage | 所 在 系 Sdept |
|---|---|---|---|---|
| 200215121 | 李勇 | 男 | 20 | CS |
| 200215122 | 刘晨 | 女 | 19 | CS |
| 200215123 | 王敏 | 女 | 18 | MA |
| 200515125 | 张立 | 男 | 19 | IS |
-
向表中指定列插入数据
# 向表中的指定列插入数据 出现主键重复时，会报错，Duplicate 重复 Duplicate entry insert into student(sno,sname,sdept) values('08070203','诸葛亮','CS'); -
向表中所有列插入数据
写法1：
# 按照列出现的顺序向表中插入数据时，(sno,sname,sage,ssex,sdept)可以省略不写，如上面的格式 insert into student(sno,sname,sage,ssex,sdept) values('08070202','张飞',30,'女',"IS");写法2：
# 插入数据，按照列的顺序插入数据 insert into student values('08070202','张飞',30,'女',"IS");
九、创建表其他
9.1自增长
自动增长的，每添加一条数据，自动在上一个记录数上加1
# auto_increment 自增长 每次增加1
create table role(
id int PRIMARY key auto_increment,
role_name varchar(20),
note varchar(100)
);
# 自增长 不需要我们去手动维护他，就是插入数据的时候，不需要关注他是什么值
# 自增长从1开始，只会增长，不会出现减少的情况
INSERT into role
values(null, '导演','导演都爱潜规则');
INSERT into role
values(null, '主演','主演都爱钱');
注意:自动增长的列必须是一个键(一般为主键)
9.2空与空串
NULL 和 NOT NULL
NULL:表示该列可以有空值数据
NOT NULL:表示该列不能有空值数据
空串 ''
空串与空一样吗？ 不一样
9.3默认值
create table a6(
a1 int,
a2 int default 66,
a3 varchar(32) default '哈哈'
);
十、更新数据
# update SET where
# 更新条件，如更新自己的个人信息，根据什么条件可以确定是自己
# 将ttt表中id列的值等于2的那个人的tnote列改为'joy'
UPDATE ttt
set tnote='joy'
where id=2
# 诸葛亮 年龄改为35岁，性别改为男，系改为IS
UPDATE student
SET SAGE=35, SSEX='男',sdept='IS'
WHERE SNO='08070203'
# 将所有人的年龄全部增加2岁
UPDATE student
SET sage=sage+2;
# 没有加where条件，意味着修改表中的所有行，对于修改语句，写的时候要慎重
update student
set ssex='M'
十一、删除
# drop database/table 数据库名/表名
# 删除表中的数据，delete
# 从ttt表中删除id为2的记录
delete from ttt where id=2
DELETE from student where sno='08070202'
# 不加任何条件，表示删除表中的所有数据
delete from student;
作业
| 列名 | 数据类型 | 是否可为空 | 默认值 | 描述 |
|---|---|---|---|---|
| ID | INT | 否 | 主键，自增长 | |
| SNO | Varchar(10) | 否 | 学号，唯一且非空 | |
| SNAME | varchar(20) | 否 | 姓名 | |
| SSEX | char(1) | 否 | ‘男’ | 性别 |
| SBIRTHDAY | DATE | 是 | 出生日期 | |
| SAGE | INT | 是 | 年龄 |
创建如图所示的hero表
向表中插入如下的数据
| ID | Sno | Sname | Ssex | Sage | SBIRTHDAY |
|---|---|---|---|---|---|
| 自增长的值 | 200215121 | 赵云 | 男 | 20 | 2000-09-15 |
| 自增长的值 | 200215122 | 不知火舞 | 女 | 19 | 1999-01-05 |
| 自增长的值 | 200215123 | 妲己 | 女 | 18 | 1990-08-07 |
- 所有人的年龄增加2岁
- 将赵云的姓名改成赵子龙
- 删除不知火舞
MYSQL
一、数据库编程基础
1.1实体与属性
•实体
现实世界中任何一个可以识别的对象
数据库中的一条记录对应java中的一个对象，数据库中的一张表对应java中的一个类
•属性
实体所具有的特性，一个实体可用若干属性来描述
•关系数据库中的表现
实体的实例是储存在表中的行，属性是储存在表中的列。
E-R图 实体关系图
1.2实体的关系
•键（key）
在实体属性中，用于区别实体集合中不同个体的某个属性或某几个属性的组合，称为关键字（键）。
•主键 （Primary key 非空唯一）
一个实体的实例上可以有多个不同的键存在，所有这些符合条件的键称为候选键，被指定某个作为主键。
•外键（Foreign key）
非本实体实例的键，但它是其他实体实例的键。又称为外关键字或外部码。
1.3关系的三类完整性约束
•实体完整性
每一表要求有一个主键(primary key)，不能取空值，也不能取相同的值
•引用完整性
外键（foreign key）的取值要么空值，要么去参考的那个列的取值
•域完整性和域约束
限制某个列的取值
check 约束
二、学生课程实例
2.1要创建的表
-
学生表：Student(Sno,Sname,Ssex,Sage,Sdept)
CREATE TABLE Student ( Sno VARCHAR(9) PRIMARY KEY, Sname VARCHAR(20) NOT NULL, Ssex VARCHAR(2), Sage INT, Sdept VARCHAR(20) ); -
课程表：Course(Cno,Cname,Cpno,Ccredit)// cpno先修课 外键
CREATE TABLE Course ( Cno INT PRIMARY KEY AUTO_INCREMENT, Cname VARCHAR(40) NOT NULL, Cpno INT , Ccredit INT, FOREIGN KEY (Cpno) REFERENCES Course(Cno) ); -
学生选课表：SC(Sno,Cno,Grade)//sno cno 外键，也是联合主键
CREATE TABLE SC (Sno VARCHAR(9), Cno INT, Grade INT, PRIMARY KEY (Sno,Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno), FOREIGN KEY (Cno) REFERENCES Course(Cno) );
2.2插入数据
学生表
| 学 号 Sno | 姓 名 Sname | 性 别 Ssex | 年 龄 Sage | 所 在 系 Sdept |
|---|---|---|---|---|
| 200215121 | 孙策 | 男 | 20 | CS |
| 200215122 | 妲己 | 女 | 19 | CS |
| 200215123 | 王昭君 | 女 | 18 | MA |
| 200515125 | 诸葛亮 | 男 | 19 | IS |
| 200215128 | 陈冬 | 男 | 18 | IS |
INSERT INTO Student (Sno,Sname,Ssex,Sdept,Sage)
VALUES ('200215121','孙策','M','CS',20);
# 一次插入多条数据
insert into student(sno,sname,ssex,sage,sdept)
values('200215122','妲己','F',19,'CS'),
('200215123','王昭君','F',18,'MA'),
('200215125','诸葛亮','M',19,'IS'),
('200215128','张飞','M',18,'IS')
课程表
| 课程号 Cno | 课程名 Cname | 先行课 Cpno | 学分 Ccredit |
|---|---|---|---|
| 1 | 数据库 | 5 | 4 |
| 2 | 数学 | 2 | |
| 3 | 信息系统 | 1 | 4 |
| 4 | 操作系统 | 6 | 3 |
| 5 | 数据结构 | 7 | 4 |
| 6 | 数据处理 | 2 | |
| 7 | JAVA语言 | 6 | 4 |
insert into course(cname,ccredit)
values( '数据库',4 ),
( '数学',2 ),
( '信息系统',4 ),
( '操作系统',3 ),
( '数据结构',4 ),
( '数据处理',2 ),
( 'JAVA语言',4 )
constraint 约束
成绩表
| 学 号 Sno | 课程号 Cno | 成绩 Grade |
|---|---|---|
| 200215121 | 1 | 92 |
| 200215121 | 2 | 85 |
| 200215121 | 3 | 88 |
| 200215122 | 2 | 90 |
| 200215122 | 3 | 80 |
insert into sc
values('200215121',1,92),
('200215121',2,85),
('200215121',3,88),
('200215122',2,90),
('200215122',3,80)
步骤
数据库sql文件导出
在对应的数据库上右击-》转储SQL文件-》结构与数据 导出到指定的位置
数据库sql文件导入
新建数据库-》右击-》运行SQL文件-》选择对应的sql文件-》开始 执行完成后F5刷新数据库，即可看到导入的表信息
2.3修改与删除
-
将学生200215121的年龄改为22岁
update student set sage=22 where sno='200215121' -
所有成绩都减少3分
update sc set grade=grade-3 -
删除学号为200215128的学生记录
delete from student where sno='200215128'主外键建立后注意事项：
- 当主表没有对应的记录时，不能将记录添加到子表（包含外键的表）
- 成绩表中不能出现学员信息表中不存在的学号
- 不能更改主表中的值导致子表中的记录孤立
- 把学员信息表中的学号改变了，学员成绩表中的学号也应当随之改变 （级联更新）
- 子表存在与主表对应的记录，不能从主表中删除该行
- 不能把有成绩的学员删除了
- 删除主标签，先删子表
- 先删学员成绩表，后删除学员信息表 （级联删除）
- 当主表没有对应的记录时，不能将记录添加到子表（包含外键的表）
2.4查询
2.4.1运算符
算术运算符
+ - * / %(取余)
# mysql中字符串的连接
select CONCAT('a','b','c','n')
select CONCAT('1','1')
%
5%3余数为2 ，取余就是结果就是2
赋值运算符
=（如在修改表记录时，set之后）
比较运算符
=(条件中) > < <>（不等于） >= <= !=（不等于） !>(不大于) !<
逻辑运算符
And与 java中使用&& 满足and两边的条件
Or 或 Java中使用|| 满足or一边的条件就行
Not 非 java中使用 ！ 非什么
其他运算符
数据范围
Between and Between 80 and 90->[80,90]
Not between and
确定集合
In (80,85,90)
Not in (80,85,90)只在这3个数中选择
字符匹配（模糊查找）
Like
Not like _单个字符（汉字） %0个或多个字符或汉字
空值（NULL）
Is Null 这里的null与空串的区别，请知晓
Is not null
2.4.2查询语法（规定了书写的顺序）
SELECT [ALL|DISTINCT] <目标列表达式>
[，<目标列表达式>] …
FROM <表名或视图名>[， <表名或视图名> ] …
[ WHERE <条件表达式> ]
[ GROUP BY <列名1> [ HAVING <条件表达式> ] ]
[ ORDER BY <列名2> [ ASC|DESC ] ]；
2.4.3查询练习
# 查询所有学生, *代表所有列，列的顺序即为表中的顺序
select * from student;
-
# 查询学号与姓名、年龄信息(指定列) select sno,sname,sage from student; -
# 全体学生的姓名及其出生年份 select sno,sname,sage,2018-sage from student; -
# 为选定的列指定别名Alias,显示记录时将显示出来 # 别名，用来显示的名字，小名 select sno '学号',sname '姓名',sage '年龄',2018-sage '出生年份' from student; # as 可加可不加 select sno as '学号',sname '姓名',sage '年龄',2018-sage '出生年份' from student; -
# 查询全体学生的姓名、出生年份和所在系， --要求用小写字母表示所有系名 select sname,2018-sage '出生年份',sdept from student; # select LOWER('aBCd') 全部小写 select UPPER("AbcdE"); 全部大写 # LOWER讲所有的字母变成小写，应用该列的所有行 select sname,2018-sage '出生年份',LOWER(sdept) from student; -
# 查询选修了课程的学号?有成绩就代表他选修了课程 # 哪些学生选修了课程，列出他们 # 去哪张表中查找数据 select * from sc; select DISTINCT sno from sc; # distinct 去除重复内容 -
-- 查询计算机科学系(CS)全体学生的名单 SELECT * from student where Sdept='CS' -
-- 查询所有年龄在20岁以下的学生姓名及其年龄 SELECT * from student WHERE SAGE<20 -
-- 查询年龄在20~23岁之间的学生的姓名、系别和年龄 SELECT * FROM student WHERE SAGE BETWEEN 20 AND 23 # [20,23] -- 20-23之间的整数 20 21 22 23 select * from student where sage in(20,21,22,23) -
-- 查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别 -- 条件都放在where的后面 -- in后面跟有穷，有限的集合 select sname,ssex from student where sdept in('IS','MA','CS'); -
-- like查找称为模糊查找 -- 查询姓诸葛的学生信息 %有特殊含义，表示0到多个任意字符 select * from student where sname like '诸葛%' -- 查询姓诸葛的学生信息，并且名字只有三个字的 _的特殊含义是1个任意字符 select * from student where sname like '诸葛_' -
-- 某些没有先修课的课程名 -- 条件判断是否为null，只能使用is null 或者is not null select cname from course where cpno is null -
-- 查询计算机系年龄在20岁以下的学生姓名 -- java && =》MYSQL中使用AND select sname from student WHERE Sdept='CS' AND SAGE<20 -
# 查询全体学生情况，查询结果按所在系的系名称升序排列 -- ORDER BY sdept asc 字典顺序 默认是升序排列 select * from student ORDER BY sdept -- 降序排列 select * from student ORDER BY sdept desc -
-- 查询全体学生情况，查询结果按所在系的系名称升序排列,同一系中的学生按年龄降序排列 select * from student ORDER BY sdept ASC, SAGE DESC -
-- 查询一共有多少个学生 COUNT，统计查询出来的行数 SELECT COUNT(*) FROM STUDENT; SELECT count(SNO) FROM STUDENT; -- 了解 SELECT count(1) FROM STUDENT;
```
-- 查询有先修课的课程一共有多少门
select count(*) from course;
select count(cno) from course;
-- count 统计不包括null值
select count(cpno) from course;
```
-
-- 查询一共有多少人选修了课程 # select DISTINCT sno from sc 去重 # 先查询 再统计 select count(DISTINCT sno) as '选课人数' from sc; -
-- 聚集函数sum 求和 avg 求平均数 max 最大值 min最小值 -- 计算2号课程的学生平均成绩 select avg(grade) from sc where cno=2 -
-- 显示2号课程的最高分 select max(grade) from sc where cno=2 -- 显示2号课程的最低分 select min(grade) from sc where cno=2 -
-- 求2号课程所有成绩的总分 select sum(grade) from sc where cno=2 -
-- 求各个课程号及其相应的选课人数 -- 先分组，在统计，统计的是组内的情况 -- GROUP BY之后的列才能出现在select语句的选择中，但是对于count统计没有这个限制 -- COUNT统计内部的列推荐为分组的列 select cno,count(cno) from sc GROUP BY cno
-
-- 按系查询男生和女生分别有多少人 select sdept, ssex, count(sno) from student GROUP BY sdept,ssex -
-- 查询选修了3门及以上课程的学生学号 (成绩表中有成绩，即表示选修了该课程) -- having是对分组后进一步进行筛选 -- group by之后进行进一步筛选必须使用having select sno from sc group by sno having count(sno)>=3;
````
-- 每个学生选修了多少门课程
select sno, count(sno) from sc group by sno;
````
-
# 执行顺序是，先进行where筛选，再分组，再进行having筛选 select sno from sc where sno='200215122' group by sno having count(sno)>=3;
HAVING短语与WHERE子句的区别：
1.WHERE从中选择满足条件的元组
2.HAVING短语作用于组，从中选择满足条件的组
-
limit
-- 分页 -- 每页显示三条记录 -- 第一页 n=1 SELECT * FROM student LIMIT 0,3; -- 第二页 n=2 SELECT * FROM student LIMIT 3,3; -- 第三页 n=3 SELECT * FROM student LIMIT 6,3; -- 第四页 n=4 SELECT * FROM student LIMIT 9,3; 起始位置 = (n-1) * 每页显示的数量; SELECT count(1) FROM student; 如果 总记录数 % 每页显示的数量 == 0 那么 总页数 = 总记录数 / 每页显示的数量 如果 总记录数 % 每页显示的数量 != 0 那么 总页数 = 总记录数 / 每页显示的数量 + 1 向上取整(12.1) = 13 向上取整(12.0) = 12 总页数 = ceil(总记录数 / 每页显示的数量); SELECT CEIL(count(1)/3) FROM student;
MYSQL
多表查询
-
查询选课学生及其选修课程的情况（等值连接）
# 查询出等值连接的所有列 select * from student,sc where student.sno=sc.sno # 查询出等值连接的指定列 select sname,ssex,sdept,cno,grade from student,sc where student.sno=sc.sno # 遇到重名的列时，一定要指定所属的表 # 否则会报如下错误 # Column 'sno' in field list is ambiguous，记住这个错误 select sc.sno,sname,ssex,sdept,cno,grade from student,sc where student.sno=sc.sno # 表的别名全权代表表,之后出现表的引用都要使用别名 select sc.sno,s.sname,ssex,sdept,cno,grade from student AS s,sc where s.sno=sc.sno -
查询每一门课的间接先修课（即先修课的先修课）
# 查询每一门课的间接先修课（即先修课的先修课） SELECT * from course c1, course c2 where c1.cpno=c2.cno SELECT c1.cno,c2.cpno from course c1, course c2 where c1.cpno=c2.cno # 查出间接先修课一定存在的 SELECT c1.cno,c2.cpno from course c1, course c2 where c1.cpno=c2.cno and c2.cpno is not null -
内连接inner join，等价于等值连接
查询选课学生及其选修课程的情况
select * from student inner JOIN sc on student.sno=sc.sno # 别名 select s.sno '学号',sname '姓名' from student s inner JOIN sc ss on s.sno=ss.sno -
外连接
保留舍弃的元组，在对应被连接表的分量部分用空值(NULL)代替,这种连接被叫做外连接
左外连接
查找每个学生的基本情况及选课情况
select * from student # table1 主表 LEFT JOIN sc # table2 从表 on student.sno=sc.sno # 显示的结果是主表与从表进行等值连接的结果 + 主表与从表没有连接的记录右外连接同理
-
UNION (ALL)
# UNION (ALL) 将查询的结果进行合并 加起来 # 两个查询的结果列数必须要一样 SELECT SNO,sname FROM student UNION ALL # UNION ALL不去重，保留重复的数据 UNION 去重 SELECT SNO,sname FROM student -
复合条件连接
查询选修2号课程且成绩在90分以上的所有学生
SELECT Student.Sno, Sname FROM Student, SC WHERE Student.Sno = SC.Sno AND SC.Cno= 2 AND SC.Grade > 90； -
查询选课学生的学号、姓名、选修的课程名及成绩
SELECT Student.Sno，Sname，Cname，Grade FROM Student，SC，Course WHERE Student.Sno = SC.Sno and SC.Cno = Course.Cno； -
嵌套查询
选修了课程号为2的学生的名字
select sname from student where sno in(select sno from sc where cno=2) -
在SELECT … FROM … WHERE 语句结构的WHERE,FROM,SELECT子句中都可嵌入一个SELECT语句块
其上层查询称为外层查询或父查询
其下层查询称为内层查询或子查询
SQL语言允许使用多重嵌套查询
嵌套查询的实现一般是从里到外，即先进行子查询，再把其结果用于父查询作为条件
查询与“诸葛亮”在同一个系学习的学生。
① 确定“诸葛亮”所在系名 select sdept from student where sname='诸葛亮'② 查找所有在IS系学习的学生 SELECT * from student where sdept= 'IS'；将第一步查询嵌入到第二步查询的条件中 SELECT * from student where sdept=(select sdept from student where sname='诸葛亮') AND SNAME != '诸葛亮' -
查询选修了课程名为“信息系统”的学生学号和姓名
SELECT Sno，Sname ③ 最后在Student关系中
FROM Student 取出Sno和Sname
WHERE Sno IN
(SELECT Sno ② 然后在SC关系中找出选
FROM SC 修了3号课程的学生学号
WHERE Cno IN
(SELECT Cno ① 首先在Course关系中找出
FROM Course “信息系统”的课程号，为3号
WHERE Cname= ‘信息系统’
)
);
- 找出每个学生超过他选修课程平均成绩的课程号。
select sno,cno
from sc x
where grade>(
select avg(grade)
from sc y
where x.sno=y.sno
)
-
查询其他系中比计算机科学某一学生年龄小的学生姓名和年龄
SELECT * FROM STUDENT WHERE SAGE< ANY(select sage from student where sdept='CS') AND SDEPT !='CS' # 另一种写法 SELECT * FROM STUDENT WHERE SAGE< (select MAX(sage) from student where sdept='CS') AND SDEPT !='CS'<all 小于最小 小于所有（10,20,5） >all 大于最大
<any 小于最大 小与任何一个 >any 大于最小
MYSQL常用函数
数学函数
ABS(VAL)绝对值
MOD(V1,V2) 取余
FLOOR(X) 取下整
CEIL(X) 取上整
ROUND(X) 四舍五入
select ABS(-20);
select MOD(9,4);
select FLOOR(1.2);
SELECT CEIL(1.2);
SELECT ROUND(1.4);
字符串函数
CONCAT(str1,str2,...):返回来自于参数连结的字符串
LENGTH(str):返回字符串str的长度
LEFT(str,len):返回字符串str的最左面len个字符。
RIGHT(str,len):返回字符串str的最右面len个字符。
SUBSTRING(str,pos):从字符串str的起始位置pos返回一个子串
SUBSTRING(str,pos,len):从字符串str的起始位置pos返回一个len长度的子串
REVERSE(str):返回颠倒字符顺序的字符串str。
SELECT CONCAT('HELLO','WORLD');
SELECT LENGTH("HELLO");
SELECT LEFT('HELLO',3); # 3代表长度，从左边开始取3个
SELECT RIGHT('HELLO',3);
SELECT SUBSTRING("HELLO WORLD",3); # 起始位置从1开始
SELECT SUBSTRING("HELLO WORLD",3, 5); # LLO W 5代表长度，
SELECT REVERSE("HELLO"); # OLLEH
日期函数
select CURTIME();
select CURDATE();
select NOW();
YEAR(date)
HOUR(time)
MINUTE(time)
SECOND(time):
DAYNAME(date):返回date的星期名字
DAYOFWEEK(date):返回日期date的星期索引(1=星期天，2=星期一, …7=星期六)。
select NOW();
select CURTIME();
select CURDATE();
select SYSDATE();
select YEAR('2012-09-09')
select MONTH('2012-10-09')
SELECT MINUTE(NOW())
select MINUTE('2012-10-09 12:12:12')
select dayname('2012-11-17 12:12:12')
select DAYOFWEEK('2011-11-11 11:11:11')
MYSQL
视图(了解) 虚表
实表：create table 创建出来的表
从用户角度来看，一个视图是从一个特定的角度来查看数据库中的数据 。从数据库系统内部来看，一个视图是由SELECT语句组成的查询定义的虚拟表 。从数据库系统内部来看，视图是由一张或多张表中的数据组成的，从数据库系统外部来看，视图就如同一张表 一样，对表能够进行的一般操作都可以应用于视图，例如查询，插入，修改，删除操作等。
从虚表中增删改数据，对实表的影响是无法预知的，所以虚表仅用查询，不允许增删改
2、视图的用途
- 筛选表中的行(可以将指定的列通过视图释放出来，也可以隐藏一些比较隐私的列)
- 防止未经许可的用户访问敏感数据(视图权限)
- 降低数据库的复杂程度（视图体现的是逻辑，也是业务）
- 将多个物理数据库抽象为一个逻辑数据库（业务）
使用视图可以给用户和开发人员带来很多好处。具体为：
A. 对最终用户的好处
（ 1 ）结果更容易理解
创建视图时，可以将列名改为有意义的名称，使用户更容易理解列所代表的内容。在视图中修改列名不会影响基表的列名。
（ 2 ）获得数据更容易
很多人对 SQL 不太了解，因此对他们来说创建对多个表的复杂查询很困难。可以通过创建视图来方便用户访问多个表中的数据。
B. 对开发人员的好处
（ 1 ）限制数据检索更容易
开发人员有时需要隐藏某些行或列中的信息。通过使用视图，用户可以灵活地访问他们需要的数据，同时保证同一个表或其他表中的其他数据的安全性。要实现这一目标，可以在创建视图时将要对用户保密的列排除在外。
（ 2 ）维护应用程序更方便
调试视图比调试查询更容易。跟踪视图中过程的各个步骤中的错误更为容易，这是因为所有的步骤都是视图的组成部分
从一个或者多个表或视图中导出的虚拟表，其结构和数据是建立在对表的查询基础上的。
理论上它可以像普通的物理表（实表）一样使用，例如增、删、改、查等，修改视图中的数据实际上是修改原始数据表。因为修改视图有许多限制，所以在实际开发中一般视图仅做查询使用。
create view v_detail
as
select * from table
实表的改变，会实时反映到虚表上
索引（了解） index
用途:我们对数据查询及处理速度已成为衡量应用系统成败的标准，而采用索引来加快数据处理速度通常是最普遍采用的优化方法。
索引是什么:数据库中的索引类似于一本书的目录，在一本书中使用目录可以快速找到你想要的信息，而不需要读完全书。在数据库中，数据库程序使用索引可以定向到表中的数据，而不必扫描整个表。书中的目录是一个字词以及各字词所在的页码列表，数据库中的索引是表中的值以及各值存储位置的列表。
索引的利弊：查询执行的大部分开销是I/O，使用索引提高性能的一个主要目标是避免全表扫描，因为全表扫描需要从磁盘上读取表的每一个数据页，如果有索引指向数据值，则查询只需要读少数次的磁盘就行啦。所以合理的使用索引能加速数据的查询。但是索引并不总是提高系统的性能，带索引的表需要在数据库中占用更多的存储空间，同样用来增删数据的命令运行时间以及维护索引所需的处理时间会更长。所以我们要合理使用索引，及时更新去除次优索引。
系统会为primary key和unique自动创建索引
是否适合创建索引的列：
- 该列数据是否经常变化，如果经常变化，不适合创建索引
- 查询使用频繁的列，适合创建索引
拓展阅读
创建索引语法
CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
# 对course表的cname列创建索引，索引的名字为index_cname
create index index_cname
on course(cname);
# 从course表中将索引index_cname删除
drop index index_cname on course;
流程控制函数
CASE-WHEN-THEN-ELSE-END
CASE
WHEN 条件1 THEN 结果1
[WHEN 条件2 THEN 结果2 ...]
[ELSE 最终结果] # 如果没有匹配的结果值，则返回结果为ELSE后的结果，如果没有ELSE部分，则返回值为 NULL
END
查询学生的成绩信息，学号，姓名，课程名，成绩，以及成绩的等级
90-100优秀 75-89 良好 60-75及格 60分以下不及格
select s.sno as '学号',sname as '姓名',cname '课程名',grade '成绩',
case
when grade>=90 then '优秀'
when grade>=75 then '良好'
when grade>=60 then '及格'
ELSE '不及格'
END AS '等级' # END 表示case语句的结束 '等级'列的别名
from student s,sc, course c
where s.sno=sc.sno and c.cno=sc.cno
系名练习
查询学生信息，如果系名为CS，则显示计算机科学，如果系名为IS，则显示信息系统，如果系名为MA，则显示数学
IF
IF(条件,条件为真时执行,条件为假时执行); #类似于java中的 boolean ? A : B 三目运算符
输出学生信息，如果性别为F则输出女，如果为M，则输出男
select sno 学号,sname 姓名,if(ssex='M','男','女') as 性别,sdept 所在系
from student
查询学生信息，如果系名为CS，则显示计算机科学，如果系名为IS，则显示信息系统，如果系名为MA，则显示数学
select sno 学号,sname 姓名,if(ssex='M','男','女') as 性别,
if(sdept='CS','计算机科学',if(sdept='MA','数学','信息系统')) 所在系
from student
IFNULL
IFNULL(EXP1,EXP2)
EXP1如果为空，则显示EXP2的内容，如果不为空，就显示其本身内容
查询课程的先修课，如果先修课为null，则显示无
select cno 课程号,cname 课程名,IFNULL(CPNO,'无') 先修课,ccredit 学分
from course
存储过程
存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。
语法
CREATE PROCEDURE 存储过程名 (参数列表)
BEGIN
-- 代码
END
参数
存储过程根据需要可能会有输入、输出、输入输出参数，多个参数用","分割开。共有三种参数类型,IN,OUT,INOUT:
IN参数的值必须在调用存储过程时指定，在存储过程中可以修改该参数的值，但不能被返回
OUT该值可在存储过程内部被改变，并返回
INOUT调用时指定，并且可被改变和返回
调用
调用存储过程: CALL 存储过程名(参数列表);
删除
删除存储过程: DROP PROCEDURE 存储过程名;
实例1
输入参数有多个
定义
DROP PROCEDURE IF EXISTS p1;# 如果存储过程p1存在则删除
# 创建存储过程
CREATE PROCEDURE p1(IN a INT, IN b VARCHAR(20))
BEGIN
select a;
select b;
set a=20;
set b='hello world';
select a;
select b;
END;
调用1
call p1(10,'abcd')
调用2
set @x=12;
set @y='abc';
call p1(@x,@y);
select @x;
select @y;
@@:表示系统变量
@:表示自定义变量
实例2
带有传出参数
DROP PROCEDURE IF EXISTS p2;
CREATE PROCEDURE p2(out a INT, out b VARCHAR(20))
BEGIN
select a; # 会输出null，参数值传不进来
select b; # 会输出null，参数值传不进来
set a=20;
set b='hello world';
select a;
select b;
END;
set @m=12;
set @n='abc';
call p2(@m,@n);
select @m; # 显示存储过程中设置的值 20
select @n; # 显示存储过程中设置的值 hello world
实例3
输入输出参数
DROP PROCEDURE IF EXISTS p3;
CREATE PROCEDURE p3(INOUT a INT, INOUT b VARCHAR(20))
BEGIN
select a;
select b;
set a=20;
set b='hello world';
select a;
select b;
END;
调用
set @m=12;
set @n='abc';
CALL p3(@m,@n);
实例4
变量声明
DROP PROCEDURE IF EXISTS p4;
CREATE PROCEDURE p4(INOUT str VARCHAR(50))
BEGIN
# DECLARE 变量名[,...] 变量类型 [DEFAULT 默认值]
# 局部变量声明必须在最上面,并且中间还不能有任何其他代码
DECLARE a VARCHAR(32);
DECLARE b VARCHAR(32);
DECLARE c VARCHAR(32) DEFAULT 'hello';
select sno,sname into a,b from student where sno='200215121';
set str=CONCAT(a,b,c);
END;
调用
set @s='';
CALL p4(@s);
select @s;
实例5
IF-THEN--ELSEIF-THEN...--ELSE-END IF
DROP PROCEDURE IF EXISTS p5;
CREATE PROCEDURE p5(IN stu VARCHAR(10), IN cou INT)
BEGIN
DECLARE DEG INT;
SELECT GRADE INTO DEG FROM sc WHERE SNO=stu and cno=cou;
IF DEG>90
THEN SELECT '优秀';
ELSEIF DEG>85
THEN SELECT '良好';
ELSEIF DEG>60
THEN SELECT '及格';
ELSE select '不及格';
END IF;
END;
调用
CALL p5('200215121',1)
实例6
[循环名:] LOOP
要循环的代码
END LOOP [循环名]
LEAVE 循环名:这个语句被用来退出任何被标注的流程控制构造 (跳出某个循环)
ITERATE 循环名:跳出某个循环,进入下一次循环
创建表，通过存储过程向表中插入100条数据
create table t1(
id int PRIMARY key auto_increment
)
存储过程
create PROCEDURE p6()
BEGIN
DECLARE i INT DEFAULT 1;
sta:LOOP
IF i>100
THEN LEAVE sta;
END IF;
INSERT into t1 VALUES(null);
SET i = i + 1;
END LOOP sta;
end;
调用
call p6();
# 清空表中的数据：删除表，重建表，可知id自增长序列，重新开始
TRUNCATE TABLE t1;
# 清空表中的数据：将数据删除，可知id自增长序列，一直在增加
delete from t1;
TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少，但 TRUNCATE无事务且不触发 trigger，有可能造成事故，故不建议在开发代码中使用此语句。 说明：TRUNCATE TABLE 在功能上与不带 WHERE 子句的 DELETE 语句相同。
拓展学习
存储过程的游标cursor，遍历表中的每一行数据
删除数据
DELETE
DELETE FROM 表名 [where condition]
condition为筛选条件，如不指定则删除该表的所有列数据
TRUNCATE
TRUNCATE [TABLE] table_name
用于完全清空表数据,但表结构、索引、约束等不变
两者比较
- 都能删除数据、不删除表结构，但TRUNCATE速度更快
- 使用TRUNCATE TABLE重新设置AUTO_INCREMENT计数器
- 使用TRUNCATE TABLE不会对事务有影响
结构化查询语句分类
NULL
理解为“没有值”或“未知值”
不要用NULL进行算术运算，结果仍为NULL
MySQL中，0或NULL都意味着为假，1为真
是否为NULL判断使用IS NULL或者 IS NOT NULL
数据表的类型
MyISAM、InnoDB 、HEAP、BOB、CSV等
适用场合：
• 使用MyISAM: 节约空间及相应速度
• 使用InnoDB:安全性，事务处理及多用户操作数据表
MySQL数据表以文件方式存放在磁盘中，包括表文件、数据文件以及数据库的选项文件
位置：MySQL安装目录\data下存放数据表。目录名对应数据库名，该目录下文件名对应数据表
了解: InnoDB类型数据表只有一个*.frm文件，以及上一级目录的 ibdata1 文件
MyISAM类型数据表对应三个文件：
- *.frm -- 表结构定义文件
- *.MYD -- 数据文件
- *.MYI -- 索引文件
CREATE TABLE 表名(
#省略一些代码
) ENGINE = MyISAM
CREATE TABLE 表名(
#省略一些代码
) ENGINE = InnoDB
数据库编码
可为数据库、数据表、数据列设定不同的字符集
CREATE TABLE 表名(
#省略一些代码
)CHARSET = utf8;
如无设定，则根据MySQL数据库配置文件my.ini，（Windows系统）中的参数设定如：character-set-sever=utf8
修改表
修改表名
ALTER TABLE 旧表名 RENAME AS 新表名
添加字段
ALTER TABLE 表名 ADD 字段名 列类型 [ 属性 ]
数据库多创建几个（2-3个）预留字段 yubei1 yubei2
修改字段
ALTER TABLE 表名 MODIFY 字段名 列类型 [ 属性 ]
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 列类型 [ 属性 ]
删除字段
ALTER TABLE 表名 DROP 字段名
设置可远程连接
5.7之前的版本中，user表中密码字段的字段名是 password，5.7版本改为了 authentication_string，查询root用户信息
select host,user,authentication_string from user;
update user set host='%' where user='root';
*.*->数据库.表名
grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
flush privileges; #刷新修改后的信息，使修改生效
关闭防火墙进行测试
触发器
触发器是一段与某个表相关的sql语句，会在某个时间点，满足某个动作(增删改)后自动触发执行。
支持的时间点：时间发生前before和发生后after
支持的事件：
- update 可用OLD访问旧数据，NEW访问新数据， 旧值-》新值
- delete 可用OLD访问新数据 旧值
- insert 可用NEW访问新数据 新值
- 可以将NEW和OLD看做一个对象，其中封装了这列数据的所有字段
delimiter //
create trigger trigger_update_order
after update
on `order` for each row
begin
update good set storage=storage + old.gnum - new.gnum where id=new.gid;
end//
delimiter ;
insert into `order` values(null, 1,10);
# 更新前 old 1 10 更新后 new 1 9
update `order` set gnum=9 where id=1
数据库备份
查阅资料了解
不得使用外键与级联，一切外键概念必须在应用层解决。 说明：以学生和成绩的关系为例，学生表中的 student_id 是主键，那么成绩表中的 student_id 则为外 键。如果更新学生表中的 student_id，同时触发成绩表中的 student_id 更新，即为级联更新。外键与级 联更新适用于单机低并发，不适合分布式、高并发集群；级联更新是强阻塞，存在数据库更新风暴的风 险；外键影响数据库的插入速度。
逻辑数据库设计—规范化逻辑模型
范式（ Normal form，简称NF ）
表示关系模式的级别，是衡量关系模式规范化程度的标准，达到范式的关系才是规范化的。
各种范式之间的联系是：5NF ∈ 4NF ∈ 3NF ∈ 2NF ∈ 1NF
规范化:一个低级范式的关系模式，通过关系模式的投影分解，可以转换为若干个高一级范式的关系模式的集合的过程。实际的关系数据库一般使用3NF以上的关系。
规范化的步骤
将数据库分为两个或更多个表；
定义表之间的关系。
规范化数据库的任务
尽可能减少冗余
减少数据的不一致性
加快数据修改（插入、更新的删除）
第一范式（ R∈1NF ）
关系模式R中的所有属性都是不可再分的数据项。
表必须符合的条件
表必须是二维的（用行和列表示）
每个数据库单元只包含一个值
每列必须具有单独的含义
学生表是1NF，职工表不是
第二范式（R∈2NF ）
规范化逻辑模型 —转换为第二范式数据模型
关系R∈1NF，且它的每一非主属性都完全依赖于主键。
它的规则是要求数据表里的所有非主属性都要和该数据表的主键有完全依赖关系；如果有哪些非主属性只和主键的一部份有关的话，它就不符合第二范式。同时可以得出：如果一个数据表的主键只有单一一个字段的话，它就一定符合第二范式(前提是该数据表符合第一范式)
多值主键的情况
course student sc(cno,sno)
sc:cno sno grade cname
student sno
原则
消除实体中的冗余信息
将只依赖于一部分多值主键的属性移到另一个表中
使存储的信息更一致
解决方法
拆分表
示例
考虑一个小学的教务管理系统，学生上课指定一个老师，一本教材，一个教室，一个时间，大家都上课去吧，没有问题。那么数据库怎么设计？
学生上课表
学生 课程 老师 老师职称 教材 教室 上课时间
小明 一年级语文（上） 大宝 副教授 《小学语文1》 101 14：30
一个学生上一门课，一定在特定某个教室。所以有（学生，课程）－>教室
一个学生上一门课，一定是特定某个老师教。所以有（学生，课程）－>老师
一个学生上一门课，他老师的职称可以确定。所以有（学生，课程）－>老师职称
一个学生上一门课，一定是特定某个教材。所以有（学生，课程）－>教材
一个学生上一门课，一定在特定时间。所以有（学生，课程）－>上课时间
因此（学生，课程）是一个码/键
然而，一个课程，一定指定了某个教材，一年级语文肯定用的是《小学语文1》，那么就有课程－>教材
（学生，课程）是个码，课程却决定了教材，这就叫做不完全依赖，或者说部分依赖。出现这样的情况，就不满足第二范式！
学生上课表新
课程 教材
学生 课程 老师 老师职称 教室 上课时间
第三范式（ R∈3NF ）
规范化逻辑模型 —创建第三范式数据模型
若R∈2NF，且它的每一非主属性都不传递依赖于关键字即消除传递依赖。
表中有可能存在某些数据元素依赖于其他非关键字数据元素的现象,必须消除
原则
除去所有不依赖某个键值而存在的列
将不直接与实体联系的任何数据都移到另一个表中
避免信息的更新异常和删除异常
确保不含冗余信息
操作步骤
将数据模型规范化为第二范式；
确定不依赖键值而存在的所有属性；
将这些独立的属性都移到各个单独的表中，并为之确定主键。且将这些主键作为外键连接到父实体中。
示例
上面的“学生上课表新”符合2NF，但是它有传递依赖！
问题就出在“老师”和“老师职称”这里。一个老师一定能确定一个老师职称。
还有什么问题吗？
课程 教材
学生 课程 老师 老师职称 教室 上课时间
- 老师升级了，变教授了，要改数据库，表中有N条，改了N次……（修改异常）
- 没人选这个老师的课了，老师的职称也没了记录……（删除异常）
- 新来一个老师，还没分配教什么课，他的职称记到哪？……（插入异常
那应该怎么解决呢？和上面一样，投影分解：
老师 老师职称
课程 教材
学生 课程 老师 教室 上课时间
数据库设计
实体关系
1.一对一
老板
1 马云 ...
公司
1 阿里巴巴 1（老板表的主键）
怎样设计：公司表中存放老板的主键作为外键，或者反之
2.一对多
一个家族->多个孩子
家族表 1 张氏 ...
孩子表 A 1
在多头一方存放一头数据的主键作为外键
3.多对多
学生与课程-》成绩表
必须引入第三张表作为中间表，建立他们之间的联系
多头和多头的主键作为外键存入第三张表中
浙公网安备 33010602011771号