Database Basics and Operations

Classification of databases:

Mainstream database products: lamp.

the nature and performance of database storage:

The essence is that a large number of "data values" through the database management system for some complex but logical structure of the clear and orderly storage

Performance:

In fact, it is also some file storage.

Common terminology for relational databases:

Data :

database : Refers to a specific "logical name" for storing data, typically a product (project/website) that uses a database to store all its data

database Management Systems Dbms:database Management system: usually refers to database software products

table (data table)table:

fields field , column:

row Row, recording record:

Basic mode of database operations (process)

Establish a connection (authenticated identity)

client sends SQL command to server side

The server side executes the command and returns the result of the execution

The client receives the result (and displays it)

Disconnect Connection

MySQL system-level operations and basic grammar rules

Command line mode:net start/stop mysql

Login / exit basic operations

Login:mysql [-H server address ] -U login name-P port number-P

Login database: (mysql-u root-p)

or login: MySQL [--host= server address]--user= user name--port= port--password

exit:quit; or exit;

Note: After logging into the database system, you need to use "set names encoded name;" To set the " environment variable " of the current connection database. The encoding of the "client" itself that is currently dealing with the database. Generally speaking :

is fixed in the CMD client Use GBK encoding,

in the PHP Web page, it is the encoding of the page file (now the mainstream is UTF8).

backing up the recovery database to exit the database execution command specify to the specific file name

To back up the database:

Mysqldump-h server address-u login name-Name of the database to be backed up > file to Save As

Native database backup-------------mysqldump-hlocalhost-uroot-p> new folder path/file name

Note: Typically the database name needs to be established first (present):

You must have files in the new database folder to be backed up, empty folders cannot be backed up

Basic Grammar Rules

Single-line comment: #注释内容

Single-line comment: --note content (note that there is a space after two "--")

Multiline Comment:/ * Comment content */

statement Terminator is a command of the database

Default is semicolon (English)

You can also set the command to: delimiter

Form: delimiter new symbol (can be multiple characters)

After entering the database:mysql> delimiter!; ---this input as a new closing sentence; Before the end is invalid

If you want to continue to use it; To end, then need to reset the delimiter operation ditto!

naming rules in the database--understanding (not recommended for modification)

MySQL itself is case insensitive.

However, in some case-sensitive operating systems, the database name and table name are case sensitive.

It is recommended to use "underline naming" to name various identifiers: the words are in lowercase letters, and the words are separated by "_".

Grammatical Form

Create DATABASE [if not EXISTS] db name [CharSet Character set ] [collate character Collation ] ;

Description

1,if notexists: Used to determine if the database name exists and does not execute if it exists

2, Character set: The character encoding name used when the intent data is stored in this database, usually utf8, or GBK.

3, character collations are not usually set, but instead use the default rules for the set of character sets (each character set has a default collation);

To Modify a database:

ALTER DATABASE name character set= new character set collate= new proofing set;

To Delete a database:

Drop database name;

other database-related statements

Select (Enter) a database: use database name;

In general, you must first "enter" the database for the data tables and data in the data.

Show all databases: show databases;

to display the CREATE statement for a database :

Show create database name;

Database Basics and Operations