First, preparation

Import the sc. SQL file

1. Enter the MySQL command line mode

① From the Windows Start menu (bottom left corner of the desktop), locate the MySQL 8.0 Command Line Client and enter the password

② (Environment variables have been configured) Run WIN+r, enter CMD, and run the following command to log in

Mysql -uroot -p your passwordCopy the code

2. Create an SC database

create database SC;
Copy the code

3, import database file (SQL file)

① Access the SC database

use SC;
Copy the code

② Run the following command to import the database

Source File path; Example: the source D: sc. SQL;Copy the code

2. DQL query statements

1: basic query

SELECT FROM table name; Similar to Java: system.out.println (something to print); Select * from (select * from); select * from (select * from)Copy the code

2: indicates conditional query

Conditional query: Filter the data of the original table according to the conditions to query the desired dataCopy the code

Grammar:

The select expression to query field | | | constant value functionfromSelect * from 'where';Copy the code

Classification:

Salary >10000Conditional operators: > < >= <= =! = < > 2, logical expressions example: salary and salary > 10000 < 20000 logical operators: and (&) : if the two conditions at the same time, the result is true, otherwise is false or (| |) : Return true if either condition is true, false otherwise not(!) If not, then false; if not, then true The and... Select Sno,Sname from Student where Sage Between 19 and 21; This query includes student numbers and names of students aged 19 and 21Copy the code

3: fuzzy query

Wild card:1, * matches all2, % can match multiple characters3, _ Only one character can be matched. For example, select *from Student where Sname like'liu %';

Copy the code

4: Sort the query

Grammar: select to query the from table (where conditions) / / don't write the order by sorting the field expression | | | function is an alias of the asc (ascending) | desc (descending).Copy the code

5: Aggregate function

Sum Max Max min Min AVG Average count The sum and avG are used to handle any data type. Max, min, and count can be used with a combination of distinct data types. Field, *, constant value, usually 1 is recommended to use count(*)Copy the code

6: Group query

Grammar:

Select query field, grouping functionfromTable group by indicates the group fieldCopy the code

Features:

        1, can be grouped by a single field2, and the grouped function together with the query field is better after the grouped field3Group filtering Table position keyword Pre-group filtering: before the original table group by WHERE post-group filtering: After the result set group by having4, can be grouped by multiple fields separated by commas5, can support sorting6, having can support aliasesCopy the code

7: multiple table join query

I. Equivalent connection (using ‘=’)

Example: select Student. Sno, Student. Sname, Sage, Sdept, SC, Cno, GradefromStudent,SC where Student.Sno = SC.Sno; Consider: How can the above query statement be simplified?Copy the code

Two, self connection

Select first.Cno,second.Cpnofrom Course first, Course second
where first.Cpno=second.Cno;
Copy the code

Three, external connection (left connection, right connection)

Select Student.*,Cno,Grade from Student where Student.*,Cno,Gradefrom Student left outer join SC on(Student.Sno=SC.Sno);/ / port connection
//on(student.sno = sc.sno) using(Sno)
//outer can be omitted
Copy the code

8, nested query

Meaning:

A query statement is nested with another complete SELECT statement. The nested SELECT statement is called a subquery or an internal query and is called a primary query or an external queryCopy the code

Features:

Subqueries can be placed after FROM, SELECT, WHERE, or HAVING, but are usually placed to the right of a condition. Subqueries take precedence over the main query, which uses the results of the subquery. Subqueries are grouped into two categories based on the number of rows in the query. (1) single subquery The result set only one line General collocation single operator use: > < = < > > = < = illegal use of sub queries: a, the result of the subquery is a set of values, the result of the subquery b is empty (2) how muck queries There are more lines in the result set General collocation of line operators use: Any, all, IN, not in: Any of the subquery results is ok. The any and all can be replaced by other queriesCopy the code