Java连接MySQL与基本SQL语句

       在JDBC编程时需要连接数据库，而导入mysql-connector-java.jar驱动包是必须的要做的，这里以链接MySQL为例,以下是导入的方法：

       1.打开eclipse，创建工程，鼠标右键工程名或目录，选择Build Path下的Configure Build Path

       2.左边选择Java Build Path,上边选择Libraries,如下图

     3.如果JARs and class folders下有mysql-connector-java包，则直接选择；如果无，点add External jars
选择jar包的路径导入。

     4.确定，导入完成。  

      添加完成后在Referenced Libraries下应有此jar包，如下图

      下面，我们来编写java文件来演示一下如何访问MySQL数据库。

import java.sql.*;

public class test001 {

    public static void main(String[] args){

        try{
            /*调用Class.forName()方法加载驱动程序*/
            Class.forName("com.mysql.jdbc.Driver");
        }catch(ClassNotFoundException e1){
            System.out.println("Sorry,can't find the Driver!");
            e1.printStackTrace();
        }
        /*JDBC的URL，test是数据库名*/ 
        String url="jdbc:mysql://localhost:3306/test";
        /*或写成String url = "jdbc:mysql://127.0.0.1:3306/test";*/

        /*调用DriverManager对象的getConnection()方法，获得一个Connection对象*/
        Connection con;
        try {
                    /*后面的root为数据库密码*/
	            con = DriverManager.getConnection(url,"root","root");
                    /*创建Statement对象*/	        
	            Statement stmt = con.createStatement(); 
	            System.out.println("Succeeded connecting to the Database!");
	            /*sql语句，查看javatest表中的所有记录*/           
	            String sql = "select * from javatest"; 
                    /*创建数据对象，获取查询结果*/   
	            ResultSet rs = stmt.executeQuery(sql);

	            while (rs.next()){
		            System.out.print(rs.getString("id") + "\t");
                            /*System.out.print(rs.getString(1) + "\t");*/
		            System.out.print(rs.getString("username") + "\t");
		            System.out.print(rs.getString("pswd") + "\t");
		            System.out.println(rs.getString("mail") + "\t");
                    }        
	            stmt.close();
	            con.close();
        	} catch(SQLException e){   
			e.printStackTrace();   
		} catch(Exception e){   
			e.printStackTrace();   
		}
    }
}

SQL语句：

创建数据库:create database databasename 
删除数据库:drop database databasename

创建表:create table Persons(id int,name varchar(25),sex varchar(2),primary key (id))
删除表:drop table tablename 

创建索引：create [unique] index idxname on tablename(col….) 
删除索引：drop index idxname

创建视图：create view viewname as select statement 
删除视图：drop view viewname


选择 ：select * from tablename where condition

消重 ：select distinct column from tablename

插入 ：insert into tablename(column1,column2) values(value1,value2)

删除 ：delete from tablename where condition

更新 ：update tablename set column=value1 where condition

查找 ：select * from tablename where column like ’%key%’

排序 ：select * from tablename order by column1,column2 [desc|asc]

分组 ：select * from tablename group by column

总数 ：select count as totalcount from tablename

求和 ：select sum(column) as sumvalue from tablename

平均 ：select avg(column) as avgvalue from tablename

最大 ：select max(column) as maxvalue from tablename

最小 ：select min(column) as minvalue from tablename

AND  ：select * from tablename where condition1 and condition2

 OR  ：select * from tablename where condition1 or condition2
 
ANDOR：select * from tablename where ( condition1 or condition2 ) and condition3

 TOP : select * from tablename limit 5 #返回前5条记录

按笔画排序：select * from tablename order by customerName collate Chinese_PRC_Stroke_ci_as

简单SQL学习可参考这篇GitBook，文章写的简洁明了。