Mysql初次接触

//创建数据库

create database mydata;

//在mydata下面工作

use mydata;

//创建一张表dept

create table dept

(

deptno int primary key,

dname varchar(14),

loc varchar(13)

);

//创建一张表emp

create table emp

(

empno int primary key,

ename varchar(10),

job varchar(10),

mgr int,

hiredate datetime,

sal double,

comm double,

deptno int,

foreign key (deptno) references dept(deptno)

);

//执行一个sql文件中的语句

.\ d:\\mysql\\mydata.sql

//查看有几个数据库

show databases;

//查看当前数据库中有几张表

show tables;

//查看表dept的结构

desc dept;

//数据库分页(根据字段deptno倒序排列后，从第3条开始【不包括第3条】往后读取2条数据)

select * from dept order by deptno desc limit 3, 2;

注：limit后面不能跟括号，第一个参数最小从0开始，不能是负数；第二个参数没有限制，可以超出表中的记录数，比如：select * from dept order by deptno desc limit 0, 200;查出的就是表dept的所有数据

//自动递增字段(auto_increment)

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');

//获取系统当前时间(from dual可以不要)

select now() from dual;

2013-07-14 17:00:20

//日期格式转换

select date_format(now(), '%y-%m-%d %h:%i:%s');

13-07-14 05:00:31

select date_format(now(), '%Y-%M-%D %H:%I:%S');

2013-July-14th 17:00:31

//往datetime格式里面插入数据，字符只要格式一样的话，也可以插入

insert into emp values (10,'zhangsan','job1',20,'2013-4-5 12:45:14',8000,1231,30);

//JDBC链接mysql

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestJDBC {
	public static void main(String[] args) {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			// Class.forName("com.mysql.jdbc.Driver").newInstance();
			// new com.mysql.jdbc.Driver();
			conn = DriverManager
					.getConnection("jdbc:mysql://localhost:3306/mydata", "root", "root");
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from dept");
			while (rs.next()) {
				System.out.println(rs.getString("dname") + "	" + rs.getInt("deptno"));
			}
		} 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();
			}
		}
	}
}

 

//mysql树形展示

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

 );

--0表示leaf,1表示非leaf

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);

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestArticleTree {
	public static void tree(Connection conn, int id, int level) {
		Statement stmt = null;
		ResultSet rs = null;
		StringBuffer sb = new StringBuffer();
		for (int i = 0; i < level; i++) {
			sb.append("*****");
		}
		try {
			stmt = conn.createStatement();
			rs = stmt.executeQuery("select * from article where pid = " + id);
			while (rs.next()) {
				System.out.println(sb.toString() + rs.getString("cont"));
				if (rs.getInt("isleaf") == 1) {
					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();
			}
		}
	}
	public static void main(String[] args) {
		ResultSet rs = null;
		Statement stmt = null;
		Connection conn = null;
		try {
			Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bbs", "root", "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();
			}
		}
	}

}

 

结果：

蚂蚁大战大象

*****大象被打趴下

**********蚂蚁也不好过

**********瞎说

***************没有瞎说

**********大象进医院了

***************蚂蚁是护士

*****怎么可能

**********怎么没有可能

**********可能性很大的