MySQL Learning notes

1. Start

net start (stop) MySQL

Mysql-h Localhost-u Root-p

2, the operation of the database

Create database name;

show databases;

Use database name; --Select database using

drop database name; --Delete Database

3, the operation of the data table

1). CREATE [temporary] TABLE [IF not EXISTS] tbl_name [(create_definition ...)] [Table_options] [Select_statement]

Create_definition:

Col_name type [not null|null] [default default_value] [auto_increment][unique_key][primary_key][comment ' string '] [ Reference_definition]

2). Show tables from database name;

Show columns from data table name from database name;

3). Describe database name. data table Name field name; -View information for a specified field

4). Modify the structure of the table

ALTER TABLE original table name rename new table name;

ALTER TABLE data table name change original field name new field definition;

ALTER TABLE name add field definition added;

ALTER TABLE data table name drop field name;

5). Delete data table

drop table data table name,...;

6) Backup and recovery

A. Backup mysqldump--opt database name-H localhost-u root-p-R backup Path

B. Recovering mysql-h localhost-u root-p database name < backup path

4. Common SQL statements

1) query statement (SELECT)

A. Simple select query

Select field name from data table name

Where Condition expression

Select name from student
--Where birthday >= ' 1990-01-01 ';

B. Select query with result sorting

Select field name from data table name

Where Condition expression

Order BY Field name ASC (DESC)

Select Name,birthday from Student
, where year (birthday) >= 1990
ORDER BY birthday Desc;

C. Select queries that limit the number of result bars-often used with order by

Select field name from data table name

Where Condition expression

Limit [Offset,] row_count---Returns the Row_count record from offset (default 0)

Select Name,birthday from Student
, where year (birthday) >= 1990
, limit 3;

2) INSERT statement (Inster)

A.insert. Values Statement

Insert into Data table name (field name,...) --If there is no default field, the field name can be omitted

VALUES (field value,...), which corresponds to

(field value ...);

B.insert. Set statement

Insert into data table name

Set field name = Field value,

...;

3) Updated statement (update)

Update data table name

Set field name = field value,...

[where query condition]--can be followed by an order by and a limit statement

4) Delete statement (deleted)

Delete from data table name

where query criteria

5. Advanced query Statement

1) Aggregation function-->count,max,min,sum,avg

A.count--The number of rows that return the statistic results

Select COUNT (*) from Stu_mark

where Mark >= 90;

Select COUNT (Distinct mark) from Stu_mark

where Mark >= 90; --Delete field value duplicate record, use DISTINCT keyword

B.max (min)

Select *,max (Mark) from Stu_mark

where Mark >= 90;

C.sum (avg)

Select SUM (Mark) from Stu_mark

where Mark >= 90;

2) Group queries--group records in a table by field, and then query calculations for each grouping with aggregate functions

Select aggregate function Number field name,... from table name

where query criteria

Group By field name

Having filter conditions; Filter the records after grouping, where the records are filtered before grouping, and have the ability to use aggregate functions

3) Union query--use Union to merge two and more select query results into one result set display

SELECT statement

Union[all]-->all omitted, the duplicate rows in the union result retain only one row, otherwise all rows are preserved

SELECT statement

Union[all] SELECT statement ...-the field name of the first table when the query results field is named in a federated query

4) Connection Query-query the information stored in multiple tables, the connection type is divided into three kinds of cross, inside and outside

Select field name,... from data table 1

Connection type data table 2 names

[on connection condition]

A. Cross join--Returns the Cartesian product of all data rows in the Join table

Select stu_info.name,stu_info.major_id from Stu_info

Cross join Major;

B. Internal connection (inner join)--Returns the row in the join table that matches the join condition

Select Stu_info.name,major.major_name from stu_info to data table name. Field Name

INNER JOIN major

on stu_info.major_id = major.major_id; -three types of equivalent, unequal and natural connections

C. outer join-->left join RIGHT Join full join

Not only lists rows that match the join criteria, but also lists all data rows in the left table (right, full) that match the search criteria

5) Sub-query--The condition of the query is the result of another SELECT statement, using subqueries, in, not in, =,! =, exists, not exists, etc.

A.in Sub-query

SELECT * FROM Stu_info

where major_id in

(select major_id from Major);

B.exists subquery-The query result of the SELECT statement after exists is not empty

SELECT * FROM Stu_info

where exists

(select major_id from major

where major_id = 1);

　　　　　　

MySQL Learning notes