9th Chapter MySQL

MySQL Introduction

Database system:

Databases: Folders

Data sheet: Files

Table record: One Piece of data

Database management software

MySQL: Open source

Oracle

Sql server

Mysql:

Service side: Mysqld

Client: cmd php python

Database operations:

show databases; Show all database names

CREATE DATABASE database_name [character set UTF8];

Show CREATE DATABASE database_name; To view the creation information for a database

Use database_name; switch database

ALTER DATABASE db_name [character set xxx] modifies the character set of the databases

drop database [if exists] db_name; Deleting a database

Data table Operations:

1 Create a table:

CREATE TABLE table_name (

filed field type [integrity constraint],

filed field type [integrity constraint],

filed field type [integrity constraint],

)；

2 Viewing tables

DESC table_name; View table Structure

Show tables; is the name of all the tables under the database

Show CREATE table table_name; View tables creation information for a database

3 Modify the table:

ALTER TABLE table_name ADD field type integrity constraint

ALTER TABLE table_name MODIFY field type integrity constraint

ALTER TABLE table_name change field type integrity constraint

ALTER TABLE table_name DROP field type integrity constraint

4 Deleting a table

drop table tab_name;

Table Logging Operations:

1 Add Record:

Insert [into] tab_name (field1,field2 ...) VALUES (Values1,values2 ...),

(Values1,values2 ...),

... ；

Insert Tab_name set Field=value, Field=value,..... ；

2 Modifying records

Update tab_name set field=value WHERE clause

3 Delete Table records:

Delete from Tab_name WHERE clause

Clear table record: 1 delete from Tab_name

2 truncate TAB_NAME (recommended when data volume is large)

4 query table records (******)

SELECT * FROM Tab_name: Displays all field information for all records

SELECT [DISTINCT] filed,filed2,..... from Tab_name

WHERE clause

GROUP BY group key: grouping conditions

HAVING clause: filtering

ORDER BY

Limit

Check the average salary for each province

Select City,avg (Salary) from the EMP group by city;

Name of the province that queries the average wage greater than 8000

Select City,avg (Salary) from EMP Group BY city has avg (salary) > 8000

Multi-Table query:

Internal connection query:

SELECT * FROM emp inner join DEP on Emp.dep_id=dep.id;

Left OUTER JOIN query:

SELECT * from the EMP left join DEP on Emp.dep_id=dep.id;

Integrity constraints:

PRIMARY KEY constraint:

Primary key: Non-null (NOT NULL) and only (unique)

FOREIGN KEY constraint (associated field):

Table relationships:

Table-to-table relationship (two sheets)

One-to-many

Set the associated field in more than a table

CREATE TABLE EMP (

ID INT PRIMARY KEY,

Name VARCHAR (20),

dep_id INT,

FOREIGN KEY (dep_id) REFERENCES dep (ID)

)

Many-to-many

With the third table,

CREATE TABLE Student2teacher (

ID INT PRIMARY KEY auto_increment,

studnet_id INT,

teacher_id INT,

FOREIGN KEY (studnet_id) REFERENCES student (ID),

FOREIGN KEY (teacher_id) REFERENCES teacher (ID),

)

One

Set the associated field to unique

CREATE TABLE Author (

ID INT PRIMARY KEY,

Name VARCHAR (20),

authordetial_id INT Unique,

FOREIGN KEY (dep_id) REFERENCES dep (ID)

)

9th Chapter MySQL