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