MySQL Database-Basic

First, the database

A database is a warehouse that organizes, stores, and manages data in accordance with its data structure . Each database has one or more different APIs for creating, accessing, managing, searching, and replicating the saved data.

relational database, which is based on the relational model, is used to deal with the data in the database by means of mathematical concepts and methods such as set algebra.

We can also store the data in a file, but it is relatively slow to read and write data in the file. So, now we're using a relational database management system (RDBMS) to store and manage large amounts of data.

The RDBMS is the feature of the relational database management system (relational Management Systems):
1. The data appears in tabular form (like an Excel table)
2. Various record names for each behavior
3. Data fields corresponding to record names for each column
4. Many rows and columns form a single table
5. A number of forms form database

Second, RDBMS terminology

Before we start learning the MySQL database, let's look at some of the terminology of the following RDBMS:

• Database:   Database is a collection of some associated tables.
• data table: The   table is the matrix of the data. Tables in a database look like a simple spreadsheet.
• columns:   A column (the data element) contains the same data, such as the postal code.
• Line: a row (= tuple, or record) is a set of related data, such as a user's subscribed data.
• Redundancy : stores twice times the data, redundancy can make the system faster. (The higher the degree of normalization of a table, the more the relationship between the table and the tables may often require a connection query between multiple tables, and a connection operation can slow down the query.) For example, the student's information is stored in the student table, and the faculty information is stored in the Department table. An association relationship is established with the Department table by using the dept_id field in the student table. If you want to query the name of a student's department, you must find the student's department number (dept_id) from the student table, and then department find the name of the department based on that number. If you often need to do this, the connection query will waste a lot of time. You can therefore add a redundant field, Dept_name, to the student table, which is used to store the names of the students ' faculties. This will not have to be connected every time.
• PRIMARY key : The primary key is unique. Only one primary key can be included in a data table. You can use the primary key to query the data.
• foreign key: foreign keys are used to correlate two tables.
• Composite key : Composite keys (key combinations) use multiple columns as an index key, which is typically used for composite indexes.
• Index: use the index to quickly access specific information in a database table. An index is a structure that sorts the values of one or more columns in a database table. A directory similar to a book.
• Referential integrity:   Referential integrity requirements relationships are not allowed to reference entities that do not exist. and entity integrity are the integrity constraints that the relational model must satisfy in order to ensure the consistency of the data.  

Third, MySQL database

MySQL is a relational database management system developed by the Swedish MySQL AB company and is currently part of Oracle's product portfolio. MySQL is one of the most popular relational database management systems, and MySQL is the best RDBMS (relational database Management system) application software for WEB applications. MySQL is an associated database management system that keeps data in separate tables rather than putting all of the data in a large warehouse, which increases speed and increases flexibility.

• MySQL is open source, so you don't have to pay an extra fee.
• MySQL supports a large database. You can handle large databases that have thousands records.
• MySQL uses the standard SQL data language form.
• MySQL can be allowed on multiple systems and is supported in multiple languages. These programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby, and Tcl.
• MySQL has good support for PHP, which is currently the most popular web development language.
• MySQL support large database, support 50 million records of Data Warehouse, 32-bit system table file maximum support 4gb,64 bit system to support the largest table file is 8TB.
• MySQL can be customized, using the GPL protocol, you can modify the source code to develop their own MySQL system.

Iv. installation of MySQL on Windows

Https://jingyan.baidu.com/album/cd4c2979033a17756f6e6047.html?picindex=1

Five, the need to do after the MySQL installation--password　

After the MySQL installation is successful, the default root user password is empty and you can use the following command to create the root user's password:

1	[[email protected]]# mysqladmin -u root password "new_password";

Now you can connect to the MySQL server with the following command:

12	[[email protected]]# mysql -u root -pEnter password:*******

Note: When you enter a password, the password is not displayed, you can enter it correctly.

Change Password: http://blog.csdn.net/th_num/article/details/71402801

VI. mysql user operation

1. Create User: Create under root user, command:

' username '@'host'password';

Description

• Username: The user name you will create
• Host: Specifies on which host the user can log on, if localhost is available to the local user, if you want the user to be able to log in from any remote host , you can use a wildcard character%
• Password: The user's login password, password can be empty, if it is empty, the user can not need password login server

Example:

CREATE USER ' dog ' @ ' localhost ' identified by ' 123456 '; CREATE USER ' pig ' @ ' 192.168.1.101_ ' idendified by ' 123456 '; CREATE USER ' pig ' @ '% ' identified by ' 123456 '; CREATE USER ' pig ' @ '% ' identified by '; CREATE USER ' pig ' @ '% ';

2. Authorization

Command:

GRANT privileges on Databasename.tablename to ' username ' @ ' host '

Description:

• Privileges: User's operation permissions, such as,, SELECT INSERT UPDATE etc., if the permission to be granted is usedALL
• DatabaseName: Database name
• TableName: Table name, if you want to grant the user the appropriate operation permissions on all databases and tables * , the representation is available, such as*.*

Example:

GRANT SELECT, INSERT on Test.user-' pig ' @ '% '; GRANT all on * * to ' pig ' at '% '; GRANT all on maindataplus.* to ' pig ' at '% ';

Attention:

A user authorized with the above command cannot authorize another user, and if you want the user to be authorized to do so, use the following command:

GRANT privileges on Databasename.tablename to ' username ' @ ' host ' with GRANT OPTION;

Show user rights:

 for user

3. Set and change user password commands:

SET PASSWORD for ' username ' @ ' host ' = PASSWORD (' NewPassword ');

If the current user is logged in with:

SET PASSWORD = PASSWORD ("NewPassword");

Example:

SET PASSWORD for ' pig ' @ '% ' = PASSWORD ("123456");

4. Revoke user PrivilegesCommand:

REVOKE privilege on Databasename.tablename from ' username ' @ ' host ';

Description:

Privilege, DatabaseName, TableName: With the authorization section

Example:

REVOKE SELECT on * * from ' pig ' @ '% ';

Attention:

If you ‘pig‘@‘%‘ are doing this (or the like) when authorizing a user, the use of a GRANT SELECT ON test.user TO ‘pig‘@‘%‘ command does REVOKE SELECT ON *.* FROM ‘pig‘@‘%‘; not revoke the user's action on the users table in the test database SELECT . Conversely, if authorization is used, GRANT SELECT ON *.* TO ‘pig‘@‘%‘; the REVOKE SELECT ON test.user FROM ‘pig‘@‘%‘; command cannot revoke the user's permissions to the users table in the test database Select .

Specific information can be viewed with commands SHOW GRANTS FOR ‘pig‘@‘%‘; .

5. Delete usersCommand:

DROP USER ' username ' @ ' host ';

Vii. commands for managing MySQL

The commands commonly used in the MySQL database process are listed below:

• SHOW DATABASES: Lists a list of databases for the MySQL database management system.

• Use database name : Select the MySQL database you want to manipulate, and all MySQL commands are only for that database after using this command.

• SHOW TABLES: #显示指定数据库的所有表, you need to use the use command to select the database you want to manipulate before using this command.

• SHOW COLUMNS from data table: #显示数据表的属性, property type, primary key information, whether null, default value, and other information.

• DESC tablename: Displays the table structure.

• Create DATABASE TestDB charset "UTF8"; #创建一个叫testdb的数据库, and let it support Chinese.

• Drop database TestDB; #删除数据库.

• Show index from data table: Displays detailed index information for the data table, including primary key (primary key).

Eight, datebase operation

1. Create a database

" UTF8 "; #创建一个叫testdb的数据库, and let it support Chinese.

2. View database support Character Set

3. Delete Database

Drop database name

MySQL Database-Basic