当前位置:网站首页>MySQL personal learning summary

MySQL personal learning summary

2022-04-23 16:31:00 Yangge landing

Mysql Personal learning summary

edition 5.6.46
Learning notes , Summarize the knowledge points in the learning process , Update from time to time .
If there is a mistake , Please point out .
Examples are cited below
https://blog.csdn.net/qq_45075198/article/details/100775251

sql Data definition function

DATE Date type

Database management

Library management commands

show databases; // Show all libraries
select user() // Show connected users
user Library name ; // Select database
select databases(); // Display the current library

create database Library name ;      // Create a library
show tables; // Displays all tables in the current library
drop databases  Library name ; // Delete Library

create datebase|schema [if not exists]  Library name   // Create a library 
show databases   Library name 				// Show all libraries 
use  Library name 									// Use the library 
drop database  Library name 					/ Delete Library     

Table management commands

Table operations include creating new tables , Modify table , Delete table

create table  Table name   [null | not null ] [primary key] [unique] 
default  The default value is 				// Create database 
drop table  Table name ;			// Delete table 
alter table  Table name  add  Field   type ;		// Add fields to the table 
alter table  Table name  modify  Field   type ;	// Modify fields 
alter table  Table name  drop  Field   type ;		// Delete field 

show tables;	// View all tables in the current library 
show create table  Table name ;
desc  Table name ;		// View table structure 

For example, the following code :
Student list
Student number , full name , Gender , Date of birth , Major number , class

create table student(
	sno varchar(10) NOT NULL,
	sname varchar(20) NOT NULL,
	ssex char(2) NULL DEFAULT ' male ',
	sbirth DATE NULL,
	zno varchar(4) null,
	sclass varchar(10) null,
	PRIMARY KEY(sno)
);

Data manipulation function

INSERT insert data

insert into  Table name  valuse ( value ) 
 // If no column is specified , The order of the column names must be consistent with the order of the table names 
 insert into  Table name ( Name , Name ....)  values ( value )
 // Specified column 
 insert into  Table name  ( Name , Name , Name ) SELECT  Name , Name , Name  from  Table name ;
 // Insert multiple rows of records , You can extract data from another table and insert 

UPDATE Modifying data

update  Table name  set  Name =' expression ' [ Name =' expression '] [where  Name =' expression '];
//set The sentence should give the modified column and its modified value  
// where Clause specifies the conditions that the record to be modified should meet ,where When omitted , Modify all records in the table 
update  Table name  set  Name =' expression '

DELETE Delete data

delete from  Table name  [where  Name =' expression ' ]
// The table name is the table to delete data 
// where Clause ,  The conditions that the records to be deleted should meet ,where When omitted , Delete all records in the table 
delete from  Table name  
//  Delete all records in the table   The table is empty , But its definition still exists in the data dictionary 

sql Data query function

Single table query

SELECT [ ALL | DISTINCT ] < Target list expression > [ AS < Alias > ]
FROM < Table or view name > [ AS < Alias > ]
[ WHERE< Conditional expression > ]
[ GROUP BY< Group by column > ]
[ HAVING < Conditional expression > ]
[ ORDER BY < Sort by column > [ ASC | DESC ] ]
[ LIMIT N,M ]

(1) [ ALL | DISTINCT ] : Specifies whether duplicate rows are displayed in the result set .ALL Presentation display , Default ALL;DISTINCT Means not to show

(2)< Target list expression > [ AS < New column names > ] : Specifies the columns selected for the result set . If this is * , It means that all columns are output

(3)< Table or view name > [ AS < Alias > ] : Specify the table or table from which to retrieve data

(4)[ WHERE< Conditional expression > ] : Specify the conditions for data retrieval

(5)[ GROUP BY< Group by column > ] : Group columns , Realize the grouping query of data
(6)[ HAVING < Conditional expression > ] : Used for the filter criteria after grouping

(7)[ ORDER BY < Sort by column > [ ASC | DESC ] ] : Sort columns , Press... On the result set < Sort by column > Sort the values of the specified column . among ASC Represents an ascending sequence ,DESC Indicates that the results are arranged in descending order .

(8)[ LIMIT N,M ] : Indicates that... Is output from the query result set . From N+1 OK, let's start , Inquire about M Column .

Whole SELECT The meaning of the sentence is : according to WHERE Conditional expression of clause , from FROM Clause to find out the tuples that meet the conditions in the basic table or view specified by , Press again SELECT Target list expression in Clause , Select the attribute value in the tuple , Form a result table .
If there is GROUP Clause , Then press the result < Group by column > Group values of , Tuples with equal values of the attribute column are a group , Each group produces a record in the result table . Usually used in groups Set function . If GROUP Wait for HAVING The phrase , Only the groups that meet the specified conditions will be output . If there is ORDER Clause , According to < Sort by column > The values of are arranged in ascending or descending order .

select * from student;
select sname,sno sclass from student ; // The column order can be different from that in the base table 

//< Target column > Not just attribute columns , You can also list the results of the queried attribute column after certain calculation 
select year(now())-year(sbirth) from student ;  //year() Is a function of the output year ,now() Is a function that outputs the current date 

select sname  as  The student's name  from student ;//as Followed by an alias 

select sno from sc ;
select distinct sno from sc;//  If there are duplicate lines , You can use distinct Eliminate duplicate 

Common query conditions

Query criteria The predicate
Compare ( Comparison operator ) =,>,>=,<,<=,<>(!=),not
Determine scope BETWEEN…AND ,NOT BETWEEN…AND
Determine set in ,not in
Character matching like,not like Fuzzy query
Null value is null, is not null
Multiple conditions and ,or

like : % For any length ,_ Single character
Logical operators and ,or Used to join multiple query criteria , At the same time where in ,and Priority over or, But you can change the priority with parentheses .

select sname from student where sclass=' business 1401';// Query business 1401 List of all students 

select sname from student where sbirth < '1998-01-01';// Inquire about 1995 Names of students born before 

select distinct sno from sc where grade<60 // Check the student number of the student who failed the exam 

select sname from student where sbirth>='1995-01-1' and sbirth<='1997-12-31';
select sname from student where sbirth between '1995-01-01' and '1997-12-31';
select sname from student where sbirth not between '1995-01-01' and '1997-12-31';
select sname from student where sbirth<='1995-01-1' or sbirth>='1997-12-31';
// The query in 1995-01-01  To 1997-12-31 The name of the student born on  between...and  and  <= ,>=

select sname from student where sclass in(' Trust tube 1401',' Business circles 1301');
select sname from student where sclass not in(' Trust tube 1401',' Business circles 1301');
// Query matching tuples ; The relative predicate is not in, Find tuples that do not belong to the specified set 

select sname from student where sname like'_ Small %';
select sname from student where sname not like ' Small %';

Null value (NULL) There is no value or the value is uncertain , Null values are not comparable in size , Out-of-service “=” Operate on null values , Should use the “is”.
Be careful , Null is not a space , An empty string or 0.
Be careful ,is Out-of-service “=” Instead of

select sname,sno from student where grade is null;
select sname,sno from student where grade is not null;

Users can use ORDER BY Clause specifies the ascending order of one or more attribute columns (ASC) Or descending (DESC) Arrange query results . Ascending is the default value

select sno,grade from sc where cno='58130540' order by grade desc;
select * from student order by sclass,sbirth desc;// In ascending order of class , Students in the same class are arranged in descending order of age 

Set function
(1)COUNT([ DISTINCT | ALL ] *): Count the number of tuples
(2)COUNT(< Name >): Count the number of median values in a column
(3)SUM(< Name >): Calculate the sum of a column of values ( This column must be numeric )
(4)AVG(< Name >): Calculate the average of a list of values ( This column must be numeric )
(5)MAX(< Name >): Find the maximum value in a column
(6)MIN(< Name >): Find the minimum value in a column

select count(*) from student; // Query the total number of people 
select count(distinct *) from student; // Avoid reuse distinct

select avg(grade) from sc where cno='58130540';
select max(grade) from sc where cno='58130540';
select max(grade),min(grade),sum(grade) from sc where cno='58130540';

group by Query result grouping
GROUP Clause can group the rows of the query result table according to the principle that the values of one or more columns are equal .

select cno,count(sno) from sc group by cno;// Query each course number and the corresponding number of selected courses 
select sno,count(cno) from sc group by sno having count(cno)>2;// Query the student number of students who have taken more than two courses 

WHERE Clause and HAVING The fundamental difference between phrases is : Different objects of action .WHRER Clause acts on records in the base table or view , Select the tuples that meet the conditions ;HAVING Phrases act on the results of grouped queries , Select the results that meet the conditions .

Link query

1. Internal connection
When using internal connections , If the related fields of two tables meet the connection conditions , Then extract the data from the two tables and combine them into a new record .
When the join operator is “=” when , It's called equivalent connection . Use other operators for non equivalent connection .
The process of performing the connection operation is : First of all, in the table 1 Find the first tuple in ; Then scan the table sequentially from the beginning or by index 2, Find tuples that meet the join conditions , Every tuple found , Just put the watch 1 The first tuple in is concatenated with the tuple , Form a tuple in the result table . surface 2 After all scanning , To the table 1 Second tuple found in , Then scan the table from the beginning or by index 2, Find tuples that meet the join conditions , Will table 1 The second tuple is spliced with the tuple to form a tuple of the result table . Repeat this operation , Until the watch 1 Until all Yuanzu have been handled .

select * from student,sc where student.sno=sc.sno;
select * from student join sc on student.sno=sc.sno;
// key word join Connection table , key word on Describe the connection conditions . The columns in both tables should be added with table names   Table name . Name 

select student.sno,sname,sex,sbirth,sclass cno,grade
from student,sc where student.sno=sc.sno; // No duplicate Columns , Natural join 
// The first way 
select student.sno,sname,cno,grade from student,sc 
where student.sno=sc.sno and cno='58130540' and grade>=90;
// The second way 
select student.sno,sname,cno,grade 
from student join sc on student.sno=sc.sno 
where  cno='58130540' and grade>=90;

// Query the name and grades of each student and their elective courses 
select student.sno,sname,cname,grade from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno;
// The second way 
select student.sno,sname,cname,grade from student
join sc on student.sno=sc.cno
join course on sc.cno=course.cno;

2. Self join
The join operation can not only be performed between two tables , It can also be a table connected to itself , This kind of join is called self join or self join of table .

select distinct a.sname from student a,student b where a.sclass=b.sclass and a.sclass=' business 1401';
// Query the names of students studying in the same class 
// by student The table takes two aliases a,b

3. External connection
Connect the two tables completely together
LEFT OUT The left outer join ,RIGHT OUT Right connection

select student.sno,sname,ssex,sbirth,sclass,cno,grade
from student LEFT OUTER JOIN sc on student.sno = sc.sno;

nested queries

stay sql In language , One SELECT…FROM…WHERE Statement is called a query block . Nesting a query block in the of a query block WHERE Clause or HAVING The query in the condition of phrase is called nested query or subquery .

select sname from student sno in
(select sno from sc where cno='2');

In this query , Lower query block (select sno from sc where cno=‘2’) Is a query block nested in the upper layer (select sname from student sno in) Of WHERE In the condition of . The upper query block is called outer query or parent query or main query , Lower level queries are called inner level queries or sub queries .
The solution method of nested query is to process from inside to outside , Each subquery is solved before its upper level query processing , The results of a subquery are used to establish the search criteria for its parent query .
sql Allow multiple nesting , That is, other subqueries can be nested in one subquery .
Be careful : The subquery SELECT You can't use ORDER BY Clause ,ORDER BY Clause can always only sort the final query results .

1. with IN Subquery of predicate
with IN The subquery of a predicate refers to the parent query and the subquery IN Connect , Judge whether the value of an attribute column is in the result of subquery .

// Query and “ Guo Shuang ” Students studying in the same class 
select * from student where sclass in
(select sclass from student where sname=' Guo Shuang ');

版权声明
本文为[Yangge landing]所创,转载请带上原文链接,感谢
https://yzsam.com/2022/04/202204231402543018.html