MySql基础-单表操作

1. MYSQL概述

1.1 数据模型

关系型数据库

关系型数据库(RDBMS)：建立在关系模型基础上，由多张相互连接的二维表组成的数据库。

特点：

使用表存储数据，格式统一，便于维护

使用SQL语言操作，标准统一，使用方便，可用于复杂查询

MYSQL数据模型

1.2  SQL简介

SQL：一门操作关系型数据库的编程语言，定义操作所有关系型数据库的统一标准。

通用语法：

SQL语句可以单号或多行书写，以分号结尾。

SQL语句可以使用空格/缩进来增强语句的可读性。

MYSQL数据库的SQL语句不区分大小写。

注释：

1. 单行注释：-- 注释内容 或 # 注释内容(MYSQL特有)

2. 多行注释：/* 注释内容 */

 SQL分类

分类	全称	说明
DDL	Data Definiton Language	数据定义语言，用来定义数据库对象(数据库，表，字段)
DML	Data Manipulation Language	数据操作语言，用来对数据库表中的数据进行增删改
DQL	Data Query Language	数据查询语言，用来查询数据库中表的记录
DCL	Data Control Language	数据控制语言，用来创建数据库用户、控制数据库的访问权限

2. 数据库设计

2.1 DDL

DDL 英文全称是 Data Definition Language，数据定义语言，用来定义数据库对象(数据库、表)。

2.1.1 数据库操作

查询

查询所有数据库：show databases;

查询当前数据库：select database();

创建

创建数据库：create database [if not exists] 数据库名；

使用

使用数据库：use 数据库名；

创建

创建数据库：create database [if not exists] 数据库名；

删除

删除数据库：drop database [if exists] 数据库名；

注意： 上述语法中的database ，也可以替换称schema。如：create schema db01;

---

MYSQL客户端工具 - 图形化工具

DataGrip
介绍：DataGrip 是JetBrains 旗下的一款数据库管理工具，是管理和开发MySQL、Oracle、PostgreSQL 的理想解决方案。

官网：DataGrip: The Cross-Platform IDE for Databases & SQL by JetBrains

2.1.2 表的操作(创建、查询、修改、删除)

创建

create table tb_user (id int comment 'ID, 唯一标识',username varchar(20) comment '用户名',name varchar(10) comment '姓名',age int comment '年龄',gender char(1) comment '性别'
) comment '用户表';

 约束

概念：约束是作用于表中字段上的规则，用于限制存储在表中的数据。

目的：保证数据库中数据的正确性、有效性和完整性。

约束	描述	关键字
非空约束	限制该字段值不能为null	not 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 '用户表';

数值类型 

查询

查询当前数据库所有表: show tables;

查询表结构：desc 表名；

查询建表语句：show create table 表名；

修改

添加字段：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束]；

修改字段类型：alter table 表名 modify 字段名 新数据类型(长度)；

修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型(长度) [comment 注释] [约束]；

删除字段：alter table 表名 drop column 字段名；

修改表名：rename table 表名 to 新表名；

删除

删除表：drop table [if exists] 表名；

2.2 DML

DML 英文全称是Data Manipulation Language(数据操作语言)，用来对数据库中表的数据记录惊醒增、删、改操作。

添加数据(INSERT)

指定字段添加数据：insert into 表名(字段名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. 插入的数据大小，应该在字段的规定范围内。

修改数据(UPDATE)

Update 语法

修改数据：update 表名 set 字段名1 = 值1，字段名2 = 值2，.......[where 条件];

注意事项

修改语句的条件可以有，也可以没有，如果没有条件，则会修改整张表的所有数据。

删除数据(DELETE)

delete语法

删除数据：delete from 表名 [where 条件]；

注意事项

1. DELETE 语句的条件可以有，也可以没有，如果没有条件，则会删除整张表的所有数据。

2. DELETE 语句不能删除某一个字段的值(如果要操作，可以使用UPDATE，将该字段的值置为NULL)。

2.2DQL

DQL英文全称是Data Query Language (数据查询语言)，用来查询数据库表中的记录。

关键字：SELECT

语法

DQL-基本查询

查询多个字段：select 字段1，字段2，字段3 from 表名；

查询所有字段(通配符)：select * from 表名；

设置别名：select 字段1 [as 别名1]，字段2 [as 别名2] from 表名；

去除重复记录：select distinct 字段列表 from 表名；

注意

* 号代表查询所有字段，在实际开发中尽量少用(不直观、影响效率)。

-- 查询指定字段 name，entrydate 并返回
select name as 姓名,entrydate as 入职日期
from tb_emp;
-- 查询返回所有字段
-- 推荐
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp;
-- 不推荐(不直观，性能低)
select *
from tb_emp;
-- 查询所有员工的 name,entrydate，并起别名(姓名，入职日期);
select name as 姓名,entrydate as 入职日期
from tb_emp;
select name as '姓 名',entrydate as 入职日期
from tb_emp;
-- 查询已有的员工关联了哪些职位(不重复)
select distinct job
from tb_emp;

 DQL-条件查询

select 字段列表 from 表名 where 条件列表；

 

-- =====================================DQL：条件查询 ===============================
-- 1. 查询姓名为 杨逍 的员工
select id, username, password, name, gender, image, job, entrydate, create_time, update_time
from tb_emp where name = '杨逍';
-- 2. 查询 id小于等于5的员工信息
select *
from tb_emp where id <= 5;
-- 3. 查询没有分配职位的员工信息
select *
from tb_emp where job is null;
-- 4. 查询有职位的员工信息
select *
from tb_emp where job is not null;
-- 5. 查询密码不等于'123456' 的员工信息
select *
from tb_emp where password != 123456;
-- 6. 查询入职日期在 '2000-01-01' (包含) 到 '2010-01-01'(包含)之间的员工信息
select *
from tb_emp where entrydate >= '2000-01-01' && entrydate <= '2010-01-01';select *
from tb_emp where entrydate between '2000-01-01' and '2010-01-01';
-- 7. 查询入职日期在 '2000-01-01' (包含) 到 '2010-01-01'(包含)之间 且性别为女的员工信息
select *
from tb_emp where entrydate >= '2000-01-01' && entrydate <= '2010-01-01' && gender = 2;
-- 8. 查询 职位是 2(讲师)，3 (学工主管)，4 (教研助管) 的员工信息
select *
from tb_emp where job in (1,2,3);
-- 9. 查询姓名为两个字的员工信息
select *
from tb_emp where name like '__';
-- 10. 查询 姓'张'的员工信息
select *
from tb_emp where name like '张%';

 DQL-聚合函数

语法：select 聚合函数(字段列表) from 表名；

聚合函数：将一列数据作为一个整体，进行纵向计算。

函数	功能
count	统计数量
max	最大值
min	最小值
avg	平均值
sum	求和

注意事项

null 值不参与所有聚合函数运算。

统计数量可以使用：count(*) count(字段) count(常量) 推荐使用 count(*)。

--  =========================== 聚合函数 ===============================
-- 聚合函数
-- 1. 统计该企业员工数量
-- A. count(字段)  不对null 值进行运算
select count(*) as 员工数量
from tb_emp;
-- B. count(常量)
select count(1)
from tb_emp;
-- C. count(*)
select count(*)
from tb_emp;
-- 2. 统计该企业最早入职的员工
select min(entrydate)
from tb_emp;
-- 3. 统计该企业最迟入职的员工 - max
select max(entrydate)
from tb_emp;
-- 4. 统计该企业员工ID的平均值
select avg(id)
from tb_emp;
-- 5. 统计该企业员工的 ID 之和
select sum(id)
from tb_emp;

DQL - 分组查询

语法：

select 字段列表 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]；

 where 与 having 区别

1. 执行时机不同：where是分组之前进行过滤，不满足where条件，不参与分组；而having是分组之后对结果进行过滤。

2. 判断条件不同：where不能对聚合函数进行判断，而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;

DQL-排序查询

条件查询：select 字段列表 from 表名 [where 条件列表][group by 分组字段] order by 字段1 排序方式1，字段2 排序方式2...；

排序方式：

ASC：升序(默认值)

DESC：降序

注意事项

如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

-- 排序查询
-- 1. 根据入职时间，对员工进行升序排序 - asc
select *
from tb_emp order by entrydate asc ;
-- 2. 根据入职时间，对员工进行降序排序
select *
from tb_emp order by entrydate desc ;
-- 3. 根据入职时间 对公司的员工进行 升序排序， 入职时间相同，再按照 更新时间，进行降序排序
select *
from tb_emp order by entrydate asc ,update_time desc ;

DQL-分页查询

语法

select 字段列表 from 表名 limit 起始索引，查询记录数；

注意事项

1. 起始索引从0开始，起始索引 = (查询页码 - 1) * 每页显示记录数

2. 分页查询是数据库的方言，不同的数据库有不同的实现，MYSQL中是LIMIT。

3. 如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10。

-- 分页查询
-- 1. 从 起始 索引0 开始查询员工数据，每页展示5条记录
select *
from tb_emp limit 0,5;
-- 2. 查询 第1页 员工数据，每页展示5条记录
select *
from tb_emp limit 0,5;
-- 3. 查询 第2页 员工数据，每页展示5条数据
select *
from tb_emp limit 5,5;
-- 4. 查询 第三页
select *
from tb_emp limit 10,5;

函数：

if(表达式，tvalue，fvalue)：当表达式为true时，取值tvalue，当表达式为false时，取值fvalue

case expr when value1 then result1 [when value2 then value2 ...][else result]end;

综合练习

-- 实例1：按需求完成员工管理的条件分页查询 - 根据输入条件，查询第一页数据，每页展示10条数据
-- 输入条件
-- 姓名：张
-- 性别：男
-- 入职时间：2000-01-01  2015-12-31
-- 根据输入的员工姓名、员工性别、入职时间 搜索满足条件的员工信息
-- 其中员工姓名支持模糊匹配；性别进行精确查询，入职时间进行范围查询
-- 支持分页查询
-- 并对查询的结果，根据最后修改时间进行倒序排序
select *
from tb_emp where name like '%张%' and gender = 1 and entrydate between '2000-01-01' and '2015-12-31' order by entrydate desc ;
-- 实例2：根据需求，完成员工性别信息的统计 -count(*)
-- if(表达式,true取值,false取值)
select if(gender = 1,'男性员工','女性员工') 性别,count(*) 员工数量
from tb_emp group by gender;
-- 实例3：case 表达式 when 值1 then 结果1 when 值2 then 结果2 ... else ... end
select case job when 1 then '班主任' when 2 then '讲师' when 3 then '学工主管' when 4 then '教研助管' else '未分配职位' end 职位,count(*)
from tb_emp group by job;

小结