數據庫基本知識和基礎sql語句

1、.數據庫的發(fā)展歷程l 沒有數據庫，使用磁盤文件存儲數據；l 層次結構模型數據庫；l 網狀結構模型數據庫；l 關系結構模型數據庫：使用二維表格來存儲數據；l 關系-對象模型數據庫；理解數據庫l RDBMS = 管理員（manager）+倉庫（database）l database = N個tablel table：l 表結構：定義表的列名和列類型！l 表記錄：一行一行的記錄！Mysql安裝目錄:l bin目錄中都是可執(zhí)行文件；l my.ini文件是MySQL的配置文件；相關命令:l 啟動：net start mysql；l 關閉：net stop mysql；l mysql -u root -p

2、 123 -h localhost； -u：后面的root是用戶名，這里使用的是超級管理員root； -p：后面的123是密碼，這是在安裝MySQL時就已經指定的密碼；l 退出：quit或exit；sql語句語法要求l SQL語句可以單行或多行書寫，以分號結尾；l 可以用空格和縮進來來增強語句的可讀性；l 關鍵字不區(qū)別大小寫，建議使用大寫；分類l DDL（Data Definition Language）：數據定義語言，用來定義數據庫對象：庫、表、列等；l DML（Data Manipulation Language）：數據操作語言，用來定義數據庫記錄（數據）；基本操作l 查看所有數據庫名稱：

3、SHOW DATABASES；l 切換數據庫：USE mydb1，切換到mydb1數據庫；l 創(chuàng)建數據庫：CREATE DATABASE IF NOT EXISTS mydb1；l 修改數據庫編碼：ALTER DATABASE mydb1 CHARACTER SET utf8l 創(chuàng)建表：CREATE TABLE 表名( 列名 列類型, 列名 列類型, .);l 查看當前數據庫中所有表名稱：SHOW TABLES；l 查看指定表的創(chuàng)建語句：SHOW CREATE TABLE emp，查看emp表的創(chuàng)建語句；l 查看表結構：DESC emp，查看emp表結構；l 刪除表：DROP TABLE em

4、p，刪除emp表；l 修改表：1. 修改之添加列：給stu表添加classname列：ALTER TABLE stu ADD (classname varchar(100);2. 修改之修改列類型：修改stu表的gender列類型為CHAR(2)：ALTER TABLE stu MODIFY gender CHAR(2);3. 修改之修改列名：修改stu表的gender列名為sex：ALTER TABLE stu change gender sex CHAR(2);4. 修改之刪除列：刪除stu表的classname列：ALTER TABLE stu DROP classname;5. 修改之

5、修改表名稱：修改stu表名稱為student：ALTER TABLE stu RENAME TO student;其他常用命令:mysql基本操作命令一、數據庫操作1.新增數據庫create database 數據庫名字 數據庫選項;數據庫選項：規(guī)定數據庫內部該用什么進行規(guī)范 字符集：charset 具體字符集(utf8) 校對集：collate 具體校對集（依賴字符集） 2.查看數據庫2.1查看所有的數據庫show databases; 匹配查詢：show databases like pattern; #pattern可以使用通配符 _：下劃線匹配，表示匹配單個任意字符，如：_s，表示任意

6、字符開始，但是以s結尾的數據庫%：百分號匹配，表示匹配任意個數的任意字符，如：student%，表示以student開始的所有數據庫2.2查看數據庫的創(chuàng)建語句show create database 數據庫名字; 3.修改數據庫數據庫名字在mysql高版本中不允許修改，所以只能修改數據庫的庫選項（字符集和校對集） alter database 數據庫名字 數據庫選項;eg:alter database stu charset utf8; 4.刪除數據庫對于數據庫的刪除要謹慎考慮，是不可逆的。 drop database 數據庫名字; 4.選擇數據庫use 數據庫名字;二、數據表操作（字段） 1

7、.新增數據表create table 表名(字段名1 數據類型 comment 備注.,字段名2 數據類型 comment 備注., . #最后一行不需要逗號)表選項;表選項：1）字符集：charset/character set（可以不寫，默認采用數據庫的） 2）校對集：collate3）存儲引擎：engine = innodb（默認的）：存儲文件的格式（數據如何存儲） 注意：創(chuàng)建數據表的時候，需要指定要在哪個數據庫下創(chuàng)建。創(chuàng)建方式有隱式創(chuàng)建和顯式創(chuàng)建 1)顯式創(chuàng)建：create table 數據庫名字.數據表名字 2)隱式創(chuàng)建：use 數據庫名字; 2.查看數據表 2.1查看所有的數據表s

8、how tables; 2.2查看表使用匹配查詢 Show tables like pattern;#與數據庫的pattern一樣：_和%兩個通配符 2.3查看數據表的創(chuàng)建語句 show create table 數據表名字; 2.4查看數據表的結構 desc 數據表名字; 3.修改數據表 3.1修改表名字 rename table 舊表名 to 新表名; 3.2修改表選項（存儲引擎，字符集和校對集）alter table 表名 表選項; 3.3修改字段（新增字段，修改字段名字,修改西段類型,刪除字段）新增字段：alter table 表名 add column 字段名字 數據庫類型 位置fi

9、rst/after; 位置選項：first 在第一個字段 after 在某個字段之后，默認就是在最后一個字段后面 修改字段名稱：alter table 表名 change 舊字段名字 新字段名字 字段數據類型 位置; eg:alter table student name fullname varchar(30) after id; 修改字段的數據類型：alter table 表名 modify 字段名字 數據類型 位置;刪除字段：alter table 表名 drop 字段名字;4.刪除數據表drop table 表名;三、數據操作1. 新增數據inser into table 表名 (字段

10、列表) values （值列表);2.查看數據select */字段列表 from 表名 where條件;3.修改數據update 表名 set 字段名 = 值 where 條件;注意：使用update操作最好配合limit 1使用,避免操作大批量數據更新錯誤.4.刪除數據delete from 表名 where 條件;注意：沒有where 條件 就是默認刪除全部數據.四、列屬性（字段）1.刪除主鍵：alter table 表名 drop primary key;2.增加主鍵：alter table 表名 add primary key(字段列表);#可以是復合主鍵3.刪除自增長：只能通過修改

11、字段屬性的方法操作.4.刪除唯一鍵：alter table 表名 drop index 索引名字;#默認的唯一鍵名字就是字段的本身5.增加唯一鍵：alter table 表名 add unique key (字段列表);#可以是復合唯一索引五、外鍵約束1.創(chuàng)建表的時候增加外鍵constraint 外鍵名字 foreign key(外鍵字段) references 父表（主鍵字段）;eg:- 創(chuàng)建父表（班級表）create table class(id int primary key auto_increment,name varchar(10) not null comment 班級名字,ro

12、om varchar(10) not null comment 教室號)charset utf8;- 創(chuàng)建子表（外鍵表）create table student(id int primary key auto_increment,number char(10) not null unique comment 學號：itcast + 四位數,name varchar(10) not null comment 姓名,c_id int comment 班級ID,- 增加外鍵foreign key(c_id) references class(id)charset utf8;2.創(chuàng)建表之后增加外鍵al

13、ter table 表名 add constraint 外鍵名字 foreign key(外鍵字段) references 父表(主鍵字段);eg:- 增加外鍵alter table student add constraint student_class_fkforeign key(c_id) references class(id);3.刪除外鍵alter table 表名 drop foreign key 外鍵名字; #查看外鍵名字需要通過表創(chuàng)建語句來查詢.eg:- 刪除外鍵alter table student drop foreign key student_ibfk_1;數據查詢語

14、法（DQL）DQL就是數據查詢語言，數據庫執(zhí)行DQL語句不會對數據進行改變，而是讓數據庫發(fā)送結果集給客戶端。SELECT selection_list /*要查詢的列名稱*/ FROM table_list /*要查詢的表名稱*/ WHERE condition /*行條件*/ GROUP BY grouping_columns /*對結果分組*/ HAVING condition /*分組后的行條件*/ ORDER BY sorting_columns /*對結果分組*/ LIMIT offset_start, row_count /*結果限定*/基礎查詢1.1查詢所有列SELECT * F

15、ROM stu;1.2查詢指定列SELECT sid, sname, age FROM stu;2條件查詢2.1條件查詢介紹條件查詢就是在查詢時給出WHERE子句，在WHERE子句中可以使用如下運算符及關鍵字：l =、!=、=；l BETWEENAND；l IN(set)；l IS NULL；l AND；l OR；l NOT；2.2查詢性別為女，并且年齡50的記錄SELECT * FROM stu WHERE gender=female AND ge=20 AND age=40;或者SELECT * FROM stu WHERE age BETWEEN 20 AND 40;2.8查詢性別非男的

16、學生記錄SELECT * FROM stuWHERE gender!=male;或者SELECT * FROM stuWHERE gendermale;或者SELECT * FROM stuWHERE NOT gender=male;2.9查詢姓名不為null的學生記錄SELECT * FROM stuWHERE NOT sname IS NULL;或者SELECT * FROM stuWHERE sname IS NOT NULL;3模糊查詢當想查詢姓名中包含a字母的學生時就需要使用模糊查詢了。模糊查詢需要使用關鍵字LIKE。3.1查詢姓名由5個字母構成的學生記錄SELECT * FROM

17、stuWHERE sname LIKE _;模糊查詢必須使用LIKE關鍵字。其中 “_”匹配任意一個字母，5個“_”表示5個任意字母。3.2查詢姓名由5個字母構成，并且第5個字母為“i”的學生記錄SELECT * FROM stuWHERE sname LIKE _i;3.3查詢姓名以“z”開頭的學生記錄SELECT * FROM stuWHERE sname LIKE z%;其中“%”匹配0n個任何字母。3.4查詢姓名中第2個字母為“i”的學生記錄SELECT * FROM stuWHERE sname LIKE _i%;3.5查詢姓名中包含“a”字母的學生記錄SELECT * FROM s

18、tuWHERE sname LIKE %a%;4字段控制查詢4.1去除重復記錄去除重復記錄（兩行或兩行以上記錄中系列的上的數據都相同），例如emp表中sal字段就存在相同的記錄。當只查詢emp表的sal字段時，那么會出現重復記錄，那么想去除重復記錄，需要使用DISTINCT：SELECT DISTINCT sal FROM emp;4.2查看雇員的月薪與傭金之和因為sal和comm兩列的類型都是數值類型，所以可以做加運算。如果sal或comm中有一個字段不是數值類型，那么會出錯。SELECT *,sal+comm FROM emp;comm列有很多記錄的值為NULL，因為任何東西與NULL相加

19、結果還是NULL，所以結算結果可能會出現NULL。下面使用了把NULL轉換成數值0的函數IFNULL：SELECT *,sal+IFNULL(comm,0) FROM emp;4.3給列名添加別名在上面查詢中出現列名為sal+IFNULL(comm,0)，這很不美觀，現在我們給這一列給出一個別名，為total：SELECT *, sal+IFNULL(comm,0) AS total FROM emp;給列起別名時，是可以省略AS關鍵字的：SELECT *,sal+IFNULL(comm,0) total FROM emp;5排序5.1查詢所有學生記錄，按年齡升序排序SELECT *FROM

20、stuORDER BY sage ASC;或者SELECT *FROM stuORDER BY sage;5.2查詢所有學生記錄，按年齡降序排序SELECT *FROM stuORDER BY age DESC;5.3查詢所有雇員，按月薪降序排序，如果月薪相同時，按編號升序排序SELECT * FROM empORDER BY sal DESC,empno ASC;6聚合函數聚合函數是用來做縱向運算的函數：l COUNT()：統(tǒng)計指定列不為NULL的記錄行數；l MAX()：計算指定列的最大值，如果指定列是字符串類型，那么使用字符串排序運算；l MIN()：計算指定列的最小值，如果指定列是字符

21、串類型，那么使用字符串排序運算；l SUM()：計算指定列的數值和，如果指定列類型不是數值類型，那么計算結果為0；l AVG()：計算指定列的平均值，如果指定列類型不是數值類型，那么計算結果為0；6.1COUNT當需要縱向統(tǒng)計時可以使用COUNT()。l 查詢emp表中記錄數：SELECT COUNT(*) AS cnt FROM emp;l 查詢emp表中有傭金的人數：SELECT COUNT(comm) cnt FROM emp;注意，因為count()函數中給出的是comm列，那么只統(tǒng)計comm列非NULL的行數。l 查詢emp表中月薪大于2500的人數：SELECT COUNT(*)

22、FROM empWHERE sal 2500;l 統(tǒng)計月薪與傭金之和大于2500元的人數：SELECT COUNT(*) AS cnt FROM emp WHERE sal+IFNULL(comm,0) 2500;l 查詢有傭金的人數，以及有領導的人數：SELECT COUNT(comm), COUNT(mgr) FROM emp;6.2SUM和AVG當需要縱向求和時使用sum()函數。l 查詢所有雇員月薪和：SELECT SUM(sal) FROM emp;l 查詢所有雇員月薪和，以及所有雇員傭金和：SELECT SUM(sal), SUM(comm) FROM emp;l 查詢所有雇員月薪

23、+傭金和：SELECT SUM(sal+IFNULL(comm,0) FROM emp;l 統(tǒng)計所有員工平均工資：SELECT SUM(sal), COUNT(sal) FROM emp;或者SELECT AVG(sal) FROM emp;6.3MAX和MINl 查詢最高工資和最低工資：SELECT MAX(sal), MIN(sal) FROM emp;分組查詢當需要分組查詢時需要使用GROUP BY子句，例如查詢每個部門的工資和，這說明要使用部分來分組。7.1分組查詢l 查詢每個部門的部門編號和每個部門的工資和：SELECT deptno, SUM(sal)FROM empGROUP B

24、Y deptno;l 查詢每個部門的部門編號以及每個部門的人數：SELECT deptno,COUNT(*)FROM empGROUP BY deptno;l 查詢每個部門的部門編號以及每個部門工資大于1500的人數：SELECT deptno,COUNT(*)FROM empWHERE sal1500GROUP BY deptno;HAVING子句l 查詢工資總和大于9000的部門編號以及工資和：SELECT deptno, SUM(sal)FROM empGROUP BY deptnoHAVING SUM(sal) 9000;注意，WHERE是對分組前記錄的條件，如果某行記錄沒有滿足WHE

25、RE子句的條件，那么這行記錄不會參加分組；而HAVING是對分組后數據的約束。8LIMITLIMIT用來限定查詢結果的起始行，以及總行數。8.1查詢5行記錄，起始行從0開始SELECT * FROM emp LIMIT 0, 5;注意，起始行從0開始，即第一行開始！8.2查詢10行記錄，起始行從3開始SELECT * FROM emp LIMIT 3, 10;8.3分頁查詢如果一頁記錄為10條，希望查看第3頁記錄應該怎么查呢？l 第一頁記錄起始行為0，一共查詢10行；l 第二頁記錄起始行為10，一共查詢10行；l 第三頁記錄起始行為20，一共查詢10行；多表連接查詢l 連接查詢 內連接 外連接

26、 左外連接 右外連接 全外連接（MySQL不支持） 自然連接l 子查詢連接查詢連接查詢就是求出多個表的乘積，例如t1連接t2，那么查詢出的結果就是t1*t2。連接查詢會產生笛卡爾積，假設集合A=a,b，集合B=0,1,2，則兩個集合的笛卡爾積為(a,0),(a,1),(a,2),(b,0),(b,1),(b,2)?？梢詳U展到多個集合的情況。那么多表查詢產生這樣的結果并不是我們想要的，那么怎么去除重復的，不想要的記錄呢，當然是通過條件過濾。通常要查詢的多個表之間都存在關聯關系，那么就通過關聯關系去除笛卡爾積。2.1內連接上面的連接語句就是內連接，但它不是SQL標準中的查詢方式，可以理解為方言！S

27、QL標準的內連接為：SELECT * FROM emp e INNER JOIN dept d ON e.deptno=d.deptno;內連接的特點：查詢結果必須滿足條件。例如我們向emp表中插入一條記錄：其中deptno為50，而在dept表中只有10、20、30、40部門，那么上面的查詢結果中就不會出現“張三”這條記錄，因為它不能滿足e.deptno=d.deptno這個條件。2.2外連接（左連接、右連接）外連接的特點：查詢出的結果存在不滿足條件的可能。左連接：SELECT * FROM emp e LEFT OUTER JOIN dept d ON e.deptno=d.deptno;

28、左連接是先查詢出左表（即以左表為主），然后查詢右表，右表中滿足條件的顯示出來，不滿足條件的顯示NULL。子查詢子查詢就是嵌套查詢，即SELECT中包含SELECT，如果一條語句中存在兩個，或兩個以上SELECT，那么就是子查詢語句了。l 子查詢出現的位置： where后，作為條件的一部分； from后，作為被查詢的一條表；l 當子查詢出現在where后作為條件時，還可以使用如下關鍵字： any alll 子查詢結果集的形式： 單行單列（用于條件） 單行多列（用于條件） 多行單列（用于條件） 多行多列（用于表）練習：1. 工資高于smith的員工。分析：查詢條件：工資smith工資，其中smit

29、h工資需要一條子查詢。第一步：查詢smith的工資SELECT sal FROM emp WHERE ename=smith第二步：查詢高于smith工資的員工SELECT * FROM emp WHERE sal ($第一步)結果：SELECT * FROM emp WHERE sal (SELECT sal FROM emp WHERE ename=smith)l 子查詢作為條件l 子查詢形式為單行單列2. 工資高于30部門所有人的員工信息分析：查詢條件：工資高于30部門所有人工資，其中30部門所有人工資是子查詢。高于所有需要使用all關鍵字。第一步：查詢30部門所有人工資SELECT sal FROM emp WHERE deptno=30;第二步：查詢高于30部門所有人工資的員工信息SELECT * FROM emp WHERE sal ALL ($第一步)結果：SELECT * FROM emp WHERE sal ALL (SELECT sal FROM emp WHERE deptno=30)l 子查詢作為