1.数据库基础概念
1、数据库(Database)是按照数据结构来组织、存储和管理数据的仓库。
2、我们使用关系型数据库管理系统(RDBMS)来存储和管理大数据量。所谓的关系型数据库,是建立在关系模型基础上的数据库,借助于集合代数等数学概念和方法来处理数据库中的数据。
3、RDBMS的一些术语:
- 数据库: 数据库是一些关联表的集合。
- 数据表: 表是数据的矩阵。在一个数据库中的表看起来像一个简单的电子表格。
- 列: 一列(数据元素) 包含了相同类型的数据, 例如邮政编码的数据。(字段 column)
- 行:一行(=元组,或记录)是一组相关的数据,例如一条用户订阅的数据。(数据/记录 data)
- 冗余:存储两倍数据,冗余降低了性能,但提高了数据的安全性。
- 主键:主键是唯一的。一个数据表中只能包含一个主键。你可以使用主键来查询数据。
- 外键:外键用于关联两个表。
- 复合键:复合键(组合键)将多个列作为一个索引键,一般用于复合索引。
- 索引:使用索引可快速访问数据库表中的特定信息。索引是对数据库表中一列或多列的值进行排序的一种结构。类似于书籍的目录。
- 参照完整性: 参照的完整性要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件,目的是保证数据的一致性
- 表头(header): 每一列的名称;
- 列(col): 具有相同数据类型的数据的集合;
- 行(row): 每一行用来描述某条记录的具体信息;
- 值(value): 行的具体信息, 每个值必须与该列的数据类型相同;
- 键(key): 键的值在当前列中具有唯一性。
2.安装登陆
详见:添加链接描述
登陆:
3.导入操作数据
Step 1: 登陆mysql管理系统
mysql -uroot -p密码
Step 2: 查看有哪些数据库
show databases;
Step 3: 创建属于自己的数据库
create database 数据库名;
Step 4: 使用创建的数据库的数据
use 数据库名;
Step 5: 查看当前数据库中的表
show tables; / show tables form 数据库;
Step 6:初始化数据(如果为空)
mysql > source sql路径名
注意:当文件的扩展名是.sql,并且该文件中编写了大量的sql语句,我们称这样的文件为sql脚本。直接使用source命令可以执行sql脚本。sql脚本中的数据量太大的时候,无法打开,请使用source命令完成初始化。
1.删除数据库
drop database 数据库名
2.查看表结构:
desc 表;
3.查看表内容:
select * from 表;
4. 常用命令
1.创建数据库
create database 数据库名字;
2.删除数据库
drop database 数据库名字;
3.查看当前使用的数据库
select database();
4.查看当前使用的数据库的名称
select version();
5.结束一条语句
\c
6.退出mysql
exit
7.4.查看建表语句
show create table 表名
5. DQL
5.1简单查询
注意:
- 任何一条sql都由“;”结尾
- sql语句不区分大小写
查询一:
select 字段名1,字段名2...,字段名n from 表名;
查询二:对字段做操作
select name, salary*12 from emp; 对工资字段*12
查询三:修改操作后的字段名(as可去掉)
select name, salary*12 as yearsalary from emp; //对工资字段*12
select ename,sal*12 as '年薪' from emp; //字符串用单引号
查询四:
select * from emp; //查询全字段
查询五:去重:distinct //distinct 只能出现在所有筛选字段的最前面,在前为联合去重
select distinct salary from emp;
5.2条件查询
例:
1、select ename from emp where deptno = ‘20’;
2、相等:
select ename from emp where sal >= 1100 and sal <=3000;
select ename from emp where sal between 1100 and 3000; //闭区间 前小后大 ,除了使用在数字还可以使用在字符串
3、and优先级大于or,一般我们加();
4、select ename from emp where sal in(1000,5000);
这里是1000 or 5000,不是区间。
5、模糊查询(like): %(任意多个字符),-(任意一个字符)
select ename from emp where enma like 'A%'; %以字母A开始的字符串
select ename from emp where ename like '_A'; %第二个字母为A
要表示字符'_',则‘\_’
5.3 其它操作
5.3.1 排序
升序: select ename,sal from emp order by sal;// 默认升序
**asc 升序, desc降序**
降序:select ename,sal from emp order by sal desc;
也可以通过这样实现,但是一般不用此:
select ename,sal from emp order by 1; 第一列变量排序
按照工资降序拍,当相同时按照名字的升序排列:
select ename,sal from emp order by sal desc, ename asc;
找到工作岗位为salesman,且以工资降序排序
select ename,sal from emp where job = 'salesman' order by sal desc;
注意:越靠前字段,越先执行; 且执行顺序from,where,select,order by
5.3.2 求和(分组函数/多行处理函数)
select sum(sal) from emp;
select avg(sal) from emp;
select max(sal) from emp;
select min(sal) from emp;
select count(empno) from emp;
分组函数自动忽略NULL
count(*) 与count(‘某字段’)区别:意义不同,结果也可能不同,count(‘某字段’)为某个字段不为空的条数。
分组函数在group by 后执行
5.3.3 单行处理函数
1、当里面有null参与运算,单行处理时则结果一定为null
select ename,(sal+comm)*12 as yearsal from emp;
2、**ifnull()**处理,如果为空,则被当做什么处理
select ename,ifnull(comm,0) as comm from emp;
select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;
5.3.4 group by 和 having
有group by时,select后只能跟分组字段以及分组函数,且group by可以对多个字段进行分组
1、找出每个部门不同岗位的最大薪水
select drptno,job,max(sal) from emp group by deptno,job;
2.找出每个部门的最高薪水,要求显示薪水大于2900
select max(sal),deptno from emp having max(sal)>2900;//这种方式效率低
select max(sal), deptno from emp where sal >2900 group by deptno
3、找出每个部门的平均薪水,并且显示平均薪水大于2000的部门
select max(sal),deptno from emp group by deptno having avg(sal)>2000; (where 语句不可接分组函数,因为分组在where后)
having只有在group by出现时搭档使用
注意:
5.4 连接查询
1、笛卡尔乘积
注意:如果两张表在进行连接查询时没有加查询条件,则查询的条数为两张表记录的乘积
如:
select e.ename, d.dname from emp e, dept d;
//这里起别名,方便查询。执行效率高,可读性强
2、如何避免笛卡尔乘积
5.4.1内连接
1.内连接的等值连接 (条件是等关系)
//要求查询每个员工的部门名称,要求显示员工名和部门名
SQL92(太老一般不用):
select e.ename,d.dname from emp e, dept d where e.deptno = d.deptno;
SQL99(常用):
select e.ename,d.dname from emp e join dept d on e.deptno = d.deptno;
//这里join代替‘,’,on代替where。这样写的好处是结构清晰,后面还可以再加where,连接条件与过滤条件被分离了 (这里应该是inner join,但是inner可以省略)
2.内连接的非等值连接 (条件不是等关系)
//要求查询员工的工资等级
select e.ename,s.grade from emp e join salgrade s on e.sal between s.losal and s.hisal;
3.自连接(将一张表看作两张表,自己连接自己)
//找出每个员工的上级领导
+--------+------+
| ename | mgr |
+--------+------+
| SMITH | 7902 |
| ALLEN | 7698 |
| WARD | 7698 |
| JONES | 7839 |
| MARTIN | 7698 |
| BLAKE | 7839 |
| CLARK | 7839 |
| SCOTT | 7566 |
| KING | NULL |
| TURNER | 7698 |
| ADAMS | 7788 |
| JAMES | 7698 |
| FORD | 7566 |
| MILLER | 7782 |
+--------+------+
员工的领导编号=领导的员工编号
select a.ename as '员工',b.ename as '上级领导' from emp a join emp b on a.mgr = b.empno;
5.4.2 外连接
//找出所有员工的上级领导,包括没有上级领导的员工
//所以此时用外连接,员工表作为主表
select a.ename,b.ename from emp a left join emp b on a.mgr = b.empno; //左连接
select a.ename,b.ename from emp b right join emp a on a.mgr = b.empno; //右连接
//left/right outer join //outer 都可以省略
注意:外连接最重要的特点是主表的数据无条件全部显示
多表连接 (先连接B再连接C)
A
join
B
on
...
join
C
on
...
left位置不能乱写
5.5 子查询
1.where过滤
//找出工资高于平均工资的员工
select * from emp where sal > (select avg(sal) from emp);
2.from嵌套
//找出每个部门平均薪水的薪水等级
select d.deptno,s.grade from salgrade s join (select deptno,avg(sal) as sal from emp group by deptno) d on d.sal between s.losal and s.hisal;
3.select后面嵌套子查询
//找出每个员工所在的部门名称,要求显示员工和部门名
select e.ename, (select d.dname from dept d where e.deptno = d.deptno) as dname from emp e;
5.6 union和limit
1.union
union 可以连接不同的表将结果拼成一列,注意:union连接的两个结果字段应该数量相同
//找出工作岗位是manager和salesman的结果
select enmae, job from emp where job = 'manager'
union
select enmae, job from emp where job = 'salesman'
2.limit(分页查询,只有mysql有)
limit取结果集中的部分数据,是sql语句中最后执行的语句
语法机制: limit starIndex,length (starIndex开始的位置,length长度)
//取出工资钱5名的员工
select ename,sal from emp order by sal desc limit 0,5;
or
select ename,sal from emp order by sal desc limit 5;
6. DDL
6.1 创建表
1.char与varcahr区别
2.表的命名格式
//创建语句
create table t_student(
no bigint,
name varchar(255),
sex char(1),
classno varchar(255),
birth char(10)
);
3.default指定默认值
4.复制表
1.创建和emp相同的表emp2
create table emp2 as select * from emp;
2.将表emp的内容插入到emp2中
insert into emp2 select * from emp;
6.2 删除表
drop table t_student;
6.3 更新表
7. DML
7.1 插入
注意:可以改变字段的位置,只要后面的值对应即可
1.插入
insert into t_student(no,name,sex,classno,birth) values('1','Bala','0','Gaosan2ban','1992-12-3');
//除了主键必须给值外,其余的可以不设置值,系统自动设置为null,主键不可重复
insert into t_student(no) values(2);
3.字段可不写的,但后面数据必须写完整且顺序也很重要
insert into t_student values (3,'Rose','1','gaosan4ban','1992-2-12');
4.一次插入多行数据
insert into t_student(no, name, sex, classno, birth) values (4,'Dose','1','gaosan3ban','1993-12-12'),(5,'Danel','1','gaosan1ban','1993-1-12');
7.2 删除
7.3 修改
8. 约束
1、not null
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) not null, //若此字段插入修改为null,则操作错误
password varchar(255)
);
2、unique(唯一性约束修饰的字段具有唯一性,但可以为null)
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique
);
//给两个列或者多个列添加unique
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
usercode varchar(255),
unique(username,usercode) //多个字段联合起来相加,称为表级
);
insert into t_user values(1,'Bala','code1');
insert into t_user values (2,'Bala','code2'); //此处由于是联合唯一,所以当username重复的时候是可以插入的
insert into t_user values(1,'Bala','code1');·//此时插入失败
drop table if exists t_user;
create table t_user(
id int,
username varchar(255) unique, //此时为列级
usercode varchar(255) unique
);
insert into t_user values(1,'Bala','code1');
insert into t_user values (2,'Bala','code2');//与上面的对比这里不是联合的,因此此时会插入失败
3、主键约束(primary key,既不可为空也必须唯一)
drop table if exists t_user;
create table t_user(
id int primary key ,
username varchar(255)
);
insert into t_user(id,username) values (1,'Bala');
insert into t_user(id,username) values (1,'Dove'); //此时错误因为主键不可以重复
insert into t_user(id,username) values (null,'Dove');//此时也会出错,因为主键不可为空
//表级约束
drop table if exists t_user;
create table t_user(
id int,
username varchar(255),
primary key(id,username) /*表级约束,复合主键*/
);
insert into t_user(id,username) values (1,'Bala');
insert into t_user(id,username) values (1,'Dove'); /*此时可以插入,因为是复合的主键*/
insert into t_user(id,username) values (1,'Bala');/*此时不可*/
//提供主键值自增
drop table if exists t_user;
create table t_user(
id int primary key aotu_increment, //注意:这里只对数字自增
username varchar(255)
);
4、foreign key外键约束(被应用的字段可为null,但必须唯一)
/*先创建父表,再创建子表; 先删除子表再删除父表*/
drop table if exists t_class;
create table t_class(
classno char(3) primary key
);
drop table if exists t_student;
create table t_student(
id int primary key auto_increment,
name varchar(255),
classno char(3),
foreign key(classno) references t_class(classno)
);
/*先给父表赋值再给子表的赋值,当子表插入的内容父表不存在则会报错*/
/*同时在删除时,先删除子表再删除父表*/
insert into t_class(classno) values ('101');
insert into t_student(name,classno) values ('Bala','101');
主键约束
外键约束
9. 存储引擎
1.存储引擎表示表的存储方式,只有mysql有,oracle叫别的名字
mysql> show engines \G
*************************** 1. row ***************************
Engine: InnoDB
Support: DEFAULT
Comment: Supports transactions, row-level locking, and foreign keys
Transactions: YES
XA: YES
Savepoints: YES
*************************** 2. row ***************************
Engine: MRG_MYISAM
Support: YES
Comment: Collection of identical MyISAM tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 3. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
Transactions: NO
XA: NO
Savepoints: NO
*************************** 4. row ***************************
Engine: BLACKHOLE
Support: YES
Comment: /dev/null storage engine (anything you write to it disappears)
Transactions: NO
XA: NO
Savepoints: NO
*************************** 5. row ***************************
Engine: MyISAM
Support: YES
Comment: MyISAM storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 6. row ***************************
Engine: CSV
Support: YES
Comment: CSV storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 7. row ***************************
Engine: ARCHIVE
Support: YES
Comment: Archive storage engine
Transactions: NO
XA: NO
Savepoints: NO
*************************** 8. row ***************************
Engine: PERFORMANCE_SCHEMA
Support: YES
Comment: Performance Schema
Transactions: NO
XA: NO
Savepoints: NO
*************************** 9. row ***************************
Engine: FEDERATED
Support: NO
Comment: Federated MySQL storage engine
Transactions: NULL
XA: NULL
Savepoints: NULL
9 rows in set (0.00 sec)
2.常见的存储引擎?
10.事务
start transaction;
insert into t_student values(2,'Sara','101');
rollback ; //comment;
11.索引
6.索引的实现原理
索引底层数据采用的数据结构是:B+ Tree
12.视图
1、什么是视图?
视图就是站在不同的角度去看数据(同一张表的数据,通过不同的角度去看待)。
2、创建数据,删除数据
/*创建视图*/
create view myview as select ename,empno from emp;
/*删除*/
drop view myview;
注意:只有DQL语句才能以视图对象的方式创建出来。
3、对视图进行增删改查,会影响原表的数据。(通过视图影响原表数据的,不是直接的操作原表)
可以对视图进行CRUD
4、
13. DBA命令
13. 三范式
为单一主键的时候,就满足第二范式