1 data query (important)
None of the queries will change the data in the table
(1) The keyword is distinct
duplicate removal
select distinct(name: Price)from 【表名】product
Copy the codeOnly the columns that have been retracted are displayed.
(2) The query can be calculated to query all data, and the price of each commodity is increased by 10%
select pid,pname,category,price*0.1 + price,category_id from product
Copy the code(3) Rename the keyword as in the query result
select pid,pname,category,price*0.1 + price as 'price',category_id from product
Copy the code(4) Simplified writing method of renaming query results
Drop as, drop “, write and rename directly.
select pid,pname,category,price*0.1 + price price,category_id from product
Copy the code(5) Query where
# Query the price of the product>3000The goodsSELECT * FROM product WHERE price > 3000; # query pid=1The goodsSELECT * FROM product WHERE pid = 1; # query pid<>1The goodsSELECT * FROM product WHERE pid <> 1; # query price in3000to6000Between the goods that are recommended to usebetween
SELECT * FROM product WHERE price > = 3000 AND price < = 6000
# betweenOnly the first number is small and the first date is earlySELECT * FROM product WHERE price BETWEEN 3000 AND 6000# query pid in1.5.7.15Range of goodsSELECT * FROM product WHERE pid = 1 OR pid = 5 OR pid =7 OR pid = 15# Recommended useinThe querySELECT * FROM product WHERE pid IN(7.1.5.15) # Query the product name beginning with incense (incense series) fuzzy query keywordlike# query wildcard,%Matches any characterSELECT * FROM product WHERE pname LIKE 'fragrance %'# Query product name contains flowersSELECT * FROM product WHERE pname LIKE '%n%'Select * from product where product name begins with incense5The word # queries wildcards and _ matches one characterSELECT * FROM product WHERE pname LIKE 'sweet ____'
Copy the code(6) Query group by
/* Query group by */# Price is calculated according to the category (s) of the product (s), and it is grouped in the last category (s) of the price (s) of the column (s) of the price (s)SELECT SUM(price),category_id FROM product
GROUP BYCategory_id # Additional requirement: total price, less than1000Don't want to/* Where is a conditional filter, which can only be applied to the original table */
SELECT SUM(price) `sum`,category_id FROM product
GROUP BY category_id HAVING `sum`> =1000
Copy the code(7) Select order by
/* Order by ascending keyword asc By default, the descending keyword desc */ can be written# Query the list of items and sort the price in ascending orderSELECT * FROM product ORDER BYPrice # query the list of items and sort them in ascending orderSELECT * FROM product ORDER BY price DESCThe first column is equal and the second column is equalSELECT * FROM product ORDER BY price, pid ASCSelect * from list where price is higher than or equal to800
SELECT * FROM product WHERE price > = 800 ORDER BY price
Copy the code(8) Aggregate function
Count Number of statistics
Max maximum
Min min
Avg average
The sum total
Note: Aggregate functions ignore null values when they are evaluated
Select * from 'product' where 'product' = 'product'SELECT COUNT(*) FROM product;
SELECT COUNT(pid) FROM product;
SELECT COUNT(1) `count` FROMproduct; The sum function calculates the total price of all itemsSELECT SUM(price) FROMProduct # mathematical function, not number column, all press0To calculateSELECT SUM(pname) FROMProduct # calculates the maximum price in the list of goodsSELECT MAX(price)FROMProduct # Averages the prices of all the goodsSELECT AVG(price) FROM product
Copy the code2 the auto_increment
Can only be added to the primary key, and must be of a data type.
What it does is, when you insert data into the database, if you don’t specify a value for the primary key, it will assign the primary key starting at 0
create table student(
id int primary key auto_increment,
name varchar(20),
garde varchar
)
Copy the code3 Foreign key constraint REFERENCES
(1) Conclusion:
For data that exists in the master table, the slave table may or may not exist.
Data that does not exist in the master table cannot exist in the slave table.
(2) Grammar
# students tablecreate table student(
id int primary key auto_increment,
name varchar(20),
garde varchar) # Score sheet, reference foreign keycreate table score(
id int primary key auto_increment,
score double,
student_id int) # Add foreign keyalter table score add constraint fk_student_id foreign key(student_id) references student(id);
Copy the codeNote:
When adding a foreign key, the foreign key must be the primary key of another table. Otherwise, an error will be reported.
(3) Requirements
-
A foreign key is created on the slave table, and the master table exists first.
-
Multiple foreign key constraints can be created for a table
-
In general, the foreign key column of the secondary table must point to the primary key column of the primary table
-
The foreign key columns of the secondary table can have different names from the columns referenced in the primary table, but the data types must be the same
(4) Delete cascade
This means that if the main table drops a piece of data, then everything associated with that piece of data from the table will be dropped as well.
(5) Delete the syntax of the foreign key constraint
ALTER TABLEThe name of the tableDROP FOREIGNKEY Specifies the name of the foreign KEY constraint. Check the constraint nameSELECT * FROM information_schema.table_constraints WHERE table_name = 'Table name'; Select * from 'foreign key' where 'foreign key' = 'foreign key' and 'foreign key' = 'foreign key'ALTER TABLEThe name of the tableDROPINDEX INDEX name; # View index nameshow index fromTable name;Copy the code4 Multi-table relationships
(1) One-to-one (understanding)
Classification: In a one-to-many relationship, a row of data from the main table can correspond to multiple rows of data from the main table, whereas a row of data from the secondary table can correspond to only one row of data from the main table. This row for row, we can think of it as a one-to-one relationship.
A row of data in table A corresponds to A row of data in table B. Conversely, A row of data in table B also corresponds to A row of data in table A. In this case, we can treat table A as the master table and table B as the slave table, or treat table B as the master table and table A as the slave table.
Rule: Specify a field in the slave table to create a foreign key that points to the primary key of the primary table, and then add unique constraints to the foreign key fields of the slave table.
(2) one-to-many
Concept: A single row of data in the main table can be mapped from multiple rows of the table at the same time, and conversely, from multiple rows of the table to the same row of data in the main table.
Add foreign key:
A foreign key is created from a field specified in the slave table and points to the primary key of the primary table, which then adds unique constraints to the foreign key field of the slave table.
Scenarios: Classification tables and merchandise tables, class tables and student tables, user tables and order tables, etc
Rule: Set one side as the primary table and many side as the slave table. Specify a field in the slave table as a foreign key that points to the primary key of the primary table.
-- Create a classification table
CREATE TABLE category(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(50));Create a list of items
CREATE TABLE product(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(50),
price DOUBLE,
cid INT
)
Add a foreign key to the list of items
alter table product add foreign key(cid) references category(cid)
Copy the code(3) Many-to-many
Concept: Both tables are multiple. A row of table A can correspond to multiple rows of table B. Conversely, A row of table B can correspond to multiple rows of table A
Scenarios: order lists and merchandise lists, student lists and class schedules, and so on
Rule: Since both tables are multi-sided, you cannot create a foreign key in either table, so you need to create a new middle table and define two fields in the middle table that will serve as foreign keys to the primary keys of each table.
Create student table
CREATE TABLE student(
sid INT PRIMARY KEY AUTO_INCREMENT,
sname VARCHAR(50));-- Creating a class schedule
CREATE TABLE course(
cid INT PRIMARY KEY AUTO_INCREMENT,
cname VARCHAR(20));Create an intermediate table
CREATE TABLE s_c_table(
sno INT,
cno INT
);
Add a foreign key to the SNO field to point to the SID primary key of the student table
ALTER TABLE s_c_table ADD CONSTRAINT fkey01 FOREIGN KEY(sno) REFERENCES student(sid);
Add a foreign key to the Cno field to point to the CID primary key of the Course table
ALTER TABLE s_c_table ADD CONSTRAINT fkey03 FOREIGN KEY(cno) REFERENCES course(cid);
Copy the code5 Implementing associated query of multiple tables
Multi-table associated query uses a SQL statement to query the data of multiple associated tables.
(1) Cross query
The result is the Cartesian product of the two tables. So any combination of two tables, most of the time you don’t need them.
(1) grammar
selectA. column, A. column, B. column, B. columnfrom a,b ;
select a.*,b.* from a,b ;
- or
select * from a,b;
Copy the code(2) sample
select * from t_category,t_product;
Copy the code③ Cartesian product
Assuming that set A = {A, b}, set b = {0}, the two sets of cartesian product for {(A, 0), (A, 1), (A, 2), (b, 0), (b, 1), (b, 2)}. Can be extended to multiple collections.
(2) Internal join query
If we want to return a result where two tables have some kind of association, we declare this condition with WHERE, and then join the two tables in a “new table” (result).
Select * from ‘inner join’ where ‘inner join’ is used
select[Field, field, field]from a,b whereJoin condition (b the foreign key in the table=Primary key in table ACopy the codeSelect * from inner join where inner join is located.
select[Field, field, field]from a [inner] join b onConnection conditions [whereOther conditions]Copy the code③ Features of inner join query:
The data in the master table and the slave table can be queried only when the join conditions are met. The data in the master table and slave table cannot be queried unless the join conditions are met.
Table names in a development environment are often complex. You can give these tables an alias in the query statement, and then use the alias in where. Column name to declare a condition.
(3) External query
① Left external connection query
Ⅰ concept
The table on the left of the join is the main table, and all the data of the main table is displayed. The data of the table on the right of the join is queried according to the conditions. If the conditions are met, the data is displayed. Make sure all the data in the left table is displayed based on the inner join.
Ⅱ grammar
selectfieldfrom a left [outer] join b onConditions;Copy the codeⅢ sample
select * from t_category c left outer join t_product p on c.cid = p.cno
Copy the code② External right connection query
Ⅰ concept
The table on the right of join is used as the main table, and all data of the table on the right is displayed. The data of the table on the left of join is queried according to the conditions. If it meets the requirements, it is displayed; if it does not meet the requirements, it is displayed as NULL. Ensure that all the data in the right table is displayed based on the inner join.
Ⅱ grammar
selectfieldfrom a right outer join b onconditionsCopy the codeⅢ sample
select * t_category c right outer join t_product p on c.cid = p.cno
Copy the code6 Union Query Implements the external join query
grammar
The query1 unionThe query2 unionThe query3.Copy the codeThe sample
# Use the left outer AunionB right awayselect * from t_category c left outer join t_product p on c.cid = p.cno
union
select * from t_category c right outer join t_product p on c.cid = p.cno;
Copy the code7 the subquery
define
If a query statement is nested within another query statement, the query statement is called a subquery. It can be categorized into WHERE, FROM, or exists based on location.
Note: Subqueries must be enclosed in () brackets, regardless of where the query is located.