MySQL database syntax

1. Create a database:

Create Database MySchool;

2. Delete the database:

Drop database MySchool;

3. Create a table:

The CREATE table [if not EXISTS] table name (

Field 1 data Type [Field Properties | constraints] [index] [note],

......

) [Table type] [table character Set] [note];

CREATE TABLE ' student ' (

' Studentno ' int (4) NOT NULL comment ' study number ' primary key, #非空, primary key

' Name ' char (10),

......

) comment= "Student table";

4. Add fields

ALTER TABLE demo02 add ' password ' varchar (+) not null;

5. Modify Fields

ALTER TABLE name change original field name new field name data type [Properties];

ALTER TABLE demo02 change ' name ' ' username ' char (ten) not null;

6. Set Default value Constraints

ALTER TABLE grade ALTER COLUMN Gtype set default ' first grade ';

7. Delete Fields

ALTER TABLE name drop field name

ALTER TABLE demo02 drop ' password ';

8. Add a PRIMARY KEY constraint

ALTER TABLE name add constraint primary Key name primary key table name (primary key field);

ALTER TABLE ' Grade ' Add constraint ' pk_grade ' primary key ' grade ' (' Gradeid ');

9. Adding FOREIGN KEY constraints

ALTER TABLE name add constraint foreign key name foreign key (foreign key field) references associated table name (associated field);

ALTER TABLE ' student ' Add constraint fk_student_grade foreign key (' Gradeid ') references ' grade ' (' Gradeid ');

10. Add Check Constraint

ALTER TABLE TEST add constraint ck_test_tprice check (tprice > = 100);

11. Inserting data

Insert INTO ' subject ' (' Subjectname ', ' classhour ', ' Gradeid ')

VALUES (' Logic java ', 220,1), (' HTML ', 160, 1);

12. Insert the query results into the new table

CREATE table new Table (select field 1, Field 2, ...). From the original table);

CREATE TABLE ' phonelist ' (select ' Studentname, ' phone ' from ' student ');

13. Update Data

Update table name set column name = update value [where update condition];

Update student Set sex = ' female ' where id = 1;

14. Delete Data

delete [from] table name [where < delete condition >];

Delete from student where id = 1;

15. Query using the SELECT statement

Select < column names | expressions | functions | Constants >

From < table name >

[where < query condition expression;]

[ORDER BY < sorted column name > [ASC or DESC]];

Select Studentno as student number, studentname as student name, address as student location

From student

where id = 1;

Or:

Select Firstname+ '. ' + LastName as name from employee;

16. Query Null value

Select Studentname from student where email is null;

17. Use constant columns in queries

Select Studentname as name, address as location, ' Beijing ' as school name from student;

18. Aggregation function

AVG ()

COUNT ()

MAX ()

MIN ()

SUM ()

Select SUM (studentresult) from result;

Select AVG (studentresult) from result;

19. String Functions

CONCAT (str1,str2,......, strn) Select CONCAT (' MY ', ' S ', ' QL '); Back to: MYSQL

Insert (STR,POS,LEN,NEWSTR) Select Insert (' This is the Oracle database ', 3,6, ' MySQL '); Back: This is the MySQL database

LOWER (str) Select LOWER (' MYSQL '); Back to: MySQL

Upper (str)

SUBSTRING (str,num,len) Select substring (' javamysqloracle ', 5,5); Back to: MySQL

20. Time-Date function

Select DateDiff (now (), ' 2008-8-8 '); Returns 3327, returns the number of days separated by the date parameter Date1 and Date2

Select Adddate (Now (), 5);

21. Mathematical Functions:

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 Select Floor (2.3) returns: 2

22. Order BY sentence

ASC: Ascending DESC: Descending

In order to sort the students ' grades, the following statements are ordered according to the course ID:

Select StudentID as student number, Studentresult as score, CourseID as course ID,

From result

Whre studentresult>60

Order BY Studentresult,courseid;

23. GROUP BY group

Select < column names | expressions | functions | Constants >

From < table name or view >

[where < query condition expression;]

[GROUP by < Group field name;]

[ORDER BY < sorted column name > [ASC or DESC]]

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

Multi-column Group query

Select COUNT (*) as number, grade as grade, sex as gender from student

Group by grade, sex

Order by grade;

24, simple sub-query

Select ' Studentno ', ' studentname ', ' sex ', ' borndate ', ' address ' from ' student '

where ' borndate ' >

(SELECT ' Borndate ' from ' student ' where ' studentname ' = ' Lis Wen ');

25, in and not subqueries

Query Logic Java course at least one test is exactly equal to a 60-point list:

Select ' Studentname ' from ' student '

where ' Studentno ' in (

Select ' Studentno ' from ' result '

where ' subjectno ' = (

Select ' Subjectno ' from ' subject '

where ' subjectname ' = ' Logic Java '

) and ' studentresult ' = 60

);

26, exists sub-query

The argument after the EXISTS keyword is an arbitrary subquery, and if the subquery has a return row, the result of the exists subquery is true, at which point the outer query statement is executed. If the subquery does not return a row, the result of the exists subquery is false, when the outer statement no longer executes the query.

Check the Java course for the last exam. If there is a score of 80 or more, the scores ranked in the top 5 students are shown in the number and score.

Select ' Studentno ' as study number, ' Studentresult ' score from ' result '

where exists (

#查询Logic the last record in Java with a test score greater than 80

SELECT * from ' result ' where ' subjectno ' = (

Select ' Subjectno ' from ' Subject ' where ' subjectname ' = ' Logic Java '

) and ' examdate ' = (

Select MAX (' Examdate ') from ' result ' where ' subjectno ' = (

Select ' Subjectno ' from ' subject '

where ' subjectname ' = ' Logic Java '

)

) and ' Studentresult ' > 80

) and ' Subjectno ' = (select ' Subjectno ' from ' Subject ' where ' subjectname ' = ' Logic Java ')

ORDER by ' Studentresult ' DESC LIMIT 5;

27. Use having words to filter the data after grouping

Select COUNT (*) as number of Gradeid as grade from student

GROUP BY Gradeid

Having count (*) > 2;

28, Internal connection query

Select Student.studentname,result.subjectno,result.studentresult

From Student,result

where Student.studentno = Result.studentno;

Or:

Select S.studentname,r.subjectno,r.studentresult

From student as S

INNER JOIN result as R on (s.student.no = R.studentno);

29. Left Outer connection query

Select S.studentname,r.subjectno,r.studentresult

From student as S

Left OUTER join result as r on s.studentno = R.studentno;

MySQL database syntax