夜夜性日日交XXX,婷婷五月网站,国产精品高潮呻吟,国产免费一区二区三区四区午夜视频 ,日韩三级片一区二区,婷婷色五月天丁香,黄色免费日本欧美,亚洲国产综合久久久精品潘金莲

點(diǎn)擊上方藍(lán)字關(guān)注我們

一

數(shù)據(jù)庫簡(jiǎn)介

　　數(shù)據(jù)庫(Database，DB)是按照數(shù)據(jù)結(jié)構(gòu)來組織，存儲(chǔ)和管理數(shù)據(jù)的倉庫。

　　典型特征：數(shù)據(jù)的結(jié)構(gòu)化、數(shù)據(jù)間的共享、減少數(shù)據(jù)的冗余度，數(shù)據(jù)的獨(dú)立性。

　　關(guān)系型數(shù)據(jù)庫：使用關(guān)系模型把數(shù)據(jù)組織到數(shù)據(jù)表(table)中。現(xiàn)實(shí)世界可以用數(shù)據(jù)來描述。

　　主流的關(guān)系型數(shù)據(jù)庫產(chǎn)品：Oracle(Oracle)、DB2(IBM)、SQL Server(MS)、MySQL(Oracle)。

　　數(shù)據(jù)表：數(shù)據(jù)表是關(guān)系數(shù)據(jù)庫的基本存儲(chǔ)結(jié)構(gòu)，二維數(shù)據(jù)表有行(Row),和列(Column)組成，也叫作記錄(行)和字段(列)。

二

MySQL數(shù)據(jù)類型(5.5版本)

　　MySQL中除了字符串類型需要設(shè)置長(zhǎng)度,其他類型都有默認(rèn)長(zhǎng)度。

三

Sql語句

(1)Sql語句簡(jiǎn)介

SQL(Structured Query Language):結(jié)構(gòu)化查詢語言。

SQL是在關(guān)系數(shù)據(jù)庫上執(zhí)行數(shù)據(jù)操作、檢索及維護(hù)所使用的標(biāo)準(zhǔn)語言，可以用來查詢數(shù)據(jù)，操縱數(shù)據(jù)，定義數(shù)據(jù)，控制數(shù)據(jù)。

　　SQL可以分為：

數(shù)據(jù)定義語言(DDL)：Data Definition Language

數(shù)據(jù)操縱語言(DML)：Data Manipulation Language

事務(wù)控制語言(TCL)：Transaction Control Language

數(shù)據(jù)查詢語言(DQL)：Data Query Language

數(shù)據(jù)控制語言(DCL)：Data Control Language

(2)數(shù)據(jù)定義語言DDLcreate,alter,drop　

--數(shù)據(jù)定義語言DDL(create,alter,drop)
-- 一、數(shù)據(jù)庫相關(guān)的DDL
-- 1.創(chuàng)建數(shù)據(jù)庫CREATE DATABASE mybase;-- 2.創(chuàng)建數(shù)據(jù)庫并指定字符集CREATE DATABASE mybase1 CHARACTER SET UTF8;-- 3.查看所有數(shù)據(jù)庫SHOW DATABASES;-- 4.查看當(dāng)前使用的數(shù)據(jù)庫SELECT DATABASE();-- 5.修改數(shù)據(jù)庫ALTER DATABASE mybase CHARACTER SET UTF8;-- 6.刪除數(shù)據(jù)庫DROP DATABASE mybase1;-- 切換數(shù)據(jù)庫USE mybase;
--二、表相關(guān)DDL
-- 1.創(chuàng)建表create table exam(  id INT(11) PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(20),  English INT,  Chinese INT,  Math int);-- 2.查看數(shù)據(jù)庫中所有表show TABLES;-- 3.查看表結(jié)構(gòu)desc exam;
-- 4.表的刪除drop table exam;
-- 5.表的修改(基于exam表)
--   5.1添加列ALTER TABLE exam ADD History INT NOT NULL;--   5.2修改列的類型、長(zhǎng)度、約束ALTER TABLE exam MODIFY History DOUBLE(7,2);--   5.3修改表的列名ALTER TABLE exam CHANGE History Physics INT NOT NULL;--   5.4修改表名RENAME TABLE exam TO score;--   5.5修改表的字符集ALTER TABLE score CHARACTER SET GBK;--   5.6刪除列ALTER TABLE score DROP Physics;--   5.7
ALTER TABLE 
-- 三、練習(xí):創(chuàng)建emp表
CREATE TABLE emp(  empno INT PRIMARY KEY AUTO_INCREMENT,  ename VARCHAR(20),  job VARCHAR(20),  mgr int,  hiredate DATE,  sal DOUBLE(7,2),  commit double(5,2),  deptno INT NOT NULL  -- 非空約束);

　　表exam:　　

　　表emp:　　

(3)數(shù)據(jù)操縱語言DMLupdate,insert,delete

-- 數(shù)據(jù)操縱語言DML(update,insert,delete)

-- 1.插入部分列INSERT INTO score(id,NAME,English,Chinese,Math) VALUE(1,'Hudie',90,90,90);INSERT INTO score(id,NAME,English,Chinese) VALUE(NULL,'diedie',91,91);-- 2.插入所有列INSERT INTO score VALUES(3,'Shu',80,80,80);-- 3.修改記錄UPDATE score set Chinese=99; --全表修改UPDATE score SET Math=100 WHERE id='1'; -- 4.刪除記錄DELETE FROM score WHERE id='2';DELETE FROM score;
-- delete與truncate的區(qū)別 √ --TRUNCATE TABLE 刪除表的記錄:將整個(gè)表刪除掉,重新創(chuàng)建一個(gè)新的表,屬于DDL.--DELETE FROM 刪除表的記錄:一條一條進(jìn)行刪除,DELETE.INSERT INTO score VALUES(3,'Shu',80,80,80);DELETE FROM score;INSERT INTO score VALUES(NULL,'Libai',10,10,10); --不會(huì)清空AUTO_INCREMENT值TRUNCATE TABLE score;INSERT INTO score VALUES(NULL,'Libai',10,10,10); --清空AUTO_INCREMENT的值
-- 事務(wù)管理:只能作用在DML語句上,如果在一個(gè)事務(wù)中使用delete刪除所有記錄,可以找回.-- 使用delete刪除后可以用COMMIT和ROLLBACK找回?cái)?shù)據(jù),使用truncate后就找不回來了.
-- delete、truncate、drop的區(qū)別 √delete、truncate、只是刪除表的記錄,而drop會(huì)直接刪除表.

(4)數(shù)據(jù)控制語言DCLgrant,revoke　

-- 數(shù)據(jù)控制語言DCL(grant,revoke)-- 主要為用戶授予和撤銷權(quán)限
-- 1.創(chuàng)建用戶:CREATE USER 用戶名@ip IDENTIFIED BY 密碼;create user Fox@localhost identified by '123456';
-- 2.給用戶授權(quán):grank 權(quán)限1,權(quán)限2,...,權(quán)限n ON 數(shù)據(jù)庫名.* TO 用戶名@IP;grant select,drop on mysql.* to Fox@localhost;-- 3.撤銷權(quán)限:REVOKE 權(quán)限1,權(quán)限2,...,權(quán)限n ON 數(shù)據(jù)庫名.* FROM 用戶名@IP;revoke select on mysql.* from Fox@localhost;
-- 4.查看用戶的權(quán)限:SHOW GRANTS FOR 用戶名@IPlshow grants for Fox@localhost;-- 5.刪除用戶: DROP USER 用戶名@IP;drop user Fox@localhost;-- 6.登錄:mysql -u 用戶名-p 密碼;mysql -u root -p-- 7.退出登錄: exit;exit;

(5)數(shù)據(jù)查詢語言DQLselect

　　exam表：

-- 數(shù)據(jù)查詢語言DQL(select)create table exam(  id INT(11) PRIMARY KEY AUTO_INCREMENT,  name VARCHAR(20),  English INT,  Chinese INT,  Math int);INSERT INTO exam VALUES(NULL,'小花',99,99,99);INSERT INTO exam VALUES(NULL,'小藍(lán)',89,89,89);

-- 1.全表查詢: SELECT * FROM 表;SELECT * FROM exam;-- 2.查詢部分字段: SELECT 字段,字段,字段... FROM 表;SELECT NAME,English,Math FROM exam;-- 3.過濾重復(fù)字段行: SELECT [DISTINCT] *|列名 FROM 表;SELECT DISTINCT Math FROM exam;SELECT DISTINCT name,Math FROM exam;-- 4.查詢字段起別名: SELECT 字段 AS 新字段名,字段 新字段名 FROM 表;SELECT NAME,English AS English_score FROM exam;-- 5.查詢指定字段SELECT NAME,English,Chinese FROM exam WHERE NAME='李白';-- 6.使用表達(dá)式+、-、*、/SELECT id,NAME,English-20 AS _English FROM exam;SELECT NAME,English+Math+Chinese FROM exam;-- 7.模糊查詢SELECT * FROM exam WHERE NAME LIKE '小_';SELECT * FROM exam WHERE NAME LIKE '%%';-- 8.使用and,orSELECT * FROM exam WHERE English > 90 AND Chinese >90;SELECT * FROM exam WHERE English < 90 or Math >99;-- 9.使用in,not inSELECT * FROM exam WHERE id=2 OR id=3 OR id=4;SELECT * FROM exam where id IN(2,3,4);SELECT * FROM exam where id not IN(2,3,4);-- 10.使用between...and []SELECT * FROM exam WHERE English BETWEEN 90 AND 100;-- 11. is null,is not nullINSERT INTO exam(id,NAME) VALUES(NULL,NULL);SELECT * FROM exam WHERE NAME IS NULL;SELECT * FROM exam WHERE NAME IS not NULL;-- 11.排序查詢SELECT * FROM exam ORDER BY Chinese ASC;SELECT * FROM exam ORDER BY Chinese DESC;SELECT * FROM exam ORDER BY English DESC,Chinese DESC;-- 如果英語成績(jī)相同,按照漢語成績(jī)降序排列SELECT * FROM exam WHERE NAME LIKE '小%' ORDER BY English ASC;-- 12.聚合函數(shù)SELECT SUM(English+Math+Chinese) FROM exam;SELECT COUNT(id) FROM exam WHERE NAME IS NOT NULL;SELECT MAX(English) FROM exam;SELECT MIN(English) FROM exam;SELECT?AVG(English)?FROM?exam?;

(6)分組查詢與分頁查詢group by，limit

-- 一、分組查詢
CREATE TABLE emp(  empno INT PRIMARY KEY AUTO_INCREMENT,  ename VARCHAR(20),  job VARCHAR(20),  mgr int,  hiredate DATE,  sal DOUBLE(7,2),  commit double(5,2),  deptno INT NOT NULL  );
INSERT INTO emp VALUES(1002,'白展堂','clerk',1001,'1983-05-09',7000.00,200.00,10),(1003,'李大嘴','clerk',1002,'1980-07-08',8000.00,100.00,10),(1004,'呂秀才','clerk',1002,'1985-11-12',4000.00,null,10),(1005,'郭芙蓉','clerk',1002,'1985-03-04',4000.00,null,10),(2001,'胡一菲','leader',null,'1994-03-04',15000.00,NULL,20),(2002,'陳美嘉','manger',2001,'1993-05-24',10000.00,300.00,20),(2003,'呂子喬','clerk',2002,'1995-05-19',7300.00,100.00,20),(2004,'張偉','clerk',2002,'1994-10-12',8000.00,500.00,20),(2005,'曾小賢','clerk',2002,'1993-05-10',9000.00,700.00,20),(3001,'劉梅','leader',null,'1968-08-08',13000.00,NULL,30),(3002,'夏冬梅','manger',3001,'1968-09-21',10000.00,600.00,30),(3003,'夏雪','clerk',3002,'1989-09-21',8000.00,300.00,30),(3004,'張一山','clerk',3002,'1991-06-16',88000.00,200.00,30);

-- 1.查詢每個(gè)部門的平均工資SELECT deptno,AVG(sal) FROM emp GROUP BY deptno;-- 2.查詢每個(gè)職位的最高工資和最低工資SELECT job,MAX(sal),MIN(sal) FROM emp GROUP BY job;-- 3.查詢每個(gè)部門每種職位的最高工資SELECT deptno,job,MAX(sal) FROM emp GROUP BY deptno,job;-- 4.查詢每個(gè)部門的最高薪水,只有最高薪水大于15000的記錄才被輸出顯示SELECT deptno,MAX(sal)AS max_sal FROM emp GROUP BY deptno HAVING max_sal>=15000;-- 5.查詢每個(gè)部門的平均工資SELECT deptno,AVG(sal) FROM emp GROUP BY deptno HAVING AVG(sal)>9000;-- 6.Havaing子句與where子句的區(qū)別(1)where是用來過濾記錄的,HAVING是用來過濾分組的(2)過濾的時(shí)機(jī)不相同,先過濾Where后過濾Having.  (3)WHERE是在查詢表時(shí)逐行過濾以選取滿足條件的記錄(4)having是在數(shù)據(jù)查詢后并且分完組后對(duì)分組進(jìn)行過濾的(5)HAVING必須跟在group BY(6)查詢語句執(zhí)行順序:5select 1from 2where 3group by 4having 6order by

-- 二、分頁查詢-- 1.從第幾頁開始多少頁(下標(biāo)從0開始)SELECT * FROM emp LIMIT 0,3;-- 2.每頁幾條第幾頁==需要查看第幾頁-1)乘以第二個(gè)參數(shù)SELECT * FROM emp LIMIT 10,5;-- 每頁五條第三頁(3-1)*5SELECT * FROM emp LIMIT 2,2; --每頁2條第2頁(2-1)*2-- 3.查看工資最高的前十個(gè)職員信息SELECT?*?FROM?emp?ORDER?BY?sal?DESC?LIMIT?0,10;

四

完整性約束(單表)

　　主鍵約束：primary key (默認(rèn)就是唯一非空的)

　　外鍵約束: 用于在兩個(gè)表之間建立關(guān)系，需要指定引用主表的哪一列。

如果表A的主鍵是表B中的字段,則該字段稱為表B的外鍵,表A(主表),表B(從表)。

外鍵是用來實(shí)現(xiàn)參照完整性的，主表更新時(shí)從表也更新,主表刪除時(shí)如果從表有匹配的項(xiàng),刪除失敗。

　　唯一約束：unique

　　非空約束：not null

CREATE TABLE emp(  empno INT PRIMARY KEY AUTO_INCREMENT,--主鍵約束  ename VARCHAR(20),  job VARCHAR(20),  mgr int,  hiredate DATE,  sal DOUBLE(7,2),  commit double(5,2),  deptno INT NOT NULL  -- 非空約束);-- √ 添加 唯一約束和非空約束ALTER TABLE exam MODIFY NAME VARCHAR(21) UNIQUE NOT NULL;
-- 創(chuàng)建主表CREATE TABLE dept(  deptno INT PRIMARY KEY,  dname VARCHAR(20),  loc VARCHAR(20));INSERT INTO dept VALUES(10,'餐飲部','上海'),(20,'銷售部','浙江'),(30,'財(cái)務(wù)部','北京'),(40,'技術(shù)部','深圳');

為從表emp加外鍵
ALTER?TABLE?emp?ADD?FOREIGN?KEY?(deptno)?REFERENCES?dept(deptno);

五

多表查詢

多張數(shù)據(jù)表或視圖的查詢叫做連接查詢
-- 1.笛卡爾積:SELECT * FROM emp,dept;-- 2.等值鏈接(SELECT * FROM A,B WHERE A.主鍵=B.外鍵;)SELECT * FROM emp,dept WHERE dept.deptno = emp.deptno;-- 3.內(nèi)連接(SELECT * FROM A INNER JOIN B ON A.主鍵=B.外鍵;)SELECT * FROM emp INNER JOIN dept ON dept.deptno = emp.deptno;-- 4.外連接:--   4.1左外連接:(SELECT * FROM A LEFT OUTER JOIN B ON 條件;)SELECT * FROM emp LEFT OUTER JOIN dept ON dept.deptno=emp.deptno;--   4.2右外連接:(SELECT * FROM A right OUTER JOIN B ON 條件;)SELECT * FROM emp RIGHT OUTER JOIN dept ON dept.deptno=emp.deptno;-- 5.子查詢:--   5.1單行單列,工作地點(diǎn)在上海的員工SELECT * FROM emp WHERE deptno=(SELECT deptno FROM dept WHERE loc ='上海');--   5.2多行單列,工作地點(diǎn)不在上海的員工SELECT * FROM emp WHERE deptno in(SELECT deptno FROM dept WHERE loc <>'上海');-- 6.自連接SELECT e1.*,e2.* FROM emp e1 inner join emp e2 ON e1.mgr =e2.empno WHERE e1.ename ='呂子喬'
多表查詢練習(xí)↓-- 1.查看每個(gè)員工的名字以及其所在部門的名字SELECT emp.ename,dept.dname,dept.loc FROM emp,dept WHERE emp.deptno = dept.deptno;-- 2.查看工作地點(diǎn)在北京的員工有哪些SELECT *FROM emp INNER JOIN dept ON emp.deptno = dept.deptnoWHERE dept.loc ='北京';-- 3.查看每個(gè)城市員工的平均工資SELECT dept.loc,ifnull(avg(sal),0)FROM emp right join depton emp.deptno =dept.deptnoGROUP BY dept.loc;-- 4.查看工作地點(diǎn)在上海的員工有哪些SELECT emp.*,dept.dname,dept.loc FROM emp LEFT OUTER JOIN dept ON emp.deptno=dept.deptnoWHERE dept.loc='上海';-- 5.查找和曾小賢同職位的員工SELECT *FROM empWHERE job=(SELECT job FROM emp WHERE ename='曾小賢');-- 6.查找薪水比整個(gè)機(jī)構(gòu)平均水平高的員工SELECT * FROM emp WHERE sal>(SELECT AVG(sal) FROM emp);-- 7.查詢出部門中有clerk但職位不是clerk的員工的信息SELECT * FROM empWHERE deptno IN(SELECT DISTINCT deptno FROM emp WHERE job='clerk') AND job!='clerk';-- 8.查看每個(gè)城市員工的平均工資'SELECT dept.loc,AVG(sal)FROM emp INNER JOIN dept ON emp.deptno=dept.deptno GROUP BY dept.loc;-- 9.查詢出最低薪水高于部門20的最低薪水的部門信息SELECT deptno,MIN(sal) AS MIN_salFROM emp GROUP BY deptno HAVING min_sal>(SELECT MIN(sal) FROM emp WHERE deptno=20);-- 10.列出所有員工的姓名及其直接上級(jí)的姓名SELECT e1.ename,e2.enameFROM emp e1 LEFT?JOIN?emp?e2?ON?e1.mgr?=?e2.empno;

MySQL數(shù)據(jù)庫總結(jié)