Basic query statements
--1. query all information in the table, where * represents all columns
select * from people
--2 queries people certain columns in the table
select PeopleName,PeopleSex,PeopleBirth,PeopleSalary,PeoplePhone from people
--3 queries people some columns in the table are displayed with specified names
select PeopleName as name, PeopleSex as gender, PeopleBirth as date of birth, PeopleSalary as salary, PeoplePhone as telephone number from people
--no need as it's also possible
select PeopleName name, PeopleSex gender, PeopleBirth date of birth, PeopleSalary salary, PeoplePhone telephone number from people
--4. query the city distribution of all employees and do not want duplicate data to be displayed
--using distinct keyword to agree not to display duplicate cities
select distinct(PeopleAddress) from people
--assuming preparing for a salary increase
--20% salary increase % view employee data after salary increase
select PeopleName,PeopleSex,PeopleSalary=PeopleSalary*1.2 from people
Conditional Query
--1. conditional query
--query employee information with female gender
select * from people where PeopleSex='female'
--2. search for information on all employees with salaries greater than or equal to 50000
select * from people where PeopleSalary>=50000
--when multiple conditions are used and combine conditions together
--search for information on individuals with a gender of female and a monthly salary greater than 50000
select * from people where PeopleSex='female' and PeopleSalary>=50000
--when the query conditions are complex, they can be enclosed in parentheses
--search for girls with a monthly salary greater than 50000 or 20000
--select * from People where condition 1 or condition 2
select * from People where PeopleSalary>=50000 or (PeopleSex='female' and PeopleSalary>20000)
--5. query personnel information with salaries between 20000 and 50000
select * from people where PeopleSalary>=20000 and PeopleSalary<=50000
--ranges like this can also be used between....and... go check, the effect is the same
select * from People where PeopleSalary between 20000 and 50000
Query empty content
--1. employees whose search address has not been filled in
--when the content of the query field is empty, it cannot be used = to determine if the content is empty
--specially used is
select * from people where PeopleAddress is NULL
--query employee information with non empty address
select * from people where PeopleAddress is not NULL
--NULL it is different from an empty string. simply not inserting the column during insertion is considered a null value. if the column is written during insertion, the inserted data will provide a null value''
--this is equivalent to giving an empty string, so when querying, the empty string should be queried using equal to, as shown below
select * from poeple where PeopleAddress =''
Subquery
--1. search for information on employees with higher salaries than xiao liu
--first, search for the salary column in xiao liu's row. make sure to compare only one data, otherwise it will not be allowed
--when there is an error stating that the subquery has more than one value, it is necessary to check if the constraint conditions are not sufficient
--using > when comparing, ensure that the result of the subquery is a comparable value
select * from people where PeopleSalary >
(select PeopleSalary from people where PeopleName='xiao liu' and RankID=1)
--2. search for information about people in the same city as xiao chen
select * from people where PeopleAddress =
(select PeopleAddress from people where PeopleName='xiao chen')
Fuzzy Query
--fuzzy query
--using like search for keywords and some wildcards
--% represents matching 0 characters, 1 character, or multiple characters
--- represents matching with only one character
--[] within the matching range
--[^] representative is not within the scope
--1. search for employee information named liu
select * from people where PeopleName like '% liu'
--2 queries people the chinese name contains white, but i don't know which character white is in the name
select * from people where PeopleName like '% white %'
--3. find out that the name contains ’ white'perhaps ’ bye'employee information for
select * from people where PeopleName like '% white %' or PeopleName like '% bye %'
--4. it was found that the employee's name is liu and their name is two characters. we don't care what their surname is
select * from people where PeopleName like '_liu'