Database Fundamentals 1

1, the two commonly used engines:
(1) InnoDB
A, to support acid, simply to support transactional integrity, consistency;
B, support row lock, and similar to the consistency of Oracle read, multi-user concurrency;
C, the unique clustered index primary key design method, can greatly improve the concurrent read and write performance;
D, support foreign key;
E, support crash data self-repair;
The InnoDB design goal is to handle a large-capacity database system whose CPU utilization is not comparable to other disk-based relational database engines.
It is a reliable transaction processing engine and does not support full-text search

(2) MyISAM
A, the atomicity of each query is not supported
b, only the table is supported
C, emphasis is on performance, which executes several degrees faster than the InnoDB type, but does not provide transactional support
D, if executing a lot of select,myisam is a better choice

2, Introduction:
A, Database-database
A container that holds organized data (usually a file or a set of files)

B, Table-table
A structured list of data of a particular type

C, Mode-schema
Information about the layout and characteristics of databases and tables

d, Column-column
A field in a table in which all tables are made up of one or more columns

E, line-row

A record in the table

F, data type-datatype
The type of data that is allowed. Each table column has a corresponding data type that restricts (or allows) the data stored in that column

Two, MySQL command line

1, connect MySQL
Format: mysql-h host address-u user name-P user Password

A, connect to local MySQL
First open the DOS window, then go to directory Mysql\bin, then type the command Mysql-u root-p,
You are prompted to lose the password after enter. The prompt for MySQL is: mysql>. (The default root user does not have a password)

b, connect to MySQL on the remote host
Assume the remote host IP is: 218.105.110.116, the user name is root, the password is abcd123456.
Type the following command: Mysql-h218.105.110.116-u root-p abcd123456; (Note: You can use no space between you and Root, the other is the same).

2. Start the MySQL service
net start MySQL

3. Stop the MySQL service
net stop MySQL

4, query port is conflicting

Netstat–na | findstr 8080 View the port being monitored, findstr is used to find out if the subsequent port exists.

5, Exit MySQL
Quit or exit

6, Change Password:
Format: Mysqladmin-u username-P Old password password new password
For example, change the password to root:
Mysqladmin-u root-p ab12 Password djg345

Three, show command
1. Displays a list of databases in the current database server
Mysql> SHOW DATABASES;

2. To display a table of data in a database

mysql> use library name;//Using a library
Mysql> SHOW tables;//Lists all the tables in the library

3. Show the structure of a data table
mysql> DESCRIBE table name;

4. Set up a database
mysql> CREATE database name;.

5. Set up a data table
Mysql> the use library name.
mysql> CREATE table name (field name VARCHAR (20), Field name CHAR (1));.

6. Deleting a database
mysql> DROP database name;.

7. Delete a data table
mysql> DROP table name;.

8. Empty records in a table
Mysql> DELETE from table name;.

9. Show records in a table
Mysql> SELECT * from table name;.

10. Inserting records into a table
mysql> INSERT into table name VALUES ("HyQ", "M");.

11. Updating data in a table
mysql-> UPDATE table name SET field name 1= ' A ', field name 2= ' B ' WHERE field name 3= ' C ';.

12. To load data into a data table in text mode
mysql> LOAD DATA LOCAL INFILE "d:/mysql.txt" into table name;.

13. Import. sql File command
mysql> use database name;.
Mysql> SOURCE d:/mysql.sql;.

14. command line to modify root password
mysql> UPDATE mysql.user SET password=password (' New password ') WHERE user= ' root ';.
mysql> FLUSH privileges;.

15. Displays the name of the database for use
Mysql> SELECT DATABASE ();.

16. Displays the current user
Mysql> SELECT USER ();.

Database Fundamentals 1