Database Note 02 SQL Statement + MySQL Constraint

Database Note 02 SQL Statement (bottom) +MySQL Constraint

1. Detailed syntax of query statements in SQL:

Query all data in the table:

select * from surface 
-- It's actually recommended that you write out all the field names

Query individual fields:

select Field name 1,Field Name 2 from surface

Conditional Query:

select field from surface where query criteria
-- where Related clauses are described in the next section

Aliase the field:

select field as newname1，Field 2 as newname2 from surface
-- Aliases are just used for presentations, not for real-world renaming

Field operations:

select field,Field operations as Temporary field display from surface
-- Assume that multiple field operations occur during the operation NULL，The result will also appear NULL

For NULL processing:

select IFNULL(Field operations,Want to replace NULL Value of) as Temporary field display from surface

Remove duplicate records:

select distinct field from surface where Deduplication condition
-- When de-duplicating, note that field combinations must be exactly the same before they can be removed

Fuzzy Query/Character Matching
Scenarios such as Baidu Search Engine, Searchs will display all the information first as s: wildcards% and _are required
Match any number of characters%Match any single character_

select * from surface where field like 's%'
-- with s All strings at the beginning

Wildcard format:'%N'ends with N,'%R%' contains R, and the second character of'a%'is composed of A''5 characters

Sort query:

select * from order by Fields to Sort
-- Default ascending order: from smallest to largest)
-- Descending query:
order by Sorted field DESC
-- Default ascending order can also be followed by ASC

Multiple sort fields: order by sort field 1 ASC, sort field 2 DESC

select field from surface where condition order by Field sorting

Full grammar:

select The sequence that follows: where, group by, having, order by, limit

Aggregation function:
Aggregation functions are often used with group by, for example, select grouping field AVG (field) from table group by grouping field
Grouping fields must appear before and after
where can have a filter before grouping, and having is used for re-filtering after grouping
limit Paging Query:
How many pieces of data are paged as a page:

select * from surface limit The page on which the article is located,How many data pages

The starting index of each page is: (Current Page-1)* Number of entries per page

2. where conditional clause:

where condition clause filters for query conditions except for basic=,!=, <, >There are other expressions besides these:

Note that NULL values cannot be judged by an equal sign
Select is null when determining NULL:

select field from surface where Information to be queried is null

A judgement not equal to NULL corresponds to an is null above which is not null
NULL's judgment can be made using the <=>operator

Query for multiple information or: Use OR:
where condition1 or condition2
Above brief notation:
If the query fields corresponding to the condition are the same, use the where field in(value1,value2...)
Query for information corresponding to multiple conditions using and
Range queries, such as greater than or equal to 1000 and less than or equal to 2000:

where field>=1000 and field<-2000

The above can be replaced by:

where field between 1000 and 2000

3. Aggregation function:

Aggregation functions in MySQL operate on columns, roughly in the following ways:

Query the number of elements corresponding to a field, using ==count (field)==to count the number:

select count(field) as Temporary field display from surface

Note: NULL values do not participate in the statistics of count() function, the general number of statistics select value is the only field for statistics

Statistics the sum in a column, using sum:

select sum(field) as Temporary field display from surface

Statistics the average in a column, using avg:

select avg(field) as Temporary field display from surface

For average values, since the mean calculation formula is equal to the sum divided by the number, it can also be written as: sum (field)/count (field)

Statistical maximum and minimum values in a column: Max (field) and min (field)

select max(field) as Temporary field display from surface
select max(field) as Temporary field display from surface

4. case when then end Conditional selection control:

The case when then end statement can be used in MySQL to modify the values of information in different groups when the where clause is conditionally controlled:

select Information to display as The original data,
case Column fields that need to be grouped 
when 'Group 1' then wages+500
...Multiple when
else Other grouping changes not mentioned above
end as Changed data

Case demo:
Classify the student's performance by interval, with more than 60 points showing passing and less than 60 points showing failing:

select sname,sno,sclass,sgrade from test.sc 
where case sgrade
when sgrade>= 60 then sgrade = 'pass'
else sgrade = 'Fail'
end;

5. Constraints in SQL:

Constraints: A constraint and a rule, a constraint in a database, is the value of a field, a restriction on Rules
Common constraints in databases are as follows:

Primary key constraint: PRIMARY KEY
 Self-growth constraints: AUTO_INCREMENT
 Non-empty constraint: NOT NULL
 Unique Constraints: UNIQUE
 Foreign key constraints: FOREIGN KEY

Primary key constraint:

Primary key constraints are non-empty and unique. It is strongly recommended that a table provide a primary key field and that a table can and can only have one primary key field
1) Syntax for adding a primary key (when creating a new table):

CREATE TABLE test(
	username VARCHAR(15) PRIMARY KEY, 
	-- Label username Is Primary Key
	age INT
    -- Another way to write the above grammar is at the end of the table: PRIMARY KEY(username)
)
-- There is actually no big difference between the two, except that the former is a column-level primary key and the latter is a table-level primary key.

Note: Primary key elements cannot be repeated and cannot be NULL
2) Primary keys can also be added by modifying the table:

ALTER TABLE test ADD PRIMARY KEY(username) 
-- The table has been created, add when modifying

3) Joint Primary Key: Use multiple columns in a column as the primary key, and only follow the second writing above:

PRIMARY KEY(username,age)

A union primary key is one that treats multiple column fields as a whole

Non-empty constraint:

A non-null constraint is one in which a field in a column in a database is not allowed to be NULL, followed by NOT NULL when the table is created

CREATE TABLE test(
	username VARCHAR(15) PRIMARY KEY, 
	age INT NOT NULL
)

Unique Constraints:

Unique constraints refer to a column in a database that does not allow duplicate field values, followed by UNIQUE when the table is created:

CREATE TABLE test(
	username VARCHAR(15) PRIMARY KEY, 
	age INT UNIQUE
)

1) Unique constraints have no effect on NULL values
2) Non-null constraints and unique constraints can be used together, but are actually different from primary keys

Enumeration type:

Enumeration types in MySQL can act as constraints, but not constraints:
Adding ENUM(value1,value2) constraints to create a field in a table can only be value1 or value2:

CREATE TABLE test(
	username VARCHAR(15) PRIMARY KEY, 
	age INT ENUM(18,19)
)

Non-negative constraints:

Negative values cannot appear in the field UNSIGNED when the table is created:

CREATE TABLE test(
	username VARCHAR(15) PRIMARY KEY, 
	age INT UNSIGNED
)

Self-growth constraints:

Self-growth constraints are usually combined with primary key constraints, often referred to as self-growth primary key constraints. Generally, for data self-growth, you can define id int in combination with PRIMARY KEY AUTO INCREMENT
In general, the primary key self-growth type is used for fields of integer type, with primary key constraints, or even with nonnegative ones.
This id is often added to each table when a table is built, and it is not necessary to add an id value to each table when a corresponding value is added. It is automatically incremented by one when generated:

CREATE TABLE test(
	id INT PRIMARY KEY AUTO INCREMENT,
	username VARCHAR(15), 
	age INT UNIQUE
)

Remove primary key constraint:

ALTER table surface drop Correspondence Constraints

If more constraints, delete one constraint per sentence

CREATE TABLE test(
	id INT PRIMARY KEY AUTO INCREMENT,
	username VARCHAR(15), 
	age INT UNIQUE
)
-- Table will be built id Data in this column removes self-increasing constraints
ALTER table test drop AUTO INCREMENT 
-- Table will be built id Data in this column removes primary key constraints
ALTER table test drop PRIMARY KEY

Posted by MysticalCodex on Thu, 30 Sep 2021 09:21:00 -0700

Programmer Group