十三 Mysql

最新推荐文章于 2024-02-20 10:12:24 发布

知食分子11

最新推荐文章于 2024-02-20 10:12:24 发布

阅读量114

点赞数

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/m0_56087583/article/details/120142204

版权

MySQL基础

1.数据库概述

1.为什么学习数据库

实现数据持久化到本地

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

2.什么是数据库

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

库中的数据

DB：数据库（DataBase）

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

DBMS：数据库管理系统（DataBase Management System）

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

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

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

3.数据库的分类

关系型数据库

Oracle:功能强大,收费.

MySQL 快捷、可靠开源、免费

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

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

非关系型数据库

	MongdoDB

Redis

2.MySQL数据库

1.MySQL数据库简述

MySQL是一个关系型数据库管理系统**，**由瑞典MySQL AB 公司开发，目前属于 Oracle旗下产品。MySQL 流行的关系型

数据库管理系统

开源,支持多种语言,支持多种操作系统,分为商业版(收费的)和社区版(免费的)

2.常用命令

连接mysql :登录：mysql [-hlocalhost -P3306]（本机可省略） -uroot -p（可以直接写密码，不能有空格）

-h：主机名 -P：端口号 -u：用户名 -p：密码退出：exit

查看mysql数据库的版本 select version();（mysql命令）

查看当前所有的数据库：show databases;

选择指定的库：use 库名

查看当前的所有表：show tables;

查看其他库的所有表：show tables from 库名;

3.可视化客户端

SQLyog / Navicat

4.sql语言及语法规范

不区分大小写关键字大写表名,列名小写每句话以 ; 结尾

单行注释# – (要有空格) 多行注释 /* */

sql语言(结构化查询语言)

脚本语言,不需要编译,直接通过某种解释器,解释执行

3.SQL–DDL

1.DDL语言

创建和修改数据库表结构的语言(定义语言)

2.创建和删除数据库

#创建数据库                设置字符级
CREATE DATABASE[IF NOT EXISTS] school_db CHARSET utf8;
#删除数据库
DROP DATABASE school_db;
#修改字符集
ALTER DATABASE school_db CHARSET utf8;

3.数据库的特点

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

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

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

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

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

4.数据库表的基本概念

数据表: 表（table）是数据存储的最常见和最简单的形式，是构成关系型数据库的基本元素

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

字段:字段是表里的一列,数据表的一列包含了特定字段的全部信息。

4.设计表

要确定以下特征:

● 表名(表信息) 表中的字段 字段的数据类型和长度 哪些约束

-- 创建表
/*
CREATE TABLE t_表名(
字段名 数据类型 约束 字段注释,
);
*/
CREATE TABLE t_student(
num INT PRIMARY KEY AUTO_INCREMENT COMMENT'计数器',-- 主键约束 + 主键自增  +字段注释
NAME VARCHAR(4) NOT NULL COMMENT '姓名',-- 不能为空约束 + 字段注释
sex CHAR(1) DEFAULT'男' COMMENT'性别',-- 默认值  + 字段注释
birthday DATE,-- 无约束(可以为空)
phone CHAR(11) UNIQUE,-- 唯一约束
high FLOAT(4,1) CHECK(high>100),-- 检查约束
reg_time DATETIME 
);
-- 删除表
DROP TABLE t_student;

1.数据类型

char（n）长度为n的定长字符串 ,存储一个字符,字符长度也会变为n

varchar(n）最大长度为n的可变长字符串 ,存储几个字符就是几个字符的长度

date 日期，包含年月日

datetime 年月日时分秒

整型

TINYINT 1 -128 127

SMALLINT 2 -32768 32767

MEDIUMINT 3 -8388608 8388607

INT 4 -2147483648 2147483647

BIGINT 8 -9223372036854775808 9223372036854775807

浮点型

float(M,D) double(M,D)

M :精度，数据的总长度； D：标度，小数点后的长度

大文本类型

BLOB 二进制大对象可以容纳可变数量的数据,用于存储图片视频信息

2.约束

主键: 在一张表中代表唯一的一条记录,不能为空,不能重复

PRIMARY KEY 设置主键约束 (一个表中仅有一个,不能为空,不能重复)默认包含唯一约束和不为空约束

NOT NULL 不能为空约束

UNIQUE 唯一性约束

CHECK(条件) 检查约束设置条件

外键约束

主键自动增长: AUTO_INCREMENT (数值自动加一)

默认值 DEFAULT 值

字段注释: comment '注释

3.对表的操作

创建表 CREATE TABLE t_表名(字段名数据类型约束字段注释,);

删除表 DROP TABLE [if exists ]表名

修改表名 RENAME TABLE 旧表名 TO 新表名

复制表结构 CREATE TABLE 新表名 LIKE 被复制表名;

4.修改表结构

-- 修改表结构
ALTER TABLE t_student ADD PRIMARY  KEY(num);-- 添加主键约束
ALTER TABLE t_student DROP PRIMARY KEY;-- 删除主键约束

ALTER TABLE t_student MODIFY num INT AUTO_INCREMENT; -- 添加主键自增
ALTER TABLE t_student MODIFY num INT -- 删除主键自增
-- 添加不能为空约束,已保存的表中数据不能为空
ALTER TABLE t_student MODIFY sex CHAR NOT NULL; -- 添加不能为空约束
ALTER TABLE t_student MODIFY sex CHAR NULL -- 删除不能为空约束

ALTER TABLE t_student ADD CONSTRAINT t_phone UNIQUE(phone) -- 添加唯一约束
ALTER TABLE t_student DROP INDEX t_phone -- 删除唯一约束

ALTER TABLE t_student ADD CONSTRAINT t_high CHECK(high>100.0) -- 添加检查约束
ALTER TABLE t_student DROP CHECK t_high -- 删除检查约束

ALTER TABLE t_student ADD weight FLOAT(5,2) AFTER high  -- 在指定的位置添加新列
ALTER TABLE t_student ADD weight FLOAT(5,2) -- 在末尾添加新的列
ALTER TABLE t_student DROP weight -- 删除指定的列

ALTER TABLE t_student CHANGE high height FLOAT(5,2) -- 修改列的名字

5.DML(数据操作语言)

-- 插入数据的三种方式
INSERT INTO t_student (NAME,sex,birthday,high,reg_time) 
		VALUES('张三','男','2000-12-02',172.1,NOW())
		
INSERT INTO t_student SET NAME='王五',sex='女',birthday='1999-2-2',high=189.2,reg_time=NOW()

INSERT INTO t_student (NAME,sex,birthday,high,reg_time)
		VALUE('张三','男','2000-12-02',172.1,NOW()),('李四','男','1989-2-12',123.2,NOW())

UPDATE t_student SET NAME='李四1' WHERE num=2

CREATE TABLE t_stu LIKE t_student -- 复制表结构

TRUNCATE TABLE stu; -- 清空整张表

DELETE FROM t_student WHERE num=2; -- 删除数据

6.QDL(数据查询语言)

1.简述

查询是使用频率最高的一个操作,可以从一张表中查询数据,也可以从多张表中查询数据

语法::select 函数(列名) from 表名

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

查询的结果是一个虚拟的表格,并不会影响真实表格中的数据

2.查询结果处理

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

3.函数

1.单行函数

调用：select 函数名(实参列表) [from 表];

1.字符函数

-- 获取字节长度
SELECT LENGTH(NAME)AS LENGTH FROM t_student
-- 获取字符长度
SELECT CHAR_LENGTH (NAME) AS charlength FROM t_student
-- 拼接字符串
SELECT CONCAT(NAME,sex) AS NAME FROM t_student
-- 改变大小写 upper()变大写  lower()变小写
SELECT UPPER('aabbdd') FROM t_student
SELECT LOWER('AASSDDFFF') FROM t_student
-- 截取字符串的位置
SELECT SUBSTRING(NAME,1,3) FROM t_student
-- 返回指定元素的第一次出现的位置
SELECT INSTR(NAME,'三') FROM t_student
-- 去掉字符串前后的空格或子串
SELECT TRIM('三'FROM NAME) FROM t_student
-- 用指定字符左(右)填充,将字符串填充至指定长度
SELECT LPAD(NAME,3,'a') FROM t_student
SELECT RPAD(NAME,1,'a') FROM t_student
-- 替换所有子串
SELECT REPLACE(NAME,'t','a') FROM t_student
-- 6.查询所有姓王员工的姓名和性别
SELECT *FROM t_staff WHERE LEFT(NAME,1)='王'
-- left 从左边开始截取

2.逻辑处理

-- case when 条件 then 结果1 else 结果2 end     (可以有多个when)
SELECT
  (
  CASE
      WHEN high > 170
      THEN '高'
      WHEN high >130 AND high<=170 THEN '中等'
      ELSE '矮'
    END
  ) AS high
FROM
  t_student
 -- ifnull(被检测列,指定值)  如果为null，则返回指定的值，否则返回原 本的值
  SELECT  IFNULL(phone,'暂未登记') FROM t_student
-- if(条件,结果1,结果2)
  SELECT  IF(high>170 ,'高','矮') FROM t_student

3.数学函数

-- round(数值,保留几位小数)  四舍五入
SELECT ROUND(23.12,0)
-- 向上取整
SELECT  CEIL(23.13)
-- 向下取整
SELECT  FLOOR(23.99)
-- truncate(数值,保留小数位数)   截取
SELECT TRUNCATE(23.123,2)
-- mod(被除数,除数)   取余
SELECT MOD(3,2)
-- rand()   生成随机数(0-1)
SELECT RAND()*100

4.日期函数

 -- 获取系统当前日期+时间    
     SELECT NOW()
 -- 获取系统当前日期
 SELECT CURDATE()    
 -- 获取系统当前时间
 SELECT CURTIME()
 -- 可以获取指定部分,年,月,日,时,分,秒
  YEAR  MONTH  DAY   HOUR   MINUTE  SECOND
 SELECT YEAR(NOW())
 -- 将日期字符串转换为指定格式字符串
 SELECT STR_TO_DATE(birthday,'%Y-%m-%d')FROM t_student
 -- 将日期转换成字符串
 SELECT DATE_FORMAT('2021-7-6','%Y年%m月%d日')
 -- 两个日期相差的天数
 SELECT DATEDIFF(NOW(),birthday) FROM t_student

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RkX4EenW-1630925852734)(C:\Users\Administrator\Desktop\日期格式.png)]

2.分组函数(聚合函数)

功能::用作统计使用

分类:sum求和 ,avg平均值 ,max最大值, min最小值, count计数(非空)

sum avg一般用于处理数值型 max, min ,count 可以适用于全部类型

以上分组函数都忽略 null

count函数一般使用count(*)用于统计行数

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

SELECT SUM(high)FROM  t_student
SELECT AVG(high),sex FROM t_student GROUP BY sex
SELECT MAX(high) FROM t_student
SELECT MIN(high) FROM t_student
SELECT COUNT(*),sex FROM t_student GROUP BY sex --  和分组函数一同查询的字段要求是group by后的字段

4.条件查询

5.union和union all

-- union语句:将多条查询结果合并,去掉重复的行
SELECT * FROM t_student WHERE high>=170 AND sex ='男'
 UNION
SELECT * FROM t_student WHERE num>2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-jXWJDFG2-1630925852737)(C:\Users\Administrator\Desktop\union.png)]

-- union all :将多条查询结果合并
SELECT * FROM t_student WHERE high>=170 AND sex ='男'
UNION ALL
SELECT * FROM t_student WHERE num>2

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-kPa0Wfzu-1630925852739)(C:\Users\Administrator\Desktop\union all.png)]

6.排序和数量限制

-- 排序 ASC 升序     DESC  降序
-- 表名 where (条件) order by 列名 asc/desc 
SELECT  * FROM t_student WHERE  num>1 ORDER BY num ASC 
 SELECT  * FROM t_student WHERE  num>1 ORDER BY num DESC

不选择排序方式,默认是按主键排序,默认为升序

排序在条件后面,对筛选结果进行排序

多个字段进行排序,当第一个相同时,使用第二个字段排序

-- limit 开始位置(从0开始),查询数量     
     SELECT  * FROM t_student WHERE  num>1 ORDER BY num ASC LIMIT 0,2; 
     SELECT  * FROM t_student WHERE  num>1 ORDER BY num DESC LIMIT 0,2;

7.分组查询

select 分组函数,列(要求出现在group by后面)

from 表名

where 筛选条件

group by 分组的列表

having 分组后的筛选条件

order by 按照那一列排序 asc/desc

limit 开始位置(从0开始),查询数量

SELECT
  COUNT (*),
  sex   
FROM
  t_student
  where high >170 -- 分组前对数据进行筛选
GROUP BY sex      -- 按照那个值进行分组
HAVING COUNT (*) > 2 -- 分组后数据进行条件筛选
ORDER BY COUNT (*) ASC -- 按照count值进行升序排列
LIMIT 0, 1   -- 数量限制

8.子查询

  -- 子查询
    -- 在修改语句时使用了子查询,注意子查询不能是正在操作的表
    UPDATE t_student SET NAME = 'aaa' WHERE num =(SELECT num FROM t_stu WHERE high =189)
    DELETE t_student  WHERE num =(SELECT num FROM t_stu WHERE high =189)
    
    -- 在查询语句中使用子查询
    -- 在一条sql语句中如果出现两个以上的表名,可以为表名定义别名
    SELECT 
    ts.num
    (SELECT t.name FROM t_student  t WHERE t.name=ts.name )
    FROM t_student ts
    
    -- where 后面的子查询
    -- 标量子查询
    SELECT * FROM t_student WHERE high =(SELECT MAX(high) FROM t_student )
    -- 列表查询  一列多行 
   SELECT * FROM t_student WHERE high IN(SELECT high FROM t_student WHERE high>175)
   -- 行子查询.一行多列      查询身高最高,num最大的
   SELECT * FROM t_student WHERE (high,num)
                       = (SELECT MAX(high),MAX(num) FROM t_student)
                       
   -- from 后面,使用表子查询,返回多行多列
   -- 查询男生,女生,总人数大于1的
   
   SELECT * FROM (SELECT sex ,COUNT(*)c FROM  t_student GROUP BY sex)t
   WHERE t.c>2

7.多表设计_关联查询

1.数据库设计范式

**范式:**为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则

五种范式:第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、

巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

第一范式(确保每列保持原子性):如果数据库表中的所有字段值都是不可分解的原子值，就说明该数据库表满足了第一范式

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

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

2.外键

外键就是引用另外一个数据表的某条记录。

CONSTRAINT  fk_stuid FOREIGN KEY(stu_id) REFERENCES t_student(num),-- 添加外键约束
CONSTRAINT  fk_subid FOREIGN KEY(sub_id) REFERENCES SUBJECT(id) -- 添加外键约束
-- constraint 约束名 foreign key(外键列) references 表名(主键)

-- 添加外键(在子表中添加一列)
ALTER TABLE t_student ADD gradeid INT AFTER num
--  弱关联:::人为定义关系,删除关联表数据没有任何限制
--  强关联 添加外键约束,外键关联另一个表的主键
ALTER TABLE t_student ADD CONSTRAINT fk_gradeid   FOREIGN KEY(gradeid) REFERENCES grade(id)
-- alter table 子表名 add constraint 约束名 freign key(外键列) references 主表名(主键)
-- 删除外键
ALTER TABLE t_student
DROP FOREIGN KEY fk_gradeid

3.关联查询

1.内连接

-- 先关联,再进行筛选
SELECT stu.name,stu.num,g.name FROM t_student stu,grade g WHERE stu.gradeid=g.id

-- 内连接   将满足条件的数据关联起来
-- 等值连接
SELECT stu.name,stu.num,g.name  
FROM t_student stu INNER JOIN grade g 
ON stu.gradeid=g.id  
-- 非等值连接
SELECT s.stu_name,s.subject,r.dengji 
FROM score s INNER JOIN rg r 
ON s.stu_score BETWEEN r.up_score AND r.low_score
-- select 要显示的列名
-- from 表一 inner join 表二 on 关联的条件

-- 自连接
SELECT ta.name,tp.name 
FROM t_area ta INNER JOIN t_area tp 
ON ta.pid =tp.id
WHERE ta.id= 61010

2.外连接

-- 左外连接
-- 无论关联条件是否成立,都会将左边表上的数据,全部打印
SELECT stu.name,stu.num,g.name 
FROM t_student stu LEFT JOIN grade g 
ON stu.gradeid=g.id 
-- select 列名 from 表1 LEFT JOIN  表2 on 条件
--  右外连接
-- 无论关联条件是否成立,都会将右边表上的数据,全部打印
SELECT g.name ,COUNT(stu.gradeid)
FROM t_student stu RIGHT JOIN grade g 
ON stu.gradeid=g.id GROUP BY g.id
-- -- select 列名 from 表1 RIGHT JOIN  表2 on 条件