- Why use Mysql?
- Simple query
- Condition query
- Data processing function
- Grouping function (multi-line processing function)
- Group query
- Connect query
- Equivalent connection of inner connection
- Non-equivalent connection
- Outer join
- Subqueries in the where clause
- Subqueries in the from clause
- union merge query result set
- Table creation
- Syntax format of table building
- Data types in mysql
- Create a student table
- Delete table
- Insert data insert (DML)
- Modify update (DML)
- Delete data delete (DML)
- Non-empty constraint
- Uniqueness constraint
- Primary key constraint (primary key)
- Foreign key constraint
Why use Mysql?
It is relatively used in Internet companies. The mysql database is free, compact and lightweight, and easy to install. Internet companies such as JD and Ali have a large amount of concurrency, and build multiple servers, and the cost of using free databases is low.
What is a database? What is a database management system? What is SQL? What is the relationship between them?
Database: Database, referred to as DB. A combination of some files stored in a certain format.
As the name implies: a warehouse that stores data is actually a bunch of files. These files store data in a specific format.
Database management system:
DataBaseManagement, referred to as DBMS. The database management system is specially used to manage the data in the database. The database management system can add, delete, modify and check the data in the database.
Common database management system:
Mysql, Oracle, MS sqlserver, DB2, sybase, etc...
SQL: Structured Query Language
Programmers need to learn SQL statements, by writing SQL statements, and then the DBMS is responsible for executing the SQL statements, and finally complete the operation of adding, deleting, modifying and checking data in the database.
SQL is a set of standards. This SQL statement can be used in any database management system.
The relationship between the three?
Check which databases are in mysql ?
mysql comes with 4 databases by default.
How to choose to use a certain database ?
The most basic unit in the database is the table: table
View the tables in the database
What is a table? Why use tables to store data?
Excel table data is a table, and the data stored in the database represents the data in the form of a table, because the table is more intuitive, and any table has rows and columns.
Row: called data/record.
Column: This is called a field.
Classification of sql statements
DQL: data query statement (all queries with the select keyword are query statements)
DML: Data Manipulation Language (all data in the table is added, deleted, modified and checked)
DDL: Data Definition Language (all with create, drop, alter)
DDL mainly operates on the structure of the table, not the data in the table
TCL: is the transaction control language
Including: transaction submission: commit;
Transaction rollback: rollback;
DCL: is a data control language.
For example: authorize grant, revoke permission revoke
View the data in the table
mysql>select * from school;
Only view the structure of the table
Note: mysql does not execute if it does not see the semicolon!
1. Query a field?
select field name from table name;
Note that: select and from are keywords
Both the field name and the table name are identifiers
2. Query two or more fields?
Use commas to separate
Query all the first way: write each field
The second way: select * from table name
3. Give the query column an alias
mysql>select deptno,dname as deptname from dept;
Use the as keyword to alias
Note: Only the column name of the displayed query result is displayed as deptname, the original column name is still called: dname
Remember: the select statement will never be modified. (Because it is only responsible for query)
select field 1, field 2, field 3 from table name where conditions;
between…and… Between two values, equivalent to >= and <=
select empno,ename,sal from emp where sal between 2450 and 3000;
Note: When using between and, you must follow the principle of small left and large right
Is null is null
Is not null is not null
And and or appear at the same time, there is a priority issue?
For example: query employees whose salary is greater than 2500 and whose department number is 10 or 20?
select * from emp where sal>2500 and deptno=10 or deptno=20;
Analyze the problem of the above sentence?
and has a higher priority than or.
The above statement will execute and first, and then execute or
select * from emp where sal>2500 and (deptno=10 or deptno=20);
And and or appear at the same time, and has a higher priority. If you want or to be executed first, you need to add "parentheses"
in contains, equivalent to multiple or
The query job is an employee of manager and salesman?
select empno, ename, job from emp where job ='manager' or job='salesman';
select empno, ename, job from emp where job in('manager','salesman');
Note: in is not an interval. In is followed by the specific value
like is called fuzzy query
Support% or underscore matching
% Matches any number of characters
Underscore: any character
Find those with o in the name?
select ename from emp where ename like'%o%';
Find the name ending in T?
select ename from emp where ename like'%T';
Find the name that starts with K?
select ename from emp where ename like'K%';
Find out where the second letter is A?
select ename from emp where ename like'_A%';
Query the salary of all employees, sort
select ename,sal from emp order by sal;//The default is ascending order
select ename,sal from emp order by sal desc; //specify descending order
select ename,sal from emp order by sal asc;//specify ascending order
Sort by salary first, same salary, sort by name
select ename,sal from emp order by sal asc, ename asc;
The order of keywords cannot be changed:
The execution order of the above statements:
Data processing function
Data processing function is also called single line processing function
Features of single-line processing functions: one input corresponds to one output
The opposite of a single-line processing function is a multi-line processing function. (Features of multi-line processing function: multiple inputs and one output)
What are the common one-line processing functions?
lower to lowercase
select lower (ename) as ename from emp;
substr takes a substring (substr (string to be intercepted, starting subscript, length of interception))
select substr (ename, 1, 1) as ename from emp;
length takes the length
trim to remove spaces
Grouping function (multi-line processing function)
The characteristics of multi-line processing function: input multiple lines, and finally output one line.
Note: The grouping function must be grouped before it can be used.
If there is no grouping, the whole table is a set of data by default.
Find the highest wage
select max(sal) from emp;
The first point: the grouping function automatically ignores null, and there is no need to deal with null in advance.
The second point: What is the difference between count (*) and count (specific fields) in the grouping function?
count (specific field): Indicates the total number of non-null elements in the field
count(*): The total number of rows in the statistics table. (As long as there is a row of data, count is ++)
Because it is impossible for each row of records to be null, and one column of a row of data is not null, then the row of data is valid
The third point: the grouping function cannot be used directly in the where clause
Fourth point: All grouping functions can be combined and used together
Combine all the previous keywords together
Order of execution:
3. Group by...
5. order by...
Why can't the grouping function be used directly behind where?
Because the grouping function must be grouped before it can be used when it is used.
When the where is executed, there is no grouping yet, so the grouping function cannot appear after the where.
select sum (sal) from emp;
There is no grouping, why can the sum() function be used?
Because select is executed after group by.
In a select statement, if there is a group by statement, select can only be followed by the fields participating in the grouping, and the grouping function, and the others cannot be followed.
Use having to further filter the data after grouping. Having cannot be used alone, having cannot replace where, having must be used in conjunction with group by.
Single table query summary
Order of execution:
3. Group by
6, order by
Query data from a table, first filter out the valuable data through where conditions, group these valuable data, and then use having to continue filtering after grouping. The select query comes out, and finally the output is sorted.
Find out the average salary of each position. It is required to show that the average salary is greater than 1500. Except for the manager position, it is required to sort in descending order of the average salary.
select job, avg(sal) as avgsal
group by job
order by avgsal desc;
Remove duplicate records from query results
Note: The original table data will not be modified, but the duplicate records of the query result will be removed.
A keyword is needed to remove duplicates: distinct
Distinct can only appear at the forefront of all fields.
1. What is a connection query?
A single query from a table is called a single-table query.
For example: the emp table and the dept table are combined to query data, and the employee name is taken from the emp table, and the department name is taken from the dept table. This kind of cross-table query, where multiple tables are combined to query data, is called join query.
2. What is the classification of the connection query?
Classified according to the way the tables are connected:
Left outer connection (left connection)
Right outer connection (right connection)
3. When two tables are connected for query, there is no restriction on any conditions. The final query result is the product of the number of the two tables. This phenomenon is called: Cartesian product phenomenon. (This is a mathematical phenomenon discovered by Descartes.)
Equivalent connection of inner connection
Case: Query the department name of each employee, and display the employee name and department name?
from emp e,dept d
Disadvantages of sql92: the structure is not clear, the connection conditions of the table and the conditions for further filtering in the later stage are all placed after where.
from emp e
(inner)join dept d //inner can be omitted
Advantages of sql99: The conditions for table connection are independent. After the connection, if further filtering is required, continue to add where in the future
Case: Find out the salary grade of each employee, and ask to display the employee's name, salary, and salary grade?
from emp e
join salgrade s
on e.sal between s.losal and s.hisal;//The condition is not an equal relationship, which is called non-equivalent connection.
Case: Query the superior leader of an employee, request to display the employee's name and the corresponding leader's name?
Technique: Treat one table as two tables
select a.ename as'employee name', b.ename as'leader name'
from emp a
join emp b
Left join: It means that the table to the left of the join keyword is regarded as the main table, mainly to display all the data in this table, and to carry the table on the right of the associated query.
Right join: It means that the table on the right of the join keyword is regarded as the main table, which mainly displays all the data in the right table, and carries the table on the left of the associated query.
How to connect three tables or four tables?
on the connection condition of a and b
The connection condition of on a and c
The connection condition of on a and d
What is a subquery?
The select statement is nested in the select statement, and the nested select statement is called a subquery.
Subqueries in the where clause
Example: Find out the names and wages of employees who are higher than the minimum wage?
select ename, sal
This way of writing is wrong, and grouping functions cannot be used directly in the where clause.
The first step: query what is the minimum wage
select min(sal) from emp;
Step 2: Find out those >800
select ename,sal from emp where sal>800;
Step 3: Combine
select ename,sal from emp where sal>(select min(sal) from emp);
Subqueries in the from clause
Note: The subquery after from can use the query result of the subquery as a temporary table.
Case: Find the salary grade of the average salary for each position.
The first step: find out the average salary of each position (calculate the average according to the position grouping)
select job,avg(sal) from emp group by job;
Step 2: Treat the above query result as a real table.
(select job,avg(sal) as avgsal from emp group by job) t
t.avgsal between s.losal and s.hisal;
union merge query result set
The efficiency of union is higher. For table joins, every time a new table is joined, the number of matches meets the Cartesian product, which is doubled. . .
But union can reduce the number of matches. In the case of reducing the number of matches, it can also complete the splicing of the two result sets.
a connect b connect c
a 10 records
b 10 records
c 10 records
a result of connecting b: 10*10–>100 times
a One result of connecting c: 10*10–>100 times
Use union: 100 times + 100 times = 200 times (union turns multiplication into addition operation)
Limit is to take out part of the query result set, which is usually used in paging queries.
The role of paging: to improve the user experience.
Full usage: limit startIndex, length
startIndex start index
length taken length
Case: In descending order of salary, remove the top 5 employees?
Note: limit in mysql is executed after order by
3 records per page
The first page: limit 0,3 [0,1,2]
The second page: limit 3, 3 [3,4,5]
The third page: limit 5, 3 [6,7,8]
Display pagesize records per page
Page pageno: limit (pageNo-1) *pagesize, pagesize
Syntax format of table building
(Building a table belongs to the DDL statement, DDL includes: create drop
create table table name (field name 1 data type, field name 2 data type, field name 3 data type);
Data types in mysql
varchar up to 255 (variable-length string, space will be dynamically allocated according to the actual data length)
Advantages: save space
Disadvantages: Need to allocate space dynamically, slow speed.
char up to 255 (fixed-length character string, no matter what the actual data length is, a fixed-length space is allocated to store the data. When it is used improperly, it may lead to a waste of space)
Advantages: no need to dynamically allocate space, fast
Disadvantages: Improper use may lead to waste of space.
How to choose varchar and char?
Gender field char
Name field varchar
int (longest 11) Integer type in numbers, equivalent to int in java
The long integer in bigint numbers, which is equivalent to long in Java
float Single-precision floating-point data
double double-precision floating-point data
date short date type
datetime long date type
clob character large object (can store up to 4G string) such as storing an article, storing a description
Blob Binary large objects (specially used to store streaming media data such as pictures, sounds, videos, etc.) are inserted using IO streams
Create a student table
Student ID, name, age, email address
create table t_student(
no int,name varchar(32),sex char(1),age int(3),email varchar(255));
drop table if exists t_student ;//Delete if this table exists
Insert data insert (DML)
Syntax format: insert into table name (field name 1, field name 2, field name 3...) values (value 1, value 2, value 3);
Note: The field name must correspond to the value, the quantity must correspond, and the data type must correspond.
insert into t_student(no,name,age,sex,email) values(01,'zhangsan',20,'f','[email protected]');
Note: If the insert statement is executed successfully, there will be one more record.
If no value is specified for other fields, the default value is null
Specify the default value default
Use str_to_date function for type conversion
The str_to_date function can convert a string into a date type date. Often used in insert
str_to_date('string date','date format')
mysql date format:
For example: insert into t_user(id,name,birth) values(1,'zhangsan',str_to_date('01-10-1990'));
If the provided date string is in this format %￥—%m—%d, mysql will automatically convert it
What is the difference between date and datetime?
date is a short date, only contains year, month, and day information
datetime is a long date, including year, month, day, hour, minute, and second information
The default format of mysql short date: %￥—%m—%d
The default format of mysql long date: %￥—%m—%d %h:%i:%s
Use now() in mysql to get the current time of the system
Modify update (DML)
Syntax format: update table name set field name 1=value 1, field name 2=value 2, field name 3=value 3... where condition;
update t_user set name='jack' where id=2;
Delete data delete (DML)
Syntax format: delete from table name where condition;
Note: If there is no condition, all data in the entire table will be deleted
Example: delete from t_user where id=2;
Quickly delete data in the table
The principle of delete statement to delete data:
The data in the table is deleted, but the real storage space of this data on the hard disk will not be released
The disadvantage of this deletion is that the deletion efficiency is relatively low
The advantage of this kind of deletion is: support rollback, you can restore data if you regret it!
The principle of truncate statement to delete data:
This kind of deletion efficiency is relatively high, the table is truncated once, and physically deleted
Disadvantages of this deletion: rollback is not supported
Delete table operation
drop table table name;//This is not to delete the data in the table, this is to delete the table
truncate is to delete the data in the table, the table is still there.
When creating a table, we can add some constraints to the fields of the table to ensure the integrity and validity of the data in this table
The function of the constraint is to limit the data in the table and ensure that the data added to the data table is accurate and reliable
What are the constraints?
Non-empty constraint: not null
Uniqueness constraint: unique
Primary key constraint: primary key
Foreign key constraint: foreign key
Check constraints: check (mysql does not support, oracle support)
Non-null constraint not null constraint field cannot be null
drop table if exists t_vip;
create table t_vip(id int,`name varchar(255) not null);
insert into t_vip(id,name) values(1,'zhangsan');
insert into t_vip(id,name) values(2,'lisi');
Unique constraint The field of the unique constraint cannot be repeated, but it can be null
drop table if exists t_vip;
create table t_vip(id int,`name varchar(255) unique, email varchar(255));//The constraint is added directly behind the column, this constraint is called column-level constraint
insert into t_vip(id,name,email)
insert into t_vip(id,name,email)
The combination of two fields is unique
idrop table if exists t_vip;
create table t_vip(id int,`name varchar(255), email varchar(255),unique(name,email));//The constraint is added directly after the column, this constraint is called table-level constraint
insert into t_vip(id,name,email) values(1,'zhangsan','[email protected]'); insert into t_vip(id,name,email) values(2,'zhangsan','[email protected]');
In mysql, if a field is constrained by not null and unique at the same time, the field automatically becomes the primary key field.
Primary key constraint (primary key)
Terms related to primary key constraints?
Primary key constraint: is a constraint
Primary key field: a primary key constraint has been added to this field
Primary key value: each value in the primary key field, the primary key value
What is the primary key? effect?
The primary key value is the unique identifier of each row of records.
The characteristics of the primary key: not null +unique
Only one primary key constraint can be added to a table.
The primary key value is recommended to use: int bigint char and other types
The primary key value is fixed-length, usually numbers.
The primary key value can be incremented by auto_increment, which starts from 1, and increments by 1.
Foreign key constraint
Foreign key constraint: a constraint
Foreign key field: foreign key constraints have been added to this field
Foreign key value: each value in the foreign key field.
Note: If t_class is the parent table
t_student is a child table
The order of deleting the table?
Delete the child first, and delete the father.
The order in which the tables are created?
Create the parent first, and create the child.
The order of deleting data?
Delete the child first, and delete the parent.
The order of inserting data?
Insert the father first, insert the son
create table t_class( classno int primary key, classname varchar(255)); create table t_student( no int primary key auto_increment, name varchar(255), cno int, foreign key(cno) references t_class(classno) );
A certain field in the parent table referenced by the foreign key in the child table, must the referenced field be the primary key?
Not necessarily the primary key, but at least the unique constraint
The foreign key value can be null