mysql基础总结

mysql使用总结

mysql安装下载

https://dev.mysql.com/downloads/mysql/
mysql驱动下载
https://dev.mysql.com/downloads/connector/j/
mysql编码格式查询及修改方式

mysql> show variables like 'character%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | ./mysql-5.7.12-osx10.11-x86_64/share/charsets/ |
+--------------------------+---------------------------------------------------------+
8 rows in set

mysql> show variables like 'collation%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | latin1_swedish_ci |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set

mysql> 

修改编码格式有两种方式

1，修改本地文件my.cnf（linux,windows my.ini)
https://blog.csdn.net/u012643122/article/details/46799943
2，进入mysql进行设置（注意，非永久有效）

mysql服务关闭与重启

net stop mysql
net start mysql

对数据库的常见操作命令

查看数据库
    show databases;
查看某个数据库的定义信息
	show create database 数据库名;
切换数据库
	use 数据库名;
正在使用的数据库
	select database();

删除数据库
	drop database 数据库();

表

创建表
mysql> create table 表名(
    -> 字段名 类型(长度) 约束,
    -> 字段名 类型(长度)
    -> );

单表约束
	主键约束:primary key,被修饰的字段唯一和非空
	唯一约束：unique
    非空约束：not null


查看表
mysql>show tables;

查看表结构：
	desc 表名;

删除表
	drop table 表名;

修改表
	alter table 表名 add 列名 类型(长度) 约束;			   //修改表添加列
	alter table 表名 modify 列名 类型(长度) 约束;            //修改表的列类型长度以及约束
	alter table 表名 cahge 旧列名 新列名 类型(长度) 约束;     //修改表的列名
    alter table 表名 drop 列名;								//修改表删除列
    rename table 表名1 to 表名2;                            //修改表名
	alter table 表名 character set 字符集;                  //修改表的字符集，详细查baidu
    

插入
	insert into 表名 (列名1,列名2) values(value1,'value2');
 	insert into 表名 values(value1,'value1','value1');

更新
	 update user set 字段名='value' where 字段名=value;

删除
	delete from 表名 where 字段名=value;


常见查询
	select * from 表名;
	select 列名1,列名2 from 表名;
	select * from 表名 as 别名;
	select * 列名 as pn(缩写) from 表名;
	select * distinct 列名 from 表名;(去重)
	select * 列名,列名+10 from 表名;
mysql> select * from product where pname like '%新%';
mysql> select * from product where pname like '%学%';
mysql> select * from product where pid in (2,5,8);
	
条件查询
	mysql> select 列名,列名 from 表名;
	mysql> select * 列名 表名 as p(别名);
	mysql> select 列名 as pn(别名) from 表名;
    mysql> select distinct price from 表名;
    mysql> select * from 表名 where 列名='左慈';
    mysql> select * from 表名 where 列名>60;                             //查询列名大于60的数据
    mysql> select * from 表名 where 列名 like '%新%';
    mysql> select * from 表名 where 列名=1 or 列名=3 or 列名 =5;
    mysql> select * from 表名 where 列名 like '%慈%' order by price desc; //查询列名中含有慈的字符，且按照price(列名)从大到小的排列


聚合
	select count(*) from 表名;	//统计商品数量
    select avg(列名) from 表名;	//平均价格
	select sum(列名) from 表名; // 价格总和


分组
	//根据cid字段进行分组，分组后统计商品的个数
	select cid,count(*) from product group by cid;
    //根据cid字段进行分组，分组统计魅族商品的平均价格，并且平均价格>60
    select cid,avg(price) from product group by cid having avg(price)>60;

多表操作

#生成笛卡尔积
select * from 表1,表2;

内连接
select * from 表1 inner join 表2 on 条件(表1.字段=表2.字段)


外链接   ----left是指显示左表全部记录,同理right显示右表全部
select * from 表1 left join 表2 on 条件(表1.字段=表2.字段)
    
    
子查询
select name from 表2 where 表2字段=(select id from 表1 where 表1字段='销售');

java下JDBC简单增删改查

1. 安装mysql，IDEA创建java工程
2. 导入下载下来的mysql驱动jar
3. 编写配置文件，测试数据库连接
4. 进行测试

JDBCUtils

package com.hardwork.test;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Properties;
import java.util.ResourceBundle;

public class JDBCUtils {
	
	private static String driver;
	private static String url;
	private static String user;
	private static String password;
	
	
	/**
	 * 1.使用ResourceBundle价值properties配置文件
	 */
//	static{
//		ResourceBundle bundle = ResourceBundle.getBundle("db");
//		driver = bundle.getString("jdbc.driver");
//		url = bundle.getString("jdbc.url");
//		user = bundle.getString("jdbc.user");
//		password = bundle.getString("jdbc.password");
//	} 
	
	/**
	 * 使用properties对象来加载
	 */
	static{
		try {
			InputStream inputStream = JDBCUtils.class.getClassLoader().getResourceAsStream("db.properties");
			Properties properties = new Properties();
			properties.load(inputStream);
			driver = properties.getProperty("jdbc.driver");
			url = properties.getProperty("jdbc.url");
			user = properties.getProperty("jdbc.user");
			password = properties.getProperty("jdbc.password");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}
	
	/**
	 * 连接数据库
	 * @return
	 */
	public static Connection getConnection(){
		Connection conn = null;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url,user,password);
			
		} catch (Exception e) {
			e.printStackTrace();
		}
		return conn;
	}
	
	/**
	 * 释放资源
	 */
	public static void release(Connection conn,PreparedStatement pstmt,ResultSet rs){
		if(conn!=null){
			try {
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(pstmt!=null){
			try {
				pstmt.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(rs!=null){
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}

配置文件db.properties

放到src目录

jdbc.driver=com.mysql.cj.jdbc.Driver
jdbc.url=jdbc:mysql://localhost:3306/userdb?serverTimezone=UTC
jdbc.user=root
jdbc.password=root

测试类

package com.hardwork.test;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class Test {
	
	public static void main(String[] args) {
//		queryUsers();
//		insertUsers();
//		updateUsers();
		deleteUsers();
	}

	private static void deleteUsers() {
		Connection conn=null;
		PreparedStatement statement=null;
		ResultSet resultSet=null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "delete from users where id=3";
			statement = conn.prepareStatement(sql);
			int result = statement.executeUpdate(sql);
			System.out.print("输出结果:"+result);//输出结果:1
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.release(conn, statement, resultSet);
		}
	}

	private static void updateUsers() {
		Connection conn=null;
		PreparedStatement statement=null;
		ResultSet resultSet=null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "update users set username='wcc' where id=3";
			statement = conn.prepareStatement(sql);
			int result = statement.executeUpdate(sql);
			System.out.print("输出结果:"+result);//输出结果:1
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.release(conn, statement, resultSet);
		}
	}

	private static void insertUsers() {
		Connection conn=null;
		PreparedStatement statement=null;
		ResultSet resultSet=null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "insert into users(id,username,password) values(3,'jdbc','678')";
			statement = conn.prepareStatement(sql);
			int result = statement.executeUpdate(sql);
			System.out.print("输出结果:"+result);//输出结果:1
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.release(conn, statement, resultSet);
		}
	}

	/**
	 * 查询
	 */
	private static void queryUsers() {
		Connection conn=null;
		PreparedStatement statement=null;
		ResultSet resultSet=null;
		try {
			conn = JDBCUtils.getConnection();
			String sql = "select * from users where id=?";
			statement = conn.prepareStatement(sql);
			statement.setInt(1, 1);
			resultSet = statement.executeQuery();
			while(resultSet.next()){
				System.out.print("username = "+resultSet.getString(2)+",password = "+resultSet.getString("password"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			JDBCUtils.release(conn, statement, resultSet);
		}
	}
}