网站开发脚本语言和数据库,成都设计院工资,湖州网站建设,昆山网络推广公司数据库#xff1a; MySQL数据模型#xff1a; MySQL是关系型数据库。 SQL#xff1a;
简介 分类#xff1a; 数据库设计-DDL
对数据库操作#xff1a; 表操作#xff1a; 小练习#xff1a; 创建下表 SQL代码#xff1a;
create table tb_user
(id int primar…数据库 MySQL数据模型 MySQL是关系型数据库。 SQL
简介 分类 数据库设计-DDL
对数据库操作 表操作 小练习 创建下表 SQL代码
create table tb_user
(id int primary key auto_increment 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(-128127)(0255)小整数值smallint2(-3276832767)(065535)大整数值mediumint3(-83886088388607)(016777215)大整数值int4(-21474836482147483647)(04294967295)大整数值bigint8(-2^632^63-1)(02^64-1)极大整数值float4(-3.402823466 E383.402823466351 E38)0 和 (1.175494351 E-383.402823466 E38)单精度浮点数值float(5,2)5表示整个数字长度2 表示小数位个数double8(-1.7976931348623157 E3081.7976931348623157 E308)0 和 (2.2250738585072014 E-3081.7976931348623157 E308)双精度浮点数值double(5,2)5表示整个数字长度2 表示小数位个数decimal小数值(精度更高)decimal(5,2)5表示整个数字长度2 表示小数位个数
字符串类型
分类类型大小描述字符串类型char0-255 bytes定长字符串char(10): 最多只能存10个字符,不足10个字符,占用10个字符空间AB性能高浪费空间varchar0-65535 bytes变长字符串varchar(10): 最多只能存10个字符,不足10个字符, 按照实际长度存储ABC性能低节省空间tinyblob0-255 bytes不超过255个字符的二进制数据tinytext0-255 bytes短文本字符串blob0-65 535 bytes二进制形式的长文本数据text0-65 535 bytes长文本数据phone char(11)mediumblob0-16 777 215 bytes二进制形式的中等长度文本数据username varchar(20)mediumtext0-16 777 215 bytes中等长度文本数据longblob0-4 294 967 295 bytes二进制形式的极大文本数据longtext0-4 294 967 295 bytes极大文本数据
日期时间类型
分类类型大小(byte)范围格式描述日期类型date31000-01-01 至 9999-12-31YYYY-MM-DD日期值time3-838:59:59 至 838:59:59HH:MM:SS时间值或持续时间year11901 至 2155YYYY年份值datetime81000-01-01 00:00:00 至 9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值timestamp41970-01-01 00:00:01 至 2038-01-19 03:14:07YYYY-MM-DD HH:MM:SS混合日期和时间值时间戳
设计表流程 根据下面需求设计表 SQL代码
CREATE TABLE tb_emp (id int NOT NULL AUTO_INCREMENT COMMENT ID,username varchar(20) NOT NULL COMMENT 用户名,password varchar(32) DEFAULT 123456 COMMENT 密码默认123456,name varchar(10) NOT NULL COMMENT 员工姓名,gender tinyint unsigned NOT NULL COMMENT 性别 1:男 2:女,image varchar(30) DEFAULT NULL COMMENT 图像,position tinyint unsigned DEFAULT NULL COMMENT 职位 1:班主任 2:讲师 3:学工主管 4:校验主管,joined_time date DEFAULT NULL COMMENT 入职日期,create_time datetime NOT NULL COMMENT 创建日期,update_time datetime NOT NULL COMMENT 更新日期,PRIMARY KEY (id),UNIQUE KEY tb_emp_pk_2 (username)
) ENGINEInnoDB AUTO_INCREMENT10 DEFAULT CHARSETutf8mb4 COLLATEutf8mb4_0900_ai_ci
数据库操作
DML 添加数据 注意事项 代码演示
-- 为tb_emp表的usernamenamegender字段插入值
insert into tb_emp(username,name,gender,create_time,update_time) values(han,han,1,now(),now());-- 为tb_emp表的所有字段插入值
insert into tb_emp values(null,jq,123,han,1,1.jpg,1,2000-10-10,now(),now());-- 批量为tb_emp表的usernamenamegender字段插入值
insert into tb_emp(username,name,gender,create_time,update_time) values(ma,han,1,now(),now()),(zhao,han,1,now(),now());
更新数据 注意事项 代码演示
-- 将tb_emp表的ID为1员工的姓名name字段更新为‘张三’
update tb_emp
set name 张三,update_time now()
where id 1;-- 将tb_emp表的所有员工的入职日期更新为‘2010-01-01’
update tb_emp
set joined_time2010-01-01,update_time now();
删除操作 注意事项 代码演示
-- 删除tb_emp表中ID为1的员工
delete
from tb_emp
where id 1;-- 删除tb_emp表中的所有员工
delete
from tb_emp; DQL 基本查询 注意事项 代码演示
-- 查询指定字段nameentrydate 并返回
select name, entrydate
from tb_emp;-- 查询所有字段并返回
-- 推荐
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp;
-- 不推荐
select *
from tb_emp;-- 查询所有员工的nameentrydate 并起别名姓名入职日期
select name as 姓名, entrydate as 入职日期
from tb_emp;-- 查询已有的员工关联了哪几种职位不要重复
select distinct job
from tb_emp; 条件查询 运算符 代码演示
-- 查询姓名为杨逍的员工
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name 杨逍;-- 查询id小于等于5的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where id 5;-- 查询没有分配职位的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where job is null;-- 查询有职位的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where job is not null;-- 查询密码不等于123456的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where password ! 123456;-- 查询入职日期在2000-01-01(包含)到2010-01-01(包含)之间的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where entrydate between 2000-01-01 and 2010-01-01;-- 查询入职日期在2000-01-01(包含)到2010-01-01(包含)之间且性别为女的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where entrydate between 2000-01-01 and 2010-01-01and gender 2;-- 查询职位是2讲师3学工主管4校验主管的员工信息
-- 第一种写法
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where job 2 or job 3 or job 4;
-- 第二种写法
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where job in (2,3,4);-- 查询姓名为两个字的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name like __;-- 查询姓张的员工信息
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name like 张%; 分组查询 这里先讲解聚合函数再讲解分组查询。
聚合函数 注意事项 代码演示
-- 统计该企业员工的数量
-- 1.count(字段)
select count(id)
from tb_emp;
-- 2.count(常量)
select count(1)
from tb_emp;
-- 3.count(*)--推荐
select count(*)
from tb_emp;-- 统计该企业最早入职的员工
select min(entrydate)
from tb_emp;-- 统计该企业最迟入职的员工
select max(entrydate)
from tb_emp;-- 统计该企业员工ID的平均值
select avg(id)
from tb_emp;-- 统计该企业员工的ID之和
select sum(id)
from tb_emp;
分组查询 注意事项 代码演示
-- 根据性别分组统计男性和女性员工的数量
select gender,count(*)
from tb_emp
group by gender;-- 先查询入职时间在2015-01-01包含以前的员工并对结果根据职位分组获取员工数量大于等于2的职位
select job,count(*)
from tb_emp
where entrydate 2015-01-01
group by job
having count(*) 2;
★where与having的区别 1.执行时机不同where是分组前进行过滤不满足where条件不参与分组而having是分组之后对结果进行过滤。 2.判断条件不同where不能对聚合函数进行判断而having可以。 排序查询 排序方式 注意事项 代码演示
-- 根据入职时间对员工进行升序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate;-- 根据入职时间对员工进行降序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate DESC;-- 根据入职时间对公司的员工进行升序排序入职时间相同再按照更新时间进行降序排序
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
order by entrydate, update_time DESC;分页查询 注意事项 代码演示
-- 从起始索引0开始查询员工数据每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 0,5;-- 查询第一页员工数据每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 0,5;-- 查询第二页员工数据每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 5,5;-- 查询第三页员工数据每页展示5条记录
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
limit 10,5;
案例 下面给出三个案例案例中还用到了这两个函数
if函数和case函数 代码演示
-- 查找 名字里有张性别男入职日期在2000-01-01到2015-12-31之间的员工
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp
where name like %张%and gender 1and entrydate between 2000-01-01 and 2015-12-31
order by update_time DESC
limit 10,10;-- 员工性别统计
select if(gender 1, 男性员工, 女性员工) as 性别,count(*)
from tb_emp
group by gender;-- 员工职位统计
select(case job when 1 then 班主任 when 2 then 讲师 when 3 then 学工主管when 4 then 教研主管 else 未分配职位 end) 职位,count(*)
from tb_emp
group by job; 多表设计
一对多 多表问题分析 外键约束 一对一 多对多 小结 多表查询 从多张表中查询数据
笛卡尔积 直接在from后加入多个表会出现这多个表的笛卡尔积个数据在多表查询时需要消除无效的笛卡尔积。 连接方式 内连接 代码演示
-- 查询员工的姓名及所属的部门名称隐式内连接实现
select tb_emp.name, tb_dept.name
from tb_emp,tb_dept
where tb_emp.dept_id tb_dept.id;
-- 起别名 --简洁
select e.name,d.name
from tb_dept d,tb_emp e
where d.id e.dept_id;-- 查询员工的姓名及所属的部门名称显式内连接实现
select tb_emp.name, tb_dept.name
from tb_empjoin tb_depton tb_emp.dept_id tb_dept.id;
外连接 代码演示
-- 查询员工表所有员工的姓名和对应的部门名称左外连接
select e.name,d.name
from tb_emp eleft join tb_dept don e.dept_id d.id;-- 查询部门表所有部门名称和对应的员工的姓名右外连接
select e.name,d.name
from tb_emp eright join tb_dept don e.dept_id d.id;
子查询 标量子查询 代码演示
-- 查询教研部的所有员工信息
select *
from tb_emp
where dept_id (select idfrom tb_deptwhere name 教研部);-- 查询在方东白入职之后的员工信息
select *
from tb_emp
where entrydate (select entrydatefrom tb_empwhere name 方东白);
列子查询 代码演示
-- 查询教研部和咨询部的所有员工信息
select *
from tb_emp
where dept_id in (select idfrom tb_deptwhere name in (教研部, 咨询部));
行子查询 代码演示
-- 查询与韦一笑的入职日期及职位都相同的员工信息
select *
from tb_emp
where (entrydate, job) (select entrydate, jobfrom tb_empwhere name 韦一笑);
表子查询 代码演示
-- 查询入职日期是2006-01-01之后的员工信息及其部门名称
select e.*, d.name
from (select *from tb_empwhere entrydate 2006-01-01) e,tb_dept d
where e.dept_id d.id;
案例
-- 1. 查询价格低于 10元 的菜品的名称 、价格 及其 菜品的分类名称 .
select d.name,d.price,c.name
from dish d,category c
where d.category_id c.idand d.price 10;-- 2. 查询所有价格在 10元(含)到50元(含)之间 且 状态为起售的菜品名称、价格 及其 菜品的分类名称 (即使菜品没有分类 , 也需要将菜品查询出来).
select d.name,d.price,c.name
from dish dleft join category con d.category_id c.id
where d.price between 10 and 50and d.status 1;-- 3. 查询每个分类下最贵的菜品, 展示出分类的名称、最贵的菜品的价格 .
select c.name, max(d.price)
from category c,dish d
where c.id d.category_id
group by c.id;-- 4. 查询各个分类下 状态为 起售 , 并且 该分类下菜品总数量大于等于3 的 分类名称 .
select c.name
from category c,dish d
where c.id d.category_idand d.status 1
group by c.name
having count(*) 3;-- 5. 查询出 商务套餐A 中包含了哪些菜品 展示出套餐名称、价格, 包含的菜品名称、价格、份数.
select s.name, s.price, d.name, d.price, sd.copies
from dish d,setmeal_dish sd,setmeal s
where d.id sd.dish_idand sd.setmeal_id s.idand s.name 商务套餐A;-- 6. 查询出低于菜品平均价格的菜品信息 (展示出菜品名称、菜品价格).
select d.name, d.price
from dish d
where d.price (select avg(d.price)from dish d); 事务
使用场景 介绍 操作 ★事物的四大特性ACID 索引 优缺点 索引的缺点到目前影响已经很小因为第一点企业的磁盘空间很大索引占用的空间与之相比很小第二点数据查询业务基本占总体业务的百分之九十所以降低增删改效率影响也不大。
结构 思考为什么索引结构不采用二叉搜索树和红黑树 答因为在大数据情况下树的层次深检索速度慢
BTree(多路平衡搜索树) 特点 代码演示
-- 创建为tb_emp表的name字段建立一个索引
create index idx_emp_name on tb_emp(name);-- 查询查询tb_emp表的索引信息
show index from tb_emp;-- 删除删除tb_emp表中的name字段的索引
drop index idx_emp_name on tb_emp;
注意事项 主键索引性能最高
