SQLite 约束和简单命令
安装sqlite3,配置环境变量。
1.打开数据库
sqlite3.exe db_name.db
2.常用命令
.tables 查看所有表
.headers on 设置显示表头
.schema table_name 查看建表语言
.output a.txt 输出重定向到a.txt中
.output stdout 输出重定向到标准输出
PRAGMA table_info(table_name); //暂时没发现作用
3.建表
CREATE TABLE IF NOT EXISTS Student( ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Class TEXT NOT NULL, Age TEXT DEFAULT '',
Grade INT CHECK(Grade > 0), UNIQUE(Name, Class));
NOT NULL 约束:确保某列不能有 NULL 值。
DEFAULT 约束:没有指定值时,填入默认值
UNIQUE 约束:key=(Name, Class),无法再次插入相同key的数据。
PRIMARY Key 约束:唯一标识数据库表中的各行/记录。
CHECK 约束:CHECK 约束确保某列中的所有值满足一定条件。
4.插入数据
INSERT INTO Student(Name,Class,Grade) values('ZHANG SAN','16',10); //success INSERT INTO Student(Name,Class,Age,Grade) values('ZHANG SAN','9','16',10); //success INSERT INTO Student(Name,Class,Age,Grade) values('ZHANG SAN','10','16',10); //success INSERT INTO Student(Name,Class,Age,Grade) values('ZHANG SAN','10','16',10); //Error: UNIQUE constraint failed: Student.Name, Student.Class INSERT INTO Student(Name,Class,Age,Grade) values('ZHANG SAN','10','16',-1); //Error: CHECK constraint failed: Grade > 0
5.查询
SELECT * FROM Student WHERE Name='ZHANG SAN' OR/AND Age='10';
5.1 查询:相同LOGINID的数据条数,并找出数量大于3的LOGINID和次数。
SELECT LOGINID,count FROM (SELECT LOGINID,COUNT(LOGINID) AS count FROM USERS GROUP BY LOGINID) WHERE count>3;
6.删除表里所有数据
DELETE FROM Student;
7.修改表名
ALTER TABLE Student RENAME TO Teacher;
8.增加/删除列
ALTER TABLE Student ADD COLUMN Status TEXT NOT NULL default ''; ALTER TABLE Student DROP COLUMN Status;
9.导出数据表
.dump Student
PRAGMA foreign_keys=OFF; BEGIN TRANSACTION; CREATE TABLE Student( ID INTEGER PRIMARY KEY AUTOINCREMENT, Name TEXT NOT NULL, Class TEXT NOT NULL, Age TEXT DEFAULT '', Grade INT CHECK(Grade > 0), UNIQUE(Name, Class)); INSERT INTO Student VALUES(1,'ZHANG SAN','9','16',10); INSERT INTO Student VALUES(2,'ZHANG SAN','10','16',10); INSERT INTO Student VALUES(3,'ZHANG SAN','16','',10); COMMIT;
10.模糊匹配
select from Student where Name like 'ZHAN%'; //百分号(%)代表零个、一个或多个数字或字符 select from Student where Name like 'ZHANG SA_'; //下划线(_)代表一个单一的数字或字符
11.修改用户密码
步骤 1):输入命令:update mysql.user set authentication_string=password('new_password') where user='user_id' and Host ='localhost'; 步骤 2):输入 flush privileges; 命令刷新权限。 步骤 3):输入quit;命令退出 MySQL 重新登录,此时密码已经修改为刚才输入的新密码了。