mysql接触过么_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();

}

}

}

}

结果：

蚂蚁大战大象

*****大象被打趴下

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

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

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

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

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

*****怎么可能

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

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