一.什么是存儲引擎
二.操作存儲引擎
-
查看存儲引擎
1.查看mysql支持的存儲引擎
show engines;
2.看你的mysql當(dāng)前默認(rèn)的存儲引擎
show variables like '%storage_engine%';
3.查看某個表使用的存儲引擎
show create table 表名
show create table newpersonTable;
4.查看mysql服務(wù)器上的版本
select version();
5.在表上插入數(shù)據(jù)
-- 插入完整的數(shù)據(jù)記錄
INSERT INTO test01 VALUES(103,'趙云'),(105,'關(guān)羽'),(106,'劉備')
-- 指定字段插入
INSERT INTO test01 (cID,`name`) VALUES(104,'張飛')
把一張一樣結(jié)構(gòu)的表的數(shù)據(jù)插入到另外一張相同結(jié)構(gòu)的表中，先創(chuàng)建一張表，再把 test01表中的數(shù)據(jù)插入到test02
CREATE TABLE test02(
cID INT(11) NOT NULL,
name VARCHAR(20) DEFAULT NULL);
INSERT INTO test02 SELECT * FROM test01
6.數(shù)據(jù)的簡單查詢
-- 簡單的數(shù)據(jù)查詢
SELECT * FROM student
-- 指定字段名
SELECT age,stu_name FROM student
-- as 作為別名 as 也可以省略
SELECT age as '年齡',stu_name as '姓名' FROM student
7.避免重復(fù)數(shù)據(jù)查詢distinct
-- 去重查詢
SELECT DISTINCT age,stu_name FROM student
8.實現(xiàn)四則運算查詢
-- 四則運算查詢
SELECT age+10,stu_name FROM student
9.concat 格式化查詢
-- 格式化
SELECT CONCAT('名字為',stu_name,'年齡是',age) FROM student
輸出結(jié)果：
名字為san年齡是12
名字為san2年齡是13
名字為san年齡是13
名字為san2年齡是12
名字為san年齡是12
9.where 條件
<1>.運算符
--where條件查詢
SELECT * FROM student WHERE age>12
SELECT *FROM student WHERE sex = '男' AND age>=13
SELECT *FROM student WHERE salary BETWEEN 2000 AND 4000
<2>帶Null值的查詢
SELECT *FROM student WHERE number_id IS NULL
<3>帶IN關(guān)鍵字集合查詢
SELECT *FROM student WHERE salary in (1000,1500,3000)
<4>帶like關(guān)鍵字模糊查詢
“_”通配符竞慢，該通配符值能匹配單個字符
“%”通配符属百，該通配符值可以匹配任意個數(shù)的字符
SELECT *FROM student WHERE stu_name like '李_'
SELECT *FROM student WHERE stu_name LIKE '李%'
輸出結(jié)果分別為：
5 李微 1 男 12 17673043786 10000.00
4 李飛刀 1 男 12 17673043786 3500.00
5 李微 1 男 12 17673043786 10000.00
<5>ORDER BY語句查詢
你可以使用 ASC 或 DESC 關(guān)鍵字來設(shè)置查詢結(jié)果是按升序或降序排列沼瘫。 默認(rèn)情況下，它是按升排列。
--升序
SELECT *FROM student ORDER BY salary ASC
--降序
SELECT *FROM student ORDER BY salary DESC
10.統(tǒng)計函數(shù)
<1>count :
image.png
-- 利用該函數(shù)計算月薪大于等于10000的學(xué)生個數(shù)
SELECT COUNT(salary) '薪資' FROM student WHERE salary>=10000
<2>AVG:平均值
-- 平均值
SELECT AVG(salary) '平均薪資' FROM student WHERE salary > 3000
<3>SUM:統(tǒng)計計算求和
-- 統(tǒng)計計算求和
SELECT SUM(salary) '薪水總和' FROM student
<4>MAX,MIN最大值最小值
-- 統(tǒng)計最大值和最小值
SELECT MAX(salary) '最高工資',MIN(salary) '最低工資',SUM(salary) '薪水總和' FROM student
11.簡單分組查詢
--分組數(shù)據(jù)查詢,和統(tǒng)計函數(shù)一起配合使用
SELECT '學(xué)生姓名',COUNT(*) FROM student GROUP BY stu_name
12.連接的使用
在前面我們已經(jīng)學(xué)會了如果在一張表中讀取數(shù)據(jù)粤策，這是相對簡單的，但是真正的應(yīng)用中經(jīng)常需要從多個數(shù)據(jù)表中讀取數(shù)據(jù)误窖，下面我們來一起學(xué)習(xí)如何使用JOIN來聯(lián)合奪標(biāo)查詢.首先創(chuàng)建兩張
表一：runoob_tbl
+-----------+--------------+---------------+---------------------+
| runoob_id | runoob_title | runoob_author | submission_date |
+-----------+--------------+---------------+---------------------+
| 1 | 學(xué)習(xí) PHP | Torin學(xué)院 | 2017-04-12 00:00:00 |
| 2 | 學(xué)習(xí)MySQL | Torin學(xué)院 | 2017-04-12 00:00:00 |
| 3 | 學(xué)習(xí) Java | RUNOOB.COM | 2015-05-01 00:00:00 |
| 4 | 學(xué)習(xí)Python | RUNOOB.COM | 2016-03-06 00:00:00 |
| 5 | 學(xué)習(xí)C | FK | 2017-04-05 00:00:00 |
+-----------+--------------+---------------+---------------------+
表二：tcount_tbl
+---------------+--------------+
| runoob_author | runoob_count |
+---------------+--------------+
| Torin學(xué)院 | 10 |
| RUNOOB.COM | 20 |
| Google | 22 |
+---------------+--------------+
<1> INNER JOIN
SELECT a.runoob_id,a.runoob_author,b.runoob_count FROM runoob_tbl a INNER JOIN tcount_tbl b ON a.runoob_author = b.runoob_author;
輸出如下：
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
| 1 | Torin學(xué)院 | 10 |
| 2 | Torin學(xué)院 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
+-----------+---------------+--------------+
<2>LEFT JOIN
SELECT a.runoob_id,a.runoob_author,b.runoob_count FROM runoob_tbl a LEFT JOIN tcount_tbl b ON a.runoob_author = b.runoob_autho;r
輸出如下：
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
| 1 | Torin學(xué)院 | 10 |
| 2 | Torin學(xué)院 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
| 5 | FK | NULL |
+-----------+---------------+--------------+
<3> RIGHT JOIN
SELECT a.runoob_id, a.runoob_author, b.runoob_count FROM runoob_tbl a RIGHT JOIN tcount_tbl b ON a.runoob_author = b.runoob_author
輸出如下：
+-----------+---------------+--------------+
| runoob_id | runoob_author | runoob_count |
+-----------+---------------+--------------+
| 1 | Torin學(xué)院 | 10 |
| 2 | Torin學(xué)院 | 10 |
| 3 | RUNOOB.COM | 20 |
| 4 | RUNOOB.COM | 20 |
| NULL | NULL | 22 |
+-----------+---------------+--------------+
