Sql-server notes

--Shortcut: Off display result: ctrl+r
--First, CREATE TABLE, delete table
/*--Creating a Table
--Format:
CREATE TABLE Table name
(
Field name data type [constraint (Identity (;p rimary key)],
Field Name 2 data type [constraint]

)
*/

/*
--Deleting databases and tables
Drop database name
DROP table Name
*/

/*
--Create TABLE student tables
CREATE TABLE Tblstudent
(
TSId int Identity (primary) key,
Tsname nvarchar () NOT NULL,
Tsgender nchar (1),
Tsage int,
Tsbirthday datetime,
Tscardid VARCHAR (18),
Tsclassid INT

)
*/

/*
--Create TABLE classroom tables
CREATE TABLE Tblclass
(
Tclassid int Identity (primary) key,
Tclassname nvarchar (50)

)
*/

/*
--Create TABLE score tables
CREATE TABLE Tblscore
(
Tscoreid int Identity (primary) key,
TSid int NOT NULL,
Tenglish float,
Tmath float
)
*/

Use HeiMal3
--Create TABLE teacher tables
CREATE TABLE Tblteacher
(
Ttid int Identity (primary) key,
Ttname nvarchar () NOT NULL,
Ttgender nchar (1),
Ttage int,
Ttsalary Money,
Ttbirthday datetime,
Ttjiondate datetime
)

/*
--Create a departmental table
CREATE TABLE Departments
(
Depid int Identity (primary) key,
Depname nvarchar (+) null
)
*/

/*
--Create an employee table
--< Employee form: Employee ID, ID number, name, gender, entry date, age, address, phone number. Department, email
CREATE TABLE Employees
(
EmpID int Identity (primary) key,
Empidcard varchar () NOT NULL,
EmpName nvarchar () null,
Empgender bit NOT NULL,
Empjiondate datetime,
Empage int,
Empaddress nvarchar (300),
Empphone varchar (100),
Empmaill varchar (100),
DeptID int NOT NULL,


)
*/

--Create TABLE human tables
CREATE TABLE Tblperson
(
autoid int Identity (primary) key,
UName nvarchar (10),
Age int,
Height int,
Gendder bit
)

--Second, insert Table
--Format:
--insert into table name (column 1, column 2, column 3) VALUES (value 1, value 2, value 3)
--Continuous insertion of multiple lines
--insert into table name (column 1, column 2, column 3) VALUES (value 1, value 2, value 3), (value 1, value 2, value 3), (value 1, value 2, value 3)

--Insert a record into the class table
--AutoNumber columns, which automatically grow by default, so you don't need to (by default, you can't insert values into AutoNumber columns)
/*
Insert into Tblclass (tclassname) VALUES ('. NET black horse phase ')

--the query is displayed.
SELECT * FROM Tblclass
*/
/*
--Insert a record into the student table
INSERT INTO
Tblstudent (TSNAME,TSGENDER,TSAGE,TSBIRTHDAY,TSCARDID,TSCLASSID)
VALUES (' Xiongli ', ' female ', ' Beijing Hai Ding District ', 16, ' 1998-5-5 ', ' 123456789654123698 ', 1)
*/
/*

INSERT INTO
Tblstudent (TSNAME,TSGENDER,TSADDRESS,TSAGE,TSBIRTHDAY,TSCARDID,TSCLASSID)
Values (' Liu Tianlong ', ' Male ', ' Beijing Sea Nail Zone ', 17, ' 1997-5-5 ', ' 12345784663135 ', 1)

INSERT INTO
Tblstudent
Values (' Liu Tianlong 12 ', ' Male ', ' Beijing Sea Nail Zone ', 17, ' 1997-5-5 ', ' 12345784663135 ', 1)
SELECT * FROM Tblstudent

Insert into Tblstudent (tsname,tsgender,tsage)
VALUES (' Hecun ', ' female ', 15)
*/
/*
--inserting values into AutoNumber (not inserted)
Insert into Tblclass (tclassid,tclassname)
Values ($, '. NET Dark Horse two period ')
--Method
--inserting values into the AutoNumber column
--the ability to manually insert a value by starting the AutoNumber column for a table
SET Identity_insert Tblclass on
Insert into Tblclass (tclassid,tclassname)
Values ($, '. NET Dark Horse two period ')
SET Identity_insert Tblclass off
SELECT * FROM Tblclass

*/
Insert into Tblclass (tclassname)
VALUES ('. NET Dark Horse three ')

--After the string is Chinese, the front plus n
Insert into Tblclass (tclassname)
VALUES (N '. NET Dark Horse four period ')

--Third, update the list
--Format:
--update table Name Set column = new value, column 2 = new value 2,......where condition

Use HeiMal3
SELECT * FROM Tblstudent

--If you do not add a where condition, it means that all data in the table is modified, so be sure to add the Where condition

Update tblstudent set tsage=tsage-1,tsname=tsname+ ' (female) ' where tsgender= ' woman '

--update table name set age=18 where name= ' Wangcan ' or age<25
--update table Name set age=30 where (Age>20and age<30) or age=50

--Four, delete the data statement:
--delete from table name where ...
The--delete statement, without a where condition, means that all data in the table is deleted, and when the where condition is added, it is only deleted by the Where condition

SELECT * FROM Tblstudent

--Delete Table student table, do not need to add after delete plus * Unconditionally Delete, only when the query with the * number
Delete from Tblstudent

INSERT INTO
Tblstudent
Values (' Liu Tianlong 12 ', ' Male ', ' Beijing Sea Nail Zone ', 17, ' 1997-5-5 ', ' 12345784663135 ', 1)
SELECT * FROM Tblstudent

--Delete all genders as ' women ', colleagues younger than 20 years old

Delete from tblstudent where tsgender= ' woman ' and tsage<20

--delete just delete the data, the table is still there, and the drop is different

--Delete all data from the table:
--1.delete from table
--2.truncate Table Tables
--If you are sure that you want to delete all the data in the table, we recommend using truncate
--truncate Features:
The--1>truncate statement cannot be followed by a where condition (cannot be deleted by condition, only all data can be deleted)
--2> is also automatically numbered back to the initial value. Delete cannot revert to initial value
--3> using truncate to delete all data in a table is more efficient than delete
--4>truncate delete data without triggering a delete trigger.
SELECT * FROM Tblteacher

/*
--inserting data into the Tblteacher
Insert into Tblteacher (ttname,ttgender,ttage,ttsalary,ttbirthday,ttjiondate)
VALUES (' Sukhumvit ', ' Male ', 18,10060, ' 1990-09-09 ', ' 2010-12-15 ')
Insert into Tblteacher (ttname,ttgender,ttage,ttsalary,ttbirthday,ttjiondate)
VALUES (' King II ', ' Male ', 18,10060, ' 1990-09-09 ', ' 2010-12-15 '),
(' John Doe ', ' Male ', 18,10060, ' 1990-09-09 ', ' 2010-12-15 '),
(' Su Yakun ', ' Male ', 18,10060, ' 1990-09-09 ', ' 2010-12-15 '),
(' Su Dakun ', ' Male ', 18,10060, ' 1990-09-09 ', ' 2010-12-15 '),
(' Li Mei ', ' female ', 19,30060, ' 1995-09-09 ', ' 2015-12-15 ')
*/

--1. Inserting 2 data into the Tblteacher table using INSERT INTO
SELECT * FROM Tblteacher
INSERT INTO Tblteacher
Values (' Liu Qi ', ' Male ', 30,150000, ' 1983-10-10 ', ' 2012-5-8 ')

--2. Insert 2 data into the Tblperson table.

SELECT * FROM Tblperson
INSERT INTO Tblperson
VALUES (' Hundred Chuan Son ', 21,175, 1), (' Xiao Li ', 22,170,0)

--3. The StudentID in Tblscore is 1 of the English score plus 10 points.
SELECT * FROM Tblscore
INSERT INTO Tblscore
VALUES (1,96,85), (2,93,88), (1,98,88)

Update Tblscore set tenglish=tenglish+10 where tsid=1

--3.1 to Tblscore in the StudentID is 1 of English scores plus 10 points, scores can not exceed 100.
--with a score of more than 100, set to 100
--3.2 give a score of not more than 100, plus 10 points per person
SELECT * FROM Tblscore
Update Tblscore set tenglish=100 where (tenglish+10>100 or tenglish+10=100)
Update Tblscore set tenglish=tenglish+10 where tenglish+10<100

--4. Age of all boys ' shoes reduced by 1 years
SELECT * FROM Tblstudent
Update tblstudent set tsage=tsage-1 where tsgender= ' man '

--5. Delete a teacher with a salary greater than 2000
SELECT * FROM Tblteacher
Delete from Tblteacher where ttsalary>20000

--6. Delete the table, restore the value of the autogrow column to a seed (revert to the default value, starting at the beginning of the state, starting at 1, adding 1 each time)
TRUNCATE TABLE Tblteacher

--Iv. Establishment of constraints

--Create a departmental table
CREATE TABLE Department
(
Depid int identity (+),
Depname varchar (50)
)

--Create an employee table

CREATE TABLE Employees
(
EmpId int identity (+),
EmpName varchar (50),
Empgender char (2),
Empage int,
Empmail varchar (100),
Empaddress varchar (500),

)

SELECT * FROM Employees
SELECT * FROM Department

--4.1 non-null constraint: cannot be empty
--4.2 PRIMARY KEY constraint (PK) PRIMARY KEY constraint unique and cannot be empty
--4.3 Unique constraint (UQ) Unique constraint only, allowed to be empty, but only once
--4.4 default constraint (DF) default constraint defaults
--4.5 Check Constraint (Ck) check constraint range and format limits
--4.6 FOREIGN KEY constraint (FK) FOREIGN key constraint table relationship

---Note: When a value in the primary key table is referenced by a foreign key table, the value in the primary key is not deleted unless the foreign key table does not reference the primary key table

/*
--Initialization table
TRUNCATE TABLE Tblscore
TRUNCATE TABLE Tblstudent
*/

--Five, set constraints through SQL

/*--Delete table, re-create table
drop table Department
drop table Employees
*/

--5.1 Modify the table structure. Delete one of the columns
--Structure:
--alter Table Name drop column empaddress
ALTER TABLE Employees drop column empaddress
SELECT * FROM Employees

--5.2 add a column empaddr nvarchar (1000)
--Add the default column to the table, so do not write column
ALTER TABLE Employees add empaddr nvarchar (1000)

--5.3 Modify table, the data type of Empemaill is varchar (200)

ALTER TABLE Employees ALTER COLUMN empmail varchar (200)

--5.4 Add a PRIMARY KEY constraint for Empid
ALTER TABLE Employees ADD constraint Pk_employees_empid primary key (EMPID)

--5.5 adds a non-null constraint to EmpName (modifies the column, modified by NULL to NOT NULL)

ALTER TABLE Employees ALTER COLUMN empname varchar (NOT NULL)

--5.6 add unique constraint to EmpName

ALTER TABLE Employees add constraint uq_employees_empname unique (empname)

--5.7 adds a default constraint for gender, which defaults to ' male '
ALTER TABLE Employees add constraint df_employees_empgender default (' Male ') for Empgender

--5.8 a check constraint for sex, requiring sex to be: ' Male ' or ' female '
ALTER TABLE Employees add constraint ck_employees_empgender check (empgender= ' male ' or empgender= ' female ')
--A CHECK constraint for age: Age must be between 0--120 years.
ALTER TABLE Employees add constraint ck_employees_empage check (empage>=0 and empage<=120)

SELECT * FROM Department
SELECT * FROM Employees
--5.9 sets the primary key for the Department Table Department table, and the primary key column is: Depid

ALTER TABLE Department ADD constraint Pk_department_depid primary key (Depid)

ALTER TABLE Employees ALTER COLUMN

--5.10 Add a depid to the employee table by adding a foreign key
ALTER TABLE Employees drop column Depid
ALTER TABLE Employees add empdepid int NOT NULL

--5.11 adding foreign KEY constraints
ALTER TABLE Employees add constraint fk_employees_department foreign key (empdepid) references Department (depid)

--5.12 Deleting a constraint
--Format:
--alter table Employees Drop constraint foreign key 1, foreign key 2, foreign key 3,
ALTER TABLE Employees DROP constraint Fkemployees_department,pk_department_depid

--5.13 adding multiple constraints through a single piece of code
--Format:
--
ALTER TABLE Employees add
Constraint fk_employees_department foreign KEY (empdepid) references Department (depid),
Constraint Pk_department_depid primary KEY (Depid),
Constraint ck_employees_empage Check (empage>=0 and empage<=120)

---Note: When creating a table, the constraint is added

drop table Department
drop table Employees
CREATE TABLE Department
(
Depid int Identity (primary) key,
Depname varchar () NOT NULL unique
)

--Create a departmental table
CREATE TABLE Department
(
Depid int Identity (primary) key,
Depname varchar () NOT NULL unique
)

--Create an employee table

CREATE TABLE Employees
(
EmpId int Identity (primary) key,
EmpName varchar () NOT NULL unique check (len (empname) >2),
Empgender char (2) Default (' Male '),
empage int Check (empage>0 and empage<120),
Empmail varchar (+) Unique,
empaddress varchar (+) NOT NULL,
empdepid int foreign key references Department (depid) on DELETE cascade
--empdepid int foreign key references Department (depid) on DELETE cascade//Cascade Delete can be implemented

)

--Six, data query (* * *)
--Show all rows, all columns
--* represents all Columns
--there is no where condition in the query statement, which means querying all rows
--executes the FROM statement before executing the SELECT statement
SELECT *
From Tblteacher

--Query only some columns
Select Ttid,ttname,ttgender from Tblteacher

--according to the conditions, only the branch of the inquiry
SELECT * FROM Tblteacher where ttid=5

--Aliases the query results, or omit as
Select Ttid as number, ttname as name, Ttgender as gender from Tblteacher

--format transform for easy viewing
Select
Ttid as ' (number) ',
Ttname as name,
Ttgender as Sex
From Tblteacher

--Note: Not select must be used with from
--Get the current time
Select GETDATE () current system time

--Seven. Remove duplicates
--distinct is to remove duplicates after the results have been queried
SELECT * FROM Tblteacher

SELECT DISTINCT * from Tblteacher

Select distinct Ttgender from Tblteacher

--Eight, Top
--top are generally used with order by

------------sort
--order By column name
--8.1 sorted by age, descending order
Update Tblteacher set ttage=16 where ttid=1
SELECT * from Tblteacher ORDER BY ttage Desc

--8.2 by age, ascending order,
--NOTE: The default is ascending sort
SELECT * from Tblteacher ORDER by Ttage

--After sorting, how many columns before filtering
--Show the top 2 top followed by the * number
Select Top 2 * from Tblteacher ORDER by Ttage

--Display the first 20% data, rounding up
Select top percent * from Tblteacher ORDER BY ttage Desc

--Nine, aggregation function--the aggregation of multiple bars must first be classified. First grouping and then counting

--9.1 Statistics The sum of the ages of all----sum ()
SELECT * FROM Tblteacher
Select SUM (ttage) as age sum from Tblteacher

How many records are in the--9.2 statistics----count ()
Select COUNT (*) from Tblteacher

--9.3 Calculate average Age---avg ()
Select AVG (ttage) as average age from tblteacher
Select AVG (ttage*1.0) as average age from tblteacher
Select ((select SUM (ttage) as age sum from Tblteacher) *1.0/(select COUNT (*) as Total from Tblteacher)) as average age
--9.4 Calculating Age Max--max ()
Select Max (ttage) as maximum age from Tblteacher

--9.4 Calculating Age minimum--min ()
Select min (ttage) as minimum age from Tblteacher

Some other issues with------aggregation functions
--aggregate function does not count null value--count (), AVG () does not count null, SUM () calculates null value by default to 0
--select count (ttage) from Tblteacher

--If the aggregate function is not manually grouped with group by, then the aggregate function will count the data in the entire table as a group.

--10, conditional query ——————————————————————————————
--Format:
--select column
--from table
--where conditions

SELECT * FROM Tblscore

Insert into Tblscore (Tsid,tenglish,tmath)
VALUES (1,85,42),
(2,85,80),
(3,35,90)

INSERT INTO Tblscore
VALUES (1,45,56)
--Search for non-pass studies
SELECT * from Tblscore where tenglish<60 or tmath<60

--C inquiry age included male classmates between 20--30 years (including 20 and 30)
SELECT * from Tblstudent where tsage>=20 and tsage<=30 and tsgender= ' men '
SELECT * from Tblstudent where tsage between and tsgender= ' men '

--Check out all the students whose class ID is 3,4,5
SELECT * from tblstudent where Tsclassid =3 or Tsclassid =4 or Tsclassid =5
--Simplified version
SELECT * from Tblstudent where Tsclassid in (3,4,5)

--If the subsequent values are continuous, simplify to the following code (interval, execution speed is efficient)
SELECT * from Tblstudent where Tsclassid >=3 and tsclassid<=5

--note: For in or or queries, if the criteria in the query are several consecutive numbers, it is best to use >= <= or between and
--do not use or or in. Increase efficiency

--11, fuzzy query (mainly for strings)

--Wildcard characters: _ (underline),%, [], ^
--:_ (underscore) denotes any single character

--check surname Zhang, three words (two underline)
SELECT * from Tblteacher where ttname like ' Sue __ '

--no matter the name of the word, as long as the Soviet opening can
SELECT * from Tblteacher where ttname like ' su% '

SELECT * from Tblteacher where ttname like ' su% ' and LEN (ttname) =2

--:[] (brackets) indicates the range
--ttname: Zhang A Rain (female) (female) (female) (removed)
Update Tblteacher set Ttname =replace (Ttname, ' (female) ', ')

--Query the beginning, sister end, the middle is the number

SELECT * from Tblteacher where ttname like ' Zhang [0-9] sister '
SELECT * from Tblteacher where ttname like ' Zhang [a-z] sister '
SELECT * from Tblteacher where ttname like ' Zhang _ Mei '

It's all right, it's not a number.
SELECT * from Tblteacher where ttname like ' Zhang [^0-9] sister '

--The data has a name that contains%. How to query (to escape) [put in brackets]
SELECT * from Tblteacher where ttname like '%\%% '--error
SELECT * from Tblteacher where ttname like '%[%]% '--error

--where ColumnA like '%5/%% ' escape '/'
SELECT * from Tblteacher where ttname like '%/%% ' escape '/'

SELECT * from Tblteacher where ttname like '%/]% ' escape '/'
SELECT * from Tblteacher where ttname like '%/[% ' escape '/'
SELECT * from Tblteacher where ttname like '%/[%/]% ' escape '/'

--12, empty value processing

SELECT * FROM Tblteacher

--Query the teacher's table for null values in age
SELECT * from Tblteacher where ttage=null--cannot be queried

--Query the teacher's table that is not empty in age
SELECT * from Tblteacher where ttage<>null--query does not come out
SELECT * from Tblteacher where ttage=18--query out
SELECT * from Tblteacher where ttage<>18--query out

--NOTE: null value is null (unknown), cannot be compared using = or <>
--The null value must be determined using is NULL or is not NULL
SELECT * from Tblteacher where ttage is null
SELECT * from Tblteacher where ttage are NOT null

--any value and NULL calculation are NULL

---13, order by sort
--1. Descending order BY column name Desc
--2. Ascending order BY column name ASC or ORDER by column name (ascending by default)
The--3.order by statement must always be placed at the end of the entire SQL statement
--select * FROM table name
--where ...
--gourp by ...
--haing ...
--order by ...

--4. Sorting by multiple columns
--First ranked according to English scores, and then by math scores
--Sort by the English score first, and then the math score
SELECT * from Tblscore ORDER by Tenglish Desc,tmath desc

--Add a column to Tblscore (average score)
ALTER TABLE Tblscore add TAVG int
Update Tblscore set tavg= (Tenglish+tmath)/2 where Tscoreid =6
Select *from Tblscore

-when displayed, add an average of
SELECT *, (Tenglish+tmath)/2 as Average

From Tblscore
ORDER BY average DESC

--Notes: Execution order
SELECT *---3 execution
From Tblscore---1 execute first
where tenglish>=60 and tmath>=60----2 execution
ORDER by Tenglish Desc,tmath desc---4 last

--top is generally used in conjunction with ORDER by

--13th Chapter data grouping

Use HeiMal3

--grouping is generally combined with aggregation functions
--Summary statistics of the data
--Grouping classes and counting the number of classes
--Query class ID and class size for each class

Select
Tsclassid as Class ID,
COUNT (*) as class number
From Tblstudent
GROUP BY Tsclassid

--What are the numbers of the male and female classmates in all the students ' tables?

Select
Tsgender as Sex,
COUNT (*) as number
From Tblstudent
GROUP BY Tsgender

--to count the class ID of each class in the student's table and the number of male students in the class
Select
Tsclassid as Class ID,
COUNT (*) as the number of male students
From Tblstudent
Where tsgender= ' man '
GROUP BY Tsclassid
--Filter first, then group

---only columns in the group appear
--
Select
SUM (tsage) age,
Tsgender as Sex,
COUNT (*) as number

From Tblstudent
GROUP BY Tsgender
--When a grouping statement (group BY) or aggregate function is used, no aliases for other columns can be included in the query list for select.
--Unless the column also appears in the group by child statement, or the column contains an aggregate function

The 14th chapter has----to filter the data after grouping:
--having and where are filtering data, where is the filtering of each row of data in a group, and
--having is to filter each group of data after grouping
Select
Tsclassid as Class ID,
COUNT (*) as the number of male students
From Tblstudent
Where tsgender= ' man '
GROUP BY Tsclassid
Having COUNT (*) >5
ORDER BY male number ASC--Sort
--5>select 5.1 Select column, 5.2>distinct,5.3>top (apply top option last calculated)
--1>from table
--2>where conditions
--3>group by column
--4>having Filter Criteria
--6>order by column

--Note: The order in which the SELECT statement comes out
--The following shows the order in which the SELECT statement is processed
--1.from
--2.on
--3.join
--4.where
--5.group by
--6.with |cube or with rollup
--7.having
--8.select
--9.distinct
--10.order by
--11.top

------------Test------------------------
/*

Select Product Name,
SUM (sales quantity) as Sales
From MyOrder
Group BY Product Name
Order by sum (sales quantity) desc

Select Product Name,
SUM (Sales quantity * sales price) as the total price
From MyOrder
Group BY Product Name
Having sum (sales quantity * sales price) >3000
Order by SUM (sales quantity * sales price) desc

Select Purchaser,
SUM (sales quantity) as Coca-Cola quantity
From MyOrder
where Product name = ' Coca-Cola '
GROUP BY buyers

*/

--When you use the result of a select query as a subsequent query, you must have an alias
--Must alias!!!!!!!!!!!!!!
/*
Select
SUM (T. Sales data) Love degree,
T. Purchaser Customer

From (SELECT * from MyOrder where product name = ' Coca-Cola ') as T
Group by T. Purchaser
ORDER BY favorite Desc
*/

Sql-server notes