1. Query
1.select * from user #Check all fields 2.select name from user # Query specified fields
2. Query and de duplication
#Query name and remove duplicate select distinct name from user
3. Query with conditions
#Query the information named Tom select * from user where name="Tom"
4. Multi criteria query
#The query name is Tom and the age is over 18 select * from user where name="Tom" and age>18 # and meets two conditions # or meet any condition
5. Query and sort
select * from user order by age # desc is arranged in descending order from large to small # asc is arranged in ascending order from small to large
6. Add data
insert into user (name,age,city) value("Jok",23,"Shanghai")
7. Modify data
#Note that update without the where constraint will modify all data in the table by default #Change Jok's age to 20 update user set age=20 where name="Jok"
8. Delete data
#Note that deleting without the where constraint will delete all data in the table by default
delete from user where name="Jok"
9. Return the specified number of pieces of data
#Return 5 pieces of data select * from user limit 5 #Get the first 5 data select * from user order by age desc limit 5
10. Fuzzy query
#Names ending with Lee select * from user where name like "%Lee" #Names starting with Li select * from user where name like "Lee%" #Include Lee's name select * from user where name like "%Lee%" #A two character name surnamed Li select * from user where name like "Lee_" #A three character name surnamed Li select * from user where name like "Lee__" #A three word name ending with Zhang select * from user where name like "__Zhang"
11.where specifies multiple value queries
#Query name Tom and Jok information
select * from user where name in ("Tom","Jok")
12. Select the value in the data range between the two values
#Return people greater than 18 and less than 25 select * from user where age between 18 and 25
13. Query and set alias
#Name the table name user as user select name as "full name" from user as "user" select name "full name" from user "user"
14. Association query
#Query data shared by two tables select a.name,b.age from user a ,user2 b where a.name=b.name #Returns a value when at least one of the two tables matches select a.name,b,age from user inner join user2 on a.name=b.name #The left query focuses on the left table and matches the right table. If there is corresponding data in the right table, the corresponding value is returned. If there is no corresponding data, Null is returned select a.name,b,age from user left join user2 on a.name=b.name #The right query is mainly based on the right table and matches the left table. If there is corresponding data in the left table, the corresponding value is returned, and if there is no corresponding data, Null is returned select a.name,b,age from user left join user2 on a.name=b.name #Full connection if the left and right tables exist in any data, the row is returned select a.name,b,age from user full outer join user2 on a.name=b.name
15. Merge two tables UNION
#Each SELECT statement inside the UNION #You must have the same number of columns. #Columns must also have similar data types. #At the same time, the order of columns in each SELECT statement must be the same # union data shared by two tables cannot have duplicate values select name,age,city from user union select name,age,city from user2 # union all data shared by two tables can have duplicate values select name,age,city from user union all select name,age,city from user2
16. Copy data
select into from requires that the target table does not exist, because it will be created automatically during insertion;
insert into select from requires the target table to exist.
#Copy all data select * into user from user2 #Copy specified field select name,age into user from user2 #Copy only eligible data select * into user from user2 where age>20 #Copy all columns in one table and insert them into another existing table insert into user2 select * from user #Copy only the columns you want to insert into another existing table insert into user2(name,age,city) select name,age,city from user
2, Database Constraints
17. Create database / table
Create database create database my_data Create data table creat table my_table ( name varchar(100), age int(10), city varchar(100) );
2. Create a data table with constraints
create table my_table ( id int(25) not null, name varchar(100) unique, age int(10), city varchar(100) ); not null - Indicates that a column cannot be stored null Value. unique - Ensure that each row of a column must have a unique value. primary key - not null and unique The combination of. Ensuring that a column (or a combination of two columns and multiple columns) has a unique identification helps to find a specific record in the table more easily and quickly. foreign key- Ensure referential integrity of data in one table matching values in another table. check - Ensure that the values in the column meet the specified criteria. default- Specifies the default value when no value is assigned to the column.
3. Add a non empty constraint
#When creating a table, add create table my_table ( id int(25) not null, name varchar(100) not null, age int(10), city varchar(100) ); #Add for existing fields alter table my_table modify age int not null #Delete constraint alter table my_table modify age int null
4. Add unique constraint
#Add non empty and unique constraints to names create table my_table ( id int(25) not null, name varchar(100) not null unique, age int(10), city varchar(100) ); #Add a unique constraint to an existing field ALTER TABLE Persons ADD UNIQUE (P_Id) #Add unique constraints to multiple fields ALTER TABLE Persons ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName) #Delete unique constraint ALTER TABLE Persons DROP INDEX uc_PersonID
5. PRIMARY KEY constraint
#Create a table with a primary key constraint create table my_table ( id int(25) not null, name varchar(100) not null unique, age int(10), city varchar(100), primary key (p_id) ); #Add primary key constraint alter table user add primary key (p_id) Delete primary key constraint alter table user drop primary key
6. foreign key constraint
#Create a table with foreign key constraints create table order ( o_id int not null, orderNo int not null, p_id int, primary key (o_id), foreign (p_id) references user(p_id) ) #Add foreign key constraint alter table order add foreign key (p_id) references user(p_id) #Delete foreign key constraint alter table order drop foreign key fk_porder(Foreign key name)
7. The check constraint is used to limit the range of values in a column
#Create check constraint create table order ( o_id int not null, orderNo int not null, check (o_id>0) ) #Add check constraint alter table user add check(p_id>0) #Delete check constraint alter table user drop check chk_user
8. DEFAULT constraint the DEFAULT constraint is used to insert DEFAULT values into columns
#Create default constraint create table order ( o_id int not null, orderNo int not null, city varchar(255) default "Beijing" ) #Add default constraint alter table user alter city set default "Shanghai" #Delete default constraint alter table user alter city drop default 5
9. Create index create index creates an index in the table.
#Create a single index create index u_index on user (u_id) #Create multiple indexes create index u_index on user (u_id,u_name)
10. Index revocation
alter table user drop index u_id
11. Delete database / table
#Delete database drop database user #Delete data table dorp table user #Delete data in table truncate table user
12.alter table add, delete, or modify columns in an existing table
#Add column alter table user add role varchar(60) #Delete column alter table user drop column role #Modify the data type of the column alter table user alter column role char(50)
13. Set automatic field growth
create table user ( id int not null auto increment, name varchar(240) not null, city varchar(255) )
14.sql view
#Simple view create view user_order as (select name,city from user where age>18) #Complex view create view user_order as (sleect a.name,a.city,b.orderNum,b.orderPrice from user a,order b where a.u_id=b.u_id) #update the view create or replace view user_order as ( sleect a.name,a.city,b.orderNum,b.orderPrice from user a,order b where a.u_id=b.u_id ) #Delete view drop view user_order
15. Date function
MySQL Date function NOW() Returns the current date and time CURDATE() Returns the current date CURTIME() Returns the current time DATE() Extraction date or date/Date part of time expression EXTRACT() Return date/Separate parts of time DATE_ADD() Adds the specified time interval to the date DATE_SUB() Subtracts the specified time interval from the date DATEDIFF() Returns the number of days between two dates DATE_FORMAT() Display dates in different formats/time
16.NULL value
Cannot compare NULL and 0; They are not equivalent
If a column in the table is optional, we can insert new records or update existing records without adding values to the column. This means that the field will be saved with a NULL value.
#Query null value
select * from user where city is null
#Mysql
select * from user where isnull(city)
#Query non null value
select * from user where city is not null
#MySql
select * from user where not isnull(city)
SQL function
1.avg() function average
#Average age select avg(age) from user
2.count() function
#Count how many users there are select count(*) from user #Statistics and de duplication select count(distinct name) from user
3.first() function
FIRST() Function returns the value of the first record in the specified column select first(name) from user #MySql select * from user limit 1;
4.last() function
FIRST() Function returns the value of the first record in the specified column select last(name) from user order by name limit 1 #MySql #Returns the last piece of data select * from user limit 1;
5.max() function maximum
#Return the oldest select max(age) from user
6.min() function min
#Return the youngest select min(age) from user
7.sum() function
#Sum of ages select sum(age) from user
8.group by group
#See which cities the user is in select * from user group by city
9.having screening
The reason for adding the HAVING clause in SQL is that the WHERE keyword cannot be used with aggregate functions
select count(b.o_id),a.name from user group by city having city!="Shenzhen";
10. The exists operator determines whether a record exists
#Beijing users select * from user where exists(select city from user where city ="Beijing")；
11. Field case (uppercase ucase | lowercase lcase)
#Capitalize select ucase(name) from user #a lowercase letter select lcase(name) from user
12.mid() function takes value from text (similar to string interception)
select mid(addres,1,3) from user
13.len() function gets the length of the string
#Gets the length of the name select name,len(name) from user
14. The round() function rounds the numeric field to the specified number of decimal places
#Get the total price of goods (two decimal places) select order_name,price,round(count_price,2) from user # select round(2.626,2)
15. The now() function returns the current system time
select * from user where insert_time=now()
16.format() function formats the string
select name,date_format(now(),"%Y-%m-%d") as date from user