SQL语句
文章目录
数据类型
mysql中的数据类型
类型 | 含义 |
---|---|
tinyint | 1字节,带符号的范围是-128到127。无符号的范围是0到255。bool或boolean,就是tinyint,0表示假,非0表示真 |
smallint | 2字节,带符号的范围是-32768到32767。无符号的单位是0到65535 |
int | 整型,4字节,同integer,带符号的范围是-2147483648到2147483647。无符号的范围是0到4294967295 |
bigint | 长整型,8字节,带符号的范围是-922337203685477580到9223372036854775807。无符号的范围是0到18446744073709551615 |
float | 单精度浮点数精确到大约7位小数位 |
double | 双精度浮点数精确到大约15位小数位 |
DATE | 日期。支持的范围为‘1000-01-01’到‘9999-12-31’ |
DATETIME | 支持的范围‘1000-01-01 00:00:00到‘9999-12-31 00:00:00’ |
TIMESTAMP | 时间戳。范围是’1970-01-01 00:00:00‘到2037年 |
char(M) | 固定长度,右边填充空格以达到长度要求。M为长度,范围0~255。指字符个数 |
varchar(M) | 边长字符串。M表示最大列长度。M的范围是0到65535。但不能突破行最大字节65535 |
text | 大文本。最大长度为65535(2^16-1)个字符 |
BLOB | 大字节。最大长度为65535(2^16-1)个字节的BLOB |
LENGTH函数返回字节数,而char和varchar定义的M是字符数限制。
char可以将字符串定义为固定长度,空间换时间,效率略高;carchar为变长,省了空间
关系操作
关系:在关系数据库中,关系就是二维表。
关系操作就是对表的操作。
选择(selection):又称限制,是从关系中选择出满足给定条件的元组。
投影(projection):在关系上投影,就是选择出的若干属性列(字段)组成新的关系
连接(join):将不同的两个关系连接成一个关系。
MDL——CRUD增删改查
insert语句
insert into table_name(col_name,...) values(value,...);
-- 向表中插入一行数据,自增字段,缺省字段,可为空字段可以不写
insert into tabl_name select ...;
-- 将select查询的结果插入到表中
insert intotable_name(col_name1,...)values(value1,...) on duplicate key update col_name1=value1,...;
-- 如果主键冲突,唯一键冲突就执行update后的设置。这条语句的意思就是主键不在新增记录,主键再就更新部分字段。
insert ignore into table_name(col_name,...) values(valueq,...);
-- 如果主键冲突,唯一键冲突就忽略错误,返回一个警告。
insert into reg(loginname,'name','password') values('tom','tom','tom')
insert into reg(id,loginname,'name','password') values(5,'tom','tom','tom');
insert into reg(id,loginname,'name','password')values(1,'tom','tom','tom') on duplicate key update name='john';
updata语句
update [ignore] tbl_name set col_name1=expr1 [,col_name2=expr2...][where where_definition]
-- ignore 意义同insert语句
update reg set name='zs' where id=5;
-- 注意这一句非常危险,会更新所有数据
update reg set name='ben';
-- 更新一定要加条件
update reg set name='ben',password='benpwd' where id=1;
delete语句
delete from tbl_name [where where_definition]
-- 删除符合条件的记录
delete from reg where id=1;
-- 一般不会真删除,删除一定要有条件
select语句
select
[distinct]
select_expr,...
[from table_references
[where where_definition]
[group by {col_name | expr | position}
[asc | desc],...[with rollup]]
[having where_definition]
[order by {col_name | expr | position}
[asc | desc],...]
[limit {[offset,]row_count | row_count offset offset}]
[for update | lock in share mode]]
查询
查询的结果成为结果集recordset。
select 1;
-- 最简单的查询
select * from test;
-- 字符串合并
select emp_no,first_name + last_name from employees;
select emp_no,concat(first_name,' ',last_name) from employees;
-- AS 定义别名,可也可不写用as定义。
select emp_no as `no`,concat(first_name,' ',last_name) name from employees emp;
limit子句
-- 返回5条记录
select * from employees limit 5;
-- 返回5条记录,偏移10条
select * from employees limit 5 offset 10;
select * from pmployees limit 10,5;
where子句
运算符 | 描述 |
---|---|
= | 等于 |
<> | 不等于 |
> 、<. >=、<= | 大于、小于、大于等于、小于等于 |
BETWEEN | 在某个范围之内,between a and b等价于[a, b] |
LIKE | 字符串模式匹配,%表示任意多个字符,表示一个字符 |
IN | 指定针对某个列的多个可能值 |
AND | 与 |
OR | 或 |
注意:如果很多表达式需要使用AND、OR计算逻辑表达式的值的时候,由于有结合律的问题,建议使用小括号来避免产生错误
-- 条件查询
select * from employees where emp_no < 10010 and last_name like 'P%';
select * from employees where emp_no between 10010 and 10015;
select * from employees where emp_no in (10001,10002,10003);
oeder by
对查询结果进行排序,可以升序ASC(默认)、降序DESC。
先以第一个字段排序,第一字段相同再以第二个字段排序。
-- 降序
select * from employees where emp_no in (10001,10003) order by emp_no desc;
select * from dept_emp order by emp_no,dept_no desc;
distinct
不返回重复记录
-- distinct使用
select distinct dept_no from dept_emp;
select distinct emp_no from dept_emp;
select distinct dept_no,emp_no from dept_emp;
select distinct name,age from reg 语句后面是联合起来一起用的,一般用键访问
聚合函数
函数 | 描述 |
---|---|
COUNT(expr) | 返回记录中记录的数目,如果指定列,则返回非NULL值的行数 |
COUNT(DISTINCT expr,[expr…]) | 返回不重复的非NULL值的行数 |
AVG([DISTINCT] expr) | 返回平均值,返回不同值的平均值 |
MIN(expr), MAX(expr) | 最小值,最大值 |
SUM([DISTINCT] expr) | 求和,Distinct返回不同值求和 |
-- 聚合函数
select count(*),avg(emp_no),sum(emp_no),min(emp_no),from employees;
group by分组查询
使用Group by子句,如果有条件,使用Having子句过滤分组、聚合过的结果。
-- 聚合所有
select emp_no,sum(salary),avg(salary),count(emp_no) from salaries;
-- 聚合被选择的记录
select emp_no,sum(salary),avg(salary),count(emp_no) from salaries where emp_no < 10003;
-- 分组
select emp_no from salaries group by emp_no;
select emp_no from salaries where emp_no <10003 group by emp_no;
-- 按照不同emp_no分组,每组分别聚合
select emp_no,sum(salary),avg(salary),count(emp_no) from salaries where emp_no < 10003 group by emp_no;
-- having子句对分组结果过滤
select emp_no,sum(salary),avg(salary),count(emp_no) from salaries group by emp_no having avg(salary) > 45000;
-- 使用别名
select emp_no,sum(salary),avg(salary) as sal_avg from salaries group by emp_no having sal_avg > 60000;
-- 最后对分组过滤后的结果排序
select emp_no,sum(salary),avg(salary) as sal_avg from salaries group by emp_no having sal_avg > 60000 order by sal_avg;
分组是将数据按照指定的字段分组,最终每组只能出来一条记录。 这就带来了问题,每一组谁做代表, 其实谁做代表都不合适。
如果只投影分组字段、聚合数据,不会有问题,如果投影非分组字段,显示的时候不能确定是组内谁的数据。
-- 分组
select emp_no,max(salary) from salaries; -- 10001 88958
select emp_no,min(salary) from salaries; -- 10001 40006
上例很好的说明了使用了聚合函数,虽然没有显式使用Group By语句,但是其实就是把所有记录当做一组,
每组只能出一条,那么一组也只能出一条,所以结果就一条。
但是emp_ no就是非分组字段,那么它就要开始覆盖,所以,显示为10001。当求最大值的时候,正好工资表中10001的工资最高,感觉是对的。但是,求最小工资的时候,明明最小工资是10003的40006,由于emp_ no不是分组字段,导致最后被覆盖为10001。
select emp_no, min(salary) from salaries group by emp_no;
上句才是正确的语义,按照不同员工emp_no工号分组,每一个人一组,每一个人有多个工资记录,按时每组只能按照人头出一条记录。
-- 单表较为复杂的语句
select
emp_no,
avg(salary) as avg_salary
from
salaries
where
salary > 70000
group by
emp_no
having
avg(salary) > 50000
order by
avg_salary desc
limit 1;
子查询
查询语句可以嵌套,内部查询就是子查询。
子查询必须在一组小括号中。
子查询中不能使用Order by。
-- 子查询
select * from employees where emp_no in (select emp_no from employees where emp_no > 10015) order by emp_no desc;
select emp_no, emp.first_name,gender from (select * from employees where emp_no > 10015) as emp where emp.emp.emp_no < 10019 order by emp_no desc;
join
交叉连接cross join
在MySQL中,CROSS JOIN从语法上说与INNER JOIN等同
Join会构建一张临时表
-- 工资40行
select * from salaries;
-- 20行
select * from employees;
-- join 后出现800行,笛卡尔乘积,全部交叉。
select * from employees cross join salaries;
-- 隐式连接,800行
select * from employees,salaries;
注意: salaries和employees表不应该直接做笛卡尔乘积,这样关联只是为了看的清楚
内连接
inner join,省略为join。
等值连接:只选某些field相等的元组 (行),使用On限定关联的结果。
自然连接:特殊的等值连接,会去掉重复的列,用的少。
-- 内连接,笛卡尔乘积 800行
select * from employees join salaries;
select * from employees join inner join salaries;
-- ON等值连接40行
select * from employees join salaries on employees.emp_no=salaries.emp_no;
-- 自然连接,去掉重复列,且自行使用employees.emp_no = salaries.enp_no的条件
select * from employees natural join salaries;
外连接
outer join,可以省略为join
分为左外连接,即左连接;右外连接,即右连接;全外连接
-- 左连接
select * from employees left join salaries on employees.emp_no=salaries.emp_no;
-- 右连接
select * from employees right join salaries on employees.emp_no=salaries.emp_no;
左外连接、右外连接
结果是先employees后salaries的字段显示,Right是看表的数据的方向,从salaries往employees看,
以salaries为准,它的所有数据都显示自连接表,自己和自己连接
即右连接;全外连接
-- 左连接
select * from employees left join salaries on employees.emp_no=salaries.emp_no;
-- 右连接
select * from employees right join salaries on employees.emp_no=salaries.emp_no;
左外连接、右外连接
结果是先employees后salaries的字段显示,Right是看表的数据的方向,从salaries往employees看,
以salaries为准,它的所有数据都显示自连接表,自己和自己连接