MySQL基础知识归纳

程序员文章站 2022-03-11 09:29:28

配置mysql的配置文件在位于安装目录下，my.ini文件中可以对mysql基本信息进行设置，注意更改完成后需要重启服务数据库SQL/*显示所有数据库*/SHOW DATABASES;/*进入数据库*/USE MYSQL1;/*显示所有表*/SHOW TABLES;/*查看当前所在库*/SELECT DATABASE();/*创建表*/CREATE TABLE student(id int,name varchar(20));/*查看表结构*/DESC student;/*...

文章目录

- - - 配置
    - 数据库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

连接查询

MySQL基础知识归纳

采用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基础知识归纳

索引

索引是帮助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

MySQL基础知识归纳

文章目录

配置

数据库SQL

常用SQL

查询SQL

单行函数

分组函数

连接查询

分页查询

索引

范式

备份

MD5加密

ODBC中遇到的错误_MySQL

php为什么选mysql作为数据库？ Mysql 创建用户方法_PHP教程

MySQL MyISAM表结构的恢复_MySQL

损坏数据的恢复操作过程总结_MySQL

新版mysql + apache + php Linux安装指南

excel导入mysql数据有关问题

mysql-分类配对的数据库建表问题

Mysql和网页显示乱码解决方法集锦_php技巧

利用硬链接原理秒删MySQL大文件_MySQL

php+mysql实现数据库随机重排实例，phpmysql重排实例_PHP教程