MySQL database operation "SQL use"

Core goal: Learn and delete change, write what business are not afraid!

I. Understanding the database and its role

1. The five basic units of a database
1) database server
2) database
3) Data Sheet
4) Data fields
5) Data Line

2, detailed these five basic units
1) database server. Refers to a computer that is used to run the database service. In small and medium enterprises is usually one. There can be more than one when the amount of data storage is very large. Multiple database servers together to store or compute. Because data security is very important, we often back up the data in the database server.

2) database. There can be multiple databases in a database server. Mainly used for classification. We can set up traffic information database, game database, hotel open Room database ... It is mainly used to divide the data of different uses into large chunks according to the business.

3) Data Sheet. For example, in a game database. According to this game is divided into different data tables. Specifically used to distinguish between different games of data. For example: User data (user, password), character data, all equipment and equipment information, user's recharge information, medicine, magic potion Information ... such as

4) data fields, also called data columns. is the column in the table we see everyday. In the table, we divide a user table into multiple columns. As shown in Table I: User number, user name, gender, age are fields. In a real database, the data fields need to be written in English: ID, username, sex, age.

5) Data rows. The real data exists in every row of the table. The fields (columns) are divided into what format a table should store the data in. And the line, is the real data. Each line needs to follow the specifications and requirements of the data field (column) to deposit data.

second, the relationship between the data table (primary key, foreign key)
A pair of one or one-to-many, many-to-many.
In the same database, there are multiple tables, and the tables often have some correspondence between them.

Third, MySQL installation tutorial
MySQL is a cross-platform server that is almost identical to the use of Linux under the Windows operating system.
1) Baidu search keywords: mysql server download
2) Visit the official website to download: [http://dev.mysql.com/downloads/mysql/] (http://dev.mysql.com/downloads/mysql/)

Iv. Primary knowledge of SQL
SQL is the core of the operational database and the beginning of this chapter: MySQL for PHP programmers is to transform the business into a table structure. Do business in the increase, delete, change, check.
The Structured Query Language (structured query Language) is a special purpose programming language, a database query and programming language for accessing data and querying, updating and managing relational database systems, as well as the extension of database script files.
SQL is the most important relational database operation language, and its influence has gone beyond the domain of database, and has been paid attention to and adopted in other fields, such as data retrieval in artificial intelligence field.
Although each database system is slightly different, they basically follow the SQL 92 standard. Or, some simple extensions and changes are made on SQL 92.
Learn the SQL syntax of MySQL, the other SQL syntax learning is original aim.

V. Three functions of SQL statement classification
SQL statements can be divided into 3 categories depending on their functional scope:
1) Data Definition language (DDL, data defintion Language) Statements: Data definition statements that define different data segments, databases, tables, columns, indexes, and so on. Common statement keywords include create, drop, alter, and so on.
2) Data Manipulation language (DML, data manipulation Language) Statements: Data manipulation statements for adding, deleting, updating, and querying database records, and for checking the integrity of data. Common statement keywords include insert, delete, update, select, and so on.
3) Data Control Language (DCL, Data Control Language) statement: A data-controlled statement that controls the level of permission and access to different data segments directly. These statements define the database, table, field, user's access rights, and security level. The main statement keywords include GRANT, revoke, and so on.

= = database/table/Field Operation = =

Vi. connecting to the database server
Mysql-h localhost-u root-p (connect local data,-h localhost can be omitted; when there is no password,-P can be omitted.) ）

Vii. Creating a database/deleting a database
CREATE database name; (If the data already exists, an error is indicated.) ）
DROP database name; ("Remember" note: After the deletion of the database, all the following data will be deleted, so be careful before deleting and make the appropriate backup. ）

Eight, show all the database
show databases;

Nine. Select and enter a database
Use database name; (using the USE statement to switch the database to operate anytime, anywhere)

X. Viewing tables in the current database
Show tables; (Displays all tables under the current database)

Xi. Creating a data table
Syntax: CREATE table table name (Field name 1 field type, ....) Field name n field type N);
Example: CREATE TABLE User (username varchar, password varchar (32));

12. View the table structure of the data table
Syntax: DESC data table name;

13. View the SQL statement used to create a data table
Syntax: Show CREATE table data table name \g; (the "\g" option means that records can be vertically arranged by fields, making it easier to display records with longer content.) ）

14. Delete Data Sheet
DROP table data table name; (delete the table.) Tables and data are lost, and you should not back up your data before deleting important tables. ）

XV, specifying the table engine and character set for the data table
(at the end of creating the table, we often use MyISAM or InnoDB engines.) ）
Specify the table engine, syntax: ENGINE=INNODB;
Specifies the default character set for the table, syntax: Defaults Charset=utf8;

Complete syntax for creating a data table: Create table data table name (Field name 1 field type, Field Name 2 field type, ... Field name n field type) Engine=innodb DEFAULT Charset=utf8;

16, modify the field name, modify the field type
Modify field name: ALTER TABLE name change field the original name segment new name segment type;
Modify field type: ALTER TABLE name modify field name varchar (20); (The specified field is changed to a varchar (20) type.) ）

17. Add new fields to the data table
Syntax 1:alter table name add column field name type; (The default new field is in the last column.) ）
Syntax 2:alter table name add field Name 2 field type after field name 1; (Create a new field 2, and leave it behind Field 1.) ）

18. Delete a field
Syntax: ALTER TABLE name drop column field name;

19. Modify the order of the fields
1) After adding a field statement and modifying a field statement (add/change/modify), you can add an optional first | After to adjust the order of the current fields.
2) Use modify adjustment order: For example ALTER TABLE user modify email varchar first; (It puts the email field in the first place.) ）

20, modify the name of the data table
Syntax: ALTER TABLE old table name rename new table name;

21. See all engines supported by the current server
Syntax: Show engines;

= = Delete and change (data operation) = =

Inserting data into a table
Syntax 1:insert into table values (value 1, value 2, value n);
Syntax 2:insert into table (field 1, Field 2, field N) VALUES (value 1, value 2, value n);

Second, query data from the table
Query all data: SELECT * from table; ("*" is the notation of a regular expression that represents the value of all fields queried.) ）
Specify field query: Select field 1, Field 2,... field n from table; (You can check the values of one or more fields at a time.) ）
Querying for records in a single field that do not duplicate "value": SELECT DISTINCT field name deptno from table name;
Condition query: Select field from table where where condition;
Sort the query results: Select field from Table order by field sort keywords;
Sorting results for multiple conditions: Select field from Table order BY field 1 sort keywords,... ... field n sort keywords;
Limit number of Queries: Select field from table Limit number;
Query in specified interval: Select field from table limit offset, quantity;
Statistics of query data: Select function (field) from table;
Group the query results: SELECT * FROM table group by field;

Three, multi-table joint query

Iv. updating one piece of data in a table
Syntax: Update table name set field 1= value 1, field 2= value 2, field n= value n where condition;

V. Deleting data from a table
Syntax: DELETE from table [where condition];
Delete all data in the table: TRUNCATE table name;

MySQL database operation "SQL use"