Basic Operation of MySQL Database Management

catalogue

1, Basic database operations

1.1 log in to the database

1.2 view database structure

View database letter

View the table structure contained in the database

Display the structure of the data table

1.3 Common database types

1.4 Differences between char and varchar

2, MySQL data file

2.1 MYD files

2.2 MYI file

2.3 MyISAM storage engine

3, SQL statement

3.1 DDL data definition language

create new database

Create a new table

Delete the specified data table

Delete the specified database

3.2 DML data manipulation language

Insert a new data record into the data table

Query data record

Modify and update data records in the data table

Delete the specified data record in the data table

3.3 DQL Data Query Language

3.4 DCL data control language

Expand table structure (add fields)

Modify Table Field Data Type

Delete field

Modify field (column) name, add unique key and delete unique key

Add Delete Primary Key

1, Basic database operation

1.1 Login to database

mysql -u root -p  enter 

Input password
 or

mysql -uroot -p Password Enter

1.2 View Database Structure

View database letter

show databases;
show databases\G

Note: database commands are case insensitive

View the table structure contained in the database

use Database name;                  #Switch to the database, and do not add it later;
show tables;                  #To view the tables in the database, add;
show tables in mysql;         #To view the tables in the specified database, this command does not need to use to switch databases and then view the tables

Displays the structure of the data table

describe [Database name].Table Name;     It can be reduced to: desc Table name;

1.3 common database types

intInteger (e.g. 1 2 3)
floatSingle precision floating-point 4-byte 32 bits (accurately representing six decimal places)
doubleDouble precision floating-point 8-byte 64 bit
charFixed length character type
varcharVariable length character type
texttext
imagepicture
decimal(5,2)5 numbers of effective length with 2 digits after the decimal point

1.4 Differences between char and varchar

For char, the maximum number of characters that can be stored is 255. If the actual length of the stored data is smaller than the specified length, char will fill in spaces to the specified length. If the actual length of the stored data is greater than the specified length, the low version will be intercepted, and the high version will report an error. The length of the char is immutable, while the length of the varchar is variable. That is, define a char[10] and varchar[10]. If the stored data is' csdn ', then the length of the char is still 10. In addition to the character' csdn ', it is followed by six spaces. The varchar immediately changes the length to four varchar storage rules:

Under version 4.0, varchar(20) refers to 20 bytes. If UTF8 Chinese characters are stored, only 6 (3 bytes for each Chinese character) can be stored. Version 5.0 or above, varchar(20) refers to 20 characters. Whether numbers, letters or UTF8 Chinese characters (3 bytes for each Chinese character) are stored, 20 can be stored. The maximum size is 65535 bytes

2, MySQL data file

The data files of the MysQL database are stored in the */usr/local/mysql/data * directory. Each database corresponds to a subdirectory, which is used to store data table files. Each data table corresponds to three files with extensions of ". frm", "MYD" and ". MYI"

2.1 MYD file

The MYD file is dedicated to the MyISAM storage engine and stores the data of the MyISAM table. Each MyISAw table will have a ". MyD" file corresponding to it, which is also stored in the folder of the database, together with the ". frm" file.

2.2 MYI file

The ". MYI" file is also dedicated to the MyISAM storage engine and mainly stores index related information of the MyISAM table. For MyISAM4 storage, the contents that can be cache d mainly come from the "MYI" file. Each MyISAM table corresponds to a ". MYI" file, which is stored in the same location as ". frm" and ". MYD".

2.3 MyISAM storage engine

The tables of the MyISAM storage engine are stored in the database. Each table is stored as three physical files (frm,myd,myi) named after the table name. Each table has only such three files as MyISAM

The storage of storage type tables, that is, no matter how many indexes the table has, it is stored in the same. MYI file.

In addition, there are ". ibd" and ibdata files, both of which are used to store Innodb data. The reason why there are two files to store Innodb data (including indexes) is that Innodb's data storage mode can be configured to decide whether to use a shared tablespace to store data or an exclusive tablespace to store data. The exclusive tablespace storage method uses ". IBD" files to store data, and each table has a ". IBD" file, which is stored in the same location as MyISAM data. If the shared storage table space is selected to store data, the ibdata file will be used to store the data. All tables share one (or more, self configurable) ibdata file.

3, SQL statement

SQL statements are used to maintain and manage databases, including data query, data update, access control, object management and other functions.

SQL language classification:

DDL data definition language, used to create database objects, such as libraries, tables, indexes and other DML data manipulation languages, used to manage data in tables DQL data query language, used to find qualified data records from data tables DCL data control language, used to set or change database user or role permissions

3.1 DDL Data Definition Language

Used to create database objects, such as databases, tables, indexes, etc. Delete databases and

create    Create databases and tables
drop    Delete databases, tables and fields
alter   Change the structure of the table

create new database

create database Database name;

Create a new table

CREATE TABLE Table Name (Field 1 data type,Field 2 Data Type[,...][,PRIMARY KEY (Primary Key Name)]);

Generally, the primary key selects fields that can represent uniqueness, and null values are not allowed( NULL),A table can only have one primary key.

 

Delete the specified data table

use Database name
drop table Table Name; For example: drop class1; 
drop table [Database name].Table Name;   

Delete the specified database

 drop database Database name;

3.2 DML data manipulation language

The data manipulation language is used to manage the data in the table, insert, delete and modify the data in the database.

insert    insert
update   modify
delete    Delete the specified content in the table

Format:

INSERT INTO Table Name(Field 1,Field 2[,...]) VALUES(Value of field 1,Value of field 2,...);

Insert a new data record into the data table

create table class1 (id int NOT NULL,name char(10) NOT NULL, score decimal(5,2), passwd char(48) DEFAULT'',PRIMARY KEY (id));   To create a table, you need to define the field structure of the table


insert into class1 (id,name,score,passwd) values(1,'Xiao Ming',55,123456);  Insert table contents

 

Query data record

SELECT Field name 1,Field name 2[,...] FROM Table Name [WHERE Conditional expression]; 

Modify and update data records in data tables

UPDATE Table Name SET Field name 1=Field value 1[,Field name 2=Field value 2] [WHERE Conditional expression];

Delete the specified data record in the data table

DELETE FROM Table Name [WHERE Conditional expression];

3.3 DQL data query language

Format: select Field 1,Field 2 from Table name[ where Conditional Expression]

select name from class1\G         Vertical display in list mode
select * from class1 limit 2;      Show only the first 2 lines
select * from class1 limit 2,3;    Display the first 3 lines after the second line

select id,name from class1; View the id,name field

 

3.4 DCL data control language

ALTER TABLE Old table name RENAME New table name;     Changing the table name does not affect the data

Expand table structure (add fields)

ALTER TABLE Table Name ADD address varchar(60) default 'Address unknown';

Modify table field data type

ALTER TABLE Table Name MODIFY Field Name Data Type;  Modify the data type of the field

Delete field

 

 

Modify field (column) name, add unique key and delete unique key

ALTER TABLE Table Name CHANGE Old column name New column name Data type [unique key];

unique key : Unique key (property: unique, but can be null, and null value can only appear once)
primary key: Primary key (property: unique and non empty)

ALTER TABLE Table Name CHANGE Old attribute name new attribute name new data type;

alter table test change name mingzi varchar(15);Modify table field name and type

Each table can only have one primary key, but many contents need uniqueness, which is the role of the unique key

Add Delete Primary Key

ALTER TABLE Table Name ADD PRIMARY KEY (Field name);     Add Primary Key

alter table test add primary key mingzi;

ALTER TABLE Table Name DROP PRIMARY KEY;  Delete primary key

alter table test drop primary key;

Tags: Database SQL

Posted by bibby on Wed, 14 Sep 2022 02:53:00 +0930