学习SQLite之路(二)

  下面就是真正关于数据库的一些知识了:

20160614更新

 参考: http://www.runoob.com/sqlite/sqlite-tutorial.html

1. SQLite创建表:

基本语法:

CREATE TABLE database_name.table_name(
   column1 datatype  PRIMARY KEY(one or more columns),
   column2 datatype,
   column3 datatype,
 .....
   columnN datatype,
);

实例: create_company.txt

drop table company;  -- 删除表,我这是为了可能会运行多次这个txt,就先删除一下,其实是多余的
create table company(
ID INT PRIMARY KEY NOT NULL,  -- ID 是列名, INT是数据类型 ,后面的是一些限制(主键,非空等)
NAME TEXT NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR(50),
SALARY REAL
);

创建完以后可以用 .table 来查看表:

也可以 .schema 命令得到表的完整信息:

2. SQLite删除表:

基本语法:可以选择指定带有表名的数据库名称

DROP TABLE database_name.table_name;

注意:表被删除,表里面的所有信息也被删除了

实例:

3. SQLite insert语句:用于向表中插入数据

语法(1):

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)]  
VALUES (value1, value2, value3,...valueN);   /* 要为表中的指定列添加值 */

语法(2):

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);  /* 要为表中的所有列添加值 */

 实例: 我这里写入一个insert_company.txt

insert into company values(1, 'Paul', 32, 'USA', 20000.0);  -- 为所有列添加值 insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(2, 'Mark',33,'JAP',1500.00);  -- 添加指定列 insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(3, 'Allen',35,'SPA',3000.00); insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(4, 'Jack',32,'JAP',2100.00); insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(5, 'David',25,'END',4500.00); insert into company (ID, NAME, AGE, ADDRESS,SALARY) VALUES(6, 'Kim',22,'AUS',3500.00); .read select_company.txt    -- 这个先不管

4. SQLite select语句: 从 SQLite 数据库表中获取数据,以结果表的形式返回数据。这些结果表也被称为结果集。

语法:

SELECT column1, column2, columnN FROM table_name;

columnN表示哪几个列,也可以用 * 表示全部

实例: select_company.txt

内容: select * from company;

实例:

(2)设置输出列的宽度:

sqlite>.width 10, 20, 10, 0

第一列宽度为10,第二列为20,第三列为10,第四列为默认(0表示默认)

(3)Schema信息:  系统里面有个表叫 sqlite_master  ,这里有所有表的信息。

schema_info.txt 

select tbl_name from sqlite_master where type = 'table'; -- 列出所有在数据库中创建的表

select sql from sqlite_master where type = 'table' and tbl_name='company'; -- 列出表company的信息

效果:

 5. SQLite 运算符:运算符是一个保留字或字符,主要用于 SQLite 语句的 WHERE 子句中执行操作,如比较和算术运算

主要有:算数运算符  比较运算符   逻辑运算符   位运算符

(1)算数运算符:

+   -    *   /   

实例:

 (2)比较运算符:

假设变量 a=10,变量 b=20,则:

           运算符                                                    描述

运算符运算符

描述

实例

==

检查两个操作数的值是否相等,如果相等则条件为真。

(a == b) 不为真。

=

检查两个操作数的值是否相等,如果相等则条件为真。

(a = b) 不为真。

!=

检查两个操作数的值是否相等,如果不相等则条件为真。

(a != b) 为真。

<>

检查两个操作数的值是否相等,如果不相等则条件为真。

(a <> b) 为真。

>

检查左操作数的值是否大于右操作数的值,如果是则条件为真。

(a > b) 不为真。

<

检查左操作数的值是否小于右操作数的值,如果是则条件为真。

(a < b) 为真。

>=

检查左操作数的值是否大于等于右操作数的值,如果是则条件为真。

(a >= b) 不为真。

<=

检查左操作数的值是否小于等于右操作数的值,如果是则条件为真。

(a <= b) 为真。

!<

检查左操作数的值是否不小于右操作数的值,如果是则条件为真。

(a !< b) 为假。

!>

检查左操作数的值是否不大于右操作数的值,如果是则条件为真。

(a !> b) 为真。

 (3)逻辑运算符:

  运算符                                       描述

AND

AND 运算符允许在一个 SQL 语句的 WHERE 子句中的多个条件的存在。

BETWEEN

BETWEEN 运算符用于在给定最小值和最大值范围内的一系列值中搜索值。

EXISTS

EXISTS 运算符用于在满足一定条件的指定表中搜索行的存在。

IN

IN 运算符用于把某个值与一系列指定列表的值进行比较。

NOT IN

IN 运算符的对立面,用于把某个值与不在一系列指定列表的值进行比较。

LIKE

LIKE 运算符用于把某个值与使用通配符运算符的相似值进行比较。

GLOB

GLOB 运算符用于把某个值与使用通配符运算符的相似值进行比较。GLOB 与 LIKE 不同之处在于,它是大小写敏感的。

NOT

NOT 运算符是所用的逻辑运算符的对立面。比如 NOT EXISTS、NOT BETWEEN、NOT IN,等等。它是否定运算符。

OR

OR 运算符用于结合一个 SQL 语句的 WHERE 子句中的多个条件。

IS NULL

NULL 运算符用于把某个值与 NULL 值进行比较。

IS

IS 运算符与 = 相似。

IS NOT

IS NOT 运算符与 != 相似。

||

连接两个不同的字符串,得到一个新的字符串。

UNIQUE

UNIQUE 运算符搜索指定表中的每一行,确保唯一性(无重复)。

(4)位运算符:

如果 A = 60,且 B = 13,则:

运算符                   描述

&

如果同时存在于两个操作数中,二进制 AND 运算符复制一位到结果中。

(A & B) 将得到 12,即为 0000 1100

|

如果存在于任一操作数中,二进制 OR 运算符复制一位到结果中。

(A | B) 将得到 61,即为 0011 1101

~

二进制补码运算符是一元运算符,具有"翻转"位效应。

(~A ) 将得到 -61,即为 1100 0011,2 的补码形式,带符号的二进制数。

<<

二进制左移运算符。左操作数的值向左移动右操作数指定的位数。

A << 2 将得到 240,即为 1111 0000

>>

二进制右移运算符。左操作数的值向右移动右操作数指定的位数。

A >> 2 将得到 15,即为 0000 1111

以上四种运算符用在where子句中比较多,以后再分析;

20160615 更新

1.SQLite表达式:

表达式是一个或多个值、运算符和计算值的SQL函数的组合。

SQL 表达式与公式类似,都写在查询语言中。您还可以使用特定的数据集来查询数据库。

(1)布尔表达式:  如: *** where ID = 1;

(2)数值表达式:  如: *** where SALARY > 4000;

(3)日期表达式:  如:sqlite> SELECT CURRENT_TIMESTAMP;

2. SQLite AND OR运算符:AND 和 OR 运算符用于编译多个条件来缩小在 SQLite 语句中所选的数据。这两个运算符被称为连接运算符。

(1)AND 运算符: 就是且,要同时满足所有条件。

基本语法:且每个条件必须为真

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] AND [condition2]...AND [conditionN];

(2)OR运算符:就是 或, 满足一个条件就可以

基本语法:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition1] OR [condition2]...OR [conditionN]

(3)实例见下面:

3. SQLite where 子句:WHERE 子句用于指定从一个表或多个表中获取数据的条件。

WHERE 子句不仅可用在 SELECT 语句中,它也可用在 UPDATE、DELETE 语句中,等等

(1)基本语法:

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

先创建并插入几条数据:

(2)实例:  where_company.txt:

-- AGE大于20 且 SALARY大于5000

select * from company where AGE>25 AND SALARY >4500;

-- AGE大于20 或 SALARY大于5000

select * from company where AGE>25 OR SALARY >4500;

-- AGE不为NULL

select * from company where AGE IS NOT NULL;

-- NAME以 ki 开头的,Ki之后的字符不做限制

select * from company where NAME LIKE 'Ki%';

select * from company where NAME GLOB 'Ki*';

-- AGE 为22或25的所有记录

select * from company where AGE IN(25, 22);

-- AGE 不为22或25的所有记录

select * from company where AGE NOT IN(25, 22);

-- AGE 为25到35之间的所有记录

select * from company where AGE BETWEEN 25 AND 35;

-- 在工资大于3000的人中找到年龄在 25到35之间的

select * from (select * from company where SALARY > 3000) where AGE BETWEEN 25 AND 35;

结果太长了,这里省略。

4. SQLite  update子句: UPDATE 查询用于修改表中已有的记录。可指定条件更新。

(1)基本语法:

UPDATE table_name
SET column1 = value1, column2 = value2...., columnN = valueN
WHERE [condition];

(2)实例: update_company.txt

-- 更新ID为6的人,工资增加1000。只更新满足条件的记录

update company set SALARY = SALARY + 1000 where ID = 6

-- 修改表中ADDRESS和SALARY的所有值,则不需要使用where子句

update company set ADDRESS = 'Texas',SALARY = 10000.0;

5.SQLite  delete子句:查询用于删除表中已有的记录。可以使用带有 WHERE 子句的 DELETE 查询来删除选定行,否则所有的记录都会被删除。

(1)基本语法:

DELETE FROM table_name
WHERE [condition];

(2)实例: delete_company.txt

-- 删除ID为5的记录

delete from company where ID = 5;

-- 删除所有记录

delete from company;

6. SQLite like子句:LIKE 运算符是用来匹配通配符指定模式的文本值(大小写不敏感)

需要两个通配符一起使用:也可以组合使用

% :代表零个、一个或多个数字或字符

_:下划线(_)代表一个单一的数字或字符

(1)说明:

        语句                                                     a  描述

WHERE SALARY LIKE '200%'

查找以 200 开头的任意值

WHERE SALARY LIKE '%200%'

查找任意位置包含 200 的任意值

WHERE SALARY LIKE '_00%'

查找第二位和第三位为 00 的任意值

WHERE SALARY LIKE '2_%_%'

查找以 2 开头,且长度至少为 3 个字符的任意值

WHERE SALARY LIKE '%2'

查找以 2 结尾的任意值

WHERE SALARY LIKE '_2%3'

查找第二位为 2,且以 3 结尾的任意值

WHERE SALARY LIKE '2___3'

查找长度为 5 位数,且以 2 开头以 3 结尾的任意值

(2)实例: like_company.txt

select * from company where SALARY like '2%';  -- 2 开头的SALARY select * from company where SALARY like '210_.0';  -- 自己体会

select * from company where NAME like 'k%';  -- 可以看出大小写不敏感

7. SQLite  GLOB子句:GLOB 运算符是用来匹配通配符指定模式的文本值(大小写敏感) 先说两个通配符:

% :星号(*)代表零个、一个或多个数字或字符

? :  问号(?)代表一个单一的数字或字符

 (1)说明:

  语句                     描述

WHERE SALARY GLOB '200*'

查找以 200 开头的任意值

WHERE SALARY GLOB '*200*'

查找任意位置包含 200 的任意值

WHERE SALARY GLOB '?00*'

查找第二位和第三位为 00 的任意值

WHERE SALARY GLOB '2??'

查找以 2 开头,且长度至少为 3 个字符的任意值

WHERE SALARY GLOB '*2'

查找以 2 结尾的任意值

WHERE SALARY GLOB '?2*3'

查找第二位为 2,且以 3 结尾的任意值

WHERE SALARY GLOB '2???3'

查找长度为 5 位数,且以 2 开头以 3 结尾的任意值

(2)实例: glob_company.txt

select * from company where SALARY glob '2*';  -- 2 开头的SALARY select * from company where SALARY glob '210?.0';  -- 自己体会 select * from company where NAME glob 'k*';  -- 可以看出大小写敏感 select * from company where NAME glob 'K*';  -- 可以看出大小写敏感

8. SQLite Limit子句:LIMIT 子句用于限制由 SELECT 语句返回的数据数量

 (1)基本语法1:

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows]

下面是 LIMIT 子句与 OFFSET 子句一起使用时的语法:

SELECT column1, column2, columnN 
FROM table_name
LIMIT [no of rows] OFFSET [row num]

 (2)实例: limit_company.txt

select * from company limit 3;  -- 只显示3行 select * from company limit 3 offset 2;  -- 从第3行开始 显示3行

9. SQLite order by子句:用来基于一个或多个列按升序或降序顺序排列数据。

(1)基本语法:

SELECT column-list 
FROM table_name 
[WHERE condition] 
[ORDER BY column1, column2, .. columnN] [ASC | DESC];

ASC :表示升序(默认就是这个,可以不写)

DESC:表示降序

(2)实例: orderby_company.txt

select * from company order by SALARY; -- 升序 ,默认asc可以不写 select * from company order by SALARY ASC; select * from company order by SALARY DESC;  -- 降序

10. SQLite  group by 子句:SQLite 的 GROUP BY 子句用于与 SELECT 语句一起使用,来对相同的数据进行分组。

在 SELECT 语句中,GROUP BY 子句放在 WHERE 子句之后,放在 ORDER BY 子句之前。

 (1)基本语法:

SELECT column-list
FROM table_name
WHERE [ conditions ]
GROUP BY column1, column2....columnN    -- where 之后      order by  之前
ORDER BY column1, column2....columnN

(2)实例: groupby_company.txt

先插入3条数据:

insert into company values(8, 'Paul', 24, 'USA', 5000); insert into company values(9, 'James', 24, 'CNA', 4000); insert into company values(10, 'James', 25, 'USA', 6000);

再进行分组

/*

insert into company values(8, 'Paul', 24, 'USA', 5000);

insert into company values(9, 'James', 24, 'CNA', 4000);

insert into company values(10, 'James', 25, 'USA', 6000);

*/

-- 按name进行分组,SUM( *** )  表示对里面的东西求和

select NAME, SUM(SALARY) from company group by NAME order by NAME;

select NAME, SUM(SALARY) from company group by NAME order by NAME DESC; -- 降序

11. SQLite  having 子句:HAVING 子句允许指定条件来过滤将出现在最终结果中的分组结果。

WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件

(1)重要知识:HAVING 子句在 SELECT 查询中的位置:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

(2)基本语法:HAVING 子句必须放在 GROUP BY 子句之后,必须放在 ORDER BY 子句之前

SELECT column1, column2
FROM table1, table2
WHERE [ conditions ]
GROUP BY column1, column2
HAVING [ conditions ]
ORDER BY column1, column2

(3)实例:having_company.txt

-- 显示名称计数小于 2 的所有记录:

select * from company group by name having count(NAME) < 2;

-- 显示名称计数等于 2 的所有记录:

select * from company group by name having count(NAME) >= 2;

12. SQLite  distinct关键字:与 SELECT 语句一起使用,来消除所有重复的记录,并只获取唯一一次记录。

只获取唯一一次记录,而不是获取重复记录

(1)语法: 在select的后面:

SELECT DISTINCT column1, column2,.....columnN 
FROM table_name
WHERE [condition]

(2)实例:distinct_company.txt

select NAME, SALARY from company;

select distinct NAME, SALARY from company;  -- 相同NAME只出现一次

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏大大的微笑

设计模式之单例模式深究

为什么使用单例模式?         我认为是由于某些对象的创建比较耗时,对内存的消耗又比较大回收成本过高,这时候如果允许 不断的创建对象代价势必太高,如果...

2196
来自专栏Dawnzhang的开发者手册

数据库截取字符串SUBSTR函数的使用

今天中午做需求的时候,有类似于根据银行卡卡号的前几位判断出是哪个银行的情况,每个银行需要截取的位数都不一样,这时我就想到了SUBSTR

1002
来自专栏Java成神之路

Oracle学习笔记_02_基本SQL

        SQL 语言大小写不敏感。         SQL 可以写在一行或者多行 关键字不能被缩写也不能分行         各子句一般要分行写。 ...

892
来自专栏吾爱乐享

软件测试之学习mysql的查询功能select及高级查询(重中之重)

1212
来自专栏PHP在线

MySQL字段类型的详细解释

MySQL支持大量的列类型,它可以被分为3类:数字类型、日期和时间类型以及字符串(字符)类型。 概述有意简化,更详细的说明应该考虑到有关特定列类型的附加信息,例...

3879
来自专栏杨建荣的学习笔记

oracle中的数组(第一篇)(r4笔记第9天)

数组在各种编程语言中都是很重要的数据结构实现,在oracle中也有自己的一席之地。自己简单做了几个实验,发现很多东西还是眼高手低,真实去做的时候,里面还是有不少...

3076
来自专栏一个会写诗的程序员的博客

MySQL中函数CONCAT 、CONCAT_WS、GROUP_CONCATCONCAT_WSCONCATGROUP_CONCAT

CONCAT_WS() 代表 CONCAT With Separator ,是CONCAT()的特殊形式。

621
来自专栏乐沙弥的世界

PL/SQL 联合数组与嵌套表

      通常情况下,在PL/SQL中,处理单行单列的数据可以使用标量变量,而处理单行多列的数据则使用PL/SQL记录是不错的选择。单列多行数据 则由联合数组...

1053
来自专栏程序猿DD

10分钟让你明白MySQL是如何利用索引的

一、前言 在MySQL中进行SQL优化的时候,经常会在一些情况下,对MySQL能否利用索引有一些迷惑。 譬如: MySQL 在遇到范围查询条件的时候就停止匹配了...

3609
来自专栏王磊的博客

MongoDB Query 的几个方法

Query.All("name", "a", "b");//通过多个元素来匹配数组 Query.And(Query.EQ("name", "a"), Query...

2978

扫码关注云+社区

领取腾讯云代金券