SQL Database language Basics

Creation of tables:

1. Create column (field): Column name + type

2. Set Primary key columns: uniquely identifies a single piece of data

3. set Unique: content cannot be duplicated

4. foreign key relationship:

A table (from a table) in which a column refers to a primary key column from another table (the primary table)

Design Table:

The three main paradigms of the database:

1. First paradigm: (Atomicity of each column)

Each column is the smallest atom that is non-detachable in a program.

2. Second paradigm: (each column is related to the primary key column)

3. the third paradigm: (each column is directly related to the primary key)

FK foreign key PK primary Key

T-SQL statement:

Creating databases: Create DATABASEmytest

Working with databases (switching databases) : Use database library name

Database: A program used to access data.

ACCESS,sql SERVER,MYSQL,Oracle

Databases: Services, interfaces

When storing data, the database is also used as a tabular method:

Column (field):

Line (record):

To create a database:

Main file: There is only one . mdf

Log file:

Secondary data files:. ndf

Attaching and detaching a database

Detach: Find the database, right click -- task -- Detach, Popup dialog, click OK

After the separation, you can copy the database files to other computers D:\Program Files\Microsoft SQL Server\mssql10. Mssqlserver\mssql\data

Attach: Select database, right -- append - -Popup dialog - - Select attached file - - Click Add to find the file you want to attach - - OK

Backup and restore of databases

1. Creating database: Create database name

2. using the database: Use database name

3. Create a data table:

CREATE TABLE Renyuan

(

Code varchar (primary key),

Name varchar (unique),

Sex bit,

Nation varchar () NOT NULL references Minzu (code),

Birthday datetime

)

CREATE TABLE Minzu

(

Code varchar (primary key),

Name varchar (50)

)

CREATE TABLE Friends

(

IDS int identity primary Key,

Mcode varchar (50),

FCode varchar (50)

)

Self-growing columns:

Go: If multiple statements are to be executed together, then the GO keyword will be added after each statement

When creating a foreign-key table, create a primary table and create a table

Key words:

Primary key primary key

Unique Uniqueness Key

Not NULL non-null

References foreign key Relationship (reference)

Identity self-growth

IDS int identity primary Key,

Identity self-growth

Primary key primary key

Unique Uniqueness Key

Not NULL non-null

References foreign key (reference)

1. Delete a table

drop table Student

2. Modify the table

ALTER TABLE Renyuan add CC int

ALTER TABLE Renyuan drop column CC

3. Deleting a database

Drop Database Ceshi

CRUD Operations

Create Add data read data update modify data Delete Remove Data

1. Add Data

INSERT into Nation values (' n002 ', ' hui ')

INSERT into Nation values (' n003 ', ')

Insert into Nation (code,name) VALUES (' n004 ', ' Uighur ')

INSERT into Friends values (' p001 ', ' p007 ')

2. Delete Data

Delete from Nation deletes all

Delete from Friends where ids = 5

3. Modify the Data

Update Friends set fcode= ' p016 ' modifies all

Update Friends set fcode= ' p006 ', mcode= ' P002 ' where ids=4

Inquire:

1. Simple Query

SELECT * FROM Info-- check all data

Select Code,name from Info-- Check the data for the specified column

Select Code as ' code ', name as ' from Info '- - assign alias to column

2. Conditional Query

SELECT * from Info where code= ' p001 '

SELECT * from Info where sex= ' true ' and nation= ' n001 '-- multi-conditional and relational

SELECT * from Info where sex= ' true ' or nation= ' n001 '-- multi-condition or relationship

3. Scope Query

SELECT * from Car where price>40 and price<50

SELECT * from Car where price between and 50

4. Discrete Query

SELECT * from Car where Code in (' c001 ', ' c005 ', ' c010 ', ' c015 ')

SELECT * from Car where Code not in (' c001 ', ' c005 ', ' c010 ', ' c015 ')

5. Fuzzy Query

SELECT * from Car where Name like '% BMW % '-- check contains BMW's

SELECT * from Car where Name like ' BMW % '-- to check the start of BMW

SELECT * from Car where Name like '% BMW '-- Check the end of BMW

SELECT * from Car where Name like ' BMW '-- check equals BMW's

SELECT * from Car where Name like ' __e% '-- Check the third character is E 's

% represents any number of characters

_ represents a character

6. Sort Queries

SELECT * FROM Car order by price ASC-in ascending order of prices

SELECT * from Car ORDER BY price desc-- in descending order of prices

SELECT * from Car ORDER BY oil desc,price ASC- sorted in two fields, preceded by a secondary condition after the main condition

7. Paging Query

Select Top 5 * from Car

Select Top 5 * from car where Code not in (select Top 5 Code from car)

Current page :p age = 2; each page shows: row = ten;

Select Top row * from car where Code not in (select Top (page-1) *row Code from car)

8. go to re-query

Select distinct Brand from Car

9. Group Queries

Select brand from Car GROUP by Brand has count (*) >2

aggregate Functions ( statistical Queries )

Select COUNT (*) from Car-- query all data bars

Select COUNT (Code) from Car-- query all data bars

Select SUM (Price) from Car-- sum

Select AVG (price) from Car-- averaging

Select Max from Car-- maximum value

Select min (Price) from Car-- minimum value

Use Zuoye

CREATE table

A

Advanced Query

1. Connection Query

SELECT * FROM Info,nation--to form a Cartesian product

SELECT * from info,nation where info.nation = Nation.code

Select Info.code,info.name,sex,nation.name,birthday from info,nation where info.nation = Nation.code

SELECT * FROM Info join Nation in info.nation = Nation.code--join on Form

2. Joint Queries

Select Code,name from Info

Union

Select Code,name from Nation

3. Sub-query

An SQL statement contains two queries, one of which is the parent query (the outer query), the other is a subquery (the inner query), and The result of the subquery query is the condition of the parent query.

-- Query the people for the Han nationality of all personnel information

SELECT * from Info where Nation = (select Code from Nation where Name = ' han ')

(1) unrelated subqueries

Subqueries can be executed independently, and the subquery does not have a relationship with the parent query

-- Inquiry series is All the car information of BMW 5 system

SELECT * from Car where brand = (select Brand_Code from Brand where brand_name = ' BMW 5 series ')

(2) Related sub-query

-- find cars with fuel consumption below the average fuel consumption of the series

SELECT * from Car where oil< ( average fuel consumption for this series )

Select AVG (oil) from Car where Brand = ( this series )

SELECT * from Car a where oil< (select Avg. from car b where B.brand = A.brand)

SQL Database language Basics