[TOC]

1.介绍

1. SQL语言全称structed query language
2. 分为DDL，DML，DCL
3. 常见的数据库分为关系型

2.常见DDL

2.1对DB的操作

SHOW DATABASES;
CREATE DATABASE IF NOT EXISTS sqltest;
DROP DATABASE my_db
USE sqltest;

2.2对table的操作

CREATE TABLE student(sid int primary key,
name varchar(20),gender char(2),age int,grade int);

#显示数据库里所有表
show tables;
#显示创建student表的语句
show create table student;
#显示student表的所有列信息
desc student;
#删除表
drop table student;
#重命名表
rename table `student` to `stu`;

#修改表增加列
alter table stu add ranking int;
#修改表的列名change用来改列名
alter table stu change sid studentID int primary key;
alter table stu change ranking `排名` int;
#修改表删除列
alter table stu drop 排名;
#修改表某一列的类型modify用来改类型
alter table stu modify sid tinyint not null

3.常见DML

SELECT "Hello SQL!"

3.1SELECT

普通查询

%匹配多个字，_匹配一个

SELECT `name` FROM `teachers`

SELECT `name`,`student_count` FROM `courses`

SELECT DISTINCT `country` FROM `teachers`

SELECT * FROM `courses` WHERE `student_count`>1000

SELECT * FROM `courses` WHERE `name`="Artificial Intelligence"
#起别名查询，用于多表查询
SELECT * FROM product as p;
#查询列也可以起别名，会以别名显示
SELECT pname as "商品名" FROM product
#所有price增加10进行显示
SELECT price+10 as newPrice FROM product
#查询所有包含“裤”的商品
select * from product where pname like "%裤%"
#查询第二个是“蔻”字的商品
select * from product where pname like "_蔻%"
#查询所有category_id是null的
select * from product where category_id is null
#order by可以加多个字段
#第一个为主要条件，第二个是次要条件
#order by一般放在最后，limit除外
#去重排序
select distinct * from product order by price desc

排序查询

聚合查询

聚合查询是针对列的查询。五个聚合函数：

• count() 它不统计值为null的列
• sum() 它忽略值为null的列
• min() 其它同上
• max()
• avg()

#查询商品总条数
select count(pid) from product
#查价格>200
select count(price>200) from product
#求category_id为c001的所有商品的价格总和
select sum(price) from product where category_id="c001"
#求最小价格
select min(price) from product
#求平均
select avg(price) from product

分组查询

• group by会按照分组列，将表分成若干个临时表
• group by后面跟多个字段，则所有字段都相同才会分到一组
• select后只能跟分组字段和聚合函数
• 分组之后的筛选用having。where是筛选from的。group by也能筛选where的结果；书写顺序：from-》where-》group by-》having
• where后面不能加聚合函数

#以category_id分组，显示每一组有多少个
select category_id ,count(pid) from product group by category_id
#以上一个为基础，只显示个数大于4的
select category_id,count(pid)  from product 
group by category_id having count(pid)>4  
#按个数升序显示
select category_id,count(pid) as cnt from product 
group by category_id having count(pid)>4 order by cnt

分页查询

limit

• limit n显示前n条
• limit m,n
  m表示从第几条索引开始，n表示显示多少条

insert into select

用于把某个表的查询结果插入另一个表

#新建表product2，把product里所有pname和price导入
create table product2(name varchar(20),price int);  
insert into product2(name,price) select pname,price from product;
select * from product2;
#新建表product3，把product里按category_id分组的信息导入
create table product3(category_id varchar(20),product_count int);
insert into product3 select category_id,count(pid) 
from product group by category_id;
select * from product3

3.2增加

INSERT INTO `courses` VALUES (14,"SQL",200,"2021-02-25",1)

INSERT INTO `teachers` (`name`,`email`,`age`,`country`) 
VALUES ("XiaoFu","XiaoFu@lintcode.com",20,"CN")

3.3删除

DELETE FROM `courses`
WHERE `created_at`<"2020-1-1"

DELETE FROM `courses`

3.4修改

UPDATE `courses`
SET `student_count`=500
WHERE `name`="Artificial Intelligence"

正则表达式

本身是字符串，用来匹配字符串，检索替换

4.约束

4.1主键约束

单列主键

1. 在create table的时候设置primary key
2. alter table change 也可以设置主键
3. alter table add也可以，列名要有括号

alter table people add primary key (id);

4. 删除主键

alter table people drop primary key;

联合主键

由一张表中多个列组合成一个主键，联合主键不能在列后面直接声明

create table teacher(
id int,
name varchar(20),
age int,
primary key(id,name)
);

4.2自增长约束

create table user(id int primary key auto_increment,
name varchar(20));

insert into user values(null,"花花");
insert into user values(null,"绿绿");
insert into user values(null,"红红");

#也可以设置增长起始
create table user(id int primary key auto_increment,
name varchar(20))auto_increment=100;
#用modify
alter table stu modify sid int auto_increment;

4.3非空约束

1. 创建表的时候指定
2. alter table(表名) modify (列名) not null

4.4唯一约束

1. 创建表的时候指定
2. alter table (表名) add constraint (约束名) unique (列名)

alter table stu add constraint uni unique (sid);

3. 删除唯一约束 约束名可以用来删除

alter table stu drop index uni;

4.5默认约束

1. 创建表的时候指定
2. alter table modify修改

alter table stu modify grade int default 3;

3. 删除默认约束

alter table stu change grade grade int default null;

4.6零填充约束

zerofill
使用zerofill会自动变成unsigned类型

5.MySql支持的运算符

1. 算数运算
   /+ - * / %
2. 比较运算
3. 逻辑运算
4. 位运算

多表查询

• 多表关系分为一对一，一对多，多对多
• 外键约束foreign key
• 主表没有数据的时候从表也不能添加数据
• 从表添加的数据必须是主键里有的
• 主表的数据被从表依赖时不能删除，从表可以随便删
• 外键约束对查询没有影响

use mydb3;
#1. 在创建表的时候增加约束
#主表
CREATE TABLE `dept` (
  `detpno` varchar(20) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`detpno`)
);
#从表
create table if not exists emp(
eid varchar(20) primary key,
ename varchar(20),
age int,
dept_id varchar(20),
constraint fk foreign key(dept_id) references dept(detpno)
);
#2. 在之后alter table增加约束
create table if not exists dept2(
detpno varchar(10) primary key,
name varchar(20)
);
create table emp2(
eid int primary key,
name varchar(20),
dept_id varchar(10)
);
alter table emp2 add constraint fk2 
foreign key(dept_id) references dept2(detpno);
#删除外键约束
alter table emp2 drop foreign key fk2;

交叉连接查询

cross join返回两表的笛卡尔积，比如A有m行，B有n行，返回m*n行

会形成很多冗余数据，一般后面会加条件筛选

内连接查询

求两张表的交集,查询每个部门所属员工

#sql92 隐式内连接
select * from A,B where...
#sql99 显式内连接
select * from A inner join B on ...

外连接

查询哪些部门有员工，哪些没有

1. 左外left outer join

   先输出左表，右表有对应关系才输出

2. 右外right outer join

3. 满外full join/union

#左外连接 查询哪些部门有对应员工，哪些没有
select * from dept3 left outer join emp3 on dept3.deptno =emp3.dept_id
#右外连接 查询哪些员工有对应部门，哪些没有
select * from dept3 right outer join emp3 on dept3.deptno =emp3.dept_id
# 满外连接
select * from dept3 left outer join emp3 on dept3.deptno =emp3.dept_id
union
select * from dept3 right outer join emp3 on dept3.deptno =emp3.dept_id

union和union all区别

union会自动去重，union all不去重性能更好

子查询

select的嵌套

#查询年龄最大的员工的信息 单行单列可以当成一个值
select * from emp3 where age = (select max(age) from emp3)
#查询所有研发部和销售部的员工的信息
#可以用内连接
select * from dept3,emp3 where dept3.deptno=emp3.dept_id 
and(name='研发部' or name='销售部')
#也可以子查询 多行单列
select * from emp3 where dept_id in 
(select deptno from dept3 where name='研发部' or name='销售部')
#查询所有员工年龄<30的员工的信息，包括部门名字
select * from emp3,dept3 where emp3.age<30 and
emp3.dept_id=dept3.deptno  
select * from emp3 inner join dept3 on
emp3.dept_id=dept3.deptno and emp3.age<30 
#把子查询的结果当成一张表
select * from (select * from dept3 where name='研发部') as tab1,
(select * from emp3 where age<30) as teb2 where deptno=dept_id

• all
  where c>a1 and c>a2 and c>a3 = where c > all(a1,a2,a3)

#查询年龄大于1001部门所有员工的年龄的员工
select * from emp3 where age > 
all(select age from emp3 where dept_id='1003')
#查询不属于任何一个部门的员工的信息
select * from emp3 where dept_id <> all(select deptno from dept3)

• any

#查询年龄大于1003部门任意员工的信息
select * from emp3 where age > any(select age from emp3 where dept_id='1003')

• some同上
• in
• exist
  exists的效率高于in

#查询是否有年龄>60的员工，有则输出
select * from emp3 a where exists (select * from emp3 b where a.age>60)
#查询有所属部门的员工的信息
select * from EMP3 a where exists (select * from DEPT3 b where a.DEPT_ID=b.DEPTNO)

自关联查询

自己和自己关联，把一张表当成多张表用，要给表起别名
自关联约束

视图

视图是一个虚拟表，创建之后可以当成一张表来使用

#创建一个视图
create or replace 
view view_1 as
select pid,pname from product;
# 查看
show full tables;
#修改视图 可以创建新视图replace旧也可以
alter view view_1 as select(新的查询语句)
#重命名视图
rename view view_1 to view2
#删除视图
drop view if exists view2

函数

聚合

group_concate 实现列转行

#多有pname组合到一起用;分割
select group_concat(pname separator ':') from product;
#按照category_id分组，每一组的pname合到一起
select category_id,group_concat(pname separator ':')
from product group by category_id;
#拼接的时候排序
select category_id,group_concat(pname order by price separator ':')
from product group by category_id;

数学

• abs()
• ceil(1.2)=2 向上取整
• floor(1.1)=1 向下取整
• greatest(1，2，3) 取列表的最大值
• least() 取列表的最小值
• max() 取一列最大值
• min()
• mod(5,2) =1取余数
• power(2,3)=8
• rand() 返回0~1之间的随机数

floor(rand()*100)

• round(1.8)=2四舍五入

round(3.5415,3)=3.542 //四舍五入取三位
select category_id,round(avg(price),2) 
from product group by category_id;

• truncate(3.5415,3)=3.541 直接截取

字符串

• substring_index('180cm,75kg,27,male',',',-1)=male

• char_length

• length() 返回的单位是字节，汉字3

• concat()合并字符串

• concat_ws('-','hello','world')指定分隔符合并

• select field('aa','bb','cc','aa')=3返回aa在列表中的位置

• ltrim()去掉字符串左边的空格

• rtrim

• trim()去掉首位空格

• mid('hello',2,2)=el

• position('b' in 'abc')返回b在abc中的位置

• replace('abc','a','x')把abc换成xbc

• reverse()

• right('abc',1)返回后1个字符

• left()

• strcmp()

• substr('avxvx',3,2)=xv substring()

• upper() ucase()

• lower() lcase()

日期

• unix_timestamp()返回从1970.1.1至今的毫秒值
• from_unixtime()根据毫秒值返回时间戳

select from_unixtime(1640783055,'%Y-%m-%d %H:%i:%s');

• curdate() current_date()
• current_time() current()
• current_timestamp()
• date()从日期字符串里截取年月日
• datediff(年月日1，年月日2)获取相隔天数

date(current_date(),年月日)

• timediff(时分秒1，时分秒2)
• date_format日期格式化

select date_format('2021.1.3 13:5:4','%Y-%m-%d %H:%i:%s');

• data_sub(interval)

select date_sub('2020.1.1',interval 2 day)=2019-12-30

• date_add(interval)
• extract(hour/day from '日期')

select extract(month from '2020.1.1')

• last_day()获取所在月的最后一天
• make_date(2020,54)获取2020年第54天
• day() 从日期获取天
• dayofyear()

控制流

• if(expr,f1,f2)相当于条件表达式

select *,if(price>800,'贵','不贵') flag from product

• ifnull(v1,v2) 如果v1==null返回v2

select category_id,ifnull(category_id,0) 
from product group by category_id
#会在后面多加一列flag
select *,ifnull(category_id,0)  flag from product

• isnull
• nullif(expr1，expr2)相同为null不同输出第一个
• case when else end可以分多种情况

#重新添加一列分情况显示
select *,
case category_id
	when 'c001' then '电器'
	when 'c002' then '服装'
	when 'c003' then '化妆品'
	else '其它'
	end as 'category' 
	from product

窗口

rank, dense_rank, row_number

<窗口函数> over (partition by <用于分组的列名>
                order by <用于排序的列名>)

SELECT device_id, university,gpa,
rank() over(partition by university order by gpa) as rk
FROM
user_profile

触发器

当A表发生改变，B表也自动改变
insert delete update才能触发器
用于日志记录，数据完整性
mysql只支持行触发

1. create

create trigger trigtest after insert on user 
for each row
insert into user_logs values(null,now(),"有新用户插入")

2. new和old关键字可以获得触发前后的数据

create trigger trigupdate after update 
on user for each row
insert into user_logs values(null,now(),
concat('有用户被修改: ', OLD.uid,OLD.username,old.password))   

create trigger trigdelete1 after delete 
on user for each row
insert into user_logs values(null,now(),
concat_ws('-','有用户被删除', OLD.uid,OLD.username,old.password))

3. 显示删除

show triggers;
drop trigger 触发器的名字

4. 注意事项 不能对本表触发
   尽量少用

索引

1. 索引的分类
   • 按数据结构分
     • 哈希索引
     • b+树索引
   • 按功能分
     • 单列
       • 普通
       • 唯一
       • 主键
     • 组合
     • 全文
     • 空间
2. 普通索引

#创建表时指定
create  table student(
    sid int primary key,
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    phone_num varchar(20),
    score double,
    index nameindex(name) -- 给name列创建索引
);
-- 方式2-直接创建
-- create index indexname on tablename(columnname); 
create index index_gender on student(gender); 

-- 方式3-修改表结构(添加索引)
-- alter table tablename add index indexname(columnname)
alter table student add index index_age(age);

删除

drop index 索引名 on 表名 
-- 或 
alter table 表名 drop index 索引名

3. 唯一索引 索引的那一列必须唯一，可以是null

-- 方式1-创建表的时候直接指定
create  table student2(
    sid int primary key,
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    phone_num varchar(20),
    score double,
    unique index_card_id(card_id) -- 给card_id列创建索引
);
-- 方式2-直接创建
-- create unique index 索引名 on 表名(列名) 
create unique index index_card_id on student2(card_id);

-- 方式3-修改表结构(添加索引)
-- alter table 表名 add unique [索引名] (列名)
alter table student2 add unique index_phone_num(phone_num)
# 查看索引
show index from 表