preface

MySQL is a real multi-threaded and multi-user SQL database service. With its high performance, high reliability and easy to use characteristics, MySQL has become the most popular open source database system in the server field. Master the basic operation of MySQL database, which must be mastered in operation and maintenance services.

1, Using MySQL database

Database - > data table - > row (record): used to describe the information of an object
Database - > data table - > column (field): used to describe the attributes of objects

1. Common data types

int: integer
float: single precision floating point 4-byte 32-bit
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

2. View database structure

3.SQL statement

SQL yes Structured Query Language An abbreviation for structured query language
SQL Statement is used to maintain and manage the database, including data query, data update, access control, object management and other functions
SQL Language classification:
DDL(Data definition language) is used to establish databases, database objects and define their columns
DML(Data manipulation language) is used to query, insert, delete and modify data in the database
DQL(Data query language) is used to find qualified data records from the data table
DCL(Data control language) is used to control the access permission, access authority, etc. of database components

2, Create and delete libraries and tables

1. Create a new database

CREATE DATABASE Database name;				#Specify the database name as a parameter

#The newly created database is empty and does not contain any tables
#In the / usr/local/mysql/data directory, a folder with the same name as the newly created library will be automatically generated

Example:
CREATE DATABASE kobe bryant;
SHOW DATABASES;

2. Create a new table

CREATE TABLE Table name (Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);
#Primary keys generally select fields that can represent uniqueness. NULL values are not allowed. A table can only have one primary key

Example:
create database Audi;
use Audi;
create table 3Q (id int not null,name char(10) not null,sex char(1),primary key (id));
#Create a table named CLASS
#Field 1: id cannot be null
#Field 2: name string is 10 bytes long and is not null
#Field 3: sex fixed length string type is 1
#Field 4: specify the primary key as the id field

3. Delete the specified data table

DROP TABLE Database name.Table name;
#You need to switch to the target library by executing the "USE" statement first
#If you do not need USE to enter the database, you need to add the database name. Do this when you know it

4. Delete the specified database

DROP DATABASE Database name;

3, Manage data records in tables

1. Insert a new data record into the data table

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

Example:
create database school;

use school;

create table CLASS2 (id int not null,name char(20) not null,sex char(1) not null,primary key (id));

insert into CLASS2 (id,name,sex) values(1,'zhangsan','male');

2. Query data records

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

example:
select * from CLASS2;
select name,sex from CLASS2 where id=1;

select * from CLASS2 limit 2;     #Only the first 2 rows are displayed
select * from CLASS2 limit 2,3;   #Displays the first 3 lines after line 2

3. Modify and update the data records in the data table

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

Example:
insert into CLASS2 (id,name,sex) values(2,'lisi','female');
insert into CLASS2 (id,name,sex) values(3,'wangwu','male');
select * from CLASS2;

update CLASS2 set id=4 where name='zhangsan';
select * from CLASS2;

update CLASS2 set name='sicong',sex='male' where id=2;
select * from CLASS2;

4. Delete the specified data record in the data table

DELETE FROM Table name [WHERE Conditional expression];

Example:
delete from CLASS2 where id=4;
select * from CLASS2;

5. Modify table name and table structure

1. Modify table name

ALTER TABLE Old table name RENAME New table name;

Example:
alter table CLASS2 rename CLASS3;
show tables;
select * from CLASS3;

2. Expand table structure (add field)

ALTER TABLE Table name ADD address varchar(50) default 'The address is unknown';
#Default "unknown address": indicates that the default value of this field is unknown; Can be used with NOT NULL

Example:
alter table CLASS3 add address varchar(50) default 'The address is unknown';

3. Modify the field (column) name and add a unique key

ALTER TABLE Table name CHANGE Old column name new column name data type [unique key];

Example:
alter table CLASS3 change name student_name varchar(20) unique key;
select * from CLASS3;

insert into CLASS3 (id,student_name,sex) values (1,'zhangsan','male');
select * from CLASS3;
insert into CLASS3 (id,student_name,sex) values (4,'zhangsan','male');

4, Create temporary table

After the temporary table is created successfully, the temporary table created cannot be seen by using the SHOW TABLES command, and the temporary table will be destroyed after the connection exits.
Before exiting the connection, you can also perform operations such as adding, deleting, modifying, and querying. For example, use the DROP TABLE statement to manually and directly delete the temporary table.

CREATE TEMPORARY TABLE Table name (Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);

Example:
CREATE TEMPORARY TABLE Table name (Field 1 data type,Field 2 data type[,...][,PRIMARY KEY (Primary key name)]);

Example:
create temporary table test04 (id int not null,name varchar(20) not null,cardid varchar(18) not null unique key,primary key (id));

show tables;

insert into test04 values (1,'wangsi','444444');
select * from test04;

summary

This is the basic operation of the database, in which the select query is what our operation and maintenance engineers must master, and other knowledge is enough.