Basic principles and instructions for MySQL database

1. What is a database

The database is the warehouse that stores the data, its essence is a file system, the data is stored in the specific format, the user can add, modify, delete and query the data in the database by SQL.

2. Introduction

MySQL is an open source small relational database management system, the developer of the Swedish MySQL AB company . MySQL is now widely used in small and medium-sized websites on the internet . because of its small size, fast speed, low total cost of ownership, especially the open source , many small and medium-sized web sites in order to reduce the total cost of ownership of the site chose MySQL as the site database .

3. Start and close

Run the cmd Command window with Administrator privileges

net start MySQL (open);

net stop MySQL (off)

Login: Mysql-u user name-p password

SQL statement Classification:

Data definition Language (DDL), which is used to define database objects: databases, table tables, column columns, etc. "keyword Create,alter,drop, etc." (structure).

Data manipulation Language (DML): Updates (data) on data in a datasheet.

Data Query Language (DQL): A record used to query a table in a database.

Data Control Language (DCL): Used to define access and security levels for a database and create users: Key: Grant, and so on.

SQL statements:

1. Create a database

Create database name;

Create database Database name character set encoding (created with encoding);

2. View all databases in the database: show databases;

View information about the definition of a database: show create database name;

3. Deleting a database

Drop database name

4. Switch database: use database name;

5. View the databases in use: Select Database ();

6. Create a table

CREATE TABLE Table name (

Field Name type (length) [constraint],

Field Name type (length) [constraint],

Field Name type (length) [constraint]

)；

7. View all tables in the database: show tables;

8. View table structure: DESC table name;

9. Delete tables: Drop table indicates;

10. Modify the table:

Add a column: ALTER TABLE name add field name type (length) [constraint];

To modify the type of a column:

ALTER TABLE name modify the type of field name (length) [constraint] to be modified;

To modify column names for columns:

ALTER TABLE name change old column name new column name type (length) [constraint];

Delete table column: ALTER TABLE name drop column name;

Modify table name: Rename table name to the new name;

Modify the character set of the table: ALTER TABLE name character set code;

To view the encoding of the current table: Show create table table name;

11. Operation of database table records (modified)

Insert Record:

Insert into table name (column name 1, column name 2, column name 3 ...) VALUES (value 1, value 2, value 3 ...) ；

Insert into table name values (value 1, value 2, value 3 ...) ；

12. Updating data

1. Conditional: Update table name set field name = value, field name = value, field name = value ... where condition;

2. Without conditions: Update table name set field name = value, field name = value, field name = value ...

13. Solve Chinese garbled problem:

CMD--set names GBK;

14. Database Query Operations

1. Simple query:

2. Conditional query:

1. Check the product information for "left mercy"

2. Check all product information of price >60 yuan

3. Check the product name contains the "Shi" word of the product information

4. Check all product information for product ID within (3,6,9) range

5. Check the product name contains the "Shi" Word and ID 6 of the product information

6. Query for product information with ID 2 or 6

15. Sorting

16. Aggregation

Common function sum () SUM, avg () average, Max () max, min () min, count () count

Like what:

Select SUM (price) from product;

Select AVG (price) from product;

17. Declaring a FOREIGN KEY constraint:

18. Finally introduced the more commonly used graphics database operation software: SQLyog.

Basic principles and instructions for MySQL database