爱站程序员基地1.关系数据库分类
关系->以二维表格形式组织数据关系(excel表格)ORACLEDB2SQLserverMYSQL
2.SQL定义
sql->结果化查询语言 structured query languagesql是在数据库上执行一系列数据操作的标准语言
3.MySQL的登陆与退出
1.MySQL登陆（登入本届电脑中数据库）mysql -u root -p其中：a)mysql指令为一个mysql的客户端程序b)-u表示用户选项c)-p 表示密码选项2）MySQL退出a)quitb)exit
4.MySQL基本指令的应用
1）status 查看当前数据库系统的状态信息2）show databases;查看当前用户下的数据库3）seelct user();查看当前登录用户（在mysql中没有dual伪表）4）select now(); 查看当前日期时间5）？ functions(?表示帮助，functions 表示函数)这里的？等价于help,例如？show 等价于help show.
5.MySQL中基本SQL的应用
1.数据库相关SQL语句
ａ）显示当前用户下的数据库show databases;b)创建数据库(语法参考 ? create database)create database 库名;create database pms;create database if not exists pms;create database if not exists pms character set utf8;要是打一半想取消 可以使用 \\c来取消**说明**：在mysql中一个用户下可以有很多数据库（DB）c)使用或打开数据库use 库命;use database;查看当前正在使用的数据库：select database();d)删除数据库（语法参考 ? drop database）drop database 库名；drop database pms;drop database if exists pms;e)查看表show tables;
2.MySQL中表的相关操作
a)创建表（语法 ? create table）create table user(username varchar(20);password varchar(30);email varchar(30);mobile varchar(20););
创建带有主键的表
-- primary key 是声明该字段为主键，数据库会检查唯一性--auto_increment 是要求数据库自动生成主键的值create table if not exists pet(id int primary key auto_increment,name varchar(100) not null);
当需要了解具体类型时可以： ? 具体数据类型
例如 ？int
当需要查看某张表的创建语句时，可以使用
show create table pet
当需要显示表结构时可以使用：
desc 表名;desc pet
b)修改表（语法 ? alter table）:追加字段（列）alter table user add sex char(1),add address varchar(100);c)删除表（语法 ? drop table）drop table 表名;drop table if exists pet;
3.MySQL表中数据的操作（增加、修改、删除、查询数据）
DML语言只包含 增 删 改DQL语言包含 查询a)向表中写入数据（insert）全列插入，表示给所有字段赋值，按建表时的顺序赋值isnert into user values(\'wukong\',\'123\',\'wukong@qq.com\',nuoll,\'M\',\'花果山\');部分列插入insert into pet(id,name) values (null,\'B\');isnert into pet(name) values(\'C\');b)更新表中数据（语法参考 ? update）--修改用户update user set email=\'ts@qq.com\',mobile=\'110\' where username=\'tangseng\';c)删除表中数据（语法参考 ? delete）--删除用户delete from user where username=\'tagnseng\';delete from suer where mobile is null;注意 若没有where条件 则全部数据都会被删除！！
DELETE 删除表中的数据，表结构还在；删除后的数据可以找回TRUNCATE 删除是把表直接DROP掉，然后在创建一个同样的新表。删除的数据不能找回，执行速度比DELETE快。d)查询表中数据（select）select * from pet;select id,name from pet;select id,namefrom petwhere id = 10;--在显示时给字段取别名select username as \'用户名\',password as \'密码\' from user;select username as NAME ,password ad PWD from user;--在查询是增加筛选条件select * from suer where sex=\'M\';select * from user where sex = \'M\' and username=\'bajie\';select * from user where email is null;select * from user where email is not null;
4.SQL分类
1)DDL Data Definition Language-数据结构定义语言-用来定义数据库对象:库、表、列等；-包含：create,alter,drop语句-重点面试题 注意DDL不支持失误 即DDL操作后无法回滚2）DML Data Manipulation Language-数据更改语言-包含：insert,update,delete-重点面试题 主要DML支持是我 即在非自动提交模式下操作后可以回滚3）DQL Data Query Language-数据查询语言-包含：select4）TCL Transaction Control Language-事物控制语言-包含：commit,rollback,savepoint保存点5）DCL Data Control Language-数据库控制语言，用了管理数据库的权限-包含：grant授权 revoke 取消授权
5.常用数据类型
int:整型doublie：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值999.99；char:固定长度字符串类型； char(10) \'aaa \' 占位10varchar:可变长度字符串类型； varchar(10) \'aaa\' 占3位text:字符串类型；blob:字节类型；date:日期类型，格式为：yyy-MM-dd;time:时间类型，格式为：hh:mm:sstimestamp:时间戳类型：yyyy-MM-dd hh:mm:ss 会自动赋值datetime:日期时间类型 yyyy-MM-dd hh:mm:ss
6.MySQL数据库DQL数据查询语言（特别重要）
数据库执行DQL语句不会对数据进行改变，而是让数据库发送结果集给客户端。查询返回的结果集是一张虚拟表。
查询关键字：SELECT语法： SELECT 列名 FROM 表名
1.条件查询
条件查询就是在查询是给WHERE字句，在WHERE子句中可以使用如下运算符及关键字：=、 ！=、<>、 <、 <=、 >、 >=;BETWEEN...AND;IN(set);IS NULL;AND;OR;NOT;--like模糊查询，%是统配符替代一个或多个字符select * from item where name like \'%联想%\';-- and,orselect * from item where name like \'%ThinkPad%\' and detail like \'%14%\';select * from item where name like \'%联想%\' or name like \'%戴尔%\';-- >,<,>=,<=,!=,<>select * from item where name like \'%显示器%\' and price<=1000;select * from item where name like \'%显示器%\' and price<=1000 and price>=900;select * from item where name like \'%显示器%\' and price!=1299;select * from item where name like \'%显示器%\' and price<>1299;-- between m and nselect * from item where price between 500 and 1000;-- in,is,notselect * from item where category_id in (7,8,11,12);select * from item where category_id=9 and image is not null;select * from item where category_id=10 and name not like \'%冰冷%\';select * from item where price not between 500 and 1000;select * from item where category_id not in (7,8,11,12);
2.排序
--order by 字段 asc/desc-- order by放在wher之后-- asc是升序，desc降序select * from item where name like \'%联想%\' order by price asc;-- 省略拍下方式时，默认为升序select * from item where name like \'%联想%\' order by price;--可以用多个字段同时排序select * from item order by category_id desc,price asc
3.分页
-- limit begin,size-- 写在order by之后-- begin是当前页的其实行号（从0开始）-- size是本页要显示的最大行数select * from item order by price limit 10,10;
4.处理显示结果
--拼接字符串select concat(name,\'\',price,\'元\') as title,detail,stock from item;-- 计算（+ - * / %）select name,detail,price * stock as total from item;--格式化日期select name,price,date_format(upload_time,\'%Y年%c月%d日 %H时%i分%s秒\') from item;-- 空值处理select name,price,ifnull(image,\'无\') as image,ifnull(detail,\'无\') as detail from item;
5.聚合函数
-- sum：求和select sum(stock) as total_stock from item;select category_id,sum(stock) as total_stock from item where category_id=7;-- 合计是，附带id,name等细节数据是无意义的-- 使用其他的聚合函数进行统计时，也是这样的select id,name,sum(stock) as total_stock from item;-- avg:求平均值select avg(price) as avg_price from item;select category_id,avg(price) as total_price from item where category_id=7;-- count:求返回数据的行数-- 括号内写要统计的列的名字select count(*) from item;select count(id) from item;select count(name) from item;select count(*) from item where name like \'%联想%\';-- max/min:求最大值/最小值select max(price) from item;select min(price) from item where name like \'%联想%\';特别注意 null值聚合函数一般都不计算
6.分组统计
-- group by 字段select category_id,sum(stock) from item group by category_id;select category_id,avg(price) from item group by category_id;-- 数据库执行SQL的顺序-- from -> where -> group -> select--下面SQL有误，因为在where里用了聚合函数select category_di,sum(stock) from item where sum(stock)>1000 group by category_id;-- 对分组数据进行筛选，应该在group by中写条件（having）select category_id,sum(stock) from item group by category_id having sum(stock)>1000;having与where的区别：1.having是在分组后对数据进行过滤。where是在分组前对数据进行过滤2.having后面可以使用分组函数（统计函数）where后面不可以使用分组函数。WHERE是对分组前记录的条件，如果某行记录没有满足WHERE子句的条件，那么这行记录不会参加分组；而HAVING是对分组后数据的约束-- 非统计的条件可以写在where 或 having内-- 能鞋子where内的条件就写在这里，效率高select category_id,sum(stock) from item where category_id>10 group by category_id;select category_id,sum(stock) from item group by category_id having category_id>10;-- 查询学生文具盒打印机的平均单价select category_id,avg(price) from item where category_id in (11,13) group by category_id;-- 查询平均单价校小于100的商品分类select category_id,avg(price) from item group by category_id having avg(price)<100;-- 分组后排序select category_id,max(price) from item group by category_id order by category_id desc;select category_id,sum(price * stock) from item group bycategory_id order by category_id;select category_id,sum(price * stock) as s from item group by category_id order by s desc;注意：凡和聚合函数同时出现的列名，则一定要写在group by之后例如 查询每个部门的部门编号以及每个部门的人数；select * deptno,COUNT(*)FROM emp GROUP BY deptno;
7.子查询
-- 在用户表中准备一些数据insert into user values(null,\'zhuge\',\'321\',null,null,now());insert into user values(null,\'zilong\',\'321\',null,null,now());insert into user values(null,\'yide\',\'321\',null,null,now());insert into user values(null,\'yunchang\',\'321\',null,null,now());insert into user values(null,\'xuande\',\'321\',null,null,now());insert into user values(null,\'mengde\',\'321\',null,null,now());insert into user values(null,\'quede\',\'321\',null,null,now());--查询所有有地址的用户的IDselect distinct user_id from address; --1,3,4--查询所有有地址的用户的全部信息--user.id=address.user_idselect * from user where id in(1,3,4);select * from user where id in (select distinct user_id from address);-- 查询所有的商品的分类信息select * from category where id in (select distinct category_id from item);-- 也可以使用exist实现上述查询逻辑-- 子查询若存在数据，则外层查询返回与子查询批评的数据-- 子查询若不存在数据，则外层擦好像返回空select * from user where eixsts(select * from address where user_id=user.id):-- 使用exists实现查询所有有商品的分类信息select * from category where exists(select id from item where category_idcategory.id);-- 查询出所有有地址的省份嘻嘻你（in，exists）select * from province where id in (select distinct province_id=province.id);-- 查询所有用户，以及他们对应的地址数量select user_id,count(*) from address group by user_id;select *,(select count(*) from address where user_id = user.id) as amount from user;-- 查询所有分类，以及他们所对应的商品的库存总数select *,(select sum(stock) from item where category_id=categiry.id) as total from cat egor;
8.关联查询
-- 等值连接：查询出A和B相匹配的数据select u.id,u.suername,u.password,a.name,a.addressfrom user as u,address as a where u.id=a.user_id;--内链接：其左右给你与等值连接相同-- inner可以忽略不写selectu.id,u.username,u.password,a.name,a.addressfrom user as u,address as awhere u.id = a.user_id;-- 内链接：其作用与等值连接相同-- inner可以省略不写selectu.idu.username.u.passworda.namea.addressfrom suer as uinner join address as a on u.id=a.user_idwhere 1=1;--左外链接-- A left outer join B on条件-- A left join B on条件selectu.id,u.username,a.name,a.addressfrom user uleft join address a on u.id=a.user_id;--右外连接-- A right outer join B on 条件-- A right join B on 条件selectu.id,u.username,a.name,a.addressfrom user uright join address a on u.id=a.user_id;特别注意：笛卡尔积经常出面试题当俩表关联时，若没有写关联条件，则返回的总条目数是俩表条目数的乘积，这个乘积就叫做笛卡尔积
9.自关联
-- 查询所有的分类，以及他们的父分类selectc.id as cid,c.name as cname,p.id as pid,p.name as pnamefrom category cleft join category p on c.parent_id=p.id;-- 1.查询出所有的二级分类，以及他们的父分类-- 查询一级分类：父亲是一级分类。select * from categorywhere parent_id in(select id from category where parent_id is null);--查询二级分类，以及他们的父亲selectc.id cid,c.name cname,p.id pid,p.name pnamefrom category cleft join category p on c.parent_id=p.idwhere c.parent_id in(select id from category where parent_id is null);-- 2.查询出所有的三级分类，以及他们的父分类selectc.id cid,c.name cname,p.id pid,p.name pnamefrom category cleft join category p on c.parent_id=p.idwhere c.id in (select category_id is null);--父类为空的分类是一级分类--pid为空的分类是一级分类select * from category where parent_id is null;--爷爷为空的分类就是二级分类-- 父亲的pid为空的分类是二级分类-- c-孩子； p- 父亲；p.parent_id-爷爷where p.parent_id is null;-- 太爷爷为空的分类分类是三级分类-- 爷爷的pid为空的分类是三级分类-- c:孩子;p:父亲;g:爷爷;g.parent_id:太爷爷;selectc.id cid,c.name cname,p.id pid,p.name pname,g.id gid,g.name gnamefrom category cinner join category p on c.parent_id=p.idinner join category g on p.parent_id=g.idwhere g.parent_id is null;
9.索引
概念：
1)用来加快查询的技术很多，其中最重要的是索引（Index）;
2)通常索引能够快速提高查询速度
3）如果不适用索引，MYSQL必须从第一条记录开始然后读完整个表，直到找出相关的行。表越大，花费的时间越多。
4）索引可以用来改善性能，有时索引可能降低性能
在没有创建索引的情况下，查询数据-- 1.0x秒select * from item2 where title=\'100\';--创建索引-- create index 索引名 on 表名（字段，字段，字段。。。）create index index_of_item2 on item2(title(10),selling_point(10),price);--在创建索引的情况下，再次查询数据-- 0.0x秒select * from item2 where title=\'100\';--删除索引drop index 索引名 on 表名；
10.约束
数据库约束是为了保证数据的完整性（正确性）而实现的一套机制
1、主键约束(PK)primary key constraint 唯一且不为空alter table Studentadd constraint PK_Student primary key(sId)其中constraint 表示约束，PK_Student为约束名，primary key(sId)指定为哪一列添加主键约束，其中的sId表示添加约束的字段。2、唯一约束(UQ)unique constraint唯一，允许为空，即可以再其中出现null值，但只能出现一次alter table Studentadd constraint UQ_Student_sNo unique(sNo)unique(sNo)中的sNo表示添加unique约束的字段名3、默认约束(DF)default constraint默认值alter table Studentadd constraint DF_Student_sSex default(\'男\') for sSex为student表中的sSex字段设置默认值为‘男’，即当该字段为填入数据时，数据库将自行为其加上数据内容为‘男’的数据其中DF_Student_sSex为默认约束名注：以上创建的主键约束、唯一约束均存在与数据库的‘ 键’目录下，而默认约束在位于‘约束’目录下4、检查约束(CK)check constraint范围以及格式限制alter table Studentadd constraint CK_Student_sSex check (sSex=\'男\' or sSex=\'女\')为student表中的sSex字段添加内容检查约束，只允许添加的内容为‘男’或者是‘女’5、非空约束(NN) not nullcreate table student(id int primary key auto_increment,age int not null);6、外键约束(FK)foreign key constraint表关系alter table studentadd constraint FK_Student_sClassId foreign key (sClassId) references Class(cId)删除主表中数据的时候，必须先删除子表中对应的数据，否则执行SQL语句时，数据库报错提到外键，不得不提的是级联删除以及级联修改alter table Studentadd constraint FK_Student_sClassId foreign key (sClassId) references Class(cId)--级联删除on delete cascade--级联修改on update cascade即当删除主表中的数据时，子表中与其有关的数据都将被删除。因此，此外键的创建方法在使用时需谨慎考虑
删除约束
alter table Studentdrop constraint CK_Student_sAge
============================================================
--1)创建订单表，不加任何约束create table order(id bigint,user_id int,item_id int,item_name varchar(100),item_price decimal(11,4),item_detail varchar(200),item_image varchar(200),create_time timestamp);--2)插入空数据insert into orders values(null,null,null,null,null,null,null,null);--3)创建订单表，增加非空约束drop table order;create table orders(id bigint not null,user_id int not null,item_id int not null,item_name varchar(100),item_price decimal(11,4),item_detail varchar(200),item_image varchar(200),create_time timestamp);--4)再次插入空数据insert into orders values(null,null,null,null,null,null,null,null,);--5) 插入非空数据isnert into orders values(1,1,2,null,null,null,null,null,);--6)插入ID相同的数据isnert into orders values(1,1,2,null,null,null,null,null,);-- 7)重建订单表,增加唯一性约束drop table orders;create table orders(id bigint not null unique,user_id int not null,item_id int not null,item_name varchar(100),item_price decimal(11,4),item_detail varchar(200),item_image varchar(200),create_time timestamp);-- 8)插入ID相同的2条数据insert into orders values(1,1,2,null,null,null,null,null);insert into orders values(1,1,2,null,null,null,null,null);-- 9)重建订单表,增加主键约束drop table orders;create table orders(id bigint primary key,user_id int not null,item_id int not null,item_name varchar(100),item_price decimal(11,4),item_detail varchar(200),item_image varchar(200),create_time timestamp);-- 10)增加ID为空的数据,增加ID重复的数据insert into orders values(null,1,2,null,null,null,null,null);insert into orders values(1,1,2,null,null,null,null,null);insert into orders values(1,1,2,null,null,null,null,null);-- 11)增加一个订单数据,用户ID乱写insert into orders values(2,100,2,null,null,null,null,null);-- 12)重建订单表,增加外键约束drop table orders;create table orders(id bigint primary key,user_id int not null,item_id int not null,item_name varchar(100),item_price decimal(11,4),item_detail varchar(200),item_image varchar(200),create_time timestamp,CONSTRAINT fk_user_id foreign key(user_id) REFERENCES user(id));-- 13)增加一个订单数据,用户ID乱写insert into orders values(2,100,2,null,null,null,null,null);-- 14)给地址表追加外键约束alter table address add CONSTRAINT fk_user_id2 foreign key (user_id) REFERENCES user(id);-- 15)给地址表增加用户ID错误的数据insert into address values(null,100,\'唐僧\',null,null,null,null,null,null,null,null,null,null);-- 16)重建订单表,增加默认约束drop table orders;create table orders(id bigint primary key,user_id int not null,item_id int not null,item_name varchar(100),item_price decimal(11,4) default 9,item_detail varchar(200),item_image varchar(200),create_time timestamp);-- 17)增加单价为空的订单数据insert into orders(id,user_id,item_id) values(1,1,1);
11.事务
1）什么是事务
满足如下规则的数据库访问叫事务1）原子性：事务是一个完整的过程，要么都成功，要么都失败。2）一致性：事务前后的数据要一致，即收支平衡。3）隔离性：事务过程中的数据不能被访问。4）持久性：事物一旦达成就永久有效数据库都支持事物，都实现了上述4点。
小例子
--假设这里也执行过DML语句isnert ...;update ...;delete ...;--关闭自动提交事务set autocommit=0;--开始事务start transaction;--执行DML语句insert into user values(null,\'wukong\',\'123\',null,null,null);--查询这条数据select * from user;--commit/rollback--回滚到开始事务那里rollback;
12.数据库补充知识点（鄙视必考）
1.顺序问题
查询语句书写顺序：select - from - where - groupby - having - order by - limit查询语句执行顺序：from - where - group by -having - select - order by - limit
2.数据库设计常见三大范式
为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则，在关系型数据库中这种规则就称为范式。
范式是符合某一种设计要求的总结，要想设计一个结构合理的关系数据库，必须满足一定的范式。
在实际开发职工最为常见的设计范式有三个：
1.第一范式（确保每列保持原子性，所有字段值都不可分解）第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式。第一范式的合理遵循需要根据系统的实际需求来定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就非要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储，这样在对地址中某一部分操作的时候将非常方便。这样设计才算满足了数据库的第一范式。这样在对用户使用城市进行分类的时候就非常方便，也提高了数据库的性能。2.第二范式（确保表中的每列都和主键相关而不能只与主键的某一部分相关（主要针对联合主键而言））第二范式在第一范式的基础之上更进一层。第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键，如下表所示。``````bash这样就产生一个问题：这个表中是以订单编号和商品编号作为联合主键。这样在该表中商品名称、单位、商品价格等信息不与该表的主键相关，而仅仅是与商品编号相关。所以在这里违反了第二范式的设计原则。而如果把这个订单信息表进行拆分，把商品信息分离到另一个表中，把订单项目表也分离到另一个表中，就非常完美了。如下所示。
这样设计，在很大程度上减小了数据库的冗余。如果要获取订单的商品信息，使用商品编号到商品信息表中查询即可。3．第三范式(确保每列都和主键列直接相关,而不是间接相关)第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。如下面这两个表所示的设计就是一个满足第三范式的数据库表。
这样在查询订单信息的时候，就可以使用客户编号来引用客户信息表中的记录，也不必再订单信息表中多次输入客户信息的内容，减小了数据冗余。
3.数据库常见对象
数据库对象是数据库的组成部分，常见的有以下几种：
1.表（Table ）
数据库中的表与我们日常生活中使用的表格类似，它也是由行（Row） 和列（Column）组成的。列由同类的信息组成，
每列又称为一个字段，每列的标题称为字段名。行包括了若干列信息项。一行数据称为一个或一条记录，它表达有一定
意义的信息组合。一个数据库表由一条或多条记录组成，没有记录的表称为空表。每个表中通常都有一个主关键字，用
于惟一地确定一条记录。
2.索引（Index）
索引是根据指定的数据库表列建立起来的顺序。它提供了快速访问数据的途径，并且可监督表的数据，使其索引所指向
的列中的数据不重复。
3.视图（View）
视图看上去同表似乎一模一样，具有一组命名的字段和数据项，但它其实是一个虚拟的表，在数据库中并不实际存。在
视图是由查询数据库表产生的，它限制了用户能看到和修改的数据。由此可见，视图可以用来控制用户对数据的访问，
并能简化数据的显示，即通过视图只显示那些需要的数据信息。
4.图表（Diagram）
图表其实就是数据库表之间的关系示意图。利用它可以编辑表与表之间的关系。
5.缺省值（Default）
缺省值是当在表中创建列或插入数据时，对没有指定其具体值的列或列数据项赋予事先设定好的值。
6.规则（Rule）
规则是对数据库表中数据信息的限制。它限定的是表的列。
7.触发器（Trigger）
触发器是一个用户定义的SQL事务命令的集合。当对一个表进行插入、更改、删除时，这组命令就会自动执行。
8.存储过程（Stored Procedure）
存储过程是为完成特定的功能而汇集在一起的一组SQL 程序语句，经编译后存储在数据库中的SQL 程序。
9.用户（User）
所谓用户就是有权限访问数据库的人。
