MySQL Library and table operations

MySQL Library and table operations

Basic syntax

Create Database db3; Create a database

show databases; //Presentation database

MySQL library, which holds the authorization table, the user table, the default table

Information_schema Data dictionary table, temporary library, is view,

Information about the performance of Performance_schema

Show CREATE Database db3; Shows what statement to use when creating the database

/*! 40100 DEFAULT CHARACTER SET latin1*/Auto-complete statement

Create database DB4 Character Set UTF8

Deleting a database

Drop database db3;

Basic concepts of MySQL Tables

What is a table, the container that holds the data, the rows and columns

Columns are structures, rows are information

Table is the important foundation of relational database

Design meets three paradigms

First paradigm:

The fields in the data table are single attributes and cannot be divided

This single attribute is made up of basic types, including integers, real numbers, character types, logical types, dates, and so on.

Can be divided into points, so that can not be divided until

Example

Number Address

1 Haidian South Street, Beijing

2 Beijing Dongcheng by the main Street

Number City counties Street

1 Haidian South Street, Beijing

2 Beijing Dongcheng by the main Street

Second paradigm:

On the basis of satisfying the first paradigm, there is no partial function dependency of non-critical fields on any of the candidate key fields in the database table (some of the function dependencies refer to situations where some fields in the composite keyword determine non-critical fields), or all non-critical fields are completely dependent on any set of candidate keywords.

Don't interfere with each other. Don't put everything in one table.

Columns do not affect each other, you want to split the table.

Example:

Data redundancy: Unified course, n students elective, credit repeat N-1 times.

caused by data redundancy. Update, delete, insert exception.

The third paradigm:

On the basis of the second paradigm, if there is no non-critical field in the data table for any of the candidate key fields the curtain function depends on the third normal form

So-called transfer function dependence, refers to if the village a-b-c

The primary key in the set table is not dependent on the other, simply unique.

Create a data table

CREATE table < table name >

(

Column name 1, data type [constraint] [default value],

Column Name 2, data type [constraint] [default value],

Column name 3, data type [constraint] [default value]

) [engine= storage engine][chararcter set= character set];

Default charset=latin1;

Example:

Create Database db1;

Use DB1;

CREATE TABLE T1 (

ID int,

Name varchar (10)

);

Show tables; View the Created table

Show CREATE Database db1;

Show CREATE table T1; \g changing the output format

View and delete data tables

Show tables;

Show CREATE TABLE table_name;

drop table if exists < table name >;

Show warning;

Error will interrupt all statement execution. Warning does not terminate

Constraints

Storing data on column constraint specifications

1 PRIMARY KEY constraint primary key,

Unique and not allowed to be empty

Example: INSERT into T1 values (2,2), (3,3);

You can set the primary key in multiple columns

Example:

CREATE TABLE T1

(

UID Int (10),

CertId Int (10),

Name varchar (10),

Sex enum (' f ', ' m '),

Primart Key (Uid,certid)

) Engine=myisam character Set UTF8;

2 FOREIGN KEY constraints

Foreign keys are used with primary keys, and if not NULL, each foreign key value must be equal to a value of the primary key in another table

The foreign key data type must be consistent with the primary key

The foreign key can be empty

Example:

CREATE TABLE T2

(

FID int,

Phone varchar (13),

Localtion varchar (50),

Constraint fk_t1 foreign KEY (FID) pefernces T1 (UID)

);

Constraint fk_t1 "FOREIGN key Name" foreign key (FID) references T1 (UID);

Treat the program like a person

3 non-null constraint NOT NULL

The value in the corresponding column for the constraint cannot be null

4 Uniqueness Constraint unique cannot be duplicated

5 default constraint ' UN '

You cannot insert empty unless the default value is empty

MySQL 5 big constraint.

Self-growth auto_increment;

Settings for automatic system growth

How to modify a data table

ALTER TABLE < old table name > rename < new table name >;

MySQL Library and table operations