Basic use of MySQL

Database introduction:

Carrier for data storage in the operating system:

Windows. Linux and macos are file based operating systems

Database system: refers to a system that can provide information services for users. It achieves organized. The function of dynamically storing a large amount of relevant data provides a convenient means for data processing and information resource sharing.

**Relational database: * * refers to a database system using a relational model

In the relational model, data is stored by category, and there can be connections between data

For example: student information, examination information, teacher information

Relational database is used in many fields: 1. Education system 2. Business system three. Medical system

Relational database can effectively organize and manage a large number of complex data. All relational databases are more important database products

Mainstream relational database: DB2 Oracle MySQL SQL Server

NoSQL database refers to a database system in which data is stored by category, but there is no association relationship between data

MySQL is the most widely used and popular open source relational database

Mysql

mysql:

MySQL is the most popular relational database management system. In terms of WEB application, MySQL is one of the best RDBMS(Relational Database Management System) application software.

What is a database?

Database is a warehouse that organizes, stores and manages data according to the data structure. Each database has one or more different API s for creating, accessing, managing, searching and copying the saved data. We can also store data in files, but the speed of reading and writing data in files is relatively slow. Therefore, now we use relational database management system (RDBMS) to store and manage the large amount of data. The so-called relational database is a database based on relational model, which processes the data in the database with the help of mathematical concepts and methods such as set algebra. RDBMS, namely relational database management system, features:

The data appears in tabular form
Name of each record
Each column is the data field corresponding to the record name
Many rows and columns form a form
Several forms form a database

mysql database:

MySQL is a relational database management system developed by MySQL AB company in Sweden. At present, it belongs to Oracle company. MySQL is an associated database management system. The associated database saves the data in different tables instead of putting all the data in a large warehouse, which increases the speed and flexibility.

MySQL is open source, so you don't have to pay extra.
MySQL supports large databases. It can handle large databases with tens of millions of records.
MySQL uses the standard SQL data language form.
MySQL can be used on multiple systems and supports multiple languages. These programming languages include C, C + +, Python, Java, Perl, PHP, Eiffel, Ruby and Tcl.
MySQL has good support for PHP, which is the most popular Web development language at present.
MySQL supports large databases and a data warehouse with 50 million records. The 32-bit system table file can support 4GB at most, and the 64 bit system can support 8TB at most.
MySQL can be customized. It adopts GPL protocol. You can modify the source code to develop your own MySQL system.

Basic database operation steps:

Create a database:

CREATE DATABASE zhangdong;

Delete a database:

DROP DATABASE zhangdong;

Create a zhang database using uft8 character set:

CREATE DATABASE zhang CHARACTER SET utf8;

Create a zd database with proofing rules using uft8 character set:

CREATE DATABASE zd CHARACTER SET utf8 COLLATE utf8_bin; #utf8_ This bin is case sensitive.

Querying the data in the database, (utf8_bin) is case sensitive. You can only find the contents in lower case. If it is the default, you can find the contents that match the case.

SELECT * FROM ti WHERE NAME = "zhangdong";

Delete and query database:

-- Show me how to delete and query a database
-- View all databases in the current database server
SHOW DATABASES
-- c View the definition information of the (database name) created earlier
SHOW CREATE DATABASE zhang
-- Delete the previously created database (in order to avoid keywords when creating databases and tables, you can use backquotes)
DROP DATABASE db_name;
-- Backup database and restore database
-- (stay dos Run in command)mysqldump -u root -p -B zhang zhangdong > d:\\abc.sql
-- Delete database
DROP DATABASE db_name；
-- sour d:\\abc.sql
-- To restore the database backup, you can run the files directly to generate a new database (that is, the original database)

-- Graphical creation and command creation
-- Note: when creating a table, create a response column according to the data to be saved, and define the corresponding column class according to the data type
-- id    integer
-- name   character string 
-- password character string
-- birthday  date
CREATE TABLE zhang (
id INT,
`name` VARCHAR(255),
`password` VARCHAR(255),
`birthday` DATE)
CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

Integer:

-- Demo integer
-- TINYINT   -128-127(Both signed and unsigned (0-255))
CREATE TABLE table_z(
id TINYINT
);
INSERT INTO table_z VALUES(-128);#This is a simple addition statement
-- INSERT INTO table_z VALUES(-129);#This is a simple addition statement
SELECT * FROM table_z

CREATE TABLE table_zh(
id TINYINT UNSIGNED
);#This is an unsigned
INSERT INTO table_zh VALUES(255);#This is an unsigned
INSERT INTO table_zh VALUES(-1);#This is an unsigned
SELECT * FROM table_zh


-- This is a demonstration int
CREATE TABLE table_zha(
id INT
)
INSERT INTO table_zha VALUES(65535);
INSERT INTO table_zha VALUES(-1);
SELECT * FROM table_zha

bit type usage

-- demonstration bit Type use
-- explain
-- 1.bit(m) m At 1-64
-- 2.Add data range
-- 3.Display by bit
-- 4.When querying, you can still query by number
CREATE TABLE table_zhan(num BIT(8));
INSERT INTO table_zhan VALUES(5);
SELECT * FROM table_zhan;
SELECT * FROM table_zhan WHERE NUM=255;


00000001
00000011
11111111
00000101

Demo data type decimal,float double use:

-- Presentation data type decimal,float double use
-- Create a table
CREATE TABLE table_zhangd(
num1 FLOAT,
num2 DOUBLE,
num3 DECIMAL(30,20)
)
-- Add data
INSERT INTO table_zhangd VALUES(88.1234567891234,88.1234567891234,88.1234567891234);
SELECT * FROM table_zhangd

DECIMAL(D,M)This range can be set by yourself, decimal The range of is larger than bigint Much bigger.
For example: (this number) binint It can't be stored, but decimal It can be stored) 89168198168518694189169891981898198198156

Demonstrates the use of string types:

-- demonstration
-- CHAR Fixed length is 255, charvarchar Length is 0-65535
-- 65535-3,Three bytes are not available
-- utf8 Encoding, storing characters in bytes (65535 bytes)-3 Divided by 3 is the number of characters stored) 21844
-- GBK Encoding, storing characters in bytes (65535 bytes)-3 Divided by 2 is the number of characters stored) 32766
-- Create a table
CREATE TABLE table_zhangdo(
num1 CHAR(255)
);

CREATE TABLE table_zhangdon(
num VARCHAR(32766)
)CHARACTER SET gbk;

-- Demonstrates the details of using string types
-- char(4)and varchar(4)Represents characters, not bytes,It doesn't distinguish whether the characters are Chinese characters or letters
CREATE TABLE table_zhangdong(
num1 CHAR(4));
-- Saving two characters will also allocate four characters of space
INSERT INTO table_zhangdong VALUES('abcd');
SELECT * FROM table_zhangdong;

CREATE TABLE table_1(
num1 VARCHAR(4)
);
INSERT INTO table_1 VALUES('Hello, Zhang Dong');
-- Spaces are also characters
-- Saving two characters will also allocate space for two characters (if you need a few, you will be allocated a few)
INSERT INTO table_1 VALUES('Na');
SELECT * FROM table_1;

-- If varchar Not enough, you can consider using it mediumtext perhaps longtext
-- If you want to be simple, use it text
CREATE TABLE table_2(
content TEXT,
content1 LONGTEXT,
content2 MEDIUMTEXT
);
INSERT INTO table_2 VALUES('Hello, world','Hello, world 2021!!!','Hello, world 2021');
SELECT * FROM table_2;

Related types of time

-- Related types of presentation time
-- date  datetime  TIMESTAMP
CREATE TABLE table_3(
-- birthday
brithday DATE,
job_time DATETIME,
login_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);
-- Contents in query table
SELECT * FROM table_3;
INSERT INTO table_3 (brithday,job_time) VALUES('2021-11-11','2021-11-11 00:00:00');
-- 2021-11-11	2021-11-11 00:00:00	2021-11-10 15:03:10

Create an employee table:

-- Create an employee table
-- id name sex brithday job_time salary resume
CREATE TABLE employee(
id INT,
`name` VARCHAR(32),
sex CHAR(1),
brithday DATE,
job_time DATETIME,
job VARCHAR(32),
salary DOUBLE,
resume 	TEXT 
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
-- Add data
INSERT INTO employee VALUES(
1998,'Zhang Dong','male','1998-08-07','2022-03-01 12:00:00','database',10000,'Responsible for database maintenance'
);
SELECT * FROM employee;

Add and modify column contents

--Add and modify column contents

ALTER TABLE employee ADD image VARCHAR(32) NOT NULL DEFAULT '' AFTER resume;
SELECT * FROM employee;
DESC employee;
-- Modify the type of column
ALTER TABLE employee MODIFY job VARCHAR(60);
desc employee;
-- Delete column
ALTER TABLE employee ADD pc VARCHAR(32) NOT NULL DEFAULT '' AFTER image;
ALTER TABLE employee DROP pc;
desc employee;
-- Modify table name
RENAME TABLE employee TO emp;
-- x Modify the character set of the table
ALTER TABLE emp CHARACTER SET utf8;
-- Modify class
ALTER TABLE emp CHANGE `name` `user_name` VARCHAR(32) NOT NULL DEFAULT '';
DESC EMP;

INSERT statement to add data

-- use INSERT Statement to add data
CREATE TABLE goods(
id INT,
goods_name VARCHAR(10),
price DOUBLE
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
-- Add data
INSERT INTO goods(id,goods_name,price)VALUES(1,'Biscuits',5);
INSERT INTO goods(id,goods_name,price)VALUES(2,'milk',4),(3,'Ham',2);
SELECT * FROM GOODS;

-- demonstration update sentence
-- Not used in statement where，Is to change all this column to 20000,
UPDATE emp SET salary = 20000;
SELECT * FROM emp;
-- use where You can specify one of the options to modify the content
UPDATE emp SET salary = 30000 WHERE user_name = 'Zhang Dong';

INSERT INTO emp VALUES(
2000,'Zhao Si','male','1990-08-07','2022-03-01 12:00:00','development',10000,'Responsible for database development','picture'
);
-- Add ten thousand to the original salary
UPDATE emp SET salary = salary+10000 WHERE user_name = 'Zhao Si';

-- demonstration delete sentence
-- Delete the content of Zhao Si
DELETE FROM emp WHERE user_name= 'Zhao Si';
SELECT * FROM emp;
-- Delete all tables
DELETE FROM tablename;

-- demonstration select sentence
CREATE TABLE student(
id int not null DEFAULT 1,
`name` VARCHAR(20) not null DEFAULT '',
ch FLOAT not null DEFAULT 0.0,
en FLOAT not null DEFAULT 0.0,
math FLOAT not null DEFAULT 0.0
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;
INSERT INTO student(id,name,ch,en,math) VALUES(01,'Zhang San',88,69,50),(02,'Zhang Si',50,89,99),(03,'Zhang Wu',85,65,56),(04,'Zhang Liu',85,99,80);
SELECT * FROM student;

-- Query the information of all students in the table
SELECT * FROM student;
-- Check the English scores of all students
SELECT `name`,en FROM student;
-- duplicate removal distinct
SELECT DISTINCT FROM student;

-- Query the total score of each student
SELECT `name`,(ch+en+math) FROM student;
-- Add 10 points to the total score of all students
SELECT `name`,(ch+en+math+10) FROM student;
-- Use aliases to represent student scores
SELECT `name`,(ch+en+math+10)AS ac FROM student;
SELECT `name`AS 'name',(ch+en+math+10)AS 'Total score' FROM student;

-- Query student scores by name
SELECT * FROM student WHERE `name` = 'Zhang San';
-- People with query scores greater than 80
SELECT * FROM student WHERE en>80;
-- Query people with a total score greater than 230
SELECT * FROM student WHERE(ch+en+math)>230;
-- When querying the contents of two requirements, use AND Connect two requirements. Students with surnames can use (Zhang)%)
-- BETWEEN Is a closed interval between....and....
-- The query meets three arbitrary requirements or Connection, you can also use IN(Interval)

-- select Medium order by sort   ASC Ascending order    DESC Descending order
SELECT * FROM student ORDER BY en ASC;
SELECT `name`,(ch+en+math)AS 'Total score' FROM student ORDER BY 'Total score' ASC;

Use of statistical functions:

-- demonstration mysql Use of statistical functions
-- Statistics student How many students are there
SELECT COUNT(*) FROM student;
-- How many students have statistical mathematics scores greater than 80?
SELECT COUNT(*) FROM student WHERE math >80;
-- How many people have a total score of more than 230?
SELECT COUNT(*) FROM student WHERE (en+ch+math)>230;
-- count(*)and count(column)Differences between
-- Explanation: count(*)Returns the number of rows of records that meet the criteria
-- count(column):Count how many columns meet the conditions, and they will be excluded on time null

CREATE TABLE table_4(
`name` VARCHAR(20)
);
INSERT INTO table_4 VALUES('tom'),('jack'),('mary'),(NULL);
SELECT * FROM table_4;
SELECT COUNT(*) FROM table_4;
SELECT COUNT(`name`) FROM table_4;

-- Count the math scores of a class
SELECT SUM(math) FROM student; 
-- Count the total score of each subject
SELECT SUM(math)AS 'Total math score',SUM(en)AS 'Total English score',SUM(ch)AS 'Chinese total score'FROM student;
-- Statistics of class grades of various subjects
SELECT SUM(math+en+ch) FROM student;
-- Count the average Chinese score of a class
SELECT SUM(ch)/8 FROM student;

-- Find the math average of a class
SELECT AVG(ch) FROM student;
SELECT AVG(math+en+ch) FROM student;

-- demonstration max   min
SELECT MAX(math+en+ch),MIN(math+en+ch) FROM student;

Use of string related functions:

-- Demonstrates the use of string related functions
-- CHARSET(str)Returns the string character set
SELECT CHARSET(`name`) FROM student;
-- CONCAT(str1,str2,...)Connection string
SELECT CONCAT(`name`,'match score',math) FROM student;
-- Convert case to uppercase( UCASE(str))  Convert lowercase( LCASE(str))
SELECT UCASE(`name`) FROM student;   -- It can only be converted if it contains lowercase letters
SELECT LCASE(`name`) FROM student;    -- It can only be converted if it contains uppercase letters
-- Take characters from left and right
SELECT RIGHT(`name`,1) FROM student;
SELECT LEFT(`name`,1) FROM student;
-- Returns the length of bytes
SELECT LENGTH(`name`) FROM student;
-- REPLACE replace content

-- LTRIM(str)     RTRIM(str)   Take out the left and right spaces
SELECT LTRIM('  Zhang Dong   ') FROM DUAL;
SELECT RTRIM('     Zhang Dong   ') FROM DUAL;
SELECT TRIM('     Zhang Dong   ') FROM DUAL;

Demonstrate mathematical correlation functions:

Demonstrate mathematical correlation functions
-- ABS(-10)  absolute value
SELECT ABS(-10) FROM DUAL;
-- CEILING(X) Round up
SELECT CEILING(1.5) FROM DUAL;
SELECT CEILING(-1.5) FROM DUAL;
-- BIN Decimal to binary
SELECT BIN(10) FROM DUAL;
-- conv Conversion between hexadecimals
-- 8 It's decimal. Convert decimal 8 to binary
SELECT CONV(8,10,2) FROM DUAL;
SELECT CONV(8,16,10) FROM DUAL;
-- Find the minimum value
SELECT LEAST(0,10,-1,-5,50,90,100) FROM DUAL;
-- Seeking remainder
SELECT MOD(100,3) FROM DUAL;
-- RAND()Returns a random number with a range of 0<=v<=1.0;
SELECT RAND(3)FROM DUAL;

Time date correlation function:

-- Time date correlation function
-- CURRENT_DATE  current date
SELECT CURRENT_DATE() FROM DUAL;
-- CURRENT_TIME  current time 
SELECT CURRENT_TIME() FROM DUAL;
-- CURRENT_TIMESTAMP  Current timestamp
SELECT CURRENT_TIMESTAMP() FROM DUAL;
-- Create test table
CREATE TABLE mes(
id INT,
content VARCHAR(30),
send_time DATETIME
);
-- Add a piece of data
INSERT INTO mes VALUES(1,'1 Journalism',CURRENT_TIMESTAMP());
SELECT * FROM mes;
-- add statictis
INSERT INTO mes VALUES(2,'2 Journalism',NOW());
-- Display news information, release date, only date, not time
SELECT id,content,DATE(send_time) FROM mes;
-- Display information published within ten minutes
SELECT * FROM mes WHERE DATE_ADD(send_time,INTERVAL 10 MINUTE)>=NOW()
-- Calculate the difference between August 7, 2022 and August 7, 1998
SELECT DATEDIFF('2022-8-7','1998-8-7')FROM DUAL;
-- Find out how long you've lived
SELECT DATEDIFF(NOW(),'1998-08-07') FROM DUAL;
-- If you live to be 80, find out how many days you can live,
SELECT DATEDIFF(DATE_ADD('1998-08-07',INTERVAL 80 YEAR),NOW()) FROM DUAL;

-- YEAR|MONTH|DAY|DATE (DATETIME)
SELECT YEAR(NOW()) FROM DUAL;
SELECT MONTH(NOW()) FROM DUAL;
SELECT DAY(NOW()) FROM DUAL;
SELECT MONTH('2021-10-1') FROM DUAL;
-- UNIX_TIMESTAMP() The return is 1970-1-1 Milliseconds to now
SELECT UNIX_TIMESTAMP()FROM DUAL;
-- FROM_UNIXTIME()  :You can put one unix_timestamp Seconds, converted to a date in the specified format
SELECT FROM_UNIXTIME(1636789308,'%Y-%m-%d')FROM DUAL;
SELECT FROM_UNIXTIME(1636789308,'%Y-%m-%d %H:%i:%s')FROM DUAL;

Demonstrate encryption and system functions:

-- Demonstrate encryption and system functions
-- USER()   Query user,You can view those users and who logged in ip
SELECT USER() FROM DUAL;
-- DATABASE() Query the currently used database name
SELECT DATABASE();
-- MD5(STR)   Calculate a value for the string md5  32 String, commonly used (user password) encryption
SELECT MD5('zhang') FROM DUAL;
SELECT LENGTH(MD5('zhang')) FROM DUAL;-- Query length

-- Demo user table, when storing passwords md5
CREATE TABLE table_user(
id INT,
`name` VARCHAR(32) NOT NULL DEFAULT ' ',
`password` CHAR(32) NOT NULL DEFAULT ' '
);
-- insert data
INSERT INTO table_user VALUES(100,'Zhang Dong',MD5('123456'));
SELECT* FROM table_user;
-- query
SELECT * FROM table_user WHERE `name`  = 'Zhang Dong' AND `password`=MD5('123456');

SELECT PASSWORD('123456') FROM DUAL;
-- Encrypted password:*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9

Process control statement

-- Demonstrate process control statements
-- IF(expr1,expr2,expr3),If exper1 by true,Then return exper2 Otherwise return exper3;
SELECT IF(TRUE,'correct','error') FROM DUAL;
-- IFNULL(expr1,expr2)If exper1 Not empty null Then return exper1,Otherwise return exper2
SELECT IFNULL(NULL,'Empty') FROM DUAL;

-- Multiple branch structure
SELECT CASE
				WHEN FALSE THEN 'ERROR'
				WHEN TRUE THEN 'RIGHT'
				ELSE 'NULL' END

-- Multiple branch structure
SELECT CASE
				WHEN FALSE THEN 'ERROR'
				WHEN TRUE THEN 'RIGHT'
				ELSE 'NULL' END  
-- Paging query
SELECT * FROM student ORDER BY `name` LIMIT 0,3;
SELECT * FROM student ORDER BY `name` LIMIT 3,3;
SELECT * FROM student ORDER BY `name` LIMIT 6,3;

Demonstration of sub table query:

-- Demonstration of subquery
-- Think about: how to display and**All employees in the same department
-- 1.Query first**de Department number obtained

INSERT INTO student VALUES(10,'Zhang Dong',90,80,100);

SELECT id AS 'ID by:'
       FROM student 
			 WHERE en=80;
		 
SELECT * 
				FROM student 
				WHERE id = (
							SELECT id 
							FROM student 
							WHERE en=80)
-- 		Multiple-column subqueries 					
SELECT * 
			FROM student
			WHERE(ch,en,math) = (
			SELECT ch,en,math 
			FROM student 
			WHERE `name` = 'Zhang Dong')

Replication and de duplication of tables:

-- Replication and de duplication of tables
-- Self replicating data (worm replication)
-- How to delete duplicate records in a table
CREATE TABLE table_5 (
id INT,
`name` VARCHAR(32),
sal DOUBLE,
job VARCHAR(32),
deptno INT
);
DESC table_5;
SELECT * FROM table_5;

-- How to self copy
-- 1.How to put student Copy to table_6 in

CREATE TABLE table_6(
id int not null DEFAULT 1,
`name` VARCHAR(20) not null DEFAULT '',
ch FLOAT not null DEFAULT 0.0,
en FLOAT not null DEFAULT 0.0,
math FLOAT not null DEFAULT 0.0
)CHARACTER SET utf8 COLLATE utf8_bin ENGINE INNODB;

INSERT INTO table_6(id,`name`,ch,en,math)
			SELECT id,`name`,ch,EN,math FROM student;
SELECT * FROM table_6;
-- Self replication
INSERT INTO table_6
			SELECT * FROM table_6;
SELECT COUNT(*) FROM table_6;   -- 2304 Data bar

-- How to delete duplicate records in a table (de duplication)
-- 1.First create a table, table_7,Have duplicate records
CREATE TABLE table_7 LIKE table_6;  
-- Copy the contents (columns) of this table to the new table
INSERT INTO table_7 SELECT * FROM student;
SELECT * FROM table_7;

-- How to solve the problem?
/*
Idea:
1.First create a temporary table, which has the same table structure as table_7
2.After the records of the temporary table are processed by the distinct keyword, the records are copied to the temporary table
3.Clear the duplicate records in the temporary table,
4.Copy the records in the temporary table to table_7
5.DROP Delete temporary table
*/
-- 1.First create a temporary table, and table_7 The table structure is the same
CREATE TABLE my_temp LIKE table_7;
-- 2.Pass the record of the temporary table distinct After keyword processing, copy the record to the temporary table
INSERT INTO my_temp SELECT DISTINCT * FROM table_7;
-- 3.Clear the duplicate records in the temporary table,
DELETE FROM 	table_7;
-- 4.Copy records from temporary tables to table_7 in
INSERT INTO table_7 SELECT * FROM my_temp;
-- 5.DROP Delete temporary table
DROP TABLE my_temp;

SELECT * FROM table_7;

-- Merge query -- UNION   UNION ALL

External connection:

/*
External connection:
1.Left outer connection (if the table on the left is completely displayed, we say it is left outer connection)
2.Right outer connection (if the table on the right is completely displayed, we say it is right outer connection)
*/
-- Create two tables
-- 1.stu   2.exam 
CREATE TABLE stu(
id INT,
`name` VARCHAR(32)
);
INSERT INTO stu (id,`name`)VALUES (1,'Classmate Zhao'),(2,'Tian classmate'),(3,'Classmate sun'),(4,'Classmate Li'),(5,'Classmate Zhou');
-- Test the data added by the left outer connection
INSERT INTO stu VALUES(6,'Classmate Wu');
SELECT * FROM stu;

CREATE TABLE exam(
id INT,
grade INT
);
INSERT INTO exam (id,grade)VALUES (1,80),(2,70),(3,90),(4,60),(5,100);
-- Test the data added by the right external connection
INSERT INTO exam (id,grade)VALUES (6,86),(7,77);
SELECT * FROM exam;

-- Use the left outer link (to display everyone's score. If there is no score, the person's name and address should also be displayed id，Score display (blank)
SELECT `name`,stu.id,grade FROM stu,exam WHERE stu.id = exam.id;
-- Change to left external connection, the left table is fully displayed, and if there is no data, it will be displayed as null
/*
SELECT .... FROM table_1 LEFT JOIN table_2 ON Condition, table_1 is the left table, and table_2 is the right table
*/
SELECT `name`,stu.id,grade FROM stu LEFT JOIN exam ON stu.id = exam.id;

-- Using the right external connection, the right side is fully displayed. If there is no corresponding data, it will be displayed as null；
/*
SELECT .... FROM table_1 RIGHT JOIN table_2 ON Condition, table_1 is the left table, and table_2 is the right table
*/
SELECT `name`,stu.id,grade FROM stu RIGHT JOIN exam ON stu.id = exam.id;

/*
In the actual development, the connection we have learned (inner connection) is used in most cases,
*/

Primary key:

-- mysql Constraints of:
-- Basic introduction
/*
Constraints are used to ensure that the database meets specific business rules

PRIMARY KEY(Primary key) - basic usage
 Field name field type PRIMARY KEY
 It is used to uniquely identify the data representing the row. When the constraint of the primary key is defined, the column cannot be repeated
*/
-- PRIMARY KEY
CREATE TABLE table_8(
id INT PRIMARY KEY,  --  express id Columns are primary keys
`name` VARCHAR(32),
email VARCHAR(32)
);
-- The value of the primary key column cannot be duplicate and cannot be empty null
INSERT INTO table_8(id,`name`,email)VALUES (1,'Zhang Dong','1791182296@qq.com');
INSERT INTO table_8(id,`name`,email)VALUES (2,'zhangdong','1791182296@souhu.com');
-- INSERT INTO table_8(id,`name`,email)VALUES (1,'Zhang Dong','1791182296@qq.com');
-- INSERT INTO table_8(id,`name`,email)VALUES (1,'Zhang Dong','1791182296@qq.com')
-- > 1062 - Duplicate entry '1' for key 'PRIMARY'
-- > time: 0.001s
/*
There can be at most one primary key in a table, but it can be a composite primary key
 There are two ways to specify a primary key
*/
-- Show me composite primary keys( id and name Make a composite primary key) the two must be the same to violate the composite primary key principle
CREATE TABLE table_9(
id INT,
`name` VARCHAR(32),
email VARCHAR(32),
PRIMARY KEY(id,`name`) -- Here is the composite primary key
);

INSERT INTO table_9(id,`name`,email)VALUES (1,'Zhang Dong','1791182296@qq.com');
INSERT INTO table_9(id,`name`,email)VALUES (2,'Zhang Dong','1791182296@souhu.com');
-- INSERT INTO table_9(id,`name`,email)VALUES (1,'Zhang Dong','1791182296@qq.com');
-- INSERT INTO table_9(id,`name`,email)VALUES (1,'Zhang Dong','1791182296@qq.com')
-- > 1062 - Duplicate entry '1-Zhang Dong' for key 'PRIMARY'  Composite primary key violation
-- > time: 0s

Use of NOT NULL non empty UNIQUE

-- NOT NULL  Non empty
-- UNIQUE Use of
CREATE TABLE table_10(
id INT UNIQUE, -- express id Columns cannot be repeated.
`name` VARCHAR(32),
email VARCHAR(32)
);

INSERT INTO table_10 VALUES(1,'Zhang Dong','1791182296@qq.com');
-- INSERT INTO table_10 VALUES(1,'Zhang Dong','1791182296@qq.com')
-- > 1062 - Duplicate entry '1' for key 'id'
-- > time: 0.009s
-- Usage details: if not specified not null ,be unique There can be more than one field null
-- A table can have more than one unique Field( unique+not null The effect is similar to primary key)

Foreign key:

-- mysql constraint  
-- Foreign key presentation:
-- Create master table (class table)
CREATE TABLE class(
id INT PRIMARY KEY,
`name` VARCHAR(32)NOT NULL DEFAULT ''
);
-- Create slave table (student table)
CREATE TABLE c_student(
id INT PRIMARY KEY,  -- Student number
`name` VARCHAR(32)NOT NULL DEFAULT '',
class_id INT,  -- Student's class number
-- Specify the foreign key relationship below
FOREIGN KEY(class_id) REFERENCES class(id));
INSERT INTO class VALUES(100,'java'),(200,'H5');
INSERT INTO c_student VALUES (1,'tom',100),(2,'jack',200);
-- Add 300 and the following statement will succeed INSERT INTO class VALUES(300,'PHP');

-- INSERT INTO c_student VALUES (3,'hu',300)
-- > 1452 - Cannot add or update a child row: a foreign ke y constraint fails (`zhang`.`c_student`, CONSTRAINT `c_student_ibfk_1` FOREIGN KEY (`class_id`) REFERENCES `class` (`id`))
-- > time: 0.011s
SELECT * FROM CLASS;
SELECT * FROM c_student;

Use of check:

-- demonstration check Use of
-- mysql 5.7 I won't support it check，Only syntax verification is performed, but it will not take effect
-- test
CREATE TABLE table_11(
id INT PRIMARY KEY,
`name` VARCHAR(32),
sex VARCHAR(6)CHECK(sex IN('man','woman')),
sal DOUBLE CHECK(sal>1000 AND sal<2000)
);

INSERT INTO table_11 VALUES(1,'jack','mid',1);
SELECT * FROM table_11;

-- Exercise (store table)
CREATE DATABASE table_shop;
-- Commodity list goods
CREATE TABLE goods(
goods_id INT PRIMARY KEY,
goods_name VARCHAR(64) NOT NULL DEFAULT '',
unitprice DECIMAL(10,2) NOT NULL DEFAULT 0
CHECK (unitprice>=1.0 AND unitprice<=9999),
category INT NOT NULL DEFAULT 0,
provider VARCHAR(64) NOT NULL DEFAULT ''
);
-- Customer table customer
CREATE TABLE customer(
customer_id INT PRIMARY KEY,
customer_name VARCHAR(64) NOT NULL DEFAULT '',
address VARCHAR(64) NOT NULL DEFAULT '',
email VARCHAR(64) UNIQUE NOT NULL,
sex ENUM('male','female')NOT NULL,
card_Id CHAR(18)
);
-- Order form( purchase)
CREATE TABLE purchase(
order_id INT UNSIGNED PRIMARY KEY,
customer_id CHAR(8)NOT NULL DEFAULT '',
goods_id INT NOT NULL DEFAULT 0,
nums INT NOT NULL DEFAULT 0,
FOREIGN KEY(customer_id) REFERENCES customer(customer_id),
FOREIGN KEY(goods_id) REFERENCES goods(goods_id)
);
DESC goods;
DESC customer;

Self growth:

-- Demonstrate self growth
-- Field name integer primary key auto_increment 
-- Add self growth mode
CREATE TABLE table_12(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
DESC table_12;
-- Test the use of self growth
INSERT INTO table_12 VALUES(NULL,'1791182296@qq.com','zd');

INSERT INTO table_12(email,`name`) VALUES('1791182296@SOUHU.COM','ZD');
SELECT * FROM table_12;
-- Self growth and primary key are used together, UNIQUE It can also be used together
-- Self growth can also be used alone 

-- The actual value of self increment can be set by yourself
ALTER TABLE table_13 AUTO_INCREMENT=100
CREATE TABLE table_13(
id INT PRIMARY KEY AUTO_INCREMENT,
email VARCHAR(32) NOT NULL DEFAULT '',
`name` VARCHAR(32) NOT NULL DEFAULT ''
);
INSERT INTO table_13 VALUES(NULL,'1791182296@qq.com','zd');
INSERT INTO table_13(email,`name`) VALUES('1791182296@SOUHU.COM','ZD');
INSERT INTO table_13 VALUES(666,'1791182296@SOUHU.CO','ZD1');
-- If 666 is added id The serial number of will increase from 666.
SELECT * FROM table_13;
-- If self increment is set, data will be added according to the self increment rule. This is better

mysql index:

-- mysql Indexes:
-- For the high performance of database, index is the best and cheapest thing.
-- Create a test database
--  Type of index
/*
1.Primary key index. The primary key is automatically the primary index (type: primary key)
2.UNIQUE index
3.General INDEX (INDEX)
4.FULLTEXT is applicable to mylsam (an article has many words. Search for a Chinese word in it and create a full-text index. Generally, SOLR and ELASTICSEARCH(ES), the indexes provided by mysql, are not used)
1.1 CREATE table_1(ID INT PRIMARY KEY)primary key 
2.1 ID INT UNIQUE Is unique
*/
-- demonstration mysql Index of
-- Create index
CREATE TABLE table_14(
id INT,
`name` VARCHAR(32)
);
-- Whether the query table has an index
SHOW INDEXES FROM table_14;
-- Add index
-- Add unique index (unique constraint) id Unique indexes are preferred without duplication
CREATE UNIQUE INDEX id_index ON table_14(id); -- 0 Represents a unique index, and 1 represents a non unique index
-- Create normal index
CREATE INDEX id_index ON table_14(id);
-- The second common index method
ALTER TABLE table_14 ADD INDEX id_index(id);
-- How to select an index
/*
1.If the value of a column will not be repeated, the unique index is preferred, otherwise the ordinary index is used
*/

-- Add primary key index
CREATE TABLE table_15(
id INT,
`name` VARCHAR(32)
);
ALTER TABLE table_15 ADD PRIMARY KEY(id);
SHOW INDEX FROM table_15;

-- Delete index
DROP INDEX id_index ON table_14;
-- Delete primary key index
ALTER TABLE table_15 DROP PRIMARY KEY;
-- To modify an index is to delete it before adding it

-- Query index
-- 1.mode
SHOW INDEX FROM table_15;
-- 2.mode
SHOW INDEXES FROM table_15;
-- 3.mode
SHOW KEYS FROM table_15;
-- 4.mode
DESC table_15;

mysql transaction:

-- mysql affair
/*
Transaction:
Transaction is used to ensure the consistency of documents. It is composed of a group of related dml statements. The reorganized dml statements either succeed or fail. Transfer is handled by transaction to ensure consistency
 Transactions and locks:
When executing transaction operations, mysql will lock the table to prevent other users from the structure of the table, which is very important to users
*/

-- 1.Transaction is an important concept and specific operation
-- 2.
-- Create a test table
CREATE TABLE table_16(
id INT,
`name` VARCHAR(32)
);
-- 1.Start transaction
START TRANSACTION;
-- 2.Set a save point a
SAVEPOINT a;
-- 3.implement dml operation
INSERT INTO table_16(id,`name`) VALUES (1,'TOM');
SELECT * FROM table_16;
-- Set another save point b
SAVEPOINT b;
-- implement dml operation
INSERT INTO table_16(id,`name`) VALUES(2,'jack');
-- Back to b
ROLLBACK TO b;
-- You can still go back a
ROLLBACK TO a;
-- If rollback If nothing is attached, all transactions will be rolled back.
-- Direct fallback to a Point can't go back b Points and intermediate save points will be deleted.
-- Commit transaction, COMMIT Delete the savepoint, release the lock, and generate the final data. All data is officially effective

-- mysql Transaction details:
-- If you do not start a transaction, by default, dml The operation is submitted automatically and cannot be returned

-- Isolation level of transaction:
-- 1.When each transaction is opened through a connection to operate the data in the database, the database system shall be responsible for the separation operation to ensure the accuracy of each connection in obtaining data.
-- 2.If isolation is not considered, the following problems may arise:
-- Dirty reading, unrepeatable reading, unreal reading

-- demonstration mysql Isolation level of things
/*
1.Two command boxes mysql console are opened
2.View the isolation level of current mysql
SELECT @@tx_isolation;
 SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)


3.Set the isolation level of one of the consoles to read UNCOMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set, 1 warning (0.00 sec)
*/
CREATE TABLE `account`(
id INT,
`name` VARCHAR(32),
money INT
);
INSERT INTO `account` VALUES(1,'zd',1000);

 CREATE TABLE table_17(id INT,`name` VARCHAR(32)NOT NULL DEFAULT '',sal DOUBLE);
 CREAT TABLE table_18(id INT,`name` VARCHAR(32),money DOUBLE);
 
--  View the current isolation level
SELECT @@tx_isolation;
-- View the current isolation level of the system
SELECT @@global,tx_isolation
-- Sets the isolation level for the current session
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

If it is not set, the transaction isolation level is repeatable by default

Table type and storage engine:

-- Table type and storage engine
-- View all storage engines
SHOW ENGINES;
-- INNODB  Support transactions, support foreign keys, and support row level locks
-- myisam 
CREATE TABLE table_19(
id INT ,
`name` VARCHAR(32)) ENGINE MYISAM;
-- 1.Foreign keys and transactions 2 are not supported.Fast addition    3.Table level locks are supported
START TRANSACTION;
SAVEPOINT T1;
INSERT INTO table_19 VALUES(1,'ZD');
SELECT * FROM table_19;
ROLLBACK TO T1;

-- MEMORY Storage engine
-- 1.Data is stored in memory 2.Fast execution, no io Reading and writing 3.Default supported index( hash Table)
CREATE TABLE table_20(
id INT ,
`name` VARCHAR(32)) ENGINE MEMORY;

INSERT INTO table_20 VALUES(1,'TOM'),(2,'JACK'),(3,'ZD');
SELECT * FROM table_20;
-- close mysql The service data will disappear, but the table structure is still there
-- 2.Users can use it when they are online memory Engine (memory storage engine, which will disappear as soon as the service is closed)
-- 1.If your application does not require transactions, myisam Is the first choice

-- Modify storage engine(Instruction modification)
ALTER TABLE 'table_name' ENGINE = 'Engine name';

View:

-- View:
-- A view is a virtual table whose contents are defined by a query. Like real tables, views contain columns whose data comes from the corresponding real table (base table)

-- Create a view
CREATE VIEW emp_view AS SELECT id,user_name,sex,job FROM emp;
-- view a chart
DESC emp_view;
SELECT * FROM emp_view;
-- If you look for other columns, you won't find them
-- View instructions for creating views
SHOW CREATE VIEW emp_view;
-- Delete view
DROP VIEW view_name;

-- Modify view
UPDATE view_name 
					SET job = 'Modified content'
				WHERE id = 'id name';
UPDATE emp_view SET job = 'java engineer' WHERE id ='1';	
SELECT * FROM emp_view;

SELECT * FROM emp;
-- Modifying the view will affect the base table (that is, the original data table). Modifying the base table will also affect the view
UPDATE emp SET job = 'database' WHERE id ='1';	

-- Views can also be used in views
DESC emp_view;
-- from emp_view Make a new view in,
CREATE VIEW emp_view1 AS SELECT id,user_name FROM emp_view;
DESC emp_view1;
SELECT * FROM emp_view1;

mysql Management:

-- mysql Administration
-- mysql
/*
1.host The location where login is allowed. localhost means that the user is only allowed to log in locally. You can also specify an ip address, such as 192.168.12.12
2.user user name
3.authentication_string: Password is the password encrypted through the password() function of mysql
*/
/*
Create user:
CREATE USER 'User name '@' allowed login location '
*/
-- mysql User management
-- Reason: when we do project development, we can assign it corresponding resources according to different developers mysql Operation authority
-- therefore mysql The database administrator creates different users according to needs and assigns corresponding permissions for personnel to use
-- Create a new user
-- 1.'zhangdong'user name@'localhost'Logged in IP
-- 2.123456 Password, but note that it is stored in mysql.user Table, yes password('123456')Encrypted password
CREATE USER 'zhangdong'@'localhost' IDENTIFIED BY '123456';

SELECT * FROM mysql.user;
SELECT PASSWORD('123456');

-- delete user
DROP USER 'user name' @ 'IP';
-- Different database users operate on different databases and tables

SC emp_view;
SELECT * FROM emp_view;
-- If you look for other columns, you won't find them
-- View instructions for creating views
SHOW CREATE VIEW emp_view;
-- Delete view
DROP VIEW view_name;

-- Modify view
UPDATE view_name 
					SET job = 'Modified content'
				WHERE id = 'id name';
UPDATE emp_view SET job = 'java engineer' WHERE id ='1';	
SELECT * FROM emp_view;

SELECT * FROM emp;
-- Modifying the view will affect the base table (that is, the original data table). Modifying the base table will also affect the view
UPDATE emp SET job = 'database' WHERE id ='1';	

-- Views can also be used in views
DESC emp_view;
-- from emp_view Make a new view in,
CREATE VIEW emp_view1 AS SELECT id,user_name FROM emp_view;
DESC emp_view1;
SELECT * FROM emp_view1;

Three tables are used for joint query to get the results.

Number of tables-1，Table name.Listing 

[External chain picture transfer...(img-9GdoNIh5-1638603177801)]

### mysql Management:

```mysql
-- mysql Administration
-- mysql
/*
1.host The location where login is allowed. localhost means that the user is only allowed to log in locally. You can also specify an ip address, such as 192.168.12.12
2.user user name
3.authentication_string: Password is the password encrypted through the password() function of mysql
*/
/*
Create user:
CREATE USER 'User name '@' allowed login location '
*/
-- mysql User management
-- Reason: when we do project development, we can assign it corresponding resources according to different developers mysql Operation authority
-- therefore mysql The database administrator creates different users according to needs and assigns corresponding permissions for personnel to use
-- Create a new user
-- 1.'zhangdong'user name@'localhost'Logged in IP
-- 2.123456 Password, but note that it is stored in mysql.user Table, yes password('123456')Encrypted password
CREATE USER 'zhangdong'@'localhost' IDENTIFIED BY '123456';

SELECT * FROM mysql.user;
SELECT PASSWORD('123456');

-- delete user
DROP USER 'user name' @ 'IP';
-- Different database users operate on different databases and tables

Posted by andrewtayloruk on Sun, 05 Dec 2021 00:18:55 -0800

Programmer Group