Mysql初步总结

淩亂°似流年 2022-05-08 15:50 189阅读 0赞

### **1.Mysql****的安装和连接** ###

连接数据库：在CMD窗口

[root@host]# mysql -u root -p

Enter password:******

创建数据库： mysql>  create  DATABASE  testTable;

删除数据库： mysql>  drop  DATABASE  testTable;

选择数据库： mysql>  use  DATABASE  testTable;

创建表：create TABLE table\_name (column\_name column\_type, column\_name column\_type, column\_name column\_type…);

删除表：DROP  TABLE  table\_name ;

### **2.Mysql****的****CRUD****操作** ###

增：insert into table\_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN );

删：DELETE FROM table\_name \[WHERE Clause\]

改：UPDATE table\_name SET field1=new-value1, field2=new-value2 \[WHERE Clause\]

查：select \*(column\_name, column\_name…) from table\_name;

### **3.where****条件语句** ###

SELECT field1, field2,...fieldN FROM table\_name1, table\_name2... \[WHERE condition1 \[AND \[OR\]\] condition2....// WHERE 子句也可以运用于 SQL 的 DELETE 或 UPDATE 命令

**condition****中的操作符可以有：**

=   !=   >   <   <=   >=

**like  ‘% xx‘ (%****表示任意字符)**

SELECT \* from runoob\_tbl WHERE runoob\_author LIKE '%书';//查询作者为“\*书”的集合

**UNION** **操作符的语法**

SELECT expression1, expression2, ... expression\_n

FROM tables

\[WHERE conditions\]

UNION \[ALL | DISTINCT\]   //**DISTINCT:** 可选，删除结果集中重复的数据。ALL: 可选，返回所有结果集，包含重复数据

SELECT expression1, expression2, ... expression\_n

FROM tables

\[WHERE conditions\];

** in****操作符的语法**

…...  where fields in (value1,value2….)

…...  where fields not in (value1,value2….)

**NULL****值处理 ** where field1 is null;

IS NULL: 当列的值是 NULL,此运算符返回 true。

IS NOT NULL: 当列的值不为 NULL, 运算符返回 true。

<=>: 比较操作符（不同于=运算符），当比较的的两个值为 NULL 时返回 true。

### **4.MySQL** **排序** ###

SELECT 语句使用 ORDER BY 子句将查询数据排序后再返回数据

SELECT field1, field2,...fieldN table\_name1, table\_name2... ORDER BY field1, \[field2...\] \[ASC \[DESC\]\]   // ASC 或 DESC 关键字来设置查询结果是按升序或降序排列，默认情况下，它是按升序排列。

### **5.MySQL** **分组** ###

GROUP BY 语句根据一个或多个列对结果集进行分组。

SELECT column\_name, function(column\_name) FROM table\_name WHERE column\_name operator value GROUP BY column\_name;// 分组的列上我们可以使用 COUNT, SUM, AVG

**配合使用 WITH ROLLUP**

WITH ROLLUP 可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）group by 可以实现一个最简单的去重查询，假设想看下有哪些员工，除了用 distinct,还可以用：SELECT name FROM employee\_tbl GROUP BY name;

**配合使用 having** **过滤结果**

注意：分组后的条件限定使用 HAVING 来限定，WHERE 是对原始数据进行条件限制。几个关键字的使用顺序为 where 、group by 、having、order by

### **6.MySQL** **联表查询****/****多表查询** ###

在真正的应用中经常需要从多个数据表中读取数据，如SELECT, UPDATE 和 DELETE 语句中使用 Mysql 的 JOIN 来联合多表查询

**INNER JOIN****（内连接****,****或等值连接）**：获取两个表中字段匹配关系的记录。

**LEFT JOIN****（左连接）：**获取左表所有记录，即使右表没有对应匹配的记录。

**RIGHT JOIN****（右连接）：** 与 LEFT JOIN 相反，用于获取右表所有记录，即使左表没有对应匹配的记录。

注意：

1. on的条件是在连接生成临时表时使用的条件,以左表为基准 ,不管on中的条件真否,都会返回左表中的记录

2.where条件是在临时表生成好后,再对临时表过滤。此时 和left join有区别(返回左表全部记录),条件不为真就全部过滤掉,on后的条件来生成左右表关联的临时表,where后的条件是生成临时表后对临时表过滤。

### **7.Mysql****事务** ###

MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你即需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。

事务用来管理 insert,update,delete 语句。

一般来说，事务是必须满足4个条件（ACID）：

原子性（Atomicity，或称不可分割性）

一致性（Consistency）

隔离性（Isolation，又称独立性）

持久性（Durability）。

事务特性的理解：

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。

一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。

隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。

持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

MYSQL 事务处理主要有两种方法：

1、用 BEGIN, ROLLBACK, COMMIT来实现

BEGIN 开始一个事务

ROLLBACK 事务回滚

COMMIT 事务确认

2、直接用 SET 来改变 MySQL 的自动提交模式:

SET AUTOCOMMIT=0 禁止自动提交

SET AUTOCOMMIT=1 开启自动提交

### **8.事务隔离级别和安全问题**   ###

安全问题

脏读 : 一个事务读到了另一个事务的未提交的数据

不可重复读 : 一个事务读到了另一个事务已经提交的 update 的数据导致多次查询结果不一致.

虚/幻读 : 一个事务读到了另一个事务已经提交的 insert 的数据导致多次查询结果不一致.

隔离级别：

未提交读 :脏读，不可重复读，虚读都有可能发生

已提交读 :避免脏读。但是不可重复读和虚读有可能发生

可重复读 :避免脏读和不可重复读.但是虚读（幻读）有可能发生.

串行化的 :避免以上所有读问题.

Mysql 默认:可重复读

Oracle 默认:读已提交

### **9.Mysql****索引** ###

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

**普通索引：**

1最基本的索引

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

2. 修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName);

3.创建表的时候直接指定

CREATE TABLE mytable(ID INT NOT NULL,  username VARCHAR(16) NOT NULL, INDEX \[indexName\] (username(length)) );  //length代表的是索引最多使用username列前length个字符

**唯一索引**

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

创建索引         CREATE UNIQUE INDEX indexName ON mytable(username(length))

修改表结构     ALTER table mytable ADD UNIQUE \[indexName\] (username(length))

创建表的时候直接指定：

CREATE TABLE mytable(ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE \[indexName\] (username(length)) );

**索引的删除：**

DROP INDEX \[indexName\] ON mytable;

### 10.  Mysql JDBC操作 ###

package jdbc;
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.sql.Statement;
    public class JdbcTest {
    	public static void main(String[] args)  {
    		Connection conn= null;
    		Statement statement = null;
    		ResultSet eq =null;
    		try {
    			Class.forName("com.mysql.jdbc.Driver");
    			conn= DriverManager.getConnection("jdbc:mysql://localhost:3306/springmvc?characterEncoding=UTF-8", "root", "1234567");
    		    statement = conn.createStatement();
    			String sql = "select * from user where id = 1 ";
    			eq = statement.executeQuery(sql);
    			if(eq.next()) {
    				System.out.println(eq.getString("username"));
    			}					
    		} catch (Exception e) {
    			e.printStackTrace();
    		}
    		finally {
    			try {
    				conn.close();
    				statement.close();
    				eq.close();
    			} catch (SQLException e) {
    				e.printStackTrace();
    			}
    		}
    	}
    }

### 11.  数据库连接池DBCP   C3P0 ###

DBCP 要添加两个jar包：commons-dbcp.jar,commons-pool.jar

配置文件dbcpconfig.properties

#连接设置
    driverClassName=com.mysql.jdbc.Driver
    url=jdbc:mysql://localhost:3306/jdbc
    username=root
    password=
    
    #
    initialSize=10
    
    #最大连接数量
    maxActive=50
    
    #
    maxIdle=20
    
    #
    minIdle=5
    
    #
    maxWait=60000
    
    
    #JDBC驱动建立连接时附带的连接属性属性的格式必须为这样：[属性名=property;] 
    #注意："user" 与 "password" 两个属性会被明确地传递，因此这里不需要包含他们。
    connectionProperties=useUnicode=true;characterEncoding=gbk
    
    #指定由连接池所创建的连接的自动提交（auto-commit）状态。
    defaultAutoCommit=true
    
    #driver default 指定由连接池所创建的连接的事务级别（TransactionIsolation）。
    #可用值为下列之一：（详情可见javadoc。）NONE,READ_UNCOMMITTED, READ_COMMITTED, REPEATABLE_READ, SERIALIZABLE
    defaultTransactionIsolation=READ_UNCOMMITTED

测试

public void demo1(){
    		Connection conn = null;
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		BasicDataSource dataSource = new BasicDataSource();
    		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
    		dataSource.setUrl("jdbc:mysql:///web_07");
    		dataSource.setUsername("root");
    		dataSource.setPassword("123");
    		try{
    			// 获得连接:
    			conn = dataSource.getConnection();
    			// 编写SQL:
    			String sql = "select * from category";
    			// 预编译SQL:
    			stmt = conn.prepareStatement(sql);
    			// 执行SQL:
    			rs = stmt.executeQuery();
    			while(rs.next()){
    				System.out.println(rs.getInt("cid")+"   "+rs.getString("cname"));
    			}
    		}catch(Exception e){
    			e.printStackTrace();
    		}finally{
    			JDBCUtils.release(rs,stmt, conn);
    		}
    	}
    	
    	@Test
    	/**
    	 * 配置文件方式：
    	 */
    	public void demo2(){
    		Connection conn = null;
    		PreparedStatement stmt = null;
    		ResultSet rs = null;
    		Properties properties = new Properties();
    		
    		try{
    			properties.load(new FileInputStream("src/dbcpconfig.properties"));
    			DataSource dataSource = BasicDataSourceFactory.createDataSource(properties);
    			// 获得连接:
    			conn = dataSource.getConnection();
    			// 编写SQL:
    			String sql = "select * from category";
    			// 预编译SQL:
    			stmt = conn.prepareStatement(sql);
    			// 执行SQL:
    			rs = stmt.executeQuery();
    			while(rs.next()){
    				System.out.println(rs.getInt("cid")+"   "+rs.getString("cname"));
    			}
    		}catch(Exception e){
    			e.printStackTrace();
    		}finally{
    			JDBCUtils.release(rs,stmt, conn);
    		}
    	}

C3P0也要引入C3P0连接池的jar包.

配置c3p0-config.xml

<?xml version="1.0" encoding="UTF-8"?>
    <c3p0-config>
      <default-config>
        <property name="driverClass">com.mysql.jdbc.Driver</property>
    	<property name="jdbcUrl">jdbc:mysql:///web_07</property>
    	<property name="user">root</property>
    	<property name="password">123</property>
    	<property name="initialPoolSize">5</property>
    	<property name="maxPoolSize">20</property>
      </default-config>
      
      <named-config name="oracle"> 
        <property name="driverClass">com.mysql.jdbc.Driver</property>
    	<property name="jdbcUrl">jdbc:mysql:///web_07</property>
    	<property name="user">root</property>
    	<property name="password">123</property>
      </named-config>  
    </c3p0-config>

建工具类：

public class JDBCUtils2 {
    	private static final ComboPooledDataSource DATA_SOURCE =new ComboPooledDataSource();
    	/**
    	 * 获得连接的方法
    	 */
    	public static Connection getConnection(){
    		Connection conn = null;
    		try {
    			conn = DATA_SOURCE.getConnection();
    		} catch (SQLException e) {
    			// TODO Auto-generated catch block
    			e.printStackTrace();
    		}
    		return conn;
    	}