如何查询 mysql 的年龄范围 mysql 查询大于 30 岁的年龄
文章目录
- 一、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的锁
一、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;
-- 字段长度
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锁起来 ,范围外的区间都会锁上不能插删改,如果有重复的例如走普通索引,则会锁区间边界)