Basic operation and understanding of MySQL database

To use a database:

1. Link MySQL server

2. Select Database

3. Increase and deletion of data sheets

4. Close the database

Exit
Quit
Exit
\q

Common operations
\c Cancel the unfinished operation.
\g instead of Terminator

Database operations:
Show Databases View Database
Note: To view all databases, MySQL database must not be moved.

Create a database
Create DATABASE name
Note: The library name is not in Chinese, do not start with a number.
Attention:
1. Each database created will create a folder named after the database name in the data directory.
2, the database is unique.

Deleting a database
drop database name;

Enter database, select Database
Use database name

Note: The database names under Windows are case insensitive, but are strictly differentiated under Linux.

View the database you have selected
Select Database ();

Operation of the data table:
View tables in the database: show tables;
Create a data table
CREATE TABLE Table name (
Field Name fields Type,
Field name 1 field type,
Field Name 2 field type
)
The last field name does not need a comma

View table structure: DESC table name

View Build Table statement: Show CREATE TABLE table name
\g make data Stand up (more intuitive to see results)
SELECT * from user \g;
Delete data table: Drop table name

Operation of the data:
Inserting data

Insert the specified field:

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

If a field that contains an empty field, non-empty, but has a default value, the Self-increment field can not appear in the Insert Field list.

Insert all fields:

Insert into table name values (value 1, value 2, value 3 ...)

When you do not specify a column name, the order after values should be the same as the order in which the fields are arranged.

Insert more than one statement:

Insert into table name (field name 1, field name 2, field name 3 ...) VALUES (value 1, value 2, value 3 ...), (value 1, value 2, value 3),......
If you insert more than one piece of data, using this method is much faster than using the second or first method.

Insert a single piece of data:

Insert into table name set field name 1= value 1, field name 2= value 2 ....

Insert the results of some queries

　
View data Select Field Name 1, field name 2 from table name
Problem:
1, if there are too many fields, it will lead to waste of traffic.
2, no conditions will be all the data inside the table query out. How to add conditions.

WHERE clause-----condition query
Where to put behind the table name

Arithmetic operators, comparison operators, logical operators can be used in the Where condition
Arithmetic operator "+ 、-、 *,/,%"

Comparison operators >, <, >=, <=,! =, =

logical operators
There are several conditions that can be given in MySQL.
and Logic and
or logical OR

Attention:
1, they can combine small conditions into large conditions, and can be used multiple times.
2, the rationality of the conditions
3. The SQL standard takes precedence over and operations before processing or operations

Other operators
In operator: Specifies the range of conditions in which each condition in the range can be matched, with each value separated by commas.
Format: In (value 1, value 2, value 3);

The between operator, between the specified two values
Format: Between start value and end value

The not operator, which negates subsequent operations, is used in conjunction with between.

Like operator
Format: Like ' string '
Wildcard: A special character used to match a portion of a value.
%: Indicates that any character appears in any number of times and can be placed in any position.

% value: End With value
Value%: Start with value
% value%: Contains the value

There is no sorted data in the SQL standard his order is not credible.
ORDER BY clause-sort the fields
Format: Order BY field name [ASC | desc]

Attention:
1, ASC is the default value, is the ascending order
2, DESC is descending order
3. Often many people think that the columns used in the ORDER BY clause must be columns that are displayed, and it is also legal to actually sort the columns that are not displayed.

Format: Order By field name 1[asc|desc], field name 2[asc|desc]

Attention:
1. If the value is the same as the value of field name 1, the same content is sorted by field name 2.

Limit clause----limiting result set
Format 1:limit m
Note: m represents how many rows are returned.

Format 2:limit n,m
Attention:
1, n indicates how many values to start with (the first data is represented by 0), and M indicates how many rows are returned

2, if there are not enough lines, then how much to give.

Statistics Query
Count () is used for counting, and COUNT (*) is used to count the number of data, and count (field name) represents the count of non-null values.
Min (field name) calculates the minimum value
Max (field name) calculates the maximum value
sum (field name) and
AVG (field name) average

　　

GROUP BY clause----grouping
Format: Group By field name

Format: Group By field name having condition
Where is not the same as having a. Where is the query of the qualifying data, having the data that meets the criteria.

The Order of the clauses:
select = = from = where = Group by .... Having a =>order by = = Limit

　　

Fully qualified:
Library name. Table Name
Table name. Field Name

Alias:
Field name as your alias

Stitching fields:
Concat ();//character or field link

Federated query:
Steps:
1. Figuring out the relationship between tables and tables
2. Select * FROM table 1, table 2
3. Plus where conditions
The conditions have to be given. If not, your results will be wrong, if you do not give the conditions would be an accident. Cartesian product
4. Replace the SELECT * with the name of the field you want

Sub-query

Front-facing subquery

Mysql> SELECT *, (SELECT COUNT (*) from types as T2 where t2.pid=t1.id) as Scount from types as T1;

Post subquery as condition
Mysql> Select *from Types where pid= (select ID from types as t1 where t1.name= ' a ');

Change data:
Update table name Segment name = value, field name 2= value 2 ... where condition
If you do not add a condition, all of the values in the specified fields in the data table will change.
Delete data:
Delete from table name where condition

Attention:
1. If you do not add a where condition, all records of the table will be deleted.
2. If you use Delete, it is best to use Select to query the results.
3. Do not use the DELETE statement when you want to delete the data. It is recommended to use the TRUNCATE table name because it is fast and clean.

Export MySQL database (when exiting MySQL)
Mysqldump-u user name-p database name > export file name entire database export
Mysqldump-u user name-P database name Table name > export file name entire data table export
Import the MySQL database, if you want to import it must be determined that this library already exists. (at the time of exiting MySQL)
Mysql-u user name-p database name < database file

To modify a user password:
Method 1, mysqladmin-u user name-p password New password exit case
Method 2, Set password for ' username ' @ ' login host ' =password (' new password '); into MySQL case

MySQL forgot password
Windows methods:
1. Close the running MySQL
2. Open DOS jump to the MySQL bin directory
CD C:\xampp\mysql\bin
3. Input
Mysqld--skip-grant-tables Enter
4. Open a window and go to the MySQL bin directory
Open dos jump to MySQL's Bin directory
CD C:\xampp\mysql\bin
5. Enter MySQL Enter
6. Go to MySQL Library
7. Change record update user set Password=password (' New password ') where user= ' root '
8. Refresh Permissions
Flush Privileges
Note: You now have MySQL directly using MySQL command to enter. MySQL will create an anonymous user by default during installation. This anonymous user is primarily used for testing, and this account has all the permissions of the test library.
show databases;
Why do you want to delete anonymous users? (operating in MySQL)
Drop user ' @ ' localhost ';

Linux methods:
One of the ways to recover MySQL root password
If you forget the MySQL root password, you can reset it in the following ways:
1.KILL off the MySQL process in the system;
Killall-term MySQLd
2. Start MySQL with the following command to start without checking permissions;
Safe_mysqld--skip-grant-tables &
3. Then log in to MySQL using the root user with a blank password;
Mysql-u Root
4. Change the password of the root user;
mysql> Update Mysql.user Set Password=password (' New password ') where user= ' root ';
mysql> flush Privileges;
Mysql> quit
Restart MySQL and you will be able to log in with your new password.
Mysqlroot Password Recovery Method II
It is possible that your system does not have a SAFE_MYSQLD program (such as the Ubuntu OS I am using now, Apt-get installed MySQL), the following method can be restored
1. Stop mysqld;
Sudo/etc/init.d/mysql stop
(You may have other ways to stop mysqld running anyway)
2. Start MySQL with the following command to start without checking permissions;
MySQLd--skip-grant-tables &
3. Then log in to MySQL using the root user with a blank password;
Mysql-u Root
4. Change the password of the root user;
mysql> Update Mysql.user Set Password=password (' NewPassword ') where user= ' root ';
mysql> flush Privileges;
Mysql> quit
Restart MySQL
/etc/init.d/mysql restart
You can log in with the new password newpassword.

Field type
When you define a data table, you specify the type of the field.
1. Integral type data
Attention:
1. The length of the shaping setting is not how many bits can be stored. Instead, the width is displayed. What is the width of the display, keep it for a while and tell you.
2. How many values the shaping can store depends entirely on the maximum value.
3, by default, the integer type is created with a signed. {Only show maximum value when greater than Max}
Problem: Show width, unsigned what's going on.
2, floating point, fixed point
Floating point and fixed point number:
1. When declaring them, there is a M and a d,m that indicate how many bits are in total, and D represents a few digits behind the decimal point.
2, floating-point number if not write precision (m) and scale (d), will be displayed according to the actual accuracy value. If there is precision and scale, it will be rounded automatically.
3, fixed-point number if not write precision and scale, will follow the decimal (10,0) to operate. If it is written, it will automatically be rounded if it exceeds M and d.
4. The floating-point number will get an approximate value when the operation is performed, and the fixed point will get an exact value.
MySQL Float Usage:
MySQL floating-point and fixed-point types can be represented by the type name plus (m,d), m represents the total length of the value, D represents the length after the decimal point, and M and D are also known as precision and scale, such as float (7,4), which can be displayed as -999.9999,mysql when the value is saved is rounded, and if 999.00009 is inserted, the result is 999.0001. float and double do not specify precision, the default is the actual precision to display, and decimal when the precision is not specified, the default integer is 10, the decimal number is 0.
Summary: Floating-point number if not write precision and scale, will follow the actual display, if there is precision and scale, will be rounded up after the data inserted, the system does not report errors, fixed-point if not set accuracy and scale, just follow the default (10,0) to operate, if the data super If the accuracy and scale values are over, an error will be made.
Note: If the float (m,d) is less than the value to be inserted, the result is the maximum value that the specified (M,D) can display.
3. Character type
Attention:
1. The difference between text and BLOB is that blobs are used to hold binary data, and text can only hold character data.
2, char and varchar can be used to store shorter strings in MySQL:
Difference:
1, char length is fixed length. The value is 0~255,varchar variable length length is 0~65535.
2, fixed length (use fixed bytes after declaration is completed), variable length (the number of bytes stored is adjusted according to what you save after the declaration is completed)
A char (255) 255
A varchar (255) 1
3, char and varchar intercept the string to the specified length when the length is exceeded.
4, fixed length and variable length is set in the range of the set.
4. Enumeration type

Sex enum (' NAN ', ' NV ')

Attention:
1, the enumeration method of the value range needs to be explicitly specified when the table is created.
2. If the value specified inside the enumeration is over, an "empty" will be inserted.
3. Enumeration types can only pick one value from the collection of values, and cannot pick multiple values at a time.
4. The value of the enum is not case-sensitive.
5, enumeration of 1~255 members requires 1 bytes to store, if 256~65535 members need 2 bytes to store, but there can be up to 65,535 members.
6, when inserting values, including when declaring this field, you must use quotation marks to wrap the inserted value. If you write 1 or 2 directly, then the first or second of the type that inserts an enum

5. Collection type

Aihao set (' A ', ' B ', ' C ', ' d ')

Attention:
1. The set type can store multiple values.
1~8 a member 1 bytes
9~16 a member 2 bytes
17~24 a member 3 bytes
25~32 a member 4 bytes
33~64 a member 8 bytes
2, set and enum in addition to storage, the main difference is that the set type can select multiple values at a time, enum can only select one.
3. If there are duplicate members, one will be retained.
4, the value of the range beyond the list is ignored.

Constraints: Some constraints on a field
Common constraint conditions:
1, NULL, NOT NULL:
NULL: Default is NULL, the value is inserted without inserting a value into the field, and the value of the field is null by default.
NOT NULL: Specifying a NOT NULL must be given the corresponding value in the field when inserting the value. Otherwise there is no value.

NOTE: Constraints are written after the field type.

2. Default: The value that is inserted when the field is not inserted.　　

Integral type constraints:
1, zerofill,0 Fill. Typically used with the width of the set after the integer, if the numeric length is less than the specified length, 0 is used before the padding.

2, unsigned (unsigned), if you want to save a non-negative number in the field or need a large upper limit, you can use this option, the value range is starting from 0.
Note: Unsigned must be followed by the field type

3, auto_increment, self-increment, you can use this property when producing a value that uniquely identifies or orders. This property can only be used for integer types, with values typically starting at 1 and +1 per line. When a null is inserted into a auto_increment column, it is also self-increasing.

Attention:
1, the use of auto_increment must be followed by a primary key or unique to use.
2, the largest number of occurrences +1

Index: Index is used in the database to improve the performance of the search, we do the database optimization is usually the first thing to do first is to do index optimization.
Note: He is automatically used, do not need our deliberate use.

Classification of indexes:
1, the general index, the most basic index, there is no limit.

Index name (field name)

Index Xxoo (name)　

To delete an index:
Drop index index name on table name;

To add an index when the table is created:
Create index index name on table name (field name)

2, unique index (unique), similar to regular indexes, but the values of indexed columns must be unique. You can add unique to multiple columns

Unique XOXO (name)　　　　　　

To delete an index:
Drop index index name on table name;

To add an index when the table is created:
Create unique index index name on table name (field name);

3, primary key index (primary key), similar to a unique index, there can be only one primary key index in a table.

ID int unsigned primary key,

To delete an index:
ALTER TABLE name drop PRIMARY key;

Attention:
If there is no auto_increment can be deleted directly, if there is auto_increment need to delete auto_increment and then delete the primary key

1, delete the primary key index if the field has auto_increment then you need to remove the auto_increment, and then delete the primary key

　　　　

Rules for creating indexes:
1. The most appropriate column to create an index is usually the one that appears in the WHERE clause.
2, index different columns of different values of the more index effect better.
3, the more data more index effect is better.
4. Do not overuse indexes, each additional index requires additional disk space. Reduce write performance.

Storage Engine:
Engine: The user can choose how to store the data according to different needs. You can provide some functionality based on the engine and improve application efficiency.

See all Engines show engines

Attention:
1, MyISAM table engine, does not support transactions, the advantage is access speed block. But he is a table lock, the probability of conflict is relatively high. You can use the MyISAM table engine when there is no requirement for transactional integrity, or if your application is dominated by select and insert.

Each myisam is stored on disk as 3 files, the same as the table names.
. frm (Definition of storage table)
. MYD (storing data)
. MYI (storage index)

2, InnoDB table: InnoDB table Write processing effect will be worse, and occupy more disk space. But row locks, the probability of conflict is relatively low.
If there is a higher requirement for transactional integrity in your application, data operations have many updates and deletions in addition to insertions and queries. Then you can use InnoDB

Character
The MySQL character set involves two concepts:
Character set: Used to define how MySQL stores strings.
Proofing rules: Defines how strings are compared.

A character set has multiple proofing rules.
Proofing rules usually end with _ci to indicate case insensitivity, and _cs-end representations are case-sensitive.

View the character set in the system show character set;

Be sure to note that UTF8 is not utf-8.

The character set is divided into 4 levels: server-level, database-level, data-table-level, data-field

If you do not set the character set for this level, then the previous level of character set is used by default

Set the character set of the database
Mysql> CREATE DATABASE lamp129 default character set UTF8;

If you set the character set of the database, the character set of the data table will use the database's character set by default.

Sets the character set of the data table Charset=utf8;

Garbled problem:
1, ensure that your page storage format is UTF-8 format. No BOM
2, to ensure that your browser parsing is UTF-8
3. Ensure that your database is in UTF8 format.
4, the linked character set also if UTF8
Mysql_set_charset (' UTF8 ');

You didn't set the character set when you created the table.
1. Change the character set of the table
2. Change the character set of a field

Gets the last self-increment value of the auto_increment column.
MYSQL_INSERT_ID ();//This does not have to write parameters, if you want to write a link to write resources.

Changing the character set of a table
ALTER TABLE name default charset= to modify character set
ALTER TABLE name modify field type constraint character Set the character set you want to modify

Modify Table
Syntax: ALTER TABLE table name action
Modify the field name of the table and modify the field type: ALTER TABLE name change old field name new field name fields type constraint condition
Modify the field type of the table: ALTER TABLE name modify field type

Note: Changes can change the field name, modify, or not. However, you can change the field type.

Add field: ALTER TABLE name add field Name field Type [First|after fields name]
Mysql> ALTER TABLE user add height double (3,2);

Delete field: ALTER TABLE name drop field name

Modify table name: ALTER TABLE old indicates rename as new indicates

Modify character set: ALTER TABLE name default character set new character set
Attention:
1, if the character set of the table is changed, then the character set in the field will not change automatically.

Basic operation and understanding of MySQL database