常用的SQL语句

常用的SQL语句

1，创建表

CREATE 创建

TABLE 表

PRIMARY KEY主键

AUTOINCREMENT自动增长

NOT NULL 非空

UNIQUE 唯一的

CREATE TABLE Teacher1 (teacher_idINTEGER PRIMARY KEYAUTOINCREMENT NOT NULLUNIQUE, teacher_name TEXT,teacher_sexBOOLEAN)

CREATE TABLE Teacher2 (teacher_idINTEGER PRIMARY KEY AUTOINCREMENT, teacher_name TEXT,teacher_sexBOOLEAN)

2, 删除表

DROP 丢弃 扔到

DROP TABLE Teacher1

3,给表添加列（SQLite轻量级数据库，不支持改）

ALTER 改变 修改

ALTER TABLE Teacher2ADD teacher_age INTEGER

4,插入数据

INSERT INTO TeacherVALUES(1,'zhangsan',1)

INSERT INTO Students (name,sex)VALUES('zhangsan',0)

5,查询数据

SELECT 查询

* 全部

FROM

SELECT * FROM Students

SELECT * FROM Students WHERE number<10

SELECT * FROM Students WHERE number<10 OR sex=1

SELECT * FROM Students WHERE number<10 AND number>5

like 匹配

SELECT * FROM Students WHERE name like 'zhang%'    %通配符能通配若干个（仅用于字符串）

SELECT * FROM Students WHERE name like 'zhang_'     _通配符只能通配一个

LIMIT 限制

从第六条开始 往后的15条

SELECT * FROM Students LIMIT 5,15

ORDER BY    搜索排序     DESC（降序）   ASC（升序）

SELECT *FROM people ORDER BY ageDESC

搜索最大年龄

SELECT MAX(age) FROM people

搜索最大年龄的所有信息（嵌套语句）

SELECT * FROM people WHERE age= (SELECT MAX(age)FROM people)

搜索表里有几条数据

SELECT count(*) FROM people

6,修改数据

UPDATE 更新

set 设置

UPDATE Students set name='lisi',sex=1where number < 10 AND number>5

7,删除数据

DELETE 删除

DELETE FROM StudentsWHERE number > 25