厚街公司网站建设,wordpress发邮件更新,哈尔滨网站制作案例,安康养老院费用文章目录 概述DDL数据库操作查询使用创建删除 表操作创建约束MySqL数据类型数值类型字符串类型日期类型 查询修改删除 DMLinsertupdatedelete DQL基本查询条件查询分组查询分组查询排序查询分页查询 多表设计一对多一对一多对多设计步骤 多表查询概述内连接外连接 子查询标量子… 文章目录 概述DDL数据库操作查询使用创建删除 表操作创建约束MySqL数据类型数值类型字符串类型日期类型 查询修改删除 DMLinsertupdatedelete DQL基本查询条件查询分组查询分组查询排序查询分页查询 多表设计一对多一对一多对多设计步骤 多表查询概述内连接外连接 子查询标量子查询列子查询行子查询表子查询案例 事务语法四大特性(ACID) 索引结构语法 概述
关系型数据库(RDBMS):多张相连的二维表组成的数据库
SQL语句操作关系型数据库的编程语言是统一标准的。 1不区分大小写 2单行注释-- 注释内容 多行注释/* 注释内容 */ SQL语句分类
DDLdefinition定义对象DMLmanipulation操作数据DQLquery查询表记录DCLcontrol控制用户、数据库权限
DDL
数据库操作
查询
查询所有数据库show databases; 查询当前数据库select database();
使用
使用数据库use 数据库名;
创建
创建数据库create database [if not exists] 数据库名;
删除
删除数据库drop database [if exists] 数据库名;
表操作
创建
create table 表名(字段1 字段类型 [约束] [comment 字段1注释]字段2 字段类型 [约束] [comment 字段2注释]......字段n 字段类型 [约束] [comment 字段n注释]
)[comment 表注释];约束
概念作用于表中字段上的规则用于限制存储在表中的数据 目的保证数据库中的数据的正确性、有效性和完整性。
约束描述关键字非空约束限制该字段值不能为nullnot null 唯一约束保证字段的所有数据都是唯一、不重复的unique 主键约束主键是一行数据的唯一标识要求非空且唯一primary key(auto_increment自增)默认约束保存数据时若为指定该字段值则采用默认值default 外键约束让两个表的数据建立联系保证数据的一致性和完整性forign key
create table tb_user(id int primary key comment ID,唯一标识;username varchar(20) not null unique comment 用户名,name varchar(10) not null comment 姓名,age int comment 年龄,gender char(1) default 男 comment 性别
) comment 用户表;MySqL数据类型
数值类型
类型大小(byte)有符号(signed)范围无符号(unsigned)范围描述tinyint1(-128,127)(0,255)小整数值smallint2(-32768,32767)(0,65535)大整数值mediumint3(-8388608,8389607)(0,16777215)大整数值int4(-2147483648,2147483647)(0,4294967295)大整数值bigint8(-263 ,263-1)(0,264-1)极大整数值float4(-3.4028 E38 ,3.4028 E38)0和(1.1754 E-383.4028 E38)单精度浮点数double8(-1.7976 E308 ,1.7976 E308)0和(2.2250 E-3081.7976 E308)双精度浮点数decimal小数值(精度更高) float(5,2),double(5,2),decimal(5,2):5表示整个数字长度2表示小数位个数。 字符串类型
类型大小描述char0-255 bytes定长字符串verchar0-65535 bytes变长字符串 char(10)最多只能存10个字符不足10个字符占用10个字符空间。【性能高、浪费空间】 varchar(10)最多只能存10个字符不足10个字符按照实际长度存储。【性能低、节省空间】 日期类型
类型大小bytes范围格式描述date31000-01-01 至9999-12-31YYYY-MM-DD日期值datetime81000-01-01 00:00:00至于9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值
查询
查询当前数据库所有表show tables; 查询表结构desc 表名; 查询建表语句show create table 表名;
修改
添加字段alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];
修改字段类型alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束];
删除字段alter table 表名 drop column 字段名;
修改表名rename table 表名 to 新表名;
例题在表tb_emp中完成如下操作 (1)为表tb_emp 添加字段 qq varchar(11) alter table tb_emp add qq varchar(11) comment QQ; (2)修改 表tb_emp 字段类型 qq varchar(13) alter table tb_emp modify qq varchar(13) comment QQ; (3)修改 表tb_emp 字段名 qq 为qq_num varchar(13) alter table tb_emp change qq qq_num varchar(13) comment QQ; (4)删除 表tb_emp 的 qq_num 字段 alter table tb_emp drop column qq_num; (5)将表tb_emp 表名修改为emp rename table tb_emp to emp;
删除
删除表drop table [if exits] 表名;
DML
insert
指定字段添加数据insert 表名(字段名1字段名2) values(值1值2); 全部字段添加数据insert into 表名 values (值1值2...); 批量添加数据指定字段:insert into 表名(字段1字段名2) values(值1,值2),(值1,值2); 批量添加数据全部字段:insert into 表名 values(值1,值2,...),(值1,值2,...); 注意 1.插入数据时指定的字段顺序需要与值的顺序是一一对应的。 2.字符串和日期型数据应该包含在引号中。 3.插入的数据大小应该在字段的规定范围内。 例子表tb_emp中有姓名name和性别gender (1)为tb_emp表的name,gender字段插入值 insert into tb_emp(name,gender) values(张无忌,1); (2)为tb_emp表的所有字段插入值。 insert into tb_emp values(周芷若,2); (3)批量为tb_emp表的username,gender字段插入数据。 inser into tb_emp (username,gender) values (韦一笑,2),(谢逊,1);
update
修改数据update 表名 set 字段名1值1字段名2值2... [where 条件]; 修改语句的条件可以有。若没有条件则修改整张表的所有数据。 delete
删除数据delete from 表名 [where 条件]; 1.删除语句的条件可以有。若没有条件则删除整张表的所有数据。 2.删除语句不能删除某一个字段的值若要操作可以使用update将该字段的值置为NULL DQL
语法
select 字段列表
from 表名列表where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数基本查询、条件查询(where)、分组查询(group by)、排序查询(order by)、分页查询(limit)
基本查询
查询多个字段:select 字段1,字段2,字段3 from 表名;
查询所有字段(通配符):select * from 表名;
设置别名:select 字段1 [as 别名1],字段2 [as 别名2] from 表名;
去除重复记录:select distinct 字段列表 from 表名; *号代表查询所有字段在实际开发中尽量少用不直观影响效率 条件查询
条件查询select 字段列表 from 表名 where 条件列表;
比较运算符功能between…and…在某个范围之间含最小值、最大值in(…)在in之后的列表中的值多选一like 占位符模糊匹配_匹配单个字符%匹配任意个字符is null是null
例子表tb_emp中有姓名name,性别gender(1为男2为女),职位job,入职时间entrydate.
(1)查询入职时间在’2000-01-01’(包含)到’2010-01-01’(包含)之间 且 性别为女 的员工信息。 select * from tb_emp where entrydate between 2000-01-01 and 2010-01-01 and gender2;
(2)查询 职位为2(讲师)3(学工主管)4(教研主管)的员工信息。 select * from tb_emp where job 2 or job 3 or job 4; 或 select * from tb_emp where job in (2,3,4);
(3)查询 姓名 为两个字的员工信息。 select * fron tb_emp where name like __;
(4)查询 姓‘张’ 的员工信息 select * from tb_emp where name like 张%;
分组查询
将一列数据作为一个整体进行纵向计算。 select 聚合函数(字段列表) from 表名;
聚合函数功能count统计数量max最大值min最小值avg平均值sum求和 NULL值不参与所有聚合函数运算。 统计数量可以用count(*) count(字段) 例子表tb_emp中有入职时间entrydate.
(1)统计该企业员工数量 select count(*) from tb_emp; (2)统计该企业最晚入职的员工 select max(entrydate) from tb_emp;
分组查询
select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤过滤条件]; 面试题 where与having 的区别 1.执行时机不同where是分组之前进行过滤不满足where条件不参与分组而having是分组之后对结果进行过滤。 2.判断条件不同where不能对聚合函数进行判断而having可以。 分组之后查询的字段一般为聚合函数和分组字段查询其他字段无意义。 执行顺序where聚合函数having 例子表tb_emp中有姓名name,性别gender(1为男2为女),职位job,入职时间entrydate.
(1)根据性别分组统计男性和女性员工的数量 select gender,count(*) from tb_emp group by gender;
(2)先查询入职时间在’2015-01-01’(包含)以前的员工并对结果根据职位分组获取员工数量大于等于2的职位。 select job,count(*) from tb_emp where entrydate 2015-01-01 group by job having count(*) 2;
排序查询
select 字段列表 from 表名 [where 条件列表] [group by 分组字段] order by 字段1 排序方式1,字段2 排序方式2...;
ASC升序默认值 DESC降序 若是多字段排序当第一个字段值相同时才会根据第二个字段进行排序。 例子表tb_emp中入职时间entrydate更新时间update_time
(1)根据 入职时间 对公司的员工进行 升序排序 入职时间相同的再按照 更新时间 进行降序排序。 select * from tb_emp order by entrydate, update_time desc;
分页查询
select 字段列表 from 表名 limit 起始索引,查询记录数; 起始索引从0开始起始索引查询页码-1*每页显示记录数。 分页查询是数据库的方言不同数据库有不用的实现MySQL中是limit. 若查询的是第一页数据起始索引可以省略直接简写成limit10. 例子在表tb_emp中操作
(1)从 起始索引0 开始查询员工数据每页展示5条记录 select * from tb_emp limit 0,5;
(2)查询 第2页 员工数据每页展示5条记录 select * from tb_emp limit 5,5;
多表设计
有一对一一对多多对多
一对多 目前上述的两个表在数据库层面并未建立关联所以是无法保证数据的一致性和完整性的。
外键约束
--创建表时指定
create table 表名(字段名 数据类型...[constraint] [外键名称] foreign key [外键字段名] references 主表(字段名)
);--建完表后添加外键
alter table 表名 add constraint 外键名 foreign key (外键字段名) references 主表(字段名);物理外键使用foreign key定义外键关联另一张表 缺点影响增删改的效率需要检查外键关系仅用于单节点数据库不适用于分布式、集群场景容易引发数据库的死锁问题消耗性能。
逻辑外键在业务层中解决外键关联 (推荐)
一对一
多用于单表拆分将一张表的基础字段放在一张表中其他字段放在另一张表中以提高效率. 实现在任意一方加入外键关联另一方的逐渐并且设置外键为唯一的(unique) 案例用户 与 身份证信息 的关系
多对多
案例学生 与 课程 的关系 关系一个学生可以选修多门课程一门课程也可以供多个学生选择 实现建立第三张中间表中间表至少包含两个外键分别关联两方主键
设计步骤
1分析各个模块涉及到的表结构及表结构之间的关系 2分析各个表结构中的具体字段及约束
多表查询
概述
多表查询从多张表中查询数据 笛卡尔积两个集合的所有组合情况(在多表查询时需要消除无效的笛卡尔积) 分类内连接 和 外连接左外连接和右外连接
内连接
相当于查询A、B交际部分数据
隐式内连接select 字段列表 from 表1,表2 where 条件...; 显式内连接select 字段列表 from 表 [inner] join 表2 on 连接条件;
例子表tb_emp有员工姓名name员工的部门编号dept_id ; 表tb_dept有部门名称name,部门编号id
(1)查询员工的姓名及所属的部门名称隐式内连接实现
select tb_emp.name,tb_dept.name
from tb_em,tb_dept
where tb_emp.dept_idtb_dept.id;-- 起别名
select e.name, d.name
from tb_emp e, tb_dept d
where e.dept_idd.id;(2)查询员工的姓名及所属的部门名称隐式外连接实现
select tb_emp.name,tb_dept.name
from tb_emp
inner join tb_dept on tb_emp.dept_idtb_dept.id;外连接
左外连接select 字段列表 from 表1 [outer] join 表2 on 连接条件; 右外连接select 字段列表 from 表1 [outer] join 表2 on 连接条件;
例子表tb_emp有员工姓名name员工的部门编号dept_id; 表tb_dept有部门名称name,部门编号id
(1)查询员工表 所有 员工的姓名和对应的部门名称左外连接
select e.name,d.name
from tb_emp e
left join tb_dept d on e.dept_idd.id;(2)查询部门表 所有 部门的名称和对应的员工名称右外连接
select e.name,d.name
from tb_emp e
right join tb_dept d on e.dept_idd.id;
--等同于
select e.name,d.name
from tb_dept d
left join tb_emp e on e.dept_idd.id;子查询
又称嵌套查询其外部的语句可以是insert/ update/ delete/ select的任何一个最常见的是select。 select * from t1 where column1 (select column from t2 ...);
分类 (1)标量子查询子查询返回的结果为单个值 (2)列子查询子查询返回的结果为一列 (3)行子查询子查询返回的结果为一行 (4)表子查询子查询返回的结果为多行多列
标量子查询
子查询返回的结果是单个值数字、字符串、日期等最简单的形式 常用的操作符:
例子表tb_dept中有部门名name部门ID id; 表tb_emp中有入职信息entrydate
(1)查询“教研部”所有员工信息
-- a.查询 教研部 的部门ID - tb_dept
select id from tb_dept where name 教研部;
-- b.再查询该部门ID下的员工信息 -tb_emp
select * from tb_emp where dept_id (select id from tb_dept where name 教研部;)(2)查询在“方东白”入职之后的员工信息
-- a.查询 方东白 的入职时间
select entrydate from tb_emp where name 方东白;
-- b.查询在“方东白”入职之后的员工信息
select * from tb_emp where entrydate (select entrydate from tb_emp where name 方东白);列子查询
子查询返回的结果是一列可以是多行 常用的操作符:in、not in等
例子表tb_dept中有部门名name部门ID id; 表tb_emp中有入职信息entrydate
(1)查询“教研部”和“咨询部”的所有员工信息
-- a.查询“教研部”和“咨询部”的部门ID -tb_dept
select id
from tb_dept
where name 教研部 or name 咨询部;-- b.根据部门ID,查询该部门下的员工信息 -tb_emp
select *
from tb_emp
where dept_id in (3,2);合并以上:
select * from tb_emp
where dept_id in (select id from tb_dept where name 教研部 or name 咨询部);行子查询
子查询返回的结果是一行可以是多列 常见的操作符: in not in
例子表tb_dept中有部门名name部门ID id; 表tb_emp中有员工姓名name入职信息entrydate职位编号job
(1)查询与“韦一笑”的入职日期 及 职位都相同的员工信息
-- a.查询“韦一笑”的入职信息 及职位
select entrydate, job
from tb_emp
where name 韦一笑;
-- b.查询与其入职信息 及 职位都相同的员工信息
select *
from tb_emp
where entrydate 2007-01-01 and job 2;
或
select *
from tb_emp
where (entrydate,job) (2007-01-01,2);合并:
select * from tb_emp
where entrydate (select entrydate from tb_emp where name 韦一笑) and job (select job from tb_emp where name 韦一笑 );
或
select * from tb_emp
where (entrydate,job) (select entrydate, job from tb_emp where name 韦一笑);表子查询
子查询返回的结果是多行多列常常作为临时表 常用的操作符:in
例子表tb_dept中有部门名name部门ID id; 表tb_emp中有员工姓名name入职信息entrydate职位job,部门ID dept_id
(1)查询入职时间是2006-01-01之后的员工信息 及其部门名称
-- a.查询入职时间是2006-01-01之后的员工信息
select *
from tb_emp
where entrydate 2006-01-01;
-- b.查询这部分员工信息及其部门名称
select e.*,d.name
from tb_dept e,tb_dept d
where e.dept_idd.id;-- 合并得
select e.*,d.name
from (select * from tb_emp where entrydate 2006-01-01);案例
如下为四张表及其对应关系
(1)查询价格低于 10元 的菜品的名称、价格 及其 菜品的分类名称
select d.name,d.price,c.name
from dish d,category c
where d.category_idc.id and d.price10;(2)查询所有价格在 10元(含)到50元(含)之间 且 状态为“起售”(为1)的菜品展示出菜品的名称、价格 及其 菜品的分类名称即使菜品没有分类也需要将菜品查询出来
select d.name, d.price c.name
from dish d
left join category c on c.id d.category_id
where d.price between 10 and 50
and d.status 1(3)查询每个分类下最贵的菜品展示出分类的名称、最贵的菜品的价格
select c.name,max(d.price)
from dish d,category c
where c.idd.category_id
group by c.name;(4)查询各个分类下 菜品状态为“起售”(为1)并且 该分类下菜品总数量大于等于3 的分类名称
select c.name,count(*)
from category c,dish d
where d.category_id c.id and d.status 1
group by c.name
having count(*)3(5)查询出商务套餐A中包含了哪些菜品展示出套餐名称、价格包含的菜品名称、价格、份数
select s.name, s.price, d.name, d.price, sd.copies
from setmeal s , dish d, setmeal_dish sd
where s.idsd.setmeal_id and sd.dish_id d.id and s.name商务套餐A;(6)查询出低于平均价格的菜品信息(展示出菜品名称、菜品价格)
-- a.查询出平均价格
select avg(price)
from dish;-- b.查询菜品信息
select * from dish
where price (select avg(price) from dish);事务
场景学工部 整个部门都解散了该部门及其部下的员工都需要删除
操作
删除学工部delete from tb_dept where id1;
删除学工部的员工delete from tb_emp where dept_id 1;问题 若删除部门成功了删除该部门员工时失败了就造成了数据的不一致。
概念是一组操作的集合是不可分割的工作单位。 事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求即这些操作要么同时成功要么同时失败
语法
开启事务start transaction; / begin; 提交事务commit 回滚事务rollback
则场景的代码可改为
start transaction;
delete from tb_dept where id1;
delete from tb_emp where dept_id 1;
commit;
rollback;commit类似crtls保存rollback类似ctrlz撤销 注意MySQL的事务是自动提交的即当执行一条DML语句MySQL会立即隐式的提交事务。 四大特性(ACID)
原子性(Atomicity)不可分割的最小单元要么全部成功要么全部失败 一致性(Consistency)事务完成时必须使所有的数据都保持一致状态 隔离性(Isolation)在不受外部并发操作影响的独立环境下运行 持久性(Durability)一旦提交或回滚对数据库中的数据的改变时永久的。
索引
概念帮助数据库高效获取数据 的 数据结构
优点提高数据查询的效率降低数据库的IO成本通过索引对数据进行排序降低数据排序的成本降低CPU消耗 缺点索引会占用存储空间大大提高了查询效率同时也降低了insert,update,delete的效率 空间换时间 结构
一般是指B Tree结构组织的索引。 1.每个节点可以存储多个key(有n个key就有n个指针) 2.所有的数据都存储在叶子节点非叶子节点仅用于索引数据。 3.叶子节点形成了一颗双向链表便于数据的排序及区间范围查询。
语法
创建索引create [unique] index 索引名 on 表名(字段名...); 查看索引show index from 表名; 删除索引drop index 索引名 on 表名; 注意 主键字段在建表时会自动创建主键索引 添加唯一约束时数据库实际上会添加唯一索引 例子 为tb_emp表的name字段建立一个索引 create index idx_emp_name on tb_name(name);
