5 mysql 查询语句

1.DML：对数据进行增删改查

提示：Execute执行 Execute and Suppress 执行并且抑制这个警告
person表的结构

/*
DML:Data Manipulation Language 数据操作语言，对数据进行 增删改查操作，因为査询的操作太频繁和复杂，将查询的操作独立成为DQL
*/
use db1109;//person表在db1109数据库里
-- 插入数据insert into，对于自增的字段，可以插入0或者null，id会自己增加
-- 在指定的字段里插入数据
insert into person(id,name,married) value(null,'jerry',true);
-- 如果给所有的字段都插入数据，字段列表可以省略，但是注意value的顺序必须要和字段的顺序保持一致，避免数据类型不匹配
insert into person/*(id,name,sex,height,hobbies,married,birthday)*/value(0,'merry","女'1.7,'RAP,篮球",0，'2000-02-14');
-- 修改数据 update
update person set sex='男'; -- 没有条件抑制，将所有人性别修改为男
update person set sex='女' where id=4 or id =6; -- 根据指定的条件修改数据
update person set hobbies='rap' where id between 2 and 5;-- 删除数据 delete
/*
都不推荐用，区别：
delete 删除数据以后，如果再插入数据，主键会在上一次的基础上自增
tuncate 清空数据，可以理解为将表drop，然后再create,表会到初始状态，主键id从0开始增加，truncate不能回滚
*/
delete from person where id =3; -- 条件删除
delete from person; -- person表里所有的数据都删除，但是person表依然存在
truncate person;
drop table person; -- 直接将表删除-- 复制一张表
create table p1 like person;--创建一个和person表结构相同的p1表
insert into p1 select *from person;--将person表里所有的数据都查询出来，然后插入到p1表

2.DQL：对数据进行查询

DQL： DataQueryLanguage 数据查询语言，是整个数据库最主要常用的语句，主要就是 select语句，配合一些子语句完成复杂的查询效果
from子句:用来指定数据源
where子句:用来过滤查询条件
having子旬:和where功能相同，也是用来过滤查询条件
order by子句:排序
limit /offset子句:设置分段查询
group by子句:分组
union子句:用来拼接多个查询结果
join...on子句:用来实现多表联合查询/*
通常情况下，select后面都需要添加from语句(mysql中可以不加，Oracle里必须加)，
指定数据源dual是一个虚拟表，为了保证select语句的完整性
from用来指定数据源可以是一个表，也可以是多个表，还可以是一个查询结果
*/
select 1+1 from dual; select 1+1;也是2，oracle中不能用，mysql可以，不推荐
select id,name,sex from person;
select * from person;-- 查所有数据
select * from person where name='merry';`是反引号，如果表名或字段名是关键字，必须加它，其它情况加不加都行
create table `student` (`id` int unsigned primary key auto_increment,`name` char(32) not null unique,`gender` enum('男', '女') not null,`city` char(32) not null,`description` text,`birthday` date not null default '1995-1-1',`money` float(7, 2) default 0,`only_child` boolean-- 是否是独生子
);
-- value插入一行数据 values插入多行数据
insert into `student`
(`name`, `gender`, `city`, `description`, `birthday`, `money`, `only_child`)
values
('张三', '男', '北京', '班长', '1997/10/1', rand() * 100, True),
('李四', '女', '上海', NULL, '1995/3/2', rand() * 100, True),
('王五', '女', '北京', '班花,不骄傲', '1995/4/4', rand() * 100, False),
('刘二', '男', '重庆', '超爱吃火锅', '1998/10/5', rand() * 100, False);create table score(
id int unsigned primary key auto_increment,
math int,
english int
);-- order by 用来对查询结果进行排序
select * from student order by money /*asc*/; --将查询结果按照money升序排序，asc可以省略，默认就是升序排序
select * from student order by money desc;-- 查询结果按照money降序排序-- limit和offset语句 用来对数据进行分段查询 offset元素偏移量
select * from student limit l; -- 查询前l行数据
select * from student limit l offset o;-- 跳过前o行，取后面的前l行
select * from student limit o,l;-- 跳过前o行，取后面的前l行，当offset省略的时候，第一个表示offset，第二个表示limit
# select * from student offset o limit l; 不对，没有这个写法，limit一定要在offset的前面select * from student limit 3;  -- 查询3个数据
select * from student limit 3 offset 5;  -- 偏移5个，然后再查询3个，前5个不查
# select * from student offset 5 limit 3;  limit一定要在offset的前面
select * from student limit 3,5;  -- 偏移3个，查询5个  当limit和offset简写时，第一个数字表示offset，第二个表示limit

函数

/*
函数可以理解为方法 面向过程中称为函数，面向对象里称为方法
MySQL两数分为两大类:
单行函数: 输入一个数据，得到一个结果
聚合函数:输入多个数据，得到一个结果
*/
-- 数值相关函数
/*
函数            功能
ABS(x)         返回x的绝对值
CEIL(x)        返回大于x的最大整数值，向上取整
FLOOR(x)       返回小于x的最大整数值，向下取整
MOD(x，y)      返回 x/y 的模
RAND()         返回0到1内的随机值
ROUND(x,y)     返回参数x的四舍五入的有y位小数的值
TRUNCATE(x,y)  返回数字x截断为y位小数的结果
*/
select abs(-45),ceil(3.2),floor(7.9),round(3.141592653,4),rand() from dual;
-- 日期相关的函数 用的比较多 datadiff 返回起始时间 和结束时间 之间的天数
-- unix_timestamp 获取到当前时间的时间戳(单位是秒) 1970-01-01 00:00:00 UTC(国际日期标准时间)距离现在的秒数
-- from_unixtime(0) 距离1970-01-01 00:00:00 UTC(国际日期标准时间) 0秒的时间 ，因为是东八区，快8个小时，所以是8点
select curdate(),curtime(),now(),unix_timestamp(),from_unixtime(0) from dual;

-- 字符串相关的函数
select concat('a','b','c') from dual; -- abc
-- 数据库相关的函数 password()函数在mySQL8.0里废弃了 ma5,返回字符串md5值
select database(),version(),user(),md5('1232423434') from dual;

/*
分组函数: group by 常用单行函数一块使用
group_concat()将分组后的数据，拼接成为字符串,如果不写的话(1.只会显示分组后的第一个 2.有的平台会报错,因为数据被分组后，不能展示全部数据 ，只根据分组名查可以)
使用as '别名’可以给査询结果起一个别名
*/
select group_concat(name) as 'names' , -- 别名可以使用单引号
max(money) as "rich",  -- 可以使用双引号
min(money) as poor, -- 可以不使用引号
avg(money) 平均值, -- as可以省略
std(money) "std money", -- 如果别名里有空格(不推荐)，引号不能省略
gender from student group by gender;
# count用来统计分组以后每个组里非空数据的个数,如果要准确的统计分组数据个数，推荐count(id),因为id通常是主键不能为空
select count(id) id_count,
count(description),
count(city), -- count默认是不去重的,查改分组下有几个数据
count(distinct city), -- 去重计数
city from student group by city;

有的报错
有的只显示分组后第一个数据

/*
MySQL里的流程控制语句
if(判断条件,语句1,语句2):不像java里的if语句，而像java里的三元表达式
ifnull(值1，值2):如果值1不是null,就显示值1:如果值1是null,就显示值2
*/-- if语句的使用
select if(3 >2,'ok','good') from dual;
select name,money,if(money >60,'较富','较穷') money_level from student;
ifnull 可以认为是给查询结果设置默认值
select ifnull('ok','good') from dual;
select ifnull(description,"帅") from student;
-- 类似于java里的if---else语句结构
select money,casewhen money<60 then '富'when money >=60 and money <80 then '穷' when money >=80 and money <=100 then '最富'else'错误'end as money_level
from student;
-- 类似于java里的switch...case语句
select name,gender,case genderwhen '男' then 'male'when '女' then 'female'else '错误'end as english_gender
from student;

/*
查询语句里设定判断条件时，可以使用where语甸，也可以使用having语句
只能使用where不能使用having的场景:
当判断条件使用的字段不再查询的结果集里时，只能使用where，不能使用having
只能使用having不能使用where的场景:
1.查询条件使用到了字段的别名时，只能使用having，不能使用where
2.查询语句里使用了 group by进行分组，判断条件只能使用 having,不能使用where
*/
-- where和having都能使用的场景
select id,name,gender,birthday from student where id=1;
select id,name,gender,birthday from student having id=1;
-- 只能使用where不能使用naving的场景
select name,gender,birthday from student where id=1;
select name,gender,birthday from student having id=1; --报错。having使用的判断字段(id)不在查询的结果集里
-- 只能使用having不能使用where
select name as n,gender as g,birthday as b from student where n='郭德纲'; -- 不行
select name as n,gender as g,birthday as b from student having n='郭德纲';
select name,gender from student where gender='男';
select group_concat(name),gender from student group by gender where gender='男'; -- 报错
select group_concat(name),gender from student group by gender having gender='男';

/*
多个表格里的数据联合查询
1.union用来纵向拼接两个表格里的数据
2.from后面设置多个数据源，自连接(不推荐)
3.join...on语句用来实现多表查询
*/
/*
一；union:用来将多个表格的查询结果纵向拼接
特点:
1、两个表里的字段个数必须要一样
2.两个表字段的类型可以不一致，查询的结果是字符串
3.如果两个表存在完全相同的数据，只显示一次
*/
select * from t1 union select * from t2;
-- 二 from的多表连接联合查询 自连接
select * from student,score; -- 笛卡尔积，A表的数据每个都和B表的对应起来
select * from student,score where student.id=score.id;
/*
三 join...on多表连接查询
inner join 内连接,inner可以省略。会显示左右表共有的数据
left outer join 左外连接,outer可以省略。显示左右表共有，以及左表特有的数据
right outer join 右外连接,outer可以省略。显示左右表共有，以及右表特有的数据
full join 全连接，两个表所有的数据都会显示。MYSQL不支持全连接，可以查左连接再union右连接
*/
-- 内连接
select * from student inner join score on student.id = score.id;
select * from student left outer join score on student.id = score.id; -- 左外连接
select * from student right outer join score on student.id = score.id; -- 右外连接
select * from student left outer join score on student.id = score.id  where score.id is null; -- 左表特有的数据
select * from student right outer join score on student.id = score.id where student.id is null; -- 右表特殊的数圳
-- 左连接 union 右连接=全连按
select * from student left outer join score on student.id = score.id
union
select * from student right outer join score on student.id = score.id;-- from子查询:数源来自一个查询结果,需要给查询结果起一个别名
-- 查上海的学生的信息和成绩
select * from
(select student.*,score.english,score.math from student join score on student.id = score.id) as ss
where ss.city='上海';-- ss表中字段名也不能一样(student表中和score表中都有id)，所以没有查score表的id
select * from student join score on student.id=score.id where city='上海';-- where子查询:判断条件来源一个查询结果，数学大于80的学生
select * from student where id in (select id from score where math > 80);-- view视图的使用:当某个操作经常使用时，可以创建视图，存储这个结果
-- 可以理解为将查询结果保存到了一个表里,当数据源里的数据变化的时候，视图里的数据有也会变化
create view view_student_score as
(select student.*,score.math,score.english from student join score on student.id =score.id);
-- 可以查看普通表和视图表
show tables;
-- 查看视图表的建表语句
show create view view_student_score; 
-- 删除视图表
drop view view_student_score;
-- 查询每个城市中，数学最高分
select city,max(math)from view_student_score group by city;
-- 查询id，这里面的id并不是最高分的id,而是城市分组查询后，城市第一个人的id
select id,city,max(math) as mm from view_student_score group by city;
-- 题
--1. 查询每个城市中，数学最高的学生信息
select * from  view_student_score
join(select city,max(math) as mm from  view_student_score group by city) as ss 
where  view_student_score.city =ss.city and view_student_score.math = ss.mm;
-- 不能查询到每个城市的数学最高分的学生信息,只要math等于max(math)就会查出来，并没有匹配城市
select id,name,city,math from  view_student_score where math in
(select max(math) from  view_student_score group by city);
-- 根据id进行查询，也不对，只能拿到该城市第一个人的id,不能拿到最高分的id,因为没有对id进行分组
select id,city,max(math) as mm from view_student_score group by city;
-- 2.计算男生和女生的数学和英语平均分
select gender,avg(math),avg(english) from view_student_score group by gender;
-- 3.求最大和最小的学生相差多少天
select max(birthday) from student;-- 查出来是最小的学生 是按时间戳进行判断的 1970年距离1998-10-05的秒数
select min(birthday )from student;-- 查出来是最大的学生 查出来是 1990-04-01 1970年距离1990-04-01的秒数
select datediff((select max(birthday) from student), (select min(birthday) from student)) as diff from dual;
-- 简化版本 因为两个查询都是来自于student,同一个源
select datediff(max(birthday),min(birthday)) as diff from student;

3.TPL：事物处理语言

/*
TransactionProcessLanguage 事务处理语言
事务的特性：1.原子性 2.一致性 3.隔离性 4.持久性
*/
create table user(
id int primary key auto_increment,
name varchar(32),
balance int);
-- jack给tony转10块钱
update user set balance=balance - 10 where name='jack';-- jack的账户减少10块钱
update user set balance=balance + 10 where name='tony';-- tony的账户增加10块钱
假设转一半的时候，服务器突然崩咯，第一条执行完成，第二天还没有执行。10块钱就找不到了。
把这两条语句放到一个事务里，保证数据的一致性，只能同时成功或者同时失败 
每条sql语句都是一个独立的自动提交事务，如果需要手动的管理事务
-- 1.关闭事务的自动提交
set autocommit =false;
-- 2.开启一个事务
begin;/*或者是 start transaction;*/ 
update user set balance=balance - 10 where name='jack';-- jack的账户减少10块钱
update user set balance=balance + 10 where name='tony';-- tony的账户增加10块钱
commit; -- 3 将事务提交
rollback; -- 回滚事务,
set autocommit =true; -- 设置自动提交在mysql中只有存储引擎为InnoDB支持事务,mysql默认的存储引擎就是InnoDB
建一个MyISAM存储引擎的
creat table t3(
id primary key auto_increment,
name varchar(32)
)engine MyISAM;
show create table t3;