Day11 学习Mysql的基本使用

学习Mysql的基本使用

Mysql是关系型数据库，使用Mysql，本质是操作Mysql中的表里面的字段和表与表之间的关系，就是使用SQL语句，查出数据。

一些基础命令使用

1. 连接数据库： mysql -uroot -p密码
2. 查询所有数据库 ： show databases;
3. 注：所有sql语句要用分号(;)结束，单行注释使用 – 多行注释 /* */
4. 使用某个数据库 ： use 数据库名;
5. 查询当前数据库的所有表： show tables;
6. 显示当前数据库某个数据表的所有信息： describe 数据表名;
7. 创建一个数据库： create database 数据库名;

操作数据库 [] 表示可选

1. 创建数据库

create database [if not exists] 数据库名

2. 删除数据库

drop database [if exists] 数据库名

3. 使用数据库

use 数据库名

4. 查看所有数据库

show databases;

5. 查看创建数据库的语句

show create database 数据库名;

数据库的字段类型和属性

1. 数值
   1.1 tinyint 1个字节
   1.2 smallint 2个字节
   1.3 int（常用） 4 个字节
   1.4 bigint 8个字节
   1.5 float 浮点数 4个字节
   1.6 double 浮点数 8个字节
   1.7 decimal 字符串形式的浮点数
2. 字符串
   2.1 char 字符串固定大小 0-255
   2.2 varchar（常用） 可变字符串 0-65535
   2.3 tinytext 微型文本 2^8 - 1 0-255
   2.4 text 文本串 2^16-1 0-65535
3. 时间日期
   3.1 日期 date YYYY-MM-DD
   3.2 时间 time HH:mm:ss
   3.3 日期时间 datetime YYYY-MM-DD HH:mm:ss
   3.4 时间戳 timestamp 1970.1.1 到现在的毫秒数
4. null 空值
5. 字段属性
   5.1 Unsigned 无符号的（非负数）
   5.2 zerofll 0填充 例 int(3) 写5 数据库存 005
   5.3 自增 默认在上一条的基础上加1
   5.4 not null 非空 不填写就会报错 null 不填写默认为 null
   5.5 default 设置默认的值 不填写时生效

操作数据表

1. 创建表

-- `表名/字段名`  防止与关键字 冲突   [ ] 的内容表示可选 
-- create table [if not exists] 表名( 字段名 列类型 [属性][索引][注释],...)[表类型][字符集设置][注释]

create table [if not exists] `表名`(
		`id` int(4) not null auto_increment comment '注释',    -- 字段名 数据类型 属性
		`name` varchar(25) not null default '佚名' comment '..',
		`pwd` varchar(20) not null default '123'  comment '..',
		primary key (`id`)               -- 设置主键（一个表只有一个）		  
)engine=innodb default charset=utf8;	-- 设置搜索引擎 和 字符集

2. 查看数据表的创建语句

show create table 表名;

3. 查看表的结构

desc 数据表名;

4. 修改表

-- 修改表名
alter table 原表名 rename as/to 新表名;

-- 增加一个字段
alter table 表名 add 新增字段名 字段类型 [属性]

-- 修改字段类型和约束
alter table 表名 modify 字段名 类型;

-- 字段重命名（也可以修改类型和约束）
alter table 表名 change 旧字段名 新字段名 类型

-- 删除某个字段
alter table 表名 drop 字段名

-- 删除表
drop table [if exists] 表名

外键

1. 创建表时添加外键 注意外键的字段类型要

create table `表名`(
	`id` int(10) not null auto_increment comment '学号',
	`name` varchar(12) not null comment '姓名',
	`gradeid` int(10) not null comment '年级id',
	`sex` varchar(2) not null default '男' comment '性别',
	primary key (`id`),
	key FK_gradeid (`gradeid`),  // 定义外键
	constraint FK_gradeid foreign key (`gradeid`) references `另一张表`(`哪个字段`)  // 给外键添加约束  执行引用   
)engine=innodb default charset=utf8;

2. 创建表时不添加外键，表生成后添加外键

create table `user`(
	`id` int(10) not null auto_increment comment '学号',
	`name` varchar(12) not null comment '姓名',
	`gradeid` int(10) not null comment '年级id',
	`sex` varchar(2) not null default '男' comment '性别',
	primary key (`id`)
)engine=innodb default charset=utf8;
-- 添加外键
alter table user add constraint `FK_gradeid` foreign key (`gradeid`) references `另一张表`(`哪个字段`);
-- 删除外键
alter table user drop foreign key FK_gradeid

DML 语句

关键字insert,update,delete

1. 添加（insert）

-- 字段和值一一对应
insert into 表名(字段名1,字段名2,字段名3..) values(值1,值2,值3..);
-- 一下子插入多个值 用()表示一个输入
insert into 表名(字段名1,字段名2,字段名3..) values(值1,值2,值3..),(值1,值2,值3..),(值1,值2,值3..);

2. 更新（update）

-- 不指定条件则指定字段全表修改
update 表名 set 字段名=新值;

-- 通过where设置条件
update 表名 set 字段名=新值 where id=1;  ----where 条件

-- 多个字段设置  ,隔开
update 表名 set 字段名1=新值1,字段名2=新值2,字段名3=新值3 where id=1;

-- 条件
= , <>或!=, >, < >=, <=, between 2 and 5 [2,5], 多个条件用and 或者 or 连接

3. 删除 （delete）

-- 删除符合条件的数据
delete from 表名 where 条件;

-- 不写条件 整张表的数据都删除
delete from 表名;
-- 另一种清空数据
truncate 表名;   

-- 区别 ： delete from 清空表，自增量会从没删除的最后一个开始
--        truncate 清空表，自增量 从 1 开始 

DQL 语句

-- 1. 查询某张表的所有数据
select * from 表名;
-- 2. 查询某张表的所有数据的某些字段
select 字段1,字段2 from 表名;
-- 3. 对字段重命名
select 字段1 as 别名1, 字段2 as 别名2 from 表名;
-- 4. 拼接字符串的函数 concate
select concat("aaa: " , 字段名) [as 别名] from 表名;

-- 5. 去重  distinct
select distinct 字段名 from 表名; 

where条件语法

1. 逻辑运算符 ： and/&& , or/|| , not/ !

select 字段1,字段2 from 表 where not id = 1;
-- 等价于
select 字段1,字段2 from 表 where id !=/<> 1;

2. 模糊查询：
   is null ， is not null , between c and d , in(1,2,3…) ，like

-- like  结合 %（表示0到任意个字符） _（表示1个字符）
select 字段1,字段2 from 表 where name='李%' -- 查询所有姓李的
select 字段1,字段2 from 表 where name='李_' -- 查询所有姓李的,且后面只有一个字的
-- in 
select 字段1,字段2 from 表 where id in(1,3,5); -- 查询id在1，3，5的信息

联表查询

联表查询的表之间一定有某个字段相同（有关系）

1. inner join 两张表的交集

select 要显示的字段 from 左表 inner join 右表 on 左表.字段=右表.字段 [where 条件];

2. left join

-- 结果以 左表为基准 并上右表 即显示左表的全部数据 右表中没有对应的显示null
select 要显示的字段 from 左表 left join 右表 on 左表.字段=右表.字段 [where 条件];

3. right join —> 与left join 相反

自连接

1. 将一张表当成两张表使用

-- 假定 表为area  , 分成 a , b 两张一样的表
select a.name,b.name  from area as a,area as b where a.id = b.fatherid;

分页和排序

1. 排序 ：order by 升序： asc 降序 ：desc

-- 排序  order by 要排序的字段(多个字段用逗号隔开) 声明是降序还是升序
order by score desc,Math_score desc  -- 先按总分降序，如果相同按照数学成绩降序 

2. 分页：limit 起始位置, 显示条数

limit 0,5;  -- 从第0条开始显示5条   0 1 2 3 4
limit 5,5;  -- 从第5条开始显示5条   5 6 7 8 9 

子查询

1. 将查询的结果当做条件继续查询

select  字段 from  表1 where nid = (select nid from 表2 where nname='xx')

函数

1. 常用函数

-- 时间
select now();
select sysdate();
-- 字符串
select replace()
select substr()

2. 聚合函数（重点）

count()
sum()
avg()
max()
min()

3. 分组 group by 分组的字段

select gender,count(*) from user group by gender

总结

-- select 查询语法  关键字顺序不能变
select [ALL| distinct]
{*|字段1,字段2,...}
from 表A [as 别名]
[left|inner|right join 表B [as 别名] on 交叉条件] (多张表可重复)
[where 条件  筛选需要的]
[group by 字段.. ] (分组)
[having .. ]   (分组后的条件判断)
[order by 字段 desc.. ] 排序
[limit 0,5]  分页

事务（ACID）

1. 原子性（atomicity）：表示一组事务内的操作，要么都成功，要么都失败
2. 一致性（consistency）：表示事务操作前后数据的结果一致
3. 持久性（durability）：事务没有提交则退回开始前，事务一旦提交则不可逆，持久化到数据库
4. 隔离性（isolation）：不同的事务相互隔离，互不影响

隔离级别导致的一些问题：
5. 脏读：表示一个事务中读取了别的事务没提交的数据
6. 不可重复读：表示在事务中，多次读取数据，读取的结果不同
7. 幻读（虚读）：在事务中读到了别的事务新插入的数据

Java 操作 Mysql 数据库

1. java有JDBC对不同的进行统一的封装，由不同的数据库厂商实现各自的驱动
2. Statement对象的代码

// 1. 注册驱动
Class.forName("com.mysql.jdbc.Driver");
// 2.定义数据库的Url username password 获得数据库连接对象
String url = "jdbc:mysql://localhost:3306/mysql_test?useUnicode=true&characterEncoding=utf8&useSSL=true";
String username="...";
String pwd = "...";
Connection conn = DriverManager.getConnection(url,username,pwd);
// 3.获得执行sql的对象
Statement stm = conn.createStatement(); 
// 4.定义sql 并且提交执行
String sql = "select * from user";   // "insert into user(name,gender) values('xxx','男')"

// 查询的时候 用executeQuery           // 增删改操作使用 executeUpdate 返回的值为 sql 执行成功的行数 一般大于0 则操作成功
ResultSet rs = stm.executeQuery(sql);  // int i = stm.executeUpdate(sql)  
// 遍历结果
while(rs.next()){
System.out.println(rs.getString("name"));   // 读取数据库user 表的name 这个字段的数据
}
// 释放资源
rs.close();
stm.close();
conn.close();

3. 由于Statement对象存在sql注入问题，需要使用PreparedStatement

// 需要先定义sql 未知的值用？ PreparedStatement 对象需要预编译
String sql = "select * from user where name=? and password=?";
PreparedStatement psmt = conn.PrepareStatement(sql);
// 对？设置值
psmt.setString("xxx");
psmt.setInt("123456");
// 提交
ResultSet rs = psmt.executeQuery();

4. java 代码中使用事务

try{
	// 关闭自动提交，就会开启事务
conn.setAutoCommit(false);
// 执行代码
// .....

// 代码执行成功，提交事务
conn.commit();
}catch(Exception e){
	// 代码出错，回滚事务
	conn.rollback();  // 也可以不写，默认也会回滚
}finally{
	// 释放资源
}

5. 为了减少数据库连接的频繁创建，可以使用池化技术 常用的DBCP，C3P0 ,Druid，本质实现DataSoure接口

// 1. 编写配置文件
// xxx.propreties 或 xxx.xml
// 2.加载配置文件，返回DataSource对象
// DBCP
InputStream in = jdbcUtils_DBCP.class.getClassLoader().getResourceAsStream("dbcpconfig.properties");
Properties prop = new Properties();
prop.load(in);
DataSource datesource= BasicDataSourceFactory.createDataSource(prop);	

// 3.根据DataSource对象，返回数据库连接对象 Connection对象
Connection conn = datesource.getConnection()

// 之后的数据库操作不变