DQL查询语句

#DQL date query language 查询语句

    -- 查看所有数据库
    show databases;
    
    -- 查看正在使用的库
    select database();
    use db1;
    
    -- 建立表
    
        create table student(
             id int,
        		 name varchar(32),
        		 age int,
        		 sex  varchar(2),
        		 address varchar(8),
        		 math double(4,1),
        		 English double(4,1)	 
        );
    
    show tables;-- 显示当前数据库的所有表

-- 插入数据

insert into student VALUES(1,"马云",55,"男","杭州",66,78);
insert into student VAlues(2,"马化腾",45,'女','深圳',98,87);
insert into student VAlues(3,"马景涛",55,'男','深圳',56,77);
insert into student VAlues(4,"柳岩",20,'女','深圳',76,65);
insert into student VAlues(5,"柳青",20,'男','深圳',86,null);
insert into student VAlues(6,"刘德华",57,'男','深圳',99,99);
insert into student VAlues(7,"马德",22,'女','深圳',99,99);
insert into student VAlues(8,"德玛西亚",18,'男','深圳',56,65);

-- 修改数据
update student set address = '香港' where id = 3;
update student set address = '湖南' where id = 4;
update student set address = '湖南' where id = 5;
update student set address = '香港' where id = 6;
update student set address = '香港' where id = 7;
update student set address = '南京' where id = 8;

基础查询

SELECT NAME, age from student;-- 查询 姓名 

SELECT * from student;-- 查询全部 用*代替年龄

-- 去除重复的结果集 distinck:区别
SELECT distinct address from student; -- address完全一样，才可去重，如空格问题
SELECT distinct name,address from student; -- 同理，name,adress要完全一样


-- 计算 math 和 english 分数之和
select name,math,english,math+english from student;

-- 如果有null参与，计算结果都位null
select name,math,english,math+ IFNULL(english,0) from student;--  用IFNULL 将null替换

-- 起别名 列名 as 别名 注:可以省区as
select name,math 数学,english 英语,math+ IFNULL(english,0) 总计 from student;

条件查询

    -- 查询年龄大于20岁
    select * from student where age >20;
    
    select * from student where age >=20;
    
    --查询年龄等于20岁
    select * from student where age =20;
    
    --查询年龄不等于20岁
    select * from student where age !=20;
    
    select * from student where age <>20;
    
    --查询年龄大于20岁 小于30
    select * from student where age >=20 && age <=30;-- 不推荐
    
    select * from student where age >=20 and age <=30;
    
    select * from student where age between 20 and 30; -- 包含20 30
    
    -- 查询年龄22岁,19岁，25岁的信息
    select * from student where age=22 or age = 18 or age = 25;
    
    select * from student where age IN(22,18,25);
    
    --查询英语成绩为null
    select * from student where english = null; --不对null值不能用=  != 判断
    
    select *from student where english is null;
    
    select *from student where english is not null;

模糊查询

    -- 查询姓马的有那些?
    select * from student where name like '马%';-- %:0或多个字符
    
    select * from student where name like '马_';-- -：单个字符
    
    -- 查询姓名中包含德的人
    select * from student where name like '%德%';

排序查询

    select *from student order by age desc;-- 降序
    
    select *from student order by age asc; -- 升序（默认）
    
    select *from student order by age; -- 升序（默认）

聚合函数

    select count(id) from student; -- 统计出id不为null的数量
    select max(math) from student; -- 最大值
    select min(math) from student; -- 最小值
    select sum(math) from student; -- 和
    select avg(english) from student;-- 平均值 不算null

分组查询

select sex,count(id),avg(math) from student group by sex;
-- where在分组前限定，having在分组后限定
select sex,count(id),avg(math) from student where math > 70 group by sex;
select sex,count(id),avg(math) from student where math > 70 group by sex having count(id)>2;
select sex,avg(math),count(id) as 人数 from student where math > 70 group by sex having 人数>2;

分页查询

-- limit '方言' mysql特有
-- limit 开始索引, 每页查询的条数页
-- 开始索引 = （当前页码-1）* 每页显示的条数
select * From student limit 0,3;
select * From student LImit 6,3; -- 不足，也会将最后的部分显示出来