mysql的前世今生

SQL简介：

    诞生于七十年代的IBM公司，最初被称为结构化查询语句。 

关系型数据库系统的十二条规则：

0.关系型数据库必须通过关系来实现对数据的完全管理

1.所有在关系型数据库的信息均可以在表中以数值的形式加以体现。

2.在关系型数据库中的每一项数据均可以通过库名，键名和列名来准确指定。

3.关系型数据库系统必须对空值提供系统级的支持。有独特的缺省值，而且具有独立域。

4.活动的，即时的数据联合-它的意思就是在数据库中的数据应有逻辑表格的行的形式来表达，并且可以通过数据处理语言来访问。

5.完善的数据子语句-它应该至少支持一种有严格语法规则和功能完善的语言，并且应该支持数据和定义，处理，完整性，权限以及事务等操作。

6.查看更新规则-所有在理论上可以更新的视图可以通过系统操作来更新。

7.数据库中数据和插入，更新，删除操作-该数据库系统不仅要支持数据行的访问，还要支持数据和的插入，更新和删除操作。

8.数据和物理独立性-当数据在物理存储结构上发生变化时应用程序在逻辑上不应受到影响。

9.数据的逻辑独立性-当改变表的结构时应用程序在最大程度上不受影响。

10.有效性独立-数据库的语言必须有定义数据完整性规则的能力，数据应即时存储在线目录，而且在处理时必须通过这一五一节。

11.发布的独立性-当数据第一次发布或当它重新发布时应用程序不受影响。

12.任何程序不可能使用更低级的语言从而绕过数据库语言的有效性规则定义。

SQL是操作和检索关系型数据库的事实上的标准语言，它允许程序员和数据库管理元做如下的工作：

1.更改数据库的结构

2.更改系统的安全设置

3.增加用户对数据库或表的许可权限。

4.在数据库中检索需要的信息

5.对数据库的信息进行更新。

整理的sql:

去掉重复的列	select DISTINCT name from users;
每列都加2,都减2	select age,age+2,age-2 from users;
每列都除以2，都乘以2	select age,age/2,age*2 from userinfo;
对某一列进行取模的两种方式	select age,age%2 from userinfo;       select age,MOD(age,2) from userinfo;
where 条件不等于的两种方式	select * from userinfo where age != 30;      select * from userinfo where age <> 30;
where 条件模糊查询	select * from userinfo where username like 'z%';
拼接两列	select *, CONCAT(username,"     ",email) from userinfo;
查找某列不为null 的记录	select * from userinfo where email is not null;
sum:返回某一列的所有数值的和	select sum(singles) from teamstats;
求平均数	select *,avg(age) from users group by name;
求最大值	select*,max(age) from users group by name;
求最小值	select *,min(age) from users group by name;
where条件模糊匹配	select* from users  where age in(24,18,20);
where 条件在...~...范围之间	select * from price where wholesale >0.25 and wholesale < 0.75
where 条件在...~...范围之间	select * from price where wholesale between 0.25 and 0.75
统计	select *,count(age) from users group by name;
求方差	select * ,variance(hits) from teamstats
标准差	select * ,stddev(hits) from users;
绝对值	select abs (a) absolute_value from numbers
返回系统日期的两种方式	select SYSDATE();   select now();
变小写	select * , lower(username)from userinfo;
变大写	select * , upper(username)from userinfo;
向上取整	select *,CEIL(framesize) from bikes;
向下取整	select *,FLOOR(framesize) from bikes;
返回给定的参数为指数，以e为底数的幂值	select *,EXP(framesize) from bikes;
对数函数，LN返回给定参数的自然对数	select *,LN(framesize) from bikes; select *,LOG(framesize) from bikes;
不够补字	select * , lpad(username,15,'&')from userinfo;
不够补字	select * , rpad(username,15,'&')from userinfo;
去左空格	select * , ltrim(username)from userinfo;

union all 与union 一样对表进行了合并，但是它不去掉重复的记录。

//intersect(相交：返回两个表中共有的行) [mysql 不支持该操作符]

select * from userinfo
intersect 
select * from userinfo_copy;

//minus(相减：minus返回的记录是存在于第一个表中但不存在第二个表中的记录) [mysql 不支持改操作符]

select * from userinfo
minus

select * from userinfo_copy;

//将给定的日期增加2个月[mysql不支持该函数] 

select *,add_months(updateTime,2) from userinfo;

//mysql将给定的日期增加2个月

select *,adddate(now(), interval 2 month),adddate(updateTime, interval 2 month) from userinfo;

//返回指定月份最后一天

select last_day(updateTime) from userinfo;

//给定的两个日期中间隔多少个月[慎用，因为每月天数不固定，返回近似值，从2月1到2-28它返回0]

select task startdate enddate months between(startdate,enddate) duration from project 

// 返回与指定日期在同一个星期或之后一个星期内的，你所要求的星期天数的确切日期(如果你想知道你锁指定的日期的星期五是几号)[mysql不支持该函数]

select next_day(sysdate,1) from dual;

//返回某一个数对另一个数的幂，在使用幂函数时，第一个参数为底数，第二个指数

select *,POWER(framesize,2) from bikes;

//该函数返回参数的平方根，负数不能开平方，所以得先取其绝对值

select *,SQRT(ABS(framesize)) from bikes;

//将参数的第一个字母变为大写，此外其他的字母则转换成小写[mysql 不支持该函数]

select *, initcap(username) from userinfo;
 

//去右空格

select * , rtrim(username)from userinfo; 

//替换给定字符

select * , replace(username,'o', '**')from userinfo;

//统计某个库下有多少table表

select count(table_name) from information_schema.`tables` where table_schema='test';

 //截取字符串，下标从0开始，截取两位

 select SUBSTR(username,  1 , 2)from userinfo;

//返回字符串的长度

select length(username) from userinfo;

//指定列转换成字符[mysql不支持:to_number/to_char] 

select *,to_char(sex) from userinfo;

//返回最大的

select GREATEST('A','BC','DEF') from dual;

//HAVING 对由sum或其它集合函数运算结果的输出进行限制。

select name,money from userinfo group by name having sum(money) >500

//插入语句

insert into tableName(col1,col2)values(value1,value2)


插入数据遵循的三条规则：

1.你所要插入的数值与它所对应的字段必须具有相同的数据类型

2.数据的长度必须小于字段的长度

3.插入的数值列表必须与字段的列表相对应

insert select 语句遵循的规则：

1.select 语句不能从被插入数据的表中选择行

2.insert into 中的列数必须与select语句返回的列数相同

3.insert into 中的数据类型要与select 语句返回的数据类型相同

//拷贝表数据

insert into userinfo_copy select * from userinfo 

//选定列，拷贝表数据
 

insert into userinfo_copy(id,username) select id,username from userinfo 

//修改某一列增加10%，千万别直接用10% ，需用0.1

update bikes set framesize = framesize+ framesize*0.1 where id = 3
 

//创建数据库

create database yidiandian;

//使用当前数据库

use yidiandian

设计数据库要考虑的问题：

1.安全问题

2.磁盘的可用空间

3.数据检索及响应的速度

4.数据更新的速度

5.多表归并返回数据的速度

6.RDBMS对临时表的支持

建立数据字典

数据字典是数据库设计人员重要的文档资料，它有以下功能：
 

1.数据库的设计意图，谁将会使用它

2.数据库的自身资料：用什么创建的数据库，数据库的大小是多少，日志文件的尺寸是多少

3.任何数据库安装或反安装的SQL源代码记录，包括导入/导出数据库的文档资料记录。

4.对每一个表的详细描述以及它的最终目的是什么

5.每一个表的内部结构的资料，包括表中的所有字段，数据类型以及注释情况以及所有的索引和所有的视图

6.对于每一个存储过程的原代码和触发机制

7.说明数据库是否具有唯一值及非空值约束，并说明这些约束是关系型数据库管理系统强加的还是数据库编制人员设定的，以及约束在原代码中的作用范围。

Oracle 标准
数据类型	说明
char	可以存储长度为1~255个字符的字符串，空格会被填充到字符串的右边以保证其内容满足定义的长度
date	包括日期的世纪，年，月，日，时，分，秒
longraw	可以存储长达2G的二进制内容
number	零，正值或负值的定点或浮点数
raw	可以存储不过255个字节的二进制代码
rowid	用一个十六进制的数来标明当前行在表内的唯一地址
Varchar2	变长的字母或数字，长度可以从1到2000

[mysql 不支持]

create table new_userinfo(id,username)as(select id ,username from userinfo)
 

//修改表增加一列

alter table userinfo 

add mouse varchar(20)

//修改表修改一列类型的长度

alter table userinfo 
modify mouse varchar(40)

//删除表

drop table tableName

//创建视图
create view v_userinfo 
AS select * from userinfo;

//选定列创建视图
create view v_userinfo_2(id ,username)
as select id,username from userinfo;
 

视图常常被称为虚表，它是用create view语句建立起来的，在视图建立后，可以对视图采用如下命令：

select, insert, input, update, delete

索引是与磁盘上数据的存储方式不同的另外一种组织数据的方法，索引的特例是表中记录依据其在磁盘上的存储位置显示，索引可以在表中创建一个列或列的组合。当应用索引以后，数据会暗战你使用create index 语句所定义的排序方式返回给用户，通过对正确的 ，特定的两个表的归并字段进行索引可以获得明显的好处。

drop view v_name

使用索引：
使用索引是另外一种让数据提供给用户的形式与它在数据库中不同的方法，此外，索引可以让存储在磁盘上的数据进行重新排序

使用索引的原因：

1.在使用unique关键字时强制性的保证数据的完整性

2.可以容易的用索引字段或其他字段进行排序

3.提高查询的执行速度

create index index_name on table_name(column_name)

drop index index_name

使用索引的技巧：

1.对于小表，使用索引对于性能不会有任何提高

2.当你的索引劣种有极多的不同的数据和空值时索引会使性能有极大的提高

3.当查询要返回的数据很少时索引可以优化你的查询(25%)

4.索引可以提高数据的返回速度，但是它使得数据的更新操作变慢，在对大量记录进行更新时，应该先删除索引，当执行更新操作后，恢复索引。

5.索引会占用你的数据库的空间。设计数据库的可用空间时要考虑索引所占用的空间

6.对字段的索引已经对两个表进行了归并操作，这一技术可以极大的提高归并的速度

7.不要对经常需要更新或修改的字段创建索引，更新索引的开销会降低你所期望获得的性能

8.不要将索引与表存储在同一个驱动器上，分开存储会去掉访问的冲突从而使结果返回得更快

9.对多个字段进行索引时，称为复合索引。使用复合索引时，要把最可能选择的字段放前面，这样可以提高查询的性能

视图主要用于以下方面：

1.提高用户数据的安全性

2.进行单位换算

3.创建一个新格式的虚表

4.使复杂查询的构筑简单化

事务控制[事务处理]是知关系数据库系统执行数据库事务的能力，事务是指在逻辑上必须完成的一命令序列的单位，单位工作期是指事务的开始和结束使其。如果在事务中产生的错误，那么整个过程可以根据需要被终止。

//删除用户

drop user username;

//添加角色

grant role to user

//删除角色

revoke role from user;

//创建视图的权利

grant crate view to userName;

使用触发机制时的限制：

1.当你在使用触发机制时你必须要知道它有如下的使用机制

2.不能在临时表中创建触发机制

3.触发机制必须在当前的表所在的数据库中创建

4.不能在视图中创建触发机制

5.当表被删除以后，所有与之相关的触发机制会被自动的删除

SQL 语句优化以提高其性能：

1.使用索引可以提高数据的访问速度

2.避免使用or.or 会不可避免的根据表的大小降低查询速度，in通常会比or要快。

3.并发操作越多，数据库性能下降的可能性越大

4.删除索引以优化数据的载入