Basic Mysql operations

Basic Mysql operations

SQL Structured Query Language (Structured Query statement)

Contains fields and records.
A database composed of multiple data tables

Build Steps for a E-R diagram:
1. Create a table for each object;
2. Select a primary key for each table;
3. Add a foreign key to indicate a one-to-many relationship;
4. Create a new table to indicate many-to-many relationships;
5. Define constraints;
6. Improve the Quality of relationship evaluation;
7. Select the appropriate data type and value range for each field;

The E-R model consists of three basic elements: entity, attribute, and relation.

Representation Method:

Entity; rectangle
Attribute: Inside the rectangle
Link: Line 1: 1: n, n: n

Primary key:

Uniqueness,

No null.
Primary Key
<Pi> Serial ------> auto_increment

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

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 constraints ):
6. default constraint (default ):

Show databases; view database information
Show engines; view storage engine information
Set table_type = InnoDB; set the current default storage engine to InnoDB
Show variables like 'table _ type ';
Show create table table_name;
Show variables like 'collation % '; view the current collation
Set names gbk; set character_set_client.character_set_connection and character-_ set_results to gbk at a time.

 

Changing the character set in the SQL script file: The file suffix is. 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 % ';

Run the script file \. C: \ wamp \ www \ SQL \ init. SQL in the command line.

Database Management:

 

Create a database:

Create database database_name;

Select the database for the current operation:

Use database_name;

Display the database structure:

Show create database database_name;

Delete database:
Drop database database_name;
Manage database tables:
Create table table_name {
Column_name1 data type [constraints]
.....
Column_name (n) Data Type [constraints]
}
Delete the columns in the current table and add columns to the existing table:
Alter table table_namealter table table_name
Drop column column_name; add column datatype constraints;
Change the column ype1 of the column in the current table and modify the column type. For example, if the column is nvarch, change 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 (100) null

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

3. Add constraints and set the default value of col3 to 0:

Alter table tb add constraint DF_col3 DEFAULT 0 FOR col3
Data Type:
Value:
String:
Date:
Additional attributes:
NULL:
Auto_increment:

Create a database table using a script file:
Use student; declare the database to be operated first;
Create table classes (create a database table;
Class_id int auto_increment primary key, the attribute name type constraints in the table
Class_no char (10) not NULL unique,
Class_name char (20) not NULL
);
Display the structure of the database table:
Show tables; view the names of all tables in the current database;
Describe table_name; view the table structure of the classes table;
Show create table table_name; view the statement for creating a table named table_name to view the table structure
Delete A database table:
Drop table table_name;

Update operations for table records:
Add:
Insert into table_name [(Field List)] values (Value List );
Insert into classes (class_in, class_no, class_name) values (NULL, '10china', '10china ');
Insert into classes values (NULL, '10china', '10chinese'); when adding data to all columns in the 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;
Query:
Select field list *: The field list is all fields of the data source.
Table name. *: specifies all fields of a table during multi-Table query.
Field List: Specifies the columns to be displayed.
Form Data Source
[Where filter condition]
[Group by expression]
[Having grouping filtering condition]
[Order by sorting expression [asc | desc];

Select Field List
Form Data Source
Limit [start, end] length; the default value of start is 0; top 2 top 50 percent
Select * from score limit; query the first three 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 the aggregate function to return the summary value;
Sum ()/avg ()/count ()/max ()/min ()

Escape special characters starting with the backslash '\'

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