sql 学习笔记

SQL 是用于访问和处理数据库的标准的计算机语言 是用于管理关系数据库管理系统rdbms SQL的范围包括数据插入，查询，更新和删除，数据库模式的创建和修改。以及数据访问控制

SQL能做什么
SQL面向数据库执行查询
SQL可以从数据库取回数据
SQL可在数据库中插入新的记录
SQL可以和没后续数据库中的数据
SQL可以创建新数据库
SQL可在数据库中创建新表
SQL可以再数据库中创建存储过程
SQL可以在数据库中创建视图
SQL可以设置表，存储过程和视图的权限

数据库表
一个数据库通常包含一个或多个表。每个表都有一个名字标识，表包含带有数据的记录（行）

select 语句用于从数据库选取语句  结果被存储在一个结果表中 被称为结果集
select distinct 语句用于返回唯一不同的值 去重 在表中，一个列可能会包含许多重复的值
where 子句用于过滤记录 = <>不等于  >  <  >=  <=  BETWEEN（在某个范围内） LIKE (搜索某种模式) IN (指定针对某个列的多个可能值)
and 同时满足两个条件的值 or满足其中一个条件的值 not 满足不包含该条件的值  is null 空值判断   between and  in  LIKE模糊查询

order by 关键字用于对结果集进行排序
关键字默认按照升序对记录进行排序。如果需要按照降序对记录进行排序可以使用desc关键字
asc表示俺升序排序（默认 描述性关键词加在末尾处）
desc表示俺降序排序

insert into 语句用于向表中插入新记录
upadte 语句用于更新表中的数据
delete语句用于删除表中的记录

select top子句用于规定要返回的记录的数目
到mysql里面是limit 

select like 操作符用于在where子句搜索列中的指定模式

sql 通配符 在sql中，通配符与sql like操作符一起使用
在sql通配符用于搜索表中的数据
% 替代0个或多个字符 
_替代一个字符
[]字符列中的任何单一字符 （这个和下面的那个要配着regexp或者 not regexp）
[^]不在字符列中的任何单一字符（或[!]）

sql in 操作符允许在where子句规定多个值

between操作符选取介于两个值直接的数据范围的值。这些值可以是数值，文本或者日期

sql别名 通过使用sql 可以为表名称或列名称指定别名 创建别名是为了让列名称的可阅读性更强  as

sql join 用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。
inner join 如果表中至少一个匹配，则返回行  （取交集）
left join 即使右表中没有匹配，也从左表中返回所有的行 （取A全部 ）
right join 即使左表中没有匹配，也从右表返回所有的行 (去B全部)
full join 只有其中一个表存在匹配，则返回行 (取并集)
在使用join 时 on和where条件的区别如下
1.on 条件是在生成临时表使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录
2.where 条件是临时表生成好以后，再对临时表进行条件过滤的条件。这时已经没有left join 的含义（必须返回左边表的记录）了，条件不为真就全部过滤掉

sql union操作符合并两个或多个select语句的结果（请注意 union 内部的每个语句必须用于相同数量的列。列也必须拥有相似的数据类型。同时每个select语句中的列的顺序必须相同）

sql select into 通过sql，可以从一个表复制信息到另一个表
语句从一个表复制数据，然后把数据插入到另一个新表中
MySQL不支持
sql insert into select 语句
通过sql 您可以从一个表复制信息到另一个表(要求这个表存在)
select into from 要求目标表不存在 因为在插入时会自动创建
insert into select from 要求目标表存在

sql create database 语句用于创建数据库
sql create table_name （）语句用于创建数据库中的表

sql constraints 约束用于规定表中的数据规则。如果存在违反约束的数据行为，行为会被约束终止。约束可以再创建表时规定（通过create table语句）,或者在表创建之后规定（通过alter table语句）
not null 指示某列不能存储null值
unique 保证某列的每行必须有唯一的值
primary key not null和unique的结合。确保某列（或两个列多个列的组合)有唯一标识，有助于更容易更快速的找到表中的一个特定记录
foreign key 保证一个表中的数据匹配另一个表中的值的参考完整性
check 保证列中的值符合指定的条件
default 规定没有给列赋值时候的默认值

not null 约束强制列不接受null值 约束强制字段始终包含值。如不添加值 ，就无法插入新记录或者更新记录
unique 约束唯一标识数据库表中的每条记录 unique和primary key 约束均为列或列集合提供了唯一性的保证
primary key约束。约束唯一标识数据库表中的每条记录 住建必须包含唯一的值 住建列 不能包含null值 每个表都应该有一个主键 并且每个表只能有一个主键
foreign key一个表中的foregin key指向另一个表中的unique key 唯一约束的键
check 约束用于限制列中的值的范围 如果对单个列定义check约束，那么该列只允许特定的值。如果对一个表定义check值，那么该约束会基于行中挤压列的值在特定的列中对值进行限制
default约束用于向列中插入默认值

sql create index 语句用于在表中创建索引 在不读取整个表的情况下，索引使数据库应用程序可以快速的查找数据

drop index 语句用于删除表中的索引
drop table 删除表
drop database 删除数据库
truncate table 删除表内数据

alter table 用于在已有的表中添加，删除或修改列

sql auto increment 会在新记录插入表中时生成一个唯一的数字 我们通常希望在每次插入新纪录时，自动地创建主键字段的值。我们可以再表中创建一个auto-increment字段

SQL视图 视图是可视化的表 视图是基于sql语句的结果集的可视化的表。视图包含行和列，就像一个真实的表。视图重的字段来自一个或多个数据库重的真实的表重的字段。我们可以向视图添加sql函数，where函数以及join语句，也可以呈现数据，就像这些数据来自某个单一的表一样
视图的作用：
1.视图隐藏了底层的表结构，简化了数据库访问操作，客户端不再需要知道底层表的结构及其之间的关系
2.视图提供了一个统一访问数据的接口(即可以允许用户通过实体访问数据的安全机制，而不授予用户直接访问底层表的权限）
3.从俄日加强了安全性，使用户只能看到视图所显示的数据
4.视图还可以被嵌套，一个视图中可以嵌套另一个视图
###视图暂时设置尝试失败 后面看到mysql再进行尝试

sql data 函数 (dates）下表是mysql中重要的内建日期函数
1.now 返回当前的日期和时间
2.curdate 返回当前的日期
3.curtime 返回当前的时间
4.date 提取日期或日期/时间表达式的日期部分被
5.extract 返回日期/时间的单独部分
6.date-add 向日期添加指定的时间间隔
7.date-sub 从日期减去指定的时间间隔
8.datediff 返回两个日期之间的天数
9.date_format 用不同格式显示日期/时间

sql null值 代表遗漏的未知数据,默认地，表的列可以存放null值.
如果表中的某个列是可选的,那么我妹可以在不向该列添加值的情况下插入新记录或更新已有的记录。这意味该字段将以null值保存。null值的处理方式和其他值不同,null用作未知的或不适用的值的占位符。无法比较null和0；它们是不等价的。
xxx is null    xxx is not null

sql 通用数据类型 数据类型定义列中存放的值的种类 
数据库表种的每个列都要求洋浦名称和数据类型。SQL开发人员必须在创建SQL表时决定表中的每个列将要存储的数据类型。数据类型时一个标签，是便于sql了解每个期望存储什么类型数据的指南。它也标识了sql日和与存储的数据进行交互
下面表格中列出了sql中通用的数据类型
1.chartcer(n) 字符/字符串 固定长度n
2.varchar(n)或character varying  字符/字符串 可变长度 最大长度n
3.binary (n) 二进制串 固定长度n
4.varbinary(n) 或 binary varying(n) 二进制串，可变长度。最大长度n
5.integer(p) 整数值(没有小数点)进度p
6.smallint 整数值（没有小数点) 精度5
7.integer 整数值(没有小数点)精度10
8.bigint 整数值(没有小数点）精度19
9.decimal（p,s）精度数值,精度p 小数点后位数s
10.numeric（p,s）精确到数值，精度p 小数点后位数s
11.float(p) 近似数值，尾数精度p
12.real 近似数值，尾数精度7
13.float 近似数值 尾数精度16
14.date 存储年，月 日的值
15.tima 存储小时 分 秒的值
16.timstamp 存储 年 月 日 小时 分 秒 的值
17.interval 由一些整数数字字段组成的，代表一段时间，取决于取区间的类型
18.array 元素的固定长度的有序集合
19. multiset 元素的可变长度的无序集合
20.xml 存储xml数据

mysql数据类型 在mysql中，有三种主要的类型 text（文本）number（数字）和date/time（日期/时间）
###text类型
1.char(size）保持固定长度的字符串（可包含字母，数字及特殊字符）在括号中指定字符串的长度
2.varchar(size）保存可变长度的字符串（可包含字母，数字及特殊字符）。在括号中指定字符串的最大长度。最多255个字符。如超过255 则被转换为text类型
3.tinytext 存放最大长度为266个字符的字符串
4.text 存放最大长度为65535个字符的字符串
5.blob 用于blobs 存放最多65535字节的数据
6.mediumtext 存放最大长度为16777215个字符的字符串
7.mediumblob 用于blobs 存放做的16777215字节的数据
8.longtext 存放最大长度为4294967295个字符的字符串
9.longblob 用于blobs 存放最多4294967295字节的数据
10.enum（x,y,z,etc）允许您数日可能值的列表。可以再enum列表中列出最大的65535个值。如果列表中不存在插入的值，则插入空值.
11.set 与enum类似，不同的是set最多只能包含64个列表项且set可以存储一个以上的选择。
###number类型
1.tinyint（size）带符号-128到127 无符号 0到255
2.smallint（size）带符号-32768到32767 无符号 0到 65535 size默认为6
3.mediumint(size) 带符号 -8388608到8388607 无符号 0到 16777215 size默认为9
4.int(size) 默认为11
5.bigint 默认为20
6.float （size,d） 最大 和后位数
7.double 带浮动的大数字
8.decimal 做为字符串存储的双浮点数
###date类型
1.date YYYY-MM-DD
2.datetime YYYY-MM-DD HH:MM:SS
3.timestamp UTC YYYY-MM-DD HH:MM:SS
4.time HH:MM:SS
5.year 2位或4位格式的年

sql 函数 sql拥有可多可用于计数和计算的内建函数
sql aggregate函数计算从列中取得的值。返回一个单一的值
有用的aggregate函数
1.avg（）返回平均值
2.count 返回行数
3.first（）返回第一个记录的值 ###mysql不支持
4.last （）返回最后一个记录的值 ###mysql不支持
5.max()返回最大值
6.min（）返回最小值
7.sum（）返回总和
sql scalar函数
sql scalar函数基于输入值，返回一个单一的值
1.ucase 将某个字段转换为大写
2.lcase 讲某个字段转换为小写
3.mid 从某个文本字段提取字符 
4.substring （字段，1,end） 从某个文本字段提取字符
5.len 返回某个文本字段的长度
6.round 对某个数值字段进行指定小数位的四舍五入
7.now 返回当前的系统的日期和时间
8.format 格式化某个字段的显示方式

sql group by语句可以结合一些聚合函数，根据一个活多个列对结果集进行分组
sql having子句 在sql中增加having子句的原因是,where关键字无法与聚合函数一起使用。having子句可以让我们筛选分组后的各组数据
where 和having之后都是筛选条件，但是是有区别的
1.where在group by之前 having 在group by 之后
2.聚合函数(avg sum max min count)不能作为条件被放在where之后，但可以放在having之后

exists 运算符用于判断查询子句是否有记录，如果有一条或者多条激励存在返回true，否则返回false

######MYSQL
creare datevase 数据库名 创建数据库
drop datebase 数据库名 删除数据库
use 数据库名 选择数据库
create table 数据库表名
drop table 数据库表名

插入语句语法
insert into 数据库表名 (xxx,xxx) values （xxx,xxx）

查询语句语法
select  xxx from xxx [where xxx] [limit xxx] [offset xxx]
查询语句中你可以使用一个或多个表，表之间使用逗号逗号(,)分割,并使用where语句来设定查询的条件，可以使用limit属性来设定返回的记录数 可以通过offset指定select语句开始查询的数据偏移量 默认情况下偏移量是0

mysql where子句 查询语句中你可以使用一个或多个表，表之间十一逗号分割。并使用where语句来设定查询条件 可以在where子句中指定任何条件 where 子句也可以运用于sql的delete 或者update命令  where子句类似于程序语言中的if条件，根据mysql表中的字段来读取指定的数据
操作符
= 等于 检测两个值是否相等，如果相等返回true
<>,!= 不等于 检测两个值是否相等，如果不相等返回true
> 大于号,检测左边的值是否大于右边的值
<小于号，检测左边的值是否小于左边的值
>= 大于等于号 检测左边的值是否大于等于右边的值
<= 小于等于号 检测左边的值是否小于右边的值

update 更新 
update 表面 set xxx=xxx,xxx=xxx where xxx

delete语句 用来删除mysql数据库表中的记录 
delete  from 数据库表名  [where xxx]
如果不写where  就删除当前数据库表中的所有的值

like sql like 子句中使用百分号%字符来代表任意字符 类似于unix活正则表达式中的星号* 如果没有使用百分号% like子句与=好的效果是一样的
select xxx from where field1 like xxxx
可以再where子句中指定任何条件 
可以再where子句中使用like子句
可以用like子句代替=
like子句通常和%一起使用，类似于一个元字符的搜索
可以使用 and或者or指定一个或多个条件
可以在delete或update命令中使用where ...like子句指定条件
% 标识任意0个或多个字符。可匹配任意类型的和长度的字符，有些情况下是中文，需要使用两个百分号(%%)表示
_表示任意单个字符。匹配单个任意字符串。用来限制表达式的字符长度语句
[]表示括号内所列字符串的一个（类似正则表达式）。指定一个字符，字符串或泛微，要求所匹配对象为它们中的任一个
[^]表示不在括号之列内的单个字符。其取值和[]相同，但它要求所匹配的对象为指定字符串以外的任一个字符
查询内容包含通配符时，由于通配符的缘故，导致我们查询特殊字符% _ [的语句无法实现，而把特殊字符用[]括起来便可正常查询

mysql 分组 group by 语句 group by语句根据一个或多个列队结果集进行分组 在分组的列上我们可以使用count sum avg 等函数
with rollup 可以实现在分组统计数据基础上再进行相同的统计

mysql 连接的使用 join
inner join （内连接，或等值连接）：获取两个表中字段匹配关系的记录
left join 左连接 获取左边所以记录，即使右表没有对应匹配的记录
right join 右连接 用于获取右表的所有记录，即使左边没有对应匹配的考虑

mysql null值处理 
mysql使用sql select 命令及where子句来读取数据表中的数据。但是当提供的查询条件字段为null时 命令就可能无法正常工作
为了处理null的情况 mysql提供了三种运算符
is null 当列的值是Null 此运算符返回true
is not null 当列的值不为null 运算符返回true
<=> 比较操作符 当比较的两个值都相等或者都为null时返回true

mysql的正则表达式
^ 匹配输入字符串的开始位置
$ 匹配输入字符串的结束的位置
. 匹配除"\n"之外的任何单个字符
[...]字符集合
[^...]负值字符集合
p1|p2|p3 匹配p1 p2 p3 
* 匹配前面的子表达式零次或多次
+ 匹配前面的子表达式一次或多次
{n}  n是一个非负整数。匹配确定的n次
{n,m}m和n均为非负整数最少匹配n次最多匹配m次

mysql 事务 
事务主要用于出出力操作量大，复杂度搞的数据。
在mysql中只有使用了innodb数据库引擎的数据库或表才支持事务。事务吃力可以用来维护数据库的完整性，保证成批的SQL语句要么全部都执行，要么全部不执行
事务用来管理 insert update delete语句
一般来说，事务是必须满足4个条件。原子性 一致性 隔离性 持久性
原子性:一个事务中的所以操作，要么全部完成，要么全部不完成，不会结束在中间的某个环节。事务在执行过程中发生错误，会被回滚到事务开始前的状态，就像这个是我从来没有执行过一样。
一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这标识写入的资料必须完全符合所以的预设规则，这包含资料的精确度，串联性以及后续数据库可以自发性的完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力。隔离性可以防止多个事务并发执行由于交差执行而导致的数据不一致。事务的隔离分为不同级别包括读未提交，读提交，可重复读和串行化。
持久性：事务处理结束后，对数据的修改就是永久的，即使系统故障也不会丢失。

在mysql命令行的默认设置下，事务都是自动提交的，即执行的sql语句后悔立马执行commit操作。因此要显式的开启一个事务必须使用命令begin或start transaction。或者执行命令 set autocommit=0.用来禁止使用当前会话功能的自动提交

begin或start transction 显示地开启一个事务
commit也可以使用commit work ，不过二者是等价的。commithi提交事务，并使已对数据库进行的所以修改都成为永久性的。
rollback也可以使用rollback work ，不过二者是等价的。回滚会结束用户的事务。并撤销正在进行的所有未提交的修改。
savepount identifier,savepoint 允许事务中创建一个保存点，一个事务中创建一个保存点，一个事务中可以有多个savepoint
release savepoint identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常
Rollback to identifier 把事务回滚到标记点
set transcation 用来设置事务的隔离级别.
mysql事务吃力主要有两种方法
1.用begin开是一个事务
2.pollback事务回滚
3.commit 事务确认
直接用set来改变mysql的自动提交模式
1.set autocommit=0禁止
2.set autommit=1开启自动提交

alter 当我们需要修改数据表名或者修改数据字段时，就需要使用到mysql alter命令 。mysql的alter 命令用于修改数据库，表和索引等对象的结构。
alter 命令允许你添加，修改或删除数据库对象，并且可以用于更改表的列定义，添加约束，创建和删除索引等操作。
alter命令非常强大，可以再数据库结构发生变化时进行灵活的修改和调整

mysql索引 mysql索引使一种数据结构，用于加快数据库查询的速度和性能。mysql索引的建立对于mysql的高效运行是很重要的，索引可以大大提高mysql的检索速度
mysql索引类似于书籍的索引，通过存储指向数据行的指针，可以快速定位和访问表中的特定数据。
单列索引，即一个索引值包含单个列，一个表可以有多个单列索引
组合索引，即一个索引包含多个列
创建索引时，你要确保该索引时应用在sql查询语句的条件。实际上索引也是一张表。该表保证了主键与索引字段。并指向实体表的记录。
索引表虽然能够提高查询性能但是需要注意
1.索引需要占用额外的存储空间
2.对表进行插入，更新和删除操作时，索引需要维护，可能会影响性能。
3.过多或不合理的索引可能会导致性能下降。因此需要谨慎选择和规划索引

mysql 临时表在我们需要保存一些临时数据时时非常有用的。临时表只在当前连接可见，当关闭连接时，mysql会自动删除表并释放所有空间。
mysql临时表仅在当前连接可见。默认情况下，当你断开与数据库的连接后，临时表就会自动销毁

mysql 复制表  
create 加 select

mysql元数据 
1.查询结果信息：select update或 delete语句影响的记录数
2.数据库和数据表的信息：包含了数据库及数据表的结构信息
3.mysql服务器信息：包含了数据库服务器的当前状态，版本号
select version()服务器版本信息
select database （）当前数据库名（或者返回空）
select user()当前用户名
show status 服务器状态
show variables 服务器配置变量

mysql序列使用
mysql序列是一组整数:1,2,3由于一张数据表只能有一个字段自增主键。如果你想实现其他字段也实现自动增加就可以使用mysql序列来实现。

mysql 处理重复数据
有些mysql数据表中可能存在重复的记录，有些情况我们允许重复数据存在，但是有时候我们也需要删除这些重复的数据
设置主键和唯一关系禁止重复数据存在
统计重复数据
select count(*) as repeat from tablename group by xxx having repeat >1
一般情况下，查询重复的值。
1.确定哪一列包含的值可能会重复
2.在列选择列表使用count(*)列出的那些列
3.在group by 子句中列出的列
4.having 子句设置重复数大于1

mysql及sql注入
如果通过网页获取用户素日的数据并将其插入一个mysql数据库，那么就有可能发生sql注入安全的问题
防止sql注入的我们需要注意以下加点
1.永远不要信任用户的输入。对用户输入进行校验。可以通过正则表达式，或限制长度。
2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。
3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。
4.不要把机密信息直接存放，加密或者hash掉密码和敏感信息
5.应用的一次信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装
6.sql注入的检测方法一般采用辅助软件或平台来检测，软件一般采用sql注入检测工具jsky.

mysql导出数据
mysql 导入数据

mysql函数
mysql有很多内置函数
1.ascii(s) 返回字符串的第一个字符的assci码
2.char_length(S) 返回字符串s的字符数
3.chartter_length(s) 返回字符串s的字符数
4.concat(s1,s2) 字符串s1,s2等多个字符串合并为一个字符串
5.concat_ws 同上 不过需要x做为分隔符号
6.field （s,s1,s2...）返回第一个字符串s在字符串列表（s1,s2...）中的位置
7.find_in_set（s1,s2）返回在字符串s2与s1匹配的字符串的位置
8.format(x,n)函数可以将数字x进行格式化“#，###，##” 将x保留到小数点后n位，最后一位四舍五入
9.inset（s1,x,len,s2）字符串替换s1的x位置开始长度为len的字符串
10.loacte（s1,s）从字符串s中获取s1的开始位置
11.locate（s）将字符串s的所有字母变成小写字母
12.left（s,n）返回字符串s的前n个字符
13.lower（s）将字符串s的所有字母变成小写字母
14.lpad 在s1的开始位置填充字符串s2,使字符串长度达到len
15.ltrim 去掉字符串s开始处的空格
16.mid（s,n,len） 从字符串s的位置截取长度为len的子字符串
17.position 从字符串s中获取s1的开始位置
18.repeat 讲字符串重复n次
19.replace 将字符串s2替代字符串s中的字符串s1
20.reverse（s） 将字符串s的顺序反过来
21.right 返回字符串s的后n个字符
22.rpad 在字符串s1的结尾处添加字符串啥
23.rtrim 去掉字符串s结尾处的空格
24.space 返回n个空格
25.strcmp 从字符串s的start位置截取长度为length的子字符串
26.substr 从字符串s的start位置截取长度为length的子字符串
27.substring 从字符串s的start位置截取字符串长度为Length的子字符串，等同于substr
28.substring_index 
未完待续不想写了太多了