MySQL database operation

Posted by herreram on Mon, 27 Dec 2021 21:39:55 +0100

1. Structure creation

create Structure type structure name structure description;

2. Display structure

show Structure type (plural)

Display structure creation details:
show create Structure type and structure name;

3. Data operation (data sheet)

Add data:
insert into Table name values

View data:
select from Table name

Update data:
update Table name set

Delete data:
delete from Table name

4. Summary

4.1 SQL is a programming language similar to natural language

Basic SQL instructions are in behavioral units
The SQL Directive requires a statement terminator

4.2 according to the object level of the database, basic SQL can be divided into three categories

Library operations: database related operations
Table operation: data table (field) related operations
Data operation: data related operation

5. SQL database operation

5.1 creating database

create database Database name[Database options]；

Library options:

The character set can be specified at the database level: charset/charcter set (inheriting DBMS by default)
At the database level, you can specify the school team set: collate (dependent character set)

For example:
create database db_1;

5.2 display database

5.2. 1 display all databases

show databases；

5.2. 2 display the created database

show create database db_1;

5.3 using database

For example: use Database name;
use db_1;

5.4 modifying the database

5.4. 1. Database modification is divided into two parts (Library options)

character set
School team collection

Database modification instruction (similar to creation instruction)

alter database Database name library options

Modify database character set

alter database db_2 charset gbk;

5.5 delete database

drop database db_1;

6.SQL table (field) operation

6.1 create data table

create table [Database name.]Table name(
	Field name field type,
	...
	Field name field type
)Table options;

For example: first use Database name
create table t_1(
	name varchar(50),
	age int；
	gendervarchar(10)
);

Expansion: storage engine refers to the way of data storage and management

(1)InnoDB

Default storage engine
Support transaction processing and mechanism
Unified data management

(2)Mylsam

Transactions and foreign keys are not supported
Data, table structure and index are managed independently
MySQL5. No maintenance for above 6

(3) If you want to create a data table that is the same as an existing table

create table Table name like Database name.Table name

6.2 display data sheet

(1) Show all tables

show tables；

(2) Displays the specified created data table

show tables from db_1;#What you see may not be true (the system will process)

(3) Show some associated data tables

show tables like '%like';
#_ It means to match one character (fixed position),% means to match N characters

\g: No difference from ordinary semicolons
\G: Display column data vertically
6.3 viewing data sheets

desc Table name;
describle Table name;
show columns from Table name;

For example:
desc tb_1;

6.4 change data sheet

Modify table name:
rename table tb_1(Original table name) to t1(New table name);

Modify table options:
alter table t1 charset gbk；

6.5 change fields

There are four types of fields:

New field: add[column]
change field name: change
Modification type: modify
Delete field: drop

6.5. 1 new field

alter table Table name add[column]Field name field type [Field properties] [Field location]

Example: add field age
alter table tb_1 add age int;

Example: add field nickname
alter table tb_1 add column nickname varchar(10);

6.5. 2 field location

There are two types of field positions:

First field: first
After a field: after field name already exists

alter table Table name field operation field location;

Example: add one id Field, put first
alter table tb_1 add int first；

Example: in name Add an ID card field after the field card
alter table tb_1 add card varchar(18) after name；

6.5. 3 change field name

alter table Table name change Original field name new field name field type [Field properties] [position]

Example:
alter table tb_1 change sfz(new field name) varchar(18);

6.5. 4 modify fields

alter table Table name modify Field name field type [Field properties] [position];

6.6 deleting fields

alter table Table name drop Field name;

7.SQL data operation

7.1 new data

There are two types of data insertion:

Full field insertion: insert into table name values (all values corresponding to the field list order);
Partial field insertion: insert into table name (field list) values (value sequence list corresponding to field list);

Example:#Give TB_ Insert a complete piece of data into table 1
insert into tb_1 values(1,'2000102','jim','Green');

Example:#Insert data according to field
insert into db_1 (id.sfz,name) values(2,'500000000','tom');

7.2 viewing data

Data viewing method:

View all fields: use * instead of all fields
View some fields: specify the field name

select */Field list from Table name;

Example: Viewing t_1 In the table name And identity information
select name，sfz from t_1;

Example: Viewing t_1 In the table id Information with a value of 1
select * from t_1 where id = 1；

7.3 update data

update Table name set field = New value[，field = New value] [where Conditional filtering]

Example: update ID information of all records
update t_1 set  sfz = '240035552';

Example: update multiple field data of a record
updata t_1 set name = 'lify',sfz = '5422211222' where id = 1;

7.4 deleting data

delete from Table name [where condition]；

Example: delete t_1 In the table id Data for 2
delete from t_1 where id = 2；

Topics: MySQL Data Warehouse

Programmer Think