mysql database learning collation
Log in to the database
Mysql-u username-p password
Look at the database and note that it ends with a semicolon
show databases;
# Display all databases (# is the symbol for mysql annotations)
_____________________________________________________________________________________
Overview of SQL
SQL(Structural query language) Structured Query Language for Operating Databases
Three Main Lines of SQL Learning
# Learning SQL is divided into three parts as follows: # DML: Data Manipulation Language - > INSERT / DELETE / UPDATE / SELECT # DDL: Data Definition Language - > CRAETE (with a column) / ALTER (modification) / DROP (deletion) / TRUNCATE (empty) / RENAME (rename) # DCL: Data Control Language - > COMMIT Submitting Data) / ROLLBACK (Rollback Operation) / GRANT / REVOKE # When it comes to databases, it's customary to mention adding, deleting, and checking. It's easier to add, delete and modify databases and more difficult to query. # Large amount of data can be displayed by limit paging, such as: query 500 data, 10 display
_____________________________________________________________________________________
query
Basic SELECT statements;
elect Query columns from Query table` show databases; use test1 #Some fields are null and java null, which means that there is no assignment. NULL and 0 are two concepts. select name myname ， age as myage from myname #Specify the form of field name aliases available for the fields in the table after query #Or the form of the field name as alias
# Pay attention to everything. "It's all half-angled."
# If a column alias wants to contain spaces, it is caused by an alias or an alias.
# Specification requires double quotation marks, mysql does not strictly oracle database must be double quotation marks
SELECT hid H_ID,gname AS "GAME NAME" FROM girl;
# Query all databases
SHOW DATABASES;
# Use the specified database
USE test1;
# Basic query operations, which columns, which columns
SELECT hid,gname #To query fields FROM girl; #Table to query
The shortcut for queries is F9
Query all columns in this table
SELECT *
FROM girl;
# aliases of columns
# You can use a pair of "" package aliases
SELECT hid 'H ID',gname AS "Game nickname" FROM girl;
# There are three common types of data:
# 1 numerical type, integral floating point type of java # 2 Character/String, Name, Street, and Provinces are not strictly required to say that a character is one and a string is multiple. # 3-day type # There are many more besides that. # Date and character variables. To use a pair of single quotation marks to refer to, it can only appear in single quotation marks. # Double quotation marks are used to alias a column.
# Display table structure
# Corresponding Change Table
# Display whether the field name data type is an empty key default extra field property
# The graphical interface is equivalent to right-clicking and changing the table after selecting the table.
DESC girl；
# Command Line Display Table Structure
# Or use the full name of the command
DESCRIBE girl;
Comparisons Operator Meaning = Equivalent to (not===) > greater than >= greater than or equal to Less than <= less than or equal to <> is not equal to (or can be!=) Emphasis: Assignment Use: = Symbols
# Other comparison operators
Operator Meaning BETWEEN Between Two Values (Containing Boundaries) ... AND ... IN(set) is equal to one in the list of values LIKE Fuzzy Query IS NULL null value
By positioning the cursor on a query statement in sqlyog, the current query can be executed
Characters in sql are understood as strings
Logical operation Operator Meaning AND logical union (and) OR logic or/or NOT Logic No (Non)
# mysql has just installed four initialized databases
# The key is mysql, where the system-related tables are placed.
# User name, password are all here, password is encrypted
_____________________________________________________________________________________
Filtering and sorting data
# 1 Filter 2 Sort
# Query all information in the table goods in test1 database that cat_id is 8 and return
SELECT * FROM goods #Filtering data WHERE cat_id=8;
#Query shop_price > 60 for goods SELECT goods_name,cat_id,shop_price FROM goods #WHERE (shop_price > 3000 or shop_price < 2000) ; WHERE shop_price != 1000;
# Between... and... (between two values, including boundaries)
SELECT * FROM goods #where shop_price between 2000 and 3000; WHERE shop_price BETWEEN 3000 AND 2000; #It's more convenient to use between and with boundaries, but easier to use operators such as >= when excluding boundaries.
# in (set): Either of the values ()
SELECT * FROM goods #where shop_price=2000 or shop_price=2500 or shop_price=3000; WHERE shop_price IN( 2000,2500,3000 );
# like: Fuzzy query queries goods table goods_name containing a data
SELECT * FROM goods #where goods_name like '%p%e%' or goods_name LIKE '%e%p%';
# Who like s to write all the details here?
#% Number represents 0 or more characters
# oracle is case-sensitive inside single quotation marks
#WHERE goods_name LIKE '%p%' and goods_name LIKE '%e%';
# Query goods_name for data whose second character is a
WHERE goods_name LIKE'_d%'; in mysql is a placeholder, and an underscore represents a character'_d%'and the fourth character is d.
# Any character is OK. Any character is OK.
# Query for data with the second character being and the third character being a
# escape character\
SELECT * FROM girl #where gname like'% female'; WHERE gname LIKE '%_%female' ESCAPE '_'; #ESCAPE''means to specify escape characters, so there are two ways to escape.
# is null: null
# is not null: non-null value
SELECT * FROM girl WHERE gname IS NOT NULL; SELECT goods_name, goods_id,market_price,goods_number , (market_price * goods_number) AS 'Total Available Sales' #Specify a new column with total fields and product contents #Every null value involved in the operation, the result is null, we should understand that null is null is not equal to 0, if you want to null as 0, you can use a function to achieve. FROM goods;
# Sort grouping order by
SELECT * FROM goods #Order By market_price;
#Order by default is sorted in ascending order #Or you can specify your own sort #ASC: ascend can also be omitted #Desc: descend ORDER BY market_price DESC;
# If you want to do some filtering while sorting, then where can't be left untouched and behind order by # where is usually next to from. # from is followed by where,order by is usually written at the end of the query statement
#Query goods_number > 10 after market_price decreases SELECT * FROM goods WHERE goods_number>10 ORDER BY market_price DESC;
# Secondary Sorting
SELECT * FROM goods WHERE goods_number>10 ORDER BY market_price DESC,click_count; #If there are more first-order sorting results, then you can specify two-level, three-level, and n-level sorting. #After the original ranking condition, the condition can be added.
# Alias can be used to sort
SELECT goods_name AS GOODSNAME #assign an alias FROM goods WHERE goods_number>10 ORDER BY GOODSNAME; #Sort by alias
_____________________________________________________________________________________
multi-table query
# Implementing queries between multiple tables
# involving multiple table joins
# Multi-table connection and multi-table query
# Why not put all the information in one table, messy, miscellaneous and inconvenient to manage, just like classes in java
# create database student information; USE Student Information; SELECT name, corresponding address FROM Basic Information, Area Table; #30 Lines 6*5=30
# The above query is wrong, which is called Cartesian set error.
# There are no join conditions for two tables, or write conditions, but they fail.
# If in a multi-table connection, no multi-table join condition or join condition is declared to be invalid.
# Cartesian coordinate system, also known as Cartesian coordinate system
SELECT 30/6 FROM DUAL; #dual is a virtual table
# Correct, multi-table connection, there must be connection conditions
USE Student Information; SELECT name, corresponding address FROM Basic Information, Area Table # Connection conditions WHERE Basic Information. `Area Number ` = Area Table. `Area Number'; # The grammar of association is Table 1. Field name = Table 2. Field name. If two field names are the same, the association is successful. # The connection conditions are shown.
# Error demonstration
USE Student Information; SELECT Name, Corresponding Address, Region Number. Is this Region Number in the Basic Information Table or in the Region Table? # Ambiguity arises, so ambiguous errors are reported. The solution at this point is to specify specific tables. # Representation by table. area number # The names and addresses of the two fields behind select are unique in the corresponding two tables, which are very easy to find. # If the query column appears in multiple tables # You must specify the table to which this column belongs. # It's better to specify tables for each column when querying. # From the point of view of SQL optimization, it is more efficient to specify the fields of the tables to which you belong. # There are other operations such as optimization in mysql advanced course FROM Basic Information, Area Table WHERE Basic Information. `Area Number ` = Area Table. `Area Number'; # ambiguous ambiguous
# Alias of tables
USE Student Information; SELECT. Name, d. Corresponding address FROM Basic Information AS e, Area Table d # Connection conditions WHERE e. `Area number `= d. `Area number `; # If you specify an alias, you can use an alias instead of the original name, and you can use AS to specify an alias.
# The aliases of columns can only be used in order by, but not elsewhere. # So column aliases can only be used for sorting, not filtering.
# Query specific address operation three tables
SELECT. Name, d. `Area Number', f. Specific Address FROM Basic Information AS e, Area Table AS d, Address Table AS f WHERE e. `Area Number ` = d. `Area Number` AND. `Address number `= f. `Address number `; # Three tables write two join conditions # Equivalent join, two tables must have common fields
# Summary 1: If there are N tables to query, then there are at least N-1 join conditions.
# Summary 2: Table join classification
# Equivalent Connection VS Non-Equivalent Connection # Self-Connecting (Self-Connecting) VS Non-Self-Connecting (Self-Connecting) # Internal connection VS external connection
# Basic information of non-equivalence connection
SELECT Basic Information. `Name, Basic Information. `Age, Wage Rank. `Rank` FROM Basic Information, Wage Rank # where basic information. `Wage `> = wage level. ` From `and basic information. Wage < = wage level. # The coprocess between and is clearer WHERE Basic Information. Wage BETWEEN Wage Rank. From AND Wage Rank. `to` # Sort the result wages in descending order after query ORDER BY Wage Rank. `Rank `DESC;
# All of the above are non-self-connected.
# Self-Connecting (Self-Connecting)
# Query each employee and their managers for information
SELECT emp. Number, emp. `Name `, mgr. ` Number `, mgr. `Name` FROM Basic Information emp, Basic Information mgr # Operating on two tables is essentially a table WHERE emp. # The order of output changed after querying.
# This is a useless example. SELECT Basic Information. `Work Number', Basic Information. `Supervisor Work Number` FROM basic information;
# Internal join: Internal join: merge rows of two or more tables with the same column
# The result set does not contain rows that do not match one table with another
# Internal join: Query only rows whose join conditions match.
# The result set does not contain mismatched rows
USE Student Information; SELECT. Name, d. `Address Number` FROM Basic Information AS e, Area Table d # Connection conditions WHERE e. `Area number `= d. `Area number `; # The area number of Beibei in this table is empty, so it is filtered out.
# External join: Two tables in the join process except for returning rows that satisfy the join condition
# In addition, it returns rows that do not satisfy the conditions in the left (or right) table.
# This connection is called a left (or right) outer connection. When there are no matching rows,
# The corresponding column in the result table is null (NULL).
# External connection
# Left External Connection: In addition to querying rows of information that match the join conditions, information that does not meet the join conditions can also be found in the left table.
# Right External Connection: In addition to querying information rows that match the join conditions, you can also find information that does not meet the join conditions in the right table.
# The number of data queried by an external connection is greater than or equal to the number of data queried by an internal connection.
# USE Student Information; # SELECT. Name, d. `Address Number` # FROM Basic Information AS e, Area Table d # Connection conditions # WHERE e. `Area Number `= d. `Area Number `(+);# Makes the left and right tables the same length # oracle is relatively simple, adding + means the left outer connection, while mysql can not be implemented.
# Query all employees'names and address numbers (someone's address number is empty)
# mysql External Connections can only use join, other connections, join and where # The grammar of sql 99 provides another way to connect multiple tables # Internal connection SELECT. Name, d. `Address Number` FROM Basic Information e JOIN Area Table d # Connection conditions ON e. `Area number `= d. `Area number `; SELECT Number `Regional Table', Name, Address Number FROM Basic Information j JOIN Area Table q # Add a Table ON j. `Area Number ` = q. `Area Number `; Connection Conditions # Where.... If there are filter conditions plus where
# Internal join of three tables
SELECT Job number`Regional table`,Full name,q.Address Number FROM Essential information j JOIN Regional table q #Add a table ON j.`Area Number` = q.`Area Number` #Connection conditions JOIN Address table AS l ON q.`Address Number`=l.`Address Number`; #join #On Multiple Tables Continue to join on #Adding the INNER keyword also means internal connection SELECT Job number`Regional table`,Full name,q.Address Number FROM Essential information j INNER JOIN Regional table q ON j.`Area Number` = q.`Area Number` #Connection conditions JOIN Address table AS l ON q.`Address Number`=l.`Address Number`;
# External Connection Using sql 99 Grammar
# Add LEFT OUTER to the left side of JOIN to call the left outer connection SELECT Industry Number `Regional Table', Name, Address Number, q. `Regional Characteristics` FROM Basic Information j LEFT OUTER JOIN Area Table q# Add a Table ON j. `Area Number ` = q. `Area Number `; Connection Conditions # Where.... If there are filter conditions plus where # List all the information in the left table
# Right External Connection
SELECT Industry Number `Regional Table', Name, Area Table `Address Number, q. ` Regional Characteristics` FROM Basic Information j RIGHT OUTER JOIN Area Table q # Add a Table ON j. `Area Number ` = q. `Area Number `; Connection Conditions # Where.... If there are filter conditions plus where # After listing the matching information between the two tables, list the unqualified information in the right table. # Equivalent to listing all the information in the right table # That is, the left table is not satisfied, and the right table is not satisfied. # Full Outside Connections: In addition to querying information about rows that match the join conditions, # It can also query information that does not satisfy the conditions in the left and right tables. # oracle supports full-out connections. Using full keyword implementation, mysql does not support # If mysql wants to achieve the same function, it can only find unio from multiple tables, and
# SELECT Industry Number `Regional Table', Name, Area Table `Address Number, q. ` Regional Characteristics` # FROM Basic Information j full OUTER JOIN Area Table q# Add a Table # ON j. `Area Number ` = q. `Area Number `; Connection Conditions SELECT Industry Number `Regional Table', Name, Area Table `Address Number, q. ` Regional Characteristics` FROM Basic Information j RIGHT OUTER JOIN Area Table q # Add a Table ON j. `Area Number ` = q. `Area Number `; Connection Conditions
_____________________________________________________________________________________
function
# One-way function
# sql is similar to the method in java
#y=f(x1,x2...n)
# One-line function: A function with one input and one output is called a one-line function.
# Multi-line function: A function with multiple inputs and one output is called a multi-line function.
# Row: A row of data in a table
# One line in and one line out is a one-way function.
# To go in and out of a row is to go in and out of multiple wages with a multi-line function, and to come out with the highest wage.
# One-line functions operate on data objects
# One-way function
# Database objects: table view index
# sequence function (FUNCTION) stored procedure (PROCEDURE) trigger
# Like a java class
# View: Unlike tables, views modify corresponding data to update source data
# Index: In order to search quickly, hundreds of thousands of data in the table can be found quickly, and the index can be created for the table.
# Sequence: When adding data, let the sequence add automatically, several numbers at a time, such as 1357
# Function, similar to java's method with no return value
# Stored procedures, similar to java methods with return values
# Trigger: Do the following similar operations, when deleting data, each deletion triggers one, can be used as a copy, backup.
# It's not DBA at work, so you don't need to dig into the three structures of functions.
# One-way function
# Operating data objects
# Accepting parameters returns a result
# Character function
SELECT 'hello','heLLo',LOWER('heLLo'),UPPER('hello')
FROM DUAL; #Write a DUAL without any table
USE Essential information;
SELECT *
FROM Essential information
WHERE LOWER(Full name) = LOWER('KING');#If the database is lowercase
#sql standard strict character case-sensitive mysql is not so strict, ignoring case-sensitive
SELECT CONCAT('AAA','bbb','ccc')
FROM DUAL;
#xxx worked for xxx
SELECT CONCAT(emp. `Name `, `worked for', mgr. `Name `) AS'Work Information' FROM Basic Information emp, Basic Information mgr # Operating on two tables is essentially a table WHERE emp.
# The feature of this method is to start with the second parameter and take the length of the third parameter as a substring.
SELECT SUBSTR('HelloWorld',2,5)#The mysql index location starts at 1
#java JDBC operates on databases, and database indexes in some APIs start from 1.
FROM DUAL;
#LENGTH() Length
#The position where the INSTR() character first appears in the string, and the output is 0 if it does not appear.
#LPAD()
SELECT INSTR('helloworld','l')
FROM DUAL;#3
#LPAD ("he", "10",'') right alignment of 10 bits, insufficient complement''
#RPAD('he',10,') left alignment
#TRIM ('h'FROM'helloh') can take anything. Here's the ending H
FROM DUAL;
SELECT TRIM(' ' FROM 'helloh')
FROM DUAL;
#REPLACE('abcd','b','m') has been replaced, and all have been replaced.
# Mathematical function
SELECT ROUND(12.345,0),ROUND(12.345,2),ROUND(12.34,-1) FROM DUAL; #When the third parameter of ROUND rounding is -1, the result is 102 rounded. #The third parameter, if 0, can be removed, is the default behavior.
SELECT TRUNCATE(12.345,0),TRUNCATE(12.345,2),TRUNCATE(12.34,-1) FROM DUAL; #truncate() truncate the above results 12.34 10, respectively #MOD(12,5) redundancy can be positive or negative SELECT MOD(12,7),MOD(-12,7),MOD(12,-7),MOD(-12,-7),MOD(12.3,3) FROM DUAL; #The above results are 5-55-50.3
# Date function
#Function now() returns: date, time SELECT NOW() , DATE(NOW()), TIME(NOW()),SECOND(NOW()),MINUTE(NOW()),HOUR(NOW()), DAY(NOW()),MONTH(NOW()) , YEAR(NOW()) FROM DUAL;
# General function
SELECT (goods_price*IFNULL(goods_number,0)) #ifNULL(expr1,expr2) If the first parameter is 0, replace the first parameter with the second parameter. FROM DUAL; #oracle uses nvl substitution
# conditional expression
#case WHEN then then then else end
# Query all employee information.
# If their age prints 10 times their salary
# If their age prints 20 times their salary
# If it prints 30 times its salary in a year
# If other ages print their wages
# The format is as follows:
SELECT Number, Wage, CASE Age WHEN 10 THEN Wage*10
WHEN 20 THEN Wages*20
WHEN 30 THEN Wages*30
ELSE Wage END AS'New Wage'
FROM Basic Information; Can Not Have ELSE
# where... where filtering can be specified
# There is no punctuation between cases when the n
# multiline function (grouping function)
# For example, query the maximum wage
# Common grouping function: avg() sum() max() min() count()
# avg() sum() only operates on numeric variables, other types are not allowed
# Character type, date type variable cannot be manipulated
# Find the average wage, and
SELECT AVG (Wage), SUM (Wage) FROM basic information; # mysql does not report errors in some meaningless manipulations, which oracle does well
# Max (), min () for numeric, character and date operations
SELECT MAX (Wage), MIN (Wage), MAX (Name), MIN (Name) FROM basic information;
# count() calculates the number of non-empty data in the specified column data
SELECT COUNT (name), COUNT(NULL),COUNT(1),COUNT(*)#1 take out a data to see if it is NULL. FROM basic information; # avg=sum/count. All they want is non-empty data without considering control. SELECT AVG (Wage), SUM (Wage), SUM (Wage)/COUNT (Wage) FROM basic information; # sum (wage) / count (*), AVG (IFNULL (wage, 0)) # group by implements grouping of departments to calculate the average wages of each group
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;#Average by group id #For example, the state calculates the average wage of various industries in China. #The school calculates the total tuition fee for each department.
# group by specifies the column to be grouped SELECT COUNT (Name), Area Number # Find the Number of Each Region FROM Basic Information GROUP BY area number;
#Average wages for different sectors and types of work SELECT department_id,AVG(salary) FROM employees GROUP BY department_id,job_id;
SELECT department_id,AVG(salary) FROM employees ###This way of writing is incorrect even if it can produce data. ###It doesn't make sense that mysql does not report errors, oracle does. #In select, there are both group functions and non-group functions. #Require that non-group functions must appear in our group by
# Wrong
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id;
# Correct: Conversely, columns that appear in group by may not be declared in select.
SELECT department_id,AVG(salary) FROM employees GROUP BY department_id,job_id;
# If there are columns of group and non-group functions in select, then columns of non-group functions must be declared in group by.
# Do this set of operations
#having
# Illegal use of group functions
# Departments with a maximum wage of 10,000
SELECT department_id,MAX(salary) FROM employees #where max(salary)>10000 #Wrong. If you use group functions in filtering conditions, you need to use them. #Have replaced where GROUP BY department_id;
# Orale can write like this, but mysql can't.
SELECT department_id,MAX(salary) FROM employees HAVING MAX(salary) >10000 GROUP BY department_id;
# mysql requires that having be declared behind group by, which oracle does not require
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) >10000;
# Query 10,20,30,40,50 departments, which departments have higher maximum wages than 10,000
# Low efficiency
SELECT department_id,MAX(salary) FROM employees GROUP BY department_id HAVING MAX(salary) >10000 AND department_id IN (10 ,20 , 30 , 40 ,50); #This way of writing is correct, but it is not recommended. #If there are multiple filtering conditions, the filtering conditions of the grouping function must be written in having. #In addition, filter conditions that do not include group functions are suggested to be written in where. Because writing in where is more efficient
# sql optimization, high efficiency
SELECT department_id,MAX(salary) FROM employees WHERE department_id IN (10 ,20 , 30 , 40 ,50) #where has started filtering at the time of full-table retrieval #It is inefficient to start filtering after having full-table retrieval. GROUP BY department_id HAVING MAX(salary) >10000 ;
# Summary:
# So far, the use of keywords in select has ended.
#select... ... ... alias #from . . . ，. . . ，. . . . join . . . . . #on . . . . . . . . . . . . . #join . . . . . . . . . . . . #where Multiple Table Connection Conditions #Filtering Conditions of and Nongroup Functions #group by . . . . . . . . . #having . . . . . . . . . . #order . . . . . . . . . . . #limit... paging
_____________________________________________________________________________________
Subquery
# Subqueries are nested select ions, nested multi-tiers, and sometimes optimized.
# Whose salary is higher than Zhang San's
# Mode 1:
# Multiple queries
USE Student Information; SELECT name, salary FROM Basic Information WHERE name='Zhang San'; SELECT name, salary FROM Basic Information WHERE Wages > 1034;
# Mode 2:
# Self-connection
# Not all problems can be solved by self-connection.
SELECT j2. `Name, j2. `Wage` FROM Basic Information AS j1, Basic Information AS j2 WHERE j1. `Name `='Zhang San' AND j1. Wages < j2. Wages;
# Mode 3
# Subquery
# When java jdbc calls SERVER background database, strive to
# Simple, can do things at one time, never divided into two.
SELECT name, salary
FROM Basic Information
WHERE Wages >(
SELECT Salary # Can't Write Excess Information
FROM Basic Information
WHERE name ='Zhang San'
);
# Two or more nested queries are called sub-queries
SELECT Full name,wages
FROM Essential information
WHERE (
SELECT wages #Can't write redundant information
FROM Essential information
WHERE Full name='Zhang San'
)<wages;
#You can write that, but it looks awkward.
#Usually you put lightweight variables where
# Subquery classification
# Single-line subquery: Returns a single-line query record
SELECT Full name,wages
FROM Essential information
WHERE (
SELECT wages #Can't write redundant information
FROM Essential information
WHERE Full name='Zhang San'
)<wages;
# From inside to outside
# Multi-row sub-query: Returns multi-row query records
# Returns the same area number as Steven in the basic information table.
# Name, number, and salary of employees who earn more than Xiaoqian
# Solutions: 1. Write from the outside to the inside, 2. Write from the inside to the outside.
SELECT Name, Number, Salary
FROM Basic Information
WHERE Area Number=(
SELECT Area Number
FROM Basic Information
WHERE Name='Steven'
)
AND
Wage >(
SELECT Wages
FROM Basic Information
WHERE Name='Xiaoqian'
);
# Generally speaking, it's easier to write from inside to outside.
# Write from outside to inside, not test
# Return the name, number, and salary of the employee with the lowest salary.
SELECT Name, Number, Salary
FROM Basic Information
WHERE Wages=
(
SELECT MIN (Wages)
FROM Basic Information
);
# Return the name, number, and salary of the second lowest paid employee in the company
SELECT Name, Number, Salary
FROM Basic Information
WHERE Wages=(
SELECT MIN (Wages)
FROM Basic Information
WHERE Wages >(
SELECT MIN (Wages)
FROM Basic Information
)
);
# Have in self-query
# Query the Department id and minimum wage of the Department whose minimum wage is greater than 60
SELECT department_id ,MIN(salary)
FROM empoyees
GROUP BY department_id
HAVING MIN(salary)>(
SELECT MIN(salary)
FROM employees
WHERE department_id=60
)
AND department_id IS NOT NULL;
# Look from the inside to the outside when you look at it, and speak from the outside to the inside when you say it.
# Learn to Translate English into Chinese
# Illegal use of subqueries
#...
SELECT employee_id, last_name
FROM employees
WHERE salary = #In should be used here
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
**#Query Null Value Problem in Subqueries**
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');
#The company does not have this type of work and this person, not this person.
# Multi-line sub-queries, using the operator: in any all
SELECT employee_id, last_name
FROM employees
WHERE salary IN
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
#any
# Topic: Return to any employee whose salary is lower than job_id in other departments.
# Number, name, job_id and salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY # any
(SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
############### ## DISTINCT Weight Removal# ###############
# Topic: Return to employees in other departments whose job_id is lower than any salary in the'IT_PROG'department.
# Number, name, job_id and salary
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL # any
(SELECT DISTINCT salary
FROM employees
WHERE job_id = 'IT_PROG')
AND job_id <> 'IT_PROG';
# The hardest part is subquery
# Query the employee number, name and salary of the employee whose salary is higher than the average salary of the company.
SELECT employee_id,last_name,salary
FROM employees
WHERE salary>(
SELECT AVG(salary)
FROM employees
);
#* The average salary of each department is higher than that of its own department.
# Employee number, name and salary of highly paid employees
# Relevant queries
# Mode 1:
SELECT employee_id,last_name,salary
FROM employees e1
WHERE salary>(
#Query the average of the incoming employees in their department
SELECT AVG(salary)
FROM employees e2
WHERE e1.`department_id`=e2.`department_id`
);
# Outside, a piece of data comes in first.
# Judge inside, compare it with what you send in, and then send it out.
# Mode 2: Subqueries can not only appear in where filtering conditions
SELECT employee_id,last_name,salary #The bottom is equivalent to a new table. FROM employees e1,(SELECT department_id,AVG(salary) dept_avg_sal FROM employees GROUP BY department_id) d) WHERE e1.`department_id`=d.dept_avg_sal;
# Subqueries can be written not only in where, but also in many places.
# The group by block can't be written anywhere else.
# Ordering this block can also be written
# Subqueries can also be used in order by
# Inquire the name of the employee, Department id, and request to be sorted by department name
SELECT last_name,department_id
FROM employees e
ORDER BY (
SELECT department_name
FROM departments d
WHERE e.`department_id`=d.`department_id`
);
# Query the name and salary of the employee whose manager is King
SELECT last_name,salary
FROM employees
WHERE manage_id IN(
SELECT employee_id
FROM employees
WHERE last_name='King'
);
# Search for department information on the lowest average wage
# One-line functions can be nested, multi-line functions can not be nested, and grouping functions can not be nested.
# oracle can be nested
SELECT MIN(AVG(salarg)) FROM employees GROUP BY department_id
# mysql
SELECT *
FROM departments
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(avg_sal)
FROM(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id ) dept_avg_sal
)
);
# Search for information about the Department with the lowest average wage and the average wage in that department
SELECT d.*,(SELECT AVG(salary) FROM emeployees e WHERE e.department_id=d.`department_id`) "Average departmental wages"
FROM departments d
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING AVG(salary)=(
SELECT MIN(avg_sal)
FROM(
SELECT AVG(salary) avg_sal
FROM employees
GROUP BY department_id ) dept_avg_sal
)
);
# What is the minimum wage for the lowest part of the highest wage in each department?
# If the maximum wage of three departments is 8000,9000,10000, then the minimum wage of that department is 8000.
SELECT MIN(salary)
FROM employees
WHERE department_id=(
SELECT department_id
FROM employees
GROUP BY department_id
HAVING MAX(salary)=(
SELECT MIN(max_sal)
FROM (
SELECT MAX(salary) max_sal
FROM employees
GROUP BY department_id
) dept_max_sal
)
)
# Semi-colon problem, if all below are selected, then insert operation will be performed one by one.
# A semicolon represents a single execution statement. It doesn't matter if a single line of code is written without a semicolon.
INSERT INTO ....; INSERT INTO ....; INSERT INTO ....; INSERT INTO ....;
_____________________________________________________________________________________
Create and manage tables
# When you write a query, you'd better finish it all at once, and do it all at once.
# Queries inevitably use sub-queries
#Three main lines of sql #DML : inset,update ，delete，select #DDL : create,alter,drop,truncate,rename #dcl : commit,rollback
# Major Learning ddl
#Display all databases SHOW DATABASES; #Use the specified database USE Student Information;
# Display all tables
SHOW TABLES;
#Create tables #Way 1: Start from scratch and build it up bit by bit. #HOW TO CREATE TABLE ? #CREATE TABLE CREATE DATABASE AAA; USE AAA;
# Design each field
# Column, or field, specifies the data type of the field
# Data types are more stringent than java
# Set Length 10 Bits Max 99999999
# Character type
# Wages, total length is 10, of which 2 are decimal
# Initiation time, date type
# How to create tables in development
CREATE TABLE myemp1( emp_id INT(10) AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(15), salary DOUBLE(10,2), hire_date DATE ); SELECT * FROM myemp1 ; DESC myemp1;
################## #AUTO_INCREMENT # Self-growth, create a self-growth ################## #PRIMARY KEY # Setting the primary key ensures that the number added automatically is unique ##################
# Constraints are used when creating tables.
# Way 2: Create new tables based on existing tables, create second generation and enrich second generation.
CREATE TABLE myemp2 AS #Column aliases act as column names for tables with newly created keys SELECT employee_id emp_id ,last_name,salary #emp_id is an alias for a column, which is used as a field in a new table after the alias is created FROM employees;
# Copy three fields from the original table for myemp to use
# The type and storage range of the original table are also copied synchronously.
# Not everything will be assigned.
# Constraints can't be copied.
# While creating tables in this way, the data of the original tables can also be assigned.
SELECT * form myemp2;
# Can't overwrite, so write an individual name.
CREATE TABLE myemp3 AS SELECT employee_id emp_id ,last_name,salary FROM employees; WHERE department_id IN (50 , 60 , 70,80) AND salary > 5000;
# You can create many new tables according to your own ideas.
CREATE TABLE myemp4 AS SELECT department_id ,AVG(salary) FROM employees GROUP BY department_id;
##### Creating new tables based on multiple tables
CREATE TABLE myemp5 AS SELECT department_name ,AVG(salary) FROM employees e, departments d WHERE e.department_id = d.department_id GROUP BY department_name;
# Duplicate employees
CREATE TABLE employee_copy AS SELECT * FROM employees; ##Constraints have some problems
# Duplicate employees, but the data is empty
CREATE TABLE employee_blank AS SELECT * FROM employees; ##Constraints have some problems WHERE 1=2; #Write an invalid condition
# Creating tables involves some common data types
#varchar()
# The variable length is usually used to correct errors if the length is not written.
# Videos and pictures are stored as blog s
# There are many different blog s
# Change table
USE aaa; CREATE TABLE myemp1( emp_id INT(10) AUTO_INCREMENT PRIMARY KEY, emp_name VARCHAR(15), salary DOUBLE(10,2), hire_date DATE );
# alter table
# 1 Add a column
ALTER TABLE myemp1 ADD email VARCHAR(25); DESC myemp1; #ora varchar2 mysql varchar
# Modify existing columns
ALTER TABLE myemp1 MODIFY salary DOUBLE(15,2) DEFAULT 2000; #Default is used to set default values #Modify is used to modify table fields
# Delete existing columns
ALTER TABLE myemp1 #You can't delete, delete is used to delete data #Here drop deletion structure is used DROP COLUMN email; #colum is called column
# Rename columns
#Column data types can be changed at the same time #Unlike ora #change USE aaa; ALTER TABLE myemp1 CHANGE salary my_sal DOUBLE(10,2);
#oracle: #alter table myemp1 rename salary to mysal； #Four things to change
# Delete tables
DROP TABLE myemp1;
# rename table
ALTER TABLE emp3 RENAME TO myemp1; #oracle : rename myemp3 to emp3; SELECT * FROM myemp3; SELECT * FROM emp3;
# Last operation
# Empty table
# Clear table: Clear all the data in the table and keep the table structure.
# Once truncate table deletes data, data cannot be rolled back
# delete from: Delete operations support rollback, add, delete and delete
COMMIT; Submit data
# Any changes made before are submitted.
# All previous changes are saved.
##DCL
# Set the data to be unable to submit automatically
USE Student Information; SET autocommit=FALSE;#No data submission DELETE FROM Address table; SELECT * FROM Address table; ROLLBACK ; #Undo Data ROLLBACK ;#They will only roll back to the last commit, and they will appear in pairs. ############# COMMIT; SET autocommit=FALSE; TRUNCATE TABLE test1.`result`; ##Non-rollback data can also be understood as automatic submission of data #Once executed, it is not retrievable ROLLBACK; SELECT * FROM test1.`result`;
Roll back here. There's a pit.
# Sometimes you can't roll back
# Solutions
https://blog.csdn.net/noaman_wgs/article/details/52121991
_____________________________________________________________________________________
Addition, deletion and modification of data processing
# pl/sql special database operation language,
# Query is the most complex and important in addition, deletion and modification
# When interacting with databases, we should strive to write query commands neatly, and never do them twice.
# Relevant sub-queries are related to the inside and the outside. They are passed in from the outside, then out from the inside and used for sub-queries.
# Creating management tables is simpler. There are two ways to create tables: starting from scratch and copying.
# There are four instructions to modify the table, such as adding columns, modifying columns, deleting columns, etc. There are few operations to modify column names. Generally, the field of database tables is formed once, and the operation of changing column names is seldom done.
# Changing the storage scope from small to large will not affect the original data. Changing the storage scope from large to small will result in data loss and error reporting.
# The data type can also be modified. It already has data. It is mismatched to change data from double. It can report errors in oracle, no data in tables can be changed, and it can not report errors in mysql.
# If there is data in it, it will be changed successfully and the data will be cleared. This column is gone.
# Clear table turnccate table, not rollback
# The table is very definite. To delete it, use trunccate table
# The middle link of delete from is delete, delete, modify, uncertain.
# Additions, deletions and changes can be rolled back. Timely commit submission saves extraordinarily long rollbacks, resulting in data loss
# Function formats are important
# sql is not difficult, you need to write more
# Dozens of lines without long code
# Invest in will be painful, will work hard
# Do not write sub-queries after group by
# Addition, deletion and modification of data processing
# Add (add) - - INSERT INTO
DROP TABLE aaa.`myemp1`;
USE aaa; CREATE TABLE myemp( id INT, nam VARCHAR(10),#Variable must specify length salary DOUBLE(10,2), hire_date DATE ); SELECT * FROM aaa.myemp;
Increase in quantity
# Way one, add data one by one
# By default, you need to assign values in the order of columns in the table
INSERT INTO myemp VALUES(1,'tom1',3400,NOW());
# By default, it comes in order of columns.
INSERT INTO myemp VALUES(1,'tom1',NOW(),3400);
# It doesn't make sense to write the test like this.
# Common situations in development
# Sometimes, it's not clear which column is first followed by which one
# You can specify the order after the table. This method is often used in program development.
INSERT INTO myemp(id,nam,hire_date,salary) #The columns in the displayed listing table are typically shown in this way VALUES(1,'tom1',NOW(),3600);
# If the employee's salary hasn't been fixed, you can fill in null.
INSERT INTO myemp(id,nam,hire_date,salary) VALUES(1,'tom1',NOW(),NULL);
# There are four columns in the table and only three in the parameter table.
# Then the following specific parameters should also be written in three.
# This situation is also used more, the remaining columns automatically fill in null
INSERT INTO myemp(id,nam,hire_date) VALUES(1,'tom1',NOW());
# User information, filling in the form may be incomplete, and automatic filling in NULL that was not written at the time of saving
# No columns are declared to add data. Data defaults to nulll, which is common in development.
# Sometimes the execution is unsuccessful, involving constraints
# If salary has added non-empty constraints, then the execution will report an error.
# Some non-empty columns must be assigned values.
# The above are one by one additions
# Mode 2: Import data based on existing tables
# When importing, pay attention to whether the type and scope of columns are the same
DESC myemp;#Lead Representation First Look at Structure DESC emp; CREATE TABLE hehehe AS SELECT * FROM myemp; INSERT INTO hehehe(id,nam,salary) SELECT id,nam,salary FROM myemp; #where.....
# Delete data: delete from
DELETE FROM myemp WHERE salary IS NULL;#Delete blank lines
# Writing is usually filtered, otherwise it's completely deleted.
# delete from is a natural batch operation
# Delete. Sometimes delete fails.
# Table does not exist
# Constraints, mainly on columns
# Foreign key constraints
# Table A has a region number and Table B has a region number.
# After table AB is associated, if you write another area number to table A
# Another value, which does not exist in the area number of table b
# It will report an error and violate the foreign key constraint.
# Values of foreign key constraints cannot be deleted
# Delete parent table data, first go to the child table to see if there is any correlation.
# Deleting failed because of foreign key constraints and staff data related to Department 90 found in employees.
DELETE FROM departments WHERE department_id=90;
# Modify data update... set... where...
UPDATE myemp SET salary=12000 WHERE id=1;
# This operation is also a batch operation, if you do not specify where conditions, then all updates
UPDATE myemp SET salary=12000,id=222,hire_date='1999-09-09' WHERE id=2;
# Careful deletion of foreign key constraints
# Characteristics of deletion
# Cascade deletion, cascade empty (see courseware)
# Meaning of multi-table synchronization
# When setting foreign key constraints, add some conditions to the back...
# The operations of one or more DML s of database transactions are combined
# Call it database transaction
# Database transaction: One or more DML operations are combined to form a database transaction
# AA transfers to BB
# A synchronization of transfer, I transfer you to receive, you can not receive, rollback
#update emp set salary=salary-100 where name='AA' #update emp set salary=salary+100 where name='BB'
# Principle of Transaction Processing: Either multiple DML operations in a transaction are successfully executed, and data is submitted at this time
# Save the modified data
# Either the execution is unsuccessful, or the already performed operations are rolled back.
# CC transfers 100 to DD
# It constitutes a business, which appears as a whole, either all or none.
# DML operation, supporting data rollback operation.
# DDL operations, do not support data rollback, delete a table, modify a table, can not rollback
SET autocommit=FALSE; Set to no autocommit operation
# In development, let the autocommit variable be false first
#try{
#update emp set salary=salary-100 where name='AA'
#update emp set salary=salary+100 where name='BB'
#commit;
#} catch(){
#rollback;
#}
#This is similar in java
#commit;
commit; SET autocommit=FALSE; SELECT * FROM goods; DELETE FROM goods; ROLLBACK;
_____________________________________________________________________________________
Constraints and Paging
# constraint keyword
# To ensure data consistency and integrity, the SQL specification imposes additional constraints on table data in a constrained manner.
# Common constraints: not null, unique, primaary key, foreign key
# Checkable Constraints (check) Default Value Constraints (default)
# How to add constraints to tables: 1. Adding constraints while creating; 2. Adding and deleting constraints by modifying tables. Constraints can not be said to be modifications (deletion first and then)
# Consider at the beginning, when making a table, consider clearly, otherwise later may appear constraints can not be added, can not be deleted.
# Addition: 1 column level constraints 2 table level constraints (written at the back of the table only)
# Constraints are actually restrictions on columns of tables, additional restrictions on data in tables
# Generally speaking, it is the restriction on the DML operation of the list.
# 1. Non-null constraints
# Only column level constraints can be used
USE aaa; CREATE TABLE myem1( id INT NOT NULL , NAME VARCHAR(15) NOT NULL, salary DOUBLE (10,2) );
DESC myem1; INSERT INTO myem1(id,NAME,salary) VALUES(1,'tom',200);
# Error Field'id'not't have a default value
# Failure to add
INSERT INTO myem1(NAME,salary)
VALUES('t1',200);
INSERT INTO myem1(NAME,salary)
VALUES(200);
# The last field can be null because there is no non-null restriction
INSERT INTO myem1(id,NAME,salary) VALUES(1,'tom',NULL);
# Delete non-null constraints
ALTER TABLE myem1 MODIFY NAME VARCHAR(15) NULL; DESC myem1;
# Adding non-null constraints
# It is possible that the execution is unsuccessful. (Cause: If null values already exist in the existing salary, the addition of constraints fails.)
ALTER TABLE myem1 MODIFY salary DOUBLE(10,2) NOT NULL;
# 2. Uniqueness Constraints
# Column values are unique
USE aaa; CREATE TABLE myem2( id INT UNIQUE, #Column level constraint, which defaults to the constraint name NAME VARCHAR(15), email VARCHAR(20), hire_date DATE, #Table level constraint name CONSTRAINT emp2_id_uk UNIQUE(email), CONSTRAINT emp2_hire_date_uk UNIQUE(hire_date) );
# NULL can occur many times and is not considered to be the same data.
# null can be added multiple times to columns declared as unique
INSERT INTO myem2(id,NAME,hire_date) VALUES(3,'tom','2019-09-10');
# Repeated additions fail to add
INSERT INTO myem2(id,NAME,hire_date,email) VALUES(31,'t11om','2019-1-10','178@qq,com');
# Delete Uniqueness Constraints
# The system automatically adds indexes to columns declared as uniqueness constraints
ALTER TABLE myem2 DROP INDEX emp2_hire_date_uk;
# When the index is deleted, the corresponding uniqueness constraint is deleted.
# Adding Uniqueness Constraints
#...
# 3. Primary key constraints
# Column Requirements for Primary Key Constraints: Nonempty and Unique
# In a table, only one primary key constraint can be declared
# Using columns with primary key constraints as query conditions, you can locate the only record in the table
# Unique constraints with null values
CREATE TABLE my3( id INT PRIMARY KEY, NAME VARCHAR(15) CONSTRAINT my3_id_pk PRIMARY KEY(id) );
CREATE TABLE my3( id INT AUTO_INCREMENT,#Usually self-growing, automatic maintenance NAME VARCHAR(15), CONSTRAINT my3_id_pk PRIMARY KEY(id) );
INSERT INTO my3(NAME)#Because the primary key is set to grow by itself, you can write only one name here.
VALUES('Zhang San');
INSERT INTO my3(NAME)#Because the primary key is set to grow by itself, you can write only one name here.
VALUES('Li Si');
SELECT * FROM my3;
# 4. Foreign key constraints: same effect on columns
CREATE TABLE m1( dept_id INT, dept_name VARCHAR(10) ); INSERT INTO m1(dept_id,dept_name) VALUES(10,'it'); INSERT INTO m1(dept_id,dept_name) VALUES(20,'Account'); #You need to add primary or unique constraints to mi first ALTER TABLE m1 ADD CONSTRAINT dept_id_pk PRIMARY KEY(dept_id); DELETE FROM m2; ROLLBACK; CREATE TABLE m2( id INT, NAME VARCHAR(15), dept_id INT, #Foreign key constraints (dept_id associated with mydept requires uniqueness constraints or primary key constraints) CONSTRAINT m2_dept_id_fk FOREIGN KEY(dept_id) REFERENCES m1(dept_id)#Add cascade deletion and cascade empty constraints here #Meaning.. );
# When adding a value to dept_id in m2 table, the required value must be the value that dept_id appeared in m1 table.
# Correct
INSERT INTO m2(id,NAME,dept_id) VALUES(2,'TOM',10);
# Wrong
INSERT INTO m2(id,NAME,dept_id) VALUES(1,'TOM',40);
# 5. Check constraint check is valid for oracle and invalid for mysql
#There's this specification in sql, and it's written in mysql as it's not. CREATE TABLE m3( id INT AUTO_INCREMENT PRIMARY KEY, NAME VARCHAR(10), salary DOUBLE(10,2) CHECK(salary>3000) );
# mysql execution is still successful and oracle reports errors
INSERT INTO m3(NAME,salary)
VALUES('tom',100);#It doesn't make sense to add check, so do it.
# 6. Default constraints
CREATE TABLE m4( id INT, NAME VARCHAR(15), salary DOUBLE(10,2) DEFAULT 3000 ); INSERT INTO m4(id,NAME) VALUE(1,'tom'); SELECT * FROM m4;
# Paging
# More data to look up
SELECT * FROM goods LIMIT 0,10;#Top Ten Data Queries
# Query goods_number top ten information
SELECT * FROM goods ORDER BY goods_number DESC
# LIMIT 0,10; Query the first ten data
# The first parameter denotes how many items to start with, and the second parameter denotes how many items to display.
# In the query, limit is written after order by
LIMIT 20,3; # Query 20-23 Data
# Write a formula to display page size data on page number page
#limit (pagenumber-1)*pagesize,pagesize
# There are more constraints, the main key constraints, tabulation is the main key will be added.
(Notebook collation from network resources, intrusion and deletion)