MySQL看这个

添砖JAVA666

已于 2024-01-09 18:07:01 修改

阅读量98

点赞数

文章标签： mysql 数据库 java

于 2023-04-14 22:16:06 首次发布

本文链接：https://blog.csdn.net/qq_53323072/article/details/130118001

版权

文章目录

一、MySQL基础语句
二、MySQL原理

一、MySQL基础语句

1、去重复，区间，升降序，函数，分页查询

SELECT address FROM stu;

--去除重复记录
SELECT DISTINCT address FROM stu;

-- 1.查询年龄大于等于20小于等于30岁的
SELECT * FROM stu WHERE age >=20 AND age <=30;
SELECT * FROM stu WHERE age BETWEEN 20 AND 30;

-- 2.查询年龄等于20,22岁
SELECT *FROM stu where age in (20,22);

-- 3.模糊查询：查询第一个字性马的.....  _ 表示占一个字符，% 表示任意
SELECT * from stu WHERE name LIKE '马%';
-- 第二字为花的人
SELECT * from stu WHERE name LIKE '_花%';
-- 查询名字包含 德 字
SELECT * from stu WHERE name LIKE  '%德%';

-- 4.ASC :升序排列（默认） 
--  DESC:降序排列；
	-- 按年龄升序排列
	SELECT * FROM stu order by age;

	-- 按年龄降序排列
	SELECT * FROM stu order by age DESC;

	-- 按数学成绩升序，若一样则英语降序
	SELECT * FROM stu order by math ASC,english DESC;



-- 5.聚合函数 ； count( )括号不能为空。  null不参与所有函数的运算
 	 -- 统计一共有多少人；
	SELECT COUNT(*) FROM stu;
	-- 查询最高最低
	SELECT MAX(math) from stu;
	-- 查询平均
	SELECT avg (math) from stu;
	-- 去重复查询数学最高分
	SELECT MAX(DISTINCT math) from stu;


-- 6.分组函数  （select中的字段不可以单独出现，必须出现在group语句中或者在组函数中）
	--  查询男女平均分
	SELECT sex,avg(math) FROM stu group BY sex;

	-- 查询男女平均分,以及人数
	SELECT sex,avg(math),count(*) FROM stu group BY sex;

	-- 查询男女平均分,以及人数,且数学成绩大于70才参与
	SELECT sex,avg(math),count(*) FROM stu WHERE math>70 group BY sex;

	-- 查询男女平均分,以及人数,且数学成绩大于70才参与分组，分组后人数要大于2
	SELECT sex,avg(math),count(*) FROM stu WHERE math>70 group BY sex HAVING count(*)>2;



-- 7.分页查询 ： 第一个数是起始索引=（当前页数-1）*每页条数，第二个数是每页条数
	-- 从1开始查询，查询3条数据
	SELECT * FROM stu LIMIT 0,3;

	-- 每页显示三条数据，从2开始查询
	SELECT * FROM stu LIMIT 1,3;

	-- 每页显示三条数据，从3开始查询
	SELECT * FROM stu LIMIT 2,3;

	-- 每页显示三条数据，从8开始查询
	SELECT * FROM stu LIMIT 7,3;

2、显隐内连接，嵌套，日期

-- 无关联字段：嵌套查询，
-- 有关联字段：隐式内连接（双表）.显示内连接（单双）

-- 1. 查询猪八戒的工资
select salary from emp1 where name = '猪八戒';

-- 2. 查询工资高于猪八戒的员工信息
select * from emp where salary > 3600;

select * from emp where salary > (select salary from emp where name = '猪八戒');

-- 3.查询 '财务部' 和 '市场部' 所有的员工信息
select did from dept1 where dname = '财务部' or dname = '市场部';

-- 4.查询入职日期是 '2011-11-11 00:00:00' 之后的员工信息
select * from emp1 where join_date > '2011-11-11 00:00:00' ;

-- 查询入职日期是 '2011-11-11 ' 之后的员工信息和部门信息
-- (隐式内连接)
select dname,NAME from (select * from emp1 where join_date > '2011-11-11' ) t1, dept1 where t1.dep1_id = dept1.did;

 -- （显示内连接 ）
SELECT * from  dept1 as a INNER JOIN emp1 as b on a.did=b.dep1_id ;


select a.user_name from wash_order as a  JOIN wash_order as b on a.phone_num=b.phone_num
WHERE a.washclothe_name='衬衣' and b.washclothe_name='裤子';

3、外键

举例：alter table goods constraint fk_goods_orders foreign key id reference orders(goods_id);
/*
	外键约束:
		* 外键用来让两个表的数据之间建立链接，保证数据的一致性和完整性

	
	-- 1.创建表时添加外键约束
	CREATE TABLE 表名(
		 列名 数据类型,
		 …
		 [CONSTRAINT] [外键名称] FOREIGN KEY(外键列名) REFERENCES 主表(主表列名) 
	); 

	-- 2.建完表后添加外键约束
	ALTER TABLE 表名 ADD CONSTRAINT 外键名称 FOREIGN KEY (外键字段名称) REFERENCES 主表名称(主表列名称);

	-- 删除约束
	ALTER TABLE 表名 DROP FOREIGN KEY 外键名称;	
*/
-- 删除表
DROP TABLE IF EXISTS emp;
DROP TABLE IF EXISTS dept;

-- 部门表
CREATE TABLE dept(
	id int primary key auto_increment,
	dep_name varchar(20),
	addr varchar(20)
);
-- 员工表 
CREATE TABLE emp(
	id int primary key auto_increment,
	name varchar(20),
	age int,
	dep_id int,

	-- 添加外键 dep_id,关联 dept 表的id主键
	CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCES dept(id)
		
);
-- 添加 2 个部门
insert into dept(dep_name,addr) values
('研发部','广州'),('销售部', '深圳');

-- 添加员工,dep_id 表示员工所在的部门
INSERT INTO emp (NAME, age, dep_id) VALUES 
('张三', 20, 1),
('李四', 20, 1),
('王五', 20, 1),
('赵六', 20, 2),
('孙七', 22, 2),
('周八', 18, 2);
-- ------------------
-- 删除外键
alter table emp drop FOREIGN key fk_emp_dept;

-- 建完表后，添加外键

alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);

4、系统函数

在这里插入图片描述

-- 8系统函数

SELECT LOWER("HELLO WORLD");

SELECT UPPER("hello");

-- 查询姓氏，从左往右取name字段第一个开始
SELECT LEFT(name,1) from stu;

-- 获取名字后一到两个字
SELECT SUBSTR(name,2,3) FROM stu;

-- 连接字符串
SELECT CONCAT(math,"分") from stu;

或者指定分隔符 concat(separator,str1,str2,.....) 


-- 字段长度
SELECT LENGTH(name) from stu;

5、日期相关

在这里插入图片描述

6、自定义函数

--  10自定义函数
CREATE FUNCTION test() RETURNS INT(11)
BEGIN
RETURN 888;
END

-- 调用自定义函数
SELECT test();

-- 删除自定义函数
DROP FUNCTION IF EXISTS test;

CREATE FUNCTION test02() RETURNS INT(11)
BEGIN
 DECLARE a int;
 SELECT COUNT(*) INTO a FROM stu;
RETURN a;
END

SELECT test02();

7、crud

insert  into book (name,description) values (#{name},#{descrition});
insert into book (name,description) values ('老人与海','顶顶顶顶'),('三体', '6666');

delete  from book where id= #{id};

update book set name=#{name},description=#{description} where id =#{id}

select *from book where id=#{id};

8、存储过程

存储过程是一个包括多条SQL语句的集合，用于特定表的特定操作，而函数一般用于数据处理，更多情况下是一条SQL语句，并且不涉及某一个特定的表。MySql的存储过程不具有原子性，出现错误时，不会回滚，需要自己编写事务保证原子性。

DROP TABLE  IF EXISTS users;

CREATE TABLE users(
id INT primary KEY auto_increment,
username VARCHAR(20),
password VARCHAR (32)
); 

DROP PROCEDURE if EXISTS pro01;
-- 存储过程
CREATE PROCEDURE pro01(userName VARCHAR(20) , pwd VARCHAR(32))
BEGIN
	
	INSERT INTO users (username , password) VALUES (userName,pwd);
END

-- 调用存储过程
CALL pro01('quanz','123456');

9、视图

CREATE or replace VIEW viewname AS
    SELECT 
        name,age
    FROM
        student
 union all
	SELECT 
    	*
	FROM
    	order;

10、union和union all的区别

union: 去重复
union all: 可重复

11、触发器

1、触发器 是一种特殊的存储过程。
2、与存储过程不同点：存储过程调用时需要调用SQL片段，而触发器不需要调用，当对数据库表中的数据执行DML操作时自动触发这个SQL片段的执行，无需手动调用。
3、在MySQL中，只有执行insert, delete, update操作时才能触发触发器的执行；
4、作用：可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

在这里插入图片描述

//当user表添加一行数据，则会自动在user_log添加日志记录
//定义触发器
create trigger trigger_test1 after insert on user for each row
insert into user_logs values(NULL,now(),'new');

-- 在user表添加数据，让触发器自动执行
insert into user values(2,'zbb','123456');



-- NEW
 drop trigger trigger_test3
create trigger trigger_test4 after update on user for each row
insert into user_logs values(NULL,now(),concat('有用户信息修改：新数据是',NEW.uid,NEW.username,NEW.password));

update user set password = '666666' where uid=3;

-- delete类型触发器
-- OLD
create trigger trigger_test5 after delete on user for each row
insert into user_logs values(NULL,now(),concat('有用户被删除，删除信息为：',OLD.uid,OLD.username,OLD.password));

delete from user where uid=3;

12、修改表字段

	alter table SubmitTs add (tst VARCHAR2(20));

	alter table SUBMITTS rename column tst to address;

	alter table submitts modify (address INT);

	alter table SUBMITTS drop (ADDRESS)

13、Case when end

	1:case 字段名 when 字段值 then 值1 else 值2 end 
	2:case 字段名 when 字段值 then 值1 
				when 字段值 then 值2 end 
	3:case when 条件 then 值1 else 值2 end
	
	select id, case when name='guoc' then '郭超' 
		or when name='quanz'  then '权总' end realyname

二、MySQL原理

1.MySQL存储引擎

1.Innodb：提供对数据库acid事务的支持，并且支持行级锁和外键约束。

2.MYIASM：不提供事务的支持，也不支持行级锁和外键。

3.MEMORY：数据存放在内存中，不过数据库重启或发生崩溃，数据会丢失。

2.MySQL索引（是帮助快速高效查找数据的数据结构）

B树和B+树的区别，Mysql为什么使用B+树？

**1.B树的特点**：
（1）.节点是排序的
（2）.一个节点可以存多个元素，并且也是排序的
**2.B+树:**
				（1）B树的特点都有。
				（2）叶子节点之间有指针。
				（3）叶子节点中存储了所有元素，并且拍好了序。

Mysql默认使用的是B+树索引的数据结构，因为B+树具有以上特点，而且B+树对数据进行了排序所有就可以提高查询速度。

MYsql索引类型
1.普通索引
2.唯一索引
3.主键索引
4.复合索引（组合索引）
5.全文索引

CREATE TABLE `NewTable` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`name`  varchar(20) NULL ,
`salary`  double(20,2) NULL ,
`joinDate`  datetime NULL ,
PRIMARY KEY (`id`),
-- 全文索引
FULLTEXT INDEX `full_name` (`name`) 

--唯一索引
-- UNIQUE INDEX  `full_name` (`name`)

--普通索引
-- INDEX  `full_name` (`name`)
);

-- 全文索引
SELECT  * from empdata where MATCH (jl) against ('666');

3.MySQL事务特性和隔离等级

1.原子性：事务的操作要么成功要么失败
2.一致性：数据库从一个一致性的状态到另一个一致性的状态；
3.隔离性：事务与事务之间相互隔离，一个事务的修改在提交之前，其他事务是不可见的
4.持久性：事务提交后，那么数据变化就会保存到数据库中。

1.RU ：Read uncommit 读未提交，读到其他事务未提交的数据，也叫脏读。
解决：写的时候加个排他锁，读的时候加个共享锁。
2．RC：read commit 读已提交，不可重复读解决了脏读的问题，只会读取已提交的事务。
3.RR :repeatable read:可重复读，这是mysql的默认级别，每次读取数据结果都是一样，但是可能会产生幻读。
4.serializable串行读，一个事务的开始，必须等待另一个事务的完成。

-- 事务隔离等级
-- 1.read uncommit
-- 2.read COMMIT
-- 3.repeatable READ
-- 4.serializable
set autocommit =FALSE;
SET SESSION transaction isolation level repeatable read ;

1.脏读：读到事务未提交的数据；
2.不重复读：在一个事务过程中，多次查询的结果不一样
3.幻读：假设事务A对某些行的内容作了更改，但是还未提交，此时事务B插入了与事务A更改前的记录相同的记录行，并且在事务A提交之前先提交了，而这时，在事务A中查询，会发现好像刚刚的更改对于某些数据未起作用，但其实是事务B刚插入进来的，让用户感觉很魔幻，感觉出现了幻觉，这就叫幻读。
解决：1.针对快照读不加锁的情况下mysql通过mvcc来避免幻读
2.针对当前读可以加锁间隙锁来避免幻读

4.MySQL的锁

1. 行锁()：
加锁粒度小，但加锁资源开销比较大，Innodb支持
a) 共享锁：（读锁），多个事务可以对同一个数据共享同一把锁。但是只能读不能改

select * from stu where id=1 lock in share mode;

//如果不走索引，例如：
select * from stu where name='小明' lock in share mode;
//则行锁会升级，会升级会类似表锁一样

b) 排他锁:（写锁），只有一个事务可以获取排他锁。Innodb会对updata/delete/insert语句自动添加排他锁

select * from stu where id=1 for update

c) 自增锁：通常针对Mysql当中的自增字段。如果有事务回滚，数据会回滚，但自增序列不会回滚。

2.表锁：
1.表共享读锁
2.表排他写锁
3.意向锁：Innodb自动添加的一种锁，就是表明表要加一些锁的操作了。

Lock table stu read/write;

3.全局锁：加锁之后，整个数据库实例都处于只读状态，数据变更操作也会被挂起。一般用于全库备份的时候。

flush tables with read lock;
 -- 手动解锁
unlock tables;

4.间隙锁：间隙锁 gaplock：锁的是一定的区间范围，而不是具体的记录，是为了防止产生幻读

5.Next_key(间隙锁+行锁)：键值在条件范围里，又不在条件范围内
比如：id只有1-50；select *from user id >49 for update; (则会把51-正无穷，负无穷到0锁起来，范围外的区间都会锁上不能插删改，如果有重复的例如走普通索引，则会锁区间边界)

添砖JAVA666

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL看这个

- 10 自定义函数RETURN 888;END-- 调用自定义函数-- 删除自定义函数RETURN a;END。
复制链接

扫一扫

MySQL看这个

文章目录

一、MySQL基础语句

1、去重复，区间，升降序，函数，分页查询

2、显隐内连接，嵌套，日期

3、外键

4、系统函数

5、日期相关

6、自定义函数

7、crud

8、存储过程

9、视图

10、union和union all的区别

11、触发器

12、修改表字段

二、MySQL原理

1.MySQL存储引擎

2.MySQL索引（是 帮助快速高效查找数据的数据结构）

3.MySQL事务特性和隔离等级

4.MySQL的锁

“相关推荐”对你有帮助么？

2.MySQL索引（是帮助快速高效查找数据的数据结构）