SQL from zero to quick mastery of "utility function (1)"

Grammar is the basis of a programming language, really want to play 6 to fly or to rely on their own definition of functions and variables.

1. Use the Declare statement to create a local variable named @mycounter with the int data type, and enter the following statement:

DECLARE @mycounter int;

2. Create 3 local variables named @name, @Phone, and @address, and initialize each variable to null, and enter the following statement.

DECLARE @Name varchar (+), @Phone varchar, @Address char (2);

Additional:

difference:
1. The length of the char is fixed, and the length of the VARCHAR2 is changeable, for example, storing the string "abc", for Char (10), which means that the characters you store will account for 10 bytes (including 7 null characters), while the same VARCHAR2 (10) consumes only 3 bytes of length. 10 is the maximum value, which is stored as the actual length when the character you store is less than 10 o'clock. 
2. The efficiency of char is slightly higher than that of VARCHAR2. 
3. Currently varchar is synonymous with VARCHAR2. The industry standard varchar type can store an empty string, but Oracle does not, although it retains the right to do so later. Oracle has developed a data type of VARCHAR2, which is not a standard varchar, and it will store null values in the database in which the varchar column stores the empty string. If you want to have backward compatibility capabilities, Oracle recommends using VARCHAR2 instead of varchar. 
when should I use char and when should I use VARCHAR2? 
Char and VARCHAR2 are a pair of contradictory unity, the two are complementary relations.
VARCHAR2 saves space than Char and is slightly less efficient than char, that is, to be efficient, you have to sacrifice a certain amount of space, which is what we often call "space-for-efficiency" in database design. 
Although VARCHAR2 is more space-saving than char, if a VARCHAR2 column is often modified and the length of each modified data is different, this causes a ' row-migration ' phenomenon, which creates extra I/O, is the database design and adjustment to avoid, in this case, using char instead of VARCHAR2 will be better. 

3. Use the SELECT statement to assign a value to the @mycount variable, and finally output the value of the @mycount variable, and the output statement is as follows.
DECLARE @MyCount INT
SELECT @MyCount =
SELECT @MyCount
GO

4.* assigns a value to a variable through a query statement, the input statement is as follows.
DECLARE @rows int
SET @rows = (SELECT COUNT (*) from Member)
SELECT @rows
GO

5. In the SELECT query statement, use the local variable assigned by set, and the input statement is as follows.
Use test
GO
DECLARE @memberType varchar (100)

6. In the SELECT query statement, use the local variable assigned by set, and the input statement is as follows.
Use test
GO
DECLARE @memberType varchar (100)
SET @memberType = ' VIP '
SELECT RTRIM (FirstName) + ' +rtrim (LastName) as Name, @memberType
From member
GO

Attached: usage of RTrim.

7. Single-line comments and multiline comments

(1) The single-line comment begins with two hyphens "--" and the range is from the beginning of the comment symbol to the end of one line;

(2) Multi-line Annotations Act on a block of code, using (/* content that requires comments */).

8. Create the name test_db database and enter the following statement.

CREATE DATABASE test_db on PRIMARY

(

name = test_db_data1,--Database logical file names

FILENAME = ' C:\SQL Server 2012\test_db_data.mdf ',--primary data file storage location

Size = 5120KB,--Master Data file sizes

MAXSIZE = 20,--the maximum growth space for the master data file is 20MB

FILEGROWTH = 1--File growth size set to 1MB

）

23333 very happy, suddenly found to shift can be the Sogou output method into the English input state, do not have to switch back and forth to get English symbols ************

9. Create the Employee table TB_EMP1 in the test_db database.

Use test_db

CREATE TABLE TB_EMP1

(

ID INT PRIMARY KEY,

Name VARCHAR (+) is not NULL,

DeptID CHAR (2) Not NULL,

Salary SmallMoney NULL

);

10. Delete the Table_emp table from the test_db database and enter the following statement.

Use test_db

GO

DROP TABLE dbo.table_emp--dbo in SQL is the Data manager meaning

11. Delete the test_db database and enter the following syntax.

DROP DATABASE test_db

12. Modify the name of the test_db database to company and enter the following statement.

ALTER DATABASE test_db

MODIFY Name=company

13. In the company database with the changed name, add a field column named birth to the TB_EMP1 data table with a data type of date and a non-empty input statement as follows.

Use Company

GO/*

If you just execute a statement, there's no go all the same
It's different if you separate multiple statements with go.
Each statement that is delimited by Go is a separate transaction, and a statement execution failure does not affect the execution of other statements. */

ALTER TABLE TB_EMP1--Select the database using use, select the table with alter; not just a simple database selection, if you want to manipulate the database then use Select

ADD Birth DATE not NULL

14. Delete The Birth field column in the TB_EMP1 table and enter the following statement:

Use Company

GO

ALTER TABLE TB_EMP1

DROP COLUMN Birth

15. Insert the following demo data--insert

(1) Create a table

CREATE TABLE Teacher

(

ID INT not NULL PRIMARY KEY,

Name VARCHAR (not NULL)

Birthday DATE,

Sex VARCHAR (4),

Cellphone VARCHAR (18)

);

(2) Insert more than one new record into the teacher table, the T-SQL code is as follows.

SELECT * FROM teacher

INSERT into teacher

VALUES (2, ' John Doe ', ' 1978-11-21 ', ' female ', ' 0018624 '),

(3, ' Harry ', ' 1976-12-05 ', ' Male ', ' 0018678 ');

SELECT * FROM teacher

16. "Data Changes" (Update-set) in the Teacher table, update the record with ID value 2, change the Birthday field value to ' 1980-8-8 ', change the cellphone field value to ' 0018600 ', and enter the following statement.

SELECT * FROM teacher WHERE id = 2;

UPDATE Teacher

SET birthday = ' 1980-8-8 ', cellphone= ' 0018600 ' WHERE id = 2;

17. "Modify all records in table" in the teacher table, change all teachers ' phones to ' 01008611 ' and enter the following statements.

UPDATE Teacher

SET cellphone = ' 01008611 ';

18. "Specify Fields in query table" Query stu_info data table the names and grades of students, enter the following statements.

SELECT S_name,s_score from Stu_info;

19. "Use expression in query results" does not modify the data table, query and display the results of all students reduced 5 points, the input statement is as follows.

SELECT s_name,s_score,s_score-5 as New_score from Stu_info;

Feel: SQL is a very human language, almost all of your operations on the table can be manipulated using SQL compound statements.

20. "Show partial query Results"

(1) query all the records in Stu_info, but only show the first 3, enter the following statement.

SELECT TOP 3 * from Stu_info;

(2) A query with a qualified condition (all problems with a qualifying condition can use where or where not) to query the information of all students in the Stu_info table that have sex as ' male ', enter the following statement.

SELECT * from Stu_info WHERE s_sex= ' man ';

(3) To inquire the information of all students who are not male in the Stu_info table, enter as follows.

SELECT * from Stu_info WHERE not s_sex= ' male ';

(4) A little more complicated. You can query operations with and, or, like, between, and with where

① Query the Stu_info table with gender as ' male ' and score greater than 80 student information, enter the following statement.

SELECT * from Stu_info WHERE s_sex= ' Men ' and S_score > 80;

② Query the Stu_info table for students with scores greater than 80, or older than 18, enter the following statement.

SELECT * from Stu_info WHERE s_score>80 OR s_age>18;

③**** using the LIKE operator for matching queries * * * (it's important that I copy someone else's explanation as follows from:http://www.w3school.com.cn/sql/sql_like.asp)

The---------like operator is used to search for a specified pattern in a column in a WHERE clause---------

Like operator

The LIKE operator is used to search for a specified pattern in a column in a WHERE clause.

SQL Like operator syntax

SELECT column_name (s) from Table_namewhere column_name like pattern

The original table (used in the example):

Persons table:

City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Example of Like operator instance 1

Now, we want to choose from the "Persons" table above for people living in cities that start with "N":

We can use the following SELECT statement:

SELECT * from Personswhere city like ' N% '

Tip: "%" can be used to define wildcard characters (missing letters in the pattern).

Result set: City

Id	LastName	FirstName	Address
2	Bush	George	Fifth Avenue	New York

Example 2

Next, we want to select from the "Persons" table the people who live in cities that end with "G":

We can use the following SELECT statement:

SELECT * from Personswhere city like '%g '

Result set: City

Id	LastName	FirstName	Address
3	Carter	Thomas	Changan Street	Beijing

Example 3

Next, we want to select from the "Persons" table the people who live in cities that contain "lon":

We can use the following SELECT statement:

SELECT * from Personswhere city like '%lon% '

Result set: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London

Example 4

By using the NOT keyword, we can select people residing in cities that do not contain "lon" from the "Persons" table:

We can use the following SELECT statement:

SELECT * from Personswhere City does like '%lon% '

Result set: City

Id	LastName	FirstName	Address
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

④between and

Query the Stu_info table for students with scores greater than 50 less than 90, enter the following statement.

SELECT * from Stu_info WHERE s_score between and 90

SQL from zero to quick mastery of "utility function (1)"