目录
一、概述
Mysql是一种关系型数据库,数据库管理系统可以通过SQL管理数据库;定义和操作数据,维护数据的完整性和安全性。
SQL的分类:
1、DDL(Data Definition Language) 数据定义语言,用来操作数据库、表、列等; 常用语句:CREATE、 ALTER、DROP
2、DML(Data Manipulation Language) 数据操作语言,用来操作数据库中表里的数据;常用语句:INSERT、 UPDATE、 DELETE
3、DCL(Data Control Language) 数据控制语言,用来操作访问权限和安全级别; 常用语句:GRANT、DENY
4、DQL(Data Query Language) 数据查询语言,用来查询数据 常用语句:SELECT
二、三大范式
1、第一范式(1NF)是指数据库表的每一列都是不可分割的基本数据线;也就是说:每列的值具有原子性,不可再分割
2、第二范式(2NF)是在第一范式(1NF)的基础上建立起来得,满足第二范式(2NF)必须先满足第一范式(1NF)。如果表是单主键,那么主键以外的列必须完全依赖于主键;如果表是复合主键,那么主键以外的列必须完全依赖于主键,不能仅依赖主键的一部分。
3、第三范式(3NF)是在第二范式的基础上建立起来的,即满足第三范式必须要先满足第二范式。第三范式(3NF)要求:表中的非主键列必须和主键直接相关而不能间接相关;也就是说:非主键列之间不能相关依赖。
三、数据类型
1.整数类型
TINYINT、SMALUNT、MEDIUMINT、INT和 BIGINT
2.浮点数类型和定点数类型
单精度浮点数类型FLOAT,双精度浮点数类型DOUBLE,定点数类型DECIMAL
3.字符串类型
CHAR 、VARCHAR
4.字符串类型
TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT
5.时间与日期类型
YEAR、 DATE、 TIME、 DATETIME 、 TIMESTAMP
YYYY、 YYYY-MM-DD、HH:MM:SS、YYYY-MM-DD HH:MM:SS、YYYY-MM-DD HH:MM:SS
DATETIME的取值范围远大于TIMESTAMP
6.二进制类型
TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
四、数据库、数据表的操作
1.数据库的操作
创建数据库:create database 数据库名称;
查看数据库信息:show create database 数据库名称;
删除数据库:drop database 数据库名称;
查询所有数据库:show databases;
切换数据库:use 数据库名;
查看当前使用的数据库:select database();
2.数据表的操作
创建数据表:create table 表名( 字段1 字段类型, 字段2 字段类型, … 字段n 字段类型 );
create table student(
id int,
name varchar(20),
gender varchar(10),
birthday date
);
查看当前数据库中所有的表:
show tables;
查看某张表的信息:
show create table 表名;
查看表的字段信息:
desc 表名;
修改表数据:
alter table 表名 rename to 新表名;
查询表中的字段:
select 字段名 from 表名;
查询多个字段:select a,b,c,d…… from 表名;
查询所有字段:可以用多个字段把所有字段写上去;
也可以select * from 表名;
分页查询:
select 字段列表 from 表名 limit 起始索引,查询条目数量
--起始索引从0开始
--计算公式:起始索引=(当前页码-1)×每页显示的条数
修改表的字段名:
alter table 表名 change 字段 新字段 数据类型属性;
例:alter table stu change name sname varchar(10);
修改字段数据类型:
alter table 表名 modify 字段 数据类型;
例:alter table stu modify sname int;
增加字段:
alter table 表名 add 字段 数据类型;
例:alter table stu add address varchar(50);
删除字段:
alter table 表名 drop 字段;
例:alter table stu drop address;
删除数据表:
drop table 表名;
给列起别名
select 字段名 as 别名 from 表名;
只是将显示的字段更改,但是字段实际上还是原来的字段名
as可以省略,别名里面不能有空格,别名中有空格时,可以将别名用单引号或者双引号括起来
在所有的数据库中字符串用单引号括起来,是标准;oracle数据库中,双引号用不了
3.字段操作
字段是可以参加加减乘除操作的,(数值类型)
条件查询:select 字段1,字段2,字段3......from 表名 where 条件;
条件中: 不等于:!= <>
查询是否为空不能用=,要用is null 或者is not null;
可以用like实现模糊查询,其中%和_都是特殊字符,一个_代表一个字符;'__a%'代表第三个字符为a的字段
模糊查询:select 字段名 from 表名 where 字段名 like '%字段里的某段内容';
4.排序
order by
select name,salary from emp order by salary;按照salary升序查询emp表中的name,salary字段信息
order by默认是升序
order by salary desc是指定降序
order by salary asc是指定升序
多字段的时候用逗号隔开,前面的优先级高
五、函数
1.单行处理函数(也叫数据处理函数)
特点:一个输入对应一个输出,一次只处理一行,一行一行的执行。
lower:转换小写 select lower(字段) from 表名;功能:将字段里面的大写变成小写,显示输出,只是显示变了,数据没变
upper:转换大写
substr:取子串,substr(被截取的字符串,起始下标,截取的长度)起始下标是从1开始的
contact:进行字符串的拼接
select contact(字段1,字段2) from 表名;将表中的两个字段内容作为字符串拼接显示输出
length:取长度
select length(字段名) from 表名;将表中的某个字段的长度显示输出;
trim:去空格 当传的数据可能有空格时,数据库中会查不到,可以用trim去空格
round:四舍五入
rand:生成随机数
case:相当于判断 case...when...then...when...then...else...end
select ename,job,(case job when 'mannage' then sal*1.1 when 'saleman' then sal*1.5 else sal end) as newsalary from emp
str_to_date:将日期转换成字符串
2.分组函数(多行处理函数)
特点:输入多行,最终输出一行;在使用时必须先分组,然后才能用;如果没有对数据进行分组,整张表默认为一组;
count:计数 select count(ename) from 表名;查询表中ename字段的数量
sum:求和
avg:平均值
max:最大值 select max(salary) from 表名;查询表中salray字段的最大值
min:最小值 同上
分组函数使用时注意:
①分组函数自动忽略null,不需要用where提前对null处理;
②count(*)与count(具体字段),前者是行的数量,后者是字段下存在的数据的数量;
③分组函数不能直接使用在where字句中
六、分组查询
1.什么是分组查询?
在实际的应用中,可能需要先进行分组,然后对每一组的数据进行操作,这个时候我们需要分组查询。
2.语法
select......from......where......group by......order by......
执行顺序 from---where---group by---having---select---order by
因为执行语句的顺序,所以分组函数不能直接使用在where字句中!!!!!!
select job,max(salary) from emp group by job;
求各工作岗位的工资和
在一条select语句中,如果有group by语句的话,select后只能跟参加分组的字段和分组函数,其他的都不能写在select后面;
多个字段分组的时候,用逗号隔开;
使用having对分组后的数据进行过滤,having必须跟着group by使用
select deptno,max(salary) from emp group by deptno having max(salary) > 3000;
先将大于3000的找出来,然后再分组
select deptno max(salary) from emp where salary > 3000 group by deptno;
能用where过滤的优先选择where,实在不行再选择having;
从某张表查询数据,先经过where条件筛选出有价值的数据,对这些有价值的顺序进行分组,再通过having进行筛选,最后排序输出;
select job,avg(salary) as avgsal from emp where job <> 'MANAGER' group by job having avg(salary) > 2500 order by avgsal desc;
理解执行顺序与含义
七、去除重复记录
distinct关键字
select distinct job from emp;
distinct关键字使用的时候注意记录数据之间的匹配性,distinct只能出现在字段的最前方
多个字段之间用逗号隔开,表示多个字段联合去重。
select count(distinct job) from emp;
统计工作岗位的种类数量
八、连接查询
1.什么是连接查询
从一张表中单独查询,称为单表查询。
在有关联的表中跨表查询,多张表联合起来查询数据,为连接查询。
2.分类
根据语法的年代分类:
SQL92:1992年出现
SQL99:1999年出现,重点学习;
根据表连接的方式分类:
内连接---:等值连接,非等值连接,自连接
外连接--:左连接,右连接
全连接(几乎不用)
3.表连接
当两张表连接没有任何限制的时候,会发生笛卡尔积现象:最终查询结果条数,是两张表条数的乘积。
如何避免笛卡尔积现象:连接时加条件,满足条件的记录被筛选出来;
select ename,dname from emp,dept where emp.deptno = dept.deptno;
查询结果被过滤了,但是匹配过程中,匹配的次数没有减少
优化:
select e.ename,d.dname from emp e,dept d where e.deptno = d.deptno;
表连接起别名
以上为SQL92语法-----------------------------------------------------------------------------------------------
通过笛卡尔积现象得出:表的连接次数越多,效率越低,尽量减少表的连接次数;
3.1.1 内连接之等值连接
查询每个员工所在部门名称,显示员工名和部门名
emp e和dept d表进行连接,条件e.deptno = d.deptno
SQL99:
select
......
from
a
(inner)join
//inner可以省略,带着的话可读性更好,可以一眼看出来连接类型
b
on
//连接条件是等量关系,所以被称为等值连接
where
//筛选条件;
99结构更清晰,后面还可以加where进行筛选,但是92筛选条件会和连接条件写在一起,比较乱
3.1.2 内连接之非等值连接
找出每个员工的薪资等级,要求显示员工名、薪资、薪资等级
select e.ename,e.sal,s.garde from emp e join salgrade s on e.sal between s.lowsal and s.highsal;
3.1.3 内连接之自连接
查询员工的上级领导,要求显示员工名和对应的领导名
技巧:一张表看成两张表
select a.ename as '员工名',b.ename as '领导名' from emp a join emp b on a.mgr = b.empno;
把一张表看成两张表,通过表中的两个字段进行条件
3.1.4 外连接
内连接的特点:完成能够匹配上条件的数据查询出来;
外连接:
select e.ename ,d.dname from emp e right (outer) join dept d on e.deptno = d.deptno;
右外连接(右连接)
select e.ename ,d.dname from dept d left (outer) join emp e d on e.deptno = d.deptno;
左外连接(左连接)
左连接可以写成右连接,右连接可以写成左连接
right:表示将join关键字右边的这张表看成主表,主要是为了将这张表的数据全部查询出来,捎带着关联查询左边的表。
在外连接当中,两张表的连接产生了主次关系。
3.1.5 外连接和内连接的区别
外连接有主次之分,是在查询主表全部内容的时候顺便输出另一张表符合条件的内容,如果主表中的一些内容在另一张表中不存在,则NULL;
内连接是平等的两张表;
外连接的查询的信息数据数量>=内连接查询的数量;
3.1.6 多张表的连接
select
.....
from
a
join
b
on
a和b的连接条件
right join
c
on
a和c的连接条件
join
d
on
a和d的连接条件
......
一条sql中内连接和外连接可以混合,都可以出现。
九、子查询
查询张三同学所在班级的信息
select * from class where cid=(select classid from student where sname='张三');
查询比张三同学所在班级编号还大的班级的信息
select * from class where cid>(select classid from student where sname='张三');
其中还可以加上EXISTS、ANY、ALL关键字
EXISTS表示存在,如果存在则查询外层信息;
ANY表示任意,表示满足其中任意一个条件就返回一个结果作为外层查询条件。
ALL关键字的子査询返回的结果需同时满足所有内层査询条件。