Operation database

Operation database

Several basic database operation commands:

update user set password=password('123456')where user='root'; Change Password
flush privileges; Refresh database
show databases; Show all databases
use dbname；Open a database
show tables; show database mysql All tables in
describe user; Display table mysql In database user Column information of table
create database name; Create database
use databasename; Select database
exit; sign out Mysql
? Command keywords : ask for help
-- Indicates a comment

Operate database > operate tables in Database > operate data of tables in database
MySQL is not case sensitive

1. Create database

CREATE DATABASE [IF NOT EXISTS] hello;

2. Delete database

DROP DATABASE [IF EXISTS] hello

3. Use database

If your table name or field name is a special character, it needs to be wrapped with ` (i.e. ~ key)

USE `user`

4. View database

SHOW DATABASES--View all databases

Column type of database

numerical value

• tinyint very small data 1 byte
• smallint smaller data 2 bytes
• mediumint medium size 3 bytes
• int standard integer 4 bytes (common)
• bigint larger data 8 bytes
• float floating point number 4 bytes
• double floating point number 8 bytes (precision problem)
• decimal is a floating-point number in the form of string. It is generally used in financial calculation.

character string

• char string fixed size 0-255
• varchar variable string 0-65535 (common)
• Tiny text 2 ^ 8-1
• text string ^ 1-2

Time and date

• date YYYY-MM-DD, date
• time HH:mm:ss time format
• datetime YYYY-MM-DD HH:mm:ss the most commonly used time format
• The number of milliseconds from timestamp 1970.1.1 to now
• Year means year

null

• No value, unknown
• Note: do not use null for operation. The result is null

Field type of database

Unsigned

• Unsigned integer
• Declare that the column cannot declare a negative number

Zero fill

• 0 filled
• The length of 10 is 1 – 0000000001, and the insufficient digits are filled with 0

Auto increment

• Automatically + 1 on the basis of the previous record
• It is usually used to design a unique primary key index, which must be an integer
• You can customize the starting value and step size of the self increment of the primary key

not Null

• If it is set to not null, an error will be reported if it is not assigned a value

• NULL if not filled in, the default value is NULL

default

• Set default values

Create database table

--target:Create a schoo1 database
--Create student table(column,field)use SQL establish
--Student number int Login password varchar(20)full name,Gender varchar(2),date of birth(datatime)，Home address, emai1

--Note: use English()，Table names and fields should be enclosed as much as possible

-- AUTO_ INCREMENT Self increasing

--Strings are enclosed in single quotes!(Pay attention to distinction~key)

--All statements are followed by,(English)，Don't add the last one

-- PRIMARY KEY Primary key, general- A table has only one unique -Primary key of!
CREATE DATABASE school
CREATE TABLE IF NOT EXISTS `student` (
`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

format

CREATE TABLE [IF NOT EXISTS] `Table name`(
`Field name` Column type[attribute][Indexes][notes],
`Field name` Column type[attribute][Indexes][notes],
...
`Field name` Column type[attribute][Indexes][notes]
)[Table type][Character set settings for table][notes]

Common commands

SHOW CREATE DATABASE school -- View the statement that created the database
SHOW CREATE TABLE student -- see student Definition statement of data table
DESC student -- Displays the structure of the table

Type of data table

database engine
INNODB (default)
MYISAM (used in earlier years)

-	MYISAM	INNODB
Transaction support	I won't support it	support
Data row locking	I won't support it	support
Foreign key constraint	I won't support it	support
Full text index	support	I won't support it
Table space size	less	Larger, about twice that of MYISAM

General operation:

• MYISAM saves space and is fast
• INNODB has high security, transaction processing, multi table and multi-user operation

Where it exists in physical space
All database files are stored in the data directory, and a folder corresponds to a database
The essence is the storage of files

Differences of MySQL engine in physical files

1. innoDB has only one *. In the database table frm file and ibdata1 file in the parent directory

2. Files corresponding to MYISAM:

• *. frm - definition file of table structure
• *. MYD - data file
• *. MYI index file

Character set encoding database settings

CHARTSET=UTF8

If it is not set, it will be the default character set code of mysql - (Chinese is not supported)
Can be in my Ini to configure the default encoding

character-set-server=utf8

Modify delete table

modify

-- Modify table name ALTER TABLE Old surface AS New table name
ALTER TABLE student RENAME  AS student1
-- Add table fields ALTER TABLE Table name ADD Field column properties
ALTER TABLE student1 ADD age INT(11)
-- Modify the fields of the table (rename, modify constraints)
ALTER TABLE student1 MODIFY age VARCHAR(11)  -- Modify constraints
ALTER TABLE student1 CHANGE age age1 INT(1)  -- Field rename

-- Delete table fields
ALTER TABLE student1 DROP age1

delete

-- Delete table
DROP TABLE IF EXISTS student1

All creation and deletion operations should be judged as much as possible to avoid errors

Note:

• Field name, use ` ` package
• Notes –/**/
• The sql keyword is case insensitive. It is recommended to write it in lowercase
• All symbols are in English

MySQL data management

Foreign key

Method 1: * * add constraints when creating tables (troublesome and complex)

CREATE TABLE `grade`(
	`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade id',
	`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name',
	PRIMARY KEY (`gradeid`)
	)ENGINE=INNODB DEFAULT CHARSET=utf8
-- Student table gradeid Field to reference the grade table gradeid
-- Define foreign keys KEY
-- Add constraints (execute references) to this foreign key references quote
CREATE TABLE IF NOT EXISTS `student` (
	`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',
	`gradeid` INT(10) NOT NULL COMMENT 'Student grade',
	`address` VARCHAR(100) DEFAULT NULL COMMENT 'Home address',
	`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
	PRIMARY KEY (`id`),
	KEY `FK_gardeid` (`gradeid`),
	CONSTRAINT `FK_gardeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (gradeid)
	)ENGINE=INNODB DEFAULT CHARSET=utf8

When deleting a table with a foreign key relationship, you must first delete the referenced table (slave table) and then the referenced table (master table)

Method 2: add foreign keys after successfully creating the table

CREATE TABLE `grade`(
`gradeid` INT(10) NOT NULL AUTO_INCREMENT COMMENT 'grade id',
`gradename` VARCHAR(50) NOT NULL COMMENT 'Grade name',
PRIMARY KEY (`gradeid`)
)ENGINE=INNODB DEFAULT CHARSET=utf8

-- Student table gradeid Field to reference the grade table gradeid
-- Define foreign keys KEY
-- Add constraints (execute references) to this foreign key references quote
CREATE TABLE IF NOT EXISTS `student` (
`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',
`gradeid` INT(10) NOT NULL COMMENT 'Student grade',
`address` VARCHAR(100) DEFAULT NULL COMMENT 'Home address',
`email` VARCHAR(50) DEFAULT NULL COMMENT 'mailbox',
PRIMARY KEY (`id`)

)ENGINE=INNODB DEFAULT CHARSET=utf8

-- There is no foreign key relationship when creating a table
ALTER TABLE `student`
ADD CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade`(`gradeid`);

-- ALTER TABLE`surface`  ADD CONSTRAINT Constraint name FOREIGN KEY(Columns as foreign keys) refer to which fields of which table

The above operations are physical foreign keys, database level foreign keys, which are not recommended. (avoid trouble caused by too many databases)

Best practices
A database is a simple table. It only stores data, only rows (data) and columns (fields)
If we want to use the data of multiple tables and foreign keys, we usually use programs to realize them

DML language (all remember)

Meaning of database: data storage, data management

DML language: data operation language

• insert
• update
• delete

add to

grammar
insert into table name ([field 1], [field 2]) values('value 1 '), (' value 2 ')

INSERT INTO `grade` (`gradename`) VALUES('Senior')

-- Since the primary key increases automatically, we can omit it (if we don't write the fields of the table, they will match one by one)
INSERT INTO `grade` VALUES('Junior')INSERT INTO `grade` (`gradeid`,`gradename`) VALUES ('Junior','null')

-- Generally, when writing insert statements, we must correspond the data and fields one by one.
-- Insert multiple fields
INSERT INTO `grade`(`gradename`) 
VALUES ('Sophomore'),('Freshman');

INSERT INTO `student`(`name`) VALUES ('Zhang San')

INSERT INTO `student`(`name`,`pwd`,`sex`) VALUES ('Zhang San','aaaaa','male')

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('Li Si','aaaaa','male'),('Wang Wu','23232','female')

Syntax: insert into table name ([field 1], [field 2]) values('value 1 '), (' value 2 ')

matters needing attention:

1. Fields are separated by commas

2. Fields can be omitted, but the following values must correspond one by one

3. Multiple pieces of data can be inserted at the same time. The VALUES after VALUES need to be separated

INSERT INTO `student`(`name`,`pwd`,`sex`) 
VALUES ('Li Si','aaaaa','male'),('Wang Wu','23232','female')

modify

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

-- Modify student name
UPDATE `student` SET `name`='Xiao Ming' WHERE id =1;

-- All tables will be changed without specifying conditions
UPDATE `student` SET `name`='233'
-- Syntax: UPDATE Table name set column_name,[] = value where condition

Condition: where clause operator id is equal to a certain value and greater than a certain value. It can be modified in a certain interval

The operator returns a Boolean value

column_name is the column of the database, with``
If no filter criteria are specified, all columns will be modified
Value is a specific value or a variable
The attributes of multiple settings are separated by English commas

delete

grammar
delete from table name [where condition]

-- Delete data (Avoid writing like this)
DELETE FROM `student`
-- Delete assignment
DELETE FROM `student` where id= 1

TRUNCATE command

Function: completely empty a database, and the table structure and index will not change

Difference between delete and TRUNCATE

• Same point: data can be deleted without deleting table structure
• Different:
  TRUNCATE resets the auto increment counter to zero
  TRUNCATE does not affect transactions

-- test delete and truncate difference
CREATE TABLE `test`(
	`id` INT(4) NOT NULL AUTO_INCREMENT,
	`coll` VARCHAR(20) NOT NULL,
	PRIMARY KEY (`id`)
	)ENGINE=INNODB DEFAULT CHARSET=utf8
	INSERT INTO `test`(`coll`) VALUES('1'),('2'),('3')
	
	DELETE FROM `test` -- Will not affect self increment
	TRUNCATE TABLE `test` -- Auto increment will return to zero

Understand the problem of delete: after restarting the database, the phenomenon is as follows:

innoDB auto incrementing will start from 1 (it exists in memory and will be lost when powered off)
MyISAM continues from the previous increment (exists in the file and will not be lost)

DQL query data (most important)

DQL

(Data Query Language): Data Query Language

• It is used for all query operations
• It can do simple query and complex query
• The core language in database
• The most frequently used language

Specify query fields

Syntax: SELECT field... FROM table

-- query  SELECT field FROM surface
-- Query specified fields  
SELECT `StudentNo`,`StudentName` FROM student
-- Alias, give the result a name AS   You can alias fields or tables
SELECT `StudentNo` AS Student number,`StudentName`AS Student name FROM student AS S
-- function Concat(a,b)
SELECT CONCAT('full name:',StudentName) AS New name FROM student

Sometimes, listing names is not so obvious. We use alias AS field name AS alias table name AS alias

duplicate removal

-- Find out which students took the exam and got good grades
SELECT * FROM result 
-- Query all test scores
-- Check which students took the exam
SELECT `studentNo` FROM result 
-- Duplicate data found, de duplication
SELECT DISTINCT `studentNo` FROM result 

Database columns (expressions)

SELECT VERSION()  --Query System Version (function)
SELECT 100*3-1 AS Calculation results -- Used to evaluate (an expression)
SELECT @@auto_increment_increment --Query self increasing step size (variable)

-- Student examination results+1 Sub view
SELECT `StudentNo`,`StudentResult`+1 AS 'After raising points' FROM result

select expression from table

Expressions in the database include: text value, column, Null, function, calculation expression, system variable

where conditional clause

Logical operator

• Logic and:&&
  Syntax: a and b or a & & B

• Logical or:||
  Grammar: a or b or a|b

• Logical non:=
  Syntax: not a or! a

-- The query test score is between 95 and 100
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult >=95 AND StudentResult<=100
-- Fuzzy query (interval)
SELECT `StduentNo`,`StudentResult` FROM result
WHERE StudentResult BETWEEN 95 AND 100
-- Grades of students other than student 1000
SELECT `StduentNo`,`StudentResult` FROM result
WHERE NOT StudentNo = 1000

Comparison operator (fuzzy query)

operator	grammar	describe
IS NULL	a is null	If the operator is null, the result is true
IS NOT NULL	a is not null	If the operator is not null, the result is true
BETWEEN	a between b and c	True if a is between b and c
LIKE	a like b	SQL matches. If a matches b, it is true
IN	a in (a1,a2,a3...)	Suppose a is true in one of A1, A2 and A3

--  Inquire about students surnamed Liu-- like combination %((representing 0 to any character)  _(One character)
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu%';
-- For students surnamed Liu, there is only one word after their name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu_';
-- For students surnamed Liu, there are only two words after their name
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE 'Liu__';
-- Query the students with Jiazi in the middle of their names %Jia%
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentName LIKE '%Jia%';
===================IN(One or more specific values)===========================
-- Query 1001 1002 1003 student information
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE StudentNo IN (1001,1002,1003);

-- Query students in Anhui, Henan and Luoyang
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `Address` IN('Anhui','Luoyang, Henan');
===================NULL NOT NULL===================================
-- Query students whose address is empty null ''
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE address=''OR address IS NULL
-- Query students with birth date cannot be blank
SELECT `StudentNo`,`StudentName` FROM `student`
WHERE `BornDate` IS NOT NULL;

Join table query

JOIN

======================Join table query join ==============================
-- Query the students participating in the examination (student number, name, examination number, score)

SELECT * FROM student 
SELECT * FROM result

/*
1. Analyze the requirements, and analyze which tables the query fields come from
2.Determine which connection query to use? 7 kinds
 Determine the intersection (which data is the same in the two tables)
Conditions for judgment: studentNo in student table = studentNo in grade table 

*/

-- JION(Table) ON (Judged conditions) connection query
-- where Equivalent query
SELECT studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
INNER JOIN result AS r
WHERE s.studentNo=r.studentNo

--Right Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
RIGHT JOIN result AS r
ON s.studentNo = r.studentNo

--LEFT Join
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo

operation	describe
Inner join	If there is at least one match in the table, the row is returned
left join	Even if there is no match in the left table, all values will be returned from the left table
right jion	Even if there is no match in the right table, all values will be returned from the right table

-- Inquire about the students taking the exam
SELECT s.studentNo,studentName,SubjectNo,StudentResult
FROM student AS s
LEFT JOIN result AS r
ON s.studentNo = r.studentNo
WHERE StudentResult IS NULL

-- Inquired the information of the students participating in the examination: student number: Student Name: Subject Name: score
SELECT s.`studentNo`,`studentName`,`SubjectName`,`studentResult`
FROM student s
RIGHT JOIN result r
ON r.studentNo=s.studentNo
INNER JOIN `subject` sub
ON r.SubjectNo=sub.SubjectNo

-- What data do I want to query SELECT ....
-- From which tables FROM surface xxx JOIN Connected tables ON Cross condition
-- Suppose there are multiple tables in one query, first query the two chapter tables, and then add them slowly

--FROM a LEFT JOIN b   Zuo weizhun
--FROM a RIGHT JOIN b	Whichever is right

Self connection

Your own table is connected with your own table. Core: one table is split into two identical tables

The same table is divided into the following two tables

Parent class

categoryid	categoryName
2	information technology
3	software development
5	Art design

Subclass

pid	categoryid	categoryName
3	4	database
2	8	Office information
3	6	web development
5	7	ps Technology

Operation: query the relationship between parent and child classes (combine the above two tables into the following table)

Parent class	Subclass
information technology	Office information
software development	database
software development	web development
Art design	ps Technology

-- Query parent-child information

SELECT a.`categroryName` AS `Parent column`,b.`categroryName` AS `Sub column`
FROM `catgroy` AS a,`catgroy` AS b
WHERE a.`categoryid`=b.`pid`

-- Query the grade of the student (student number, student name, grade)
SELECT 	studentNo,studentName,gradeName
FROM student s
INNER JOIN `grade` g
ON s.`GradeId`=g.`GradeId`

Paging and sorting

============================paging limit And sorting order by=================


-- Sorting: ascending ASC  Descending order  DESC
SELECT  xx
FROM xx
JOIN xx
WHERE  xx
ORDER BY  xx
ASC   ||  DESC

paging
Current page size, syntax: limit

-- Why pagination
-- Ease the pressure of database and give people a better experience


-- Pagination, displaying five pieces of data per page

-- Syntax: limit Current page, page size
-- limit 0,5 1-5
-- limit 1,5 1-5
-- limit 6,5
SELECT s.`StudentNo`,`StudentName`,`SubjectName`,`StudentResult`
FROM student s
INNER JOIN `result` r
ON s.`StudentNo`=r.`StudentNo`
INNER JOIN `subject` sub
ON r.`subjectNo`=sub.`subjectNo`
WHERE subjectName='data structure-1'
ORDER BY StudentResult ASC
LIMIT 0,5

-- first page limit 0,5
-- Page 2 limit 5,5
-- Page 3 limit 10,5
-- The first N page limit 5*(n-1),5

Subquery

where (this value is calculated)

Essence: nest a subquery statement in the where statement

-- ===========================where=========================

-- 1.Query database structure-1 All examination structures (student number, subject number, score) in descending order
-- Method 1: connection query
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result` r
INNER JOIN `subject` sub
ON r.SubjectNo = sun.SubjectNo
WHERE subjectName = 'database structure -1'
ORDER BY StudentResult DESC

-- Method 2: use sub query(From inside to outside)
SELECT `StudentNo`,r.`SubjectName`,`StudentResult`
FROM `result`
WHERE StudentNo=(
	SELECT SubjectNo FROM  `subject` 
    WHERE SubjectName = 'database structure -1'
)
ORDER BY StudentResult DESC


-- Student number and name of students with a score of no less than 80
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80

-- On this basis, add a subject, advanced mathematics-2
SELECT DISTINCT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON r.StudentNo = s.StudentNo
WHERE StudentResult>=80 AND `SubjectNo`=(
    SELECT Subject FROM `subject`
    WHERE SubjectName='Advanced mathematics-2'
)

-- The inquiry course is advanced mathematics-2 Student number and name of students with a score of no less than 80
SELECT s.`StudentNo`,`StudentName`
FROM student s
INNER JOIN result r
ON s.StudentNo = r.StudentNo
INNER JOIN `subject` sub
ON r.`SubjectName`='Advanced mathematics-2'
WHERE `SubjectaName`='Advanced mathematics-2' AND StudentResult >=80


-- Remoulding (From inside to outside)
SELECT `StudentNo`,`StudentName` FROM student
WHERE StudentNo IN(
SELECT StudentNo result WHERE StudentResult >80 AND SubjectNo =(
SELECT SubjectNo FROM `subject` WHERE `SubjectaName`='Advanced mathematics-2'
)
)

grouping

-- Query the average score of different courses, the highest score, the lowest score, and the average score is greater than 80
-- Core: (grouped according to different courses)

SELECT `SubjectName`,AVG(StudentResult),MAX(StudentResult)
FROM result r
INNER JOIN `Subject` sub
ON r.SubjectNo=sub.SubjectNo

GROUP BY r.SubjectNo -- By what field
HAVING AVG(StudentResult)>80