数据库sqlite3常用指令.docVIP

数据库 1、创建数据库 CREATE DATABASE Sales ON (NAME=Sales_dat, FILENAME=’C:\DataBase\salesdat.mdf’, SIZE=10000KB, MAXSIZE=500000KB, FILEGROWTH=5%) LOG ON (NAME=’Sales_log’, FILENAME=’C:\DataBase\saleslog.ldf’, SIZE=5, MAXSIZE=25, FILEGROWTH=5) 2、修改数据库(更改数据库名) ALTER DATABASE Sales MODIFY NAME=NewSales 或 EXEC sp_renamedb ‘Sales’,’NewSales’ 3、向数据库添加(日志)文件 ALTER DATABASE Sales ADD (LOG)FILE (NAME=Sales_dat2, FILENAME=’C:\Program File\Data\Sales2.(ldf)ndf’, SIZE=5, MAXSIZE=100, FILEGROWTH=5) 4、在数据库中删除文件 ALTER DATABASE Sales REMOVE FILE Sales2_dat 5、更改数据库文件 ALTER DATABASE Sales MODIFY FILE (NAME=SalesGIF_dat, SIZE=20) 6、删除数据库 DROP DATABASE Sales,…..、 表 1、创建表 USE Sales GO CREATE TABLE employee (employee_id char(4) NOT NULL, employee_name char(8) NOT NULL, sex char(2) NOT NULL, birth_char(2) NOT NULL ) 2、为表指定文件组 CREATE TABLE Sales.dbo.supplier (supplier_id int identity(1,1) NOT NULL, Supplier_name varchar(50) NOT NULL, Linkman_name varchar(8) NOT NULL, )ON [PRIMARY] 3、更改表，添加新列，然后删除此列 ALTER TABLE employee ADD email varchar(20) NULL GO Sp_help employee //查看 ALTER TABLE employee DROP COLUMN email GO Sp_help employe 4、更改表列的内容 ALTER TABLE employee ALTER COLUMN address varchar(150) NOT NULL //把address改为varchar类型 且不为空 5、删除表 USE Sales GO DROP TABLE employee 6、向表中插入数据 INSERT Sales.dbo.Supplier (supplier_id,supplier_name,linkman_name,address,telephone) VALUES (‘S001’,’华科电子有限科技公司’,’施彬彬’，’朝阳路’,’2434234’) SET IDENTITY_INSERT Sales.dbo.Customer ON VALUES(‘-100’,’’,’’,’’) //即可对表示符列更改 7、更改表中数据 UPDATE Customer SET linkman_name=’佚名’,address=NULL,telephone=’’ WHERE supplier_id=2 8、删除数据 USE Sales GO DELETE Customer //将表中所有行删除 TRUNCATE TABLE customer //清空表中的所有数据，并将标识置为种子 数据查询 1、查询并统计、汇总 SELECT department_id, wages from employee WHERE department_id=’D0001’ ORDER BY department_id COMPUTE sum(wages) 2、单值嵌套查询 SELECT employee FROM employee WHERE department_id=(SELECT department_id FROM department WHERE department_name=’市场部’) 3、多值嵌套查询 SELECT employee_id,wages FROM employee WHERE department_id=’D