Introduction to "Workshop" issue 21 SQL

Posted by Gomesh on Sat, 22 Jan 2022 10:03:41 +0100

Click the link to watch the explanation video of station B

https://www.bilibili.com/video/BV1XA411L766?share_medium=android&share_plat=android&share_source=WEIXIN&share_tag=s_i&timestamp=1603677657&unique_k=aUKJ9w

SQL

Structured Query Language: a Structured Query Language used to access and operate database systems. SQL can be queried or modified

reference resources:

https://www.runoob.com/sql/sql-tutorial.html
https://www.liaoxuefeng.com/wiki/1177760294764384
https://www.runoob.com/python/python-mysql.html

Relational data

relational model

relational model

The relational model is like a two-dimensional table, which is uniquely determined by row number and column number. The data is like an Excel table

ID	full name	Class ID	Gender	Age
1	a	201	M	9
2	b	202	F	8
3	c	202	M	8
4	d	201	F	9

ID	name	headmaster
201	Class 1, grade 2	xxx
202	Class 2, grade 2	yyy

Mainstream database

Commercial databases, such as Oracle, SQL Server, DB2, etc;
Open source databases, such as MySQL, PostgreSQL, etc;
Desktop database, represented by Microsoft Access, is suitable for desktop applications;
Embedded database, represented by Sqlite, is suitable for mobile applications and desktop applications

MySQL installation

Install MySQL

Download address( https://dev.mysql.com/downloads/ )

sudo apt-get install mysql-server

MySQL startup

mysql -u root -p

Enter the password and press enter without password.

Enter exit to exit, but the MySQL server is still running in the background.

relational model

Each row of the table becomes a record

Each column becomes a field.

"One to many", "many to one" and "one to one" relationships can be established between tables in relational row database.

For example:

Each row in the class list corresponds to a class, and a class corresponds to multiple students, so the relationship between the class table and the student table is one to many

In relational databases, relationships are maintained through primary keys and foreign keys

Primary key

For a relational table, an important constraint is that two records cannot be duplicate, that is, they are not identical. They can be uniquely distinguished by a field, which is called a primary key

If name is used as the primary key, the records can be determined by the names Xiao Ming and Xiao Hong, but there is no way to store records with the same name.

The key to the requirements of primary keys is that they should not be modified once inserted.

All fields involving information are generally not used as primary keys: mobile phone number, email, etc.

The primary key should preferably be a completely business independent field, which is generally named id.

composite keys

Relational databases actually allow records to be uniquely identified by multiple fields, that is, two or more fields are set as primary keys, which is called joint primary keys.

As long as not all primary key columns are duplicate

Foreign key

id	name	...
1	Xiao Ming	...
2	Xiao Hong	...

id	name	..
1	Class one	...
2	Class two	...

In the above two tables, we can determine the only record of a student and a class through the primary key, but how to link the two data?

Add a column of class in the first table_ ID corresponds to a record of the class

id	class_id	name	...
1	1	xxx	....
2	1	yyy	...
5	2	zzz	...

In this way, according to class_id directly locates which record students correspond to.

Foreign keys are implemented by definition

ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

The name of the foreign key constraint fk_class_id can be arbitrary. FOREIGN KEY (class_id) specifies class_ ID as the foreign key, REFERENCES classes (id) specifies that the foreign key will be associated with the ID column of the classes table (that is, the primary key of the classes table).

By defining foreign key constraints, relational databases can ensure that invalid data cannot be inserted.

If there is no record with id=99 in the classes table, the students table cannot insert classes_ Record with id=99

Many to many

In many to many situations, one teacher corresponds to multiple classes and one class corresponds to multiple teachers.

Many to many is actually realized through two one to many relationships, that is, two one to many relationships are associated through an intermediate table

teachers table:

id	name
1	Miss Zhang
2	Miss Wang
3	Miss Li
4	Miss Zhao

classes table:

id	name
1	Class one
2	Class two

Intermediate table teacher_class associates two one to many relationships:

id	teacher_id	class_id
1	1	1
2	1	2
3	2	1
4	2	2
5	3	1
6	4	2

one-on-one

id	student_id	mobile
1	1	135xxxx6300
2	2	138xxxx2209
3	5	139xxxx8086

Each student in the students table has his own contact information. If the contact information is stored in another table, it will form "one-to-one"

Indexes

The application scenario of index is to realize fast search when there are many records

An index is a data structure that preorders a column or multiple fragmented values in a relational database. Through the index, you can locate directly without scanning.

For example, for the students table:

id	class_id	name	gender	score
1	1	Xiao Ming	M	90
2	1	Xiao Hong	F	95
3	1	Xiaojun	M	88

If you want to frequently query based on the score column, you can create an index on the score column:

ALTER TABLE students
ADD INDEX idx_score (score);

Use ADD INDEX idx_score (score) creates a file named idx_score, using the index of the column score. The index name is arbitrary. If the index has multiple columns, you can write them in parentheses, for example:

ALTER TABLE students
ADD INDEX idx_name_score (name, score);

The efficiency of the index depends on the value of the index column, that is, if the values of the column are different from each other, the higher the efficiency of the index.

You can create multiple indexes on a table. The advantage of index is to improve the query efficiency. The disadvantage is that the index needs to be modified at the same time when inserting, updating and deleting records. Therefore, the more indexes, the slower the speed of inserting, updating and deleting records.

For a primary key, the relational database automatically creates a primary key index on it. The efficiency of using the primary key index is the highest, because the primary key will ensure absolute uniqueness.

unique index

Although some columns are not suitable as primary keys, they need to be unique: such as ID card and mailbox.

This is the only index that can be added to the column

ALTER TABLE students
ADD UNIQUE INDEX uni_name (name);

Through the UNIQUE keyword, we add a UNIQUE index

You can also add a unique constraint to a column without creating a unique index, because the index occupies disk space:

ALTER TABLE students
ADD CONSTRAINT uni_name UNIQUE (name);

In this case, the name column has no index, but it still has uniqueness guarantee.

Query data

Sample data preparation

SQL script

-- If test If the database does not exist, create it test Database:
CREATE DATABASE IF NOT EXISTS test;

-- Switch to test database
USE test;

-- delete classes Table and students Table (if any):
DROP TABLE IF EXISTS classes;
DROP TABLE IF EXISTS students;

-- establish classes Table:
CREATE TABLE classes (
    id BIGINT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- establish students Table:
CREATE TABLE students (
    id BIGINT NOT NULL AUTO_INCREMENT,
    class_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    gender VARCHAR(1) NOT NULL,
    score INT NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- insert classes record:
INSERT INTO classes(id, name) VALUES (1, 'Class one');
INSERT INTO classes(id, name) VALUES (2, 'Class two');
INSERT INTO classes(id, name) VALUES (3, 'Class three');
INSERT INTO classes(id, name) VALUES (4, 'Class four');

-- insert students record:
INSERT INTO students (id, class_id, name, gender, score) VALUES (1, 1, 'Xiao Ming', 'M', 90);
INSERT INTO students (id, class_id, name, gender, score) VALUES (2, 1, 'Xiao Hong', 'F', 95);
INSERT INTO students (id, class_id, name, gender, score) VALUES (3, 1, 'Xiaojun', 'M', 88);
INSERT INTO students (id, class_id, name, gender, score) VALUES (4, 1, 'millet', 'F', 73);
INSERT INTO students (id, class_id, name, gender, score) VALUES (5, 2, 'Xiaobai', 'F', 81);
INSERT INTO students (id, class_id, name, gender, score) VALUES (6, 2, 'Small soldier', 'M', 55);
INSERT INTO students (id, class_id, name, gender, score) VALUES (7, 2, 'Kobayashi', 'M', 85);
INSERT INTO students (id, class_id, name, gender, score) VALUES (8, 3, 'Xiaoxin', 'F', 91);
INSERT INTO students (id, class_id, name, gender, score) VALUES (9, 3, 'Xiao Wang', 'M', 89);
INSERT INTO students (id, class_id, name, gender, score) VALUES (10, 3, 'Xiao Li', 'F', 85);

-- OK:
SELECT 'ok' as 'result:';

Basic query

Select * from < table name >;

mysql> SELECT * FROM students;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    73 |
|  5 |        2 | Xiaobai   | F      |    81 |
|  6 |        2 | Small soldier   | M      |    55 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
+----+----------+--------+--------+-------+
10 rows in set (0.00 sec)

SELECT is a keyword indicating that a query is to be executed, * indicates all columns, and FROM indicates which table to query FROM

mysql> SELECT * FROM classes;
+----+--------+
| id | name   |
+----+--------+
|  1 | Class one   |
|  2 | Class two   |
|  3 | Class three   |
|  4 | Class four   |
+----+--------+
4 rows in set (0.00 sec)

SELECT does not have to be FROM

mysql> SELECT 100 + 200;
+-----------+
| 100 + 200 |
+-----------+
|       300 |
+-----------+
1 row in set (0.00 sec)

One purpose of SELECT without FROM is to judge whether the current database link is valid.

Condition query

That is, select subsets according to conditions.

Use WHERE.

Select * from < table name > where < expression >

mysql> SELECT * FROM students WHERE score >= 80;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  5 |        2 | Xiaobai   | F      |    81 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
+----+----------+--------+--------+-------+
8 rows in set (0.00 sec)

Conditions can be linked with AND,OR,NOT

mysql> SELECT * FROM students WHERE score >= 80 AND gender = 'M';
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  9 |        3 | Xiao Wang   | M      |    89 |
+----+----------+--------+--------+-------+
4 rows in set (0.02 sec)

mysql> SELECT * FROM students WHERE score >= 80 OR gender = 'M';
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  5 |        2 | Xiaobai   | F      |    81 |
|  6 |        2 | Small soldier   | M      |    55 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
+----+----------+--------+--------+-------+
9 rows in set (0.01 sec)

mysql> SELECT * FROM students WHERE NOT class_id = 2;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    73 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
+----+----------+--------+--------+-------+
7 rows in set (0.00 sec)

NOT class_id = 2 is equivalent to class_ id <> 2

The combination of three conditions requires ()

mysql> SELECT * FROM students WHERE (score < 80 OR score > 90) AND gender = 'M';
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  6 |        2 | Small soldier   | M      |    55 |
+----+----------+--------+--------+-------+
1 row in set (0.00 sec)

Common expressions

condition	Expression example 1	Expression example 2	explain
Use = judge equality	score = 80	name = 'abc'	The string needs to be enclosed in single quotes
Use > to determine greater than	score > 80	name > 'abc'	String comparison is based on ASCII code, and Chinese character comparison is based on database settings
Use > = to determine greater than or equal	score >= 80	name >= 'abc'
Use < to judge less than	score < 80	name <= 'abc'
Use < = to judge less than or equal	score <= 80	name <= 'abc'
Use < > to judge inequality	score <> 80	name <> 'abc'
Use LIKE to judge similarity	name LIKE 'ab%'	name LIKE '%bc%'	%Represents any character, for example, 'AB%' will match 'ab', 'abc', 'abcd'

Projection query

That is, you only want to return some columns, not all columns

mysql> SELECT class_id, score, name FROM students;
+----------+-------+--------+
| class_id | score | name   |
+----------+-------+--------+
|        1 |    90 | Xiao Ming   |
|        1 |    95 | Xiao Hong   |
|        1 |    88 | Xiaojun   |
|        1 |    73 | millet   |
|        2 |    81 | Xiaobai   |
|        2 |    55 | Small soldier   |
|        2 |    85 | Kobayashi   |
|        3 |    91 | Xiaoxin   |
|        3 |    89 | Xiao Wang   |
|        3 |    85 | Xiao Li   |
+----------+-------+--------+
10 rows in set (0.00 sec)

At the same time, each column can be given an alias, and the column name of the result can be different from that of the original table.

mysql> SELECT id, score point, name FROM students;
+----+-------+--------+
| id | point | name   |
+----+-------+--------+
|  1 |    90 | Xiao Ming   |
|  2 |    95 | Xiao Hong   |
|  3 |    88 | Xiaojun   |
|  4 |    73 | millet   |
|  5 |    81 | Xiaobai   |
|  6 |    55 | Small soldier   |
|  7 |    85 | Kobayashi   |
|  8 |    91 | Xiaoxin   |
|  9 |    89 | Xiao Wang   |
| 10 |    85 | Xiao Li   |
+----+-------+--------+
10 rows in set (0.00 sec)

WHERE is also supported

mysql> SELECT id, score points, name FROM students WHERE gender = "M";
+----+--------+--------+
| id | points | name   |
+----+--------+--------+
|  1 |     90 | Xiao Ming   |
|  3 |     88 | Xiaojun   |
|  6 |     55 | Small soldier   |
|  7 |     85 | Kobayashi   |
|  9 |     89 | Xiao Wang   |
+----+--------+--------+
5 rows in set (0.02 sec)

sort

When querying, it is sorted by primary key by default. If there is a need to sort by other criteria. You can use ORDER BY

The default is from low to high

mysql> SELECT id, name, score FROM students ORDER BY score;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  6 | Small soldier   |    55 |
|  4 | millet   |    73 |
|  5 | Xiaobai   |    81 |
|  7 | Kobayashi   |    85 |
| 10 | Xiao Li   |    85 |
|  3 | Xiaojun   |    88 |
|  9 | Xiao Wang   |    89 |
|  1 | Xiao Ming   |    90 |
|  8 | Xiaoxin   |    91 |
|  2 | Xiao Hong   |    95 |
+----+--------+-------+
10 rows in set (0.02 sec)

Add DESC from high to bottom

mysql> SELECT id, name, score FROM students ORDER BY score DESC;
+----+--------+-------+
| id | name   | score |
+----+--------+-------+
|  2 | Xiao Hong   |    95 |
|  8 | Xiaoxin   |    91 |
|  1 | Xiao Ming   |    90 |
|  9 | Xiao Wang   |    89 |
|  3 | Xiaojun   |    88 |
|  7 | Kobayashi   |    85 |
| 10 | Xiao Li   |    85 |
|  5 | Xiaobai   |    81 |
|  4 | millet   |    73 |
|  6 | Small soldier   |    55 |
+----+--------+-------+
10 rows in set (0.00 sec)

Sort by multiple columns

mysql> SELECT id, name,gender, score FROM students ORDER BY score DESC, gender;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | Xiao Hong   | F      |    95 |
|  8 | Xiaoxin   | F      |    91 |
|  1 | Xiao Ming   | M      |    90 |
|  9 | Xiao Wang   | M      |    89 |
|  3 | Xiaojun   | M      |    88 |
| 10 | Xiao Li   | F      |    85 |
|  7 | Kobayashi   | M      |    85 |
|  5 | Xiaobai   | F      |    81 |
|  4 | millet   | F      |    73 |
|  6 | Small soldier   | M      |    55 |
+----+--------+--------+-------+
10 rows in set (0.00 sec)

mysql> SELECT id, name, gender, score 
    -> FROM students
    -> WHERE  class_id = 1
    -> ORDER BY score DESC;
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | Xiao Hong   | F      |    95 |
|  1 | Xiao Ming   | M      |    90 |
|  3 | Xiaojun   | M      |    88 |
|  4 | millet   | F      |    73 |
+----+--------+--------+-------+
4 rows in set (0.02 sec)

Paging query

That is, subset the query results

Use limit < m > offset < n >

mysql> SELECT id, name, gender, score  FROM students ORDER BY score DESC LIMIT 3 OFFSET 0; 
+----+--------+--------+-------+
| id | name   | gender | score |
+----+--------+--------+-------+
|  2 | Xiao Hong   | F      |    95 |
|  8 | Xiaoxin   | F      |    91 |
|  1 | Xiao Ming   | M      |    90 |
+----+--------+--------+-------+
3 rows in set (0.00 sec)

LIMIT 3 OFFSET 0 means to start from record 0, and take up to 3 records each time

If OFFSET exceeds the maximum number, no error will be reported, but an empty result set will be returned.

mysql> SELECT id, name, gender, score  FROM students ORDER BY score DESC LIMIT 3 OFFSET 10;


Empty set (0.00 sec)

OFFSET is optional. If only LIMIT 15 is written, it is equivalent to LIMIT 15 OFFSET 0.

In MySQL, LIMIT 15 OFFSET 30 can also be abbreviated as LIMIT 30, 15.

When using LIMIT OFFSET paging, the query efficiency will become lower and lower as the N becomes larger and larger.

Aggregate query

For total statistics, sql provides special functions for average time calculation

COUNT() is used to query the number of records

mysql> SELECT COUNT(*) FROM students;
+----------+
| COUNT(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

Aggregate queries can also use the WHERE condition

mysql> SELECT COUNT(*) boys FROM students WHERE gender = 'M';
+------+
| boys |
+------+
|    5 |
+------+
1 row in set (0.00 sec)

Other aggregate functions

function	explain
SUM	To calculate the total value of a column, the column must be of numeric type
AVG	Calculate the average value of a column, which must be of numeric type
MAX	Calculate the maximum value of a column
MIN	Calculate the minimum value of a column

mysql> SELECT SUM(score) num 
    -> FROM students 
    -> WHERE gender = 'M';
+------+
| num  |
+------+
|  407 |
+------+
1 row in set (0.08 sec)

Note that the MAX() and MIN() functions are not limited to numeric types. If it is a character type, MAX() and MIN() will return the last and first sorted characters.

grouping

I want to count the number of students in all classes at once

Using GROUP BY

mysql> SELECT COUNT(*) num FROM students GROUP BY class_id;
+-----+
| num |
+-----+
|   4 |
|   3 |
|   3 |
+-----+
3 rows in set (0.00 sec)

Add specific class ID

mysql> SELECT class_id, COUNT(*) num FROM students GROUP BY class_id;
+----------+-----+
| class_id | num |
+----------+-----+
|        1 |   4 |
|        2 |   3 |
|        3 |   3 |
+----------+-----+
3 rows in set (0.00 sec)

multi-table query

SELECT can query data from multiple tables at the same time

mysql> SELECT * FROM students, classes;
+----+----------+--------+--------+-------+----+--------+
| id | class_id | name   | gender | score | id | name   |
+----+----------+--------+--------+-------+----+--------+
|  1 |        1 | Xiao Ming   | M      |    90 |  1 | Class one   |
|  1 |        1 | Xiao Ming   | M      |    90 |  2 | Class two   |
|  1 |        1 | Xiao Ming   | M      |    90 |  3 | Class three   |
|  1 |        1 | Xiao Ming   | M      |    90 |  4 | Class four   |
|  2 |        1 | Xiao Hong   | F      |    95 |  1 | Class one   |
|  2 |        1 | Xiao Hong   | F      |    95 |  2 | Class two   |
|  2 |        1 | Xiao Hong   | F      |    95 |  3 | Class three   |
|  2 |        1 | Xiao Hong   | F      |    95 |  4 | Class four   |
|  3 |        1 | Xiaojun   | M      |    88 |  1 | Class one   |
|  3 |        1 | Xiaojun   | M      |    88 |  2 | Class two   |
|  3 |        1 | Xiaojun   | M      |    88 |  3 | Class three   |
|  3 |        1 | Xiaojun   | M      |    88 |  4 | Class four   |
|  4 |        1 | millet   | F      |    73 |  1 | Class one   |
|  4 |        1 | millet   | F      |    73 |  2 | Class two   |
|  4 |        1 | millet   | F      |    73 |  3 | Class three   |
|  4 |        1 | millet   | F      |    73 |  4 | Class four   |
|  5 |        2 | Xiaobai   | F      |    81 |  1 | Class one   |
|  5 |        2 | Xiaobai   | F      |    81 |  2 | Class two   |
|  5 |        2 | Xiaobai   | F      |    81 |  3 | Class three   |
|  5 |        2 | Xiaobai   | F      |    81 |  4 | Class four   |
|  6 |        2 | Small soldier   | M      |    55 |  1 | Class one   |
|  6 |        2 | Small soldier   | M      |    55 |  2 | Class two   |
|  6 |        2 | Small soldier   | M      |    55 |  3 | Class three   |
|  6 |        2 | Small soldier   | M      |    55 |  4 | Class four   |
|  7 |        2 | Kobayashi   | M      |    85 |  1 | Class one   |
|  7 |        2 | Kobayashi   | M      |    85 |  2 | Class two   |
|  7 |        2 | Kobayashi   | M      |    85 |  3 | Class three   |
|  7 |        2 | Kobayashi   | M      |    85 |  4 | Class four   |
|  8 |        3 | Xiaoxin   | F      |    91 |  1 | Class one   |
|  8 |        3 | Xiaoxin   | F      |    91 |  2 | Class two   |
|  8 |        3 | Xiaoxin   | F      |    91 |  3 | Class three   |
|  8 |        3 | Xiaoxin   | F      |    91 |  4 | Class four   |
|  9 |        3 | Xiao Wang   | M      |    89 |  1 | Class one   |
|  9 |        3 | Xiao Wang   | M      |    89 |  2 | Class two   |
|  9 |        3 | Xiao Wang   | M      |    89 |  3 | Class three   |
|  9 |        3 | Xiao Wang   | M      |    89 |  4 | Class four   |
| 10 |        3 | Xiao Li   | F      |    85 |  1 | Class one   |
| 10 |        3 | Xiao Li   | F      |    85 |  2 | Class two   |
| 10 |        3 | Xiao Li   | F      |    85 |  3 | Class three   |
| 10 |        3 | Xiao Li   | F      |    85 |  4 | Class four   |
+----+----------+--------+--------+-------+----+--------+
40 rows in set (0.12 sec)

The "product" of the two tables is returned. The number of columns in the result set is the sum of the number of columns in the students table and the classes table, and the number of rows is the product of the number of rows in the students table and the classes table.

Aliases can be set for duplicate names

SELECT
    students.id sid,
    students.name,
    students.gender,
    students.score,
    classes.id cid,
    classes.name cname
FROM students, classes;

join query

Connect multiple tables, determine a main table as the result set, and selectively connect other tables.

For example:

Select student information in students

mysql> SELECT * FROM students;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    73 |
|  5 |        2 | Xiaobai   | F      |    81 |
|  6 |        2 | Small soldier   | M      |    55 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
+----+----------+--------+--------+-------+
10 rows in set (0.00 sec)

Although there are classes_ ID, but we want to add the specific name of the class

You need to use the connection query according to the class in the students table_ Id find the corresponding name column in the classes.

mysql> SELECT s.id, s.name, s.class_id, s.gender,s.score, c.name class_name
    -> FROM students s
    -> INNER JOIN classes c
    -> ON s.class_id = c.id;
+----+--------+----------+--------+-------+------------+
| id | name   | class_id | gender | score | class_name |
+----+--------+----------+--------+-------+------------+
|  1 | Xiao Ming   |        1 | M      |    90 | Class one       |
|  2 | Xiao Hong   |        1 | F      |    95 | Class one       |
|  3 | Xiaojun   |        1 | M      |    88 | Class one       |
|  4 | millet   |        1 | F      |    73 | Class one       |
|  5 | Xiaobai   |        2 | F      |    81 | Class two       |
|  6 | Small soldier   |        2 | M      |    55 | Class two       |
|  7 | Kobayashi   |        2 | M      |    85 | Class two       |
|  8 | Xiaoxin   |        3 | F      |    91 | Class three       |
|  9 | Xiao Wang   |        3 | M      |    89 | Class three       |
| 10 | Xiao Li   |        3 | F      |    85 | Class three       |
+----+--------+----------+--------+-------+------------+
10 rows in set (0.03 sec)

Note that the INNER JOIN query is written as follows:

First determine the main table, and still use the syntax of from < Table 1 >;
Then determine the table to be connected, and use the syntax of inner join < Table 2 >;
Then determine the connection condition, using on < condition... >, The condition here is s.class_id = c.id, indicating the class of the students table_ Rows with the same ID column as the ID column of the classes table need to be connected;
Optional: add sub clauses such as WHERE clause and ORDER BY.

If there is an INNER JOIN, there is an OUTER JOIN

mysql> SELECT s.id, s.name, s.class_id, s.gender, s.score, c.name class_name
    -> FROM students s
    ->RIGHT OUTER JOIN classes c
    -> ON s.class_id = c.id;
+------+--------+----------+--------+-------+------------+
| id   | name   | class_id | gender | score | class_name |
+------+--------+----------+--------+-------+------------+
|    1 | Xiao Ming   |        1 | M      |    90 | Class one       |
|    2 | Xiao Hong   |        1 | F      |    95 | Class one       |
|    3 | Xiaojun   |        1 | M      |    88 | Class one       |
|    4 | millet   |        1 | F      |    73 | Class one       |
|    5 | Xiaobai   |        2 | F      |    81 | Class two       |
|    6 | Small soldier   |        2 | M      |    55 | Class two       |
|    7 | Kobayashi   |        2 | M      |    85 | Class two       |
|    8 | Xiaoxin   |        3 | F      |    91 | Class three       |
|    9 | Xiao Wang   |        3 | M      |    89 | Class three       |
|   10 | Xiao Li   |        3 | F      |    85 | Class three       |
| NULL | NULL   |     NULL | NULL   |  NULL | Class four       |
+------+--------+----------+--------+-------+------------+
11 rows in set (0.00 sec)

By executing the above RIGHT OUTER JOIN, you can see that the RIGHT OUTER JOIN has one more row than the INNER JOIN, and the extra row is "class 4". However, the columns related to students such as name, gender and score are NULL.

The reason is that there are no four classes in the students table

RIGHT OUTER JOIN, LEFT OUTER JOIN and FULL OUTER JOIN. Their differences are:

INNER JOIN only returns row data that exists in two tables at the same time, because of the class of the students table_ The ID contains 1, 2 and 3. The ID of the classes table contains 1, 2, 3 and 4. Therefore, the INNER JOIN is based on the condition s.class_id = c.id the returned result set contains only 1, 2, 3.

RIGHT OUTER JOIN returns the rows that exist in the right table. If a row exists only in the right table, the result set fills the remaining fields with NULL.

Modify data

INSERT

Basic grammar

Insert into < table name > (field) VALUES;

mysql> INSERT INTO students(class_id, name, gender, score) VALUES (2, 'XX', 'M', 80);
Query OK, 1 row affected (0.22 sec)

mysql> SELECT * FROM students;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    73 |
|  5 |        2 | Xiaobai   | F      |    81 |
|  6 |        2 | Small soldier   | M      |    55 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
| 11 |        2 | XX     | M      |    80 |
+----+----------+--------+--------+-------+
11 rows in set (0.00 sec)

Note that the value of the id field is not listed because the id field is a self incrementing primary key. In addition, if there are fields with default values, they can not be listed.

The order of fields is not necessarily the same as that in the database table, but the order of values must be the same as that of fields.

Add multiple records at a time

mysql> INSERT INTO students(class_id, name, gender, score)
    -> VALUES
    -> (1, 'yy', 'M', 87),
    -> (1, 'zz', 'F', 100);
Query OK, 2 rows affected (0.11 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM students;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | Xiao Ming   | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    73 |
|  5 |        2 | Xiaobai   | F      |    81 |
|  6 |        2 | Small soldier   | M      |    55 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
| 11 |        2 | XX     | M      |    80 |
| 12 |        1 | yy     | M      |    87 |
| 13 |        1 | zz     | F      |   100 |
+----+----------+--------+--------+-------+
13 rows in set (0.00 sec)

UPADTE

Update data

Basic grammar

UPDATE <Table name> SET Field 1=Value 1, Field 2=Value 2, ... WHERE ...;

Change the name of the record with id = 1 to cc

mysql> UPDATE students
    -> SET name = 'cc'
    -> WHERE id = 1;
Query OK, 1 row affected (0.18 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM students;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | cc     | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    73 |
|  5 |        2 | Xiaobai   | F      |    81 |
|  6 |        2 | Small soldier   | M      |    55 |
|  7 |        2 | Kobayashi   | M      |    85 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
| 11 |        2 | XX     | M      |    80 |
| 12 |        1 | yy     | M      |    87 |
| 13 |        1 | zz     | F      |   100 |
+----+----------+--------+--------+-------+
13 rows in set (0.00 sec)

Update multiple records

mysql> UPDATE students
    -> SET name = 'qq', score = 77 
    -> WHERE id >= 5 AND id <= 7
    -> ;
Query OK, 3 rows affected (0.16 sec)
Rows matched: 3  Changed: 3  Warnings: 0

mysql> SELECT * FROM students
    -> ;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | cc     | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    73 |
|  5 |        2 | qq     | F      |    77 |
|  6 |        2 | qq     | M      |    77 |
|  7 |        2 | qq     | M      |    77 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
| 11 |        2 | XX     | M      |    80 |
| 12 |        1 | yy     | M      |    87 |
| 13 |        1 | zz     | F      |   100 |
+----+----------+--------+--------+-------+
13 rows in set (0.00 sec)

mysql> UPDATE students
    -> SET score = score + 10
    -> WHERE score < 80;
Query OK, 4 rows affected (0.11 sec)
Rows matched: 4  Changed: 4  Warnings: 0

mysql> SELECT * FROM students
    -> ;
+----+----------+--------+--------+-------+
| id | class_id | name   | gender | score |
+----+----------+--------+--------+-------+
|  1 |        1 | cc     | M      |    90 |
|  2 |        1 | Xiao Hong   | F      |    95 |
|  3 |        1 | Xiaojun   | M      |    88 |
|  4 |        1 | millet   | F      |    83 |
|  5 |        2 | qq     | F      |    87 |
|  6 |        2 | qq     | M      |    87 |
|  7 |        2 | qq     | M      |    87 |
|  8 |        3 | Xiaoxin   | F      |    91 |
|  9 |        3 | Xiao Wang   | M      |    89 |
| 10 |        3 | Xiao Li   | F      |    85 |
| 11 |        2 | XX     | M      |    80 |
| 12 |        1 | yy     | M      |    87 |
| 13 |        1 | zz     | F      |   100 |
+----+----------+--------+--------+-------+
13 rows in set (0.00 sec)

The UPDATE statement can have no WHERE condition, for example:

UPDATE students SET score=60;

At this time, all records of the whole table will be updated. Therefore, when executing the UPDATE statement, it is best to use the SELECT statement to test whether the WHERE condition filters out the desired recordset, and then UPDATE it with UPDATE.

DELETE

Basic grammar

DELETE FROM <Table name> WHERE ...;

Delete the record with id = 1 in the students table

mysql> DELETE FROM students WHERE id = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT *  FROM students;
+----+----------+------+--------+-------+
| id | class_id | name | gender | score |
+----+----------+------+--------+-------+
|  2 |        1 | Xiao Hong | F      |    95 |
|  3 |        1 | Xiaojun | M      |    88 |
|  4 |        1 | millet | F      |    73 |
|  5 |        2 | Xiaobai | F      |    81 |
|  6 |        2 | Small soldier | M      |    55 |
|  7 |        2 | Kobayashi | M      |    85 |
|  8 |        3 | Xiaoxin | F      |    91 |
|  9 |        3 | Xiao Wang | M      |    89 |
| 10 |        3 | Xiao Li | F      |    85 |
+----+----------+------+--------+-------+
9 rows in set (0.00 sec)

The usage of WHERE is similar to the previous one. You can delete multiple records at one time.

If the WHERE condition does not match any record, no error will be reported and no record will be deleted. However, if there is no WHERE, the entire data will be deleted.

Therefore, we usually use the SELECT test first to see whether the desired data is successfully selected and deleted

Operation database

On a running MySQL server, you can actually create multiple databases.

List all databases

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

Among them, information_schema,mysql,performance_schema and sys are system libraries.

create new database

mysql> CREATE DATABASE test;
Query OK, 1 row affected (0.01 sec)

Delete database

mysql> DROP DATABASE test;
Query OK, 0 rows affected (0.01 sec)

When operating on a database, first switch it to the current database:

mysql> USE test;
Database changed

surface

Lists all tables in the current database

mysql> SHOW TABLES;
+----------------+
| Tables_in_test |
+----------------+
| classes        |
| students       |
+----------------+
2 rows in set (0.00 sec)

View the structure of a table

mysql> DESC students;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | bigint       | NO   | PRI | NULL    | auto_increment |
| class_id | bigint       | NO   |     | NULL    |                |
| name     | varchar(100) | NO   |     | NULL    |                |
| gender   | varchar(1)   | NO   |     | NULL    |                |
| score    | int          | NO   |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

You can also use the following command to view the SQL statement that created the table:

mysql> SHOW CREATE TABLE students;
+----------+-------------------------------------------------------+
| students | CREATE TABLE `students` (                             |
|          |   `id` bigint(20) NOT NULL AUTO_INCREMENT,            |
|          |   `class_id` bigint(20) NOT NULL,                     |
|          |   `name` varchar(100) NOT NULL,                       |
|          |   `gender` varchar(1) NOT NULL,                       |
|          |   `score` int(11) NOT NULL,                           |
|          |   PRIMARY KEY (`id`)                                  |
|          | ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 |
+----------+-------------------------------------------------------+
1 row in set (0.00 sec)

CREATE TABLE statement is used to create a table, and DROP TABLE statement is used to delete a table:

mysql> DROP TABLE students;
Query OK, 0 rows affected (0.01 sec)

Add a new column

ALTER TABLE students ADD COLUMN birth VARCHAR(10) NOT NULL;

Modify column

ALTER TABLE students CHANGE COLUMN birth birthday VARCHAR(20) NOT NULL;

Delete column

ALTER TABLE students DROP COLUMN birthday;

Python operation MySQL database

import  

conn = pymysql.connect(user = 'root', password = 'wx19960323', database = 'test')


cursor = conn.cursor()

sql = "SELECT * FROM students;"
cursor.execute(sql)

results = cursor.fetchall()


for row in results:
    print(row)

(1, 1, 'Xiao Ming', 'M', 90)
(2, 1, 'Xiao Hong', 'F', 95)
(3, 1, 'Xiaojun', 'M', 88)
(4, 1, 'millet', 'F', 73)
(5, 2, 'Xiaobai', 'F', 81)
(6, 2, 'Small soldier', 'M', 55)
(7, 2, 'Kobayashi', 'M', 85)
(8, 3, 'Xiaoxin', 'F', 91)
(9, 3, 'Xiao Wang', 'M', 89)
(10, 3, 'Xiao Li', 'F', 85)

Programmer Think

Introduction to "Workshop" issue 21 SQL

SQL

Relational data

Mainstream database

MySQL installation

MySQL startup

relational model

Primary key

composite keys

Foreign key

Many to many

one-on-one

Indexes

unique index

Query data

Sample data preparation

Basic query

Condition query

Projection query

sort

Paging query

Aggregate query

grouping

multi-table query

join query

Modify data

INSERT

Basic grammar

UPADTE

Basic grammar

DELETE

Basic grammar

Operation database

List all databases

create new database

Delete database

surface

Python operation MySQL database

Hot Topics