MySQL数据库

数据库的基本操作

//创建数据库
    create Database school
//删除数据库
    drop Database school
//创建表
//主键（primary key ）  能够代表该条数据的唯一性
//not null 指定给数据段不能为空
//int(11) 指明数据的长度
    create table student(id int(11) primary key ,user_name varchar(30) not null,user_password varchar(30))
//删除表
    drop table student
//表中新添加字段
    alter table student add(age int(3) not null)
//表中删除字段
    alter table student drop age
//向表中字段增加一条数据
    insert into student(id,user_name,user_password)values(4,'阿毛',789456)
//删除数据，将所有匹配的数据删除
    delete from student  where user_name='阿毛'
//查询地址是大连或者北京的学生的信息   in关键字
    select * from student where address in("大连","北京")
//查询地址为北京的学生的信息
    select * from student where address="北京"
//查询地址为北京且学生姓名为王五的学生信息    and关键字
    select * from student where address="北京" and sname="王五"
//查询地址为成都或者学生名字叫王五的所有学生信息  or关键字
    select * from student where address="成都" or sname="王五"
//创建新表，text表示文本字段
    create table cookbook(id int(10) primary key auto_increment,sname varchar(10),ing text)
//查询在ing字段内容包含有肉的所有信息  like，模糊查询 %表示任意长度的字符串  _表示一个字符
    select * from cookbook where ing like "%肉%"
//聚合函数 sum  avg min max count计数
    select sum(score) from scores where score>=60

    select avg(score) from scores where score>=60

    select max(score) from scores 

    select min(score) from scores

    select count(*) from scores
//as将查询的显示的表的相应字段值改为as后面的字符串 group by按照by后面的字段进行分组查询
    select count(*) as 人数,class as 班级 from scores group by class

    select sum(score),class from scores group by class
//having 在查询的结果中在添加限定，下面是只显示结果中平均值大于70的数据
    select avg(score),class from scores group by class having avg(score)>=70
//order by 按照by后面的字段进行排序  desc降序  asc升序
    select * from scores order by score desc
//分页查询，limit num 只显示查询结果的前num个结果
    select * from scores order by score desc limit 2
//limit num offset k 只显示从第k条结果开始的num条结果
    select * from scores order by score desc limit 2 offset 4
//子查询，查询在1班中成绩大于2班王五成绩的学生信息
    select * from scores where class="1" and score>(select score from scores where sname="王五" and class="2")

//更新数据，将密码为123456的学生的姓名改为李四
    update student set sname='李四' where sword='123456'

约束概述

JDBC

通过JDBC连接eclipse和MySQL数据库

• 加载JDBC驱动
• 使用驱动程序DriverManager得到数据库的连接Connection
• 得到发送sql语句到mysql的Statement对象。Statement对象是执行静态sql语句，并将结果返回

Java代码

public class MySQLTest {
    public static void main(String[] args) {
        try {
            //第一步  加载JDBC驱动
            Class.forName("com.mysql.jdbc.Driver");
            System.out.println("驱动加载成功");
            //使用驱动程序DriverManager得到数据库的连接Connection
            Connection conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/school", "root", "123456");
            //第三步 得到发送sql语句到mysql的Statement对象。Statement对象是执行静态sql语句，并将结果返回
            Statement statement=conn.createStatement();
            //第四步 执行sql语句
            String sql="insert into student (user_name,user_password,address)values('zhangsan','223344','兰州')";
            statement.execute(sql);
            ResultSet set=statement.executeQuery("select * from student");
            while (set.next()) {
                System.out.println("姓名："+set.getString("user_name")+
                        "   密码："+set.getString("user_password")+"   地址："+set.getString("address"));
            }

预处理语句

• 一般执行的sql语句使用PreparedStatement预编译命令，预编译命令可防止输入的字符串中包含sql语句关键字而出现错误

    PreparedStatement statement=conn.prepareStatement("select * from student where user_name=? and user_password=?");
    //指明第一个问号和第二个问号所代表的值
    statement.setString(1, name);
    statement.setString(2, password);

元数据

• 即为每一列的字段值，也就是列的名称

PreparedStatement statement=conn.prepareStatement
            ("select * from student");
            ResultSet set=statement.executeQuery();
            //获取元数据，就是获取列的名称
            ResultSetMetaData data=set.getMetaData();
            //获取列的长度
            System.out.println(data.getColumnCount());
            set.first();
            for (int i = 0; i < data.getColumnCount(); i++) {
                //mysql中，列的计数是从1开始的，与java不同
                System.out.println(data.getColumnName(i+1));
                System.out.println(data.getColumnType(i+1));
            }

批处理语句

• 可实现一次录入多个数据的条目，然后统一执行插入的语句，不必每插入一条数据就执行一次sql语句

            //批处理命令
            statement.setString(1, "阿毛");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            //添加到批处理命令
            statement.addBatch();
            statement.setString(1, "阿毛");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            statement.addBatch();
            statement.setString(1, "阿毛");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            statement.addBatch();
            statement.setString(1, "阿毛");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            statement.addBatch();
            //执行批处理命令
            statement.executeBatch();

事务

• 事务语句具有原子性，一致性，特点是事务中的sql语句如果执行的话就全部执行，如果其中任意一条sql语句执行失败，那么就全部不执行

//事务，要么全执行，要么都不执行
            //关闭自动提交
            conn.setAutoCommit(false);
            statement.setString(1, "阿毛1");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            statement.execute();
            statement.setString(1, "阿毛2");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            statement.execute();
            statement.setString(1, "阿毛3");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            //特意添加的错误语句
            statement.setString(4, "石家庄");
            statement.execute();
            statement.setString(1, "阿毛4");
            statement.setString(2, "123456");
            statement.setString(3, "石家庄");
            statement.execute();
            //执行提交操作
            conn.commit();
            //事务执行完毕后，打开自动提交
            conn.setAutoCommit(true);

数据库连接池的配置

• 数据库连接池配置在WEB项目中，也就是在服务器端，用于服务器与数据库之间的数据处理

如何创建连接池

1. 安装mysql 安装tomcat 将jdbc Driver的jar放置到tomcat的目录下的lib文件夹下
2. 创建数据库，在数据库中添加数据
3. 配置tomcat的context.xml。一种是全局配置，二是在web项目中配置，放置在meta

4. 配置web.xml文件

   • 第三步的Contex.xml配置文件放置在META-INF文件夹下

<Context>
//需要自己修改的内容有数据库的用户名，密码，要访问的数据库名称
//默认设置了最大连接数，最小连接数，等待超时时间
  <Resource name="jdbc/TestDB" auth="Container" type="javax.sql.DataSource"
               maxTotal="100" maxIdle="30" maxWaitMillis="10000"
               username="root" password="123456" driverClassName="com.mysql.jdbc.Driver"
               url="jdbc:mysql://localhost:3306/people"/>


</Context>

• 配置web文件在WEB-INF文件夹下，在创建项目时选择自动生成即可,在其中相应位置（后面）添加代码即可，不用修改任何地方

<description>MySQL Test App</description>
  <resource-ref>
    <description>DB Connection</description>
    <res-ref-name>jdbc/TestDB</res-ref-name>
    <res-type>javax.sql.DataSource</res-type>
    <res-auth>Container</res-auth>
  </resource-ref>

• 自己新建一个工具包，配置获取connection对象的类，使用单例模式
• 注意导包，全都是sql和naming的包，这点要注意
  

//单例模式的代码
    public static ConnectionFactory factory;
    private ConnectionFactory() {
    }
    public static synchronized ConnectionFactory newInstance() {
        if (factory == null) {
            factory = new ConnectionFactory();
        }
        return factory;
    }
    //获取connection连接对象的方法
    public Connection creat() {
        Connection connection = null;
        try {
        //下面三句话是配置连接池的固定写法
            Context context = new InitialContext();
            DataSource dataSource = (DataSource) context.lookup("java:comp/env/jdbc/TestDB");
            connection = dataSource.getConnection();
        } catch (NamingException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    //最后将创建的connection对象返回
        return connection;
    }
}