MySql 基础篇

数据库概述

为什么学习数据库

实现数据持久化到本地

使用完整的管理系统统一管理，可以实现结构化查询，方便管理

数据库（ DataBase）

为了方便数据的存储和管理，它将数据按照特定的规则存储在磁盘上,就是一个存储数据的仓库。通过数据库管理系统，可以有效的组织和管理存储在数据库中的数据

数据库的相关概念

DB：数据库（DateBase）

存储数据的容器，它保存了一系列有组织的数据

DBMS：数据关系系统（DateBase Management System）

又称为数据库软件或数据库产品，用于创建或管理DB

SQL：结构化查询语言（Structure Query Language）

用于和数据库通信的语言，不是某个数据库软件特有的，而是几乎所有的主流数据、库软件通用的语言

根据存储类型分类

关系型数据库

关系型数据库管理系统称为RDBMS，R指Relation

Oracle:功能强大,收费

MySQL 快捷、可靠 开源、免费

SQL Server(微软): 只能安装在Windows操作系统

DB2 (IBM):适合处理海量数据,收费

非关系型数据库

MongdoDB

Redis

Mysql数据库

MySQL是一个关系型数据库管理系统，具有快速、可靠和易于使用的特点。使用标准的sql语言,并且支持多种操作系统,支持多种语言。

MySQL语法规范 ：

不区分大小写，建议关键字大写，表名、列名小写

每句话用;

注释 ：

单行注释：#注释文字

单行注释：-- 注释文字（要有空格）

多行注释：/* 注释文字 */

sql

结构化查询语言(Structured Query Language)简称SQL，是一种特殊 目的的编程语言，是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统。

SQL优点：

不是某个特定数据库供应商专有的语是言，几乎所有DBMS都支持SQL

简单易学

实际上强有力的语言，灵活使用可以进行非常复杂和高级的数据库操作

DDL

数据(结构)定义语言DDL(Data Defi n ition Language)，是用于创建和修改数据库表结构的语言

语句

创建

CREATE DATABASE [if not exists] 数据库名 [CHARSET utf8]

删除

DROP DATABASE 数据库名 / [IFEXISTS数据库名] 

修改字符集

ALTER DATABASE 数据库名 CHARSET gbk

数据库存储数据的特点

将数据放到表中，表再放到库中

一个数据库中可以有多个表，每个表都有一个名字，用来标识自己。表名具有唯一性

表具有一些特性，这些特性定义了数据在表中如何存储，类似Java中“类”的 设计

表由列组成，我们也称列为字段。所有表都是由一个或多个列组成的，每一列 类似java中 的”属性”

表中的数据是按行存储的，每一行类似于Java中的“对象”

数据库表的基本概念

1、数据表

表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素。表的最简单形式是由行和列组成，分别都包含着数据。美美格尔表都有一个表头和表体，表头定义表名和列名。表中的行被看作是文件中的记录，表中的列被看作是这些记录的字段。

2、记录

记录也被称为一行数据，是表里的一行，在关系型数据库的表里，一行数据是指一条完整的记录。

3、字段

字段是表里的一列，用于保存每条记录的特定信息。数据表的一列包含了特定字段的全部信息。

设计表

对于具体的某一个表，在创建之前，需要确定表的下列特征：

表名(表信息)

表中的字段

字段的数据类型和长度

哪些约束

数据类型

字符类型是需要给定长度的

char(4) 定长(长度是固定的 例如给定长度是4,即使存储了一个字符,仍占4个长度)

varchar(4) 最大存储4个字符,如果只存储了1个字符,那么就占1个长度

date 年月日

datetime 年月日 时分秒

整数

浮点数

BLOB（大容量，可以用来存储图片）

text（大容量的字符串）例如新闻、小说

创建表

CREATE TABLE t_student(
            
            )

添加约束

PRIMARY KEY 主键约束 不能为空,不能重复,一个表中只有一个唯一约束

​AUTO_INCREMENT mysql主键自增(选填) 自增列是主键,类型是整数 ​

NOT NULL 不能为空 ​

UNIQUE 唯一约束 ​

CHECK(height>100.0) 检查约束 ​

default 默认值 ​

comment 字段注释

CREATE TABLE t_student(
            num INT  PRIMARY KEY AUTO_INCREMENT COMMENT'学号 主键',
            NAME VARCHAR(4) NOT NULL COMMENT'姓名',
            sex CHAR(1) DEFAULT '男' COMMENT'性别',
            birthday DATE NOT NULL,
            height FLOAT(4,1) CHECK(height>100.0),
            phone CHAR(11) NOT NULL UNIQUE,
            reg_time DATETIME
)

编辑

-- 删除表  
DROP TABLE IF EXISTS t_student;
​
-- 修改表名   旧表名       新表名
RENAME TABLE student TO t_student;
​
-- 复制表  例如对某张表进行操作时,备份数据
CREATE TABLE stu LIKE t_student;

DML

数据操纵语言D M L（ Data Man i pu lation Lang uage）

语句

insert(插入数据)

delete(删除数据)

update(修改数据)

--  now() 获取数据库所在系统的当前时间
-- 向数据库插入数据 no是自动增长的,不需设置值
INSERT INTO student(NAME,gender,birthday,height,phone,address,reg_time)
              VALUE('赵六','男','2000-1-1',1.77,'15666663333','西安',NOW())
              
INSERT INTO student SET NAME='丽丽',gender='女',phone='1893333000',reg_time=NOW()        
​
INSERT INTO student(NAME,gender,birthday,height,phone,address,reg_time)
              VALUES('jim','男','2000-1-1',1.57,'15666663322','西安',NOW()),
                    ('tom','男','2000-1-1',1.67,'15666663366','西安',NOW())
​
-- 将查询中的结果插入到指定的表中
INSERT INTO stu(NO,NAME,gender,phone) SELECT  NO,NAME,gender,phone FROM student
​
-- 修改表数据  记得加修改条件,条件要准确  否则会误操作数据
UPDATE stu SET birthday='2001-2-2',height='1.55',address='陕理工'
UPDATE student SET birthday='2001-2-2',height='1.55',address='陕理工' WHERE NO=6
​
-- 删除表数据
DELETE FROM stu -- 删除表中的数据
​
DROP TABLE stu -- 删除表结构 

DQL-基础查询

DQL（ Data Query Lang uage）数据查询语言查询是使用频率最高的一个操作， 可以从一个表中查询数据，也可以从多个表中查询数据

语法

select 查询列表 from 表名 ;

特点

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

-- 查询语句 DQL  使用频率最高的语句
-- select  查询列表(结果)  from  表名;
​
​
-- select 结果  from  表名   where  条件  排序  分组  行数限制  子查询
-- 多表  关联查询
​
-- 查询结果进行处理
--     常量
SELECT 100,NAME FROM t_student 
--     表达式
SELECT 10*10,NAME FROM t_student 
SELECT height+5,NAME FROM t_student 
--     函数
SELECT VERSION()
SELECT CHAR_LENGTH(NAME) FROM t_student
​
-- * 所有列
SELECT * FROM t_student
​
-- 查询特定列(建议使用的)
SELECT num,NAME,sex FROM t_student
​
-- 去除重复数据, 针对查询出来的结果, 要求是所有列都是相同
SELECT DISTINCT * FROM t_student
​
SELECT DISTINCT NAME,sex FROM t_student

查询结果处理

函数

类似于java中的方法，将一组逻辑语句，在数据库中定义好，可以直接调用

好处：隐藏了实现细节 提高了代码额复用性

调用：select 函数名 （实参列表）[from 表]

分类

单行函数：对查询的每行数据进行操作，查询几行处理几行

分组函数: (聚合函数） 多行转为一行

单行函数

字符函数

-- 查询结果处理--单行函数(会对查询的每一行进行处理)   
-- 字符函数
-- length(列名)-以字节为单位  CHAR_LENGTH(列名)-- 字符为单位
 SELECT LENGTH(NAME),CHAR_LENGTH(NAME) FROM student
 
 -- CONCAT(str1,str2,...) 连接多个字符串
 SELECT CONCAT(NAME,":",gender) FROM student
 
 -- 转大写  转小写
 SELECT UPPER(NAME),LOWER(NAME) FROM student
 
 -- substring(列,开始位置,长度)  开始位置最小是1
 SELECT SUBSTRING(NAME,1,2)FROM student
 
 -- instr(列名,'查找的字符')  查找指定字符首次出现的位置
 SELECT INSTR(NAME,'i') FROM student
 
 -- trim(列名)去除前后空格  trim(子串 from 列名) 去掉前后指定的子串
 SELECT CHAR_LENGTH(TRIM(NAME)) FROM student
 SELECT TRIM('a' FROM  NAME) FROM student
​
 -- lpad(列,长度,填充内容)
 SELECT LPAD(NAME,5,'a'), RPAD(NAME,5,'b')FROM student
​
-- repalce(列名,'old','new')
SELECT REPLACE(NAME,'i','M')AS NAME FROM student

逻辑处理

- 逻辑函数
SELECT NO,
       NAME,
       gender,
       (CASE WHEN height>=1.70 THEN '高个子' ELSE '非高个子' END)AS height
       FROM student
   
-- (case when 条件 then 条件成立执行 else 不成立执行 end) as 别名  
--   (case when 条件1 then 条件1成立执行 when 条件2 then 条件2成立执行 else 不成立执行 end) as 别名   
SELECT NO,
       NAME,
       gender,
       (CASE WHEN height>=1.80 THEN '大高个子' 
             WHEN height>=1.70 THEN '高个子'
             ELSE '非高个子' END)AS height
       FROM student
-- IFNULL(列,'默认值')  列为null时,给予默认值      
SELECT
  NO,
  NAME,
  gender,
  IFNULL(height,'暂未录入')height
FROM
  student     
  
  SELECT
  NO,
  NAME,
  gender,
  IF(height>=1.70,'高个子','非高个子')height
FROM
  student  

数学函数

-- 数学函数
SELECT ROUND(height)height FROM student
SELECT CEIL(height)height,FLOOR(height) FROM student
-- truncate(列,位数)  截断 保留指定的位数  不会进位,舍去
SELECT TRUNCATE(height,1)height FROM student
-- mod(被除数,除数)取余 rand() 返回0-1之间的随机数
SELECT MOD(10,3),RAND() FROM student

日期函数

-- 日期函数
-- NOW()年月日时分秒,CURDATE(年月日,CURTIME()时分秒
SELECT NAME,gender,NOW(),CURDATE(),CURTIME() FROM student
SELECT NAME,gender,NOW(),CURDATE(),CURTIME() FROM student
SELECT NAME,gender,YEAR(reg_time),MONTH(reg_time),DAY(reg_time) FROM student
​
-- 字符串转日期
SELECT STR_TO_DATE('2001-10-10','%Y-%m-%d') FROM student 
-- 日期格式为指定格式的字符串
SELECT DATE_FORMAT(reg_time,'%Y')FROM student 
SELECT DATE_FORMAT(reg_time,'%Y-%m')FROM student 
-- 计算两个日期之间相差的天数
SELECT DATEDIFF(NOW(),'2022-1-1') FROM student

 

分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

注意：

1.sum，avg一般用于处理数值型max，min，count可以处理任何类型

2.以上分组函数都忽略null值

3.count函数的一般使用count（*）用作统计行数

4.和分组函数一同查询的字段要求是group by后的字段

-- 分组函数(往往和分组函数一同使用)  统计函数/聚合函数(多变一)     
-- sum 求和、avg 平均值、max 最大值、min 最小值、count计数（非空）
-- 统计数据量
SELECT COUNT(*) FROM student 
​
-- 求和  只能处理数值型
SELECT SUM(height) FROM student
-- 平均值 只能处理数值型
SELECT AVG(height) FROM student
​
-- max(列) min(列) 
SELECT MAX(height),MAX(NAME),MIN(height),MIN(NAME) FROM student

条件查询

-- 查询条件  
-- select * from student where  条件
​
SELECT * FROM student WHERE gender = '男'
SELECT * FROM student WHERE gender != '男'
SELECT * FROM student WHERE gender <> '男'  -- <>不等于
SELECT * FROM student WHERE height>1.60
​
-- and 且  多个条件同时成立
SELECT * FROM student WHERE gender = '男' AND height>1.70 
-- or 或 成立一个条件即可
SELECT * FROM student WHERE gender = '男' OR height>1.70
​
SELECT * FROM student WHERE height  IN(1.79,1.90)
​
SELECT * FROM student WHERE height IS NOT NULL

模糊查询

-- like '基本字符%'  %模糊匹配任意位数
SELECT * FROM student WHERE NAME LIKE '%三%'
-- like '基本字符_'  _匹配单个字符   __
SELECT * FROM student WHERE NAME LIKE '张__'
​
SELECT * FROM student WHERE height >= 1.60 AND height<=1.90
SELECT * FROM student WHERE height BETWEEN 1.60 AND 1.90

union 合并查询结果

-- union 合并多条查询的结果,会去除重复数据
-- union all 合并多条查询的结果 不会去除重复的数据  效率高
SELECT * FROM student WHERE gender = '男'
 UNION
SELECT * FROM student WHERE  height>1.50
​
SELECT * FROM student WHERE gender = '男'
 UNION ALL
SELECT * FROM student WHERE  height>1.50

排序

-- 排序 默认主键  升序排序
SELECT * FROM student  
​
-- order by 列名  默认是升序排序
SELECT * FROM student   ORDER BY reg_time 
​
--  order by 列名 desc 降序排序   asc 升序排序
SELECT * FROM student   ORDER BY reg_time DESC,height ASC 

数量限制

-- 数量限制  limit 开始的位置从0开始,数量
-- 实现分页功能
SELECT * FROM student LIMIT 0,4
SELECT * FROM student LIMIT 4,4
​
SELECT
  *
FROM
  student
WHERE gender = '男'
ORDER BY reg_time DESC
LIMIT 0, 1

分组查询

-- 分组查询   把条件相同数据分到一个组中
-- 查询男生和女生分别有多少人  需要按性别进行分组  然后统计数量
SELECT  gender,COUNT(*),SUM(height) FROM student GROUP BY gender 
​
SELECT  birthday,COUNT(*) FROM student GROUP BY birthday 
​
-- 分组后的条件查询
​
-- where  条件   对原始表中的数据进行条件筛选
-- having 条件   对分组后的数据进行条件筛选
SELECT
  gender,
  COUNT(*) AS c
FROM
  student
WHERE NO >= 1
GROUP BY gender
HAVING c > 2

注意：

查询列表比较特殊，要求是分组函数和group by 后出现的字段

分组查询中的筛选条件分为两类

数据源 源位置 关键字

分组前筛选 原始表 group by 的前面 where

分组后筛选 分组后的结果集 group by 的后面 having

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询；外部的查询语句，称为主查询或外查询

分类： 按子查询出现的位置： insert into后面：列子查询，表子查询 select后面：仅仅支持标量子查询 from后面：支持表子查询 where或having后面：支持标量子查询，列子查询，行子查询 按功能、结果集的行列数不同： 标量子查询（结果集只有一行一列） 列子查询（结果集只有一列多行） 行子查询（结果集有一行多列）（较少） 表子查询（结果集一般为多行多列）

/*
 子查询
    出现在其他语句中的select语句  称为子查询/内查询
    
   按子查询出现的为:
    select  语句中子查询   只支持标量查询  使用的较少
    form    语句中子查询   表子查询 
    where   语句中子查询   标量子查询,列子查询,行子查询 
   按功能、结果集的行列数不同：
     标量子查询（结果集只有一行一列）
     列子查询（结果集只有一列多行）
     行子查询（结果集有一行多列）（较少）
     表子查询（结果集一般为多行多列）    
        
*/
-- 在select后面 使用子查询
SELECT NO,gender,(SELECT NAME FROM student st WHERE st.no = s.no ) FROM student s
​
​
-- 在from后面使用子查询  把一个查询的结果可以当做一张表  继续作为数据源查询
  SELECT * FROM (SELECT gender,COUNT(*)AS c FROM student GROUP BY gender)AS t
           WHERE t.c>2
           
-- 在where后面使用子查询   标量子查询           
-- 查询身高大于平均身高的学生    平均身高?
    SELECT * FROM student WHERE height > (SELECT AVG(height) FROM student)
​
-- 在where后面使用子查询   列子查询
    SELECT * FROM student WHERE NO IN(SELECT NO FROM student WHERE height>1.60)
    
 -- 在where后面使用子查询   行子查询
SELECT * FROM student WHERE (NO,height) = (SELECT MAX(NO),MAX(height) FROM student)

多表设计

为什么需要多表关联

减少数据冗余

设计范式

第一范式

第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就

说明该 数据库表满足了第一范式

第二范式

第二范式就是要有主键，要求其他字段都依赖于主键

第三范式

第三范式就是要消除传递依赖，方便理解，可以看做是“消除冗余”

-- 创建年级表
CREATE TABLE grade(
    id INT PRIMARY KEY AUTO_INCREMENT,
    grades VARCHAR(10)
    )
DROP TABLE garde
-- 弱关联  人为定义的关系 没有实际约束  删除关联表数据没有任何限制
​
-- 强关联  添加外键约束  外键关联另一个表的主键   与grade表中的id关联  student表为从表，grade表为主表 
-- 数据表之间的关联/引用关系是依靠具体的主键（primary key）和外键（foreign key）建立起来 的
ALTER TABLE student ADD CONSTRAINT fk_gradeid FOREIGN KEY(gradeid) REFERENCES grade(id)
​
-- 解除关联
ALTER TABLE student DROP FOREIGN KEY fk_gradeid

当主表中没有对应的记录时，不能将记录添加到从表 不能更改主表中的值而导致从表中的记录孤立 从表存在与主表对应的记录，不能从主表中删除该行 删除主表前，先删从表

关联查询

含义：又称多表查询，当查询的字段来自于多个表时，就会用到连接查询

笛卡尔乘积

笛卡尔乘积现象：表1有m行，表2有n行，结果=m*n

SELECT * FROM student,grade

发生原因：没有有效的连接条件

如何避免：添加有效的连接条件

-- 关联查询  又称多表查询
-- 学号 姓名 性别 所在年级(年级的名称)  
-- 查询的数据来源于多张表  为表定义别名 
-- 问题: 笛卡尔乘积现象   结果不对,避免发生, 添加条件
SELECT
  s.no,
  s.name,
  s.gender,
  g.name 
FROM
  student s,
  grade g
WHERE 
  s.gradeid = g.id
  
-- 内连接 
-- 等值连接
SELECT 
  s.no,
  s.name,
  s.gender,
  g.name gname
 FROM  
 student s INNER JOIN grade g ON s.gradeid = g.id
               
-- 非等值连接  
CREATE TABLE height_level(
   level_name VARCHAR(1),
   level_lower FLOAT(3,2),
   level_upper FLOAT(3,2)
)            
 
SELECT 
   s.no,
   s.name,
   h.level_name 
  FROM student s 
  INNER JOIN height_level  h 
  ON s.height BETWEEN h.level_lower AND h.level_upper

自关联

-- 内连接  自连接 
-- 地区表  省  市  县/区    陕西省   西安市   雁塔区  需要几张表?  1张表
CREATE TABLE t_area(
     id INT,
     NAME VARCHAR(10),
     pid INT -- 父级id
) 
​
SELECT 
   ta1.name pname,
   ta.name
 FROM t_area ta INNER JOIN t_area ta1 ON ta.pid = ta1.id
 
 SELECT * FROM t_area WHERE pid = 100
 SELECT * FROM t_area WHERE pid = 610 
 SELECT * FROM t_area WHERE pid = 610001

左外关联和右外关联

-- 左外连接
 -- 查询出所有的学生 不管有没有分配年级,成绩... 都需把学生查询出来
 -- 即使不满足连接条件,也会被查询出来
 SELECT 
  s.no,
  s.name,
  s.gender,
  g.name gname
 FROM  
 student s  LEFT JOIN grade g ON s.gradeid = g.id
 
-- 查询每个年级的学生人数
-- 右外连接查询  无论条件是否成立  都会把右边表中的数据查询出来
SELECT
    g.name,
    COUNT(s.gradeid)
 FROM student s RIGHT JOIN grade g ON s.gradeid = g.id 
 GROUP BY g.name