A blog entry in 5 hours and 30,000 words takes you to learn SQL

Posted by jfourman on Thu, 23 Dec 2021 14:59:35 +0100

Personal home page: 💖 A Niu💖

Note: Most of the key knowledge in this article is in the code comments, you must look carefully! I suggest you follow me! Wishing you all full harvest!

                           Here's the point! Here's the point!!💗💗💗
   Pull out your computer and start typing, buddies! (To be in full spirits~)

1. Data Definition (DDL)

Relational database systems support a three-level schema structure. The basic objects in the schema, internal and external schemas are schemas, tables, views, indexes, etc. Therefore, the data definition functions of SQL include schema definition, table definition, view and index definition. mysql differs from other databases in that its schema is a database, so I do not explain the contents of the schema. Interested students can learn by themselves!

1.1 Definition, deletion and modification of basic tables

We use the create table <table name> SQL statement to create the table, the drop table <table name> SQL statement to delete the table, and the alter table <table name> SQL statement to modify the table.

First, we set up a table field to store information about students, including number, name, gender, age, date of enrollment, class, email, and so on.

-- Notes
#It's just a single line comment, --it's also a single line comment
/*multiline comment
 multiline comment*/

-- Create database tables
/*create table Table name (
Field name data type,
Field name data type
);
*/

-- View the structure of the table:Details of the presentation table
-- desc Table Name;
 
-- View table building statements
-- show create table Table Name;

/*Create a table to store student information
 The fields contain information such as school number, name, gender, age, date of enrollment, class, email, etc.*/
-- DDL
create table t_student(
 sno  int(6),   -- 6，Display Length
 sname varchar(5),
 sex char(1),
 age int(3),
 enterdata date,
 classname varchar(10),
 email varchar(15)
 );
 
-- View the structure of the table:Details of the presentation table
desc t_student;

-- View data in tables-- DQL
select * from t_student;

-- View table building statements
show create table t_student;

/*CREATE TABLE `t_student` (
  `sno` int DEFAULT NULL,
  `sname` varchar(5) DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `age` int DEFAULT NULL,
  `enterdata` date DEFAULT NULL,
  `classname` varchar(10) DEFAULT NULL,
  `email` varchar(15) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci */

-- Add a table, add quickly, structure and data follow t_student All are consistent
create table t_student_2
as
select * from t_student;

-- Add a table, add it quickly, follow the structure t_student Same, there is no data;
create table t_student_3
as
select * from t_student where 1=2;


-- Quickly add, as long as some columns, some data
create table t_student_4
as
select sno,sname from t_student where sno=2;

Then insert some data into the table for us to learn and operate! (The following operations belong to DML)

-- DML
-- stay t_student Insert data into table
-- Integer data exceeds number of digits, system will automatically complete
insert into t_student values (1,'Zhang San','male',18,'2022-5-8','Software Class 1','123@126.com');
insert into t_student values (1,'Zhang San','male',18,now(),'Software Class 1','123@126.com');
insert into t_student values (4,'Zhang San','male',18,'2022-5-8','Software Class 1','123@126.com');
insert into t_student values (1,'Li Si','male',18,now(),'Software Class 1','123@126.com');
insert into t_student values (4,'Zhang San','male',18,now(),'python Class One','123@126.com');
insert into t_student values (6,'Zhang San','male',18,now(),'python Class One','123@126.com');
-- If not all fields insert data, the name of the field needs to be added
insert into t_student (sno,sname,enterdata) values (3,'Li Si','2021-7-5');

-- Modify data in tables
-- Modify an entire column
update t_student set sex = 'female';

-- Modify Determined
update t_student set sex = 'male' where sno = 1;

-- Table name, keyword, field,Content is case insensitive
UPDATE T_STUDENT SET AGE = 20 WHERE SNO = 1;

-- Delete operation
-- Full Table Delete
delete from t_student;

-- Conditional Delete
delete from t_student where sno = 1;

Then we delete and modify the table (note: deleting and modifying the table is a DDL operation, deleting and modifying the data in the table is a DML operation).

-- View data
select * from t_student;

-- Modify the table structure
-- DDL 
/*
(1)Modify table name
rename table Old name to new name;
alter table Old name rename new name;

(2)Add a field by adding a column to a table
alter table Table name add [column] Field name data type; #Default at last
alter table Table name add [column] Field name Data type after another field name; #Specify a specific location
alter table Table name add [column] Field name data type first; --at the top

(3)Delete a field by deleting a column from a table
alter table Table name drop [column] field name;

(4)Modify the data type of the field
alter table Table name modify [column] field name data type;

(5)Modify the name of the field
alter table Table name change [column] Old field name New field name data type;

(6)Modify the location of the field
alter table Table name modify [column] Field name Data type after another field name; #Specify a specific location
alter table Table name modify [column] field name data type first; --placed first */

-- Modify table name
rename table t_student to t;

alter table t rename t_student;

-- Add a column  add column
alter table t_student add sex char(2);

alter table t_student add score double(5,2);  -- 5,Total 2, decimal places
update t_student set score = '123.5678' where sno = 1;

-- Add a column (to the front)
alter table t_student add score double(5,2) first; 

-- Add a column (place in sex Back)-- column Can be omitted
alter table t_student add column score double(5,2) after sex; 

-- Delete a column  -- drop column
alter table t_student drop column score;  -- column Can be omitted

 -- Modify a column --  alter column + New Column Name+New data type (in mysql Can't use this grammar in) mysql Of use modify and change
 alter table t_student modify column score float(4,1);  #modify modifies the definition of the column type, but does not change the column name -- column can be omitted
 
 alter table t_student change column  score scores double(5,1);  # change changes column names and column type definitions -- column can be omitted, scope is the old column name, and scope is the new column name
 
-- Delete Table
drop table t_student  -- drop by DDL operation

-- Delete data: Empty data
truncate  t_student; -- truncate belong to DDL operation.That is, the structure of the table is preserved, and the table is recreated. It is efficient, submits implicitly, and cannot be rolled back. It increases from 1
delete from t_student; -- delete belong to DML Operation, delete one by one, inefficient, but can be rolled back, deletion from the previous sequence number start
-- efficiency drop>truncate>delete

There are only two photos here. Please verify for other operations!

1.1. 1. Table Integrity Constraints

Data integrity of a database refers to the correctness and compatibility of data. The integrity of relational databases can be divided into entity integrity, referential integrity, and user-defined integrity.

Entity integrity is defined by primary key when a table is created, which means that primary attributes cannot be null and primary key values cannot be duplicated in a basic table.

Reference integrity defines which columns are foreign codes when creating tables, and references indicate which tables are referenced by these foreign codes. Referential integrity refers to the valid value that a code in a basic table can be null or another relative bamboo code.

User-defined integrity is the semantic requirements that must be met for a specific application of data, including attribute constraints and tuple constraints, such as not null, unique, check, default, and so on.

1.1. 1.1. Non-External Constraints

T_created above The student table is deleted, and the following data is recreated and inserted:

Create Table - Use Column Level Constraints

-- Create Table  -- Use column level constraints
-- Integrity constraints on tables, not built-in constraints
create table t_student(
 sno  int(6) primary key auto_increment,   -- Primary Key Constraints and Self-Increasing 
 -- auto_increment Causes the inserted sno Automatically increment from 1, at this time sno You can insert null values if you don't auto_increment，You cannot insert a null value because the primary key cannot be null
 sname varchar(5)  not null, -- Non-empty constraint
 sex char(1) default 'male' check(sex='male' || sex='female') , -- Default Constraints and Check Constraints
 age int(3)  check(age>=18 && age<=50),
 enterdata date,
 classname varchar(10),
 email varchar(15) unique   -- Unique Constraint
 );
select * from t_student;
insert into t_student values (1,'Zhang San','male',18,'2021-9-3','python-1 class','zs@126.com');
 
insert into t_student (sname,age,enterdata,classname,email)values('Li Si',18,'2021-9-4','python-1 class','ls@126.com');
insert into t_student values (null,'King Five','male',19,'2021-9-9','python-1 class','wu@126.com');
-- email Repeated error, but sno Still grow by itself,So there's a discontinuity behind the insertion
insert into t_student values (null,'King Five','male',19,'2021-9-9','python-1 class','wu@126.com');
insert into t_student values (null,'Wang Six','male',19,'2021-9-10','python-1 class','wl@126.com');

select * from t_student;

Create Table - With Table Level Constraints

drop table t_student;
create table t_student(
sno int(6)  auto_increment ,   -- Column Level Constraints
sname varchar(5) not null,
sex char(1)  default 'male',
age int(3),
enterdata date,
classname varchar(10),
email varchar(15),
-- Table Level Constraints  -- And alias constraints
constraint pk_stu primary key (sno), -- pk_stu Name of primary key constraint
constraint ck_stu_sex  check (sex='male' || sex='female'),
constraint ck_stu_age  check (age>=18 and age<=50),
constraint uq_stu_email unique (email)
);
 
select * from t_student;
insert into t_student values (1,'Zhang San','male',18,'2021-9-3','python-1 class','zs@126.com');
 
insert into t_student (sname,age,enterdata,classname,email)values('Li Si',18,'2021-9-4','python-1 class','ls@126.com');
 
 -- Or add constraints after the table is built
alter table t_student add constraint pk_stu primary key (sno), -- pk_stu Name of primary key constraint
alter table t_student add constraint ck_stu_sex  check (sex='male' || sex='female'),
alter table t_student add constraint ck_stu_age  check (age>=18 and age<=50),
alter table t_student add constraint uq_stu_email unique (email)

1.1. 1.2. External Constraints and Foreign Key Policies

-- Create Subtable (Student Table)
create table t_student(
 sno  int(6)  primary key auto_increment,   -- 6，Display Length
 sname varchar(5) not null,
 classno int(4)
 );
 
 -- Add Student Information
 insert into t_student values (null,'Zhang San',1),(null,'Li Si',1),(null,'King Five',3);
 
 select * from t_student;
 
 
 -- Problem
 -- 1.Add a student to the class number 4
 insert into t_student values (null,'Lili',4);
 -- 2.Delete Class 2
 delete from t_class where cno=2
 
 -- Reasons for the problem
 -- The foreign key constraint is not added grammatically, but logically assumes that the class number is a foreign key and is not defined grammatically
 
 -- Solution, add foreign key constraints
 -- Note: Foreign key constraints have only table-level constraints and no column-level constraints
create table t_student(
		 sno  int(6)  primary key auto_increment,   -- 6，Display Length
		 sname varchar(5) not null,
		 classno int(4),
		 constraint fk_stu_classno foreign key (classno) references t_class (cno) -- Foreign Key Constraints
 );
  -- The above two error messages have been resolved
	-- Add Student Information
 insert into t_student values (null,'Zhang San',1),(null,'Li Si',1),(null,'King Five',3);
  -- Delete Class 1
 delete from t_class where cno=1;

-- Student table deletion
drop table t_student;
-- Class table deletion
drop table t_class;
-- Delete the primary table first when foreign key constraints exist and constraints are not cascaded t_student(Subtable), and then delete from the table t_class(Parent Table)

create table t_class(
	cno int(4) primary key auto_increment,
	cname varchar(10) not null,
	room char(4)
);

insert into t_class values (null,'java001','r803'),(null,'java002','r203'),(null,'Big Data 001','r416');

select * from t_class;

-- Create Subtable (Student Table)
create table t_student(
 sno  int(6)  primary key auto_increment,   -- 6，Display Length
 sname varchar(5) not null,
 classno int(4),
 constraint fk_stu_classno foreign key (classno) references t_class (cno) -- Foreign Key Constraints
 );
 
 -- Add Student Information
 insert into t_student values (null,'Zhang San',1),(null,'Li Si',2),(null,'King Five',3);

  select * from t_student;
 
 -- Delete Class 2 Directly: Not if deleted directly because of foreign key constraints
 -- So add a foreign key policy
 -- Strategy 1: no action Operation not allowed
 -- You can change class 2 to null，Then delete Class 2
 update t_student set classno = null where classno=2;
 delete from t_class where cno = 2;
 
 -- Strategy 2: cascade Cascade operation: affects foreign key information from the secondary table when operating on the primary table
 -- Delete foreign keys
 alter table t_student drop foreign key fk_stu_classno;
 -- Add band cascade Foreign Key
 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete cascade;
 
 -- Update action:-- Class 3 students will also change to Class 5, which is t_student In table classno Will also become 5
 update t_class set cno = 5 where cno=3; 
 
 -- Delete operation  -- t_student In table classno=5 Students will also be deleted
 delete from t_class where cno=5;
 
 
 
 -- Strategy 3: set null Vacuum operation
 alter table t_student drop foreign key fk_stu_classno;
 
 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;
 update t_class set cno = 8 where cno = 1;
 
 -- Be careful:
 -- 1,Policy cascade 2 and vacancy 3 can be mixed
 alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update cascade on delete set null;

Here is an example picture of cascading updates and deletions:

1.2. Definition, deletion, and modification of views

(1). The concept of a view: A view is a virtual table built from a single or multiple underlying data tables or other views. Like the base tables, views contain a series of column and row data with names, but the database only stores the definition of the view, that is, query statements that dynamically retrieve the data, not the data in the view. The data is still stored in the base table where the view is built, and the corresponding data is only requested from the database when the user uses the view. That is, the data in the view is generated dynamically when the view is referenced. Therefore, the data in the view depends on the underlying table in which the view is built, and if the data in the underlying table changes, the corresponding data in the view changes as well.
PS: View is essentially a query statement, a virtual table, no table exists. When you look at a view, you are actually looking at the sql statement corresponding to the view.
(2). Benefits of views: Simplify user actions: Views allow users to focus on the data they care about, without having to care about the structure of the data table, its association with other tables, query conditions, and so on. Provides security for confidential data. Views allow you to define different views for different users when designing a database application system, avoiding the appearance of confidential data (for example, sensitive field "salary") in user views where you should not see it. Views automatically provide security for confidential data.

Let's do it:

First create two tables and insert the data:

-- dept(Department table)
create table DEPT(
DEPTNO int(2) not null,
DNAME VARCHAR (14),
LOC VARCHAR(13)
);
alter table DEPT add constraint PK_DEPT primary key(DEPTNO);


-- empty(Employee table)
create table EMP(
EMPNO int(4) primary key,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int(4),
HIREDATE DATE,
SAL double(7,2),
COMM double(7,2),
DEPTNO int(2)
);
-- Establish foreign keys to connect employees with departments
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);

-- Insert data into department table
 insert into DEPT(DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
 insert into DEPT(DEPTNO,DNAME,LOC) values (20,'RESEARCH', 'DALLAS');
 insert into DEPT(DEPTNO,DNAME,LOC) values (30,'SALES', 'CHICAGO');
 insert into DEPT(DEPTNO,DNAME,LOC) values (40,'OPERATIONS', 'BOSTON');

 -- Insert data into employee table
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'1981-04-02',2957,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7566,'1987-06-09',2450,null,10);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDNET',null,'1981-11-17',5000,null,10);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'1987-0419',1500,0,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'1981-12-03',5000,null,10);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'1987-12-03',950,null,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);

Then let's do an exercise in views

-- Create View
-- create [or replace ] view <View Name> [Attribute 1, Attribute 2,...] as  select clause [with check option];

-- 1.Create a Form View
create view myview01
as
select empno,ename,job,deptno from emp
where deptno = 20;

-- Equivalent to
create view myview01 (empno,ename,job,deptno)  -- Number of fields and select Clauses should have the same number of fields and different property names
as
select empno,ename,job,deptno from emp
where deptno = 20;

-- The above two create views SQL For creating views with the same name, one SQL Can only run once, run again will error view already exists
-- Use the following methods to modify the one that created the view SQL，And replace the old view
create or replace view myview01  -- So this one sql You can run it multiple times, modifying the view only requires modifying this SQL，The new view replaces the old one
as
select empno,ename,job,deptno from emp
where deptno = 20;

-- Insert data into view
-- Conditions when we build the view where deptno=20,So insert dept=20 No errors in data
insert into myview01 (empno,ename,job,deptno) values (22,'lili','clerk',20);
insert into myview01  values (33,'nana','clerk',20);

-- So let's insert dept=30 The data syntax is fine, but logically we don't allow the insertion to succeed, and dept=30 This data was actually inserted into the emp In table
insert into myview01  values (44,'feifei','clerk',30);

select * from emp;

-- To solve this, we add the with check option
-- with check option Represents the execution of a view update,delete,insert The operation checks whether the predicate condition when attempting to define is met.
create or replace view myview01 
as
select empno,ename,job,deptno from emp
where deptno = 20
with check option ; -- Only then dept=20 Only data can be inserted

insert into myview01  values (44,'feiei','clerk',30);

-- 2.Establish/Replace multi-table view
create or replace view myview02
as
select e.empno,e.ename,e.sal,d.deptno,d.dname
from emp e
join dept d
on e.deptno=d.deptno
where sal >2000
with check option;

   -- Be careful
		select e.empno,e.ename,e.sal,d.deptno,d.dname
		from emp e
		join dept d
		on e.deptno=d.deptno
		where sal >2000;
		-- Equivalent to
		select e.empno,e.ename,e.sal,d.deptno,d.dname
		from emp e, dept d
		where e.deptno=d.deptno and sal >2000;  -- As mentioned in the connection query, this is a common way to write all databases

-- view a chart myview02
select * from myview02;


-- 3.Create Statistical View -- With aggregate function and groupby The group view is called the statistical view here
create or replace view myview03
as
select e.deptno,d.dname,avg(sal),min(sal),count(1)
from emp e join dept d
using(deptno) -- As mentioned in the join query, the mysql in using Clause Eliminates part of the common name column and places the same name column that you want to eliminate in the using()In parentheses
group by e.deptno;

-- view a chart myview03
select * from myview03;

-- 4.Create a view-based view
create or replace view myview04
as
select * from myview03 where deptno=20;

-- view a chart myview04
select  * from myview04;


-- Modify View
alter view  myview01
as 
select * from emp where deptno = 20;

-- view a chart
SELECT * FROM myview01;

-- Delete View
drop view myview01;

Paste some photos below

1.3. Index creation and deletion

Introduction to Indexes: Indexes are data structures, such as B-Tree, that require additional writing and storage to speed up data retrieval on tables. Once indexed, the query optimizer in the database uses indexes to quickly locate the data, and then does not need to scan each row of a given query in the table.
Where. When a table is created with a primary or unique key, MySQL automatically creates a special index called PRIMARY, which is called a clustered index. The PRIMARY index is special in that it is stored in the same table with the data. In addition, indexes other than the PRIMARY index are referred to as secondary or non-clustered indexes.
(Note: Tables are usually indexed when they are created. To add an index to a column or set of columns, use the CREATE INDEX index name ON table name < column name > statement

-- Typically, tables are indexed when they are created. For example, the following statement creates a new table with two columns c2 and c3 Composed index.
CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);

-- To add an index to a column or set of columns, however, you can use CREATE INDEX Sentence

#For t_ Before the sno of the student table is indexed
-- We want to see MySQL How to execute this query internally can be SELECT Add at the beginning of a statement EXPLAIN Clause,
explain  select * from t_student where sno = 6;

-- For the previous t_student Table sno Field adds a name index_1 Index of
create index  index_1 on t_student(sno);
-- Equivalent to
alter table t_student add index index_1(sno);

-- Modify Index 
-- take t_student Index name of table index_1 Change to index_2
alter table t_student rename index index_1  to index_2;

-- Delete Index 
alter table t_student drop index index_2;

To see how MySQL executes this query internally, you can add the EXPLAIN clause at the beginning of the SELECT statement and you will get the following results:

You can see that MySQL must scan the entire table with seven rows to find people with sno = 7, and when we index the sno fields of this table, the results are as follows:

You can see that MySQL saves time by finding only one row in the sno index indicated in the key column and not scanning the entire table.
In fact, there are many more points of knowledge about indexing (for mysql we create the index without declaring it, the default is B_TREE type index), here only write the index base, interested students can go to Baidu by themselves.

                                  Hold one's own!!💗💗💗

2. Data Query (DQL)

Data query is the core operation of the database, there are many contents in this block, the main keywords are: select, where, group by, have, order by, etc.

Before learning about this, let's create four more classical tables, as follows:

-- Prepare four tables:dept(Department table)，empty(Employee table), salgrade(PayLevels)，bonus(Bonus Form)
-- dept(Department table)
create table DEPT(
DEPTNO int(2) not null,
DNAME VARCHAR (14),
LOC VARCHAR(13)
);
alter table DEPT add constraint PK_DEPT primary key(DEPTNO);


-- empty(Employee table)
create table EMP(
EMPNO int(4) primary key,
ENAME VARCHAR(10),
JOB VARCHAR(9),
MGR int(4),
HIREDATE DATE,
SAL double(7,2),
COMM double(7,2),
DEPTNO int(2)
);
-- Establish foreign keys to connect employees with departments
alter table EMP add constraint FK_DEPTNO foreign key (DEPTNO) references DEPT (DEPTNO);


-- salgrade(PayLevels)
create table SALGRADE(
GRADE int primary key,
LOSAL double(7,2),
HISAL double(7,2)
);


-- bonus(Bonus Form)
CREATE TABLE bonus (
  ENAME varchar(10) DEFAULT NULL,
  JOB varchar(9) DEFAULT NULL,
  SAL double(7,2) DEFAULT NULL,
  COMM double(7,2) DEFAULT NULL
);



-- Insert data into three tables
-- Insert data into department table
 insert into DEPT(DEPTNO,DNAME,LOC) values (10,'ACCOUNTING','NEW YORK');
 insert into DEPT(DEPTNO,DNAME,LOC) values (20,'RESEARCH', 'DALLAS');
 insert into DEPT(DEPTNO,DNAME,LOC) values (30,'SALES', 'CHICAGO');
 insert into DEPT(DEPTNO,DNAME,LOC) values (40,'OPERATIONS', 'BOSTON');

 -- Insert data into employee table
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,'1980-12-17',800,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,'1981-04-02',2957,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7566,'1987-06-09',2450,null,10);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,'1987-04-19',3000,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDNET',null,'1981-11-17',5000,null,10);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,'1987-0419',1500,0,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,'1987-05-23',1100,null,20);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,'1981-12-03',5000,null,10);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,'1987-12-03',950,null,30);
 insert into EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,'1982-01-23',1300,null,10);

-- Insert data into payroll
insert into SALGRADE (GRADE,LOSAL,HISAL) values (1,700,1200);
insert into SALGRADE (GRADE,LOSAL,HISAL) values (2,1201,1400);
insert into SALGRADE (GRADE,LOSAL,HISAL) values (3,1401,2000);
insert into SALGRADE (GRADE,LOSAL,HISAL) values (4,2001,3000);
insert into SALGRADE (GRADE,LOSAL,HISAL) values (5,3001,9999);


-- Department table   LOC-position
select * from dept;

-- Employee Sheet  MGR-Superior leaders, COMM- Subsidy
select * from emp;

-- Salary scale
select *from salgrade;
 
-- Bonus Form 
select * from bonus;

2.1. Form Query

-- Yes EMP Table Query
select * from emp;
-- Show some columns
select empno,ename,sal from emp;
-- Show some rows
select * from emp where sal>2000;
-- Show some rows, some columns
select empno,ename,job,mgr from emp where sal>2000;

-- Alias
select empno Employee number,ename Full name,sal wages from emp; -- ellipsis as And quotation marks
-- as (alias)alias
select empno as Employee number,ename as Full name,sal as wages from emp; 
-- Quotes cannot be omitted when there is a special symbol in the alias
select empno as 'Employee number',ename as "Full name",sal as wages from emp; 
 
 -- Arithmetic operator
 select empno,ename,sal,sal+1000 as 'After New',deptno from emp where sal<2000;
 select empno,ename,sal,comm,sal+comm from emp; -- null + int = null
 
 -- Remove operation
 select distinct job from emp;
 select distinct job,deptno from emp;
 
 -- sort
 select * from emp order by sal; -- Ascending by default
 select * from emp order by sal asc;  -- asc Ascending order
 select * from emp order by sal desc;  -- desc Descending order
 select * from emp order by sal，deptno desc; -- In the case of rising wages, deptno Sort in descending order

2.1.1.where clause

-- See emp surface
select * from emp;


-- where clause + Relational Operators
select * from emp where deptno = 10;
select * from emp where deptno > 10;
select * from emp where deptno >= 10;
select * from emp where deptno < 10;
select * from emp where deptno <= 10;
select * from emp where deptno <> 10;   -- <>  Not equal to
select * from emp where deptno != 10;
select * from emp where deptno = 'CLERK';  -- By default, case insensitive
select * from emp where binary job = 'clerk'; -- binary Case sensitive
select * from emp where hiredate <'1981-12-25';

-- where clause + Logical operators
select * from emp where sal > 1500 and sal < 3000;
select * from emp where sal > 1500 && sal < 3000 order by sal;
select * from emp where sal between 1500 and 3000; -- Closed Interval
select * from emp where sal = 1500 or sal = 3000;
select * from emp where sal > 1500 || sal < 3000;
select * from emp where deptno in (10,20);
select * from emp where job in ('MANAGER','CLERK','ANALYST');


-- where clause + Fuzzy Query
-- Query name with A Employees of  --%Represents any number of characters, 0,1,2···
select * from emp where ename like '%A%';
select * from emp where ename like '_A%'; --  _Represents any character
select * from emp where ename like '__A%'; -- Two_
-- Query name without A Employees of
select* from emp where ename not like '%A%';
-- Note: If the query string contains wildcards%and_,This is the time to use escape '<Transcoding Characters>'Escape wildcards
-- Suppose you have an employee named mit_b,Query this employee for information
--select * from emp where ename like 'mit\_b' escape'\';

-- about null Judgment of
select * from emp where comm is null;
select * from emp where comm is not null;

-- Use of parentheses  -- and Priority level is greater than or
select * from emp where job = 'SALESMAN' or job = 'CLERK' and sal >=1500;
select * from emp where job = 'SALESMAN' or (job = 'CLERK' and sal >=1500);

select * from emp where (job = 'SALESMAN' or job = 'CLERK') and sal >=1500;

2.1. 2. Functions

-- Classification of functions
-- lower(ename),upper(ename);  -- Change each result, one result for each data -- Single-Row Functions
-- max(sal),min(sal),count(sal),sum(sal),avg(sal); -- Multiple pieces of data, one result at a time   -- Multiline function

-- One-line function contains
		-- 1.String function
		select ename,length(ename),substring(ename,2,3) from emp; 
		-- substring(),Provides three parameters, the first is the column name, the second is the character intercept from which the subscript starts, and the third is the number of digits intercepted
		-- 2.Numeric Functions
		select abs(-5) as absolute value ,ceil(5.3) ceil,floor(5.9) Rounding Down,round(3.14) Rounding from dual;  -- dual It's actually a pseudo table
		select abs(-5) as absolute value ,ceil(5.3) ceil,floor(5.9) Rounding Down,round(3.14) Rounding;  -- Without where Conditions, from dual Omit not writing
		select ceil(sal) from emp;
		 -- 3.date
		 select curdate(),curtime();  -- curdate()Specific date   -- curtime()Hours and seconds
		 select now(),sysdate(),sleep(3),now(),sysdate() from dual;   -- now()current time  -- sysdate()Function Execution Time
		 
		 -- 4.Process Functions
		 
		   -- if Relevant
		   select empno,ename,sal,if (sal >= 2500,'High-tech','low pay') as 'pay grade' from emp; -- if -else Double Branch Structure
		   select empno,ename,sal,comm,sal+ifnull(comm,0) from emp; -- If comm by null,be comm Value 0  -- Single Branch Structure
		 
		   select nullif(1,1), nullif(1,2) from dual;  -- If value1 Be equal to value2,Then return null,Otherwise return 1;

		  -- case Relevant
			select empno,ename,job,
			case job
			 when "CLERK" then "Clerk"
			 when "SALESMAN" then "Sale"
			 when "MANAGER"  then "manager"
			 else "Other"
			 end  as 'post',
			sal from emp;
			 
			 -- case Interval Judgment
			select empno,ename,sal,
			 case sal
				 when sal<= 1000 then 'A'
				 when sal<= 2000 then 'B'
				 when sal<= 3000 then 'C'
				 else 'D'
				 end 'Wage Rank',
			 deptno from emp;
		 
		-- 5.json function
		-- 6.Other Functions
		select database() ,user(),version() from dual;
		
		
-- Multiline function(Aggregate function) -- Note that aggregation functions can only be placed on select perhaps group by Of having After clause
select max(sal),min(sal),count(sal),sum(sal),sum(sal)/count(sal),avg(sal) from emp;
-- Multiline function auto-ignore null Value;
select * from emp;
select max(comm),min(comm),count(comm),sum(comm),sum(comm)/count(comm),avg(comm) from emp;
-- max(),min(),count()For all types,  sum(),avg()Valid only for numeric types
select max(ename),min(ename),count(ename),sum(ename),avg(ename) from emp;

-- count() -- count
-- Number of records in the statistics table:Mode 1
select count(ename) from emp;
select count(*) from emp;

-- Number of records in the statistics table:Mode 2
select 1 from dual; -- dual It's actually a pseudo table
select 1 from emp;
select count(1) from emp;

2.1. 3. Grouping group by and having

-- Statistics of average wages in various departments;
select deptno,avg(sal) from emp;  -- Field and multiline functions cannot be used at the same time, which requires grouping

select deptno,avg(sal) from emp group by deptno;  -- Field and multiline functions cannot be used together,Unless this field belongs to a group
select deptno,avg(sal) from emp group by deptno order by deptno desc;  

-- Statistics of average wages per post
select job,avg(sal) from emp group by job;
select job,lower(job),avg(sal) from emp group by job;

-- Statistics of average wages in various departments show only those with an average processing wage of more than 2000
select deptno,avg(sal) from emp group by deptno having avg(sal) > 2000;
select deptno,avg(sal) average wage from emp group by deptno having average wage > 2000;

-- Count the average salary for each job except MANAGER
-- where Filter before grouping, having Filter after grouping
select job,avg(sal) from emp where job != 'MANAGER' group by job;
select job,avg(sal) from emp group by job having job != "MANAGER";

-- Form Query Exercise
-- List jobs with a minimum salary of less than 2000
select job,min(sal) from emp group by job having min(sal)<2000;

-- List departments and job mixes with an average salary of more than 1,200 yuan
select job,deptno,avg(sal)
from emp group by job,deptno
having avg(sal)>1200;

Note: Interviewers always ask you the difference between where and having:
They function differently. The WHERE clause acts on tables and views, and the HAVING clause acts on groups. WHERE selects the input rows before the grouping and aggregation calculations (therefore, it controls which rows go into the aggregation calculation), while HAVING selects the grouped rows after the grouping and aggregation. Therefore, the WHERE clause cannot contain aggregation functions; it is meaningless to try to use aggregation functions to determine which rows are input to the aggregation operation. Conversely, the HAVING clause always contains aggregation functions. (Strictly speaking, you can write HAVING clauses that don't use clustering, but that's a waste of effort. The same conditions can be used more effectively in the WHERE phase.)
In summary:
having typically follows a group by and performs part of the record group selection.
where does all the data work.
Moreover, having can use aggregate functions.

2.2. Multi-table Query (Join Query)

-- Double Table Query
	select * from emp;
	select * from dept;
	-- multi-table query
	-- Cross Connection cross join
	select * from emp cross join dept;  -- 7*4 = 28 Cartesian product, no practical, theoretical
	select * from emp join dept; -- cross You can omit not writing, mysql Yes, Oracle Not possible in
	
	-- Equivalent connections (columns with the same name are shown twice)
	select emp.*,dept.* from emp,dept where emp.DEPTNO = dept.DEPTNO;

	-- Natural Connection natural join
	-- Benefits: Automatically matches all columns with the same name, showing the same column only once, eliminating duplicate attributes
	select * from emp natural join dept;
	-- Equivalent to
	select emp.*,dept.dname,dept.loc from emp,dept where emp.DEPTNO = dept.DEPTNO; -- (All databases are common)


	select empno,ename,sal,dname,loc from emp natural join dept;
	-- Disadvantages: It is inefficient to query a field without a database table to which the specified field belongs.
	-- Solution: Specify table name
	select emp.empno,emp.ename,emp.sal,dept.dname,dept.loc,dept.deptno from emp natural join dept;

	-- Alias tables if they are too long
	select e.empno,e.ename,e.sal,d.dname,d.loc,d.deptno from emp e natural join dept d;

	-- Internal connection  -- inner Can be omitted
	-- The disadvantage of natural joins is that all columns with the same name in a table are automatically matched, but sometimes we only want to match some columns with the same name
	-- Solution: 1.using clause  
	select * from emp e  inner join dept d using(deptno);  -- inner Can be omitted
	-- Like this, we can all use where Clause resolution, in select After that, reduce duplicate fields, and this is a versatile way to write, suitable for all databases
	
	-- and on Clause, showing all matching information
	select * from emp e inner join dept d on(e.deptno = d.deptno);
	-- Equivalent to equivalence join (columns with the same name are shown twice)
	select emp.*,dept.* from emp,dept where emp.DEPTNO = dept.DEPTNO;
	
	
	select * from emp e inner join dept d on(e.deptno = d.deptno) where sal >3500;



	-- Question:
	-- 40 No. Department has no employees and is not shown in the query results, employees scott No departments, not shown in query results

	-- Outside Connections: In addition to displaying matching data, mismatched data can also be displayed -- outer Omit not writing

	-- Left outer connection: left outer join -- The information from the table on the left can be displayed even if it does not match

	select * from emp e left outer join dept d on e.deptno = d.deptno;

	-- Right Outer Connection: left outer join -- The information from the table on the right can be displayed even if it does not match
	select * from emp e right outer join dept d on e.deptno = d.deptno;


	-- External connection: full outer join -- This is in mysql Not supported in,stay oracle Support in
	-- Solution: Union
	select * from emp e left outer join dept d on e.deptno = d.deptno
	union -- Union for weight removal
	select * from emp e right outer join dept d on e.deptno = d.deptno


	select * from emp e left outer join dept d on e.deptno = d.deptno
	union all -- Union does not weigh
	select * from emp e right outer join dept d on e.deptno = d.deptno;

	-- mysql Operations on sets are weak, only union is supported, and intersection difference is not ( Oracle Support in)

-- Three-table query
-- Query employee's number, name, salary, Department number, Department name, salary level
select * from emp;
select * from dept;
select * from salgrade;

select * from emp e inner join dept d on e.deptno = d.deptno inner join salgrade s on e.sal between s.losal and s.hisal;


-- Self-connection - Connect yourself
-- Query employee number, name, superior number, superior name
select * from emp;

select e1.empno Employee number,e1.ename Employee Name,e1.mgr Leader Number,e2.ename Employee Leader Name
from emp e1
inner join emp e2
on e1.mgr = e2.empno;

Here are some of the run results

2.3. nested queries

-- Irrelevant subqueries
	-- Introducing subqueries
	-- Query all comparisons CLARK"Highly paid employees'wages
	-- Step 1: Query " CLARK"wages
	select sal from emp where ename = "CLARK";
	-- Step 2: Query all employees whose wages are higher than 1450
	select * from emp where sal>2450;
	-- Two commands solve problems inefficiently, the second command relies on the first command
	-- Solve:
	-- Merge Step 1 with Step 2    - Subquery
	select * from emp where sal>(select sal from emp where ename = "CLARK");
	
	-- single-row subqueries
	-- Query the names and salaries of employees whose wages are above average
	select ename,sal from emp where sal > (select avg(sal) from emp);

  -- Query and " CLARK"Name and salary of an employee in the same department who is paid less than him
	select ename,sal from emp where deptno = (select deptno from emp where ename = "CLARK") and sal < (select sal from emp where ename = 'CLARK');
	
	-- Query job title and SCOTT Same, better than SCOTT Early Employee Information
	select * from emp where job = (select job from emp where ename = 'SCOTT') and hiredate < (select hiredate from emp where ename = 'SCOTT');

  -- Multi-row subquery   -- Query statements inside result in multiple rows
	-- Query employee information in department 20 with the same job title as in department 10
	select * from emp where deptno = 20 and job in (select job from emp where deptno =10);
	
	select * from emp where deptno = 20 and job = any(select job from emp where deptno =10);
  -- Query salary ratio for all " SALESMAN"Number, name and salary of employees who are both high.
	select empno,ename,sal from emp where sal > all(select sal from emp where job = "SALESMAN");
	  -- Single-line subquery writing
	select empno,ename,sal from emp where sal > (select max(sal) from emp where job = "SALESMAN");
	
	-- Query for wages below any one of " CLERK"Employee information for wages
	select * from emp where sal < any(select sal from emp where job = 'CLERK');
	  -- Single-line subquery writing
	select * from emp where sal < (select max(sal) from emp where job = 'CLERK');
-- Note: Contains any，all Subqueries of predicates can be used with aggregate functions select Clause means, they are equivalent
	
-- Related Subqueries
	select * from emp where sal = (select max(sal) from emp); -- Irrelevant subqueries
	-- Query the employees with the highest salary in the Department 
	-- Method 1: Implemented by unrelated subqueries
	select * from emp where deptno = 10 and sal = (select max(sal) from emp where deptno = 10)
	union
	select * from emp where deptno = 20 and sal = (select max(sal) from emp where deptno = 20)
	union
	select * from emp where deptno = 30 and sal = (select max(sal) from emp where deptno = 30);
	
	-- Method 2: Related subqueries
	select * from emp e where sal=(select max(sal) from emp where deptno = e.deptno); 
	
	-- Query employees whose wages are higher than the average for their positions
	select * from emp e where sal>= (select avg(sal) from emp where job = e.job);

-- Have exists Subquery of predicate
select sal from emp where EXISTS (select sal from emp where sal between 800 and 1800);
select sal from emp where not EXISTS (select sal from emp where sal between 800 and 1800);
-- Note: with exists A subquery of a predicate returns only logical authenticity, that is, if the inner result query is not empty, the outer result query where Clause returns true value, otherwise false value

The following shows some of the results:

Note: If the query conditions of a subquery are independent of the parent query, such subqueries are called irrelevant subqueries, and the entire query statement is called irrelevant nested queries. If the query conditions of a subquery depend on the parent query, such subqueries are called related subqueries, and the entire query statement is called related nested queries.

3. Data Manipulation (Data Update) (DML)

There are three main keywords: insert, update, delete.

3.1 Insertion, modification, deletion of data

-- insert
/*
#Assign values to all fields, adding one row at a time
insert into Table name values (list of values); # The number and order of the required values correspond to the number and order of the table structure
#Add rows one at a time to assign values to the specified fields
insert into Table name (field list) values (value list);

#Assign values to all fields, adding multiple rows at a time
insert into Table name values (value list), (value list), (value list)...;
#Add multiple rows at a time to assign values to a specified field
insert into Table name (field list) values (value list), (value list), (value list)...;
*/

-- modify
#This is a partial field that modifies all rows
/*update Table name set field name = field value, field name = field value...; 
#This is a partial field that modifies some rows (those that meet the criteria)
update Table name set field name = field value, field name = field value... where condition; 
*/

-- delete
/*
1,Delete data from the entire table
(1)delete from Table name;
(2)truncate [table] table name; -- Tables can be omitted--DDL
 Note: (1) The difference between (2):
-- delete It is a DML operation. Deleting one by one is inefficient, but can be rolled back. After deletion, the increase still starts from the previous sequential number.
-- truncate It is a DDL operation. That is, the structure of the table is preserved, and the table is recreated. It is efficient, submits implicitly, and cannot be rolled back. It increases from 1
-- Efficiency drop (DDL)>truncate (DDL)>delete (DML)
2,Delete some rows
delete from where condition for table name; # Delete qualified rows
*/
create table t_student(
 sno  int(6),   -- 6，Display Length
 sname varchar(5),
 sex char(1),
 age int(3),
 enterdata date,
 classname varchar(10),
 email varchar(15)
 );
-- View table records
select * from t_student;

-- DML
-- stay t_student Insert data into table
-- Integer data exceeds number of digits, system will automatically complete
-- First insertion method
insert into t_student values (1,'Zhang San','male',18,'2022-5-8','Software Class 1','123@126.com');
insert into t_student values (1,'Zhang San','male',18,now(),'Software Class 1','123@126.com');
insert into t_student values (4,'Zhang San','male',18,now(),'python Class One','123@126.com');

-- Second insertion method
insert into t_student values (1,'Zhang San','male',18,'2022-5-8','Software Class 1','123@126.com'),(1,'Zhang San','male',18,now(),'Software Class 1','123@126.com'),(4,'Zhang San','male',18,now(),'python Class One','123@126.com');
-- If not all fields insert data, the name of the field needs to be added
insert into t_student (sno,sname,enterdata) values (3,'Li Si','2021-7-5');


-- Modify data in tables
-- Modify an entire column
update t_student set sex = 'female';
-- Modify Determined
update t_student set sex = 'male' where sno = 1;

-- Table name, keyword, field,Content is case insensitive
UPDATE T_STUDENT SET AGE = 20 WHERE SNO = 1;

-- Delete operation
-- Full Table Delete
delete  from t_student;

-- Conditional Delete
delete from t_student where sno = 1;

This piece of content is simpler, not more narrative!

4. Data Control (DCL)

DCL (Data Control Language) statement: A data control language used primarily to set/change database user permissions. Common keywords are GRANT, REVOKE, etc.
Common people rarely use DCL statements, so don't elaborate, know GRANT, ==REVOKE, ==Remove permission

5. Transactions

I can't write any more. This piece of content is just a few pictures. The students who want to know will check it by themselves. The most famous one is the transfer case of the bank. And the difference between dirty reading, non-repeatable reading and fantasy reading, comrades will understand for themselves...

Note: Before rollback and submission, the data in the database is the data in the operation cache, not the actual data in the database. Both rollback and commit close the transaction.

                                       Already confused!!!

6. Conclusion

That's all about the basics of mysql. As long as you have the patience to read and follow, I will make sure that the practical part of your database passes. It will explode for 5 hours. The original is not easy. Please do not extract it!

If this blog post is helpful to you, you may want to answer it one by one, one by one, if you have questions, comments or trust me privately. In fact, it is important to know the knowledge of the database, such as sql injection in the field of network security. It is also very interesting for interested partners to know about it.

Learn this Do you think you've finished! No, you can only say that your database learning is just beginning and there is still a lot of knowledge to learn, including assertions, triggers, cursor objects, stored procedures, and so on! So come on!

MYSQL Installation Point Here 👉 Installation and use of mysql 💖
Navicate Installation Point Here 👉 Navicat installation and connection to mysql database nanny level tutorial

"All life has so far written about failure, but it doesn't stop me from moving forward!"

Topics: Database MySQL SQL

Programmer Think