SQL Serever Learning 10--t-sql Statements

The Transact-SQL language, referred to as T-SQL, is used in sqlserver2018.

Database creation and Management data definition language DDL

DDL features include databases, tables, indexes, views, stored procedures

Databases: CREATE db, DROP database

Table: CREATE table, DROP table, ALTER table

Index: CREATE index, DROP Index

View: CREATE view, DROP view

Database creation

Create DATABASE includes: database name, file location, size, transaction log file location and size

The database definition needs to be noted:

Define the database name, SQL Server, database name up to 128 characters, each system can manage the user database up to 32,767

Define data file, database file minimum is 3MB, file grows 10%, can define multiple data files, default first main file

Defines the log file, in the log on clause, with a minimum length of 1MB

Create a database

The database name is sales management, the file location is saved to the D packing directory under the Sales database folder, the file name is Sales management _data, the initial value is 16MB, each increase to 6MB, the upper limit is 100MB.

The log file name is Sales management _log, with an initial value of 1MB and a maximum of 5MB, up to 10% per cent

/* Create databases */create database sales management on (name= Sales Management _data,filename= ' d:\ sales management database \ Sales Management _data.mdf ', size=16,maxsize=100, filegrowth=6) LOGON (name= sales Management _log,filename= ' d:\ sales management database \ Sales Management _log.ldf ', size=1,maxsize=5,filegrowth=10%) GO

Deleting a database

/* Delete databases */drop database sales management Go

Creation of data tables and creation of management data tables

Each data table has a name, called the table name, or the relationship name, the table name must start with a letter, the maximum length is 30 characters, one table includes several fields, and the field name is unique.

Create 5 tables in the Sales Management database: Commodity table, Buyer's table, buyer level table, commodity Type table, sales table.

/* Switch to the database */use sales Management go/* Create a Product table */create table (Product number CHAR (3), Product name varchar (50), Brand varchar (20), model varchar (20),
Type char (3), price money, sales price money, inventory INT) go/* Create buyer table */create table Buyer Table (buyer number CHAR (3), buyer name varchar (50), phone varchar (20), Level CHAR (3) go/* Create buyer Level table */create table Buyer level sheet (level number CHAR (3), Level name VARCHAR (50), enjoy discount FLOAT, privileged VARCHAR ()) go/* Create commodity type table */create tables Commodity type table (type number char (3), type name varchar (50), level varchar) go/* Create sales table */create Table Sales table (ID INT, item number char (3), buyer number char (3), actual sales price Money, Sales date DATETIME, sales quantity INT) GO

Add constraint

When you create a table, you can add constraints after the field, but generally not, the recommendation is to add constraints and create tables separated from the statement, first create the data table, and then modify the structure of the data table to add constraints.

There are 5 commonly used constraints:

1. PRIMARY KEY constraint, which is to add a primary key that requires the primary key field to be unique and not empty
2. Unique constraint, requires field unique
3. Check constraints, which are check constraints, restrict field formatting
4. Default constraints, which are default constraints, give field defaults
5. A FOREIGN KEY constraint is a foreign constraint, a relationship between 2 tables

I just created the data table and now we're going to add a constraint to the data table.

Add a PRIMARY KEY constraint

Buyer's Table's buyer number color is set as the primary key, the product number of the commodity table is set as the primary key

Note: A field that is set as the primary key requires not NULL

/* Switch to the database */use sales Management go/* Commodity table */alter Table Product table Add CONSTRAINT pk_ Item number PRIMARY KEY (product code)/* Buyer table */alter Table Buyer Table Add CONSTRAINT P K_ Buyer number PRIMARY KEY (buyer number)

Add a DEFAULT constraint

Set the brand name of the product list defaults think a card

/* Switch to database */use sales Management go/* Commodity Table */alter Table list add CONSTRAINT df_ brand DEFAULT (' a card ') for Brand

Add a Conditional constraint

Ask for the price of the commodity table >0, the buyer's form of the buyer number is m** format

/* Switch to the database */use sales Management go/* commodity sheet */alter Table table add CONSTRAINT ck_ price CHECK (input >0)/* Buyer table */alter Table Buyer Table Add CONSTRAINT ck_ number CHECK (buyer number like ' m__ ')

Add a FOREIGN KEY constraint

Establish an association between the buyer's and buyer's type tables, and create a list of commodity and commodity types

Note: Foreign key table can not have data, otherwise error!

/* Switch to the database */use sales management go/* Commodity Tables */alter Table Commodity table add CONSTRAINT fk_sp FOREIGN KEY (type) REFERENCES commodity type table (type number) go/* buyer table */alter tabl E Buyer table Add CONSTRAINT fk_mj FOREIGN KEY (level) REFERENCES Buyer Level table (level number) GO

Deletion of data tables

Delete Buyer Table

/* Switch to database */use sales Management go/* buyer table */drop table Buyer form Go

Creation and management of views

Because many of the company's daily work is regular, if you introduce some view mechanism, it will greatly improve work efficiency.

Create a View

Create a high-priced view of the goods, show all the price of more than 4000 yuan in the name of the product, brand and price

/* Switch to the database */use sales Management go/* Create a View */create view high price commodity _viewasselect product name, brand, price from commodity table where price >4000go

Working with views

See the name of a high-priced item

/* Switch to database */use sales Management go/* use view */select product name from high-priced goods _viewgo

Delete a view

Delete high-priced items view

/* Delete views */drop View High-priced items _view

SQL Serever Learning 10--t-sql Statements