MySQL Premium content

I. INTRODUCTION

• There are 3 correspondence between entities and entities, and these relationships need to be stored
• In development you need to do some processing of stored data, using some of the built-in functions
• View is used to complete the encapsulation of query statements
• Transactions can ensure that complex additions and deletions are effective

Two. Relationship

• Create a score table scores, structured as follows
• Id
• Students
• Subjects
• Results
• Thinking: What kind of information should students have in their list?
• A: The student column data is not new here, but should be quoted from the student table, the relationship is also a data, according to the pattern requirements should be stored the student's number, not the student's name and other information
• Similarly, the chart of accounts is also a relational column, referencing the data in the chart of accounts

• The statements that create the table are as follows

CREATE TABLE scores (ID int primary key auto_increment,stuid int,subid int,score decimal (5,2)); foreign key

• Thinking: How to ensure the validity of relational column data? Can I have any integers?
• A: It must be the data in the ID column of the student table, and the validity of the data can be verified by the foreign KEY constraint.
• Add a FOREIGN KEY constraint for Stuid

ALTER TABLE scores add constraint Stu_sco foreign key (STUID) references students (ID);

• When inserting or modifying data at this point, if the value of Stuid is not present in the students table, the error will be
• You can create a constraint directly when you create a table

CREATE TABLE scores (ID int primary key auto_increment,stuid int,subid int,score Decimal (5,2), foreign key (STUID) reference s students (ID), foreign key (SubID) references subjects (ID)), cascade operation for foreign keys

• When deleting data from a students table, if the ID value already exists in scores, an exception is thrown
• It is recommended to use tombstone, also to solve this problem
• You can specify cascading actions when you create a table, or you can modify a foreign key cascade after you create a table
• Grammar

ALTER TABLE scores add constraint Stu_sco foreign key (STUID) references students (ID) on DELETE cascade;

• The types of cascading operations include:
• Restrict (limit): Default value, throw exception
• Cascade (CASCADE): If the record of the primary table is deleted, the associated records from the table are deleted
• Set NULL: Sets the foreign key to null
• No action: Do nothing

Three. Let's take a look at the question

• Q: Query each student's score for each subject
• Analysis: Student name from students table, account name from subjects, score from scores table, how to put 3 tables together query, and the results displayed in the same result set?
• Answer: When the query results from multiple tables, you need to use a connection query
• Key: Find relationships between tables, and the current relationship is
  • The ID of the students table---the STUID of the scores table
  • The ID of the subjects table---the subid of the scores table
• The answer to the above question is:

select students.sname,subjects.stitle,scores.scorefrom scoresinner join students on scores.stuid=students.idinner join subjects on scores.subid=subjects.id;

• Conclusion: When you need to query multiple tables that have a relationship, you need to use the join join

Connection Query

• The connection queries are categorized as follows:
  • Table A INNER JOIN table B: Table A rows matching table B appear in the results
  • Table A LEFT JOIN table B: Rows with table A matching table B appear in the results, plus data unique in table A, with no corresponding data using null padding
  • Table A RIGHT Join Table B: Table A matches table B appears in the results, plus data unique in table B, with no corresponding data using null padding
• Recommended syntax for "table name. Column Name" in a query or condition
• If the column names in multiple tables are not repeated, you can omit the "table name." Part
• If the name of the table is too long, you can use the ' as shorthand name ' or ' abbreviated name ' after the table name to give the table a temporary abbreviated name

Practice

• Check student's name, average score

select students.sname,avg(scores.score)from scoresinner join students on scores.stuid=students.idgroup by students.sname;

• Search for boys ' names and scores

select students.sname,avg(scores.score)from scoresinner join students on scores.stuid=students.idwhere students.gender=1group by students.sname;

• Query the name of the account, the average score

select subjects.stitle,avg(scores.score)from scoresinner join subjects on scores.subid=subjects.idgroup by subjects.stitle;

• Query the name of the non-deleted account, the highest score, the average

select subjects.stitle,avg(scores.score),max(scores.score)from scoresinner join subjects on scores.subid=subjects.idwhere subjects.isdelete=0group by subjects.stitle;

Four. Sub-query

• Query support nested use
• Query the students ' grades in Chinese, maths and English

Select Sname, (select Sco.score from scores SCO inner join subjects sub on sco.subid=sub.id where sub.stitle= ' language ' and Stuid =stu.id) as language, (select Sco.score from scores SCO inner join subjects sub on sco.subid=sub.id where sub.stitle= ' math ' and Stu Id=stu.id) as Mathematics, (select Sco.score from scores SCO inner join subjects sub on Sco.subid=sub.id where sub.stitle= ' English ' and s Tuid=stu.id) as English from students Stu;

Five. Built-in function string functions

• The ASCII code value of the view character is ASCII (str), and Str is empty when the string returns 0

select ascii(‘a‘);

• To view the ASCII code value corresponding to the character char (number)

select char(97);

• Stitching string concat (Str1,str2 ...)

select concat(12,34,‘ab‘);

• Contains the number of characters length (str)

select length(‘abc‘);

• Intercept string
  • Left (Str,len) returns Len character of the string str
  • Right (Str,len) returns string Str to the left of Len characters
  • SUBSTRING (Str,pos,len) returns the position of the string str at POS from Len characters

select substring(‘abc123‘,2,3);

• Remove spaces
  • LTrim (str) returns the string with the left space removed str
  • RTrim (str) returns the string with the right space removed str
  • Trim ([direction remstr from Str) returns the string str after removing remstr from a side, the direction word includes both, leading, trailing, indicating both sides, left, and right

select trim(‘  bar   ‘);select trim(leading ‘x‘ FROM ‘xxxbarxxx‘);select trim(both ‘x‘ FROM ‘xxxbarxxx‘);select trim(trailing ‘x‘ FROM ‘xxxbarxxx‘);

• Returns a string consisting of n space characters space (n)

select space(10);

• Replacement string replace (STR,FROM_STR,TO_STR)

select replace(‘abc123‘,‘123‘,‘def‘);

• Uppercase and lowercase conversions, functions as follows
  • Lower (str)
  • Upper (str)

select lower(‘aBcD‘);

Mathematical functions

• Absolute ABS (N)

select abs(-32);

• The remainder of the m divided by N MoD (m,n), the same operator%

select mod(10,3);select 10%3;

• Flooring Floor (n), representing the largest integer not greater than n

select floor(2.3);

• Ceiling Ceiling (n), representing the largest integer not less than n

select ceiling(2.3);

• Rounding value round (n,d), n = original number, d for decimal position, default to 0

select round(1.6);

• The Y Power pow (x, y)

select pow(2,3);

• Getting Pi Pi ()

select PI();

• Random number rand (), floating point value of 0-1.0

select rand();

• There are many other trigonometric functions that you can use to query documents

Date-time functions

• Gets the child value, the syntax is as follows
  • Year (date) returns the date (range 1000 to 9999)
  • Month (date) returns the number of months in date
  • Day (date) returns the date value
  • Hour (time) returns the hours of time (range 0 to 23)
  • Minute (time) returns the number of minutes of time (range 0 to 59)
  • Second (time) returns the number of seconds in time (range 0 to 59)

select year(‘2016-12-21‘);

• Date calculation, using the + + operator, the keywords following the numbers are year, month, day, hour, minute, second

select ‘2016-12-21‘+interval 1 day;

• Date format date_format (Date,format), the values available for the format parameter are as follows

  • Gets the year%y, returning a 4-bit integer

    * Get year%y, return 2-bit integer

    * Get month%m, an integer value of 1-12

  • Get day%d, return integer

    * Gets%h, an integer value of 0-23

    * Gets%h, an integer value of 1-12

    * Get a%i with a value of 0-59 integers

    * Gets the seconds%s, an integer with a value of 0-59

select date_format(‘2016-12-21‘,‘%Y %m %d‘);

• Current date current_date ()

select current_date();

• Current Time Current_time ()

select current_time();

• Current date time now ()

select now();

Six. View

• For complex queries, maintenance is a very troublesome thing to do after multiple use.
• FIX: Define a view
• The essence of view is an encapsulation of queries
• Defining views

create view stuscore as select students.*,scores.score from scoresinner join students on scores.stuid=students.id;

• The purpose of a view is to query

select * from stuscore;

Seven. Business

• When a business logic requires multiple SQL completion, if one of the SQL statements goes wrong, you want the entire operation to be returned
• Transaction can be used to complete the function of return, to ensure the correctness of business logic
• Four major features of transactions (acid, for short)
  • Atomicity (atomicity): All operations in a transaction are indivisible in the database, either all completed or not executed
  • Consistency (consistency): Several transactions executed in parallel, whose execution results must be consistent with the results executed serially in a sequential order
  • Isolation (Isolation): The execution of a transaction is not disturbed by other transactions, and the intermediate result of the transaction execution must be transparent to other transactions
  • Persistence (Durability): For any committed transaction, the system must ensure that the transaction's changes to the database are not lost, even if the database fails
• Requirement: The type of the table must be a innodb or BDB type before the transaction can be used on this table
• View creation statements for a table

show create table students;

• Modifying the type of a table

alter table ‘表名‘ engine=innodb;

• Transaction statements

开启begin;提交commit;回滚rollback;

Example 1

• Step 1: Open two terminals, connect MySQL, use the same database, operate the same table

终端1：select * from students;------------------------终端2：begin;insert into students(sname) values(‘张飞‘);

• Step 2

终端1：select * from students;

• Step 3

终端2：commit;------------------------终端1：select * from students;

Example 2

• Step 1: Open two terminals, connect MySQL, use the same database, operate the same table

终端1：select * from students;------------------------终端2：begin;insert into students(sname) values(‘张飞‘);

• Step 2

终端1：select * from students;

• Step 3

终端2：rollback;------------------------终端1：select * from students;

Eight. Summary

• Storage of relationships
• Connection Query
• Self-correlating
• Sub-query
• Common built-in functions
• View
• Transaction

Homework

• Designing class tables, associating with student tables, and querying
• Design a classification table, self-correlating, and query
• Create a view store above the two

MySQL Premium content