SQL Server How to write database tables (build, build, modify statements) _mssql

Learning points:

SQL-Build a database, build a table, build constraints, relational SQL Basic statement encyclopedia. TXT can be lifted up under the weight of lifting, lifting can not be called weight. The head must have the courage, the rise must have the emboldened. Learn to add, pride to reduce, opportunity to multiply, lazy to be apart. Three difficult problems in life: thinking, Acacia, single acacia.

SQL-building, building tables, building constraints, relationships, partial T-SQL statements

---Create library before creating a library to see if there are any secondary databases in the database---if exists (SELECT * from sys.sysdatabases where name= ' constructiondb ') begin U  SE master drop database constructiondb end go to create Database constructiondb on () if exists (select * from sysobjects where name = ' Constructiondb ')--Find command drop database constructiondb--delete command Create DATABASE constructiondb on (name= ' construction Db_date ', filename= ' e:\ Skills Test Second module (database) \ Question--1\ task One \constructiondb_date.mdf ', SIZE=3MB, MAXSIZE=10MB, filegrowth=5%-- Growth rate is) log on (name= ' Constructiondb_log ', filename= ' e:\ skill spot Test Second module (database) \ Question--1\ task One \constructiondb_date.ldf ', SIZE=2MB , MAXSIZE=5MB, FILEGROWTH=1MB)--Use T-SQL statement to create a table using CONSTRUCTIONDB go---Query library if this table exists, delete if exists (SELECT * from Sysobje CTS WHERE name = ' t_flow_step_def ') drop table T_flow_step_def---method two if object_id (N ' Bas_cardtype ') is NULL BEGIN--If not The table is present, the Create--drop table Com_coderecord--Process step definition Table CREATE TABLE t_flow_step_def (step_no int NOT NULL,--Process step ID step_name varchar NOT NULL,--Process step name Step_Des varchar NOT NULL,--Process step description Limit_time int NOT NULL,--time limit URL varchar (+) NOT null--level two menu link comment varchar (256) not n ULL,)---Process category table CREATE TABLE T_flow_type (flow_type_id char (3) NOT NULL,--process category number Flow_type_name varchar () NOT NULL,-- Process category name in_method_id char (3) NOT null--tender method code-named in_choice_id Char (3) NOT null--project option code note varchar (256) NOT NULL,)--- e_id char (3) NOT null--process category number Sub_pro_name varchar () NOT NULL,--the name of the header (tender item name) Usb_no varchar NOT null--password lock number In_m ethod_id char (3) is not null--tender method code-named in_scope_id Char (3) NOT null--tender scope code in_choice_id char (3) NOT null--project option code name Proj _type_id char (3) NOT null--project Property Code engi_type_id char (1) NOT null--engineering Property Code name Pack_type char (1) NOT NULL,---contract mode grade_ty Pe_idv char (1) NOT null,--rating category number Flag_done char (1) NOT NULL,--completion flag Flag_forcebreak char (1) NOT null,--force interrupt flag Note varchar (2 Not NULL,)--Creates a numberAccording to the library name ' sql_test ' CREATE database sql_test go--open databases Sql_test use sql_test Go--Create Student table CREATE table student (student number char (4) Primar Y key, student name varchar (NOT NULL) go--Modify Student table ALTER TABLE student Add Class number char (4) null--Add Class Number field-(Note if the added field is not empty, it cannot be added) G O--Establish class table Create table Class (class number char (4) primary key, class name varchar () NOT NULL) go--Set up Schedule CREATE TABLE course (Course number char (4) PR Imary key, course name varchar (m) not NULL, start date datetime) Go--Modify Schedule ALTER TABLE course add course code varchar NULL--Add Course Code field go alt Er table course drop column Start date--delete the start date field go ALTER TABLE course ALTER COLUMN course name varchar NOT NULL--Modify Course Name field Go--Create a Produ Ct_test_one table, similar to the next table, except that there is a ' comma ' in front of constraint that does not affect the Execute CREATE TABLE Product_test_one (ID char () NOT NULL, name varchar NU ll, Price $ default 20.5,quantity smallint NULL, constraint pk_id PRIMARY key clustered (ID) Go--Create a product_test_t Wo table CREATE table product_test_two (ID char not NULL, name varchar () NULL, price money default 20.5,quantity Smalli NT NULL constraint pk_id2 primary key clustered (ID) Go--Delete table Pruduct_test_one table drop table Product_test_one Go--Create a student table so that the name field is unique CREATE TABLE student (ID char (8), name char (10)-table field constraint pk_id primary key (ID)--Add a PRIMARY KEY constraint constraint Uk_name Unique (name)--Add a Uniqueness constraint Go--Create a student4 table, ditto (NOTE: constraint and constraint must have a comma, otherwise error!) CREATE TABLE Student4 (ID char (8), name char (10)-table field constraint Pk_id4 primary key (ID), constraint uk_name4 unique
(name)) Go--Delete table student4 drop table student4 Go--Create a student3 table, ditto create TABLE Student3 (ID char (8), name char (10),--table field const Raint PK_ID3 primary KEY (ID), constraint uk_name3 unique (name) go--Delete table student3 drop table student3 go--constraint constraint Name Check (logical conditional expression)--Create an ' employee ' table so that the gender field (sex) that it enters can only accept ' m ' or ' f ', not other data--and create a check constraint for the phone field to restrict input to data like 0108564712. You can't enter other data. CREATE TABLE employee (ID char (5), name char (sex char (2), phone int constraint Pk_zid primary key (ID),--Must have ' Comma ' delimited, defining a PRIMARY KEY constraint constraint chk_sex check (sex in (' f ', ' m ')),
Constraint chk_phone check (phone like "(010) [0-9] [0-9] [0-9] [0-9] [0-9] [0-9] [0-9])" Go--constraint constraint name default about bundle expression [for field name]--Creates a table ' default constraint ', creates a default constraint for field sex CREATE TABLE default constraint (ID char (5) primary key, sex varchar (2) Constraint Con_sex D Efault ' m ') go--Modify ' DEFAULT constraint ' table ALTER TABLE DEFAULT constraint add name varchar NULL constraint con_name default ' Hello Baby '--Add a field to ' name ', The default value is ' Hello Baby ' Go-add 8 records to the class table insert into class values (' bj01 ', ' class ') insert into class values (' bj02 ', ' Second class ') INSERT into class values (' B  J03 ', ' Class three ') insert into class values (' bj04 ', ' Class four ') insert into class values (' bj05 ', ' class five ') insert into class values (' bj06 ', ' Class six ') insert into class values (' bj07 ', ' class seven ') insert into class values (' bj08 ', ' eight classes ') Go--show class so record select * FROM class Go--delete class number greater than bj06 record de 
Lete from class where class number > ' bj06 ' Go-show class so record select * FROM class Go-add a record to the student table insert into student values (' xs01 ', ' One ', ' bj01 ') INSERT into student values (' XS02 ', ' two ', ' bj01 ') insert into student values (' Xs03 ', ' three ', ' bj01 ') insert into student values (' Xs04 ', ' fou R ', ' bj02 ') insert into learningBirth values (' Xs05 ', ' Five ', ' bj03 ') insert into student values (' Xs06 ', ' Six ', ' bj02 ') insert into student values (' Xs07 ', ' seven ', ' bj04 ') INSERT into student values (' xs08 ', ' eight ', ' bj03 ') insert into student values (' xs09 ', ' nine ', ' bj04 ') insert into student values (' Xs10 ', ' Te n ', ' bj05 ') insert into student values (' Xs11 ', ' eleven ', ' bj06 ') insert into student values (' xs12 ', ' twleve ', ' bj06 ') go--Show all student Records SEL ECT * FROM student Go-connect query select * FROM student, class where student. Class number = class. The class number go--The following effect is the same as the same--select the student. Student number, Class. Class number, student name Word, class. Class name from student, class where student. Class number = class. The class number go--The following effects are the same--query the students in class select* from student where class number in (select class number from class whe 
Re class number = ' bj01 ') go--function as a query statement above select a. Student number, A. Class number from student as a, class as B where A. Class number =b. class number and B. class numbers = ' bj01 ' Go--Statistics of class number of students select COUNT (student number) as student statistics from student where class number in (select class number from class where class number = ' bj01 ') use and count of Go--group () The use of the function--statistics of the number of students in a class, and displays the name of the student and the class select count (student number) as student statistics, student name, class number from student where class number in (select class number from class where Class)
Number = ' bj01 ') Group by class number, student nameGo 

The above is a small set of SQL Server to introduce the operation of the database table (build the library, build the table, modify the statement), I hope to help you, if you have any questions please give me a message, small series will promptly reply to everyone. Here also thank you very much for the cloud Habitat Community website support!