Basic details of MySQL (the essence of writing sql statements is English translation + mathematical logic!)

MySQL introduction

/*
MySQL database management system (file management system in essence)
Advantages over ordinary files: 1. Easy to manage data; 2. Suitable for complex multi-threaded operations

In MySQL: database database (essentially folder)
Create database: name of create database;
Display databases: show databases;
Data table: data files of table in different databases
Select database: use database name; you can choose any database you want
Database: data files of table in different databases
View the data tables in the current database: show tables;
Create a file: create table table name; (column name type)
Example: create table student;
View table structure: desc table name;
Delete table: drop table table name;
Delete database: drop databases database name;
Storage data: insert into table name valuess (value 1, value 2,... Value N)
varchar type is actually Java's String type. You can use 'or' save recommended '
Date type is the date type in java to be saved in the way of 'yyyy MM DD'

The above contents are collectively referred to as sql statements in four cases: DQL DML DDL DCL
DQL: data result language
DML: data operation language addition, deletion, modification and query
DDL: data definition language building tables, changing tables and changing databases
DCL: data control language control database access rights and functions

1. Enter mysql, and enter mysql -uroot -p ා (password) on the command line
2. View the databases in MySQL: show databases;
3. Use a database: user database name;
4. Create a new database: create database database database name
5. View the data tables in the specified database: show tables;
6. create table customer
7. Result of viewing table: desc table name
8. Drop table: drop table table name
*/

#This is the comment in the sql file
/*
Large database: oracle medium database: SQL server small database: MySQL
1. Case insensitive sql
2. sql can be on one or more lines
3. Keywords cannot be abbreviated or separated
4. Each clause is generally written separately (easy to read)
5. Using indentation improves the readability of statements
*/

The essence of sql is English translation!!!

SQL query statement

/*
Syntax of query statement in sql
select column name 1, column name 2 Column name N
from table name
*: not recommended for all column tests
*/

#1 Simple query

#sql query
SELECT * from employees;

#Query employee number and employee name and salary in the employees table
select employee_id,first_name,last_name,salary from employees;

select employee_id,first_name,last_name
from employees;
/*
		sql Syntax of query statement in
				select Column name 1, column name 2,... Column name N
				from Table name
		* :  Not recommended for all column tests 		
*/

#Allow aliases in database  (Omit a keyword as)
#If a string is used as the alias of a column, the''Slightly
#Dates and characters can only appear in single quotes, and the string is output once each time a line is returned
select emp.employee_id as Employee number,emp.salary as wages,emp.last_name as surname
from employees as emp;

select emp.employee_id,emp.salary,emp.last_name
from employees  emp;

select employee_id "Employee number" 
from employees;

#Find out all positions of the company   
select job_id from employees;

#Find out all positions of the company and de duplicate them
select distinct job_id from employees;

#Find out the names of all employees in the company and de duplicate them
#If the contents of the two columns are identical, de duplicate and the distinct keyword only needs to be written at the beginning
select distinct first_name,last_name from employees;

#Filtering and sorting in database
#It is found that the salary is greater than10000 Employee number, name and salary of
/*
		Syntax: select column name 1, column name 2,... Column name N
					from Table name
					where Conditional expression
		Comparison operator: > in part of database! = not recognized
		Logical operator: and (& & |! )Some databases do not recognize & &|
		Filter method: between...and
		not between ... and ...
		in(Value 1, value 2,... Value N) in a set
	  _ : Each "represents a character
		%: If% is in front, it means that you have not specified how many characters are in front
		   If% is followed, it means that no number of characters are specified
			 If there are% before and after, it means the content with the specified characters

There is a null value in the database:
			1,In all databases, the value result of any operation with null is null
			      You need to use the function ifnull (column, specified value) that handles null values
						If the specified column contains a null value, the null value becomes the specified value
						oracle No ifnull function in uses NVL (column, specified value)
			2,In the database, null value cannot use = or < >
						If you need to judge whether the control needs is or is not
		

*/
select employee_id,last_name,salary
from employees
where salary > 10000;

#It is found that the salary is less than3000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary <= 3000;

#Find out the employee number, name and salary named King
select employee_id,last_name,salary
from employees
where last_name = 'King';

#Find out the employee number, name and salary whose name is not King
select employee_id,last_name,salary
from employees
where last_name != 'King';

#Find out the employee number, name and salary whose name is not King
select employee_id,last_name,salary
from employees
where last_name <> 'King';

#It is found that the salary is greater than3000And the salary is less than10000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary > 3000 and salary<10000;

#It is found that the salary is greater than10000Or pay less than3000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary >10000 or salary<3000;

#The salary is not found3000reach10000Employee number, name and salary between
select employee_id,last_name,salary
from employees
where not(salary >= 3000 and salary <=10000);

#Find out the salary in3000reach10000Employee number, name and salary between
select employee_id,last_name,salary
from employees
where salary not between 3000 and 10000;

#Find out the salary is3000or10000，or17000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary in(3000,10000,17000);

#Query wage is not3000or10000，or17000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary not in (3000,10000,17000);



#Find out the employee number and name without bonus
select employee_id,last_name
from employees
where commission_pct is null;

#Find out the number and name of employees with bonus
select employee_id,last_name
from employees
where commission_pct is not null;

#To query the company's position name again
select distinct job_id 
from employees;

select distinct first_name,distinct job_id
from employees;

#The key words for de duplication must be written before the first column. Bing qian can only write them once
#If you write more than one column, you will get rid of the duplicate data in both columns
select distinct job_id,first_name 
from employees;

SQL syntax - operators

/*
Standard sql syntax
Select distinct column name 1, column name 2 Column name N
from table name alias (omitted)
where condition (omitted)

*/
#In addition to the standard comparison operators, there are other filtering operators in the database

#Query out salary5000-15000Employee number, name and salary between
select employee_id,first_name,salary
from employees 
where salary >= 5000 and salary <=15000;

#It is found that the salary is less than3000Or the salary is greater than16000Employee number, name and salary of
select employee_id,first_name,salary
from employees
where salary < 3000 || salary > 16000

#Found that the salary is not greater than5000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where not(salary > 5000);

#Find out the salary in8000-13000Employee number, name and salary between
select employee_id,first_name,salary
from employees
where salary between 8000 and  13000;

#The salary is not found8000-13000Employee number, name and salary between
select employee_id,first_name,salary
from employees
where not(salary between 8000 and  13000);

select employee_id,first_name,salary
from employees
where salary not between 8000 and 13000;

#Find out the employee No110,119,120,122Employee number and name of one of
select employee_id,first_name
from employees
where employee_id = 110 or employee_id = 119 or employee_id = 120 or employee_id = 122;

#in(value1,value2,...value N) In a collection
#not in(value1,value2,...value N) Not in a collection
#Query employee number in110,119,120,122Employee number and name of one of
#in keyword is inefficient, index index index or exists optimization can be used
select employee_id,first_name
from employees
where employee_id in (110,120,119,122);

select employee_id,first_name
from employees
where employee_id not in (110,120,119,122);

#Fuzzy query like
#If you need to use fuzzy query, you need to know two wildcards
#  _ :Represents a character
#  % :Represents multiple characters
#Commonly used  %Keyword%

#Find out the employee number, name and salary with a in the name
select employee_id,first_name,salary
from employees 
where first_name like '___a';

select employee_id,first_name,salary
from employees 
where first_name like '%a%';

#Find out that the name contains employee number, name and salary
select employee_id,first_name,salary
from employees where first_name like '%\_%';

#Find the name with'a'Employee number and name of
select employee_id,last_name
from employees
where last_name like '____a'; --Represents the total length of5 The fifth character is a --

#Find the name with'a'Employee number and name of
select employee_id,last_name
from employees
where last_name like '__a__'; --Represents the total length of5 The third character is a --

select employee_id,last_name
from employees
where last_name like '%a'; -- I don't know the length, but at the end a --

select employee_id,last_name
from employees
where last_name like '%a%';-- There are both before and after.%  Represents a fixed value a Content --

#To find out the employee number and name whose name has "\" can be used to query "\" and "\" %
select employee_id,first_name
from employees
where first name like '%\_%' ;

select employee_id,first_name
from employees
where first name like '%$_%' escape '$';

#Some databases may not support the escape keyword
select employee_id,first_name,salary
from employees
where first_name like '%$_%' escape '$';

#Find out the bonus of each employee
select salary * commission_pct 
from employees;

SQL92

/*	
		null Characteristic of value
				1,null value in database cannot be judged by comparison operator
							Use Is or is not to judge
				2,Value result of any value and null operation returns null
								Use ifnull function to convert null value to specified value
								ifnull(Column name, specify value): 
											Converts a null value in a column to the specified value
								The functions used by different databases are inconsistent
								! oracle The use of nvl in is consistent with Ifnull, only the function name is inconsistent
				3,MySQL If the ascending order is always at the top
					 oracle In, the null value represents infinity. If the ascending order is always the last
*/

select employee_id,last_name,salary
from employees
where salary > 10000;

#It is found that the salary is less than3000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary <= 3000;

#Find out the employee number, name and salary named King
select employee_id,last_name,salary
from employees
where last_name = 'King';

#Find out the employee number, name and salary whose name is not King
select employee_id,last_name,salary
from employees
where last_name != 'King';

#Find out the employee number, name and salary whose name is not King
select employee_id,last_name,salary
from employees
where last_name <> 'King';

#It is found that the salary is greater than3000And the salary is less than10000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary > 3000 and salary<10000;

#It is found that the salary is greater than10000Or pay less than3000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary >10000 or salary<3000;

#The salary is not found3000reach10000Employee number, name and salary between
select employee_id,last_name,salary
from employees
where not(salary >= 3000 and salary <=10000);

#Find out the salary in3000reach10000Employee number, name and salary between
select employee_id,last_name,salary
from employees
where salary not between 3000 and 10000;

#Find out the salary is3000or10000，or17000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary in(3000,10000,17000);

#Query wage is not3000or10000，or17000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where salary not in (3000,10000,17000);



#Find out the employee number and name without bonus
select employee_id,last_name
from employees
where commission_pct is null;

#Find out the number and name of employees with bonus
select employee_id,last_name
from employees
where commission_pct is not null;

#To query the company's position name again
select distinct job_id 
from employees;

select distinct first_name,distinct job_id
from employees;

#The key words for de duplication must be written before the first column. Bing qian can only write them once
#If you write more than one column, you will get rid of the duplicate data in both columns
select distinct job_id,first_name 
from employees;

/*
		Standard sql syntax
		select distinct(Can be omitted) column 1, column 2,... Column N
		from Table name alias (omitted)
		where Conditions (omitted)
		
*/
#In addition to the standard comparison operators, there are other filter operators in the database
#Query out salary5000-15000Employee number, name and salary between
select employee_id,first_name,salary
from employees 
where salary >= 5000 and salary <=15000;

#It is found that the salary is less than3000Or the salary is greater than16000Employee number, name and salary of
select employee_id,first_name,salary
from employees
where salary < 3000 || salary > 16000

#Found that the salary is not greater than5000Employee number, name and salary of
select employee_id,last_name,salary
from employees
where not(salary > 5000);

#Find out the salary in8000-13000Employee number, name and salary between
select employee_id,first_name,salary
from employees
where salary between 8000 and  13000;

#The salary is not found8000-13000Employee number, name and salary between
select employee_id,first_name,salary
from employees
where not(salary between 8000 and  13000);

select employee_id,first_name,salary
from employees
where salary not between 8000 and 13000;

#Find out the employee No110,119,120,122Employee number and name of one of
select employee_id,first_name
from employees
where employee_id = 110 or employee_id = 119 or employee_id = 120 or employee_id = 122;

#in(value1,value2,...value N) In a collection
#not in(value1,value2,...value N) Not in a collection
#Query employee number in110,119,120,122Employee number and name of one of
#in keyword is inefficient, index index index or exists optimization can be used
select employee_id,first_name
from employees
where employee_id in (110,120,119,122);

select employee_id,first_name
from employees
where employee_id not in (110,120,119,122);

#Fuzzy query like
#If you need to use fuzzy query, you need to know two wildcards
#  _ :Represents a character
#  % :Represents multiple characters
#Commonly used  %Keyword%

#Find out the employee number, name and salary with a in the name
select employee_id,first_name,salary
from employees 
where first_name like '___a';

select employee_id,first_name,salary
from employees 
where first_name like '%a%';

#Find out that the name contains employee number, name and salary
select employee_id,first_name,salary
from employees where first_name like '%\_%';

#Find the name with'a'Employee number and name of
select employee_id,last_name
from employees
where last_name like '____a'; --Represents the total length of5 The fifth character is a --

#Find the name with'a'Employee number and name of
select employee_id,last_name
from employees
where last_name like '__a__'; --Represents the total length of5 The third character is a --

select employee_id,last_name
from employees
where last_name like '%a'; -- I don't know the length, but at the end a --

select employee_id,last_name
from employees
where last_name like '%a%';-- There are both before and after.%  Represents a fixed value a Content --

#To find out the employee number and name whose name has "\" can be used to query "\" and "\" %
select employee_id,first_name
from employees
where first name like '%\_%' ;

select employee_id,first_name
from employees
where first name like '%$_%' escape '$';

#Some databases may not support the escape keyword
select employee_id,first_name,salary
from employees
where first_name like '%$_%' escape '$';

#Find out the bonus of each employee
select salary * commission_pct 
from employees;

/*	
		null Characteristic of value
				1,null value in database cannot be judged by comparison operator
							Use Is or is not to judge
				2,Value result of any value and null operation returns null
								Use ifnull function to convert null value to specified value
								ifnull(Column name, specify value): 
											Converts a null value in a column to the specified value
								The functions used by different databases are inconsistent
								! oracle The use of nvl in is consistent with Ifnull, only the function name is inconsistent
				3,MySQL If the ascending order is always at the top
					 oracle In, the null value represents infinity. If the ascending order is always the last
*/
#Find out which employees have bonuses
select employee_id,first_name,commission_pct
from employees
where commission_pct is not null;

#Find out the number, name and monthly salary of each employee
select employee_id,first_name,salary+salary * ifnull(commmission_pct,0) A monthly salary
from employees

#Find out the number, name and monthly salary of each hospital
select employee_id,first_name,salary + salary * ifnull(commission_pct,0) A monthly salary
from employees

#Order keyword order by
#Sort from small to large by default
#Default ascending uses the keyword asc asc which can be omitted
#Use the keyword desc in descending order
#Find out the number, name and commission percentage of each employee and sort them by Commission

select employee_id,first_name,commission_pct
from employees
where commission_pct is not null
order by commission_pct desc;

#Other contents of sorting
select employee_id,first_name,ifnull(commission_pct,0)
from employees
order by Percentage of commission asc;

#Multi column sorting
#Find out the company's employee number, name, salary and bonus in descending order of salary. If the salary is the same, they will be in ascending order of bonus
select employee_id,first_name,salary,commission_pct
from employees
order by salary desc,commission_pct asc;

#multi-table query
#Query employee number, name, department name and department address
#Cartesian product
select employee_id,first_name,department_name,street_address
from employees,departments,locations

#All multi table associated queries are based on the filtering of Cartesian product
#SQL92 and SQL99

#SQL92 equivalent connection
#Find out the employee number, name and department name
select employee_id,first_name,department_name
from employees,departments
where employees.department_id = departments.department_id;

#The table can be aliased and fuzzy columns can be accessed through the alias of the table
select employee_id,first_name,department_name
from employees emp,departments dept
where emp.department_id = dept.department_id;

#Find out the employee number, name, department name and department address
select e.employee_id,e.first_name,d.department_name,l.street_address
from employees e,departments d,locations l
where e.department_id = d.department_id 
and d.location_id = l.location_id;

#Unequal connection of SQL92
#Find out the employee number, name, salary and salary grade
select employee_id,first_name,salary,grade
from employees emp,job_grades jg
where salary between lowest_sal and highest_sal;

#Find out the employee number and name of each employee and the employee number and name of his / her supervisor
select emp.employee_id,emp.first_name,manager.employee_id,manager.first_name
from employees emp,employees manager
where emp.manager_id = manager.employee_id;

#Create a database love
create database love;

use love;
#Create a girl table
create table girls(
id int,
name varchar(20),
boy_friend_id int
);

create table boys(
id int,
name varchar(20)
);

insert into girls values(1001,'Xiaohong',1);
insert into girls values(1002,'Blue',1);
insert into girls values(1003,'Little green',2);
insert into girls values(1004,'Xiao Huang',3);
insert into girls values(1001,'Little purple',4);
insert into girls values(1002,'Xiao Bai',null);
insert into girls values(1001,'Naranjito',null);
insert into girls values(1002,'Powder',null);

insert into boys values(1,'Chinese rhubarb');
insert into boys values(2,'Two ha');
insert into boys values(3,'Three jump');
insert into boys values(4,'Single dog');

#Find out all the boys and girls
#Equivalent connection
select *
from firls g,boys b
where g.boy_friend_id = b.id ;

SQL99

#SQL99 is equivalent to the way of unequal connection
select employee_id,first_name,salary,grade
from employees e
join job_grades jg
on e.salary between jg.lowest_sal and jg.highest_sal;

#Find out the employee number name, salary and grade whose salary grade is e
#SQL92 version connection condition and filter condition are used together
#Poor sql readability if multiple tables are connected and multiple filter conditions are mixed
#sql is not easy to write
select employee_id,first_name,salary,grade
from employees e,job_grades jg
where e.salary between jg.lowest_sal and jg.highest_sal
and jg.grade = 'e';

#SQL99 distinguishes join on from where to improve readability
#Split the connection condition and filter condition. The connection condition and filter condition do not affect each other
select employee_id,first_name,salary,grade
from employees e
join job_grades jg
on e.salary between jg.lowest_sal and jg.highest_sal
where jg.grade='e';

#sql99 self connect (Multi table associated query)
#Find out the employee number and name and the employee number and name of his supervisor
select e.employee_id,e.first_name,m.employee_id,m.first_name
from employees e
join employees m
on e.manager_id = m.employee_id;

#sql99 external connection
#join by default, there is a keyword in front of it. Inner is connected by default
#outer join outer can be omitted
#Left join shows the left table
#right JOIN shows the right table
select *
from girls g
inner join boys b
on g.boy friend_id = b.id;

select *
from girls g
right outer join boys b
on g.boy friend_id = b.id;

#full keyword is not supported in mysql for all external connections
#You must use the union keyword to implement an all out connection
#oracle supports full keyword

Left table Association, right table Association

/*
		Drive table: from the following table drive table
		Matching table: table matching table after join

		Left outer connection: display all contents in the drive table
		Right outer connection: display all contents in the matching table
*/
#Find out the number, name, department name and work place of the employee, including the Department without employee
select employee_id,first_name,department_name,street_address
from employees e
join departments d
on e.department_id = d.department_id
join locations l
on d.location_id = l.location_id;

Character function, dual virtual table

/*
Common character functions:
Length: find the length
lower: lowercase the specified column
upper: capitalizes the specified column
*/

#Character function
#Amount togetBytes().getLength()
select length("Eating, drinking and smoking");

#Find out Steven's personal information mysql does not distinguish between numerical value and letter case
#oracle distinguishes case of data
select *
from employees 
where first_name='Steven';

#Find out the number and name of the company's employees. All names are required to be capitalized
select employee_id,upper(first_name) from employees;
select employee_id,lower(first_name) from employees;

select upper("hello world!");

/*
dual virtual table:
All databases have virtual tables
Virtual tables are used to hold constant temporary data and functions
Constants and methods can be omitted from dual through virtual table query
***Virtual table not allowed to view structure not allowed to query single table not allowed to combine Cartesian product with other tables
*/

select "Hello World!" from dual;

#Index subscript in database from1start
select substr("asdfg",3,5);

#Index found return specified first index value not found return0
select instr("asdfgh",'g');

#Fill content
select lpad("abc",5,"*");
select lpad("abc",5,"Idle away in seeking pleasure");
select lpad("abc",10,"Sauna one-stop service");

#Remove space before and after
select trim('   abc   ') trim;

select trim("H" from "Hello World!");

#trim can only remove the contents before and after, not the middle
select trim ("1" from "Hello World!");

#Replace after replace before
select replace("Hello World!",'l','s');

#Numeric function

#Rounding: Rounding and when negative java Different algorithms recommended in Java
select round(-4.5);
#Rounding up
select ceil(5.1);
#Rounding down
select floor(4.5);
#Absolute value
select abs(-4);
#Truncate to identify decimal places
select truncate(4.1111);
select truncate(1111,-3);
#Seek surplus
select mod(9,4);
#Random number generation [0,1) Random number of interval
select rand();
select floor(rand()*100+1);

#Date function
#There are three formats for dates in mysql
/*
		yyyy-MM-dd            curdate()
		yyyy-MM-dd hh:mm:ss   now()
		hh:mm:ss
*/

select now();  Accurate to seconds
select curdate();	Accurate to date
select curtime(); Day to second

select employee_id,first_name,department_id,
			case department_id  when 10 then salary * 1.1
													when 20 then salary * 1.2
													when 30 then salary * 1.3
			else	salary end sal
from employees;

Grouping function

/*
Grouping function in multiple values, get the value of the specified requirement and return one row of the result value
null value is ignored by grouping function by default
There are four characteristics of null value:
1. Null value cannot be judged by comparison operator. It must use is or is NOT
2 null value and any data operation result are null
3. Default minimum of null value in MySQL oracle default maximum
4 null value ignored by all grouping functions

#Grouping function
#Find the value of the specified requirement in multiple values
#Count count
#Find out how many people there are in the company
select count(employee_id) from employees;
#sum summation
#Find out the total monthly salary of the company
select sum(salary) from employees;
#avg average
#Find out the average salary of the company
select sum(salary)/count(employee_id) from 	employees
select AVG(salary) from employees;

#Average monthly wage of the company
select AVG(salary + salary * commission_pct) from employees;
select AVG(salary + salary * ifnull(commission_pct,0)) from employees;
#max Max
#Min to min
#Seeking the maximum and minimum wage of the company
select max(salary),min(salary) from employees;
#The number, name and salary of the employee seeking the highest salary of the company
#When group function and common column are queried together, common column has no practical significance
#Most of the data will be wrong
select * from employees;
select employee_id,first_name,max(salary) from employees;

select AVG(salary) from employees where department_id=10;

#group BY
select department_id,AVG(salary)
from employees 
group by department_id;

#Find out the quantity of each position in each department
select department_id,job_id,count(*)
from employees
group by department_id;

#A grouping function cannot be used as a condition after a where statement
#The grouping function could have been used as a condition after the group by statement
#Subquery
#Question: do you care about the maximum wage now?

select employee_id,first_name,salary
from employees
where salary =
(select min(salary) from empployees);

#It is found that the number of people in the Department exceeds10Department number and number of employees
#If the grouping query statement needs to be filtered, you must use having
#having can only be used for filtering conditions after group query
#Group functions can no longer be used in where clause
#You can use group functions in the having clause
select department_id,count(employee_id)
from employees
group by department_id
having count(employee_id)>5;

#How much is Abel's salary
select salary from employees where last_name = 'abel';
#than11000Many
select employee_id,last_name,salary
from employees
where salary>11000;

# Connect to a sql for efficiency
# And I don't care about the salary of abel, so I don't need to query the salary of abel
select employee_id,last_name,salary
from employees
where salary>
(select salary from employees where last_name='abel');

#Multiline subquery
#The result of the subquery exceeds1Lines cannot be judged only by comparison operators
#Find out who is paid more than steven
select employee_id,first_name,salary
from employees
where salary >
(select salary from employees where first_name = 'steven');

#Find out who's paid less than all steven's
select employee_id,first_name,salary
from employees
where salary < all
(select salary from employees where first_name = 'steven');

#Find out who is paid less than any steven
select employee_id,first_name,salary
from employees
where salary < any
(select salary from employees where first_name = 'steven');

#Seeking job_id = it_prog Minimum wage of
select min(salary)
from employees
where job_id = 'it_prog';

#Ask for employee number, name, job Ou ID and salary lower than the lowest
select employee_id,first_name,job_id,salary
from employees
where salary<any
(select min(salary)
from employees
where job_id = 'it_prog';);

#Other departments required
select employee_id,first_name,job_id,salary
from employees
where salary<any
(select min(salary)
from employees
where job_id = 'it_prog';) 
and job_id <> 'it_prog';

#Find out the employee No. and name without Supervisor
select employee_id,first_name
from employees
where manager_id is null;

SQL data type, transaction, constraint

/*
	Data types in the database
			Different databases are inconsistent
			Integer: byte short int long in java 
						mysql tinyint smallint int bigint
						!! Usually, both java and database use int
			Floating point: float double in java
							mysql Middle float double
							Because of the problem of floating point precision, we usually use decimal
			boolean: boolean in java
							mysql The type does not exist in some databases with boolean type in
			Character type: char in java
							mysql char type in is different from java
							mysql char fixed length String in
										varchar Variable length string StringBuilder
			date time datetime timestamp
			Large types: blob and text binary content long text content

Data types in the database have length by default
 Most data types can be omitted varchar cannot be omitted
#int(11)  11 digits
#double(5,2) The representative length is 5, with two significant digits. The maximum value is 999.99
#char Is the default length of a fixed length string. Usually, the gender uses m or f

*/
#Modify data in table
update Table name set Column names = Column value
update student set gender = 'm';#Modify all data in the table
#Add filter condition
update student set score = 100
where score < 100;

/*
		Affairs:
					1,In order to deal with complex business situations
								The function that can cause undo and wait is called transaction
							set autocommit = false; Open transaction
							commit:Submission
							rollback；RollBACK
							Blocking occurs when two transactions occur simultaneously for a piece of content
*/
#Open transaction
set autocommit = false;
delete from student;
rollback;

/*
		Constraints:
					Detection of storage data existing in each column added during table creation
					Common constraints:
					unique   Unique constraint current column value is not repeatable
          not null  Non empty constraint current column value cannot be empty  
          primary key Primary key constraint is unique and not empty
											Primary key index reduces the efficiency of addition, deletion and modification, and improves the efficiency of query. Each table will have a primary key
          foreign key  A foreign key constraint specifies that a column is a child of another column
												The parent column must have the same data before the child column can have it
												The parent column can be deleted only when the child column must be empty
          default    check								

				!!mysql The self growth function provided in requires that the column must be the primary key auto ﹣ increment
						oracle oracle uses sequence to achieve self growth

*/
drop table student;

create table student(
id int unique,
name varchar(20));

insert into student values(1,'a');

#limit keyword index from0Start index, number of rows
#Query the top five employees of the company
select * 
from employees
order by salary DESC
limit 5;

select * 
from employees
order by salary DESC
limit 0,5;
#Find out the salary of the company's employees6-10name
select * 
from employees
order by salary DESC
limit 5,5;

Code level - dipper

Published 6 original articles, won praise 0, visited 128

Private letter follow

Posted by adt2007 on Sun, 15 Mar 2020 03:56:00 -0700

Programmer Group