MySQL基础知识归纳

• 生活家

文章目录

        • 配置
        • 数据库SQL
        • 常用SQL
        • 查询SQL
        • 单行函数
        • 分组函数
        • 连接查询
        • 分页查询
        • 索引
        • 范式
        • **备份**
        • MD5加密

配置

mysql的配置文件在位于安装目录下，my.ini文件中可以对mysql基本信息进行设置，注意更改完成后需要重启服务

数据库SQL

/*显示所有数据库*/
SHOW DATABASES;
/*进入数据库*/
USE MYSQL1;
/*显示所有表*/
SHOW TABLES;
/*查看当前所在库*/
SELECT DATABASE();
/*创建表*/
CREATE TABLE student(
id int,
name varchar(20));
/*查看表结构*/
DESC student;
/*查看MySQL版本*/
SELECT VERSION();

常用SQL

/*SQL顺序*/
SELECT [ALL | DISTINCT]
{
  * | TABLE.* |[TABLE.field1[AS alias1][,TABLE.field2[AS alias2]][...]]}
FROM table_name[AS table_alias]
	[left | right | inner join table table_name2]			--联表查询
	[where ...]												--指定条件
	[group by ...]											--分组
	[having]												--过滤分组必须满足的次要条件
	[order by ...]											--排序
	[limit {
  [Offeset,]row_cout | row_countOFFSET offset}]	--分页

/*insert*/
INSERT INTO student(id,name) values(1, 'xiaohua');

/*update*/
UPDATE student SET name = 'xiaohua' WHERE id = 1;

/*delete 表结构与索引不变 不会重新设置自增列 */
DELETE FROM student WHERE id = 1;

/*truncate 清空表数据 表结构与索引不变 重新设置自增列，但计数器不会归零 不会影响事务*/
TRUNCATE TABLE student

/*取别名*/
SELECT id AS 学生号,name 姓名 FROM student;

查询SQL

/* select from 基础查询****************************************************** */
SELECT * FROM student;
/*取别名*/
SELECT id AS 学生号,name 姓名 FROM student;
/*去重*/
SELECT DISTINCT id AS '学 号' FROM student;
/* + 的用法仅有运算符，一方为null，结果为null,下方’123‘转换整数失败即为整数0，结果为1*/
SELECT '123'+1;
/*字符串拼接*/
SELECT CONCAT(last_name,first_name) FROM student;
/*字符串拼接需要注意null问题,first_name为null时候显示''*/
SELECT CONCAT(last_name,IFNULL(first_name,'')) AS 姓名 FROM student;

/* select from where 条件查询********************************************************** 条件运算：> < = != <>(!=) >= <= 逻辑运算:and or not 模糊查询:like between and in is null */
/*查询学生中第三个字符为e，第五个字符为a的学生信息*/
SELECT * FROM student WHERE name like '__e_a%';
/*查询年龄在[17,22]区间的学生，and的简约写法*/
SELECT * FROM student WHERE age BETWEEN 17 AND 22;
/*查询张三，李四，王五的信息，or的简约写法*/
SELECT * FROM student WHERE name IN('张三'，'李四'，'王五')；
/* =, != 无法判断null，null需要使用is，is not判断,<=>是安全等于，不受null限制*/

单行函数

/* 排序查询*********************************************************** order by asc 升序 desc 降序 */
SELECT * FROM student ORDER BY age ASC;
/*按年薪高低显示员工信息和年薪*/
SELECT *, salary*12*(1+IFNULL(commission_pct,0)) AS 年薪 
FROM employees
ORDER BY 年薪 DESC;
/*先按学生年龄降序，再按学生名字字节长度升序显示学生信息*/
SELECT *
FROM student
ORDER BY age DESC, LENGTH(name) ASC;

/* 字符函数********************************************************** length() 返回字节数 concat() 拼接字符串 upper() 字符转大写 lower() 字符转小写 substr(str,n) 字符截取 instr(str,s) 返回子字符起始位置 trim() 去空格 lpad(str,n,s) 以s按n长度左填充str replace() 替换 */

/* 数学函数*************************************************************** round() 四舍五入 ceil() 向上取整 floor() 向下取整 truncate(1.22,1)截断,结果为1.2 Mod(m,n) 取余,m%n(取余结果符号与m一致) */

/* 日期函数**************************************************************** now() 当前日期+时间 curdate() 返回当前日期 curtime() 返回当前时间 year(now()) month(now()) monthname(now()) str_to_date('2020-12-2','%Y-%m-%d') 字符转日期 date_format('2020/12/2','%Y年%m月%d日') 日期转字符 */

/* 流程控制函数************************************************************** if(false,'yes','no') 按三元函数部署 case用法： java中: switch(变量){ case 常量1：语句1;break; ..... default:语句;break; } sql中：（case 后写条件是java的switch,不写条件是多重else if） case 变量/条件 when 常量1 then 语句1;/值1 ....... else 语句;/值 end; */
SELECT salary 原工资, department_id 部门,
CASE department_id
WHEN 1 THEN salary*1.1
WHEN 2 THEN salary*1.2
ELSE salary
END AS 新工资
FROM employees;

分组函数

常用函数

/* 分组函数********************************************************************** sum() avg() count() max() min() 特点： sum,avg一般用于处理数据类型 max,min,count处理类型不限 分组函数都忽略null值 count(*) 不会忽略null count(1) 不会忽略null，相比count(1)通常效率更高 */

group by 列名 having 条件

/* HAVING分组条件********************************************************************** 过滤分组必须满足的次要条件,作用类似where，放置在GROUP BY 之后 */
/*查询平均分大于80的课程的平均分以及最高分*/
SELECT subject_name, AVG(student_result) AS 平均分，MAX(student_result) AS 最高分
FROM result r
INNER JOIN subject sub
ON r.subject_id = sub.subject_id
GROUP BY r.subject_id
HAVING 平均分 > 80

实例：

SELECT SUM(salary),AVG(salary)，COUNT(DISTINCT salary) FROM empleyees;
/*查询员工表中最早入职时间与最晚入职时间相差天数*/
SELECT DATEDIFF(MAX(hiredate),MIN(hiredate)) FROM empleyees;
/*查询各工种平均工资*/
SELECT AVG(salary) FROM empleyees GROUP BY job_id;
/*查询每个领导手下，有奖金的工资最高的工资*/
SELECT MAX(salary)， mannager_id
FROM empleyees;
WHERE commission_pct IS NOT NULL;
GROUP BY manager_id

连接查询

采用sql99标准，按功能分三类

  • 内连接

    • 等值连接

      /*查询每个工种的工种名和员工的个数，并且按员工个数降序*/
SELECT job_title, COUNT(*)
FROM employees e, jobs j
WHERE e.job_id = j.iob_id
GROUP BY job_title
ORDER BY COUNT(*) DESC;

    • 非等值连接

      /*查询员工工资与工资级别*/
SELECT salary, gred_level
FROM employees e, job_grades g
WHERE salary BETWEEN g.lowest_sal AND g.highest_sal

    • 自连接

       /*查询员工名与其上级名称*/
 SELECT e.name employees_name, m.name manager_name
 FROM employees e,employees m
 WHERE e.manager_id = m.employees_id

  • 外连接

    外连接查询显示主表所有记录，如果从表有和它匹配的，则显示匹配的值，没有和它匹配的显示null

    • INNER连接

      inner join == join，交集

      /*查询 JAVA第一学年，成绩要大于80的学生信息，按成绩排名（学号，姓名，课程名称，分数）*/
SELECT s.student_id, student_name, subject_name, result 
FROM student s
INNER JOIN subject sub
ON s.student_id = sub.student_id
INNER JOIN result r
ON sub.subject_id = r.subject_id
WHERE sub.subject_name = 'JAVA第一学年' AND r.result > 80
ORDER BY r.result DESC

    • 左外连接

      left join 左边的是主表

    • 右外连接

      right join 右边的是主表

    • 全外连接

      两表的合集

  • 交叉连接

    cross join，笛卡尔乘积

分页查询

limit 起始值，页面大小

规律：第n页 LIMIT (n-1)*pageSize, pageSize;

​ pageSize：页面大小

​ (n-1)*pageSize：起始值

​ n：当前页

​ 总页数 = 数据总数 / 页面大小

SELECT * FROM student
FROM student
WHERE sex = '男'
ORDER BY age ASC
LIMIT 1, 10;

/*查询 JAVA第一学年，课程成绩排名前十，成绩要大于80的学生信息（学号，姓名，课程名称，分数）*/
SELECT s.student_id, student_name, subject_name, result 
FROM student s
INNER JOIN subject sub
ON s.student_id = sub.student_id
INNER JOIN result r
ON sub.subject_id = r.subject_id
WHERE sub.subject_name = 'JAVA第一学年' AND r.result > 80
ORDER BY r.result DESC
LIMIT 1, 10;

事务

什么是事务?**

可以简单理解为一组sql的集合，具有原子性，要么都成功，要么都失败

ACID原则：原子性，一致性，隔离性，持久性 (脏读，幻读)

原子性：要么都成功，要么都失败

一致性：事务操作前后的状态保持一致，如转钱前后钱的总额不能改变

隔离性：事物执行过程中不受其他操作影响

持久性：若事务没有提交应当回滚为原始数据,若已经提交应当持久化到数据库(不可逆)

脏读：一个事务读取了另一个事务未提交的数据（如两地同时取钱，最终总额不一致)

幻读：一个事务读到了别的事务新插入的数据导致前后不一致(一般是行影响,多了一行)

不可重复读：一个事务对事务的多次读取结果不同

怎么用?

SET autocomit = 0			--关闭事务自动提交设置
START TRANSACTION			--开启事务
...
SAVEPOINT 保存点名称			 --创建保存点
--回滚到保存点
ROLLBAKE TO SAVEPOINT 保存点名称
--删除保存点
RELEASE SAVEPOINT 保存点名称
...
COMMIT						--提交事务
ROLLBACK					--回滚事务
SET autocommit = 1			--开启事务自动提交设置

索引

索引是帮助MySQL高效获取数据的数据结构

  • 分类

    • 主键索引(PRIMARY KEY)

      • 主键不可重复，只能有一个列作为主键

    • 唯一索引(UNIQUE KEY)

      • 避免列名重复

    • 常规索引(KEY/INDEX)

      • 默认索引

    • 全文索引(FULLTEXT)

      • 特定引擎下才有，快速定位数据

  • 使用

    • 建表的时候给字段增加索引

      SHOW INDEX FROM student				--显示表所有索引信息

--增加一个全文索引 列名（索引名） 
ALTER TABLE school.student ADD FULLTEXT INDEX student_name(student_name);

--EXPLAIN 分析SQL执行状况
EXPLAIN SELECT * FROM student 												 --常规索引
EXPLAIN SELECT * FROM student WHERE MATCH(student_name) AGAINS('小华')		--全文索引

    • 给建好的表某字段添加索引

      CREATE INDEX id_daName_tableName_name on tableName(name);

  • 索引原则

    • 索引不是越多越好
    • 不要对常变动的数据加索引
    • 小数据量的表不用加索引
    • 索引一般加在常常被查询的字段上

  • 索引的数据结构（了解）

    • hash类型的索引
    • Btree(innoDB引擎默认索引)

范式

范式：符合一定规范的形式

  • 第一范式：（原子性）每一列不可再分割

  • 第二范式：（主键）有唯一标识符

  • 第三范式：（外键）一个表中的数据不能同时是其他表里的非主键数据

  • 反三范式：有时候为了查询效率，可以违背第三范式

备份

  • 直接拷贝物理文件

  • 可视化工具中备份

  • 命令行mysqldump备份(会导出SQL命令的合集文件)

    #导出数据库[表]
#mysqldump -h主机 -u用户名 -p密码 数据库 [表名1 ...] > 物理磁盘位置/文件名
mysqldump -hlocalhost -uroot -p123456 school student >D:/database/mysql.sql

#导入数据库[表]
#登录情况下：
source D:/database/mysql.sql
#未登录的情况下 
mysql -u用户名 -p密码 数据库 < 物理磁盘位置/文件名

MD5加密

什么是MD5?

数据库级别MD5加密，主要增强算法复杂度和不可逆性

不可逆的特性让它安全性更高,我们无法通过数据库知道用户输入的密码是什么

如何用？

--明文密码
INSERT INTO user VALUES(1, 'howe', '123456'), (2, 'xiaohong', '123456');
--MD5加密,sql执行后数据库中将储存被加密后的密码
UPDATE user SET pwd = MD5(ped) WHERE id = 1;
--插入数据时就进行MD5加密
INSERT INTO user VALUES(3, 'xiaohua', MD5('123456'));
--校验
SELECT * FROM user WHERE name = 'xiaohua' AND pwd = MD5('123456');

本文地址：https://blog.csdn.net/weixin_42049458/article/details/110633228

赞 (0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐