一、数据库类型
关系性数据库:以行和列的形式展示,行与列的形式组织起来的数据集合。
常见的关系性数据库:Access、MySQL、SQL Server、Oracle、Sybase、DB2
非关系性数据库:以kay:value模型,组要解决海量数据下的数据库性能和扩展能力。
常见的非关系性数据库:HBase、MongoDB、Redis、Memcached。
二、MySQL
(1)MySQL常见数据类型
int:整型,可以-2的31次方到2的31次方减1之间的整数
float:近似数值型,供浮点数使用
char:字符型,指定列长,搜索速度快
varcher:字符型,指定最大长度,减小内存使用
(2)约束
PRIMARY KEY:主键约束
FOREIGN KEY:外键约束
UNIQUE:唯一约束
CHECK:检查约束(MySQL中使用无效)
DEFAULT:默认约束
NULL:空值约束
(3)数据定义语言(DDL)
create table 表名() #创建表
create index 索引名 #创建索引
alter table 原表名 rename to 目标表名 #修改表名
alter table 表名 add 字段名 字符类型 约束 #新增字段
alter table 表名 change 原字段名 新字段名 字符类型 约束 #修改字段属性
alter table 表名 modify(列名 数据类型) #修改字段类型,MySQL不支持该语法
alter table 表名 drop column 字段名 #删除列
drop table 表名 #删除表
drop index 索引名 #删除索引
(4)数据操纵语句(DML)
insert into 表名(列名,列名……) values('',''……) #插入数值
insert into 表名(列名,列名……)select 语句 #将查询的数据对应插入表中
update 表名 set 列名=‘更新值’ where 条件 #更新数值,多个字段使用逗号隔开
delete from 表名 where 条件 #删除数值
(5)数据查询语句
select 搜索列表,多个用逗号隔开
from 表名,多个用逗号隔开
where 搜索条件
group by 分组表达式
having 子搜索条件,当聚合函数为条件时使用,需要和group by一起使用
order by 排序,asc|desc 升序|降序
limit m,n 分页表达式,从m+1行开始取n行(MySQL8.0版本,使用limit n offset m进行,oecale可以使用rownum字典进行分页)
disrinct 字段名 # 去重,删除数据重复的行
模糊匹配:
[not] like 模糊条件匹配
%百分号:匹配任意长度字符
_下划线:匹配任意单个字符
UNION和UNION ALL
UNION在进行表链接后会筛选掉重复的记录,并对所产生的结果集进行排序运算,删除重复的记录再返回结果。而UNION ALL则不会去除重复记录,只是简单地将两个结果集合并起来,不进行排序操作。
嵌套查询(一个select语句的结果作为条件来使用)
联接查询(对多个表进行连接操作查询包括内外联结join)
92SQL语法
SELECT 列名 FROM 表1,表2 WHERE 表1.列名=表2.列名 #内连接
SELECT 列名 FROM 表1,表2 WHERE 表1.列名=表2.列名(+) #外连接,“+”号在左边表示为左连接,同理在右边就是右连接,两边同时存在就为满外连接(注意:MySQL不支持该语法)
99SQL语法
SELECT 列名 FROM 表1 INNER JOIN 表2 ON 表1.列名1=表2.列名1 JOIN 表3 ON 表1.列名2=表3.列名2 #内连接,INNER可以省略,蓝色部分为三个及以上表格相连
SELECT 列名 FROM 表1 LEFT OUTER JOIN 表2 ON 表1.列名1=表2.列名1 #左连接,OUTER可以省略
SELECT 列名 FROM 表1 RIGHT OUTER JOIN 表2 ON 表1.列名1=表2.列名1 #右连接,OUTER可以省略
SELECT 列名 FROM 表1 FULL OUTER JOIN 表2 ON 表1.列名1=表2.列名1 #满外连接,OUTER可以省略(注意:MySQL不支持该语法,MySQL可以使用UNION或UNION ALL对两个语句连接实现满外连接)
注意:上述的ON可以使用USING替代,不过需要两张表的列名相同
SELECT 列名 FROM 表1 INNER JOIN 表2 USING (列名)
使用较少
SELECT 列名 FROM 表1 NATURAL JOIN 表2 #自然连接,两个表中所有相同字段相等
例如:现在有两个表分别是员工表和部门表,员工表emplotees有字段员工id(emplote_id)、姓名(last_name)、部门id(department_id)、领导id(manager_id),部门表departments有字段部门id(department_id)、领导id(manager_id)和领导名称(department_name),现在需要查看员工id、姓名和领导姓名
方法一:
SELECT emplote_id,last_name, department_name
FROM emplotees as e INNER JOIN departments as d
ON e.manager_id=d.manager_id AND e.department_id = d.department_id
方法二:
SELECT emplote_id,last_name, department_name
FROM emplotees NATURAL JOIN departments
上述两个方法结果相同
(6)数据控制语句(DCL)
grant 权限1,权限2 on 表名 to 用户名 #授权用户权限,增删改查(insert、delete、update、select)
revoke 权限1,权限2 on 表名 from 用户名 #回收用户权限,增删改查(insert、delete、update、select)
(7)常见聚合函数
avg 平均值
sum 求和
min 最小值
max 最大值
count 计数统计
(8) 常见函数
now()、year()、month()、day() 日期时间函数
isnull() 判断是否为null
left(character_expression,integer_expression) 从左边开始取指定个数的字符串,character_expression 字符或二进制数据表达式,integer_expression 正整数,为负数时,返回值为空字符串
right(character_expression,integer_expression) 同上,从右边开始取字符串
substring(string_expression,inter_expression1,inter_expression1) 同上从指定位置开始截取字符串,string_expression 字符串,inter_expression1要截取的起始位,inter_expression1截取的个数
length(string_expression) 计算字符串的字符个数,string_expression 字符串
replace(string_expression1,string_expression2,string_expression3) 替换字符,string_expression1 目标字符串,string_expression2需要替换的字符,string_expression3新字符
cast(expression as data_type) 转换数据类型,expression 任何有效的SQL表达式,data_type数据类型
datediff(date1,date2) 两个日期间的天数
union 合并两个或多个select语句的结果集,select语句必须列数量、顺序相同,数据类型相似,union选取不同的值,允许重复的值用union all.
ABS(x) 返回X的绝对值
SIGN(X) X为正数时,返回1;X为负数时,返回-1;X为0时,返回0
PI() 返回Π的值3.141593
CEIL(X),CEILING(X) 返回大于或等于X的最小整数
FLOOR(X) 返回小于或等于X的最小整数
MOD(X,Y) 返回X%Y,即X除以Y的余数
RAND() 返回0~1之间的随机数,括号中写入数字,那么值就固定不变
ROUND(X,NUM=0) 返回X四舍五入的值,NUM是保留小数位数
TRUNC(X,NUM) 返回X向下保留的数,NUM是保留小数位数
SQRT(X) 返回X的平方根,X为负数时,返回NULL
(9)逻辑函数
IF(value,value1,value2) #如果value为True,则返回value1,否则返回value2
IFNULL(value1,value2) #如果value1不为null,则返回value1,否则返回value2
CAST WHEN 条件1 THEN 结果1 WHEN 条件2 THEN 结果2 …ELSE 最后结果 END #如果满足条件1,则输出结果1,满足条件2,输出结果2 …否则输出最后结果,ELSE可以不写即最后结果没有
CAST 条件 WHEN 要求1 THEN 结果1 WHEN 要求2 THEN 结果2 …ELSE 最后结果 END #条件满足要求1,则执行结果1,满足条件2,则执行结果2…否则执行最后结果
例如:SELECT student_name,CAST age WHEN 6 THEN "6岁" WHEN 8 THEN "8岁" ELSE "不知道年龄" END
(9)存储过程
create procedure 存储名称
(in/out/inout 参数名 参数类型)
begin
SQL语句块
end #创建存储过程
call 存储名() #执行存储过程
(10)数据库备份
#备份整个数据库
mysqldump -u root -h host -p dbname > backdb.sql
#备份数据库中的某个表
mysqldump -u root -h host -p dbname tbname1 > backdb.sql
#备份多个数据库
mysqldump -u root -h host -p --databases dbname1, dbname2 > backdb.sql
#备份系统中所有数据库
mysqldump -u root -h host -p --all-databases > backdb.sql
三、MongoDB
MongoDB是由C++语言编写的,是一个基于分布式文件存储的开源数据库系统。
1、下载安装
下载地址:https://www.mongodb.com/download-center/enterprise
根据系统下载对应的.msi文件,下载成功后,双击该文件,根据操作提示进行安装即可。
安装过程中,可以通过点击“Custom”(自定义)按钮来设置安装目录。
目录选择后,下一步安装“install mongoDB compass”不勾选(勾选后,会下载一个图形界面管理工具(MongoDB Compass),可能需要更久的安装时间),该工具可以在官网下载:https://www.mongodb.com/download-center/compass
2、常用语句
(1)use database_name 数据库不存在,创建数据库,否则切换到数据库
(2)show dbs 查看所有的数据库,数据库没有数据不会显示
(3)db.dropDatabase() 删除当前的数据库
(4)db.createCollection(name,options) 创建集合
参数说明:name 要创建的集合名称
options:可选参数,指定有关内存大小及索引的选项
options可选参数如下
capped 布尔值,为true时,创建固定集合,即有着固定大小的集合,达到最大值时,会自动覆盖最早的文档,当为true时,必须指定size参数,默认为false。
autoIndexId 布尔值,为true时,自动在_id字段创建索引,默认为false。
size 数值,固定集合指定一个最大值,以千字节计(KB)。
max 数值,固定集合中包含文档的最大数量。
在插入文档时,MongoDB首先检查固定集合的size字段,再检查max字段。
db.creatCollection("test") #创建了一个非固定集合
db.creatCollection("test",{capped:true,autoIndexId:true,size:6142800,max:10000}) #创建了一个固定集合,空间大小为6142800KB,最大个数为10000,且_id字段会自动添加索引
(4)db.集合名.drop() 删除集合
(5)db.集合名.insert() 插入文档
(6)db.集合名.update(条件,{$set:需要更新字段}[,{multi:true}]) 更新文档
设置multi为1或true,修改所有符合条件项,不设置该参数或者为false,则修改第一条符合条件的文档
(7)db.集合名.find().pretty() 查询文档
pretty()方法以格式化发方式来显示所有文档
(8)db.集合名.remove(条件[,{justOne:1}]) 删除文档
设置justOne为1或true,仅删除第一符合条件的文档,不设置该参数或者为false,则删除所有符合条件项
(9)比较语句
操作 | 格式 | 示例 |
等于 | {kay:value} | db.test.find({name:"小王"}).pretty |
小于 | {kay:{$lt:value}} | db.test.find({age:{$lt:18}}).pretty |
小于等于 | {kay:{$lte:value}} | db.test.find({age:{$lte:18}}).pretty |
大于 | {kay:{$gt:value}} | db.test.find({sge:{$gt:18}}).pretty |
大于等于 | {kay:{$gte:value}} | db.test.find({age:{$gte:18}}).pretty |
不等于 | {kay:{$ne:value}} | db.test.find({age:{$ne:20}}).pretty |
(10)条件语句
AND 条件
db.集合名.find{kay1:value1,kay2:value2}.pretty()
OR 条件
db.集合名.find(
{
$or:[
{kay1:value1},{kay2:value2}
]
}).pretty()