Common MySQL Syntax

First, enter MySQL and exit MySQL

1. Go to MySQL step: Open cmd command line first, command:c:\users\admin> mysql-h (domain name, can fill or not fill)-U (account)-p (password);

When the connection succeeds, the following command jumps out:

Connection Id:9 "This means: number of connections"
Current database: "This represents: the name of the databases currently operating"
Current User: [email protected] "This means: the user name of the currently logged on username @ address"
Ssl:not in use "This indicates whether encrypted link is used for SSL encryption"
Using delimiter:; "This means: command delimiter, ending with a semicolon"
Server version:5.5.48 mysql Community server (GPL) "This represents: version number of the current MySQL database"
Protocol Version:10 "This expression: protocol version"
connection:127.0.0.1 via TCP/IP "This means: connection information"
Server Characterset:utf8 "This represents: installation code for MySQL database management system"
DB Characterset:utf8 "This representation: Database Encoding"
Client Characterset:utf8 "This represents: the default encoding for the current client"
Conn. Characterset:utf8 "This means: the encoding used by the current database link"
TCP port:3306 "This representation: Port number"
Uptime:7 min Each SEC "This represents: the start time of the database [from the time of the last reboot]"

2. Exit Mysql:quit or exit (enter) or CTRL + C

Second, the MySQL basic operation

1. Create DATABASE command:mysql> CREATE DATABASE < name >

2. Show all database commands:mysql> show databases (note: last one s)

3. Delete Database command:mysql> drop databases < database name >

4. Enter database command:mysql> use < database name >

5. Conversion encoding Command:mysql> set names GBK

6. View the database command currently in use:mysql> Select Database ();

7. Current database All table Information command:mysql> show tables (note: There is a last s);

8. Export Database command: mysqldump-h (domain name)-U (account number)-p (password) database name > Local path + file name; (Note: You must exit the MySQL console before you export the database.) There are also exported file suffix names that are typically used with. sql)

9. Import Database command: First login to MySQL console c:\users\admin> mysql-h (domain name, can be filled or not filled)-U (account)-p (password); On entering the database mysql> use < database >; In the Import Database mysql> source E:/school.sql (Note: The absolute path of your SQL file is followed by source)

10. Add field command to MySQL table:mysql> ALTER TABLE < table name > Add column userid smallint not null primary key auto_increment; In this way, a field userid is added to this table dbname, and the type is smallint

11. Delete Table command:mysql> drop table student

12. Clear Table command:mysql> delete from student

13. Update Table command:mysql> update student set ' name ' = ' small a ' where ' id ' =1

Third, table operation, must be connected to a database before operation

1. Build table command: CREATE table < table name > (< Field name 1> < type 1> [,.. < Field name N> < type n>]);

For example:

Mysql> CREATE TABLE Student (

-ID smallint unsigned NOT NULL primary key auto_increment comment ' Comment id ',

, name char (a) NOT null comment ' comment name ',

Sex tinyint null default "1" comment ' note sex[male 1, female 2] ',

) engine = InnoDB;

2. Get table structure command: DESC < table name > or Show columns from < table name > also show create table < table name >

For example:

Mysql> desc Student

Mysql> Show columns from Student

Mysql> Show CREATE TABLE Student

3. Delete Table command: Drop table < table name >

For example:

mysql> drop table Student

4. Add New Data command: INSERT into < table name > (field Name 1, ... Field name N) VALUES (value 1, ...) Value N). or INSERT into < table name > values (to enter all data structures) (Note: If you want to add more than one, separate them with commas)

For example:

mysql> INSERT INTO Student values (1, ' small a ', 2), (2, ' small B ', 1), (3, ' small C ', 2)

5. Querying tables for data commands: Select < Field 1, Field 2, ... Field n> from < table name > where < condition >. or select * FROM < table name >

For example:

Mysql> SELECT * from Student

6. Delete data in table command: Delete from < table name > where < condition >. or delete from < table name >

For example:

Mysql> Delete from Student

7. Modify the data in the Table command: Update < table name > Set field = new value, ... where < condition >

For example:

mysql> Update Student set name= ' small d ' where id=3

8. Change table name command: Rename table < old name > to < new table name >

For example:

Mysql> Rename table Student to Class

9. Loading data into a database table in text mode

Example: D:/mysql.txt

mysql> Load Data local infile "d:/mysql.txt" into table Student

Iv. Types of data

1. String type

CHAR (M): fixed-length string with a maximum length of 255 bytes

varchar (M) Type: variable length up to 65 535 bytes, if varchar (n) is specified at creation time, 0~n bytes can be stored

Tinytext Type: variable-length string with a maximum length of 255 bytes

Text type: variable-length string, supports a maximum length of 65 535 bytes

Mediumtext: variable-length string, maximum length 16 777 215 bytes Supported

Longtext: variable-length string, Maximum length 4 294 967 295 bytes

2. Integral type and float type signed [plus/minus] unsigned unsingn

Tinyint type: 128 ~ 127 0 ~ 255 (2 8-1)

SmallInt type: 32768 ~ 32767 0 ~ 65535 (2 16-1)

Mediumint type: 8388608 ~ 8388607 0 ~ 16777215 (2 24-1)

int type:-2147483648 ~ 2147483647 0 ~ 4294967296 (2 32-1)

bigint type: 9223372036854775808 ~ 9223372036854775807 0 ~ 18446744073709551615 (2 64-1)

3. Date and Time type

Date: Yyyy-mm-dd format value/range 1000-01-01 ~ 9999-12-31

Time: Hh:mm:ss format value/range of values -838:59:59-838:59:59

DateTime type: YYYY-MM-DD hh:mm:ss format represents date and time/range 1000-01-01 00:00:00 ~ 9999-12-31

Type timestamp: Timestamp/range of values in YYYYMMDDHHMMSS format 19700101080001--20380119111407

Year Type: YYYY format years/range of values 1901~2155

V. Field constraints

We just have to define the data type of the field is not enough, we also need some additional attributes to constrain or standardize the field

UNSIGNED can only be used to set the data type, does not allow negative numbers, the maximum storage length will increase by one times, Zerofill can only be used to set the numeric type, before the value automatically with 0 of the insufficient number of digits, auto_increment If you define an optional Auto_increment property for an integer data column in a data table, when the user inserts a new record into the data table, MySQL automatically assigns the current maximum value of the integer data column to the whole number field in the new record after adding 1; There are several issues to be aware of when using the Auto_increment property: This property must be used in conjunction with not NULL, PRIMARY KEY, or UNIQUE attribute; Each data table can have a maximum of one auto_increment data column; This automatic ID value generation mechanism for MySQL only works if the user inserts a new record with the Insert command, and does not explicitly give a value or NULL for the ID field. If the user gives a specific value and the value appears in the ID column, MySQL will use this ID value to generate a new record;

NULL and NOT NULL default to NULL, that is, inserting a value without inserting a value in this field, default to a null value, and if not NULL, you must fill in the value in this field when inserting the value default can specify a default value by this property, if no value is added to this column, Then the default addition of this value primary key (PRIMARY key) can uniquely identify a row in a table for a property or property group. A table can have only one primary key, but there may be multiple candidate indexes. Primary keys often form referential integrity constraints with foreign keys, preventing data inconsistencies. The primary key guarantees that the record is unique and the primary key domain is not empty, and the database management system automatically generates a unique index for the primary key, so the primary key is also a special index. A foreign key (foreign key) is one or more columns that are used to establish and strengthen links between two table data. FOREIGN key constraints are primarily used to maintain data consistency between two tables. In short, the foreign key of the table is the primary key of the other table, and the foreign key ties the two tables together. In general, to delete a primary key in a table you must first make sure that no other table has the same foreign key (that is, the primary key in the table does not have a foreign key associated with it)

VI. MySQL operator

1. Comparison operators

= equals
> Greater than
< less than
>= greater than or equal to
<= less than or equal to
<> Not equal to
!> not greater than
!< not less than

% matches any number of characters

_ Match any one character

2. Logical operators

And if the combined condition is true, returns True

OR if the condition of the combination is one of true, returns True

Not if the condition is FALSE, returns TRUE

Vii. aggregation function

SUM () sum
AVG () Average
Number of records in COUNT () expression
COUNT (*) calculates the number of records
Max Maximum Value
Min min value
VAR Variance
STDEV standard Error
First value
Last value

Eight, query words

1. Sort the result with an ORDER BY clause (ASC means ascending, default, desc descending)

The order clause sorts the query results by one or more (up to 16) fields, either ascending (ASC) or Descending (DESC), ascending by default

The order clause is usually placed at the end of the SQL statement

Multiple fields are defined in the order clause, sorted by field order

For example: Sort the age of a student

SELECT * FROM student ORDER by age DESC;

The 2.LIMIT clause restricts the results of the query, often we don't want to take out all the data at once

Limit has two parameters: one parameter, which indicates the number of rows to get! Two parameters, the first parameter indicates the number of rows to go from, the second argument indicates how many rows to take

3.LEFT JOIN on connected table query

There are three ways to query a table: Left join and right-connected

Left join: Main Table on the left, if the main table does not have data, will not return results, even if there is data from the table
Left join ' table name ' on even table condition

Right-hand side: The table on the right is the main, if there is no data from the table, it will not return results, even if the main table has data
Right join ' table name ' on even table condition

Nellian: Two table is the main, the two tables must have data, the results will be returned.
Inner join ' table name ' on even table condition

ix. functions of MySQL

Mysqli_connect connection database, parameter is access address, account number, password, database name

Mysqi_connect_errno return error status code for database connection

Mysqli_connect_error return error message for database connection

Mysqli_close Close database connection, parameter is database connection

Mysqli_query Execution of SQL statements once to the database

Mysqli_num_rows get the number of rows in the result set

Mysqli_fetch_array gets a row from the result set as an associative array, or a numeric array, or both, the second parameter index array mysqli_num the associative array Mysqli_assoc both have Mysqli_both

Mysqli_fetch_assoc getting a row from the result set as an associative array

Mysqli_fetch_row a row from the result set as an enumerated array

Mysqli_fetch_object a row from the result set as an object

Mysqli_free_result Releasing result memory

X. Business

A transaction is a collection of a series of update processing that needs to be performed in the same processing unit. By using transactions, you can manage the submission and cancellation of database update processing in databases in the database. The termination instructions for a transaction include commit (commit processing) and rollback (cancel processing). The transaction of the DBMS has four characteristics of atomicity (atomicity), consistency (consistency), isolation (isolation), and persistence (durability). The first letters of these four properties are usually combined, collectively known as ACID properties.

Example: Give a June minus 10 yuan, give B June plus 10 Yuan. START TRANSACTION; UPDATE user SET money=money-10 WHERE name = ' a June '; UPDATE user SET money=money+10 WHERE name = ' B June '; COMMIT;

(note), the following are some of the management experience of using MySQL in the network

In Windows, MySQL exists as a service and you should ensure that the service is started before use, and that the available net start MySQL command is not started. While Linux starts with the "/etc/rc.d/init.d/mysqld start" command, note that the initiator should have administrator privileges.
The newly installed MySQL contains a root account with a blank password and an anonymous account, which is a great security risk, for some important applications we should improve security as far as possible, the anonymous account should be deleted, the root account password, the following commands can be used:
Use MySQL;
Delete from User where user= "";
Update User set Password=password (' NewPassword ') where user= ' root ';
If you want to restrict the logon terminal used by users, you can update the user's host field in the user table, and you should restart the database service when you make the above changes, and you will be able to log in with a command like this:
Mysql-uroot-p;
Mysql-uroot-pnewpassword;
MySQL mydb-uroot-p;
MySQL Mydb-uroot-pnewpassword;
The above command parameters are part of the common parameters, which can be referenced in detail in the documentation. The mydb here is the name of the database to log in to.
In the development and the actual application, the user should not only use the root user to connect the database, although uses the root user to carry on the test to be convenient, but will bring the system the significant security hidden danger, also is not advantageous to the management technology enhancement. We give the most appropriate database permissions to the users used in an application. A user who only inserts data should not be given permission to delete the data. The user management of MySQL is implemented through the users table, there are two common methods for adding new users, one is to insert the corresponding data row in the user table, set the appropriate permissions, and the other is to create a user with some kind of permission through the grant command. The common usage of grant is as follows:
Grant all on mydb.* to [e-mail protected] identified by "password";
Grant Usage on * * to [e-mail protected] identified by "password";
Grant Select,insert,update on mydb.* to [e-mail protected] identified by "password";
Grant Update,delete on MyDB. TestTable to [e-mail protected] identified by "password";
To give this user the ability to manage the permissions on the object, you can add the WITH GRANT option after Grant. For users added with the Insert User table, the password field applies the password function to update the encryption to prevent the malicious person from stealing the password. For those who have not used the user should be given clearance, the permission of the user should be in a timely manner to reclaim permissions, recycling permissions can be updated by the user table corresponding fields, you can also use the revoke operation.
The following is an explanation of the common permissions I have obtained from other sources (www.cn-java.com):
Global Administrative permissions:
File: Read and write files on the MySQL server.
PROCESS: Displays or kills service threads belonging to other users.
RELOAD: Overloads the Access Control table, refreshes the log, and so on.
SHUTDOWN: Turn off the MySQL service.
Database/data Table/Data column permissions:
Alter: Modifies an existing data table (for example, add/Remove Columns) and index.
Create: Create a new database or data table.
Delete: Deletes the record for the table.
Drop: Deletes a data table or database.
Index: Establish or delete the indexes.
Insert: Adds a table record.
Select: Displays/searches the table's records.
Update: Modifies a record that already exists in the table.
Special permissions:
All: Allow to do anything (as root).
USAGE: Only allow login-nothing else is allowed.

A small operating technique:

If you are in the command, after the return to forget to add the end of the semicolon, you do not have to re-command, as long as a semicolon to enter the return on it. In other words, you can break a complete command into a few lines and then end it with a semicolon to make it OK.

Common MySQL Syntax