MySQL数据库超长重点整理
一、什么是sql?
Sql 结构化查询语言，专门为数据库而建立的操作命令集，是一种功能齐全的数据库语言。只需要发出做什么命令，怎么做不用考虑。
二、Data-database-dbms-dbs：
- data：客观事物的符号表示，数据库存储的基本对象。
数据vs信息:信息是指数据经过加工处理之后所获取的有用知识，以某种数据形式表现。
- database DB：长期存储在计算机内，有组织的，可共享的数据集合。
特征：较小的冗余度，较高的数据独立性，易扩展性，并为各个用户所共享。
- Dbms数据库管理系统：位于应用程序和存储数据之间的一层数据管理软件。
用途：科学组织和存储数据，高效获取和维护数据。
定义：ddl数据定义语言，create,alter,drop
操作：dml数据操作语言,select,insert,update,delete
优点：相互关联数据集合，少数据冗余，程序数据相互独立，数据安全可靠正确
- Dbs数据库系统:db+dbms+应用系统+dba数据库管理员+用户
关系：data--database-dbms-dbs
三、数据库系统的发展:
- 网状模型：图形结构
- 层次模型：树形结构
- 关系模型：简单的二维表结构
四、关系模型：
1、概念：
关系：一个关系就是一张二维表
元祖：二维表的一行，一个元祖对应表中的一个记录
属性：二维表的一列，每个属性都有一个属性名，属性值则是各个元祖的取值
域：属性的取值范围为域，属性值的集合。如年龄：18-25
关键字：唯一区分确定不同元祖的属性，针对表中的列，关键字修饰列唯一
- 主键：针对一个表来说，主键修饰的列唯一且不能为空，只能有一个主键
- 外键：针对两个表来说，加强表和表之间的联系。
关系-表，关系名-表名，元祖-行，属性-列，属性值-属性对应的取值，域-属性的取值范围，关键字-主键、外键
2、关系模式特点：
- 关系必须规范化，属性不能再分割
- 同一关系中不允许出现相同的属性名
- 同一关系中元祖、属性名任意
3、关系运算：
- 选择：找出满足条件的元祖组成新关系（行）
- 投影：若干属性组成新关系（列）
- 连接：两个表的笛卡尔积中选取属性满足一定条件的元祖，组成新关系。
例如：A={a,b},B={0,1,2} 笛卡尔积：{(a,0)(a,1)(a,2)(b,0)(b,1)(b,2)} 两个表匹配的所有结果全部罗列 缺点：冗余
选择-行，投影-列，连接-笛卡尔积
4、关系的完整性约束：（约束即限制）
- 实体完整性：主属性值不能为空，学生信息表中学号不能为空
- 参照完整性：主键和外键的关系 主表和从表 ，从表想要插入一条信息，查看主表有没有，有则插，没有则拒绝
- 域完整性：限制了某些属性中出现的值，把属性限制在一个有限的集合
五、三大范式：
关系模式要满足的条件成为规范化形式，范式，NF,即这个表要遵循哪些条件。
目的：消除存储异常，减少数据冗余，保证数据的完整性和存储效率。一般3NF即可。
- 第一范式：关系R所有属性均为简单属性，表中每个属性不可再分。
- 第二范式：如果满足第一范式，并且每个属性不可再分，并且每一个非关键字完全依赖于主键，通过学号可以找到任何其他相关值。
- 第三范式：满足了第二范式。非主键之间不存在依赖关系。若从表引入主表的id，从表内就不能在引入主表非关键字了，强调表与表之间的关系，只能引入主表的一个列。
六、实体-联系模型：
E-R图，提供了表示实体类型、属性、联系的方法，表明实体与实体之间的联系。
比如：手机（实体）-大小、品牌、价格（数据项）学生（实体）-学号、性别、年龄（数据项）
- 实体：客观事物在信息世界中成为实体，现实世界中任何可区分，识别的事物。
- 属性：主体、联系的性质或特征
- 联系：事物内部、事物之间的关联集合
实体与实体之间的关联：一对一 1:1、一对多1：m、多对多m：n
矩形-实体，椭圆-实体属性，菱形-实体之间的关系
七、数据库操作：
- show databases;
- create database 库名;
- drop database 库名;
- use 库名;
八、数据类型：
1、数值型
TINYINT 1 微整型 SMALLINT 2 小整型 MEDIUMINT 3 INT 4
FLOAT 4 DOUBLE 8
DECIMAL（总位数，小数位）小数首选 比如：decimal(5,3) 98.234
2、日期和时间类型：
DATE 3 yyyy-mm-dd
TIME 3 HH:MM:SS
YEAR 1 YYYY
DATETIME 8 yyyy-mm-dd hh:mm:ss √
TIMESTAMP YYYYMMDDHHMMSS
3、字符串类型：
char varchar binary varbinary blob
char varchar 区别：
char（存储字符串的长度）固定的长度值 char(10)剩余空间没有用完，用空格填满
varchar(存储字符串的长度) 变长 varchar(10)abc剩余空间没有用完，会释放空间 首选，因为节省内存空间
九、DDL数据定义语言
- 创建表
create table 表名(
属性1 数据类型[约束条件],
属性2 数据类型[约束条件],
属性3 数据类型[约束条件]
);
- 删除表
drop table 表1,表2;
- 修改
添加列：Alter table 表名add 属性名 数据类型；
删除列：Alter table 表名 drop 属性名；
修改属性数据类型：alter table 表名 modify 属性名 数据类型；
修改字段名：alter table 表名 change 旧字段名 新字段名 数据类型；
显示表结构：desc 表名；
比如：
- Alter table stu add score varchar(10);
- Alter table stu drop score;
- Alter table stu modify class varchar(20);
- Alter table stu change class cla varchar(10);
4、约束
正常情况下，不允许学号重复，若相同学号不报错，所以这个表不完整，所以添加约束。
（1）实体完整性：
主键约束：primary key
特征：唯一、不重复、不为空
1、创建好表时，创建约束
Alter table 表名 modify 列名 数据类型 primary key;
Alter table 表名 add primary key(列名);
Alter table 表名 add constraint 主键约束名字 primary key(列名)；
删除：alter table drop primary key;
比如：
- Alter table stu add primary key(id);
- Alter table stu add constraint sid primary key(id);
- Alter table stu modify id varchar(10) primary key;
2、建表时添加：
create table 表名（
列名1 数据类型 primary key,
列名2 数据类型
）；
create table 表名（
列名1 数据类型，
列名2 数据类型，
……
constraint 主键约束的名字 primary key(列名1)
）；
create table 表名(
列名1 数据类型，
列名2 数据类型，
primary key(列名1)
);
联合主键：看成一列
create table 表名(
列名1 数据类型，
列名2 数据类型，
primary key(列名1，列名2)
);
唯一约束：unique
唯一约束不允许出现重复的值，但是可以为多个null，同一个表有多个唯一约束。
1、创建表的同时创建唯一约束：
create table 表名(
列名1 数据类型 unique，
列名2 数据类型，
列名3 数据类型
);
可以加别的约束，不冲突
create table 表名（
列名1 数据类型，
列名2 数据类型，
……
constraint 唯一约束的名字 unique(列名1)
）；
2、针对已经创建好表的情况下，添加、删除唯一约束
- alter table 表名 add unique(列名);
- alter table 表名 drop index 唯一约束的名字;
非空约束：not null 一个表里面可以有多个非空的
格式：列名 数据类型 not null
比如：
create table stu(
sno int not null，
sname varcahr(20)
);
alter table stu modify sno int not null;
（2）域完整性：限制数据类型，缺省值，规则，约束，是否有可以非空
默认约束:default
create table 表名（
列名1 数据类型，
列名2 数据类型 default '默认值'
）；
alter table 表名 modify 列名 数据类型 default '默认值'；
alter table 表名 modify 列名 数据类型；
十、DML数据操作语句:针对表中数据
Insert
- Insert into 表名 (列名1，列名2……) values (值1，值2……)；
- Insert into 表名 values（值1，值2，值3……）;
- insert into 表名 values(值1，值2，值3……)，values(值1，值2，值3……)；
Delete
- Delete from 表名；
- Delete from 表名 where 条件；
Update
- Update 表名 set 字段=值；
- Update 表名 set 字段=值，字段=值；
- Update 表名 set 字段=值，字段=值 where 条件；
比如：
- update person set age=77;
- update person set sex='女',age=age+40;
- update person set age=12,sex='男' where name='ls';
Select
- Select 列名 from 表名；
- Select 列名1,列名2,列名3……from 表名；
- Select * from 表名；
十一、关于查询：
1、distinct删除重复行：
- select distinct 列名 from 表名;
2、别名：修改显示的标题
- select 列名1‘别名1’，列名2 ‘别名2’ from 表名；
3、as
- select 列名 as ‘别名’ from 表名；
4、条件查询
- Select 列名 from 表名 where 条件；
多个条件可以用and和or连接
5、比较搜索条件
<> != 不等于 !> !<不大于不小于
- select * from person where age<23;
6、范围搜索条件
- 在范围之内：select 列名 from 表名 where 列名 between 开始值 and 结束值；（闭区间）
- 不在范围内：select 列名 from 表名 where 列名 not between 开始值 and 结束值；
7、列表搜索条件
in只要匹配到括号里面任何一个值就会有查询结果
- Select 列名 from 表名 where 列名 in (值1，值2，值3……)；
- Select 列名 from 表名 where 列名 not in (值1，值2，值3……)；
8、搜索条件中的字符匹配
%表示0个或多个字符，_表示一个字符
- select 列名 from 表名 where 列名 like '匹配的字符';
9、涉及空值的查询
- select name from person where age is null;
- select name from person where age is not null;
10、聚合函数
count(*):统计表中元祖的个数 不忽略null；
count(列名)：统计某个列有多少条记录,忽略null。
sum avg max min 忽略null值
11、行数限定
- Select 列名，列名……from 表名 limit [start,] nums;
Start 从第几行开始，可选，默认从0开始，nums：总共要查询几行
比如：
- Select * from person limit 5;
- Select * from person limit 2,3;
12、分组查询
- Select 列名，聚合函数
- From 表名
- Group by 列名
- Having 条件（聚合函数）;
- Having可以包含聚合函数，where不可以。
例子：
1、根据性别来查看最小年龄：
- select sex,min(age)
- from person
- group by sex;
2、每个性别有多少个人：
- select sex,count(*) as 'totalpeople'
- from person
- group by sex;
3、年龄小于23的：Having 和group by一起使用。
- select *
- from person
- group by name
- having age<23;
4、总成绩小于600：
- Select *
- From sc
- Group by id
- Having sum(score)<600;
5、查总人数，根据年级分类：
- select 年级，sum(人数) as ‘总人数’,count(*) as ‘班级总数’
- from 班级信息
- group by 年级；
12、排序
- Select 列名 from 表名 order by 列 desc|asc；
比如：
- select age,name,sex
- from person
- order by age desc;
查询题思路：
确定表，一个表还是两个表
确定查询的列（注意聚合函数）
确定条件（根据条件）
十二、Mysql中的函数：
字符串函数
length:字符串字节长度,中文字符占两个字节
- 比如：select * ,length(姓名) as '姓名的字节长度' from student;
Char_length:字符串的字符长度,中文字符占一个字节
- select * ,char_length(姓名) from student;
Mid:截取 从某一个位置获取某个长度的字符。2为开始位置，最小值是1,1位要截取的长度。
- Select *,mid(name,2,1) as ‘截取字符’ from stu;
round实现四舍五入 round(数值,保留小数位)
- Select round(23.77777,2);
- Select round(23.7777);四舍五入
- Select round(avg(年龄),3) as ‘平均成绩’ from stu;
Least:可以求最小的数字 least(1,2,3,4,5,6);
Greatest:可以求最大的数字 greatest(7,4,8,2,3);
日期和时间函数
Now:当前日期时间 select now();
Current_date:当前日期 select current_date();
Current_time:当前时间 select current_time();
To_days:日期转换成总天数 select to_days(now());
Dayofyear:可以求该年已过天数 select dayofyear(now());
Week可以返回当前的时日是第几周：Select week(now());
控制函数：
if有三个参数，第一个参数为空输出第三个参数，否则输出第二个参数
- select if(布尔表达式，'你好'，'你不好');
- select if(null,'你好'，'你不好');
ifnull 有两个参数，第一个为空输出第二个，否则输出第一个
- select ifnull(null,'你好');
- select ifnull('你好','你不好');
十三、表连接：
多表查询，需要进行表连接。
内连接：只有匹配到的情况下才会返回结果值。
- Select 表名1.列名1，表名1.列名2，表名2.列名1，表名2.列名2……
- From 表名1，表名2
- Where 表名1.列名1=表名2.列名2；
比如：
- Select sc.grade,stu.sname
- From sc,stu
- Where sc.sno=stu.sno;
- select 表名1.列名1，表名1.列名2，表名2.列名1，表名2.列名2……
- From 表名1 inner join 表名2
- On 表名1.列=表名2.列;
Inner 可有可无，join一定要写 分为等值连接，非等值连接
比如：
- select sc.sno,sc.grade,stu.sname
- from sc inner join stu
- on sc.sno = stu.sno and sc.grade>80;
简化写法：
- Select *
- From 学生信息 a inner join 班级信息 b
- On a.sno=b.sno;
Select 列名
From 表名
Where
Group by
Having
Order by
比如：
- select sc.cno,grade,stu.sname,sage
- from sc ,stu
- where sc.sno = stu.sno and sc.grade>80 and stu.sname like '小%'
- group by sc.grade
- having stu.sage = 18
- order by sc.grade desc;
若是两个表没有相同的地方可以作为连接的条件，那就找一个中间人，中间表。
方法一：
- select stu.sname,sdept
- from stu,course,sc
- where stu.sno=sc.sno,sc.cno=course.cno and course.cname='VB';
方法二:先两两结合，然后在连接另一个表
- select stu.sname,sdept
- from stu inner join sc
- on stu.sno = sc.sno inner join course
- on sc.cno=course.cno
- where course.cname='VB';
外连接：
外部连接会返回from子句提到的至少一个表或视图中的所有行。不匹配的显示为null。分左外部连接、右外部连接。
左：对连接条件中左边的表不加限制。
右：对连接条件中右边的表不加限制。
左：left outer join
- select sc.sno,sc.grade,stu.sname
- from stu left outer join sc
- on sc.sno = stu.sno;
右：right outer join
- select sc.sno,sc.grade,stu.sname
- from stu right outer join sc
- on sc.sno = stu.sno;
十四、子查询
子查询：子查询在其他查询结果的基础之上提供了一种有效的方式来表示where子句的条件。可以嵌套在select insert update delete语句之中。子查询的select查询总是用圆括号括起来。嵌套子查询 一个子查询中还可以包含另一个子查询 最多255个
比如：
- select cno
- from sc
- where sno in (select sno from stu where sdept='计算机系');
（从sc表里面来查询cno 但是要查询计算机系的学生号 sc里面没有计算机系 所以根据stu里面的学号来查询）
对于子查询来说，外查询要什么，子查询就查什么。一一对应的关系。
比如：
- select sno,grade
- from sc
- where cno='c02' and grade>(select avg(grade) from sc where cno = 'c02');
相关子查询：（单值子查询）
这样的子查询只返回一个值
- select sc.cno,grade,category,stu.sname,ssex
- from sc ,stu
- where sc.sno = stu.sno
- and sc.sno = (select sno from stu where sname='小胡');
十五、视图
视图：基于某个查询结果的虚表。（表）作用就是方便用户数据的操作。
create view 视图名字 as select 语句;
创建视图：
- create view view_stu as select * from stu;
- Select * from view_stu;
修改视图：修改了数据也修改了表结构
- alter view view_stu as select sno,sname,sage from stu;
- select * from view_stu;
插入数据：视图的修改也会影响原表的数据
- insert into 试图名字 values (值1，值2……);
修改数据：
- update 视图名字 set 列=值 where 条件； 也会影响原表的数据
删除数据：
- delete from 视图名字 where 条件 ；
- delete from 视图名字；
比如：
- insert into view_stu values('6','xiaoxiao','12');
- update view_stu set sage = 53 where sname='小猪';
- delete from view_stu where sage=53;
十六、索引
类似于目录，有助于更快的获取信息，用来定位的。创建了索引的列几乎是立即响应，而不创建索引的列则需要较长的时间的等待，增加查询速度。
作用：加快数据的检索，保证数据的唯一性，实现表和表之间的参照完整性，在使用group by和order by的时候进行查询时，利用索引可以减少排序和分组的时间。
是否必须：索引要占用数据空间并花费一定的时间，建立索引会减慢数据修改的速度。
选择创建索引的数据列：定义有主键和外键的列，在指定的范围中加快或频繁查询的列，连接中频繁使用的列，需要按照排序顺序快速或频繁检索的列。
类型：普通索引* 唯一索引* 全文索引 单列索引 多列索引 空间索引*
设计索引时需注意：
- 一个表如果建有大量的索引，会影响insert update delete语句的性能
- 避免对经常更新的表进行过多的索引
- 使用多个索引可以提高更新少而数据量大的查询的性能。
- 对小表进行索引可能不会产生优化的结果。
- create index 索引的名字 on 表名（列名）；
创建唯一索引：
- create unique index 索引的名字 on 表名（列名）；
删除索引：
- drop index 索引名字 on 表名；
比如：
- create index ix_stu_sno on stu(sno);
- create unique index ix_person_name on person(name);
- drop index ix_person_name on person;