MySQL 增加创建两个root mysql创建两个表

转载

mob64ca1415f0ab 2023-10-28 21:02:39

文章标签 MySQL 增加创建两个root mysql 连接2表数据 sql java System 文章分类 MySQL 数据库

MySQL笔记2

1.如何创建带有外键的表

两种方式，一是创建表时，就在字段后面添加外键约束的语法声明

如：

主表：

create table A(

A_id...

...

);

从表：

create table B(

字段1 类型 primary key [auto increament],

字段2 类型,

B_id 类型，

constraint [约束名] foreign key 从表B(B_id) references

主表A(A_id)

);

二是，在创建好的表之后，在额外用语句添加外键约束

如：

alert table B add [constraint] [外键名_fk] foreign key 从表B(B_id)

references 主表A(A_id);

2.表的主键及从表的外键如何删除

主键删除：(前提是主键没有自增长约束(即没有auto increament))

alter table 表名 drop primary key;

从表外键删除：

alter table 从表B drop foreign key 外键名_fk;

3.三个关联查询

3.1多表的关系：一对一，一对多，多对多

注意：

1.从表无法添加或更新主表中不存在的数据

2.主表无法删除或更新从表中已经使用的数据

3.2如何查询：

1.交叉查询--得笛卡尔积图表--一般不用

select * from A,B [条件]

2.内连接查询--可以跟自己表连接

隐式：select * from A,B where 条件

显示：select * reom A inner join B on 条件

3.外连接查询

左连接：select * from A left join B on 条件

右连接：select * from A right join B on 条件

3.3子查询--就是嵌套--查询的结果作为条件或表再使用

1.单行子查询，也就是先查询的结果是一个值，可以将其作为比较条件，再次参与查询

2.多行子查询，第一次查询的结果相当于一个数组，可以将其作为比如in条件，再次查询

3.多行多列子查询，第一次查询的结果相当于一个表，将其作为表，再参与查询，往往需要as一个别名

4.三种常用常见的数据库连接池的使用规则及方法

4.1Apache的dbcp/pool提供的jar包，并创建键值名规范的xx.properties配置文件，结合mysql-connector的jar包

4.2c3p0提供的c3p0的jar包，结合规范格式的c3p0-config.xml配置文件

4.3Apache的dbutils工具类，将数据库查询结果集封装成9种常见格式，重要的两个东西就是QueryRunner和ResultSetHandler(对ReSultset封装成了9个常用结果集)，但是事前必须先构造一个属性名与数据库字段名相同的类，用来接收查询结果集，这里主要区别上面两个，这个是用他生成的数据池，然后提供给QueryRunner使用，而不是用connection

5.针对4的三个非常有用和常用的例子，可以直接使用

5.1

package com.dou;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import javax.sql.DataSource;
import org.apache.commons.dbcp.BasicDataSourceFactory;
public class DBCPFactoryDemo {
public static void main(String[] args) throws Exception
{
// TODO Auto-generated method stub
//3.加载属性文件，并创建数据池对象
FileInputStream fins = new
FileInputStream("dbcp.properties");
Properties prop = new Properties();
prop.load(fins);
DataSource dataSource =
BasicDataSourceFactory.createDataSource(prop);
Connection con = dataSource.getConnection();
String sql = "select * from emp where deptno=?";
PreparedStatement pst = con.prepareStatement(sql);
try {
pst.setInt(1, 10);//【索引从1开始】
ResultSet res = pst.executeQuery();
while(res.next()){
//【索引从1开始getXxx(1,2,3..)】或者getXxx("字段名")
System.out.println(res.getObject(1)+"-"+res.getObject("ename")+"-"+res.getString(3)+"-"+res.getObject("mgr"));
}
con.close();
res.close();
fins.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
dbcp.properties:

5.2

package com.dou2.c3p0;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class C3P0Utils {
private static ComboPooledDataSource ds = new
ComboPooledDataSource();
public static Connection getConnection(){
Connection con = null;
try {
con = ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
//提供连接池的--Apache的dbutils会用到
public static DataSource
getDataSource(){//提供连接池的--Apache的dbutils会用到
return ds;
}
//提供链接的方法
public static void closeResource(Connection con,Statement
stat,ResultSet res){
if(con!=null){
try {
con.close();//将该连接还给数据池ds
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
con = null;
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
stat = null;
}
}
if(res!=null){
try {
res.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
res = null;
}
}
}
}
c3p0-config.xml:
package com.dou2.c3p0;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class c3p0Demo {
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection con = C3P0Utils.getConnection();
String sql = "select * from emp where deptno=?";
try {
PreparedStatement pst = con.prepareStatement(sql);
pst.setInt(1, 10);//【索引从1开始】
ResultSet res = pst.executeQuery();
while(res.next()){
//【索引从1开始getXxx(1,2,3..)】或者getXxx("字段名")
System.out.println(res.getObject(1)+"-"+res.getObject("ename")+"-"+res.getString(3)+"-"+res.getObject("mgr"));
}
con.close();
res.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
5.3
package com.dou.dbutils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.sql.DataSource;
import com.mchange.v2.c3p0.ComboPooledDataSource;
public class ApacheDBUtils {
private static ComboPooledDataSource ds = new
ComboPooledDataSource();
public static Connection getConnection(){
Connection con = null;
try {
con = ds.getConnection();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
return con;
}
//提供连接池的--Apache的dbutils会用到
public static DataSource
getDataSource(){//提供连接池的--Apache的dbutils会用到
return ds;
}
//提供链接的方法
public static void closeResource(Connection con,Statement
stat,ResultSet res){
if(con!=null){
try {
con.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
con = null;
}
}
if(stat!=null){
try {
stat.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
stat = null;
}
}
if(res!=null){
try {
res.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}finally{
res = null;
}
}
}
}
User:
package com.dou.dbutils;
public class User {
private int uid;
private String uname;
public User() {
super();
// TODO Auto-generated constructor stub
}
public User(int uid, String uname) {
super();
this.uid = uid;
this.uname = uname;
}
public int getUid() {
return uid;
}
public void setUid(int uid) {
this.uid = uid;
}
public String getUname() {
return uname;
}
public void setUname(String uname) {
this.uname = uname;
}
@Override
public String toString() {
return "[uid=" + uid + ", uname=" + uname + "]";
}
}
Demo:
package com.dou.dbutils;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import
org.apache.commons.dbutils.handlers.BeanListHandler;
import
org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import
org.apache.commons.dbutils.handlers.MapListHandler;
import
org.apache.commons.dbutils.handlers.ScalarHandler;
public class DBUtilsHandlerDemo {
public static void main(String[] args) {
// TODO Auto-generated method stub
DBUtilsHandlerDemo demo1 = new DBUtilsHandlerDemo();
System.out.println("-------One---------");
demo1.queryOne();
System.out.println("--------Many--------");
demo1.queryMany();
System.out.println("--------OneMap--------");
demo1.queryOneMap();
System.out.println("--------ManyMap--------");
demo1.queryManyMap();
System.out.println("--------Scalar--------");
demo1.queryScalar();
System.out.println("--------Columns--------");
demo1.queryColumns();
}
public void queryOne(){
QueryRunner qr = new
QueryRunner(ApacheDBUtils.getDataSource());
String sql = "select * from user where uid=?";
Object[] objs = {1};
try {
User user = qr.query(sql, new
BeanHandler(User.class),objs);
System.out.println(user.toString());
System.out.println(user.getUid()+"->"+user.getUname());
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void queryMany(){
QueryRunner qr = new
QueryRunner(ApacheDBUtils.getDataSource());
String sql = "select * from user";
try {
List users = qr.query(sql, new
BeanListHandler(User.class));
for (User user : users) {
System.out.println(user.toString());
System.out.println(user.getUid()+"->"+user.getUname());
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void queryOneMap(){
QueryRunner qr = new
QueryRunner(ApacheDBUtils.getDataSource());
String sql = "select * from user where uid=?";
Object[] objs = {1};
try {
Map one = qr.query(sql, new MapHandler(),objs);
System.out.println(one.toString());
System.out.println(one.get("uid")+"->"+one.get("uname"));
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void queryManyMap(){
QueryRunner qr = new
QueryRunner(ApacheDBUtils.getDataSource());
String sql = "select * from user";
try {
List> list = qr.query(sql, new
MapListHandler());
for (Map user : list) {
System.out.println(user.toString());
System.out.println(user.get("uid")+"->"+user.get("uname"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void queryScalar(){
QueryRunner qr = new
QueryRunner(ApacheDBUtils.getDataSource());
String sql = "select count(*) from user";
try {
Object num = qr.query(sql, new ScalarHandler());
System.out.println("查询到了数据条数："+num);
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
public void queryColumns(){
QueryRunner qr = new
QueryRunner(ApacheDBUtils.getDataSource());
String sql = "select uname from user";
try {
Listlist = qr.query(sql, new
ColumnListHandler("uname"));
for (Object obj : list) {
System.out.println(obj.toString());
System.out.println(obj);
System.out.println("--");
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。