Summary of basic operations for MySQL beginners

Library Operations

Query

1.SHOW DATABASE; ----Query all databases

2. SHOW CREATE DATABASE database name; ----Query the creation mode of a database

3.SHOW CREATE DATABASE xxx; ----Query the creation mode of xxx database

create

1.CREATE DATABASE database name; ----Create a database Note: You cannot create an existing database

2.CREATE DATABASE IS NOT EXISTS database name; ---- Check if the database exists, if not, create it

3.CREATE DATABASE database name CHARACTER SET character set name; ----Create a database and specify the character set, generally use utf-8

Revise

ALTER DATABASE database name CHARACTER SET character set name; ----Modify the character set of the database

delete

1.DROP DATABASE database name; ---- delete the database

2.DROP DATABASE IS EXISTS database name; ----First determine whether it exists and then delete it

use

SELETE DATABASE (); ----Query the database currently in use

USE database name; ----Use the specified database

Table Structure

Table query

1.SHOW TABLES; ----Query all tables in the database

2.DESC table name; ----View the structure of the specified table

3. SHOW TABLE STATUS FROM library name LIKE 'table name'; ----query table character set (like is equivalent to "=" in no special case)

Create Table

CREATE TABLE tablename(
Column Name 1 Data Type 1 Constraint,
Column Name 2 Data Type 2 Constraints,
…
Column Name n Data Type n Constraints
);

int: integer type

• age int
  

double: decimal type

• score double(5,2)
• price double
  

date: Date, only contains year, month, and day yyyy-MM-dd

datetime: Date, including year, month, day, hour, minute, and second yyyy-MM-dd HH:mm:ss

timestamp: The timestamp type contains year, month, day, hour, minute, and second yyyy-MM-dd HH:mm:ss

• If you do not assign a value to this field in the future, or assign a value of null, the current system time will be used by default to automatically assign a value.
  

varchar: string

• name varchar(20): maximum length of name is 20 characters
• zhangsan 8 characters Zhangsan 2 characters
  

CREATE TABLE The name of the table to be created LIKE the name of the table to be copied; ----Copy the table

Modify Table

1. ALTER TABLE table name RENAME TO new table name; ----Change the table name

2. ALTER TABLE table name CHARACTER SET character set name; ----Modify the character set of the table

**3.**ALTER TABLE table name ADD column name data type; ----Add a new column

4. ALTER TABLE table name MODIFY column name new column name new data type; ----Modify the new column name and data type

Delete operation in table

1.DROP TABLE table name; ---- delete the table

2.ALTER TABLE table name DROP column name; ---- delete the column

Adding data to the table - INSERT statement

1. INSERT INTO table name (column name 1, column name 2, ...) VALUES (value 1, value 2, ...); ---- add data to the table

2.SELECT * FROM product; ----View all data in the table

3. INSERT INTO table name VALUES (value 1, value 2, value 3, ...); ----Add data to all columns by default

4. INSERT INTO table name VALUES (value 1, value 2, value 3, ...), (value 1, value 2, value 3, ...), (value 1, value 2, value 3, ...); ----Batch add data

Note:

• The column names and the number of values ​​and data types should correspond
• Except for numeric types, data of other data types need to be quoted (single or double quotes are acceptable, single quotes are recommended)
  

Modify data in the table -updata

UPDATE table name SET column name 1 = value 1, column name 2 = value 2, … [where condition];
Note:

• Conditions must be added to the modification statement. If no conditions are added, all data will be modified.
  

Delete data from the table - delete

DELETE FROM table name [WHERE condition];

Note:

• It is the same as modifying data
  

Single table query

Query syntax:

select field list from table name list where condition list group by grouping field having condition after grouping order by sort limit paging limit

SELECT column name 1, column name 2, ... FROM table name; ----Multiple field queries

SELECT DISTINCT column name 1, column name 2, ... FROM table name; ----Duplicate removal, but only when all the columns in the query are repeated will duplicate removal be performed

SELECT column name 1 operator (+ - * /) column name 2 FROM table name; ---- four arithmetic operations

SELECT column name 1, column name 2, ... AS alias FROM table name; ----as is used to create aliases, which is simple and easy to understand

Conditional Query

Condition Classification

Aggregate functions

Function name and its function

Aggregate Function Syntax

-- Standard syntax SELECT function name (column name) FROM table name [WHERE condition];

-- Calculate the total number of records in the product table SELECT COUNT(*) FROM product;

-- Get the highest price SELECT MAX(price) FROM product;
-- Get the name of the product with the highest price SELECT NAME,price FROM product WHERE price = (SELECT MAX(price) FROM product);

-- Get the minimum stock SELECT MIN(stock) FROM product;
-- Get the name of the product with the lowest stock SELECT NAME,stock FROM product WHERE stock = (SELECT MIN(stock) FROM product);

-- Get the total stock quantity SELECT SUM(stock) FROM product;
-- Get the total stock quantity of the brand Apple SELECT SUM(stock) FROM product WHERE brand='Apple';

-- Get the average price of products with brand Xiaomi SELECT AVG(price) FROM product WHERE brand='小米';

Sorting query

Classification

- Note: For multiple sorting conditions, the second condition will be evaluated only when the condition value of the current side is the same

Sorting syntax

-- Standard syntax SELECT column name FROM table name [WHERE condition] ORDER BY column name 1 sorting method 1, column name 2 sorting method 2;

-- Sort by stock in ascending order SELECT * FROM product ORDER BY stock ASC;

Group query

-- Standard syntax SELECT column name FROM table name [WHERE condition] GROUP BY grouping column name [HAVING condition filtering after grouping] [ORDER BY sorting column name sorting method];

Pagination Query

-- Standard syntax SELECT column name FROM table name [WHERE condition] GROUP BY grouping column name [HAVING filter condition after grouping] [ORDER BY sorting column name sorting method] LIMIT start index, query number;
-- Formula: Start index = (current page number - 1) * number of items displayed per page

constraint

1. Concept and classification of constraints

The concept of constraints

• Limit the data in the table to ensure the correctness, validity and completeness of the data!
  

Classification of constraints

2. Primary key constraint

Primary key constraint characteristics

• The primary key constraint contains two functions: non-empty and unique
• A table can have only one column as the primary key
• The primary key is generally used to uniquely identify the data in the table.
  

Add a primary key constraint when creating a table

-- Standard syntax CREATE TABLE table name (
	Column name Data type PRIMARY KEY,
    Column name data type,
    ...
);

-- Create the student table CREATE TABLE student(
	id INT PRIMARY KEY -- add a primary key constraint to id);

-- Add data INSERT INTO student VALUES (1),(2);
-- The primary key is unique by default. If duplicate data is added, an error will be reported. INSERT INTO student VALUES (2);
-- The primary key is not empty by default, and null data cannot be added. INSERT INTO student VALUES (NULL);

-- Query the student table SELECT * FROM student;
-- Query the student table details DESC student;

Deleting a primary key

-- Standard syntax ALTER TABLE table name DROP PRIMARY KEY;

-- Delete the primary key ALTER TABLE student DROP PRIMARY KEY;

Add a primary key separately after creating the table

-- Standard syntax ALTER TABLE table name MODIFY column name data type PRIMARY KEY;

-- Add primary key ALTER TABLE student MODIFY id INT PRIMARY KEY;

3. Primary key auto-growth constraint

Add a primary key auto-increment constraint when creating a table

-- Standard syntax CREATE TABLE table name (
	Column Name Data Type PRIMARY KEY AUTO_INCREMENT,
    Column name data type,
    ...
);

-- Create the student2 table CREATE TABLE student2(
	id INT PRIMARY KEY AUTO_INCREMENT -- add a primary key auto-increment constraint to id);

-- Add data INSERT INTO student2 VALUES (1),(2);
-- Adding null value will automatically increase INSERT INTO student2 VALUES (NULL), (NULL);

-- Query the student2 table SELECT * FROM student2;
-- Student2 table details DESC student2;

Remove autogrowth

-- Standard syntax ALTER TABLE table name MODIFY column name data type;

-- Delete automatic growth ALTER TABLE student2 MODIFY id INT;

Add auto-growth separately after creating the table

-- Standard syntax ALTER TABLE table name MODIFY column name data type AUTO_INCREMENT;

-- Add automatic growth ALTER TABLE student2 MODIFY id INT AUTO_INCREMENT;

4. Unique Constraint

Add unique constraints when creating a table

-- Standard syntax CREATE TABLE table name (
	Column name data type UNIQUE,
    Column name data type,
    ...
);

-- Create the student3 table CREATE TABLE student3(
	id INT PRIMARY KEY AUTO_INCREMENT,
	tel VARCHAR(20) UNIQUE -- add a unique constraint to the tel column);

-- Add data INSERT INTO student3 VALUES (NULL,'18888888888'),(NULL,'186666666666');
-- Adding duplicate data will result in an error INSERT INTO student3 VALUES (NULL,'18666666666');

-- Query the student3 data table SELECT * FROM student3;
-- Student3 table details DESC student3;

Dropping a unique constraint

-- Standard syntax ALTER TABLE table name DROP INDEX column name;

-- Delete the unique constraint ALTER TABLE student3 DROP INDEX tel;

Add a unique constraint after creating the table

-- Standard syntax ALTER TABLE table name MODIFY column name data type UNIQUE;

-- Add a unique constraint ALTER TABLE student3 MODIFY tel VARCHAR(20) UNIQUE;

5. Not Null Constraint

Add a non-null constraint when creating a table

-- Standard syntax CREATE TABLE table name (
	Column name data type NOT NULL,
    Column name data type,
    ...
);

-- Create the student4 table CREATE TABLE student4(
	id INT PRIMARY KEY AUTO_INCREMENT,
	NAME VARCHAR(20) NOT NULL -- add a non-null constraint to name);

-- Add data INSERT INTO student4 VALUES (NULL,'张三'),(NULL,'李四');
-- Adding a null value will result in an error INSERT INTO student4 VALUES (NULL,NULL);

Remove Not Null Constraint

-- Standard syntax ALTER TABLE table name MODIFY column name data type;

-- Delete the not-null constraint ALTER TABLE student4 MODIFY NAME VARCHAR(20);

Add a non-null constraint after creating the table

 -- Standard syntax ALTER TABLE table name MODIFY column name data type NOT NULL
 
 -- Add a non-null constraint ALTER TABLE student4 MODIFY NAME VARCHAR(20) NOT NULL;

Foreign key constraints

Format of foreign key constraints (usually written at the end of creating a table)

CONSTRAINT Foreign key name FOREIGN KEY (name of foreign key column in this table) REFERENCES Primary table name (name of primary key column in the primary table)

Summarize

This concludes this article on the basic operations for MySQL beginners. For more information on basic MySQL operations, please search previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!