目录
SQL语句(结构化查询语言)
增删改
添加 insert into 表名(字段名) values(值)
修改update 表名 set 字段1 = 值1,.............where 条件
删除delete from 表名 where 条件
运算符 = != <> < <= > >=
关键字 between...and... in(set) is nul and or not
SQL语句里写运算符会降低执行效率
--新增语句
INSERT into userinfo(id,username,password,phonenumber,age) VALUES(2,'lisi','123456',12456753456,16)
--给指定字段添加数据
insert into userinfo(id,username,password,age) values(0,'王五','654321',14)
--给全部字段添加数据
insert into userinfo values(0,'张三','9876',25687645367,90)
--给指定字段批量添加数据
insert into userinfo(id,username,password,age) values(0,'李四','7890',3),(0,'王五','6789',3),(0,'周六','7654',6)
--给全部字段批量添加数据
insert into userinfo values(0,'李四2','7890',12456753456,3),(0,'张三2','6789',12456753456,3),(0,'张三2','7654',12456753456,6)
--修改语句
update userinfo set username='老8',password='111'
--修改指定用户的密码
update userinfo set password='666' where username = '老6'
--删除语句
delete from userinfo where id = 10
delete from userinfo where id < 10
-- BETWEEN 11 AND 13在什么和什么之间
update userinfo set age = 30 where id BETWEEN 11 AND 13
-- IN()给括号内指定的值,可以多个
update userinfo set age = 900 where id IN(11,15,13)
-- AND与
update userinfo set age = 75 where password = '9876' AND age = 90
查
基本查询
select * from 表名 *通配符
模糊查询(搜索) 查询效率低 关键字like %占位符 %能占多个字符 _代表匹配一个字符
使用左模糊会导致索引失效,查询效率降低
分页查询
语法 limit 起始索引个数
公式 第m页,每页展示n条数据 limit(m-1)*n,n
排序 order by
ASC升序、DESC降序
聚合函数
count( )总数 sum( ) 总和 avg( )平均值 max( )最大值 min( )最小值
SQL语句里涉及到运算,运算效率都会降低。
分组查询
group by
having条件查询 where 使用在分组之前,having使用在分组之后
子查询(嵌套查询)
把查询到的结果当作另一个查询的条件
内连接
笛卡尔积--查询出的结果没意义
显示内连接inner join inner可以隐藏
隐式内连接 from后面跟两个表名
外连接
左外连接 left [outer] join on
右外连接 right [outer] join on
左外连接:主表放左,从表放右
右外连接:主表放右,从表放左
-- 查询语句
SELECT id,username,password,phonenumber,age FROM userinfo
SELECT * FROM userinfo,dept
-- 基础条件查询语句
SELECT id,username,password,age FROM userinfo where id BETWEEN 11 and 15
SELECT id,username,password,age from userinfo where id in(11,14,16)
SELECT id,username,password,age from userinfo where id < 15 AND age < 50
SELECT * FROM userinfo where id > 15 or age > 1000
-- 模糊查询(搜索) like %占位符
-- 使用左模糊会导致索引失效,查询效率降低
SELECT id,username,password FROM userinfo where username LIKE "张%"
SELECT id,username,password FROM userinfo where username LIKE "%三"
SELECT id,username,password FROM userinfo where username LIKE "%牛%"
SELECT id,username,password FROM userinfo where username LIKE "__三"
-- 分页查询 limit
SELECT * FROM userinfo limit 0,5
SELECT * FROM userinfo limit 5,5
SELECT * FROM userinfo limit 10, 5
SELECT * FROM userinfo limit 15,5
-- 排序 ORDER BY ASC升序 DESC降序
SELECT * FROM userinfo ORDER BY age ASC
SELECT * FROM userinfo ORDER BY age DESC
-- 聚合函数
-- count 查询有多少条数据
SELECT count(0) from userinfo
SELECT count(phonenumber) from userinfo
-- sum 总和
SELECT sum(age) from userinfo
-- avg 平均年龄
SELECT avg(age) from userinfo
-- min最小值 max最大值
SELECT id,username,password,phonenumber,min(age) from userinfo
SELECT id,username,password,phonenumber,max(age) from userinfo
-- 分组查询 group by
SELECT sex,count(0),avg(age) from userinfo GROUP BY sex
SELECT sex,count(0),avg(age) FROM userinfo where age > 8 GROUP BY sex HAVING avg(age) > 20
-- 子查询 把一个查询结果作为另一个查询的条件 嵌套查询
SELECT id, username, password from userinfo where id in(SELECT id from userinfo where age = 75)
-- 内连接
SELECT * FROM userinfo INNER JOIN dept where userinfo.did = dept.id
-- 显示内连接
SELECT u.id,u.username,u.password,u.phonenumber,u.age,d.deptname FROM userinfo u INNER JOIN dept d where u.did = d.id
SELECT u.id,u.username,u.password,u.phonenumber,u.age,d.deptname FROM userinfo u JOIN dept d where u.did = d.id
-- 隐式内连接
SELECT u.id,u.username,u.password,u.phonenumber,u.age,d.deptname FROM userinfo u,dept d where u.did = d.id
-- 左外连接
SELECT u.id,u.username,u.password,u.phonenumber,u.age,d.deptname FROM userinfo u LEFT JOIN dept d on u.did = d.id
-- 右外连接
SELECT u.id,u.username,u.password,u.phonenumber,u.age,d.deptname FROM dept d right join userinfo u on u.did=d.id
SELECT d.deptname,u.id,u.username,u.password,u.phonenumber,u.age FROM userinfo u right join dept d on u.did=d.id