Mysql和JDBC

Linux操作mysql：
安装服务器端： sudo apt-get install mysql-server
启动服务：sudo service mysql start
检查过程中是否存在mysql服务：
ps -ajx|grep mysql
ps查看当前系统进程 -a显示所有用户流程 - j任务格式显示过程 -x显示无控终端过程
停止服务：sudo service mysql stop
重启：sudo sevice mysql restart
MySQL配置文件
配置文件路径：/etc/mysql/mysql.conf.d/mysqld.cnf
主要配置如下：
bind-address表示服务器绑定ip，默认为127.0.0.1
port默认为3306
datadir表示数据库目录，默认/var/lib/mysql
general_los_file表示普通日志 默认/var/log/mysql/mysql.log
log_error表示错误日志，默认/var/log/mysql/error.log
客户安装：
客户端是开发人员和数据库管理员(dba)使用，
图形界面客户端navicat,在navicat官网下载，下载linux系统的navicat，打开下载的位置，使用命令chmod x nevicat15-mysql-cs.AppImage 运行命令
黑马安装流程：
操作命令：
./start_navicat
试用期到期：删除用户目录.navicat64目录即可解决

Windows下安装Mysql数据库：
1、官网：http://dev.mysql.com/downloads/mysql
2、
3、
4.安装双击安装包
5、
6、
7、
8、

其实安装完windows版本的mysql默认情况下，服务器将有自己的客户端。Linux可以使用：sudo apt-get install mysql-client,安装:查看帮助文件:mysql --help
最基本的连接命令：mysql -u 用户名 -p密码
退出：quit 或者exit 或者ctrl d

备份和恢复数据库(导入和导出)
备份
mysqldump -u用户名 -p 数据库名 > 目录/文件名.sql
mysqldump -uroot -p java_db1 > java_db1.sql
恢复(恢复):首先创建空数据库 java_db，然后再还原
mysql -u用户名 -p 新数据库名 < 数据库文件
mysql -uroot -p java_db < java-db1.sql;

Windows操作：由于MySQL默认使用UTF-因此，8字符串Windows可以通过”MySQL8.0 Command Line Client -Unicode使用菜单启动命令行工具UTF-8字符集，启动步骤：
开始菜单–>MySQL–>MySQL Server8.0–>”MySQL8.0 Command Line Client -Unicode”启动

操作MySQL数据库的图形界面软件：SQLyog 、navicat、Datagrip

数据库管理系统：Database Management System，简称DBMS，负责数据的存储、安全、一致性、并发、恢复和访问，DMBS有一个数据字典(系统表)存储每个事务的相关信息，如名称、结构等，称为元数据。
数据库管理系统包括三个部分：
1.数据库文件集：主要是存储数据的一系列数据文件
2.数据库服务器端：主要负责管理数据文件和文件中的数据
3.数据库客户端主要负责与服务器通信，将数据传输到服务器或从服务器获取数据。
数据库通常被称为数据库 数据库管理系统。
数据库开发：网状数据库—>层次数据库---->关系型数据库---->面向对象数据库
也可分为关系型(二维表存储)和非关系型数据库NoSQL（以key-value对存储）
关系数据库：最基本的数据存储单元是数据表。数据表由行和列组成。每行称为记录，存储完整的信息，每列称为字段。每个表都应制定一个特殊列，以识别每行记录。这是主键列。MySQL默认情况下，每以分号结束，MySQL不区分大小写
Windows启动和登录mysql数据库：mysql -u用户名 -p
MySQL一个数据库的例子（Server Instance）可同时包含多个数据库
常见数据库对象：
对象名称 对应关键字 描述
表 Table 表是存储数据的逻辑单元，以行和列的形式存在，列是字段，行是记录
数据字典 系统表， 对于存储数据库相关信息的表，系统表中的数据通常由数据库系统维护。
约束 Constraint 执行数据验证规则，确保数据完整性
视图 View 一个或多个数据表中的数据逻辑显示，视图不存储数据
函数 Function 具有返回值的特定计算完成
存储过程 Procedure 用于完成一个完整的业务处理，没有返回值，但多个值可以通过传输参数传输到参考环境
触发器 Trigger 相当于事件监听器，当数据库发生特定事件时，触发器被触发并完成相应的处理。
查看当前实例中包含的数据库：Show databases;
创建数据库：create database [IF NOT EXISTS] 数据库名;
使用数据库：use 数据库名
查询数据库中包含的表：show tables;
查询数据表的表结构：desc 表名;
MySQL通常支持以下两种存储机制：
MyISAM：早期MySQL5.0之前默认的存储机制对事物支持不够好
InnoDB：提供事务安全带存储机制，InnoDB确保事务的完整性，并建立行级锁Oracle处理风格共享锁Select语句。默认启动系统InnoDB如果不想使用存储机制，InnoDB可使用表skip-innodb选项。MySQL5.0版本后默认使用InnoDB存储机制。
MyISAM和InnoDB区别：
主外键：MyISAM不支持，InnoDB支持。
事务：只有InnoDB支持
行表锁：MyISAM：支持表锁 InnoDB支持行锁
缓存：MyISAM只缓存索引，不缓存数据。 InnoDB不仅缓存索引，还缓存真实数据，对内存要求较高，内存大小对性能有决定性影响。
表空间：MyISAM和InnoDB都很小
关注点：MyIsam注重性能，功能少，性能好 InnoDB注重事务，功能多，性能差。
默认安装：MyISAM和InnoDB都默认安装
文件结构：MyISAM：b.frm：描述表结构文件、字段长度等
b.MYD(MYData)：存储数据信息(如果采用独立表存储模式)
b.MYI(MYindex)：索引信息文件。
InnoDB：b.frm：描述表结构信息、字段长度等
b.idb：存储数据信息和索引信息

默认使用创建表InnoDB，也可以加如下语句强制选择：
ENGINE=MyISAM：强制使用MyISAM存储机制
ENGINE=InnoDB：强制使用InnoDB存储机制
SQL全称Structured Query Language，结构化查询语言，标准化SQL，用于操作任何数据库。
SQL语句分为以下类型：
DQL：数据查询语言主要由select关键字完成，功能最复杂、最丰富的句子
DML：数据操作语言，由insert、update和delete完成三个关键字
DDL：语言的数据定义主要是由create、alter、drop、和truncate完成四个关键字
DCL：主要通过数据控制语言grant和revoke为数据库用户授权或回收用户权限完成两个关键字。
事务控制语句：由commit、rollback和savepoint完成三个关键字。
MySQL支持列类型：
MYSQL定义数据字段类型大致分为三类：数值、日期、时间和字符串（字符）类型
数值：
整形:如果设置为无符号，可以使用0~XXX的取值范围。

浮点型：

Float:单精度型(保证6位内有效数字的准确性)
double:双精度型(保证16位有效数字的准确性)
定义方式：
Float(M,D) 、double(M,D)
M最大值为255，代表数字为255，D代表小数的位数。
定点数：保存精确的小数
定义方式：decimal(M,D),M最大代表总数的位数为65，D代表小数位，如：decimal(5，2)代表5位数，其中2位为小数。主要用于需要准确的小数，如价格

字符串：

Varchar最多65535字节（UTF编码时，一个字符占三个字节，GBK编码时，一个字符占2个字节)
Utf8时——>65535/35=2万多个字符
Char和varchar的区别；
Char:长字符串：创建表时，char占用硬盘空间的字段尺寸已固定。
定义name char(10)，代表name字符占据10个字符的硬盘空间，具体有多少字节，取决于字段的编码，如果是UTF8编码，一个字符占三个字节，所以char(10)将占用30个字节，无论内容是否足够，10个字节将占用30个字节。例如，输入一个字符也将占用30个字节。
Varchar:长字符串。创建表时，varchar字段占用硬盘空间的大小不固定，如：name varchar(10)，代表name最多保存10个字符，如果是UTF如果输入内容不足30个字节，最多可占用30个字节，例如：abc，然后只占三个字符9字节 共占10个字节。因为字符后面有一个\n结束符，所以占用字节 1.
Text类型：与char和varhar不同的是，text不可以有默认值，其最大长度是2的16次方-1

超过255个字符的只能用varchar或者text
能用varchar的地方不用text和char
枚举类型：enum,在定义字段时就预告规定好固定的几个值，然后插入记录时值只能用这几个固定好的值中选择一个。
语法定义：enum(‘男’，‘女’，‘妖’）
一个enum最多可以设置65535个值，这个字段最终只占1到2个字节（要看设置值是多少）比较节省空间。
时间类型：

Datetime和timestamp的区别：
范围不同：
datetime保存时间的范围:”1000-01-01 00：00：00”到”9999-12-31 23：59：58”
Timestamp保存时间的范围：”1970-01-01 00：00：00”到”2038-01-19 03:14:07”
存储空间不同

登录和退出数据库过程：

连接数据库2、输入用户名和密码3、完成对数据库的操作4、完成对表结构和表数据的操作5、退出数据库

数据库完整性和约束：
大部分数据库支持5大约束：
NOT NULL：非空约束，指定列不能为空
UNIQUE：唯一约束，指定某列或者几列组合不能重复
PRIMARY KEY：主键，指定该列的值可以唯一地表示该条记录
FOREIGN KEY：外键，指定该行记录从属于主表中的一条记录，用于保证参照完整性
CHECK：检查，指定一个布尔表达式，指定对应列必须满足该表达式。
MySQL不支持CHECK约束，即使使用也不会有任何作用。
约束的作用用于保证数据表的完整性，约束也是数据库对象，存储在系统表中。
MySQL使用information_schema数据库中的TABLE_CONSTRAINTS表保存约束信息。

NOT NULL非空约束： 指定列不允许为空，只能作为列级约束使用。
UNIQUE约束：指定某列或者几列组合不允许出现重复值，但可以出现多个null值，因为在
SQL中的null不等于null，也不等于空字符串，Mysql所有数据类型的值都可以是null。
同一个表中可以建立多个唯一约束，建立唯一约束后，MySQL会为该列相应的创建唯一索引，若不指定唯一约束名，则默认与列名同名，
语法一：列级约束语法：在创建表时，在指定列后添加唯一约束即可。只能指定一列
语法二：表级约束语法：在建表语句最后一行加：unique(列1,列2) 可以指定1列或多列
表级约束语法还可以指定唯一约束的名 语法： constraint 约束名 unique(列1,列2) 如：
Create table 表名
(
Column1 数据类型 约束，
…
ColumnN 数据类型 约束,
constraint 约束名 unique(列1,列2)
)
创表后使用add添加：alter table 表名 add unique(列1,列2)
创表后使用modify修改类型增加：alter table 表名 modify name varchar(20) unique
对于大部分数据库，删除约束：drop constraint 约束名
MySQL使用：alter table 表名 drop index 约束名;

PRIMARY KEY主键约束（索引）：相当于非空和唯一约束，不重复，不可以为空
如果对多列组合建立了主键约束，则多列都不能为空，但只要求多列组合不重复。
用于标识表中一条记录，一个表最多有一个，可以使用列级（一列）和表级语法（多列）。
MySQL将所有主键约束命名为PRIMARY，用户指定也无效（允许用户指定只是为了和标准SQL保持兼容性）
创建主键时，MYSQL在主键约束所在列或列组合上建立对应的唯一索引。
删除主键约束：alter table 表名 drop primary key;
为指定表增加主键：alter table 表名 add primary key(列1，列2)
若只为单独列增加，也可以使用modify：alter table 表名 modify列名 primary key;

FOREIGN KEY：外键约束 ，保证一个或两个数据表之间的参照完整性，主要用于构建一个表中的两个字段或两个表的两个字段之间的参照关系。从表外键列的值必须在主表被参照列的值范围之内，或者为空（也可以使用非空约束使其不能为空）
数据库的主键和外键类型一定要一致，两个表必须是innoDB类型。
从表外键参照的只能是主表主键列或唯一键列，这样才能保证从表记录可以准确定位到被参照的主表记录，同一个表内可以拥有多个外键，建立外键时，MySQL也会为该列建立索引。
一对多时，通常在多的列增加外键，多对多时，需要额外增加一个连接表添加外键来记录它们的关联关系。
因为外键的值必须是参照列已有的值，因此，从表插入记录前必须先向主表插入记录，否则从表记录的外键只能为null，外键约束只保证被参照的记录必须存在，并不保证必须要被参照记录，所以外键可以为null,若希望保证每条从表记录必须存在对应的主表记录，则应使用非空、外键两个约束。
外键同样可以采用列级别（只为一列建立）和表级别（一列或多列）语法。但MySQL的列级别语法不会生效。提供只是为了和标准SQL保持兼容性。
语法：
Create table 表名
(
Column1 数据类型 约束，
…
ColumnN 数据类型 约束,
constraint 约束名 unique(列1,列2),
Primary key((列1,列2)),
Constraint 外键名 foreign key(列1，列2…) references 主表(主表列1，主表列2…)
)
若指定外键名，则加constraint 外键名，若不指定，则可以省略，MySQL默认给外键约束命名为：table_name_ibfk_n 即表名_ibfk_n n为第几个外键，从1开始。
删除外键：drop foreign key 外键约束名
外键不仅可以参照其他表，也可以参照自身的列，即自关联，语法：
Foreign key(列1…) references 自身表名(列n…)
如果希望当删除主表记录时，从表记录也随之删除，则需要在建立外键约束后添加on delete cascade或添加on delete set null
若添加on delete cascade，删除主表记录时，参照该主表的从表记录全部级联删除
若添加on delete set null，删除主表记录时，参照该主表的从表记录的外键设为null

MySQL不支持的CHECK约束：要求指定列满足表达式。
在定义表的所有列定义后使用：check(表达式) 如check(列1 > 10)即要求列1值大于10

数据库基本操作：
查看（show）、使用（use）、创建（create）、删除（drop）数据库的命令格式。
Ctrl + a回到开头 ctrl + e回到结尾 ctrl + l 清屏
退出ctrl +d
查看所有数据库：show databases;
查看版本：select version();
查看时间:select now();
查看当前使用的数据库：select database();

DDL语句：
创建指定数据库字符集：create database 数据库名 charset=utf8;
若数据库不存在才创建：create database if not exists 数据库名;
创建默认数据库：create database 数据库名
查看创建数据库信息的语句：show create database 数据库名
使用（切换）数据库：use 数据库名
删除数据库：drop database 数据库名
表结构的创建：查看（show）、创建(create)、显示表字段(desc)
创建表：create table 表名(
字段名称 数据类型 可选的约束条件，
Column1 datatype contrai,
Column2 datatype,
…
columnN datatype,
PRIMARY KEY(one or more columns)
);

auto_increment表示自动增长

利用子查询创建表：create table 表名 as 子表 新表和子表拥有同样的字段和数据类型
查看数据中有什么表：show tables
查看数据表的创建过程：show table 表名
查看表有什么字段：desc 表名
修改表结构的语法：
表中添加列（add）、修改字段名（change）、修改字段类型（modify）、删除（drop）
添加字段：
alter table 表名 add 列名 类型； 添加多个列可以使用()，各列逗号隔开
如：添加生日字段 datetime类型，id int类型
alter table students add (birthday datetime,id int);
注意：如果数据表中已有数据记录，除非给新增的列指定了默认值，否则新增数据列不可指定非空约束。
重命名字段：alter table 表名 change 字段原名 新名 类型及约束； 如；
alter table students change birthday birth datetime not null;
修改字段类型：已有字段，只修改类型：alter table 表名 modify 列名 新的类型及约束；如：Alter table students modify birth date not null;
Mysql的modify每次只能修改一个列定义,其他数据库如Oracle等可以修改多个列定义，语法和add相似，多个列定义使用括号，各列用逗号隔开，若希望mysql也支持一次修改多个列定义，在alter table后使用多个modify，多个modify用逗号隔开如：
alter table students modify test3 text,modify test4 double(3,2);
删除列/字段：alter table 表名 drop 列名;如：alter table students drop birth；
删除列定义时，会删除整列数据，释放该列在数据库中占用的内存。
修改表名：alter table 旧表名 rename to 新表名;
修改列名同时修改数据类型:alter table 表名 change 旧列名 新列名 数据类型 约束
Change用于修改列名，若只修改数据类型，用modify即可。
删除表：drop table 表名
删除数据表的效果：表结构被删除，表对象不再存在，表里的所有数据、相关索引、约束也被删除
truncate 表名：删除表的全部数据，保留表结构，实际上删除了表重新创建结构相同的新表
delete from 表名[约束条件]:可以一次删除一条，也可以删除多条或全部，
非InnoDB,truncate效率高。

DML：
select、insert、update、delete能够增删改查数据库中的数据
增删改查（curd）解释：
创建（create），更新（update）,读取（retrieve/Read）,删除（delete）

Insert into 插入
全行插入：insert into 表名values(…)
只插入指定字段： insert into 表名(列1，列2…) value(值1，值2…);
主键字段若为自动增长，可以使用0 null default来占位,系统会自动为该列分配至
多行插入：插入的多条记录使用逗号,隔开
insert into 表名 values(字段1…),(字段1…);
通过子查询插入数据到新的分类表中,如下方将查询出来的cate_name插入到表goods_cates的name列中。
insert into goods_cates(name) (select cate_name from goods group by cate_name);

update修改：
修改整个表一整列的值
update 表名 set 列1=值
修改指定行的列，按条件修改：
update 表名 set　列1=值　where 条件列名=值

使用as为列或者表指定别名：select 字段 as 别名,字段 as　别名 from 数据表;
也可以省略as，如：select 字段 别名,字段 别名 from 数据表;

查询时，字段的顺序会根据你首先输入的字段名优先显示，如
select age,name from students;
会显示age为第一列，name 为第二列。

delete from删除，删除整行
物理删除：delete from 表名 where 条件判断 物理删除是无法恢复的。
如：delete from students where id=4; 删除一行
删除整个表的数据 delete from 表名

逻辑删除：用一个字段来表示，这条信息是否已经不能再使用了，给students表添加一个is_delete字段bit类型。bit类型只能保存0和１，
is_delete = 1表示逻辑删除 is_delete = 0　表示没有删除
alter table students add is_delete bit default 0;
select * fron students where is_delete = 0;
只能查看到is_delete = 0的数据，逻辑上被删除，实际数据还在数据库中。

as、distinct的作用。
查询强化：
给表名起别名
select a.id,a.name from students as a
消除重复行：distinct字段，消除它后跟的字段的重复行
select distinct gender from students;

Where条件之比较运算
Where语句的作用：使用where句子对表中的数据筛选，结果为True的行才会进行操作
Where支持多种运算符，进行条件处理：
比较运算符、逻辑运算符、模糊查询、范围查询、空判断。
在mysql判断两个东西相等只需要一个等号。
比较运算符：=、>、 <、 >=、 !=、<>
<> 18 表示取大于18和小于18的数据，相当于不等于。
比较运算符不仅可以跟数字连接，还可以跟表达式或变量连接，如：select news_id + 5…
逻辑运算符：
and表示多个条件必须同时成立（值为True）
Or:表示多个条件满足任意一个时成立
Not：求否
运算符 优先级（优先级小的优先执行）
所有比较运算符 1
Not 2
And 3
Or 4

字符串连接：concat()函数
如：select concat(news_title,’xxx’)… 选择出news_id和’xxx’连接后的结果
但如果xxx改为null，则最后返回的都是null。
Not:表示取反操作。

模糊查询：使用like
like:配合字符使用
%表示任意多个任意字符 _表示一个任意字符
若希望使用下划线和%这个字符本身，加\转义，标准SQL没有提供\转义，使用escape
查询姓名中以小开头的名字：select * from students where name like ‘小%’;
查询姓名中有小的名字：select * from students where name like ‘%小%’;
查询有两个字的名字：select * from students where name like '’;
查询至少两个字的名字：Select * from students where name like ‘%’;

范围查询：
in表示在一个非连续的范围内,相当于多个值的or（或）关系
val1 In(val2,val3):要求val1等于后面括号里的任意一个表达式的值

查询编号是1或3或8的学生：select * from students where id in(1,3,8);
查询不是18和34岁的学生：Select * from students where age not in(18,34)
in还可以跟变量，如：select * from student where 2 in(student_id,teacher_id);
查询出student_id为2，teacher_id为2的记录。

beetween and：连续范围查询
Val1 between val2 and val3表示在一个连续的范围内，要求val1 >=val2且val1<=val3
查询编号3至8的学生：select * from students where id between 3 and 8;
还可以跟变量，如：select * from student where 2 between teacher_id and student_id;
查询出teacher_id小于等于2,student_id大于等于2的所有记录。

Where条件之空值判断：空判断：is null
查询没有填写身高的学生:select * from students where height is null;
null和“”不一样，null是什么都没有。
非空判断：is not null
查询填写了身高的学生：select * from students where height is not null;

Order by排序
排序查询语法：select * from 表名 order by 列1 asc/desc ，列2 asc/desc；
语法说明：
将数据按照列1进行排序，如果某些行的列1值相同时,则按照列2排序,以此类推。
asc从小到大排列，即升序，默认排序方式，可以不写
Desc从大到小排列，即降序
查询编号是1或3或8的学生查询年龄在18-34岁之间的男性，按照年龄从小到大的排序
select * from students where age between 18 and 34 and gender = ‘男’ order by age asc;
查询年龄在18-34的女性，身高从高到低排序
select * from students where age between 18 and 34 and gender = ‘女’ order by height desc;
order by 多个字段
查询年龄在18-34岁的女性，身高从高到低排序，如果身高一致，则按照年龄倒序排序
select * from students where age between 18 and 34 and gender = ‘女’ order by height desc,age desc;
查询年龄在18-34岁之间的男性，身高从高到低排序，如果身高相同按照年龄倒叙排序，如果年龄相同，则按照id的从大到小排序。
select * from students where age between 18 and 34 and gender = ‘男’ order by height desc,age desc,id asc;

Sql常用函数：
字符串函数：
Concat(s1,s2)将传的字符连接成一个字符串，任何字符串与null连接结果都是null，如：
Select concat(‘aaa’,’bbb’); 查询结果为aaabbb
Insert(str,x,y,instr)将字符串从x位置开始，y个字符长的子串替换为指定的字符。
Lower(str)和upper(str)：将字符串变成小写和大写
Left(str,x)和right(str,x)分别返回字符串最左边的x个字符和最右边的x个字符，如果第二个参数为null,那么都不返回任何字符。
Lpad(str,n,pad)和rpad(str,n,pad)用字符串pad对str最左边或最右边进行填充，直到长度为n个字符长度，即str最多不能超过长度n.
Ltrim(str)和rtrim(str)：去掉字符串当中做左侧和右侧的空格
Trim(str)去掉字符串(左右)两边的空格
Repeat(str,x) 返回str重复x次的结果
Replace(str,a,b)用字符串b替换字符串str中所有出现a字符的地方。
Substring(str,x,y)返回（截取）字符串str中第x位置起y个字符长度的字符

数值函数：
Abs(x):绝对值
Ceil(x) 向上取整
Floor(x)向下取整
Mod(x,y)：返回x/y的摸，即返回x对y取余（余数） 如：9/2余1 ,则mod（9，2）返回1
Rand()返回0-1内容的随机值

日期和时间函数：
Curdate()返回当前日期，只包含年月日
Curtime()返回当前时间，只包含时分秒
Now()返回当前日期和时间，年月日时分秒
Unix_timestamo返回当前时间戳
From_unixtime(unixtime)将一个时间戳转换成日期
Week(date)返回当前是一年中的第几周
Year()返回所给日期是哪一年
Hour(time)返回当前时间的小时
Minute(time)返回当前时间的分钟
Date_format(date,fmt)按字符串格式化日期date值
Date add(date,interval exprtype)计算日期间隔
dateDiff(date1,date2)计算两个日期相差的天数

流程函数：
If(value,t,f)如果value为真返回t，否则返回f.
Ifnull(value1,value2)如果value1不为空，返回value1，for则返回value2
Case when then end用法：select case 2 >3 then ‘对’ else ‘错’ end;
其他常用函数：
Select Database()：返回当前数据库
Select version() 返回当前数据库版本
User()返回当前登录用户名
Password(str) 对str进行加密
Md5()返回str的MD5值

聚合函数：aggregation function又称为组函数。
聚合函数的作用：聚合函数会把当前所在表当做一个组进行统计。
聚合函数特点：每个组函数接收一个参数（字段名或者表达式）
统计结果中默认忽略字段为null的记录，要想列值为Null的行也参与组函数的计算，必须使用IF NULL函数对NULL值做转换
不允许出现嵌套，比如：sum(max(xx)) #错误
Mysql常见的聚合函数：
命令 作用 示例
Count() 计算总行数 Select count() from
表名;
Max(列) 求此列的最大值 Select max(列名) from 表名;
Min(列) 求此列的最小值 Select min(列名) from 表名;
Sum(列) 求此列的和 Select sum(列名) from
表名;
Avg(列) 求此列的平均值 Select avg(列名) from
表名;
coutt()所有求总，也可以在括号内写字段：count(字段)，表示求的是该字段的总数，只对非空值进行累计。
查询男性有多少人，显示的字段是count(),可以指定别名。
select count() ‘女性总人数’ from students where gender = ‘男’;
最大值：max()
学生的最高身高
select max(age) from students;
女性的最高身高
select max(height) from studens where gender = ‘女’;
最小值：min
男性的最小年龄:select min(age) from students where gender =‘男’;
求和：sum
计算所有人的年龄总和:select sum(age) from students;
平均值:avg
计算平均年龄:
方法一：select avg(age) from students;
方法二：select sum(age)/count() from students;
四舍五入 round(数值，保留小数位数),如:round(123.23,1)保留一位小数123.2
计算所有人的平均年龄，保留两位小数：
select round(avg(age),2) from students;
计算男生的平均身高，保留两位小数。
select round(avg(height),2) from students where gender = ‘男’;
聚合函数是不能嵌套的，但是round不是聚合函数，因此可以嵌套。

调用存储过程：
Call 名称();
查看所有存储过程：show procedure status;
查看指定数据库中的存储过程：show procedure status where db = ‘数据库名’;
查看指定存储过程源代码：show create procedure 存储过程名
删除存储过程：drop procedure 名称

存储过程变量：使用DECLARE语句声明
单个变量声明语法：declare 变量名 数据类型（varchar()等） 默认值
如：declare avgRes int default 0;
同时声明两个或多个变量 declare 变量名1，变量名2 数据类型（varchar()等） 默认值
如：declare x,y int default 0;
分配变量值：
方式一：为变量分配一个值，使用set语句:set x = 3; set y = 4;
方式二：使用select into语句将查询的结果分配给一个变量：select avg(salary) into avgRes from info; 该语句将查询结果赋值给了avgRes;

变量的范围：只能在gegin和end之间进行使用。

存储过程参数：
三种类型：
In参数：表示调用者向过程传入值（传入值可以是字面值或变量）
Delimiter $$CreateproceduregetName(innamevarchar(50))BeginSelect\ast frominfowhereename=name;End$$
Delimiter ;
Call getName(‘李白’);
上面存储过程表示调用存储过程时，根据调用者传入的参数去查询信息。

out参数：表示存储过程向调用者传出值
Delimiter $$CreateproceduregetSalary(innamevarchar(50),outresint)BeginSelectsalaryintoresfrominfowhereename=name;End$$
Delimiter ;
Call getSalary(‘李白’,@res);
Select @res
上面存储过程表示调用者调用存储过程时，向存储过程传入参数’李白’，存储过程根据传入参数，返回一个值，调用者使用@res进行接收。

Inout：inout参数是in 和out参数的组合
delimiter $$
create procedure test(inout num int,in inc int)
BEGIN
set num = num + inc;

END$$
delimiter ;

set @num1 = 20;
call test(@num1,10);
select @num1;
上面的存储过程表示在存储过程外定义了一个可以传入的参数 @num1=20，可以传入的参数前缀用@修饰，调用时，存储过程根据调用者传入的@num，在存储过程进行操作，把最后得到的@num1值返回给调用者，即最后的@num1值为30

存储过程语句：
If 语句：if
自定义函数：
delimiter $$
create function rand_str(n int) returns varchar(255)
BEGIN
DECLARE str varchar(100) default ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ’;
declare i int default 0;
declare res_str varchar(255) default ‘’;
while i < n do

set res_str = concat(res_str,SUBSTR(str,floor(1 + RAND() * 52),1));
set i = i + 1;
end while;
return res_str;
END$$
delimiter ;

– select rand_str(4);

如果创建自定义函数时出现错误：This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)
这是我们开启了bin-log, 我们就必须指定我们的函数是否是
1 DETERMINISTIC 不确定的
2 NO SQL 没有SQl语句，当然也不会修改数据
3 READS SQL DATA 只是读取数据，当然也不会修改数据
4 MODIFIES SQL DATA 要修改数据
5 CONTAINS SQL 包含了SQL语句
其中在function里面，只有 DETERMINISTIC, NO SQL 和 READS SQL DATA 被支持。如果我们开启了 bin-log, 我们就必须为我们的function指定一个参数。
在MySQL中创建函数时出现这种错误的解决方法：
set global log_bin_trust_function_creators=TRUE;
这样添加了参数以后，如果mysqld重启，那个参数又会消失，因此记得在my.cnf配置文件中添加：
log_bin_trust_function_creators=1

索引：用于快速找出某个列中有一特定值的行，不使用索引，mysql必须从第一条记录开始读完整的表，直到找出相关的行，如果表中有索引mysql能够快速到达一个位置去搜索数据文件，而不必查看所有数据，提高查询效率。
优势：提高数据检索效率，降低数据库的IO成本，通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势：索引也是一种数据表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占空间，虽然索引提高了查询速度，同时会降低更新表的速度，如对表进行insert、update、deleter

Group by:分组：对大量数据进行分类统计或者分类计算。
分组用法：分组就是将一个‘数据集’划分成若干个‘小区域’，然后针对若干个小区域进行数据处理。
Group by 分组：
特点：1、group by的含义：将查询结果按照1个或多个字段进行分组，字段值相同的为一组。
group by 可用于单个字段分组，也可以用于多个字段分组。
分组的字段必须要出现在select后面。

在mysql默认情况下，select后的字段只能出现以下两种情况：
1、在group by后出现过
2、在聚合函数中出现。
分组group by
按照性别分组，查询所有性别
select gender from students group by gender;
Group by +聚合函数：
计算每种性别中的人数
select gender,count() from students group by gender;
查看不同年龄段有多少人
select age,count() from students group by age;
查询男性、女性的年龄最大值
select gender,max(age) from students where gender = ‘男’ or gender = ‘女’ group by gender;

Group by + group_concat()
group_concat（字段名）表示根据分组结果，使用group_concat()来放置每一个分组中某字段的集合
查询同种性别中的姓名
select gender,group_concat(name) from students group by gender;
查询每组性别的平均年龄
select gender,avg(age) from students group by gender;
。

group by + having
Having条件表达式：用来过滤分组结果。
Having作用和where 类似，但having只能用于过滤group by 而where是用来过滤表数据。
查询平均年龄超过18岁的性别，
select gender,avg(age) from students group by gender having avg(age) > 18;
查询每组性别的平均年龄和名字
select gender,avg(age),group_concat(name) from students group by gender;
查询每种性别中的人数多余2个的性别和姓名
select gender,count() from students group by gender having count() > 2;
如果是取了别名，后面也要用别名，如：
select gender,count(*) as a from students group by gender having a > 2;

with rollup汇总的作用
在语法的最后加上with rollup会对所有数据进行统计。
select gender,count(*) from students group by gender with rollup;

Limit 限制记录，从多行数据中取出指定数量的数据。
Limit的使用：可以使用limit限制取出记录的数量，但limit要写在sql语句的最后。
语法：limit 起始记录，记录数
说明：1、起始记录时只从第几条记录开始取，第一条记录的下标是0
记录数是指从起始记录开始向后依次取的记录数。
例如：limit 1 2 既是从第一个位置开始取，取两条。
如果不写第一个参数，默认从0开始，如：limit 5 表示取出前五条数据。
标准的SQL书写格式：[]表示可以省略
Select 字段1，字段2 from 表名
[where 条件]
[group by 字段名]
[having 条件]
[order by 字段名 排序规则]
[limit 起始位置，数量];

分页查询：目标：能够使用公式计算limit查询的起始位置。
分页查询语法：select * from 表名 limit start=0,count
分页公式：
limit (第x页-1)*每页数量，每页数量

分页查询：
每页显示2个，第3个页面：
select * from students limit 4,2;
每页显示2个，第10个页面：
select * from students limit (10-1)*2,2;

连接查询：
连接概念：当查询结果的列来源于多张表时，需要将多张表连接成一个大的数据集进行汇总显示。
Mysql支持多种类型的链接查询，分别为：
内连接:inner join
内连接查询：查询的结果为两个表匹配到的数据（交集）。

外连接：分为左外连接和右外连接。
右（外）连接查询：right join查询的结果为两个表匹配到的数据和右表（主表）特有的数据，对于左表中不匹配的数据使用null填充。

左（外）连接查询：left join查询的结果为两个表匹配到的数据和左表（主表）特有的数据，对于右表中匹配的数据使用null填充

连接查询语法：
左连接：主表 left join 从表on 连接条件;

内连接将两个表格连接后，连接后的字段是两个表的字段之和，连接后的行数，是两个表的行数相乘，因为表A的数据要跟表B的每一行都要进行连接，这就是笛卡尔积运算。
进行左连接时，是一行行进行匹配的，先找到从表的第一行数据，再从主表寻找匹配数据，然后继续找从表第二行，因此左连接的id排列顺序是不固定的，如：

连接查询：
内连接：inner join … on
两个表之间进行连接：select * from 表A inner join 表B;
select * from students inner join classes;
查询出每个学生对应的班级信息：
select * from students inner join classes where students.cls_id = classes.id;
按照要求显示姓名，班名
select students.name,classes.name from students inner join classelses where students.cls_id = classes.id;
给数据表取别名
select s.name,c.name from students s inner join classes c where s.cls_id = c.id;
内连接的另外一种写法,去掉inner join换成,连接两个表，后面的on换成where，如：
select s.name,c.name from students s,classes c where s.cls_id = c.id;

Inner join on上的on表示在某某条件下，作用和where一样，如：
查询 有能够对应班级的学生以及班级信息，显示学生的所有信息，students.，只显示班级名称classes.name
select students.,classes.name from students inner join classes on students.cls_id = classes.id;
在以上的查询中，将班级姓名显示在第1列。
select classes.name, students.*from students inner join classes on students.cls_id = classes.id;
查询有能够对应班级的学生以及班级信息，按照班级编号进行排序。
select * from students inner join classes on students.cls_id = classes.id order by classes.id asc;
当是同一个班级时，按照学生的id 进行从小到大排序：
select * from students inner join classes on students.cls_id = classes.id order by classes.id asc，students.id asc;

外连接：
总字段依然等于两个表的字段之和
左（外）连接：主表 left join 从表 on 连接条件；
语法：select * from 主表 left join 从表 on 连接条件;

从左边主表去右边从表找满足连接条件的数据，如果找到，保留主表和从表对应的数据，如果没找到，左边主表数据依然保留，右边补null。

查询每一位学生对应的班级信息
select * from students left join classes on students.cls_id = classes.id;
查询没有班级对应的学生
select students.* from students left join classes on students.cls_id = classes.id where classes.id = null;
注意：不建议使用select * from students left join classes on students.cls_id =classes.id having classes.id is null;

右（外）连接：从表 right join 主表 on 连接条件;

语法：select * from 从表 right join 主表 on 连接条件;

从右边主表去左边从表找满足连接条件的数据，如果找到，保留主表和从表对应的数据，如果没找到右边主表数据依然保留，左边补null。
select * from students right join classes on students.cls_id = classes.id;
上语句可以查询出哪个班没有学生。

自连接查询：特殊的内连接，将自己的表和自己的表进行连接关联（自己连接自己）。
目标：能够说出自连接的作用以及自连接和普通连接上使用的区别。
自连接概述：

如需要查询一个省，比如广东省对应的所有的市信息，可以使用两个表连接查询。
自连接查询用法：

查询一共有多少个省
Select count(*) from areas where pid is null;
查询省的名称为‘山西省’的所有城市。
Select * from areas as city inner join areas as province on city.pid = province.aid where province.atitle=’山西省’;
如果要将脚本的数据导入表中吗可以使用：source 文件名.sql

子查询：
子查询概念：在一个select语句中，嵌入另外一个select语句，那么嵌入的select语句称之为子查询语句，外部select语句则成为主查询。
子查询和主查询的关系：子查询是嵌入到主查询中。子查询是辅助主查询的，要么充当条件，要么充当数据源。子查询是可以独立存在的语句，是一条完整的select语句。
嵌套时，子查询需要用括号括住。
子查询分类：
标量子查询：子查询返回的结果是一个数据（一行一列），如:
列子查询：返回的结果是一列（一列多行）
行子查询：返回的结果是一行（一行多列）
#表子查询：返回结果是一个表格（多行多列）
行子查询
查出高于平均身高的所有人的信息
Select * from students where height > (Select avg(height) from students);

列子查询：
查询学生的班级号能够对应的学生名字
select * from students where id in(select id from classes);

创建京东数据库
create database jing_dong charset=utf8;

use jing_dong;
创建一个商品goods数据表
create table goods(
id int unsigned primary key auto_increment,
name varchar(40) not null,
cate_name varchar(40) not null,
brand_name varchar(40) not null,
price decimal(10,3) not null default 0,
is_show bit default 1,
is_saleoff bit not null default 0
);

插入数据：
insert into goods values(0,‘r510vc 15.6英寸笔记本’,‘笔记本’,‘华硕’,‘3399’,default,default),(0,‘y400n 14.0英寸笔记本’,‘笔记本’,‘联想’,‘4999’,default,default),(0,‘g150th 15.6英寸游戏本’,‘游戏本’,‘雷神’,‘8499’,default,default),(0,‘x550cc 15.6英寸笔记本’,‘笔记本’,‘华硕’,‘2799’,default,default),(0,‘x240 超级本’,‘超级本’,‘联想’,‘4880’,default,default),(0,‘u330p 超级本’,‘超级本’,‘联想’,‘4299’,default,default),(0,‘svp13226scb 触控超级本’,‘超级本’,‘索尼’,‘7999’,default,default),(0,‘ipad mini 7.9英寸平板电脑’,‘平板电脑’,‘苹果’,‘1998’,default,default),(0,‘ideacentre c340 20英寸一体电脑’,‘台式机’,‘戴尔’,‘2899’,default,default),(0,‘imac me086ch/a 21.5英寸一体机’,‘台式机’,‘苹果’,‘9188’,default,default),(0,‘at7-7414lp 台式电脑linux’,‘台式机’,‘鸿基’,‘36999’,default,default),(0,‘z220sff f4f06pa工作站’,‘服务器/工作站’,‘惠普’,‘4288’,default,default),(0,‘poweredge ii服务器’,‘服务器/工作站’,‘戴尔’,‘5388’,default,default),(0,‘mac pro 专业级台式电脑’,‘服务器/工作站’,‘苹果’,‘2888’,default,default);

查询类型cate_name 为’超极本’的商品名称name、价格price
select name.price from goods where cate_name = ‘超级本’;
显示商品的种类
方法一：分组的方式
select cate_name from goods group by cate_name
方法二:去重的方式
select distinct cate_name from goods;
求所有电脑产品的平均价格，保留两位小数
select round(avg(price),2) from goods;
显示每种类型cate_name的平均价格
select cate_name,avg(price) from goods group by cate_name;
查询每种类型的商品中，最贵，最便宜，平均价格，数量。
select cate_name,max(price)/min(price)/avg(price)/count(price) from goods group by cate_name;
查询所有价格大于平均价格的商品，并且按照价格降序排序
select * from goods where price > (select avg(price) from goods) order by price desc;
查询每种类型中最贵的电脑的所有信息
select goods.* from goods inner join (select cate_name,max(price) as max_price from goods group by cate_name) as c on goods.cate_name = c.cate_name and goods.price = c.max_price;

SQL操作实战：优化商品表
上方创建的goods表格中，类型名称和品牌名称有多个数据是一样的，如果需要修改类型名称或者品牌名称，需要用update set语句进行修改
update 表名 set　列１　＝　值　where 条件列名　＝　值
update goods set cate_name =新型台式机 where cate_name = 台式机;
这么一修改，则将全部为台式机的类型全部都改为了新型台式机，达不到只修改一条数据的目的，如需修改一条，还要增加更多条件，代码复杂。

创建商品分类表
第一步：创建表（商品种类表goods_cates）
#if not exists 表示如果表不存在才创建。
create table if not exists goods_cates(
id int unsigned primary key auto_increment,
name varchar(40) not null
);
第二步　同步　商品分类表　数据，将商品的所有（种类信息）写入到（商品种类表）中
按照分组的方式查询goods　表中的所有种类（cate_name）
首先写子查询的类型分组查询。
select cate_name from goods group by cate_name;
通过子查询插入数据到新的分类表中
insert into goods_cates(name) (select cate_name from goods group by cate_name);

第三步　　同步商品表数据，通过goods_cates数据表来更新goods表
update goods inner join goods_cates on goods.cate_name = goods_cates.name set goods.cate_name = goods_cates.id;

修改表结构字段名字不同change把cate_name改成cate_id
alter table goods change cate_name cate_id int unsigned not null;

创建品牌分类表：
1、查询品牌的名称
Select brand_name from goods group by brand_name;
子查询插将数据插入表goods_brands。
insert into goods_brands(name) (select brand_name from goods group by brand_name);

第二步：同步数据
更新goods
update goods inner join goods_brands on goods.brand_name = goods_brands.name set goods.brand_name = goods_brands.id;

第三步修改表结构
alter table goods change brand_name brand_id int unsigned not null;

外键使用：
假如往表中插入数据：
insert into goods(name,cate_id,brand_id,price) values(‘LaserJet pro P16０6dn’,12,4,‘1849’);
插入数据虽然显示成功，但是产品分类表中并没有12这个产品类型，所以类型无知。
这时候就需要添加外键约束，表和表之间的约束。
外键：一个表A的主键(primar key)，在另一个表B中出现，我们说A是B的一个外键
外键约束：对外键字段的值，在更新和植入时进行和引用的表中字段数据进行对标
foreign key,只有innodb数据库引擎支持外键约束。
语法：alter table 需要添加约束的表名 add foreign key(需要添加外键约束的列名) references 表名(列名)
对已经存在的字段添加外键约束：
给brand_id 添加外键约束和goods_brands的id建立外键关联。
alter table goods add foreign key(brand_id) references goods_brands(id);
给cate_id 添加外键约束和goods_cates的id建立关联
alter table goods add foreign key(cate_id) references goods_cates(id);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (jing_dong.#sql-1648_5, CONSTRAINT #sql-1648_5_ibfk_2 FOREIGN KEY (cate_id) REFERENCES goods_cates (id))
表示添加外键失败，表中已经有关键表不存在的数据。需要删除表中这条数据
delete from goods where cate_id = 12;

注意：添加外键时，两字段的数据类型必须完全一致，且必须有一个是主键字段。
对于不存在的表，可以在创建表的时候直接添加外键约束，而外键关联的表必须事先已经存在，如：创建goods_test表，字段为(id,name,cates_id,brands_id)，其中cate_id外键关联goods_cates的id 字段，brand_id外键关联goods_brands的is字段
create table goods_test(
id int unsigned primary key auto_increment, name varchar(40) not null, cate_id int unsigned not null, brand_id int unsigned not null, foreign key(cate_id) references goods_cates(id), foreign key(brand_id) references goods_brands(id) );

删除外键约束，需要先获取外键约束名称，该名称系统会自动生成，可以通过查看表创建语句来获取名称：
show create table goods;
获取名称之后就可以直接根据名称来删除外键约束
Alter table goods drop foreign key 外键名称;
使用到外键约束会极大地降低表更新的效率，所以在追求读写效率优先的场景下一般很少使用外键

视图：
是一张虚拟表，视图是对若干张基本表的引用，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）； 视图的本质其实就是一条被命名的SQL查询语句。
视图的好处：限制用户对数据的访问，使负责的查询变得简单，提供数据的独立性，对相同数据的不同显示。

定义视图：建议以V_开头
Create view 视图名称 as select语句;
若担心视图已存在，可以使用语句：create or replace view 视图名 as select语句;
和基本表的结构一致，不能有重复字段。
查看所有表或视图：show tables;
当原表数据发生改变，视图的数据也会发生改变。
注意：视图只用于查询，不要修改视图数据，因为视图本身没有存储数据。
若强制不允许改变视图的数据，可以在创建视图时使用with check option
语法：create or replace view 视图名 as SQL查询语句 with check option;
使用视图：查询时和使用普通表效果一致。
删除视图：drop view 视图名称; 如：drop view v_goods_info;
创建视图使用完整参数：
Create [ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}] VIEW 视图名[(属性清单)] as select语句 [WITH[CASCADED|LOCAL]CHECK OPTION];
ALGORITHM参数：
Merge:处理方式为替换式，可以进行更新真实表中的数据，为默认模式。
如果使用默认的替换式，则如果修改视图的数据，会把基表的数据也修改掉。
TEMPTABLE：具化式，由于数据存储在临时表中，所以不可以进行更新操作。
UNDEFINED：相当于没有定义ALGORITEM参数，mysql更倾向于选择替换方式，更有效。
WITH CHECK OPTION:更新数据时不能插入或更新不符合视图限制条件的记录。即不能不满足创建视图时select后面的where条件。
LOCAL和CASCADED：为可选参数，决定了检查测试的范围。默认值为CASCADED。
视图机制：
替换式：操作视图时，视图名直接被视图定义给替换掉，如操作视图info_salary_view:
语句：Select * from info_salary_view; 系统底层实际是将视图info_salary_view替换成创建视图的语句：select * from (select * from info where salary > 2000);
具化式：mysql先得到了视图执行的结果，该结果形成一个中间结果暂时存在内存中，外面的select语句就调用了这些中间结果（临时表）如：
Select * from info_salary_view;系统底层实际是将创建视图的select语句先执行查询出来在内存保存为