sql语句中字符串一般用 单引号。
sql语句是大小写不敏感的。
光标放在最后,shift+上、下、左、右、home、end 选中一行
ctrl+shift+r 执行选中的sql语句
查 select
SELECT * FROM t_students
有条件的查找
SELECT Name,Salary FROM t_oa;
列别名 —- as
SELECT Name as 姓名,Salary as 工资 FROM t_oa;
计算列
SELECT Name as 姓名,Salary+1000 as 工资 FROM t_oa;
where 查询
SELECT * FROM t_oa WHERE Age>25
SELECT * FROM t_oa WHERE Age>25 or Salary>5000增 insert
INSERT INTO t_students (Id,Gender,Name,Hobbies) values(4,1,’汪峰’,’游泳’)
改 update
修改一列:
UPDATE t_students SET Age=30
修改多个列:
UPDATE t_students SET Age=33,Name=’aa’
有条件的更新 —-where:
UPDATE t_students SET Age=10,Name=’huihui’ WHERE Gender=0删 delete
有条件的删除 — where
DELETE FROM t_students WHERE Age=10
删除表中全部数据
DELETE FROM t_students
delete 只是删除数据
drop 是删除表: drop table 表名SQL聚合函数。MAX、MIN、AVG、SUM、COUNT
聚合函数对一组值执行计算并返回单一的值。除了 COUNT 以外,聚合函数忽略空值。聚合函数经常与 SELECT 语句的 GROUP BY 子句一同使用。
SELECT MAX(Salary) from t_oa WHERE Age<24
别名
SELECT MAX(Salary) as 最高工资,MIN(Salary) as 最低工资 from t_oa WHERE Age>24数据排序
ORDER BY 子句位于SELECT语句的末尾。where语句不能放在order by 的后面
SELECT * FROM t_oa ORDER BY “Age列” ASC
SELECT * FROM t_oa ORDER BY “salary列” desc通配符过滤
“_” 匹配单个出现的字符,以任意字符开头
SELECT * FROM t_oa WHERE NAME LIKE ‘_ina’
SELECT * FROM t_oa WHERE NAME LIKE ‘j___’“%” 匹配任意次数(零或多个)出现的任意字符
SELECT * FROM t_oa WHERE NAME LIKE ‘o%’ //o开头的
SELECT * FROM t_oa WHERE NAME LIKE ‘%n%’ //包含n的LIKE 性能较差,很容易造成全表扫描,谨慎使用。
空值处理
null=> 表示”不知道”
SELECT * FROM t_oa WHERE NAME IS NULL //name 为空的数据
SELECT * FROM t_oa WHERE NAME IS NOT NULL //name 不为空的数据limit
limit 一定放到所有的语句后面。如果有order by,limit 要放在order by 后面
SELECT * FROM t_oa LIMIT 2,4 //从第二行开始,取4调数据group by 分组
SELECT * FROM t_oa GROUP BY age //年龄相同的分为一组
SELECT age,AVG(salary),MIN(salary)FROM t_oa GROUP BY age
SELECT age,COUNT(*) FROM t_oa GROUP BY age联合查询 : join – 表连接
最广泛的是left join (等值连接)
A表tid 与 B表的id 相关联 ,A表name 与 B表的name 相关联
省略了as
SELECT s.tid A_ID,o.id B_ID,s.name A姓名,o.name B姓名
FROM t_students s //省略了as
LEFT JOIN t_oa o //省略了as
ON s.tid=o.id