Mad YYDS MySQL Learning Notes

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 */

MYISAMINNODBTransaction supportI won't support itSupportData row lockingI won't support itSupportForeign Key ConstraintsI won't support itSupportFull-text IndexSupportI won't support itTable 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`='[email protected]' 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<>6trueBETWEEN...and...[...] Closed interval[2,5]ANDand5>1 and 1>2falseORor5>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

operatorgrammardescribeand &&a and bLogical andora or bLogical orNot !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

operatorgrammardescribeIS NULLa is nullIf the operator is NULL, the result is trueIS NOT NULLa is not nullIf the operator is not null, the result is trueBETWEENa between b and cIf a is between b and c, the result is trueLikea like bSQL matches, if a matches b, the result is trueIna 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

operationdescribeInner JoinReturns a value if there is at least one match in the tableRight JoinAll values are returned from the right table even if there is no match in the left tableLeft 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:

categoryidcategoryName2information technology3software development5Art Design

Subclass:

pidcategoryidcategoryName34data base28Office Information36web development57ps Technology

Operation: Query the parent's subclass relationship

Parent ClassSubclassinformation technologyOffice Informationsoftware developmentdata basesoftware developmentweb developmentArt 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 NamedescribeCOUNTcountSUM()SummationAVG()average valueMAX()MaximumMIN()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),'[email protected]',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),'[email protected]' ,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)

Add new comment

For adding a comment, please log in

Login or create account

0 comments