MySQL学习笔记

一、MySQL基本命令集合：

1、  create database mydata；//创建数据库

2、  use mydata; //在mydata这个数据库下工作

3、  create table dept    //在mydata数据库下创建表dept

(

deptno int primary key,

dname varchar(14),

loc varchar(13)

);

 

create table emp  //在mydata数据库下创建表emp

(

empno int primary key,

ename varchar(10),

job varchar(10),

mar int,

hiredate datetime,

sal double,

comm double,

deptno int,

foreign key (deptno) references dept(deptno)

);

4、  show databases；//显示数据库

5、  show tables；//显示表

6、  desc dept；//显示dept表的结构

7、  insert into dept values（10, ‘A’, ‘A’）；//向dept表中添加记录

   insert into dept values（20, ‘B’, ‘B’）；

   insert into dept values（30, ‘C’, ‘C’）；

   commit； //提交

8、  select * from dept；//查询记录

select * from dept order by deptno desc limit 3,2；//显示记录

二、MySQL的自动递增：

create  table article          //创建自动递增表

(

Id int primary key auto_increment,

Title varchar(255)

);

Insert into article values(null,’a’);

Insert into article values(null,’b’);

Insert into article(title) values(‘c’);

三、MySQL的日期时间：

1、  select now（）；//显示当前时间

2、  select date_format(now( ),’%y-%m-%d  %H：%i：%s’)；//显示指定格式的时间

四、MySQL_jdbc连接：

import java.sql.*;

public class TestMysqlConnection {

    public static void main(String[] args) {

       Connection conn = null;

       Statement stmt = null;

       ResultSet rs = null;

       try {

           Class.forName("com.mysql.jdbc.Driver");

           conn = DriverManager

                  .getConnection("jdbc:mysql://localhost/mydata?user=root&password=root");

           stmt = conn.createStatement();

           rs = stmt.executeQuery("select * from dept");

           while (rs.next()) {

              System.out.println(rs.getString("deptno"));

           }

       } catch (ClassNotFoundException e) {

           e.printStackTrace();

       } catch (SQLException ex) {

           System.out.println("SQLException: " + ex.getMessage());

           System.out.println("SQLState: " + ex.getSQLState());

           System.out.println("VendorError: " + ex.getErrorCode());

       } finally {

           try {

              if (rs != null) {

                  rs.close();

                  rs = null;

              }

              if (stmt != null) {

                  stmt.close();

                  stmt = null;

              }

              if (conn != null) {

                  conn.close();

                  conn = null;

              }

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

}

五、在MySQL中创建数据库和表：

在mysql中执行语句：/·E:/JDBC_MySQL_BBS项目/source/BBS/SQL/bbs.sql 可以执行bbs.sql中的语句

create database bbs;

use bbs;

create table article

(

id int primary key auto_increment,

pid int,

rootid int,

title varchar(255),

cont text,

pdate datetime,

isleaf int

);

 

insert into article values (null, 0, 1, '蚂蚁大战大象', '蚂蚁大战大象', now(), 1);

insert into article values (null, 1, 1, '大象被打趴下了', '大象被打趴下了',now(), 1);

insert into article values (null, 2, 1, '蚂蚁也不好过','蚂蚁也不好过', now(), 0);

insert into article values (null, 2, 1, '瞎说', '瞎说', now(), 1);

insert into article values (null, 4, 1, '没有瞎说', '没有瞎说', now(), 0);

insert into article values (null, 1, 1, '怎么可能', '怎么可能', now(), 1);

insert into article values (null, 6, 1, '怎么没有可能', '怎么没有可能', now(), 0);

insert into article values (null, 6, 1, '可能性是很大的', '可能性是很大的', now(), 0);

insert into article values (null, 2, 1, '大象进医院了', '大象进医院了', now(), 1);

insert into article values (null, 9, 1, '护士是蚂蚁', '护士是蚂蚁', now(), 0);

 

六、JDBC树状显示（tree）

import java.sql.*;

 

public class ArticleTree {

    public static void main(String[] args) {

       new ArticleTree().show();

    }

 

    public void show() {

       Connection conn = null;

       Statement stmt = null;

       ResultSet rs = null;

       try {

           Class.forName("com.mysql.jdbc.Driver");

           conn = DriverManager

                  .getConnection("jdbc:mysql://localhost/bbs?user=root&password=root");

           stmt = conn.createStatement();

           rs = stmt.executeQuery("select * from article where pid = 0");

           while (rs.next()) {

              System.out.println(rs.getString("cont"));

               tree(conn, rs.getInt("id"), 1);

           }

       } catch (ClassNotFoundException e) {

           e.printStackTrace();

       } catch (SQLException e) {

           e.printStackTrace();

       } finally {

           try {

              if (rs != null) {

                  rs.close();

                  rs = null;

              }

              if (stmt != null) {

                  stmt.close();

                  stmt = null;

              }

              if (conn != null) {

                  conn.close();

                  conn = null;

              }

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

 

    private void tree(Connection conn, int id, int level) {

       Statement stmt = null;

       ResultSet rs = null;

 

       StringBuffer strPre = new StringBuffer("");

       for (int i = 0; i < level; i++) {

           strPre.append("    ");

       }

 

       try {

           stmt = conn.createStatement();

           String sql = "select * from article where pid = " + id;

           rs = stmt.executeQuery(sql);

           while (rs.next()) {

              System.out.println(strPre + rs.getString("cont"));

              if (rs.getInt("isleaf") != 0) {

                  tree(conn, rs.getInt("id"), level + 1);

              }

           }

 

       } catch (SQLException e) {

           e.printStackTrace();

       } finally {

           try {

              if (rs != null) {

                  rs.close();

                  rs = null;

              }

              if (stmt != null) {

                  stmt.close();

                  stmt = null;

              }

           } catch (SQLException e) {

              e.printStackTrace();

           }

       }

    }

}