MySQL Base statement

MySQL database this represents a software, and then the software can have a lot of database, which means that the library can also be considered an instance, and then every real
There are many tables in the case.
SQL statement Structure
Manipulate the data inside the table: Select Insert Update Delete to delete
Action on an instance database or table: Create drop alter show
Enter the database to view the instances inside the library
show databases;
Enter an instance
Use MySQL
View all the tables in the instance
Show tables;
See which instance you are currently viewing where you are in the database
Select Database ();
The operation of the database in the system, mainly for the use of shell scripts, directly outside the system to operate, and do not need to log in to the database to operate
, the main user shell script, where E is the meaning of execute
Mysql-e ' show databases '-uroot-p123456
Mysql-e ' select * from Mysql.user \g '-uroot-p123456
Create a database
Create database Zyg; created a name for the DB named Zyg, the database is named without a special symbol, if you want to use the anti-quote
Cover it up as shown below
Create database ' zyg-1 ';

Show create database zyg; to be able to see back in the background with anti-quotes

Select Now (), user (), database (); View the current time of the system, which user and current database

Deleting a database
Drop database ' zyg-1 ';
Files that delete a database can be deleted by dropping the database, or by querying the configuration file DataDir the directory of the file, and then entering this
Directories to find the location of the file where the directory is located in order to delete the database, so the deletion is also possible, of course, in turn, the database is prepared
We can also make a backup copy of this file
Rm-rf/data/zyg-1

Through if exists to determine whether the library exists, and then do the deletion, mainly used in the shell script, such as the execution of a script to delete the database, when the
Now this database does not exist, that execution will interrupt the execution process, this time the shell execution process is interrupted, then through if exists this condition
The log is not present when the statement is executed, so the script is not interrupted
Drop database if exists ' zyg-1 '; This way the script is executed without the error log message interrupting the script
The reverse is the same.
Create database if not exists HA;

Use HA to enter the database and create the table in the database, as shown below
CREATE TABLE student (id int (), name char (+), age int (10)); Create a table that represents the columns of the table, which indicates that this
There are three columns in a table
int default complement is 11,char default complement is 1
drop table student; Delete a table
Show tables; View all the tables in this library
DESC student; View table Structure
You can also use the following methods to view table structure conditions
Explain Mysql.user;
Show columns from Mysql.user;
Show fields from Mysql.user;
Show columns from mysql.user like '%user ';
Like represents a fuzzy match, the following different usages of fuzzy matching
User: Full match user display
%user: End of user
user%: Starting with user
%user%:user may appear anywhere

See which commands were executed by the build table
Show CREATE TABLE student \g is actually a process of looking at a table.
Show CREATE DATABASE HA \g View a process for building a library

CREATE TABLE Student2 (id int (), name char (+), age int) Engine=myisam DEFAULT Charset=utf8; You can create a table by creating
Specify the storage engine and character set when
drop table Student2; Delete Student2 This table

Performance_schema is pre-read, but it consumes performance the first time you read and write, so it is generally recommended to turn off this read-ahead function.
Mysql-usystem-p123456-a when entering the database, take the-a option to close the read-ahead function

Modifying tables using ALTER
to modify table names
Syntax: ALTER TABLE name rename new name This means to change one table to another
ALTER TABLE class rename classes; Change the table name of class to classes the name
Modify the field name in table
Syntax: ALTER TABLE name modify the type of field name to modify
ALTER TABLE classes modify BJ Int (10);
ALTER TABLE classes modify XH int (15);
Modify the original type of the field or the length of the field, or change the original int to char, or change the original int (10) to int (20), etc.
, and
Modify the field type and field name
Syntax: ALTER TABLE table name Change original field name new field name new field type
ALTER TABLE classes changes XH Xuehao char (10) Classes The XH column name in the table to the number, and the field type is also modified to
Char (10)
Note: The difference between change and modify
changes can be made to the column name and the type of the column
Modify can only change the column type, cannot change the column name

Add a field to a table
Syntax: ALTER TABLE table name Add field name fields type;
ALTER TABLE classes add sex enum (' M ', ' W '); Add a sex column to the columns in the table, and remember that this is the new column. This new column is added by default
To the last column, if you need to add new columns to the last column, use the following method to add new
ALTER TABLE classes add UID Int (ten) first; Add column to First row
ALTER TABLE classes add address char (+) after Xuehao; This means adding a new column to the back of the learning number.
The order in which the fields are stored in the table can be stored arbitrarily, which is not the case, because the fields displayed by the query can not be stored in the order of the table.
Display, the display is completely based on the order of select queries.
Select Stname,sex,age from classes; So the presentation is done exactly this way, so do not struggle with the original table storage
Sequence of

Delete a field from a table
Syntax: ALTER TABLE name drop field name;
ALTER TABLE classes drop address; Delete the Address field from the Classes table
Drop use to delete database, table, field, etc.

Manipulating data within a table
Actions for inserting fields
Syntax: INSERT into Table name values (field 1, Field 2, Field 3);
Insert into classes values (2,1, ' Zhangsan ', ' M '); Insert the following field into the classes table, remembering the English alphabet using the ' ' Package
Contains, this means that all columns must have values to be able to insert the data into the table, so that all the values need to be filled, directly resulting
And sometimes there are unreasonable places.
Insert into classes (Bj,xs,sex) VALUES (5, ' Wangwu ', ' M '); Specify which columns to insert into the table
The columns that are not specified are displayed in the default form, and if NULL is the default, then NULL is displayed if not specified.

Inquire
SELECT * from classes; This is a query for the entire table.
Select Sx,sname from classes; This is a query on a single column.
Select name from classes;
The order of this query can be freely combined, any combination to display the desired query results, the query shows that the results are not related to the order stored in the table.

Delete
Syntax: Delete from table name condition
Delete from classes; This means that all the data in this table will be classes and executed directly into the table.
Delete from classes where xs= ' Lisi '; This indicates that the XS field in the Classes table is Lisi this record is deleted
MySQL is deleted by default when executing the delete, there will not be a commit commit in the table to delete the data, of course, the Auto-submit function
Is can be closed directly
Where statement decoration limits the scope of the preceding criteria
Delete from classes where-age is null;
This is usually done by qualifying the query, and then checking to see if the results of the query are items that need to be deleted, if the query results are items that need to be deleted
, then it is OK to delete the result of the query by deleting it, which ensures that deleting this dangerous action will not be deleted by mistake.
Set autocommit=0 turn off autocommit, which is turned on by default and needs to be turned off before it can be set to not automatically commit
For example
Set autocommit=0;
Delete from classes where id=1;
SELECT * from Classes;
You can see in the query that the Id=1 field is actually deleted, but you can retrieve the deleted item by rollback this command.
Rollback
SELECT * from Classes; You can see that the deleted items are back again.
If you are sure that you can delete it,
Commit
This can be found that even through rollback can not find the data back, the data is directly all lost.

Update record
Syntax: Update from table name condition
Update classes set bj=9; This means that all the data from the BJ column in the classes table is changed to 9;
Update classes set bj=10 where xs= ' Zhangsan '; This represents the classes of the Xs=zhangsan in this line of data in the BJ changed to 10;
Update classes set bj=10,age=23 where xs= ' Zhangsan '; This represents a one-time change of data for two columns at once.
Update classes set uid=3,sex= ' W ' where xs= ' Lisi '; The result of modifying two columns in a table at once by set and then by separating them
。

Inquire
Select Name,age from Classes; When you look at what you need to see, define the options you need to query directly.
To repeat the query distinct
Select distinct Uid,xs from classes where sex= ' M ';
Using and and or for multi-condition queries
SELECT * from classes where uid>4 and uid<6; Represents the row of query uid=5, this means that all of the search to meet the query out
SELECT * from classes where uid>4 or uid>6; The one that satisfies one of these conditions is queried.

MySQL default is case insensitive
SELECT * FROM classes where xs= ' FF '; Through the query can find MySQL default is a case-insensitive query;
This is done by binary binary, so that it can be case-sensitive, converting characters directly to ASCII encoding to be processed, so
Out of this code is not the same, so that it can be case-sensitive
SELECT * FROM classes where binary xs= ' FF ';
SELECT * FROM classes where binary xs= ' FF ';
The results of the above two queries are not the same, mainly through binary, the condition of the query is loaded

MySQL Query sort
Syntax: SELECT DISTINCT field 1, Field 2 from table name order by field name;
The default is ascending ASC
SELECT * FROM Classes order by BJ; The results of the query are sorted by the sort of BJ ascending order, sorted from small to large
SELECT * FROM Classes ORDER by BJ Desc; The results of the query are sorted in descending order, from large to small

MySQL command help
Syntax: Help + subordinate Keywords
Such as
Help Show
Help Select
Help Delete
And so on .....

MySQL Base statement