MySQL's basic statement

Connection

1, mysql-h localhost-u root-p ****** (carriage return) 2, mysql-h localhost-u root-p (carriage return) ****** (carriage return) 3, mysql-u root-p ****** (carriage return) (this does not write- H words is also the default link localhost) 4, mysql-u root-p (carriage return) ****** (enter) (here root is the user name, ****** is the password.) This is a native test)

Exit

Mysql>exit (carriage return)

Or

Mysql>quit (carriage return)

View Gallery

mysql>show databases; (carriage return) (note here that the concluding sentence is a semicolon in the English state)

Create a library

Mysql>create database mysql2; (carriage return) (MYSQL2 here is the name of the library to be created)

Select Library

Mysql>use mysql2; (carriage return) (the MYSQL2 here is to select the name of the library)

View Table

mysql>show tables; (carriage return) (The following table is empty because it is the library that was just created)

Delete a library

Mysql>drop database test; (enter) (here the test is the name of the library to be deleted)

Create a table

Mysql>create Table Class (Stu Int,name varchar (), age int);(carriage return)

Change table name

Mysql>rename table class to Newclass; (carriage return) (the class here is the name of the table to be changed, Newclass is the new table name)

Delete a table

Mysql>drop table Newclass; (carriage return) (Newclass here is the name of the table to be deleted)

Structure of the Description table

Mysql>desc class; (carriage return)

Increase

Mysql>insert into Class (Stu,name,age) VALUES (1, ' Zhangsan ', All);(Enter)

Set the encoding format

Mysql>set names GBK; (carriage return)

Find

Mysql>select * from class; (carriage return)
(This means finding all the information under the class table)

Or

Mysql>select stu,name from class; (carriage return)
(means to see all the information in the Stu and name columns under the class table only)

Mysql>select * from class Stu = 2; (carriage return) (indicates that all information is only seen on stu=2 rows under the class table)

Mysql>select stu,name from class Stu = 2; (carriage return) (indicates that all information is only seen on the stu=2 rows of the Stu and name columns under the class table)

Modify

Mysql>update Classsetstu = 3,age = 244wherestu = 2; (carriage return) (the class here is under the table to be modified, the set to where the middle is the content to be modified, where the back is the positioning)

Delete

Mysql>delete from classwherestu = 3; (carriage return) (the class here is deleted below the table, where the statement is the location function)

Data type 1, positive type

tinyint, smallint, mediumint, int, bigint1, tinyint:1 bytes, the range that can be represented is -2^7~2^7-1 ( -128~127), if the declaration is unsigned unsigned, the range that can be represented is 0~2^ 8 (0~256); 2, Smallint:2 bytes, the range that can be represented is -2^15~2^15-1 (32,768~32,767), if the declaration is unsigned unsigned, the range that can be expressed is 0~2^16 (0~65,535); 3. Mediumint:3 bytes, the range that can be represented is -2^23~2^23-1 ( -8,388,608~8,388,607), and if the declaration is unsigned unsigned, the range that can be represented is 0~2^24 ( -16,777,216~ 16,777,215) 4, Int:4 bytes, the range that can be represented is -2^31~2^31-1 ( -21,4748,3648~21,4748,3647), if the declaration is unsigned unsigned, the range that can be represented is 0~2^32 (0~ 4,294,967,296) 5, Bigint:8 bytes, can be expressed in the range of -2^63~2^63-1 ( -922,3372,0368,5477,5808~922,3372,0368,5477,5807), If the declaration is unsigned unsigned, the range that can be expressed is 0~2^64;

2. Decimal type

float, double, decimal no matter how many bytes under MySQL, use 1, float (m,d): M indicates that except for the decimal point, D represents the number of digits of the decimal point. For example, the range represented by float (4,2) is -99.99~99.992, decimal (m,d): M represents all digits except the decimal point, and D represents the number of digits in the decimal point. For example: Decimal (4,2) indicates that the range is -99.99~99.99 the difference between the two is that decimal is more precise.

Character type

char, varchar, text is also not tube a few bytes, when used 1, char (m): M is able to put a few characters, is fixed length, in memory is accounted for M and characters. However, char types cannot hold spaces at the end of a string. Under the Utf-8 Code table, a Chinese character occupies three bytes. 2, varchar (m): M is able to put a few characters, is indefinite length, actually accounted for n<=m characters, then in memory accounted for n characters. 3, Text: is stored text, but not commonly used, you can choose char and varchar.

Time Date Type

Year, date, time, DateTime is not a few bytes, use 1, Year: The storage range 1901~2155 and 0000, where 0000 is to store illegal input and no input. Enter a two-digit line, if the input is 01~69 is the input is 2001~2069, if the input is 70~99 on behalf of the input is 1970~1999. 2, Date: The storage range 1000-01-01~9999-12-313, time: The storage range -838:59:59~838:59:594, DateTime: The storage range 1000-01-01 00:00:00~9999-12-31 23:59:59

The five syntax of the query where, group BY, having, order by, limit1, where function is mainly used to locate the 2, group by is a group

Generally works with the five statistical functions of Max, Min, Sum, AVG, Count

Max: Ask for maximum

Min: Ask for minimum

Sum: Find sum

Avg: Averaging

Count: Total Row count

3, having is the query results of the temporary table for filtering operations 4, order by is the sort 5, limit play to restrict the role of the entry

Use case

Three-seed query where, from, exists

1. Where sub-query: The results of the inner query as the comparison condition of the outer layer

2, from sub-query: The query results of the inner layer as a temporary table, for the outer SQL query again

3, Exists type sub-query: The outer query results, to get the inner layer of the query is set up

Use case

Use of Union

Union means merging two or more query results together.

The UNION is used to combine the result set of two or more SELECT statements and eliminate any duplicate rows in the table.
The SELECT statement inside the UNION must have the same number of columns, and the column must have a similar data type.
Also, the order of the columns in each SELECT statement must be the same.

SELECT column_name from Table1unionselect column_name from table2

Union ALL is to keep the repetition.

Use case

Left join the use of the left join, right join, and inner join inner JOIN

Use case

Diagram Demo

The increment of the column

Mysql>alter table class Add phone char (one);(enter) (the table name of the class here, the phone is the added column name, char (11) is the data type to increase the column name)

Deletion of columns

Mysql>alter table Class Drop Phone (enter) (the table name of the class here, the phone is the column name to delete)

Modification of columns

Mysql>alter Table class change newage age int; (carriage return) (the table name of class here, NewAge is the name of the column to be modified, age is the modified column name, int is the data type that modifies the column name, as before the modification)

Use case

The use of Views view

Views are virtual data, which by default is a save statement

The Created view

Mysql>create View St As select * from class where id>2; (carriage return) SELECT * FROM St where id<6; (carriage return)

is equivalent to

Mysql>select * from class where id>2 and id<6;

Delete View

Mysql>drop View St; (carriage return)
(Here St is the view name)

Modify a View

MYSQL>ALETR View St As select * from Class ID < 6 (carriage return) (here St is the view name, class is the table name)

Use case

Modify Terminator

Mysql>delimiter $ (carriage return) (at which point the Terminator is $)

Create a Trigger

Add Class

Mysql>create trigger Triggername1after Insert on Buyfor each rowbeginupdate sell set sum = sum-new.sum where animal = new.animal;end$ (carriage return) (here the triggerName1 is the name of the creation trigger, buy is the name of the data table to be inserted, the Begin and HM are the SQL statement, the new point represents the newly inserted row. ）

View triggers

Mysql>show triggers$ (carriage return) (here's $ is previously modified Terminator)

Backup and recovery of data export table

mysqldump-uroot-p123456 mysql2 sell buy>d:/sellandbuy.sqlmysqldump-u user name-p password Library Name table 1 table 2> address/backup file name

Export all tables under library

mysqldump-uroot-p123456 mysql2>d:/mysql2alltable.sqlmysqldump-u user name-p password Library name > address/backup file name

Export Library

mysqldump-uroot-p123456-b MySQL mysql2>d:/mysqlandmysql2.sqlmysqldump-u user name-p password-B library name 1 Library name 2> address/backup file name

Export all libraries

Mysqldump-uroot-p123456-a>d:/databases.sqlmysqldump-u Username-p password-a> address/backup file name

Restore library 1, do not log on

mysql-uroot-p123456 <d:/mysql2.sqlmysql-u user name-p password < address/recover file name

2. Login

SOURCE D:/mysql2.sql;source Address/recovery file name;

Restore table 1, do not log on

mysql-uroot-p123456 mysql2<d:/sellandbuy.sqlmysql-u Username-p Password Library name < address/recover file name

2. Login

Use Mysql2;source d:/sellandbuy.sql;use library name; Source address/recovery file name;

Use case

Storage Engine MyISAM, Innodbmyisam

CREATE table name (ID tinyint) Engine=myisam;

Transaction is not supported, the advantage is that the access speed is fast.

InnoDB

CREATE TABLE name2 (ID tinyint) Engine=innodb;

Transactions are supported, but the access speed is relatively slow.

The opening and submission of a transaction is successful

Start transaction; (Start transaction) commit; (End transaction)

Failure of

Start transaction; (Start transaction) rollback; (Transaction end rollback)

Use case

Normal index, primary key index, unique index, full-text index normal index: Index

Just speed up the query.

Primary KEY index: primary key

Cannot repeat, the primary key is at most one

Unique indexes: Unique index

Values on a row cannot be duplicated

Full-text index: Fulltext index indexing

ALTER TABLE buy add index index1 (sum); ALTER TABLE table name add indexed type [index name] (column name); (The index name here is optional)

View all indexes on a single table

Show index from Buy;show index from table name;

Delete Index

ALTER TABLE buy DROP INDEX index1;alter table name drop index name;

Use case

Stored Procedures

It means creating a function.

Create a storage

CREATE PROCEDURE function1 () Beginselect * from buy;end$ (here function1 is the storage name, no parameters, select * from buy; Is SQL statement, $ is modified Terminator)

Using storage

Call Function1 () $call Store name () $

View Storage

Show procedure status$

Delete Storage

drop procedure Function1$drop Procedure store name $

To create a stored with a parameter

CREATE PROCEDURE function2 (sum tinyint) beginif sum=4 Thenselect * from class where Id>sum;elseselect * from class wher E id<=sum;end if;end$

Use case

MySQL's basic statement