SQL基础

操作数据库

创建数据库

​ create database 数据库名 ;

创建数据库同时指定编码集

​ create database 数据库名 character set 编码集 ;

查看所有数据库

​ show databases ;

查看建库信息

​ show create database 数据库名 ;

修改数据库的编码集

​ alter database 数据库名 character set 编码集 ;

删除数据库

​ drop database 数据库名 ;

切换数据库

​ use 数据库名 ;

查看当前正在使用数据

​ select database();

操作表

创建表

​ create table 表名 (

​ 字段1名 字段类型 约束,

​ 字段2名 字段类型 约束,

​ )

​ eg ： create table t_student(id int,name varchar(100),age int);

查询当前数据库所有表

​ show tables;

查询表中字段[列]信息

​ desc 表名;
​ eg：desc t_student;.

修改/更新表

​ 增加表中字段

​ alter table 表名 add 字段名 字段类型 ;
​ eg：alter table t_student add image blob;

​

​ 修改字段的类型
​ alter table 表名 modify 字段名 字段类型 ;

删除表

​ 删除表中字段

​ alter table 表名 drop 列名 ;

​ 删除表

​ drop table 表名 ;

更改表的名字

​ rename table 表名 to 新表名 ;

查看建表细节

​ show create table 表名 ;

操作表中数据

insert 向表中插入数据

​ insert into 表名 (字段1, 字段2…) values (字段值1, 字段值2…)

update 修改表中数据

​ update 表名 set 字段名=新值，字段名=新值…[where 约束]

​ 例如： 将小白的年龄修改为19岁
​ update t_user set age = 19 where name = ‘小白’;

delete 删除表中数据

​ delete from 表名 [where 约束]

select 查询表中数据

select 字段名 as[别名] from 表名 where[条件] group by**[分组] ** having**[分组之后条件过滤] ** order by**[排序] **asc|desc limit [限定显示结果]

• from 指定数据来源于哪张表
• where 对查询数据做第一个过滤
• group by 对数据进行分组
• having 对分组之后的数据进行二次过滤
• select 查询过滤分组之后数据结果集
• order by 对这个查询之后结果进行排序【asc 升序 | desc 降序】
• limit 限制最终输出结果的个数

查询所有

select * from 表名;

查询指定字段

select 字段1, 字段2 from 表名;

去重查询

select distinct 字段1, 字段2… from 表名;

运算符查询

整数小数 ：+ - * /

日期（date）：+ -

比较相等：=

先括号，再乘除，后加减，从左到右

查询所有货品ID，商品名称和 批发价格（批发价格= 零售*折扣）
select id,productName,salePrice * cutoff from product;

别名查询

select 字段 AS 别名 from 表名;

查询所有货品ID，货品名称和个进50个成本价格,每一个鼠标都要+1运费
select id,productName,costPrice,(costPrice+1)*50 AS totalCP from product;

where子句查询

select 列名 from 表名 where 限制条件

将lisi的薪水调整为1
UPDATE emp SET salary = 1 WHERE name = ‘lisi’;

查询货品名称不是‘罗技G9X’的所有商品信息
select * from product where productName != ‘罗技M305’;

where子句的后面可以添加 计算表达是进行判断
select productName,salePrice * cutoff from product where salePrice*cutoff >350;

逻辑运算查询（AND OR NOT）

AND（并且）

​ 查询货品ID，货品名称，批发价格在300~400之间的货品
​ select id,productName, salePrice * cutoff 批发价格 from product where salePrice * cutoff >=300 AND salePrice * cutoff <=400;

OR（或者）

​ 查询所有商品信息，商品编号为2或为4的商品
​ select * from product where dir_id =2 or dir_id = 4;

NOT（取非）

​ 查询或ID,商品名称，分类编号不为2的所有商品
​ 方式1：
​ select id,productName,dir_id FROM product where dir_id != 2;
​ 方式2：
​ select id,productName,dir_id FROM product where NOT dir_id = 2;

Between…And查询

select 字段 from 表名 where 列名 between 最小范围 and 最大范围;

查询货品ID，货品名称，零售价格在300~400之间的货品
select id,productName, salePrice from product where salePrice between 300 and 400;

In查询

select 字段 from 表名 where 列名 in(值1，值2…);

查询商品编号为2或为4的商品
select * from product where dir_id in(2,4);

null 查询

select 字段 from 表名 where 列名 is null;

select 字段 from 表名 where 列名 is not null;

like模糊查询

% 表示0个或任意字符

_ 表示1个字符

查询货品id，货品名称，货品名称匹配【罗技M】的所有商品
select id,productName from product where productName like ‘罗技M%’;
查询货品id，货品名称，货品名称匹配罗技M1并且有两个字符 或者 零售价格 大于等于200
select id,productName,salePrice from product where productName like ‘罗技M1__’ or salePrice >= 200;

case…when查询

select 字段
case
when 条件1 then 结果1
when 条件2 then 结果2
when 条件3 then 结果3
…
else 结果4
end from 表名;

#查询员工信息（编号，名字，薪资 , 薪资级别<对应条件表达式生成>）
SELECT
employee_id as 员工编号,
CONCAT(first_name,last_name) as 名字,
salary as 薪资,
department_id as 部门编号,
CASE
WHEN salary>=5000 AND salary<=8000 THEN ‘C’
WHEN salary>=8000 AND salary<=10000 THEN ‘B’
WHEN salary>=10000 THEN ‘A’
ELSE ‘C’
END as ‘level’
FROM t_employees ;

order by 查询

select 字段 from 表名 where 条件 order by 字段 [asc/desc]

asc 升序[默认]

desc 降序

查询所有学生记录，按年龄升序排序
select * from stu order by age;

查询商品ID，商品名称，分类编号，零售价格，先按照编号降序，然后按照零售价格升序
SELECT id,productName,dir_id,salePrice from product ORDER BY dir_id desc,salePrice asc;

limit 查询

limit：显著查询返回结果个数

select 字段 from 表名 limit 条数;

select 字段 from 表名 limit n,m;

查询所有数据但是只显示前三条

​ select * from product limit 3;

从第三条开始，查询10条数据

​ select * from product limit 3,10;

分组查询

group by 关键字

select 列或聚合函数 from 表名 where 条件 group by 要分组的类;

• 查询每个部门的部门编号和每个部门的工资和
  select deptno,sum(sal) from emp GROUP BY deptno;
• 查询每个部门的部门编号以及每个部门的总人数
  select deptno,count( * ) from emp GROUP BY deptno;
• 查询每个部门编号以及每个部门工资大于1500的总人数
  select deptno,count(*) from emp where sal > 1500 GROUP BY deptno;

having语句

• 查询工资总和大于9000的部门编号以及工资总和
  select deptno, SUM(sal) from emp GROUP BY deptno HAVING SUM(sal) > 9000;

• having和where子句的区别

  • having子句是在分组之后对数据进行过滤
  • where子句是在分组之前对数据进行过滤
  • having子句的后面是可以使用聚合函数，但是where是不可以使用聚合函数
  • where子句是对分组之前数据进行条件过滤，没有满足where子句条件的语句不会参与到分组中，而having是对分组之后数据的约束
  • where子句使用场景比较访问基本上可以和任意查询语句进行搭配而having只适合使用在group by 的后面

建表完整性

primary key

主键约束：这一列数据 非空且唯一

unique

唯一约束： 表示表中数据 不可重复，但是可以为null

AUTO_INCREMENT

自动增长列：修饰主键列，让主键列可以自动增长

NOT NULL

非空约束：此列必须有值

DEFAULT

默认值：没有传递值前提下有一个默认值出现在列中

外键列

外键列：将一张表中非空且唯一的这个一列，作为在另外一张表中列存在

#创建一张表【专业表】
create table specialty(
id INT PRIMARY KEY AUTO_INCREMENT,
specialtyName varchar(20) UNIQUE NOT NULL
)

#创建一张表【课程表】

create table subject(
subjectID INT PRIMARY KEY AUTO_INCREMENT, #subjectID 主键自增
subjectName varchar(100) UNIQUE NOT NULL, #subjectName 唯一且不为空
subjectHours INT DEFAULT 20, #subjectHours 默认20个int
specialtyID INT NOT NULL, #specialtyID 外键关联字段

​ # 设定subject表中的specialtyID字段为外键字段，外键名字fk_subject_specialty，数据关联的是specialty表中的id字段

​ CONSTRAINT fk_subject_specialty FOREIGN KEY(specialtyID) REFERENCES specialty(id)
)

多表联合查询

合并查询

被合并的两个表字段数和类型必须相同

UNION ：

合并两个表，去重

• 例如：
  select * from t1 UNION select * from t2;
  #t1 表中 a1 b2 c3 t2 c3 d4 e5
  #结果 a1 b2 c3 d4 e5

UNION ALL ：

合并两个表，不去重

• 例如：
  select * from t1 UNION ALL select * from t2;
  #t1 表中 a1 b2 c3 t2 c3 d4 e5
  #结果 a1 b2 c3 c3 d4 e5

连接查询

内连接 INNER JOIN

• SELECT * FROM t1 INNER JOIN t2 ON 条件约束;
• 结果只展示ON后条件相同的数据
  

外连接 OUTER JOIN

左连接 LEFT JOIN

• SELECT * FROM t1 LEFT JOIN t2 ON 条件约束;
• 左表为主驱动查询右表，满足条件的展示数据，不满足展示null
  

右连接 RIGHT JOIN

• 右表为主驱动左表，满足条件展示数据，不满足展示null
  

子查询

在一个select语句中嵌套一个select语句，这种叫做子查询

any

all

时间函数

​ select 时间函数() ;

时间函数	描述
SYSDATE()	当前系统时间（日、月、年、时、分、秒）
CURDATE()	获取当前日期
CURTIME()	获取当前时间
WEEK(DATE)	获取指定日期为一年中的第几周
YEAR(DATE)	获取指定日期的年份
HOUR(TIME)	获取指定时间的小时值
MINUTE(TIME)	获取时间的分钟值
DATEDIFF(DATE1,DATE2)	获取DATE1 和 DATE2 之间相隔的天数
ADDDATE(DATE,N)	计算DATE 加上 N 天后的日期
now()	获取当前时间(date+time)

• 查询在某段日期之间的数据:
  select * from 数据表 where 时间字段名 between ‘2016-02-01’ and ‘2016-02-05’

• 查询往前3个月的数据

  select * from 数据表 where 时间字段名 between DATE_SUB(NOW(),INTERVAL 3 MONTH) AND NOW()

• 查询往前一年的数据

  select * from 数据表 where 时间字段名 BETWEEN DATE_SUB(NOW(),INTERVAL 1 YEAR) AND NOW()

• 查询本月的数据

  select * from 数据表 where DATE_FORMAT(时间字段名,‘%Y-%m’)=DATE_FORMAT(NOW(),‘%Y-%m’)

• 查询上月的数据:
  select * from 数据表 where DATE_FORMAT(时间字段名,‘%Y-%m’)=DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),‘%Y-%m’)

• 查询本周的数据:
  select * from 数据表 where YEARWEEK(DATE_FORMAT(时间字段名,‘%Y-%m-%d’)) = YEARWEEK(NOW())

• 查询上周数据:
  select * from 数据表 where YEARWEEK(DATE_FORMAT(CREATE_TIME,‘%Y-%m-%d’)) = YEARWEEK(NOW())-1

• 查询往前7天的数据:
  select * from 数据表 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) <= 你要判断的时间字段名

• 查询往前30天的数据:
  select * from 数据表 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= 你要判断的时间字段名

• 根据国人习惯 周一为一周的第一天习惯算一周的数据
  SELECT * FROM t_browse_statistics WHERE YEARWEEK(date_format(brows_date,‘%Y-%m-%d’),1) = YEARWEEK(now())+1
  ORDER BY brows_date DESC

字符串函数

字符串函数	说明
CONCAT(str1,str2,str…)	将 多个字符串连接
INSERT(str,pos,len,newStr)	将str 中指定 pos 位置开始 len 长度的内容替换为 newStr
LOWER(str)	将指定字符串转换为小写
UPPER(str)	将指定字符串转换为大写
SUBSTRING(str,num,len)	将str 字符串指定num位置开始截取 len 个内容

• 字符串函数
  select concat(‘1900-1-1’,‘~’,‘2020-1-1’) as 日期范围;

• 字符串替换
  select insert(“这是一个数据库”,3,2,‘MySQL’);

• 字符串截取
  select SUBSTRING(‘JavaMySQLOracle’,5,5);

聚合函数

聚合函数	说明
SUM()	求所有行中单列结果的总和
AVG()	平均值
MAX()	最大值
MIN()	最小值
COUNT()	求总行数

• 查询所有商品的平均零售价格
  select avg(salePrice) from product; #avg就相当于是获取了所有表中的数据的总条数，然后将所有的零售价格相加 ，然后进行相除得到结果

• 统计表中有多少条记录
  SELECT count(id) from product;

• 思考题： 为什么这样写可以 * 和 1 的区别是什么？
  SELECT count(*) from product;
  select count(1) from product;

• 查询商品最小零售价格，最高零售价格以及商品零售价格总和
  select MIN(salePrice),MAX(salePrice),SUM(salePrice) from product;

• 统计分类编号为4的商品总数
  select count(id) from product WHERE dir_id = 4;

索引

• 最佳左前缀法则。
• 不在索引列上做任何操作（计算、函数、(自动或者手动)类型转换），会导致索引失效而转向全表扫描。
• 索引中范围条件右边的字段会全部失效。
• 尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少SELECT *。
• MySQL在使用!=或者<>的时候无法使用索引会导致全表扫描。
• is not null无法使用索引,但是is null是可以使用索引的
• like以通配符开头%abc索引失效会变成全表扫描。
• 字符串不加单引号索引失效。
• 少用or，用它来连接时会索引失效。

创建索引：

创建普通的索引

alter table table_name add index index_name (字段名)

创建主键索引

alter table table_name add primary key (字段名)

创建 唯一索引

alter table table_name add unique (字段名)

创建全文的索引

alter table table_name add fulltext (字段名)

创建多个索引

alter table table_name add index index_Name(column,column1,column_N…)

删除索引：

drop index index_name on table_name

alter table table_name drop index index_name