mysql學習筆記(一)

標籤：

#if exists如果存在結果集就執行,if是where條件如果為true就執行
DROP TABLE IF EXISTS itcast_class;
#建立表格
CREATE TABLE itcast_class(
class_id INT PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(10) NOT NULL DEFAULT ‘‘
)CHARACTER SET utf8;
#添加欄位
ALTER TABLE itcast_class ADD stu_id INT;
#刪除欄位
ALTER TABLE itcast_class DROP COLUMN stu_id;
#添加資料
INSERT INTO itcast_class VALUES(NULL,‘班級四‘);
#修改資料
UPDATE itcast_class SET stu_id = 1 WHERE stu_id IS NULL;
#查詢表格
SELECT * FROM itcast_class;
#查詢表結構
SHOW CREATE TABLE itcast_class;

CREATE TABLE itcast_student(
stu_id INT PRIMARY KEY AUTO_INCREMENT,
stu_name VARCHAR(10) NOT NULL,
class_id INT,
#建立外鍵,一般項目不會使用外鍵,只會做邏輯關聯
FOREIGN KEY (class_id) REFERENCES itcast_class (class_id)
)CHARACTER SET utf8;

INSERT INTO itcast_student VALUES(NULL,‘趙六‘,4);

SELECT * FROM itcast_student;

CREATE TABLE itcast_teacher(
teacher_id INT PRIMARY KEY AUTO_INCREMENT,
teacher_name VARCHAR(10) NOT NULL,
teacher_days INT NOT NULL#上課天數
)CHARACTER SET utf8;

CREATE TABLE itcast_cla_tea(
id INT PRIMARY KEY AUTO_INCREMENT,
class_id INT NOT NULL,
teacher_id INT NOT NULL
)CHARACTER SET utf8;

SELECT * FROM itcast_cla_tea;

SELECT * FROM itcast_teacher;

#儲存引擎即是表類型，類型不同，儲存資料的速度會有影響，預設InnoDb，唯一支援外鍵的引擎，在mysql安裝目錄下的
#my.ini檔案中配置
#basedir = "D:\ProgramFiles\Mysql"
# datadir = .....
#datadir = "D:\ProgramFiles\Mysql\data"
# 資料庫引擎
#default-storage-engine=INNODB
# 設定mysql編碼
#character_set_server=utf8
# 設定mysql字元集
#collation-server=utf8_general_ci

#分組和排序
SELECT * FROM itcast_student GROUP BY class_id ORDER BY stu_id DESC;
SELECT * FROM itcast_student ORDER BY class_id,stu_id DESC;

#limit,限制查詢條數,即分頁,他有兩個條件,offset位移量,即起始索引,預設是0,row_count查詢總記錄數,即長度
SELECT * FROM itcast_student LIMIT 0,4;

#distinct去掉重複記錄,注:重複值是你查詢的所有欄位是否相同，而不是某一個欄位
#與之相對的是all，預設
#資料庫日誌系統用的較多,一個錯誤多次發生。
SELECT DISTINCT * FROM itcast_student;

#union聯集查詢,將多條select查詢結果集合并 注:union操作的結果集如果有重複資料，那麼會消除，類似distinct
#union all會查詢所有
#子語句order by排序時
#配合limit才會生效，union在做子語句時，會對沒有limit子句的order by失效(最佳化)
(SELECT * FROM itcast_student WHERE class_id = 1 ORDER BY stu_id DESC LIMIT 1)
UNION
(SELECT * FROM itcast_student WHERE class_id = 3 ORDER BY stu_id DESC LIMIT 1)
#子查詢
SELECT * FROM itcast_teacher WHERE teacher_days = (SELECT MAX(teacher_days) FROM itcast_teacher);
#帶過班級三的老師帶過那些班級？
SELECT * FROM itcast_class WHERE class_id IN (
SELECT class_id FROM itcast_cla_tea WHERE teacher_id IN(
SELECT teacher_id FROM itcast_cla_tea WHERE class_id =
(SELECT class_id FROM itcast_class WHERE class_name = ‘班級三‘)));

#和in同樣的運算子any,all,some和any一樣,只是國外為了兼顧他們的程式員理解和出現的文法
#=any()和in一樣
#!=any()不等同於not in，在實際開發中很少用，因為只要這個值和集合中任意一個數不相等即會查出,一般都會查出所有
#=all()等於集合中所有,一般一個值都查不出
#!=all()值和集合中的任意一個都不相等,查出集合外的所有值
#行子查詢 select * from 表名 where (欄位1,欄位2...) = (select (欄位1,欄位2...)from 表名);
#表子查詢
SELECT * FROM (SELECT * FROM itcast_student WHERE stu_id > 20) AS temp WHERE stu_name LIKE ‘李%‘;

#表名查詢
SHOW TABLES;

#exists是否存在
SELECT * FROM itcast_class AS cla WHERE EXISTS;
(SELECT * FROM itcast_cla_tea WHERE cla.class_id = class_id);

CREATE TABLE join_teacher(
id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(20),
gender ENUM(‘male‘,‘female‘,‘secret‘)#性別，使用枚舉類型
)ENGINE INNODB CHARACTER SET utf8;

INSERT INTO join_teacher VALUES
(1,‘韓星‘,‘male‘),
(2,‘李白‘,‘female‘),
(3,‘韓非子‘,‘secret‘),
(‘4‘,‘孫武‘,‘male‘);

SELECT * FROM join_teacher;

CREATE TABLE join_class(
id INT PRIMARY KEY AUTO_INCREMENT,
c_name CHAR(5),
room CHAR(3)
)ENGINE INNODB CHARACTER SET utf8;

#修改欄位類型
ALTER TABLE join_class MODIFY COLUMN c_name CHAR(7);
SHOW CREATE TABLE join_class;

INSERT INTO join_class VALUES
(1,‘php0115‘,‘207‘),
(2,‘php0228‘,‘104‘),
(3,‘php0331‘,‘102‘),
(4,‘php0505‘,‘202‘);

SELECT * FROM join_class;

CREATE TABLE join_teacher_class(
id INT PRIMARY KEY AUTO_INCREMENT,
t_id INT,
c_id INT,
days TINYINT,
begin_date DATE,
end_date DATE
)ENGINE INNODB CHARACTER SET utf8;

INSERT INTO join_teacher_class VALUES
(1,1,1,15,‘2013-01-15‘,‘2013-02-20‘),
(2,1,2,18,‘2013-02-28‘,‘2013-03-30‘),
(3,1,3,22,‘2013-03-31‘,‘2013-05-05‘),
(4,2,1,20,‘2013-02-22‘,‘2013-03-25‘),
(5,2,2,22,‘2013-03-31‘,‘2013-04-29‘),
(6,3,1,15,‘2013-03-27‘,‘2013-04-18‘),
(7,1,1,15,‘2013-04-19‘,‘2013-05-01‘),
(8,3,3,15,‘2013-05-28‘,‘2013-06-15‘),
(9,2,1,5,‘2013-05-04‘,‘2013-05-15‘);

SELECT * FROM join_teacher_class;

#內串連,串連資料不存在則不會出現
SELECT t.t_name,t_c.days FROM join_teacher AS t INNER JOIN join_teacher_class AS t_c
ON t.id = t_c.t_id;

#交叉串連,即笛卡爾積
SELECT t.t_name,t_c.days FROM join_teacher AS t CROSS JOIN join_teacher_class AS t_c;

#on和where都是條件式篩選,但是on是在資料形成連結時判斷，不通過就不串連,而where是資料形成笛卡爾積後判斷篩選
#簡單說on是判斷後形成新表,where是新表形成後判斷,邏輯含義不一樣
SELECT t.t_name,t_c.days FROM join_teacher AS t INNER JOIN join_teacher_class AS t_c
ON t.id = t_c.t_id WHERE t_c.days>15;

#using快捷文法,這裡只做文法測試,即兩張表的id會自動匹配,實際中會和一張表的外鍵匹配,而他們的欄位必須相同
SELECT t.t_name,t_c.days FROM join_teacher AS t INNER JOIN join_teacher_class AS t_c
USING(id);

#外串連,串連資料不存在也會以空出現
SELECT t.t_name,t_c.days FROM join_teacher AS t LEFT OUTER JOIN join_teacher_class AS t_c
ON t.id = t_c.t_id

SELECT t.t_name,t_c.days FROM join_teacher AS t RIGHT OUTER JOIN join_teacher_class AS t_c
ON t.id = t_c.t_id

#自然串連NATURAL，它會自己找相同的欄位如id匹配,分內外
SELECT * FROM join_teacher NATURAL JOIN join_teacher_class;

#查詢資料匯入硬碟，存資料備份
#預設欄位之間用‘\t‘，行之間用‘\d‘
SELECT NULL,stu_name,class_id INTO OUTFILE ‘C:/Users/Administrator/Desktop/itcast_student‘ FROM itcast_student;

#匯入資料
LOAD DATA INFILE ‘C:/Users/Administrator/Desktop/itcast_student‘ INTO TABLE itcast_student;

#修改預設分隔
SELECT * INTO OUTFILE ‘C:/Users/Administrator/Desktop/itcast_student‘
FIELDS TERMINATED BY ‘\t‘ ENCLOSED BY ‘\‘‘#欄位分隔
LINES TERMINATED BY ‘\n‘ STARTING BY ‘start:‘#行分隔
FROM itcast_student;

#二進位匯出
#測試幾次，好像只能匯出一條沒有分隔的資料
SELECT * INTO DUMPFILE ‘C:/Users/Administrator/Desktop/itcast_student‘ FROM itcast_student LIMIT 1;

#不同插入
INSERT INTO itcast_student SET stu_name = ‘王夫子‘,class_id = 2;

#當插入主鍵衝突時可以改為修改
INSERT INTO itcast_student VALUES(2,‘麻子‘,2)
ON DUPLICATE KEY UPDATE
stu_name=‘麻子‘,class_id=2;

#REPLACE==ON DUPLICATE KEY UPDATE
REPLACE INTO itcast_student VALUES(3,‘流雲‘,3);

#查詢插入*蠕蟲複製
INSERT INTO itcast_student(stu_name,class_id) SELECT stu_name,class_id FROM itcast_student;

SELECT * FROM itcast_student;

DESC itcast_student;

SELECT * FROM itcast_student;
SELECT * FROM itcast_class;
SELECT * FROM itcast_cla_tea;

#多表刪除,刪除一班相關所有的詳細資料
SELECT * FROM
(SELECT c.*,t.stu_id,t.stu_name FROM itcast_class AS c JOIN itcast_student AS t
ON c.class_id = t.class_id WHERE c.class_name=‘班級一‘ )AS temp
JOIN itcast_cla_tea AS c_t ON temp.class_id = c_t.class_id;

SHOW CREATE TABLE itcast_student;
#刪除外鍵，外鍵名稱沒有寫時，系統預設：表名_ibfk_n(n代表第幾個外鍵)
ALTER TABLE itcast_student DROP FOREIGN KEY itcast_student_ibfk_1;

#兩張表刪除
DELETE FROM itcast_class,itcast_student USING
itcast_class JOIN itcast_student
ON itcast_class.class_id = itcast_student.class_id WHERE itcast_class.class_name=‘班級一‘

SELECT * FROM
(SELECT c.*,t.stu_id,t.stu_name FROM itcast_class AS c JOIN itcast_student AS t
ON c.class_id = t.class_id WHERE c.class_name=‘班級二‘ )AS temp
JOIN itcast_cla_tea AS c_t ON temp.class_id = c_t.class_id;

#三張表刪除
DELETE FROM itcast_class,itcast_student,itcast_cla_tea USING
itcast_class JOIN itcast_student
ON itcast_class.class_id = itcast_student.class_id JOIN itcast_cla_tea
ON itcast_cla_tea.class_id = itcast_class.class_id
WHERE itcast_class.class_name=‘班級二‘

#TRUNCATE刪除表，建立表，不會記錄原來表的資訊，最典型的是索引從零開始
#delete逐行刪除，儲存原表的一些資訊，比如索引不變

#多表更新
UPDATE tab1 JOIN tab2 ON tab1.a = tab2.a SET tab1.b = ‘‘,tab2.c=‘‘ WHERE tab1.id=‘‘;

#備份還原
#1,最簡單的方法是直接到mysql檔案夾中找到對應表資料(frm)，表結構(myd)，表格儲存體引擎(myi)三個檔案打包
#需要時將他們解壓到指定的資料目錄下
#注:預設innodb儲存引擎下的表執行該方法，使用show tables可以看到該表名，但是不能使用

#2，備份:直接在cmd中的mysqld.exe中執行 mysqldump -uroot -p test > C:/Users/Administrator/Desktop/test.sql
#還原:source C:/Users/Administrator/Desktop/test.sql或者直接執行sql檔案中的sql命令
#而在sqlYong編輯器中在最為方便簡單，直接在工具選項中執行相應操作就好

#s視圖
CREATE TABLE info_teacher(
id INT PRIMARY KEY AUTO_INCREMENT,
t_name VARCHAR(10),
salary DECIMAL(10,2)
);

INSERT INTO info_teacher VALUES
(NULL,‘孔子‘,100.45),
(NULL,‘老子‘,200.10),
(NULL,‘離子‘,155.44);

SELECT * FROM info_teacher;

CREATE VIEW v_teacher AS SELECT id,t_name FROM info_teacher;
INSERT INTO v_teacher VALUES(NULL,‘宏利‘);

SELECT * FROM v_teacher;

#視圖的理解，即是查詢語句的封裝，簡化一些複雜商務邏輯
#內部執行過程有兩種，1，合并執行方式，即查詢中套用子查詢
#2，暫存資料表，將視圖作為暫存資料表，然後操作它

#事務
CREATE TABLE zj_School(
id INT PRIMARY KEY AUTO_INCREMENT,
cz_money DECIMAL(10,2) COMMENT ‘班費‘
)ENGINE INNODB CHARACTER SET utf8;

INSERT INTO zj_School VALUES(NULL,1250);
SELECT * FROM zj_School;

CREATE TABLE zj_Student(
id INT PRIMARY KEY AUTO_INCREMENT,
stu_money DECIMAL(10,2) COMMENT ‘生活費‘
)ENGINE INNODB CHARACTER SET utf8;

INSERT INTO zj_Student VALUES(NULL,5000);
SELECT * FROM zj_Student;

#班費30
UPDATE zj_Student SET stu_money = stu_money - 30 WHERE id = 1;
UPDATE zj_School SET cz_money = cz_money + 30 WHERE id = 1;

#是否提交
SET autocommit = 1;
SHOW VARIABLES LIKE ‘autocommit‘;
#成功commit
#失敗rollback一般不用這種方式操作事務，會影響別的操作

#開啟事務
START TRANSACTION;
#提交
COMMIT;
#事務在innodb或者DBD兩種儲存引擎中生效

#1,原子性，即同時成功或者同時失敗
#2,一致性，你在一個資料庫連接裡開啟了事務，若是沒有進行操作，可以在其他串連中操作，但是，一旦你在
#開啟事務的串連中操作了某張表而沒有提交，此時無法再其他串連中操作該表
#3，隔離性，事務之間不會相互影響
#4，持久性，一旦提交，永久生效(ACID事務特點)

#觸發器
#Create trigger 名字 事件 執行代碼
#觸發不包括查詢，即事件關鍵字三種insert,delete,update,執行時機兩種After,before,組合一起共六種事件
#old和new擷取事件操作的資料
#insert不能使用old
#delete不能使用new

#1，觸發器不能同名
#2，目前mysql只支援一類事件設定一個觸發器
CREATE TRIGGER pay_cost AFTER UPDATE ON zj_student
FOR EACH ROW
UPDATE zj_school SET cz_money = cz_money + 20;

UPDATE zj_student SET stu_money = stu_money - 40 WHERE id = 1;

SHOW CREATE TRIGGER pay_cost;

DROP TRIGGER pay_cost;

CREATE TRIGGER pay_cost AFTER UPDATE ON zj_student
FOR EACH ROW
UPDATE zj_school SET cz_money = cz_money + (old.stu_money - new.stu_money);

#如果多條sql語句，需要使用特殊字元
DELIMITER $$
CREATE TRIGGER ruxue AFTER INSERT ON zj_student
FOR EACH ROW
BEGIN
UPDATE zj_school SET stu_count = stu_count+1;
UPDATE zj_school SET stu_money = stu_money+20;
END
$$

DELIMITER;

#sql編程
SELECT NOW();
SHOW VARIABLES LIKE ‘char%‘;
#定義變數set 變數名 = 變數值;為了區分系統和自訂變數，需要在變數前加上@
SET @who = ‘李想‘;
SELECT @who;
#into注入
SELECT 10,20,30 INTO @a,@b,@c;#該方式可以查詢注入
SELECT @who := ‘小李‘;#賦值
SELECT @who = ‘大李‘;#判斷語句

#變數有效期間:串連中斷，範圍:使用者定義是全域，函數內部是局部
SELECT @a,@b,@c;

SELECT RAND();#1到0之間的隨機數，mysql各種內建函數自己百度瞭解
SELECT FROM_UNIXTIME(UNIX_TIMESTAMP());
SELECT NOW();
SELECT LENGTH(‘sdbasjd‘);
SELECT LENGTH(‘位元組數‘);#utf8三位元組
SELECT CHAR_LENGTH(‘字元數‘);
SELECT SUBSTRING(‘中國好聲音‘,3,3);#位置從1開始
SELECT LPAD(‘1‘,4,‘0‘);#補足位元，從左邊開始
SELECT RPAD(‘2‘,3,‘0‘);#右邊開始
SELECT MD5(‘1‘);#加密函數
SELECT PASSWORD(‘1‘);

#自訂函數(儲存函數)create function 函數名(參數列表) 傳回值類型 函數體

DELIMITER $$
CREATE FUNCTION sayHello() RETURNS VARCHAR(20)
BEGIN
RETURN ‘Hello World!‘;
END
$$

DELIMITER;

SELECT sayHello();

#sql中的流程式控制制
DELIMITER $$
CREATE FUNCTION fun1() RETURNS VARCHAR(20)
BEGIN
IF HOUR(NOW()) > 18 THEN
RETURN ‘晚‘;
ELSE
RETURN ‘早‘;
END IF;
END
$$

DELIMITER;

SELECT fun1();

DROP FUNCTION IF EXISTS fun2;
DELIMITER $$
CREATE FUNCTION fun2() RETURNS INT
BEGIN
SET @i = 1;
SET @sun = 0;
WHILE @i <= 10 DO
SET @sun = @sun + @i;
SET @i = @i + 1;
END WHILE;
RETURN @sun;
END
$$

DELIMITER;

SELECT fun2();

#迴圈提前終止,退出迴圈和java不同，不是由位置決定，而是由標籤決定
#leave 相當於 break
#iterate 相當於 continue

DELIMITER $$
CREATE FUNCTION fun3() RETURNS INT
BEGIN
SET @i = 1;
SET @sun = 0;
w:WHILE @i <= 10 DO
IF @i = 5 THEN
LEAVE w;
END IF;
SET @sun = @sun + @i;
SET @i = @i + 1;
END WHILE w;

RETURN @sun;
END
$$

DELIMITER;

SELECT fun3();

DELIMITER $$
DROP FUNCTION IF EXISTS fun4;
CREATE FUNCTION fun4(u_name VARCHAR(10)) RETURNS VARCHAR(20)
BEGIN
RETURN CONCAT(‘hello‘,u_name);
END
$$

DELIMITER;

SELECT fun4(‘你好‘);

SHOW CREATE FUNCTION fun4;

#where和having一樣，不過where條件作用於表，而having作用於查詢後的結果集
SELECT * FROM itcast_student WHERE class_id = 3 HAVING stu_name = ‘流雲‘;

#四大索引
#普通索引 (index)
#主鍵索引 (primary key)
#唯一索引 (unique)
#全文索引 (fulltext)

mysql學習筆記(一)