--一些簡單語句記錄：

/*mysql*/
SHOW DATABASES

CREATE DATABASE guoDROP DATABASE guo

/*查看創建的數據庫*/
SHOW CREATE DATABASE zf

/*字符集*/
CREATE DATABASE guo CHARACTER SET gbk
SHOW CREATE DATABASE guo
ALTER DATABASE guo CHARACTER SET utf8;
SHOW CREATE DATABASE guo

-- 創建表
USE zf
CREATE TABLE hh
(
id INT,
sex CHAR(50),
PRIMARY KEY(id)
)
--
自增列起始數值：
ALTER TABLE tt AUTO_INCREMENT = 100;

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
NAME CHAR(60) NOT NULL,
/*創建主鍵一定要這樣嗎？不知道*/
PRIMARY KEY (id)
);
DROP TABLE person

?

SHOW CREATE TABLE hh
ALTER TABLE hh ADD guo VARCHAR(50)
ALTER TABLE hh MODIFY guo VARCHAR(60)
ALTER TABLE hh DROP guo
SELECT * FROM hh
RENAME TABLE hh TO tt

ALTER TABLE tt CHARACTER SET utf8
-- 查詢表詳情
USE zf;
DESC tt

-- 關閉鏈接：quit

-- 版本號
SELECT VERSION() v;
-- 當前日期
SELECT CURRENT_DATE AS c;
SELECT CURDATE()

SELECT 1+2

-- 日期+時間
SELECT NOW();
-- 用戶
SELECT USER();
-- USE test 和quick一樣，無需分號，不過加上也沒關系；
-- SELECT * FROM pet WHERE (species = 'cat' AND sex = 'm')
-- ORDER BY BINARY col_name. 對大小寫排序；
--

USE zf;
-- 日期計算
SELECT YEAR('2012-4-5')

SELECT MONTH('2015-4-5');
SELECT DATE('2015-4-5');
SELECT DAYOFMONTH('2015-4-16');

-- month是整形；
SELECT YEAR(DATE_ADD(CURDATE(),INTERVAL 1 YEAR));

--
SELECT RIGHT('gzf123456',2)

SELECT LEFT('gzf',2)

SELECT * FROM tt WHERE sex IS NOT NULL

SELECT MOD(MONTH(CURDATE()), 12) + 1

SELECT MOD(14, 12)

-- 取余運算（求余數）
SELECT MOD(14, 12)

-- GROUP BY中，兩個NULL值視為相同。roder by中，NULL值當做是最小；
--

-- lilke '___':包含三個字符的...

SELECT * FROM tt WHERE sex LIKE '___'

-- 正則表達式 以a開始...正則表達式
SELECT * FROM tt WHERE sex REGEXP '^a';
-- SELECT * FROM pet WHERE name REGEXP 'fy$'----------^以什么開始，$以什么結尾，以fy結尾的。

-- REGEXP 'w';包含w的。

-- 正好包含5個字符：REGEXP '^.....$' 或者是 REGEXP '^.{5}$'

-- 查詢第一行：order by ...limit 1;

/*
經典查詢：
可以用這樣一個子查詢解決該問題：

SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

*/
-- enum類型，指的是字符串區間：不如定義列：color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL;
SHOW CREATE TABLE tt

-- 創建表的時候添加外鍵： owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id),
-- REFERENCES person(id),
-- 同事插入多列：
/*
INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', @last),
(NULL, 'dress', 'white', @last),
(NULL, 't-shirt', 'blue', @last);
*/

/*修改密碼
UPDATE mysql.user SET Password=PASSWORD('newpwd')
-> WHERE User='root';

/*顯示所有表的名稱*/*/
show tables;
/*顯示表結構*/

-- in ('湖南', '四川')

-- between 20 and 30

-- 包括=,<>,<,<=,>,>=）select * from students where name = '李山'

-- like '[^F-M]%' --------- (^排除指定范圍)

-- 修改密碼mysqladmin -uroot -password ab12

-- 增加用戶

GRANT SELECT,INSERT,UPDATE,DELETE ON *.* TO test1@"%" IDENTIFIED BY "abc";
GRANT SELECT,INSERT,UPDATE,DELETE ON mydb.* TO test2@localhost IDENTIFIED BY "abc";
GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' IDENTIFIED BY 'some_pass' WITH GRANT OPTION; -- 超級賬戶
FLUSH PRIVILEGES;-- 刷新權限
-- 刪除賬戶'權限，同時刪除user表。
DROP USER 'test1'@'%';
-- 修改密碼
/*
4.修改指定用戶密碼

@>mysql -u root -p

@>密碼

mysql>update mysql.user set password=password('新密碼') where User="test" and Host="localhost";

mysql>flush privileges;
*/
-- 對密碼進行加密

SELECT PASSWORD('gzf')

-- 查詢用戶權限
SHOW GRANTS FOR 'test1'@'%';

-- 取消權限 但需要手動刪除用戶表
REVOKE ALL ON *.* FROM test1@'%' ;
DELETE FROM USER WHERE USER = "test1" AND HOST = "%" ;
FLUSH PRIVILEGES;
-- 查詢當前用戶
SELECT USER()
-- 查詢當前數據庫
SELECT DATABASE();
-- 裝載文件數據[文本文件用tab隔開]
LOAD DATA LOCAL INFILE "C:/Users/ZeFeng/Desktop/gzf.txt" INTO TABLE tt;

-- 查詢語句
SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
SELECT "hello", "'hello'", "''hello''", "hello", "\"hello";

-- 十六進轉為字符
SELECT 0x636174
--
SELECT HEX('ert')
--
SELECT NULL=NULL
-- 和=相同
SELECT NULL<=>NULL
SET @t1='sfdsfd';
SELECT @t1
SELECT @t1:=(@t2:=1)+@t3:=4,@t1,@t2,@t3;
-- 查詢最小的
SELECT LEAST(5,7,3)
-- 查詢最大的
SELECT GREATEST(34.0,3.0,5.0,767.0);
--
SELECT ISNULL('')
-- 支持not和！
SELECT NOT 0
--
SELECT !1

DECLARE @a VARCHAR(50);
SELECT a:=sex FROM tt ORDER BY id LIMIT 1;
SELECT a;
-- 支持&&和||
SELECT * FROM tt WHERE id=123 AND sex IS NULL

-- 相當于三元運算
SELECT IF(1<2,2,3);

-- 返回兩者中不是null的數值,如果都為數值，返回第一個；
SELECT IFNULL(NULL,10);
SELECT IFNULL(10,12);
SELECT IFNULL(10,NULL);

--
SELECT CHAR('gzf')
--
SELECT CONCAT('My', 'S', 'QL','123');
-- 第一個參數為分隔符；這是與concat的區別；會忽略null,但不會忽略空字符串；
SELECT CONCAT_WS(',','First name','Second name','Last Name',NULL,' ','null');
-- 相當于sql的stuff
SELECT INSERT('Quadratic', 3, 4, 'What');

-- 日期：
SELECT DATE_ADD('1998-01-02', INTERVAL 31 DAY);
SELECT ADDDATE('1998-01-02', 31);
SELECT ADDTIME('1997-12-31 23:59:59.999999','1 1:1:1.000002');
SELECT CURTIME();
-- 轉化類型
SELECT CONVERT('abc' USING utf8);

--
SELECT CAST('2000-01-01' AS DATE);

SELECT CAST(1 AS CHAR) +'1233';
SELECT '1'+'2'

-- 將兩個字符串結合
SELECT CONCAT(1,2)
-- 加密

SELECT MD5('123')
SELECT PASSWORD('123')

-- 查看字符集
SELECT CHARSET('abc');

-- 強轉字符集
SELECT CHARSET(CONVERT('abc' USING latin1));

--
SELECT LAST_INSERT_ID();

-- 提取字符串
SELECT SUBSTRING_INDEX(USER(),'@',1);
SELECT SUBSTRING_INDEX('guo,ze,feng,4',',',4);
--
-- DEFAULT(col_name) 查看某列的默認值

-- 保留四位小數點
SELECT FORMAT(1234.00,4)

-- 唯一標識碼
SELECT UUID()

-- 修改列明,更改列的類型
ALTER TABLE tt CHANGE sex sexx INTEGER;
ALTER TABLE t1 CHANGE b b BIGINT NOT NULL;
-- 也可以使用change
-- 刪除多個列：
ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d;
-- 修改索引
ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

-- 創建索引
CREATE INDEX index_name ON table_name (column_list)

CREATE UNIQUE INDEX index_name ON table_name (column_list)
-- 刪除索引
DROP INDEX index_name ON talbe_name

ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE table_name DROP PRIMARY KEY
-- 查看索引
SHOW INDEX FROM tt;
-- 創建外鍵
ALTER TABLE tb_active ADD CONSTRAINT FK_ID FOREIGN KEY(user_id) REFERENCES tb_user(id)
-- 刪除外鍵
ALTER TABLE `tb_active` DROP FOREIGN KEY `FK_ID`
-- 查看函數功能是否開啟
SHOW VARIABLES LIKE '%func%';
-- 開啟函數：
SET GLOBAL log_bin_trust_function_creators=1;

-- 創建函數
DELIMITER $$
CREATE FUNCTION first_func(param1 VARCHAR(5),parmam2 VARCHAR(5),param3 VARCHAR(10))
RETURNS TINYINT
BEGIN
RETURN 1;
END
$$
DELIMITER ;
SELECT first_func('1','2','3');
-- 刪除函數
DROP FUNCTION first_func

-- 創建存儲過程
DELIMITER $$
CREATE PROCEDURE guo_pro(
canshu VARCHAR(10)
)
BEGIN
SELECT canshu;
END
$$
DELIMITER ;
CALL guo_pro('ggg');
-- 刪除存儲過程
DROP PROCEDURE guo_pro
-- 創建視圖
CREATE VIEW guo_view
AS SELECT * FROM tt;

SELECT * FROM guo_view

-- 復制表結構1,2
-- 1新版本
CREATE TABLE t1 LIKE tt
DROP TABLE t1
-- 通用
CREATE TABLE t1 SELECT * FROM tt WHERE 1=1
-- 創建臨時表
CREATE TEMPORARY TABLE guo(id INT)
INSERT INTO guo VALUES(1)
SELECT * FROM guo
-- 刪除虛擬表
DROP TABLE guo
-- 備份
-- 分頁:跳過前3條，取第四第五條數據；性能差

SELECT * FROM testtwo LIMIT 3,2
-- 提高性能
elect * FROM TABLE AS a INNER JOIN (SELECT id FROM TABLE ORDER BY id LIMIT m, n) AS b ON a.id = b.id ORDER BY a.id;

SQL代碼1：平均用時6.6秒 SELECT * FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 30

SQL代碼2：平均用時0.6秒 SELECT * FROM `cdb_posts` WHERE pid >= (SELECT pid FROM `cdb_posts` ORDER BY pid LIMIT 1000000 , 1) LIMIT 30
-- 相當于
/*
mysql中變量不用事前申明，在用的時候直接用“@變量名”使用就可以了。

第一種用法：set @num=1; 或set @num:=1; //這里要使用變量來保存數據，直接使用@num變量

第二種用法：select @num:=1; 或 select @num:=字段名 from 表名 where ……

注意上面兩種賦值符號，使用set時可以用“=”或“：=”，但是使用select時必須用“：=賦值”
*/

SET @a=1;
SELECT @a;

-- 創建表
CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT PRIMARY KEY);
-- 創建觸發器
CREATE TRIGGER gzf_tri BEFORE INSERT ON test
FOR EACH ROW BEGIN
-- INSERT INTO test2 SET a2 = NEW.a1;
-- DELETE FROM test3 WHERE a3 = NEW.a1;
-- UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
END
--

DELIMITER ;
-- 創建存儲過程
DELIMITER //
CREATE PROCEDURE p8 ()
BEGIN
DECLARE a INT;
DECLARE b INT;
SET a = 5;
SET b = 5;
END;
//
DELIMITER ;
DROP PROCEDURE p8
-- 事務
TRUNCATE TABLE test;
DELIMITER //
SET @a=0;
START TRANSACTION;
INSERT INTO test VALUES(333,'sdfs');
IF ROW_COUNT() < 1 THEN
SET @a = -1;
INSERT INTO test VALUES(444,'sdfs',00);
IF ROW_COUNT() < 1 THEN
SET @a = -1;
IF @=0 THEN
COMMIT;
ELSE
ROLLBACK;
//

COMMIT;
ROLLBACK;
SELECT * FROM test;
-- 控制語句

SELECT CASE 1 WHEN 0 THEN 'zero' WHEN 1 THEN 'one' ELSE 'no one' END;
SELECT IF(1=2,1,2)
-- 控制語句
DELIMITER //
CREATE PROCEDURE test_pro(
id INT
)
BEGIN
IF id>1 THEN SELECT 1;
ELSEIF id>2 THEN SELECT 2;
ELSE
SELECT 3;
END IF;
END
//
DELIMITER ;
CALL test_pro(3)
-- 存儲過程測試：
DROP PROCEDURE test_pro1;

CALL test_pro1(3);

--
-- 第一個如果是null,則返回第二個，否則返回第一個；
SELECT IFNULL(0,1)
SELECT IFNULL(NULL,1)
-- 如果第一個和第二個相等，則返回null;否則返回第一個
SELECT NULLIF(1,1)
SELECT NULLIF(1,2)
-- 查詢是否自動提交
SELECT @@autocommit;
-- 控制語句
DELIMITER //
CREATE PROCEDURE pro
BEGIN
DECLARE a INT DEFAULT 5;
IF a>2 THEN
SELECT @a;
ELSEIF @a<5 THEN
SELECT 2;
END IF;
END
//
-- while do用法：
DELIMITER /
CREATE PROCEDURE guo()
BEGIN
DECLARE gg INT DEFAULT 0;
DECLARE tt VARCHAR(50) DEFAULT '';
WHILE(gg<5) DO
SET gg=gg+1;
SET tt=CONCAT(tt,'a');
END WHILE;
SELECT tt;
END
/
DELIMITER ;
CALL guo();
DROP PROCEDURE guo

-- 循環控制語句
DELIMITER /
CREATE PROCEDURE ABC()
BEGIN
DECLARE a VARCHAR(50) DEFAULT '';
SET @a=1;
gzf: LOOP
SET @a=@a+1;
SET a=CONCAT(a,CONVERT(@a,CHAR));
IF @a=5 THEN
LEAVE gzf;
END IF;
END LOOP gzf;
SELECT a;
END
/
DELIMITER ;
CALL abc();
DROP PROCEDURE abc
-- 創建游標
DELIMITER /
CREATE PROCEDURE ABC()
BEGIN
DECLARE tmpName VARCHAR(20) DEFAULT '' ;
DECLARE allName VARCHAR(255) DEFAULT '' ;
DECLARE cur1 CURSOR FOR SELECT NAME FROM test ;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tmpname = NULL;
OPEN cur1;
FETCH cur1 INTO tmpName;
WHILE ( tmpname IS NOT NULL) DO
SET tmpName = CONCAT(tmpName ,";") ;
SET allName = CONCAT(allName ,tmpName) ;
FETCH cur1 INTO tmpName;
END WHILE;
CLOSE cur1;
SELECT allName ;
END
/
DELIMITER ;
CALL abc();
DROP PROCEDURE abc
-- loop游標：
ELIMITER $$

DROP PROCEDURE IF EXITS cursor_example$$
CREATE PROCEDURE cursor_example()
READS SQL DATA
BEGIN
DECLARE l_employee_id INT;
DECLARE l_salary NUMERIC(8,2);
DECLARE l_department_id INT;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT employee_id, salary, department_id FROM employees;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN cur1;
emp_loop: LOOP
FETCH cur1 INTO l_employee_id, l_salary, l_department_id;
IF done=1 THEN
LEAVE emp_loop;
END IF;
END LOOP emp_loop;
CLOSE cur1;
END$$
DELIMITER ;
-- repeat游標：
/*創建過程*/
DELIMITER //
DROP PROCEDURE IF EXISTS test //
CREATE PROCEDURE test()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE a VARCHAR(200) DEFAULT '';
DECLARE c VARCHAR(200) DEFAULT '';

DECLARE mycursor CURSOR FOR SELECT fusername FROM uchome_friend;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN mycursor;

REPEAT
FETCH mycursor INTO a;
IF NOT done THEN
SET c=CONCAT(c,a);/*字符串相加*/
END IF;

UNTIL done END REPEAT;

CLOSE mycursor;

SELECT c;
END //
DELIMITER ;

SHOW SESSION VARIABLES;
DROP PROCEDURE p8
--
-- 游標的使用

CREATE PROCEDURE gzf_proc ()
BEGIN
-- 需要定義接收游標數據的變量
DECLARE a VARCHAR(50);
DECLARE b VARCHAR(50);
-- 游標
DECLARE cur CURSOR FOR SELECT NAME FROM test;
-- 遍歷數據結束標志
DECLARE done INT DEFAULT FALSE;
-- 將結束標志綁定到游標
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
-- 打開游標
OPEN cur;

-- 開始循環
read_loop: LOOP
-- 提取游標里的數據，這里只有一個，多個的話也一樣；
FETCH cur INTO a;
-- 聲明結束的時候
IF done THEN
LEAVE read_loop;
END IF;
-- 這里做你想做的循環的事件
SET b=b+"-"+a;
END LOOP;
-- 關閉游標
CLOSE cur;

END

?

?

?

?

-- -- 與sqlserver不同的地方：mysql sqlwerver

/*
1.ALTER TABLE hh MODIFY guo VARCHAR(60) ALTER table Teacher alter column TName varchar(50)
2.ALTER TABLE hh DROP guo ALTER table Teacher drop column TName
3.RENAME TABLE hh TO tt 修改表名 sp_rename 'oldname','newname'
4.
*/

-- 與sqlserver相同的地方：
/*
創建數據庫和創建表都一樣
*/

?

Mysql個人語句筆記