本文是2016年的学习笔记,最近工作用到了SQL,顺手调整了下格式分享出来。
SQL简介
结构化查询语言SQL(structured query language),是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系型数据库,遵循ANSI SQL标准。结构化查询语言sql是高级的非过程化的编程语言,允许用户在高层数据结构工作。不要求用户指定对数据的存放方法,也不要求用户了解具体的数据存放方式,所以具有完全不同底层结构的数据库系统可以使用相同的sql作为数据输入和管理的接口。
SQL语言的4个部分:
- 数据定义语言DDL:例如CREATE(创建库表),DROP(销毁库表),ALTER(改变表结构)
- 数据操作语言DML:例如INSERT(插入),UPDATE(修改),DELETE,
- 数据查询语言DQL:例如SELECT(查询)
- 数据控制语言DCL:分配GRANT,回收REVOKE,COMMIT,ROLLBACK,拒绝deny等权限相关语句
数据类型type
大多数数据库表的字段都指定了相关的数据类型,相当于Java语言编程中变量的数据类型。SQLite采用动态数据类型,可以对字段不指定任何数据类型,SQLite会根据存入值自动判断。
sqlite的5种常用数据类型
NULL
空值,相当于Java中的null;INTEGER
带符号的整型,相当于Java中的int;REAL
浮点数字,相当于Java中的float/double型;TEXT/VARCHAR
字符串,相当于Java中的string类,一个字符2个字节。text很长(比如文章),varchar比如姓名等字段,char(10)不能超过指定的长度;BLOB
二进制对象,相当于Java中的byte数组,用于存储声音、视频、图片等;
mysql的常用数据类型
- 整数:
- tinyint(1个字节,-128~127)
- smallint(2个字节)
- int(4个字节)
- bigint(8个字节)
- 小数
- 定点数:decimal(p,s),p是总位数,s是小数部分的位数,其实际占空间大小为p+2个字节;
- 浮点数:float(p,s)4个字节、double(p,s)8个字节
- 日期
- date:年月日
- time:时分秒,[-89小时59分59秒,89小时59分59秒]
- datetime:年月日时分秒
- 文本
- char:长度固定的字符串
- varchar:长度可变的字符串
- text:长文本,text字段不能作为查询的条件
- 二进制bit
约束contraint
CREATE TABLE
创建表时,应该将每个字段的约束条件进行说明,以后往表里输入数据的时候,系统会自动检查是否满足约束条件,如不满足会报错。主要有以下5种约束:
约束 | 关键词 | 示例 |
---|---|---|
条件检查 | CHECK(condition) | 年龄–至少大于20岁小于80:age int check(age >20 and age<80) |
默认 | DEFAULT (XXX) | 国籍–默认中国:nationality varchar(10) default(‘china’) |
非空 | NOT NULL | 姓名—不能为空:name varchar(10) not null |
主键 | PRIMARY KEY | 员工号—主键:id int primary key autoincrement |
外键 | FOREIGN KEY | 员工饭卡号—根据员工号得来:card_id int references workers.id |
唯一 | unique | - |
自增列 | mysql:auto_increment sqlite:autoincrement | - |
注意:
- sqlite主键的类型要用integer,不然不会自增长,
- MySQL必须显示声明auto-increment才能自增长;主键不一定是整型,可以是任意的数据类型;
基本操作
数据库操作(DDL)
创建数据库
- sqlite:
# 创建数据库,命令:sqlite3 <db_name>
sqlite3 stu.db
安卓不用管数据库的创建,sqliteOpenHelper对象new出来时就自动创建了
- mysql:
# 创建
create database <db_name>
# 删除
drop database <db_name>
# 打开数据库
use <db_name>
数据库的模式控制
- sqlite:
# 输出列头
.header on
# 列表显示
.mode column
# 以列的形式显示
.mode line
- mysql:待补充
查看当前库
.database
查看表信息
- sqlite终端
# 查看所有表
.table
# 查看当前数据库指定表
.tables <table_name>
- 使用SQL
# 查看所有表结构及索引信息
select * from sqlite_master
# 查看所有表结构信息
select * from sqlite_master where type = "table"
# 查看所有表名
select name from sqlite_master where type = "table"
# 查看指定表结构
select * from sqlite_master where type = "table" and name = "<table_name>"
# 查看所有表索引信息
select * from sqlite_master where type = "index"
# 查看指定表索引信息
select * from sqlite_master where tyoe = "index" and name = "<table_name>"
查看表所有字段
- sqlite:
pragma table_info('<table_name>')
- mysql:
desc <table_name>
代码获取:
openHelper.writableDatabase.query("pragma table_info(`$table`)").use {
while (it.moveToNext()) {
it.columnNames.forEach {col->
val index = it.getColumnIndex(col)
if (index != -1) {
Log.d("test", "[$table] - $col: ${it.getString(index)}")
}
}
}
}
查看建表语句
-sqlite:
# 所有表的建表语句
.schema
# 查看指定数据表的建表语句
.schema requires
- mysql:
show create table tableName \G
查询记录数目
select count(*) from requires
表操作(DDL)
创建表
create table [if not exists] tableName(
col1 type1 [not null] [primary key],
col2 type2 [not null],
...
)
SQLite 中的表和列名称不区分大小写
示例 :创建一个表,字段如下:
字段名 | 类型 | 约束 | 说明 |
---|---|---|---|
id | integer | 主键,自增长 | 编号 |
name | varchar | 长度20,非空 | 姓名 |
cid | integer | - | 所在班级 |
age | integer | 大于10且小于60 | 年龄 |
gender | bit | 默认1,表示男 | 性别 |
score | real | - | 成绩 |
相应的语句为:分号表示一条语句结束
create table student(
id INTEGER PRIMARY KEY AUTOINCREMENT,
name VARCHAR(20) NOT NULL,
cid INTEGER,
age INTEGER CHECK(age>18 and age<60),
gender BIT DEFAULT(1),
score REAL);
向表中添加字段
- Sqlite:
alter table tableName add 字段名 字段类型(此处不能加约束)
- MySQL:
# 连续添加:
alter table 表名
add 字段1 字段类型 限制,
add 字段2 字段类型 限制;
# 直接添加:
alter table 表名 add(
字段1 字段类型 限制,
字段2 字段类型 限制
);
查看表字段类型
# 括号内可不输引号
select typeof('<col_name>') from <table_name>
修改字段类型
- Sqlite:
alter table tableName modify 字段名 字段类型
- MySQL:可以通过逗号连续修改
删除一个字段
alter table tableName drop 字段名
重命名表
alter table tableName rename to 新表名
重命名列
alter table tableName change 旧字段名 新字段名 字段类型
添加约束
约束名规范:约束简称+表明+字段名
- mysql:
# 主键约束:
alter table tableName add constraint 约束名 primary key (字段名);
# 外键约束:
alter table tableName add constraint 约束名 foreign key (字段名) references 表名(字段名)
# 检查约束:
alter table tableName add constraint 约束名 check(表达式)
# 默认约束
alter table tableName alter 字段名 set default 值
# 自增列约束
alter table tableName modify column 字段名 类型 其他约束 auto_increment
Insert语句(DML)
插入一条记录
- 语法
# 所有字段插入数据:
insert [into] tableName values(v1,v2…)
# 为部分字段插入数据:
insert [into] 表名(字段1,字段2,字段3...) values(值1,值2,值3...)
- 示例
insert into student(name, cid, gender, age, score)values(‘宋江’,1,1,45,46.5);
插入多条记录
insert into tableName(field1,field2…) values(value1,value2…), (value1,value2…), (value1,value2…)…
从其他表格复制数据
insert into 表名(a,b,c,...) select a1,b1,c1,... from 表名
上面的语句要求两张表都已存在,还有一种要求新表不存在,会自动创建新表:
select a,b insert into table_not_exist from table_name
replace语法
replace into 表名(字段1,字段2,字段3...) values(值1,值2,值3...)
在使用replace的时候,处理唯一值的时候,先去表中找这个值存不存在,如果不存在插入数据,如果存在,先删除原来的数据,再插入自己的数据。
注意:
- MySQL可以一次插入多条数据,sqlite不行;
- 由于主键id已经设为autoincrement,因此不必手动赋值;
- 数据值涉及到字符串和日期需要使用单引号;
Update语句(DML)
语法:
update 表名 set 字段1 = 新值, 字段2= 新值where 条件
# 新值与旧值相关:
update tableName set 字段1=f(字段1) where 条件
示例:
# 将学生中姓名叫“小明”的改为“小刚”
update student set name=’小刚’ where name=’小明’;
注意 :
- MySQL和sqlite都可以一次更新多个字段;
- 不加Where条件是全部更新;
Delete语句(DML)
删除表
- 删除没有关联关系的表
drop table [if exists] tableName1, tableName2…
- 表之间有引用关系:先解除引用关系,再删表格
alter table 从表名 drop foreign key 外键字段名;
drop table 从表名 主表名;
- 删除所有数据,保留表结构,不能撤销还原
truncate table tableName;
删除记录
# 会把整个表数据都删掉
delete from 表名
# 删除符合条件的记录
delete from 表名 where 列名="value"
示例
# 删除成绩低于60分的学生信息:
delete from students where score<60;
Select语句(DQL)
格式
# 查询全部:
select * from tableName
# 通用格式
select col1,col2,...
from table1,table2,...
[where <condition>]
[group by <group_by_list>]
[having <condition>]
[order by <order_list> [asc|desc]]
Select语句的执行顺序:
- from指定数据源;
- where利用指定的条件对记录进行筛选;
- group by将粗筛后数据划分为多个分组;
- 聚合函数计算统计数据;
- having子句对分组后的结果进行细筛;
- order by子句对最终结果进行排序。
这种执行顺序操作的数据量最小,效率最高。
查询字段
查询的字段(所有/某个/某些):
# 单个字段:
select id from student where age=18
# 多个字段:
select id, name, score from student
# 所有字段:
select * from student
where条件
单一条件
select * from students where age>18;
多个条件
select * from students where age>18 and gender=1 and score>60.0;
数值条件
# 值在某个区间内
select * from students where age between 10 and 20;
# 列举条件:值是某几个数
select * from students where age in(11,13);
# 排除条件:值不是某几个数
select * from students where age notin(11,13)
模糊查询
select * from students where name like ‘%mike%’
结果去重
select distinct 字段名 from 表名
select distinct * from students where age>18
限制查询数量
select * from students where age>18 limit 5
聚合函数
查询结果的统计值:
# 结果的数量:
select count(*) from students where age>18;
# 结果的平均值
select avg(age) from students where gender=1;
# 结果的总和
select sum(age) from students where gender=1;
# 结果的最大值
select max(score) from students where gender=0;
# 结果的最小值
select min(score) from students where gender=1;
group by结果分组
查询结果按某个字段分组,分组+聚合函数配合使用可以非常灵活对结果进行查询分析。
# 查询每个专业各有多少学生
select major as ‘专业’,count(*) as ‘人数’ from students group by major;
# 根据不同的年龄、性别统计人数
select age,gender,count(*) from students group by age, gender;
having筛选分组结果
查询分数高于80分的学生姓名、平均分
select sid,name,avg(score) from students group by sid having avg(score)>80
order by结果排序
按某个字段降序/升序
# 分数从高到低排序:
select * from students order by score desc;
年龄从小到大排序:
select * from students order by age asc;
按学号和年龄排序:
select * from students order by id,age;
多表联查
主外键关联
语法:从表字段 references 主表(字段) 约束;
例如:c_id integer references students(s_id) not null;
表格可以分为两种:
- 主表:主表不含外键,比如:student(主键s_id)、course(主键c_id)、teacher(主键t_id)
- 从表:从表含多个外键,比如:课程表schedule(含主键_id,外键c_id、 s_id、t_id),成绩表score(含主键_id,外键s_id,外键c_id)
内连接
连接的两张表地位平等,多张表的数据只有一一对应才会连接。
- 隐式内连接:
两张表:select col_list from table1,table2 where table1.col=table2.col and 条件;
三张表:select table1.col,table2.col from table1,table2 where table1.主键 = table2.外键, table3.主键=表2.外键 and 条件1 and 条件2;
示例:查询选修了数据库课程的学号、成绩:
select s_id, grade
from course, score
where course.c_id = score.c_id and c_name = ‘数据库’
- 显式内连接:查询速度快于隐式内连接
语法:
select col_list from table1 [inner] join table2 on table1.col=table2.col;
select col_list from table1 t1 [inner] join table2 t2 on t1.col=t2.col;
on 自定义名.主键 = 表2.外键
示例:
select students.s_id, student.s_name, score.c_id, grade from students
inner join score
on student.s_id = score.s_id
外连接
两张表的地位不平等,其中基础表的每条数据必须出现,即使另一张表中没有与之匹配的数据也要用null补齐。
格式:
select col_list from table1 left|right [outer] join table2 on table1.col=table2.col;
示例:查询所有学生的生物成绩(有些人没选这门课)
Select s_name as ‘学生姓名’, course as ‘课程’,score as ‘成绩’ from students s
Left outter join scores s on s.s_id=c.s_id
And outter join courses c on c.c_id=s.c_id
And c.c_name=’生物’
子查询
把一个查询结果作为另一个查询的条件.
示例:查询选修了1号课程的学生编号和姓名
select s_id,s_name from students
s_id in(select s_id from courses where c_id=1)
使用exists关键字的子查询:子查询有结果外查询才能执行
示例:如何存在姓名为“老张”的学生,则查询其成绩
Select * from scores
Where exists (select * from students where s_name=’老张’);
联合查询
1)可以把多次查询结果纵向排列;
2)有all关键字才可以显式重复数据;
3)列的数量和类型都要兼容;
示例:查询男生的信息或年龄大于20的学生信息
select * from students where gender=’male’
union [all] -->将信息合并,如果不行合并可以加all
select * from students where age>20
union [all]
select …
示例:实现如下表的查询结果
SELECT user_qq,gno,score FROM scores
WHERE user_qq='12301'
UNION ALL
SELECT '总分',' ',SUM(score) FROM scores
WHERE user_qq='12301'
常见需求
数据库升级表迁移
sqlite中如果你的数据库设计发生变更,会触发数据库onMigrate或者onUpdate之类的回调,需要开发者给出相应的处理。总结了三种处理策略及其使用场景。
首先数据库变更可以分为两种:
- 不兼容变更:创建新表-复制旧表-删除旧表-重命名新表
- 兼容变更:仅需alter table即可
不兼容变更包括:
- 字段类型改变:比如从integer变成string
- 删除字段等
兼容变更包括:
- 新增字段
- 字段约束条件改变:比如从notnull为true变为false等
三种处理策略及其实现代码如下:
1. 直接删旧表
database.execSQL("drop table if exists table_name")
database.execSQL("delete from table_name")
database.execSQL("delete from sqlite_sequence where name = table_name")
2. 创建新表-复制旧表-删除旧表-重命名新表
database.execSQL("CREATE TABLE IF NOT EXISTS `new_table_name` (`col1` INTEGER PRIMARY KEY AUTOINCREMENT, `col2` TEXT NOT NULL, `col3` VARCHAR 20))
database.execSQL("INSERT INTO new_table_name (col1,col2,col3) SELECT col1,col2,col3 FROM table_name");
database.execSQL("DROP TABLE table_name")
database.execSQL("ALTER TABLE new_table_name RENAME TO table_name")
3. 修改旧表
// 新增字段
database.execSQL("ALTER TABLE table_name ADD COLUMN col INTEGER")
// 修改字段约束
database.execSQL("ALTER TABLE table_name MODIFY col INTEGER NOT NULL)
SQLite
Sqlite是一款轻型轻量级、关系型DBMS,设计目标是嵌入式环境;Sqlite占用资源非常低,在嵌入式设备中可能只需要几百K的内存;比起MySQL、postgreSQL这两款开源DBMS,它的处理速度更快;支持windows/Linux/UNIX等主流操作系统,同时能跟很多多线程语言相结合,如Tcl、C#、PHP、Java等
环境搭建
下载及安装
下载:SQLite第一个alpha版本诞生于2000年,较新的的版本是SQLite3官网:http://www.sqlite
安装:sqlite无需安装,下载解压后将sqlite3.exe文件放在D盘下即可使用:
进入命令控制台,输入命令创建一个数据库文件text.db(文件数据库,一个db文件即一个数据库),并退出。
图形化管理软件
常用工具软件
- sqlite developer
- sqlite administrator
- sqlite database browser
这些工具已经内置sqlite3数据库,无需再进行数据库连接配置,可直接使用;
以Sqlite developer为例:
- 安装
较新的版本是sqlite developer3.9.9。安装文件sqliteDev399.exe,默认安装路径c:、program files\sharpPlus\sqlitedev - 使用方法
首次打开数据库需要先对创建的数据库文件进行注册,数据库→注册数据库→输入数据库文件名和数据库别名(列表里面显示的是别名)→确定
创建表:找到创建的数据库文件→表文件夹→右键,创建表→创建数据的字段(要有主键)
sqlite中的限制
sqlite官网中说明了sqlite数据库目前存在的限制条件,相关C库也有限制,我们也可以通过命令行获取相关限制:
# 打开sqlite
sqlite
# 获取限制信息
.limits
内容如下:来源sqlite中的限制
length 1000000000 // 字符串或BLOB的最大长度10亿,一行的最大长度
sql_length 1000000000 // sql语句最大长度
column 2000 // 列数,可以在编译时才可以将最大列出改为32767
expr_depth 1000 // 表达式树的最大深度,SQLite将表达式解析到树中进行处理。
compound_select 500 // 复合SELECT语句中的最大术语数
vdbe_op 25000 // 虚拟机程序中用于实现SQL语句的最大指令数
function_arg 127 // 一个函数的最大参数个数
attached 10 // ATTACH语句,附加数据库最大值为125
like_pattern_length 50000 // LIKE模式匹配算法或GLOB模式的最大长度
variable_number 250000 // 任何参数的索引号
trigger_depth 1000 // 触发递归的最大深度
worker_threads 0 // 可以启动的辅助工作线程的最大数量
sqlite函数
MySQL
1985年瑞典MySQL AB公司用basic语言开发,2008年MySQL AB被sun公司收购,2009年sun被Oracle收购。
特点:开源、占用资源少、适用于多种编程语言、支持客户端访问。
版本:
- MySQL community server社区版本,开源免费,但不提供官方技术支持
- MySQL enterprise edition企业版本,付费,使用30天
- MySQL Cluster集群版,开源免费,可将几个MySQL server封装成一个server
环境搭建
下载与安装
网址:www.mysql 下载专区里面,选择社区版的图形化安装包mysql installer msi进行下载。对于一般的开发而言,只需要自定义安装mysql server和mysql workbench就可以了。
登录mysql
-
命令窗口
启动mysql服务
打开mysql command line client窗口,输入密码root -
图形化工具workbench
国内使用navicat更多,这里主要介绍navicat的使用:
- 打开链接,选择数据库。
- 右键选择新建表。
- 填写表各个字段的名称、数据类型、勾选是否允许空
- 保存表
MySQL函数
函数的概念:按指定格式输入参数,返回正确结果的运算单元。
日期与时间函数
- 返回当前日期
curdate()
current_date() 与crudate()功能一样,current_date()+0可以将当前日期转换为数值型 - 返回当前时间
curtime()
current_time() 与curtime()功能一样,current_date()+0可以将当前时间值转换为数值型 - 返回当前日期和时间
current_timestamp()
localtime()
now()
sysdate() 与now()的返回结果一样 - 获取月份
month(‘date’) 返回一个数字,其中date格式为yyyy-MM-dd
monthname(‘date’) 返回月份的英文名 - 获取星期
dayname(‘date’) 返回星期几的英文名
dayofweek(‘date’) 返回星期几对应的数字,星期天星期六(17)
weekday(‘date’) 返回星期几对应的数字,星期一星期天(06)
week(‘date’) 返回一年当中的第几个星期,从0开始
weekofyear(‘date’) 返回一年当中的第几个星期,从1开始 - 获取天数
dayofmonth()
dayofyear() - 获取年份
year(‘date’) - 获取季度
quarter(‘date’) - 获取小时
hour(‘time’) time的格式为 hh-mm-ss - 获取分钟
minute(‘time’) - 获取秒钟
second(‘time’)
数学函数
- 绝对值函数
abs(x) - 符号函数
sign(x) 1表示正数,-1表示负数,0表示零 - 获取随机数
rand() (0,1]
rand(x) (0,1],每次运行产生的数字都一样 - 获取整数的函数
ceil(x)/ceiling(x) 获取右整数
floor(x) 获取左整数 - 四舍五入函数
round(x) 返回整数
round(x,y) 返回y指定的小数位数,y可以为负数
truncate(x,y) 截取而不四舍五入 - 求模函数
mod(x,y) x%y - 幂函数
pow(x,y)/power(x,y) xy
exp(x) ex
sqrt(x) x0.5 - 弧度角度互换
radians(x) 角度转弧度
degrees(x) 弧度转角度 - 圆周率
pi() - 三角函数
sin(x)
asin(x)
cos(x)
acos(x)
tan(x)
atan(x)
cot(x)
字符串函数
- 计算字符串长度
char_length(s) 计算字符的个数
length(s) 计算字符串的字节数 - 合并字符串
concat(s1,s2…) 如果某个参数为null,则最终结果为null
concat_ws(x,s1,s2…) x为连接符 - 替换字符串
insert(s1,offset,len,s2) 第一个字符的index为1
replace(s,old,new) - 截取字符串
left(s,n) 从左边开始截取
right(s,n) 从右边开始截取 - 重复生成字符串
repeat(s,n) s重复n次 - 转换小写
lower(s)/lcase(s) - 转换大写
upper(s)/ucase(s) - 填充字符串
lpad(s1,len,s2) 把s1用s2填充到长度为len,s2从左边补齐
rpad(s1,len,s2) 把s1用s2填充到长度为len,s2从右边补齐 - 删除空格
ltrim(s) 删除左空格
rtrim(s) 删除右空格
trim(s) 删除两边的空格 - 删除指定字符串
trim(s1 from s) 删除s中左起第一个s1(如果是的话) - 获取字符串
substring(s,n,len)
mid(s,n,len) - 返回指定位置字符串函数
elt(n,s1,s2…) 从s1,s2…字符串中返回第n个字符串 - 返回指定字符串位置
field(s,s1,s2…) 从s1,s2…字符串中返回s的位置,下标从1开始,没有返回0
系统函数
- 获取MySQL版本号
version() - 查看当前用户的连接数
connection_id() - 查看当前所用数据库
database()
schema() - 获取用户名
user()/current_user()/
system_user()/session_user()
更多推荐
SQL:快速入门
发布评论