SQL Language Review 2

First, query

Select field List from table list [where conditional expression][group by field list [having conditional expression]] [Order BY field List [Asc|desc]]

Meaning: In the table given in the From, select out the specified field list, according to the conditions specified in where. How to have a group By field list, the result set is sorted by the list of fields following group by, and the conditional expression behind the having is the result output condition when grouping. Order by indicates that the result set is sorted in ascending or descending order by the list of fields following it. Each field list is separated from the table names by commas.

?

Single-Table query:

SELECT * FROM Mag_emp

Select Empname,sexinfo,empage from Mag_emp

Select Depname department name, Deptel as contact phone from mag_dept

Select distinct emprole from mag_emp remove duplicates

?

Use of the WHERE clause:

Select EmpName, emprole from mag_emp where emprole= ' edit '

Select Empname,emprole,empage,sexinfo from mag_emp where sexinfo = ' man ' and Empage >=30

Select Empname,emprole,empage form mag_emp where empage (not) between and (Empage >=25 and Empage <=40)

Select Magid,designername from Mag_info where Designername in (' Sakura ', ' Spring ', ' millet ') equal (dn= ' * ' or dn= ' * ' or dn= ' * ')

Fuzzy query:

% means matches 0 or more characters _ represents a character [] matches a range of data

Select Empage from mag_emp where empname like ' Zhang% '

Select EmpName, Permister from Emp_mag where Permister like '% certified% '

Select Empname,depmanager, Deptel from mag_dept where Deptel like ' ______2__ '

Select Magid,designername from Mag_info where Magname= ' Beijing Information Weekly ' and Magid like ' ____20040[1-5] '

Use of the ORDER BY clause: (the default is ascending ASC)

SELECT * from Mag_emp ORDER BY empage Desc

Select* from Mag_emp ORDER by EmpName

SELECT * from Mag_emp ORDER by Depid, Empage Desc (first sorted by first sort field, sorted by second field if the first field is the same)

?

Multi-Table query:

Select Depname from Mag_dept, mag_emp where Mag_dept.depid=mag_emp.depid and Empname= ' Zhang Bin '

Select Magname,designername,empage,emprole from Mag_info I, mag_emp, Mag_doc d where i.magid = D.magid and designername=e Mpname and doctitle = ' xxx '

The table is named I and D, respectively, by using the alias method. The named variable is then referenced for conditional filtering.

?

Querying aggregated data:

AVG () sum () max () min () count () count (*)

Select AVG (empage), COUNT (Empid) from Mag_emp

Select AVG (empage) ' average age ', count (empid) as ' number of employees ' from Mag_emp

Select Sexinfo, AVG (empage) ' average age ', count (empid) from Mag_emp GROUP by Sexinfo

Select Depname,avg (empage) ' average age ' from Mag_emp, mag_dept where Mag_dept.depid=mag_mag.depid and Mag_emp.depid =2 GROUP by Depname,mag_emp.depid

?

Nested queries:

Nested queries can resolve multiple table query problems, but the fields that require the output of the main query must come from the same table and can be nested in multiple layers

Select Empname,empage from Mag_emp where empage> (select AVG (empage) from Mag_emp)

Select Depname from Mag_dept where depid= (select Depid from mag_emp where dmpname= ' xxx ')

?

To save the query results:

Just add the into + table name to the select and from center.

Select DocTitle into #temp_title from Mag_doc where Wordssum > (select AVG (wordssum) from Mag_doc)

SELECT * FROM #temp_title

?

Second, the Data update

Input data:

insert into table name [field] VALUES ()

Enter a single tuple:

INSERT into mag_info values (' xxx ', ' xxx ', ' xxx ', ' xxx ', ' xxx ', ' xxx ')

Insert into Mag_emp (empname,sexinfo,empage,depid) VALUES (' Chen ', ' woman ', 23, 1)

Enter multiple tuples:

NOTE: The following statement omits into because we are entering more than one tuple here, so the format varies

Insert Dept_bak (depname,depmanger) Select Depname,depmanger from Mag_dept

?

Change data:

Update table name set column name = value [, Column name = value,] [where condition expression]

Update Dept_bak set deptel= ' Ssssss '

Update Dept_bak set deptel= ' 11111 ', depmanager= ' xxxx ' where depid=2

Update Stu Set age = Age + 4

?

Delete data:

Delete from Dept_bak

?

Third, view

A view is a logical structure that represents a form of data.

What the user can do with the data through the view will work directly on the data table, but deleting a view will not affect any data in the datasheet

CREATE View name [(column name Group)] [with encryption] as subquery [with CHECK option]

Where a subquery can say a SELECT statement, with encryption that the view is encrypted. With CHECK option to make sure that the condition satisfies the subquery when the data update operation is performed on the view.

Also, you can only use the view in the current database, and if the view is deleted, the current view is not available and you need to recreate the new view or base table.

CREATE VIEW department 1 as SELECT * Form mag_dept where depid = 1

Create VIEW Chinese typesetting staff information with encryption as select Empid,empname,sexinfo,empage,emprole from Mag_emp E, MAG_DEP D where E.depi D=d.depid and depname= ' xxx ' with CHECK option

?

To modify a view:

Change create to alter.

ALTER VIEW Department 1 (department number, department name, department head, phone) as SELECT * from mag_dept where depid = 1

Working with Views:

Use it as a table.

SELECT * FROM Department 1

Update Department 1 set Department name = ' Chinese editorial Office ' where department number =1

To delete a view:

Drop View Name

Drop View Department 1

Advantages: Simplify user operations, view data from different perspectives, and provide security for your data

?

?

Iv. rules and default values

Rules and defaults can also achieve data integrity, but are not defined and used in the same way as constraints.

Rules:

Create rule rule name as Rule expression

You need to add an @ symbol before the argument in the expression.

Create Rule Age_rule as @age >=1 and @age <=100

Create Rule Sex_rule as @sex in (' Woman ', ' mans ')

?

Before you can use rules, you need to bind the rules first.

Sp_bindrule rule name, ' table name. Column name ' where sp_bindrule is a stored procedure.

Sp_bindrule age_rule, ' mag_emp.empage '

Insert into Mag_emp (empname,sexinfo,empage,edpid) VALUES (' xxx ', ' woman ', 321, 1)

The above statement will be an error because it does not conform to the rules we define.

View rule: sp_helptext rule name

Sp_helptext Sex_rule

Release rule: sp_unbindrule

Sp_unbindrule ' Mag_emp.empage ', age_rule

Delete rules: Drop rule Rule name group

Drop Rule Age_rule,sex_rule

?

Create and manage default values:

Create default name as expression

Create default Sex_default as ' woman '

Use default values:

Sp_binddefault sex_default, ' Mag_emp.sexinfo '

To view the default values:

Sp_helpconstraint mag_emp

To dismiss the default value:

Sp_unbinddefault ' table name. Column name ', ' default value '

Sp_unbinddefault ' Mag_emp.sexinfo ', Sex_default

Delete default values:

Drop Default Sex_default

Two methods for implementing data integrity are compared:

1. Object level different database object vs data Table Object

2, the definition method is different from the creation of the statement, the method used in this section is more flexible

3, using different methods used in this section, once defined, multiple use.

SQL Language Review 2