SQL语法笔记

基础查询

distinct去除重复行；
concat()使用concat包含多个字段作为一个字段输出；

select concat(last_name ,',', first_name ,',',salary) as 员工工资 from employees;

as 别名，as也可以省略但是加as可以提高可读性；
ifnull()函数：如果连接查询某个字段包含null，会导致该列的数据显示为空，使用ifnull判断一下可以有效解决该问题

select concat(department_id,',',last_name,',',manager_id) from employees ; 
-- 上图是不加ifnull函数，返回的值中有null 
select concat(department_id,',',last_name,',',ifnull(manager_id,0)) as 奖金率 from employees ;

limit 限制输出返回行输
字符型和日期型的常量值必须用单引号引起来，数值型不需要
isnull函数，判断某字段或表达式是否为null,如果是则返回1，否则返回0

select isnull(commission_pct), commission_pct from employees;

条件查询

select 查询字段 from 表名 where 筛选条件;

先运行from表名，先判断表是否存在，再判断条件是否成立，最后是查询

条件运算符：> < <> != >= <=;

-- 大于 
select concat(last_name,' ',first_name,': ',department_id,', ',salary) as 员工工资 from employees where salary > 12000; 
-- 小于 
select concat(last_name,' ',first_name,': ',department_id,', ',salary) as 员工工资 from employees where salary < 12000; 
-- 不等于 mysql官方推荐使用 <> 方式来表示不等于 
select concat(last_name,' ',first_name,': ',department_id,', ',salary) as 员工工资 from employees where salary <> 12000; 
select concat(last_name,' ',first_name,': ',department_id,', ',salary) as 员工工资 from employees where salary != 12000; 
-- 大于等于 
select concat(last_name,' ',first_name,': ',department_id,', ',salary) as 员工工资 from employees where salary >= 12000; 
-- 小于等于 
select concat(last_name,' ',first_name,': ',department_id,', ',salary) as 员工工资 from employees where salary <= 12000;

逻辑运算符：&& || ！ and or not ;

-- and 
select * from departments where department_id = 10 and manager_id = 200 ; 
-- or 
select * from departments where department_id=10 or location_id=1700 ; 
-- not 
select * from departments where not(department_id=10 or location_id=1700);

模糊查询：like , between and ,in , is null,is not null ;

/* 
%表示任意多个字符； 
_表示任意单个字符； 
ESCAPE可以指定转义符，不能使用%作为转义符； 
between and : 表示大于等于左边的值小于等于右边的值。 
*/ 
-- %任意多个字符 
select * from admin where username like '%oh%'; 
-- _任意单个字符 
select * from admin where username like '__h_' ; 
-- ESCAPE指定转义符,\也可以作为转义符 
select * from admin where username like 'ma_$_hu' ESCAPE '$' ; 
-- between and 
select concat(last_name,first_name,': ',salary) as 员工工资 from employees where salary between 10000 and 12000 ; 
-- in in内不能包含通配符 
select * from beauty where boyfriend_id in (8,9,3); 
-- is null 、is not null is只能搭配null使用 
select last_name ,first_name ,salary ,commission_pct from employees where commission_pct is null ; 
select last_name, first_name ,salary , commission_pct from employees where commission_pct is not null; 
-- <=> 安全等于，即可以判断null也可以判断其他值 
select last_name ,first_name ,salary , commission_pct from employees where commission_pct <=> null ; 
select concat(last_name,first_name,': ',salary) from employees where salary <=> 12000;

排序查询

order by 排序字段 asc | desc

-- asc 升序 
select * from employees order by salary desc ; 
-- desc 降序 
select * from employees order by salary asc;

按表达式排序

select * , salary * 12 *(1+ifnull(commission_pct,0)) as 年薪 from employees order by salary * 12 *(1+ifnull(commission_pct,0)) desc;

按照别名排序

select * , salary * 12 *(1+ifnull(commission_pct,0)) as 年薪 from employees order by 年薪 desc;

按照函数排序

select length(last_name) as 字节长度,last_name , salary from employees order by 字节长度 desc;

按多个字段排序

select * from employees order by salary desc , employee_id ;

常用函数

一、单行函数

字符函数

-- length(): 获取参数值的字节个数； 
select length(last_name) from employees; 
-- concat(): 拼接字符串 
select concat(last_name,',',first_name) as 姓名 from employees ; 
-- upper(): 将字符变大写 
select upper(`john`) ; 
-- lower(): 将字符变小写 
select lower('JOHN'); 
-- substr(): 截取从指定字符后面所有字符 
select substr('I love you km',3) ; 
-- substr(): 截取从指定字符后面指定长度的字符 
select substr('I love you km',3,4) ; 
select concat(upper(substr(last_name,1,1)) ,'_',substr(last_name,2),first_name) as 姓名 from employees; 
-- instr 返回子串第一次出现的索引，如果找不到返回零 
select instr('I love you km','km') ; 
select instr('I love you km','kmd') ; 
-- trim 删除指定字符前后的字符，默认删除前后的空格 
select trim(' zhangcuisan '); 
select trim('a' from 'aaazhangsuisanaaa'); 
-- lpad 用指定的字符实现左填充指定字符 
select lpad('km', 10 ,'love') ; 
-- rpad 用指定的字符实现右填充指定字符 
select rpad('km', 10 ,'love') ; 
-- replace 替换指定字符 
select replace('字符串','原来字符','想要替换成的字符') 
select replace('I love you km','km','kanmin') ; 
select replace('kmkmkmI love you km','km','kanmin') ;

数学函数

-- round(),四舍五入 
select round(浮点数,需要保留的小数点位数); 
select round(1.68) ; 
select round(1.6874,3) ; 
-- ceil(),向上取整，返回>=该参数的最小整数 
select ceil(1.68) ; 
-- floor(),向下取整，返回<=该参数的最小整数 
select floor(1.68) ; 
-- truncate(),截断 
select truncate(1.68973335,4); 
-- mod(),取余 
select mod(10,3) ;

日期函数

-- now(),返回当前系统日期加时间 
select now() ; 
-- curdate(),返回当前系统日期，不包含时间 
select curdate(); 
-- curtime(),返回当前系统时间，不包含日期 
-- year(),获取时间年份 
select year(now()) ; 
-- month(),获取时间月份 
select month(now()) ; 
-- monthname(),获取英文的时间月份 
select monthname('2019-08-09'); 
-- str_to_date(),将字符转换为日期 
select str_to_date('1998-08-09','%Y-%m-%d') as 年月日; 
-- date_format(),将日期转换为字符 
select date_format(now(),'%Y年%m月%d日') as 年月日 ; 
select last_name, first_name , salary , commission_pct, date_format(hiredate,'%Y年%m月%d日') as 入职日期 from employees where commission_pct is not null ; 
-- datediff(),计算两个日期之间相差的天数 
语法是 select datediff('第一个时间','第二个时间') ;计算的是由第一个时间减去第二个时间的值 
select datediff(now(),'19980129') as 活了多少天;

其他函数

-- version(),查看当前版本号 
select version(); 
-- database(),查看当前数据库 
select database(); 
-- user(),查看当前用户 
select user();

流程控制函数

-- if(),语法if(‘表达式’,'[true]输出这个信息','[false]输出这个信息') 
select last_name, commission_pct,if(commission_pct is null ,'没奖金，呵呵','有奖金,嘻嘻') as 奖金情况 from employees ; 
-- case(),语法 
case 要判断的字段或表达式 
when 常量1 then 要显示的值1或语句1 
when 常量2 then 要显示的值2或语句2 
... 
else 要显示的值n或语句n 
end 
select salary ,department_id ,case department_id when department_id = 30 then salary * 1.1 when department_id=40 then salary * 1.2 when department_id = 50 then salary * 1.3 else salary end as 新工资 from employees ; 
case 
when 条件1 then 要显示的值1或语句1 
when 条件2 then 要显示的值2或语句2 
。。。 
else 要显示的值n或语句n 
end 
select last_name ,salary ,department_id ,case when salary > 20000 then 'A' when salary > 15000 then 'B' when salary > 10000 then 'C' else 'D' end as 工资级别 from employees ;

二、分组函数

sum() 求和

select sum(salary) from employees;

avg() 平均值

select round(avg(salary),2) from employees;

min() 最小值

select min(salary) from employees;

max() 最大值

select max(salary) from employees;

count() 非null总行数

select count(commission_pct) from employees;

特点

sum、avg 一般用于处理数值型
max、min、count可以处理任何类型
以上分组函数都可以忽略null值
可以实现和distinct搭配去重的运算

和分组函数一同查询的字段要求是group by 后的字段

分组查询

/* 
语法： 
select 分组函数，列（要求出现在group by的后面） 
from 表 
[where 筛选条件] 
group by 分组的列表 
[order by 子句] 
注意： 
查询列表必须特殊，要求是分组函数和group by 后出现的字段 
*/

-- 分组后的筛选,数据源是分组后的结果集，意思是将分组查询输出后，对输出结果进行过滤，使用having关键字； 
select count(*) as a,department_id from employees group by department_id having count(*) > 2 ; 
-- 分组前筛选,数据源是原始表，需要把where 语句写在groub by 之前 
select max(salary),job_id from employees where commission_pct is not null group by job_id ; 
-- 分组函数做条件肯定是放在having子句中 
-- 按多个字段分组,group by 子句支持单个字段分组和多个字段分组 
select avg(salary), department_id ,job_id from employees group by job_id ,department_id ; 
select avg(salary), department_id ,job_id from employees group by job_id ,department_id order by avg(salary) desc ;

连接查询

/* 
含义：多表查询， 
按功能分类： 
> 内连接： 
> 等值连接 
> 非等值连接 
> 自连接 
> 外连接： 
> 左外连接 
> 右外连接 
> 全外连接 
> 交叉连接 
*/

SQL 92语法

等值连接

select beauty.boyfriend_id as a , name , boyName from beauty , boys where beauty.boyfriend_id =boys.id; 
select last_name, department_name from employees as a , departments as b where a.department_id=b.department_id ;

非等值连接

select e.job_id , j.job_title , e.salary 
from employees as e , jobs as j 
where e.job_id=j.job_id 
and salary between 10000 and 20000
order by salary desc;

自连接

-- 连接同一个表内的数据
select e.employee_id , e.last_name , m.manager_id from employees as e , employees as m where e.employee_id=m.manager_id;

sql 99语法

sql 99语法
/*
语法：
            select 查询列表
            from  表1  别名 【连接类型】
            join  表2  别名 
            on 连接条件
            【where 筛选条件】
            【group by 分组】
            【having 筛选条件】
            【order by排序列表】

分类：
内连接：inner
外连接：
                左外：left outer 
                右外: right  outer 
                全外：full outer 
交叉连接：cross
*/

等值连接

select last_name , department_name 
from employees as e 
inner join departments as d 
on e.department_id=d.department_id;
-- 添加筛选条件
select last_name ,job_title 
from employees as e 
inner join jobs as d 
on e.job_id=d.job_id
where last_name like '%e%';
-- having 字句和排序
select department_name ,count(*)
from departments as d 
inner join employees as e 
on d.department_id=e.department_id
group by department_name
having count(*) > 3 
order by count(*) desc ;
-- 三表连接
select last_name, department_name, job_title
from employees as e 
inner join departments as d 
on e.department_id=d.department_id
inner join jobs as j 
on e.job_id=j.job_id
order by department_name desc ;

非等值连接

SELECT salary ,grade_level 
from employees as e 
inner join job_grades as j 
on e.salary between j.lowest_sal and j.highest_sal;
-- 非等值连接加筛选
select count(*),grade_level 
from employees as e 
inner join job_grades as j 
on e.salary between j.lowest_sal and j.highest_sal
group by grade_level
having count(*) > 20 ;

自连接

select e.last_name as 员工名,e.employee_id as 员工ID , m.last_name as 领导名 ,m.employee_id as 领导编号
from employees as e 
inner join employees as m 
on e.manager_id = m.employee_id;

左外连接

select d.*,e.employee_id 
from departments as d 
left outer join employees as e 
on d.department_id=e.department_id
where e.employee_id is NULL;

右外连接

select d.* , e.employee_id 
from employees as e 
right outer join departments as d 
on e.department_id=d.department_id
where e.employee_id is NULL ;

子查询

含义：出现在其他语句中的select语句，称为子查询或内查询

外部的查询语句，称为主查询或外查询

分类

/*
按子查询出现的位置：
            select 后面：
                    仅仅支持标量子查询
            from后面：
                    支持表子查询
            where或having后面：
                    标量子查询（单行）
                    列子查询（多行）
                    行子查询
            exists后面（相关子查询）
                    表子查询
按结果集的行列数不同：
            标量子查询（结果集只有一行一列）
            列子查询（结果集只有一列多行）
            行子查询（结果集有一行多列）
            表子查询（结果集一般为多行多列）
*/

一、where或having后面

/*
1.标量子查询（单行子查询）
2.列子查询（多行子查询）
3.行子查询（多列多行）
特点：
    1.子查询放在小括号内
    2.子查询一般放在条件的右侧
    3.标量子查询，一般搭配着单行操作符使用
    >  <  >= <= = <>
    列子查询，一般搭配着多行操作符使用
    in , any/some, all 
    4.子查询的执行优先于主查询执行，主查询的条件用到了子查询的结果
*/

标量子查询

select min(salary),department_id 
from employees 
group by department_id 
having min(salary) >  (
 select min(salary) 
 from employees 
 where department_id = 50 
);

列子查询（多行子查询）

select last_name 
from employees 
where department_id in (
     select department_id
     from departments
     where location_id IN (1400,1700)
);
-- any 关键字，只要是查询出来的子句中的任意一个值就可以
select last_name,employee_id ,job_id ,salary 
from employees 
where salary < any (
select DISTINCT salary 
from employees 
where job_id = 'IT_PROG'
)
and job_id<>'IT_PROG';
-- all关键字，条件要满足all后面的所有字段才成立，类似于下面这条sql中，比所有的员工工资都低
select employee_id , last_name,job_id,salary 
from employees 
where salary < all(
select salary 
from employees 
where job_id='IT_PROG'
);

行子查询（结果集一行多列或多行多列）

-- 行子查询，必须使用等号连接子查询，并且子查询返回的数据是同一行数据
select * 
from employees 
where (employee_id,salary)=(
select min(employee_id),max(salary)
from employees
);

二、select 后面

select场景

-- select 后面仅仅支持标量子查询
select d.* ,(
             select count(*)
             from employees as e 
             where e.department_id=d.department_id
             ) as 个数
from departments as d ;

三、from后面

场景一

-- 在from 后面添加子查询，将子查询出来的数据作为查询表，必须要将子查询出来的数据取别名，如果子查询中有分组函数，分组函数字段也需要取别人
select pjgz.*, j.grade_level
from 
     (select avg(salary) as g ,department_id 
     from employees 
     group by department_id) as pjgz
     inner join job_grades as j 
on pjgz.g
between j.lowest_sal and j.highest_sal;

四、exists后面的子查询

语法

exists(完整的查询语句)
结果：
1或0

示例

-- 使用exists示例
select department_name 
from departments  as d 
where exists(
 select * from 
 employees as e  
 where  d.department_id=e.department_id
);
select boyName 
from boys as m
where NOT exists (
 select boyfriend_id
 from beauty as b 
 where b.boyfriend_id = m.id
);
-- 使用in示例
select department_name 
from departments as d  
where d.department_id in (
 select department_id 
 from employees
);

select m.* 
from boys as m
where m.id  not in (
 select boyfriend_id 
 from beauty as g 
);

分页查询

/*
语法：
select 查询列表
from 表
【join type join 表2
on 连接条件
where 筛选条件
group by 分组字段
having 分组后的筛选
order by 排序的字段】
limit offset,size ; 
limit 放在SQL语句的最后，offset要显示条目的起始索引（起始索引从0开始），size要显示的条目个数
*/
select  * 
from employees 
limit 0,5;

select * 
from employees 
limit 10,15;

联合查询

联合查询
UNION联合 合并：将多条查询语句的结果合并成一个结果
语法：
查询语句1
UNION
查询语句2
UNION
...

应用场景：
需要查询多个表，多个表之间没有直接的关联关系

特点：
1.要求多条语句的查询列数一致
2.要求多条查询语句的查询的每一类的类型和顺序最好一致
3.union关键字默认去重，如果使用union all 可以包含重复项

DML语言

/*
数据操作语言：
插入： insert 
修改：update 
删除：delete
*/

一、插入语句

/*
语法：
insert into 表名(列名,...) values(值1,...); 
*/

-- 单行插入
insert into test VALUES(13,'李小龙',1000);
-- 多行插入
insert into test values(14,'杜康',100),(15,'铜雀',50);
-- 从select语句中获取值插入
insert into test select id,name,boyfriend_id from beauty where id > 7;

二、修改语句

/*
语法：
update 表名 set 列名=值 where 筛选条件;
*/

三、删除语句

/*
语法：
delete from 表名 where 筛选条件;
delete 删除的数据自增长列从断点数继续
*/
/*
语法：
truncate table 表名;
*/

DDL

/*
数据定义语言
库和表的管理
一、库的管理
创建、修改、删除
一、库的管理
1.库的创建
语法：
-- create database 库名;
-- create database if not exists 库名;
2.库的修改
RENAME DATABASE books to 新库名;
更改库的字符集
alter database books character set  utf8; 
3.库的删除
drop database 库名;
drop database if exists 库名;

二、表的管理
创建、修改、删除
创建：create

-- 1.表的创建
/*
create table  表名(
    列名 列的类型(长度) 约束,
    列名 列的类型(长度) 约束,
    列名 列的类型(长度) 约束
)
*/
create table if not exists book(
    id int,
    bname VARCHAR(20),
    price  double,
    authorId INT,
    publishDate datetime
);

修改：alter
/*
alter table 表名 add |  drop | modify | change column 列名 列类型 约束;
1.修改列名
alter table book change column publishdate pubDate DATETIME;
2.修改列的类型或约束
alter table book modify column pubDate timestamp; 
3.添加新列
alter table author add column annual double; 
4.删除列
alter table author drop column annual ; 
5.修改表名
alter table author rename to book_author ; 
*/
删除：drop
/*
表的删除
drop table book_author; 
drop table if exists book_author; 
*/

常见的数据类型

/*
数值型：
            整型
            小数：
                        定点数
                        浮点数
字符型：
            较短的文本：char、 varchar 
            较长的文本：text、 blob（较长的二进制数据）
日期型：        
*/

一、整型

-- 1字节TINYINT、2字节SMALLINT、 3字节MEDIUMINT、4字节int | integer、8字节bigint
/*
如何设置无符号和有符号
create table tab_int(
    t1 int 
)
1.如果不设置无符号还是有符号，默认是有符号，如果想设置无符号，需要添加unsigned关键字
2.如果插入的数值超出了整型的范围，会报out of range 异常，并且插入临界值
3.如果不设置长度，会有默认的长度
长度代表了显示的最大宽度，如果不够用会用0在左边填充，但必须搭配zerofill使用！
drop table if exists tab_int;
create table   if not exists tab_int(
    t1 int(7) zerofill,
    t2 int(8)  ZEROFILL
);
*/

二、小数

/*
分类：
1.浮点型
float(M,D)
double(M,D)
2.定点型
dec(M,D)
decimal(M,D)
特点：
1.
M ：整数部位+小数部位
D ：小数部位
如果超过范围，则插入临界值,mysql5.7直接报错值超过临界值无法插入
2.
M和D都可以省略
如果是decimal,则M默认为10，D默认为0
如果是float和double，则会根据插入的数值的精度来决定精度
3.定点型的精确度较高，如果要求插入数值的精度较高如货币运算等则考虑使用
*/

create table if not exists tab_float (
    t1 float(5,2),
    t2 double(5,2),
    t3 decimal(5,2)
);

insert into tab_float values(123.45,123.45,123.45);
select * from tab_float;
insert into tab_float values(1111.45,11111.45,1111.45);

三、字符型

/*
较短的文本；
CHAR
VARCHAR
较长的文本：
text
blob(较大的二进制)
*/
其他：
binary和varbinary用于保存较短的二进制
enum用于保存枚举
set用于保存集合
create table if not exists tab_char(
    c1 char,
    c2 varchar(200)
);

	写法	M的意思	特点	空间的耗费	效率
char	char(M)	最大的字符数，可以省略，默认为1	固定长度的字符	比较耗费	高
varchar	varchar(M)	最大的字符数，不可以省略	可变长度的字符	比较节省	低

四、日期型

/*
分类：
date 只保存日期
time 只保存时间
year 只保存年
datetime 保存日期+时间
timestamp 保存日期+时间
*/
CREATE TABLE `tab_date` (
  `d1` datetime DEFAULT NULL,
  `d2` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

常见约束

/*
含义：一种限制，用于限制表中的数据，为了保证表中的数据的准确和可靠性
分类：六大约束
            NOT NULL：非空，用于保证该字段的值不能为比如姓名、学号等
            DEFAULT：默认，用于保证该字段有默认值
            PRIMARY KEY：主键，用于保证该字段的值具有唯一性，并且非空
            UNIQUE：唯一，用于保证该字段的值具有唯一性，可以为空
            CHECK：检车约束
            FOREIGN KEY：外键，用于限制两个表的关系，用于保证该字段的值必须来自于主表的关联列的值，在从表添加外键约束，用于引用主表中某列的值
约束的添加分类：
            列级约束：
                六大约束语法上都支持，但外键约束没有效果
            表级约束：
                除了非空、默认其他的都支持
*/
-- 主要需要记住主键、默认、唯一、非空四个约束
 CREATE TABLE `major` (
  `id` int(11) NOT NULL,
  `majorName` varchar(20) DEFAULT NULL
);

create table stuinfo (
    id int primary key, -- 主键
    stuName varchar(20) not null ,-- 非空
    gender char(1) check(gender='男' or gender='女'),-- 检查
    seat int unique ,-- 唯一
    age int default 18, -- 默认约束
    majorId int references major(id) -- 外键
);
show index from stuinfo ;

MySql语法笔记

MySql语法笔记

SQL语法笔记

基础查询

条件查询

排序查询

常用函数

一、单行函数

二、分组函数

特点

分组查询

连接查询

SQL 92语法

sql 99语法

子查询

分类

一、where或having后面

二、select 后面

三、from后面

四、exists后面的子查询

分页查询

联合查询

DML语言

一、插入语句

二、修改语句

三、删除语句

DDL

常见的数据类型

一、整型

二、小数

三、字符型

四、日期型

常见约束