SQL statement records

Tables Used for operations: student (sno, sname, ssex, sage, sdept) SC (sno, cno, grade) course (cno, cname, cpno, credits) 1. CREATE a table (CREATE) CREATETABLE table name (column Name Data Type [column integrity constraints] [, column Name Data Type [column integrity constraints]... [, table-level integrity constraints

Tables Used for operations: student (sno, sname, ssex, sage, sdept) SC (sno, cno, grade) course (cno, cname, cpno, credits) 1. create table (CREATE) create table Name (column Name Data Type [column level integrity constraints] [, column Name Data Type [column level integrity constraints]... [, table-level integrity constraints

Table used for operation:

Student (sno, sname, ssex, sage, sdept)

SC (sno, cno, grade)

Course (cno, cname, cpno, credits)

1. CREATE a table)

CREATE TABLE <表名> ( <列名> <数据类型> [Constraints on column-level integrity]

[, <列名> <数据类型> [Constraints on column-level integrity]...

[, <表级完整性约束条件> ]);

Create table student ('sno' CHAR (5) not null unique, 'sname' CHAR (8) not null unique, 'ssex 'int, 'sdept 'CHAR (20 ), primary key ('sno '));

Create table course ('cno' CHAR (4) not null, 'cname' CHAR (10) not null, 'cpno' CHAR (4), 'credits 'INT, primary key ('cno '));

Create table SC ('sno' CHAR (5), 'cno' CHAR (4), 'case' INT, PRIMARY KEY ('sno', 'cno '), foreign key ('sno') REFERENCES student ('sno'), foreign key ('cno') REFERENCES course ('cno '), CHECK ('grad' BETWEEN 0 AND 100 ));

2. ALTER)

ALTEER TALBE <表名> [ADD <新列名> <数据类型> [Integrity constraints]

[DROP <完整性约束名> ]

[MODIFY <列名> <数据类型> ];

(1) Add Columns

Alter table 'student 'add' address 'Char (30)

No matter whether data already exists in the basic table, the newly added Column cannot be defined as "not null"

(2) supplementary definition of the master code

ALTER TABLE <表名> Add primary key ( <列名> )

(3) revoking the definition master code

ALTER TABLE <表名> Drop primary key;

(4) supplementary definition of external code

ALTER TALBE <表名1> Add foreign key ( <列名> ) REFERENCES <表名2> ( <列名> )

(5) undo external code definition

ALTER TABLE <表名> DROP <外码名>

(6) Modify attribute data types and constraints

Alter table 'studen' MODIFY 'sage' SMALLINT;

Alter table 'student 'drop UNIQUE ('sname ');

3. DROP)

DROP TABLE <表名> ;

Drop table 'student ';

4. SQL data query (SELECT)

SELECT [ALL | DISTINCT] <目标列表达式> [, <目标列表达式> ]…

FROM <表名> [, <表名> ]…

[WHERE <条件表达式> ]

[GROUP <列名1> [HAVING <条件表达式> ]

[ORDER <列名2> [ASC | DESC];

(1) query specified Columns

Select 'sno', 'sname', 'sage', 'ssex, 'sdept' from 'student ';

(2) query all columns

Select * from 'student ';

(3) query computed Columns

Select 'sname', 2013-'sage' from 'student;

Select ISLOWER ('sno') LSNO, 'sname', 'year OF birth' BRITH, 2007-'sage' BIRTHDAY from 'student ';

(The Field followed by the expression is the new column name, such as BRITH and BIRTHDAY. The former is the expression or value)

Select 'sno' from 'SC ';

(4) Remove tuples with the same values

Select DISTINCT 'sno' from 'scs ';

The default value is "ALL". If DISTINCT is specified, duplicate rows are eliminated (only one row is displayed when duplicate tuples have the same value)

(5) query the qualified tuples

Query condition Predicate

Comparison =,>, <, >=, <= ,! =, <>, NOT + comparison operator

The value range is between and, not between and.

Determine the set IN, NOT IN

Character match LIKE, NOT LIKE

Null is null, IS NOT NULL

Multiple conditions (logical predicates) AND, OR

1. Compare the size

Select 'sno', 'sname' from 'student 'where 'sdept' = 'computer System'

Select 'sname', 'sage' from 'student 'where 'sage' <21;

Equivalent

Select 'sname', 'sage' from 'student 'where NOT 'sage'> = 21;

2. determine the scope

Select 'sno', 'sname', 'sage' from 'student 'where 'sage' BETWEEN '19' AND '21 ';

Equivalent

Select 'sno', 'sname', 'sage' from 'student 'where 'sage'> = '19' AND 'sage' <= '21'

Select 'sno', 'sname', 'sage' from 'student 'where 'sage' not between '19' AND '21 ';

Equivalent

Select 'sno', 'sname', 'sage' from 'student 'where 'sage' <'19' OR 'sage'> '21 ';

3. Confirm the set Query

Select 'sno', 'sname', 'ssex 'from 'student 'where 'sdept' IN ('computer Department', 'mathematics Department ');

Equivalent

Select 'sno', 'sname', 'ssex 'from 'studen' where 'sdept' = 'computer Department' OR 'sdept' = 'mathematics Department ';

Select 'sno', 'sname', 'ssex 'from 'studen' where 'sdept' not in ('computer Department', 'mathematics Department ');

Equivalent

Select 'sno', 'sname', 'ssex 'from 'student 'where 'sdept '! = 'Computer system' AND 'sdept '! = 'Department of mathematics ';

4. Character matching Query

[NOT] LIKE 'matched string' [ESCAPE' <换码字符> '] Note: a space is required after the character is changed, such as ESCAPE.

% (Percent) represents a string of any length (the length can be 0. For example, a % B indicates a string of any length starting with a and ending with B, such as acb, adertb, and abdedb.

_ (Underline) indicates any single character. For example, a_ B indicates any string starting with a and ending with B as 3, such as adb and agb.

Select * from student where 'sname' like 'Lee % ';

Select * from student where 'sname' not like 'Lee % ';

Select * from 'student 'where 'sname' like '_ % ';

Note: a Chinese character occupies two characters. Therefore, two "_" characters are required before the matching string "small".

Assume that a field value is DB_Design,

Select 'cno', 'credits 'from 'Course' where 'cname' LIKE 'db \ _ Design 'escape '\';

5. query involving null values

NULL has a special meaning in the database. It indicates an uncertain value. Determines whether a value is NULL. Common comparison operations cannot be used,

You can only use a clause that specifically judges NULL to complete this task.

Select 'sno', 'cno' from 'SC 'where 'case' IS [NOT] NULL;

6. Multi-condition query:

AND has a higher priority than OR, but you can use parentheses to change the priority.

(6) sort the query results

ORDER <列名1> [ASC | DESC] [, <列名n> [ASC | DESC]...

By default, it is sorted in ascending order, that is, ASC.

Select * from 'student 'order BY 'sdept' DESC, 'sage', 'sno ';

(7) use the set function

COUNT ([DISTINCT | ALL] *) number of groups in the statistical table

COUNT ([DISTINCT | ALL] <列名> ) Count the number of values in a column.

SUM calculates the SUM of values in a column (this column must be numerical)

AVG calculates the average value of a column (this column must be numerical)

MAX calculates the maximum value in a column.

MIN calculates the minimum value in a column.

Query the total number of students

Select COUNT (*) from student;

Query the number of students who have selected the course

Select COUNT (DISTINCT 'sno') from 'scs ';

Calculate the average score of students taking course 3

Select AVG ('grade ') from 'scs' where 'cno' = '3 ';

Query the maximum score of students who take course 3

Select MAX ('grade ') from 'scs' where 'cno' = '3 ';

(8) query groups

The group by clause groups the query result table BY values of one or more columns. The group by clause groups the same values.

HAVING phrases are used only in groups, and are usually used together with group.

Query each course number and the number of students selected

Select 'cno', COUNT ('sno') CNTSNO from 'scs' group by 'cno ';

Query the student ID for more than two courses

Select 'sno' from 'scs' group by 'sno' having count (*)> 2;

Note: The where clause acts on the table, and the having clause acts on the GROUP.

(9) connection Query

1. query connections between different tables

Query the status of each student and their Optional Courses

Select 'student '. *, 'scs'. * from student, SC where student. sno = SC. sno

2. Self-connection Query

Select A. cno, A. cname, B. cpno from course A, course B where A. cpno = B. cno;

3. External Connection Query

Standard SQL use *, and some databases use +. (It seems that the practice is not reliable, RIGHT (LEFT) [OUTER] JOIN is used)

Outer outer left Outer Join and outer join.

SELECT student. *, SC. * FROM student right join SC ON student. sno = SC. sno

Note: right join is a RIGHT outer JOIN, and left join is a LEFT Outer JOIN. You must use ON to determine the RIGHT Outer JOIN. No where clause is required. Data ON which side of JOIN is complete, data that does not exist on the other side is expressed as NULL.

(10) nested Query

A query block is nested in the WHERE clause or HAVING phrase of another query block.

For example

Select 'sname' from 'student 'where 'sno' IN (select sno from SC where 'cno' = '2 ');

(11) subqueries with EXISTS predicates

With EXISTS, only the logic "TRUE" and "FALSE" are returned"

Select * from student where EXISTS (select * from SC where 'sno' = student. sno and cno = '3 ')

5. insert SQL data)

1. Insert a single tuple into the table

Insert <表名> [( <属性1> [, <属性2> ...])] VALUES ( <常量1> [,, <常量2> ]...)

Note: For attribute columns that do not appear after into, the new element group takes null values for these columns. If the next attribute does not appear, all attributes are defaulted.

Insert into student VALUES ('s8804', 'Liu hongying', 'female ', '20', 'mathematics Department ')

Insert into SC ('sno', 'cno') VALUES ('s8804', '2'); null VALUES for other fields not added

2. insert Self-query results

INSERT <表名> [( <属性1> [, <属性2> ]...)] Subquery;

Insert into history-student select * from student;

Insert into TG ('sno', 'tgrade ') select 'sno', SUM ('grade') from SC group by sno;

6. SQL data modification (UPDATE)

UPDATE <表名> SET <列名1> = <表达式1> [, <列名n> = <表达式n> ...] [WHERE <条件> ]

Update 'SC' set grade = 0 where 'mathematics Department '= (select 'sdept' from 'student 'where 'student'. 'sno' = SC. sno );

7. DELETE data using SQL statements)

DELETE FROM <表名> [WHERE <条件> ]

Delete deletes data, not table definitions and attributes.

Delete from 'student 'where 'sno' = 's8203'

Delete from 'student '; delete the table data

Note: The add, delete, and modify operations can only be performed on one table, and the query can be performed on multiple tables.

Note: all the preceding statements are verified by SQLyog, but the standard SQL statements cannot be executed. All the statements are changed to executable statements, mainly actual statements.