渣渣白的MySQL笔记

Oracle：甲骨文公司，收购了sun 公司，sun公司收购了Mysql公司。大型电商企业
Mysql:中小型企业，收购之前开源免费
Mariadb:mysql创始人创建，所有命令语句基本与Mysql相同
db2:银行
SQL Server:教学，政府，图形化界面

SQL：structure qurery language结构化查询语言
DDL:数据库定义语言,数据库表结构create、drop、alter
DML：数据库操纵语言，操作表数据insert、delete、update
DCL：数据库控制语言，设置用户的访问权限、安全
DQL数据库查询语言 select、from、where
数据库操作
创建：
create database 数据库名
Create database 数据库名 character set 字符集 --指定字符集utf8、gbk
Create database 数据库名 character set 字符集 collate utfb_bin
删除：
drop database 数据库名
修改：
alter database 数据库名 character set 字符集
查看：
show databases
Show create database 数据库名
Select database() --查看当前使用的数据库
切换数据库：use 数据库名
表结构的操作
创建：
create table 表名(
列名 列的类型 列的约束
列名 列的类型 列的约束
)
列的类型：
int char/varchar(固定长度/可变长度) double float boolean
date(YYYY-MM-DD)
time(hh:mm:ss)
datatime(YYYY-MM-DD hh:mm:ss)默认值是null
timestamp(YYYY-MM-DD hh:mm:ss)默认值是当前时间
列的约束：
primary key主键约束：唯一，不为空，外键指向另外一张表的主键，一张表只有一个主键
Unique 唯一约束：列的内容唯一、不重复，可以为空，一表多个
Not null非空约束
自动增长auto_increment
删除：
drop table 表名
修改：
rename table 旧表名 to 新表名
alter table 表名 character set 字符集
修改列
alter table 表名 add 列名 列的类型 (列的约束)
alter table 表明 modify 列名 列的类型 （列的约束）
alter table 表名 change 旧列名 新列名 列的类型 （列的约束）
alter table 表名 drop 列名
查看
show tables
Show create table 表名 --表的创建语句、表的定义
desc table 表名 --查看表的结构
表中数据的操作
插入
insert into table(列名，列名)values(列值，列值)
删除
delete from table [where语句]
修改
updata 表名 set 列名=值 [where语句]
查询
select [distinct]*[列名，列名]from 表名[where 语句]
as 关键字 别名
where关键词后
关系运算符 > 、>=、 <、<=、!=、<>
Is null、is not null
In()
between and
逻辑运算符 and or not
模糊查询 like

分组Group by
分组之后条件过滤having
聚合函数 sum() avg() min() max() count()
排序Order by(asc 升序 默认 ，desc降序)

多表之间的关系如何来维护：
外键约束 foreign key
添加一个外键 alter table 表名 add foreign key(列名) references 表名(列名)
foreign key(列名) references 表名(列名)
删除的时候，先删除外键关联的所有数据，再删除分类的数据
建表原则
一对多:在多的一方增加一个外键指向一的一方
多对多：将多对多转成一对多的关系，创建一个中间表，至少有两个外键
一对一：不常用，拆表操作用
建表原则：
将两张表合成一张表
将两张表的主键建立关系
将一对一的关系当作一对多的关系处理

– 交叉连接查询，笛卡儿积,结果为两张表的乘积
SELECT * FROM product,category;
SELECT * FROM product,category WHERE pnumber=cid;

– 内连接查询 相对于外连接仅显示两表可相互对应的数据
– 隐式内连接在查询出结果的基础上执行where条件过滤
– 显式内连接带着条件 查结果，执行效率高
SELECT * FROM product AS p,category AS c WHERE p.pnumber=c.cid;-- 隐式内连接
SELECT * FROM product p INNER JOIN category c ON p.pnumber = c.cid;-- 显式内连接

– 外连接查询
– 左外连接会将左表中的数据全部查询出来，若右表无对应数据用NULL代替
– 右外连接会将右表中的数据全部查询出来，若左表无对应数据用NULL代替
SELECT * FROM product;
INSERT INTO product VALUES(NULL,‘耐克’,698,NULL,NULL); – 向左表插入数据
SELECT * FROM product p LEFT OUTER JOIN category c ON p.pnumber = c.cid;-- 左外连接
SELECT * FROM product p RIGHT OUTER JOIN category c ON p.pnumber = c.cid;-- 右外连接

SELECT * FROM category;
INSERT INTO category VALUES(NULL,‘电脑办公’,‘戴尔，联想等’);
SELECT * FROM product p RIGHT OUTER JOIN category c ON p.pnumber = c.cid;-- 右外连接
SELECT * FROM product p LEFT OUTER JOIN category c ON p.pnumber = c.cid;-- 左外连接

– 分页查询
SELECT * FROM product LIMIT 0,3;-- 初始索引为0，每页显示3条
– 子查询
– 查询 分类名称为手机数码的 所有商品
SELECT * FROM product;
SELECT * FROM product WHERE pnumber =(SELECT cid FROM category WHERE cname=‘数码相机’);
– 查询 商品名称 商品分类名称 信息
SELECT p.pname,c.cname FROM product p LEFT OUTER JOIN category c ON p.pnumber=c.cid;-- z左连接
SELECT pname,(SELECT cname FROM category c WHERE c.cid=p.pnumber) FROM product p;

使用JDBC的基本步骤（详见eclipse）
1.注册驱动
2.建立链接
3.创建statement对象
4.执行sql，得到ResultSet
5.遍历结果集
6.释放资源

package com.sdnu.sy;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.Connection;

public class MainTest2 {
public static void main(String[] args) {
//Driver ctrl+t 看到他的继承体系
Connection conn=null;
Statement st=null;
ResultSet rs=null;
try {
//1.注册驱动
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
//2.建立连接 参数一：协议+访问的数据库 参数二：用户名 参数三：密码
conn = (Connection) DriverManager.getConnection(“jdbc:mysql://localhost/student”, “root”, “123”);
//3.创建statement。跟数据库打交道一定需要这个对象
//Statement createStatement = conn.createStatement(); Ctrl1可以用来接对象
st = conn.createStatement();
//4.执行查询,得到结果集
String sql=“select * from student”;
rs=st.executeQuery(sql);

		while(rs.next()) {
			int id=rs.getInt("sid");
			String name=rs.getString("sname");
			int age=rs.getInt("sage");
			System.out.println("id是"+id+"  name是"+name+"   age是"+age);
		}

		
	} catch (SQLException e) {
		// TODO 自动生成的 catch 块
		e.printStackTrace();
	}finally {
		try {
			if(rs != null) {
				rs.close();	
			}
			
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			rs=null;
		}
		try {
			if(st != null) {
				st.close();	
			}
			
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			st=null;
		}
		try {
			if(conn != null) {
				conn.close();	
			}
			
		} catch (SQLException e) {
			// TODO 自动生成的 catch 块
			e.printStackTrace();
		}finally {
			conn=null;
		}
	}
}

}

JDBC工具类构建
1.资源释放工作的整合
JDBCUtil
2.驱动防二次注册
MainTest4
DriverManager.registerDriver(new com.mysql.jdbc.Driver());
Driver类里有静态代码块，一上来就执行了，所以等同注册两次驱动，没有必要
//静态代码块—>类加载了就执行。java.sql.DriverManager.registerDriver(new Driver())
最后形成以下代码即可
Class.f orName(“com.mysql.jdbc.Driver”);
3.使用properties配置文件
(1)在src中声明一个文件，命名为xxx.properties.其中内容如下
driverClass=com.mysql.jdbc.Driver
url=jdbc:mysql://localhost/day06
name=root
password=123
(2)JDBCUtil创建静态代码块，读取属性
static {//静态代码块，类一被加载到JDBCUtil中，马上读jdbc.properties文件
try { //抛出异常快捷键Alt+Shit+z
//1.创建一个属性配置对象
Properties properties =new Properties();
//InputStream is = new FileInputSteam(“jdbc.properties”);
//对应文件位于项目工程中
//对应文件位于src目录底下
InputStream is = JDBCUtil.class.getResourceAsStream(“jdbc.properties”);
//导入输入流
properties.load(is);
//读取属性
driverClass = properties.getProperty(“driverClass”);
url = properties.getProperty(url);
name = properties.getProperty(name);
password = properties.getProperty(password);
} catch (IOException e) {
// TODO 自动生成的 catch 块
e.printStackTrace();
}
}
4.使用单元测试，测试代码
(1)定义一个类，TestXxx，里面定义方法testXxx
(2)支持
右键项目名称 构建路径 添加库 JUnit