SQL statement Additions, deletions, and subqueries

Source: Internet
Author: User
Tags date1 table definition

Modify Table Modification Table

Grammar:

Alter table < old table name > rename [to] < new table name >;

Example: Alter table ' demo01 ' rename ' demo02 ';

Add Field

Grammar:

Alter table name ADD field name data type [Properties];

Example: Alter Table ' Demo02 ' ADD ' passWord ' varchar (not NULL);

modifying fields

Grammar:

Alter Table name change original field name new field name data type [Properties];

% Modified field Name%

Alter Table ' Demo02 ' change ' name ' username ' char (ten) not NULL;

Delete a field

Deleting a field removes a field from the table in the data table:

Syntax: ALTER table name DROP field name;

Add primary foreign key

1. Add a PRIMARY KEY constraint

Syntax: ALTER table name ADD CONSTRAINT PRIMARY Key name PRIMARY key table name (primary key field);

2. Add a foreign KEY constraint

Syntax: ALTER table name ADD CONSTRAINT foreign key name FOREIGN key (foreign key field) REFERENCES associated table name (associated field);

DML statement review Insert data record

1. Inserting a single row of data

Syntax: INSERT into table name [(field list)] VALUES (list of values);

2. Inserting multiple rows of data

Syntax: INSERT into new Table (field list) VALUES (Value List 1), (Value List 2), ..., (value list n);

Note: If you do not include the field name in the INSERT statement when inserting a record, the order of the fields in the values list must be the same as the order of the fields in the table definition, or an error will occur if the table structure changes (such as adding a data operation). If you specify an inserted field name, This problem is avoided, so it is recommended that you specify a specific field name when inserting data.

3. Inserting a query structure into a new table

Syntax 1:

INSERT into new Table (Field 1, Field 2,......)

SELECT Field 1, Field 2,......

from the original table;

Syntax 2:

CREATE Table New Table (SELECT field 1, Field 2, ...). From the original table);

Update data records

Syntax: UPDATE table name SET field 1= value 1, field 2= value 2,........ field n= value n [WHERE condition];

Delete data records

Syntax: DELETE from table name [where condition];

Syntax: TRUNCATE table name;

Data Query DQL statement review

Data query statements in SQL Server:

Syntax: select< List of field names >

from< table name or view >

[WHERE < query conditions;]

[GROUP by < Group field name;]

[Having < conditions;]

[Order by < sorted field name > [ASC or DESC]];

Limit clause

Syntax: select< List of field names >

from< table name or view >

[WHERE < query conditions;]

[GROUP by < Group field name;]

[Order by < sorted field name > [ASC or DESC]]

[LIMIT [position offset,] number of rows];

Where: 1. Position offset refers to starting from the first few data in the result set (the position offset of the first record is 0, the 2nd is 1,....), and is displayed when omitted from the beginning of the record.

2. Row values show the number of records

Common functions

Common aggregate functions:

AVG ()

Returns the average of a field

COUNT ()

Returns the number of rows in a field

MAX ()

Returns the maximum value of a field

MIN ()

Returns the minimum value of a field

SUM ()

Returns the and of a field

Name of function

Role

Commonly used String functions

Name of function

Role

Example

CONCAT (str1,str2,..., strn)

Connection string STR1,STR2,..., strn as a full string

SELECT CONCAT (' My ', ' S ', ' QL ');

Back to: MySQL

INSERT (STR,POS,LEN,NEWSTR)

Starts the string str from the POS position, and the Len characters are replaced with a string newstr

SELECT INSERT (' This is SQL Server database ', 3,10, ' MySQL ');

Back: This is the MySQL database

LOWER (str)

Turns all characters in the string str to lowercase

UPPER (str)

Capitalize all strings in the string str

SELECT UPPER (' MySQL ');

Back to: MYSQL

SUBSTRING (Str,num,len)

Returns a substring of the first num position of the string str with a length of Len

3. Time-Date function

Name of function

Role

Example

Curdate ()

Get Current date

SELECT curdate ();

Curtime ()

Get current time

Now ()

Get the current date and time

WEEK (date)

Return date is the week ordinal of a year

Year (date)

Returns the year of the date

HOUR (Time)

Returns the hour value of time

MINUTE (Time)

Returns the minute value of time

DATEDIFF (DATE1,DATE2)

Returns the number of days separated by the date parameter Date1 and Date2

Adddate (Date,n)

Calculate date parameter date plus n days of day

4. Numeric functions

Name of function

Role

Example

Ceil (x)

Returns the smallest integer greater than or equal to the number X

SELECT Ceil (2.3)

Returns: 3

Floor (x)

Returns the largest integer less than or equal to the number X

RAND ()

Returns the random number between 0~1

Sub-query Simple subquery

Syntax: SELECT ... From table 1 WHERE field 1 comparison operator (subquery);

Use the AS keyword to comply with ANSI standards

The alias of the SELECT field list from the table name as table;

A simple way to use spaces

The alias of the SELECT field list from the Table name table;

In and not in subquery in sub-query

Use a subquery when there are multiple records in the returned result, the = of the condition is changed to in

The in subquery can follow a subquery that returns multiple records to detect whether a field's value exists in a range.

Not in sub-query

Other data that is not in the in subquery scope.

SQL statement Additions, deletions, and subqueries

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.