MySql手记

注意：本文章所有指令均在LinuxUbuntu20.04，MySql8.0环境下运行

基本命令

启动和关闭MySql服务器

service mysql start
service mysql stop

确认是否启动成功

MySql节点处于LISTEN状态表示启动成功

sudo netstat -tap | grep mysql

MySql登录和退出

登录

mysql -uroot -p
输入密码：

退出

quit;
或
exit;

注意 ‘;’

SQL语句的分类

1. DDL：数据库定义语言，这里面的sql语句，主要是用来操作数据库和表的结构的
2. DML：数据库操作语言，这里面的sql语句，主要是用来操作数据库中的表中的数据的增删改的
3. DQL：数据库查询语言，这里面的sql语句，主要是用来操作数据库中的表中的数据的查询的
4. DCL：数据库控制语言，这里面的sql语句，主要是用来操作数据库的用户的账号和密码，权限，访问级别等等

DDL：数据库定义语言，操作数据库和表的结构

针对数据库结构的操作：

1. 查看有哪些数据库？

show databases;

1. 创建一个新的数据库？

create database 数据库的名字;

1. 查看数据库的创建细节(查看创建数据库的sql语句长什么样)？

show create database 数据库的名字;

1. 删除数据库？

drop database 数据库名字;

1. 指定使用的数据库？

use 数据库名字;

针对表结构的操作：

1. 查看数据库中有哪些表？

show tables;

1. 创建一张表？

create table 表名 (

字段名 数据类型,

字段名 数据类型,

... ...

);

例如：

create table person (

name varchar(20),

age int

);

常见的数据类型？

int

decimal(5,2)

date：精确到年月日

datetime：精确到年月日时分秒

timestamp：精确到年月日时分秒，如果没有赋值，会按照当前系统时间自动填充

varchar(20)

1. 查看表的创建细节(创建表使用的sql语句是什么)？

show create table 表名;

1. 查看表结构？

desc 表名;

1. 在原表的基础之上，增加一个新的字段？

alter table 表名 add 字段名 数据类型;

alter table person add sex varchar(20);

1. 在原表的基础之上，修改字段的名字？

alter table 表名 change 原字段名 新字段名 数据类型;

alter table person change sex gender varchar(20);

1. 在原表的基础之上，修改字段的数据类型？

alter table 表名 change 原字段名 新字段名 数据类型;

alter table person change gender gender int;

alter table 表名 modify 字段名 数据类型;

alter table person modify gender varchar(20);

1. 在原表的基础之上，删除某个字段？

alter table 表名 drop 字段名;

alter table person drop gender;

1. 修改表的名字？

rename table 原表名 to 新表名;

rename table person to user;

1. 删除指定的表？

drop table 表名;

drop table student;

DML：数据库操作语言，主要用来操作数据库中的表中的数据的增删改

添加操作：insert into …

1. 添加一条记录

a.

insert into 表名 (字段名, 字段名, …) values (数据1, 数据2, …);

insert into user (name, age) values (‘tom’, 18);

insert into user (name) values (‘jerry’);

insert into user (age) values (19);

b.

insert into 表名 values (数据1, 数据2, …);

insert into user values (‘rose’, 19);

注意：有多少个字段，就要给多少个值

注意：如果添加中文内容，在添加之前设置编码：set names gbk;

1. 添加多条记录

a.

insert into 表名 (字段名, 字段名, …) values (数据1, 数据2, …), (数据1, 数据2, …),..;

insert into user (name, age) values (‘jack’, 20), (‘tony’, 21);

b.

insert into 表名 values (数据1, 数据2, …), (数据1, 数据2, …),…;

修改操作：update … set …

1. 修改所有记录

update 表名 set 字段名=值;

update user set age = 18;

update user set name = ‘tom’;

update user set name = ‘jerry’, age = 19;

1. 修改一条记录

a.

update 表名 set 字段名=值 where 字段名=值;

需求：将名字为jerry的年龄改为20

update person set age=20 where name=’jerry’;

删除操作：delete from …

1. 删除所有记录

a.

delete from 表名;

delete from person;

b.

truncate 表名;

truncate person;

注意：这条sql语句属于DDL

a和b有什么区别？

a的删除所有是逐行删除

b的删除所有是先将整张表给删除，然后再重新创建一张新的表

1. 删除一条记录

a.

delete from 表名 where 字段名=值;

delete from person where name = ‘tom’;

delete from person where age = 20;

delete from person where name = ‘jerry’ and age = 18;

DQL：数据库查询语言，主要是操作表中的查询

查询的分类？

单表查询

简单查询：

a.

select * from 表名; 查询表中所有的记录信息

select * from person;

b.

select 字段名,字段名,… from 表名; 查询表中的所有记录信息(某些字段)

select name, age from person;

条件查询：

=

>

<

>=

<=

!=,<>

between…and…

in ()

is null

is not null

and, &&

or, ||

distint

like：模糊查询

as：起别名

…

搭建环境：

create table user (

id int,

name varchar(20),

age int,

sex varchar(20)

);

例子

insert into user values(1, 'tom', 18, '男');
insert into user values(2, 'jerry', 20, '男');
insert into user values(3, 'rose', 17, '女');
insert into user values(4, 'jack', 21, '男');
insert into user values(5, 'tony', 22, '男');
insert into user values(6, 'mary', 28, '女');

需求：查询表中的名字为jack的那条记录信息

select * from user where name = 'jack';

需求：查询表中年龄为18岁的记录信息

select * from user where age = 18;

需求：查询表中年龄大于20岁的记录信息

select * from user where age > 20;

需求：查询表中年龄小于20岁的记录信息

select * from user where age < 20;

需求：查询表中年龄大于等于20的记录信息

select * from user where age >= 20;

需求：查询表中年龄小于等于20的记录信息

select * from user where age <= 20;

需求：查询表中年龄不等于20岁的记录信息

select * from user where age != 20;
select * from user where age <> 20;

需求：查询表中姓名不为tom记录信息

select * from user where name != 'tom';

需求：查询表中年龄在20岁到25岁之间的记录信息

select * from user where age between 20 and 25;
select * from user where age >= 20 and age <= 25;

需求：查询表中年龄为18,21,28的记录信息

select * from user where age in (18, 21, 28);
select * from user where age = 18 or age = 21 or age = 28;

需求：查询表中姓名为tom,rose,tony的记录信息

select * from user where name in ('tom', 'rose', 'tony');

需求：查询表中性别为null的记录信息

select * from user where sex is null;

注意：select * from user where sex = null; —错误写法

需求：查询表中性别不为null的记录信息

select * from user where sex is not null;

需求：查询表中姓名为tom，年龄为20岁的记录信息

select * from user where name = 'tom' and age = 20;
select * from user where name = 'tom' && age = 20;

需求：查询表中姓名为tom，或者年龄为20岁的记录信息

select * from user where name = 'tom' or age = 20;
select * from user where name = 'tom' || age = 20;

需求：查询表中看一看有哪些年龄段

select distinct age from user;

注意：distinct是去重的意思

select distinct age, name from user; --针对age和name相同的去重

需求：查询表中名字中第二个字带’三’字的记录信息

select * from user where name like '_三%';

_：代表任意一个字符

%：代表任意0到多个的字符

需求：查询表中名字为三个字，并且第二个字带’三’的记录信息

select * from user where name like '_三_';

需求：查询表中名为带’三’的记录信息

select * from user where name like '%三%';

需求：查询表中名字姓’张’的记录信息

select * from user where name like '张%';

需求：查询表中所有的记录信息(给字段起别名)

select * from user;

select id, name, age, sex from user;
select id as 编号, name as 姓名, age as 年龄, sex as 性别 from user;
select id 编号, name 姓名, age 年龄, sex 性别 from user;

需求：查询表中所有的记录信息(给表起别名)

select * from user;
select user.id, user.name, user.age, user.sex from user;
select * from user as u;
select u.id, u.name, u.age, u.sex from user as u;

复杂的条件查询：

聚合函数

count()：统计

需求：查询user表中有多少个用户

select count(*) from user;
select count(id) from user;
select count(1) from user;

sum()：求和

需求：查询表中所有的年龄总和为多少

select sum(age) from user;

max()：最大值

需求：查询表中年龄最大的记录信息

select max(age) from user;

min()：最小值

需求：查询表中年龄最小的记录信息

select min(age) from user;

avg()：平均值

需求：查询表中年龄平均值为多少

select avg(age) from user;

排序查询

order by 字段名 排序方式

排序方式：
    升序：asc
    降序：desc

注意：默认是升序的

需求：根据年龄进行升序排列

select * from user order by age;
select * from user order by age asc;

需求：查询年龄为17,18,28的信息，并且按照年龄升序排列

select * from user where age in (17, 18, 28) order by age asc;

需求：根据年龄进行降序排列

select * from user order by age desc;

需求：根据姓名字典顺序进行排序

select * from user order by name asc;

需求：根据年龄由大到小，如果年龄相同，再根据姓名字典顺序排序

select * from user order by age desc, name asc;

分组查询

group by 分组的字段

需求：按照性别进行分组，查询出每组的人数是多少

select count(*) from user group by sex;
select sex, count(*) from user group by sex;

select id, name, age, sex, count(*) from user group by sex; --这么写没有意义

注意：一般使用分组查询，查询出来的是聚合函数，或者根据分组字段的字段信息

需求：按照年龄进行分组，查询出每组的人数是多少

select count(*) from user group by age;
select age, count(*) from user group by age;

需求：按照年龄进行分组得到每组的人数，要求每组的每个人的年龄要大于20岁

(对20岁以上的人按照年龄进行分组,得到每组的人数)？

先筛选，后分组

select age, count(*) from user where age > 20 group by age;

需求：按照年龄进行分组得到每组的人数，要求每组的人数必须大于等于2

先分组，后筛选

select age, count(*) from user group by age having count(*) >= 2;

需求：按照年龄进行分组得到每组人数，要求每组的每个人的年龄大于18，并且分组之后每组的人数大于等于2

先筛选，然后再分组，最后再筛选

select age, count(*) from user where age > 18 group by age having count(*) >= 2;

where和having的区别？

1.where是在分组之前进行条件判断，having是在分组之后进行条件判断
2.where只能写在group by的前面，having只能写在group by的后面
3.where后面不能写聚合函数，having后面可以写聚合函数

分页查询

limit 起始个数, 每页显示的条数;

需求：查询第一页的记录

select * from user limit 0, 3;

需求：查询第二页的记录

select * from user limit 3, 3;

需求：查询第三页的记录

select * from user limit 6, 3;

需求：查询第N页的记录

select * from user limit (N-1) * 每页显示的条数, 每页显示的条数;

多表查询

环境搭建：

create table a (
    id int,
    name varchar(20)
);


insert into a values(1,'tom');
insert into a values(2,'jerry');
nsert into a values(3,'rose');

create table b (
    id int,
    name varchar(20)
);

insert into b values(1,'张三');
insert into b values(2,'李四');
insert into b values(4,'王五');

笛卡尔积查询

是所有多表查询的最基础的查询，其他的多表查询都是建立在笛卡尔积查询的基础之上的

但是笛卡尔积查询本身没有任何的意义。

select * from a, b;

内连接查询

隐式内连接查询：

select * from a, b where a.id = b.id;

显示内连接查询：

select * from a inner join b on a.id = b.id;

注意：显示内连接和隐式内连接的查询结果是一样的，只是写法不同

外连接查询

左外连接查询：

select * from a left join b on a.id = b.id;

右外连接查询：

select * from a right join b on a.id = b.id;

子查询

嵌套查询：查询语句里面还有查询语句

3种情况

情况一：里面查询语句查询出来的结果是一个数据

需求：查询出a表中id值等于b表中名字为李四的id值的记录信息

select * from a where id = (select id from b where name = '李四');

情况二：里面查询语句查询出来的结果是多个数据

需求：查询出a表中id值等于b表中名字为张三,李四的id值的记录信息

select * from a where id in (select id from b where name = '张三' or name = '李四');

情况三：里面查询语句查询出来的结果是一张表

需求：查询a表中的记录信息(按照id值降序查询)(使用子查询来实现)

select * from a order by id desc;

select * from (select * from a order by id desc) newA;

select * from newA;

约束

非空约束

not null

加与不加有什么区别？

加了非空约束，值就不能为NULL

在创建表时候添加非空约束？

create table user (
    name varchar(20) not null,
    age int
);

删除非空约束？
alter table user modify name varchar(20);

在创建表之后再去添加非空约束？

alter table user modify name varchar(20) not null

唯一约束

unique

加与不加有什么区别？

加了唯一约束之后，这一列的值不能有重复的

在创建表的时候添加唯一约束？

create table user (
    name varchar(20) unique,
    ge int
);

删除唯一约束？

alter table user drop index name;

在创建表之后再添加唯一约束？

alter table user modify name varchar(20) unique;

主键约束

primary key

加与不加有什么区别？

加了主键约束，就代表非空且唯一

每张表中只能有一个主键

在创建表的时候添加主键约束？

create table user (
    d int primary key,
    name varchar(20)
);

删除主键约束？
alter table user drop pirmary key;

在创建表之后再去添加主键约束？
alter table user modify id int primary key;

自动增长

auto_increment

加与不加有什么区别？

加了自动增长，会自动+1

在创建表的时候添加自动增长？

create table user (
    id int primary key auto_increment,
    name varchar(20)
);

删除自动增长？

alter table user modify id int;

在创建表之后再去添加自动增长？

alter table user modify id int auto_increment;

删除表中的所有记录？

1.delete from user;
2.truncate user;

注意：只有主键约束的属性才可以自动增长

外键约束

外键约束是建立在有两张表的基础之上的

格式：

constraint 外键的名字 foreign key (外键字段) references 另外一张表名(字段);

简化格式：

foreign key (外键字段) references 表名(字段);

在创建表的时候添加外键约束？

create table user (
    id int primary key,
    name varchar(20)
);

create table computer (
    id int primary key,
    name varchar(20),
    uid int,
    constraint c_u_key foreign key (uid) references user(id)
);

删除外键约束？

alter table computer drop foreign key c_u_key;

在创建表之后再去添加外键约束？

alter table computer add constraint c_u_key foreign key (uid) references user(id);

事务

什么是事务？

一般是指要做的或所做的事情
事务就是你要做的事

事情有大有小，我们要分得清什么是大事，什么是小事。

事物的大小

什么是大事？

至少需要2条或者以上的sql语句才可以完成的操作，就是大事

什么是小事？

只需要用一条sql语句就可以完成的操作就是小事

举例：

需求：增加一条记录信息

insert into a values(xxx,ooo);

$\rightarrow$小事情

需求：删除一条记录信息

delete from a where id = 1;

$\rightarrow$小事情

需求：修改某条记录的信息

update a set name = xxx where id = xxx;

$\rightarrow$小事情

需求：查询表中的记录信息

select * from a;

$\rightarrow$小事情

需求：转账：tom给jerry转了100元钱

create table user (
    id int primary key auto_increment,
    name varchar(20),
    money int
);

insert into user values(1,'tom',1000);
insert into user values(2,'jerry',1000);

需求：转账：tom给jerry转了100元钱

update user set money = 900 where name = 'tom';
update user set money = 1100 where name = 'jerry';

$\rightarrow$大事情

事务处理：

方案一：

start transaction; 开启事务
commit;            提交事务
rollback;          回滚事务

常见的组合：

start transaction;
commmit;

start transaction;
rollback

方案二：

select @@autocommit; 查看当前的提交方式是自动还是手动
    1：自动提交
    0：手动提交
set @@autocommit = 0;

事务的四大特性：

1. 原子性：原子是化学反应中最小的粒子，不能再分割了
2. 持久性：就是指通过事务操作完的数据，也是永久的存储，永久的改变
3. 一致性：在使用事务操作的时候，操作前和操作后的数据是一致的
4. 隔离性：事务与事务之间是没有关系，互相隔离，互相不干扰的

但是，有的时候还是会打破隔离性，当多个事务并发操作同一张表的时候，就会打破隔离性

如果隔离性被打破就会产生一些问题？

1. 脏读： 事务A 读到了 事务B 的未提交的数据
2. 不可重复读：事务A 读到了 事务B 已经提交后的修改(update)的数据
3. 虚读(幻读)：事务A 读到了 事务B 已经提交后的增加(insert)或者减少的数据

事务的隔离级别？

1. read uncommitted：设置这个隔离级别，上面的三个问题都会发生
2. read committed：设置这个隔离级别，能够解决脏读，但是会产生幻读和不可重复读Oracle数据库的默认隔离级别
3. repeatable read：设置这个隔离级别，能够解决脏读，不可重复读，但是x会产生幻读MySql数据库的默认隔离级别
4. serializable：设置这个隔离级别，都能够解决

查看当前的隔离级别：

select @@tx_isolation;x

修改隔离级别：

set global transaction isolation level ???;

视图

什么是视图？

视图是一张虚拟的表，在数据库中不是真实存在的

视图有什么用？

可以将一些复杂的查询语句通过封装成视图，方便我们后续的查询操作

一般是查询次数比较频繁，同样的需求经常出现，就可以封装为视图

例子：用户表和电脑表

需求：查询名字叫tom的用户拥有哪些电脑，将用户名和所拥有的电脑名给展示出来。

select u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';

如何创建视图？

create view 视图名 (列名, ...) as 复杂的查询语句;

create view user_computer (user_name, computer_name) as
select u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';

修改视图的内容？

update user_computer set computer_name = 'lenovo' where computer_name = 'xiaomi';

修改视图的结构？

alter view user_computer (user_name, cname) as
select u.name, c.name from user u, computer c where u.id = c.uid and u.name = 'tom';

删除视图？

drop view user_computer;

索引

什么是索引？

是帮助MySql数据库高效获取数据的一种数据结构，所以说索引的本质就是数据结构

数据库管理系统平时除了要管理表数据的各种操作，要还维护索引，因为索引是数据结构，里面涉及到了一些高级算法，这些高级算法通过某些方式指向了数据表中的某些列

索引的分类？

按照功能分类？

1. 普通索引
2. 主键索引(一张表的主键自动添加主键索引)
3. 外键索引(一张表的外键自动添加外键索引)
4. 联合索引
5. 全文索引
6. 唯一索引

按照结构分类？

1. BTree索引(底层B + Tree数据结构)
2. Hash索引

索引的操作？

1.查看索引

show index from user;

2.创建索引

create index idx_name on user(name);

3.删除索引

drop index idx_name on user;

JDBC

基本操作

0.导入jar包

为什么要jar包？

因为我们用java语句操作数据库，具体的实现类和方法都在jar里呢，JDBC只是提供一些接口

1.注册驱动

Class.forName("com.mysql.jdbc.Driver");

版本较高时可能会出现错误，不妨试试

Class.forName("com.mysql.cj.jdbc.Driver");

解释：将com.mysql.jdbc.Driver.class文件加载进内存方法区中，形成字节码文件对象

为什么要注册驱动？

告诉程序要操作的数据库是哪一个数据库

注册驱动的原始代码：DriverManager.registerDrvier(new Driver());

按照原始写法有两点不好的地方：

1. 太过依赖于jar包，如果jar不存在，编译的时候就报错了
2. 会造成2次注册

2.获取数据库连接对象

Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db6", "root", "123");

三个参数：

1.第一个参数：jdbc:mysql://localhost:3306/db6

jdbc:mysql	localhost	3306	db6
协议	IP地址	端口	数据库名

2.第二个参数：root 用户名

3.第三个参数：123 密码

有几个重载的方法：

public static Connection getConnection(String url,String user, String password)

public static Connection getConnection(String url)
    //jdbc:mysql://localhost:3306/db6?user=root&password=123

public static Connection getConnection(String url,java.util.Properties info)
    //Properties p=new Properties();
    //p.setProperty("user","root");
    //p.setProperty("password","123);

注意：如果ip是localhost，端口是3306的话，第一个参数可以简写为：jdbc:mysql:///db6

3.获取执行sql语句的对象

Statement stat = conn.createStatement( );

后面要修改写法，因为上面的写法是有弊端的

PreparedStatement ps = conn.prepareStatement("sql语句");

使用PreparedStatement能够解决sql注入问题，因为它采用的是预处理模式，里面使用的是占位符“？”

4.执行sql语句

int i = stat.executeUpdate("insert into user values(1,'tom', 18)");
System.out.println(i);

int executeUpdate()：增删改

返回值：实际影响的行数

ResultSet executeQuery()：查询

返回值：结果集对象

ResultSet：

boolean next()：判断结果集里有没有数据
int getInt(int i)：根据第几个字段获取对应的字段的数据
int getInt(String name)：根据指定的字段名获取对应的字段数据
String getString(int i)：根据第几个字段获取对应的字段的数据
String getString(String name)：根据指定的字段名获取对应的字段数据

boolean execute()：增删改查，底层使用的，我们自己用的很少

返回值：

增删改 返回false
查询   返回true

5.释放资源

rs.close();//查询的要释放
stat.close();
conn.close();

注意事项

1. 不知道什么原因，没有主键的表是read-only的状态，但是可以删除，建议以后创建所有的表都加一个主键
2. 我使用的MySQL版本为8.0，connector也是相应的版本，注册驱动时要用Class.forName(“com.mysql.cj.jdbc.Driver”);不过他会自动帮你改的

参考：蜗牛学院 Java开发全套1300集