16th Lesson-Database development and ado.net-database SQL, creating databases and tables, adding and deleting statements, constraints, top and distinct, aggregation functions

Section - class - database Development and ADO

Database SQl, creating databases and Tables , Adding and deleting statements , Constraints , Top and the Distinct, Introduction to Aggregation functions

Getting Started with SQL statements (scripts, commands)

SQL full name is a structured Query language (structured query Language)

The SOL statement is a language specific to the DBMS "talk", and the SQL syntax is recognized by different DBMS.

strings in SQL use single quotation marks: a single quotation mark is escaped by writing two single quotation marks.

Comment "--" in SQL is better than single line comment

Determine if two data are equal using = (single equals)

SQL code is case insensitive in SQL statements

SQL is mainly divided into:

DDL (data definition language, build table, build library and other languages) (Example: Create table, Drop table, Alter table)

DML (Data Manipulation language) (example: Select, Insert, Update, Delete)

DCL (Database Control Language) (example: Grant authorization, revoke cancellation authorization)

Instance code:

--1. Creating a Database

Create Database School;

--Delete Database

Drop database School;

--Specify some options when creating a database

Create DATABASE School

On primary

(

Name= ' School ',

Filename= ' D:\Desktop\ A place of heart, nothing is impossible. \ Two, programming \ (four) notes \ Dark Horse programmer. NET Video tutorial-time\ lesson-database development and Ado.net\school.mdf ',

SIZE=5MB,

--FILEGROWTH=10MB,

filegrowth=10%,--grow as a percentage of the file

maxsize=100mb

)

Log on

(

Name= ' School_log ',

Filename= ' D:\Desktop\ A place of heart, nothing is impossible. \ Two, programming \ (four) notes \ Dark Horse programmer. NET Video tutorial-time\ lesson-database development and Ado.net\school_log.ldf ',

SIZE=3MB,

filegrowth=3%,

maxsize=20mb

)

--Switch database

Use School;

--Create a student table in the school database. tablestudent

Create Table Tblstudent

(

--The definition of the column in the table in this pair of small scratches

TSId int Identity (primary) key,

Tsname nvarchar (ten) NOT NULL,

Tsgender bit NOT NULL,

Tsaddress nvarchar (300),

Tsphone varchar (100),

Tsage int,

Tsbirthday datetime,

Tscardid varchar (18),

Tsclassid int not null--last line does not add ","

)

Go

--Create a class table

CREATE TABLE Tblclass

(

Tclassid int Identity (primary) key,

Tclassname nvarchar () NOT NULL,

Tclassdesc nvarchar (100)

)

Introduction to the Build Script tool

Database → Right-click → task → generate script

You can choose what kind of script to generate

1. Select the generated database version
2. Whether to include certain scripts, etc.
3. Whether to generate a script with data (2005, 2008 has this feature Express No. ）

Simple Data Insertion INSERT

--Insert the data into a table in the database using the INSERT statement

SELECT * FROM Tblclass

--1.insert inserting a piece of data into a table

Insert into Tblclass (Tclassname, Tclassdesc) VALUES (' Time third ', ' Cherish the Hours ')

--inserting data into the AutoNumber column

SET Identity_insert Tblclass on

Insert into Tblclass (Tclassid, Tclassname, Tclassdesc) VALUES (101, ' time of the third ', ' Cherish the Hours ')

SET Identity_insert Tblclass off

--ctrl+r shortcut keys

--If you want to insert data into all columns in the table other than AutoNumber, you can omit the column name

INSERT into Tblclass values (' Time fourth ', ' Don't be old, you and I don't scatter ')

SELECT * FROM Tblstudent

--inserting data into the Tblstudent table

The definition of a column in a----table in this pair of small scratches

--TSId int identity (primary) key,

--tsname nvarchar (ten) NOT NULL,

--Tsgender bit NOT NULL,

--tsaddress nvarchar (300),

--tsphone varchar (100),

--Tsage int,

--Tsbirthday datetime,

--Tscardid varchar (18),

--Tsclassid int not null--last line does not add ","

Insert into Tblstudent (TSNAME,TSGENDER,TSADDRESS,TSPHONE,TSAGE,TSBIRTHDAY,TSCARDID,TSCLASSID)

VALUES (' Time001 ', 0, ' Shanghai area ', ' 1778918281 ', ' 1989-10-11 ', ' 78267287282819829X ', 1)

INSERT INTO Tblstudent

VALUES (' Time001 ', 0, ' Shanghai area ', ' 1778918281 ', ' 1989-10-11 ', ' 78267287282819829X ', 1)

The--insert statement inserts data into those columns in the table that are not allowed to be empty (Tsclassid is not allowed to be empty and data must be inserted)

Insert into Tblstudent (Tsname,tsgender, tsaddress, Tsclassid)

VALUES (' Time002 ', 0, ' Shanghai ' suburbs ', 2)

--null value means null value

--insert into statements can only insert one record into a table at a time, and if multiple records are inserted into the table through a single statement,

--You need to insert the statement in a different way.

SELECT * FROM Tblclass

--Inserting multiple SQL statements into a table with an SQL statement

Insert into Tblclass (Tclassname, Tclassdesc)

Select ' Time fifth ', ' cherish the Times, the years are not old ' union

Select ' Time fifth ', ' cherish the Times, the years are not old ' union

Select ' Time fifth ', ' cherish the Times, the years are not old ' union

Select ' Time fifth ', ' cherish the Times, the years are not old ' union

Select ' Time fifth ', ' cherish the hours, the years are not old '--the last line does not need union

CREATE TABLE Tblclassbak

(

ClsId int identity (primary key),

Clsname nvarchar () NOT NULL,

Clsdesc nvarchar (+) null

)

SELECT * FROM Tblclassbak

--Import (copy) the data from the Tblclass table into the Tblclassbak table

--Also using INSERT statements

--insert into table (column) Select column, column from table

Insert into Tblclassbak (Clsname, Clsdesc)

Select Tclassname,tclassdesc from Tblclass

--n prefix, in the storage of characters, you must increase the write letter N.

Simple Data Update (data modification)

Use School

SELECT * FROM Tblclass

--UPDATE statement

Update table name set column name = value, column name = value WHERE condition

--When writing an UPDATE statement, the absence of a where condition means that all data in the table is updated to the specified data.

--All student age plus

Update Student Set sage=sage+1

--So write tclassname= ' ▲ ', indicating that the value of this column becomes a ' ▲ '

--and our requirement is to add ' ▲ ' on the original column basis

Update Tblclass set Tclassname=tclassname + ' ▲ ' where Tclassid <100

--Update multiple columns

Update Tblclass set Tclassname=tclassname + ' ▲ ', Tclassdesc =tclassdesc+ ' ★ ' WHERE Tclassid =100

Other operators can be used in--where: (| |) or,<> (! =), (&&) and, (!) Not

Simple Data removal (DELETE)

Delete all data in table: Delete from Student

Delete just deletes the data, the table is still there, and the drop table is different

Delete can also take a whre clause to delete part of the data: example: Delete from student where Saage >20

Truncate table student functions like delete from student

is to delete all the data in the student table, the difference is:

1. The TRUNCATE statement is very efficient. Because the truncate operation uses minimally logged logging, the efficiency is higher. For millions of data to be deleted using truncate for only a few seconds, while using delete takes several hours.

2. The TRUNCATE statement resets the auto-numbering in the table to the default value (where it reverts to the seed), and after deleting the data in the table, it continues to add from the previous data.

3. The TRUNCATE statement does not trigger a delete trigger.

4. Truncate can only delete all the data in the table, cannot add where, and cannot be deleted according to the condition.

--Review

Insert into table (column) values (value)

Update table Set column = value, column = value where ...

--DELETE statement

Delete from table name where ...

SELECT * FROM Tblclass

--delete Tclassid to even data

DELETE from Tblclass where tclassid%2=0

--Delete all data in the table

Delete from Tblclass

TRUNCATE TABLE Tblclass

--Delete Table

drop table Tblclass

16th Lesson-Database development and ado.net-database SQL, creating databases and tables, adding and deleting statements, constraints, top and distinct, aggregation functions