java学习笔记21,22-------mysql

1. mysql简介：

1.定义：mysql是一个关系数据库管理系统（Relational Database Management System）,是最流行的RDBMS之一，在WEB应用方面，其是最好的RDBMS应用软件。
2.作用：
*永久保存数据；
*提供数据的安全；
*提供对数据的并发访问;
*提供事务支持；
*提供了SQL语言操作数据。
3．著名的数据库有：
*Oracle 甲骨文
*MySQL 属于Oracle
*Sql Server微软（主要用在windows环境下）
*DB2 IBM公司
*SQLlite 用在移动端开发

2安装

1.解压

解压后bin目录下的程序有：
bin\mysql.exe 客户端程序
bin\mysqld.exe服务器端程序
bin\mysqldump.exe数据备份程序。
此处解压到E:\mysql-5.7.24-winx64，解压后的文件夹内容如下：

2.在命令窗口执行以下步骤进行安装使用：

1.初始化(只需要执行一次)

bin\mysqld --initialize --console
初始化后会出现data的文件夹，其中保存系统与用户数据。
注意事项：要记录临时密码 (g-H>(*Iq4YW，后面使用客户端连接时需要用到此密码；

###2.启动数据库服务（以后可以不用了）
bin\mysqld --console
在此操作中可以看到端口用的是：3306
可以使用Ctrl-C停止服务程序，或者关闭窗口。

###3.使用客户端连接
此处应该另开一个命令窗口，作为客户端；
连接命令：bin\mysql -u 用户名 -p
例如：初始化mysql有一个root(数据库管理员用户)

bin\mysql  –u root  –p

正确登录后会出现mysql>提示符；

4.修改密码（执行一次就行）

alter user‘root’@’localhost’identified by ‘root’;    //密码修改为了root，记得最后要有分号

5.配置环境变量（执行一遍就行）

每次运行mysql我们都必须在解压目录E:\mysql-5.7.24-winx64下才能运行，为了方便操作，需要把bin路径添加到环境变量中，这样在哪里都可以运行mysql，
步骤：把bin的整体路径：E:\mysql-5.7.24-winx64\bin 添加到计算机属性中的环境变量path中（放在最前面或者最后面，并用分号与其他路径隔开）。

计算机->右键属性->高级系统配置->环境变量

6.将mysql安装为系统服务（只执行一次）

bin\mysqld install 服务名
卸载 :sc delete 服务名
注：如果服务名没有提供，系统默认MySQL;
如果安装服务或者删除服务时出现权限不足时，要以管理员的身份运行.
安装成功在计算机->右键管理->服务和应用程序->服务中会出现MySQL

点击MySQL右键启动服务。

7.建库

设置默认的字符编码有以下两种方式：
一种是在建库语句上带上字符编码，另一种是在启动服务时添加配置文件。

第一种：

建库SQL语句

create database  数据库名 character set utf8mb4;

mysql中的utf8字符不够完整，选择utf8mb4是完整版的。

第二种：

要修改全局设置，在mysql解压目录添加my.ini配置文件，在其中输入

[mysqld]
character-set-server=utf8mb4

库操作的一些语法：

建库：

create database  数据库名;

例子：创建了data1数据库

删库的语法是：

drop database 数据库名；

查看建库信息

show  create  database 数据库名;

一般我们在选择设置字符编码时选第二种，后续中我们还可以在my.ini配置文件中添加东西，方便。

服务操作的一些语法：

停止旧服务 net stop mysql
删除旧服务 sc delete mysql
安装新服务mysqld install
启动新服务 net start mysql

3.基本操作

建表（table）

一个库中有多张表，每张表里有多条数据；表可分成行(横向row)和列（纵向column）例如：
学号 姓名 性别
1 张三 男
2 李四 女

建表步骤：

1.选库

use 库名；

2.建表

create table 表名（
列1名 数据类型，
列2名 数据类型
）;
例子：

create table student(
id tinyint,
name  varchar(10),
sex  char(1)
);

sql中数据类型的分类

1.整数类型：tinyint（一个字节）,smallint （两个字节）,int(4个字节)，bigint(8个字节)；无符号数字：tinyint unsigned(0~255);
2.浮点类型：float,double
3.定点小数：decimal(总位数，小数位数)；
4.字符类型：
char(长度) char(10)表示最多存10个字符，定长
varchar(长度) varchar(10) 表示最多存储10个字符，变长。
“abc”“abc ”存储时，长度不足10 ，空格补齐够10，定长；
“abc”“abc ” 存储时，根据实际长度存储，可以节省空间。
5. 日期类型 datetime timestamp

3.对表的一些操作

1.插入数据

insert into 表名（列1，列2，….列n）values(值1，值2，值3)；

例如：insert into student(id,name,sex) values(1,’张三’,’男’);
注意：值个数与列个数要一致。如果不能操作中文，查看数据库信息show create database 数据库名;看是否编码是否是utf8mb4，如果不是需要更改编码，或者删除此数据库，重新建一个。

2.查询数据的语法

select  列1，列2，…from 表名；

例子：select id,name,sex from student;
3.查看所有库

show databases;

4.查看所有表
show tables;
5.唯一主键
每张表只能有一个主键
主键的值必须唯一，且非空。

create table 表名（列1名  类型  primary  key, 列2名  类型，….）;

自增列，用来解决主键冲突问题
在主键列后加入：auto_increment
在id列加入自增列之后，id 的列由数据库来维护,插入数据时，不用给自增列赋值
例子：create table student(id int primary key auto_increment,name varchar(10),sex char(1));
一次插入 多条记录（mysql独有）
insert into student(name,sex) values(‘明明’, ‘男’), (‘明明1’, ‘男’), (‘明明2’, ‘男’);
8查询所有列
select * from student;
mysql会把*翻译成：id,name,sex；
1.删除记录（只删记录，不删表）

delete from 表 where 条件；

例子：delete from student where id=6;
删除表（连表和数据一块删除）

drop table 表

create table a(birthday datetime);
alter table auto_incremen=1001;//修改原来的b表；

第二部分 常用的SQL语句：

1.DDL 数据定义语言

create database 数据库名；
create table 表名（列定义）;
drop database 数据库名
drop table 表名；
alter table 表…（添加列，修改列,删除列，重命名列）
alter user 用户
create –-创建xx定义，drop—删除xx定义，alter—修改xx定义
添加列
语法：alter table 表名 add 列名 数据类型；
例子：给学生表新增一列：

alter table student add age tinyint unsigned;

修改列
语法：alter table 表名 modify 列名 新类型；
例子：

alter table  student modify name varchar(20);

删除列
语法：alter table 表名 drop 列名；
例子：

alter table student drop id;

重命名列(8.0版本才有)
语法：alter table 表名 rename column 旧列名 to 新列名

2.DML数据操控语言

1.复制表

语法1：insert into 表名（列…）values(值…);
语法2：insert into 表名（列…）values（值…）,(值…), (值…), (值…);
语法3：从表1查询，把查询结果插入表2
insert into 表2 select * from 表1；
如果两张表的结构不一样，可以在select后面加具体的列名，以便和新表匹配；

2. loda data

可以把外部的文本文件的内容导入到数据库表中
语法:load data infile ‘文件的路径\名字’ into table 表名；
例如：把heroes.txt文本文件中的数据加载到hero 表格中：
//创建表hero

create table hero(
id int primary key auto_increment,
name varchar(10),
loc  varchar(10),
sex char(1),
birth int unsigned,
dath int unsigned,
power int unsigned
);

//load文件

load data infile ‘e:\\herose.txt’ into table hero;

注意：要让load data 命令生效，必须修改设置,即在my.ini文件中写上以下内容：
[mysqld]
character-set-server=utf8mb4
secure-file-priv=
其中secure-file-priv=默认是null值，表示不允许加载文件，可以改为具体的文件名，表示只能加载这个文件，如果改为“ ”，即什么都不写，表示可以从任意目录加载文件。
更改设置后，要停止原来的服务net stop 服务名，重新开启服务 net start 服务名；
如果文件中的分隔符是逗号，不是\t制表符，需要用 fields terminated by ‘ 指定的分隔符’ ；
例子：

load data infile 'e:\\person.txt' into table person fields terminated  by ',';

3.source

source命令是针对sql文件进行操作的。
语法：source 文件路径/文件名
注意事项：*其文件内容必须是合法的sql语句；
*与Loda data 的区别：不用引号，建议用/分隔路径，文件编码与操作系统编码一致（gbk）;
例子：scott.sql表
里面有两个表emp，dept；

4.update 更新

语法： update 表名 set 列名=新值 where 条件；
例如：

update person set sex=’男’； //修改所有记录
update person set sex=’男’ where id=1; //按条件修改

5.delete 删除

语法：delete from 表名；//删除表中所有的记录（危险操作）
delete from 表名 where 条件；//删除满足条件的记录

6 select 查询

语法： select 列名…from 表 where 条件 group by 分组条件 having 分组筛选条件 order by 排序条件；

1）条件

   = 等值匹配， ！= 不等于,> 大于，<小于，>=大于等于,<=小于等于

逻辑运算符组合多个条件： 逻辑与and，逻辑或 or，逻辑非 not
列 between 值1 and 值2 =等价= 列>= 值1 and 列<=值2
列in （值1，值2，…值n） =等价= 列= 值 1 or 列=值2…
//有一个满足就是满足整个
like 模糊查询，部分匹配，其中匹配通配符 %表示匹配0~多个任意字符，
通配符_表示匹配1个字符；
例子：

 select * from hero where  name like ‘马%’；

取反的应用：not in, not between and , not like,
例如：

select * from hero where sex!=’男’；
select * from hero where sex=’女’ and loc=’建业’；

2） 排序条件

排序条件：列名 升降序，如果升降序关键字省略，默认从小到大排序。
升序->由小到大 asc
降序->由大到小 desc
语法：select * from hero order by power desc limit 10;
多列排序：排序条件1，排序条件2…
先按照条件1排序，条件1中的取值相同，再按照条件2 排序。
限制返回个数：
limit m;//最多返回m个结果；
limit n,m;//最多返回m个结果，n代表起始下标，下标从0开始。
经常用来实现分页应用，假设每页10条
第一页 limit 0,10;
第二页 limit 10,10;
第一页 limit 20,10;
####3）分组条件
例如：select count(),max(sal), deptno from emp group by deptno;
count() 表示求每组的个数
max(列) 求最大值
min(列)求最小值
sum(列)求和
avg(列)求平均

注意事项：分组之后，select子句中只能出现分组条件列和组函数，其他列不能出现在select子句中；order by 子句中只能出现分组条件列和组函数，其他列不能出现在order by子句中.
例如：select deptno,max(sal),ename from emp group by deptno;//ename不符合规定；
select deptno,max(sal) from emp group by deptno;

4）having 也是过滤

where>group by >having >select >order by>limit //sql语句的执行顺序；
有时筛选条件既可以写在where上，也可以写在having上（优先选择where,次数少）；
例如

select count(*), deptno from emp where count(*) >=5 group by deptno; // 因为where先执行，这时候还没有分组，不知道个数 
select count(*), deptno from emp group by deptno having count(*)>=5;

5）多列分组

多个列取值都相同的分为一组
group by 列1，列2…
group by deptno,job

6)多表结构和连接查询

select列…from表1 inner join 表2 on 连接条件
where group by having order by limit;

几种连接查询

(1).表1 inner join 表2 on 连接条件 （内连接）
内连接:两张表的记录必须完全满足连接条件，才会出现在最终结果中。
(2).表1 left outer join 表2 on 连接条件 （左外连接）注：outer可以省略
左外连接：位于连接左侧的表，不管是否连接到记录，都会出现在结果中，符合连接条件的记录和内连接效果一样，不符合连接条件的记录，对应另一张表的列都是null值。
例如： select empno,ename,e.deptno,d.dname,d.loc from dept d left outer join emp e on d.deptno=e.deptno;
(3).表1 right outer join 表2 on 连接条件 （右外连接）注：outer可以省略
右外连接：位于连接右侧的表，不管是否连接到记录，都会出现在结果中，符合连接条件的记录和内连接效果一样，不符合连接条件的记录，对应另一张表的列都是null值。

连接查询的等价写法（可能除了mysql其他的数据库不适用）：

内连接的等价写法：
select …from 表1，表2 where 连接条件
例如：select e.empno,e.ename,e.deptno,d.dname from emp e, dept d where e.deptno=d.deptno;
mysql独有的
select…from 表1 inner join 表2 using(deptno);//两张表连接列名要相同。

7.常用函数

注：查看帮助help ：
count() max()
例子：select count() from emp;//求整张表的 行数
select max(sal) from emp;//求整张表的工资最大的值
Bit Functions 位运算函数
Comparison operators 比较运算符
Control flow functions 流程控制
Date and Time Functions 日期函数

    year() 截取年份
    month()
    date()
     date_add(日期 时间间隔); 其中时间间隔的语法：interval n 单位
    select empno,ename,date_add(hiredate, interval 1 month ),hiredate from emp; 加一个月
    select empno,ename,date_add(hiredate, interval 3 day ),hiredate from emp; 加3天
    SELECT EXTRACT(DAY_MINUTE FROM '2009-07-02 13:02:03'); 提取日期中的从天到分钟的部分
    select now() 获取当前时间

Encryption Functions 加密
Information Functions
Logical operators 逻辑运算符
Miscellaneous Functions 剩余的函数
Numeric Functions 数学函数

    rand() 生成一个从[0.0 ~ 1.0) 之间的随机小数， 小于1.0
    floor() 舍去小数
    round() 四舍五入

String Functions 字符串函数

    left(字符串, n)  n代表从左边要截取的字符
    lower()
    upper()
    substr(字符串,下标, 长度) 下标从1开始
    求字符串长度的例子：select * from hero where char_length(name)=4;

导出数据（把数据库中的文件导出来）

1.cmd >mysqldump –u root –p 库名 >>要保存的文件.sql(source的逆操作，内部是sql语句)
2. 把表中的数据保存到文本文件中 （load data infile的逆操作）
select * from 表 into outfile ‘文件路径\文件名’