SQL Syntax review: Additions and deletions, various database object creation and function usage

Recommended Tools: If the machine configuration is low, you can install gsql this tool to obtain the SQL execution environment (as a manual view built-in data type functions and stored procedures, etc.)

--Before the database things touch not much, although the basic syntax is to understand, but not very familiar with
--recent projects have been tossing stored procedures (some data logic is implemented through stored procedures on the database side),
--A review of the SQL of the east, here to record a bit.

/*
--Create a database
Create DATABASE Test
Use test;
--Create TABLE field definition list primary key foreign key
CREATE TABLE score (ID int primary KEY, student varchar (), Subjectid int foreign key references subject (SID), score int );
*/

/*
--Add Delete query record
Insert INTO score (Id,student,subject,score) VALUES (1, ' Zhangsan ', ' math ', 73);
Delete from score;
SELECT * FROM Score
*/

/*
--Aggregation function avg (), GROUP by grouping, order by sort, having to filter groups
Select Student, AVG (score) as Avgscore from score group by student Order by Avgscore;
Select student from score Group by student have min (score) >80; --Select students with 80 or more grades in each section
*/

/*
--distinct non-repeating, relational operator: (not) between. And.., like '%si ', in (' Zhangsan ', ' Lisi ')
Select distinct student from score;
SELECT * from score where score between and 99;
SELECT * FROM score where score not between and 99;
SELECT * FROM score where score = 93;
SELECT * FROM score where score >86;
SELECT * FROM score where score >=93;
SELECT * FROM score where student like '% four ';
SELECT * FROM score where student in (' Zhang San ', ' John Doe ');
SELECT * from score where is not student in (' Zhang San ', ' John Doe '); --Where not field in (list)
*/

/*
--and, or logical operators
SELECT * FROM score where student= ' Zhang San ' and score >80;
SELECT * FROM score where student = ' Zhang San ' or student= ' John Doe '
*/

/*
--order by F1,F2 Multi-field sorting
SELECT * FROM score where student = ' Zhang San ' or student= ' John Doe ' ORDER by score ASC
SELECT * FROM score where student= ' Zhang San ' or student= ' John Doe ' ORDER by score Desc;

SELECT * FROM score where student= ' Zhang San ' or student= ' John Doe ' ORDER by student, score;
*/

--Insert Update Delete
--insert into score values (7, ' small segment ', ' Math ', 87);

--update score set score=60 where id=7;

--delete from score where score<70;

/*
--select Top 2 Select several previous records
Select top 2 * from score;
Select top percent * from score;--top 50% Records
*/

/*
--like pattern matches wildcard character% _ [] [^] [a-c]
SELECT * FROM score where subject like '% text ';
SELECT * FROM score where subject is not a like '% text ';
SELECT * FROM score where subject like ' _ Language ';
SELECT * FROM score where subject like ' _[, Wen] ';
SELECT * FROM score where subject like ' _[^, Wen] ';
SELECT * FROM customers where City like ' [a-c]% ';
*/

--select * from score;
--select * from Stdinfo;

/*
--As to field aliases or table aliases
Select ID as ' No. ', student as ' name ', subject as ' subject ', score as ' score ' from score;
Select ID as ' No. ', student as [student name] from score;
Select S.student, S.subject, S.score from score as s;
*/

--inner join TBL2 on Tbl1.scoreid=tbl2.scoreid connection Query extension fields and filter records
--select score.student, Score.subject, Score.score, stdinfo.height from score inner join stdinfo on stdinfo.name = SCORE.S Tudent;

/*
--Insert new record full field value list without listing fields
INSERT into Stdinfo values (4, ' small New ', ' n ', ' cm ');
SELECT * from Stdinfo;
*/

/*
--Connection query inner join, left join connected, right Join
Select Score.student, Score.subject, Score.score, stdinfo.height from score inner join stdinfo on stdinfo.name = Score.stu Dent

Select Score.student, Score.subject, Score.score, stdinfo.height from score joins stdinfo on stdinfo.name = score.student;
Select Score.student, Score.subject, Score.score, Stdinfo.height from score left joins Stdinfo on stdinfo.name = Score.stud Ent
Select Score.student, Score.subject, Score.score, stdinfo.height from Stdinfo left joins score on stdinfo.name = Score.stud Ent
Select Score.student, Score.subject, Score.score, stdinfo.height from score right join stdinfo on stdinfo.name = Score.stu Dent
Select Score.student, Score.subject, Score.score, stdinfo.height from Stdinfo right join score on stdinfo.name = Score.stu Dent
Select Score.student, Score.subject, Score.score, stdinfo.height from score full join stdinfo on stdinfo.name = Score.stud Ent
*/

/*
--union result set merging, requiring two result sets for the same number of fields and field types
Select ID, student from score Union select ID, name from Stdinfo;
Select ID, student from score union ALL select ID, name from Stdinfo;
*/

--create database test2;

/*
--query result set stored in new table
SELECT * into Score_back from score;
Insert into Score_back (student,score) select Student,score from score;
*/

/*
--Creating databases and tables
Create DATABASE family;
Use family;
CREATE TABLE person (ID int, lname varchar (), fname varchar (+), address varchar ($), City varchar (20));
select * from person;
*/

/*
-->>sql constraint:
Not NULL, unique, primary key,foreign Key,check, default
*/

The--getdate () function returns the current date time
--select GETDATE () as Date

--create Index Creating indexes
--create index idx_s on score (student);

/*
--create View view_name as SELECT * from TBL Create views
CREATE VIEW MyInfo as SELECT * FROM Score
SELECT * from sysobjects where xtype= ' V '
*/

/*
--GETDATE (), DATEPART ()
Select GETDATE () as date;
Select DATEPART (D, GETDATE ());
*/

/*
--Aggregate function: AVG (), COUNT (), first (), Max (), Min (), SUM ()
Select AVG (Score) as ' Avgscore ' from score;
Select COUNT (*) as Rsamount from score;
Select count (subject) as Subcount from score;
Select COUNT (distinct subject) as Subdiffcount from score;
Select first (score) from score;
Select Max (score) as Maxscore from score;
Select min (score) as Minscore from score;
Select SUM (Score) as Sumscore from score;
*/

/*
--Modify table structure plus fields
ALTER TABLE score add PY varchar (10);
Update score set py= ' Yuwen ' where subject= ' language ';
*/

/*
---character function Len (), Upper (), Lower (), Mid () Math function: Round ()
Select Len (py) as Py from score;
SELECT *, Upper (py) as Py from score;
Update score Set py = upper (py);
Select GETDATE () as date;
Update score Set Py=lower (PY);
Select Mid (student,1,1) as fname from score;
Select Round (score,2) as Score_pre from score;
*/

/*
--View database tables and table structures
Use test
Go
SELECT * from sysobjects where xtype= ' u '
EXEC sp_help Testtbl
SELECT * FROM sys.databases
SELECT @ @version

*/
/*
--OBJECT_ID () Find the ID of the object (from the sysobjects table), OBJECTPROPERTY (Objid,prop)
Select object_id (' Ptest ')

SELECT * from sysobjects

SELECT * from sysobjects where id = object_id (' ptest ') and OBJECTPROPERTY (ID, ' isusertable ') = 1
*/

-------------------------------------------------------

/*
--View the user of the current database
EXEC Sp_helpuser
--View database (built-in database and user-created)
EXEC sp_helpdb
--View the tables of the current database (including: system tables, user tables, and views)
EXEC sp_tables
--View the details of the table (field constraint ...)
exec sp_help ' binarytbl '
--View stored procedure definitions
exec sp_helptext ' MyProc '
--View the index of the specified table
EXEC sp_helpindex Score
--Set the database compatibility level
EXEC sp_dbcmptlevel 80
*/

--Set options for the database
--exec sp_dboption ' test ', ' quoted identifier ', ' on '
--exec sp_dboption

--set QUOTED_IDENTIFIER ON
--use Test

/*
--Test Decimal (3,2)
CREATE TABLE Ptest (F1 decimal (3,2)); --F1 is a 1-bit integer part, and the value of the 2-bit fractional fraction
INSERT into ptest values (2.234545);
INSERT into ptest values (34.4454); --Error will overflow
SELECT * FROM Ptest
*/

/*
--Test bit
drop table bittbl;
CREATE TABLE Bittbl (F1 bit, F2 bit, F3 bit);
INSERT into BITTBL values (12,5,2); --Non-0 for true translates to 1
SELECT * FROM Bittbl
*/

/*
--Test money
CREATE TABLE Moneytbl (F1 money, F2 smallmoney);
INSERT into MONEYTBL values ($234, $23.29);
SELECT * from Moneytbl;
*/

/*
--Test binary, varbinary, image
CREATE TABLE Binarytbl (F1 binary, F2 varbinary (+), F3 image);
INSERT into BINARYTBL values (0x123, 0xFFFF, 0x12fff02);
SELECT * from Binarytbl;
--Modify the data type of the field
ALTER TABLE BINARYTBL ALTER COLUMN F2 varbinary (50);
ALTER TABLE BINARYTBL ALTER COLUMN F1 binary (40);
exec sp_help ' binarytbl '
*/

--Data type conversion cast (@var as datatype)
--select cast (' 2003-03-23 12:02:23.443 ' as smalldatetime) as Smdt

/*
--declaration assignment and printing of variables
DECLARE @nvar nchar (26);
Set @nvar = N ' is a Unicode string ';
Print @nvar;
*/

/*
--newid () function and uniqueidentifier data type convert (datatype, @var) function
Print newid ()
declare @uid uniqueidentifier;
Set @uid = NEWID ();
print ' Value of @uid is: ' + CONVERT (varchar (255), @uid);
*/

/*
--Stored procedures for custom data types sp_addtype
EXEC sp_addtype telephone, ' varchar ', ' NOT NULL '
exec sp_addtype Fax, ' varchar ', ' not NULL ';
EXEC sp_droptype Fax
*/

/*
--Use of variables
declare @mycounter int;
Set @mycounter = 18%5;
Print CONVERT (varchar (255), @mycounter);
*/

The--id field value is ' user '
--select id= ' user ', student,score from score;

/*
--Bitwise operator
declare @a int, @b int;
Set @a = 5;
Set @b = 10;
Select @a & @b, @a | @b, @a^@b;
*/

/*
--variables are used in the WHERE clause
declare @score int, @py varchar (20);
Set @score = 88;
Select @py = ' Shuxue ';
SELECT * FROM score where score> @score and [email protected];
*/

/*
--Variable Assignment Select
SELECT * FROM Dbo.score
declare @sc int;
Select @sc = score from score;
Print @sc;
*/

/*
--Global variables
Print @ @connections
Print @ @cpu_busy
Print @ @datefirst
Print @ @dbts
Print @ @identity
Print @ @langid
Print @ @language

Print @ @max_connections
Print @ @max_precision

Print @ @nestlevel
Print @ @options

Print @ @version
Print @ @spid
*/

/*
--while Control Flow begin: End
DECLARE @i int, @r int;
Set @i = 0;
Set @r = 0;
While @i<=100
Begin
Set @r = @i + @r;
Set @i = @i + 1;
End
Print @r
*/

/*
--Statement label Goto LabelName
DECLARE @i int, @r int
Set @i = 0;
Set @r = 0;
Myloop:
Set @r = @r + @i;
Set @i = @i +1;
If @i<=100
Goto Myloop
Print cast (@r as varchar (20));
*/

--use Test
--go

--Delete stored procedures
--drop proc MyProc

/*
--Get the return value of the stored procedure
DECLARE @stud varchar (), @rst int;
Set @stud = ' Zhang San ';
exec @rst = MyProc @stud--Gets the return value of the stored procedure
If @rst = 1
print ' Very good '
Else
print ' should come on '
Go
*/

--View stored procedure definitions
--exec sp_helptext ' MyProc '

/*
--Create a function
Create function Cubicvolume (@len decimal (4,1), @width decimal (4,1), @h decimal (4,1))
Returns decimal (12,3)
As
Begin
Return (@len * @width * @h)
End
Go

Print cast (Dbo.cubicvolume (20,2,5) as varchar (20));
Go
*/

/*
Create function Searchstud (@score int)
Returns @studInfo table (ID int,student varchar ($), subject varchar), score int,py varchar (10))
As
Begin
Insert @studInfo SELECT * from score where score > @score
Return
End
Go

--drop function Dbo.searchstud
SELECT * FROM Searchstud (88);
*/

/*
Update Dbo.score Set score = the Where id = 3;
SELECT * from score;
*/

/*
--Case-value conversion
Select student as ' student ', subject as ' subject ',
Case
When score >80 and then ' excellent '
When score >70 then ' good '
Else ' fail '
End as ' score '
From score
Go
*/

--sysindexes System Index Table
--select * from Dbo.sysindexes;

/*
Use test
Go

if exists (SELECT * from dbo.sysindexes where name= ' Score_studidx ')
Drop INDEX Score.score_studidx
Go

Create nonclustered index SCORE_STUDIDX on score (student)
Go
*/

--exec Sp_helpuser

/*
--View the index of the specified table, delete the index
EXEC sp_helpindex Score
Drop INDEX Score.score_studidx
*/

SQL Syntax review: Additions and deletions, various database object creation and function usage