SQL Server review notes 01

-- 1. basic operations on a table: -- create a table book1create table book1 (number char (8) Not null, ibsn char (13) not null, name nvarchar (255), price money, press nvarchar (255), publication date datetime) Create Table book2 (number char (8) Not null, ibsn char (13) not null, title nvarchar (255), pricing money, press nvarchar (255), publication date datetime) Create Table teacher (Author No. Char (4) default '001', author name nvarchar (200) not null, Gender char (2) not null default 'male', title char (20), contact number char (10), No. C Har (8) Not null) Create Table Booking (number char (8) Not null, ibsn char (13 )) -- View table structure exec sp_help book1 -- add a column in the table to use bookalter table book1add author varchar null -- delete a column alter table book1drop column author -- modify the column data type alter table book1alter column pricing smallmoney -- View table content select * From book1 -- insert into book1 values ('20140901 ', '123', 'SQL database', 35, 'Sun Yat-sen ', '03-13-2008 ') -- insert into book1 (ID, ibsn, title) into the complete table structure) values ('20140901 ', '123', 'java programming ') -- insert some columns in the table -- modify the table content update book1 set Title = 'jsp dynamic web page parts' where ibsn = '000000' -- delete the table content delete from book1 where ibsn = '2016' -- delete all table content delete book1delete from book1 -- delete table drop table book1 -- 2. constraint Condition -- 1, check -- add constraint alter table teacher add constraint ch_teacher check (Gender = 'male' or gender = 'female ')/* at this time, if the inserted gender is not male or female, the following error message 547, level 16, status 0, and row 2nd conflict with the check constraint "ch_teacher" is returned. This conflict occurs in the Database "book", table "DBO. Teacher", column 'gender '. The statement has been terminated. */Insert into teacher (Author name, gender, number) values ('22', 'ds ', '2 ') -- delete the constraint alter table teacher drop constraint ch_teacher -- after deleting the constraint, insert into teacher (Author name, gender, number) values ('22', 'ds ', '2') -- 2. default -- add constraint alter table teacher add constraint de _ gender default 'male' for gender -- delete constraint alter table teacher drop constraint de _ gender -- 3, primary Key -- add constraint alter table teacher add constraint pri _ Author Number Primary Key clustered (Author Number)/* message 8111, level 16, status 1, and 2nd rows cannot define the primary key constraint for null columns in the 'teacher' table. Message 1750, level 16, status 0, 2nd rows cannot create constraints. See the preceding error message. View the table structure. You can find that the author number can be null and cannot be used as the primary key. modify it as follows: */-- add the constraint alter table teacher add constraint pri _ number primary key clustered (number) -- delete the constraint alter table teacher drop constraint pri _ number -- 4, foreign key -- creates a foreign key for booking. The primary key is added to the constraint for _ number foreign key in book1 alter table Booking (No) references book1 (No.)/* the following error occurs: Message 1776, level 16, status 0, in the referenced table 'book1', row 2nd does not have a primary key or candidate key that matches the list of referenced columns in the foreign key 'for _ number. Message 1750, level 16, status 0, 2nd rows cannot create constraints. See the preceding error message. Because no primary key constraint is set for "Number" in the book1 table, first set */alter table book1add constraint pri _ number primary key clustered (number) -- then set foreign key as follows: alter table booking add constraint for _ No. Foreign key (No.) References book1 (No)
Exec sp_help booking
Course Materials: SQL .server.2005.data warehouse basics and practical technical courses and training .zhou Qianyi)