MySQL基本语法学习篇

技术2022-07-10 190

使用工具：Navicat mysql官网

一、函数、运算符、操作符

1、聚合函数

select MAX(age) maxAge from userinfo -- MAX() 求最大值 select MIN(age) minAge from userinfo -- MIN() 求最小值 SELECT AVG(age) averageAge from userinfo -- AVG() 求平均值 SELECT SUM(age) sumAge from userinfo -- SUM() 求和 select COUNT(age) FROM userinfo -- 返回统计字段非null的数据条数

2、数值函数和运算符

-- ABS() 返回绝对值 select ABS(-20) -- 20 -- CEIL() 向上取整，FLOOR()向下取整 select CEIL(2.3) -- 3 select CEIL(-2.3) -- -2 select FLOOR(2.3) -- 2 select FLOOR(-2.3) -- -3 -- ROUND(X) ， ROUND(X,D) 四舍五入，负数先将绝对值四舍五入然后加上负号 select ROUND(2.31) -- 2 没有参数D时默认四舍五入到整数位 select ROUND(-2.31) -- -2 select ROUND(-2.5) -- -3 select ROUND(2.5) -- 3 SELECT ROUND(1.2345, 3) -- 1.235 -- TRUNCATE(X,D) 返回数字X，截取到D小数位 select TRUNCATE(1.2356, 3) -- 1.235 select TRUNCATE(-0.458, 1) -- -0.4

3、日期和时间函数

select CURDATE() -- 返回当天2020-06-30 select CURRENT_DATE -- 同CURDATE() 返回当前日期2020-06-30 select CURTIME() -- 返回当前时间点22:07:06 select CURRENT_TIME -- 同CURTIME()返回当前时间点22:07:06 SELECT DAYNAME(CURDATE()) -- 返回当天是星期几 SELECT NOW() -- 返回当前时间 2020-07-02 09:50:50 SELECT DATE(NOW()) -- 提取日期或日期时间表达式的日期部分 2020-07-02 SELECT YEAR(CURRENT_DATE) -- 返回年份 2020

4、字符串操作函数

-- CONCAT() 字符串拼接，有NULL则结果为NULL SELECT CONCAT('I',' LOVE',' CHINA') -- I LOVE CHINA SELECT CONCAT('I','LOVE',NULL,'CHINA') -- NULL SELECT LENGTH('china') -- 5 -- LOCATE(substr,str) 返回子串在str中首次出现时的位置(首字母的下标位置) SELECT LOCATE('bar', 'farbartool') -- 4 -- LOCATE(substr,str,pos) 从pos位置开始查找 SELECT LOCATE('bar', 'farbartobark', 5) -- 9 -- LOWER() 转小写 SELECT LOWER('CHiNa') -- china -- UPPER() 转大写 SELECT UPPER('CHiNa') -- CHINA

5、字符串比较函数LIKE

LIKE、NOT LIKE配合通配符%( 匹配任意数量的字符，甚至零个字符)和_ （完全匹配一个字符）一般用来做模糊查询

-- 比较时不区分大小写,1表示true 0表示false,不使用%时类似于=比较 SELECT 'all' like 'ALL' -- 1 select 'all' LIKE 'ALL_' -- 0 select 'allk' LIKE 'ALL_' -- 1 select 'all' LIKE '%all%' -- 1 select 'allk' LIKE '%all_%' -- 1 select 'allk' NOT LIKE '%all_%' -- 0

6、正则表达式函数

函数匹配时不区分字母大小写,也可以使用正则表达式进行模糊查询

SELECT 'a' REGEXP '^[a-d]' -- 1 SELECT name FROM person_tbl WHERE name REGEXP '^st' -- 匹配名字以st开头的

二、SQL操作关键字

1、CREATE

创建库、表、视图、事件、索引、触发器

CREATE INDEX index_phone ON userinfo(phone) -- 创建索引

2、ALTER

更改数据库特征、存储过程、更改表及字段…

添加列

添加新列，使用ADD

AlTER TABLE userinfo ADD column salary VARCHAR(256) null

更改列

修改某一列，列名不改变，用MODIFY

AlTER TABLE userinfo MODIFY column salary int(10)

修改某一列，列名要改变，用CHANGE

AlTER TABLE userinfo CHANGE column salary userSalary INT(12)

3、DROP

所有能CREATE的都可以使用DROP删除

DROP INDEX index_phone ON userinfo

4、ORDER BY

按某一个字段升序（ASC）/降序（DESC）方式来进行排序，默认升序

SELECT * FROM userinfo ORDER BY AGE desc

5、GROUP BY

根据一个或多个列对结果集进行分组，在分组的列上我们可以使用 COUNT, SUM, AVG,等聚合函数

select age,count(age) from userinfo GROUP BY age

6、HAVING

having子句可以让我们筛选成分组后的数据，一般跟在group by之后，执行记录组选择的一部分来工作的，可以配合聚合函数使用

select sum(score) from student where sex='man' group by name having sum(score)>210;

7、DISTINCT

删除结果集中重复的数据

select DISTINCT age FROM userinfo

#####8、UNION 和 UNION ALL 用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中,UNION不包括重复数据，而UNION ALL包含所有数据注意：union前后连接的查询列数要相同，且列与列位置要对应

SELECT webName, loginTime FROM Websites UNION SELECT appName, loginTime FROM apps

9、JOIN

多表联查

INNER JOIN（内连接,或等值连接）：获取两个表中字段匹配关系的记录，取交集 LEFT JOIN（左连接）：获取左表所有记录，即使右表没有对应匹配的记录。 RIGHT JOIN（右连接）：与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录

select a.id, a.userName, b.job, b.salary FROM userinfo a inner JOIN job b ON a.id = b.id -- inner join等同于FROM userinfo a , job b 从笛卡尔积（a*b）的结果集再去筛选，效率低下 select a.id, a.userName, b.job, b.salary FROM userinfo a , job b where a.id = b.id -- 以左表为主表，显示左表中包含的所有查询字段，右表字段在左表中无对应关系的显示null select a.id, a.userName, b.job, b.salary FROM userinfo a LEFT JOIN job b ON a.id = b.id -- 与左联查相反 select a.id, a.userName, b.job, b.salary FROM userinfo a RIGHT JOIN job b ON a.id = b.id

10、IS NULL 和 IS NOT NULL

可以配合联查关键字使用，去掉为NULL的数据

-- 左联查的结果中去掉job为NULL的数据 select a.id, a.userName, b.job, b.salary FROM userinfo a LEFT JOIN job b ON a.id = b.id where job IS NOT NULL

11、LIMIT

常用于查询结果集的几行数据、分页等等

-- limit后给定1个参数表示获取前几行数据 SELECT * FROM loop_test limit 5; -- 等价于 limit 0,5 -- limit后2个参数,limit x y,表示返回从x后的y行数据,常用作分页查询 SELECT * FROM loop_test limit 5, 3; -- 返回6，7，8三行数据

12、扩展字段：Virtual Generated Column与Stored Generated Column

Mysql5.7及以上支持

Mysql 5.7 支持两种Generated Column(表示该字段依赖另一字段值)，即Virtual Generated Column和Stored Generated Column，前者只将Generated Column保存在数据字典中（表的元数据），并不会将这一列数据持久化到磁盘上；后者会将Generated Column持久化到磁盘上，而不是每次读取的时候计算所得。很明显，后者存放了可以通过已有数据计算而得的数据，需要更多的磁盘空间，与Virtual Column相比并没有优势，因此，不指定Generated Column的类型，默认是Virtual Column

ALTER TABLE t1 ADD COLUMN c2 INT GENERATED ALWAYS AS (c1 + 1) STORED

sql执行顺序

(1)from (3) join (2) on (4) where (5)group by(开始使用select中的别名，后面的语句中都可以使用) (6) avg,sum… (7)having (8) select (9) distinct (10) order by (11) limit