Collect and organize common MySQL database commands

1. -- Data Operations

Select -- retrieve data rows and columns from database tables

Select * from Table order by OID desclimit; last 5 records in the table

Insert -- add new data rows to the database table

Delete -- delete data rows from a database table

Update -- update data in the database table

-- Data Definition

Create Table -- create a database table

Drop table -- delete a table from a database

Alter table -- modify the database table structure

Create View -- create a view

Drop View -- delete a view from a database

Create index -- create an index for the database table

Drop index -- delete an index from a database

Create procedure -- create a stored procedure

Drop procedure -- delete a stored procedure from a database

Create trigger -- create a trigger

Drop trigger -- delete a trigger from a database

Create schema -- Add a new schema to the database

Drop schema -- delete a schema from the database

Create Domain -- create a Data Value Domain

Alter Domain -- change domain definition

Drop Domain -- delete a domain from the database

-- Data Control

 

Grant -- Grant the user access permission

Deny-Deny User Access

Revoke -- revoke User Access Permissions

-- Transaction control

Commit -- end the current transaction

Rollback -- abort the current transaction

SET transaction -- define the data access features of the current transaction

-- Programmatic SQL

 

Declare -- set the cursor for the query

Explain -- describe the data access plan for query

Open -- open a cursor for retrieving query results

Fetch -- retrieve a row of query results

Close -- close the cursor

Prepare -- prepare SQL statements for dynamic execution

Execute -- dynamically Execute SQL statements

Describe -- describe the prepared Query

 

--- Local variables

 

Declare @ ID char (10)

-- Set @ ID = '20140901'

Select @ ID = '000000'

2. Commonly Used and practical:

DDL-Data Definition Language (create, alter, drop, declare)

DML-data manipulation language (select, delete, update, insert)

DCL-Data Control Language (Grant, revoke, commit, rollback)

First, we will briefly introduce the basic statements:

1. Description: Create Database database-name

2. Description: Delete the database drop database dbname.

3. Description: Back up SQL Server

--- Create a deviceuse master for backup data

Exec sp_addumpdevice 'disk', 'testback', 'c: \ mssql7backup \ mynwind_1.dat'

--- Start backing up backupdatabase pubs to testback

4. Description:

Create Table tabname (col1type1 [not null] [primary key], col2 type2 [not null],...)

Create a new table based on an existing table:

A: createtable tab_new like tab_old (use the old table to create a new table)

B: createtable tab_new as select col1, col2... From tab_old definition only

5. Description:

Delete a new table: Drop table tabname

6. Description:

Add a column: alter table tabname AddColumn Col type note: the column cannot be deleted after it is added. After columns are added to DB2, the data type cannot be changed. The only change is to increase the length of the varchar type.

7. Description:

Add primary key: alter table tabname addprimary key (COL)

Note:

Delete primary key: alter table tabname dropprimary key (COL)

8. Description:

Create an index: Create [unique] indexidxname on tabname (COL ....)

Delete index: drop index idxname Note: The index cannot be changed. To change the index, you must delete it and recreate it.

9. Description:

Create view: Create view viewname asselect statement

Delete view: Drop view viewname

10. Description: several simple basic SQL statements

Select: Select * From Table1 where range

Insert: insert into Table1 (field1, field2) values (value1, value2)

Delete: delete from Table1 where range

Update: Update Table1 setfield1 = value1 where range

Search: Select * From Table1 wherefield1 like '% value1 %' --- the like syntax is very subtle, query information!

Sort: Select * From Table1 order byfield1, field2 [DESC]

Total: Select count * as totalcountfrom Table1

Sum: Select sum (field1) assumvalue from Table1

Average: Select AVG (field1) asavgvalue from Table1

MAX: Select max (field1) asmaxvalue from Table1

Min: select Min (field1) asminvalue from Table1

11. Description: several advanced query Operators

A: Union operator

The Union operator combines two other result tables (such as Table1 and table2) and removes any duplicate rows from the table to generate a result table. When all is used together with Union (that is, Union all), duplicate rows are not eliminated. In either case, each row of the derived table is from either Table1 or table2.

B: Random t operator

The distinct t operator derives a result table by including all rows in Table 1 but not in table 2 and eliminating all repeated rows. When all is used with distinct T (distinct t all), duplicate rows are not eliminated.

C: intersect Operator

The Intersect operator derives a result table by only including the rows in Table1 and Table2 and eliminating all repeated rows. When all is used with intersect (intersect all), duplicate rows are not eliminated. Note: The query results of several computation words must be consistent.

 

12. Note: use external connections

A. leftouter join:

Left Outer Join (left join): the result set contains the matched rows in the connected table, and all rows in the left connected table. SQL: select a. a, a. B, A. C, B. C, B. D, B. F from a left out join B ona. A = B. C

B: rightouter join:

Right Outer Join (right join): the result set includes both matched join rows in the connection table and all rows in the right join table.

C: fullouter join:

Full outer join: includes not only matching rows in the symbolic join table, but also all records in the two join tables.

 

3. Basic Elements of SQL replication include

Publishing servers, subscription servers, distribution servers, publications, and articles.

How SQL replication works

SQL Server mainly uses publications and subscriptions to process replication. The server on which the source data is located is the publishing server, which is responsible for publishing data. The Publishing Server copies all changes to the data to be published to the distribution server. The distribution server contains a distribution database that can receive all changes to the data and save the changes, then, distribute these changes to the subscription server.

SQL Server replication technology type

SQL Server provides three Replication technologies:

1. Copy a snapshot (this will be used if we stay there)

2. Transaction Replication

3. Merge and copy

As long as the above concepts are clarified, we will have a certain understanding of replication. Next we will perform the copy step.

I. Configure the Publishing Server first

(1) Select the specified [server] node.

(2) Select the [Publish, subscribe server, and distribute] command from the [copy] sub-menu in the [tools] drop-down menu.

(3) In the dialog box that appears, click [next] And then follow the prompts until the operation is completed.

(4) After setting the Publishing Server, the system adds a replication monitor to the tree structure of the server. A distribution database is also generated ).

Ii. Create a publication

(1) Select the specified server.

(2) Select the [create and manage release] command from the [copy] sub-menu in the [tools] menu. A dialog box is displayed.

(3) Select the database for which you want to create the publication and click [Create release].

(4) In the prompt dialog box of the [Create release wizard], click [next]. A dialog box is displayed. The dialog box contains three types of copies. Now we select the first one, that is, the default snapshot release (the other two can be checked for help ).

(5) Click [next] system requirements to specify the type of database server that can subscribe to the release. sqlserver allows data replication between different databases, such as oracle or access. But here we choose to run the "SQL Server2000" Database Server

(6) Click [next]. A dialog box for defining the document is displayed, that is, the table to be published.

(7) then [next] until the operation is completed. After the publication is created, the database for creating the publication becomes a shared database.

3. Design subscription

(1) Select the specified subscription server.

(2) select [request subscription] from the [tools] drop-down menu in the [copy] submenu.

(3) Click [next] until the system prompts you to check the running status of the SQL Server proxy service. The precondition for performing the copy operation is that the SQL Server proxy service must be started. Then [next] until the operation is complete.

4.1>, sysobjects

System Object table. Save the objects of the current database, such as constraints, default values, logs, rules, and stored procedures.

Description of important sysobjects fields:

Sysobjects (

Name sysname, -- Object Name

Id int, -- Object ID

Xtype char (2), -- Object Type

Type char (2), -- object type (exactly the same as xtype? A little depressing ...)

UID smallint, -- ID of the object owner

... -- Other fields are not commonly used.

)

 

Note: The xtype and type are exactly the same. The data is as follows:

C = check Constraints

D = default value or default Constraint

F = foreign key constraint

Fn = scalar function

If = embedded table functions

K = primary key or unique constraint

L = Log

P = Stored Procedure

R = rule

Rf = copy and filter the Stored Procedure

S = system table

TF = table functions

Tr = trigger

U = User table

V = View

X = Extended Stored Procedure

This table contains all objects in the database, such as the table Stored Procedure view.

2. syscolumns database Field table. All fields in the current database are retained.

Important Field explanation:

Syscolumns (

Name sysname, -- field name

Id int, -- the ID of the table to which the field belongs

Xtype tinyint, -- this field type, associated with the policypes table

Length smallint, -- physical storage length of the field

...

)

 

 

For example, you need to query the fields in a table and the length of these fields.

3. sysusers

System group and user of the current database.

Sysusers (

UID smallint, -- User ID

Name smallint, -- name

UID varbinary (85), -- belongs to a login

....

)

Manage database users

 

4. sysdenpends

The dependency of the current database. For example, when I want to modify a structure, I am afraid that the modified structure will affect the stored procedures of other view functions. This can be queried before modification. Those view function stored procedures call this table.

In this way, the stored procedure of view functions can be modified after modification,