MySQL (vi)

Self-correlating

• Design the table structure of provincial information provinces
  • Id
  • Ptitle
• Table Structure of Design city information Citys
  • Id
  • Ctitle
  • Proid
• The proid of the Citys table represents the province to which the city belongs, corresponding to the ID value of the provinces table
• Question: Can I synthesize a table of two tables?
• Thinking: Observing two tables found that the Citys table is one more column proid than the provinces table, and the other columns are of the same type
• Meaning: The storage is the region information, and the data of each kind of information is limited, no need to add a new table, or in the future to the storage area, township information, all increase the cost of the new table is too large
• Answer: Define table areas, structure as follows
  • Id
  • Atitle
  • Pid
• Because provinces do not belong to the province, you can fill in null
• City-owned province PID, fill in the province of the corresponding number ID
• This is the self-association, a column in the table, associated with another column in the table, but their business logic meaning is not the same, the city information PID refers to the provincial information ID
• In this table, the structure is unchanged, you can add County, township streets, village communities and other information
• The statement that creates the areas table is as follows:

Create Table  intprimarykey varchar (int , Foreign Key References areas (ID));

• Import data from a SQL file

SOURCE Areas.sql;

• Find out how many provinces there are
• Find all cities in province named "Shanxi Province"

Select City. *  from  as  City Inner Join  as  on City.pid=province.idwhere province.atitle=' Shanxi Province ';

• The city's name is "Guangzhou" for all districts and counties

   select  dis.* , Dis2.*  from  areas as  Span style= "COLOR: #000000" > dis  inner  join  areas as  city on  city.id=  dis.pid  left  Span style= "COLOR: #808080" >join  areas as  Dis2 on  dis.id=   Dis2.pid  where  city.atitle=   "  Guangzhou  " ; 

Sub-query

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

Selectsname, (SelectSco.score fromScores SCOInner JoinSubjects Sub onSco.subid=Sub.idwhereSub.stitle='language'  andStuid=Stu.id) aslanguage, (SelectSco.score fromScores SCOInner JoinSubjects Sub onSco.subid=Sub.idwhereSub.stitle='Mathematics'  andStuid=Stu.id) asMathematics, (SelectSco.score fromScores SCOInner JoinSubjects Sub onSco.subid=Sub.idwhereSub.stitle='English'  andStuid=Stu.id) asEnglish fromStudents Stu;

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();

MySQL (vi)