【MYSQL系列】【基础版】第一章 MYSQL的安装以及测试

1. MYSQL的安装以及测试
    1.1 什么是数据库，有什么作用，以及有哪些特点
        1.1.1 Database，DB，是一个数据的仓库；
        1.1.2 用于保存、管理数据
        1.1.3 特点:
            1.1.3.1 一致性、完整性
            1.1.3.2 降低冗余（重复）
            1.1.3.3 应用的共享，以及有组织
        1.1.4 数据仓库： 偏向于数据分析，是数据挖掘的一种
   
    1.2 数据库的分类
        1.2.1 关系型数据库（SQL），用“表”来存储
            1.2.1.1 MYSQL
            1.2.1.2 Oracle
            1.2.1.3 SQL Server
            1.2.1.4 SQLite
            1.2.1.5 DB2
        1.2.2 非关系型数据库（NoSQL）
            1.2.2.1 MongoDB
            1.2.2.2 Redis
    
     1.3 DBMS
         1.3.1 database management system
   
     1.4 mysql-5.5.40-win32.msi的安装
         1.4.1 Next -> custom setup -> install -> Next -> configuration enabled -> Detail -> Developer Machine -> Multifunctional Database -> Path -> connection number(DSS/OLAP 20) -> enabled tcp/ip port = 3306, enable strict mode -> mannual selected default character set/collation(utf-8) -> install as windows service -> modify security settings -> enabled root access from remote machines -> Execute
         1.4.2 环境变量的配置
             1.4.2.1 找到系统高级设置的环境变量PATH，添加MYSQL 5.5\bin;（绝对路径）
         1.4.3 测试
             1.4.3.1 计算机 -> 管理 -> 服务和应用程序 -> 查看MYSQL  （或者在命令窗口输入service.msc）
             1.4.3.2 cmd -> 以管理员身份运行 -> net stop mysql -> net start mysql -> mysql -uroot -p***
         1.4.4 软件的卸载
             1.4.4.1 控制面板 -> 卸载软件
             1.4.4.2 将安装文件夹删除
             1.4.4.3 C: -> programdata -> MYSQL 文件删除  （如果找不到programData，组织->文件夹和搜索->查看->显示隐藏文件、文件夹）
             1.4.4.4 regedit -> HKEY_local_machine -> system -> service -> MySQL 删除
             1.4.4.5 测试： show databases ：      
             1.4.4.6 查看版本 select version():
     
      1.5 SQLyog管理工具
          1.3.1 可手动操作、管理MYSQL数据库的软件工具
          1.3.2 MYSQL |   localhost root  ***  3306  连接
          1.3.3 User Manager -> 编辑用户  
          1.3.4 用户名：yunjian   注册码：81f43d3dd20872b6
   
      1.6 sql常用代码：
DESC tabledemo; --查看表结构；

CREATE TABLE tabledemo2(
stu_uid VARCHAR(255) NOT NULL COMMENT '学生ID',
stu_name VARCHAR(255) NOT NULL COMMENT '学生名称',
stu_Birthday DATE DEFAULT NULL COMMENT '学生生日',
PRIMARY KEY(stu_uid)
)ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_Format=COMPACT COMMENT='学生类'; --新建一个tabledemo2的表；必须先build至少一个column；

DESC tabledemo2;

ALTER TABLE tabledemo rename TO tableNew; --重命名一个表；

TRUNCATE tableNew; --清空表数据，保留表结构；

DROP TABLE tabledemo2; --删除一个表；

INSERT INTO tabledemo(stu_id, stu_name, stu_age, stu_favorite) VALUES ('3', 'lisi', 123, 'playgames'); 

SELECT * FROM tabledemo; --查看表中详细信息；

ALTER table tabledemo ADD(
stu_love VARCHAR(255) NULL COMMENT '学生配偶'
);

Alter TABLE tabledemo MODIFY stu_love VARCHAR(512) NULL; --修改列定义；

ALTER TABLE tabledemo CHANGE stu_love stu_hobby VARCHAR(512) NULL; --修改列名；

ALTER TABLE tabledemo DROP stu_hobby; --删除列；


INSERT INTO tabledemo(stu_id, stu_name, stu_age, stu_favorite) VALUES ('4', 'jenney', 22, 'apple'); 

INSERT INTO `tabledemo` VALUES('5', 'jie', 23, 'samung');

INSERT INTO tabledemo(stu_name) (SELECT  stu_sname FROM student where stu_id=4);

UPDATE tabledemo SET stu_name='郭靖' where stu_id='2';

DELETE from tabledemo where stu_name='lisi';--删除where条件的行

SELECT stu_name,stu_id from tabledemo; --多列查询；

SELECT DISTINCT stu_id from tabledemo; --行排重，重复名称只显示一条；

SELECT stu_id,stu_name from tabledemo ORDER BY stu_age,stu_id DESC; --多列排序；

SELECT * from tabledemo LIMIT 2,3; --分页查询；

SELECT * from tabledemo WHERE stu_id BETWEEN '2' and '3'; --WHERE条件查询；

SELECT * FROM tabledemo WHERE stu_id in(1,2,3); --where in 语句的查询；


==========================================进阶===========================================

show DATABASES;
use test1;
SELECT * FROM tabledemo;
SELECT * FROM tabledemo WHERE stu_name LIKE '%e'; --%表示任意字符出现任意次；
SELECT stu_favorite FROM tabledemo WHERE stu_name LIKE '%i_'; --_表示匹配任意单个字符；
SELECT s.stu_name 姓名 FROM tabledemo as s WHERE s.stu_id='2'; --as 给表或字段设置别名，as可以缺省；

SELECT CONCAT(stu_id,stu_name) FROM tabledemo; --用CONCAT将stu_id 和 stu_name 合并到一起；

SELECT SUM(stu_age) FROM tabledemo; --求tableDemo表中stu_age的和；

SELECT COUNT(stu_age) FROM tabledemo WHERE stu_id=2; --COUNT(expr)用于对stu_age进行计数；

SELECT AVG(stu_age) FROM tabledemo; --取平均值，最大值，最小值；

ALTER table tabledemo ADD(stu_comment VARCHAR(255) NOT NULL);

SELECT IFNULL(stu_age, 0) FROM tabledemo;

CREATE table tabletoday(
	tabletoday_id INT not null PRIMARY KEY auto_increment,
	tabletoday_name VARCHAR(15) UNIQUE NOT NULL,
	tabletoday_teacher VARCHAR(15) NOT NULL DEFAULT 'dabai'
	
)ENGINE=INNODB DEFAULT CHARSET=UTF8 ROW_FORMAT=COMPACT COMMENT='跟天';

DROP TABLE tabletoday1;

SELECT * FROM tableDemo;

SELECT stu_id FROM tabledemo GROUP BY stu_id; --group by 分组，其中相同的取第一个匹配的，其余的不管；

SELECT stu_id,COUNT(stu_name) FROM tabledemo where stu_age>20 GROUP BY stu_id; --通过stu_id 分组，并且查询std_id和count;


SELECT stu_class 班级ID,COUNT(stu_class) 班级人数 FROM tabledemo GROUP BY stu_class HAVING COUNT(stu_class)>2 ;
--分组的情况下，使用having对人数的判断条件，where此时不奏效；


SELECT stu_id,stu_name,stu_age,stu_class FROM tabledemo
UNION all
SELECT tabletoday_id, tabletoday_name, tabletoday_teacher, tabletoday_class FROM tabletoday;
--union代表联合两个表并去重，加上all后，保留所有结果


SELECT * from tabledemo, tabletoday;  //隐式的交叉连接，得到的是笛卡尔积


SELECT * from tabledemo cross JOIN tabletoday where stu_class=tabletoday_class;  --用班级号相等这个条件将两个表联系起来，避免重复的数据

SELECT * FROM tabledemo t1 INNER JOIN tabletoday t2 on  t1.stu_class = t2.tabletoday_class;

SELECT * FROM tabledemo t1 LEFT OUTER JOIN tabletoday t2 on t1.stu_class = t2.tabletoday_class;


SELECT * FROM tabledemo t1 RIGHT OUTER JOIN  tabletoday t2 on t1.stu_class = t2.tabletoday_class;


SELECT * FROM tabledemo t1 LEFT  OUTER JOIN tabletoday t2 on t1.stu_class = t2.tabletoday_class
Union
SELECT * FROM tabledemo t1 RIGHT OUTER JOIN  tabletoday t2 on t1.stu_class = t2.tabletoday_class;



-----------------------------------------------------------------------------------------
SELECT * from tablenew;
SELECT * from tableold;


--创建视图--
CREATE VIEW table_view AS 
SELECT id, table_name, table_other, table_id,COUNT(*) as CountNum FROM tablenew n GROUP BY table_id;

--调用视图--
SELECT table_id FROM table_view WHERE CountNum > 1;

--查看引擎--
show engines;

--查看MYSQL当前默认的引擎--
show variables like '%storage_engine%';

--查看某个表当前用的引擎--
show CREATE TABLE tablenew;

--事务的四大属性：ACID（Atomicity, Consistency, Isolation and Durability）--
--MyISAM不支持事务，InnoDB支持事务；

--设置提交状态
SET AUTOCOMMIT = FALSE;
OR
SET autocommit = 0;

AUTOCOMMIT = TRUE; --关闭提交状态

--显示执行--
start TRANSACTION
BEGIN

--提交一个事务
COMMIT
DLL(自动提交)

--回滚
ROLLBACK
用户回话正常结束
异常终止



--设置和查看事物级别
SELECT @@tx_isolation; --查看当前回话隔离级别

SELECT @@global.tx_isolation; --查看系统当前隔离级别

--事物隔离级别名字： READ UNCOMMITTED读未提交, READ COMMITTED读已提交, REPEATABLE READ可重复读, SERIALIZABLE可串行化
--脏读（A事务读到B事务尚未提交的修改（update，delete和insert）） -> 不可重复读（同一事务两次读到了不同的数据（select）） 
-- -> 幻读 （）由于其他事务的插入或删除操作，倒置两次读取不一样
SET transction ISOLATION LEVEL 事务隔离级别名字 下一次事务隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL 设置当前回话隔离级别
SET GLOBAL TRANSACTION ISOLATION LEVEL 设置系统当前隔离级别

--------------------------------------------------------------------------------

SELECT * from tablenew;
SELECT * from tableold;

--查询一班女生的所有信息
SELECT s.* FROM
(SELECT * FROM tablenew, tableold WHERE old_classid = table_id) s
WHERE
s.table_other = '江苏'
AND
s.table_id = 2;

--查询表里面成绩最高分的同学信息--
SELECT * FROM tablenew WHERE 
table_score =
(SELECT MAX(table_score) FROM tablenew);

--查询出成绩高于平均分的成绩的同学信息
SELECT * from tablenew
WHERE 
table_score > 
(SELECT AVG(table_score) FROM tablenew);


--查询班级为一班的男生同学的所有信息
SELECT s.* FROM
(SELECT * FROM tablenew,tableold WHERE tablenew.table_id = tableold.old_classid) s
WHERE 
s.table_id = 1;



--创建视图
CREATE VIEW table_view_one AS
SELECT * FROM tablenew,tableold WHERE tablenew.table_id = tableold.old_classid;


--使用视图
SELECT table_other,COUNT(table_other) FROM table_view_one GROUP BY table_other HAVING COUNT(*) > 1;

--创建一个班级分组后的视图
CREATE VIEW table_view_two AS
SELECT * FROM tablenew,tableold WHERE tablenew.table_id = tableold.old_classid GROUP BY table_id;

--查询一个班级人数大于1的班级有哪些
SELECT table_id FROM table_view_two WHERE (SELECT COUNT(table_id) FROM table_view_two) > 1;


SELECT * FROM tablenew;
SELECT * FROM tableold;

--修改表中的值
UPDATE tablenew SET table_score = 88 WHERE table_id = 7;

--修改表中的值
UPDATE tablenew SET table_score = table_score + 30 WHERE id = 2;
UPDATE tablenew SET table_score = table_score + 10 WHERE id = 1;


--开启事务
SET autocommit = false; //关闭自动提交
set autocommit = true ; //开启自动提交


SELECT table_score from tablenew WHERE table_score > 80;
UPDATE tablenew SET table_score = table_score + 10 WHERE table_score < 60;

START TRANSACTION; --开启事务，或者
BEGIN; 


COMMIT; --提交
ALTER TABLE tablenew DROP table_other; 

INSERT INTO tablenew VALUES('kk', 'll');


ROLLBACK; --回滚




========================================高阶=============================================
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;


public class JDBCDemo {
	public static void main(String[] args) throws Exception {
		//注册驱动
//		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		//加载驱动的方式
		Class.forName("com.mysql.jdbc.Driver");
		
		Properties info = new Properties();
		info.setProperty("user", "root");
		info.setProperty("password", "gdadmin");
		
		
		//建立连接
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/tablenew", info);
	//	Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/tablenew", "root", "gdadmin");
//		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/tablenew?user=root&password=gdadmin");

		//获得执行sql语句的对象
		Statement stmt = conn.createStatement();
		
		//执行sql，获得结果集
		String sql = "select stu_id, stu_name from tablenew";
		
		//执行了sql，并且得到了结果集
		ResultSet rs = stmt.executeQuery(sql);
		
		//处理结果集
		while(rs.next()){
			System.out.println(rs.getObject(1)+"\t");
			System.out.println(rs.getObject("table_score") + "\t");
		}
		
		//关闭资源
		rs.close(); //结果集
		stmt.close(); //小货车
		conn.close(); //桥梁
	}
}



=========================================================================================
第一部分：测试类
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Scanner;


public class JDBCTest {
	public static void main(String[] args) throws Exception {
		Scanner sc = new Scanner(System.in);
		System.out.println("请输入登录用户名");
		String name = sc.nextLine();
		System.out.println("请输入登录密码");
		String password = sc.nextLine();
		Statement st = DButil.getstsm();
		
		String sql = "SELECT table_score from tablenew WHERE table_score > 80;";
		ResultSet rs = st.executeQuery(sql);
		
		if(rs.next()){
			System.out.println("尊敬的vip,欢迎您的加入！！");
		}else{
			System.out.println("游客禁入！！！");
		}
	}
}



第二部分：方法类

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;


public class DButil {
	//封装一个静态方法，用来启动连接sql数据库
	public static Statement getstsm() throws Exception{
		Class.forName("com.mysql.jdbc.Driver");//加载驱动
		//获取连接
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1", "root", "gdadmin");
		//返回连接结果表达式
		return conn.createStatement();
	} 
}