MySQL数据管理

MySQL数据管理

1.外键（了解）

方式一：在创建表的时候，增加约束

CREATE TABLE`grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename`VARCHAR(50 NOT NuLL COMMENT "年级名称",
     PRIMARY KEY ( 'gradeid' )
)ENGINE=INNOD DEFAULT CHARSET=utf8
​
--学生表的gradeid字段要去引用年级表的gradeid
--定义外键key
-- 给这个外键添加约束(执行引用）references引用
    CREATE TABLE IF NOT EXISTs `student` (
        `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT "学号",
        `name` VAROHAR(30) NOT NULL DEFAULT '匿名’ COMMENT "姓名",
        `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT "密码',
        `sex` VARCHAR(2) NOT NULL DEFAULT '女’COMENT "性别",
        `birthday` DATETIME DEFAULT NULL COMMENT '出生日期",
        `gradeid` INT(10) NOT NULL CONMMENT "学生的年级",
        `address` VARCHAR(100) DEFAULT NULL COMMENT “家庭住址",
        `emai7` VARCHAR(50)DEFAULT NULL COMMENT '邮箱',
        PRIMARY KEY(id ),
        KEY FK gradeidT ( gradeid`),
        CONSTRAINT ‘FK_gradeid’FOREIGN KEY ( 'gradeid') REFERENCES grade '( gradeid')
    )ENGINE=INNOD8 DEFAULT CHARSET=utf8

方式二 ：创建表成功后，添加外键约束

CREATE TABLE`grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT '年级id',
    `gradename`VARCHAR(50 NOT NuLL COMMENT "年级名称",
     PRIMARY KEY ( 'gradeid' )
)ENGINE=INNOD DEFAULT CHARSET=utf8
​
--学生表的gradeid字段要去引用年级表的gradeid
--定义外键key
-- 给这个外键添加约束(执行引用）references引用
    CREATE TABLE IF NOT EXISTs `student` (
        `id` INT(4) NOT NULL AUTO_INCREMENT COMMENT "学号",
        `name` VAROHAR(30) NOT NULL DEFAULT '匿名’ COMMENT "姓名",
        `pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT "密码',
        `sex` VARCHAR(2) NOT NULL DEFAULT '女’COMENT "性别",
        `birthday` DATETIME DEFAULT NULL COMMENT '出生日期",
        `gradeid` INT(10) NOT NULL CONMMENT "学生的年级",
        `address` VARCHAR(100) DEFAULT NULL COMMENT “家庭住址",
        `emai7` VARCHAR(50)DEFAULT NULL COMMENT '邮箱',
        PRIMARY KEY(id ),
        KEY FK_gradeidT ( gradeid`),
        CONSTRAINT ‘FK_gradeid’FOREIGN KEY ( 'gradeid') REFERENCES grade '( gradeid')
    )ENGINE=INNOD8 DEFAULT CHARSET=utf8
    
    -- 创建时候没有外链关系
    ALTER TABLE `student`
    ADD CONSTRAINT ‘FK_gradeid’ FCREIGN KEY( gradeid ) REFERENCES‘grade ( gradeid ');
                                                          --ALTER TABLE 表 ADD CONSTRAINT约束名FCREIGN KEY(作为外键的列)REFERENCES那个表(哪个字段)

以上都是物理外键，数据库外键，不建议使用！（避免数据库过多造成困扰）

最佳实践 ：

  1. 数据库就是单纯的表，只用来存数据，只有行（数据）和列(字段)。

  2. 我们想使用多张表的数据，想使用外键(程序去实现)

2.DML语言（必须全部记住）

数据库意义：数据储存，数据管理

DML语言：数据操作语言

1. insert
1. update
1. delete
3.添加

insert 语句

-- 插入语句（添加）
-- insert into 表名（字段名，字段1，字段2，字段3）values('值1'),('值2'),('值3'...) 
INSERT INTO `school`.`grade` (`gradename`) VALUES ('211');
​
-- 由于主键自增我们可以省略（如果不写表的字段，他就会一一匹配）报错
INSERT INTO `school`.`grade` VALUES ('111'); 
​
-- 一般写插入语句，我们一定要数据和字段一一对应！
-- 插入多条数据
INSERT INTO `school`.`grade`(`gradename`) VALUES ('nihao'),('tahao');
​
INSERT INTO `school`.`student`(`id`,`name`,`pwd`,`sex`) VALUES (1,'张三','aaa','男');
INSERT INTO `school`.`student`(`id`,`name`,`pwd`,`sex`) VALUES (2,'张4','aaa','男'),(3,'张5','aaa','男');

语法：insert into 数据库名.表名（字段名，字段1，字段2，字段3）values ('值1','值2','值3'),（'值1'，'值2'，'值3'）

注意事项

  1. 字段和字段之间使用英文 逗号 隔开。

  2. 字段可以省略的，但是后面的值必须要一一对应，不能少

  3. 可以同时插入多条数据，VALUES后面的值，需要使用，隔开即可 VALUES('','','')

4.修改

update 修改谁（条件）set 原来的值 = 新值。

-- 修改学员名字
UPDATE `school`.`student` SET `name` ='zhang7' WHERE id = 3;
​
-- 不指定条件情况下，会改动所有表
UPDATE `school`.`student` SET `name` ='zhang789';
​
-- 修改多个属性,逗号隔开
UPDATE `school`.`student` SET `name`='zhang1',`pwd`='bbb' WHERE id = 1;
​
-- 语法：
-- UPDATE `数据库名`.`表名` set column_name = value,[column_name2 = value2,...... ] where [条件]

条件：where字句 运算符 id 等于某个值，大于某个值，在某个值区间内修改......

操作符会返回

操作符含义范围结果
= 等于 5=6 false
<> 或 != 不等于 5<>6 true
>      
<      
<=      
>=      
BETWEEN....AND...... 在某个范围内 [2,5] [2,5]
AND 我和你&&    
OR 我或你 ||     
-- 通过多个条件定位数据
UPDATE `school`.`student` SET `name`='长江7号' WHERE `name` = 'zhang7' AND sex = '男';
​
UPDATE `school`.`student` SET `name`='长江7号' WHERE `name` = 'zhang7' OR sex = '男';

语法：UPDATE 数据库名.表名 set column_name = value,[column_name2 = value2,...... ] where [条件]

注意：

  1. column_name 是数据库得列，尽量带上``;

  2. 条件，筛选条件，如果没有指定，则会修改所有得列。

  3. value，是一个具体得值，也可以是一个变量。

  4. 多个设置的属性之间，使用英文逗号隔开。

  5. UPDATE `school`.`student` SET `birthday`=CURRENT_TIME WHERE `name` = 'zhang7' OR `sex` = '男';
5.删除

delete 命令

语法：delete from 数据库.表名 [where 条件]

-- 删除数据(避免这样写,会全部删除)
DELETE FROM `school`.student;
​
-- 删除指定数据
DELETE FROM `school`.`student` WHERE id = 4;
TRUNCATE 命令
TRUNCATE `school`.student;

作用：完全清空一个数据库表，表的结构和索引约束不会变

delete的truncate区别：

  1. 相同点：都能删除数据，都不会删除表结构。

  2. 不同：

    1. TRUNCATE 重新设置 自增列 计数器会归零

    2. TRUNCATE 不会影响事务

    -- 测试delete 和 truncate 区别
    -- 创建表
    INSERT INTO `school`.`test`(`id`,`coll`) VALUES (3,'ni'),(4,'ta');

    DELETE FROM `school`.`test`  -- 不会影响自增
    TRUNCATE TABLE `school`.`test`  -- 自增会归零

    了解即可： Delete 删除问题，重启数据库，现象

    1. InnoDB 自增列会从1开始（存在内存当中，断电即失）

    2. MyISAM 继续从上一个自增量开始（存在文件中，不会丢失）

6.DQL查询数据（最重点）

  1. （Data Query Language：数据查询语言）

    1. 所有的查询都用它 Select

    2. 简单的查询，复杂的查询它也能做~

    3. == 数据库种最核心的语言，最重要的语句 ==

    4. 使用频率最高的语言

    5. 语法完整

      SELECT column1, column2, ...
      FROM table_name 
      [left|right|inner join table_name2 on] -- 联表查询
      WHERE condition -- 指定结果满足条件
      GROUP BY column1, column2, ... -- 指定字段按照哪几个字段来分组
      HAVING condition -- 过滤分组的记录必须满足的次要条件
      ORDER BY column1, column2, ... -- 指定查询记录按-个或多个条件查询
      LIMIT number_of_rows; -- 指定查询的记录从哪条至哪条

    6. select小结：

       

  2. 指定查询字段

    1. -- 查询全部的学生  slect 字段 from 表
      SELECT * FROM `school`.`student`;

      -- 查询指定字段
      SELECT `name`,`sex` FROM `school`.`student`;

      -- 别名，给结果起一个名字  AS  可以给字段起别名，也可以给表起别名
      SELECT `id` AS `学号`,`name` AS `学生姓名` FROM `school`.`student` AS `学生`;

      -- 函数  Concat(a,b)
      SELECT CONCAT('姓名：',`name`) AS `新名字` FROM `school`.`student`;
      -- 根据某一项查某一条
      SELECT * FROM `school`.`student` WHERE `id` = 1;

      语法：slect 字段 ，...... from 表

      有的时候，列名字不是那么的见名知意，我们起别名 AS 字段名 AS 别名 表名 AS 别名

    2. 去重：distinct

      作用：去除SELECT查询出来的结果中重复的数据，重复的数据只显示一条。

      -- 查询哪些同学参加了考试，成绩
      SELECT * FROM `school`.`result`;  -- 查询全部成绩
      -- 查询哪些同学参加了考试
      SELECT `id` FROM result -- 查询有哪些同学参加了考试

      -- 发现重复数据，去重
      SELECT DISTINCT `id` FROM result;

    3. 数据库列（表达式）

      -- 查询系统版本
      SELECT VERSION()
      -- 用来计算
      SELECT 100*3-1 AS 计算结果;
      -- 查询自增的步长
      SELECT @@auto_increment_increment;
      -- 学员考试成绩+1分 查看
      SELECT `id`,`name`,`score`+1 AS '提分后' FROM `school`.`result`;

      数据库中的表达式：文本值，列，Null，函数，计算表达式，系统变量。。。。

      select 表达式 from 表;

    4. where条件子句

      作用：检索数据中符合条件的值

      搜索的条件由一个或者多个表达式组成！结果 布尔值！

      逻辑运算符

      运算符语法描述
      and && a and b a&&b 逻辑与，两个为真，结果为真
      or || a or b a||b 逻辑或，其中一个为真，则结果为真
      Not ！ not a ！a 逻辑非，真为假，假为真！

      == 尽量使用英文字母 ==

-- ==============where============
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `score`>5 && `score`<11;
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `score`>5 AND `score`<11;

-- 模糊查询（区间）
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `score` BETWEEN 5 AND 8;

-- 除了id为2学生之外的成绩
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE `id` != 2;
SELECT `id`,`name`,`score` FROM `school`.`result` WHERE NOT `id` = 2;

模糊查询：比较运算符

运算符语法描述
IS NULL a is null 如果操作符为NULL，结果为真
IS NOT NULL a is not null 如果操作符为not null，结果为真
BETWEEN a between b and c 若a在b和c之间，则结果为真。
LIKE a like b SQL匹配，如果a匹配b，则结果为真
IN a in（a1,a2,a3....） 假设a在a1，或者a2....其中的某一个值中，结果为真 
-- ===== 模糊查询====
-- 查询姓s的同学
-- Like结合  %(代表0到任意个字符) _（一个字符）
SELECT * FROM `school`.`student` WHERE `name` LIKE 's%';
SELECT * FROM `school`.`student` WHERE `name` LIKE 's_';
-- Like结合  __（一个字符）
SELECT * FROM `school`.`student` WHERE `name` LIKE 's__';
-- 查询名字中间有a字的同学 %a%
SELECT * FROM `school`.`student` WHERE `name` LIKE '%a%';

-- ========in(具体的一个或者多个值)=======
-- 查询 id 为1，2，3的学员
SELECT * FROM  `school`.`student` WHERE `id` IN (1,2,3); 

-- ==== null not null ======
SELECT * FROM  `school`.`student` WHERE `address`='' OR `address` IS NULL;

-- 查询有出生日期 不为空 
SELECT * FROM  `school`.`student` WHERE `birthday`='' OR `birthday` IS NULL;
5.联表查询

join对比

image-20230830170718833

在这里插入图片描述

-- ======联表查询======
--  查询参加考试的同学（学号，姓名）
SELECT * FROM student
SELECT * FROM result

/* 思路
1.分许需求，分析查询字段来自哪些表，（连接查询）
2. 确定使用哪种查询？7种
确定交叉点（这两个表中哪个数据是相同的）
判断的条件：学生表中的 id = 成绩表 id
*/
-- join（连接的表） on（判断的条件）  连接查询
-- where  等值查询


-- INNER JOIN 交集查询
SELECT s.`id`,s.`name`,`score` FROM `school`.`student`AS s INNER JOIN result AS r WHERE s.id = r.id
-- RIGHT Join 右表查询
SELECT s.`id`,s.`name`,`pwd` FROM `school`.`student` AS s RIGHT JOIN result AS r ON s.id = r.id;
-- LEFT Join  左表查询
SELECT s.`id`,s.`name`,`pwd` FROM `school`.`student` AS s LEFT JOIN result AS r ON s.id = r.id;

-- 我要查询哪些数据 select ...
-- 从哪几个表中查，From表  XXX join 连接的表 on 交叉条件
-- 假设存在一种多张表查询。慢慢来，先查两张表再慢慢增加条件

操作描述
Inner join 如果表中至少有一个匹配，就返回行
left join 也会从左表中返回所有值，即使右表中没有匹配
right join 也会从右表中返回所有值，即使左表中没有匹配

自连接：（了解）

自己的表和自己的表连接，核心：一张表拆为两张一样的表即可。

父类

categoryIdcategoryName
2 信息技术
3 软件开发
5 美术设计
   

子类

pidcategoryIdcategoryName
3 4 数据库
2 8 办公信息
3 6 web开发
5 7 PS设计

操作：查询父类对应的子类关系

父类子类
信息技术 办公信息
软件开发 数据库
软件开发 web开发
美术设计 PS技术 
-- 查询父子信息
SELECT a.`categoryName` AS '父栏目',b.`categoryName` AS '子栏目' FROM `school`.`category` AS a,`category` AS b WHERE a.`categoryId` = b.`pid`
6.分页和排序

排序：

-- ========== 分页 limit 和排序  order by ==========
-- 排序：升序ASC，降序DESC

-- order BY  通过哪个字段排序，怎么排
-- 查询的结果排序，降序

SELECT * FROM `school`.`student` ORDER BY id DESC

分页：

-- 为什么分页？
-- 缓解数据库压力，给人体验更好，瀑布流

-- 分页，每页只显示五条数据
-- 语法：limit 当前页，页面的大小
-- 网页应用：一般显示当前，总页数
SELECT * FROM `school`.`student` ORDER BY id ASC LIMIT 0,5；

-- 第一页  limit 0,5  （1-1）*5,5
-- 第二页  limit  5,5   (2-1)*5,5
-- 第N页   limit  (n-1)*5,5    (n-1)*pageSize,pageSize
-- 【pageSize：页面大小】
-- [(n-1)*pageSize：起始值]
-- 【n：当前页】
-- 【数据总数/页面大小 = 总页数】

语法：limit（查询起始下标，pageSize）

7.子查询

where（这个值是计算出来的）

本质：在where语句中嵌套一个子查询语句 where（select * from）

-- ======= where ========
-- 方式一：使用连接查询
SELECT s.`id`,s.`name`,`sex` 
FROM `school`.`student` AS s 
INNER JOIN `school`.`result` AS r ON s.id = r.id
WHERE r.`name` = '321'
ORDER BY id ASC

-- 方式二：使用子查询（由里既外）
SELECT s.`id`,s.`name`
FROM `school`.`student` AS s
WHERE s.`id`=(SELECT `id` FROM `school`.`result` WHERE `name` = '321')

分组和过滤：

-- 查询不同课程的平均分，最高分，最低分,平均分大于80
-- 核心：（根据不同的深程分组）
SELECT subjectName，AVG(studentResu1t) AS 平均分,MAX(StudentResu1t)AS 最高分 ,MIN(StudentResu1t) AS最低分
FROM result AS r
INNER JOIN `subject` sub
ON r.SubjectNO -- 通过什么字段来分组
HAVING 平均分>80

 

8.MySQL函数

  1. 常用函数

    -- ==== 常用函数 ====

    -- 数学运算
    SELECT ABS(-8)  -- 绝对值
    SELECT CEILING(9.4) -- 向上取整
    SELECT FLOOR(9.4) -- 向下取整
    SELECT RAND() -- 取0-1的随机数
    SELECT SIGN() -- 返回参数的符号

    -- 字符串函数
    SELECT CHAR_LENGTH('nihao') -- 字符串长度
    SELECT CONCAT('我','l')  -- 拼接字符产
    SELECT LOWER('rungshen') -- 小写字母
    SELECT UPPER('rungshen') -- 大写字母
    SELECT INSTR('rungshen','h') -- 返回第一次出现的字串的索引
    SELECT REPLACE('坚持就能成功','坚持','农历') -- 替换出现的字
    SELECT SUBSTR('检出就能成功啊啊啊啊',4,6) -- 返回指定的字符串（源字符串，截取位置，截取长度）
    SELECT REVERSE ('检出就能成功啊啊啊啊') -- 反转

    -- 查询姓周的同学，姓改为 邹
    SELECT REPLACE(studentname,'周','邹') FROM `school`.`student` WHERE studentname LIKE '周%'

    -- 时间和日期函数（记住）
    SELECT CURRENT_DATE() -- 获取当前日期
    SELECT CURDATE() -- 获取当前日期
    SELECT NOW() -- 获取当前时间
    SELECT LOCALTIME() -- 本地时间
    SELECT SYSDATE() -- 系统时间

    SELECT YEAR(NOW())
    SELECT MONTH(NOW())
    SELECT DAY(NOW)

    -- 系统
    SELECT SYSTEM_USER()
    SELECT USER()
    SELECT VERSION()

     

  2. 聚合函数（常用）

函数名称描述
COUNT( ) 计数
SUM( ) 求和
AVG() 平均值
MAX( ) 最大值
MIN( ) 最小值
...  
9.数据库级别MD5加密（扩展）

什么是md5？

主要增强算法复杂度，不可逆。

MD5不可逆，具体值md5是一样的。

MD5破解网站原理：背后有一个字典，MD5加密后的值，加密的前值

-- =====测试 MD5 加密====
-- 明文密码
INSERT INTO `school`.`testmd5`(`id`,`name`,`pwd`) VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- 加密
UPDATE `school`.`testmd5` SET pwd=MD5(pwd);

-- 插入时候加密
INSERT INTO `school`.`testmd5`(`id`,`name`,`pwd`) VALUES(4,'xiaomiung',MD5('123456'))

-- 如何校验：将用户传进来的密码，进行MD5加密，然后比对加密后的值
SELECT * FROM `school`.`testmd5` WHERE `name`='xiaomiung' AND pwd=MD5('123456')

DELETE FROM `school`.`testmd5` WHERE id =4
TRUNCATE `school`.`testmd5`
 
posted @ 2023-08-30 17:59  Y~~~  阅读(59)  评论(0)    收藏  举报