1.SELECT 查询

基本语法

1
select column_name,column_name,... from table_name;

SELECT DISTINCT (查找值并去重)

列出表中该字段某一的值(去重)

select distinct column_name from table_name;

查找去重后的数量

select count(distinct userName) from yt_ksbao_video_userclick;

1.SELECT INTO [MYSQL不支持]

复制所有的列插入到新表中

基本语法

select * into newTable [in externaldb] from table1;
或者只复制希望的列插入新表中
select column_name(s) into newTable [in externaldb] from table1;
// mysql不支持此语法

2.WHERE 查询条件

where 用于过滤记录,如果where条件是数字 则不需要加引号

基本语
select column_name from table_name where column_name=’要排除的内容’

可以使用的运算符

=       等于
<>      不等于,在其他SQL版本中可以将改操作符改写成!=
>       大于
<       小于
>=      大于等于
<=      小于等于
between 在某个范围
like    搜索某种模式
in      指定针对某个列的多个可能值

逻辑运算

and     同时满足多个条件
or      满足其中一个值
not     非 满足不包含该条件的值

逻辑运算的优先级
() not and or

特殊条件

1.空值判断    is null
    select * from table_name where column_name is null;
2.between  and
    包含边界(mysql)
    select * from table_name where between 1500 and 3000;
    大于等于1500小于等于3000
    注意 not between 不包括边界

3.in
    查询EMP sal字段等于  5000,3000,1500 的值
    select * from table_name where sal in (5000,3000,1500)

4.like

    Like模糊查询
    查询EMP表中Ename列中有M的值,M为查询内容中的模糊信息
    %表示多个字值,_表示一个字符;
    M%: 表示的意思为模糊查询信息为M开头的。
    %M%:表示查询包含M的所有内容。
    %M_:表示查询以M在倒数第二位的所有内容。

5.不带比较运算符的where子句    

    如果不带云算法会把0抓换成false,其余的抓换成true,

    SELECT studentNO FROM student WHERE 0
    上面的放回空集

    SELECT studentNO FROM student WHERE 'abc'
    返回所有值

不使用join查询

select tj.name,tjs.name,tjs.id from testJoin as tj,testJoinSave as tjs where tj.id=tjs.testJoinId;

is null is not null

select * from where table_name where is null;

3.ORDER BY 排序

ORDER BY 关键字用于对结果集按照一个列或者多个列进行排序。

基本语法

select * from table_name order by column_name,column_name desc|asc;

示例

select * from talbe_name order by country,alexa;
按照country alexa进行顺序排序(country,alexa)

order by 多列

order by A desc,B // 这个时候A是降序
order by A,B desc // 这个时候A是升序,B是降序
order by a desc,b asc // a降序 b升序

4.INSERT INTO 插入

表中插入新数据

基本语法
1.第一种形式无需指定要插入数据的列名,只需提供被插入的值即可:

insert int table_name values (value1,value2,value3....)

2.第二种形式需要执行列名和被插入的值

insert into table_name (column1,column2) values (value1,value2)

3.插入多条数据

insert into table_name values (value1,value2),(value1,value2);

注意 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 不存在

1.INSERT INTO SELECT

通过 SQL,您可以从一个表复制信息到另一个表。

基本语法

insert into newTable(column_name(s)) select column_name(s) from table1;

示例

insert into testSql02(name,sqlTest) select name,sqlTest from testSql;

5.UPDATE 修改

UPDATE语句用于更新表中的记录

基本语法

update table_name set column1=value1,column2=value2 where some_column=some_value;

注意事项

如果没有where子句的update语句需要慎重,会把所有的数据都修改掉。

sql_safe_updates

可以设置sql_safe_updates自带的参数来解决,如果没有会报错。

set sql_safe_updates=1; 
设置这个后无法进行删除操作。

6.DELETE 删除

delete语句用于删除表中的记录,删除的是标记是可以恢复的。所以不会释放空间。可以定期使用

基本语法

delete from table_name where some_column=some_value;

注释事项(关乎存储空间)

delete删除数据后,mysql不会回收被已经删除数据占据的存储空间,以及索引,等待新的数据来补充这个空缺。如果一直空着会导致浪费,所以可以定期使用。

OPTIMIZE TABLE tableName;来清理。

其余删除操作

drop test;
删除test,并释放空间,将test删除的一干二净。

truncate test;

删除表test里的内容,并释放空间,但不删除表的定义,表的结构还在。如果插入新数据继续使用空间。

delete 删除整个表

    仅删除表test内的所有内容,保留表的定义,不释放空间。
    DELETE FROM test 或者 DELETE FROM test;
    DELETE * FROM test 或者 DELETE * FROM test;

7.分页操作

1.MYSQL

基本语法

1.取前number条。

select column_name(s) from table_name limit number; 


2.第一个参数是偏移量,第二个是要取的条数。

select column_name(s) from table_name LIMIT [offset,] rows; // 

select * from table_name limt 5,10; // 检索记录行 6-15

2.Oracle

基本语法

select * from table_name where rownum<=number;

3.SQL Server /MS Access

基本语法

select top number | percent * from table_name;

8.LIKE 模糊查询

like 操作符用于再where字句中搜索列中的指定模式。   

基本语法

select column_name(s) from table_name where column_name like pattern;

like 通配符

'%a'    //以a结尾的数据
'a%'    //以a开头的数据
'%a%'   //含有a的数据
'_a_'   //三位且中间字母是a的
'_a'    //两位且结尾字母是a的
'a_'    //两位且开头字母是a的

%                       替代0或者多个字符
-                       替代一个字符
[charlist]              字符列中的任何单一字符
[^charlist]或[!charlist]不在字符列中的任何单一字符

示例

1.使用%通配符(代替一个或者多个字符)

匹配所有https开始的所有网站
select * from table_name where url like 'https%';

匹配url中包括模式'oo'的所有网站
select * from table_name where url like '%oo%';

2.使用_通配符(代表一个字符)

匹配所有oogle的所有客户
select * from table_name where name like '_oogle';

匹配以G开头,任意字符然后是o,然后一个任意字符,然后以le的所有网站。
select * from table_name where name like 'G_o_le';

3.使用SQL[charlist]通配符、正则表达式

使用REGEXP或NOT REGEXP运算符或者RLIKE和NOT RLIKE来操作正则表达式。

匹配G F或者s开始的所有内容

select * from table_name where name REGEXP '^[GFs]';

匹配以A-H字母开头的所有内容

select * from table_name where name REGEXP '^[A-H]';


匹配不以A-H字母开头的网站

select * from table_name where name regexp '^[^A-H]';

9.IN 操作符 包含

IN操作符允许在where子句中规定多个值

基本语法

select * from table_name where column_name in (value1,value2,...);

按照in的顺序来查询内容

1
select * from table_name where column_name in (value1,value2,...) order by field(column_name,value1,value2,...);

10.BETWEEN 在..与..之间

基本语法

select * from table_name where column_name between number01 and number02;

查询时间

select * from testSql where createTime between '2018-09-21 03:48:44' and '2018-09-21 03:52:11';

查询文本

查询A-H之间的字母开始的所有网站
select * from table_name where column_name between 'A' and 'H';

not between 不在..与..之间

select * from table_name where column_name not between 1 and 2;

between 与 in结合的例子

select * from table_name where (column_name between 1 and 2) and not column_name2 in (1,2,4);

11.SQL别名

通过使用SQL,可以为表名称或列名称指定别名。

基本语法

字段别名

select column_name as alias_name from table_name;

表别名

select column_name(s) from table_name as alias_names;

示例

select w.name,w.url,a.count from table_name as w,assess_log as a where a.site_id=w.id and w.name='哈哈哈';

12.SQL连接(join)

SQL join用于把来及两个或者多个表的行结合起来。

1.INNER JOIN 内连接 (一对多、一对一、多对多 取交集)

inner join关键字再表中存在至少一个匹配时返回行

基本语法

select column_name(s) from table1 inner join table2 on table1.column_name=table2.column_name;
or
select column_name(s) from table1 join table2 on table1.column_name=table2.column_name;

inner join 与 join是相同的

示例

select uii.name,uif.name from UserInfo uii inner join UserInfoForeign  uif on uii.UserId=uif.userPrimaryId;

图例

2.LEFT JOIN 左连接查询

left join关键字从左表返回所有的行,即使右边中没有匹配。如果右表中没有匹配,则结果为null。

基本语法

select column_name(s) from table1 left join table2 on table1.column_name=table2.column_name;
or
select column_name(s) from table1 left outer join table2 on table1.column_name=table2.column_name;

示例

select ui.name,uif.name from userinfo as ui left join userinfoforeign as uif on ui.UserID=uif.userPrimaryId;

图例

1.多个left join查询

select * from (select appid,vn from vnandapp where vnandapp.appid=1659 and buylisthide=0 and enable=1) as vnandapp left join vnumber on vnandapp.vn=vnumber.vn left join app on app.AppID=vnandapp.appid;

3.RIGHT JOIN右连接

right join 关键字从右表返回所有的行,即使左表中没有匹配。如果左表中没有匹配,则结果为NULL;

基本语法

select column_name(s) from table1 right join table2 on table1.column_name=table2.column_name;
or
select column_name(s) from table1 right out join table2 on table1.column_name=table12.column_name;

示例

select ui.name,uif.name from userinfo as ui right join userinfoforeign as uif on ui.UserID=uif.userPrimaryId;

图例

4.FULL OUT JOIN

FULL OUTER JOIN 关键字只要左表(table1)和右表(table2)其中一个表中存在匹配,则返回行.

FULL OUTER JOIN 关键字结合了 LEFT JOIN 和 RIGHT JOIN 的结果。

基本语法

select column_name(s) from table1 full outer join table2 on table1.column_name=table2.colunm_name;

图例

5.UNION操作符

SQL union操作符合两个或多个select语句的结果。
union操作符用于合并两个或多个select语句的结果集。

备注

union内部的每个select语句必须拥有相同数量的列。列也必须有相似的数据类型。同时,每个select语句中的顺序必须相同。

基本语法

select column_name(s) from table1 union select column_name(s) from table2;

示例

select name from userinfo union select name from userinfoforeign;

union all 允许有重复值 允许带有where

select colunm_name(s) from table1 union all select colum_name(s) from table2;

country 
CN
CN
CN
CN
USA
USA
IND

6.子查询

1
select name from (select name from table_name where num=1) as table_name1;

13.CREATE DATABASE

创建数据库

基本语法

create database dbname;

14.CREATE TABLE

创建表

基本语法

create table tableName (column_name data_type);

1.约束 (Constraints)

SQL约束用于规定表中的数据规则,如果存在违反约束的数据行为,行为会被终止。约束可以在创建表时规定(通过create table语句),或者创建之后(通过alter table语句)

基本语法

create table table_name(
    column_name1 data_type(size) constraint_name,
    column_name2 data_type(size) constraint_name,
    column_name3 data_type(size) constraint_name
);

约束列表

NOT NULL        指示某列不能存储NULL值
UNIQUE          保证某列的每行必须有唯一的值
PRIMARY KEY     NOT NULL 和UNIQUE的结合。确保某列(或者两个多个列的结合)有唯一标识,有助于更容易更快速地找到表中的一个特定记录。
FOREIGN KEY     保证一个表中的数据匹配另一个表的值的参照完整性
CHECK           保证列中的值符合指定的条件
DEFAULT         规定没有给列赋值的默认值

1.NOT NULL

NOT NULL约束强制不为NULL值。

示例

create table table_name 
(
    P_Id int not null
);

2.UNIQUE 约束

约束唯一标识数据库表中的每条记录。
UNIQUE和PRIMARY KEY约束均为列或者列集合提供了唯一性的保证。
PRIMARY KEY约束拥有自定义的UNIQUE约束。

每个表中可以有多个UNIQUE约束但是只能有一个PRIMARY KEY约束。

示例

MYSQL
    create table table_name
    (
        P_Id int not null.
        LastName varchar(100) not null,
        unique(P_Id)
    );

SQL Server/Oracle/MS Access

    create table table_name
    (
        P_Id int not null unique
    );

如需命名unique约束,或者定义多个UNIQUE约束

MYSQL/SQL Server/Oracle/MS Access

create table table_name
(
    P_Id int not null,
    City varchar(255),
    constraint uc_PersonID unique (P_Id,City);
);

ALTER TABLE时的SQL UNIQUE约束

当表已经被创建,如需创建约束。

MYSQL/SQL Server /Oracle/MS Access

    alter table table_name add unique(p_id);

如需命名unique约束,并定义多个列的UNIQUE的约束时

    alter table table_name add constraint uc_PersonID UNIQUE(P_Id,LasterName);

撤销UNIQUE的约束

MYSQL
    alter table table_name drop index uc_PersonID

SQL Server/Oracle/Ms Access

    alter table table_name drop constraint uc_PersonID;

3.SQL PRIMARY KEY

PRIMARY KEY 约束唯一标识数据库中的每条记录。
主键必须包含唯一的值
主检列不能包含NULL值
每个表都应该有一个主键,并且每个表只能有一个主键,多个字段建立主键会建立成复合主键

示例

MYSQL

create table table_name
(
    P_Id int not null,
    LastName varchar(200) not null,
    primary key(P_Id)
);

SQL Server/Oracle/MS Access

create table table_name
(
    P_Id int not null primary key
);

如需命名PRIMARY KEY约束,并定义多个列的PRIMARY KEY约束,请使用下面的SQL语法。

MYSQL /SQL Server/Oracle/MS Access

create table table_name
(
    P_Id int not null,
    LastName varchar(255) not null,
    constraint pk_PersionID primary key(P_Id,LastName); // 复合主键
);

ALTER TABLE 时的SQL PRIMARY KEY约束

当表已经被创建,如需再P_Id列创建PRIMARY KEY约束

MYSQL/SQL Server/Oracle/MS Access

alter table table_name add primary key (P_Id);

如需命名PRIMARY KEY约束,并定义多个列的PRIMARY KEY约束

alter table table_name add constraint pk_PersionID primary key (P_Id,LastName);

撤销PRIMARY KEY

MYSQL

alter table table_name drop primary key;

SQL Server/Oracle/MS Access

alter table table_name drop constraint pk_PersionID;
alter table table_name drop primary key;

4.FOREIGN KEY

一个表中FOREIGN KEY指向另一个表中的UNIQUE KEY(唯一键约束)

创建foreign key

MySQL
create table Orders
(
    O_Id int not null,
    OrderNo int not null,
    P_Id int,
    primary key(O_Id),
    foreign key(P_Id) references Persons(P_Id)
);

SQL Server/Oracle/MS Access

create table Orders
(
    O_Id int not null primary key,
    OrderNo int not null,
    P_Id int foreign key references Person(P_Id)
);

alter table时的SQL Foreign key约束

当Orders表已创建时,如需P_Id列创建Foreign key约束

Mysql/SQL Server/Oracle/MS Access

alter table Orders add foreign key(P_Id)
references Persion(p_Id);

如需命名foreign key约束,并定义多个列的foreign key约束

Mysql/SQL Server/Oracle/MS Access

alter table Orders add constraint fk_PerOrders foreign key (P_Id) references Persion(P_Id);

撤销Foreign key约束

MySQL 

alter table table_name drop foreign key fk_PerOrders;

SQL Server/Oracle/MS Access

alter table Orders drop constraint fk_PerOrders;

5.CHECK约束约束范围

CHECK约束用于限制列中的值的范围
如果对单个列定义CHECK约束,那么该列只允许特定的值
如果对一个表定义CHECK约束,那么次约束会基于行中其他列的值在特定的列中对值进行限制。

创建CHECK约束

MySQL

create table table_name
(
    P_Id int not null,
    LastName varchar(200) not null,
    check(P_Id>0)
);

SQL Server/Oracle/MS Access

create table Persons
(
    P_Id int not null check(P_Id>10)
);

如需命名并创建多个CHECK约束

create table table_name
(
    P_Id int not null,
    City varchar(200) not null,
    constraint checkPC check(P_Id>10 AND City='beijing')
);

修改SQL CEHCK

MySQL/Sql Server/Oracle/MS Access

alter table Persion add check(P_Id>10);

如需定义多个check约束,并命名

alter table Persons add constraint checkPC check(P_Id>10 and city='beijing');

撤销CHECK约束

SQL Server/Oracle/MS Access

alter table table_name drop constraint chk_Person;

MySQl

alter table table_name drop check chk_person;

6.DEFAULT约束

default约束用于向列中插入默认值
如果没有规定其他的值,那么将默认值添加到所有的新纪录。

创建DEFAULT约束

MySql/SQL Server/Oracle/MS Access

create table table_name
(
    P_Id int not null,
    City varchar(255) default 'Sandnes';
);
// default 当前时间 CURRENT_TIMESTAMP
create table table_name
(
    P_Id int not null,
    City varchar(255) default 'Sandnes',
    createTime datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
);

DEFAULT约束也可以插入系统值

create table Orders
(
    O_Id int not null,
    OrderNo int not null,
    OrderDate date DEFAULT GETDATE()
);

修改约束

MySQL

alter table table_name alter city set default 'defaultCity';

SQL Server/MS Access

alter table table_name 
add constraint ab_c default 'defautCity' for City;

Oracle 

alter table table_name
modify City default 'defaultCity';

删除default约束

MySQL

alter table table_name alter City drop default;

SQL Server/Oracle/MS Access

alter table table_name alter column City drop default;

2.索引 INDEX

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

索引

创建索引是为了更好更高效的查询数据
用户无法看到索引,它们只能被用来加速搜索/查询
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间,这是由于索引本身也需要更新。因此比较理想的做法是在经常查询的列上以及表上创建索引。

创建索引

create index index_name on table_name(column_name);

创建唯一索引

在表上创建唯一的索引。不允许使用重复的值:唯一的索引意味着两个行不能拥有相同的索引值。
create unique index index_name on table_name(column_name);

创建复合索引(联合索引)

create index index_name on table_name(column_name01,column_name02); 

1.全文索引 FULLTEXT

全文索引是类型FULLTEXT
全文索引只能使用InnoDB或者MyISAM表格,并且只能用与创建CHAR、VARCHAR和TEXT列。
MySQL内置了全文ngram解析器,支持中文日文和韩文。

有三种类型的全文搜索

1.自然语言搜索

使用IN NATURAL LANGUAGE MODE修饰符,将搜索字符串解释为自然人类语言(自由文本中的短语)。没有特殊运算符,但双引号字除外。禁用词列表适用。

创建

create table table_name(
    id int unsigned auto_increment not null primary key,
    title varchar(20),
    body text,
    FULLTEXt(title,body)
) ENGINE=InnoDB;

使用

select * from table_name where match(title,body) AGAINST ('database', IN NATURAL LANGUAGE MODE);

默认是不区分大小写的,如果要区分请对索引列使用区分大小写或二进制排序规则。例如utf8mb4可以为使用字符集的列分配整数utf8mb4_0900_as_cs或utf8mb4_bin使其全文搜索。

如果单独搜索title,body,需要给title body创建单独的索引。

2.布尔搜索

使用特殊查询语言的规则来解释搜索字符串。该字符串包含要搜索的单词。它还可以包含指定要求的运算符,以便在匹配的行中必须存在或不存在的单词或者他应该比通常更高或者更低的权重。
可以使用IN BOOLEAN MODE修饰符执行布尔全文搜索。

使用

+和-运算符分别表示一个单词必须存在或不存在才能进行匹配。因此查询检索包含单词MySQL但,不包含YourSQL的所有行。

SELECT * FROM TABLE_NAME WHERE MATCH(TITLE,BODY) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);

备注:
    在实现次功能时,MySQL使用时被称为隐含布尔逻辑的内容,其中

    +代表了AND
    -代表了NOT
    [无操作] 代表了OR

3.查询扩展搜索

自然语言搜索的修改。搜索字符串用于执行自然语言搜索。

通过添加WITH QUERY EXPANSION或IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION跟随搜索短语来启用盲查询扩展(也称为自动相关性反馈)。它通过执行两次搜索来工作,其中第二次搜索的搜索短语是与来自第一次搜索的少数最高度相关的文档连接的原始搜索短语。因此,如果这些文档中的一个包含单词 “ databases ”和单词“ MySQL ”,则第二个搜索查找包含单词“ MySQL ”的文档, 即使它们不包含单词 “ database ”。以下示例显示了这种差异:

SELECT * FROM TABLE_NAME WHERE MATCH(TITLE,BODY) AGAINST ('database', IN NATURAL LANGUAGE MODE);

SELECT * FROM TABLE_NAME WHERE MATCH(TITLE,BODY) AGAINST ('DATABASE' WITH QUERY EXPANSION);

2.普通索引 NORMAL

3.唯一索引 UNIQUE

4.空间索引 SPATIAL

15.DROP 删除索引、删除表以及删除数据库

删除索引DROP INDEX

MS Access 

drop index index_name on table_name;

MS SQL Server 

drop index table_name.index;

MySQL 

alter table table_name drop index index_name;

删除表

drop table table_name;

删除库

drop database database_name;

truncate table

删除表内数据,不删除表本身

16.ALTER TABLE

用于在已有的表中添加、删除或者修改列

添加列

alter table table_name add column_name dataType,其他属性;
// 添加

删除列

alter table table_name drop column column_name;

更改列数据类型

MySQL/Oracle

alter table table_name modify column column_name dataType;

SQL Server/MS Assess

alter table table_name alter column column_name dataType;

Oracle 10G之后的版本

alter table table_name modify column_name datatype;

修改字段名称

alter table table_name rename column oldName to newName;

修改主键

alter table table_name add constraint 主键名称 primary key (column1,column2,...);

17.AUTO INCREMENT字段

Auto-increment会在新记录插入表中时生成一个唯一的数字,自增

基本语法

MySQL

create table table_name
(
    ID int not null auto_increment,
    primary key (ID)
);

MySQL使用auto_increment关键字来执行auto_increment任务。开始值为1,每条新记录递增1。
如果使用其他值

alter table table_name auto_increment=100;

SQL Server

create table table_name
(
    ID int IDENTITY(1,1) primary key,
);
MS SQL Server使用IDENTITY关键字来执行auto_increment任务。

Oracle

在 Oracle 中,代码稍微复杂一点。

您必须通过 sequence 对象(该对象生成数字序列)创建 auto-increment 字段。

请使用下面的 CREATE SEQUENCE 语法:

CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10

要在 "Persons" 表中插入新记录,我们必须使用 nextval 函数(该函数从 seq_person 序列中取回下一个值):

INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"ID" 列会被赋值为来自 seq_person 序列的下一个数字。"FirstName"列 会被设置为 "Lars","LastName" 列会被设置为 "Monsen"。

18.视图 Views

在SQL中,视图是基于SQL语句的结果集的可视化的表、
视图包含行和列,就像一个真实的表。视图中的字段就是来自一个或者多个数据库中的真实的表中的字段。
您可以向视图添加SQL函数、where以及join语句,也可以呈现数据,就像这些数据来自于某个单个的表一样。

创建视图

create view view_name as select column_name(s) from table_name where condition;

示例

create view testSqlView as select * from testSql;
select * from testSqlView where name='哈哈哈';      // 查询所有数据

更新视图

create or replace view view_name as select column_name(s) from table_name where condition;

示例

create or replace view testSqlView as select createTime from testSql02;  // 会更新掉视图里面的内容并且替换

撤销视图

drop view view_name;

创建多个视图

示例
create view stu_class(id,name,class) as select student.s_id,studuent.name,stu_info.class from student,stu_info where student.s_id = stu_info.s_id;

19.日期

20.NULL 函数

判断是否为null并参与运算
如果UnitsOnOrder是null,则不会影响计算,因为如果是null则isnull返回0;

MySQL

select ProductName,UnitPrice*(UnitsInstock+ifnull(UnitsOnOrder,0) from Products;
或者
select ProductName,UnitPrice*(UnitsStock+coalesce(UnitsOnOrder,0))

SQL Server/MS Access

select productName,UnitPrice*(UnitsInstock+NVL(UnitsOnOrder,0)) from Products;

Oracle

SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0)) FROM Products

21.数据类型

1.通用数据类型

数据类型 描述
character(n) 字符、字符串。固定长度
varchar(n)或character varying(n) 字符、字符串。可变长度。最大长度n
binary(n) 二进制串。固定长度n。
boolean 存储true、false
varbinary(n)或binary varying(n) 二进制。可变长度。最大长度n。
interger(p) 整数值(没有小数点)。精度p。
smallint 整数值(没有小数点)。精度5。
integer 整数值(没有小数点)。精度10。
bigint 整数值(没有小数点)。精度19。
decimal(p,s) 精确数值,精度p,小数点后位数s。(与decimal相同)
numeric(p,s) 精确数值,精度p,小数点后位数s。(与decimail相同)
float(m,d) 近似数值,尾数精度d,一个采用10为基数的指数计数法的浮点数,这个数整数位为m-d
real 近似数值,尾数精度7。
float 近似数值,尾数精度16。
double precision 近似数值,尾数精度16。
date 存储年月日的值
time 存储小时、月、日的值
timestamp 存储年月日小时分钟秒的值
interval 由一些整数字段组成,代表一段时间,取决于区间的类型。
array 元素的固定长度的有序集合。
multiset 元素的可变长度的无序集合
xml 存储xml数据

2.SQL数据类型快速参考手册

数据类型 Access SQLServer Oracle MySQL Pg
boolean Yes/No Bit Byte N/A Boolean
integer Number(integer) Int Number Int Integer Int Integer
float Number(single) FLoat Real Number Float Numeric
currency Currency Money N/A N/A Money
string(fixed) N/A Char Char Char Char
string(variable) Text(<256) Memo(65K+) Varchar Varchar Varchar2 Varchar Varchar
binary object OLE Object Memo Binary(fixed up to 8k) Varbinary(<8k) Image(<2GB) Long Raw Blob Text Binary Varbinary

22.各种DB的数据类型

1.MS Access

数据类型 描述 存储
Text 用于文本或文本与数字的组合。最多 255 个字符。
Memo Memo 用于更大数量的文本。最多存储 65,536 个字符。注释:无法对 memo 字段进行排序。不过它们是可搜索的。
Byte 允许 0 到 255 的数字。 1 字节
Integer 允许介于 -32,768 与 32,767 之间的全部数字。 2 字节
Long 允许介于 -2,147,483,648 与 2,147,483,647 之间的全部数字。 4 字节
Single 单精度浮点。处理大多数小数。 4 字节
Double 双精度浮点。处理大多数小数。 8 字节
Currency 用于货币。支持 15 位的元,外加 4 位小数。提示:您可以选择使用哪个国家的货币。 8 字节
AutoNumber AutoNumber 字段自动为每条记录分配数字,通常从 1 开始。 4 字节
Date/Time 用于日期和时间 8 字节
Yes/No 逻辑字段,可以显示为 Yes/No、True/False 或 On/Off。在代码中,使用常量 True 和 False (等价于 1 和 0)。注释:Yes/No 字段中不允许 Null 值 1 比特
Ole Object 可以存储图片、音频、视频或其他 BLOBs(Binary Large OBjects)。 最多 1GB
Hyperlink 包含指向其他文件的链接,包括网页。
Lookup Wizard 允许您创建一个可从下拉列表中进行选择的选项列表。 4 字节

2.MySQL

TEXT类型

数据类型 描述
CHAR(size) 保存固定长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的长度。最多 255 个字符。
VARCHAR(size) 保存可变长度的字符串(可包含字母、数字以及特殊字符)。在括号中指定字符串的最大长度。最多 255 个字符。注释:如果值的长度大于 255,则被转换为 TEXT 类型。
TINYTEXT 存放最大长度为 255 个字符的字符串。
TEXT 存放最大长度为 65,535 个字符的字符串。
BLOB 用于 BLOBs(Binary Large OBjects)。存放最多 65,535 字节的数据。
MEDIUMTEXT 存放最大长度为 16,777,215 个字符的字符串。
MEDIUMBLOB 用于 BLOBs(Binary Large OBjects)。存放最多 16,777,215 字节的数据。
LONGTEXT 存放最大长度为 4,294,967,295 个字符的字符串。
LONGBLOB 用于 BLOBs (Binary Large OBjects)。存放最多 4,294,967,295 字节的数据。
ENUM(x,y,z,etc.) 允许您输入可能值的列表。可以在 ENUM 列表中列出最大 65535 个值。如果列表中不存在插入的值,则插入空值。注释:这些值是按照您输入的顺序排序的。可以按照此格式输入可能的值: ENUM(‘X’,’Y’,’Z’)
SET 与 ENUM 类似,不同的是,SET 最多只能包含 64 个列表项且 SET 可存储一个以上的选择。

Number类型

数据类型 描述
TINYINT(size) 带符号-128到127 ,无符号0到255。
SMALLINT(size) 带符号范围-32768到32767,无符号0到65535, size 默认为 6。
MEDIUMINT(size) 带符号范围-8388608到8388607,无符号的范围是0到16777215。 size 默认为9
INT(size) 带符号范围-2147483648到2147483647,无符号的范围是0到4294967295。 size 默认为 11
BIGINT(size) 带符号的范围是-9223372036854775808到9223372036854775807,无符号的范围是0到18446744073709551615。size 默认为 20
FLOAT(size,d) 带有浮动小数点的小数字。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
DOUBLE(size,d) 带有浮动小数点的大数字。在 size 参数中规显示定最大位数。在 d 参数中规定小数点右侧的最大位数。
DECIMAL(size,d) 作为字符串存储的 DOUBLE 类型,允许固定的小数点。在 size 参数中规定显示最大位数。在 d 参数中规定小数点右侧的最大位数。
以上的 size 代表的并不是存储在数据库中的具体的长度,如 int(4) 并不是只能存储4个长度的数字。
实际上int(size)所占多少存储空间并无任何关系。
int(3)、int(4)、int(8) 在磁盘上都是占用 4 btyes 的存储空间。
就是在显示给用户的方式有点不同外,int(M) 跟 int 数据类型是相同的。

例如:
int的值为10 (指定zerofill)
int(9)显示结果为000000010int(3)显示结果为010
就是显示的长度不一样而已 都是占用四个字节的空间

Date

数据类型 描述
DATE() 日期。格式:YYYY-MM-DD注释:支持的范围是从 ‘1000-01-01’ 到 ‘9999-12-31’
DATETIME() *日期和时间的组合。格式:YYYY-MM-DD HH:MM:SS注释:支持的范围是从 ‘1000-01-01 00:00:00’ 到 ‘9999-12-31 23:59:59’
TIMESTAMP() *时间戳。TIMESTAMP 值使用 Unix 纪元(‘1970-01-01 00:00:00’ UTC) 至今的秒数来存储。格式:YYYY-MM-DD HH:MM:SS注释:支持的范围是从 ‘1970-01-01 00:00:01’ UTC 到 ‘2038-01-09 03:14:07’ UTC
TIME() 时间。格式:HH:MM:SS注释:支持的范围是从 ‘-838:59:59’ 到 ‘838:59:59’
YEAR() 2 位或 4 位格式的年。注释:4 位格式所允许的值:1901 到 2155。2 位格式所允许的值:70 到 69,表示从 1970 到 2069。
*即便 DATETIME 和 TIMESTAMP 返回相同的格式,它们的工作方式很不同。在 INSERT 或 UPDATE 查询中,TIMESTAMP 自动把自身设置为当前的日期和时间。TIMESTAMP 也接受不同的格式,比如 YYYYMMDDHHMMSS、YYMMDDHHMMSS、YYYYMMDD 或 YYMMDD。

3.SQL Server

String类型

数据类型 描述 存储
char(n) 固定长度的字符串。最多 8,000 个字符。 Defined width
varchar(n) 可变长度的字符串。最多 8,000 个字符。 2 bytes + number of chars
varchar(max) 可变长度的字符串。最多 1,073,741,824 个字符。 2 bytes + number of chars
text 可变长度的字符串。最多 2GB 文本数据。 4 bytes + number of chars
nchar 固定长度的 Unicode 字符串。最多 4,000 个字符。 Defined width x 2
nvarchar 可变长度的 Unicode 字符串。最多 4,000 个字符。
nvarchar(max) 可变长度的 Unicode 字符串。最多 536,870,912 个字符。
ntext 可变长度的 Unicode 字符串。最多 2GB 文本数据。
bit 允许 0、1 或 NULL
binary(n) 固定长度的二进制字符串。最多 8,000 字节。
varbinary 可变长度的二进制字符串。最多 8,000 字节。
varbinary(max) 可变长度的二进制字符串。最多 2GB。
image 可变长度的二进制字符串。最多 2GB。

Number类型

数据类型 描述 存储
tinyint 允许从 0 到 255 的所有数字。 1 字节
smallint 允许介于 -32,768 与 32,767 的所有数字。 2 字节
int 允许介于 -2,147,483,648 与 2,147,483,647 的所有数字。 4 字节
bigint 允许介于 -9,223,372,036,854,775,808 与 9,223,372,036,854,775,807 之间的所有数字。 8 字节
decimal(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
numeric(p,s) 固定精度和比例的数字。允许从 -10^38 +1 到 10^38 -1 之间的数字。p 参数指示可以存储的最大位数(小数点左侧和右侧)。p 必须是 1 到 38 之间的值。默认是 18。s 参数指示小数点右侧存储的最大位数。s 必须是 0 到 p 之间的值。默认是 0。 5-17 字节
smallmoney 介于 -214,748.3648 与 214,748.3647 之间的货币数据。 4 字节
money 介于 -922,337,203,685,477.5808 与 922,337,203,685,477.5807 之间的货币数据。 8 字节
float(n) 从 -1.79E + 308 到 1.79E + 308 的浮动精度数字数据。n 参数指示该字段保存 4 字节还是 8 字节。float(24) 保存 4 字节,而 float(53) 保存 8 字节。n 的默认值是 53。 4 或 8 字节
real 从 -3.40E + 38 到 3.40E + 38 的浮动精度数字数据。 4 字节

Date 类型

数据类型 描述 存储
datetime 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 3.33 毫秒。 8 字节
datetime2 从 1753 年 1 月 1 日 到 9999 年 12 月 31 日,精度为 100 纳秒。 6-8 字节
smalldatetime 从 1900 年 1 月 1 日 到 2079 年 6 月 6 日,精度为 1 分钟。 4 字节
date 仅存储日期。从 0001 年 1 月 1 日 到 9999 年 12 月 31 日。 3 bytes
time 仅存储时间。精度为 100 纳秒。 3-5 字节
datetimeoffset 与 datetime2 相同,外加时区偏移。 8-10 字节
timestamp 存储唯一的数字,每当创建或修改某行时,该数字会更新。timestamp 值基于内部时钟,不对应真实时间。每个表只能有一个 timestamp 变量。

其他数据类型

数据类型 描述
sql_variant 存储最多 8,000 字节不同数据类型的数据,除了 text、ntext 以及 timestamp。
uniqueidentifier 存储全局唯一标识符 (GUID)。
xml 存储 XML 格式化数据。最多 2GB。
cursor 存储对用于数据库操作的指针的引用。
table 存储结果集,供稍后处理。

23.函数

1.Aggregate函数

Aggregate 函数计算从列中取得的值,返回一个单一的值。
函数名 描述
AVG() 返回平均值
count() 返回行数
first() 返回第一个记录的值
last() 返回最后一个值
max() 返回最大值
min() 返回最小值
sum() 返回总和

1.AVG函数

返回数值列的平均值

基本语法

select avg(column_name) from table_name;

示例

select avg(uid) from table_name;

2.count()函数

返回匹配指定条件的行数

基本语法

select count(colunm_name) from table_name;

返回指定列不同值的数目

select count(distinct column_name) from table_name;

count(distinct)适用于Oracle、SQL Server和MySQL但是无法适用Access

3.first() 函数(只有MS Access支持)

first()函数返回指定的列中第一个记录的值

基本语法

select first(column_name) from table_name;

1.SQL Server语法

select top 1 column_name from table_name order by column_name asc;

2.MySQL语法

select column_name(s) from table_name order by column_name desc limt 1;

3.Oracle语法

select column_name from table_name order by column_name asc where rownum<=1;

4.last() 函数只有MS Assess支持

last() 函数返回指定的列中最后一个记录的值

基本语法
select last(column_name) from table_name;

1.MySQL语法

select column_name(s) from table_name order by column_name desc limt 1;

2.SQL Server

select top 1 column_name from table_name order by column_name desc;

3.Oracle语法

select column_name(s) from table_name ordre by column_name desc where rownum<=1;

5.MAX() 函数

返回指定列的最大值

基本语法
select max(column_name) from table_name;

6.MIN()函数

返回指定列的最小值

基本语法

select min(column_name) from table_name;

如果使用min() 函数作为条件需要和group by一块使用

1
select min(column_name1) from table_name group by column_name1;

7.sum()函数

select sum(column_name) from table_name;

2.Scalar函数

Scalar函数基于输入值,返回一个单一的值
函数名 描述
ucase() 将某个字段转换成大写
lcase() 将某个字段转换成小写
mid() 从某个文本字段提取字符,MySQL中使用
Substring(字段,1,end) 将某个文本字段提取字符
len() 返回某个文本字段的长度
round() 对某个数值字段进行小数位数的四舍五入
now() 返回当前的系统日期和时间
format() 格式化某个字段的显示方式

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;

2.MID()函数

用于从文本中提取字符

基本语法
select mid(column_name,start[,length]) from table_name;

3.LEN()函数、MySQL为length()

函数返回文本字段中值的长度

基本语法

select len(column_name) from table_name;

MySQL 语法

select length(column_name) from table_name;

4.round() 函数

round()函数用于把数值字段舍入为指定的小数位数。

基本语法

select round(column_name,decimals) from table_name;
参数 描述
column_name 必须。要舍入的字段
decimails 必需。规定要返回的小数位数。

实例

select round() from table_name; // 一个参数时四舍五入

// round返回值被变换为一个bigint

4.NOW()函数

返回当前系统的日期和时间

基本语法

select now() from table_name;

5.FORMAT()函数

对字段进行的显示进行格式化

基本语法

select format(column_name.format) from table_name;
参数 描述
column_name 必须。要格式化的字段
format 必须。规定格式化

示例

select DATE_FORMAT(now(),'%Y-%m-%d') from testSql;

3.GROUP BY() 分组

可以结合一些聚合函数来使用

基本语法

select column_name,aggregate_function(column_name) from table_name where column_name operator value group by column_name;

示例

select site_id,sum(access_log.count) as nums from access_log group by site_id;
aid site_id count date
1 1 45 2016-05-10
2 3 100
3 1 230
4 2 10
5 5 205
6 4 13
7 3 220
8 5 545
9 3 201

结果

site_id nums
1 275
2 10
3 521
4 13
5 750

group by多表连接

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log LEFT JOIN Websites ON access_log.site_id=Websites.id GROUP BY Websites.name;
执行以上 SQL 输出结果如下:
name nums
FaceBook 2
Google 2
微博 1
淘宝 1
菜鸟教程 3

group by 多个字段同时group

SELECT Websites.name,COUNT(access_log.aid) AS nums FROM access_log LEFT JOIN Websites ON access_log.site_id=Websites.id GROUP BY Websites.name,Websites.name1;

先对name进行分组,然后在对name1进行分组

4.HAVING子句(无法与聚合函数一块使用所以使用having)

在SQL中增加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;

示例

DB Websites
id name url alexa country
1 Google https://www.google.cm/ 1 USA
2 淘宝 https://www.taobao.com/ 13 CN
3 菜鸟教程 http://www.runoob.com/ 4689 CN
4 微博 http://weibo.com/ 20 CN
5 Facebook https://www.facebook.com/ 3 USA
7 stackoverflow http://stackoverflow.com/ 0 IND
DB access_log
aid site_id count date
1 1 45 2016-05-10
2 3 100 2016-05-13
3 1 230 2016-05-14
4 2 10 2016-05-14
5 5 205 2016-05-14
6 4 13 2016-05-15
7 3 220 2016-05-15
8 5 545 2016-05-16
9 3 201 2016-05-17
SQL

SELECT Websites.name, Websites.url, SUM(access_log.count) AS nums FROM (access_log INNER JOIN Websites ON access_log.site_id=Websites.id) GROUP BY Websites.name HAVING SUM(access_log.count) > 200;

结果

name url nums
FaceBook http://www.facebook.com/ 750
Google http://www.google.com 275
菜鸟教程 http://www.runoob.com 521

24.当记录不存在的插入,当记录存在时更新

插入多条记录

insert into clients(client_id,client_name,client_type) select supplier_id,supplier_name,'advertising' from suppliers where not exists (select * from clients where client.client_id=suppliers.supplier_id);

插入单条记录

insert into clients(client_id,client_name,client_type) select 10345,'IBM','advertising' from dual where not exists(select * from clients where clients.client_id=10345);

使用ON DUPLICATE KEY UPDATE

如果您指定了ON DUPLICATE KEY UPDATE,并且插入行后会导致一个UNIQUE索引或PRIMARY  KEY中出现重复值,则执行旧行UPDATE。例如,如果列a被定义为UNIQUE,并且包含值1,则以下两个语句具有相同的效果。

insert into table_name(a,b,c) values(1,2,3) on duplicate key update c=c+1;

||

update table_name set c=c+1 where a=1;

REPLACE语句

我们在使用数据库时可能会经常遇到这种情况。如果一个表在一个字段上建立了唯一索引,当我们再向这个表中使用已经存在的键值插入一条记录,那将会抛出一个主键冲突的错误。当然,我们可能想用新记录的值来覆盖原来的记录值。如果使用传统的做法,必须先使用DELETE语句删除原先的记录,然后再使用INSERT插入新的记录。而在MySQL中为我们提供了一种新的解决方案,这就是REPLACE语句。使用REPLACE插入一条记录时,如果不重复,REPLACE就和INSERT的功能一样,如果有重复记录,REPLACE就使用新记录的值来替换原来的记录值。 

使用REPLACE的最大好处就是可以将DELETE和INSERT合二为一,形成一个原子操作。这样就可以不必考虑在同时使用DELETE和INSERT时添加事务等复杂操作了。 

REPLACE的语法和INSERT非常的相似,如下面的REPLACE语句是插入活更新一条记录。

REPLACE INTO USERS(id,name,age) VALUES(123,'赵本山',50);

当使用复合唯一索引是也可以使用这种方式

REPLACE INTO TABLE_NAME SET ID=123,NAME='abc';

25.查看表结构 describe

describe 表名;

26.case when

select  sum(case u.sex when 1 then 1 else 0 end)男性,
        sum(case u.sex when 2 then 1 else 0 end)女性,
        sum(case when u.sex <>1 and u.sex<>2 then 1 else 0 end)性别为空
from users u;

    男性         女性       性别为空
---------- ---------- ----------
     3          2          0

--------------------------------------------------------------------------------

select  count(case when u.sex=1 then 1 end) as 男性,
        count(case when u.sex=2 then 1 end)女,
        count(case when u.sex <>1 and u.sex<>2 then 1 end)性别为空
from users u;

    男性          女       性别为空
---------- ---------- ----------
     3          2          0

备注:
    当判断NULL时 使
    case when column_name is NULL then 'uncommited' else 'commited' end

27.mysql查询数据库时区

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);// 如果返回08:00:00是中国时区

28.错误信息

What is the error “Every derived table must have its own alias” in MySQL?

SELECT ID FROM (
    SELECT ID, msisdn FROM (
        SELECT * FROM TT2
    ) AS T
) AS T; // 需要加别名

29.多字段组队查询

举例:

a,b两个字段 [{a:1,b:1},{a:2,b:2}] 查询成对出现

SQL实现

where (column1, column2) in ((1,2),(3,4),(5,4),(2,4))

30.统计只出现一次的数据

1.通过去重、分组查出数量
select count(*) as countValue,userName,agentCode,AppID,videoSource,clientType,directory,progress,createTime from yt_ksbao_video_userclick group by userName

2.通过子句过滤数据

select * from (select count(*) as countValue,userName,agentCode,AppID,videoSource,clientType,directory,progress,createTime from yt_ksbao_video_userclick group by userName) as values1 where values1.countValue=1;

31.查询不同的库

表名前面加上数据库名称即可
select * from DBNAME.TABLE_NAME;

32.CURRENT_TIMESTAMP

create table test(
    id int,
    expire_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

当设置默认值为CURRENT_TIMESTAMP时创建时的时间就为当前的时间
当用了 ON UPDATE CURRENT_TIMESTAMP时每次修改时更新为当前的时间

33.优化表结构

  • 1.如果出现唯一索引时 使用联合主键 并把ID设置成自增加查询索引
  • 2.id使用自增无符号
  • 3.如果字段中出现type这样的数字 可以设置tinyint 无符号

34.随机数

语法

  • 公式

    1
    2
    // 生成 i<=R<=j
    i + RAND() * (j – i + 1)
  • 取值范围

    1
    2
    [0,1]
    select RAND();// 会生成一个0<=num<=1的数字
  • 生成一个10-20的随机数

    1
    select (10+RAND()*(20-10+1))// 会生成一个带有小数的随机数 1x.xxx 
  • 使用floor取int

    1
    2
    // 去掉小数,只要整数
    select floor(10+RAND()*(20-10+1))
  • 使用round取int

    1
    2
    // 四舍五入
    select ROUND((10+RAND()*(20-10+1)))

    35.优化当limit较大时的查询较慢的问题

  • [ ]具体原因

    1
    select * from test a inner join (select id from test where val=4 limit 300000,5) b on a.id=b.id;

    36.按数据分组去分组后的前两条数据

    1
    2
    3
    4
    5
    6
    7
    8
    # 内部数据进行分组
    # 然后对分组后的数据取最大的两条

    select * from (select DATE_FORMAT(createTime,'%Y-%m-%d') as time,count(appEName) as ca,appEName from yt_ksbao_userclick as b where createTime BETWEEN '2020-9-1' and '2020-9-15' and btnName="查找试题" GROUP BY time,appEName) as a where
    (
    select count(1) from (select DATE_FORMAT(createTime,'%Y-%m-%d') as time,count(appEName) as ca,appEName from yt_ksbao_userclick as b where createTime BETWEEN '2020-9-1' and '2020-9-15' and btnName="查找试题" GROUP BY time,appEName) as b where b.time=a.time and b.ca >a.ca
    )<=2
    ORDER BY time,appEName

37.查询指定索引

1
2
select * from table_name force index (index_name) where conditions;
# index_name 则是索引名称

如有侵权行为,请点击这里联系我删除

如发现疑问或者错误点击反馈