Create, create, and modify SQL statements

-- Create a database named 'SQL _ Test'
Create database SQL _test
Go
-- Open the database SQL _test
Use SQL _test
Go

-- Create a student table
Create table student
(Student ID char (4) primary key, Student name varchar (50) not null)
Go

-- Modify the student table
Alter table student
Add class number char (4) null -- add class number field
-- (Note that if the added field is not empty, it cannot be added)
Go

-- Create a class table
Create table class
(Class number char (4) primary key, class name varchar (50) not null)
Go

-- Create a curriculum
Create table Course
(Course No. char (4) primary key, course name varchar (50) not null, course class date datetime)
Go

-- Modify the course schedule
Alter table Course
Add course code varchar (10) null -- add course code field
Go

Alter table Course
Drop column course class date -- delete course class Date Field
Go

Alter table Course
Alter column Course name varchar (20) not null -- modify course Name field
Go

-- Create a product_test_one table, which is similar to the next table, but there is a comma before the constraint, which does not affect execution.
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
/*
Add the 'primary key constraint '. Statement: constraint pk_id primary key clustered (id)
The name of the constraint, primary key clustered (field name). clustered is the clustered index, which is also the default value of the system.
*/
Create table product_test_two
(
Id char (10) not null, 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_one
Go

/* The uniqueness constraint is used to specify the combination values of one or more columns to be unique,
To prevent repeated values from being input in a column, no column can have only one primary key. Therefore, when a table already has a primary key,
If you need to ensure that other identifiers are unique, you can use the unique constraint */

-- Create a student table to make the name field 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 unique constraint
)
Go

-- Create a student4 table, same as above (Note: there must be a comma between constraint and constraint, otherwise an error will occur !)
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, same as above
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 student3
Drop table student3
Go

/* Check constraints: Set check conditions for values in the input column or the entire table to restrict input values and ensure database integrity.
Multiple check constraints can be defined in a table. Only one check constraint can be defined for each field in each table;
Check constraints must be defined as table-level constraints when multiple fields are defined;
When an insert statement or an update statement is executed, the check constraint verifies the data. The check constraint cannot contain subqueries */
-- Constraint name check (logical condition expression)

-- Create an 'employees' table so that the sex field entered by the table can only accept 'M' or 'F', but cannot accept other data
-- Create check constraints for the phone field. Only data similar to 0108564712 can be entered, and other data cannot be entered at will.
Create table employee
(
Id char (5), name char (20), sex char (2), phone int
Constraint pk_zid primary key (id), -- separate them with commas (,) to define primary key constraints.
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

/* The default constraint specifies that if the input value is not provided in the insert operation, the system automatically specifies the value.
Default constraints can include constants, functions, built-in functions without variable elements, or null values.
Each field can only define one default constraint,
If the default value is longer than the allowed length of the corresponding field, the default value entered in the table will be truncated.
Cannot be added to a field with the identity attribute or timestamp Data Type
If a field is defined as a user-defined data type and is bound to this data type by default, no default constraints are allowed for this field.
*/
-- Constraint name default constraint expression [for field name]

-- Create a table 'default limited' to create a default constraint for the field sex.
Default create table Constraint
(
Id char (5) primary key, sex varchar (2) constraint con_sex default 'M'
)
Go

-- Modify the 'default limited' table
Alter table default Constraints
Add name varchar (10) null constraint con_name default 'Hello baby' -- add a field 'name', default value: 'Hello baby'
Go

/* Use the system stored procedure sp_help to view table information. You can also specify the information of database objects,
You can also provide information about the system or user-defined data types. The sq_help stored procedure is only used for the current database,
If the object name is not specified, the system defaults to the current database name, object owner, and object type.
Syntax example: sq_help [[@ objname =] name] */

-- Add 8 records to the class table
Insert into CLASS values ('bj01', 'class1 ')
Insert into CLASS values ('bj02', 'second class ')
Insert into CLASS values ('bj03', 'three class ')
Insert into CLASS values ('bj04 ', 'class 4 ')
Insert into CLASS values ('bj05 ', 'class 5 ')
Insert into CLASS values ('bj06', 'class 6 ')
Insert into CLASS values ('bj07', '7 ')
Insert into CLASS values ('bj08', '8ban ')
Go
-- Display the class so records
Select * from Class
Go
-- Delete records with class numbers greater than bj06 in the class table
Delete from class where class no.> 'bj06'
Go
-- Display the class so records
Select * from Class
Go

-- Add records 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 ', '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 ('xs08', '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
-- Display all student records
Select * from student
Go

-- Connection Query
Select * from student, class where student. Class number = Class. Class number
Go

-- The following results are the same as the previous one.
/*
-- First Effect
Select * from student a, Class B where a. Class number = B. Class number
Go
-- Second Effect
Select * from student as a, class as B where a. Class No. = B. Class No.
Go
*/

-- Query the selected connection
Select student. 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.
/*
-- First Effect
Select a. Student ID, B. Class number, a. Student name, B. class name from student a, Class B where a. Class number = B. Class number
Go
-- Second Effect
Select a. Student ID, B. Class number, a. Student name, B. class name from student as a, class as B where a. Class number = B. Class number
Go
*/
-- Query the students in the first class
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 the first class
Select count (student ID) 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

/* When an aggregate function appears in the select statement and other fields exist, the group by clause must exist,
And write each field in select in group by. Otherwise, an error occurs.
For example, the following statement is incorrect:
Select count (student ID) as Student Statistics, Student name, class number from student
Where Class number in (select class number from class where class number = 'bj01 ')
Group by Student name
Go
*/
-- Count the number of students in the first class, and display the student's name and class
Select count (student ID) 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 name
Go