數(shù)據(jù)庫簡介

l?之前已經(jīng)學(xué)習(xí)過通過流操作文件存儲(chǔ)數(shù)據(jù)，相當(dāng)于自己寫了一個(gè)簡單版的數(shù)據(jù)庫軟件，存在很多弊端：

a)????? 效率低

b)???? 一般只能保存小量數(shù)據(jù)

c)????? 只能保存文本類型

什么是DB

l?DataBase數(shù)據(jù)庫，代表保存到磁盤中的文件集

什么是DBMS

l?DataBaseManagementSystem 數(shù)據(jù)庫管理系統(tǒng)，是用于管理數(shù)據(jù)庫文件的軟件系統(tǒng)，常見的DBMS：MySQL、Oracle、DB2、SQLServer、SQLite等

數(shù)據(jù)庫的分類 （了解）

2.????? 關(guān)系型數(shù)據(jù)庫：經(jīng)過數(shù)學(xué)理論驗(yàn)證可以保存現(xiàn)實(shí)生活中存在的任何關(guān)系，關(guān)系型數(shù)據(jù)庫以表為存儲(chǔ)單位。

3.????? 非關(guān)系型數(shù)據(jù)庫：用于處理某些特殊需求，比如：解決數(shù)據(jù)緩存問題，redis數(shù)據(jù)庫就是一款解決數(shù)據(jù)緩存的數(shù)據(jù)庫，保存數(shù)據(jù)的方式是以鍵值對

主流數(shù)據(jù)庫軟件介紹

4.????? MySQL：Oracle公司產(chǎn)品， 08年被Sun公司收購，09年Sun公司又被Oracle收購， 開源數(shù)據(jù)庫， 原MySQL在面臨MySQL閉源的情況下離開Oracle又創(chuàng)建了一個(gè)新的數(shù)據(jù)庫叫MariaDB。市場占有率排名第一

5.????? Oracle：閉源收費(fèi)，性能最高收費(fèi)最貴，拉里。埃里森 排名第二

6.????? SQLserver：微軟公司產(chǎn)品，閉源產(chǎn)品，市場排名第三，主要應(yīng)用在微軟的整體解決方案中

7.????? DB2：IBM公司產(chǎn)品，閉源產(chǎn)品，主要應(yīng)用在IBM的整體解決方案中

8.????? SQLite：輕量級數(shù)據(jù)庫，只具備基礎(chǔ)的增刪改查功能，

開源和閉源

9.????? 開源：開放源代碼，免費(fèi) 盈利方式：賣服務(wù)

10.?? 閉源：不開放源代碼，盈利方式：通過賣產(chǎn)品+賣服務(wù)

SQL

l?什么是SQL：Structured Query Language 結(jié)構(gòu)化查詢語言，用于程序員和數(shù)據(jù)庫管理系統(tǒng)進(jìn)行交流的語言

在終端或命令行中和數(shù)據(jù)庫軟件建立連接

mysql -uroot -p

數(shù)據(jù)庫相關(guān)SQL

11.?? 查看所有數(shù)據(jù)庫

12.?? 格式: show databases;

13.?? 創(chuàng)建數(shù)據(jù)庫

14.?? 格式：create database 數(shù)據(jù)庫名; create database db1;

15.?? 查看數(shù)據(jù)庫詳情

16.?? 格式：show create databse 數(shù)據(jù)庫名; show createdatabase db1;

17.?? 創(chuàng)建數(shù)據(jù)庫指定字符集

18.?? 格式：create database 數(shù)據(jù)庫名 character set utf8/gbk;create database db2 character set gbk;

19.?? 刪除數(shù)據(jù)庫

20.?? 格式：drop database 數(shù)據(jù)庫名; drop database db1;

21.?? 使用數(shù)據(jù)庫

22.?? 格式：use 數(shù)據(jù)庫名; use db1;

表相關(guān)的SQL 前提需要使用了某個(gè)數(shù)據(jù)庫

23.?? 查詢所有表 show tables;

24.?? 創(chuàng)建表

25.?? 格式：create table 表名(字段1名 字段1類型,字段2名 字段2類型); createtable person(name varchar(10),age int);

26.?? 練習(xí)：創(chuàng)建一個(gè)學(xué)生表student有學(xué)號id 姓名name 語文chinese數(shù)學(xué)math英語englishcreate table student(id int,name varchar(10),chinese int,math int,english int);

27.?? 查看表詳情 show create table person;

28.?? 表引擎：

a)????? innodb：支持?jǐn)?shù)據(jù)庫的高級操作包括：事務(wù)、外鍵等

b)???? myisam：只支持?jǐn)?shù)據(jù)基礎(chǔ)的增刪改查操作

29.?? 創(chuàng)建表指定引擎和字符集

30.?? 格式：create table 表名(字段1名字段1類型,字段2名字段2類型) engine=myisam/innodbcharset=utf8/gbk; create table t1(name varchar(10),age int) engine=myisamcharset=gbk;

31.?? 練習(xí)：創(chuàng)建2個(gè)數(shù)據(jù)庫分別是 mydb1和mydb2在第一個(gè)數(shù)據(jù)庫里面創(chuàng)建員工表emp 里面有姓名name年齡age工資sal字段，在mydb2里面創(chuàng)建英雄表hero，字段有名字name 年齡age英雄類型type 字符串類型 并且指定此表的引擎為myisam字符集為gbk create database mydb1; use mydb1; create tableemp(name varchar(10),age int,sal int); create database mydb2; use mydb2; createtable hero(name varchar(10),age int,type varchar(10))engine=myisam charset=gbk;

32.?? 查看表字段

33.?? 格式：desc 表名; desc hero;

34.?? 刪除表

35.?? 格式：drop table 表名; drop table hero;

36.?? 修改表名

37.?? 格式：rename table 原名 to 新名; create table t1(name varchar(10));

renametable t1 to t2;

38.?? 修改表引擎和字符集

39.?? 格式：alter table 表名 engine=myisam/innodbcharset=utf8/gbk; alter table t2 engine=myisam charset=gbk;

40.?? 添加表字段

41.?? 最后面添加格式：alter table 表名 add 字段名 字段類型;

42.?? 最前面添加格式：alter table 表名 add 字段名 字段類型 first;

43.?? xxx后面添加格式：altertable 表名 add 字段名 字段類型 afterxxx; alter table t2 add age int; alter table t2 add sal int first; alter tablet2 add id int after name;

44.?? 刪除表字段

45.?? 格式：alter table 表名 drop 字段名; alter table t2 drop id;

46.?? 修改字段名和類型

47.?? 格式：alter table 表名 change 原名 新名 新類型; alter table t2 change sal salary varchar(10);

48.?? 修改字段類型和位置

49.?? 格式：alter table 表名 modify 字段名 新類型 first/after xxx; alter table t2 modify salary int after age;

50.?? 練習(xí)：

51.?? 創(chuàng)建數(shù)據(jù)庫mydb3 指定字符集utf8 并使用create database mydb3 character set utf8; use mydb3;

52.?? 創(chuàng)建temp表 只有id字段 指定引擎為myisam 字符集為gbk create table temp(id int) engine=myisam charset=gbk;

53.?? 修改表名為emp rename table t_emp to emp;

54.?? 修改引擎為innodb 修改字符集為utf8 alter table empengine=innodb charset=utf8;

55.?? 在最后面添加name字段 alter table emp add name varchar(10);

56.?? 在name前面添加age字段 altertable emp add age int after id;

57.?? 在age后面添加工資sal字段 altertable emp add sal int after age;

58.?? 修改sal字段名稱為salary alter table emp change salsalary int;

59.?? 修改age字段到最后面 alter table emp modify age intafter name;

60.?? 刪除salary字段 alter table emp drop salary;

61.?? 刪除表 drop table emp;

62.?? 刪除數(shù)據(jù)庫mydb3 drop database mydb3;

數(shù)據(jù)相關(guān)的SQL

?

63.?? 插入數(shù)據(jù)

64.?? 全表插入格式：insert into 表名 values (值1,值2,值3); insertinto person values(1,'Tom',20);

65.?? 指定字段格式：insert into 表名 (字段1,字段2) values(值1,值2);insert into person (id,name) values(2,'Jerry');

66.?? 批量插入：insert into 表名 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3),(值1,值2,值3); insertinto 表名 (字段1,字段2) values(值1,值2),(值1,值2),(值1,值2); insert into person values(3,'a1',25),(4,'a2',26); insert intoperson (id,name) values(5,'b1'),(6,'b2');

67.?? 查詢數(shù)據(jù)

68.?? 格式：select 字段信息 from 表名where 條件; select * from person; select name fromperson; select name,age from person; select * from person where name='Tom';

69.?? 修改數(shù)據(jù)

70.?? 格式：update 表名 set 字段名=值,字段名=值 where 條件; update person set age=18 where name='Jerry'; -修改id大于4的年齡為35 updateperson set age=35 where id>4;

71.?? 刪除數(shù)據(jù)

72.?? 格式：delete from 表名 where 條件; delete from person where name='a2'; -刪除年齡大于25歲的數(shù)據(jù) delete from person where age>25; -刪除所有數(shù)據(jù) delete from person;

中文亂碼問題

?

?

//以上代碼個(gè)別同學(xué)會(huì)出現(xiàn)執(zhí)行出錯(cuò)的情況通過在終端中執(zhí)行 set names gbk; 解決 select *from person; - 如果出現(xiàn)的不是報(bào)錯(cuò)而是查詢數(shù)據(jù)時(shí)出現(xiàn)亂碼 把現(xiàn)有的數(shù)據(jù)庫和現(xiàn)有的表刪除掉重新創(chuàng)建數(shù)據(jù)庫創(chuàng)建表 保證字符集全部為utf8

?

主鍵約束

l?什么是主鍵：用于表示數(shù)據(jù)唯一性的字段稱為主鍵

l?什么是約束：就是創(chuàng)建表的時(shí)候給字段添加的限制條件

l?主鍵約束：插入數(shù)據(jù)必須是唯一且非空的

l?格式：create tablet1(id int primary key,name varchar(10)); insert into t1 values(1,'劉備'); insert into t1 values(1,'關(guān)羽'); //報(bào)錯(cuò)不能重復(fù) insert into t1 values(null,'關(guān)羽');//報(bào)錯(cuò)不能為null

主鍵約束+自增

l?自增數(shù)值只增不減

l?從歷史最大值基礎(chǔ)上+1

l?格式：create tablet2(id int primary key auto_increment, name varchar(10)); insert into t2values(null,'悟空'); //1 insert into t2 values(null,'八戒'); //2 insert into t2 values(10,'八戒'); //10insert into t2 values(null,'沙僧');//11 delete from t2id>=10; insert into t2 values(null,'沙僧');//12

注釋

l?對表的字段進(jìn)行描述 createtable t3(id int primary key auto_increment comment '主鍵字段',name varchar(10) comment '這是姓名');

'和`的區(qū)別

l?' 是用來修飾字符串的

l?是用來修飾表名和字段名的 可以省略 create tablet4(idint,name`varchar(10));

數(shù)據(jù)冗余

l?如果數(shù)據(jù)庫中的表設(shè)計(jì)不夠合理，隨著數(shù)據(jù)量的增長出現(xiàn)大量的重復(fù)數(shù)據(jù)，這種重復(fù)數(shù)據(jù)的現(xiàn)象稱為數(shù)據(jù)冗余，通過拆分表的形式解決此問題

l?練習(xí)：請?jiān)O(shè)計(jì)表保存以下兩條數(shù)據(jù)

a)????? 集團(tuán)總部下的教學(xué)研發(fā)部下的Java一部的員工蒼老師，年齡18歲，工資100，性別男

b)???? 人事部下的員工小明工資5000，年齡30 -創(chuàng)建員工表 empid,name,age,salary,gender,deptid createtable emp(id int primary key autoincrement,name varchar(10),age int,salaryint,gender varchar(5),deptid int); -創(chuàng)建部門表 dept id,name,parentidcreate table dept(id int primary key autoincrement,name varchar(10),parentid int); -往兩個(gè)表插入數(shù)據(jù) insert into deptvalues(null,'集團(tuán)總部',null),(null,'教學(xué)研發(fā)部',1),(null,'Java一部',2),(null,'人事部',1); insert into emp values(null,'蒼老師',18,100,'男',3),(null,'小明',30,5000,'男',4);

l?練習(xí)2：設(shè)計(jì)表保存以下數(shù)據(jù)

c)????? 家電分類下電視機(jī)分類下的小米電視價(jià)格(price)2588,庫存(num)500

d)???? 辦公用品分類下的打印機(jī)分類下的惠普打印機(jī)價(jià)格1500,庫存100 創(chuàng)建商品表 分類表create table item(id int primary key autoincrement,namevarchar(10),price int,num int,categoryid int); create table category(id intprimary key autoincrement,namevarchar(10),parentid int); -插入數(shù)據(jù) insert intocategory values(null,'家電',null),(null,'電視機(jī)',1),(null,'辦公用品',null),(null,'打印機(jī)',3); insert into item values(null,'小米電視',2588,500,2),(null,'惠普打印機(jī)',1500,100,4);

事務(wù)

l?什么是數(shù)據(jù)庫中的事務(wù)？事務(wù)是數(shù)據(jù)庫中執(zhí)行同一業(yè)務(wù)多條SQL語句的工作單元，可以保證多條SQL語句全部執(zhí)行成功或者全部執(zhí)行失敗

l?事務(wù)相關(guān)指令：

l?開啟事務(wù) begin;

l?提交事務(wù) commit;

l?回滾事務(wù) rollback;

createtable user(id int primary key auto_increment,name varchar(10),money int,statevarchar(5));

insertinto user values(null,'鋼鐵俠',5000,'正常'),(null,'綠巨人',500,'正常'),(null,'超人',100,'凍結(jié)');

l?鋼鐵俠給綠巨人轉(zhuǎn)賬1000update user set money=money-1000 where id=1 and state='正常'; update user set money=money+1000 where id=2 and state='正常';

l?鋼鐵俠給超人轉(zhuǎn)賬1000update user set money=money-1000 where id=1 and state='正常'; update user set money=money+1000 where id=3 and state='正常';

l?在事務(wù)保護(hù)下執(zhí)行:鋼鐵俠給超人轉(zhuǎn)賬1000 begin; //開啟事務(wù) update user setmoney=money-1000 where id=1 and state='正常'; -在這個(gè)時(shí)間點(diǎn)再開一個(gè)窗口檢查數(shù)據(jù)庫里面的數(shù)據(jù)是否改變 update user set money=money+1000 where id=3 and state='正常'; rollback; //轉(zhuǎn)賬失敗 回滾事務(wù)

l?在事務(wù)保護(hù)下執(zhí)行:鋼鐵俠給綠巨人轉(zhuǎn)賬1000 begin; //開啟事務(wù) update user setmoney=money-1000 where id=1 and state='正常'; update userset money=money+1000 where id=2 and state='正常'; commit;//轉(zhuǎn)賬成功 提交事務(wù)

l?savepoint; 保存回滾點(diǎn) begin; update user set money=2001 where id=1; savepoint s1; updateuser set money=2002 where id=1; savepoint s2; update user set money=2003 whereid=1; rollback to s2;

SQL分類

73.?? DDL：Data DefinitionLanguage 數(shù)據(jù)定義語言，包括：create 、drop、alter、 truncate ,不支持事務(wù)

74.?? truncate table 表名：刪除表并創(chuàng)建新表 自增數(shù)值清零

75.?? DML：DataManipulation Language 數(shù)據(jù)操作語言，包括：insert、delete、update、select(DQL)，支持事務(wù)

76.?? DQL: Data Query Language 數(shù)據(jù)查詢語言，包括：select

77.?? TCL：TransactionControl Language 事務(wù)控制語言，包括：begin、commit、rollback、savepointxxx、rollback to xxx

78.?? DCL：Data ControlLanguage 數(shù)據(jù)控制語言，分配用戶權(quán)限相關(guān)SQL

數(shù)據(jù)類型

79.?? 整數(shù)：常用類型int(m) 和 bigint(m), m代表顯示長度，需要結(jié)合zerofill關(guān)鍵字使用 create table tint(id int(5) zerofill); insert into tintvalues(18);

80.?? 浮點(diǎn)數(shù)：常用類型 double(m,d) m代表總長度 d代表小數(shù)長度 25.321 m=5 d=3 decimal超高精度浮點(diǎn)數(shù)，當(dāng)涉及超高精度運(yùn)算時(shí)使用

81.?? 字符串：char(m)固定長度 執(zhí)行效率高 最大長度255 varchar(m)可變長度節(jié)省資源 最大65535 超高255建議使用text， text可變長度 最大65535

82.?? 日期：date 只能保存年月日 ，time 只能保存時(shí)分秒 ，datetime 最大值 9999-12-31 默認(rèn)值為null，timestamp 最大值2038-1-19默認(rèn)值 當(dāng)前的系統(tǒng)時(shí)間 create table t_date(t1 date,t2 time,t3datetime,t4 timestamp);

insertinto tdate values('2019-2-20',null,null,null);insert into tdate values(null,'16:32:20','2019-2-20 16:32:22',null);

83.?? 其它類型

導(dǎo)入*.sql文件

84.?? windows系統(tǒng) 把下載的文件放在d盤下面在終端中執(zhí)行以下指令 source d:/tables.sql;

85.?? linux 系統(tǒng) 把文件放在桌面 在終端中執(zhí)行以下指令 source /home/soft01/桌面/tables.sql;

86.?? 導(dǎo)入后執(zhí)行show tables; 查看是否有四張表 有說明搞定

isnull 和 is not null

87.?? 查詢獎(jiǎng)金為null的員工信息 select * from emp where comm isnull;

88.?? 查詢mgr不為null值得員工姓名select ename from emp where mgr is not null;

別名

selectename as '姓名',sal as '工資'from emp; select ename '姓名',sal '工資' from emp; select ename 姓名,sal 工資 from emp;

去重 distinct

select distinct job from emp;

?

比較運(yùn)算符 > < = >= <= !=和<>

89.?? 查詢工資高于2000的所有員工編號empno,姓名ename，職位job，工資sal select empno,ename,job,sal fromemp where sal>2000;

90.?? 查詢工資小于等于1600的所有員工的編號，姓名，工資 select empno,ename,salfrom emp where sal<=1600;

91.?? 查詢部門編號是20的所有員工姓名、職位、部門編號deptno selectename,job,deptno from emp where deptno=20;

92.?? 查詢職位是manager的所有員工姓名和職位 select ename,job from empwhere job='manager';

93.?? 查詢不是10號部門的所有員工編號，姓名，部門編號（兩種寫法） selectempno,ename,deptno from emp where deptno!=10; select empno,ename,deptno fromemp where deptno<>10;

94.?? 查詢titem表單價(jià)price等于23的商品信息 select * from titem where price=23; select * from t_item where price=23 \G;

95.?? 查詢單價(jià)不等于8443的商品標(biāo)題title和商品單價(jià)select title,price from t_item where price!=8443;

and和or

l?and 并且&& 需要同時(shí)滿足多個(gè)條件時(shí)使用

l?or 或|| 需要滿足多個(gè)條件中的某一個(gè)條件時(shí)使用

l?查詢20號部門工資大于2000的員工信息 select * from emp where deptno=20and sal>2000;

l?查詢10號部門獎(jiǎng)金為null的員工信息 select * from emp where deptno=10and comm is null;

l?查詢有上級領(lǐng)導(dǎo)mgr并且職位是manager的員工信息 select * from emp where mgr isnot null and job='manager';

l?查詢20號部門或者工資小于1000的員工信息 select * from emp where deptno=20or sal<1000;

l?查詢名字為king和james的員工信息 select * from emp whereename='king' or ename='james';

in 和 not in

96.?? 查詢工資為5000，950，3000的員工信息 select * from emp where sal=5000 or sal=950 or sal=3000; select *from emp where sal in (5000,950,3000);

97.?? 查詢James、king、ford的工資和獎(jiǎng)金 select sal,comm from emp where ename in('james','king','ford');

98.?? 查詢工資不是5000，950，3000的員工信息 select * from emp where sal not in (5000,950,3000);

betweenx and y 包括xy

99.?? 查詢工資在2000到3000之間的員工信息select * from emp where sal>=2000 and sal<=3000; select * from emp wheresal between 2000 and 3000;

100.查詢商品表單價(jià)在50到100之間的商品名稱和商品單價(jià)select title,price from t_item where price between 50 and 100;

101.查詢工資小于2000并且大于3000的員工信息select * from emp where sal not between 2000 and 3000;

模糊查詢 like

l?_代表單個(gè)未知字符

l?%代表0或多個(gè)未知字符 -舉例：以a開頭 a% 以b結(jié)尾 %b 包含c %c% 第一個(gè)字符是a 倒數(shù)第二個(gè)字符是b a%b_ 匹配163郵箱%@163.com 任意郵箱 %@%.com

l?案例：

l?查詢員工姓名以k開頭的員工信息 select * from emp where ename like 'k%';

l?查詢標(biāo)題包含記事本的商品標(biāo)題和商品單價(jià) select title,price from t_item where title like '%記事本%';

l?查詢單價(jià)低于100的記事本 select * from t_item where price<100 and title like '%記事本%';

l?查詢有贈(zèng)品的dell商品(賣點(diǎn)sellpoint包含贈(zèng)字,標(biāo)題包含dell) select * from titem wheresell_point like '%贈(zèng)%' and title like '%dell%';

l?查詢單價(jià)在100到200之外的聯(lián)想商品 select * from t_item where pricenot between 100 and 200 and title like '%聯(lián)想%';

l?查詢分類categoryid為238和917的齊心商品 select * from titem where category_idin(238,917) and title like '%齊心%';

l?查詢商品標(biāo)題中不包含得力的商品信息select * from t_item where title not like '%得力%';

l?查詢員工姓名包含a并且工資低于3000的員工姓名和工資 select ename,sal from emp whereename like '%a%' and sal<3000;

l?查詢員工姓名不是以k開頭并且有獎(jiǎng)金的員工信息 select * from emp where ename not like 'k%' and comm>0;

l?查詢30號部門職位包含man的員工姓名、職位、部門編號 select ename,job,deptnofrom emp where deptno=30 and job like '%man%';

排序

l?order by 字段名 asc/desc;

l?查詢員工姓名和工資降序select ename,sal from emp order by sal desc;

l?查詢30號部門的員工信息工資降序排序 select * from emp where deptno=30 order by sal desc;

l?查詢名字中包含a并且工資大于1000的員工信息按照工資升序排序 select * from emp whereename like '%a%' and sal>1000 order by sal;

l?查詢所有員工信息按照部門編號升序排序 select * from emp order by deptno,sal desc;

l?查詢帶燃字的商品單價(jià)升序排序select * from t_item where title like '%燃%' order byprice;

l?查詢所有dell商品標(biāo)題，分類categoryid，單價(jià) 按照分類升序排序，單價(jià)降序排序 selecttitle,categoryid,price from titem where title like '%dell%' order by categoryid,price desc;

分頁查詢

l?limit 跳過的條數(shù),請求的條數(shù)(每頁的條數(shù))

l?查詢員工表工資最高的前五條數(shù)據(jù)select * from emp order by sal desc limit 0,5; -以上數(shù)據(jù)的第二頁數(shù)據(jù) select * from emp order by sal desc limit 5,5;

l?查詢商品表單價(jià)升序第三頁每頁四條數(shù)據(jù) select * from t_item order by price limit 8,4;

l?limit (頁數(shù)-1)*每頁的條數(shù),每頁的條數(shù)

課程回顧

102.比較運(yùn)算符 > < >= <= = != <>

103.and 和 or

104.in 和 not in

105.between x and y not between x andy

106.like not like _單個(gè)未知 %0或多個(gè)未知

107.order by 字段1 asc/desc,字段2;

108.limit 跳過的條數(shù),請求的條數(shù)

concat()函數(shù)

l?可以將字符串進(jìn)行拼接

l?查詢員工表每個(gè)員工的姓名和工資 要求工資顯示單位元 select ename,concat(sal,'元') from emp;

l?查詢商品表，顯示商品名稱，單價(jià)（價(jià)格：25元） select title,concat('價(jià)格：',price,'元') from t_item;

l?select concat('a','b'); select'helloworld';

數(shù)值計(jì)算 + - * / % 7%2 = mod(7,2)

109.查詢員工的姓名，工資，年終獎(jiǎng)（年終獎(jiǎng)=工資5） select ename,sal,sal5 年終獎(jiǎng) from emp;

110.查詢商品名稱，單價(jià)，庫存，總價(jià)值（單價(jià)庫存） selecttitle,price,num,numprice 總價(jià)值 from t_item;

日期相關(guān)函數(shù)

111.獲取當(dāng)前日期+時(shí)間 select now();

112.獲取當(dāng)前年月日和 時(shí)分秒 select curdate(),curtime();

113.從完整的年月日時(shí)分秒中提取年月日 和 提取時(shí)分秒 select date(now()); select time(now());

114.從完整的年月日時(shí)分秒中提取時(shí)間分量 extract select extract(year/month/day/hour/minute/second fromnow());

n?查詢每個(gè)員工的姓名和入職的年份 入職時(shí)間字段：hiredate select ename,extract(year from hiredate) from emp;

115.日期格式化 date_format()

n?date_format(時(shí)間,格式);

n?格式：%Y 四位年 %y 兩位年 %m 兩位月 %c 一位月 %d 日 %H 24小時(shí) %h 12小時(shí) %i 分 %s 秒

n?把默認(rèn)的時(shí)間格式轉(zhuǎn)成 年月日時(shí)分秒select date_format(now(),'%Y年%m月%d日 %H時(shí)%i分%s秒');

116.把非標(biāo)準(zhǔn)時(shí)間格式轉(zhuǎn)成標(biāo)準(zhǔn)格式 strtodate()

n?strtodate(字符串時(shí)間,格式) 14.08.2019 08:00:00 select strtodate('14.08.201908:00:00','%d.%m.%Y %H:%i:%s');

ifnull()

l?age = ifnull(x,y) 如果x值為null則age=y 如果x不為null則age=x

l?把emp表中獎(jiǎng)金為null的改成0 update emp set comm=ifnull(comm,0);

聚合函數(shù)

l?對多行數(shù)據(jù)進(jìn)行統(tǒng)計(jì)查詢：求和 平均值 最大值 最小值 計(jì)數(shù)

l?求和：sum(求和的字段) 統(tǒng)計(jì)20號部門的工資總和select sum(sal) from emp where deptno=20;

l?平均值：avg(字段) 統(tǒng)計(jì)所有員工的平均工資 select avg(sal) from emp;

l?最大值：max(字段) 查詢30號部門的最高工資select max(sal) from emp where deptno=30;

l?最小值：min(字段) 查詢30號部門的最低工資select min(sal) from emp where deptno=30;

l?計(jì)數(shù)：count(字段) 一般寫count() 只有涉及null值時(shí)才使用字段名 查詢員工表中10號部門的員工數(shù)量 select count() from emp wheredeptno=10; 查詢所有員工中有上級領(lǐng)導(dǎo)的員工數(shù)量 select count(mgr) fromemp;

字符串相關(guān)函數(shù)

l?charlength(str)獲取字符串的長度 select ename,charlength(ename)from emp;

l?instr(str,substr) 獲取substr在str中出現(xiàn)的位置 從1開始 select instr('abcdefg','d');

l?insert(str,start,length,newstr)select insert('abcdefg',3,2,'m');

l?lower(str) upper(str) selectlower('NBa'),upper('NBa');

l?trim(str) 去兩端空白 select trim(' a b ');

l?left(str,index) 從左邊截取

l?right(str,index) 從右邊截取

l?substring(str,index,?length) 從指定位置截取 select left('abcdefg',2); select right('abcdefg',2); selectsubstring('abcdefg',3,2); select substring('abcdefg',3);

l?repeat(str,count) 重復(fù) select repeat('ab',3);

l?replace(str,old,new) 替換 select replace('This is mysql','my','your');

l?reverse() 反轉(zhuǎn) select reverse('abc');

數(shù)學(xué)相關(guān)的函數(shù)

l?floor(num) 向下取整 select floor(3.84);

l?round(num) 四舍五入 select round(3.8);

l?round(num,m) m代表保留幾位小數(shù) select round(3.8679,2); 3.87

l?truncate(num,m) 非四舍五入 select truncate(3.8679,2); 3.86

l?rand() 隨機(jī)數(shù)0-1 獲取 0-5的隨機(jī)整數(shù)select floor(rand()6); 獲取3-6的隨機(jī)整數(shù) 0-3 + 3 select floor(rand()4)+3;

數(shù)學(xué)相關(guān)的函數(shù)

117.向下取整 floor(num) select floor(3.14);

118.四舍五入 round(num) select round(23.8);

119.round(num,m) select round(23.879,2);

120.非四舍五入 truncate(num,m) select truncate(23.879,2);

121.隨機(jī)數(shù) rand() 0-1 select rand();

122.獲取0-5的整數(shù)隨機(jī)數(shù) select floor(rand()*6);

123.3-5的隨機(jī)數(shù)

分組查詢 group by

124.查詢每個(gè)部門的平均工資 select deptno,avg(sal) from emp group by deptno;

125.查詢每個(gè)部門的工資總和 select deptno,sum(sal) from emp group by deptno;

126.查詢每種職業(yè)的最高工資 select job,max(sal) from emp group by job;

127.查詢每個(gè)領(lǐng)導(dǎo)下的人數(shù) select mgr,count(*) from emp where mgr is not null group by mgr;

128.查詢每個(gè)部門工資大于1000的員工數(shù)量 select deptno,count(*) from empwhere sal>1000 group by deptno;

129.多字段分組查詢只需要在group by后面寫多個(gè)字段名通過逗號分隔

130.每個(gè)部門每個(gè)主管的手下人數(shù) select deptno,mgr,count(*) from emp where mgr is not null group bydeptno,mgr;

53.案例：查詢emp表中每個(gè)部門的編號，人數(shù)，工資總和，最后根據(jù)人數(shù)進(jìn)行升序排列，如果人數(shù)一致，根據(jù)工資總和降序排列。select deptno,count(),sum(sal)from emp group by deptno order by count(),sum(sal) desc; -別名寫法 select deptno,count(*) c,sum(sal) s from emp group by deptno orderby c,s desc;

54.案例：查詢工資在1000~3000之間的員工信息，每個(gè)部門的編號，平均工資，最低工資，最高工資，根據(jù)平均工資進(jìn)行升序排列。select deptno,avg(sal) a,min(sal),max(sal) from emp where salbetween 1000 and 3000 group by deptno order by a; 55.案例：查詢含有上級領(lǐng)導(dǎo)的員工，每個(gè)職業(yè)的人數(shù)，工資的總和，平均工資，最低工資，最后根據(jù)人數(shù)進(jìn)行降序排列，如果人數(shù)一致，根據(jù)平均工資進(jìn)行升序排列 select job,count(*) c,sum(sal),avg(sal) a,min(sal) from emp wheremgr is not null group by job order by c desc,a;

having

l?where后面只能寫普通字段的條件 不能寫聚合函數(shù)的條件

l?having和where類似都是用于添加條件的，having后面可以寫普通字段的條件也可以寫聚合函數(shù)的條件，但是建議寫聚合函數(shù)的條件，而且要結(jié)合group by 使用

l?查詢每個(gè)部門的平均工資，要求平均工資大于2000 -錯(cuò)誤寫法 where后面不能寫聚合函數(shù) select deptno,avg(sal) from emp where avg(sal)>2000 group bydeptno; -正確寫法：select deptno,avg(sal) a from emp groupby deptno having a>2000;

l?查詢商品表中每個(gè)分類的平均單價(jià)，要求平均單價(jià)小于100 select categoryid,avg(price)a from titem group by category_id having a<100;

l?查詢emp表中工資在1000-3000之間的員工，每個(gè)部門的編號，工資總和，平均工資，過濾掉平均工資低于2000的部門，按照平均工資降序排序 select deptno,sum(sal),avg(sal) a from emp where sal between 1000and 3000 group by deptno having a>=2000 order by a desc;

l?查詢emp表中平均工資高于2000的部門編號，部門人數(shù)，平均工資，最后根據(jù)平均工資降序排序 selectdeptno,count(*),avg(sal) a from emp group by deptno having a>2000 order by adesc;

l?查詢emp表中不是以s開頭的職位，每個(gè)職位的名字，人數(shù)，工資總和，最高工資，過濾掉平均工資是3000的職位，根據(jù)人數(shù)升序，如果人數(shù)一致則根據(jù)工資總和降序 select job,count(*) c,sum(sal) s,max(sal) from emp where job notlike 's%' group by job having avg(sal)!=3000 order by c, s desc;

l?查詢每年入職的人數(shù)(擴(kuò)展) select extract(year from hiredate) year,count(*) from emp group byyear;

l?查詢最高平均工資的部門編號（擴(kuò)展） （并列第一的問題不能解決） select deptno from emp group by deptno order by avg(sal) desc limit0,1;

子查詢（嵌套查詢）

l?可以在查詢語句中嵌套另一條sql語句

l?可以嵌套n層

l?查詢emp表中工資最高的員工信息 select max(sal) from emp; select * from emp where sal=(selectmax(sal) from emp);

l?查詢emp表中工資大于平均工資的員工信息 select avg(sal) from emp; select * from emp where sal>(selectavg(sal) from emp);

l?查詢工資高于20號部門最高工資的員工信息 select max(sal) from emp where deptno=20; select * from emp wheresal>(select max(sal) from emp where deptno=20);

l?查詢和jones相同工作的其它員工信息 select job from emp where ename='jones'; select * from emp wherejob=(select job from emp where ename='jones') and ename!='jones';

l?查詢工資最低的員工的同事們的信息(同事=相同job) -得到最低工資select min(sal) from emp;

n?得到拿最低工資員工的職位select job from emp where sal=(select min(sal) from emp);

n?通過職位得到此職位的員工信息排除最低工資那個(gè)哥們兒 select * from emp where job=(select job from emp where sal=(selectmin(sal) from emp)) and sal!=(select min(sal) from emp);

l?查詢最后入職的員工信息select max(hiredate) from emp; select * from emp where hiredate=(selectmax(hiredate) from emp);

l?查詢員工king所屬的部門編號和部門名稱（需要用到部門表dept） select deptno from emp whereename='king'; select deptno,dname from dept where deptno=(select deptno fromemp where ename='king');

l?查詢有員工的部門信息(查詢在員工表中出現(xiàn)的部門的信息) -得到員工表中的部門編號 select distinct deptno fromemp; -查詢上面結(jié)果對應(yīng)的部門詳情 select * from dept where deptno in(select distinct deptno from emp);

l?查詢平均工資最高的部門信息(難度最高需要考慮并列第一問題) -查詢最高的平均工資 select avg(sal)a from emp group by deptno order by a desc limit 0,1; -通過最高的平均工資找到對應(yīng)的部門編號 select deptno from emp group by deptno having avg(sal)=(selectavg(sal) a from emp group by deptno order by a desc limit 0,1); -通過部門編號查詢部門信息 select * from dept where deptno in(上面一坨);

l?子查詢總結(jié)：

l?嵌套在sql語句中的查詢語句稱為子查詢

l?子查詢可以嵌套n層

l?子查詢可以寫在什么位置？

l?寫在where和having的后面 當(dāng)做查詢條件的值

l?寫在創(chuàng)建表的時(shí)候 -格式：create table 表名 as (子查詢) create table newemp as (select * from emp where deptno=10);

l?寫在from后面當(dāng)成一個(gè)虛擬表?必須有別名?select ename from (select * from emp where deptno=10)newtable;

關(guān)聯(lián)查詢

l?同時(shí)查詢多張表的查詢方式稱為關(guān)聯(lián)查詢

l?查詢每一個(gè)員工的姓名和其對應(yīng)的部門名稱 select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

l?查詢在new york工作的所有員工信息 select e.* from emp e,dept d where e.deptno=d.deptno and d.loc='newyork';

笛卡爾積

l?關(guān)聯(lián)查詢必須寫關(guān)聯(lián)關(guān)系，如果不寫則得到兩張表的乘積，這個(gè)乘積稱為笛卡爾積

l?工作中不允許出現(xiàn)因?yàn)?超級耗內(nèi)存 有可能直接崩潰

等值連接和內(nèi)連接

l?等值連接和內(nèi)連接都是關(guān)聯(lián)查詢的查詢方式

l?等值連接和內(nèi)連接查詢到的結(jié)果一樣，都為兩張表的交集數(shù)據(jù)

l?等值連接：select *from A,B where A.x=B.x and A.age=18;

l?內(nèi)連接：select * fromA join B on A.x=B.x where A.age=18;

l?查詢每個(gè)員工的姓名和對應(yīng)的部門名稱select e.ename,d.dname from emp e join dept d on e.deptno=d.deptno;

l?查詢在new york工作的所有員工信息 select e.* from emp e join dept d on e.deptno=d.deptno whered.loc='new york';

外鏈接

l?格式：select * fromA left/right join B on A.x=B.x where A.age=18;

l?查詢部門表的全部名稱和對應(yīng)的員工姓名 select d.dname,e.ename from emp e right join dept d one.deptno=d.deptno;

關(guān)聯(lián)查詢總結(jié)：

131.關(guān)聯(lián)查詢的查詢方式包括三種：等值連接、內(nèi)連接和外鏈接

132.如果查詢兩張表的交集數(shù)據(jù)使用等值連接和內(nèi)連接，推薦使用內(nèi)連接

133.如果查詢一張表的全部數(shù)據(jù)和另外一張表的交集數(shù)據(jù)使用外連接，外鏈接只需要掌握一種即可

表設(shè)計(jì)之關(guān)聯(lián)關(guān)系

一對一

l?什么是一對一：有AB兩張表，A表中一條數(shù)據(jù)對應(yīng)B表中的一條數(shù)據(jù)，同時(shí)B表中一條數(shù)據(jù)也對應(yīng)A表中的一條數(shù)據(jù)

l?應(yīng)用場景：用戶表和用戶信息擴(kuò)展表，商品表和商品信息擴(kuò)展表

l?如何建立關(guān)系：在從表中添加外鍵字段指向主表的主鍵

l?練習(xí)：創(chuàng)建用戶表user（id,username,password） 和擴(kuò)展表userinfo(user_id,nick,loc)并且保存以下數(shù)據(jù)

l?建表：create tableuser(id int primary key autoincrement,usernamevarchar(10),password varchar(10)); create table userinfo(userid int,nickvarchar(10),loc varchar(10));

libaiadmin 李白 建國門外大街 liubei 12345 劉皇叔 荊州 liudehua aabbcc 劉德華 香港 insert into uservalues(null,'libai','admin'),(null,'liubei','12345'),(null,'liudehua','aabbcc');insert into userinfo values(1,'李白','建國門外大街'),(2,'劉皇叔','荊州'),(3,'劉德華','香港');

l?查詢libai的密碼是什么 select password from user where username='libai';

l?查詢每個(gè)用戶的用戶名和昵稱select u.username,ui.nick from user u join userinfo ui on u.id=ui.user_id;

l?查詢劉德華的用戶名 selectu.username from user u join userinfo ui on u.id=ui.user_id where ui.nick='劉德華';

一對多

l?什么是一對多：有AB兩張表：A表中一條數(shù)據(jù)對應(yīng)B表的多條數(shù)據(jù)，同時(shí)B表中的一條數(shù)據(jù)對應(yīng)A表的一條數(shù)據(jù)

l?場景：員工表和部門表 商品表和商品分類表

l?如何建立關(guān)系：在多的表中添加外鍵指向另外一張表的主鍵

l?練習(xí)：創(chuàng)建temp(id,name,deptid)和tdept(id,name) create tabletemp(id int primary key autoincrement,name varchar(10),deptid int); createtable tdept(id int primary key autoincrement,namevarchar(10));

l?保存以下數(shù)據(jù)：神仙部門的孫悟空和豬八戒，妖怪部門的蜘蛛精和白骨精 insert into tdeptvalues(null,'神仙'),(null,'妖怪'); insert into temp values(null,'孫悟空',1),(null,'豬八戒',1),(null,'蜘蛛精',2),(null,'白骨精',2);

l?查詢每個(gè)員工的姓名和對應(yīng)的部門名稱select e.name,d.name from temp e join tdeptd on e.dept_id=d.id;

l?查詢豬八戒的所在的部門名select d.name from temp e join tdeptd on e.dept_id=d.id where e.name='豬八戒';

l?查詢妖怪部的員工都有誰select e.name from temp e join tdeptd on e.dept_id=d.id where d.name='妖怪';

多對多

l?什么是多對多：有AB兩張表，A表中一條數(shù)據(jù)對應(yīng)B表的多條數(shù)據(jù)，同時(shí)B表的一條數(shù)據(jù)對應(yīng)A表的多條，稱為多對多

l?應(yīng)用場景：老師和學(xué)生

l?如何建立關(guān)系：創(chuàng)建第三張關(guān)系表，在關(guān)系表中有兩個(gè)字段指向另外兩個(gè)表的主鍵

l?練習(xí)：創(chuàng)建老師表teacher(id,name)和學(xué)生表student(id,name)保存以下數(shù)據(jù) 關(guān)系表t_s(tid,sid)

l?創(chuàng)建表 create tableteacher(id int primary key autoincrement,namevarchar(10)); create table student(id int primary key autoincrement,namevarchar(10)); create table ts(tid int,sidint); 蒼老師:小米、小紅、小綠傳奇哥：小白、小綠 insert into teacher values(null,'蒼老師'),(null,'傳奇哥'); insert into studentvalues(null,'小米'),(null,'小紅'),(null,'小綠'),(null,'小白'); insert into ts values(1,1),(1,2),(1,3),(2,3),(2,4);

l?查詢每個(gè)老師的姓名和對應(yīng)的學(xué)生姓名select t.name,s.name from teacher t join t_s ts on t.id=ts.tid join student son s.id=ts.sid;

l?查詢蒼老師的學(xué)生都有誰select s.name from teacher t join t_s ts on t.id=ts.tid join student s ons.id=ts.sid where t.name='蒼老師';

l?查詢小綠的老師都有誰 selectt.name from teacher t join t_s ts on t.id=ts.tid join student s on s.id=ts.sidwhere s.name='小綠';

自關(guān)聯(lián)

l?當(dāng)前表的外鍵指向當(dāng)前表的主鍵稱為自關(guān)聯(lián)

l?應(yīng)用場景：需要保存如：上級領(lǐng)導(dǎo)、上級分類、上級部門

l?查詢方式：把一張表當(dāng)成兩張表

連接方式和關(guān)聯(lián)關(guān)系

l?連接方式指內(nèi)連接和外鏈接

l?關(guān)聯(lián)關(guān)系是指兩張表之間存在邏輯關(guān)系包括：一對一、一對多、多對多

表設(shè)計(jì)案例：權(quán)限管理

l?需要?jiǎng)?chuàng)建三張主表：用戶表user(id,name)角色表role(id,name) 權(quán)限表module(idname)，還需要兩張關(guān)系表：用戶角色關(guān)系表 ur(uid,rid) 、 角色權(quán)限關(guān)系表 rm(rid,mid) create table user(id intprimary key autoincrement,namevarchar(10)); create table role(id int primary key autoincrement,namevarchar(10)); create table module(id int primary key autoincrement,namevarchar(10)); create table ur(uid int,rid int); create table r_m(ridint,mid int);

l?保存以下數(shù)據(jù)：用戶表：劉德華、張學(xué)友、王菲 角色表：男游客、男會(huì)員、女游客、女管理員權(quán)限表：男瀏覽、男發(fā)帖、男刪帖、女瀏覽、女發(fā)帖、女刪帖 關(guān)系數(shù)據(jù)：劉德華(男會(huì)員，女游客) 張學(xué)友(男游客，女游客) 王菲(女管理員，男會(huì)員) 男游客(男瀏覽) ，男會(huì)員（男瀏覽，男發(fā)帖），女游客（女瀏覽），女管理員（女瀏覽、女發(fā)帖、女刪帖）insert into user (name) values('劉德華'),('張學(xué)友'),('王菲'); insert into role (name) values('男游客'),('男會(huì)員'),('女游客'),('女管理員'); insert into module (name) values('男瀏覽'),('男發(fā)帖'),('男刪帖'),('女瀏覽'),('女發(fā)帖'),('女刪帖'); insert into ur values(1,2),(1,3),(2,1),(2,3),(3,4),(3,2);insert into rm values(1,1),(2,1),(2,2),(3,4),(4,4),(4,5),(4,6);

l?查詢每個(gè)用戶的名字和對應(yīng)的權(quán)限名字select u.name,m.name from user u join urur on u.id=ur.uid join rm rm on ur.rid=rm.rid join module m on rm.mid=m.id;

l?查詢劉德華擁有的權(quán)限 selectm.name from user u join ur ur onu.id=ur.uid join rm rm on ur.rid=rm.rid join module m on rm.mid=m.id whereu.name='劉德華';

l?查詢有男發(fā)帖權(quán)限的用戶都有誰select u.name from user u join ur ur on u.id=ur.uidjoin rm rm on ur.rid=rm.rid join module m on rm.mid=m.id where m.name='男發(fā)帖';

面試題

時(shí)間?? 金額? 關(guān)系? 性別? 紅包類型? 姓名

l?流水表trade: id timemoney type pid create table trade(id int primary key auto_increment,timedate,money int,type varchar(5),pid int);

l?人員表person：id name gender rel create table person(id int primary keyauto_increment,name varchar(10),gender varchar(5),rel varchar(5));

l?插入人員表數(shù)據(jù)：insertinto person values(null,'劉德華','男','親戚'),(null,'楊冪','女','親戚'),(null,'馬云','男','同事'),(null,'特朗普','男','朋友'),(null,'貂蟬','女','朋友');

insertinto trade values(null,'2018-03-20',1000,'現(xiàn)金',1),(null,'2018-04-14',500,'現(xiàn)金',2),(null,'2018-04-14',-50,'現(xiàn)金',2),(null,'2018-03-11',20000,'支付寶',3),(null,'2018-03-11',-5,'支付寶',1),(null,'2018-05-14',2000,'微信',4),(null,'2018-06-25',-20000,'微信',5);

134.統(tǒng)計(jì)春節(jié)（2018年2月15號）到現(xiàn)在的紅包收益 select sum(money) from trade where time>strtodate('2018年2月15號','%Y年%c月%d號');

135.查詢春節(jié)到現(xiàn)在金額大于100所有女性親戚的名字和金額 selectp.name,t.money from trade t join person p on t.pid=p.id where time>strtodate('2018年2月15號','%Y年%c月%d號') and t.money not between -100 and 100and p.gender='女' and p.rel='親戚';

136.查詢?nèi)齻€(gè)平臺(tái)分別收入的紅包金額 select type,sum(money) from trade where money>0 group by type;

視圖

l?什么是視圖：視圖和表都是數(shù)據(jù)庫中的對象，視圖可以理解成是一張?zhí)摂M的表，視圖本質(zhì)就是取代了一段SQL查詢語句。

l?為什么使用視圖：使用視圖可以起到SQL語句重用的作用，提高開發(fā)效率，還可以隱藏敏感信息

l?創(chuàng)建視圖格式：createview 視圖名 as (子查詢);

a)????? 創(chuàng)建10號部門員工的視圖 create view vemp10 as (select * from emp where deptno=10);

b)???? 創(chuàng)建沒有工資的視圖 create view vempnosal as(select ename,deptno,mgr from emp);

c)????? 創(chuàng)建一個(gè)顯示每個(gè)部門編號、平均工資、最高工資、最低工資、工資總和、員工人數(shù)的視圖 create view vempinfo as(select deptno,avg(sal),max(sal),min(sal),sum(sal),count(*) from emp group bydeptno);

l?視圖的分類

d)???? 簡單視圖：創(chuàng)建視圖時(shí)的子查詢不包含：去重、函數(shù)、分組、關(guān)聯(lián)查詢創(chuàng)建的視圖稱為簡單視圖，可以對簡單視圖進(jìn)行增刪改查操作

e)???? 復(fù)雜視圖：和簡單視圖相反，只能對復(fù)雜視圖進(jìn)行查詢操作

l?對簡單視圖進(jìn)行增刪改操作 操作方式和table一樣

f)????? 插入數(shù)據(jù) insert into vemp10(empno,ename,deptno) values(10010,'Tom',10);//成功 insertinto vemp10 (empno,ename,deptno)values(10011,'Jerry',20); //成功 數(shù)據(jù)污染

g)???? 往視圖中插入一條視圖中不可見但是在原表中可見的數(shù)據(jù)稱為數(shù)據(jù)污染，通過with check option 關(guān)鍵字避免出現(xiàn)數(shù)據(jù)污染現(xiàn)象 create viewvemp20 as(select * from emp wheredeptno=20) with check option; insert into vemp20(empno,ename,deptno) values(10012,'Lucy',20);//成功 insert into vemp20(empno,ename,deptno) values(10013,'Lily',30); //失敗 數(shù)據(jù)污染 插入不進(jìn)去

h)???? 修改和刪除只能操作視圖中存在的數(shù)據(jù) update vemp20 setsal=1000 where empno=10010;//修改失敗 delete from vemp20 where empno=10010;//刪除失敗 delete from vemp10 where empno=10010;//刪除成功

l?創(chuàng)建或修改視圖 create orreplace view vemp20 as (select enamefrom emp where deptno=20);

l?刪除視圖 drop view 視圖名;

l?創(chuàng)建視圖時(shí)如果子查詢中使用了別名則之后對視圖進(jìn)行操作只能使用別名 create view vemp30 as(select ename name,sal from emp where deptno=30); select ename from vemp30; //報(bào)錯(cuò) 找不到ename

約束

l?什么是約束：約束就是給字段添加的限制條件

非空約束 not null

l?字段的值不能為nullcreate table t1(id int,age int not null); -測試：insertinto t1 values(1,20); //成功 insert into t1values(2,null); //失敗 不能為null值

唯一約束 unique

l?字段的值不能重復(fù) createtable t2(id int,age int unique); -測試：insert into t2values(1,20);//成功 insert into t2 values(2,20);//失敗不能重復(fù)

默認(rèn)約束 default

l?給字段設(shè)置默認(rèn)值 createtable t3(id int,age int default 20); -測試：insert intot3 (id) values(1); //觸發(fā)默認(rèn)值生效 insert into t3 values(2,50);//不觸發(fā) insert into t3 values(3,null);//不觸發(fā)

主鍵約束 primary key

l?主鍵：表示數(shù)據(jù)唯一性的字段稱為主鍵

l?主鍵約束：唯一且非空

外鍵約束

l?外鍵：用于建立關(guān)系的字段

l?外鍵約束：為了保證兩張表之間建立正確的關(guān)系，外鍵字段的值可以為null，可以重復(fù)，不能是另外一張表中不存在的數(shù)據(jù)，建立好關(guān)系后被依賴的數(shù)據(jù)不能先刪除，被依賴的表不能先刪除

l?如何使用外鍵約束

i)?????? 先創(chuàng)建部門表 create table dept(id int primary key auto_increment,namevarchar(10));

j)?????? 創(chuàng)建員工表 create table emp(id int primary key autoincrement,name varchar(10),deptid int,constraint fkdept foreignkey(deptid) references dept(id)); -格式介紹：constraint 約束名稱 foreign key(外鍵字段名) references 被依賴的表名(被依賴的字段名)

-測試：insert into dept values(null,'神仙'),(null,'妖怪'); insert into emp values(null,'悟空',1);//成功 insert into emp values(null,'賽亞人',3);//報(bào)錯(cuò) delete from dept where id=1;//報(bào)錯(cuò) 因?yàn)橛幸蕾嚁?shù)據(jù)drop table dept;//報(bào)錯(cuò) 因?yàn)橛幸蕾嚁?shù)據(jù) drop table emp;//成功 drop table dept;//成功 再次刪除成功因?yàn)闆]有依賴表和數(shù)據(jù)了

索引

l?什么是索引：索引是數(shù)據(jù)庫中用于提高查詢效率的技術(shù)，工作原理類似于目錄

l?為什么使用索引：如果不使用索引，數(shù)據(jù)會(huì)零散的保存到每一個(gè)磁盤塊中，查找數(shù)據(jù)時(shí)需要逐個(gè)遍歷每一個(gè)磁盤塊直到找到數(shù)據(jù)為止，使用索引后磁盤塊會(huì)以樹樁結(jié)構(gòu)保存，查找數(shù)據(jù)時(shí)可以大大減低磁盤塊的訪問量，從而提高查詢效率

l?有索引就一定好嗎？不是，如果數(shù)據(jù)量比較小，有索引反而會(huì)降低查詢效率 -測試沒有索引的查詢效率 select * from item2 wheretitle='100'; //1.17秒

創(chuàng)建索引

l?格式：create index 索引名 on 表名(字段名(?字段長度)); create index iitemtitleon item2(title); -再次測試：select * from item2 wheretitle='100'; //0.03秒

l?索引是越多越好嗎？不是，只針對常用的查詢字段創(chuàng)建索引,因?yàn)樗饕龝?huì)占磁盤空間

查看索引

l?格式：show indexfrom 表名; show index from item2;

刪除索引

l?格式: drop index 索引名 on 表名; drop index iitemtitle on item2;

復(fù)合索引

l?通過多個(gè)字段創(chuàng)建的索引稱為復(fù)合索引create index iitemtitle_price onitem2(title,price);

事務(wù)

l?數(shù)據(jù)庫中執(zhí)行同一業(yè)務(wù)多條SQL語句的工作單元，可以保證多條SQL全部執(zhí)行成功或全部執(zhí)行失敗

l?事務(wù)的ACID特性，此特性是保證事務(wù)正確執(zhí)行的四大基本要素

l?Atomicity：原子性，最小不可拆分，保證全部成功或全部失敗

l?Consistency：一致性，保證從一個(gè)一致狀態(tài)到另一個(gè)一致狀態(tài)

l?Isolation：隔離性，多個(gè)事務(wù)直接互不影響

l?Durability：持久性，事務(wù)提交后數(shù)據(jù)持久保存到磁盤中

l?事務(wù)相關(guān)指令：begin,commit,rollback,savepoint xxx,rollback to xxx;

JDBC

l?Java DataBase Connecivity,java數(shù)據(jù)庫連接，Sun公司提供的一套Java與數(shù)據(jù)庫連接的API（application program interface應(yīng)用程序編程接口）

l?為什么使用JDBC？如果沒有JDBC，則每個(gè)數(shù)據(jù)庫廠商都會(huì)定制自己的一套API，java程序員連接不同的數(shù)據(jù)庫時(shí)需要學(xué)習(xí)多種數(shù)據(jù)庫的API，學(xué)習(xí)成本太高，使用JDBC接口后，各個(gè)數(shù)據(jù)庫廠商針對此接口寫實(shí)現(xiàn)類(驅(qū)動(dòng))，這樣java程序員只需要掌握J(rèn)DBC里面的一套方法就可以訪問各種數(shù)據(jù)庫

l?如何使用JDBC和數(shù)據(jù)庫建立連接

k)????? 創(chuàng)建maven工程

l)?????? 從maven.tedu.cn網(wǎng)站中搜索mysql 找到5.1.6版本的坐標(biāo)復(fù)制到工程的pom.xml中

m)??? 創(chuàng)建Demo01.java 在main方法中寫如下代碼：//1. 注冊驅(qū)動(dòng)Class.forName("com.mysql.jdbc.Driver"); //2. 獲取連接對象 Connection conn =

DriverManager.getConnection("jdbc:mysql://localhost:3306/newdb3", "root","root"); System.out.println(conn); //3. 創(chuàng)建執(zhí)行SQL語句的對象 Statement stat =conn.createStatement(); //4. 執(zhí)行SQL String sql ="create table jdbct1" + "(id int primary keyauto_increment," + "name varchar(10))"; stat.execute(sql);System.out.println("執(zhí)行完成！"); //5. 關(guān)閉資源 conn.close();

Statement

l?execute(sql) 執(zhí)行sql，返回值為布爾值，返回值true代表有結(jié)果集 false代表沒有結(jié)果集，此方法可以執(zhí)行任意SQL，但是只推薦執(zhí)行DDL(數(shù)據(jù)定義語言)

l?executeUpdate(sql) 此方法執(zhí)行insert、update、delete，返回值int，返回值代表生效行數(shù)

l?executeQuery(sql) 此方法執(zhí)行select 返回值為ResultSet(結(jié)果集)

ResultSet

l?獲取數(shù)據(jù)時(shí)數(shù)據(jù)庫類型和Java類型對比 mysql java int getInt() varchar getString() float/doublegetFloat()/getDouble() datetime/timestamp getDate()

l?兩種獲取數(shù)據(jù)的方式

l?rs.getString("查詢結(jié)果中字段的名稱");

l?rs.getString(查詢結(jié)果中字段的位置); 位置從1開始

連接池DBCP

l?Database Connection Pool 數(shù)據(jù)庫連接池

l?為什么使用連接池：將連接重用，避免資源浪費(fèi)，提高執(zhí)行效率

l?如何使用連接池

PreparedStatement

l?預(yù)編譯sql執(zhí)行對象

l?好處：

l?代碼結(jié)構(gòu)整齊，可讀性高

l?帶有預(yù)編譯功能，在創(chuàng)建對象時(shí)就把sql邏輯固定，可以避免sql注入

l?因?yàn)閹в蓄A(yù)編譯功能，如果批量插入多條數(shù)據(jù)時(shí)，可以只編譯一次sql，從而提高執(zhí)行效率

l?當(dāng)sql語句中沒有變量時(shí)使用Statement，如果有變量時(shí)使用PreparedStatement

批量操作

l?可以將多條sql語句的多次數(shù)據(jù)傳輸合并成一次，從而提高執(zhí)行效率

l?Statement：//添加到批量操作 stat.addBatch(sql1);stat.addBatch(sql2); stat.addBatch(sql3); //執(zhí)行批量操作stat.executeBatch();

l?PreparedStatementPreparedStatement ps = conn.prepareStatement(sql1); ps.setString(1, "悟空"); ps.setInt(2, 500); ps.addBatch();

??? ps.setString(1, "悟能");???? ps.setInt(2,300);???? ps.addBatch();????? ps.setString(1, "吳京");???? ps.setInt(2,200);???? ps.addBatch();???? //執(zhí)行批量操作???? ps.executeBatch();

分頁查詢

??????? String sql ="select name from jdbcperson "????????????????+ "limit?,?";???????? PreparedStatement ps=?????????????????conn.prepareStatement(sql);????????ps.setInt(1, (page-1)*count);????????ps.setInt(2, count);???????? //執(zhí)行???????? ResultSet rs =ps.executeQuery();????????while(rs.next()) {?????? ??????String name = rs.getString(1);???????????? System.out.println(name);???????? }

事務(wù)

137.關(guān)閉事務(wù)自動(dòng)提交 conn.setAutoCommit(false)

138.提交事務(wù) conn.commit();

139.回滾事務(wù) conn.rollback();

實(shí)現(xiàn)轉(zhuǎn)賬流程

createtable person(id int primary key auto_increment,name varchar(10),money int); insertinto person values(null,'超人',500),(null,'蝙蝠俠',5000);

獲取自增主鍵的值

createtable team(id int primary key autoincrement,namevarchar(10)); create table player(id int primary key autoincrement,namevarchar(10),team_id int);

獲取元數(shù)據(jù)

//數(shù)據(jù)庫元數(shù)據(jù)???????? DatabaseMetaData dbmd =conn.getMetaData();??????? System.out.println("驅(qū)動(dòng)版本："????????????????????+dbmd.getDriverVersion());??????? System.out.println("用戶名："???????????????? +dbmd.getUserName());???????? System.out.println("數(shù)據(jù)庫廠商："????????????????+dbmd.getDatabaseProductName());????????//獲取表的元數(shù)據(jù)??????? String sql = "select * from emp";???????? Statement stat =conn.createStatement();???????? ResultSetrs = stat.executeQuery(sql);??????? ResultSetMetaData rsmd = rs.getMetaData();???????? //獲取表字段數(shù)量???????? int count =rsmd.getColumnCount();???????? //獲取每個(gè)字段的名稱???????? for(inti=0;i

?

?

?