数据库简介
1、什么是数据库？
存储数据的仓库，本质是一个文件系统，按照特定的格式将文件存储起来（实现持久式存储），使用户可以方便的对数据进行增删改查。
2、数据库的发展
层次数据库 ->网状数据库->关系型数据库->面向对象数据库->分布式数据库
3、常见的关系型数据库
MySQL:免费的开源的数据库，中小型数据库首选 从6.x版本开始收费
Oracle：收费的大型数据库，安全性高
DB2：IBM公司的产品，收费的数据库，性能极高，运行成本高，常用在银行、金融系统
SQLServer ：微软收费的中型数据库 性能一般
SQLite:嵌入式超小型数据库，用在手机端
4、MySQL安装与卸载可以查百度
安装上MySQL，相当于安了一个服务器，对外提供服务
5、连接MySQL数据库
两种方式：使用命令窗口或使用可视化的客户端
客户端：负责向服务器发送控制命令 负责展示处理结果
服务器端：负责保存数据 负责管理和维护数据安全 负责执行客户端传来的控制命令
6、命令窗口连接MySQL
mysql -u用户名 -p密码
mysql -h主机名 -u用户名 -p密码 eg:mysql -h127.0.0.1 -uroot -p1234
7、可视化工具
SQLYog
Navicat分为小黄和小绿
进入工具后输入地址，用户名，密码和端口就可以连接
8、DBMS数据库管理系统
所谓安装数据库服务器，只是在机器上装了一个数据库管理程序，这个管理程序可以管理多个数据库，一般开发人员会针对每一个应用创建一个数据库。
为保存应用中实体的数据，一般会在数据库创建多个表，以保存程序中实体的数据。
SQL语句
1、数据库定义语言：DDL(Data define language)用来操作数据库和表，关键字create alter drop 结构上的操作
数据操作语言：DML(Manipulation)用来操作表中的数据 ，关键字insert,delete,updata 不涉及结构的变化，只改变数据
数据查询语言：DQL (Query)用来查询表中的记录 ，关键字select from where 结构和数据都不会 发生改变
数据控制语言：DCL（Control）用来定义数据库的访问权限和安全级别及创建用户
2、SQL语句可以单行或多行书写
可使用空格和缩进来增强语句的可读性
数据库的SQL语句不区分大小写
同样可以使用/**/的方式完成多行注释（MySQL还可以使用#作为单行注释）
3、DDL操作数据库
create database 数据库名; // 创建一个新的数据库
create database 数据库名 character set 字符集;
use 数据库名; //切换数据库
select database(); //查看当前正在使用哪个数据库
show databases; //查看服务器中有哪些数据库
show create database 数据库名; //查看某个数据库定义的信息
drop database 数据库名; //删除数据库
alter database 数据库名 character set gbk; //修改数据库编码方式
4、DDL操作表
create table 表名(
字段名1 类型 约束，字段名2 类型 长度...
)
MySQL中常使用的数据类型
int double varchar字符串 date日期(yyyy-MM-dd 没有时分秒)
***注意：datetime和timestamp的区别
datetime最大表示时间 9999-12-31 23：59：59
timestamp最大表示时间 2037-12-31 23：59：59
当数据表是datetime类型的时候，默认值null
如果是时间戳，默认为当前时间。
show tables; //查看当前数据库中的所有表名
desc 表名;describe 表名 //查看某张数据表的结构
drop table 表名; //删除表
alter table 表名 关键字;
1.alter table 表名 add 列名 类型（长度） [约束]; //添加列
2.alter table 表名 modify 列名 类型（长度） [约束]; //只能修改列的类型长度和约束，不能改列名
3.alter table 表名 change 旧列名 新列名 类型（长度） [约束]; //可以修改列名
4.alter table 表名 drop 列名; //删除列
5.rename table 表名 to 新表名; //修改表名
6.alter table 表名 character set 字符集; //修改字符集
7.alter table 表名 character set 字符集 collate utf8_general_ci; //修改排序方式
5、DML操作
1.insert into 表名 (字段名1，字段名2) values(“值1,”值2”);
Insert into 表名 values(“值”,....) 注意：值必须与字段类型顺序相同，除了数字都需要引号引起来
可一次性插入多条INSERT INTO student VALUES(111，222),(333，444),(555，666);
2.update 表名 set 字段 =值 where 条件;
列名的类型与修改的值要一致.
修改值得时候不能超过最大长度.
除了数值类型外，其它的字段类型的值必须使用引号引起
3.delete from 表名 where 条件; //删除符合条件的记录
6、DQL查询数据
1.select * from 表名; //查询所有记录 效率低 一般使用 select 所有字段的名字 from 表名;
2.select * from 表名 where 条件; //查询符合条件的记录
3.select name as 名字 from 表名 as a; //别名 as可以省
4.select distinct 列名 from 表名; //去掉重复值
5.select num+10 from 表名; //数字+10查询
6.where后的条件
比较运算符 > < <= >= <> !=
between ...and... 某一区间的值，包含头尾
in(100,200) 替代...or...or...100或200
like 模糊查询 %代表0个或多个字符 _代表一个字符
is null判断空
and or not 逻辑运算符
7、排序
select * from 表名 order by 排序字段 ASC|DESC; //ASC是升序 DESC是降序
select * from 表名 order by 排序字段 ASC，排序字段 DESC; //当第一个字段的值相等时按第二个字段排序
8、聚合函数
count(字段)统计总记录数
sum(字段)计算指定列的数值和，非数字为0
max(字段)计算指定列的最大值
min(字段)最小值
avg(字段)平均值
9、分组
select * from 表名 group by 分组字段名 having 条件 order by 字段;
Group by 字段 字段值相同的数据会划分成一组
Having 条件 对每一组分别进行一次运算
Select 字段 把每组中第一条数据取出来。合并成一张新伪表展示这个新伪表上的部分字段
order by 字段 对新伪表进行最后排序
10、数据的备份和还原
在可视化工具中右击数据库，可以看到备份导出字样，也有导入字样
SQL约束
1、主键约束
primary key 唯一且不能为空，每个表只有一个主键 三种方式
create table t(
id int primary key,
name varchar(20)
)
create table t2(
id int,
name varchar(10),
primary key(id)
)
alter table t3 add primary key(id);
2、删除主键约束
alter table 表名 drop primary key;
3、非空约束
not null 不接受空值
create table t4(
id int,
name varchar(10) not null
)
alter table t4 change name name varchar(10) not null;
4、唯一约束
Unique 约束数据库表中的每条记录不重复 会忽略null值
可以有多个Unique约束
添加方式类似于主键
5、外键约束
Foreign key 用于多表查询
6、自动增长
auto_increament 可以为唯一约束或主键约束
多表查询
1、外键特点 作用：进行多表数据关联，维持数据的完整性
从表外键指向主表主键
从表外键的数据类型和长度，必须和主表主键数据类型和长度 一致
格式 ：
alter table 从表 add [constraint 外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);
2、多表关系
1.一对多 例如：省和市
在多的一方添加外键，关联到一的那方主键
eg: create table province(
pid int primary key auto_increament,
name varchar(20)
);
给citys添加外键，关联到province
create table citys(
cid int primary key auto_increament,
name varchar(20),
pid int,
foreign key (pid) references province(pid)
);
也可以将建表的最后一句写在外面
alter table citys add foreign key (pid) references province(pid);
一对多关系图
2.多对多 例如：老师和学生
增加一个中间表，分别用两列外键对应两个表的主键
eg:create table teachers(
tid int PRIMARY KEY AUTO_INCREMENT,
name varchar(20)
);
CREATE TABLE students(
sid INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(20)
);
创建中间表
CREATE TABLE teacher_student(
tid INT,
sid INT,
FOREIGN KEY(tid) REFERENCES teachers(tid),
FOREIGN KEY(sid) REFERENCES students(sid)
);
多对多关系图
3.一对一 例如：夫妻
将两个表的主键关连起来
eg: create table husband(
hid int primary key auto_increament,
name varchar(20)
);
create table wife(
wid int primary key auto_increament,
name varchar(20),
foreign key (wid) references husband(hid)
)
一对一关系图
3、一对多查询
多表查询时，不在按照每个表进行查询，而是使用连接查询展示两个表的内容
连接的方式有内连接和外连接
1.内连接 隐式内连接 更常用
select p.name,c.name from province p,citys c where p.pid=c.pid and p.name='北京';
2. 内连接 显式内连接 inner join on
select * from province p inner join citys c on p.pid=c.pid and p.name='北京';
3.外连接 左外连接 left join on
左外连接，以左边的表数据为主，如果右表中没数据使用null填充
4.右外连接 right join on
右外连接，以右边的表数据为主，如果左表中没数据使用null填充
以一张表为基准，查找满足条优的记录，如果没有满足此条件，基准表全部显示，关联表使用null填充
4、多对多查询
select * from teachers t,students s,teacher_student ts where s.name='小明' and s.sid=sc.sid and sc.tid=t.tid;
5、一对一查询
select * from husband h,wife w where h.name='小明' and h.hid=w.wid;
6、注意一定要避免笛卡尔积
笛卡尔积 a={0,1} b={1,2,3} a*b={(0,1),(0,2),(0,3),(1,1),(1,2),(1,3)}
要加上查询的关键条件 eg:h.name='小明'
7、自连接
SELECT e1.name,e1.salary FROM emp e1,emp e2 WHERE e1.salary>e2.salary AND e2.name='小明';
8、子查询
将上一条SELECT语句结果作为另一条SELECT语法一部分（查询条件，查询结果，表） SELECT 查询字段 FROM 表WHERE 查询条件;
子查询结果只要是 单列 ，肯定在 WHERE 后面作为 条件
SELECT 查询字段 FROM 表 WHERE 字段=（子查询）;
子查询结果只要是 多列 ，肯定在 FROM 后面作为 表
SELECT 查询字段 FROM （子查询） 表别名 WHERE 条件
- 子查询 CREATE TABLE emp( empno INT, ename VARCHAR(50), job VARCHAR(50), mgr INT, hiredate DATE, sal DECIMAL(7,2), comm DECIMAL(7,2), deptno INT ) ; INSERT INTO emp VALUES(7369,'SMITH','CLERK',7902,'1980-12-17',800,NULL,20); INSERT INTO emp VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30); INSERT INTO emp VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30); INSERT INTO emp VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20); INSERT INTO emp VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30); INSERT INTO emp VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30); INSERT INTO emp VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10); INSERT INTO emp VALUES(7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,NULL,20); INSERT INTO emp VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10); INSERT INTO emp VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30); INSERT INTO emp VALUES(7876,'ADAMS','CLERK',7788,'1987-05-23',1100,NULL,20); INSERT INTO emp VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30); INSERT INTO emp VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20); INSERT INTO emp VALUES(7934,'MILLER','CLERK',7782,'1982-01-23',1300,NULL,10); CREATE TABLE dept( deptno INT, dname VARCHAR(14), loc VARCHAR(13) ); INSERT INTO dept VALUES(10, 'ACCOUNTING', 'NEW YORK'); INSERT INTO dept VALUES(20, 'RESEARCH', 'DALLAS'); INSERT INTO dept VALUES(30, 'SALES', 'CHICAGO'); INSERT INTO dept VALUES(40, 'OPERATIONS', 'BOSTON'); ```sql -- 单行子查询(> < >= <= = <>) -- 查询出高于10号部门的平均工资的员工信息 SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno='10') ; -- 多行子查询(in not in any all) -- 查询出比10号部门任何员工薪资高的员工信息 SELECT * FROM emp WHERE sal>ALL(SELECT sal FROM emp WHERE deptno='10'); -- 多列子查询(实际使用较少) -- 和10号部门同名同工作的员工信息 SELECT * FROM emp WHERE ename,job IN (SELECT ename,job FROM emp WHERE deptno='10') AND deptno!='10'; -- select 后面接子查询 -- 获取员工的名字和部门的名字 SELECT p.ename,d.dname FROM emp p,dept d WHERE p.deptno=d.deptno;; -- from 后面接子查询 -- 查询emp表中经理信息 SELECT ename FROM (SELECT * FROM emp WHERE job='manager') r WHERE r.deptno='10'; -- where 后面接子查询 -- 薪资高于10号部门平均工资的所有员工信息 SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp WHERE deptno='10'); -- group by 后面接子查询 -- 有哪些部门的平均工资高于30号部门的平均工资 SELECT deptno, AVG(sal) AS bumen FROM emp GROUP BY deptno HAVING bumen > (SELECT AVG(sal) FROM emp WHERE deptno=30); -- 工资>JONES工资 SELECT * FROM emp WHERE sal>(SELECT sal FROM emp WHERE ename='jones'); -- 查询与SCOTT同一个部门的员工 SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='scott'); -- 工资高于30号部门所有人的员工信息 SELECT * FROM emp WHERE sal>(SELECT MAX(sal) FROM emp WHERE deptno='30'); -- 查询工作和工资与MARTIN完全相同的员工信息 SELECT * FROM emp WHERE job=(SELECT job FROM emp WHERE ename='martin') AND sal=(SELECT sal FROM emp WHERE ename='martin'); -- 有两个以上直接下属的员工信息 SELECT * FROM emp WHERE empno IN (SELECT mgr FROM emp GROUP BY mgr HAVING COUNT(*)>=2); -- 查询员工编号为7788的员工名称,员工工资,部门名称,部门地址 SELECT e.*,d.dname,d.loc FROM emp e,dept d WHERE e.empno='7788' AND e.deptno=d.deptno; ``` - SQL查询的综合案例 1. 查询出高于本部门平均工资的员工信息 SELECT * FROM emp e,(SELECT AVG(sal) a,deptno FROM emp GROUP BY deptno) t WHERE e.sal>t.a AND e.deptno=t.deptno; 2. 列出达拉斯加工作的人中,比纽约平均工资高的人 SELECT e.ename,e.sal,d.loc FROM emp e,dept d WHERE sal>(SELECT AVG(sal) FROM emp e,dept d WHERE d.loc='new york' AND d.deptno=e.deptno) AND d.loc='dallas' AND d.deptno=e.deptno; 3. 查询7369员工编号,姓名,经理编号和经理姓名 SELECT b.empno,b.ename,e.empno,e.ename FROM emp e,(SELECT empno,ename,deptno FROM emp WHERE empno='7369') b WHERE e.deptno=b.deptno AND e.job = 'manager'; 4. 查询出各个部门薪水最高的员工所有信息 SELECT e.* FROM emp e,(SELECT MAX(sal) m,deptno FROM emp GROUP BY deptno) b WHERE e.deptno=b.deptno AND e.sal=b.m; #### 面试题 ```sql CREATE TABLE test( NAME CHAR(20), kecheng CHAR(20), fenshu CHAR(20) ); INSERT INTO test VALUES('张三','语文',81), ('张三','数学',75), ('李四','语文',76), ('李四','数学',90), ('王五','语文',81), ('王五','数学',82); --请用一条Sql语句查处分数大于80的学生 ```SELECT DISTINCT NAME FROM test WHERE fenshu>80;