SQLite version 3.7.9 2011-11-01 00:52:41 Enter ".help" for instructions Enter SQL statements terminated with a ";" sqlite> create table students( ...> id integer primary key, ...> name text not null unique, ...> sex int default 1, ...> bak text); sqlite> .tables students sqlite> .help .backup ?DB? FILE Backup DB (default "main") to FILE .bail ON|OFF Stop after hitting an error. Default OFF .databases List names and files of attached databases .dump ?TABLE? ... Dump the database in an SQL text format If TABLE specified, only dump tables matching LIKE pattern TABLE. .echo ON|OFF Turn command echo on or off .exit Exit this program .explain ?ON|OFF? Turn output mode suitable for EXPLAIN on or off. With no args, it turns EXPLAIN on. .header(s) ON|OFF Turn display of headers on or off .help Show this message .import FILE TABLE Import data from FILE into TABLE .indices ?TABLE? Show names of all indices If TABLE specified, only show indices for tables matching LIKE pattern TABLE. .load FILE ?ENTRY? Load an extension library .log FILE|off Turn logging on or off. FILE can be stderr/stdout .mode MODE ?TABLE? Set output mode where MODE is one of: csv Comma-separated values column Left-aligned columns. (See .width) html HTML
code insert SQL insert statements for TABLE line One value per line list Values delimited by .separator string tabs Tab-separated values tcl TCL list elements .nullvalue STRING Print STRING in place of NULL values .output FILENAME Send output to FILENAME .output stdout Send output to the screen .prompt MAIN CONTINUE Replace the standard prompts .quit Exit this program .read FILENAME Execute SQL in FILENAME
数据库基础操作语句
一、基础 1、说明:创建数据库 CREATE DATABASE database-name 2、说明:删除数据库 drop database dbname 3、说明:备份sql server --- 创建备份数据的device USE master EXEC sp_addumpdevice 'disk', 'testBack', 'c:\mssql7backup\MyNwind_1.dat' --- 开始备份 BACKUP DATABASE pubs TO testBack 4、说明:创建新表 create table tabname(col1 type1 [not null] [primary key],col2 type2 [not null],..) 根据已有的表创建新表: A:create table tab_new like tab_old (使用旧表创建新表) B:create table tab_new as select col1,col2…from tab_old definition only 5、说明:删除新表 drop table tabname 6、说明:增加一个列 Alter table tabname add column col type 注:列增加后将不能删除。DB2中列加上后数据类型也不能改变,唯一能改变的是增加varchar 类型的长度。 7、说明:添加主键:Alter table tabname add primary key(col) 说明:删除主键:Alter table tabname drop primary key(col) 8、说明:创建索引:create [unique] index idxname on tabname(col….) 删除索引:drop index idxname 注:索引是不可更改的,想更改必须删除重新建。 9、说明:创建视图:create view viewname as select statement 删除视图:drop view viewname 10、说明:几个简单的基本的sql语句 选择:select * from table1 where 范围 插入:insert into table1(field1,field2) values(value1,value2) 删除:delete from table1 where 范围 更新:update table1 set field1=value1 where 范围 查找:select * from table1 where field1 like ’%value1%’---like的语法很精妙,查资料! 排序:select * from table1 order by field1,field2 [desc] 总数:select count as totalcount from table1 求和:select sum(field1) as sumvalue from table1 平均:select avg(field1) as avgvalue from table1 最大:select max(field1) as maxvalue from table1 最小:select min(field1) as minvalue from table1 11、说明:几个高级查询运算词 A:UNION 运算符
数据库复习资料准备 1、创建/删除数据库:Create/ Drop database name 2、创建新表:Create table name(id int not null primary key, name char(20)) // 带主键 Create table name(id int not null, name char(20), primary key (id, name)) // 带复合主键Create table name(id int not null default 0, name char(20)) // 带默认值 3、删除表:Drop table name 4、表中添加一列:Alter table name add column size int 5、添加/删除主键:Alter table name add/drop primary key(size) 6、创建索引:Create [unique] index idxname on tabname(col) 7、删除索引:Drop index idxname 8、选择:Select *from table where 范围 9、删除重复记录Delete from name where id not in (select max(id) from name group by col1) 10、插入:Insert into table(field1, field2) values (value1, value) 11、删除:Delete from table where 范围 12、更新:Update table set field=value where 范围 13、查找:Select *from table where field like “” 14、排序:Select *from table order by field [desc] 15、总数:Select count as totalcount from table 16、求和:Select sum(field) as sumvalue from table 17、平均:Select avg(field) as avgvalue from table 18、最大:Select max(field) as maxvalue from table 19、最小:Select min(field) as minvalue from table 20、复制表:Select * into b from a where 范围Select top 0 * into b from a where 范围 21、拷贝表:Insert into b(a, b, c) select d,e,f from b; 22、子查询: select ename from emp where deptno=(select deptno from dept where loc='NEW');// 单查询select ename from emp where deptno in (select deptno from dept where dname like 'A%');// 多行子查询 select deptno,ename ,sal from emp where (deptno,sal) IN (select deptno,MAX(sal) from emp group by deptno);// 多列子查询 23、外连接查询:Select a.a, a.b, a.c, b.c, b.d, b.f from a left out join b on a.a = b.c 24、between用法:Select a,b,c, from table where a not between 数值1 and 数值2 25、in用法:select * from table1 where a [not] in (‘值1’,‘值2’,‘值4’,‘值6’) 26、两张关联表,删除主表中在副表中没有的信息:delete from table1 where not exists ( select * from table2 where table1.field1=table2.field1) 27、日程提前五分钟提醒:select * from 日程where datediff(‘minute‘,f开始时间,getdate())>5 28、前10条记录:select top 10 * form table1 where 范围 29、包括所有在TableA 中但不在TableB和TableC 中的行:select a from tableA except (select a from tableB) except (select a from tableC) 30、随机取出10条数据:select top 10 * from tablename order by newid() 31、列出数据库里所有的表名:select name from sysobjects where type=‘U’ 32、列出表里的所有的字段:select name from syscolumns where id=object_id(‘TableName’)