SQL语句

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为准，它的所有数据都显示自连接表,自己和自己连接