SQLite学习1

  一些基础知识

   

1.数据库后缀名是自定义的，如student.sqlite3,ldci.db。

2.SQL语句是以;结束的，并且对大小写不敏感。

   使用单引号来环绕文本值（大部分数据库系统也接受双引号）,如果是数值，不使用引号。

3.主键：primary key

   自增长主键：integer primary key

4.

SQL DML 和 DDL

可以把 SQL 分为两个部分：数据操作语言 (DML) 和 数据定义语言 (DDL)。

SQL (结构化查询语言)是用于执行查询的语法。但是 SQL 语言也包含用于更新、插入和删除记录的语法。

查询和更新指令构成了 SQL 的 DML 部分：

• SELECT - 从数据库表中获取数据
• UPDATE - 更新数据库表中的数据
• DELETE - 从数据库表中删除数据
• INSERT INTO - 向数据库表中插入数据

SQL 的数据定义语言 (DDL) 部分使我们有能力创建或删除表格。我们也可以定义索引（键），规定表之间的链接，以及施加表间的约束。

SQL 中最重要的 DDL 语句:

• CREATE DATABASE - 创建新数据库
• ALTER DATABASE - 修改数据库
• CREATE TABLE - 创建新表
• ALTER TABLE - 变更（改变）数据库表
• DROP TABLE - 删除表
• CREATE INDEX - 创建索引（搜索键）
• DROP INDEX - 删除索引

   一、创建数据表

   打开终端，输入“sqlite3 stu.db”（stu.db是数据库名称，若存在，则打开已有的文件，若不存在，就会创建它）

     1、创建简单的数据表

      sqlite>create table student(id integer primary key,name text,age text);

     2、在指定的数据库创建表

      sqlite>attach database '/users/qiaoqiao/stu.db' as newstu;

      sqlite> create table newstu.text(id integer primary key,name text,age text);

      这里先通过ATTACH DATABASE命令将一个已经存在的数据库文件attach到当前的连接中,之后再通过指定数据库名newstu的方式在目标数据库中创建数据表text。

     如果我们在创建数据表时没有指定数据库名,那么会在当前连接的main数据库中创建该表,在一个连接中只能有一个main数据库。如果需要创建临时表,就无需指定数据库名.

      --创建两个表，一个临时表和普通表。
     sqlite> CREATE TEMP TABLE temptable(first_col integer);

     sqlite> CREATE TABLE testtable (first_col integer);
     --将当前连接中的缓存数据导出到本地文件，同时退出当前连接。

     sqlite> .backup d:/mydb.db

     sqlite> .exit
     --重新建立sqlite的连接，并将刚刚导出的数据库作为主库重新导入。
     --查看该数据库中的表信息，通过结果可以看出临时表并没有被持久化到数据库文件中。

      sqlite> .tablestesttable

     3、如果当前创建的数据表名已经存在,即与已经存在的表名、视图名和索引名冲突,那么本次创建操作将失败并报错。如

       sqlite> create table student(id integer primary,name text,age text);
       Error: table student already exists

       如果在创建表时加上"IF NOT EXISTS"从句,那么本次创建操作将不会有任何影响,即不会有错误抛出,除非当前的表名和某一索引名冲突。 

       sqlite> create table if not exists student(id integer primary key,name text,age text);

    4、根据已有的表创建新表

      sqlite> create table student1 as select *from student;

      通过该方式创建的数据表将与SELECT查询返回的结果集具有相同的Schema信息,但是不包含缺省值和主键等约束信息。然而新创建的表将会包含结果集返回的所有数据。

   5、创建带约束的表

    （1）主键约束

      --->直接在字段的定义上指定主键。

     sqlite> create table newtable(first integer primary key asc);

     --->在所有字段定义完后在定义表的数约束

     sqlite> create table text2(
         ...> first integer,
         ...> second integer,
         ...> unique(first,second)
         ...> );

    ( 2 )唯一性约束

      1）直接在字段的定义上指定唯一性约束条件

      sqlite> create table text3(first integer quique);

       2)在所有字段定义完毕后，再定义表的唯一性约束。

      sqlite> create table text4(
          ...> first integer,
          ...> second integer,

          ...> unique(first,second)
          ...> );

      

   ( 3 )非空约束(NOT NULL)

      在SQLite中,NULL值被视为和其他任何值都是不同的，如下例

       sqlite> select count (*)from text3;
       0

       sqlite> insert into text3 values(null);

       sqlite> select count (*)from text3;
      1

       可见，插入的null值插入成功。

       sqlite> create table text5(fitsr integer not null);
       sqlite> insert into text5 values(null);
       Error: text5.fitsr may not be NULL
      从上述结果可以看到，text5已被定义成非空的，因此不能再插入null值。

     

  ( 4 )检查性约束(CHECK)

     sqlite> create table new(first integer CHECK(first < 1));
     sqlite> insert into new values(5);
     Error: constraint failed    

     插入的值为5，违反了字段first<1的检查行约束 
 

     二、表的修改(ALTER TABLE)

    SQLite对ALTER TABLE命令支持的非常有限,仅仅是修改表名和添加新字段。 

   1、修改表名

    sqlite> create table stu(first integer);
    sqlite> .table
    stu
    sqlite> alter table stu rename to newstu;
    sqlite> .table
    newstu

    可以看到，stu已经被重命名为newstu。

    SOLite中表名的修改只能在再同一个数据库中，一旦表名被修改，该表已经存在的索引不会受到影响，但依赖该表的的视图和触发器要重新修改定义。

   

    2、新增字段

    sqlite> alter table newstu add column second integer;

    sqlite> .schema newstu
    CREATE TABLE "newstu"(first integer, second integer);

    关于ALTER TABLE最后需要说明的是,在SQLite中该命令的执行时间是不会受到当前表行数的影响,也就是说,修改有一千万行数据的表和修改只有一条数据的表所需的时间几乎是相等 。

    3、表的删除：

      在SQLite中如果某个表被删除了，那么与之相关的索引和触发器也会被随之删除。在很多其他的关系型数据库中是不可以这样的，如果必须要删除相关对象，只能在删除表语句中加入WITH CASCADE从句。见如下示例

     sqlite> CREATE TABLE testtable (first_col integer);

     sqlite> DROP TABLE testtable;

     sqlite> DROP TABLE testtable;

     Error: no such table: testtable
     sqlite> DROP TABLE IF EXISTS testtable;
   从上面的示例中可以看出，如果删除的表不存在，SQLite将会报错并输出错误信息。如果希望在执行时不抛出异常，我们可以添加IF EXISTS从句，该从句的语义和CREATE TABLE中的完全相同。

      三、对表进行操作

    一些指令

          .database ---查看数据库

          .tables ---查看所有表

          创建数据库：sqlite3 ldci.db

          查看数据库：.database

          查看表的命令：.tables

          显示表结构:  .schema  表名

      （1）插入数据

         1 )插入一行新的数据 

        sqlite> create table student(id integer primary key,name text,age text);
        sqlite> insert into student values('1','张三','12');
        sqlite> insert into student values('2','李四','15');
        sqlite> insert into student values('3','王五','11');

        或者用insert  into  表名（列1，列2......）values(值1，值2.....) 语句。

         sqlite> insert into student （'name','age'）values('张三','12');

      

        2）在指定列插入新的数据

        在name列和age列插入新数据。

        sqlite> insert into student(name,age)values('hello','34');

      （2） 查询

        1> 查询所有数据

        select *是获取所有列的数据

        sqlite> select *from student;
        1|张三|12
        2|李四|15
        3|王五|11

        2>查询某一行数据

        查询序号为1的数据

         sqlite> select *from student where id=1;
         1|张三|12

        3> 查询某一列的数据

         查询name列的数据         

         sqlite> select name from student;

         张三
         李四
         王五

        有时候，在表中某一列，可能会包含相同数据，而我们想列出不重复的数据，这时可以用到distinct关键字，该关键字用于返回唯一不同的值；

         sqlite> select distinct age from student;

         

         4>有条件的从表中查询数据，要用到where关键字。

         有条件的查询age列里面等于23的数据

          sqlite> select *from student where age = 23;
          3|王五|23
          4|刘淇|23

       查询age列里等于12或者20的数据，用到了or运算符。

        sqlite> select *from student where age=12 or age=2;

       1|张三|12
        2|李四|2

   

       查询表里age＝23并且name＝'王五'的数据，用到了and运算符。        

        sqlite> select *from student where age=23 and name = '王五';
        3|王五|23

    

      结合and和or运算符的查询

       sqlite> select *from student where(age=12 or age=23) and name='王五';
       3|王五|23

      

     5> 模糊查询

       查询姓为王的数据

       select *from stu where name like '%王%';

    

        ( 3 )对表进行排序

          order by语句用于根据指定的列对结果进行排序，默认是按升序排列，

         根据年龄从小到大排序

          sqlite> select *from student order by age;
          3|王五|11
          1|张三|12
          2|李四|15

        

        根据年龄从大到小排序

        sqlite> select *from student order by age desc;
        2|李四|15
       1|张三|12
        3|王五|11

        

      （4）修改表中数据 

       修改数据用update语句，用法：

       update 表名称  set  行名 ＝ 新值  where   列名 ＝ 某值

      

       sqlite> update student set age=30 where id=1;把序号为1的age改为30.

       sqlite> select *from student where id=1;
       1|张三|30        

       （5）删除表里的数据（delete语句）

        删除某一列数据

        sqlite> delete from student where id=2;
        sqlite> select *from student;
       1|张三|30
        3|王五|11

        可以看到，序号为2的数据已被删除。

       删除所有行。

      sqlite> delete from student;

      或者

      sqlite> delete from student;