<11day>_ database operation-"Mysql"

Content:

1.Mysql

Program:

　　　　　　--socket Client

2. Connect based on IP and port

3. Send the instruction (sent):

Xxxxx

7. Acceptance of results

　　　　　　--socket Service Side

1. Run up

4. Get instructions (recv):

Xxxxx

5. Parsing commands

Folder operations:

File operation:

File content Operations

6. Return results

----------------------------------theSQL statement ---------------------------------

One, folder operation (database):

　　　　Create:

Create Database db1;

Create DATABASE DB1 default CharSet utf-8;

　　　　Delete:

Drop database db1;

　　　　Go to Folder:

Use DB1;

Second, file operation (data sheet):

To create a table:

CREATE TABLE TB1 (

ID int,

Name Char (20),

Age int,

Gender char (1)

)

To delete a table:

drop table tb1;

----------------------------------------------------------------------------------

　1. Column names

　2. Data type

　3. Whether it is possible to empty

　4. Default value

CREATE TABLE TB1 (

ID int NOT NULL, # ID column cannot be empty

Name Char (20),

Age int Default, # ages is defaulted to

Gender char (1)

)

　5. Self-increment (one table can only have one self-increment column)

CREATE TABLE TB1 (

ID int NOT NULL auto_increment, # auto_increment indicates self-increment

Name Char (20),

Age int Default, # ages is defaulted to

Gender char (1)

)

　6. Primary key:

Constraint: cannot be empty, cannot be duplicated

Indexing: Accelerating lookups

　7. Foreign Key:

Using foreign keys to create a data table reduces the use of space (one table to multiple tables)

Constraint: Only data that already exists for a column in a table

　　　　Constraint xxxx foreign KEY (department_id) references Deparment (ID)

---------------------------------------Create a table-a single table--------------------------------

CREATE TABLE UserInfo (

ID int NULL Auto_increment primary KEY, # ID is column name, auto_increment represents ID increment, primary key represents primary key

Name Char (20),

Age int Default 18,

Gender char (1)

) Engine=innodb default Charset=utf8; # ENGINE=INNODB engine operation, set the default character set when creating a table UTF8

---------------------------------inserting data into the table---------------------------------

Insert data is ignore ID column

The Age column can be written without writing, the default is 18

Insert into UserInfo (Name,age,gender) VALUES (' ZHAOSJ ', 25, ' Male ');

################################# Creation--Multi-table #################################

1. Reasons for creating multiple tables:

　　

Assuming that the above data table occupies 12M on the hard disk, it means that there is a lot of wasted disk space.

2, save resources Way to create two tables

First sheet:

Statement:

CREATE TABLE UserInfo (

ID int NOT NULL auto_increment primary key,

Name Char (20),

Age int Default 18,

Gender char (1),

department_id int,

Constraint xxxx foreign KEY (department_id) references Deparment (ID) # Add constraints to prevent messy data

) Engine=innodb default Charset=utf8;

To add a constraint description:

This constraint is called: foreign key

Constraint xxxx foreign KEY (department_id) references Deparment (ID) # Add Constraint statement that represents the department_id and Deparment tables in the currently created table statement ID to associate with. One of the ' XXXX ' is the constraint of the name, can be arbitrarily up.

Second sheet:

Statement:

CREATE TABLE Deparment (

ID int NOT NULL auto_increment primary key,

Title char (32)

) Engine=innodb default Charset=utf8;

Data table Operations:

To insert data into the Deparment table:

Insert into Deparment (title) value (' IT department ');

Insert into Deparment (title) value (' Consulting department ');

To insert data into the UserInfo table:

Insert into UserInfo (name,age,gender,department_id) VALUES (' Alex ', 18, ' Male ', 2); # where the value of department_id can only write 1 or 2

Third, the file content operation (data line):

<11day>_ Database operation-"Mysql"