【MYSQL系列】【基礎(chǔ)版】第一章 MYSQL的安裝以及測試

1. MYSQL的安裝以及測試
    1.1 什么是數(shù)據(jù)庫，有什么作用，以及有哪些特點(diǎn)
        1.1.1 Database，DB，是一個數(shù)據(jù)的倉庫；
        1.1.2 用于保存、管理數(shù)據(jù)
        1.1.3 特點(diǎn):
            1.1.3.1 一致性、完整性
            1.1.3.2 降低冗余（重復(fù)）
            1.1.3.3 應(yīng)用的共享，以及有組織
        1.1.4 數(shù)據(jù)倉庫： 偏向于數(shù)據(jù)分析，是數(shù)據(jù)挖掘的一種
   
    1.2 數(shù)據(jù)庫的分類
        1.2.1 關(guān)系型數(shù)據(jù)庫（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 非關(guān)系型數(shù)據(jù)庫（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 環(huán)境變量的配置
             1.4.2.1 找到系統(tǒng)高級設(shè)置的環(huán)境變量PATH，添加MYSQL 5.5\bin;（絕對路徑）
         1.4.3 測試
             1.4.3.1 計(jì)算機(jī) -> 管理 -> 服務(wù)和應(yīng)用程序 -> 查看MYSQL  （或者在命令窗口輸入service.msc）
             1.4.3.2 cmd -> 以管理員身份運(yùn)行 -> 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數(shù)據(jù)庫的軟件工具
          1.3.2 MYSQL |   localhost root  ***  3306  連接
          1.3.3 User Manager -> 編輯用戶  
          1.3.4 用戶名：yunjian   注冊碼：81f43d3dd20872b6
   
      1.6 sql常用代碼：
DESC tabledemo; --查看表結(jié)構(gòu)；

CREATE TABLE tabledemo2(
stu_uid VARCHAR(255) NOT NULL COMMENT '學(xué)生ID',
stu_name VARCHAR(255) NOT NULL COMMENT '學(xué)生名稱',
stu_Birthday DATE DEFAULT NULL COMMENT '學(xué)生生日',
PRIMARY KEY(stu_uid)
)ENGINE=INNODB DEFAULT CHARSET=utf8 ROW_Format=COMPACT COMMENT='學(xué)生類'; --新建一個tabledemo2的表；必須先build至少一個column；

DESC tabledemo2;

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

TRUNCATE tableNew; --清空表數(shù)據(jù)，保留表結(jié)構(gòu)；

DROP TABLE tabledemo2; --刪除一個表；

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

SELECT * FROM tabledemo; --查看表中詳細(xì)信息；

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

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; --行排重，重復(fù)名稱只顯示一條；

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 語句的查詢；


==========================================進(jìn)階===========================================

show DATABASES;
use test1;
SELECT * FROM tabledemo;
SELECT * FROM tabledemo WHERE stu_name LIKE '%e'; --%表示任意字符出現(xiàn)任意次；
SELECT stu_favorite FROM tabledemo WHERE stu_name LIKE '%i_'; --_表示匹配任意單個字符；
SELECT s.stu_name 姓名 FROM tabledemo as s WHERE s.stu_id='2'; --as 給表或字段設(shè)置別名，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進(jìn)行計(jì)數(shù)；

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) 班級人數(shù) FROM tabledemo GROUP BY stu_class HAVING COUNT(stu_class)>2 ;
--分組的情況下，使用having對人數(shù)的判斷條件，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代表聯(lián)合兩個表并去重，加上all后，保留所有結(jié)果


SELECT * from tabledemo, tabletoday;  //隱式的交叉連接，得到的是笛卡爾積


SELECT * from tabledemo cross JOIN tabletoday where stu_class=tabletoday_class;  --用班級號相等這個條件將兩個表聯(lián)系起來，避免重復(fù)的數(shù)據(jù)

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;


--創(chuàng)建視圖--
CREATE VIEW table_view AS 
SELECT id, table_name, table_other, table_id,COUNT(*) as CountNum FROM tablenew n GROUP BY table_id;

--調(diào)用視圖--
SELECT table_id FROM table_view WHERE CountNum > 1;

--查看引擎--
show engines;

--查看MYSQL當(dāng)前默認(rèn)的引擎--
show variables like '%storage_engine%';

--查看某個表當(dāng)前用的引擎--
show CREATE TABLE tablenew;

--事務(wù)的四大屬性：ACID（Atomicity, Consistency, Isolation and Durability）--
--MyISAM不支持事務(wù)，InnoDB支持事務(wù)；

--設(shè)置提交狀態(tài)
SET AUTOCOMMIT = FALSE;
OR
SET autocommit = 0;

AUTOCOMMIT = TRUE; --關(guān)閉提交狀態(tài)

--顯示執(zhí)行--
start TRANSACTION
BEGIN

--提交一個事務(wù)
COMMIT
DLL(自動提交)

--回滾
ROLLBACK
用戶回話正常結(jié)束
異常終止



--設(shè)置和查看事物級別
SELECT @@tx_isolation; --查看當(dāng)前回話隔離級別

SELECT @@global.tx_isolation; --查看系統(tǒng)當(dāng)前隔離級別

--事物隔離級別名字： READ UNCOMMITTED讀未提交, READ COMMITTED讀已提交, REPEATABLE READ可重復(fù)讀, SERIALIZABLE可串行化
--臟讀（A事務(wù)讀到B事務(wù)尚未提交的修改（update，delete和insert）） -> 不可重復(fù)讀（同一事務(wù)兩次讀到了不同的數(shù)據(jù)（select）） 
-- -> 幻讀 （）由于其他事務(wù)的插入或刪除操作，倒置兩次讀取不一樣
SET transction ISOLATION LEVEL 事務(wù)隔離級別名字 下一次事務(wù)隔離級別
SET SESSION TRANSACTION ISOLATION LEVEL 設(shè)置當(dāng)前回話隔離級別
SET GLOBAL TRANSACTION ISOLATION LEVEL 設(shè)置系統(tǒng)當(dāng)前隔離級別

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

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;

--查詢表里面成績最高分的同學(xué)信息--
SELECT * FROM tablenew WHERE 
table_score =
(SELECT MAX(table_score) FROM tablenew);

--查詢出成績高于平均分的成績的同學(xué)信息
SELECT * from tablenew
WHERE 
table_score > 
(SELECT AVG(table_score) FROM tablenew);


--查詢班級為一班的男生同學(xué)的所有信息
SELECT s.* FROM
(SELECT * FROM tablenew,tableold WHERE tablenew.table_id = tableold.old_classid) s
WHERE 
s.table_id = 1;



--創(chuàng)建視圖
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;

--創(chuàng)建一個班級分組后的視圖
CREATE VIEW table_view_two AS
SELECT * FROM tablenew,tableold WHERE tablenew.table_id = tableold.old_classid GROUP BY table_id;

--查詢一個班級人數(shù)大于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;


--開啟事務(wù)
SET autocommit = false; //關(guān)閉自動提交
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; --開啟事務(wù)，或者
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;

/**
 * JDBC demo 
 *   
 */
public class JDBCDemo {
	public static void main(String[] args) throws Exception {
		//注冊驅(qū)動
//		DriverManager.registerDriver(new com.mysql.jdbc.Driver());
		//加載驅(qū)動的方式
		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");

		//獲得執(zhí)行sql語句的對象
		Statement stmt = conn.createStatement();
		
		//執(zhí)行sql，獲得結(jié)果集
		String sql = "select stu_id, stu_name from tablenew";
		
		//執(zhí)行了sql，并且得到了結(jié)果集
		ResultSet rs = stmt.executeQuery(sql);
		
		//處理結(jié)果集
		while(rs.next()){
			System.out.println(rs.getObject(1)+"\t");
			System.out.println(rs.getObject("table_score") + "\t");
		}
		
		//關(guān)閉資源
		rs.close(); //結(jié)果集
		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;

/**
 * DButil.java
 * 
 */
public class DButil {
	//封裝一個靜態(tài)方法，用來啟動連接sql數(shù)據(jù)庫
	public static Statement getstsm() throws Exception{
		Class.forName("com.mysql.jdbc.Driver");//加載驅(qū)動
		//獲取連接
		Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test1", "root", "gdadmin");
		//返回連接結(jié)果表達(dá)式
		return conn.createStatement();
	} 
}