一、mysql的启动与创建表格:
create database msdata; //创建mydata数据库
use mydata;
create table dept ( deptno int primary key, dname varchar(14), loc varchar(13) );
Note:在命令行中逐行输入时,容易出错,可以编写小的.sql脚本。在命令行中,可以用/.来执行一个docBase
(绝对路径)//.sql脚本。
show databases;// 显示数据库;
show tables;//显示数据库mydata中的表;
desc dept;//查看dept表的结构;
insert into dept values(10,abc,abc);//向数据库中插入数据 ;
commit;//提交
二、数据库之间的区别:
1、数据类型不同;
2、分页程序;
select *
from dept
order by deptno desc //按deptno的降序排列;
limit 3,2;//从第三条之后数两条;
3、数据库的递增字段;
create table article ( id int primary key auto_increment, title varchar(255) );
insert into article values(null,'123');
insert into article values(null,'456');
数据库会自动插入(1,123)和(2,456)。
或者用: insert into article(title) values('你好啊!')。
日期处理: select now(); select date_format(now(),'%Y %D %M %h:%i:%s %x');
三、jdbc编程步骤:
以mysql为例:
import java.sql.*;
public class TestJDBC {
public static void main(String[] args) {
Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
try {
Class.forName("com.mysql.jdbc.Driver");//Load the Driver
conn = DriverManager.getConnection("jdbc:mysql://localhost/mydata?" + "user=root&password=19871114");//Connect to the DataBase
// Execute the SQL
stmt = conn.createStatement();
rs = stmt.executeQuery("select * from student");
while(rs.next()){ //Retrive the result data
//Show the result data
System.out.print(rs.getString("Sno"));
System.out.println(rs.getString("Sname"));
}
}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{ //close
try{
if(rs != null){
conn.close();
conn = null;
}
if(stmt != null){
stmt.close();
stmt = null;
}
if(conn != null){
conn.close();
conn = null;
}
}catch(SQLException ex2){
ex2.printStackTrace();
}
}
}
}