Summary: MySQL (Basic, field constraint, index, foreign key, stored procedure, transaction) operation syntax

1. Display the list of databases

show databases; # View all current databases

Show Databases \g #以行的方式显示

2. On the command line, execute the SQL statement

Mysql-e ' show databases '-uroot-p123456

mysqlshow-uroot-p123456 # not used, just remember the one above.

3. Create DATABASE syntax

Create database name;

For example: Create database ' ha-test ';

4. Switching the database

Use Ha-test;

5. When logging in, switch directly to the specified database

mysql-uroot-p123456 < database name >

6. View the current time of the database, the user, and the current database

Select Now (), user (), database ();

7. Deleting a database

The 1:

Drop databases < database name >;

2: Delete the corresponding file directly from the database data directory

Cd/usr/local/mysql/data

# switch to the database storage directory,/ETC/MY.CNF can find

MV [email protected]/tmp

8. if (not) exists

# when creating and deleting, check for presence, in shell programming, available to

Drop database if exists ' ha-test ';

Create database if not exists HA;

9. Create a table

CREATE TABLE Student (

ID Int (20),

Name Char (40),

age int);

10. After switching the database, view all tables in the current database

Use HA;

Show tables;

11. View table Structure DESC # There is another layer of meaning, after order by, indicating descending order

DESC student;

Mysql> explain Mysql.user; # similar to desc mysql.user;

Mysql> show columns from Mysql.user;

Mysql> show fields from Mysql.user;

Mysql> Show columns from mysql.user like '%user ';

12. View table Creation statement # \g by row

Show CREATE TABLE student \g

13. When creating a table, use the specified storage engine and character set

CREATE TABLE ' Student ' (

' id ' int (DEFAULT NULL),

' Name ' char (+) DEFAULT NULL,

' Age ' int (one) DEFAULT NULL

) Engine=innodb DEFAULT Charset=utf8

# #---> Specify the storage engine and character set when creating a table

13. Delete a table

drop table Student2;

14. Prohibit pre-reading table information

mysql> use Performance_schema;

Reading table information for completion of table and column names

Can turn off this feature to get a quicker startup with-a

MYSQL-UROOT–P123456-A # Prevent read-ahead table information

# when logging in to MySQL, add-a option

14. Modifying property information for a table

ALTER TABLE table name

Add Field Name

Column type

[Not Null|null] [PRIMARY KEY] [Unique] [Auto_increment] [Default value]

14.1 Modifying table names

ALTER TABLE name rename new table name;

14.2 Modifying a field type in a table

ALTER TABLE name modify the type to modify for the field name to be modified;

14.3 Modify the name of a field in a table---> must specify a type at this point, even if the type is not changed

ALTER TABLE name change the original field name new field name new field type;

14.4 Adding a new field to a table

ALTER TABLE table name Add field name fields type;

14.5 Specify the field location to add

# field Expire Line

ALTER TABLE students add UID Int (ten) first;

# field after age

ALTER TABLE students add address char (+) after age;

14.6 Delete a field from a table

ALTER TABLE name drop field name;

15.1 inserting information into a table

The 1:

Insert [into] Datasheet name [(field list)] Values|value

(Expression |null|default,...),

(Expression |null|default,...)

The 2:

Insert [into] data table name set

Field name = value,

...

Difference: The difference between insert and insert...set--->insert...set can be queried with subqueries.

15.2 Other ways to insert information into a table

Way 1: Field values are in the same order as the table was built

Insert into Table name values (field value 1, field value 2, field value 3);

Way 2: Field name = field value, this method does not tangle order

Insert into Table name values (field name = field value,...);

Way 3: According to the field name order specified earlier

Insert into table name (field name, field name ...) VALUES (field value, field value ...);

Way 4: Inserting multiple data

Insert into table name values

(Field value, field value ...),

(Field value, field value ...) ...;

16. View the information in the table SELECT statement

View all information in a table * represents all fields in a table

SELECT * from table name;

View specify one or more field information

Select field name, field name ... from table name;

Cross-database view

SELECT field from database name. Table name;

17. Delete Information from the table

Delete from data table name [WHERE condition]

If the Where condition is omitted, all records are deleted

Delete from students where id=3;

Delete from students where-is null;

Delete from students; # delete all records in a table

18. Modify the information in the Update table

Update data table name SET field name = Value,... [Where Condition]

If you omit the Where condition, all records are updated.

Update students set sex= ' M ' where id=2;

Update students set id=2; All of them become 2.

Update students set Stname= ' Zhangsan ', age=21 where uid=1;

. WHERE clause

Select Field Name 1, field name 2 from table name [where condition];

20. Query results to go heavy

Select distinct name,age from students;

Select DISTINCT id,name,age from students where id=3;

Distinct only for query statements directly processing the demerit of the query, not the actual data in the database has an impact

. and or multi-condition queries

# and priority is higher than or, occurs at the same time, without parentheses, first and after or

Using and and or for multi-condition queries

When the OR and and are present, the values of and are counted first, and the logic is executed first.

Select Id,name,age from students where id>3 and age>25;

Binary type conversion operators

# Troubleshoot case-insensitive issues in queries

MySQL queries are case-insensitive and can be modified binary to solve

SELECT * from students where binary name= ' JK ';

Binary is a type conversion operator

It is used to force the string behind it to be a binary string, which can be understood to be case-sensitive when comparing strings.

Select field list from data table [[as] alias] [Where Condition]

23. Use of aliases

Alias:

data table [[as] aliases]

Select Aa.money,bb.name from

Product_offer_instance_object_xxx as AA,

Product_offer_instance_object_zzz BB

where aa.id = Bb.id

field name [[as] alias]

For example:

Select product_offer_instance_object_id as ID,

Product_offer_instance_object_name name,

Coumn33 ' Amount ' from TableName;

24. Sort the results of the query

# Sort by one or more fields you specify

# can be used to repeat fields

Select DISTINCT field 1, Field 2 from table name order by field name;

ASC Ascending default

Desc Descending

Select DISTINCT ID from students order by id DESC;

Sort:

Ascending: Order By "sorted fields" ASC default

Descending: Oredr by "sorted field" desc

Sort multiple fields

Select Bname,price from books where Price in (50,60,70) Order by Price Desc,bname desc;

Summary of data additions and deletions in table

The SELECT statement returns 0 or more records, a record read operation

Insert, UPDATE, delete only returns the number of records affected by this operation;

Functions in the MySQL database

Select Now (); --Print the current date and time

Select Curdate (); --Print the current date

Select Curtime (); --Print the current time

Select Database ();--Print the current databases

Select version (); --Print MySQL version

Select User (); --Print the current user

Some information query methods for MySQL database

Show variables;--viewing system Information

Show global variables; --View global variables

Show global variables like '%version% '; --View version information

Show variables like '%storage_engine% '; --The default storage engine

Show engines;--see which storage engines are supported

Show status;--View system run status information

Show global status like ' thread% '; --View current running status

27. Help

Help show;

? Show

28. Database Import

You must create an empty database before you import the database

The 1:

Create database book;

mysql-uroot-p123456 Book < Book.sql

The 2:

Create database book;

mysql> use book;

mysql> Source/root/book.sql #sql脚本的路径 This path is relative to the current path

29. Database Export

To export a database:

Usage:

Mysqldump-u user name-p database name > exported file name

30. Query results Export outfile # MySQL users need access to the exported path

For example:

SELECT * into outfile '/tmp/123.txt ' from books;

Select Name,ctfid,birthday,mobile,tel,email from info where ctfid like ' 130000% ' into outfile '/tmp/fuping-of-rujia ';

Select Bname,publishing,price from books where price=30 or price=40 or price=50 or price=60;

31.

In operator

The in operator is used in the WHERE expression to support multiple selections in the form of list items, with the following syntax:

WHERE column in (Value1,value2,...)

WHERE column not in (Value1,value2,...)

Not in and in opposite

When in is preceded by the NOT operator, it means the opposite of in, that is, not selected within these list items.

32. Arithmetic operators

= equals,<> Not equal to!=,> greater than,< less than, >= greater than equals, <= less than equals

33. Range Arithmetic

[NOT] between ....

Between and can be substituted in a way greater than or less, and use greater than less meaning to express more clearly

Query Scope Summary:

There are three kinds of query conditions here: Between...and,or and in

(30,60) >30 and <60

[30,60] >=30 and <=60

Fuzzy matching query:

Field name [not]like ' wildcard character '----'% any number of characters

For example

SELECT * from students where stname like '%l%1%2%3% ';

MySQL Sub-query:

Concept: A Select is also present in the Where condition of select

Queries are nested within a query

SELECT * FROM book where price= (select Max (price) from book);

Items displayed with limit limit:

Usage:

SELECT * FROM table LIMIT [offset,] rows

Offset offsets, rows rows

For example, select * FROM table limit m,n statement

Indicates where m refers to the index at which the record starts, starting at 0, indicating the first record

n means starting from section m+1 and taking N.

36. Multiline subquery: All indicates that the minimum value in the total value returned in the subquery is less than

Mysql> Select Bname,price from Books where price< (select price from books where publishing= "Electronics publishing House" ORDER by Price ASC limit 0,1);

Mysql> Select Bname,price from Books where Price<all (select price from books where publishing= "Electronics publishing house");

37. Connection query:

Internal connection usage:

Select field from table 1 inner JOIN table 2 on table 1. field = table 2. Field

External connection

Left join: Select field from a table left join B table on join condition

Right connection: Select field from Table A to join B table on condition

Right connection, multiple table connections available

38. Aggregate functions--functions: Blocks of code that perform specific functions.

Arithmetic arithmetic functions:

38.1 sum: Sum ()--Displays the total price of all books

Select SUM (price) from books;

38.2 Average value: AVG ()

For example: Find the average price for all books with book ID less than 3

Mysql> Select AVG (price) from books where bid<=3;

For example: The price of the query book is less than the average price of all data (nested subqueries)

Select Bname,price from Books

Where price< (

Select AVG (price) from books);

38.3 max value: Max ()

Find the most expensive books in all books

Mysql> Select Bname,max (price) from books; # this method is wrong.

Mysql> Select Bname,price from Books where price= (select Max, from books);

38.4 min Value: Min ()

Find books that are cheap in all books

Mysql> Select Bname,price from Books where price= (select min (price) from books);

38.5 count () statistics record number:

Number of books with a statistical price greater than 40

Mysql> Select COUNT (*) from books where price>40;

Count () can also increase the content you need, such as adding distinct to work with

38.6 string function substr (string, Start,len)

Intercept: From start, intercept Len long.

# Start counting from 1 onwards.

Mysql> Select substr (btypename,1,6) from category where btypeid=10;

For example: capturing Chinese characters

Mysql> Select substr (btypename,8,10) from category where btypeid=1;

+------------------------+

| substr (btypename,8,10) |

+------------------------+

|  Application | Windows apps

+------------------------+

38.7 Case Conversion # case conversion, not for Chinese, will appear garbled

Uppercase: Upper ()

Mysql> Select Upper (bname) from books where bid=9; # There are Chinese will appear

Lowercase: Lower ()

Mysql> Select Lower (bname) from books where bid=10;

38.8 Date

Current date: Curdate ():

Current time: Curtime ();

Current date and Time: Now ();

39. Arithmetic operation: +-*/# Note, MySQL does not have self-added minus + + + +

For all books with a price of less than 40 yuan, a 5 yuan hike

mysql> Update books set price=price+5 where price<40;

80 percent for all books priced above 70 yuan

mysql> Update books set price=price*0.8 where price>70;

40. Field modifiers

[Not Null|null] [PRIMARY KEY] [Unique] [Auto_increment] [Default value]

40.1 null and NOT NULL modifiers

40.2 Default Settings field defaults

Summarize:

If the field is not set to default, MySQL depends on whether the field is null or NOT NULL, or null if it is nullable. If it cannot be null, error:

If the Time field, the default is the current time, insert 0 o'clock, the default is the current time.

If it is an enum type, the default is the first element.

40.3 auto_increment field constraint autogrow can only be decorated with an int field.

This is useful for primary keys. You can create a unique identifier for each record

41. Clear all records in the table

Method One: Delete Does not add a where condition, emptying all table records.

But delete does not clear 0 auto_increment value

Method Two: Truncate

Role: Delete all records of the table and clear 0 auto_increment values. The newly inserted record starts at 1.

usage:truncate table name;

42. The index is like a directory in front of a book

42.1 the most basic index, not uniqueness, is to speed up the query speed

Method One: Add an index when creating a table

CREATE TABLE Table name (

Column definition

Index index name (field)

Index name (field);

Note: You can use the key, or you can use index. Index name (field), index name, you can add or do not add, do not use the field name as the index name.

Method Two: When the table is created, use ALTER to add an index to the table:

ALTER TABLE name add index index name (field 1, field 2 ...);

42.2 Viewing Index desc

DESC demo; # View the description of the table, which can be seen in the key column

42.3 Deleting an index

ALTER TABLE demo drop key pwd; Note that the PWD here refers to the name of the index, not the field of the PWD in the table.

42.4 adding an index

Mysql> ALTER TABLE demo add key (PWD);

42.5 all values for a unique indexed index column can only occur once, that is, must be unique

Uniqueness allows null values < allow NULL >

To create a unique index:

Method One: Add a unique index when creating a table

CREATE TABLE Table name (

Column definition:

Unique key index name (field);

);

Method Two: Add a unique index when modifying a table

ALTER TABLE name add unique index name (field);

42.6 primary key index query by primary key is the fastest, only one primary key column per table

Method One: Create a table to create a primary key index

CREATE TABLE Demo4 (

ID int (4) NOT NULL Auto_increment primary key,

Name varchar (4) not NULL);

CREATE TABLE Demo5 (

ID int (4) NOT NULL auto_increment,

Name varchar (default) NULL,

Primary key (ID));

Show index from Demo5 \g--View index in table

Method Two: Create a table after adding < not recommended >

mysql> ALTER TABLE DEMO5 change ID ID int (4) is not null primary key auto_increment;

42.7 Delete primary key index test auto_increment

Remove the cause of this condition being auto_increment

mysql> ALTER TABLE DEMO5 change ID ID int (4) is not null;

mysql> ALTER TABLE DEMO5 drop PRIMARY key;

Summary: Primary key index, uniqueness Index difference: Primary key index cannot have null, uniqueness index can have null value

Composite Index

An index can contain one, two, or more columns. An index on two or more columns is called a composite index

43. Federated PRIMARY Key Creation example

CREATE TABLE Firewall (

Host varchar (NOT NULL),

Port smallint (4) is not NULL,

Access enum (' Deny ', ' allow ') is not NULL,

Primary KEY (Host,port)); # The Federated primary key can only be used in the last face

44. Summary: When building a table, if you add various indexes, the order is as follows:

CREATE TABLE Table name (

Field definition,

PRIMARYKEY (' BId '),

UNIQUE KEY ' bi ' (' bimg '),

KEY ' bn ' (' bname '),

KEY ' Ba ' (' author '));

45. Full-text indexing can only be used in varchar text

To create a full-text index:

Method One: Create the table when you create it

CREATE TABLE Table name (

Column definition,

Fulltext Key index name (field));

Method Two: Add when modifying a table

ALTER TABLE name ADD Fulltext index name (field);

Strong note: MySQL comes with a full-text index only for data tables that are MyISAM by the database engine, and if it is a different data engine, the full-text index does not take effect

MySQL's own full-text index can only be full-text search in English, currently cannot be full-text retrieval of Chinese.

Generally submitted to third-party software for full-text indexing

http://sphinxsearch.com/

46. FOREIGN KEY constraint foreign key

To create a FOREIGN KEY constraint:

Method One: Creating a foreign key from create table

Usage:

CREATE table data table name (

...,

[CONSTRAINT [constraint name]]

FOREIGN key [Foreign key field] REFERENCES

[Foreign key table name] (Foreign key field, foreign key field 2 ...)

[on DELETE [CASCADE | RESTRICT]]

[on UPDATE [CASCADE | RESTRICT]]);

Usage: (Lite version)

Foreign key The field of the current table references

External table name (associated field)

Type=innodb

Note: The following 4 conditions must be met for a successful creation:

1. Ensure that the referenced tables and fields exist.

2. The fields that make up the foreign key are indexed.

3. The storage engine must be specified with type: InnoDB.

4. Foreign key fields and associated fields must be of the same data type.

#创建时, if the table name is a SQL keyword, use the anti-quote '

Method Two: Create a foreign key and cascade update via ALTER TABLE, CASCADE Delete

Usage:

ALTER TABLE data table name add

[CONSTRAINT [constraint name]] FOREIGN key (foreign key field,..) references

Data table (Reference field,...)

[ON update cascade|set null|no action]

[ON delete cascade|set null|no action]) Type=innodb;

46.1 viewing foreign key information

Mysql> Show CREATE TABLE order1;

46.2 Delete foreign key:

Usage:

ALTER TABLE data table name drop FOREIGN KEY constraint (foreign key) name

47. View

47.1 Creating a View

Usage:

Create view name (that is, virtual table name) AS SELECT statement

47.2 viewing statements that create views

Show CREATE VIEW BC \G

47.3 updating or modifying a view

Usage:

Alter VIEW view name (that is, virtual table name) as SELECT statement.

Update view name (that is, virtual table name) set

# Views in the general database are not supported for updates and modifications

Update BC set bname= ' HA ' where price=34;

47.4 Deleting a view

Drop view name;

48. Modifying SQL execution symbols

Note that before you define a stored procedure, it is best to use delimiter to modify the execution symbol

# Otherwise, when you write a stored procedure, it terminates prematurely.

Usage:

Delimiter New execution symbol

49. Defining Stored Procedures

Usage:

CREATE PROCEDURE procedure name (parameter 1, parameter 2 ...)

Begin

SQL statements;

End

*). viewing stored procedures

A). View the contents of the stored procedure:

Show CREATE procedure [Stored procedure name] \g

B). View Stored Procedure Status:

Show procedure status \g view all stored procedures

*). To modify a stored procedure:

Use the ALTER statement to modify

ALTER {PROCEDURE | FUNCTION} Sp_name [characteristic ...]

Characteristic:

{CONTAINS SQL | NO SQL | READS SQL DATA | Modifies SQL DATA}

| SQL SECURITY {definer | INVOKER}

| COMMENT ' String '

The Sp_name parameter represents the name of a stored procedure or function

The characteristic parameter specifies the properties of the stored function

CONTAINS SQL indicates that a subroutine contains SQL statements, but does not contain statements that read or write data;

No SQL indicates that the subroutine does not contain SQL statements

READS SQL data represents a statement in a subroutine that contains read data

Modifies SQL data represents a statement in a subroutine that contains write data

SQL SECURITY {definer | INVOKER} indicates who has permission to execute

Definer that only the definition can perform

Invoker indicates that the caller can execute

COMMENT ' String ' is a comment message.

--

/**/

*). To delete a stored procedure

Usage:

Method One: DROP PROCEDURE procedure Name

Mysql> drop procedure P_inout;

Method Two: DROP PROCEDURE IF exists stored procedure name

This statement is used to remove a stored program. You cannot delete another stored procedure in one stored procedure, only another stored procedure can be called

49.1 calling a stored procedure

Usage:

Call Procedure Name (parameter 1, parameter 2);

49.2 stored procedure parameter types

In Parameter feature: Read external variable value, and valid range only within stored procedure

Out parameter feature: Do not read the external variable value, retain the new value after the stored procedure has finished executing

Set @p_out = 1; # when defining a variable, it is recommended to add an @ compile to recognize it at a glance.

InOut parameter features: Read external variables, retain new values after the stored procedure executes < similar bank deposits >

49.3 use of Stored procedure variables

Variable definition:

DECLARE variable_name [, variable_name ...] datatype [DEFAULT value];

DataType data types for MySQL, such as: int, float, date, varchar (length)

Variable assignment: SET variable name = expression Value [, Variable_name = "expression ...]

Variable assignments can be inherited from different stored procedures

49.4 notes for Stored procedure statements

"--": Single-line comment

"/*.....*/": generally used for multi-line annotations

50. Scope in a variable scope reference programming language

51. Stored Procedure Flow control statements

51.1 article statements

Usage:

*). Single branch:

If [judging condition] Then

...

End If

* *). Dual Branch:

If [judging condition] Then

...

Else

...

End If

51.2 Case Branch Statement:

Usage:

case [variable name]

When [variable value] Then

...

When [variable value] Then

...

Else

...

End case;

51.3 loop control Statements

*). While loop

Whlie [Judging condition] do

...

End while;

* *). Repeat cycle

Set v=0; --Set the loop variable

Repeat-marking cycle start

...--Looping statements

Set v=v+1; --Cyclic value change

Until v>=5--cyclic judging conditions

End repeat; --Loop End tag

). Loop loop

# loop loop does not require initial conditions,

# leave means to end the loop

Loop_lable:loop

...

Set v=v+1;

If V>=5 Then

Leave loop_lable;

End If;

End Loop;

). LABLES Marking

The label can be used before the begin repeat while loop statement

Can only be used in front of legitimate statements (not recommended, easy to mess)

). Iterate Iteration

To start a compound statement from scratch by referencing the label of a compound statement

52. Trigger:

52.1 Create a trigger:

Usage:

Create TRIGGER Trigger Name

Triggered by the timing of the action on the table name for each row

The trigger state.

Parameter description:

Trigger Name: Define Yourself

Time to trigger: Before/after before or after performing the action

Triggered action: Refers to the type of statement that fires the trigger <insert,update,delete>

Trigger creation syntax four elements:

1. Monitoring Location (table)

2. Monitoring Events (Insert/update/delete)

3. Trigger Time (After/before)

4. Trigger Event (Insert/update/delete)

For example:

Mysql> delimiter//

Mysql> CREATE trigger Delcategory after delete on category for each row

--delete from books where btypeid=3;

//

52.2 viewing triggers:

1). View the creation process

Mysql> Show CREATE Trigger Delcategory\g

2). View trigger Details

Mysql> Show Triggers\g This view all

52.3 Delete trigger:

Usage:

Drop trigger trigger name;

mysql> drop trigger delcategory;

Is the trigger permanently retained?

53. Business

Methods for MySQL transaction processing:

1, with Begin,rollback,commit to achieve

START TRANSACTION | Begin [Work] open transaction

commit [Work] [and [No] CHAIN] [[No] RELEASE] commits the current transaction and performs a permanent operation.

ROLLBACK [Work] [and [No] CHAIN] [[No] RELEASE] rolls back the current transaction to the start point, canceling all operations after the last start point.

SavePoint name Foldback Point

2, directly with set to change the MySQL automatic submission mode

MySQL is automatically submitted by default, that is, you submit a query, it is executed directly!

SET autocommit = {0 | 1}

# Sets whether the transaction is automatically committed and is automatically committed by default.

0: Prohibit auto-commit

1: Turn on auto-commit.

Only InnoDB and BDB types of data tables in ※mysql can support transactional processing! Other types are not supported!

As long as the transaction is not committed, it can be rolled back, the committed transaction, is not back!

Summary: MySQL (Basic, field constraint, index, foreign key, stored procedure, transaction) operation syntax