网站所有页面只显示域名,移动商城网站开发,代理记账一般多少钱一个月,公司介绍网页模板SQL简介
一门操作关系型数据库的编程语言#xff0c;定义操作所有关系型数据库的统一标准
通用语法#xff1a;
可以单行或者多行书写#xff0c;以分号结尾#xff1b;
可以使用空格 /
SQL语句可以使用空格/缩进来增强语句的可读性。 MySQL数据库的SOL语句不区分大小…SQL简介
一门操作关系型数据库的编程语言定义操作所有关系型数据库的统一标准
通用语法
可以单行或者多行书写以分号结尾
可以使用空格 /
SQL语句可以使用空格/缩进来增强语句的可读性。 MySQL数据库的SOL语句不区分大小写。
SOL语句通常被分为四大类:
分类说明数据定义语言 (DDL)用来定义数据库对象(数据库表字段)数据操作语言 (DML)用来对数据库表中的数据进行 增 删 改数据查询语言 (DQL)用来查询数据库中表的记录数据控制语言 (DCL)用来创建数据库用户、控制数据库的访问权限
注释
1、单行注释
-- 注释内容 或 #注释内容(MySQL特有)2、多行注释 /*注释内容 */3、SQL语句内注释
字段 字段类型 [约束][comment 字段1注释],数据库设计
字段数量 该表 包含什么字段 原型字段业务需求 基础字段如id、create_time、update_time表设计 数据类型、字段长度、约束、注释数据类型的选择在业务允许的情况下只要满足需求尽可能选择占用磁盘空间少的数据类型
CREATE TABLE 表名 (column1 datatype comment ID,唯一标示,列/字段名 数据类型 [约束] [comment 字段注释],...(最后一行语句不要逗号)
) [comment 表注释] ;多表设计
根据业务需求以及业务模块中之间关系分析并设计表结构设计
表结构联系分为一对一、一对多、多对多
设计步骤看业务需求理清各表之间的关系、设计单表加对应外键 设计多表
一对一
例如用户 与 身份证信息 的关系
解决多用于单表拆分 将一张表的基础字段放在一张表中其他字段放在另一张表以提升效率
实现
在任意一方加入外键关联另外一方的主键并且设置外键为唯一的(UNIQUE)
示例 身份证信息表 加入 用户信息表 主键ID 作为外键
user id int unsigned not null unique comment 用户ID,
constraint fk_user_id foreign key (user_id) references tb user(id)一对多
例如部门 与 员工 就是一对多关系一个部门有多个员工部门为父表 员工为子表
实现
1.1 字段关联在 员工表(多) 中添加 部门字段(一关联部门的 ID 主键
1.2 外键约束保持数据完整性和一致性
语法
1、创建表时指定
字段名 数据类型 ... [constraint关键字][指定外键名称] foreign key (外键字段名) references 主表(字段名)
示例
create table students ( id int primary key, name varchar(255), class_id int, foreign key (class_id) references classes(id)
);2、建完表后添加外键
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(字段名);物理外键使用foreign key关联表为物理外键
缺点较多
影响增、删、改的效率(需要检查外键关系)仅用于单节点数据库不适用与分布式、集群场景。容易引发数据库的死锁问题消耗性能。
逻辑外键 在业务层逻辑中解决外键关联。
现在 更推荐 的一种方式
通过逻辑外键就可以很方便的解决上述问题。
例如:
在Java中 员工类 和部门类员工中有部门外键
通过 在员工类中定义部门属性并在方法中实现 方法来获取对应的 部门对象 并得到对应部门ID多对多
例如学生 与 课程的关系一个学生可以选修多门课程一门课程也可以供多个学生选择
**实现**建立第三张中间表中间表至少包含两个外键分别关联两方主键
示例 关联的两个外键分别为 学生id、课程id; 即可解决每个 学生id 与 课程id的一一对应关系
CREATE TABLE tb_student_course ( id INT AUTO_INCREMENT PRIMARY KEY COMMENT 主键, student_id INT NOT NULL COMMENT 学生ID, course_id INT NOT NULL COMMENT 课程ID, CONSTRAINT fk_courseid FOREIGN KEY (course_id) REFERENCES tb_course (id), CONSTRAINT fk_studentid FOREIGN KEY (student_id) REFERENCES tb_student (id)
) COMMENT 学生课程中间表;
字段类型数据库
总介绍
下面的表格展示了常见的数据库数据类型及其在Java中的对应类型并附上每种数据类型的说明
数据库类型Java类型说明备注charString固定长度的字符串varcharString可变长度的字符串textString大文本数据intint整数integerint整数tinyintbyte小整数smallintshort小整数mediumintint中等大小整数bigintlong大整数floatfloat单精度浮点数float(5,2)5表示整个数字长度2 表示小数位个数doubledouble双精度浮点数double(5,2)5表示整个数字长度2 表示小数位个数decimaljava.math.BigDecimal高精度定点数decimal(5,2)5表示整个数字长度2 表示小数位个数numericjava.math.BigDecimal高精度定点数datejava.sql.Date日期不包含时间部分timejava.sql.Time时间不包含日期部分timestampjava.sql.Timestamp日期和时间包含纳秒精度datetimejava.sql.Timestamp日期和时间booleanboolean布尔值真/假blobbyte[]二进制大对象clobjava.sql.Clob字符大对象
常用数据类型主要分为三类
1、数值类型
类型名大小(byte)有符号(SIGNED)范围无符号(UNSIGNED)范围描述tinyint1(-128127)(0255)小整数值smallint2(-3276832767)(065535)大整数值mediumint3(-83886088388607)(016777215)大整数值int4(-21474836482147483647)(04294967295)大整数值bigint8(-263263-1)(02^64-1)极大整数值float4(-3.402823466 E383.402823466351 E38)0 和 (1.175494351 E-383.402823466 E38)单精度浮点数值double8(-1.7976931348623157 E3081.7976931348623157 E308)0 和 (2.2250738585072014 E-3081.7976931348623157 E308)双精度浮点数值decimal小数值(精度更高)
说明
1、默认为有符号范围加关键字unsigned表示无符号范围
如
age tinyint 表示有符号范围(-128127)
age tinyint unsigned 表示无符号范围 (0255)2、字符串类型
类型名称大小描述适用场景char0-255 bytes定长字符串varchar0-65535 bytes变长字符串tinyblob0-255 bytes不超过255个字符的二进制数据视频、音频适合用blob后缀数据类型但是实际应用中一般会存储在服务器中所以很少用tinytext0-255 bytes短文本字符串text后缀适合文本blob0-65 535 bytes二进制形式的长文本数据text0-65 535 bytes长文本数据mediumblob0-16 777 215 bytes二进制形式的中等长度文本数据mediumtext0-16 777 215 bytes中等长度文本数据longblob0-4 294 967 295 bytes二进制形式的极大文本数据longtext0-4 294 967 295 bytes极大文本数据
说明
1、定长 、 变长
如
定长char(10): 最多只能存10个字符,不足10个字符(其余会用空字符占位),占用10个字符空间 性能高 浪费空间
变长varchar(10): 最多只能存10个字符,不足10个字符, 按照实际长度存储 性能低 节省空间3、日期时间类型
类型大小(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混合日期和时间值时间戳
一、数据库
创建数据库 create
create database 数据库; 数据库带符号时用反引号即可如create database test;;
使用数据库 use
use database 数据库名;查询数据库 show
查询所有数据库show databases; 查询当前数据库select database();
删除数据库 drop
drop database 数据库;二、数据表
约束
概念约束是作用于表中字段上的规则用于限制存储在表中的数据
MySQL主要提供以下约束
约束类型描述关键字非空约束限制该字段值不能为nullnot null唯一约束保证字段的所有数据都是唯一、不重复的unique主键约束主键是一行数据的唯一标识要求非空且唯一primary key auto_increment 自增默认约束保存数据时如果未指定该字段值则采用默认值default外键约束让两张表的数据建立连接保证数据的一致性和完整性foreign key
示例
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 用户表;创建表 / 字段 create
CREATE TABLE 表名 (column1 datatype comment ID,唯一标示,列/字段名 数据类型 [约束] [comment 字段注释],...(最后一行语句不要逗号)
) [comment 表注释] ;添加字段
alter table 表名 add 字段名 类型(长度)[comment 注释][约束];示例
CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY comment ‘ID,唯一标示’, username VARCHAR(50) NOT NULL, email VARCHAR(100) NOT NULL, birthdate DATE, is_active BOOLEAN DEFAULT TRUE ) comment ‘用户表’;
查询表 / 结构 show
查询当前数据库所有表show tables查询表的结构desc 表名;查询建表语句show create table 表名;
删除表 / 字段 drop
drop table 表名;
drop column 字段名;修改表 / 字段 alter
修改表名: rename table 表名 to 新表名,
修改字段类型alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型alter table 表名 change 旧字段名 新字段名 类型(长度)[comment 注释][约束];三、操作数据
插入数据 insert
INSERT INTO 表名 (column1, column2, 列名3, ...) VALUES (value1, value2, 数值3, ...);示例
INSERT INTO users (username, email, birthdate, is_active)
VALUES (test, testrunoob.com, 1990-01-01, true);一次插入多条数据
INSERT INTO users (username, email, birthdate, is_active) VALUES
(test, testrunoob.com, 1990-01-01, true)(test2, testrunoob.com, 1990-01-01, true);删除数据 delect
删除表中 所有行 / 指定条件的行
DELETE FROM 表名
WHERE 条件;示例
删除指定行
DELETE FROM students WHERE graduation_year 2021;
修改数据 update
UPDATE 表名
SET column1 value1, 列名2 数值2, ...
WHERE 条件;示例
UPDATE employees SET name ‘张三’ , salary 60000 WHERE employee_id 101;
四、查询数据 select
基本查询
查询返回所有字段 不推荐该方法(不直观性能低需要什么查什么)
select * from 表名;查询多个(指定) 字段
select 字段1,字段2,字段3 from 表名;起别名
字段起别名查询(指定) 字段并将字段名以别名形式返回
select 字段1 [as 别名1],字段2 [as 别名2] from 表名;如
select name as 姓名,entrydate as 入职日期 from tb_emp;
1、as关键字可以省略 2、别名中有空格、特殊符号时可以加单引号
select name 姓 名,entrydate 入职日期 from tb_emp; 表起别名
化简查询语句中重复出现较长的表名
示例select * from tb_emp e;去除重复记录查询
select distinct 字段列表 from 表名;条件查询(where)
语法
条件查询select 字段列表 from 表名 where 条件列表;SQL中构建条件的运算符
逻辑运算符功能and 或 并且(多个条件同时成立)or 或 ||或者(多个条件任意一个成立)not 或 !非 不是
比较运算符功能大于大于等于小于小于等于等于!不等于between…and…在某个范围之内(含最小、最大值)in(…)在in之后的列表中的值多选一like模糊匹配 ( _ 匹配单个字符 % 匹配任意个字符 )is null是nullis not null不是null
分组查询(group by)
select 字段列表 聚合函数 from 表名列表 [where 条件列表] group by 分组字段列表 [having 分组后过滤条件];示例
1.根据性别分组 统计男性和女性员工的数量 - count(*)
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;where 与 having区别
1、执行时机不同:where是分组之前进行过滤不满足where条件不参与分组;而having是分组之后对结果进行过滤。
2、判断条件不同:where不能对聚合函数进行判断而having可以。
△注意事项
分组之后查询的字段一般为聚合函数和分组字段查询其他字段无任何意义
执行顺序where 聚合函数having
聚合函数
介绍:将一列数据作为一个整体进行纵向计算。
语法select 聚合函数(字段列表) from 表名;函数功能count统计数量max最大值min最小值avg平均值sum求和
注意 聚合函数不对 null 值进行运算如 统计 需选择非空字段
示例 统计企业员工数量
方法1count(字段)select count(id) from tb_emp;
方法2count(常量)select count(A) from tb_emp;
方法3count(*) 推荐(MySQL做了专门优化)select count(*) fromth tb_emp;排序查询(order by)
select 字段列表 from 表名 [where 条件列表] [group by 分组字段] [order by 字段1 排序方式1, 字段2 排序方式2, ...];排序方式
ASC升序(默认值) DESC降序
示例
--1.根据入职时间对员工进行降序排序-desc
select * from tb_emp order by entrydate DESC;分页查询(limit)
语法 select 字段列表 from 表名 limit 起始索引,查询记录数; 示例
--1.从 起始索引0 开始查询员工数据每页展示5条记录
select * from tb_emp limit 0,5;△注意事项
起始索引从 0 开始
分页查询是数据库的方言不同的数据库有不同的实现MySQL中是LIMIT
起始索引 与 页码 间计算公式
起始索引 (页码-1) * 每页展示记录数
示例
每页为5条数据的页面第3页起始索引为 1010 3-1* 5多表查询
普通查询两张表展示的结果会是笛卡尔积
如select * from tb_emp,tb_dept笛卡尔积 A 集合 和 B 集合的所有组合情况
实现 指定连接条件 即根据需求消除无效的笛卡尔积
指定连接条件 员工表中部门id 与 部门表id 一样的 组合
select * from tb_emp,tb_dept where tb_emp.dept_id tb_dept.id;多表查询两大类连接查询、子查询
连接查询 内连接相当于查询A、B**两张表交集部分**有关联数据 语法、可读性、使用场景不同的两种语法
隐式内连接select 字段列表 from表1, 表2 where 条件…;
显式内连接select 字段列表 from 表1 [inner] join 表2 on 连接条件 …; 更推荐显式隐式只适合简单查询场景 外连接 左外连接查询左表所有数据(包括两张表交集部分数据) 更常用右外连接查询右表所有数据(包括两张表交集部分数据) 左外连接 select 字段列表 from 表1 left [outer] join 表2 on 连接条件...;
右外连接 select 字段列表 from 表1 right [outer] join 表2 on 连接条件...;子查询
能用连接查询替代子查询就用连接查询执行效率、查询计划的优化有优势
SOL语句中嵌套select语句称为嵌套查询又称子查询
形式select * from tA where column1 ( select columnl from tB …);
子查询外部的语句可以是 insert / update / delete / select 的任何一个最常见的是 select。
方法把查询拆分先写好一个查询在把这个查询当做一个子查询通过 修改子查询 嵌入另一个查询作为一个结果
根据返回结果将子查询分类
标量子查询子查询返回的结果为单个值
select from tb_emp where dept_id (select id from tb_dept where name 教研部);列子查询子查询返回的结果为一列
查询教研部”和咨询部”的所有员工信息
select from tb_emp where dept_id in (select id from tb_dept where name 教研部 or name 咨询部);行子查询子查询返回的结果为一行
査询与 “韦一笑”的入职日期及职位都相同的员工信息
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 韦一笑);表子查询子查询返回的结果为多行多列常作为临时表
査询入职日期是2006-01-01”之后的员工信息及其部门名称
五、数据库优化
索引最有效
需求产生当表中数据越大查询效率越低可以通过建立索引来提高查询效率
概念 索引(index)是帮助数据库 高效获取数据 的 数据结构
优缺点 优点 1、提高数据【查询】的效率降低数据库的I0成本。 2、通过索引列对数据进行排序降低数据【排序】的成本降低CPU消耗。 缺点 (在优点面前以下缺点已经可以忽略) 1、索引会占用【存储空间】。 2、索引大大提高了查询效率同时却也降低了 insert、update、delete 的效率。(因为进行这些操作后又要重新维护 查询所需的数据结构 )
注意事项为什么可以忽略缺点——现在的企业服务器的磁盘空间足够大 在实际业务中查询占80%以上
语法
创建索引
create [unique] index 索引名 on 表名(字段名,字段名2... ); (可同时为 多个字段或仅一个字段建立)查看索引
show index from 表名;删除索引
drop index 索引名 on 表名;示例 为 表tb_sku 中 sn 字段建立索引
create index idx_sku_sn on tb_sku(sn);注意事项 表中的主键、带唯一约束的字段 都会分别自带 主键索引、 唯一索引
结构
MySQL数据库支持的索引结构有很多如Hash索引、BTree索引、Full-Text索引等。我们平常所说的索引如果没有特别指明都是指默认的 BTree多路平衡搜索树结构组织的索引。
B树 特点高效自平衡排序树数据全存叶子索引分层管理支持快速查找与范围遍历。
SQL优化
分库分表
六、常见其他问题
事务
事务 是一组操作的集合它是一个不可分割的工作单位。这些 操作要么同时成功要么同时失败
事务四大特性
原子性事务是不可分割的最小单元要么全部成功要么全部失败
一致性事务完成时必须使所有的数据都保持一致状态
隔离性数据库系统提供的隔离机制保证事务在不受外部并发操作影响
持久性事务一旦提交或回滚它对数据库中的数据的改变就是永久的的独立环境下运行
场景示例学工部 整个部门解散了该部门及部门下的员工都需要删除了
正常操作分两个单独的步骤删除学工部删除学工部的员工可能出现异常数据不一致
实现
开启事务start transaction; / begin; 提交事务commit; 回滚事务rollback;
示例
-- 开启事务
start transaction-- 删除部门
delete from tb dept where id 3;
-- 删除部门下的员工
delete from tb emp where dept id3;-- 提交事务
commit;
-- 回滚事务 一部分操作失败时执行回滚事务可以让先前的操作被取消 包含该事务中成功和失败的操作
rollback ;MySQL 处理重复数据
可以在 MySQL 数据表中设置指定的字段为 PRIMARY KEY主键 或者 UNIQUE唯一 索引来保证数据的唯一性
函数
MySQL中提供了一些便捷的函数
1、系统时间 now()
insert into tb emp (username, create time, update time) values (‘wuji’, now(),now() );
2、流程控制函数 if
if(条件表达式表达式为true时的取值表达式为false时的取值)示例
select if(gender 1, 男性员工,女性员工),count(*) from tb_emp group by gender;3、流程控制函数 case
case 表达式 when 值l then 结果1 when 值2 then 结果2 ... else ... end示例
select(case job when 1 then 班主任 when 2 then 讲师 else 未分配职位 end)count(*)
from tb_emp group by job;
