Create databases, tables

<span style="font-size:14px;">create database hncu character set utf8; </span>

Create a database named hncu coded utf-8.

<span style="font-size:14px;">use hncu; </span>

<span style="font-size:14px;"> create table stud(
 sno varchar(15) not null primary key,
 sname varchar(15) not null,
 age int,
 saddress varchar(15)
 );</span>

<span style="font-size:14px;">insert into stud values('1001','Jack',20,'New York');
insert into stud values('1002','Tom',30,'New York');
insert into stud values('1003','Zhang San',24,'Yiyang');
insert into stud values('1004','Zhang Si',15,'Changsha, Hunan');
insert into stud values('1005','Li Si',22,'Yiyang');
insert into stud values('1006','Zhang Sanfeng',80,'Knight errant');
insert into stud values('1007','Guoxiang',75,'Knight errant');
insert into stud values('1008','Extinction of Shitai',10,'Knight errant');</span>

View the data from the stud table:

<span style="font-size:14px;">select * from stud;</span>

Aliases the column names to show:

<span style="font-size:14px;">select SnO as number, sname as name, age as age, saddress as address from stud;</span>

select complex queries:

Query stud table with age greater than or equal to 24:

<span style="font-size:14px;">select * from stud where age>=24;</span>

Query stud table for data with age greater than or equal to 20 and rain equal to 30:

<span style="font-size:14px;">select * from stud where age>=20 and age <=30;</span>

There is another way:

<span style="font-size:14px;">select * from stud where age between 20 and 30;</span>

Query data in stud tables aged 20 or 30:

select * from stud where age=20 or 30;

Another way is to use in();

Query data in stud tables aged 20, 22 and 30:

<span style="font-size:14px;">select * from stud where age in(20,22,30);</span>

There is a relative to in: not in

<span style="font-size:14px;">select * from stud where age not in(20,22,30);</span>

Fuzzy query LIKE'%'matches all'' matching single characters - must be used together with LIKE:

That is to say, wildcards can only be used with like, if they are used with = then they are just ordinary characters.

The query name is open at the beginning:

select * from stud where sname like'Zhang%';

The query name opens, and the name has only two characters:

<span style="font-size:14px;">select * from stud where sname like 'Zhang_';</span>

The query name opens, and the name has only three characters:

<span style="font-size:14px;">select * from stud where sname like 'Zhang__';</span>

The query name has three:

<span style="font-size:14px;">select * fom stud where sname like '%three%';</span>

Query names with three and older than 30:

<span style="font-size:14px;">select * from stud where sname like '%three%' and age >30;</span>

Add a column to the table:

<span style="font-size:14px;">alter table stud add column sex char(1);</span>

Eliminating column can also be added

<span style="font-size:14px;">alter table stud add sex char(1);</span>

Delete the sex column from the stud table

<span style="font-size:14px;">alter table stud drop sex;</span>

It can also be used:

<span style="font-size:14px;">alter table stud drop column sex;</span>

When judging NULL value, we can not use'='to judge, but is:

Insert a row of data and make his age null.

<span style="font-size:14px;">update stud set age=20 where age=null;</span>

The following sentence should be used:

<span style="font-size:14px;">elect stud set age=20 where age is null;</span>

If you decide which character is empty, you can directly use =''to judge.

Example:

<span style="font-size:14px;">select * from stud where saddress='';</span>

The function is: if saddress is empty in the stud table (note! If it is empty, not null, the query will be displayed.

Change saddress to New York to Silicon Valley

<span style="font-size:14px;">update stud set saddress='silicon valley' where saddress='New York'; </span>

Note: No: you can't write update table stud set here.

Modify the values of multiple fields at the same time:

<span style="font-size:14px;">update stud set sname='ROSE', saddress='Beijing' where sno='1002';</span>

Deleting the name is Wukong's line:

<span style="font-size:14px;">delete from stud where sname='name of a fictitious monkey with supernatural powers';</span>

Knowledge Points:

select field from table name where condition and condition or condition

update tableName set values where conditions need to be set

delete from tableName where condition

Create View: cerate view view view name as select clause

(virtual table) - - only in memory

create view aview as select * from stud where age>20;

Query sname,age,ano under 40 from view aview:

<span style="font-size:14px;">select sname,sno,age from aview where age<40;</span>

Aggregation function:

Number of rows for non-null data: (* number and 1 represent non-null rows as long as a row in a table has non-null column data)

Generally, it should be used for individual purposes: as alias

<span style="font-size:14px;">select count(*) from stud;
select count(1) from stud;</span>

Number of rows whose age is not empty:

That is to say, age is null and will not be counted.

<span style="font-size:14px;">select count(age) from stud;</span>

Displays the average value of all ages in the stud table:

<span style="font-size:14px;">select avg(age) as averageAge from stud;</span>

Displays rounding of all age averages.

<span style="font-size:14px;">select round(avg(age)) as averageAge2 from stud;</span>

Also:

Sum summation.
Max gets the maximum.
Min gets the minimum.

<span style="font-size:14px;">select  sum(age)  as sunAge from stud;
select max(age) as maxAge from stud;
select min(age) as minAge from stud;</span>

Choose the name and age of the youngest person:

<span style="font-size:14px;">select sname , age from stud where age = ( selectt min(age) from stud );</span>

<span style="font-size:14px;">select sname ,age from stud where age in(select min(age) from stud);</span>

Create another line with an age of 10:

<span style="font-size:14px;">insert into stud value('1009','Li Bai',10,'Hunan');</span>

<span style="font-size:14px;">select age from stud where age=(select min(age) from stud);</span>

We can see that because the age of the two data is the minimum, all of them show two rows, but they are repetitive, and there is no need to display two rows at all.

At this point we will use: distinct, the same lines, merge display!

<span style="font-size:14px;">select distinct age from stud where age = (select min(age) from stud);</span>

Sort-ascend and descend:

In ascending order of age:

<span style="font-size:14px;">select * from stud order by age asc;</span>

In descending order of age:

<span style="font-size:14px;">select sno,sname,age from stud order by age desc;</span>

Existence Judgment of exists

<span style="font-size:14px;">select sname,age from stud where exists (select * from stud where age = 20);</span>

exists (select * from stud where age=20) -- Returns true as long as age=20 exists.

That is to say, exists(...) is to determine whether the expression in parentheses is null, if null, it returns false, otherwise it returns true;

Because stud has a row with age=20, it will output all sname, age.

Grouping

<span style="font-size:14px;">select saddress , avg(age) as Average age from stud group by saddress;</span>

According to saddress, the average age of each group was calculated.

As long as saddress is different, it's a different group!

According to saddress grouping, the total age of each group was:

select saddress,sum(age) as age sum from stud group by saddress;

There are two fixed collocations:

Sort:

select ... from ... where ... order by ...  

Grouping:

Select... from... group by... by... have... (conditional judgment is after having, not where)

Here sum(age) can also be used as an alias, which can be used directly in judgment.

String Processing Function

<pre-name="code" class="sql">span style="font-size:14px;">Length(str) - Find string length
 Ltrim(str) - Remove the space on the left
 Rtrim(str) - Remove the space on the right
 trim(str) - Remove the spaces on both sides
 Left(str,n); - Take n characters from the left
 Right(str,n); - Take n characters from the right
 Substring(str,begin,end) - Returns substrings
 Reverse(str) - Returns an inverted string
 Lower(str) - Convert to lowercase
 Upper(str) - To capitalize
 Concat(Str,str... Series strings.
Instr(str,s) - Returns the position where s appears in STR and 0 if not</span>

Demonstrate left();

Show that saddress starts with two characters as lines in Hunan

<span style="font-size:14px;">select * from stud where left(saddress,2)='Hunan';</span>

Series string:

<span style="font-size:14px;">select concat(snon,sname,saddress) as Series strings from stud;</span>

instr(str,s) returns the position where s appears in str, and 0 if not

It actually returns the location of the string from the first occurrence (counting from the beginning)

select sname,instr(sname,'three') as ind from stud;'