chalmery

1 SQL#

Full name is Structured Query Language: Structured Query Language. Each database statement has its own dialect, but the basic commands remain unchanged.

MySQL statements are case-insensitive, but it is recommended to write keywords in uppercase

• DDL: Operations on databases and tables
• DML: Adding, deleting, and modifying data in tables
• DQL: Querying data from tables
• DCL: Authorization

2 Data Types#

Data types are generally divided into three categories: numeric types, string types, and date types.

1 Numeric Types:

Here, M represents the maximum number of digits, and D represents the number of digits after the decimal point.

2 String Types:

In MySQL, the maximum byte size for ==each record== is 64k (numbers, strings), text, and binary files are not included.

Each record refers to a row of data, such as id + name + age + address combined.

3 Date Types:

3 DDL Syntax#

Operations on databases and tables.

1 Operations on Databases#

1. Create Database:

# Create database
create database database_name; 
# Create if it does not exist, do not create if it exists
create database if not exists database_name;
# Create and set character set
create database database_name character set character_set_name;

2. Query Databases:

show databases;
# Show and create
show create database database_name;

3. Modify Database Character Set:

# Modify database character set
alter database database_name character set character_set_name; 

4. Delete Database:

drop database database_name;
# Delete if it exists
drop database if exists database_name;

5. Query the name of the currently used database:

select database();

6. Use Database:

use database_name;

2 Operations on Tables#

1. Create Table:

create table table_name(
    column_name1 data_type1,
    column_name2 data_type2,
    column_name3 data_type3,
    ......
    column_nameN data_typeN # No semicolon after the last column
); 

2. Copy Table:

create table new_table_name like table_name;

3. Query All Tables:

# Query all tables
show tables;
# Query table structure
desc table_name;

4. Modify Table:

# Rename table
alter table table_name rename to new_table_name;
# Modify table character set
alter table table_name character set character_set_name;
# Add a column
alter table table_name add column_name data_type;
# Modify column
alter table table_name change column_name new_column_name new_data_type; [[Change name and type]]
alter table table_name modify column_name new_data_type; [[Only change the column's data type]]

5. Delete:

# Delete column
alter table table_name drop column_name; 
# Delete table
drop table table_name;
[[Check before deleting table]]
drop table if exists table_name;

4 DML Syntax#

Operations on the content of tables, which are the most commonly used statements.

1 Add Data#

Add data:

# Add all columns
insert into stu(column_name1,column_name2....column_nameN) values (data1,data2...dataN);
# If adding all data, column names can be omitted
insert into stu values (data1,data2...dataN); 
# If a row does not need to be added, it can be omitted
# If you want to set a default value, you can write it as default

2 Delete Data#

# Delete data
delete from table_name where condition;
# Clear table
delete from table_name;    
# Directly delete the table and create a table with the same name, which is faster
truncate table table_name;

3 Modify Data#

# Modify data
update table_name set column_name1 = value1,column_name2 = value2, .... where condition
# Not adding a where condition means modifying all specified data in the table
update table_name set column_name1 = value1,column_name2 = value2, ....   

5 DQL Syntax#

Querying data from tables, the writing order is as follows:

select   field_list
from     table_list
where    condition_list
group by group_condition
having   filter on sorted data
order by sorting_column
limit    pagination, limit is MySQL's dialect

1 Basic Query#

# Query all
select * from table_name;
# Query specified columns
select column_name1,column_name2... from table_name;
# Query and remove duplicates
select distinct column_name1,column_name2... from table_name;
# Calculate two columns
select column1 + column2.... from table_name;
# If there are null values above, normal calculation cannot be performed, so you can use if null keyword
select math + if null(english,0) from table_name;
# Query with alias
select column1 as alias, column2 as alias..... from table_name;
[[Omitting as is also acceptable]]
select column1 alias, column2 alias....... from table_name; 

2 Conditional Query#

Add where fields after the statement, then write the query conditions.

3 Sorting Query#

Add sorting after order by.

Statement:

Multi-condition sorting means that when the first condition is the same, the second sorting is performed.

select * from student order by math_score, english_score; 

4 Aggregate Functions#

Aggregate Functions: Treat a column of data as a whole for vertical calculation, that is, calculating on columns.

Note: Aggregate functions will exclude null values, if counting entries, it is recommended not to have empty columns.

Usage:

Count total number of people:

select count(name) from student;

5 Group Query#

Add grouping after group by.

If grouping is performed, the result queried by select must be a whole, so it can only write: the field of grouping itself, aggregate function.

For example, to check the average math score of boys and girls, as well as their respective counts:

select sex, avg(math), count(id) from student group by sex;

If you need to limit after grouping, you need to use having for filtering.

For example, to filter out groups with more than 3 people after querying:

select sex, avg(math), count(id) from student group by sex having count(id) > 3;

6 Pagination Query#

Pagination Query: If the query results are very large, and we want to display them in pages, this is pagination query, write conditions in the limit field.

limit starting_index, number_of_rows_per_page

If you want to query the first three people, and display 3 on the first page:

select * from student limit 0,3;  [[Page 1]]
select * from student limit 3,3;  [[Page 2]]
select * from student limit 6,3;  [[Page 3]]

Thus we see a rule: starting index = (page number - 1) * number of rows displayed per page.

7 Multi-table Query#

Its syntax is basically the same as single table query, but the meaning is different. First, let’s introduce Cartesian product.

• Cartesian Product: This is all combinations of several items. In databases, if querying multiple tables, it means calculating the Cartesian product of the data from multiple tables, which prints out all combinations.

Assuming there are tables A and B, A has x data, and B has y data, then the query will yield x*y data.

select * from A, B;

Note: Directly performing such queries will yield a lot of useless data, so below we introduce how to eliminate useless Cartesian products.

1 Inner Join Query

• Implicit Inner Join: Directly specify in the where condition that the associated fields of the two tables are equal.

select * from table1, table2 where table1.id = table2.id;

• Explicit Inner Join:

select query_fields from table1 inner join table2 on table1.id = table2.id;
select query_fields from table1 join table2 on table1.id = table2.id;        [[inner can be omitted]]

2 Outer Join Query

• Left Outer Join: Query all data from the left table, as well as the intersection part (generally use left outer join).

select query_fields from table1 left join table2 on condition;  

• Right Outer Join: Query all data from the right table, as well as the intersection part.

select query_fields from table1 right join table2 on condition; 

3 Subquery

Concept: This is nested querying, where the nested query is called a subquery of the outer query.

• 1 Subquery result is a single row and single column

Using operators for judgment are all this type, for example, querying the highest score must be a single value.

# Query information of the person with the highest score
-- 1 What is the highest score
select max(salary) from emp;   -- 90 points
-- 2 Query information of the person with the highest score
select * from emp where salary = 90
# It can actually be written as a nested query
select * from emp where salary = (select max(salary) from emp);

• 2 Subquery is multiple rows and single column

Using the operator in to receive.

# Query information of all students in class 2 and class 3
-- 1 Query the class numbers corresponding to class 2 and class 3
select id 
from dept
where name = 'Class 1' or name = 'Class 2'; -- 1, 2
-- 2 Query student information corresponding to class numbers, or using in
select * 
from emp 
where dept_id = 1 or dept_id = 2;
select * 
from emp 
where dept_id in (1, 2);
# Can be written as a nested query
select * 
from emp 
where dept_id 
in (select id from dept where name = 'Class 1' or name = 'Class 2');

• 3 Subquery is multiple rows and multiple columns

Multiple rows and multiple columns are equivalent to a virtual table.

# Query all students whose enrollment date is after January 1, 2020 and their class information
# Normal query
select * 
from emp t1, dept t2 
where t1.id = t2.id and t1.date > '2020-01-01';

# Nested query
select * 
from dept t1, (select * from emp where emp.date > '2020-01-01') t2 
where t1.id = t2.id;

6 DCL User Management#

1 User Management#

1 Add User

create user username identified by password
create user username @ hostname identified by password
# Hostname indicates that this user can only log in from this host
# If not written, it defaults to the following form, which can log in from any machine
create user 'username' @ '%' identified by password

2 Modify User Password

# Method 1
update user set password = password('new_password') where user = 'username';
# Method 2
set password for 'username'@ 'hostname' = password('new_password');

3 Forgot root user password

# Execute the following command in cmd to stop MySQL service
net stop mysql
# Start MySQL service without verification
mysqld --skip-grant-tables
# Open a new window, enter the following command to enter without verification, then modify the password
mysql

4 Delete User

drop user 'username' @ 'hostname'
drop user 'username' 

5 Query Users

When MySQL is downloaded, there is a built-in database called mysql, which has a table called user, where user information is stored. So the query is:

use mysql;
select * from user;

2 Permission Management#

1 Query Permissions

show grants for 'username' @ 'hostname';
show grants for 'username' @ '%';

2 Grant Permissions

# Grant permissions one by one
grant permission_list on database_name.table_name to 'username' @ 'hostname';
# Permission list: select, update, delete, insert
# Grant all permissions at once
grant all on *.* to 'username' @ 'hostname';

3 Revoke Permissions

# Revoke permissions one by one
revoke permission_list on database_name.table_name from 'username' @ 'hostname';
# Revoke all at once
revoke all on *.* from 'username' @ 'hostname';

7 Constraints#

As the name suggests, it regulates what can be stored and what cannot. There are three main categories:

1 Entity Integrity Constraints:

2 Domain Integrity Constraints

3 Referential Integrity Constraints:

Also known as foreign key constraints, the advantage is that it can ensure database integrity, but the disadvantage is that it is slow and has low performance. Syntax:

# Add when creating a table
constraint foreign key (foreign_key_field) references another_table_name(another_table_primary_key)
# Add foreign key after creating a table
alter table table_name add constraint FK_ID foreign key(your_foreign_key_field_name) REFERENCES foreign_table_name(corresponding_table_primary_key_field_name);

4 Delete Constraints:

• Delete Not Null Constraint:

# Change name, type, and constraint
alter table table_name change column_name new_column_name new_data_type new_constraint; 
# Only change the column's data type
alter table table_name modify column_name new_data_type new_constraint;  
# That is, re-specify the column's attributes, removing the constraint
alter table stu modify id int;

• Delete Unique Constraint:

alter table stu drop index column_name;

• Delete Primary Key Constraint:

alter table stu drop primary key;

8 Normalization#

Normalization: Normalization refers to the standards that need to be followed when designing a database.

1 Classification of Normal Forms

2 Functional Dependency

• Complete Functional Dependency: Can uniquely determine the latter from the former, such as A-->B or (A,B)-->C.
• Partial Functional Dependency: (A,B)-->C but A-->C, thus it is partially functionally dependent.
• Transitive Functional Dependency: A-->B and B-->C, thus A-->C, this is transitive dependency.
• Key: If in a table, an attribute or a group of attributes is fully dependent on all other attributes, then that attribute is called the key of that table.
• Primary Attribute: All attributes in the key attribute group.
• Non-primary Attribute: Other attributes outside the key attribute group.

9 Database Backup and Restoration#

Save the database as an SQL file and import the SQL file.

# Backup:
mysqldump -u username -p password database_name > D:\\xxx.sql;
# Restore, a database needs to exist,
create database xxx;
use xxx;
source D:\\xxx.sql;

10 Transactions#

A transaction is to integrate these operations into a whole; either all execute or none execute. If an exception occurs during the transaction, it will roll back to the beginning of the transaction. If there are no issues, the transaction will be committed.

Each time an SQL statement is executed, a transaction is automatically started. The reason we manually start a transaction is to break this default single-row statement transaction and make multiple-row statements atomic operations.

Start Transaction

Check and set whether to auto-commit:

# Check if it is auto-commit
select @@autocommit; 
# If the result is 1-----auto-commit, MySQL default
# If the result is 0-----manual commit, Oracle default
# Set auto-commit
set @@autocommit; 

Four Major Characteristics of Transactions

1 Atomicity: A transaction is the smallest indivisible unit, meaning it is a whole; either all succeed or all fail, i.e., the transaction runs as a whole.

2 Consistency: The consistency of a transaction means that the total amount of the transaction remains unchanged before and after the transaction operation. For example, in a transfer, the total amount before and after the transfer remains unchanged.

3 Isolation: Multiple transactions are independent of each other, but some mutual influences may occur, requiring transaction isolation levels to limit them.

4 Durability: Once a transaction is committed or rolled back, the data will be persistently saved.

Transaction Isolation Levels

Multiple transactions are isolated and independent of each other, but multiple transactions operating on the same batch of data can cause some issues. Different isolation levels are set to solve these problems:

1. Dirty Read: A transaction reads data that has not been committed by another transaction.

2. Non-repeatable Read (Phantom Read): In the same transaction, two reads yield different data because someone modified the data during the read.

3. Phantom Read: A transaction operates (DML: insert, delete, update) on all records in a data table, while another transaction adds a new record, making the first transaction unable to see its own operation results, as if a phantom appeared. However, MySQL has handled this and will not produce phantom reads.

Isolation Levels

The isolation level increases from low to high, safety increases but efficiency decreases.

Query Isolation Level

select @@tx_isolation
# The result is as follows: MySQL default
# +-----------------+
# | @@tx_isolation  |
# +-----------------+
# | REPEATABLE-READ |
# +-----------------+

Set Isolation Level

# Global setting
set global transaction isolation level level_string; 
# Session setting
set session transaction isolation level level_string;

12 Views#

Virtual tables, but like real tables.

Advantages: Simple, high security, users can only see data within the view.

Disadvantages:

• View data comes from the database; if the database data changes, it changes accordingly.
• Does not optimize query performance.
• If the view contains the following, it will not update:
  • Results of aggregate functions.
  • Results of distinct deduplication.
  • Results of group by.
  • Results of having filtering.
  • Results of union, union all merging.

Create View:

create view your_view_name as database_query_statement
# If there is a view with this name, it will be replaced, can be used as a modification statement
create or replace view your_view_name as database_query_statement
# Modify view
alter view view_name as query_statement;
# Delete view, deleting a view does not affect the original table
drop view view_name;

Operations on views can also be updated to the database, but generally, this is not done. For example, if the data source of the view is all content from one table, then modifications can be made, while if the data source of the view is the result of a join query, modifications cannot be made, which is easy to understand.