MySQL learning notes _ overview MySQL Database Management System Overview

1. MySQL Architecture

C/S: client/server Architecture

MySQL DBMS (Data Bank Management System): Database Management System

Client <---> server ---> database ---> data table ---> (record/row, field/column)

 

2. Database programmers must be proficient in the following operations: (not a DBA (Database Administrator ))

1. Design a table for the project

Ii. use SQL statements (SQL statement programming)

Other tools can be used.

3. MySQL file structure

Configuration File: my. ini: you can modify this file to configure MySQL attributes.

Binfile Directory: stores all MySQL commands

Data File directory: stores the databases contained in MySQL. Each database contains a corresponding table!

[During backup, you only need to package and back up the data folder. in Linux, It is var /]

4. SQL statement operations

Structured Query Language is an advanced non-procedural Language.

SQL statement: simple structure, powerful functions, easy to learn!

By function:

DDL: statements used to create databases and data tables

DML: statement used to operate data

DQL: database query statement

DCL: a data control statement that can be executed by tools.

For example, \ s Viewing Status

Show databases; view all databases

Show tables; view all tables

Desc tables; view the table structure

Show variables; view the variables in the configuration file

DDL: 1. to execute an SQL statement, First connect to the database server:

Mysql-h localhost-u root-p # log on to the local database as a root user

\ S: view the database status

Show variables;: view the default variables in the system. Remember: end;

Show variables like 'time _ zone ';

Show variables like 'Port';: view the port

Show databases;: displays all the databases in the system.

2. Create a database

Create database [name];

For example, create database boost;

3. delete a database

Drop database [name];

For example, drop datebase boost;

Extended: cteate database if not exists boost;

Drop database if exists boost;

4. Create a data table

Create table boost. users (id int, name char (30), age int, sex char (3 ));

5. Select a database as the default database.

Use boost;

6. View All Tables

Show tables;

7. view the table structure

Desc users;

8. delete a table

Drop table users; // drop table if exists users;

9. Continue to create the default database

Create table users (id int, name char (32), age int, sex char (2 ));

Expansion:

Create table is not exists users (id int, name char (32 ));

10. Create another table

Create table is not exists articles (title char (64 ));

DML: 11. insert data

Insert into users values ('20170101', 'xiaofang ', '34', 'nan ');

Or: insert into users values (2012, 'xiaofang ', 34, 'man'); // weak type check

Best Practice: insert into users (id, name, age) values ('000000', 'hangw', '56 ');

You can insert parts in sequence.

12. Update Data Information

Update users set name = 'ashun' where id = '000000 ';

Promotion: update users set name = 'xiaochang', sex = 'nv 'where id = '000000 ';

13. delete data information

Delete from users where id = '20140901 ';

Promotion: delete from users // delete all

DQL: 14. View data information and query statements

Select * from users;

5. Use of help

1. view the information provided by the help

? Contents;

2. view more details

Data types; // The information type listed above

3. view more details

Int;

Show;

Create tables; // view the create TABLE structure syntax

Update;