mysql

1, Database

1). What is a database?

Note: database is a professional warehouse for storing and managing data

2). Classification of database

Extended content:Classification of database
	early stage : Hierarchical database , Network database
	Now? : Relational database , Non relational database
	Now most of the databases on the market are still relational databases

(2.1). What is a relational database

Note: the underlying database that stores data in the form of * * two-dimensional table * * is a relational database

What are the common relational databases?

​ Oracle :

Oracle provides a database product for a fee. Previously, it had a market share of more than 50% in java

Features: mainly applicable to some large or super large application systems

​ SQL Server :

Microsoft provides a database product for a fee

Features: mainly used with some medium or large application systems

​ MySQL :

A database product provided by a Swedish company (MySQLAB),

Has been acquired by (Oracle)

Features:

(1) small, light and easy to use, suitable for some small or medium-sized application systems,

(2) mysql Cluster can also be used for some large or super large systems

DB2: a product of IBM company. It is mostly used in financial / banking systems. There are many charges

SQLite: Mini database for embedded devices (mobile phone / smart home and other products)

3). Explanation of database related names

Database server: install mysql on the computer, and the computer can be used as a database server,

It can save and retrieve data A database server can contain multiple databases

For example, the installed mysql server comes with four databases

	+--------------------+
	| Database           |
	+--------------------+
	| information_schema |
	| mysql              |
	| performance_schema |
	| test               |
	+--------------------+	

	-- database : Is the warehouse where data is stored , Usually a website(system)All data in will be stored in one database 
		  example : All data of JD website    db_jd
		  	   All data of Taobao website	db_taobao
		  	   All data of Baidu website	db_baidu
	-- surface: The data in the database is stored by type , A kind of data is often stored in a table , Multiple tables can be created in a database
		 example : User data of Jingdong website 		tb_user
		 	  Commodity data of jd.com		 tb_product
		 	  Order data of jd.com		 tb_order
	-- Table record : A table can contain multiple rows of table records , A table used to store a type of information , A row of table records is used to store a specific data
		example : Table in database	 java Classes in(student)
        	Table record         object

​

II SQL language

1) What is SQL language

SQL language is a general language for operating relational databases (learning SQL can operate all relational databases)

SQL operations include:

(1) addition, deletion, modification and query of database

(2) addition, deletion, modification and query of table

(3) addition, deletion, modification and query of table records (data)

(4) stored procedure / view / index can also be operated

Note: SQL language is a general language for operating relational databases: however, each database manufacturer provides a "small number of unique" SQL languages,

General SQL language

Monologue sentences are not common!

(1.1) how to link mysql server

cmd window

-- Mode 1 : mysql -u user name -p user name
-- Mode II : mysql -u user name -p   Enter the password on the second line
-- Mode III : mysql -u user name -p -h Host name or ip address -p port
		-h : Host name or ip address 
		-- notes:  If you don't write-h Default connection localhost(127.0.0.1)
        -p : port
        -- notes:  If you don't write-p The default connection is 3306
   Exit connection mysql The server : 
   	exit
   	quit
   	Close window directly

2). Database and table operation

Import SQL script

source absolute path

(2.1) . Add, delete, modify and query database

-- (1) . check 
	-- see mysql Database in server
		show((display) databases;
	-- View the statements during database creation
		show create database mydb1;
	-- See what library you entered
    	select database();
        
-- (2) . increase 
	-- establish	mydb1	library , And specify the code as UTF8; 
		-- grammar: CREATE(establish) DATABASE(library)  Library name CHARSET(code) uft8;
				create database mydb1 charset utf8;
		-- Enhanced grammar : 
			-- use if(whether) not(no) exists(existence)Determine whether this database exists in the database
			-- without(not) Then execute crate database Create a library
			-- If so, do not recreate
				result: create database 
				Judgment: if not exists mydb1 charset utf8;
		
-- (3) . change ? 

-- (4) . Delete 
	-- delete mydb1 library
		drop(delete) database(library) mydb1;  
	-- Code optimization:
		result: drop database 
		Judgment: if exists mydb1;  
	-- judge: 
		-- if exists If mydb1 existence 
		-- Then execute drop database Delete Library
		-- If it does not exist, it will not be executed and no error will be reported
	

(2.2) . Addition, deletion and modification query table

-- (1) check
	-- View table examples in the library:mydb1 There is a watch in the stu
		show((display) tables;
	-- 	View created stu Table structure
		desc(describe) stu;
		
-- (2) increase
	-- establish stu Student list ( id[int] , full name , Gender , date of birth , Examination results(floating-point) )
		-- grammar:
				create table stu(
					id int primary key auto_increment,				 
					name varchar(50),			 
					gander varchar(10),             
					birthday date,   
					score double           
				);
-- (3) Delete
	-- delete stu Student list
		drop table stu;
		result: drop table 
		Judgment: if exists stu;

-- (4) Change?
		
	

(2.3) . Addition, deletion and modification of query table records

-- (1) check
	-- see stu All student information in the table
		select * from stu
	-- see stu Information in a single column in the table
		example: select((select) column name from(From) stu

-- (2) increase	
		insert(insert) into(get into) stu(Table name)
	-- Increase student information
		-- Add one at a time (value)	
			insert into stu value(2,'Chen Zishu','male','1978-10-11',82);
		-- Add more than one message at a time (values)
			insert into stu values(2,'Chen Zishu','male','1978-10-11',82),
								  (3,'Junmo','male','2000-7-25',98.8);

-- (3) Delete	delete(delete)
	-- Delete syntax:
		delete from Table name where(judge) Judgment conditions;
		result: delete from stu 
		Judgment: where id<2; -- Delete only qualified records
	-- Delete all:
		delete from Table name;

-- (4) change	update(Modify upgrade)
	-- Modify syntax
		update Table name set Column name to be modified = Value to be modified where(judge) Judgment conditions;
	-- for example: 
		-- where Judge will id For 4 updata(Modify update)  
		result: updata stu set id=1,name='Son of heaven' 
		Judgment:	where id=4;

3) . Aggregate function (query)

is - yes not - no

Or - and / or and - and

(3.1) . where clause query

Question 1: if necessary+Appears in the processed data null 
	annotation: null Value and any data operation result is null Instead of 0
	
	
-- null of ( Treatment method ) :
	Mode 1:  
		All bonuses will be null All column values are updated to 0
		update stu set bonus=0 where bonus is null;
	Mode II: 
		When querying, the null Treat as 0 --- ifnull(Listing,0) 
		result: select name as full name,sal+ifnull(bonus,0) as Total salary from emp  
		Judgment: where sal+ifnull(bonus,0)>3500;
-- as Specify another name for the header as It can be omitted

(3.2). Fuzzy query (like)

Wildcard:

%: can identify 0 or more arbitrary characters

​ _ : Only one character can be identified

Syntax: query employees whose names contain the word Jun in the emp table

	query
	-- result: select name from emp
	-- Judgment: where name like '%King%'

(3.3). Multiline function query (count)

Note: the count function will automatically ignore null

For example, 19 rows in the score column are null, and only 18 rows will be calculated during count calculation

Syntax:

select function (column name) from emp;

Common multiline functions are:

-- count(Listing / *): 
	count(Column name): indicates how many values of the current column are counted (not counted by default) null Value)
	count(*): Count the number of rows of records in query results in behavioral units
	
	max((column name) -- Counts the maximum of all values in the current column
	min((column name) -- Counts the smallest of all values in the current column
	
	sum((column name) -- Count the sum of all values in the current column (the values of this column will be added together and returned) 
	avg((column name) -- Count the average of all values in the current column (the sum of all values in this column)

null handle:
	select avg(sal+bonus) from emp；
	wrong: bonus Bonus is null If yes, the result is svg(null)
	
	select avg(sal+ifnull(bonus,0))from emp;
	-- take null Change to 0 before judgment

(3.4). Group query (group by)

Syntax: select name / function from emp group by column name;

Example:

Result: select count(*) – output the number of each salary

Judgment: from emp group by dept; – Group by salary

(3.5). Sort query

ASC default ascending DESC default descending

Syntax:

​

	Fixed syntax: order by Listing asc 

​			result : select Listing from emp 

    ​			judge : order(Sort) by((by) sal(Column) asc((ascending)   -- take sal Ascending arrangement 
   													desc
​	for example : select * from emp order by sal asc;

(3.6). Paging query (limit)

Query formula:

Limit (page number - 1) * number of displays per page, number of displays per page

 -- query emp Records in table , Three per page , Query page 1
	select * from emp limit 0 , 3;
 -- query emp Records in table ,Three queries per page Page Page 2
	select * from emp limit 3 , 3;
 -- query emp Records in table, Three queries per page Page Page 3
 	select * from emp limit 6, 3;
 -- query emp Records in table , Three per page , Query page 4
 	select * from emp limit 9, 3;

Question: find the information of the top three employees with the highest salary in the emp table, and display the name and salary

	result: select name,sal from emp 
	judge: order by sal asc desc limit 0,3;  --Sort in ascending order, return, and then output the result

(3.7). Other functions

Function has:
curdate() 	-- Get current date: specific date
curtime()	-- Get current world: Hour, minute and second

sysdate()/now() 	-- Get current date+time ,Mm / DD / yyyy H / min / S
year('2020'-8-10) 	-- Returns the year in a numeric value
month('2020-8-10') 	-- Returns the month in the value
day('2020-8-10') 	-- Returns the number of days in a date

hour('2020-8-10 11:22:6') -- Hours in return time 11
minute() -- Minutes in return time
second() -- Returns the second value of the time

--------------------------------------------------------------------------
concat_ws(x,s1,s2,...sn)  -- Splicing statement 
-- take x,The following characters are spliced together , And will x Splice as separator

-----------------------------------------------------------------------------

III data type

MySQL There are three types of support in: numerical value
						   String type
						   date / time

1. Value type

type	size	purpose
tinyint (byte)	1 byte	Small integer value
smallint (short)	2 byte	Large integer value
int (int)	4 byte	Large integer value
bigint (long)	8 byte	Maximum integer value
float (float)	4 byte	Single precision floating point value
double (double)	8 byte	Double precision floating point value
decimal	-	Store exact decimal values

Note: decimal

-- decimal usage
	grammar:  Variable name decimal(P,D);
	p: Represents the precision of a significant number       Represents an integer+Total number of decimal places
	D: Represents the number of digits after the decimal point      Indicates how many decimal places can be left after the decimal point : (D<=P)
	--for example:
	uid DECIMAL(6,2);
	express:   
		P : amout Up to 6 digits can be stored,
		D :	The decimal position is 2 digits ---- That is, integer bits can store up to four bits

2 . String type

Common string types:

type	size	purpose
char	0 ~ 255 (characters)	Fixed length string
varchar	0 ~ 65535 (bytes)	Side length string
mediumtext	0 ~ 16 777 215 (bytes, about 16M)	Large text / long text
mediumblob	0 ~ 16 777 215 (bytes, about 16M)	Binary
longtext	0 ~ 4 294 967 295 (bytes, about 4G)	Maximal text
longblob	0 ~ 4 294 967 295 (bytes, about 4G)	Maximal binary

Fixed length string: – - char is used when the length is fixed

-- example:
	-- create table user(
	--		username char(10),
	--		...
	-- )
result: 
	1) Set a string of length 10
	2) Store 3-character data
	3) There are 7 characters left
	4) This 7 character space is filled with spaces (Waste space)

Side length string: - used when the length of varchar is not fixed

-- in use varchar It is 65535 bytes. Actually, only 65532 bytes can be used
-- Actually:
-- 65535-1-2
-- -1 : varchar The input of is from the second character, and the first character is a space-1
-- -2 : varchar The head takes out two characters for statistics varchar Length of 65 -- Two characters

3. Date type

type	Size (bytes)	Range	format	purpose
date	3	1000-01-01 / 9999-12-31	YYYY-MM-DD	date
time	3	'-838:59:59' / '838:59:59'	HH:MM:SS	time
datetime	8	1000-01-01 00:00:00 / 9999-12-31 23:59:59	YYY-MM-DD HH:MM:SS	Time and date
timestamp	4	1970-01-0100:00:00/2038	YYYYMMDD HHMMSS	Date and introduction
year	1	1901 / 2155	YYYY	particular year

	timestamp: time stamp :  What is actually stored is a 1970-1-1 The time in milliseconds to the date time value

IV Join table query

(1) Multi table query

operation	describe	Name
Inner Join	If one of the tables matches, the row is returned	Inner connection
Left Join	According to the left table query, even if the right table has no, it will return a null	Left outer connection
Right Join	Query and display all data according to the right table. Even if there is no data in the left table, a null will be returned	Right outer connection
cross Join	Returns all rows of the left table. Each row of the left table is combined with all rows in the right table	Cross connection (Cartesian product connection)
outer Join	All rows and columns without values are returned and null	Full connection

Note:

(1). The two tables need to have an intersection

(2). Set an alias for a table to clear the fields that define a table

• Example: query name( study Table) id(study Table) age( A Table)    Spread point name
   select s.name ,s.id ，age 
   from study s inner Join A a 
   on s.name = a.name ; 
  

  select e.name name, d.name department, d.id Department ID

  from emp e inner Join dept d

  on e.dept_id=id;

Field analysis:

Two table query field analysis

join on where

Query name id Age     :  select s.name ,s.id ，age 
What table to query            :  from study s inner Join A a
 query criteria             :  on s.name = a.name
where(Re judgment)      :  where age is null           Query age is not equal to empty

Three table query field analysis

Query name id Age:
select a.name ，a.id ，age          
Two table query
from A a inner Join B b
 Intersection query criteria
on a.name = b.name
 The result of two table query is the same as that of the third table query
inner Join C c 
Intersection query criteria
on b.name = c.name

(2) Self connection

Connect your own table with your own table: the core table can be split into two identical tables

For example, there are one floor, two floors and three floors in your table. Each floor has a different company

The results are all in one table, so you need to sort the query yourself and yourself

Aid	pid	name
1	1	F1
4	1 (floor)	Company A
2	7	two-storied building
5	3 (floor)	Company B
3	5	Three storey building
6	2 (floor)	Company C
7	2 (floor)	D company

Requirement: a mapped field is required for query

Query criteria:

Query field
select a.name as 'floor' ， a2.name as 'company'
What table to query
From A a ，A a2 
query criteria
where a.Aid = a2.pid

V cascade

(6). update cascade

Syntax:

​ on update cascade

summary:
		surface(dept surface)When the primary key in is updated (for example, the sales department's id Change to 40),
		From table( emp The foreign key data recorded in table) will also follow the table(That is, the Department numbers of Zhao Liu and Liu Neng will also be updated to 40)

(7). cascading deletion

Syntax:

​ on delete cascade

summary:
	When cascade deletion is not added:
					When deleting a department in the Department table (for example, deleting department 4),
					If the Department has corresponding employees (Zhao Liu and Liu Neng) in the employee table, deletion will fail!
	    Add cascade delete 
					When deleting a department in the Department table, if the Department has a corresponding employee in the employee table,
					The corresponding employee in the employee table will also be deleted when the Department is deleted!

	create table emp(
         id int primary key auto_increment, -- Employee number -- Primary key auto increment
         name varchar(20), -- Employee name
         dept_id int, -- Department number
         foreign key(dept_id) references dept(id) -- Specify foreign key
         on update cascade -- update cascade 
         on delete cascade -- cascading deletion
	);
      insert into emp values(null, 'Zhang San', 1);
      insert into emp values(null, 'Li Si', 2);
      insert into emp values(null, 'Lao Wang', 3);
      insert into emp values(null, 'Zhao Liu', 4);