Getting Started with 1.SQL statements

--Getting Started with SQL statements----1.sql language is interpreted language--2. It is case insensitive--3. No "", all characters or strings are used ' contains--4.sql also has an operator similar to C #--arithmetic operators: +-*/%--relational Operators:> < >= <= = (Assignment is equal to logical =), <> ! =--logical operator:! (not) && (and) | | (OR)--5. In SQL, there is no concept of bool value, it means that the condition can not write true/false but for a bit type of value, in the view can only enter True/false, but in the code can only input/0--6. There is also an if in SQL. Else there's a while loop--7. It also has some reserved keywords: object address user var--8.sql is not strict on type requirements, so types can be converted to strings for processing---Create DATABASE:----database name----Logical name--name--Initial Size--size--file Growth---filegrowth--file path--filename--Syntax:--CREATE database name--On file group--(----Database name- -----Logical name--name----Initial size--size----File growth---filegrowth----File path--filename--)--Log on--(----Database name- -----Logical name--name----Initial size--size----File growth---filegrowth----File path--filename--)--automatically create folder execute execution xp--extends procedureexecsp_configure'Show advanced Options',1GoReconfigureGoexecsp_configure'xp_cmdshell',1GoReconfigureGoExecutexp_cmdshell'mkdir d:\project'--determine if the database already exists, and if it exists, delete it before creating UseMaster--because the database records are stored in the sysdatabases of the Master library.if exists(Select *  fromsysdatabaseswhereName='MyBase')--exists is a function that is used to determine whether the result set in () is null if NULL. Returns FALSE, otherwise true Drop DatabaseMyBase--drop is used to unregister the structureGoCreate DatabaseMyBase--Specify the database name on Primary --The default is the primary filegroup(Name='Mybase_data',--logical name When a statement is not a sentence that can be executed independently, it needs to be added, which is often a sentence in a block of statementsSize=3MB,--Initial SizeFileGrowth=Ten%,--file growth, each time the capacity of the previous growth%MaxSize=1000MB,--limit the maximum capacity of a fileFileName='D:\project\MyBase_data.mdf' --file full path, you must specify the file extension, the last sentence does not need to be added,), filegroup mygroup--Create a filegroup, the next database will be created on this filegroup(Name='mybase_data1',--logical name When a statement is not a sentence that can be executed independently, it needs to be added, which is often a sentence in a block of statementsSize=3MB,--Initial SizeFileGrowth=Ten%,--file growth, each time the capacity of the previous growth%MaxSize=1000MB,--limit the maximum capacity of a fileFileName='E:\AA\MYBASE_DATA1.NDF' --file full path, you must specify the file extension, the last sentence does not need to be added,)Log  on(Name='Mybase_log',--logical name When a statement is not a sentence that can be executed independently, it needs to be added, which is often a sentence in a block of statementsSize=3MB,--Initial SizeFileGrowth=Ten%,--file growth, each time the capacity of the previous growth%, log files generally do not limit file sizeFileName='D:\project\MyBase_log.ldf' --file full path, you must specify the file extension, the last sentence does not need to be added,), (name='Mybase_log1',--logical name When a statement is not a sentence that can be executed independently, it needs to be added, which is often a sentence in a block of statementsSize=3MB,--Initial SizeFileGrowth=Ten%,--file growth, each time the capacity of the previous growth%, log files generally do not limit file sizeFileName='D:\project\MyBase_log1.ldf' --file full path, you must specify the file extension, the last sentence does not need to be added,)--Create a data table--Syntax:--CREATE TABLE table name--(--the characteristics of the field Name Type field (identifies whether the column can be a null primary key unique key check constraint),--rank in no particular order--the characteristics of the field Name Type field (identifies whether the column can be a null primary key, a unique key, a foreign key check constraint)--)--Student:id (student number, auto number, primary key), name (student name), Gender (gender), address (home address), phone (phone), age (Ages), Birthday (date of birth) CardId (Social Security number), CID (class ID) UseMyBaseif exists(Select *  fromsysobjectswhereName='Student')--if the if must be handled in SQLbegin--print ' data table exists '--default contains only one sentenceDrop TableStudentEndGo--token for end of batch processingCreate TableStudent (Idint Primary Key Identity(1,1) ,--The first parameter is the identity seed, the second parameter is the identity incrementNamenvarchar( -) not NULL,--Be sure to remember to set the length for the field of the character type, otherwise the length default is not NULL is the word printable null--Must give the valueGenderChar(2) not NULL,[Address] nvarchar( -)NULL,--if NULL can be set to NULL, or simply do not write, it may indicate that the value of this field is nullPhoneChar( -), ageint Check(age>0  andAge< -), CardIdChar( -) not NULL, Birthdaydatetime  not NULL, CIdint  not NULL)--DECO UseMyschoolmoredataif exists(Select * fromsysobjectswhereName='juststudent')begin Drop TablejuststudentEndGoCreate Tablejuststudent (Idint Primary Key Identity(1,1), Namenvarchar( -) not NULL, GenderChar(2) not NULL,[Address] nvarchar( -)NULL, PhoneChar( -), CarridChar( -) not NULL, Birthdaydatetime  not NULL, Cidint  not NULL)

Getting Started with 1.SQL statements