Phase 2 ------------------------------------------------------------------------------- database

What is a database?

In short, it is a warehouse for storing and managing data.

Common databases are divided into:

Relational database, Oracle, MySQL, SQLServer, Access non relational database, MongoDB, Redis, Solr, ElasticSearch, Hive, HBase

Mysql database

1.mysql server, which handles specific data maintenance and saves the disk

2.mysql client, CRUD, add, modify, delete, query

Note: SQL is not case sensitive

default character set utf8;--------------- Set to utf8 format

Build database

Create database, database name: cgb2108

create database cgb2108 default character set utf8;

Delete Library

Delete the database named cgb2108

drop database cgb2108;

inspection of the treasury

View all databases

show databases;

Common operations of tables

: using libraries

use cgb2108;

Create table

Create a student table with ID, name and Tel fields

create table student(
id int primary key auto_increment,
name varchar(100),
tel varchar(50)
);

Modify table ⭕

#Add column add age column to student table

alter table student add column age varchar(10); 

Delete table

#Delete the table whose name is student

drop table student;

View all tables

show tables;

View table structure / table design

#View student table structure

 desc student;

Common operations of table records

insert record

#Insert 2 records into the student table

insert into student values(1,'A Ming');
insert into student values(2,'Amin');

View records

#Query all records in the student table

select * from student;

catalogue

Modify record

#Modify the record with id 1 in the student table (age changed to 2)

update student set age=2 where id=1

Delete record

#Delete the data with id 2 in the student

delete from student where id=2

sort

#Sort student table records by tel

select * from student order by tel #positive sequence
select * from student order by tel desc;#Reverse order

Total records

#Query the total number of records in the student table

select count(1) from student;
select count(*) from student;

data type

Naming rules

1. The field name must start with a letter. Try not to use pinyin

2. The length cannot exceed 30 characters (different databases, different versions)

3. SQL reserved words, such as where, order and group, cannot be used

4. Only the following characters AZ, AZ, 0 ~ 9, $, etc. can be used

5. Oracle custom all caps: user_ Name, mysql, all lowercase: user_ name

6. Multiple words are separated by underscores, rather than the hump rule of the java language

   character

   • char is fixed in length and cannot be filled with spaces. It can hold up to 2000 characters. char(11) stores abc, accounting for 11 bits. Query speed is very fast, but it wastes space

   • varchar variable length string, which can hold up to 4000 characters. varchar(11) stores abc, accounting for only 3 bits. Query is slightly slow, but saves space. Oracle is varchar2

   • Large text: large amount of text (not recommended, try to use varchar instead)

   • Calculated by utf8 coding, a Chinese character occupies 3 bytes under u8

   • Note: different database versions may have different length limits

Number:

1.tinyint,int integer type

2.float,double decimal type

3.numeric(5,2) decimal(5,2) -- it can also represent decimals, representing a total of 5 digits, of which there can be two decimals

4.decimal and numeric represent exact integer numbers

date

• date includes mm / DD / yy

• time hour minute second

• datetime includes month, day, hour, minute and second

• Timestamp timestamp is not a date, but the number of milliseconds from January 1, 1970 to the specified date

picture

blob binary data can store pictures and sounds, with a capacity of 4g. In the early days, such a design had obvious disadvantages, such as huge database and slow backup. It is of little value to back up multiple copies of these contents. At the same time, the database migration is too large and the migration time is too long. Therefore, at present, the mainstream will not directly store such data, but only store its access path, and the files are stored on disk.

Add data

Department table dept

Field name	data type	Is it empty	remarks
deptno	int		Number, primary key
dname	varcahr(20)	yes	Department name
loc	varchar(10)	yes	Department location

create table dept(
deptno int primary key auto_increment,
dname varchar(20),
loc varchar(10)
);
insert into dept values(null,'cgb','Class one');
insert into dept values(null,'jsd','Class two');
insert into dept values(null,'ccb','Class three');

Employee form emp

Field name	data type	Is it empty	remarks
empno	int		Employee number, PK primary key
ename	varchar(10)	Y	Employee name
job	varchar(10)	Y	position
mgr	int	Y	Superior number
hiredate	datetime	Y	Entry time
sal	double		Monthly salary
comm	NUMERIC(8,2)	Y	bonus
deptno	int	Y	Department FK foreign key

create table emp(
empno int primary key auto_increment,
ename varchar(10),
job varchar(10) unique,
mgr int,
hiredate datetime,
sal double not null,
comm NUMERIC(8,2),
deptno int
);
insert into emp values(null,'jack','manager','null','2002-05-01',99888,null,1);
insert into emp values(null,'tony','chief inspector',100,'2011-01-01',10000,2000,2);
insert into emp values(null,'haha','manager',200,'2012-02-02',8000,1000,2);
insert into emp values(null,'lao','staff',300,'2013-03-03',3000,200.12,2);
insert into emp values(null,'liu','staff',300,'2014-04-04',3500,200.58,2);

Field constraints

Primary key constraint

#Primary key constraint: if a primary key constraint is added to a column, the column is the primary key. The primary key is unique and cannot be empty. Usually, each table has a primary key.
#Auto increment of primary key when the primary key is of numerical type, you can set auto increment of primary key for maintenance convenience

create table abc(
id int primary key auto_increment
);
insert into abc values(null);
insert into abc values(null);
insert into abc values(null);
select * from abc;

Non NULL constraint

#Non empty constraint: if a non empty constraint is added to a column, the value of the column cannot be empty, but can be repeated

create table user(
id int primary key auto_increment,
password varchar(50) not null
);
show tables;
insert into user values(null,null);#Non NULL constraint not met
insert into user values(null,123;);#OK

Unique constraint

#Unique constraint: if a unique constraint is added to a column, the value of the column must be unique (that is, it cannot be repeated), but it can be empty.

create table test(
id int primary key auto_increment,
username varchar(50) unique--Unique constraint
);
show tables;
insert into test values(null,'lisi');
insert into test values(null,'lisi');  #The value of username should be unique. If it is repeated, an error will be reported
select * from test;

Basic function

lower

select'ABC',lower('ABC') from dept;  #Data to lowercase

upper

select 'ABC',upper('ABC') from dept; #Data to uppercase

length

select length(job) from dept; #Length of data

Condition query

distinct

#Use the distinct keyword to remove duplicate record lines
select loc from dept;#View the loc table in the dept library
select distinct loc from dept;
#distinct de duplication row removes duplicate rows in the loc table

where

#Note: aliases and aggregate functions cannot be used in where, which is more efficient than having!
select * from emp
select * from emp where id=1--Unique condition
select * from emp where id=1 and age=2 ---Equivalent to two conditions&Relationships and needs to be met at the same time
select * from emp where id=1 or age=2 ---Same as two conditions|Relationship, do not need to meet at the same time
select name, sal, from emp where sal=1000 or sal=1200 or sal=1400;---Query the name of a person whose salary is 1000 or 1200 or 1400
select name,sal from emp where sal in(1000,1200,1400);---ditto
select name,sal from emp where sal sal not in(1000,1200,1400);Ibid., opposite

like

wildcard%Represents 0 to n Characters, wildcard underscore_Represents 1 character
select * from emp where name like '1%'---Beginning with 1
select * from emp where name like '%a'---with a Ending
select * from emp where name like '%b%'---Intermediate inclusion b of
select * from emp where name like 'l__'---l Followed by two characters,_Represents the position of a character

null

select * from emp where mgr is null---Filter fields with empty values
select * from emp where mgr is not null ---Filter fields with empty values

between and

select * from emp 
select * from emp where sal<3000 and sal>10000
select * from emp where sal<=3000 and sal>=10000---to between and equivalent
select * from emp where sal between 3000 and 10000---ditto

limit

#Record with the highest score: after sorting by score, limit n returns the first n records. Oracle is stupid and cumbersome to implement. It is introduced in the later stage, while mysql is great with concise and efficient syntax.
#In mysql, perform paging query through limit:
select * from emp limit 2 ---List the first two
select * from emp limit 1,2---Start with the second one and show 2 records (A comma is preceded by a subscript, and a comma is followed by the number of entries)
select * from emp limit 0,3---Start with the first one and show 3 records---First three articles

order by

select * from emp order by sal ASC#The default ascending ASC can be omitted without writing
select * from emp order by sal desc #Descending order
SELECT * FROM emp ORDER BY ename #In ascending alphabetical order
SELECT * FROM emp ORDER BY hiredate #Sort in ascending order of number size
SELECT * FROM emp ORDER BY job 
#Understanding: when sorting Chinese characters, you will check the corresponding numbers in utf8 and sort them in ascending order

Statistical cases

Enrollment statistics

#Employees employed before 2015

select * from emp where year(hiredate)<2015
select * from emp where hiredate<'2019-1-1'#The number size is compared, not the date

#For employees signed after 2019, the date format is converted for comparison

select * from emp where year(date_format(hiredate,'%Y-%m-%d'))<=2019;

#Employees from 2015 to 2019

select * from emp
where
str_to_date(hiredate,'%Y-%m-%d')<='2015-01-01'
and
str_to_date(hiredate,'%Y-%m-%d')<='2019-12-31'

Annual salary statistics

#The company's welfare is good, with double salary at the end of the year. The annual salary of employees = sal13+comm13

select empno,ename,job,sal*13+comm*13 from emp;
select empno,ename,job,sal*13+comm*13 as alias from emp; use as List aliases for
select empno,ename,job,sal*13+comm*13 alias from emp; as It can also be omitted
select ename,sal+comm from emp
select ename,sal,comm,sal+ifnull(comm,0)from emp  ---If comm by null,Replace with 0

aggregation function

count

#According to the column statistics result, how many records are there in the count() table

select count(*) from emp ---The bottom layer is optimized #Total records
selcet count(1) from emp ---Effect and*equally
selcet count(comm) from emp ---Slow, no statistics null element

max/min

select max(sal) from emp #Query maximum wage
select min(sal) from emp #Query minimum wage
select max(sal) max,min(sal) min from emp #Query maximum and minimum wages
select ename,max(sal) from emp group by  ename #grouping

sum/avg

select sum(sal) from emp #Sum
select avg(sal) from emp #average
SELECT SUM(sal),AVG(sal) FROM emp #SUM query salary SUM, AVG average salary

Grouping group

Grouping: used to perform grouping statistics on query results

: divide the data into groups according to some dimensions, and then continue to analyze this group of data

group by indicates grouping. The having clause is similar to the results returned by where filtering

#The maximum salary and average salary of each position in each department, and the non aggregate column in the result must appear in the group, otherwise the business meaning is incorrect
 #1. Group by: group by, group data according to dimensions, and then perform data analysis
  #Pithy formula:
  #When do you want to group? Mixed columns occurred during query (when aggregated and non aggregated columns are included)
  #By what group? Group by non aggregate column
 #Exercise 1: calculate the average salary of each position
select job ,avg(sal) from emp group by job 
#Exercise 2: count the number of employees in each department
select deptno,count(1) from emp group deptno
 #Exercise 3: count the number of employees each year
select year(hiredate),count(1) from emp group by year(hiredate)
#Exercise 4: count the maximum salary of each department
select deptno,max(sal) from emp group by deptno

#2. Filtering having after grouping
 #Exercise 1: count the maximum salary of each department, as long as the record > 10000
select deptno,max(sal) from emp 
group by deptno # by non aggregate column
 Filtering after having max (SAL) > 1000 #group by must use having
 #Exercise 2: count the number of employees each year, as long as the number is > 1
select count(1),year(hiredate) from emp 
#Where count (1) > 1 error #where year (delay) > 2019 can be used
 #Aliases and aggregate functions cannot be used in where, which is more efficient than having
 Group by year (hide) # by non aggregate column
 Having count (1) > 1 # filter by number of people

affair

What is a transaction: a database transaction refers to a series of operations performed as a single logical unit of work, either completely or not.

To put it simply: a transaction is to bind a pile of SQL statements (usually add, delete and modify operations) together for execution. Either all of them are executed successfully or all of them fail. That is, only when all of them are executed successfully will they be successful, otherwise they will return to the state before the execution of this pile of SQL statements.

Four characteristics of transactions:

1. Atomicity: multiple SQL statements are regarded as one atom and are inseparable, either all successful or all failed. 2. Isolation: in order to ensure performance, the database also supports high concurrency, but there are potential security risks. Ensure multiple operations. 3. Persistence: the operation on the database is durable. 4. Consistency: ensure that the data is consistent in multiple systems

Isolation level

: from top to bottom, the security is getting better and better, but the performance is getting worse and worse

1. Read uncommitted: the security is the worst, concurrent data problems may occur, and the performance is the best

2. Read committed: the default isolation level of Oracle

3. Repeatable reading: Mysql is the default isolation level, with low security and average performance

4. Serialization: table level locking, locking both read and write, low efficiency, high security and no concurrency

Query the isolation level of mysql

By default, each SQL statement executed by MySQL is a separate transaction. If you need to include multiple SQL statements in a transaction, you need to manually start and end the transaction.

Start transaction: star transaction;

End transaction: commit or rollback

Commit (commit transaction)

BEGIN; #Turn off the automatic submission of transactions, which is equivalent to start transaction
 
INSERT INTO user (id) VALUES(25);#success
 
INSERT INTO user (id) VALUES(5);#It already exists for 5 years and will fail
 
COMMIT; #Manually commit transactions

Rollback (rollback transaction)

BEGIN;
 
INSERT INTO user (id) VALUES(15);
 
INSERT INTO user (id) VALUES(35);#There it is
 
ROLLBACK;#If the transaction is rolled back, it will not be committed again

Table 6: constraints

1. Non NULL constraint not null

create table b(
id int primary key auto_increment,
name varchar(10)not null
)

2. unique constraint

create table a(
id int primary key auto_increment,
name varchar(10)unique
)

3. primary key constraint

create table e(
id int primary key auto_increment,#The unique primary key cannot be empty and self incrementing is added
name varchar(10)
)

4. foreign key constraint

#Foreign key constraint: it prevents redundant data and describes the relationship between two tables through foreign keys
 #The feature is: when adding data to the sub table, the primary key value of the sub table must take the independent table!!!
       #When the main table deletes data, the sub table has no related records

create table tb_user(
id int primary key auto_increment,
name varchar(10),
password varchar(10)
)
create table tb_user_addr(
user_id int primary key,
address varchar(100),
    #Description and tb_user table relationships, foreign keys
    #Syntax: foreign key (primary key name of this table) references opposite table name (primary key of opposite table)
foreign key(user_id) references tb_user(id)#Create foreign key
)

5. check constraints

create table f(
id int primary key auto_increment,
age int,
check(age<100)#Check the constraints. You cannot save illegal data when you enter it
)

6. default constraint

create table e(
 id int primary key auto_increment,
 sex varchar(10) default 'male' #Default constraints, setting default values
)

Indexes

summary:

In order to improve query efficiency, the index will generate a separate table, and the index should be used reasonably

definition

Index is a kind of data structure with good order and fast search. It helps the database to retrieve data efficiently. In addition to data, the database system also maintains data structures (additional storage space) that meet specific search algorithms. These data structures point to data in some way, so that efficient search algorithms can be realized on these data structures. This data structure is called an index.

Generally speaking, the index itself is too large to be stored in memory, so it is often stored on disk in the form of index file. At present, most indexes are built by BTree tree.

Classification:

1. Single value index

: an index contains only one field, that is, one column. A table can contain multiple columns

2. Unique index

: an index contains only one field, but the fields cannot be repeated

3. Composite index

: an index can contain multiple fields

Usage steps: create an index (often query according to the specified field) + use an index

View index

#The primary key automatically creates an index

show index from Table name

Create single valued index

#create index name on table name (field name)

create index en_index on emp(ename)

Create unique index

#The value of a unique index column must be unique
 #create unique index name on table name (field name)

create unique index mc on emp(loc)

Create composite index

#The names of composite index columns are separated by commas in parentheses
 #create index name on table name (field name, field name)

create index a on emp(sal,age)

Delete index

#alter table name drop index index name

alter table emp drop index mc 

Leftmost feature

create index ace on emp(sal,age,job)
#The creation of a composite index is equal to the creation of three indexes (sal)(sal,age) (sal,age,job). This is the leftmost matching principle, also known as the leftmost feature
explain   select * from emp where sal=100 #take effect
explain   select * from emp where sal=100 and age=10 #take effect
explain   select * from emp where sal=100 and job='development' #invalid
explain   select * from emp where sal=100 and age=10 and job='development' #take effect

Index scan type

ALL full table scanning, no optimization, the slowest way
 Index index full scan, followed by slow mode
 Range index range scanning, common words <, < =, > =, between and other operations
 ref uses non unique index scanning or unique index prefix scanning to return a single record, which often appears in association queries
 eq_ref is similar to ref, except that it uses a unique index and an associated query using a primary key
 const/system for a single record, the system will treat other columns in the matching row as constants, such as primary key or unique index query. System is a special case of const
 null MySQL does not access any tables or indexes and returns results directly

Why is indexing fast?

Obviously, querying the index table is much faster than directly querying the data table. First, the index table is sorted, which can be similar to binary search, which effectively improves the query speed.
The process is shown in the following figure. First search the query in the sorted index table. After finding its primary key, locate it directly to the location of the record, and then return this data directly.
    Sorting, tree structure, similar to binary search
    Index table small

Advantages and disadvantages

advantage:
   1. Index is database optimization
   2. The primary key of the table will automatically create an index by default
   3. Each field can be indexed
   4. Greatly reduce the IO disk read and write cost of the database, and greatly improve the retrieval speed
   5. The index sorts the data in advance, which greatly improves the query efficiency
 Disadvantages:
   1. The index itself is also a table, which saves the primary key and index fields and points to the records of the entity table, so the index column also takes up space
   2. The contents of the index table are all in the business table. The data is repetitive and the space is "wasted"
   3. Although the index greatly improves the query speed, the operation of adding, deleting and changing data needs to update the index table information. If the amount of data is very large, the update efficiency is very slow, because MySQL should save not only the data but also the index file when updating the table
   4. With the continuous change of business, the previously established index may not meet the query requirements, and it will take us time to update the index

view

summary:
Like index, it is an effective scheme for database optimization
 characteristic:
1. The view can be used as a table. 2. The data stored in the view is the result of sql query. 3.sql cannot be optimized. Use the view reasonably

#Testing
 #View: the execution results of sql statements are cached and used as tables
 #The advantages are as follows: 1. The sql query is simplified (for the same sql requirements, there is no need to write sql, but directly query the view). 2
 #2. Views can be shared. Views mask the complexity of real business tables
 #The disadvantage is that once the view is created, sql cannot be optimized
 #Create view syntax: create view name as the sql statement of the query

create view name_view as like'%1'
select * from name_view

Table association

concept

Table represents an entity in life, such as department table dept and employee table emp. Table association represents the relationship between tables, such as department and employee, commodity and commodity classification, teacher and student, classroom and student.

At the same time, we should also know that tables are not all related. They form their own small circle. For example, it may not matter if there is a circle of commodities and commodity details, a circle of departments and employees, and it may not matter if they leave the circle. For example, commodities have nothing to do with employees, and commodities have nothing to do with students

There are four types of table relationships

one to one QQ and QQ email, employee and employee number

one to many is the most common, departments and employees, users and orders

many to one many to many in turn, employees and departments, orders and users

many to many teachers and students, teachers and courses

Multi table associated query join

Multi table query refers to the query of two or more comparison tables. In actual use, querying a single table may not meet your needs,
For example, the employee table emp displays not only deptno, but also the Department name, and the Department name dname is in the dept table

Cartesian product

selec * from dept,emp
 #The above method of querying two tables is called Cartesian product, also known as direct product. Generally, Cartesian product has no practical business significance, but multi table queries are formed into Cartesian product, and then the data is filtered.

#It is worth noting that in the actual development, multi table joint query is used as little as possible. This is the fundamental reason. In the query process, a large result set is built in memory, and then the data is filtered. The construction process and the memory resources used, including the judgment during filtering, are both resource-consuming and time-consuming.

#This is why the Alibaba specification prohibits associated queries on more than three tables:

Three kinds of link join

inner join

Only those with records on both sides of the inner join are displayed, and the others are removed

Left (outer) join

The data in the table on the left of the left join appears, and no data on the right is filled with null

right join

right join the data in the right table appears, and no data on the left is filled with null

Case: List tony's department information

select * from emp inner join dept
on emp.empno = dept.empno
where emp.ename='tony'

#There are differences between left join and right join

Case: list the information of all employees in adc department

select * from where #nested queries 
deptno=(select deptno from dept where dname='adc')

Case: how to use INNER JOIN to realize the above requirements?

#select emp.* from  emp inner join dept
select emp.ename,emp.job,dept.dname from  emp inner join dept
on emp.deptno=dept.deptno
where dept.dname='adc'

#There are differences between left join and right join

Subquery subquery

Concept:
Subquery refers to the select statement embedded in other select statements, also known as nested query. Sub query execution efficiency is low. Use with caution. When there are few records, it has little impact on the efficiency, and the diagram is convenient for direct use. When there are many records, it is best to use other methods instead.

single-row subqueries

#List all the people in tony's department

select deptno from emp where ename='tony'
select * from emp where deptno= ~

select * from emp where deptno=(select deptno from emp where ename='tony')

Multiline subquery in

in subquery ⭕

select * from emp where job in ('manager','staff')

select * from emp where job in (select distinct job from emp)

Subsection exercise

#Exercise 1: query the name of the employee whose department number is 1

#Method 1: nested query

SELECT deptno FROM dept WHERE empno=1
SELECT ename FROM emp WHERE deptno=1

SELECT ename FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='Bomb Department')

#Method 2: Cartesian product query

SELECT emp.ename FROM emp,dept
WHERE dept.deptno=emp.deptno
AND dept.deptno=1

#Method 3: connection query

SELECT emp.ename FROM emp JOIN dept
ON dept.deptno=emp.deptno
WHERE dept.deptno=1

#Exercise 2 query the Department information of the employee whose name is jack

#Method 1: nested query

SELECT deptno FROM emp WHERE ename='jack'
SELECT * FROM dept WHERE deptno= 1

SELECT * FROM dept WHERE deptno=(SELECT deptno FROM emp WHERE ename='jack')

#Method 2: Cartesian product query

SELECT * FROM emp,dept
WHERE emp.deptno=dept.deptno
AND emp.ename='jack'

#Method 3: connection query

SELECT * FROM emp JOIN dept
ON emp.deptno=dept.deptno
WHERE emp.ename='jack'

#Exercise 3: query the Department information where the position is the director

#Method 1: nested query

SELECT deptno FROM emp WHERE job='chief inspector'
SELECT dname FROM dept WHERE deptno=2

SELECT * FROM dept WHERE deptno=(SELECT deptno FROM emp WHERE job='chief inspector')

#Method 2: Cartesian product query

SELECT * FROM emp,dept
WHERE emp.deptno=dept.deptno
AND emp.job='chief inspector'

#Method 3: connection query

SELECT * FROM emp JOIN dept
WHERE emp.deptno=dept.deptno
AND emp.job='chief inspector'

#Exercise 4: list the information of all employees in the java development department

#Method 1: nested query

SELECT deptno FROM dept WHERE dname='Development Department'
SELECT * FROM emp WHERE deptno=5

SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE dname='Development Department')

#Method 2: Cartesian product query

SELECT * FROM emp,dept
WHERE emp.deptno=dept.deptno
AND dept.dname='Development Department'

#Method 3: connection query

SELECT * FROM emp JOIN dept
ON emp.deptno=dept.deptno
WHERE dept.dname='Development Department'

#Exercise 5: query employee information with department addresses in Beijing and Guangzhou

#Method 1: nested query

SELECT deptno FROM dept WHERE loc='Beijing'OR loc='Guangzhou'
SELECT * FROM emp WHERE deptno=1 OR deptno=3

SELECT * FROM emp WHERE 
#deptno in(SELECT deptno FROM dept WHERE loc BETWEEN 'Beijing' AND 'Guangzhou')
#deptno=any(SELECT deptno FROM dept WHERE loc = 'Beijing' or loc = 'Guangzhou') 
#deptno in (SELECT deptno FROM dept WHERE loc = 'Beijing' or loc = 'Guangzhou') 
#deptno in (SELECT deptno FROM dept WHERE loc in('Beijing ',' Guangzhou ')) 
deptno=(SELECT deptno FROM dept WHERE loc='Beijing' ) 
OR 
deptno=(SELECT deptno FROM dept WHERE  loc='Guangzhou')

#Method 2: Cartesian product query

SELECT emp.* FROM emp,dept
WHERE emp.deptno=dept.deptno
AND dept.loc BETWEEN 'Beijing' AND 'Guangzhou'

#Method 3: connection query

SELECT emp.* FROM emp JOIN dept
ON emp.deptno=dept.deptno
WHERE dept.loc BETWEEN 'Beijing' AND 'Guangzhou'

SQL interview questions

#SQL interview questions
 #1. An error is reported. There is no id field in the emp table

select * from emp where id=100;

#2. For fuzzy query, it is better to specify the start element, otherwise the index will become invalid

show index from emp; #Check the index in the table and confirm that ename has index
explain  #Viewed possible_keys have value, that is, the index takes effect
select * from emp  where ename='jack' #Index validation
explain select * from emp where ename like ='j%' #take effect
explain select * from emp where ename like ='%j' #invalid
explain select * from emp where ename like ='%j%' #invalid

#3. Query the data within the employee No. [100300]

select * from dept where deptno between 100 and 300

#4. Comprehensive sorting order by 
#Descending by name and ascending by sal. The default is ascending. ASC can be omitted

select * from emp order by ename desc ,sal asc
select * from emp order by sal 

#5. Grouping: query the student table, the total number of students in each class, as long as the data of class 95033
 #When aggregate columns (using aggregate functions) and non aggregate columns appear in query results, they must be grouped

select count(*),class from students
group by class #Group by non aggregate column
having class=95033

#5.1 is more efficient than the above sentence SQL

select count(*),class from students 
where class=95033 #For filtering before grouping, use where (alias and aggregate function are not allowed)
group by class=95033

#6. Sub query: query > employee information of average salary

select * from emp where sal>(select avg(sal)from emp)

#6.1 query the name of the highest paid employee

select ename from emp where sal>(select avg(sal)from emp)

JDBC

summary

Short name, full name: java database connectivity, a technology specially used to complete the connection between java programs and databases

Use steps

1. Import the jar package (JDBC provides rich tool classes)
2. Provide parameters for connecting to the database (user name, root password, root port number 3307)
3. In java program, initiate SQL statement to operate database
 4. If the database has found the result, return it to the java program

Introductory case

1. Create project: file new project - select Java next next - enter project name - Finish
 2. Import jar package:
Find mysql-connector-java-5.1.32.jar in the disk, copy it and paste it into Project
 In the IDEA, select the jar package, right-click compile (add as library...), and click OK
 Check whether the compilation is successful: you can see that the jar package in the IDEA can be clicked

//1. Register driver
Class.froName("com.mysql.jdbc.Driver");
//2. Get database connection
String url="jdbc:mysql://localhost:3307/test";
Connection c= DriverManager.getConnection(url,"root","root");
//3. Get the transmitter
Statement s=c.createStatement();
//4. Execute SQL
String sql="select * from dept"
ResultSet r=s.executeQuery(sql);
//5. Analytic bound set
while(r.next()){
    int a=r.getInt(1);
    String b = r.getString(2);
    int c1=r.getInt(3);
    System.out.println(a+","+b+","+c1);
}
//6. Release resources
r.close();
s.close();
c.close();

Create tool class

//Provide rich methods and convenient jdbc operation
public class JDBCUtils {
    //1. Get the connection to the database (register driver + get connection)
    /**
     * Get connection to database
     * @return Connection object of database connection
     * @throws Exception
     */
    static public Connection getConnection() throws Exception{
        //1. Register driver
        Class.forName("com.mysql.jdbc.Driver");//Full path
        //2. Get the connection to the database (user name / password)
        //jdbc protocol for connecting to mysql database. / / native: port number / database name. Resolve Chinese garbled code. Specify time zone. Turn off permission verification
        String url="jdbc:mysql://localhost:3306/cgb2108?characterEncoding=utf8&serverTimezone=Asia/Shanghai&useSSL=false" ;
        Connection c = DriverManager.getConnection(
                url,"root","root");
        return c ;//Return to caller
    }
}
Simulated user

Sign in

//Requirement: query TB using JDBC_ Data in user table

/* 1,Create a table, insert data 2, and query data using JDBC
CREATE TABLE tb_user(
   id int PRIMARY KEY auto_increment,
   name varchar(20) default NULL,
   password varchar(20) default NULL
)
insert into tb_user values(null,'jack','321')
 */
    public static void main(String[] args) throws Exception {
//        method();// Query TB_ Data in user table
        method2();//Resolve SQL attacks
    }//Resolve SQL attacks
    private static void method2() throws Exception {
        //1. Register the driver 2. Obtain the connection
        Connection c = JDBCUtils.getConnection();
        //3. Execute SQL
        String a = new Scanner(System.in).nextLine();//user name
        String b = new Scanner(System.in).nextLine();//password
        //If the string is spliced dynamically, the data is in the middle position "+ a +"
//        String sql="select * from tb_user where name='jack' and password='321'" ;
//        String sql="select * from tb_user where name='"+a+"' and password='"+b+"'" ;
        //SQL skeleton: use? Instead of the position of the parameter,? Called placeholders, benefits: simplicity (avoiding SQL splicing parameters)
        String sql="select * from tb_user where name=? and password=?" ;
        //4. Obtain the transmitter
//        Statement s = c.createStatement();
        PreparedStatement s = c.prepareStatement(sql);
        //Set SQL parameters -- setXxx() set different types of parameters
        s.setString(1,a);//? The index to be given? Set value
        s.setString(2,b);//? The index to be given? Set value
        //TODO, will SQL attacks still occur when the user name enters jack '#???
        ResultSet r = s.executeQuery();
        //5. Parse result set
        if(r.next()){//Did you find the data? Log in successfully when you find it
            System.out.println("Login succeeded~");
        }else{
            System.out.println("User name or password input error,Login failed~");
        }
        //6. Close resources
        r.close();
        s.close();
        c.close();
    }

    /*Simulate user login
 1. Initiate SQL:select * from tb_user where name='jack' and password='321'
2. Judge the result. If there is a result, the login succeeds, and if there is no result, the login fails

Problem: SQL attack / SQL injection,
In essence, special symbols (#, some conditions are commented out) appear in SQL statements, which leads to the change of SQL semantics
 Solution: Statement is a low-level transmitter, which is unsafe and inefficient
      Change to PreparedStatement advanced, safe
    */
  

  private static void method() throws Exception {
        //1. Register the driver 2. Obtain the connection
        Connection c = JDBCUtils.getConnection();
        //3. Obtain the transmitter
        Statement s = c.createStatement();
        //4. Execute SQL
        String a = new Scanner(System.in).nextLine();//user name
        String b = new Scanner(System.in).nextLine();//password
        //If the string is spliced dynamically, the data is in the middle position "+ a +"
//        String sql="select * from tb_user where name='jack' and password='321'" ;
String sql="select * from tb_user where name='"+a+"' and password='"+b+"'";
        ResultSet r = s.executeQuery(sql);
        //5. Parse result set
        if(r.next()){//Did you find the data? Log in successfully when you find it
            System.out.println("Login succeeded~");
        }else{
            System.out.println("User name or password input error,Login failed~");
        }
        //6. Close resources
        r.close();
        s.close();
        c.close();
    }

JDBC exercises

//Practice querying and inserting data by conditions

    public static void main(String[] args) throws Exception{
        method();//Query < 100 data of department table
        method2();//Insert data into dept table
    }
    //Insert data into dept table
    private static void method2() throws Exception {
        Connection c = JDBCUtils.getConnection();
        //How do you decide how many question marks to use when inserting data? To see how many fields in the table need to be set
        String sql = "insert into dept values(?,?,?)" ;
        PreparedStatement p = c.prepareStatement(sql);
        //Set parameters for SQL
        p.setObject(1,666);
        p.setObject(2,"Software testing department");
        p.setObject(3,"Great Shanxi");
        //Execute SQL
        int rows=p.executeUpdate();//SQL for adding, deleting and modifying
        System.out.println("Number of rows affected:"+rows);
        p.close();
        c.close();
        //Will TODO return the result set? What did you return?
    }
    //Query < 100 data of department table
    private static void method() throws Exception{
        Connection c = JDBCUtils.getConnection();//Use the tool class to obtain the connection to the database
        //Get the transmitter and execute the SQL skeleton
        String sql = "select * from dept where deptno < ?";
        PreparedStatement s = c.prepareStatement(sql);
        //Set parameters for SQL
        s.setInt(1,100);//For the first one? Set 100
        ResultSet r = s.executeQuery();//Execute SQL statement of query
        //Processing result set
        while(r.next()){//Does next() judge whether there is data
            //Get data getXxx() -- get the value of the dname field in the table and print it
            String str = r.getString("dname");
            System.out.println(str);
        }
        //close resource
        r.close();
        s.close();
        c.close();
    }

Modify the code for releasing resources

  //Insert data into dept table
    //For resources will be released?
    //Put the code that releases resources into finally + expand the scope of the variable
    //+ modify the default value of variable null in try + try catch in finally

private static void method() {
        Connection c =null;
        PreparedStatement s =null;
        ResultSet r =null;
        try{
            c = JDBCUtils.getConnection();//Use the tool class to obtain the connection to the database
            //Get the transmitter and execute the SQL skeleton
            String sql = "select * from dept where deptno < ?";
             s = c.prepareStatement(sql);
            //Set parameters for SQL
            s.setInt(1,100);//For the first one? Set 100
             r = s.executeQuery();//Execute SQL statement of query
            //Processing result set
            while(r.next()){//Does next() judge whether there is data
                //Get data getXxx() -- get the value of the dname field in the table and print it
                String str = r.getString("dname");
                System.out.println(str);
            }
        }catch (Exception e){
           //In the online phase of the project, provide solutions, such as output
            System.out.println("Database connection error~~");
           //In the project development and commissioning stage, the solutions given are based on the error information
            e.printStackTrace();
        }finally {
            //close resource
            try {
                r.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                s.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
            try {
                c.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }} }

.