catalogue
3. Mysql simple query operation
4. Multi table relationship and table building principle
4. Mysql multi table Association query
4.1 mysql Association query classification
4.2 write sql statement to realize mysql multi table Association query
1. DML - modify operation
CREATE TABLE `stu` ( `sid` int(11) NOT NULL AUTO_INCREMENT, `sname` varchar(100) NOT NULL, `age` int(11) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, PRIMARY KEY (`sid`) )
1. Modify sql statement syntax
update Table name set Field name=value, Field name=Value where condition
2. Practice
#Modify Zhang Wuji's address is Songshan
UPDATE stu SET address='Songshan Mountain' WHERE sname='zhang wuji'
#Modify Zhou Zhiruo's age 50 and address Emei
UPDATE stu SET age=50,address='Mount Emei' WHERE sname='Zhou Zhiruo'
#Modify owner's age + 10
UPDATE stu SET age=age+10
2. DML delete
1. Delete sql statement syntax
delete from Table name where condition # Delete records with sid=2 DELETE FROM stu WHERE sid=2
2. delete from and truncate all records in the table
# Delete all records in the table DELETE FROM stu # Delete the whole table first, and then create a table with the same structure as before TRUNCATE stu
3. Mysql simple query operation
1. Syntax of mysql query statement
SELECT Field 1,Field 2 FROM Table name 1,Table name 2 WHERE condition GROUP BY field HAVING Screening conditions ORDER BY field ASC | DESC LIMIT
2. Query all records in the table
# Query all records in stu table SELECT sid,sname,age,address FROM stu SELECT * FROM stu # Query all sid and sname values in stu table SELECT sid,sname FROM stu
3. Alias
# Alias operation SELECT sid AS sid_id,sname AS sname_name FROM stu s
4. Floating symbol (backquote)
CREATE TABLE t1 ( id INT, `name` VARCHAR(100) )
5. Simple where condition
# The query name is sheson information SELECT * FROM stu s WHERE s.sname='Thankson ' # Query the information of students with sid greater than 2 and age greater than 30 SELECT * FROM stu WHERE sid>2 AND age>30
4. Multi table relationship and table building principle
1. What are the relationships between multiple tables
(1) One to many relationship
*Departments and employees
*A department has many employees, and an employee belongs to a department
*The Department is one, and there are many employees
(2) Many to many relationship
*Students and courses
*A student can choose multiple courses, and a course can be selected by multiple students
(3) One to one relationship
*Person and ID number
*A person has only one ID number, and one ID number corresponds to a person
2. One to many table creation
Create a field in the party with more than one as a foreign key and associate the primary key of the party with one
3. Many to many table creation
4. One to one table building
(1) Create table corresponding to primary key
5. Take one to many as an example to create a table and use foreign keys
### Create department table CREATE TABLE dept ( did INT PRIMARY KEY, dname VARCHAR(100) ) ### Create employee table CREATE TABLE emp ( eid INT PRIMARY KEY, ename VARCHAR(100), age INT, edid INT, FOREIGN KEY(edid) REFERENCES dept(did) )
6. Main functions of foreign keys:
(1) For data association
(2) Foreign keys ensure data integrity and consistency
*When adding an employee, add the Department id, which must be the Department table id
*When deleting a department, if there are associated employees under the Department, the department cannot be deleted
7. When actually creating a table, foreign keys are generally not declared and can be used as associations
4. Mysql multi table Association query
4.1 mysql Association query classification
1. Cartesian product (understanding)
2. Internal connection query
Get associated data of two tables
3. External connection - left external connection
All data in the left table and associated data in the right table
4. External connection - right external connection
All data in the right table and associated data in the left table
4.2 write sql statement to realize mysql multi table Association query
1. Cartesian product
#Cartesian product
SELECT * FROM dept,emp
2. Internal connection query
# Internal connection query # select field from table 1 # inner # join # Table 2 # on # Association conditions SELECT * FROM dept INNER JOIN emp ON dept.did=emp.edid SELECT * FROM dept,emp WHERE dept.did=emp.edid
3. External connection - left external connection
# External connection - left external connection # select field from table 1 left outer join table 2 # on # Association conditions SELECT * FROM dept LEFT OUTER JOIN emp ON dept.did=emp.edid
4. External connection - right external connection
# External connection - right external connection # select field from table 1 right outer join table 2 # on # Association conditions SELECT * FROM dept RIGHT OUTER JOIN emp ON dept.did=emp.edid
5. Table A unique data
# Unique to table A SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid WHERE emp.edid IS NULL
6. Table B unique data
# Table B unique data SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid WHERE dept.did IS NULL
7. AB all (similar to full connection effect)
# AB all SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid UNION SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid
8. A unique + B unique
# A unique + B unique SELECT * FROM dept LEFT JOIN emp ON dept.did=emp.edid WHERE emp.edid IS NULL UNION SELECT * FROM dept RIGHT JOIN emp ON dept.did=emp.edid WHERE dept.did IS NULL
5. Mysql query operation
1. De duplication operation DISTINCT
SELECT DISTINCT ename FROM emp
2. where conditional use
# Query age is over 20 years old SELECT * FROM emp WHERE age>20
3. Fuzzy query
# Query the information of all personnel surnamed Zhang # %Represents a wildcard to match any content SELECT * FROM emp WHERE ename LIKE 'Zhang%' # The last word in the query name is highlighted at the end SELECT * FROM emp WHERE ename LIKE '%bright' # Query name includes Zhang SELECT * FROM emp WHERE ename LIKE '%Zhang%' # The first letter in the query name is m, and M is followed by three letters SELECT * FROM emp WHERE ename LIKE 'm___'
4. Sort operation
Ascending and descending
# Query all records in emp table and sort them according to eid SELECT * FROM emp ORDER BY eid ASC SELECT * FROM emp ORDER BY eid DESC
5. Query range
# Query range # Query emp table age range 30-50 SELECT * FROM emp WHERE age>=30 AND age<=50 # BETWEEN start value AND end value SELECT * FROM emp WHERE age BETWEEN 30 AND 50 # The query age is 20 40 60 SELECT * FROM emp WHERE age IN(20,40,60)
6. mysql paging query
mysql implements paging and uses the keyword limit. The limit keyword can only be used in mysql database, not standard sql
7. limit usage
(1) limit is followed by two parameters
The first parameter is the starting position of query data, starting from 0
The second parameter is how many records are displayed per page
(2) Query data start position calculation formula
*(current page - 1) * number of records displayed per page
8. Write paging statements
# Paging statement SELECT * FROM emp LIMIT 0,3 SELECT * FROM emp LIMIT 3,3
9. mysql writes some functions for specific functions, and directly uses these functions to realize specific functions. These functions become aggregate (set) functions
(1) count(): Statistics
## How many records are there in the query table SELECT COUNT(*) AS num FROM emp ## Query the number of persons older than 40 SELECT COUNT(*) FROM emp WHERE age>40
(2) sum(): Sum
# sum(): Sum SELECT SUM(age) FROM emp
(3) avg(): calculate the average
# avg(): calculate the average SELECT AVG(age) FROM emp ## Put the average number of digits after the decimal point SELECT CAST(AVG(age) AS DECIMAL(10,2)) AS avgnum FROM emp
(4) Get max(): Max
(5) min(): get the minimum value
10. Grouping query
group by field
11. Write group sql
# How many employees are there in each department # Standard sql SELECT COUNT(*) AS num,edid AS deptid FROM emp GROUP BY edid