"Database" MySQL database (ii)

I. Export of database files
1. Export the database (with data) at the DOS command line
Mysqldump-u root-p database name > E:\wamp\www\lamp175\lamp175.sql

2. Export the data table (with data) at the DOS command line
Mysqldump-u root-p database name data table name > E:\wamp\www\lamp175\lamp175_stu.sql

3. Export the database (without data) at the DOS command line
Mysqldump-u root-p-D database name > E:\wamp\www\lamp175\lamp175.sql

4. Export the data table (without data) at the DOS command line
Mysqldump-u root-p-D database name data table name > E:\wamp\www\lamp175\lamp175_stu.sql

Second, the database file import
1. Import the database file under DOS command line
Mysql-u root-p database name < E:\wamp\www\lamp175\lamp175.sql

2. Import the database files under the MySQL command line (must be in the specified library)
SOURCE E:\wamp\www\lamp175\lesson24_MySQL02\lamp175.sql

Third, the database password add:
1. Under DOS command line
mysqladmin-u root-p Password New password
Attention:
Do not add double quotes or semicolons to the new password, otherwise problems may occur

2. Under the MySQL command line
Set password for ' root ' @ ' localhost ' = password (' new password ');

Four, the password to change the wrong way back:

1. Close the database service first (right-click on taskbar, open Task Manager, find Mysqld.exe)

2. Open a DOS command box to jump the path to the E:\wamp\bin\mysql\mysql5.7.9\bin directory

3. Enter the command mysqld--skip-grant-tables, the cursor will always blink below

4. Open a DOS command box and enter MySQL directly into the database service system with root access

5. Enter the MySQL library, view the user table, and then modify the root user's authentication_string value

6.update user Set Authentication_string=password (' 123456 ') where user= ' root ';

7.flush privileges; After the modification is complete, use this command to refresh several times

8. Restart the MySQL service, enter the DOS command box, log in the modified password

V. Wamp server crashes, to perform data backup:

1.data Catalog:

2.WWW Catalog:

Vi. Build table (data type in MySQL):

When we store different data, we need to match different types to store them.

Data types in 1.php:
Boolean, Integer, floating-point, String, array, object, resource, empty

Data types in 2.MySQL:
Integer, float, fixed-point, String, DateTime, set, enum

1> Integral type

tinyint: Short-integer (Common)
smallint: Small integer type
Mediumint: Medium integer type
int: integral type (Common)
bigint: Long Integer type

These data types, in fact, are all stored in integers, only occupy a different length, storage range is different

Attention:
1. When defining integer data, you do not need to specify a length, it is stored with the default length
2. If the length is defined, the length is prepared for a subsequent constraint
3. When inserting a value of integer type, be careful not to cross the border or you will get an error
4. When we use an integer type, we select the appropriate storage range to

2> floating Point type:

Float (m,d): Single precision
Double (M,D): Dual precision
Decimal (M,D): Fixed number of points

3.143.1415926
When using floating-point data, you will definitely specify the accuracy and scale

Attention:
1. Single-precision, double-precision only difference is the length and storage range
2. Accuracy: The length of the integer and decimal digits and
3. Scale: The length of the decimal place is the scale
4. When we want to store some data that requires accurate and precise expression, do not use single or double precision
5. We recommend using fixed-point numbers for storage

3> String Type:

Char: fixed-length string (common)
VARCHAR: variable-length strings (common)
Tinytext: Short Book
Text: Texts (Common)
Mediumtext: Chinese text
Longtext: Long text

The biggest difference between the string types is their storage range

Attention:
A 1.char fixed length string that can be specified without length, default length of 1, and if specified length,
Then the length is fixed length!
2.varchar variable length string, must specify the length, stored in the varchar data, will
Automatically adjusts the storage range based on the length of the data, but cannot cross the line!
3. If the length is exceeded, the error will be
4.char and varchar In addition to the above differences, the storage range is different
5. When we store long text with longer content, such as novels, articles, etc., you can use
Text long text format to store!

4> Date and Time type:
This date and time type, we can understand, storage time we recommend the use of integral type, because
Time stamp is an integer value

5> Enum Type:

Choose one More
Enum ("Dabolang", "Xiaobolang", "Meibolang", "Lang");

Attention:
1. Enumerate the types of data, you must enclose each value in double quotation marks, each with a value of
Comma separated
2. enum-type values, only 1

6> set Type:
Multi-choice

Set ("Dabolang", "Xiaobolang", "Meibolang", "Lang");

Attention:
1. Aggregate data is defined in the same way as enumerated data
2. The difference is that the enumeration type can only be more than one, the collection of more than the choice!

3. Constraints on fields
1. Constraints to the preparation of the integral type
1> unsigned: unsigned
Attention:
1. When we add an unsigned remainder condition to a field of integral type, the storage of the field
Scope changes (Specific scope reference Word document)
The 2.unsigned unsigned modifier must be added after the field type we have defined

2> Zerofill: Leading zeros
Attention:
1. The length defined in the integral type is actually for the leading zero
2. When we add a leading zero constraint to a field of an integral type, it automatically
We have added a unsigned constraint stating that if we use leading zeros
You cannot add a negative value

2. General Conditions of constraint

1> NOT NULL: cannot be empty
Attention:
1. The field modified by the Modifier keyword cannot be empty
2. If you have to make it empty, you can only fix the problem by giving the default value
The order of 3.not NULL is unlimited, but cannot be placed before unsigned

2> Default: Defaults
Attention:
1. When we add a default value to a field, the default value is
is the type
2. The default value is usually put to the last

3> auto_increment: Self-increment
Attention:
1. Self-increment is usually a constraint that is prepared for the ID in the table
2. Each table will have an ID field
3. If we want to add a self-increment keyword to the ID field, you must give the field a
Index decoration, which can be either primary key (primary key index) or
Unique (single index)
4. field with self-increment, 1 per increment
5. Only one self-increment field can be found in a table

"Database" MySQL database (ii)