数据库sqlite3常用指令

数据库sqlite3常用指令 数据库 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=’D0001’AND wages>ANY (SELECT wages FROM employee WHERE department_id=’D002’) 3、连接查询 (1) 内链接 SELECT * FROM department INNER JOIN employee.department_id=department_id (2)、外连接(不符合条件的用空表示(左，左表)) SELECT a.employee_id,a.employee_name,b.goods_id,b.order_num,b.send_date FROM employee a LEFT(RIGHT、FULL) OUTER JOIN Sell_order b ON a.employee_id = b.employee_id (3)、交叉连接 SELECT a.employee_id,a.employee_name,b.goods_id,b.order_num,b.send_date FROM employee a CROSS JOIN Sell_order b 索引 1、创建非聚集索引 CREATE NONCLUSTERED INDEX name_idx ON employee(employee_name) 2、查看索引 USE Sales GO EXEC sp_helpindex employee GO 3、编辑索引 USE Sales GO EXEC sp_rename ‘employee.employee_index_1’,’employee.index_id’,’index’ 4、删除索引 USE Sales IF EXISTS(SELECT name FROM sysindexes WHERE name=’employee_index_2’) DORP INDEX employee.employee_index_2 视图 1、创建视图 CREATE VIEW sell_view AS SELECT employee.employee_name, employee.employee_id,sell_order.order_num, Sell_order.discount,goods.goods_name,goods.unit_price FROM employee INNER JOIN sell_order ON Employee.employee_id=sell_order.employee_id INNER JOIN Goods ON sell_order.goods_id = goods.goods_id 2、删除视图 USE Sales IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VAEWS WHERE TABLE_NAME=’view1’) DORP VIEW view1 3、使用系统存储过程查看视图 Sp_helptext v_employee_department 4、使用存储过程重命名视图 Sp_rename v_employee_department,view_e_d 5、通过视图检索表数据 SELECT goods_name FROM sell_view WHERE employee_name=’前大力’ ORDER BY order_date 6、通过视图添加表数据 INSERT INTO goods_view VALUES (‘G0008’,’DVD’,’P002’,343) 7、通过视图修改表数据 UPDATE goods_view SET classification_id=’P004’ 8、通过视图删除表数据 DELETE FROM goods_view WHERE goods_name LIKE Canon% 规则 1、创建规则 CREATE RULE hire_rule AS @hire_date>=’1989-01-01’and @hire_data<=getdate(); 2、用系统存储过程查看规则 EXECUTE sp_helptext hire_data_rule 3、规则的绑定与松绑 EXEC sp_bindrule hire_date_rule,’empoyee.hire_date’ //绑定 EXEC sp_unbindrule’employee.hire_date’ 4、删除规则 DORP RULE sex_rule,hire_date_rule 默认值 1、创建默认值 CREATE DEFAULT birthday_defa AS getday() 2、查看默认值 EXEC sp_helptext today_defa 3、默认值的绑定与松绑 同rule 4、删除默认值 DROP DEFAULT birthday_defa 主键约束 CREATE TABLE Sales.dbo.customer (customer_id bigint NOT NULL IDENTITY(0,1) PRIMARY KEY, Linkman_name varchar(50) NOT NULL CONSTRAINT l_linkman_name UNIQUE NONCLUSTERED ) CREATE TABLE employees (emp_id char(8), Emp_name char(10) NOT NULL DEFAULT (‘科大’), Emp_cardid char(18), Emp_count int , CONSTRAINT pk_emp_id PRIMARY KEY(emp_id), CONSTRAINT uk_emp_cardid UNIQUE (emp_cardid) CONSTRAINT chk_emp_count CHECK(emp_count >=10) )