Create, Modify, and Delete Tables

6,6 Tạo, Sửa đổi, và Xóa Bàn Nó là phổ biến trong các ứng dụng cơ sở dữ liệu để lập trình tạo, chỉnh sửa, và xoá các bảng. Làm thế nào để làm điều này bằng cách sử dụng T-SQL? Kỹ thuật Thực hiện các nhiệm vụ này, bạn sẽ sử dụng CREATE TABLE, ALTER TABLE, | Create Modify and Delete Tables It is common in database applications to programmatically create modify and delete tables. How do I do this using T-SQL Technique To perform these tasks you will use the CREATE TABLE ALTER TABLE and DROP TABLE T-SQL statements. With these statements you can handle any requirements that your application might have. Look at these statements one at a time. Creating a Table Using CREATE TABLE With the CREATE TABLE statement not only can you specify columns and their data types but you also can specify indexes check constraints and other table level properties. For this How-To you will be use the following T-SQL statement CREATE TABLE ListsExample ListID smallint IDENTITY 1 1 PRIMARY KEY CLUSTERED LastName varchar 50 NOT NULL FirstName varchar 50 NOT NULL Age smallint DateEntered datetime NOT NULL DEFAULT GetDate CHECK DateEntered GetDate This statement shows how to perform a number of different tasks 1. Specify the name of the table 2. CREATE TABLE ListsExample 3. Create an Identity column which is the primary key 4. ListID smallint IDENTITY 1 1 PRIMARY KEY CLUSTERED 5. Create fields that can t be NULL and using different data types 6. LastName varchar 50 NOT NULL 7. FirstName varchar 50 NOT NULL 8. Age smallint 9. Create a field with the Default Value set and with a Check Constraint specified. 10. DateEntered datetime NOT NULL DEFAULT GetDate 11. CHECK DateEntered GetDate Modifying a Table Using ALTER TABLE The example T-SQL statement used for modifying the table in this How-To is pretty simple in that it adds a column and removes drops a column ALTER TABLE ListsExample ADD MyNewColumn varchar 30 ALTER TABLE ListsExample DROP COLUMN Age You can perform quite a few other tasks with this statement. You can even see by the syntax displayed here that you can handle many tasks including dropping constraints. ALTER TABLE table ALTER COLUMN column_name new_data_type precision scale COLLATE collation_name NULL NOT NULL ADD DROP ROWGUIDCOL .