mySQL数据库基本查询语言汇总

1) DDL(Data Definition Language)数据定义语言
     

   用来定义数据库对象：数据库，表，列等。关键字：create, drop,alter 等

库的DDL操作
        创建库: create database 库名； create database if not exists 库名；
        创建库并制定字符类型：create database 库名 character set 字符集名；
        查询所有数据库：show databases;
        查询特定数据库的字符集名或创建语句：show create database 库名;
        修改库的字符集：alter database 库名 character set 字符集名;
        删除库：drop database 库名; drop database if exists 库名;
        使用库: 查询当前库名: select database(); 使用数据库：use 库名;
表的DDL操作：
        创建：create table (if not exists)表名(列名 数据类型);      

       （创建时间属性注意事项） date:日期，只包含年月日，yyyy-MM-dd  
        datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss    timestamp:时间戳 类型 包含年月日时分秒     yyyy-MM-dd HH:mm:ss
        复制：create table 表名 like 旧表名; 
        查询表： 查询名称: show tables; 查询表结构: desc 表名; 查看表字符集: show create table 表名;
        修改表：修改表名:alter table 表名 rename to 新表名; 修改表的字符集: alter table 表名 character set 字符集名
        添加一列: alter table 表名 add 列名 数据类型;

        修改列名:alter table 表名 change 列名 新列名 列名数据类型
        只修改数据类型: alter table 表名 modify 列名 列名数据类型; 删除列: alter table 表名 drop列名;
        删除表: drop table (if exists) 表名;  

2) DML(Data Manipulation Language)数据操作语言

        用来对数据库中表的数据进行增删改。关键字：insert, delete, update 等
        增添表内数据: insert into 表名(列名1，列名2) values(值1，值2);

                                insert into 表名 values(值1，值2)，(值1，值2);
        删除数据: delete from表名 where ;一行一行删除   truncate from 表名;全部删除 建一张新表
        修改数据: update 表名 set 列名1 = 值1，列名2 = 值2 where;

3) DQL(Data Query Language)数据查询语言

      *  执行次序
        from>where>group by>聚合函数>having>select>order by>limit
      * 书写次序
        from>where>group by>select>聚合函数>having>order by
-- 准备数据
CREATE TABLE student ( 
  id int,  -- 编号 
  name varchar(20), -- 姓名 
  age int, -- 年龄 
  sex varchar(5),  -- 性别 
  address varchar(100),  -- 地址 
  math int, -- 数学 
  english int -- 英语 
); 
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男', '杭州',66,78),(2,'马化腾',45,'女','深圳',98,87),(3,'马景涛',55,'男','香港',56,77),(4,'柳岩',20,'女','湖南',76,65),(5,'柳青',20,'男','湖南',86,NULL),(6,'刘德华',57,'男','香港',99, 99),(7,'马德',22,'女','香港',99,99),(8,'德玛西亚',18,'男','南京',56,65),(9,'k_ing',25,'男','苏州',100,100);

select  挑选 选择
  类似于java sout 将结果打印到控制条
                  形成一张虚拟的表
  select  字段,常量,表达式,函数
from   从哪查
1.全表查询
select * from student;
2.单个字段查询
select `name` from student;
3.多个字段查询
select name,age,address from student;
select id,name,age,sex,address,english,math from student
4.查询常量
select 'john' ;
select *,1 from student;
5.查询表达式
select 100%98;
取别名
   取别名的好处是 1.见名知意
                             2.便于区分
select 100%98 as 结果;

select name as 姓名 , address as 地址 from student

select 100%98 as '结 果';
6.查询函数
select version();
select database();
7.配合distinct 去重  后面多个字段的时候 是将多个字段拼接的结果去重
查询表中所涉及的地址有哪一些
select address from student;

select distinct address from student;
查询表中所涉及的性别有哪一些
select distinct sex from student;

select distinct age,address from student;

select name,address from student
将 姓名和地址 拼接到一列中输出
select concat(name,'-',address) as 姓名和地址 from student
select name+math from student;
+ 在mysql里面 只有一个含义 就是计算，在java里面 + 只有有一方为字符串类型就是拼接
select 1+1;    2
select '1'+1;   2
select 'a'+1;   1
select 'a'+'a'; 0
  Mysql + 计算时 有强制行为将数据类型进行转化数值型，如果能转化成功，那么转化之后的结果相加，如果能转化不成功则直接置为0，再相加。
select 1+null; null
--求总分
select math+english from student;

条件查询
  select 
    查询列表
  from 
     表名
  where 
     筛选条件;
筛选条件
  1.条件表达式
     > < = !=   <>     <=>
  2.逻辑表达式  连接多个条件表达式
    &&  都为true 结果为true   and
    ||  一边为true            or 
     !  取反                  not
  3.模糊查询
     LIKE
     between AND
     in 
     is null

1.条件表达式
案例1： 查询年龄>30岁的学生信息
select * from student where age>30;
案例2： 查询不住在杭州的学生姓名和地址
select name,address from student where address != '杭州'
select name,address from student where address <> '杭州'  --不建议

按逻辑表达式筛选
 案例1：查询数学分数大于80并且小于90分的学生姓名,年龄，和数学分数
select name,age,math from student where math>80 && math<90
select name,age,math from student where math>80 and math<90
 案例2: 查询年龄不在20-30岁之间的学生，或者数学成绩大于90分的学生信息
select * from student where age<20 or age>30 or math>90;
select * from student where not (age>=20 and age<=30) or math>90;
5.模糊查询
like 像  在不使用特殊字符情况下 就类似于=
 支持一些通配符
 %  任意字符出现任意次数
 _  任意一个字符

案例1：查询员工名中包含字符德的学生信息
select * from student where name like '%德%'

案例2: 查询员工中第二个字为德的人学生信息
select * from student where name like '_德%'

案例3: 查询员工中第二个字为德的人学生信息且名字为两个字的

select * from student where name like '_德'

案例4.查询员工中名字长度为3的学生信息
select * from student where name like '___'
案例5 查询员工中名字里带有_的学生信息 如果有特殊字符出现 需要转移
select * from student where name like '%\_%';

-- between and
案例1：查询年龄在18 到 30 岁之间的学生信息
select * from student where age>=18 and age<=30;
select * from student where age between 18 and 30;

select * from student where age<=30 and age>=18;
in
案例1：查询住在 杭州, 香港，深圳的学生信息
select * from student where address ='杭州' or address='香港' or address='深圳'

select * from student where address in ('杭州','香港','深圳')
select * from student where address in ('杭州','苏州')

is null 是否为null
查询英语成绩为null的学生信息
select * from student where english = null; 错误
<=> 安全= 的含义 是能比较Null 值
select 1<=>1;
select * from student where english <=> null;  -- 不建议

select * from student where english is null;  -- 建议
select * from student where english is not null;

排序查询
    select 
      查询列表
    from
      表
    [where 筛选条件]
    order by 排序列表 [asc|desc] asc 正序 desc 倒序 默认按正序排序
案例1:查询学生信息,要求按数学成绩从高到低排序

select * from student order by math desc

案例2:查询学生信息,要求按数学成绩从高到低排序,当数学成绩相同时按英语成绩正序排
 
select * from student order by math desc,english asc

 案例3:查询男性学生信息 ，并按照数学成绩从高到低排序
select * from student where sex='男' order by math desc

案例4:查询学生信息，要求按照总分从高到低排序
order by 排序的东西 必须在select 中查询
order by 将挑选出来的结果 进行全局排序
select * ,math+english as 总分 from student order by 总分 desc
null值怎么处理
select ifnull(2,1); -- 如果为空，则输出1，否则输出2

select ifnull(english,0) from student;
select * ,math+ ifnull(english,0) as 总分 from student order by 总分 desc

函数  对应 java api
函数的好处是什么
      1.隐藏了实现细节
      2.能够反复使用
关心函数的什么
      1.叫什么
      2.有啥用
函数进行分类
      1.单行函数
        1.字符函数
        2.数字函数
        3.日期函数
        4.流程控制
        5.其他函数
      2.统计函数
        1.count
        2.max
        3.min
        4.sum
        5.avg 
单行函数
   1.字符函数
    length 字节数长度
    select length('john');
    select LENGTH('你好');
    select LENGTH(name) from student;
    concat 拼接
    select CONCAT('你好','再见');
    upper lower
    select upper('ABCD');
    select lower('abcd')
    nihao zaijian
    select concat(upper('nihao'),lower('zaijian'));
    -- 按照名字的长度进行排序,学生信息
    select *,length(name) from student order by length(name) desc
    mysql 是从1开始  limit 0
    select substr('2011-11-11',1,7)
    select substr('李莫愁爱上了陆展元',7,3)

    instr 
    select instr('张无忌赵敏爱上了赵敏','bbbb赵敏');
    trim 前后去字符 默认情况下前后去空格
    select length(trim('    a b c d      '));
    select trim('aa' from 'aaaaaab c daaaaaaa');
    lpad rpad 向左向右补全
    select lpad('aaaa',10,'*')
    select rpad('aaaa',10,'*')
    replace 替换
    select replace('张无忌爱上了周芷若','周芷若','赵敏');
-- 数学函数
1. round
   select round(1.9);
2. ceil  向上取整  >= 该数的最小整数
   select ceil(-1.1);  
3. floor 向下取整  <= 该数的最大整数
   select floor(-1.1);
4. truncate  截断
   select truncate(1.6999,2)
5. mod  a-a/b*b
   select mod(-10,-3)  

-- 日期函数
1 select now(); 当前日期+时间
2 select curdate(); 当前日期
3 select monthname(now())
4 str_to_date:将日期格式的字符转换成指定格式的日期
  select str_to_date('9/13/2004','%m/%d/%Y')
5. date_format : 标准的日期格式转化成字符串
  select date_format('2020-01-01','%Y/%m/%d');
-- 其他函数
select version();
select database();
select user();
-- 流程控制函数
 if
select if(10<5,'大于','小于');  三元运算符

case

   switch(变量){
       case 常量1：执行1 ; break;
       case 常量2：执行2 ; break;
       ....
       dafult:执行 n;
}

case 列 
    when 常量1 then 结果1
    when 常量2 then 结果2
    ...
    else 结果n
end
案例 根据不同的地区，将数学分数乘以不同的系数(杭州 1.1 深圳 1.2 香港 1.3 其他不变)

SELECT
    NAME,
    math,
    address,
    CASE address
WHEN '杭州' THEN
    math * 1.1
WHEN '深圳' THEN
    math * 1.2
WHEN '香港' THEN
    math * 1.3
ELSE
    math
END
FROM
    student;

-- 聚合函数
 1.count  统计个数 统计这张表有多少行数据
 select count(*) from student;
 2.max
 select max(math) from student;
 3.min
 select min(math) from student
 4.sum
 select sum(math) from student;
 5.avg 
 select avg(math) from student;
1. 聚合函数能不能放在一起用
select count(*),max(math),min(math),sum(math),avg(math) from student;
2. 聚合函数是否全部忽略null值 所有聚合函数都在忽略Null值
  select min(ifnull(english,0)) from student;
  select sum(english) from student;
  select avg(ifnull(english,0)) from student;
3. 聚合函数里面能放什么类型的参数
   count(*) 所有类型都已
   max  min 数值型和字符型
   select max(name) from student;
   select min(name) from student;
   sum  avg  数值型
   select sum(name) from student;
   select avg(name) from student;
4. 聚合函数能和普通列一起查询 聚合函数不能和普通列一起查询  歧义  有歧义还不如报错
   select name,max(math) from student;
5. 聚合函数能搭配distinct 来使用
   select count(distinct address),count(address) from student;

-- 分组查询
select 分组函数,列(要求出现在group by 后面)
from 表
[where 筛选条件]
group by 分组列表
[having 筛选条件]
[order by 排序列表]

案例1:查询不同性别的数学最高分和英语最高分
select sex,max(math),max(english) from student group by sex;
案例2：统计不同地区的学生个数
select address, count(*) from student group by address; 

案例3:统计来自香港的学生并按照性别求出数学平均分
select sex,avg(math) from student where address='香港' group by sex;

案例4:统计不同地区，不同性别学生的数学平均分
select address,sex,avg(math) from student group by address,sex;
案例5:统计不同地区学生的数学平均分并要求平均分大于80
select address,avg(math) from student group by address;
having 筛选 分组后过滤
where  筛选 分组前过滤
select address ,avg(math) as avg_math from student group by address having avg_math >80;

执行次序
from>where>group by>聚合函数>having>select>order by>limit
书写次序
from>where>group by>select>聚合函数>having>order by

案例5:统计不同地区学生的数学平均分并要求平均分大于80
select
  address,
  avg(math) avg_math
from
  student
group by
  address
having
  avg_math>80
案例6：统计来自香港的学生按照性别求数学平均分，并要求平均分大于80
-- 写法1
select
  sex,
  avg(math) avg_math
from 
  student
where 
  address='香港'
group by
  sex
having
  avg_math>80

-- 写法2
select
  address,
  sex,
  avg(math) avg_math
from
  student
group by 
  address,sex
having 
  address='香港' and  avg_math>80

   
案例7：按学生姓名的长度分组，统计每一组学生个数（group by 后面可以接函数）
select
  length(name),
  count(*)
from 
  student
group by 
  length(name)
   
案例8:统计不同地区，不同性别学生的数学平均分，并且要求按照数学平均分从大到小排序
SELECT
  address,
  sex,
  avg(math) avg_math
from 
  student
group by
  address,sex
order by
  avg_math desc
-- 分组查询能不能查 group by 之外的字段  歧义
select
  name,
  sex,
  max(math)
from
  student
group by 
  sex
-- 分页查询
    select
        字段列表
    from
        表名列表
    [where
        条件列表]
    [group by
        分组字段]
    [having
        分组之后的条件]
    [order by
        排序]
    limit
        分页限定
案例1:显示前3条学生信息    
select * from student limit 3;
select * from student limit 3,4;

案例2:显示数学成绩前3的学生信息
select * from student order by math desc limit 3
执行次序
from>where>group by>聚合函数>having>select>order by>limit
书写次序
from>where>group by>select>聚合函数>having>order by>limit

页码 page  行数 size
select * from student limit (page-1)*size,size;

多表查询

use test;
# 创建部门表
    CREATE TABLE dept(
        id INT ,
        NAME VARCHAR(20),
        location_id int
    );
    
    INSERT INTO dept (id,NAME,location_id) VALUES (1,'开发部',1),(2,'市场部',2),(3,'财务部',3);
# 创建位置表
    create table location(
       id int,
       location_name varchar(25)
    );
    insert into location(id,location_name) values(1,'北京'),(2,'深圳'),(3,'上海');
# 创建员工表
    CREATE TABLE emp (
        id INT ,
        NAME VARCHAR(10),
        gender CHAR(1), -- 性别
        salary DOUBLE, -- 工资
        join_date DATE, -- 入职日期
        dept_id INT
    );
    INSERT INTO emp(id,NAME,gender,salary,join_date,dept_id) VALUES(1,'孙悟空','男',7200,'2013-02-24',1);
    INSERT INTO emp(id,NAME,gender,salary,join_date,dept_id) VALUES(2,'猪八戒','男',3600,'2010-12-02',2);
    INSERT INTO emp(id,NAME,gender,salary,join_date,dept_id) VALUES(3,'唐僧','男',9000,'2008-08-08',2);
    INSERT INTO emp(id,NAME,gender,salary,join_date,dept_id) VALUES(4,'白骨精','女',5000,'2015-10-07',3);
    INSERT INTO emp(id,NAME,gender,salary,join_date,dept_id) VALUES(5,'蜘蛛精','女',4500,'2011-03-14',1);
#创建薪水等级表    
   create table salary_level(
     salary_level varchar(3),
     lowest_sal int,
     highest_sal int
   );
   
   insert into salary_level values('A',2000,4000);
   insert into salary_level values('B',4000,6000);
   insert into salary_level values('C',6000,8000);
   insert into salary_level values('D',8000,10000);

SELECT * FROM emp,dept WHERE emp.dept_id = dept.id;
select * from emp e join dept d on e.dept_id = d.id;
案例.查询员工表的名称，性别,部门名称
select e.name,e.gender,d.name from emp e join dept d on e.dept_id = d.id;
查询所有男性员工的名称，性别,部门名称
select e.name,e.gender,d.name from emp e join dept d on e.dept_id = d.id where gender = '男';
统计员工的平均工资，按照部门名称分组，并且按平均工资从高到低排序
select d.name,avg(e.salary) avg_s from emp e join dept d on e.dept_id = d.id group by d.name order by avg_s desc;
求每个人的薪资级别
select e.name,s.salary_level from emp e join salary_level s on e.salary>=s.lowest_sal and e.salary < s.highest_sal;-- 只能左闭右开
查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
select e.*,p.name from emp e left join dept p on e.dept_id = p.id;
select e.*,p.name from emp e right join dept p on e.dept_id = p.id;
查询员工信息，最高的工资的
select max(salary) from emp;
select * from emp where salary = (select max(salary) from emp);
查询员工工资小于平均工资的人
select * from emp where salary<(select avg(salary) from emp);
查询'财务部'和'市场部'所有的员工信息
select * from emp where dept_id in (select id from dept where name ='财务部'or name = '市场部');
查询员工入职日期是2011-11-11日之后的员工信息和部门信息
select e.*,p.name from dept p join (select * from emp where join_date < '2011-11-11' ) e on e.dept_id = p.id;
select e.*,p.name from emp e join dept p where e.dept_id = p.id and e.join_date < '2011-11-11';