MySQL Tutorial


Basic Concepts

MySQL is the most popular open source database system, can run on almost all of the operating system platform.

After installing the MySQL database system, the system will create a default named test database (database). We can also create multiple databases, but for the sake of simplicity, we tentatively use the default test database, the corresponding database connection information is as follows:

Host: localhost
Database name : test
Psername : root
Password : root123
Port: 3306

Table

Data tables (or tables) are one of the most important parts of a database. Database is just a framework, the data table is the substance of the database in the presence of one or more tables. Data table is used to store specific data objects, is a collection of structured data.

To reduce data entry errors and make the database efficient, table design should be in accordance with certain principles of information classification. At the same time to ensure the rationality of the table structure design, usually on the table to standardize the design to eliminate the redundancy table to ensure that a table only around a theme, and make the table easy to maintain. Such as user information table user, to record the user's account information, and user statements in the forum or comments on the article, should be recorded with another table.

There are other attributes such as "constraints", "index", "default value" and the corresponding data structure.


Field

A table is made up of columns, which are also called "fields." Each field must specify a name, the field name. Field names should be appropriate to identify the meaning of the field, and must comply with SQL naming conventions.

The following is an example of a table 'user' :

id username password email
1 admin b7e591c246d010bb2ccd77d52490c85e admin@gmail.com
2 John a193686a53e4de85ee3f2ff0576adf01 john@gmail.com
3 Jack 0193686a35e4de85ee3f2ff0567adf49 jack@gmail.com

In this example, table has four fields, id, username, password, email.


SQL language

SQL (Structured Query Language) is a database query and programming language used to access data and query, update and manage relational database systems.

SQL language contains four parts:

  1. Data Definition Language (DDL), such as: CREATE, DROP, ALTER and other statements
  2. Data manipulation language (DML), such as: INSERT (insert), UPDATE (modified), DELETE (delete) statement
  3. Data query language (DQL), for example: SELECT statement
  4. Data Control Language (DCL), for example: GRANT, REVOKE, COMMIT, ROLLBACK and other statements

MySQL supports standard SQL statements, but also has its own private SQL statement.


QL data basic operations are divided into to CURD (create, update, read and delete), and on this basis, some of the more complex data operations.

  • Create: divided into create and insert into, used to create a database (table) and add data records to the table
  • Update: Updates the database (table) information and updates the specific data information in the table
  • Read: reads the database (table) information and the table of specific data information records
  • Delete: Delete the database (table) information and the table of specific data information records

An example of reading data:

SELECT username FROM user

This statement indicates that all user names are queried from the table user, and the result is a dataset returned by a query. The result is as follows:

username
admin
John
jack

Note

SQL statements are not case sensitive, SELECT username FROM user and select username from user the same.




Copyright 2014-2017 by DeveloperTut.com. All Rights Reserved.
TwitterGoogle+Facebook