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
- Select the generated database version
- Whether to include certain scripts, etc.
- 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