SQL server2008 0 Basic Learning

Introduction to the SQL Foundation database:

A database is a warehouse that organizes, stores, and manages data according to its structure, which is generated more than 50 years ago, with the development of information technology and markets, especially after the 1990s, data management is no longer just a way to store and manage data, but to transform it into the kind of data management that users need. There are many types of databases, ranging from the simplest tables with various data to large database systems that can store massive amounts of data, are widely used in all aspects.

Database Common Products

SQL Server Database

A relational database system introduced by Microsoft Corporation in the United States. SQL Server is a scalable, high-performance database management system designed for distributed client/server computing, which integrates organically with WindowsNT and provides a business-level information management system solution based on transaction.

Its main features are as follows:

(1) High-performance design, can make full use of the advantages of windowsnt.

(2) Advanced system Management, support Windows graphical management tools, support local and remote system management and configuration.

(3) Strong transaction processing function, the use of various methods to ensure the integrity of the data.

(4) Supports symmetric multiprocessor architecture, stored procedures, ODBC, and has an autonomous SQL language. SQL Server provides a superior database platform for users, developers, and system integrators with its built-in data replication capabilities, powerful management tools, tight integration with the Internet, and an open system architecture.

Install SQL Server enable SA user Telnet

Set Password

Turn on the SA user.

Click Properties to enable remote connections.

Restart the service to make it effective.

Command: Services.msc

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

The 1433 port is open. When we close the service, the port will also be closed.

Database language

Data Query Language DQL

DQL: (data query Language) SELECT queries language

Data Manipulation Language DML

such as Insert,delete,update,select (INSERT, delete, modify, retrieve)

Data Definition Language DDL

such as Drop,alter,truncate, are all DDL.

Data Control Language DCL

The DCL (Data Control Language) is a database control language. is a statement that is used to set or change permissions for a database user or role, including (Grant,deny,revoke, etc.) statements. By default, only people such as Sysadmin,dbcreator,db_owner or db_securityadmin have the power to execute the DCL

Create a first database

New Database

Limit growth:

The file suffix of the database

Ydxx.mdf

Log file suffix

Ydxx _log.ldf

Data type

First big class: Integer data

The Bit:bit data type represents 0,1 or null, which means true,false. Consumes 1byte.

int: with 4 bytes to store the positive and negative numbers. Available Storage range: -2^31 to 2^31-1.

SmallInt: Stores the positive and negative numbers in 2 bytes. Storage range: -2^15 to 2^15-1

Tinyint: is the smallest integer type, with only 1 bytes, range: 0 so ^8-1

Second class: Accurate numerical data

Numeric: The number represented can reach 38 bits, and the number of bytes used to store the data varies with the number of bits used for use.

Decimal: Similar to numeric

Class Three: Approximate floating-point numeric data

float: with 8 bytes to store the data. can be up to bit. range is: -1.79E+308 to 1.79E+308.

Real: 24 digits, 4 bytes, numeric range: -3.04E+38 to 3.04E+38

Fourth Category: Date-time data

Datatime: indicates that the time range can be represented from the 1753/1/1 to 9999/12/31, time can be expressed to 3.33/1000 seconds. Use 8 a byte.

smalldatetime: Indicates that the time range can be expressed from 1900/1/1 to 2079/12/31. Use 4 bytes.

Category five: String data

Char: the length is set, Minimum of 1 bytes, the longest is 8000 a byte. the insufficient length is blank.

varchar: The length is also set, Minimum of 1 bytes, the longest is 8000 bytes, the trailing blanks are removed.

Text: the length of the width is also set, can hold up to 2G maximum the data.

Category six: Unincode string data

NCHAR: The length is set, the shortest is 1 bytes, and the maximum is 4,000 bytes. The length of the gap is blank. Storing one character requires 2 bytes.

nvarchar: the length is set, Minimum of 1 bytes, the longest is 4000 a byte. the trailing blanks are removed. storing one character requires 2 a byte.

ntext: The length is set, the shortest is 1 bytes, the longest is 2G. The trailing blanks are removed and 2 bytes are required to store a single character.

Seventh Category: Currency data types

Money : The amount recorded is: -92233720368577.5808 to 92233720368577.5807. need 8 a byte.

SmallMoney: The recorded amount ranges from 214748.3648 to 214748.36487. Requires 4 bytes.

Eighth Class: Tag data

Timestamp: This data type is unique in every table! When one record in the table changes, the timestamp field of the record is automatically updated.

uniqueidentifier: The only record used to identify many tables within a database.

Nineth Category: Binary character string data

Binary: fixed-length binary string field with a minimum of 1 and a maximum of 8000.

varbinary: With binary difference is the data tail is 00 o'clock, varbinary will remove it

Image: is a variable-length binary code string, The maximum length is 2G.

Primary key

To build a database in a continuous phase

Detach database table, table name: BJ (class meaning)

Refine your form

Underlying query SQL comment (not executed)

First Type: Single line

SELECT * FROM BJ--select * from BJ

The second type: multirow

SELECT * FROM BJ/*select * from bj*/

Check all:

Select * FROM table name

SELECT * FROM BJ

Querying a specified column

Select column name, column name from table name

Select Name,sex,age from BJ

Conditional query WHERE clause

SELECT column name from table name where condition

Select*from bj where name= ' goat big fairy '

Check all records older than 10 years

SELECT * from BJ where age>10

Number of query owners (count aggregate function)

Select COUNT (*) from BJ

Range query (between)

Select*from BJ where ID between 2 and 8

Check all records not equal to 23 years of age

SELECT * from BJ where age<> 23

Fuzzy query (like)

1, the percent semicolon represents all but does not include the empty

SELECT * from BJ where address like '% '

2, match the middle

Select*from BJ where phone like '%22% '

3. Check that the name contains the name of the sheep

SELECT * from BJ where name like '% sheep% '

4, the query number contains "9"

SELECT * from BJ where phone like '%9% '

, 5 matches the Beginning (contains "1" before matching the phone number)

Select*from BJ where phone like ' 1% '

6. Match end (match phone number with "4" at end)

Select*from BJ where phone like '%4 '

7, Match start and end (beginning with "1", End "4")

Select*from BJ where phone like ' 1%4 '

8, match a single character (match a number followed by 33).

Select*from BJ where phone like ' _33% '

9, match multiple characters

Select*from BJ where phone like ' _[a-z-1-9]7% '

1

10, mismatch

Select*from BJ where phone like ' _! [8] [!7]% '

Query null

1, query non-null

Select*from BJ where phone is not null

2. Querying for null values

Select*from BJ where address is null

Logical Query

1,and Query

Select*from bj where name= ' goat big fairy ' and sex= ' man '

2,or Query

Select*from bj where name= ' goat big fairy ' or sex= ' woman '

3,or for queries

SELECT * from BJ where id=5 or id=7 or id=1

3,in Query

SELECT * from BJ where id=5 or id=7 or ID in (1,3,5,7)

4,not in Query

SELECT * FROM BJ where ID not in (1,3,5,7)

Top words

1, query the first two lines

SELECT TOP 3 * from BJ

2 additional statements

2.1: Query first three row ID is greater than 6 and ID is not equal to 7 not equal to 8 value

SELECT TOP 3 * from BJ where id>6 and Id<>7 and id<>8

2.2: Query first three row ID is greater than 6 and ID is not equal to 7 not equal to 8 value (optimized version)

SELECT TOP 3 * from BJ where id>6 and ID not in (7,8)

ORDER by statement

Sequential statements

1,acs (default, positive order)

SELECT * from BJ ORDER by ID ASC SELECT * from BJ ORDER by Age,id

2,desc (Flashback)

SELECT * from BJ ORDER by ID DESC

3, according to age from small to large query

SELECT * FROM BJ ORDER BY age ASC

4, according to age from big to small query

SELECT * FROM BJ ORDER BY age DESC

5.1, querying the table's columns (correct)

SELECT * FROM BJ ORDER by 7 SELECT * from BJ ORDER by 1,2,3,4,5,6,7

5.2, Query table column (Error) only 7 columns

SELECT * FROM BJ ORDER by 8 SELECT * from BJ ORDER by 1,2,3,4,5,6,7,8

DISTINCT Removing duplicate data

Select distinct age from BJ ORDER by 1

Database diagrams

In the database, select the database diagram, right-click, and create a new database diagram. Then choose a table

Insert statement (INSERT INTO)

INSERT into BJ (column 1, column 2,...) Values (value 1, value 2,....)

The first: complete notation

Insert into BJ (id,name,sex,age,greadname,phone,address) VALUES (' 14 ', ' Jen ', ' Female ', ' 20 ', ' Nine Grade ', ' 1878664624834 ', ' Zhongqing Nanan District South Ping Association Letter City ')

The second type: simple notation

(The wording is as follows, but if the ID of the auto-growth is valid, the ID is not automatic growth can not be executed, I ID not automatically grow, all can not be executed, for reference only)

INSERT into student values (' Small fang ', ' Female ', ' 18 ', ' Junior ', ' 137694743 ', ' Beijing Changping District Huilongguan ');

Change column name Display name (plus as)

Select COUNT (name) as ' name ' from BJ where name= ' goat big fairy '

Delete statement (DELETE statement)

Delete from BJ where id=16

Delete a fat name from a fuzzy query

Delete from BJ where name is like ' Fat% '

Before statement execution

After statement execution

Modify Statement (UPDATE)

UPDATE table name SET column name = new value WHERE Column name = value

Change name to ' goat Big fairy ' to ' fat '

Update BJ set name= ' fat ' where name= ' goat big fairy '

Before executing a statement

After executing the statement

Or

Change the address of the fat sister to ' the home of the Sheep fairy '

Update BJ set address= ' Sheep Daisen's home ' where name= ' fat sister '

Before statement execution

After statement execution

Change the grade of sex as a male into preschool

Update BJ set sex= ' Male ' where greadname= ' preschool '

Before executing a statement

After executing the statement

Primary FOREIGN Key

First, set up two databases.

First one

A second

Start setting up the primary foreign key

Click "Confirm" and then close "Last Shift+s save Click" is

In the right-click Database diagram, create a new database diagram. Two tables added "in self-naming save

View database: yyyyy

View database: Ydxx

Adding data to the database yyyyy

View (should have been added before the data has been deleted, so here the ID starts from 24 growth)

To begin inserting a foreign key association in a ydxx table

Insert Successful

View

Insert an ID value that does not have

Execute command

End.....

Change a column query

Select ID as ' Learn ', name as ' name ', age as ' old ', sex as ' gender ', Gradeid as ' age ', address as ' location ', phone as ' phone ' from ydxx

Two-table joint investigation

The first type: two-table joint investigation

Select Ydxx.id as ' School Number ', name as ' name ', age as ' ages ', ' age ', address as ' addresses ', phone as ' contact ', yyyyy.gradename as ' class ' From Ydxx, yyyyy

Sub-query

A subquery is a query that is nested within a SELECT, INSERT, UPDATE, or DELETE statement or other subquery. Subqueries can be used anywhere that an expression is allowed to be used. In this example, the subquery is used as a column expression named Maxunitprice in the SELECT statement.

Select Ydxx.id as ' School Number ', name as ' name ', age as ' old ', sex as ' gender ', Address as ' addresses ', phone as ' contact ', (select Gradename from yyyyy where Yyyyy.id=ydxx.gradeid) From Ydxx

Query Azom in that class

Select Yyyyy.gradename from yyyyy WHERE id = (select Gradeid from ydxx where name = ' Apache ')

Check all students in Grade Two

Select Name,age,sex,gradeid from Ydxx where ydxx.gradeid= (select ID from yyyyy where Gradename= ' second grade ')

Check all students in Grade Two

Select Ydxx.name,ydxx.sex, ydxx.age from ydxx where Ydxx.gradeid = (select id from yyyyy where gradename like ' second Grade ')

Nested subqueries

Select Name,sex, (select (select 1+5) +2) as ' fraction ' from ydxx

SQL Advanced

SQL Top

SQL like

SQL in

SQL Join

SQL Inner Join

SQL Union

SQL functions

Aggregation functions

Aggregations perform calculations on a set of values and return a single value. In addition to COUNT, the aggregate function ignores null values. Aggregate functions are often used with the GROUP by clause of a SELECT statement

Where can the aggregation function be used?

1. Select list (subquery or external query) for the selected statement.

2, COMPUTE, or COMPUTE by clause.

3. HAVING clause

Common functions

SQL avg (average)

SQL count (total)

SQL First ()

SQL last ()

SQL Max (max)

SQL min (min)

SQL sum (consolidated)

SQL Group by

SQL have

SQL UCase ()

SQL LCase ()

SQL Mid ()

SQL Len ()

SQL round ()

SQL Now ()

SQL format ()

1. Count function

1.1, the total number of inquiries, all classes of students

Select COUNT (*) as ' total number of students ' from YDXX Select COUNT (*) as ' class total ' from yyyyy

2. Max function

2.1. Check the maximum age of the class

SELECT Max (ydxx.age) as ' maximum age ' from ydxx

3. Min function

3.1 Check the minimum age in the class

SELECT MIN (ydxx. Age) as ' minimum ages ' from ydxx

3.2. Search for maximum age and minimum age at the same time

Select Max (ydxx.age) as ' Maximum Age ', min (ydxx.age) as ' minimum age ' from ydxx

4. Avg function

4.1, the average annual query

Select AVG (ydxx.age) as ' average age ' from ydxx

4.2. More than the average age of the students to inquire

Select Name,age,sex,gradeid from Ydxx where ydxx.age> (select AVG (ydxx.age) as ' average age ' from ydxx)

5. Sum function

5.1. Find out the sum of all learners ' ages

Select SUM (ydxx.age) as ' total age of all learners ' from ydxx

Post-band update ....

SQL server2008 0 Basic Learning