基础部分
SELECT语句
列名有空格的时候要加’‘,如’new price’。所以起名的时候我们尽量取new_price CONCAT 组合多个列,格式为CONCAT(xxx,' ',xxx,' ',xxx)
DESC 降序排序,用法:ORDER BY...DESC
DISTINCT 防止重复日期的格式如’1991-01-01’ LIMIT 限制返回的个数,写在最后,如LIMIT 3
SELECT a,b,c
FROM order_item oi JOIN products p ON oi.product_id=p.product_id或者
JOIN products p USING(product_id)
JOIN语句(ON/USING)
自连接 :该表的某两部分有关(上下级),我们需要将表自连接。给该表起两个别名,如FROM employees e
后自连接JOIN employees m ON e.reports_to = m.employee_id
多表连接:中间表 联系其他相关表,采用FROM 中间表
的格式,剩下的用多个JOIN即可 外连接:我们query的表经过连接后可能会损失一些元组,有时候我们又需要让某个表的所有元组都呈现出来 ,就要用到外连接。外连接分为左连接(LEFT JOIN)、右连接(RIGHT JOIN). 交叉连接 :CROSS JOIN ,结果返回两表记录的笛卡尔积 。等效于隐式链接语法:SELECT * FROM table1 table2
%% 同理,我们也有多表外连接,自外连接,格式与多表连接、外连接一样
INSERT INTO orders(customer_id,order_date,status)
VALUES(1,‘2019-01-02’,1)
INSERT INTO插入语句
格式如上,INSERT INTO table(field) VALUES(field value) 如果插入的字段属于主键(PK) ,则要么不用写这个字段,要么写了字段后面value为DEFAULT ,不要给主键加入具体的值,否则会破坏主键的稳定性 内置函数LAST_INSERT_ID()
:返回我们插入新行时MySQL生成的id %% 该内置函数常用于向多个表插入数据,比如我么在table1中插入了数据,其中PK一般是id,并且由系统默认分配值。我们又想在table2中插入数据,table2中id不是主键时我们必须为其id分配一个具体的值,这时就要使用LAST_INSERT_ID( )函数作为table2的id
查询进阶
快速创建表:CREATE TABLE...AS SELECT...FROM...
浅浅涉及到我们的子查询subqueries 用子查询插入部分行:把VALUES()换成 SELECT … ,用查询后返回的记录充当field value,或者用某个select的值作为value之一也完全OK 子查询+UPDATE
更新语句结构:UPDATE table SET field=... WHERE...
where很重要,否则所有field的值都会被修改 子查询就相当于一个具有特殊需要的值,我们既可SET field = SELECT… ,又可WHERE … IN (SELECT…) 删除记录:注意DELETE删除的是记录 ,字段目前还不会删除。格式为DELETE FROM table WHERE...
合并查询UNION
求和函数SUM( ),后面直接加字段,用于求某字段的总和。聚合函数只能用于SELECT/ORDER BY/HAVING ,不能用于WHERE 。 UNION是在SELECT之间的运算符,结构就是SELECT...UNION SELECT...UNION SELECT...
,合并所有SELECT的结果。(比如求19年上半年、下半年、全年的总XX,就用三个query,每个q查到的列名都要修改为一样的 ,这样合并的时候对应值才能在一列上。) 相关子查询
概念:外部查询的条件与内部查询有关的查询 类比:双重循环 具体问题:求工资大于本部门平均工资 的员工。在判断这个工资的条件时,我们必须要满足求的工资是员工所属部门的平均工资 ,如何找到该员工所属的部门呢?我们可以遍历整个表,找到表中部门的office_id值与外部搜索员工部门的id值相同的情况。每执行一行,我们就遍历一次链表 ,就要实现一次子查询,子查询的条件就是查询到的id=此时WHERE执行行的id,实现部门信息归属,源码如下: SELECT *
FROM employees e WHERE salary > ( //这个where就要求一行一行去判断
SELECT AVG(salary) FROM employees
WHERE office_id=e.office_id `` )//这里可以看成是两个独立的table
复杂查询
EXISTS运算符
属于运算符函数,适合大数据统计 。 用法:SELECT * FROM A WHERE EXISTS(SELECT B.id FROM B WHERE A.id=B.id)
EXISTS 是不需要字段的,它判断每一个A是否符合后面的条件,子查询没有给外查询返回一个结果 ,若内层查询结果为非空,它会返回一个指令TRUE给EXISTS运算符 ,说明子查询中有符合条件的行。EXISTS运算符接收TRUE,在最终结果里添加当前(符合条件的)记录,但子查询中是不返回结果集的 。而IN运算符 是外表内表做笛卡尔积 ,复杂度n^2 ,EXISTS 复杂度n 。IN查询到内表中所有的字段并缓存起来,再检查外表字段与内表字段是否相等 ,如果相等将外表的记录放入结果集中,直到遍历外表所有记录。相当于两层for,遍历外表for,遍历内表for,若A有1000条记录,B有100000000条记录,最坏结果就是遍历100000000000次。其效果如下:,如果相等将外表的记录放入结果集中,直到遍历外表所有记录。相当于两层for,遍历外表for,遍历内表for,若A有1000条记录,B有100000000条记录,最坏结果就是遍历100000000000次。其效果如下: EXISTS不缓存内查询的结果集 ,只返回信号值,判断是否有记录返回,属于布尔函数 。若A有1000条记录,B有1000000000条记录,EXISTS会执行1000次,返回1000个信号 。至于内查询,它不会缓存查询到的结果集,所占的空间就会小很多。其效果如下:。至于内查询,它不会缓存查询到的结果集,所占的空间就会小很多。其效果如下:本质上只遍历了一次外表 但是,IN是在内存里遍历比较 ,EXISTS需要查询数据库 ,查询数据库消耗的性能更高,内存比较速度快 所以,如果A表的记录大于B表,那么建议还是使用IN;如果B>A建议使用EXISTS,不能说EXISTS完全适用于大数据统计 SELECT嵌套,常用作把SELECT结果作为一个临时的表/列,再在这个表中做相关的操作,三重嵌套代码如下: SELECT *
FROM ( SELECT client_id,
name, (SELECT SUM(invoice_total)
FROM invoices WHERE client_id=c.client_id) AS total_sales,
(SELECT AVG(invoice_total) FROM invoices) AS average,
(SELECT total_sales-average)AS difference FROM clients c
)AS sales_summary //新的表名 `` WHERE total_sales IS NOT NULL
内置函数
数值函数
ROUND(4.5659,n) :轮函数,常用于四舍五入 ,一个参数时保留整数,两个参数时保留小数点后n位。TRUNCATE(3.52256,3) :截断函数,直接砍到目标位数,不进位CEILING(6.32) :上限函数,得到大于等于 数的最小整数,就是上确界FLOOR(6.32) :下限函数,得到小于等于数的最大整数ABS(-20) :绝对值函数RAND( ) :用于生成0-1区间的随机浮点数 字符串函数(字符都要加’')
LENGTH('sky')
,-- 返回字符串中字符个数UPPER('sky')
,-- 大写LOWER(‘SKY’) ,-- 小写LTRIM(' SKY')
,-- 左修整(left trim),删除字符串左边多余的空格,同理有右修整、修整RTRIM('SKY ‘),TRIM(’ SKY ')。LEFT('Kindergarten',4)
,-- 返回字符串左侧的前几个字符,同理有右侧RIGHT()SUBSTR('Kindergarten',3,6)
,-- 字符截取函数:得到字符串中任何位置的字符,第二个参数表示起点位置**(第一个字符的位置为1,不为0)**,第三个参数表示一共读取的字符数 ,不是终点的位置!不写的话默认全读。LOCATE('n','Kingdergarten')
,-- 返回字符或字符串位置,不区分大小写,如果没有该字符,那么会返回0,字符串形式为LOCATE(‘garten’,‘Kindergarten’)。REPLACE('Kdgarten','garten','garden')
,置换CONCAT('Kindergarten','','1')
– 串联多个字符串 日期函数
NOW() ,-- 返回当前电脑的现在日期和时间YEAR(NOW()) ,-- YEAR函数,只返回当前的年份,可以换成MONTH,DAY,HOUR,MINUTE,SECOND ,只返回整数值。
如果需要不同DBMS交互的话,建议使用EXTRACT(YEAR FROM NOW( ))
,同理MONTH等也可。 我们可以将该类函数应用到SELECT语句中,因为时间是常常改变的,我们有时候q的数据也是经常更改,所以YEAR能保证其时效性 DAYNAME(NOW()) ,-- DAYNAME函数,MONTHNAME也可以,返回的是字符串的日期类型Sunday\March这种。CURDATE() ,-- current date返回当前电脑的日期CURTIME() ,-- current time返回当前电脑的时间如16:12:00 格式化日期和时间FORMAT
DATE_FORMAT(NOW(),'%Y %M %D')
格式化日期,两个参数,前者是日期,后者是格式。也有%y(两位年份),%m(数字月份) ,%d(数字日期)TIME_FORMAT(NOW(),'%H:%i%p')
格式化时间,%i分钟,%I不知道是什么,%p增加PM/AM 修改日期和时间
DATE_ADD(NOW(),INTERVAL 1 MONTH)
增加日期,减少的话1变成-1或者函数变成DATE_SUB()
,INTERVAL()作为临时时间间隔。也可以直接NOW()-INTERVAL 1 YEAR
进行表达式的计算。DATEDIFF(NOW(),'2022-05-01 05:00')
计算日期间隔,前减后,只能返回天数,不管具体的时间 。TIMEDIFF(NOW(),'2022-06-13 05:00')
计算时间间隔,前减后,返回的是时间格式 ,并且可以与*time_to_sec()*并用返回间隔秒数。如TIME_TO_SEC(TIMEDIFF(NOW(),'2022-06-13 05:00'))
空值的判断
*IFNULL()*两个参数,如果参数一是空,就会填充参数二的内容,以后会常常用到,如SELECT IFNULL(phone,'Unknown')
用于填充NULL *COALESCE()*无数个参数,返回第一个非空值coalesce本身是结合的意思 ++到目前为止,就是MySQL的基础部分,进阶部分在另一个文档上,看到这里,祝你天天开心,难过的事不要去想,做你自己就好,像一只快快乐乐的小猪一样美美地吃饭、睡觉、生活!++