catalogue
View the table structure contained in the database
Display the structure of the data table
1.4 Differences between char and varchar
3.1 DDL data definition language
Delete the specified data table
3.2 DML data manipulation language
Insert a new data record into the data table
Modify and update data records in the data table
Delete the specified data record in the data table
Expand table structure (add fields)
Modify field (column) name, add unique key and delete unique 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
| int | Integer (e.g. 1 2 3) |
|---|---|
| float | Single precision floating-point 4-byte 32 bits (accurately representing six decimal places) |
| double | Double precision floating-point 8-byte 64 bit |
| char | Fixed length character type |
| varchar | Variable length character type |
| text | text |
| image | picture |
| 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；