Neekko33

Basics of Database Operations#

1 - Database Operations#

1.1 Show Databases#

show databases;

1.2 Create Database#

create database [if not exists] `database_name` [character_set];

Note:

1. If the database being created already exists, an error will occur, so it is necessary to check for existence before creating the database;
2. If the database name is a keyword or contains special characters, an error will occur, and you need to enclose special characters and keywords in backticks;

1.3 Drop Database#

drop database [if exists] database_name;

1.4 Show SQL Statement for Creating Database#

show create database database_name;

1.5 Modify Database#

Modify the character set of the database:

alter database database_name charset=character_set;

1.6 Select Database#

use database_name;

2 - Table Operations#

2.1 Show All Tables#

show tables;

2.2 Create Table#

create table [if not exists] table_name(
	field_name data_type [null|not null] [auto_increment] [primary key] [comment]
	field_name data_type [default]
)engine=storage_engine

Create a simple table:

create table stu(
	id int,
	name varchar(30)
);

Create a complex table:

# If it contains Chinese
set names gbk;
# Create complex table
create table if not exists teacher(
		id int auto_increment primary key comment 'Primary Key',
  	name varchar(20) not null comment 'Name',
  	phone varchar(20) comment 'Phone Number',
  	address varchar(100) default 'Address Unknown' comment 'Address'
)engine=innodb;

Tip: Use database_name.table_name to create tables in other databases.

2.3 Show Create Table Statement#

show create table table_name [\G];

2.4 View Table Structure#

desc[ribe] table_name;

2.5 Drop Table#

drop table [if exists] table1, table2,...;

2.6 Modify Table#

alter table table_name

1. Add Field

alter table table_name add [column] field_name data_type [position];

Position:

• first: Add at the first position
• after: Add after the xx field

2. Drop Field

alter table table_name drop [column] field_name;

3. Modify Field (Name and Type)

alter table table_name change field_name new_field_name data_type...

4. Modify Field (Without Renaming)

alter table table_name modify field_name field_attributes...

5. Modify Engine

alter table table_name engine=engine_name;

6. Rename Table

alter table table_name rename to new_table_name;

2.7 Copy Table#

create table table_name select field from old_table

Feature: Cannot copy the primary key of the parent table, can only copy the data of the parent table.

create table new_table like old_table

Feature: Can only copy the table structure, cannot copy table data.

3 - Data Operations#

3.1 Insert Data#

Insert a Single Record

insert into table_name (field1, field2,...) values (value1, value2,...);

1. You can insert partial fields, but non-null fields must be inserted.
2. Auto-increment fields do not need to be inserted; the database will automatically insert the incremented number.
3. Insert null for the value of the auto-increment column.
4. The order and number of inserted values must match the order and number of table fields, and the inserted fields can be omitted.
5. You can insert default values using default.

Insert Multiple Records

insert into table_name values (value1, value2,...),(value1, value2,...),...;

3.2 Update Data#

update table_name set field=value [where condition]

3.3 Delete Data#

delete from table_name [where condition]

3.4 Truncate Table#

truncate table table_name

**delete from table**** and **truncate table table** difference**:
delete from table: Traverses table records, deleting one by one;
truncate table table: Destroys the original table and creates a new table with the same structure, making this method more efficient for clearing the table;

3.5 Query Statement (Single Table Query)#

Syntax: select [options] column_name [from table_name] [where condition] [order by sorting] [group by grouping] [having condition][limit restriction]

3.5.1 Field Expression#

You can use as to alias (as can be omitted).

3.5.2 From Statement#

from is followed by the data source, which can contain multiple sources, returning a Cartesian product.

3.5.3 Dual Table#

The dual table is a pseudo table. In certain specific cases, when there is no specific table involved, but a table name is required to ensure the completeness of the select statement, the pseudo table is used.

3.5.4 Where Clause#

MySQL supported operators: >, <, >=, <=, =, !=, and, or, not
The condition statement follows where, returning records where the condition is true.

3.5.5 In|Not In#

Multiple condition queries can be implemented using the in statement:

select * from stu where stuaddress in ('Beijing','Shanghai');

3.5.6 Between...And|Not Between...And#

Find records within a certain range:

select * from stu where stuage between 18 and 20;

3.5.7 Is Null|Is Not Null#

Check for null:

select * from stu where ch is null;

3.5.8 Aggregate Functions#

1. sum(): Sum
2. avg(): Average
3. max(): Maximum
4. min(): Minimum
5. count(): Count of records

3.5.9 Wildcards#

1. _ [underscore] represents any one character
2. % represents any character

3.5.10 Fuzzy Query (Like)#

Query students with the surname Zhang in the student table:

select * from stu where stuname like 'Zhang_'

3.5.11 Order By Sorting#

• ASC: Ascending [default]
• DESC: Descending
• Multi-column sorting:

# Age ascending, score descending
select *,(ch+math) as 'Total Score' from stu order by stuage asc,(ch+math) desc;

3.5.12 Group By [Grouping Query]#

Group the query results, the purpose of grouping queries is to summarize data.

# Group by gender, showing the average age of each group
select avg(stuage) as 'Average Age', stusex from stu group by stusex;

Note:

1. If it is a grouping query, the queried fields must be grouping fields and aggregate functions;
2. If the queried field is a normal field, only the first value of the group is taken;
3. You can use the group_concat() function to concatenate values of the same group for display.
4. After grouping, the structure is sorted in ascending order by default; you can use desc for descending order (just add desc).

Multi-column Grouping:

# Group by gender and region, showing the average age of each group
select stuaddress, stusex, avg(stuage) as 'Average Age'

3.5.13 Having Condition#

The difference between where and having:
where filters the raw data, having filters the result set.

3.5.14 Limit#

Syntax: limit starting_position, display_length
Note:

1. The starting position can be omitted, defaulting to start from 0.
2. Limit can be used in update and delete statements.

3.5.15 Options in Query Statements#

There are two options in query statements:

1. all: Display all data [default]
2. distinct: Remove duplicate data from the result set.

3.6 Query Statement (Multi-table Query)#

3.6.1 Inner Join#

# Syntax One:
select column_name from table1 inner join table2 on table1.common_field=table2.common_field;
# Syntax Two:
select column_name from table1, table2 where table1.common_field=table2.common_field;

Note: If you want to display common fields, you must specify the table name.

3.6.2 Left Outer Join#

Using the left table as the standard, if the right table does not have corresponding records, null will fill the result.

select column_name from table1 left join table2 on table1.common_field=table2.common_field;

3.6.3 Right Outer Join#

Using the right table as the standard, if the left table does not have corresponding records, null will fill the result.

select column_name from table1 right join table2 on table1.common_field=table2.common_field;

3.6.4 Cross Join#

1. If there is no join expression, it returns a Cartesian product.
2. If there is a join expression, it is equivalent to an inner join.

3.6.5 Natural Join#

Automatically determines the join condition, which is judged by the same field name.
Natural join includes:

1. Natural inner join: natural join
2. Natural left outer join: natural left join
3. Natural right outer join: natural right join

Natural join conclusions:

1. Table joins are connected by fields with the same name.
2. If there are no fields with the same name, it returns a Cartesian product.
3. The results will be organized according to the following rules:
   • Retain one of the join fields.
   • Place the join field at the front.

3.6.6 Using()#

1. Used to specify join fields.
2. using() will also organize the join fields, with the same rules as natural join.

3.7 Subqueries#

3.7.1 Only One Return Value#

Syntax:

select statement where condition (select ... from table)

1. The outer query is called the parent query, and the query in parentheses is called the subquery.
2. The subquery provides the query condition for the parent query.
3. = is only applicable when the subquery returns one value.

3.7.2 In|Not In Subquery#

Used when the subquery returns multiple values.

3.7.3 Exists|Not Exists#

# If there are students with written exam scores greater than 80, display all students
select * from stuinfo where exists(select * from stumarks where writtenexam>=80);

3.7.4 Subquery Classification#

1. Scalar subquery: The subquery returns a single result.
2. Column subquery: The subquery returns a list of results.
3. Row subquery: The subquery returns a single row of results.

• Example: Query the highest scoring male and female students.

select stuname, stusex, ch from stu where (stusex, ch) in (select stusex, max(ch) from stu group by stusex);

4. Table subquery: The subquery returns results treated as a table.

• Note: If treating the subquery as a table, it must be given an alias.

3.8 Union#

3.8.1 Using Union#

Purpose: To vertically combine the result sets of multiple select statements.

select statement union [options] select statement union [options] select statement

3.8.2 Union Options#

1. all: Display all data.
2. distinct: Remove duplicate data [default].

3.8.3 Union Considerations#

1. The number of fields in the select statements on both sides of the union must be the same.
2. The field names in the select statements on both sides of the union can be different; the final result will use the field names from the first select statement.
3. The data types in the select statements on both sides of the union can be different.

4 - SQL Classification#

DDL (Data Definition Language) database definition language CREATE, ALTER, DROP
DML (Data Manipulation Language) data manipulation language SELECT, UPDATE, INSERT, DELETE
DCL (Data Control Language) database control language, used to set or change database user or role permissions.

5 - Introduction to Data Table Files#

• One database corresponds to one folder.
• One table corresponds to one or more files.
  • myisam: One table corresponds to three files (table structure, table data, table index).
  • innodb: One table corresponds to one table structure file.

6 - Character Set#

Character Set: A collection of binary encodings corresponding to characters during storage and transmission.

set names character_set_encoding

Column Attributes and Data Integrity#

1 - Data Types#

1.1 Data Types - Value Types#

1.1.1 Integer Types

Note:

1. Unsigned integers (unsigned) have no negative numbers, and the integer part is twice that of signed integers.
2. Integer types support display width (minimum display digits), for example, int(5), if the number of digits is less than 5, leading zeros are added. (Only effective when combined with zerofill).

1.1.2 Floating Point Types (Save Approximate Decimal Values)

Note:

1. Floating point declaration: float(M,D) double(M,D)
   • M: Total digits
   • D: Decimal digits
2. Floating point precision may be lost.

1.1.3 Fixed Point Numbers
Syntax: decimal(M, D)
Note:

1. Fixed point numbers are variable length, roughly every 9 digits use 4 bytes for storage. Fixed point numbers can save precise decimals because integers and decimals are stored separately, occupying more resources than floating point numbers.
2. Both fixed point and floating point numbers support display width and unsigned numbers.

1.2 Data Types - Character Types#

Note:

1. What is the difference between char(10) and varchar(10)?

   Similarities: Both can save up to 10 characters;
   Differences: char does not reclaim excess characters, varchar does reclaim excess characters; char is more efficient but wastes space, varchar saves space but is less efficient than char.

2. The maximum length of char is 255.

3. The theoretical length of varchar is 65535.

1.3 Data Types - Enumeration (enum)#

Characteristics:

1. MySQL's enumeration type is managed by integers, with the first value being 1, the second value being 2, and so on;
2. Enumeration types can be directly inserted as numbers.

Advantages:

1. Fast execution speed (numbers are faster than strings).
2. Restricts data, ensuring data integrity.
3. Saves space.

1.4 Data Types - Set#

1. The order of insertion in the set is different, but the displayed order is the same.
2. Cannot insert data that is not in the set.
3. Each element in the set is assigned a fixed number, arranged from left to right as 20, 21, 22...

1.5 Data Types - Date and Time#

1.6 Data Types - Boolean#

True and false correspond to 1 and 0 in the database.

Database Design#

1 - Basic Concepts of Database#

1. Relationship: Common fields between two tables.
2. Row: Also called a record or entity.
3. Column: Also called a field or attribute.
4. Data Redundancy: The same data stored in different places.
5. Data Integrity: Correctness + Accuracy = Data Integrity
   • Correctness: Correct data type.
   • Accuracy: Accurate data range.

2 - Relationships Between Entities#

2.1 One-to-One#

1. One record in the main table corresponds to one record in the sub-table.
2. Implementation: Establish a relationship between primary keys.
3. Question: Why separate two tables when a one-to-one relationship can be implemented with one table?
   • When there are many fields, querying requires retrieving a large amount of data, which is inefficient. Therefore, all fields can be divided into "common fields" and "uncommon fields," improving efficiency for most query users. [Vertical partitioning of tables]

2.2 One-to-Many#

1. One record in the main table corresponds to multiple records in the sub-table.
2. Implementation: Establish a relationship between primary keys and non-primary keys.

2.3 Many-to-Many#

1. One record in the main table corresponds to multiple records in the sub-table, and one record in the sub-table corresponds to multiple records in the main table.
2. Implementation: Establish a third table to save the relationship.

3 - Steps in Database Design#

3.1 Specific Steps in Database Design#

1. Collect information: Communicate and discuss with personnel related to the system to fully understand the tasks the database needs to accomplish.
2. Identify objects (entities) that the database needs to manage.
3. Identify the attributes of each entity.
4. Identify the relationships between objects.
5. Convert the model into a database.
6. Normalize.

3.2 Draw E-R Diagram (Entity-Relationship Diagram)#

E-R Diagram Syntax

3.3 Convert E-R Diagram to Tables#

4 - Data Normalization#

Practical experience shows that the third normal form is the most cost-effective.

4.1 First Normal Form: Ensure Atomicity of Each Column#

If each column is the smallest indivisible data unit (also known as the smallest atomic unit), then it meets the first normal form.
Note: Should the address containing province, city, county, and district be split?

• If it only serves the purpose of an address and does not require statistics, it can remain unsplit; if there is a need for statistical functions by region, then it should be split.
• In actual projects, it is recommended to split.

4.2 Second Normal Form: Non-key Fields Must Depend on Key Fields#

If a relationship meets the first normal form, and all columns except the primary key depend on that primary key, it meets the second normal form.
Note: The second normal form requires each table to describe only one thing.

4.3 Third Normal Form: Eliminate Transitive Dependencies#

If a relationship meets the second normal form, and all columns except the primary key do not transitively depend on the primary key column, it meets the third normal form.
Note: Transitive dependencies cannot exist in non-key fields.

Views#

1. A view is a virtual table that represents part of a table or a composite structure of multiple tables.
2. A view only has a table structure, not table data. The structure and data of the view are based on the table.

1 Create View#

Syntax:

create [or replace] view view_name as select statement

Note: Because a view is a table structure, creating a view will create a frm file with the view name in the database folder.

2 View the Structure of a View#

desc view_name;

3 View the Syntax for Creating a View#

show create view view_name\G;

4 Show All Views#

show tables;

select table_name from information_schema.views;

show table status where comment='view'\G;

5 Modify View#

alter view view_name as select statement;

6 Drop View#

drop view [if exists] view1, view2...;

7 Functions of Views#

1. Filter data to prevent unauthorized access to sensitive data.
2. Hide table structure.
3. Reduce the complexity of SQL statements.

8 View Algorithms#

Scenario: Find the male and female students with the highest Chinese scores.

select * from (select * from stu order by ch desc) as t group by stusex;

We can encapsulate the subquery into a view:

create view vw_stu as select * from stu order by ch desc;

Querying through the view:

select * from vw_stu group by stusex;

However, the result is different from the direct query above due to the view's algorithm:

1. merge: Merge algorithm, combines the view's statement with the outer statement before execution.
2. temptable: Temporary table algorithm, generates a temporary table for the view before executing the outer statement.
3. undefined: Undefined, which algorithm MySQL uses is determined by MySQL; generally, views will automatically use the merge algorithm for better efficiency.

Solution: Specify the algorithm when creating the view.

create algorithm=temptable view view_name as select statement;

Transactions#

1. A transaction is an indivisible unit of execution.
2. A transaction as a whole must either execute together or roll back together.

1 Transaction Operations#

# Start Transaction
start transaction or begin [work]
# Commit Transaction
commit
# Rollback Transaction
rollback

Example: Bank Transfer

start transaction
update bank set money=money-100 where cardid='1001'
update bank set money=money+100 where cardid='1002';
# If there is an error:
rollback;
# If successful:
commit;

Note:

1. A transaction is initiated when it starts, and both commit and rollback end the transaction.
2. Only innodb and BDB support transactions; myisam does not support transactions.

2 Set Rollback Points#

Syntax:

# Set Rollback Point
savepoint rollback_point_name
# Rollback to Rollback Point
rollback to rollback_point

2.3 Transaction Characteristics (ACID)#

1. Atomicity: A transaction is a whole; it cannot be divided; it must either execute together or not at all.
2. Consistency: When a transaction is completed, the data must be in a consistent state.
3. Isolation: Each transaction is isolated from others.
4. Durability: Once a transaction is completed, the changes to the data are permanent.

Indexes#

Advantages: Fast query speed.
Disadvantages:

1. Efficiency of data manipulation statements decreases.
2. Occupies space.

1 Types of Indexes#

1. Normal Index
2. Unique Index (Unique Key)
3. Primary Key Index: A primary key automatically creates a primary key index; no manual creation is needed.
4. Full-text Index, used by search engines; MySQL does not support full-text indexing in Chinese, which we solve through sphinx.

2 Create Normal Index#

# Method One to Create Index
create index index_name on table_name(field_name);
# Method Two to Create Index
alter table table_name add index [index_name](column_name);
# Add index when creating the table
create table emp(
	id int,
    name varchar(10),
    index ix_name(name) # Create index
);

3 Create Unique Index#

Syntax One: create unique index index_name on table_name(field_name);
Syntax Two: alter table table_name add unique [index] [index_name](column_name);
Syntax Three: Add unique index when creating the table, same as creating a unique key.

4 Drop Index#

drop index index_name on table_name;

5 Guidelines for Creating Indexes#

1. The column is used for frequent searches.
2. The column is used for sorting.
3. Common fields should have indexes created.
4. If the data in the table is very small, there is no need to create an index. The time to search the index in MySQL is longer than searching data one by one.
5. If there is only one different value in the data of a field, that field is not suitable for indexing, such as gender.

Built-in Functions#

1 Numeric Functions#

# Generate Random Number
select rand();
select * from stu order by rand() limit 2; # Randomly select two students

# Rounding (Round, Ceiling, Floor)
select round(number);
select ceil(number);
select floor(number);

# Truncate Number
select truncate(number, truncate_decimal_places);

2 String Functions#

# Convert to Uppercase/Lowercase
select ucase/lcase(string);

# Substring
select left/right(string, length);
select substring(string, start_position [starting from 1], length);

# String Concatenation
select concat(string...);

# String Replacement
coalesce(field1, field2); # If field is not null, display field1; otherwise, display field2.
select stuname, coalesce(writtenexam, 'Absent') from stuinfo natural left join stumarks; # If written exam score is null, display 'Absent'.

# String Length
select length(string); # Byte length
select char_length(string); # Character count

3 Time Functions#

# Get Current Timestamp
select unix_timestamp();

# Convert Timestamp to Time Format
select from_unixtime(unix_timestamp());

# Get Current Time
select now();

# Date Difference
select datediff(now(),'2008-8-8') # How many days from the current date to 2008-08-08.

# Convert Format
select cast(now() as time);
select convert(now(), time);

4 Encryption Functions#

select md5(string);
select sha(string);

5 Conditional Functions#

select stuname, if(ch>=60 && math>=60, 'Pass', 'Fail') 'Pass Status' from stu;

Prepared Statements#

Precompiled once, can be executed multiple times. Used to solve the problem of frequently executing a single SQL statement.

# Prepare Statement
prepare prepared_name from prepared_statement;

# Execute Prepared Statement
execute prepared_name [using variable];

# Declare Variables in MySQL
set @id=value;

Stored Procedures#

1 Advantages of Stored Procedures#

1. Stored procedures can reduce network traffic.
2. Allow modular design.
3. Support transactions.

2 Create Stored Procedure#

create procedure procedure_name(parameter)
	begin
	sql...
	end;

3 Call Stored Procedure#

call procedure_name;

4 Drop Stored Procedure#

drop procedure [if exists] procedure_name;

5 View Information About Stored Procedures#

show create procedure procedure_name\G;

6 Show All Stored Procedures#

show procedure status\G;

7 Parameters of Stored Procedures#

Stored procedure parameters are divided into: input parameters (in) [default], output parameters (out), input-output parameters (inout).
Stored procedures cannot use return to return values; to return values, you can only pass values through "output parameters."
Example 1: Input Parameter

create procedure proc(in param varchar(10))
select * from stuinfo where stuno=param;

call proc('s25301');

Example 2: Find Deskmates

create procedure proc(in name varchar(10))
begin
declare seat tinyint;
select stuseat into seat from stuinfo where stuname=name;
select * from stuinfo where stuseat=seat+1 or stuseat=seat-1;
end;

Summary:

1. Use the declare keyword to declare local variables; global variables start with @.
2. There are two ways to assign values to variables:
   Method One: set variable_name=value;
   Method Two: select field into variable from table where condition;
3. Declared variables cannot have the same name as column names.

Example 3: Output Parameter

create procedure proc(num int, out result int)
begin
set result=num*num;
end;

call proc(10, @result);

select @result;

Example 4: Input-Output Parameter

create procedure proc(inout num int)
begin
set num=num*num;
end;

set @num=10;

call proc(@num);

select @num;