MySQL中用sql语句设建立记录单当天_mysql详解常用命令操作，利用SQL语句创建数据表—增删改查...

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 * ...

用具体字段代替*，不要返回用不到的任何字段