2.1-MySQL

MySQL

1.操作数据库

create database [if not exists] dbName;
drop database [if exists] dbName;

use dbName;
--若含有特殊字符使用 `dbName`

show databases;

1.1 数据类型

• 数值
  • tinyint 1B
  • smallint 2B
  • mediumint 3B
  • int 4B
  • bigint 8B
  • float 4B
  • double 8B
  • decimal 字符串形式浮点数（金融场景）
• 字符串
  • char 固定大小 0~255
  • varchar 变长 0~65535 （常用变量）
  • tinytext 微型文本 2^8-1
  • text 文本串 2^16-1 (大文本)
• 时间日期
  • date YYYY-MM-DD
  • time HH: mm: ss
  • datetime YYYY-MM-DD HH: mm: ss
  • timestamp 时间戳 1970-1-1 00:00:00 ~
  • year
• null

1.2 数据库字段属性

• Unsigned 无符号整数
• zerofill 0填充
• 自增
• not null
• 默认

---

/*
id  主键
`version`  乐观锁
is_delete  伪删除
gmt_create  创建时间
gmt_update  修改时间
*/

2.3 create table

create table if not exists `student`(
    `id` int(4) not null auto_increment comment '学号',
    `name` varchar(30) not null default '匿名' comment '姓名',
    primary key(`id`)
)engine=InnoDB default charset=utf8

--常用命令
show create database dbName --查看创建数据库的语句
show create table tName --查看创建tName表的定义语句
desc tName --查看表结构

2.4 数据表的类型

--数据库引擎
/*
InnoDB  默认使用
MyIsam
*/

2.5 修改删除表

alter table tName rename as newName;

alter table tName add age int(11); --添加字段

alter table tName modify age varchar(11); --修改约束

alter table tName change age age1 int(11); --重命名

alter table tName drop age1 --删除字段

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

2.MySQL数据管理

2.1 外键

--创建表时添加
constraint `fk_name` foreign key (`sid`) references `dbName`.`tName`(`id`)

--
alter table `tName` add constraint `fk_name` foreign key (`sid`) references `dbName`.`tName`(`id`);

2.2 DML

数据操纵语言

• insert
• update
• delete

2.3 添加

insert into `tName`(`field_i`,`field_j`) values('fi_value','fj_value'),('fi_value2','fj_value2');

2.4 修改

update `tName` set `field`='value'[,`field2`='value2'] [where condition];

--逻辑运算
=
<> 或 !=
>
<
<=
>=
between a and b    -- >=a and <=b
and
or

2.5 删除

delete from `tName` [where condition];

--truncate 完全清空一个数据库表，表的结构和索引约束不变
truncate `tName`;

/*
truncate 重新设置自增列，计数器会清零
truncate 不会影响事务
*/

3.DQL查询

select fields from tName where condition;
-- as 起别名
-- concat(a,b)拼接字段值
select concat("name:",name) as 'newName' from student as 's';

--distinct
select distinct `stuNo` from test;

--查询系统版本
select version();

--计算
select 100*3-1 as result;

--查询自增的步长
select @@auto_increment_increment;

---

数据库中的表达式：文本值，列，null，函数，计算表达式，系统变量

select 表达式 from 表

where

--逻辑运算符
&&        and
||        or
!=        not

--模糊查询
is null     -- a is null (if a is null --> true)
is not null
between a and b
like
in

Join

inner join [on condition]

left join [on condition]

right join [on condition]

自连接

select a.name, b.name from tA as a, tb as b where a.id=b.pid;

分页和排序

limit

order by

select
from
[where]
[limit]
[order by]

--
order by fieldName [desc/asc]

--
limit 起始值,页大小

子查询

函数

--
abs()
ceiling() --向上取整
floor() --向下取整
rand() --0~1随机数
sign() --返回参数符号 -1 0 1

--
char_length()
concat()
insert()
lower()
upper()
instr()
replace()
substr()
reverse()

--日期时间
current_date() --当前日期
curdate()
now() --当前时间
localtime()
sysdate()
year()

--系统
system_user()
user()
version()

--聚合
count()
/*
count(fieldName) 忽略null值
count(*) 不会忽略null值
count(1) 
*/
sum()
avg()
max()
min()

MD5

md5()

4.事务

ACID

• 原子性
• 一致性
• 持久性
• 隔离性

5.索引

主键索引 primary key
唯一索引 unique key
常规索引 key/index
全文索引 fulltext

--
show index from tName

alter table tName add fulltext index indexName(fieldName)

create index indexName on tName(fieldName)

explain sql执行语句 --分析SQL执行情况

6.权限管理和备份

--创建用户
create user uName identified by pwd;
--修改当前用户密码
set password = password(newPwd);
--修改指定用户密码
set password for uName = pasword(newPwd);
--重命名
rename user oldName to newName
--用户授权
grant all privileges on *.* to uName; --除grant权限的所有权限，对所有数据库所有表
--
show grant for uName@localhost
--撤销权限
revoke all privileges on *.* from uName;
--删除用户
drop user uName;

MySql备份

• 直接拷贝物理文件

• 可视化工具导出

• 命令行 mysqldump

  mysqldump -hlocalhost -uroot -p1234 dbName [tName1 tName2 ...] > D:/a.sql
  
  --导入
  source D:/a.sqp --登录，use指定数据库
  mysql -uroot -p1234 dbName< D:/a.sql
  

7.规范数据库设计

1. 需求分析
2. 概要设计：E-R图，关系图

需求工程包括7项明确的任务

1.起始，建立基本的理解，包括存在的问题，谁需要解决方案，所期望解决方案的性质，与项目利益相关者和开发人员之间达成初步交流合作的效果

2.获取，询问客户，用户和其他人：系统或产品的目标是什么，想要实现什么，系统和产品如何满足业务的要求，最终系统或产品如何用于日常工作。建立商业目标

3.细化，该任务核心开发一个精确的需求模型，用以说明软件的功能、特征和信息的各个方面

4.协商，调节需求冲突

5.规格说明

6.确认，质量评估。

7.需求管理，标识，控制和跟踪需求以及需求变更的一组活动

8.JDBC

mysql-connector-java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class Test {

	public static void main(String[] args) throws ClassNotFoundException, SQLException {
		//1.加载驱动
		Class.forName("com.mysql.cj.jdbc.Driver"); //高版本
		 
		//2.用户信息 url
		 String url = "jdbc:mysql://localhost:3306/stu?useUnicode=true&characterEncoding=utf8&useSSL=true&serverTimezone=Asia/Shanghai";//需要时区参数
		 String userName = "root";
		 String userPwd = "pwd";
		 
		//3.连接，数据库对象
		Connection conn = DriverManager.getConnection(url,userName,userPwd);
		
		//4.Statement
		Statement statement = conn.createStatement();
        //PreparedStatement 防止SQL注入，效率高
        /**
        //?占位符
       	String sql = "insert into users(`id`,`name`) values(?,?)";
       	//预编译sql,不执行
        PreparedStatement pst = conn.preparedStatement(sql);
        //给参数赋值
        pst.setInt(1,4); //第一个占位符设置参数为 int 4，从1开始数
        pst.setString(2,"name");
        //执行
        int i = pst.executeUpdate();//i>0 成功
        //ResultSet rs = pst.executeQuery();
        
        **/
		
		//5.执行sql
		String sql = "select * from student";
		ResultSet result = statement.executeQuery(sql);
		while(result.next()) {
			System.out.println(result.getObject("sid")+" "+result.getObject("sname"));
		
		}
		
		//6.释放连接
		result.close();
		statement.close();
		conn.close();
	}

}

---

//事务
try{
  
    conn.setAutoCommit(false);//关闭自动提交，自动开启事务
    //execute sql
    conn.commit();//提交
} catch(SQLException e){
    try{
        conn.rollback();//异常，回滚
    }catch(SQLException e1){
        
    }
} finally{
   	//释放资源
}

---

数据库连接池

• 数据库连接释放耗费资源

池化技术：预先准备资源，等待使用

编程实现：实现接口 DataSource

开源数据源实现：

• DBCP
• C3P0
• Druid ： 阿里