[database] basic writing specifications of sql statements, basic writing methods and precautions of database (based on mysql)

prelude

Simple example: select * from courses where name = "Li Ming";
The above sql is used to query all the information of the person named Li Ming in the courses table (information in the case of no associated table)

Note that all sql statements are semicolon; end

Note the following

Case problem of sql statement

This document I queried: original link: http://c.biancheng.net/view/7178.html
A simple summary is:

  • Our commonly used sql keywords, function names, and table column names are not case sensitive

As for other view names, database names and table names, they should be divided according to the system used. For details, click the link above

The following statements are equivalent:

SELECT name FROM student ;
SELECT NAME FROM student ;
SELECT nAmE FROM student ;
select name from student ;

Opening sql Basics

Query statement select

Questions about backquotes in select query statements:

Print character

select "Hello SQL!";
The string to be printed is written directly in the quotation marks of the sql statement

Multi column query:

select name,student from courses
Query the name and student columns from the courses table

Filter criteria where

where name = "Li Ming"
The where operator has equal = greater than > less than < not equal to= Or < >
Note that there is no = = symbol

Distinct keyword anti duplication

This distict is easier to understand by using examples
For example, query all nationalities in the teacher table shown below

If it is directly written as select country from teacher
The query result will be displayed as the last column of the table:

If the sql statement of the query is changed to: select DISTINCT country from teacher
Then it will automatically de duplicate, and the result is

This de duplication query is more effective in determining where and who needs to consider duplication

Insert statement insert into

The function is to add new data to the table

  • The first insertion method: (the inserted data is the data of one row of the table, and the data of value n is the order of the corresponding columns of the table)
    INSERT INTO `table_name` VALUES (value1, value2, value3,...);
    For example: existing database tables:

    How to insert a new piece of data:
INSERT INTO courses
VALUES (13, "Python", 400, "2021-05-23", 3);

It should be noted that the value n of the corresponding integer type in the designed table can be written as an integer directly, and other character types such as String type should be written in the form of "" or ''

  • The second insertion method: (insert the data of the specified column name)
    INSERT INTO `table_name` (`column1`, `column2`, `column3`,...) VALUES (value1, value2, value3,...);
    The name of the corresponding column corresponds to the data to be inserted by value
    For example, insert a piece of data into the teachers table
INSERT INTO teachers (name,email,age,country) 
 values ("XiaoFu","XiaoFu@lintcode.com",20,"CN");

Effect of insertion (the first column is self incrementing column, so you don't need to insert data, and the database adds the sequence itself):

Note: regardless of the insertion method, it cannot insert a column that does not exist

UPDATE statement update

Syntax of insert statement:

UPDATE `table_name`
SET `column1`=value1,`column2`=value2,...
WHERE `some_column`=some_value;

where is generally used as a filter condition, which column of data can be determined according to the filter condition
For example: modify the course schedule courses. The number of people who select artistic intelligence is 500 (if there is no course with this name in the table, the table will not be modified)

update courses 
set student_count = 500
where name = "Artificial Intelligence"; 

Delete statement

Delete a row of data in standard format (filter through where):

DELETE FROM `table_name`
WHERE `some_column` = `some_value`;

For example, delete all information before 2020 in the courses table

delete from courses
where created_at < "2020-1-1";

If the where determination condition is not added, the data in this table will be cleared after directly executing delete from courses

Note that drop table is used to delete the table definition and the entire table_ name

For deleting tables and deleting data in tables, refer to this website or Baidu (about the relevant operations of drop, truncate and delete on database tables): https://www.cnblogs.com/1312mn/p/4422396.html

Operator

Comparison operator

< > = < > or! = > =<=
Greater than, less than, equal to, not equal to (< > or! =), greater than or equal to, less than or equal to
WHERE A operator B
For example: where age > 20 or where name = "Xiao Zhang"
Remember to enclose b in quotation marks when b is a string

For example, query all names whose nationality is China in the teachers table

select name from teachers
where country = "CN"

Logical operator and or not

  • and
    WHERE condition1 AND condition2;
    It is used to connect multiple conditions. Condition is that each individual condition requires multiple conditions to be met at the same time
    For example, query the course information in the courses table where the teacher id is 4 and the student's class is greater than 500:
SELECT * FROM courses 
WHERE teacher_id = 4 and student_count > 500;
  • or
    Connecting multiple conditions requires one of these conditions to be met
    For example, query the course information named 'Web' or 'Big Data' in the courses table
SELECT * FROM courses
WHERE name = 'Web' or name = 'Big Data';
  • not
    Filter unqualified data
    For example: query courses with teacher id not 3 and number greater than 800
SELECT *
FROM courses
WHERE not teacher_id = 3 and student_count > 800

Special symbols (in, not in, between... and..., is NULL, LIKE)

  • in
    Usage:
SELECT *
FROM `table_name`
WHERE `column_name` IN `value`;

It can be used to query multiple conditions. value can be a set or a set obtained by nesting an sql query statement
For example, query all the course information in the course schedule courses whose opening date is 2021-01-01 or 2021-01-03

SELECT * FROM courses
WHERE created_at in ('2021-01-01','2021-01-03');
  • not in exclusion

Syntax:

SELECT *
FROM `table_name`
WHERE `column_name` NOT IN value;

For example: query all the course names in the course table courses whose teacher ID is not 1 or 3

SELECT name FROM courses
WHERE teacher_id not in (1,3);
  • Between... and... Query the data range between two values

Syntax:

SELECT *
FROM `table_name`
WHERE `column_name` BETWEEN `value` AND `value`;

For example, query all course information with a number of students between 50 and 55 in the course table courses

SELECT * from courses
where student_count BETWEEN 50 and 55;
  • IS NULL query null data
    NULL values represent missing unknown data. By default, the columns of the table can hold NULL values.

For example, query all teacher information in the teachers table, whose nationality is' CN 'or' JP 'and whose email information is not empty

SELECT * FROM teachers
WHERE country in ('CN','JP') and email is not  NULL;
  • LIKE fuzzy query

Syntax:

SELECT *
FROM `table_name`
WHERE `column_name` LIKE  `value`;

About the usage specification in value:

For example, query the names and mailboxes of all teachers using qq mailbox in the teachers table

SELECT name,email FROM teachers
WHERE email like '%@qq.com'

The% above represents that there are 0 or more characters in front and end with @ qq.com, which is a bit similar to the regular expression in python

Sort order by and limit output

  • Order by sorts the data in ascending order by default
    ASC: sort in ascending order. ORDER BY sorts records in ascending ORDER BY default. Therefore, the ascending keyword ASC can be omitted.
    DESC: sort in descending order. If you need to sort records in descending order, you can use the DESC keyword.

Syntax:

SELECT `column_name`, `column_name`
FROM `table_name`
ORDER BY `column_name`, `column_name` ASC|DESC;

For example, query the Chinese teachers in the teachers table and sort them in descending order by age

SELECT * FROM teachers 
WHERE country = 'CN'
order by age DESC
  • limit constrains the number of rows in the output result set
    offset: is the initial label of the return set. The starting point is 0, not 1
    count: Specifies the returned quantity

Syntax:

SELECT `column_name`, `column_name`
FROM `table_name`
LIMIT `offset` , `count`;

For example, query the information of the oldest Chinese teacher from the teachers table

SELECT * FROM teachers
WHERE country = 'CN'
order by age DESC
limit 1;

function

Arithmetic function (1) (avg, max, min, sum)

  • AVG() function
    The average function is used to find the average value of numeric columns. It can be used to return the average value of all columns or the average value of specific columns and rows. When the data in the column is empty, the result will return NULL

Syntax:

SELECT AVG(`student_count`) AS `average_student_count`
FROM `courses`;

After as is a name for the new column of the calculation result
For example, query the average value of the age of teachers whose email ends at '@qq.com' in the teachers table, and the final returned result column name is displayed as' average_teacher_age '.

SELECT avg(age) as average_teacher_age FROM teachers
WHERE email like '%@qq.com';
  • MAX() function
    Used to return the maximum value in the specified column. When the data in the column is empty, the result will return NULL

Syntax:

SELECT MAX(`column_name`) 
FROM `table_name`;

For example, from the teachers table, query the information of the oldest Chinese teacher and return the age of the teacher.

SELECT MAX(age) as max_age from teachers 
WHERE country = 'CN'
  • MIN() function
    It is used to return the minimum value in the specified column, which is the same as MAX

  • SUM() function
    Used to count the total number of numeric columns and return their values

Syntax:

SELECT SUM(`column_name`) 
FROM `table_name`;

For example: Statistics of teachers in courses_ The total number of students taught by the teacher with ID 3, and use select_student_sum as the result set column name.

SELECT SUM(student_count) as select_student_sum FROM courses
WHERE teacher_id = 3

Arithmetic function (2) (ROUND, NULL, COUNT)

  • ROUND() function
    Used to round (round) a numeric field to the specified number of decimal places

Syntax:

SELECT ROUND(`column_name`, `decimals`) 
FROM `table_name`;

For example, query the average age of teachers over 20 years old (excluding 20 years old) in the teachers table, and the returned field is avg_teacher_age, the result retains the rounded integer

SELECT ROUND(AVG(age)) as avg_teacher_age FROM teachers
where age > 20;
  • The description of NULL() function is not very detailed. Please refer to: Jump
    • ISNULL() function is used to determine whether the field is NULL. It has only one parameter column_name is the column name, according to column_ Whether the field in the name column is NULL returns 0 or 1.
      Syntax:
SELECT ISNULL(`column_name`)
FROM `table_name`;

If column_ If a field in the name column is NULL, 1 is returned; if not, 0 is returned

  • The IFNULL() function is also used to determine whether the field is NULL, but unlike ISNULL(), it receives two parameters, the first parameter column_name is the column name, and the second parameter value is equivalent to the alternate value.
    Syntax:
SELECT IFNULL(`column_name`, `value`)
FROM `table_name`; 

If column_ If a field in the name column is NULL, the value value is returned; otherwise, the corresponding content is returned.
The COALESCE(column_name, value) function is also used to determine whether a field is NULL. Its usage is the same as IFNULL()

For example, find out the information of teachers who have no mailbox and are older than 20 years old from the teachers table

SELECT * FROM teachers 
WHERE isnull(email) and age > 20

The return value of isnull function can be used to judge the condition

  • count() function

    • Count (column_name) counts the number of rows the specified column has, but removes rows with NULL values
    • COUNT(*) counts the number of rows in the table, including the row with NULL value and the row with duplicate items

For example, count the number of teachers aged between 20 and 28 and whose nationality is China or Britain in the teacher table. Finally, return the statistical value, and the result column name is displayed as teacher_count

SELECT COUNT(*) AS teacher_count 
FROM teachers
WHERE age between 20 and 28 and country in ('CN','UK')

Note: the difference between COUNT(column_name) and COUNT(*)

In COUNT(column_name), if column_ If the value in the name field is NULL, the count will not increase, and if the field value is empty string '', the field value will increase by 1

In COUNT(), unless the whole record is NULL, the count will not increase. If there is a record that is not NULL or an empty string '', the count value will increase by 1. Normally, a table will have a primary key, but the primary key is not empty, so count () is equivalent to COUNT(PRIMARY_KEY) in a table with a primary key, that is, how many records are queried.

  • COUNT(DISTINCT column_name)
    The COUNT(DISTINCT column_name) function returns the number of different values for the specified column

For example: count the different teacher IDS in the curriculum courses_ The number of IDS. Finally, the statistical value is returned, and the result column name is displayed as teacher_count

SELECT COUNT(DISTINCT teacher_id) as teacher_count
FROM courses

It is equivalent to counting the number of teachers_ ID (how many teachers are there)

TIME function (1) (NOW, CURDATE, CURTIME, DATE, TIME, EXTRACT, DATE_FORMAT)

  • NOW() CURDATE() CURTIME() get the current time
    • NOW() can be used to return the current date and time format: YYYY-MM-DD hh:mm:ss
    • CURDATE() can be used to return the current date format: YYYY-MM-DD
    • CURTIME() can be used to return the current time format: hh:mm:ss
in use NOW() and CURTIME() If you want to accurately calculate the time after seconds,
You can add numbers in (). The number you add indicates the number of digits after seconds
 such as NOW(3) It is accurate to milliseconds, expressed as 2021-03-31 15:27:20.645

For example, insert the current date into the records table records

INSERT INTO records
VALUES(CURDATE())
  • DATE(), TIME() get date and time

For example: query the creation date of the course in the course table

SELECT `name`,DATE(`created_at`) AS `created_date`
FROM `courses`;

Results obtained:

For example 2: query the date and time in the course creation time of the course table respectively

SELECT `name`, `created_at`, 
	DATE_FORMAT(DATE(`created_at`),"%Y-%m-%d") AS `created_date`, 
	DATE_FORMAT(TIME(`created_at`),"%H:%i:%s") AS `created_time`
FROM `courses`;

The results are as follows:

  • The EXTRACT() function extracts the specified time information

Syntax:

SELECT EXTRACT(unit FROM date)
FROM `table`

The date parameter is a legal date expression.
The unit parameter is the time part to be returned, such as YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, etc.

For example, query the course name (name) and the hours of the course creation time (created_at) of all courses from the course table courses, and alias the column name of the extracted hours as created_hour.

SELECT name,EXTRACT(hour FROM created_at) as created_hour
FROM courses
  • DATE_FORMAT() formats the output date

Syntax:

SELECT DATE_FORMAT(date,format);

among
Date is a valid date.
Format is the output format of date/time. Where% m represents the month,% d represents the date,% Y represents the year, and w represents the week.
Format requirements for format: reference resources
%i. % L (lowercase L) and% I (uppercase I) each represent a different format. Refer to the link above
For example, query the courses table, query the course creation time, and return the results in the format of 'year month day hour: minute: second', and the returned column name is displayed as DATE_FORMAT

SELECT DATE_FORMAT(created_at,'%Y-%m-%d %H:%i:%S') AS DATE_FORMAT FROM courses

Time function 2 (2) (DATE_ADD, DATE_SUB, DATEDIFF, TIMESTAMPDIFF)

  • DATE_ADD() increase time
    One of the common time functions used to add a specified time interval to a date

Syntax:

SELECT DATE_ADD(date, INTERVAL expr type)
FROM table_name

Date refers to the effective date of the desired operation, which is the start date
Expr is the value of the time interval you want to add (expr is a string. For negative intervals, you can start with "-")
Type is the specific data type, indicating the unit added with the time interval (it can be microsecond, second, minute, hour, day, week, month, quarter, year, etc.)

For example, modify the course creation date of the course in the courses table, postpone the course creation date by one day, and finally return the course name and the modified course creation time. The modified course creation time is named new_created

SELECT name, DATE_ADD(created_at, INTERVAL 1 day) as new_created
FROM courses
  • DATE_SUB() function
    Used to subtract the specified time interval from a date. It has a similar usage to the DATE_ADD() function.

Syntax:

SELECT DATE_SUB(date, INTERVAL expr type)
FROM table_name

expr and type are the same as the above DATE_ADD() function
Note: date_add itself is plus, when expr is negative, it is minus, date_SUB itself is minus, and when expr is negative, it is plus

  • DATEDIFF() function
    The commonly used date difference can only be calculated in MySQL by default

Syntax:

SELECT DATEDIFF(Time 1,Time 2) AS date_diff FROM courses;

DATEDIFF() difference calculation rule: Time 1 - time 2

For example, query the courses table and calculate the number of days from March 26, 2019 to created_at. The result column name is displayed as date_diff

select  DATEDIFF(created_at,'2019-03-26') AS date_diff from courses
  • Timestamp diff() function
    MySQL's own date function with multiple query functions can calculate the YEAR (YEAR, time 1, time 2), MONTH (MONTH, time 1, time 2), WEEK (WEEK, time 1, time 2), DAY (DAY, time 1, time 2) and HOUR (HOUR, time 1, time 2) of the difference between the two dates.

Syntax:

SELECT TIMESTAMPDIFF (type,Time 1,Time 2) AS year_diff;

TIMESTAMPDIFF() difference calculation rule: Time 2 - time 1
For example, query the courses table, calculate the difference between the course creation time and the number of months' 2020-04-22 ', and the returned column name is displayed as MonthDiff

select TIMESTAMPDIFF(month,created_at,'2020-04-22') as MonthDiff from courses

Note the direction in which the two times subtract

Constraints and links

Constraints (not null, UNIQUE, PRIMARY KEY, CHECK, DEFAULT)

In SQL, a constraint is a rule that specifies the data in a table. If there is a violation of the constraint, the behavior will be stopped. It can help administrators better manage the database and ensure the correctness and effectiveness of the data in the database. For example, in the background database design, check or judge the data to be input, and then decide whether to write to the database, which are the application of constraints.

  • not null non NULL constraint
    The NOT NULL constraint forces the column not to accept null values, and forces the field to always contain values, which means that if you do not add values to the field, you cannot insert new records or update records.

For example:

CREATE TABLE `Persons` (
    `ID` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255) NOT NULL,
    `Age` int
);

Add the NOT NULL constraint in the Age field of a created table as follows:

ALTER TABLE `Persons`
MODIFY `Age` int NOT NULL;

Delete the NOT NULL constraint in the Age field of a created table as follows:

ALTER TABLE `Persons`
MODIFY `Age` int NULL;
be careful
 Don't put NULL Value is confused with an empty string. NULL Value is no value,

It's not an empty string. If you specify '' (two single quotes with no characters between them), this is allowed in the NOT NULL column. An empty string is a valid value. It is NOT NULL. The NULL value is specified with the keyword NULL instead of an empty string

  • UNIQUE unique constraint Writing reference
    Three features:
    • The UNIQUE constraint uniquely identifies each record in the database table
    • UNIQUE and primary key constraints guarantee the uniqueness of columns or column sets (UNIQUE in the same column)
    • The primary key constraint will automatically define a UNIQUE constraint, or the primary key constraint is a special UNIQUE constraint. However, there is an obvious difference between the two: each table can have multiple UNIQUE constraints, but only one primary key constraint.

When creating a table, the unique constraint is created
The following SQL is in P when the Persons table is created_ Create UNIQUE constraint on ID column:
In MYSQL:

CREATE TABLE `Persons`
(
`P_Id` int NOT NULL,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255),
UNIQUE (`P_Id`)
)

In SQL Server / Oracle / MS Access:

CREATE TABLE `Persons`
(
`P_Id` int NOT NULL UNIQUE,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255)
)

Name the UNIQUE constraint and define the UNIQUE constraint for multiple columns:
MYSQL / SQL Server / Oracle / MS Access all are:

CREATE TABLE `Persons`
(
`P_Id` int NOT NULL,
`LastName` varchar(255) NOT NULL,
`FirstName` varchar(255),
`Address` varchar(255),
`City` varchar(255),
CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)
)

UNIQUE constraint when ALTER TABLE:
When the table has been created, in P_ Create UNIQUE constraint for ID column:
Add constraints in MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE `Persons`
ADD UNIQUE(`P_Id`)

For example, when a table has been created, you need to name the UNIQUE constraint and define the UNIQUE constraint for multiple columns:
MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE `Persons`
ADD CONSTRAINT uc_PersonID UNIQUE (`P_Id`,`LastName`)

constraint is an alias for

To undo the UNIQUE constraint:
In MySQL:

ALTER TABLE `Persons`
DROP INDEX uc_PersonID

In SQL Server / Oracle / MS Access:

ALTER TABLE `Persons`
DROP CONSTRAINT uc_PersonID
  • PRIMARY KEY primary key constraint
    Each record in the database table is uniquely identified. In short, PRIMARY KEY = UNIQUE + NOT NULL. From a technical point of view, PRIMARY KEY and UNIQUE have many similarities. However, there are the following differences:
    • NOT NULL UNIQUE can define one or more columns of the table as unique attributes. When the PRIMARY KEY is set to multiple columns, only the sum of multiple columns can be guaranteed to be unique. Specific to a column, it may be repeated.
    • PRIMARY KEY can cooperate with foreign keys to form the relationship between master and slave tables, while NOT NULL UNIQUE cannot
    • The bigger difference is in logic design. PRIMARY KEY is generally used as a record ID in logic design, which is also the original intention of setting PRIMARY KEY, and UNIQUE is only to ensure the uniqueness of domain / domain group.
as:
Table 1: users id (Primary key),user name
 Table II: Bank card No id (Primary key),user id (Foreign key)
Then table 1 is the master table and table 2 is the slave table.

Add PRIMARY KEY constraint when creating table
The following SQL is created in P when the personal information table Persons is created_ Add PRIMARY KEY constraint on ID column:
Mode 1:

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255),
    PRIMARY KEY (`P_Id`)
);

Mode 2:

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL PRIMARY KEY,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255)
)

A primary key consists of multiple values:

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255),
    CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)
)

To add a primary key to a created table:

ALTER TABLE `Persons`
ADD CONSTRAINT pk_PersonID PRIMARY KEY (`P_Id`,`LastName`)
notes:
If you use ALTER TABLE Statement to add a primary key, you must declare the primary key column as not containing NULL Value (when the table is first created)

Undo primary key:

ALTER TABLE `Persons`
DROP PRIMARY KEY

Another method (take the primary key of the alias, which can also be removed in the previous method):

ALTER TABLE `Persons`
DROP CONSTRAINT pk_PersonID
  • FOREIGN KEY constraint
    • FOREIGN KEY in one table points to UNIQUE KEY in another table.
    • The most fundamental function of foreign keys is to ensure the integrity and consistency of data.
    • Foreign key constraints are used to establish a relationship between two tables. You need to specify which column of the main table to reference.

SQL FOREIGN KEY constraint when creating table
Create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is created:

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)

Writing 2:

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL PRIMARY KEY,
`OrderNo` int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
among
NOT NULL Indicates that the field is not empty
REFERENCES Indicates that a table is referenced

Named foreign key constraints:

CREATE TABLE `Orders`
(
`O_Id` int NOT NULL,
`OrderNo` int NOT NULL,
`P_Id` int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
)
among
CONSTRAINT Represents a constraint, followed by the constraint name, which is often used to create and delete constraints;

Add a foreign key constraint:
Writing 1:

ALTER TABLE `Orders`
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Writing 2:

ALTER TABLE `Orders`
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)

Undo foreign key constraint:

ALTER TABLE `Orders`
DROP FOREIGN KEY fk_PerOrders
  • Check check constraints
    Used to limit the range of values in a column and evaluate inserted or modified values. Values that meet the criteria will be inserted into the table, otherwise the insertion operation will be abandoned. You can specify multiple CHECK constraints for the same column.
    CHECK constraints can be used for either a column or a table:
    1. If you define a CHECK constraint on a single column, only specific values are allowed for that column.
    2. If you define a CHECK constraint on a table, the constraint limits values in specific columns based on the values of other columns in the row.

    Defining a CHECK constraint is somewhat similar to writing a WHERE clause of a query. It uses different comparison operators (such as AND, OR, BETWEEN, IN, LIKE, AND IS NULL) to write its Boolean expression, which returns TRUE, FALSE, OR UNKNOWN. The CHECK constraint returns an UNKNOWN value when a NULL value exists IN the condition.

When creating the curriculum courses, give the total number of students to the students_ Count field with a constraint greater than 0:

`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0)
)

Add a CHECK constraint to multiple columns:

CREATE TABLE `courses`
(
`id` int,
`name` varchar(255),
`student_count` int,
`created_at` date,
`teacher_id` int,
CHECK (`student_count` > 0 AND `teacher_id` > 0)
)

Add CHECK constraint when table already exists:

ALTER TABLE `courses` 
ADD CHECK ( `student_count` > 0);

Undo CHECK constraint:

ALTER TABLE `courses` 
DROP CHECK chk_courses
  • DEFAULT default constraint
    1. The DEFAULT constraint is used to insert DEFAULT values into columns.
    2. If no other value is specified, the default value is added to all new records.
    For example, if there are many female students, the gender can be "female" by default. If this field is not assigned when inserting a new record, the system will automatically assign "female" to this field.

Syntax:
< field name > < data type > Default < default >

DEFAULT constraint when creating table:

CREATE TABLE `Persons`
(
    `P_Id` int NOT NULL,
    `LastName` varchar(255) NOT NULL,
    `FirstName` varchar(255),
    `Address` varchar(255),
    `City` varchar(255) DEFAULT 'Sandnes'
)

DEFAULT constraint when ALTER TABLE:

ALTER TABLE `Persons`
ALTER `City` SET DEFAULT 'SANDNES'

To undo the DEFAULT constraint:

ALTER TABLE `Persons`
ALTER `City` DROP DEFAULT

Link

Join is a mechanism used to associate multiple tables in a SELECT statement and return a set of output.
At this time, we will talk about the two protagonists in the connection - PRIMARY KEY and FOREIGN KEY

JOIN join clause

  • INNER JOIN: inline join returns a row if there is at least one match in the table
  • LEFT JOIN: LEFT JOIN returns all rows from the left table even if there is no match in the right table
  • RIGHT JOIN: RIGHT JOIN returns all rows from the right table even if there is no match in the left table
  • FULL JOIN: FULL JOIN returns rows as long as there is a match in one of the tables
  • CROSS JOIN: CROSS JOIN is also called Cartesian product. The data of two tables correspond to each other one by one. The number of rows returned is equal to the product of the number of rows of two tables
  • . INNER JOIN simply put, INNER JOIN is to take the intersection of two tables, and the returned result is the part that meets the conditions in both connected tables

INNER JOIN

Syntax:

Writing method 1:
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
INNER JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

Method 2:
SELECT `table1`.`column1`, `table2`.`column2`...
FROM `table1`
JOIN `table2`
ON `table1`.`common_field` = `table2`.`common_field`;

It should be noted that the join condition needs to be given with a specific ON clause.

For example, please write an SQL statement to connect the course table courses and the teacher table teachers internally, query the course name and course number of all courses taught by the "Eastern Heretic" teacher, and the result column names are based on the course number id and course name respectively_ Name and teacher name_ Name displays.

select c.id,c.name as course_name,t.name as teacher_name 
from courses c join teachers t on c.teacher_id = t.id
where t.name = 'Eastern Heretic'

External join

In MySQL, the external connection query will return all data records of at least one table in the operated table. In MySQL, data query is realized through the SQL statement "OUTER JOIN... ON". External join query can be divided into the following three categories:

  • Left outer connection
    The result of the LEFT OUTER join includes all rows of the left table specified in the LEFT OUTER clause, not just the rows matched by the join column, which means that the left join will return all records in the left table plus the matched records in the right table. If a row in the left table has no matching row in the right table, all the selection lists in the right table are null in the associated result row.

  • Right outer connection
    The right outer join in the outer join query refers to the table on the right of the keyword RIGHT JOIN as the reference table when the matching criteria are executed in the new relationship. If a row in the right table does not have a matching row in the left table, the left table returns a null value.

  • Total external connection
    FULL OUTER JOIN keyword returns rows as long as there is a match in one of the left table (table1) and the right table (table2). The FULL OUTER JOIN keyword combines the results of LEFT JOIN and RIGHT JOIN.
    mysql does not support full join. You can use left join+ union+right join
    (SELECT * from a left JOIN b on a.name=b.id) UNION (SELECT * from a RIGHT JOIN b on a.name=b.id );

Query syntax:

SELECT column_name 1,column_name 2 ... column_name n
    FROM table1
        LEFT | RIGHT | FULL  (OUTER) JOIN table2
        ON CONDITION;

In the above statement, the parameter column_name indicates the name of the field to be queried, which comes from the connected tables table1 and table2. The keyword OUTER JOIN indicates the external connection of the table, and the parameter CONDITION indicates the matching conditions.

Test sample table:

For example 1:
Please write an SQL statement, left connect the teachers table and the courses table, and query the names of teachers and courses from China (lecturer's nationality country ='CN '). Please list the results with the course name course respectively_ Name, teacher name_ Name displays.

select c.name as course_name,t.name  as teacher_name
from teachers t left join courses c on t.id = c.teacher_id
where country ='CN'

Example 2:
Please write an SQL statement, right connect the course table courses with the teacher table teachers, and query the name, email address and course name of teachers from China (Lecturer nationality country ='CN '). Please list the results with the course name course respectively_ Name, teacher name_ Name, teacher email_ Email display.

select c.name as course_name,t.name as teacher_name,t.email as teacher_email
from courses c right join teachers t on c.teacher_id = t.id
where country = 'CN'

For example 3:
Connect the course table courses and teacher table teachers to query all course names and their corresponding teacher names and teacher nationalities. Please list the results with the course name course respectively_ Name, teacher name_ Name, nationality of teacher_country display.

select c.name as course_name, t.name as teacher_name,t.country as teacher_country
from courses c left join teachers t on c.teacher_id = t.id
union
select c.name as course_name, t.name as teacher_name,t.country as teacher_country
from courses c right join teachers t on c.teacher_id = t.id

CROSS JOIN

Compared with inner join and outer join, cross join is very simple because there is no ON clause
Cross connect: returns all rows in the left table. Each row in the left table is combined with all rows in the right table. That is, the data of the two tables correspond one by one. The number of rows in the query result is the number of rows in the left table multiplied by the number of rows in the right table.

The result of CROSS JOIN is also called Cartesian product
Cartesian product refers to the Cartesian product of two sets X and Y in mathematics, also known as direct product, expressed as X × Y. The first object is a member of X and the second object is one of all possible ordered pairs of Y.

Definition method:
Cross connection can be defined in two ways: implicit connection and explicit connection. The query results of the two definitions are the same.
Implicit cross connection: the CROSS JOIN keyword is not required, as long as the name of the table to be cross connected is listed after the FROM statement of the SELECT statement. This method can basically be supported by any database system.
Implicit syntax:

SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`,`teachers` `t`;

Display syntax:

SELECT  `c`.`name` AS `course_name`, `t`.`name` AS `teacher_name`
FROM `courses` `c`
CROSS JOIN `teachers` `t`;

The following is the advanced knowledge (work needs to be mastered)

Advanced query (group by, having, sub query)

Group query group by

Syntax:

SELECT `column_name`, aggregate_function(`column_name`)
FROM `table_name`
WHERE `column_name` operator value
GROUP BY `column_name`;

For example, query the teachers table, count the number of teachers of different ages, and arrange the results according to age, and the returned column name is displayed as age_count:

select  age ,count(age) as age_count
from teachers 
group by age
order by age DESC

For example 2: query teacher table teachers and course table courses, count the number of courses taught by each teacher, and arrange the results according to the number of courses from large to small. If the same number of courses is arranged according to the teacher's name, return the column name, and the column name is displayed as teacher_name, the course quantity column name is displayed as course_count:

select t.name as teacher_name,IFNULL(count(c.name),0) as course_count
from courses c right join teachers t on c.teacher_id = t.id
group by teacher_name
order by course_count DESC,teacher_name

HAVING clause

When we use the WHERE conditional clause, we will find that it cannot be used in conjunction with aggregate functions. To solve this, SQL provides a HAVING clause. When used, the HAVING clause is often used in conjunction with GROUP BY. The HAVING clause is a clause that filters the group statistics function
The way in which HAVING clause sets conditions for GROUP BY clause is similar to that in WHERE clause and SELECT clause, and the syntax is also similar, but the search condition of WHERE clause is before grouping operation, while HAVING is after grouping operation
Syntax:

SELECT   `column_name`, aggregate_function(`column_name`) 
FROM     `table_name` 
WHERE    `column_name` operator value 
GROUP BY `column_name` 
HAVING   aggregate_function(`column_name`) operator value;

For example, query the teachers' information in the teachers table that the average age of all teachers in each country is greater than the average age of teachers in all countries:

To be added

Simple subquery

Syntax:

SELECT `column_name(s)`
FROM `table_name`
WHERE `column_name` OPERATOR (
    SELECT `column_name(s)`
    FROM `table_name`
);
1,Subquery method:
select name from teachers
where id = (
    select teacher_id from courses
    where name = 'Big Data'
)
2,Table connection method:
select t.name from
teachers t join courses c on t.id = c.teacher_id
where c.name = 'Big Data'

Method 1 time consuming:

Method 2: time consuming:

For the sub query in the INSERT statement, first use the SELECT statement of the sub query to find the data to be inserted, and then INSERT the returned data into another table. The data selected in the subquery can be modified by any character, date or numeric function:
insert into sub query:
Syntax:

INSERT INTO `table_name`
	SELECT `colnum_name(s)`
	FROM `table_name`
	[ WHERE VALUE OPERATOR ]

For example, back up the data of the teachers table

INSERT INTO `teachers_bkp` 
SELECT * 
FROM `teachers`;

update subquery usage:
Syntax:

UPDATE `table_name` 
SET `column_name` = `new_value`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`
   [WHERE] )

Subquery of DELETE statement
Syntax:

DELETE FROM `table_name`
WHERE `column_name` OPERATOR 
   (SELECT `column_name`
   FROM `table_name`  
   [WHERE] )

For example, delete the teachers who have created courses before 2020 (excluding 2020) in the teachers table

delete from teachers 
where id in (
    select distinct teacher_id from courses 
    where created_at < '2020-01-01'
)

for example

To be updated later
to be continue. . . . .

Tags: Database MySQL SQL

Posted on Tue, 21 Sep 2021 23:00:55 -0400 by THESiUS