Javaweb基础之mysql回溯笔记(一)

---

mysql

启动

可以查看任务管理器中，是否有mysqlId的进程，如果有，说明mysql的服务已经启动了；这个是开机就能够启动滴，占内存也不大；

记一下，自己本地安装目录：
C:\Program Files\MySQL\MySQL Server 5.5\

那么这个mysqlId的可执行文件是在哪里呢？

在mysql的安装目录的bin目录下，有一个可执行程序mysqlId.exe;

如下：

一些文件

在mysql的安装目录的下，有一个my.in的配置文件

如图所示，默认端口为3306；

还有一个文件是其数据库存储的数据；默认安装的话是
在C:\ProgramData\MySQL\MySQL Server 5.5；

也就是在ProgramData目录下的文件；打开里面有自己创建的数据库，每一个frm后缀的文件都是一张表

连接和退出

登录：

在cmd命令行中直接使用命令mysql -u账号 -p密码 -hIP地址

注意这里登录不能打英文冒号。。。。会报错

退出

直接使用quit或者exit都能实现退出；

sql语句

其语句分为DDL，DML，DCL，DQL语言；

DDL：全名是 Data Definition Language
数据库或者表结构操作

DML：全名是 Data Manipulation Language
对表的记录更新（增删改）

DCL：全名是 Data Control Language
对用户的创建和权限

DQL：全名是 Data Query Language
对表记录查询

DDL之数据库

数据库或者表结构操作

查看所有数据库

show databases;

注意点：
a.base后加s，这一句后面必须要有英文冒号 ; 否则只能回退到上一步操作
b.凡是出现syntax的，都是语法错误！！！也就是SQL语句有错误！

切换数据库

use 数据库名

注意点： a.切记先选库，再选表，不然默认就是选择mysql库。。。。

创建数据库

create database [if not exists] mydb1 [charset=utf8];

注意点：
a.[ ]这个方括号里面的语句，表示可写可不写；区别只是语句运行成功，和失败；
b.方括号只是用来提示的，在mysql上操作的时候，不需要写方括号！！！
c.utf-8，中间的 - 不需要写！！！

删除数据库

drop database 数据库名;

修改数据库编码

alter database 数据库名 character set utf8;

注明：
alter英文意思是修改的意思

常用数据类型

更为详细的数据类型，菜鸟教程中：
菜鸟教程mysql数据类型

括号中的长度，表示字节。utf-8编码是一个汉字三个字节

列类型	解释
int	整型
double	浮点型，例如double(5,2)表示最多5位，必须有两位 小数，即最大为999.99
decimal	浮点型，在表单钱方面使用该类型，不会出现精度缺少的问题
char	固定长度字符串类型，char(255)，长度要是不足指定的长度，则补足到指定的长度；
varchar	可变长度字符串类型，char(65535)，长度要是不足指定长度，不需要补足指定的长度；但是需要空间来记录数据的长度
mysql中为text(标准中为clob)	字符串类型，文本方式存储，区分大小写
blob	字节类型，二进制的方式存储，不区分大小写
date	日期类型，格式为yyyy-MM-dd；年月日
time	时间类型，格式为hh:mm:ss；时分秒 时间值或持续时间
timestamp	时间戳类型，格式为：YYYYMMDD HHMMSS ；混合日期和时间值；时分秒和年月日

DDL之表

注意：

创建表的最后一个列的时候，其列名后面是不能加英文 , 的！！！ 否则会报错;

操作	SQL语句	举例
创建表	create table if not exists 表名( 列名 数据类型(长度), )	create table if not exists stu_2( number varchar(12), name char(12), ..... )
查看所有表名称	show tables;	无
查看指定表创建语句	show create table 表名;	show create table stu_2;
查看表结构	desc 表名;	desc stu_2;
删除表	drop table 表名;	drop table stu_2;
更改之添加列	alter table 表名 add( 列名 列类型, 列名 列类型.....);	alter table stu_2 add( gender cahr(10));
更改之修改列类型(包括数据长度)	alter table 表名 modify 列名 新的列类型;	alter table stu_2 modify gender char(80);
更改之修改列名(包括数据长度)	alter table 表名 change 旧列名 新列名 列数据类型(列长度);	alter table stu_2 change gender sex char(10);
更改之删除列类型(包括数据长度)	alter table 表名 drop 列名;	alter table stu_2 drop gender;
更改之修改表名	alter table 表名 rename (可要可不要)to 新的表名;	alter table stu_2 rename db_stu_2;

总结

1.查看数据库和表的关键字都是 show;
2.修改数据库和表的关键字都是 alter;
3.创建数据库和表的关键字都是 create；
4.任何对表结构修改的前部分操作都是 alter table 表名;
5.对表结构更改的关键字为:add modify drop

DML

对表的记录更新（增删改）

操作	SQL语句	注意点
插入	insert into 表名(列名,列名......) values( 列值， 列值，......)	a.不给出插入列，表示插入所有列; b.值的个数必须是该表列的个数; c.值的顺序必须和表创建时的列的顺序相同. d.如果只写部分列名，那么其没写的列名的列值为null；
举例	insert into stu_2(number,name,age) values(55,'zhangsna',18);
修改	update 表名 set 列名1=列值1, 列名2=列值2... where 要修改的列名=其列值	a.条件必须是一个boolean类型的值或表达式; b.set后面的等号表示赋值，where后面的等号表示条件; c.运算符： =、!=、 <>(不等于)、>、<、>=、<= between....and(例如：between 20 and 40 表示>=20 <=40)、 in(1,2)、这括号里面是一个集合，表示在这个集合中任意一个条件满足，就会执行set中的内容 is null、not(非的意思)、or（或者）、and
举例	update stu_2 set name=‘lisi’ where id=1;
删除	delete form 表名 where 条件 还可以使用如下语句： truncate table 表名	a.如果删除，没有带上where条件的话，就会相当于执行了 drop table 表名 这个语句，会将整个表删除。。。。。。 b.如果执行truncate那个语句，会先删除该表再创建该表，且无法回滚

总结

1.数据库中所有字符串类型（包括日期类型），必须使用单引号，不能使用双引号；
2.在where语句中，任何条件为null的，都不能写=null，而是写is null；同样的不等于null，也是写is not null；

DCL

对用户的创建和权限

注意： 这些都是超级用户管理员root进行操作的。

1.创建用户

create user 用户名@IP地址 identified by ‘密码’;

举例：

指定用户只能在指定的ip地址上登录
create user zz@localhost identified by ‘123’;

指定用户能在任意ip地址上登录
create user 用户名@'%' identified by '密码';

2.给用户授权

grant 权限1,权限2,…,权限n on 数据库.* to 用户名@IP地址;

举例：

给指定用户 授权 指定数据库上的指定权限；
grant select drop create alter on mydb1.* to zz@localhost;

给指定用户 授权 指定数据库的所有权限；
grant all on mydb1.* to zz@localhost;

3.撤销用户权限

revoke 权限1,权限2,…,权限n on 数据库.* from 用户名@IP地址;

举例：

撤销指定用户指定数据库上的指定权限；
revoke select drop create alter on mydb1.* from zz@localhost;

撤销指定用户指定数据库的所有权限；
revoke all on mydb1.* from zz@localhost;

4.查看用户权限

show grants for 用户名@IP地址

举例：

查看指定用户的权限
show grants for zz@localhost;

5.删除指定用户

drop user 用户名@IP地址

举例：

删除指定的用户
drop user zz@localhost;

总结

1.创建用户权限，有一个关键字是identified，确认的意思；
2.撤销跟授权用户权限，差别在第一个字母revoke和grant，跟后面的from和头的区别；to是授权，from是撤销；

DQL

对表记录查询

表

操作	SQL语句	注意点
查询所有列	select * from 表名	a.* 是表示所有列的意思;
举例	select * from emp;
查询指定列	select 列名1,列名,..... from 表名	a.列名之间用英文逗号隔开;
举例	select empno,ename from emp;
查询去除重复行的所有列	select distinct * from 表名	a.是在*的前面加一个关键词： distinct；
举例	select distinct * from emp;
查询去除重复行的某列	select distinct 列名1,列名2,... from 表名	a.是在列名的前面加一个关键词： distinct；
举例	select distinct empno,ename from emp;
列运算
数量类型的列可以做：+,-,*,/	select 列名1+列名2, 列名1*列名2.... from 表名	a.这并不会改变数据库的任何数据; b.无法转换成整数的，例如中文，都当做0来计算
举例	select sal*1.5 from emp; select sal+com from emp;
字符串类型的列可以做连续运算	select concat(,) from 表名	a.这是连接字符串用的，java中是用"+"，这里用函数;
举例	select concat('$',sal) from emp;
转换null值	select ifnull(某个列名,0) from from 表名	a.适用于某些列的值为null，但是我们要进行运算，要是按照null计算，那结果总为null;
举例	select ifnull(com,0) from emp;
给列起别名	select 某个列名 as 奖金 from 表名	a.这样让查询结果更清晰 b.其中as是可以省略的；
举例	select ifnull(com,0) as 奖金 from emp;

---

条件控制
(跟delete，update一样，使用where子句控制查询) 举例:	selec empno,ename,sql,comm, from emp where sal>1000 and comm is not null; selec empno,ename,sql,comm, from emp where sal between 500 and 1000; selec empno,ename,sql,comm, from emp where job in ('经理','董事长');
模糊查询： (在于查询某几个关键字,其中"_"为匹配一个任意字符，"%"匹配0-N个字符；这两个是有区别的) 举例:	selec ename from emp where like '张__'; 匹配以张开头的任意三个字的名字 selec ename from emp where like '%刚'; 匹配所有以刚结尾的名字 selec ename from emp where like '%刚%'; 匹配所有含刚（无论哪个位置）的名字
单列排序： (默认为升序，为asc；降序为desc) 举例:	selec * from emp order by sal desc; 以sal为降序查询emp表的所有信息
多列排序： (默认为升序，为asc；降序为desc；写多个时候用英文逗号隔开) 举例:	selec * from emp order by sal desc,comm asc; 以sal为降序查询emp表的所有信息，如果sal有相同的，那么以comm为升序排序
聚合函数： (查询某列的纵向) 举例:	select count(*或者整数；效果一样) from emp 所有列中不为null的行总数，也就是查询emp总数据 select count(comm) from emp 查询comm列中不为null的行总数 select sum(sal) from emp 查询sal列数值相加的总数 select max(sal) from emp 查询sal列数值中最大的数值 select min(sal) from emp 查询sal列数值中最小的数值 select avg(sal) from emp 查询sal列所有数值中的平均数值
分组查询： (group by后面跟着的是分组的列，然后select后面**只能**跟着分组的列和**聚合函数**) 举例:	select job,count(*) from emp group by job; 以职位为分组，并且查询出每个分组的人数 select depno,count(*) from emp where sal>10000 group by depno 以职位为分组，并且查询出sal大于一万的部门和人数（分组前） select depno,count(*)from emp where sal>10000 group by depno having count(*)>2 以职位为分组，并且查询出sal大于一万的部门中人数大于等于2个的部门和人数（分组后）
方言limit 举例:	select * from emp limit 0,6; 查询emp表信息,从下标为0的第一行开始，一共查询6条数据；注意是包括了起始的第一行数据；

查看数据库的编码

语句为：
show variables like ‘char%’;

mysql备份与恢复数据库

定义

备份就是备份SQL相关语句，然后丢失数据的时候，顺便再执行SQL语句插入；

1.数据库导出sql脚本

注意了，导出的是SQL的数据，而不是数据库；也就是说数据库还得我们自己创建
语句如下

mysqldump -u用户名 -p密码 数据库名>生成的SQL脚本路径

举例
mysqldump -uroot -padmin mydb1>D:\mydb1.sql

2.sql脚本执行

第一种方式：
要执行某个sql脚本，可以在cmd中执行如下语句:

mysql -u用户名 -p密码 数据库名<要执行的SQL脚本路径

举例
mysql -uroot -padmin mydb1<D:\mydb1.sql

第二种方式
登录mysql,切换到自己要使用的数据库；
执行如下语句：
source 要执行的SQL脚本路径

约束

主键约束

有两种方式实现主键约束，如下：

第一种方式：

create table stu(
	sid char(6) primary key,
	sname char(10),
	age int,
	gender varchar(20)
)

第二种方式

create table stu(
	sid char(6),
	sname cahr(10),
	age int,
	gender varchar(20),
	primary key(sid)
)

其还可以通过修改表时指定主键约束，语句如下

alter table stu
add primary key(sid);

删除主键：

alter table stu
drop primary key;

自增长

设置非主键的自增长，在mysql中有一个很重要的点，那就是必须要设置该字段为unique，也就是唯一约束，不然就会报错。
亲测！！！！

一开始将字段设置为int类型，但是没有设置为unique，报了一下错误：

Incorrect table definition; there can be only one auto column and it
must be defined as a key

之后设置为unqiue就没有报错了。所以切记！！！

第一种方式：

create table stu(
	sid char(6) unique auto_increment,
	sname char(10),
	age int,
	gender varchar(20)
)

第二种方式

注意：
第二种方式同样需要将非主键字段设置为unique约束

alter table stu
change sid sid int auto_increment;

删除自增长

alter table stu
change sid sid int unsigned;

主键自增长

第一种方式：

create table stu(
	sid char(6) primary key auto_increment,
	sname char(10),
	age int,
	gender varchar(20)
)

第二种方式

alter table stu
change sid sid int primary key auto_increment;

也可以如此：
alter table stuinfo 
modify num int(5) not null auto_increment;

删除主键自增长

alter table stu
change sid sid int;

注意了

跟主键自增长的添加和删除其都不是add和drop，而是change

重置自增长

如果自增长的字段，需要重置其自增长的数字，那么需要truncate关键字，这个单词是截断的意思。

如下：

truncate table teacher; 

非空约束和唯一约束

非空约束如下：

create table stu(
	sid char(6) primary key auto_increment,
	sname char(10) not null,
	age int not null,
	gender varchar(20) not null,
)

唯一约束如下：

create table stu(
	sid char(6) primary key auto_increment,
	sname char(10) not null unique,
	age int not null,
	gender varchar(20) not null
)

外键约束

两种方式跟前面的一样，可以创建表的时候添加外键约束，也可以在修改表时添加外键约束；

第一种方式
constraint 外键约束名（可自定义，一般是fk_从表名_主表名）foreign key(从表的要设置外键约束的字段) references 主表名(主表主键)

create table stu(
	sid char(6) primary key auto_increment,
	sname char(10) not null,
	age int not null,
	gender varchar(20) not null,
	deptno int,
	constraint fk_stu_dept foreign key(deptno) 
	references dept(deptno)
)

第二种方式

alter table stu
add constraint fk_stu_dept foreign key(deptno)
references dept(deptno)

注意了

a.所有设置外键，都是在从表中设置的；
b.从表的外键字段其类型必须要跟主键的一样，但是外键字段名字可以不一样；

连接查询

分类

分为：

内连接，外连接，自然连接

内连接

SQL 89标准，隐式连接符号逗号

select  * from 表1 别名1,表2 别名2
where 
别名1.字段名=别名2.字段名

SQL 92标准，显示连接

select  * from 表1 别名1 inner join 表2 别名2
on 
别名1.字段名=别名2.字段名

在上面的两种方式，无论是使用join还是使用逗号将两个表连接到一起，其实现的效果都是一样的，并且其效率也是一样的。。。。

参考：
sql用逗号连接多张表对应哪个join？

自然

select  * from 表1 别名1 natural join 表2 别名2

自然连接和内连接的区别

请参考以下文章：
数据库中自然连接与内连接的区别

mysql 内连接、自然连接、外连接的区别

通过具体的SQL实例，通俗易懂的搞清楚笛卡尔积、内连接、外连接、自然连接的区别

自然连接和内连接之间的区别

外连接

左外连接：

左边表记录无论是否满足条件都会查询出来，不满足条件的在右边表显示为null，右边表只有满足条件才会查询出来；

select * from 表1 别名1 left outer join 表2 别名
on
别名1.字段名=别名2.字段名

这个outer可以省略

左外自然：

select * from 表1 别名 natural left outer join 表2 别名

右外连接：

右边表记录无论是否满足条件都会查询出来，不满足条件的在左边表显示为null，左边表只有满足条件才会查询出来；

select * from 表1 别名1 right outer join 表2 别名
on
别名1.字段名=别名2.字段名

这个outer可以省略

右外自然：

select * from 表1 别名 natural right outer join 表2 别名

**注意**:

在from后面的表为左表；
也可以说，在outer join左边的表为左表；

全连接

左边表和右边表记录无论是否满足条件都会查询出来，左边表不满足条件的在左边表显示为null，右边表不满足条件的在右边表显示为null；
标准语言的话，是在left和right的位置改为full，就可以完成全连接；但是在mysql中不支持。。。。

将使用左外连接和右外连接查询出来的结果集，使用union合并结果集，那么也能达到全连接的效果

合并结果集

1.使用关键字：union all

不去除重复行

如下：

select *from ab
union all
select *from cd

2.使用关键字：union

去除重复行

如下：

select *from ab
union
select *from cd

注意： 要求其结果集的列类型和列数完全一样

子查询

定义

也就是说在查询中还有查询；
可以根据select的个数判断是否是子查询；

位置

其存在的位置有三个：
第一个是select后面,作为要查询的字段名;（貌似这个不标准。。）
第二个是from后面,作为表存在;
第三个是where后面，作为条件存在；

举例：
有一个员工表emp，其中有sal字段为工资

查询出工资最高的人的详细信息？

select * 
from emp
where sal=max(sal);

这个语句是不允许的....会报错。。。
聚合函数不允许出现在where后面

所以我们可以分开写：
1.先查询出最高工资的人
select max(sal) from emp;

2.再将这个最高工资的条件写进去
select * from emp 
where
sal=(select max(sal) from emp);

常见用法

因为子查询的结果集可以有如下几种情况：
单行单列，单列多行，单行多列，多行多列

这些是存在条件上

单行单列：

select * from 表1 别名1 
where 表1的列 [可使用的符号：=、>、<、>=、<=、!=]
(select 表2的列 from 表2 别名2 where 条件)

单列多行：

select * from 表1 别名1
where 表1的列 [可使用的符号：in、all、any]
(select 表2的列 from 表2 别名2 where 条件）

单行多列：

select * from 表1 别名1
where 表1的列 [列1，列2] in
(select 列1,列2 from 表2 别名2 where条件)

这个是存在表上

多行多列

select * from 表1 别名1,
(select 表2的列 from 表2 别名2 where 条件) 别名2
where 条件

注意：
在from后面，即作为表存在的话，要给子查询的语句起一个别名；

---

注意点

1.使用左右外连接可以查询出不匹配的连接字段的数据；
2.连接查询，可以连接自身查询；
3.列运算在select关键字的后面可以用表中的列字段进行一些运算；这个很容易忘记;