sql学习笔记

SQL语句分类： DQL：数据查询语言（凡是带有select关键字的都是查询语句）
select……
DML:数据操作语言(凡是对表当中的数据进行增删改的都是DML)
insert delete update
insert 增
delete 删
update 改

DDL:数据定义语言（凡是带有create 、drop、alter的都是DDL）
主要操作的是表的结构、不是表中的数据。
create:新建，等同于增
drop:删除
alter:修改

TCL:事务控制语言，
包括：
事务提交：commit；
事务回滚：rollback；

DCL:数据控制语言
授权grant、撤销权限revoke……



常用命令：
简单查询语句
1、select … from …
2、desc dept；查询表结构
注意：在所有的数据库当中，字符串统一使用单引号括起来，单引号是标准。

条件查询语句：
select … from …. where 条件
= ：等于；
<>或 != ：不等于;
< ：小于；
>= : 大于等于;
between … and … :两个值之间，等同于 >= and <=；
is null :为 null（is not null 不为空）；
and :并且；
or :或者;
in :包含，相当于多个or（not in 不在这个范围中），注意不是区间，是具体值;
not not :可以取非，主要用在is或in中
like like :称为模糊查询，支持%或下划线匹配
% :匹配任意个字符
下划线 :一个下划线只匹配一个字符

排序查询语句：
order by …. asc ：默认是升序
order by …. desc ：降序

不常用命令：
select version() ——查看数据库版本号
select database()——查看当前是哪个数据库

单行函数处理：
lower
upper
substr
length
trim
strto_date 将字符串转换成日期
date_format 格式化日期
format 设置千分位
round 四舍五入
rand（） 生成随机数
ifnull 可以将null转换成一个具体值


分组函数：
count 计数
sum 求和
avg 平均值
max 最大值
min 最小值


分组查询：
having

关键字的执行顺序：
1、from
2、where
3、group by
4、select
5、order by

总结（单表的查询）
select
…
from
…
where
…
group by
…
having
…
order by
…
从某张表中查询数据，
先经过where条件筛选出有价值的数据。
对这些有价值的数据进行分组。
分组之后可以使用having继续筛选。



distinct关键字（放在所有字段的前面，用于数据去重）

1、内连接：
等值连接：两个表中某个值相等
非等值连接：一个表中的条件在另一个表的值某个区间
自连接：一张表看作两张表
sql92语法缺点：结构不清晰，表的连接条件，和后期进一步筛选的条件，都放到了where后面。
sql99语法：表连接的条件是独立的，连接之后，如果还需要进一步筛选，再往后继续添加where
select
…
from
a
inner join
b
on
ab连接条件
where
筛选条件


2、外连接：主要是为了将其中一张表的数据全部查询出来，捎带着查询另外一张表，出现了主次关系
右外连接：ringht join
左外连接：left join


3、一条sql中内连和外连可以混合:
select
…
from
a
join
b
on
a和b的连接条件
join
c
on
a和c的连接条件
right join
d
on
a和d的连接条件

4、子查询
select语句中嵌套select语句，被嵌套的select语句称为子查询。
where查询中出现的子查询；
from后面的子查询，可以将子查询的查询结果当做一张临时表。


5、union合并查询结果集
合并时要求结果合并集的列数相同。
mysql允许数据类型不同，oracle不可以

6、limit
6.1、limit作用：将查询结果集的一部分去处理啊。通常使用在分页查询当中。
用法：
select
….
from
….
order by
……
limit 5 //取前5 limit 0,5；（0代表位置，5代表长度）
注意：mysql当中limit再order by之后执行！！！！！
记公式：limit （pageNo-1）*pageSize，pageSize


7、表的创建（建表）
7.1、建表的语法格式：（建表属于DDL语句，DDL包括：creat drop alter）
create table 表名（字段名1 数据类型，字段名2 数据类型，字段名3 数据类型）；
create table （
字段名1 数据类型，
字段名2 数据类型，
字段名3 数据类型
）；
表名：建议以t或者tbl_开始，可读性强。见名知意。
表名和字段名都属于标识符。

7.2、关于mysql中的数据类型？
varchar（最长255）：
可变长度的字符串
比较智能，节省空间。
会根据实际的数据长度动态分配空间。
优点：节省空间
缺点：：需要动态分配空间，速度慢


char（最长255）
定长字符串
不管实际的数据长度是多少
分配固定长度的空间去存储数据。
使用不恰当的时候，可能会导致空间的浪费。
优点：不需要动态分配空间，速度快。
缺点：使用不当可能导致空间的浪费。


int（最长11）：
数字中的整数型，等同于JAVA的int

bigint：
数字中的长整数型，等同于java中的long。

float：
单精度浮点型数据

double：
双精度浮点型数据


date
短日期类型

datetime
长日期类型

clob
字符大对象
最多可以存储4G的字符串
比如：存储一片文章，存储一个说明。
超过255个字符的都要采用CLOB字符大对象来存储。
character Large OBject：CLOB

blob
二进制大对象
Binary Large OBject
专门用来存储图片、声音、视频等流媒体数据。
往BLOB类型的字段上插入数据的时候，例如插入一个图片、视频等，
你需要使用IO流才行。

7.4 插入数据insert （DML）
语法格式：
insert into 表名（字段名1，字段名2….）values(值1，值2…..);
insert插入多条记录： insert into 表名（字段名1，字段名2….,create_time）values(值1，值2…..，now())，(值1，值2…..，now()),(值1，值2…..，now());
注意：字段名和值要一一对应。


7.5插入日期：
mysql日期格式：
%Y 年
%m 月
%d 日
%h 时
%i 分
%s 秒
str_to_date(‘01-10-1990’，‘%d-%m-%Y’)
date_format(birth,‘%m/%d/%Y’)

7.6date和datetime两个类型的区别？
date是短日期，只包括年月日信息。
datetime是长日期：包括年月日时分秒信息。

7.7、修改update（DML）
语法格式：
update 表名 set 字段名1=值1，字段名2=值2…..where 条件；
注意：如果没有条件限制会导致所有数据全部更新。

7..8删除数据delete（DML）
语法格式：
delete from where 条件
注意：没有条件整张表会全部删除！

1、快速创建表：
create table emp2 as select from emp；
create table empx as select from emp where ….；
insert：
insert into dept select from dept//很少用

2、快速删除表中的数据：
delete from dept_bak;
delete语句删除数据的原理？
表中的数据被删除了，但是这个数据在硬盘上的真实存储空间不会被释放！！！
缺点：删除效率低。
有点：支持回滚，后悔了可以再回滚，rollback。

truncate语句删除数据的原理？
用法：truncate table dept；
这种删除效率比较高，表被一次截断，物理删除。
这种删除缺点：不支持回滚。
这种删除优点：删除速率快。




约束
7.1、什么是约束（constraint）？
在创建表的时候，我们可以给表中的字段加上一些约束，来保证这个表中数据的完整性、有效性！！！
约束的作用就是为了保证：表中的数据有效！！

7.2、约束包括哪些？
非空约束：not null
唯一性约束：unique
主键约束：primary key（简称PK）
外键约束：foreign key（简称FK）
检查约束：check（mysql不支持，oracle支持）


7.3、非空约束：
非空约束not null约束的字段不能为NULL。
not null 只是列级约束，没有表级约束；
示例：
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255) not null
);

7.4、唯一性约束：unique
唯一性约束unique约束的字段不能重复，但是可以为NULL。
多个字段可以联合唯一
示例：
drop table ifexists t_vip
create table t_vip(
id int,
name varchar(255) unique,
email varchar(255)
);
联合唯一约束示例：
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255),
unique(name,email);
);

not null 和unique联合：
drop table if exists t_vip;
create table t_vip(
id int,
name varchar(255),
email varchar(255) not null unique,
);


7.5主键约束（primary key ，简称PK）
主键值是每一行记录的唯一标识。
可以复合主键，建议使用单一主键。
主键建议使用int，bigint，char等类型，不建议使用varchar来做主键。
示例：
drop table if exists t_vip;
create table t_vip(
id int primary key,
name varchar(255)
);


7.6、外键约束
示例：
create table t_class(
classno int primary key,
classname varchar(255),
);

create table t_student(
no int primary key auto_inorement,
name varchar(255),
cno int,
foreign key(cno) references t_class(classno)
);







索引：
创建索引：
mysql>create index emp_ename_index on emp(ename);
删除索引：
mysql>drop index emp_ename_index on emp;
explain:
explain select from emp where ename=’KING’