Shulou(Shulou.com)05/31 Report--

This article mainly introduces the use of high-level SQL statements in the MySQL database examples, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

I. preparatory work

1. Install MySQL database

One-click deployment of Shell script-source code compilation and installation of MySQL

2. Experiment preparation, data sheet configuration

Mysql-uroot-pshow databases;create database train_ticket;use train_ticket;create table REGION (region varchar (10), site varchar (20)); create table FARE (site varchar (20), money int (10), date varchar (15)); desc REGION;desc FARE;insert into REGION values ('south','changsha'); insert into REGION values (' south','nanchang'); insert into REGION values ('north','beijing'); insert into REGION values (' north','tianjin') Insert into FARE values ('changsha',1000,'2021-01-30'); insert into FARE values ('nanchang',700,'2021-01-30'); insert into FARE values ('beijing',1500,'2021-01-30'); insert into FARE values ('tianjin',1200,'2021-01-30'); insert into FARE values ('beijing',2200,'2021-02-05'); select * from REGION;select * from FARE

II. MySQL Advanced (Advanced) SQL statement

1 、 SELECT

Display all data for one or more fields in the table

Syntax: SELECT field FROM table name

Select region from REGION

2 、 DISTINCT

Do not display duplicate data (deduplicated)

Syntax: SELECT DISTINCT field FROM table name

Select distinct region from REGION

3 、 WHERE

Conditional query

Syntax: SELECT field FROM table name WHERE condition

Select site from FARE where money > 1000X select site from FARE where money

< 1000;select site from FARE where money = 1000; 4、AND、OR and（并且）、or（或者） 语法：SELECT 字段 FROM 表名 WHERE 条件1 ([AND|OR] 条件2)+； select site from FARE where money >

1000 and (money

< 1500);select site,money from FARE where money < 500 or (money < 1500 and money >

Select site,money,date from FARE where money > = 500and (date)

< '2021-02-05' and money < 1000); 5、IN 显示已知的值的资料 语法：SELECT 字段 FROM 表名 WHERE 字段 IN ('值1','值2',……); select site,money from FARE where money in (700,1000); 6、BETWEEN 显示两个值范围内的资料 语法：SELECT 字段 FROM 表名 WHERE 字段 BETWEEN '值一' and '值二'; select * from FARE where money between 500 and 1000; 7、通配符、LIKE 通常通配符都是跟LIKE一起使用 %：百分号表示零个、一个或多个字符 _：下划线表示单个字符 LIKE：用于匹配模式来查找资料 语法：SELECT 字段 FROM 表名 WHERE 字段 LIKE '模式'; select * from FARE where site like 'be%';select site,money from FARE where site like '%jin_'; 8、ORDER BY 按关键字排序 语法：SELECT 字段 FROM 表名 [WHERE 条件] ORDER BY 字段 [ASC,DESC]; #ASC：按照升序进行排序，默认的排序方式 #DESC：按照降序进行排序 select * from FARE order by money desc;select date,money from FARE order by money desc; 函数 1、数学函数 abs(x)返回 x 的绝对值rand()返回 0 到 1 的随机数mod(x,y)返回 x 除以 y 以后的余数power(x,y)返回 x 的 y 次方round(x)返回离 x 最近的整数round(x,y)保留 x 的 y 位小数四舍五入后的值sqrt(x)返回 x 的平方根truncate(x,y)返回数字 x 截断为 y 位小数的值ceil(x)返回大于或等于 x 的最小整数floor(x)返回小于或等于 x 的最大整数greatest(x1,x2…)返回集合中最大的值least(x1,x2…)返回集合中最小的值select abs(-1),rand(),mod(5,3),power(2,3),round (1.579),round(1.734,2); select sqrt(9),truncate(1.234,2),ceil(1.2),floor(1.9),greatest(1,2,3,4),least(1,2,3,4); 2、聚合函数 avg()返回指定列的平均值count()返回指定列中非 NULL 值的个数min()返回指定列的最小值max()返回指定列的最大值sum(x)返回指定列的所有值之和select avg(money) from FARE;select count(money) from FARE;select min(money) from FARE;select max(money) from FARE;select sum(money) from FARE; #count(*)包括所有列的行数，在统计结果时，不好忽略值为null #count(字段)只包括那一行的列数，在统计结果的时候，会忽略列值为null的值 3、字符串函数 trim()返回去除指定格式的值concat(x,y)将提供的参数 x 和 y 拼接成一个字符串substr(x,y)获取从字符串 x 中的第 y 个位置开始的字符串，跟substring()函数作用相同substr(x,y,z)获取从字符串 x 中的第 y 个位置开始长度为 z 的字符串length(x)返回字符串 x 的长度replace(x,y,z)将字符串 z 替代字符串 x 中的字符串 yupper(x)将字符串 x 的所有字母变成大写字母lower(x)将字符串 x 的所有字母变成小写字母left(x,y)返回字符串 x 的前 y 个字符right(x,y)返回字符串 x 的后 y 个字符repeat(x,y)将字符串 x 重复 y 次space(x)返回 x 个空格strcmp(x,y)比较 x 和 y，返回的值可以为-1,0,1reverse(x)将字符串 x 反转 SELECT TRIM ([ [位置] [要移除的字符串] FROM ] 字符串); #[位置]：的值可以为 LEADING (起头), TRAILING (结尾), BOTH (起头及结尾)。 #[要移除的字符串]：从字串的起头、结尾，或起头及结尾移除的字符串。缺省时为空格。 select trim(leading 'na' from 'nanchang');select trim(trailing '--' from 'nanchang--');select trim(both '--' from '--nanchang--');select concat(region,site) from REGION where region = 'south';select concat(region,' ',site) from REGION where region = 'south';select substr(money,1,2) from FARE;select length(site) from FARE;select replace(site,'ji','--') from FARE;select upper(site) from FARE;select lower('HAHAHA');select left(site,2) from FARE;select right(site,3) from FARE;select repeat(site,2) from FARE;select space(2); select strcmp(100,200);select reverse(site) from FARE; 4、| | 连接符 如果sql_mode开启开启了PIPES_AS_CONCAT，"||"视为字符串的连接操作符而非或运算符，和字符串的拼接函数Concat相类似，这和Oracle数据库使用方法一样的 mysql -uroot -puse train_ticket;select region || ' ' || site from REGION where region = 'north';select site || ' ' || money || ' ' || date from FARE; 5、GROUP BY BY后面的栏位的查询结果进行汇总分组，通常是结合聚合函数一起使用的 GROUP BY 有一个原则，就是 SELECT 后面的所有列中，没有使用聚合函数的列，必须出现在GROUP BY后面。 语法：SELECT 字段1,SUM(字段2) FROM 表名 GROUP BY 字段1; select site,sum(money) from FARE group by site;select site,sum(money),date from FARE group by site order by money desc;select site,count(money),sum(money),date from FARE group by site order by money desc; 6、HAVING 用来过滤由GROUP BY语句返回的记录集，通常与GROUP BY语句联合使用。 HAVING语句的存在弥补了WHERE关键字不能与聚合函数联合使用的不足。如果被SELECT的只有函数栏，那就不需要GROUP BY子句。 语法：SELECT 字段1,SUM(字段2) FROM 表名 GROUP BY 字段1 HAVING(函数条件); select site,count(money),sum(money),date from FARE group by site having sum(money) >

= 700

7. Alias

Field aliases, table aliases

Syntax: SELECT "table alias". Field 1 "[AS]" field 1 alias "FROM" table name "[AS]" table alias "

Select RE.region AS reg, count (site) from REGION AS RE group by reg;select FA.site AS si,sum (money), count (money), date AS da from FARE AS FA group by si

8. Subquery

Join the table and insert another SQL statement in the WHERE clause or HAVING clause

Syntax: SELECT field 1 FROM form 1 WHERE field 2 [comparison operator] (SELECT field 1 FROM form 2 WHERE condition)

Can be the operator of a symbol

Example: =, >, =

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.