数据库基础

1.SQL对大小写不敏感，SELECT与select是相同的

2.一些重要的SQL命令：select：从数据库中提取数据 update：更新数据库中的数据 delete：从数据库中删除数据 insert into：向数据库中插入新数据 create database：创建新数据库
alter database：修改数据库 create table：创建新表 alter table：改变数据库表 drop table：删除表 create index：创建索引 drop index：删除索引

3.select语句用于从数据库中选取数据，结果被存储在一个结果表中，称为结果集
语法：select column_name,column_name from table_name;
select *from table_name;

4.select distinct语句，在表中，一个列可能会包含多个重复值，如果希望仅仅列出不同的值，distinct关键字用于返回唯一不同的值
语法：select distinct column_name,column_name from table_name;

5.where子句用于提取那些满足指定标准的记录，语法：
select column_name,column_name from table_name where column_name operator value;
where子句的运算符：=，<>(不等于），>,<,>=,<=,BETWEEN(在某个范围内），LIKE（搜索某种模式），IN（指定针对某个列的多个可能值）
逻辑运算的优先级：（）、not、and、or

6.and&or运算符：如果第一个条件和第二个条件都成立，则and运算符显示一条记录 如果第一个条件或第二个条件只要有一个成立，则or运算符显示一条记录

7.order by关键字用于对结果集按照一个列或多个列进行排序 order by关键字默认按照升序对记录进行排序，如果需要按照降序对记录进行排序，可以使用desc关键字
语法：select column_name,column_name from table_name order by column_name,column_name asc|desc（升序或降序）
order by A，B都升序 order by A desc,B A降序，B升序 order by A ，B desc A升序，B降序
即desc或asc只对她紧跟的第一个列名有效，其他的不受影响，仍然是默认的升序

8.insert into语句用于向表中插入新纪录，语法:insert into有两种编写形式，第一种形式无需指定要插入数据的列名，只需提供被查入的值即可：
insert into table_name values (value1,value2,value3,…);
第二种形式需要指定列名及被插入的值：insert into table_name (column1,column2,column3,…) values (value1,value2,value3,…);

9.insert into select与select into from的区别：
insert into scorebak select *from score where neza=‘neza’; 插入一行，要求表scorebak必须存在
select *into scorebak from score where neza=‘neza’; 插入一行，要求表scorebak不存在

10.update语句用于更新表中以存在的记录，语法：
update table_name set column1=value1,column2=value2,…where some_column=some_value;
where子句规定那条记录或那些记录需要更新，如果省略了where子句，所有的记录都会被更新

1.delete语句用于删除表中的行，语法：delete from table_name where some_column=some_value;
where子句规定那条记录或那些记录需要删除，如果省略了where子句，所有的记录都会被删除

2.SQL关于删除的三个语句：drop、truncate、delete的区别：
drop：drop test 删除表test，并释放空间，将test删除的一干二净
truncate：truncate test 删除表test里的内容，并释放空间，但不删除表的定义，表的结构还在
delete：（1）删除制定数据：删除表test中年龄等于30且国家为US的数据：delete from test where age=30 and country =‘US’；
（2）删除整个表：仅删除表test内的所有内容，保留表的定义，不释放空间：delete from test 或delete *from test

3.select top子句用于规定要返回的记录的数目，对于拥有数千条记录的大型表来说是非常有用的
并非所有的数据库系统都支持select top语句，MYSQL支持limit语句来选取指定的条数数据，Oracle可以使用rownum来选取
语法：SQL：select top number|percent column_name(s) from table_name;
MYSQL: select column_name(s) from table_name limit number;
Oracle: select column_name(s) from table_name where rownum<=number;
变相返回后N行：前5行：select top 5 * from websites;
后5行：select top 5 * from websites order by ID desc;

4.like操作符用于在where子句搜索列中的指定模式，语法：
select column_name(s) from table_name where column_name like pattern;

5.通配符可用于替代字符串中的任何其他字符，在SQL中，通配符与SQL like操作符一起使用，用于搜索表中的数据，%：替代0或多个字符 _：替代一个字符 _ _:替代两个字符
[charlist ]:字符列中的任何单一字符 [^charlist] 或[!charlist]:不在字符列中的任何单一字符
MySQL 中使用regexp或not regexp运算符来操作正则表达式
如选取name以“G”、“F”或“s”开始的所有网站：select * from websites where name regexp '^[GFs ';
like ‘[M-Z]inger’ 将搜索一字符串inger结尾，以从M到Z的任何单个字母开头的所有名称，如Ringer
like ‘M[^c]%’ 将搜索以字母M开头，并且第二个字母不是c的所有名称，如MacBook；

6.in操作符允许在where子句中规定多个值，语法：
select column_name(s) from table_name where column_name in (value1,value2,…);

7.between 操作符用于选取介于两个值之间的数据范围内的值，这些值可以是数值，文本或日期，语法：
select colum_name(s) from table_name where column_name between value1 and value2;

8.SQL别名：可以通过使用SQL为表名称或列名称指定别名，语法：
列的SQL别名语法：select column_name as alias_name from table_name;
表的SQL别名语法: select column_name from table_name as alias_name;
如同一语句使用别名与不使用别名的比较：select w.name,w.url,a.count,a.data from websites as w,access_log as a where a.site_id=w.id and w.name=“菜鸟教程”;
select webstes.name,websites.url,access_log.count,access_log.data from websites,access_log where access_log.site_id=websites.id and websites.name=“菜鸟教程”；
在以下情况下，使用别名很有用：在查询中涉及超过一个表 在查询中使用了函数 列名称很长或可读性差 需要把两个列或多个列结合在一起

9.inner join 关键字：inner join关键字在表中存在至少一个匹配时返回行，语法：
select column_name(s) from table1 inner join table2 on table1.column_name=table2.column_name; inner join与join是相同的
如：select websites.name,access_log.count , access_log.date from websites inner join access_log on websites.id=access_log.site_id order by access_log.count;

10.left join关键字：从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表没有匹配，则返回空值null；语法：
select column_name(s) from table1 left join table2 on table1.column_name=table2.column_name;
如：select websites.name,access_log.count,access_log.date from websites left join access_log on websites.id=access_log.site_id order by access_log.count desc;

1.right join 关键字：从右表（table2）返回所有的行，即使左表（table1）中没有匹配。如果左表没有匹配，则返回空值null；语法：
select column_name(s) from table1 right join table2 on table1.column_name=table2.column_name;

2.union操作符：用于合并两个或多个select语句的结果集，语法：
select column_name(s) from table1 union select column_name(s) from table2; 默认地，union操作符选取不同的值，如果允许重复的值，使用union all；
select column_name(s) from table1 union all select column_name(s) from table2; union结果集中的列名总是等于union中第一个select语句中的列名

3.select into 语句：通过SQL，可以从一个表复制信息到另一个表，select into语句从一个表复制数据，然后把数据插入到另一个新表中； MySQL数据库不支持select…into语句；语法：
select column_name(s) into newtable[IN externaldb] from table1;

4.insert into select语句：从一个表复制数据，然后把数据插入到一个已存在的表中。目标表中任何已存在的行都不会受到影响，语法：
从一个表中复制所有的列插入到另一个已存在的表中：insert into table2 select * from table1;
只复制希望的列到另一个已存在的表中：insert into table2(column_name(s)) select column_name(s) from table1;

5.create database：用于创建数据库，语法：create database dbname；

6.create table：用于创建数据库中的表，标由列和行组成，每个表都必须有个表名；语法：
create table table_name（column_name1 date_type(size), column_name2 data_type(size), column_name3 data_type(size),…）；
其中column_name参数规定表中列的名称，data_type参数规定列的数据类型（如varchar、integer、date等），size参数规定表中列的最大长度

7.SQL约束：用于规定表中的数据规则，如果存在违反约束的数据行为，行为会被约束终止；约束可以在创建表时规定（通过create table 语句），或在表创建之后规定（通过alter table语句），语法：
create table table_name(column_name1 data_type(size) constraint_name,column_name2 data_type(size) constraint_name,…);
在SQL中，有如下约束：not null：只是某列不能存储null值 unique：保证某列的每行必须有唯一的值 primary key：not null和unique的结合，确保某列（或两个列多个列的结合）有唯一标识，有助于更容易更快速的找到表中的一个特定的记录
foreign key：保证一个表中的数据匹配另一个表中的值的参照完整性 check：保证列中的值符合指定的条件 default：规定没有给列赋值时的默认值

8.not null约束：在默认的情况下，表的列接受null值，not null约束强制列不接受null值，not null约束强制字段始终包含值，意味着如果不向字段添加值，就无法插入新记录或更新记录；

9.unique约束：唯一标识数据库表中的每条记录，unique和primary key约束均为列或列集合提供了唯一性的保证，primary key约束拥有自动定义的unique约束，但是每个表中可以有多个unique约束，但只能有一个primary key约束；
如需命名unique约束，并定义多个列的unique约束，可以使用语法：constraint uc_PersonID unique(column_name1,column_name2,…);
uc_PersonID 是一个约束名 ！上面建的是唯一约束，为了方便区别约束名一般起得有规律点比如 UC（就是 UNIQUE CONSTRAINT 的缩写意思是唯一约束） uc_PersonID 就是对表中的PersonID 建唯一约束，强制约束 Id_P 和 LastName 唯一。
alter table时的SQL unique约束：当表已被创建时，如需在“P_Id”列创建unique约束，使用下面的SQL：alter table table_name add unique(P_Id);
如需命名unique约束，并定义多个列的unique约束，可使用语法：alter table table_name add constraint uc_PersonID unique(column_name1,column_name2,…);
撤销unique约束：可使用语法：MySQL：alter table table_name drop index uc_table_nameID; SQL Server/Oracle: alter table table_name drop constraint uc_table_nameID;

10.primary key 约束：唯一标识数据库表中的每条记录，not null和unique的结合，每个表都应该有一个主键，且每个表只能有一个主键；语法：
如需命名primary key约束，并定义多个列的primary key约束，可使用语法：constraint pk_table_nameID primary key（column_name1,column_name2,…）;
alter table时的SQL primary key约束：当表已被创建时，如需在“P_Id”列创建primary key约束，可使用语句：
alter table table_name add primary key(P_Id); alter table table_name add constraint pk_table_nameID primary key(column_name1,column_name2,…);
撤销primary key约束：MySQL：alter table table_name drop primary key; SQL Server/Oracle/MS Access: alter table table_name drop constraint pk_table_nameID;
撤销primary key 约束时，不论约束条件是一列还是多列，对于MySQL，撤销都是：alter table table_name drop primary key
由于primary key唯一性，MySQL处理方法简单，但对于SQL Server/Oracle/MS Access，一个列：alter table table_name drop constraint P_Id;
如多个列：alter table table_name drop constraint pk_table_nameID;

1.foreign key约束：一个表中的foreign key指向另一个表中的unique key，
MySQL：foreign key (column_name) references table_name(column_name);
SQL Server/Oracle/MS Access: column_name data_type foreign key references table_name（column_name）;
alter table 时的SQL foreign key约束：当表一已被创建时，如需在column_name列创建foreign key约束：
alter table table_name add foreign key(column_name) references table_name(column_name);
如需命名foreign key约束，并定义多个列的foreign key约束：
alter table table_name add constraint fk_table_name foreign key(column_name) references table_name(column_name);
撤销foreign key 约束：MySQL：alter table table_name drop foreign key fk_table_name；
SQL Server/Oracle/MS Access: alter table table_name drop constraint fk_table_name;

2.check 约束：用于限制列中的值的范围，如果对单个列定义check约束，那么该列只允许特定的值；如果对一个表定义check约束，那么此约束会基于行中其他列的值在特定的列中对值进行限制，语法：
MySQL：如check（column_name>0） SQL Server/Oracle/MS Access:如 column_name data_type not null check(column_name>0);
如需约束多个列的check约束：constraint chk_table_name check (column_name>0 and column_name=“sad”);
alter table 时的SQL check约束：alter table table_name add check(column_name>0);
约束多个列的check约束：alter table table_name add constraint chk_table_name check(column_name>0 and column_name=“sad”);
撤销check约束：SQL Server/Oracle/MS Access: alter table table_name drop constraint chk_table_name;
MySQL: alter table table_name drop check chk_table_name;

3.default约束：用于向列中插入默认值，如果没有规定其它的值，那么会将默认值添加到所有的新纪录；语法
column_name data_type(size) default ‘value’
alter table 时的的default约束：MySQL：alter table table_name alter column_name set default ‘value’
SQL Server/MS Access: alter table table_name add constraint ab_c default ‘value’ for column_name;
Oracle: alter table table_name modify column_name default ‘value’
撤销default约束：MySQL： alter table table_name alter column_name drop default;
SQL Server/Oracle/MS Access: alter table_name alter column column_name drop default

4.create index语句：用于在表中创建索引，在不读取整个表的情况下，索引使数据库应用程序可以更快的查找数据
可以在表中创建索引，以便更加快速高效的查询数据，用户无法看到索引，它们只能被用来加速搜索/查询；一般只在常常被搜索的列（以及表）上面创建索引
语法：在表上创建一个简单的索引，允许使用重复的值：create index index_name on table_name(column_name);
在表上创建一个唯一的索引，不允许使用重复的值，唯一的索引意味着两个行不能拥有相同的索引值。语法：
create unique index index_name on table_name(column_name,column_name)

5.SQL撤销索引，撤销表以及撤销数据库：通过使用drop语句，可以轻松删除索引，表和数据库
drop index语句用于删除表中的索引：用于 MS SQL Server 的 DROP INDEX 语法： drop index table_name.index_name
用于 MySQL 的 DROP INDEX 语法： alter table table_name drop index index_name
drop table 语句用于删除表：drop table table_name
drop database语句用于删除数据库：drop database database_name
truncate table语句用于删除表内的数据而不删除表本身：truncate table table_name

6.alter table 语句用于在已有的表中添加、删除或修改列；
如需在表中添加列：alter table table_name add column_name datatype
如需删除表中的列：alter table table_name drop column column_name
要改变表中列的数据类型：SQL Server / MS Access： alter table table_name alter column column_name datatype
My SQL / Oracle： alter table table_name modify column column_name datatype

7.auto increment字段：在每次插入新记录是自动的创建主键字段的值

8.视图view：视图包含行和列，就像一个真实的表，视图中的字段是来自一个或多个数据库中的真实的表中的字段。可以向视图添加SQL函数，where以及join语句，也可以呈现数据，就像这些数据来自某个单一的表一样
语法： 创建视图： create veiw view_name as select column_name(s) from table_name where condition
更新视图： create or replace view view_name as select column_name(s) from table_name where condition
撤销视图：drop view view_name
视图的作用：a.视图隐藏了底层的表结构，简化了数据访问操作，客户端不再需要知道底层表的结构及其之间的关系
b.视图提供了一个统一访问数据的接口，即可以允许用户通过视图访问数据的安全机制，而不授予用户直接访问底层表的权限
c.从而加强了安全性，使用户只能看到视图所显示的数据
d.试图还可以被嵌套，一个视图中可以嵌套另一个视图

9.Date函数：SQL Server Date函数：getdate()返回当前的日期和时间 datepart()返回日期/时间的单独部分 dateadd()在日期中添加或减去指定的时间间隔 datediff()返回两个日期之间的时间 convert()用不同的格式显示日期/时间
SQL Server使用下列数据类型在数据库中存储日期或日期时间值：DATE-格式：YYYY-MM-DD DATETIME-格式:YYYY-MM-DD HH:MM:SS SMALLDATETIME-格式:YYYY-MM-DD HH:MM:SS TIMESTAMP-格式:唯一的数字

10.null值代表遗漏的未知数据，可使用is null 和is not null来测试null值

1.通用数据类型：数据类型定义列中存放的值的种类；常见数据类型：char(n)：字符/字符串。固定长度 n varchar(n)：字符/字符串。可变长度。最大长度 n。 BOOLEAN：存储TRUE或FALSE值 integer：整数值（没有小数点）。精度 10
smallint：整数值（没有小数点），精度 5 decimal（p,s）：精确数值，精度 p，小数点后位数 s。例如：decimal(5,2) 是一个小数点前有 3 位数，小数点后有 2 位数的数字。 numeric（p,s）： 精确数值，精度 p，小数点后位数 s。（与 DECIMAL 相同）
date ：存储年、月、日的值。 time： 存储小时、分、秒的值。 timestamp：存储年、月、日、小时、分、秒的值 float： 近似数值，尾数精度 16
SQL aggregate函数：计算从列中取得的值，返回一个单一的值，有用的aggregate函数：avg() count() first() last() max() min() sum()

2.avg（）函数：返回数值列的平均值；语法：select avg(column_name) from table_name
如选择访问量高于平均访问量的site_id 和count：select site_id,count from access_log where count>(select avg(count) from access_log);

3.count()函数：返回匹配指定条件的行数 ；语法：select count (column_name) from table_name; count(column_name)函数返回指定列的值的数目，（null值不计入）
select count（） from table_name ；count() 函数返回表中的记录数
select count(distinct column_name) from table_name； count（distinct column_name）返回指定列的不同值的数目

4.first（）函数：返回指定的列中第一个记录的值；语法：select first(column_name) from table_name; 注：只有MS Access支持first（）函数
SQL Server语法：select top 1 column_name from table_name order by column_name asc;

5.last() 函数：返回指定的列中的最后一个记录的值； 语法：select last(column_name) from table_name; 注：只有MS Access支持first（）函数
SQL Server语法：select top 1 column_name from table_name order by column_name desc;

6.max()函数：返回指定列的最大值； 语法： select max(column_name) from table_name;

7.min()函数：返回指定列的最小值； 语法：select min（column_name） from table_name;

8.sum() 函数：返回数值列的总数； 语法：select sum(column_name) from table_name;

9.group by语句：用于结合聚合函数，根据一个或多个列对结果集进行分组；语法：
select column_name ,aggregate_function(column_name) from table_name where column_name operator value group by column_name;
如统计access_log 各个site_id的访问量：select site_id ,sum(access_log.count) from access_log group by site_id;

10.having子句：使用having子句原因是where关键字无法与聚合函数一起使用；having子句可以让我们筛选分组后的各组数据；语法：
select column_name ,aggregate_function(column_name) from table_name where column_name operator value group by column_name having aggregate_function(column_name) operator value;
如要查找总访问量大于200的网站：select websites.name, websites.url, sum(access_log.count) from (access_log inner join websites on accesss_log.site_id=websites.id) group by websites.name having sum(access_log)>200;

1.ucase（）函数：把字段的值转换为大写，语法：select ucase(column_name) from table_name;
用于SQL Server的语法：select upper(column_name) from table_name;

2.lcase(）函数：把字段的值转换为小写，语法：select lcase(column_name) from table_name;
用于SQL Server的语法：select lower (column_name) from table_name;

3.mid() 函数：从文本字段中提取字符，语法：select mid(column_name, start [,length]) from table_name;
参数column_name、start都是必需，且规定开始位置即起始值为1，length为可选，表示要返回的字符数，如果省略，则mid（）函数返回剩余文本

4.len（）函数：返回文本字段中值的长度，语法：select len(column_name) from table_name;
MySQL中函数为length（）：select length (column_name) from table_name;

5.round() 函数：用于把数值字段舍入为指定的小数位数，语法：
select round (column_name ,decimals) from table_name; 参数：column_name:要舍入的字段 decimals：规定要返回的小数位数
round（x）:返回参数x的四舍五入的一个整数 如：select round(-1.23) 得-1 select round(-1.68) 得-2 select round(1.23) 得1
round(x,d) :返回参数x的四舍五入的有d位小数的一个数字，如果d为0，结果将没有小数点或小数部分； 如：select round(1.583,2) 得1.59

6.now（）函数：返回当前系统的日期和时间，语法：select now() from table_name;

7.format() 函数：用于对字段的显示进行格式化，语法：select format (column_name,format) from table_name;