The essential skill of program ape: Database Management--about MySQL

First, the initial knowledge of MySQL

1. What is a database?

A database (DATABASE,DB) is simply a repository of data, a collection of data that is organized to achieve a certain purpose, in accordance with a rule.

2. The need to use a database

(1) Structured storage of large amounts of data information, convenient for users to effectively search and access.

(2) can effectively maintain the consistency of data information, integrity, reduce data redundancy.

(3) can meet the sharing and security requirements of the application.

3. Common databases: Oracle, SQL Server, MySQL.

Ii. Database Tables (entities: Entity)

1. In the database, the entity refers to all the things that are objective and can be described.

2. Each row (row) actually corresponds to an entity, usually called a record, and each column (COLNM) is called a field.

3. The same type of records are organized together as data structures called database tables (tables), which are collections of entities used to store specific data.

4. Represents a collection of records, and a database is a collection of tables and relationships (relationship).

Third, the database system

The database System,dbs contains a database management system (databases Management System,dbms) and a database administrator (db administrator,dba).

1. In a database system, data duplication is the data redundancy (redundance).

2. The completeness of the data refers to the accuracy of the data (integrality).

3. Primary key PRIMARY key: The value of this column is used to uniquely identify each row in the table and is used to enforce the entity integrity of the table. A table can have only one primary key, and null values are not allowed (null).

If two or more columns are combined to uniquely identify each row in the table, the key is also called a composite primary key.

(1) Minimum is a key with the fewest number of lists

(2) Stability refers to the characteristics of the data in the column do not update frequently

4. Foreign key FOREIGN key: a referenced relationship that ensures that a data item in the from table must exist in the primary table to avoid errors that do not exist.

"Foreign key" is used to achieve this, it is relative to the primary key, that is, "from the table" corresponds to the column in the "Main Table", in "from table" is called a foreign key or reference key, its value requirements and "primary table" the primary key or unique key corresponds.

Foreign keys are used to enforce referential integrity. The number of foreign keys a table can have.

Iv. Command Line Connection database

1.net stop MySQL

2.net start MySQL

3.mysql-h Server host address-u user name-p password

In native operation, you can omit the-h parameter

After-P can not write the password, press ENTER to enter the password. If you write a password, there is no space between-p and the password.

4. View MySQL version information and user name: Select version (), User ();

V. SQL (structured Query Language)

(1) DML (data manipulation Language) operation language: Insert,update,delete

(2) DDL (data definition Language) Definition language: Create table,create view,drop table

(3) DQL (data query Language) querying language: Select

(4) DCL Data Control Language language: Grant,revoke

Arithmetic operators: Assignment operators: Comparison operators: Logical operators

VI. Operational Database

Create database name;

show databases;

Use database name;

drop database name;

CREATE TABLE [if exists] table name () Table type | Table Character Set | comment;

drop table [if exists] tables name;

ALTER TABLE table name 1rename table Name 2;

ALTER TABLE name add new field;

ALTER TABLE name change original field new field data type [property];

ALTER TABLE name drop field;

Add a PRIMARY KEY constraint

ALTER TABLE name add constraint primary Key name primary key table name (primary key field);

Add a FOREIGN KEY constraint

ALTER TABLE name add constraint foreign key name foreign key (foreign key field) references associated table name (associated field);

VII. attribute constraints

nonempty constraint NOT NULL

Default constraint Defaults

Uniquely constrained unique key

PRIMARY KEY constraint primary key

FOREIGN KEY constraint foreign key

Auto Growth auto_increment

Eight, MySQL system help

Help query content;

The essential skill of program ape: Database Management--about MySQL