catalogue
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
- id Primary key
- version Optimistic lock
- is_delete Pseudo deletion
- gmt_create Creation time
- 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 join | If there is at least one match in the table, the row is returned |
| right join | All values will be returned from the right table, even if there is no match in the left table |
| left join | All 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