sqlite

对已有表添加新的列: 

PRAGMA foreign_keys = 0;

CREATE TABLE sqlitestudio_temp_table AS SELECT *
                                          FROM Students;

DROP TABLE Students;

CREATE TABLE Students (
    Id    INTEGER PRIMARY KEY AUTOINCREMENT
                  NOT NULL,
    Name  STRING  NOT NULL,
    Age   INTEGER NOT NULL,
    Addr  STRING,
    Grade INTEGER NOT NULL
);

INSERT INTO Students (
                         Id,
                         Name,
                         Age,
                         Addr
                     )
                     SELECT Id,
                            Name,
                            Age,
                            Addr
                       FROM sqlitestudio_temp_table;

DROP TABLE sqlitestudio_temp_table;

PRAGMA foreign_keys = 1;

使用group by 分组查询

select Age,Max(Grade),MIN(Grade),AVG(Grade),SUM(Grade),COUNT(Grade)/COUNT(*) from Students  group by Age

以Age 为分组(相同的Age为一组) 显示每组中最高的分数和对应的年纪 

select COUNT(*) from Students

排序

select* from 表名 order by 列名  ACS/ DESC(降序) ，列名

高级数据过滤

1、名字以T 开头

select * from 表名 where name like 'T%'

2、名字以ke结尾

select* from 表名 where name like '%T'

3、名字中包含的

select * from 表名 where name like '%T%'

多值检测

Select age,name from Students where age = 1 or age = 18 or age = 23

Select age,name from Students where age in(1,18,23)

NULL 值检测 

select 1, 2,3 

select 1,2,3,4,6=6,null++1,null+null  与null 作任何运算都是null

限制查询结果

insert into Students (Name,Age,addr,grade) select Name,age,addr,grade from Students

将查询结果作为值插入表中 

select * from Students order by Id limit 3 offset 2 从第二行开始（0开始） 取最多三条数据 

select distinct Age from Students order by age limit 3

数据库表之间的关系 (外键)

Join 语句

select * from T_Authors a join T_books b where a.Id == b.authorid

select * from T_Authors a join T_books b on a.Id == b.authorid

select b.id as bookId from T_Authors a join T_books b on a.Id == b.authorid 

 表间多对多关系

select * from StudentsTeachersRealations r 

join Teachers t on r.TeacherId = t.id

join Students s on r.StudentId = s.id

where t.name == ''

MySQL 数据库

SQL 注入

避免SQL 字符串拼接，使用参数的方式