Java连接MySQL与基本SQL语句

落日映苍穹つ 2022-08-04 11:55 182阅读 0赞

![20150825212419789][]

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

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

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

![20150825212703426][]

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

4.确定，导入完成。

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

![20150825212753634][]

下面，我们来编写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][]，文章写的简洁明了。*

[20150825212419789]: /images/20220731/e9ccf726eb6a44888237cf61e8fee908.png
[mysql-connector-java.jar]: http://ibasic.qiniudn.com/mysql-connector-java.zip
[MySQL]: http://ibasic.qiniudn.com/mysql-5.5.19-win32.zip
[20150825212703426]: /images/20220731/908ec363da2e4d35b434a86bcec63f9d.png
[20150825212753634]: /images/20220731/58a589c489b84b93b3d6f1a462002383.png
[GitBook]: https://cxiaodian.gitbooks.io/mysql/content/