MySQL learning notes _ overview MySQL database management system overview bitsCN.com
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, view the/s 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;
BitsCN.com