?

part-1:

USE mysql;
CREATE TABLE tb_x
(
id INT,
NAME CHAR(10)
);
INSERT INTO tb_x VALUES(5,"a");
SELECT COUNT(*) AS total_Pepolefrom FROM tb_x WHERE id>0;
#統計函數:條目數
#再次通過修改tb_x增加一個字段用于一下統計
ALTER TABLE tb_x ADD money INT;

#單獨選擇money字段插入測試值
INSERT INTO tb_x(money)VALUES(210);

SELECT AVG(money) ,MAX(money),MIN(money),SUM(money) speend FROM tb_x;
#統計函數:{平均值,最大值，最小值，求和}

#
SELECT id, GROUP_CONCAT(NAME) AS NAMES ,COUNT(NAME) FROM tb_x GROUP BY id;
#對指定字段進行分組,以篩選不必要的數據
#分組實現統計查詢

SELECT * FROM tb_x;
SELECT id,money,GROUP_CONCAT(NAME) AS NAMES,COUNT(NAME) FROM tb_x GROUP BY id,money;
#分組實現多字段查詢并統計

#創建一張新表包含{ename,salary,id)
CREATE TABLE tb_y
(
id INT,
ename VARCHAR(20),
salary INT
);
INSERT INTO tb_y VALUES(6,"ru",490);

SELECT salary,ename ,GROUP_CONCAT(ename) AS enames,COUNT(ename) FROM tb_y GROUP BY
ename HAVING salary>1000;
#限制分組的條目

#實現多表連接查詢

DROP TABLE tb_z;
CREATE TABLE tb_z
(
id INT,
ename CHAR(10),
lname VARCHAR(40)
);

DROP TABLE tb_w;
CREATE TABLE tb_w
(
id INT,
wname CHAR(10),
sex VARCHAR(40)
);

CREATE TABLE tb_n
(
id INT,
nname CHAR(10),
nsex VARCHAR(20)
);

INSERT INTO tb_z VALUES(1,"aa","ab"),(2,"fg","women"),(5,"tg","man"),(3,"oe","man");
INSERT INTO tb_w VALUES(1,"aa","aa"),(4,"ty","man"),(6,"yi","man"),(5,"oy","women");
INSERT INTO tb_n VALUES(2,"aa","aa"),(6,"fi","po"),(5,"yi","woap"),(1,"fj","wjw");

SELECT * FROM tb_z;
SELECT * FROM tb_w;
#單張表實現內連接

#where 實現內連接
SELECT z.ename ,z.id,l.lname
FROM tb_z z ,tb_z l
WHERE z.ename=l.lname;
#inner join on實現內連接

SELECT z.ename,z.id,l.lname
FROM tb_z z INNER JOIN tb_z l ON z.ename=l.lname;

#多張表實現內連接

SELECT z.ename,z.lname,w.wname
FROM tb_z z INNER JOIN tb_w w
ON z.ename=w.wname;
SELECT z.ename,z.lname,w.sex
FROM tb_z z,tb_w w
WHERE z.ename=w.wname;


#再次把內連接擴大化
SELECT z.ename,z.lname,w.sex,n.nsex
FROM tb_z z /*inner join*/LEFT JOIN tb_w w ON z.ename=w.wname
/*inner join*/LEFT JOIN tb_n n ON n.nname=w.wname;
#通過修改連接形式{左連接,右連接，外連接}
SELECT z.ename,z.lname,w.sex,n.nsex
FROM tb_z z,tb_w w,tb_n n
WHERE z.ename=w.wname AND n.nname=w.wname;

SELECT * FROM tb_w
UNION ALL #聯合多個表,合并字段{all不可避免重復數據}
SELECT * FROM tb_z;

CREATE TABLE company
(
id INT,
salary INT,
NAME VARCHAR(20)
);
INSERT INTO company VALUES(2,5670,"smith"),(2,5670,"zburk"),(7,6678,"amblamba"),(2,5569,"tines");

SELECT * FROM company;
#實現子查詢,規定薪水大于員工smith的
SELECT * FROM company WHERE salary >(
SELECT salary FROM company
WHERE NAME="smith"
);
#實現子查詢，規定編號和薪水和員工smith相同的員工
SELECT NAME,id,salary FROM company WHERE(id,salary)=ANY(
SELECT id,salary FROM company
WHERE NAME="smith"#limit 1
);
#報錯:子查詢無法匹配多個參數字段?
#解決:limit 1:限制顯示條目數
# any:匹配符合條件的全部條目

#為公司創建另一張表為辦理離職
CREATE TABLE company_x
(
NAME VARCHAR(20),
dates TIME,
days INT
);
INSERT INTO company_x VALUES("poqe",2018,100),("tya",2016,27),("quenn",2018,104);

SELECT * FROM company_x;

#規定子查詢，凡是入職的員工在離職的員工表出現過，給與其辦理離職手續
SELECT * FROM company WHERE NAME IN (
SELECT NAME FROM company_x
);
#規定子查詢，對于入職的員工的薪水在離職的員工工作時間在100到200天的辦理離職

SELECT NAME,salary FROM company WHERE salary =ALL /*any*/(
SELECT salary FROM company_x
WHERE days IN(100,200)
);
#all,any子查詢關鍵字,匹配范圍和條件不同


SELECT * FROM company;
SELECT * FROM company_x;
#exists子查詢關鍵字，布爾匹配
SELECT * FROM company castQ
WHERE EXISTS (
SELECT *
FROM company_x
WHERE NAME=c.name
);

SELECT * FROM company;
#運算符

SELECT salary/10 AS salary_x ,5*10+1-2 ,10/5+1*2 FROM company;
#算數運算符:{+,-，*，/}

SELECT 1>2 AND 3<2
UNION ALL
SELECT "abc"<>"def"
UNION ALL
SELECT "abc"<=>"abc"; #比較運算符:{>,<,<>,<=>,!=}

#特殊比較運算符:正則匹配
SELECT "hello" REGEXP "o$" #結尾
UNION ALL
SELECT "hello" REGEXP "^h" #開頭
UNION ALL
SELECT "hello" REGEXP "[h..o]" #字符中間
UNION ALL
SELECT "hello" REGEXP "[a-b]" #范圍
UNION ALL
SELECT "hello" REGEXP "[^g~z]" #范圍之外
UNION ALL
SELECT "hello" REGEXP "h+o" #字符包含
UNION ALL
SELECT "hello" REGEXP "h*o"
UNION ALL
SELECT "hello" REGEXP "h|e|o" #存在
UNION ALL
SELECT "hello" REGEXP "l{2}" #出現次數
UNION ALL
SELECT "hello" REGEXP "1{0,2}";

SELECT 1 || 2 #邏輯運算符:{||(or),&&(and),xor}
UNION ALL
SELECT 1 OR 2
UNION ALL
SELECT 1 && 2
UNION ALL
SELECT 1 AND 2
UNION ALL
SELECT 1 XOR 2
/*
union all
select 1 not 2;

*/ #位運算符:{|,&,^,~,>>,<<}
SELECT 3|4|5,BIN(3|4|5) #按位或
UNION ALL
SELECT 3 & 4 & 5,BIN(3 & 4 & 5) #按位與
UNION ALL
SELECT 3 ^ 4 ^ 5,BIN(3 ^ 4 ^ 5) #按位異或
UNION ALL
SELECT ~1,BIN(~1) #取反
UNION ALL
SELECT 3>>4,BIN(3>>4) #右移
UNION ALL
SELECT 4<<5,BIN(4<<5); #左移

#函數


SELECT CONCAT_WS("-",NULL,"10","20");
#字符函數:{concat():連接字符串,concat_ws():條件分割連接字符串}

SELECT STRCMP("abc","abc"),STRCMP("abc","abb"),STRCMP("abc","abd");

SELECT LENGTH("mysql"),CHAR_LENGTH("mysql");
#字符函數:{strcmp():比較字符串大小;length()&&char_length():字符串長度}

SELECT UPPER("mysql"),UCASE("mysql"),LOWER("MYSQL"),LCASE("MYSQL");
#字符串函數:{upper&&ucase:字符轉大寫;lower&&lcase:字符轉小寫}
SELECT FIND_IN_SET("mysql","oracle,sql server,mysql"),
FIELD("mysql","oracle","sql server","mysql"),
INSTR("mysql","q"),
LOCATE("mysql","veryfastandmysqlisyoubielve"),
POSITION("mysql" IN "veryfastandmysqlisyoubielve")
; #字符串函數:{find_in_set(),locate(),position(),filed(),insert():查找字符串的位置}

SELECT ELT(2,"cisco","h3c","huawei");
SELECT BIN(7),MAKE_SET(7,"cisco","h3c","huawei");
#字符串函數:{elt():指定要顯示特定字符串位置,make_set():按二進制匹配};

SELECT LEFT("mysql",2),RIGHT("mysql",3),SUBSTRING("mysql",3,3),MID("mysql",3,3);
#字符串函數:{left():左截取;right():右截取;substring(),med():按位置和長度截取}

SELECT "未處理的字符串長度",CHAR_LENGTH(CONCAT("-"," mysql ","-"))
UNION ALL
SELECT "處理后的字符串長度",CHAR_LENGTH(CONCAT("-",TRIM(" mysql "),"-"));
#字符串函數:{ltrim(),trim():處理字符串中的空格}

SELECT "是個不錯的mysql數據庫" AS "原字符串" ,
INSERT("是個不錯的mysql數據庫",10,1,"oracle")
AS "目的字符串";
#字符串函數:{insert():替換指定位置的字符}

SELECT REPLACE("高性能的網站后臺處理mysql數據庫","mysql","oracle");
#字符串函數:{replace():字符替換函數}

#數學函數:{fllor(),cell(),trucate(),round()};
SELECT FLOOR(2.45) AS "取大的整數",CEIL(4.02) AS "取小的整數",
TRUNCATE(45.00,-1) AS "保留小數后一位" ,ROUND(3.1415926,3) AS "四舍五入";
#日期函數:{now(),current_date(),current_time()}
SELECT NOW() AS "現在的日期和時間",
CURRENT_DATE() AS "現在的日期",
CURRENT_TIME() AS "現在的時間"
;

SELECT UNIX_TIMESTAMP(NOW()) AS "unix時間",
FROM_UNIXTIME(NOW()) "普通時間"
;

SELECT UTC_DATE() AS "中文國際日期",
UTC_TIME() AS "中文國際時間";

SELECT NOW() AS "當前的日期" , QUARTER(NOW())"當前的季度", YEAR(NOW()) AS "當前的年份",
MONTH(NOW()) AS "當前的月份",MINUTE(NOW()) AS "當前的分鐘" , HOUR(NOW()) AS "當前的小時"
;

SELECT MONTHNAME(NOW()) "英文月份",DAYOFMONTH(NOW()) "月的第幾天" ,DAYNAME(NOW())
"英文天數",WEEKOFYEAR(NOW()) "年的第幾周",WEEKDAY(NOW()) "星期的第幾天";

SELECT EXTRACT(YEAR FROM NOW()) "年" ,EXTRACT(MONTH FROM NOW()) "月"
,EXTRACT(WEEK FROM NOW()) "星期";

SELECT NOW() "當前時間",TO_DAYS(NOW())"據原始的時間",
FROM_DAYS(TO_DAYS (NOW())) "相差的時間";

SELECT DATEDIFF(NOW(),"2000-01-20"); #datediff():計算日期差

SELECT ADDDATE(CURDATE(),5) AS "5天后的日期",SUBDATE(CURDATE(),5) "5天前的日期";

SELECT CURDATE() "當前的日期",ADDDATE(CURDATE(),INTERVAL "2,3" YEAR_MONTH) "2年3個月后的日期",
SUBDATE(CURDATE(),INTERVAL "2,3" YEAR_MONTH) "2年3個月之前的日期";

?

SELECT CURTIME() "當前的時間",ADDTIME(CURTIME(),5) "5s后的時間",SUBTIME(CURTIME(),5)
"5s前的時間";

?

SELECT VERSION() "系統版本",USER() "登陸用戶";

#為last_inset_id創建一張表
DROP TABLE test_x;
CREATE TABLE test_x
(
id INT NOT NULL AUTO_INCREMENT UNIQUE
);

INSERT INTO test_x VALUES(NULL);

#執行系統函數獲取最后自增的id字段號
SELECT LAST_INSERT_ID(id) FROM test_x;

#其它函數
SELECT INET_ATON("127.0.0.1") AS "ip地址轉數字";
SELECT INET_NTOA("1230133112") AS "數字轉ip地址";

?

?

part-2:

#為存儲過程創建一張表
CREATE TABLE tb_y
(
id INT,
salary INT
);
INSERT INTO tb_y VALUES(1,130);

#創建一個存儲過程為查詢字段salary
DELIMITER $$
CREATE PROCEDURE pro_y()
COMMENT "開始這個存儲過程"
BEGIN
SELECT salary FROM tb_y;
END$$

DELIMITER;
#使用這個存儲過程

DELIMITER $$
#創建函數
CREATE FUNCTION fu_y(id INT) #函數參數

RETURNS INT #返回另一個數據類型
COMMENT "這是一個函數"
BEGIN
RETURN(
SELECT salary FROM tb_y
WHERE tb_y.id=id
);
END$$
DELIMITER;

/*
create view vi_y
as
select salary from tb_y;
*/

USE mysql;
SHOW PROCEDURE STATUS; #pro_y ;
#查看存儲過程
SHOW PROCEDURE STATUS LIKE "pro_y";
#函數信息
SHOW FUNCTION STATUS LIKE "fu_y";

#MariaDB [information_schema]> select * from ROUTINES\G;
#系統表的存儲過程
USE mysql;
SHOW CREATE FUNCTION fu_y ; #函數的簡要信息
SHOW CREATE PROCEDURE pro_y; #存儲過程的簡要信息

?

?

?

part-3:

?

USE mysql;
SHOW PROCEDURE STATUS; #pro_y ;
#查看存儲過程
SHOW PROCEDURE STATUS LIKE "pro_y";
#函數信息
SHOW FUNCTION STATUS LIKE "fu_y";

#MariaDB [information_schema]> select * from ROUTINES\G;
#系統表的存儲過程
USE mysql;
SHOW CREATE FUNCTION fu_y ; #函數的簡要信息
SHOW CREATE PROCEDURE pro_y; #存儲過程的簡要信息

/*事務*/
#創建一個表格，為事務提供條件
CREATE TABLE tb_r
(
id INT,
NAME CHAR(10)
);
INSERT INTO tb_r VALUES(1,"a");
BEGIN; #開始事務
SELECT * FROM tb_r;
UPDATE tb_r SET NAME="b" WHERE id=1;
COMMIT; #提交事務
DELETE FROM tb_r WHERE NAME=id;
ROLLBACK; #回滾事務

#修改id字段
BEGIN;
ALTER TABLE tb_r MODIFY NAME VARCHAR(10);
COMMIT;
ALTER TABLE tb_r MODIFY NAME CHAR(10);
ROLLBACK;
DESC tb_r;

#不可讀事務
#A事務
BEGIN; #開始
SELECT * FROM tb_r;
#B事務
BEGIN;
UPDATE tb_r SET id=id*2 WHERE NAME="b";
#
SELECT * FROM tb_r;
#B事務回滾
ROLLBACK;
#A事務臟讀
SELECT * FROM tb_r;
#不可重復讀事務

#A事務
SELECT * FROM tb_r;
#B事務
UPDATE tb_r SET id=id*2 WHERE NAME="b";
#開始A事務
SELECT * FROM tb_r;
#開始B事務
BEGIN;
#B事務提交
COMMIT;
#A事務狀態
SELECT * FROM tb_r;
#隱式提交

#A事務狀態
SELECT * FROM tb_r;
#B事務開始
BEGIN;
#B事務DDL語句
UPDATE tb_r SET id=1 WHERE NAME="b";
#COMMIT
#A事務中間狀態
SELECT * FROM tb_r;#A事務狀態
#除DDL語句觸發B事務自動提交
ALTER TABLE tb_r ADD sex CHAR(10);
#A事務結束狀態
SELECT * FROM tb_r;#A事務狀態

#鎖機制
#A,B事務開始
BEGIN;
#A,B事務同時為表單更新id字段
UPDATE tb_r SET id=2*3 WHERE NAME="b";
UPDATE tb_r SET id=2*4 WHERE NAME="b";
#同時提交A,B事務
COMMIT;
#查看A事狀態遭到鎖機制
SELECT * FROM tb_r;

#create創建用戶
CREATE USER u1@localhost IDENTIFIED BY "123";
#inset into創建用戶
USE mysql;
INSERT INTO USER(HOST,USER,PASSWORD)VALUES("localhost","u2","123");

FLUSH PRIVILEGES;
#授權并創建用戶
GRANT ALL ON PRIVILEGES mysql.* TO u3@localhost IDENTIFIED BY "123";
#修改root密碼
#grant select ,show on *.* to "u3"@"localhost" identified by "123";

SET PASSWORD FOR "u3"@"localhost"=PASSWORD("123");

#為普通用戶創建密碼
SET PASSWORD=PASSWORD("123");
USE mysql;
UPDATE mysql.user SET PASSWORD=PASSWORD("456")
WHERE HOST="localhost" AND USER="u3";
SELECT PASSWORD ,USER FROM USER;

DROP USER u2@localhost; #刪除用戶

CREATE USER u2@localhost;

DELETE FROM mysql.user
WHERE USER="u2" AND h7ost="localhost"

SELECT * FROM USER;
GRANT SELECT ON *.* TO u2@localhost IDENTIFIED BY "123";

#作用在所有的表上
SHOW GRANTS FOR u1@localhost;
#查看用戶權限

REVOKE SELECT ON *.* FROM u1@localhost ;
#回收用戶權限
REVOKE ALL PRIVILEGES,GRANT OPTION
ON *.* FROM u1@localhost;


#mysqlbinlog :mysql二進制日志

RESET MASTER; #刪除二進制日志

SET GLOBAL general_log=ON; #開啟通用查詢日志

SHOW VARIABLES; #查看環境變量
#開啟慢查詢日志
SET GLOBAL slow_log=ON;

SELECT * FROM tb_x INTO OUTFILE /home/tb_x;
#導出表格

?

?

?

轉載于:https://www.cnblogs.com/activecode/p/9824999.html

總結

以上是生活随笔為你收集整理的【mysql学习-2】的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。