October 28, 2021 (MySQL database review)

catalogue

1. Database type:

2. Create table:

3. Modification table:

4. Management table:

4. Query table: select

where conditional clause

Fuzzy query;

Associated table query:

Self connection

Paging and sorting:

Subquery:

Grouping and filtering:

1. Database type:

numerical value

  • tinyint                 1 byte               Very small
  • smallint               2 bytes               less
  • mediumint           3 bytes               secondary
  • int                       4 bytes                     Standard data
  • bigint                   8 bytes                 Larger data
  • float                     4 bytes                 Floating point number
  • double                   8 bytes               Floating point number
  • decimal                 Floating point number in string form, financial calculation

character string:

  • char           String fixed size             0~2
  • varchar       Variable string               0~65535        
  • tinytext         Micro text                 The 8th power of 2 - 1
  • text               Text string                     The 16th power of 2 - 1        

Time date

  • date                 YYYY-MM-DD                 Date format
  • time                   HH: mm: ss                 Time format
  • datetime             Date plus format
  • timestamp         time stamp     From January 1, 1970 to now
  • year                 Year expression

Null: there is null in the operation   The results are null

Unsigned: unsigned integer (no negative number)

Zerofill: 0 fill

Note: each table must have the following five fields

  1.   id         Primary key
  2. version         Optimistic lock
  3. is_delete         Pseudo deletion
  4. gmt_create       Creation time
  5. gmt_update       Modification time

2. Create table:

-- AUTO_INCREMENT Self increasing
-- All statements are followed by ,(English),Don't add the last one
-- default Default value
-- notes
-- primary key Primary key: generally, a table has only one unique primary key;
-- Set engine ENGINE=INNODB
-- CHARSET code
CREATE TABLE teacher(
	`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT 'Student 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 'male' 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

show create database school           View the statement that created the database
show create table student             View the definition statement of student data table
desc student                          Displays the structure of the table

3. Modification table:

ALTER TABLE teacher RENAME AS teacher1     -- Modify table name
ALTER TABLE teacher ADD age INT(1) -- add field
ALTER TABLE teacher MODIFY age VARCHAR(11) -- modify constraints
Alter table teacher change age 1 int (1) -- field renaming;  

alter table teacher1 drop age1    -- Delete table fields

alter table name drop field name  

delete
drop table teacher1
drop table name

4. Management table:

insert

Foreign key (just understand): link another table through a foreign key.

A database is a simple table. It is only used to store rows and columns, only rows (data) and columns (fields)
We want to use multiple account tables and foreign keys (programs).

insert into table name ([field 1, field 2, field 3]) values ("value 1", "value 2", "value 3")

--Since the primary key is self incremented, you can fill in null or do not write this field
Insert into grade (gradename) values
Insert into grade (grade ID, grade name) values (null, "freshman")

--Generally, when inserting data, the data must correspond to the field one by one

--Insert multiple fields
Insert into grade (grade name) values ("junior"), ("senior")

Modify: update modify who (condition) set original value = new value

--Modify the student name with conditions
update `student` set name = "Xiao Ming" where id=1

--Modify multiple values
update `student` set name = "Xiao Ming", ` email`=“ bloghut@163.com " where id=1
--Modify student name without conditions
update student set name = "Xiao Ming"       // Will change all
--Syntax update table name set column_name=value,...[column_name=value]   where   [conditions]

Delete: delete

Syntax: delete   from table name [where condition]

--Delete data (avoid writing like this, all data will be deleted)
delete from student

--Delete specified data
delete from student where id = 1

delete from test -- does not affect auto increment

truncate test -- auto increment will return to zero

--Empty table
truncate `student`

4. Query table: select

  • Query all: select * from indicates
  • Query specified field: select field as alias from indicates
  • select distinct studentno from result     Duplicate attributes found, de duplication
  • SELECT CONCAT('name ', StudentName) AS' new name' FROM student       Splicing

select version() -- Query System Version (function)
select 100*3-1 as calculation result  -- Query calculation result (expression)
select @@AUTO_INCREMENT_INCREMENT  -- Query self increasing step size (variable)


--Students' scores can be viewed after points are raised
select studentno,studentresult+1 as from result

where conditional clause

Fuzzy query;

--Inquire about students surnamed Liu
--like combination,  % (represents 0 to any character)   _ (one character)  
select * from student where studentname like "Liu%"

--For the students surnamed Liu, there is only one word after them
select * from student where studentname like "Liu"

--Check the students with good words in the middle of their names
select *   from student where studentname like "% good%"

--========================== in (specific one or more values)===================
--Query student 1001 1002 1003
select * from student where studentno in(1001,1002,1003)

--Query students in Beijing
select * from student where address in ('anhui ',' Luoyang, Henan ')

--Query students with empty address
select * from student where address is null or address = ''

--Query students with birth date; cannot be blank
select * from student where borndate is not null

Associated table query:

Seven tables

Conditions for judgment: studentNo in student table = studentNo in grade table

Join on join query
where equivalent query

-- ============== Linked list query ==================
-- Query the students who took the exam (student number, name, subject number, score)

select s.studentno,studentname,subjectno,studentresult
from student as s inner join result as r
on s.studentno = r.studentno

--  right join
select s.studentno,studentname,subjectno,studentresult
from student as s right join result r
on s.studentno = r.studentno

--  left join
select s.studentno,studentname,subjectno,studentresult
from student as s left join result r
on s.studentno = r.studentno

-- Query absent students
select s.studentno,studentname,subjectno,studentresult
from student as s left join result as r
on s.studentno = r.studentno
where studentresult is null
inner joinIf there is at least one match in the table, the row is returned
right joinAll values will be returned from the right table, even if there is no match in the left table
left joinAll values are returned from the left table, even if there is no match in the right table

Self connection


  connect your own table with your own table. Core: split a table into two identical tables

-- Query parent-child information: split a table into two tables
select a.categoryName as Parent column,b.categoryName as Sub column
from category as a,category as b
where a.categoryid = b.pid

-- Query the grade information of the student (student number, name, grade name)
select studentno,studentname,gradename
from student,grade
where student.gradeid = grade.gradeid

-- Query the grade of the account (account name, grade name)
select subjectname,gradename
from `subject`,grade
where `subject`.gradeid = grade.gradeid


-- The query participates in the database structure-1 Information of students in the exam: student number, name, subject name and score

-- Which tables does the query data come from student , result ,subject
select student.studentno,studentname,subjectname,studentresult
from student 
inner join result 
on student.studentno = result.studentno 
inner join `subject` 
on result.subjectno = `subject`.subjectno 
where subjectname = 'database structure -1'

Paging and sorting:

order by: sort: ascending asc descending desc

Limit: limit 0,5 1~5, syntax: limit starting value, page size

Subquery:

-- Query student information (student number, name) with a score of no less than 80
-- Use join query
select distinct student.studentno,studentname
from student inner join result
on student.studentno = result.studentno
where studentresult >= 80



-- Query student information (student number, name) with a score of no less than 80
-- On this basis, a subject of advanced mathematics is added-2
-- Method 1: use sub query
select distinct student.studentno,studentname
from student inner join result
on student.studentno = result.studentno
where studentresult >= 80 and 
subjectno = (select subjectno from `subject` where subjectname = "Advanced mathematics-2")

-- Method 2: use nested sub query
select 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"
	)
)

-- Method 3: use internal connection query
select student.studentno,studentname
from student INNER JOIN result
on student.studentno = result.studentno
INNER JOIN `subject`
on result.subjectno = `subject`.subjectno
where subjectname = "Advanced mathematics-2" and studentresult >= 80

Grouping and filtering:

--Query the average score, highest score and lowest score of different courses
--Core (according to different courses)
select subjectname,avg(studentresult),max(studentresult),min(studentresult)
from result inner join `subject` 
on result.subjectno = `subject`.subjectno
GROUP BY result.subjectno  -- By what


--Query the average score, highest score, lowest score and average score greater than 80 of different courses
select subjectname,avg(studentresult),max(studentresult),min(studentresult)
from result inner join `subject` 
on result.subjectno = `subject`.subjectno
GROUP BY result.subjectno  -- By what
having avg(studentresult) > 80 

Tags: Java Database MySQL

Posted on Thu, 28 Oct 2021 10:56:59 -0400 by chrisio