The basic operation of MySQL

SQL Structured Query Language (Structured query statement)

Contains fields and records.
A library of multiple data tables

Steps to build a E-r diagram:
1. Establish a table for each entity;
2. Select a primary key for each table;
3. Increase the foreign key to indicate a one-to-many relationship;
4. The establishment of a new table represents a many-to-many relationship;
5. Define constraint conditions;
6. Improve the quality of the evaluation relationship;
7. Select the appropriate data type and range of values for each field;

The E-R model consists of 3 basic elements of entities, attributes, and relationships.

Representation method:

An entity; a rectangle.
Properties: Inside the rectangle
Relationship: Straight line 1:1, 1:n, n

Primary key:

Uniqueness,

NO NULL.
Primary Key
<pi>serial------>auto_increment

Foreign key: A field A in table a corresponds to the primary key B of Table B, and field a becomes the foreign key of table A.

Constraints
1. PRIMARY KEY constraint (Primary key):
2. FOREIGN KEY constraint (Foreign key):
3. Uniqueness constraint (unique):
4. Non-null constraint (NOT NULL):
5. Check the constraint (check):
6. Default value constraint:

show databases; View database Information
Show engines; View Storage engine information
Set Table_type=innodb; Sets the current default storage engine to InnoDB
Show variables like ' table_type ';
Show CREATE TABLE table_name;
Show variables like ' collation% '; View the current word Fu She
Set names GBK; set character_set_client.character_set_connection and Character-_set_results as GBK

SQL script file for character set changes: File suffix. sql

Set Table_type=innodb;
Show variable like ' table_type ';
SET character_set_client = GBK;
SET character_set_connection = GBK;
SET character_set_database = GBK;
SET character_set_results = GBK;
SET character_set_server = GBK;
SET character_database = gbk_chinese_ci;
SET character_connection = gbk_chinese_ci;
SET character_server = gbk_chinese_ci;
Show variables like ' character% ';
Show variables like ' collation% ';

Execute the script file on the command line: \. C:\wamp\www\sql\init.sql

Management of the database:

To create a database:

CREATE DATABASE database_name;

Select the database for the current operation:

Use database_name;

Show the structure of the database:

Show CREATE DATABASE database_name;

To delete a database:
DROP DATABASE database_name;
Management of database tables:
CREATE TABLE table_name{
COLUMN_NAME1 data type [constraint]
.....
COLUMN_NAME (n) data type [constraint]
}
Delete the columns in the current table to add columns to the table that currently exists:
ALTER TABLE TABLE_NAMEALTER TABLE table_name
Drop column column_name;add column datatype constraint condition;
Change the datatype1 of columns in the current table, modify the column type, such as listed as Nvarch type, and modify its length to 100:
ALTER TABLE TABLE_NAME
Alter COLUMN column_name datatypealter TABLE TB ALTER column Col nvarchar (100)

2. Add a column:

ALTER TABLE TB ADD col2 nvarchar (+) null

When there is data in the table, the newly added column must be null or identity.

3, increase the constraint, set the default value of column col3 is 0:

ALTER TABLE TB ADD CONSTRAINT df_col3 DEFAULT 0 for Col3
Data type:
Numerical:
String:
Date:
Additional properties:
Null:
Auto_increment:

Create a database table using a script file:
use student; First declare the database of operations;
CREATE table classes (for database table creation;
class_id int auto_increment Primary key, attribute name type constraint in table
Class_no char (TEN) not null unique,
Class_name char (a) not null
);
Displays the structure of the database table:
Show tables; View all table names in the current operational database;
describe table_name; View the table structure of the classes table;
Show CREATE TABLE table_name; View the table structure
Delete database table:
DROP TABLE table_name by viewing the CREATE statement with the table named table_name;

Update operations for Table records:
Add to:
INSERT INTO table_name [(field List)] VALUES (list of values);
Insert into classes (Class_in,class_no,class_name) VALUES (NULL, ' 10chinese ', ' 10 Chinese ');
Insert into classes values (NULL, ' 10chinese ', ' 10 Chinese '); When adding data to all columns in a table, the field list can be omitted;
Modify:
Update table_name;
Set column_name = New_value[,next_column = new_value2]
[Where Condition expression]
Update student set student= ' Zhang Sanfeng ' where student_id=1;
Update score set grade=grade-5;
Update score set grade=grade+10 where student_id=1 and courses_id=2;
Delete:
Delete from table_name
[Where condition expression];
Delete Form score where student_id=1 and course_id=2;
Inquire:
Select Field List *: The field list is the full field of the data source.
Table name. *: When querying multiple tables, specify all fields of a table.
Field List: Specify the columns that you want to display.
form data source
[Where filter condition]
[GROUP by group expression]
[Having group filter conditions]
[Order by sort expression [Asc|desc]];

Select Field List
form data source
Limit [Start,end]length; The value of start defaults to 0; Top 2 Top percent
SELECT * FROM score limit 0, 3; query the first 3 records of the score table.
Equivalent to: SELECT * from score limit 3;

SELECT * FROM score where grade>80;
SELECT * FROM score order BY grade Desc;
Select sum (grade) from score where course_id=1; Use aggregate functions to return summary values;
SUM ()/avg ()/count ()/max ()/min ()

Escape of special characters begins with the backslash sign ' \ '

How to create an INDEX in a table?
How to alter the datatype of a table ' s column?
How create a view?
What is the view?

The basic operation of MySQL