SQL Server database table writing operations (database creation, table creation, and statement modification), SQL Server database creation

SQL Server database table writing operations (database creation, table creation, and statement modification), SQL Server database creation

Learning points:

SQL-SQL statements. The head should have courage, and the head should have confidence. To learn, to be proud, to take advantage of opportunities, to be lazy. Three difficulties in life: Thinking, lovesickness, and lovesickness.

SQL-database creation, table creation, constraints, relationships, and some SQL statements

--- Before creating a database, you can first check whether the database already exists. --- if exists (select * from sys. sysdatabases where name = 'constructiondb') begin use master drop database ConstructionDB end go create database ConstructionDB on () if exists (select * from sysobjects where name = 'constructiondb ') -- SEARCH Command drop DATABASE ConstructionDB -- delete command Create database ConstructionDBon (name = 'constructiondb _ date', filename = 'e: \ skill spot check question module 2 (DATABASE) \ question -- 1 \ Task 1 \ ConstructionDB_date.mdf ', size = 3 mb, maxsize = 10 mb, filegrowth = 5% -- the growth rate is) log on (name = 'structiondb _ log ', filename = 'e: \ skill spot check questions module 2 (database) \ questions -- 1 \ Task 1 \ ConstructionDB_date.ldf ', size = 2 mb, maxsize = 5 mb, filegrowth = 1 mb) -- use a T-SQL statement to create a table use ConstructionDBgo --- query whether the table exists in the Database, delete if exists (select * from sysobjects where name = 't_ flow_step_def ') drop table T_flow_step_def --- method 2 IF OBJECT_ID (N 'bas _ cardtype') IS NUL LBEGIN -- if this table does not exist, 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 (30) not null, -- process step name Step_des varchar (64) not null, -- process step description Limit_time int not null, -- Time Limit URL varchar (64) not null, -- level 2 menu link remarks varchar (256) not null,) --- process category table create table T_flow_type (Flow_type_id char (3) not null, -- process category No. Flow_type_name varchar (64) not null ,- -Process category name In_method_id char (3) not null, -- bidding method code In_choice_id char (3) not null, -- project option code remarks varchar (256) not null ,) --- create table T_sub_project (Project_id varchar (32) not null, --- Project No. Sub_pro_id char (2) not null, -- Project No. Flow_type_id char (3) not null, -- process category No. Sub_pro_name varchar (64) not null, -- tender name (tender project name) Usb_no varchar (64) not null, -- password lock No. In_method_id char (3) not null, -- bidding method code: In_scope _ Id char (3) not null, -- bidding Range code: In_choice_id char (3) not null, -- Project Option Code: Proj_type_id char (3) not null, -- Project Property Code: Engi_type_id char (1) not null, -- Project Property Code: Pack_type char (1) not null, --- package method: Grade_type_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 remarks varchar (256) not null ,) -- create a database named 'SQL _ test' create database SQL _testgo -- open the database SQL _testuse SQL _te Stgo -- create table student (student no. char (4) primary key, Student name varchar (50) not null) go -- modify the student table alter table student add class no. char (4) null -- add the class number field -- (note that the added field cannot be added if it is not empty) go -- create table class (class number char (4) primary key, class name varchar (50) not null) go -- create table course (course number char (4) primary key, course name varchar (50) not null, course class date datetime) go -- modify course curriculum alter table course add course code varchar (10) null -- add course code field goalter tabl E course drop column course class date -- delete course class Date Field goalter table course alter column Course name varchar (20) not null -- modify course Name field go -- create a product_test_one table, similar to the next table, but there is a 'comma 'before the constraint, which does not affect the execution of create table product_test_one (id char (10) not null, name varchar (20) null, price money default 20.5, quantity smallint null, constraint pk_id primary key clustered (id) go -- create a product_test_two table create table product_test_two (id char (10) not nul L, name varchar (20) null, price money default 20.5, quantity smallint null constraint pk_id2 primary key clustered (id )) go -- delete the pruduct_test_one table drop table product_test_onego -- create a student table so that the name field has the unique create table student (id char (8), name char (10) -- table field constraint pk_id primary key (id), -- add a primary key constraint uk_name unique (name) -- add a unique constraint) go -- create a student4 table, same as above (note: one between constraint and constraint A comma is required. Otherwise, an error occurs !) 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 student4drop table student4go -- create a student3 table, same as create table student3 (id char (8), name char (10 ), -- table field constraint pk_id3 primary key (id), constraint uk_name3 unique (name) go -- delete table student3drop table student3go -- constraint name check (logical condition expression) -- create an 'employees' table so that the entered sex field can only accept 'M' or 'F ', but cannot accept other data -- and create check constraints for the phone field. Only data similar to 0108564712 can be entered, and other data cannot be input randomly. create table employee (id char (5 ), name char (20), sex char (2), phone intconstraint pk_zid primary key (id), which must be separated by commas, define the primary key 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 name default constraint expression [for field name] -- create a table 'default constraint 'and create default constraint for field sex create table default constraint (id char (5) primary key, sex varchar (2) constraint con_sex default 'M') go -- modify the 'default limited' table alter table default constraint add name varchar (10) null constraint con_name default 'Hello baby' -- add a field 'name ', the default value is 'Hello baby' go -- add eight records to the class table: insert into class values ('bj01', 'class1') insert into class values ('bj02 ', 'second class') insert into class values ('bj03', 'third class') insert into class values ('bj04 ', 'four class') insert into class values ('bj05 ', 'class 5 ') insert into class values ('bj06', 'class 6') insert into class values ('bj07', 'class 7 ') insert into CLASS values ('bj08', '8ban ') go -- show the class so the record select * from class go -- delete the record with the class number in the class Table greater than bj06 delete from class where class number> 'bj06' go -- display the class so the record select * from class go -- add an 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 ', 'four', 'bj02 ') insert into student values ('xs05', 'five', 'bj03 ') insert into student values ('xs06', 'six', 'bj02') insert into student values ('xs07 ', 'seven', 'bj04 ') insert into student values ('x08', 'Eight ', 'bj03') insert into student values ('xs09', 'nine', 'bj04 ') insert into student values ('xs10', 'ten ', 'bj05') insert into student values ('xs11', 'even', 'bj06 ') insert into student values ('xs12', 'twleve', 'bj06') go -- show all student records select * from student go -- connection query select * from student, class where student. class number = Class. class no. go -- the following results are the same as those of the previous query -- select student for the selected connection. student ID, class. class number, student. student name, class. class Name from student, class where student. class number = Class. class number go -- the following results are the same as the previous one -- query the select * from student where class number in (select class number from class where class number = 'bj01 ') go -- same as the preceding query statement, select. student ID,. student name,. class number from student as a, class as B where. class No. = B. class number and B. class No. = 'bj01' go -- count the number of students in a select count (student no) as student statistics from student where class number in (select class number from class where class number = 'bj01 ') go -- group usage and count () function usage-count the number of students in a class, and display the student's name and select count (student ID) as Student Statistics in the class, Student name, class No. from student where class no. in (select class no. from class where class no. = 'bj01') group by class no., Student name go

The preceding section describes how to compile database tables (create databases, create tables, and modify statements) in SqlServer, if you have any questions, please leave a message and the editor will reply to you in time. Thank you very much for your support for the help House website!