SQL高级查询
1 总结
3 select ... 聚合函数 from 表名
1 where ...
2 group by ... 4 having ...
5 order by ...
6 limit ...
2 order by... 给查询结果排序
1 order by 字段名 ASC(默认升序) / DESC(降序)
2 按防御值从高到低排序
3 将蜀国英雄按攻击值从高到低排序
4 将魏蜀两国英雄中名字为3个字符的,按防御值升序排序
3 limit (永远放在SQL命令的的最后写)
1 显示查询记录的条数
2 用法
limit n;--->显示n条记录
limit m,n; -->从第m+1条记录开始,显示 n 条
3 分页
每页显示5条记录,显示第4页的内容
每页显示n条记录,显示第M页的内容
第M页,limit(m-1)*m,n
4 聚合函数
1 分类
avg(字段名):求该字段的平均值
sum(字段名):求和
max(字段名):最大值
min(字段名): 最小值
count(字段名):统计该字段记录的个数 select max(gongji) from sanguo;
select name,max(gongji) as max from sanguo; 引入别名函数 别名[max]
5 group by : 给查询的结果进行分组
1 查询表中都有哪些国家
select country from sanguo group by country;
2 计算每个国家的平均攻击力
先分组,再聚合, 再去重
select country,avg(gongji) from sanguo group by country;
3 select 之后的字段名如果没有在group by 之后出现,则必须要对该字段进行聚合处理(聚合函数)
4 查找所有国家中英雄数量最多的前2名的国家名称和英雄数量
5 having 语句
1 作用 :对查询结果进行进一步的筛选
2 找出平均攻击力大于105的国家的前2名, 显示国家名称和平均攻击力
3
1 having 语句通常和group by 语句联合使用,过滤由 group by 语句返回的记录集
2 where 只能操作表中实际存在字段,having语句可操作由聚合函数生成的显示列
4 查询记录时做数学运算
1 运算符 + - * / %
2 查询时显示所有英雄攻击力翻倍
select id,name,gongji*2 as newgj from sanguo;
2 嵌套查询(子查询)
1 定义: 把内层的查询结果作为外层的查询条件
2 语法
select ... from 表名 where 字段名 运算符 (select ... from 表名 where 条件);
练习 找出每个国家攻击力最高的英雄的名字和攻击值
上面的查询可能会出现一些人BUG,所以为了防止BUG的发生,作出一点修改
多表查询。连表查询
1 SELECT * FROM list_char LEFT JOIN list_name on list_char.pid=list_name.pid
1 两种方式
1 笛卡尔积 : 不加where条件
使用一条记录跟后面的每个表的记录逐一匹配
2 加where 条件
select .. from 表1,表2 where 条件;
4 连接查询
1 内连接(inner join)
1 语法格式
select 字段名列表 from 表1
inner join 表2 on 条件;【可以加个 】
inner join(等值连接) 只返回两个表中联结字段相等的行
2 外连接
1 左连接(left join)
1 以左表为主 显示查询结果
2 select 字段名列表 from 表1
left join 表2 on 条件
left join 表2 on 条件
3 练习 ,
显示省,市详细信息,要求省全部显示
2 右连接(right join)
以右表为主显示查询结果,用法同左连接一模一样
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
inner join(等值连接) 只返回两个表中联结字段相等的行
锁
读锁(共享锁)
写锁(互斥锁,排他锁)
2 锁粒度
1 行级锁:Innodb
select : 加读锁,锁1行
update : 加写锁,锁1行
2 表级锁 : MyISAM
select : 加读锁,锁当前整张表
update : 加写锁,锁1张表6 存储引擎(Engine)
1 查看所有存储引擎
show engines;
2 查看已有表的存储引擎;
show create table 表名;
3 创建表指定存储引擎
新表
create table 表名(...) engine=myisam;
已有表
alter tbale 表名 engine=myisam;
常用存储引擎的特点
1 InnoDB 特点
1,支持事务,外键,行级锁
2 共享表空间
表名.frm : 表结构和索引信息
表名.ibd : 表记录
2 MyISAM特点
1 支持表级锁
2 独享表空间
表名.frm 表结构
表名.myd 表记录 mydata
表名.myi 表索引 myindex
3 MEMORY存储引擎
1 数据存储在内存中,速度快
2 服务器重启,MYSQL服务重启后表记录消失
4 如何决定使用哪个存储引擎
1 执行查询操作多的表使用 MyISAM引擎 【使用 InnoDB节省资源,表加锁比行加锁节省资源】
2 执行写操作多的表使用 InnoDB
约束
1 作用 : 保证数据的一致性,有效性
2 约束分类
1 默认约束 (default)
插入记录时,不给该字段赋值,则使用默认值
sex enum('H','F') default 'S',
2 非空约束(not null)
不允许该字段的值为null
id int not null,
id int not null default 0
create table t1(
id int not null,
name varchar(15) not null,
sex enum('m','f','s') default 's',
course varchar(20) not null default 'python'
)character set utf8;
索引
1 定义 :
对数据库中表的一列或多列的值进行排序的一种结构(bTree)
2 优点
加快数据的检索速度
3 缺点
1 当对表中数据更新时,索引需要动态维护,降低数据的维护速度
2 索引需要占用物理存储空间
4 索引示例
使用 show variables like 'profiling' 查看当前状态1 开启运行时间检测 : mysql> set profiling=1
2 执行查询语句 select name fromm t1 where name='lucy99999';
3 查看执行时间 show profiles;
4 在name 字段创建索引
create index name on t1(name);
5 再次执行查询语句 select name fromm t1 where name='lucy99999';
5 索引 排序都是bTree,,区别只是约束不一样
1 普通索引(index)
1 使用规则
1,可设置多个字段 ,字段值无约束
2 把经常用来查询的字段设置为索引字段
3 KEY标志 : MUL
2 创建
1 创建表时创建 【create table t1 (id int ,name varchar(15) ,index(name),index(id));】 【创建两个索引】
2 在已有表中创建索引 【create index 索引名 on 表名(字段名);】-->索引名一般写字段名
3 查看索引
1 desc表名;--->KEY标志为 MUL
2 show index from 表名;
4 删除index
drop index 索引名 on 表名;
2 唯一索引(unique)
1 使用规则:
1 可设置多个字段
2 约束:字段的值不允许重复,但可以为NULL
3 KEY标志 : UNI
2 创建
1 创建表时
unique(phnumber),
unique(cardnumber)
2 已有表
create unique index 索引名 on 表名(字段名);
3 查看,删除同普通索引
删除:drop index 索引名 on 表名;
3 主键索引(primary key)&&自增长属性(auto_increment)
1 使用规则
1 只能有一个字段为主键字段
2 约束:字段值不允许重复,也不能为NULL
3 KEY标志:PRI
4 通常设置记录编号字段id,能够唯一锁定一条记录
2 创建
1 创建表时
1 id int primary key auto_increment,
2 id int auto_increment,
name varchar(20) not null
primary key(id,name)) 复合主键
auto_increment=10000,... 【ID从10000开始】
2 已有表
alter table 表名 add primary key(id);
alter table 表名 auto_increment=10000; 【补充】
3 删除主键
1,先删除自增长属性(modify更改数据类型)
alter table 表名 modify id int;
2 删除主键
alter table 表名 drop primary key;
4 外键(foreign key)
***明天讲***
数据导入
1 作用: 把文件系统中的内容导入到数据库中 出现乱码情况往下看
2 语法格式
load data infile '文件名'
into table 表名
fields terminated by '分隔符'
lines terminated by '\n'
3 导入数据库中
1 在数据库中创建对应的表
2 执行数据导入
1 查看搜索路径
show variables like 'secure_file_priv';
2 拷贝文件到上图路径
3 注意路径和分隔符
乱码情况下,,更改字符编码
4 导出数据
语法格式
select ... from 表名
into outfile '文件名'
fields terminated by '分隔符'
lines terminated by '\n';
导入导出时,特别需要注意文件路径,单词的正确程度
5 查看,更改文件权限
1 【ls -l score.txt】
r : 读
w : 写
x :可执行
表的复制
1 语法
create table 表名 select ... from 表名 where 条件;
2 示例
3
复制下表,且若每页显示2条记录,复制第3页的内容
4 复制表结构
create table 表名 select ... from 表名 where false;
1 外键
1 定义: 让当前表的字段值在另一张表的范围内去选择
2 语法格式
foreign key (参考字段名)
regerances 主表(被参考字段名)
on delete 级联动作
on update 级联动作;
3 使用规则
1 主表,从表字段数据类型要一致
2 主表 : 被参考字段是主键
3 创建主键
4 删除外键
alter table 表名 drop foreign key 外键名;
外键名查看 : show create table 表名; -- >CONSTRAINT `bjtab_ibfk_1` FOREIGN KEY (`stu_id`) REFERENCES `jftab` (`id`)【红字为外键名】
5 已有表创建主键
alter table bjtab add foreign key(stu_id) references jftab(id);
6 级联动作:
1 cascade 数据级联删除,级联更新(参考字段)
2 restrict(默认)
如果从表中有相关联记录,不允许主表操作
3 set null
主表删除,更新,从表相关联记录字段值为null,
MYSQL 用户账户管理
1 开启mysql远程连接(获取root权限 改配置文件)
bind-address
2 用root 用户添加授权用户
1 用root 用户登录mysql
2 授权
mysql> grant 权限列表 on 库名.表名 to "用户名"@"%" identified by "密码" with grant option;
权限列表 : all privileges | select | update
库名.表名 : db4.* | *.*(所有库的所有表)
3 示例
1 添加授权用户tiger,密码123,对所有库的所有表有所有的权限,可从任何IP去连接
数据备份(mysqldump,在linux终端操作)
1 命令格式
msyqldump -u用户名 -p 源库名 > ***.sql
回车输入数据库密码
2 源库名的表示方式
--all-databases 备份所有库
库名 备份一个库
-B 库1 库2 库3 备份备份多个库
库名 表1 表2 表3 备份多张表
数据恢复
1 命令格式(linux终端)
mysql -u用户名 -p 目标库名 < ***.sql
2 从所有库备份all.sql中恢复某一个库
mysql -u 用户名 -p --one-database < all.sql
注意:
1,恢复库时,如果恢复到原库会将表中的数据覆盖,,新增表不会删除
2 恢复库时,如果库不存在,则必须先创建空库
MYSQL 调优
1 创建索引
在select .where .order by常涉及到的字段建立索引
2 选择合适的存储引擎
读操作多 : MyISAM
写操作多 : InnoDB
3 SQL语句优化(避免全表扫描)
1 where 子句尽量不使用 != ,否则放弃索引
2 尽量避免 NULL判断,否则全表扫描
优化前 :
select number from t1 where number is null;
优化后 :
在number 字段设置默认值0,确保number 字段无NULL
select number from t1 where number=0
3 尽量避免用or 连接条件,否则全表扫描
优化前:
select id from t1 where id=10 or id=20
优化后:
select id from t1 where id=10
union all
select id fromm t1 where id=20
4 模糊查询尽量避免使用前置 % ,否则全表扫描
select variable from t1 where name="%secure%";
5 尽量避免使用 in 和 not in,否则全表扫描
优化前
select id from t1 where id in (1,2,3,4);
优化后
select id from t1 where id between 1 and 4;
6 不能使用select * ...
用具体字段代替*,不要返回用不到的任何字段