Mad YYDS MySQL Learning Notes

#MySQL

MySQL is a relational database management system

5.7 ->Stability
8.0 -> ~

###my.ini

[mysqld]

port=3306

basedir=D:\Program Files\mysql

datadir=D:\Program Files\mysql\data

max_connections=200

max_connect_errors=10

character-set-server=utf8

default-storage-engine=INNODB

default_authentication_plugin=mysql_native_password
[mysql]

default-character-set=utf8
[client]
port=3306

default-character-set=utf8

Create a database

Base character set: utf-8
Database collation: utf8 - general-ci
####1. Connect to the database

mysql -uroot -p123456 -- Connect to database

update mysql.user
set authentication_string=password('123456')
where user = 'root'
  and Host = 'localhost';  -- Modify user password

flush privileges ;  -- Refresh Permissions
-- -----------------------------------------
-- All statements use;Ending
show databases ; -- View all databases

mysql>use school -- Switch database use
Database changed

show tables ; -- View all tables in the database
describe student; -- Display information for all tables in the database

create database whlll; -- Create a database

exit;
-- Single-Line Comments
/*(Multiline comments)
Hello
whlll
Bye!
 */

Database xxx language (CRUD add delete check!)
DDL Definition
DML Operation
DQL Query
DCL Control
####2. Operating the database

Operations Database - > Operations Database Table - > Operations Database Table Data
###2.1 Operation Database (Understanding)

  1. Create a database
create database [if not exists] whlll 
  1. Delete database
drop database [if exists] whlll 
  1. Use database
-- If your table or field name is a special character you need to take ``
use `school`;
select `user` from student;
  1. view the database
show databases -- View all databases

Learning ideas:

  • View sql against sqlyog visual history
  • Fixed grammar or keywords help memory

Column type of ###2.2 database

numerical value

  • tinyint very small data byte
  • Smllint smaller data 2 bytes
  • Meumint medium size data 3 bytes
  • int standard integer 4 bytes
  • bigint Big Data 8 Bytes
  • float floating point 4 bytes
  • double 8 bytes floating point number (accuracy problem!)
  • Decimal is commonly used in floating point financial calculations as a decimal string

Character string

  • char string fixed size 0-255
  • Commonly used varchar variable strings 0-65535
  • tinytext minitext 2^8-1
  • text Text String 2^16-1 Save Large text

Time Date

  • date YYYY-MM-DD, date
  • time HH:mm:ss time format
  • datetime YYYY-MM-DD HH:mm:ss Most commonly used time format
  • Timstamp, milliseconds since 1970.1.1!More common
  • Year year representation

null

  • No value, unknown
  • Do not use null values for calculations

Field Properties for ##2.3 Database (Key)

Unsigned:

  • Unsigned integer
  • Declares that the column cannot be declared negative

zerofill:

  • 0 Filled
  • Insufficient digits, use 0 to fill in int(3) -> 5-> 005

Self-increasing

  • Usually understood as self-increasing, based on the previous record + 1 (default)
  • Usually used to design unique primary keys, must be of integer type
  • You can customize the starting value and increment of the primary key auto-increment

Non-empty NULL not null

  • Assume it is set to not null, if no value is assigned, an error will be reported!
  • NULL, defaults to null if no value is filled in

Default:

  • Set Default Value
  • eg:sex defaults to male
Each table must have the following five fields -> project
id Primary key
version Optimistic Lock
is_delete Pseudo Delete
gmt_create Creation Time
gmt_update Modification Time

###2.4 Create database tables

-- Goal: Create a school data base
-- Create Student Table(Column, Field) Use SQL Establish
-- School Number int Logon Password varchar(20) Name, gender varchar(2),Birthday Date(datetime),Home address, email

-- Notes, in English() , Use table names and fields whenever possible `` Enclosed
-- AUTO INCREMENT Self-increasing
-- Strings are enclosed in single quotes!
-- All statements followed by,(English) ,Last without adding
CREATE TABLE IF NOT EXISTS `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Age id',
    `gradename` VARCHAR(50) NOT NULL COMMENT 'Age Name',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

CREATE TABLE IF NOT EXISTS `student`
(
    `id`   INT(4)      NOT NULL AUTO_INCREMENT COMMENT 'School Number',
    `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name',
    `pwd`  VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'Password',
    `sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
    `birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth',
    `address` VARCHAR(100) DEFAULT NULL COMMENT 'Home Address',
    `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

format

CREATE TABLE [IF NOT EXISTS] `Table Name`(
    `Field name` Column type [attribute] [Indexes] [Notes],
    `Field name` Column type [attribute] [Indexes] [Notes],
    ......
    `Field name` Column type [attribute] [Indexes] [Notes]
) [Table type][Character Set Settings][Notes]

SHOW CREATE DATABASE school -- View statement to create database
SHOW CREATE TABLE student -- See student Definition statements for data tables
DESC student -- Show the structure of the table

Type of ###2.5 Datasheet

-- About Database Engine
/*
 INNODB Default Use
 MYISAM Used in earlier years
 */
MYISAMINNODB
Transaction supportI won't support itSupport
Data row lockingI won't support itSupport
Foreign Key ConstraintsI won't support itSupport
Full-text IndexSupportI won't support it
Table space sizelessLarger, about twice as large

General usage operations:

  • MYISAM saves space and is fast
  • INNODB high security, transaction processing, multi-table, multi-user operation

Existing location of physical space
All database files exist in the data directory, and a folder corresponds to a database
Essentially storage of files

MySQL Engine Differences on Physical Files

  • INNODB has only one *.frm file in the database table and an ibdata1 file in the parent directory
  • MYISAM corresponding file
    • *.frm - Definition file for table structure
    • *.MYD Data File (data)
    • *.MYI Index File (index)

Set Character Set Encoding for Database Tables

CHARSET=utf8

No Settings - > Default - > Chinese is not supported
Set default encoding in my.ini

character_set_server=utf8

###2.6 Modify Delete Table

modify

-- Modify Table ALTER TABLE Old table name RENAME AS New table name
ALTER TABLE teacher RENAME AS teacher1;
-- Add Field to Table ALTER TABLE Table Name ADD Field Name Column Properties
ALTER TABLE teacher1 ADD age INT(11);
-- Modify the fields of the table(Rename, modify the constraint!)
-- ALTER TABLE Table Name MODIFY Field Name Column Properties[]
ALTER TABLE teacher1 MODIFY age VARCHAR(11); -- Modify Constraints
-- ALTER TABLE Table Name CHANGE Old Name New Name Column Properties[]
ALTER TABLE teacher1 CHANGE age age1 INT(1); -- Field Rename
    
-- Delete field from table
ALTER TABLE teacher1 DROP age1;

delete

-- Delete Table
DROP TABLE IF EXISTS teacher1

All creation and deletion should be judged as much as possible to avoid error~
Note:

  • ```Field name, use this package!
  • Comments -/**/
  • sql keyword case insensitive - > recommended lower case
  • All symbols in English

MySQL Data Management

###3.1 Foreign Key (Learn)

Mode 1. Increase constraints when creating tables (cumbersome, more complex)

CREATE TABLE IF NOT EXISTS `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Age id',
    `gradename` VARCHAR(50) NOT NULL COMMENT 'Age Name',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
    
-- Student Table gradeid The field is to refer to the grade table's gradeid
-- Define foreign keys key
-- Add a constraint to this foreign key(Execute Reference) references Quote
CREATE TABLE IF NOT EXISTS `student`
(
    `id`   INT(4)      NOT NULL AUTO_INCREMENT COMMENT 'School Number',
    `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name',
    `pwd`  VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'Password',
    `sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
    `gradeid` INT(10) NOT NULL COMMENT 'Grade of the student',
    `birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth',
    `address` VARCHAR(100) DEFAULT NULL COMMENT 'Home Address',
    `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
    PRIMARY KEY (`id`),
    KEY `FK_gradeid` (`gradeid`),
    CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

When deleting tables with foreign key relationships, you must delete tables that refer to others (from tables) and delete referenced tables (primary tables).

Mode 2: Add foreign key constraints after successful table creation

CREATE TABLE IF NOT EXISTS `grade`(
    `gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'Age id',
    `gradename` VARCHAR(50) NOT NULL COMMENT 'Age Name',
    PRIMARY KEY(`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8
    
-- Student Table gradeid The field is to refer to the grade table's gradeid
-- Define foreign keys key
-- Add a constraint to this foreign key(Execute Reference) references Quote
CREATE TABLE IF NOT EXISTS `student`
(
    `id`   INT(4)      NOT NULL AUTO_INCREMENT COMMENT 'School Number',
    `name` VARCHAR(30) NOT NULL DEFAULT 'anonymous' COMMENT 'Full name',
    `pwd`  VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT 'Password',
    `sex` VARCHAR(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
    `gradeid` INT(10) NOT NULL COMMENT 'Grade of the student',
    `birthday` DATETIME DEFAULT NULL COMMENT 'Date of birth',
    `address` VARCHAR(100) DEFAULT NULL COMMENT 'Home Address',
    `email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
    PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- There was no foreign key relationship when the table was created
ALTER TABLE `student`
    ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE surface ADD CONSTRAINT Constraint Name FOREIGN KEY(Columns as foreign keys) REFERENCES Which table(Which field);

These operations are all physical foreign keys, database-level foreign keys, we do not recommend using! (Avoid problems with too many databases)

Best Practices

  • A database is simply a table that holds data, only rows (data) and columns (fields)
  • We want to use data from multiple tables, we want to use foreign keys (program implementation)
    ###3.2 DML Language (remember all)
    Database meaning: data storage, data management
    DML Language: Data Operation Language
  • insert
  • update
  • delete
    ###3.3, Add

insert

-- Insert statement(Add to)
-- insert into Table Name([Field Name 1, Field 2, Field 3])values('Value 1'),('Value 2'),('Value 3'),...
INSERT INTO `grade`(`gradename`) VALUES('Senior')

-- We can omit this because the primary key increases itself(If you don't write the fields of the table, they will match one another)
INSERT INTO `grade` VALUES('Junior')

-- Generally write insert statements, we must have data and fields one-to-one correspondence!

-- Insert Multiple Fields
INSERT INTO `g`student`rade`(`gradename`) VALUES('Sophomore'),('Freshman')


INSERT INTO `student`(`name`) VALUES ('Zhang San')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('Zhang San','aaaaaa','male')

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('Li Si','aaaaaa','male'),('King Five','aaaaaa','male')

Syntax: insert into table name ([field name 1, field 2, field 3])values('value 1'), ('value 2'), ('value 3'),...

Matters needing attention:

  1. Separate fields with English commas
  2. Fields can be omitted, but subsequent values must be one-to-one
  3. Multiple data can be inserted at the same time, the value after VALUES, need to use, separated VALUES(), ()

###3.4, Modification

update Modifies Who (Conditions) set Original Value = New Value

-- Modify Student Name,With conditions
UPDATE `student` SET `name`='whlll' WHERE id=1;

-- Without specifying a condition,All tables will be changed!
UPDATE `student` SET `name`='The Yangtze River seven'

-- Modify multiple attributes
UPDATE `student` SET `name`='whlll',`email`='whlll0305lhl@gmail.com' WHERE id=1;

-- Grammar:
-- UPDATE Table Name set column_name = value[column_name=value,...] where [condition]

Conditions: where clause operator id is equal to a value, greater than a value, and modified within an interval...

OperatorSignificanceRangeResult
=Be equal to5=6false
<>or!=Not equal to5<>6true
BETWEEN...and...[...] Closed interval[2,5]
ANDand5>1 and 1>2false
ORor5>1 or 1>2true
-- Locating data through multiple conditions
UPDATE `student` SET `name`='Galaxy Fleet' WHERE `name`='whlll' AND sex='female'

Syntax: UPDATE table name set column_name = value[column_name=value,...] where [condition]
Be careful:

  • column_name is a column in the database, try bringing ````
  • Condition, filter condition, if not given, all columns will be modified
  • Value is either a specific value or a variable
  • Separate multiple set attributes with English commas
UPDATE `student` SET `birthday`=CURRENT_TIME WHERE `name`='Galaxy Fleet' AND sex='female'

###3.5, Delete

delete command

Syntax: delete from table name [where condition]

-- Delete data(Avoid writing like this) All will be deleted
DELETE FROM `student`;
-- Delete specified data
DELETE FROM `student` WHERE id=1

TRUNCATE command
What it does: Clear a database table completely, and its structure and index constraints will not change!

-- empty student surface
TRUNCATE `student`

The difference between delete and TRUNCATE

  • Same thing: you can delete data without deleting table structures
  • Different:
    • TRUNCATE Reset Self-Increasing Column Counter Zero
    • TRUNCATE does not affect transactions
-- test delete and TRUNCATE Difference
CREATE TABLE `test`(
`id` INT(4) NOT NULL AUTO_INCREMENT,
`coll` VARCHAR(20) NOT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3');

DELETE FROM `test`; -- Will not affect self-growth

TRUNCATE TABLE `test` -- Self-increasing returns to zero

Know it: DELETE deletion problems, restart the database, phenomena

  • INNODB self-incrementing columns start from 1 (in-memory, power-off is lost)
  • MyISAM continues from the previous increment (power outages will not be lost if files exist)

##4, DQL Query Data (Most Important)

SELECT Syntax

SELECT [ALL | DISTINCT]
{* | table.* | [table.field1[as alias1][,table.field2[as alias2]][,...]]}
FROM table_name [as table_alias]
  [left | right | inner join table_name2]  -- Joint Query
  [WHERE ...]  -- Specify the conditions for the results to be met
  [GROUP BY ...]  -- Specify which fields the results will be grouped by
  [HAVING]  -- Secondary conditions that must be met to filter grouped records
  [ORDER BY ...]  -- Specify that query records are sorted by one or more criteria
  [LIMIT {[offset,]row_count | row_countOFFSET offset}];
   -- Specify which records to query from

###4.1,DQL
(Data Query LANGUAGE: Data Query Language)

  • Use Slect for all query operations
  • Simple queries, complex queries it does~
  • _uThe Core Language, the Most Important Statement in a Database
  • The most frequently used statement
-- Query all students SELECT field FROM surface
SELECT * FROM student;
  
-- Query specified fields
SELECT `StudentNo`,`StudentName` FROM student; 
  
-- Alias gives the result a name AS You can alias fields or tables
SELECT `StudentNo` AS School Number,`StudentName` AS Student Name FROM student AS s

-- function Concat(a,b)
SELECT CONCAT('Full name',StudentName) AS New name FROM student

Syntax: SELECT field...FROM table

Sometimes, column names are not so well understood, alias AS (field name AS alias)

De-distinct
Role: Remove duplicate data from the results of SELECT queries, showing only one duplicate data

SELECT DISTINCT `StudnetNo` FROM result; -- Duplicate data deduplication found

Columns (expressions) in Databases

SELECT VERSION() -- Query System Version(function)

SELECT 100*3-1 AS Calculation results -- For calculation(Calculate expression)

SELECT @@auto_increment_increment -- Query Self-Increasing Step(variable)

-- Student Test Results +1 Subview
SELECT `StudentNo`,`StudentResult`+1 AS 'After scoring' FROM result

Expressions in the database: text values, columns, nulls, functions, calculation expressions, system variables...

select expression from table

###4.3, where conditional clause
Role: Retrieve eligible values from data

The search criteria consist of one or more expressions!Result Boolean

Logical operators

operatorgrammardescribe
and &&a and bLogical and
ora or bLogical or
Not !not aLogical NOT

Use English letters whenever possible

-- =====================where===========================

SELECT studentNo,`StudentResult` FROM result

-- Query exam results at 95-100 Between parts
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>95 AND StudentResult<=100


-- and &&
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult>95 && StudentResult<=100


-- Fuzzy Query (Section)
SELECT studentNo,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100

-- Except for Class 1000
SELECT studentNo,`StudentResult` FROM result
WHERE studentNo!=1000;

-- != not
SELECT studentNo,`StudentResult` FROM result
WHERE NOT studentNo = 1000

Fuzzy Query: Comparison Operators

operatorgrammardescribe
IS NULLa is nullIf the operator is NULL, the result is true
IS NOT NULLa is not nullIf the operator is not null, the result is true
BETWEENa between b and cIf a is between b and c, the result is true
Likea like bSQL matches, if a matches b, the result is true
Ina in (a1,a2,a3...)Assume A is in either a1 or a2... and the result is true
-- ==============Fuzzy Query===================

-- Query a classmate named Liu
-- like Combination %(Represents 0 to any character) _(A character)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu%'

-- Query a classmate named Liu who has a word after his name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu_'

-- Query a classmate named Liu who has two words after his name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu__'


-- Query classmates with Jia in the middle of their names
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%Jia%'


-- ============== in (Specific one or more values)==========
-- Query 10001 1002 1003 students
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);

-- Query students in Beijing
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Adress` IN ('Hangzhou, Zhejiang','Changde, Hunan')

-- ==============null not null=======================

-- Query for students with empty addresses null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address='' OR address IS NULL

-- Query classmates with birth date
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `birthday` IS NOT NULL


-- Query for classmates with no birth date
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `birthday` IS NULL

###4.4, join table query

JOIN comparison

-- =============Joint Table Query join =====================

-- Query the students who took the exam(School number, name, subject number, score)
SELECT * FROM student
SELECT * FROM result


/*
thinking
1.Analyze requirements, which tables the fields of the query come from (join queries)
2.Determine which join query to use
 Determine the intersection (which data in the two tables is the same)
Criteria for judgment: studentNo in the student table = studentNo on the score table
*/

SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM stdent AS s
INNER JOIN result AS r
WHERE s.studentNO = r.studentNO

-- Right Join
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.studentNO = r.studentNO

-- Left Join
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNO = r.studentNO
operationdescribe
Inner JoinReturns a value if there is at least one match in the table
Right JoinAll values are returned from the right table even if there is no match in the left table
Left JoinAll values are returned from the left table even if there is no match in the right table
-- =============Joint Table Query join =====================

-- Query the students who took the exam(School number, name, subject number, score)
SELECT * FROM student
SELECT * FROM result


/*
thinking
1.Analyze requirements, which tables the fields of the query come from (join queries)
2.Determine which join query to use
 Determine the intersection (which data in the two tables is the same)
Criteria for judgment: studentNo in the student table = studentNo on the score table
*/

-- join(Joined table) on(Judgement) Join Query
-- where Equivalent Query



SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM stdent AS s
INNER JOIN result AS r
WHERE s.studentNO = r.studentNO

-- Right Join
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
RIGHT JOIN result r
ON s.studentNO = r.studentNO

-- Left Join
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNO = r.studentNO


-- Query students who are absent
SELECT s.studentNO,studentName,SubjectNo,StudentResult
FROM student s
LEFT JOIN result r
ON s.studentNO = r.studentNO
WHERE StudentResult IS NULL


-- Queried the information of the students taking the exam: school number, student name, subject name, score

SELECT s.studentNo,studentName,SubjectName,`StudentResult`
FROM student s
RIGHT JOIN result r
ON s.studentNo = r.studentNo
INNER JOIN `subject` sub
ON r.subjectNo = sub.subjectNo

-- What data do I want to query select ...
-- Which tables to check from FROM surface xxx Join Joined tables on Intersection Conditions
-- Assume that there is a slow multitable query, querying two tables before slowly increasing

-- From a left join b
-- From a right join b

Self-connection

Your own table is joined to your own table, core: one table is split into two identical tables

Parent Class:

categoryidcategoryName
2information technology
3software development
5Art Design

Subclass:

pidcategoryidcategoryName
34data base
28Office Information
36web development
57ps Technology

Operation: Query the parent's subclass relationship

Parent ClassSubclass
information technologyOffice Information
software developmentdata base
software developmentweb development
Art Designps Technology
-- Query parent-child information to see one table as two identical tables
SELECT a.`categoryName` AS 'Parent Column',b.`categoryName` AS 'Subcolumn'
FROM `category` AS a,`category` AS b
WHERE a.`categoryid`=b.`pid`
  
-- Query the grade to which the student belongs (number, student's name, grade name)
SELECT studentNo,studentName,`GradeName`
FROM student s
       INNER JOIN `grade` g
                  ON s.`GradeID`=g.`GradeID`
  
-- Query the grade of the account
SELECT `SubjectName`,`GradeName`
FROM `subject` sub
       INNER JOIN `grade` g
                  ON sub.`GradeID`=g.`GradeID`

###4.5, Paging and Sorting

sort

-- ============paging limit sort order by==================

-- Sort: Ascending ASC   Descending order DESC

--The results of the query are sorted in descending order according to the results
SELECT s.`Student`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject`=sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = 'database structure-1'
ORDER BY StudentResult ASC

paging

-- Relieving database pressure for a better experience of waterfall flow

-- Page breaks with five bars per page
-- Grammar: limit Current page size
-- Page application: total page size of the current page
-- limit 0,5   1~5
-- limit 1,5   2~6
-- limit 6,5   6~10
SELECT s.`Student`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject`=sub
ON r.`SubjectNo` = sub.`SubjectNo`
WHERE subjectName = 'database structure-1'
ORDER BY StudentResult ASC
LIMIT 5,5

-- First page limit 0,5
-- Page 2 limit 5,5
-- Page 3 limit 10,5
-- No. N page limit (n-1)*pageSize,5


SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM `student` s 
INNER JOIN `result` r 
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON sub.`SubjectNo` = r.`SubjectNo`
WHERE SubjectName = 'JAVA' AND StudentResult>=80
ORDER BY StudentResult DESC 
LIMIT 0,10

Syntax: limit (start subscript, pageSize)

###4.6, subquery

Where (this value is calculated)

Essential: Nesting a subquery statement within a where statement

-- =============== where =================

-- 1.Query database structure-1 All test results (number, subject number, result) are in descending order
-- Mode 1: Use join query
SELECT `StudentNo`,r.`SubjectNo`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sub.SubjectNo
WHERE SubjectName = 'database structure-1'
ORDER BY StudentResult DESC

-- Mode 2: Use subqueries(From inside to outside)
SELECT `StudentNo`,`SubjectNo`,`StudentResult`
FROM `result`
WHERE SubjectNo=(
SELECT SubjectNo FROM `subject`
WHERE SubjectName = 'database structure-1'
)


-- Number and name of a student with a score of not less than 80
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE `StudentResult`>=80

-- On this basis, add a subject called Advanced Mathematics-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE `StudentResult`>=80 AND ·`SubjectNo`= (
SELECT subjectNo FROM `subject` 
WHERE `SubjectName`='Advanced mathematics-2'
) 

-- Re-reform (From inside to outside)
SELECT DISTINCT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN (
SELECT StudentNo FROM result WHERE StudentResult>80 AND SubjectNo=(
SELECT SubjectNo FROM `subject` WHERE `SubjectName`='Advanced mathematics-2'
)
)


-- Query course is advanced mathematics-2 The number and name of a student who scored no less than 80
SELECT s.StudentNo,StudentName
FROM student s
INNER JOIN result r
ON s.StudentNo=r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjecNo` = sub.`SubjectNo`
WHERE `SubjectName` = 'Advanced mathematics-2' AND StudentResult>=80

###4.7, Grouping and Filtering

-- Query average, highest, lowest, average of different courses over 80
-- Core: (grouped according to different courses)
SELECT SubjectName, AVG(StudentResult) AS Average,MAX(StudentResult) AS Top Score,MIN(StudentResult) AS Minimum score
FROM result r
INNER JOIN `subject` sub
ON r.`SubjectNo` = sub.`SubjectNo`
GROUP BY r.SubjectNo -- By which field to group
HAVING Average>80

###4.8, SELECT summary

Query - > Cross-table Cross-database Cross-region (eg. Taobao merchandise page)

##5, MySQL function

###5.1, Common Functions

-- ======================= Common Functions ========================

-- Mathematical operations
SELECT ABS(-8)  -- absolute value
SELECT CEILING(9.4) -- ceil
SELECT RAND() -- Return a 0-1 Random number between
SELECT SIGN() -- Judging the sign 0 of a number-0 negative->-1 Positive number->1

-- Character string
SELECT CHAR_LENGTH() -- String Length
SELECT CONCAT('I','love','you') -- Split String
SELECT INSERT('I love SQL',1,2,'Super love') -- Query, replacing a length from a location
SELECT LOWER('WHLLL')
SELECT UPPER('whlll')
SELECT INSTR('whlll','h')
SELECT REPLACE('whooo','o','l')
SELECT SUBSTRING('whlll never give up',1,5)
SELECT REVERSE('LiHanlU')


-- Query for the name of Zhou's classmate Zou
SELECT REPLACE(studentNmae,'week','Zou') FROM student
WHERE studentname LIKE 'week%'

-- Time and Date Functions
SELECT CURRENT_DATE() -- Get the current date
SELECT CURDATE() -- Get the current date
SELECT NOW() -- Get the time of the current 1
SELECT LOCALTIME() -- Local Time
SELECT SYSDATE() -- system time

SELECT YEAR(NOW())
SELECT MONTH(NOW())
SELECT DAY(NOW())
SELECT HOUR(NOW())
SELECT MINUTE(NOW())
SELECT SECOND(NOW())

-- system
SELECT USER()
SELECT VERSION()

###5.2, aggregate function (common)

Function Namedescribe
COUNTcount
SUM()Summation
AVG()average value
MAX()Maximum
MIN()minimum value
......
-- ================= Aggregate function  ==================
-- Can all count the data in the table
SELECT COUNT(studentname) FROM student; -- Count(Specify Columns),Will ignore all null value
SELECT COUNT(*) FROM student; -- Count(*) -- Will Not Ignore null value
SELECT COUNT(1) FROM result; -- Count(1) -- Will Not Ignore null value

SELECT SUM(`StudentResult`) AS The sum FROM result
SELECT AVG(`StudentResult`) AS Average FROM result
SELECT MAX(`StudentResult`) AS Top Score FROM result
SELECT MIN(`StudentResult`) AS Minimum score FROM result

###5.3, database-level MD5 encryption

What is MD5?

Major enhancements are algorithm complexity and irreversibility

MD5 is not reversible, the MD5 of the specific value is the same

How MD5 cracks websites, there is a dictionary behind it, the value encrypted by MD5 - > the value encrypted before

-- ================= test MD5 encryption ==================

CREATE TABLE `testmd5`(
`id` INT(4) NOT NULL,
`name` VARCHAR(20) NOT NULL,
`pwd` VARCHAR(50) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- enable password
INSERT INTO testmd5 VALUES(1,'zhangsan','123456'),(2,'lisi','123456'),(3,'wangwu','123456')

-- encryption
UPDATE testmd5 SET pwd=MD5(pwd)

-- Encrypt when inserting
INSERT INTO testmd5 VALUES(4,'xiaoming',MD5('123456'))

-- How to verify: Passwords passed in by users MD5 Encrypt and then compare the encrypted values
SELECT * FROM testmd5 WHERE `name`='xiaoming' AND pwd=MD5('123456')

##6, Transaction

####6.1, what is a transaction

Either succeed or fail

Put a set of SQLs in a batch to execute~

Transaction Principle: ACID Principle Atomic Consistency Isolation Persistence (Dirty Read, Magic Read...)

  • Atomicity: Either succeed or fail together for the same transaction - > the smallest atom
  • Consistency: Final consistency
  • Isolation: Different transactions do not interact with each other
  • Durability: Once a transaction is committed, it cannot be reversed if it is not committed - > revert to its original state;Transaction committed - > persisted to database

Transaction isolation level

  • Dirty Read: One transaction read data that another transaction did not commit
  • Non-repeatable reading: When a transaction reads data, the results of multiple reads are different (not errors)
  • False reading (magic reading): reading data inserted by another transaction within one transaction, resulting in inconsistent reading

Execute Transaction

-- ======================= affair =========================

-- mysql Is the default open transaction autocommit
SET autocommit = 0 /* Close */
SET autocommit = 1 /* open */

-- Manual transaction
SET autocommit = 0 -- Turn off automatic submission

-- Open Transaction
START TRANSACTION -- Mark the beginning of a transaction after this sql All in the same transaction

-- Commit Persistence
COMMIT
-- Roll back to the original
ROLLBACK

-- End of Transaction
SET autocommit = 1 -- Turn on automatic submission

-- understand
SAVEPOINT Save Point Name -- Set a save point for a transaction
ROLLBACK TO SAVEPOINT Save Point Name -- Rollback to savepoint
RELEASE SAVEPOINT Save Point Name -- Undo Save Point

Simulate Scene

-- ======================= affair =========================

-- mysql Is the default open transaction autocommit
SET autocommit = 0 /* Close */
SET autocommit = 1 /* open */

-- Manual transaction
SET autocommit = 0 -- Turn off automatic submission

-- Open Transaction
START TRANSACTION -- Mark the beginning of a transaction after this sql All in the same transaction

-- Commit Persistence
COMMIT
-- Roll back to the original
ROLLBACK

-- End of Transaction
SET autocommit = 1 -- Turn on automatic submission

-- understand
SAVEPOINT Save Point Name -- Set a save point for a transaction
ROLLBACK TO SAVEPOINT Save Point Name -- Rollback to savepoint
RELEASE SAVEPOINT Save Point Name -- Undo Save Point


-- Transfer accounts
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci

CREATE TABLE `account`(
`id` INT(3) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(30) NOT NULL,
`money` DECIMAL(9,2) NOT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO account(`name`,`money`)
VALUES ('A',2000.00),('B',10000.00)

-- Simulated transfer
SET autocommit = 0; -- Turn off automatic submission
START TRANSACTION -- Open a transaction (A set of transactions)
UPDATE account SET money=money-500 WHERE `name`='A' -- A Minus 500 
UPDATE account SET money=money+500 WHERE `name`='B' -- A Minus 500 

COMMIT; -- Submit Transaction
ROLLBACK; -- RollBACK

SET autocommit = 1;

###7, Index

MySQL officially defines an Index as a data structure that helps MySQL obtain data efficiently.By extracting the sentence backbone, you can get the essence of an Index: an Index is a data structure.

7.1. Classification of indexes

In a table, there can only be one primary key index and more than one unique index

  • Primary key index
    • Unique identification, primary key is not repeatable, only one column can be used as primary key
  • Unique key
    • Avoid duplicate listings, unique indexes can be duplicated, and multiple columns can be identified as unique indexes
  • General index (key/index)
    • Default, index/key keyword to set
  • Full Text Index (fulltext)
    • Only under a specific database engine, MyISAM
    • Quick positioning data

Basic Grammar

-- Use of indexes
-- 1,Adding an index to a field when creating a table
-- 2,Add index after creation

-- Show all index information
SHOW INDEX FROM student

-- Add a full-text index
ALTER TABLE school.`student` ADD FULLTEXT INDEX `name`(`name`);

-- EXPLAIN Analysis sql Status of implementation

EXPLAIN SELECT * FROM student; -- Non-Full-Text Index

EXPLAIN SELECT * FROM student WHERE MATCH(`name`) AGAINST('Hidden');

###7.2, Test Index

CREATE TABLE `app_user` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) DEFAULT '' COMMENT 'nickname',
`email` VARCHAR(50) NOT NULL COMMENT 'email',
`phone` VARCHAR(20) DEFAULT '' COMMENT 'Cell-phone number',
`gender` TINYINT(4) UNSIGNED DEFAULT '0' COMMENT 'Gender (0: male: 1: female)',
`password` VARCHAR(100) NOT NULL COMMENT 'Password',
`age` TINYINT(4) DEFAULT '0' COMMENT 'Age',
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP,
`update_time` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4 COMMENT='app User table'


SET GLOBAL log_bin_trust_function_creators = 1;

-- Insert 1 million pieces of data
DELIMITER $$ -- Function must be written before writing, flag
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
   DECLARE num INT DEFAULT 1000000;
   DECLARE i INT DEFAULT 0;
   WHILE i<num DO
   INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)VALUES(CONCAT('user',i),'whlll0305lhl@gmail.com',CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000))),FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100));
   SET i = i+1;
  END WHILE;
  RETURN i;
END;

SELECT mock_data()

INSERT INTO app_user(`name`,`email`,`phone`,`gender`,`password`,`age`)
VALUES(CONCAT('user',i),'whlll0305lhl@gmail.com'
,CONCAT('18',FLOOR(RAND()*((999999999-100000000)+100000000)))
,FLOOR(RAND()*2),UUID(),FLOOR(RAND()*100))

SELECT * FROM app_user WHERE `name`='User 9999';

EXPLAIN SELECT * FROM app_user WHERE `name`='User 9999';

-- id_Table Name_Field name
-- CREATE INDEX Index Name on surface(field)
CREATE INDEX id_app_user_name ON app_user(`name`);

Indexes are not useful in small amounts of data, but the difference is obvious in large amounts of data ~

###7.3, Indexing Principles

  • Not as many indexes as possible
  • Do not index data that changes frequently
  • Tables with small amounts of data do not need to be indexed
  • An index is usually added to a field commonly used for queries!

Data structure of index

Hash type index
Data structure of Btree:INNODB

###8, Rights Management and Backup

SQL Command Action

User table: mysql.user
Essential: Check this table for additions and deletions

-- Create User CREATE USER User name IDENTIFIED BY 'Password'
CREATE USER whlll IDENTIFIED BY '123456'

-- Modify current user password
ALTER USER 'root'@'localhost' IDENTIFIED BY  '123456';


-- Modify specified user password
ALTER USER 'whlll'@'localhost' IDENTIFIED BY  '123456';

-- rename
RENAME USER 'whlll'@'localhost' TO 'whlll2'@'localhost'

-- User Authorization
-- Everything but authorization
GRANT ALL PRIVILEGES ON *.* TO 'whlll2'@'localhost'

-- View permissions
SHOW GRANTS FOR 'whlll2'@'localhost'
SHOW GRANTS FOR 'root'@'localhost'

-- root User rights: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION

-- Revoke Permission REVOKE
REVOKE ALL PRIVILEGES ON *.* FROM 'whlll2'@'localhost'

-- delete user
DROP USER 'whlll2'@'localhost'

###8.2, MySQL backup
Why backup:

  • Ensure important data is not lost
  • Data Transfer

How MySQL database is backed up

  • Direct Copy of Physical Files
  • Export manually in a visualization tool like Sqlyog
    • In want
  • Export mysqldump command line using command line
# Mysqldump-h host-u username-p password database table name>physical disk location/file name
mysql -hlocalhost -uroot -p123456 school student >D:/a.sql

# Mysqldump-h host-u username-p password database table name 1 table name 2 > physical disk location/file name
mysql -hlocalhost -uroot -p123456 school student result >D:/b.sql

# Mysqldump-h host-u username-p password database>physical disk location/file name
mysqldump -hlocalhost -uroot -p123456 school >D:/c.sql

# Import
# Switch to the specified database with login
# Method of source backup
source d:/a.sql

mysql -u User name -p Password Library Name<Backup Files

Prevent data loss if you want to back up the database

Give the whole data to a friend, sql file to someone else

###9, specification database design

#####9.1, why design is needed

When the database is complex, we need to design it

Bad database design:

  • Data redundancy, waste of space
  • Insertion and deletion of databases can be cumbersome, and exceptions [block the use of physical foreign keys]
  • Poor program performance

Good database design:

  • Save memory space
  • Ensure the integrity of the database
  • Convenient for us to develop the system

In software development, about database design

  • Analysis Requirements: Analyze business and database needs to be addressed
  • Summary Design: Design Diagram (E-R Diagram)

Steps to design a database:

  • Collect information and analyze requirements
    • User table (user login logoff, user profile, blog, category creation)
    • Categories (article categories, who created them)
    • Article Table (article information)
    • Comment Table
    • Friends List (Friends Chain Information)
    • Custom table (system information, a key word, or some primary field) key:value
    • Talk about tables (express your mood...id...content...create_time)
  • Identify entities (implement requirements into each field)
  • Identify relationships between entities
    • Blog: user -> blog
    • Create categories: user -> category
    • Attention: user -> user
    • Friend chain: links
    • Comment: user-user-blog

Three Norms ###9,2

Why do I need data normalization?

  • Repeated information
  • Update Exception
  • Insert Exception
    • Information cannot be displayed properly
  • Delete Exception
    • Loss of valid information

Three Paradigms

First Norm (1NF)

Atomicity: data is indivisible

Second Norm (2NF)

Prerequisite: First paradigm must be satisfied

Each table describes only one thing

Third Norm (3NF)

Prerequisite: The second and first paradigms must be satisfied

The third paradigm needs to ensure that each column of data in a data table is directly related to the primary key, not indirectly.

(specification of database design)

Specification and performance issues

Associated queries must have no more than three tables

  • Consider the needs and objectives of commercialization (cost, user experience!)Database performance is more important
  • When it comes to normative issues, you need to give due consideration to normative issues!
  • Intentionally add redundant fields to some tables (from multi-table queries to single-table queries)
  • Intentionally adding some computed columns (from large to small: index)

###10, JDBC (focus)

#####10.1, database driver

Driver: Sound card, graphics card, database

Our programs are database driven and work with databases

####10.2,JDBC

SUN provides a specification, commonly referred to as JDBC, for simplifying developer (unified database) operations.

The implementation of these specifications is done by the specific manufacturer~

We just do it by operating JDBC

java.sql

javax.sql

You also need to import a database-driven package (Maven)

(Later on, JDBC, I'll put it with maven and mybatis later)

Tags: Database MySQL SQL

Posted on Tue, 07 Sep 2021 12:29:43 -0400 by toms