7、修改數據庫編碼集
語法:alter database 數據庫名稱 character set 字符集 collate 比較規則;
修改mydb2字符集為gbk
8、切換數據和查看正在使用的數據
切換當前使用數據庫
語法:use 數據庫名
查看當前使用的數據庫:
語法:select database();
所有數據庫相關操作語句都屬於DDL 語句
【第三階段】
【目標:創建表的語句】
1、數據表的創建
語法:create table 表名(列名 類型(長度),列名 類型(長度)... );
一個數據表可以存在很多列(字段),每列具有類型和長度。
注意:
1、創建表時沒有指定字符集,將採用數據庫默認字符集。
2、創建表之前必須使用use db 語法指定操作數據庫。
創建數據表時,一個表中的字段就和Java中的對象的屬性是對應的。一個對象就是這張表中的一行數據記錄。
class Employee{
private String name;
private int age;
private String password;
private Date birthday;
}
上述的Java類,在數據庫對應的表的創建方式應該是:
create table user(
);
而Java類中的每個屬性的數據類型在數據庫中也有對應的數據類型:
Java中的類型數據庫中的類型說明
String,charchar(長度),varchar(長度)char是定長 varchar是變長
解釋:
char(8) 保存 lisi,因為lisi 只有四個字符,所有會補充四個空格,成為8個字符存入 char(8)中
如果有 varchar(8) 自動根據存放內容改變長度
byte、short、int、long、float、doubleTINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE
booleanbit存放0或者1
Datedate (只有日期)
time(只有時間)
datetime(日期時間都有)
timestamp(日期時間都有 )datetime和timestamp 表現形式上完全相同,區別就在於timestamp 在數據庫可以自定更新(當前時間)
大數據類型 inputStream 二進制文件 Reader 文本文件blob(存放大二進制數據)
text(存放大的文本文件)tinyblob tinytext 255字節
blob text 64KB
mediumblob mediumtext 16MB
longblob longtext 4GB
上述的Java類對應的數據庫中的數據表為:
create table employee(
name varchar(32),
age int,
password varchar(32),
birthday date
);
查看錶結構 desc 表名;
創建數據表時,只有字符串類型必須寫長度,而其他類型都有默認長度。
2、單表創建時約束
在創建數據表的時候,一般都需要對錶中的字段進行約束限制,而把這個約束稱為表的約束。
約束的作用:保證數據有效性和完整性。
表的約束有三種:
1、主鍵約束,primary key
主鍵主要作用是記錄某個列(字段)可以唯一區分其他一行信息記錄,這個列(字段)就可以是主鍵 (唯一非空)
2、唯一約束,unique
該列(字段)的值不允許重複。
解釋:一張表中可以有很多個唯一約束,只能有一個(兩個)作為主鍵約束。
即就是在一張表唯一約束的列可以有多列,但是主鍵一般只會使用一列或者兩列(聯合主鍵)
3、非空約束,not null
該字段的值不能為空
創建有約束的單表:
create table employee2(
id int primary key auto_increment,
name varchar(32) unique not null,
age int not null,
password varchar(32) not null,
birthday date
);
如果表中的主鍵約束類型為 數值型 int bigint ,添加auto_increment ,在給表中增加數據的時候,主鍵會自定增加,不用插入。
3、數據表結構修改
表結構修改語法:
alter table 表名 增/刪/改 列名 類型(長度) 約束;
1、增加列: alter table 表名 add 列名 類型(長度) 約束;
2、修改現有列類型、長度和約束 語法:alter table 表名 modify 列名 類型(長度) 約束;
3、修改現有列名稱 語法:alter table 表名 change 舊列名 新列名 類型(長度) 約束;
4、刪除現有列 語法:alter table 表名 drop 列名 ;
5、修改表名 rename table 舊錶名 to 新表名;
6、修改表的字符集:alter table person character set utf8;
練習:
1、在employee2表上增加salary列。
2、修改birthday列不能為null
3、修改列名name為username
4、修改username列的長度為60
5、刪除age列
6、將employee2表名修改為person表
4、數據表刪除
語法:drop table 表名;
5、查看數據表結構
desc 表名; 查看錶結構
show tables ; 查看當前庫內所有表名
show create table 表名; 查看建表語句和字符集
所有數據表結構操作語句都是 DDL
【第四階段】
【目標:簡單增刪改查】
學習了數據表本身的增刪改查之後,現在學習對數據表中的數據的操作。學習的順序是:增,改,刪,查詢。
1、向數據表插入記錄
語法:insert into 表名 (列名,列名,列名......) values (值,值,值......);
.
上述的語法是給表中每列插入數據,需要注意以下問題:
1、插入值類型必須和列類型匹配。
2、值長度不能超過列定義長度。
3、值的順序和列順序對應。
4、字符串和日期型值必須寫單引號。
5、插入空值可以寫null。
創建一個學生表,擁有id,年齡,姓名,性別,成績,出生年月日字段:
create table student(
id int primary key auto_increment,
sage int ,
sname varchar(32) not null,
gender varchar(10) not null,
score double not null,
birthday date
);
插入數據:
insert into student (id,sage,sname,gender,score,birthday) values(null,22,'zhangsan','male',98.99,'1999-09-09');
如果是給數據表中每列都插入數據,可以省略列名:但是後面值必須要和表中所有列進行匹配,按照表中列順序
insert into 表名values(值,值,值……);
如果表中的列可以為空,或擁有默認值,省略可以為空,有默認值部分列名,後面值要和前面列進行匹配
insert into 表名 (列,列,列.... ) values(值,值,值.....);
使用控制檯插入中文:
錯誤原因:mysql client 採用默認字符集編碼 gbk
查看系統所有字符集 : show variables like 'character%';
看到mysql中默認客戶端是UTF8的編碼表,而我們的dos控制檯使用的GBK。這樣我們使用控制檯給數據庫中插入的數據全部都是會亂碼。
這時我們需要修改mysql中控制檯的編碼表為gbk即可。
MYSQL中共有6個地方字符集 :client connetion result 和客戶端相關 、database server system 和服務器端相關
修改客戶端的編碼表有2種方式:
第一種:當前窗口臨時修改 set names gbk ;只對當前窗口有效,關閉後就會失效
第二種:配置mysql/my.ini 文件
[mysql] 客戶端配置
[mysqld] 服務器端配置
修改客戶端字符集 [mysql] 後字符集 default-character-set=gbk
修改了mysql的配置文件之後,需要重啟mysql服務。
2、數據記錄更改操作
語法: update 表名 set 列名=值,列名=值.... where條件語句; 如果沒有where條件語句,默認修改所有行數據
注意:一般修改數據表中的數據,都需要添加where條件,否則將會把整個表中的數據修改,開發時需要十分謹慎。
練習:
1、將所有學生的年齡修改為20歲
2、將姓名為’zhangsan’的學生成績修改為60分
3、將姓名為’lisi’的學生成績修改為80分,性別改為女
4、將wangwu的成績在原有基礎上減去10分
3、數據記錄的刪除操作
語法:delete from 表名 where條件語句 ;
如果不加where條件就會刪除當前表中的所有數據。
練習:
1、刪除表中名稱為’zhangsan’的記錄
2、刪除表中所有記錄
如果要刪除表中所有數據記錄,使用 truncate table 表名; 等價於 delete from 表名;
4、面試題:
使用delete刪除表中所有記錄和使用truncate table刪除表中所有記錄有何不同 ?
truncate 刪除數據,過程先將整個表刪除,再重新創建
delete 刪除數據,逐行刪除記錄
ttruncate 效率要好於 delete
truncate 屬於DDL ,delete 屬於DML
事務管理只能對DML 有效 ,被事務管理SQL語句可以回滾到SQL執行前狀態
insert update delete 屬於DML 語句
5、數據表記錄的查詢
5.1、語法一 :
select [distinct] * | 列名,列名... from 表名;
select * from 表名; 查詢該表中所有列信息
select 列名,列名... from 表名; 查詢表中指定列的信息
distinct 用於去除掉查詢中的重複數據。
重新給student表中插入如下數據:
insert into student (id,sage,sname,gender,score,birthday) values(null,23,'zhangsan','male',98.99,'1990-09-09');
insert into student (id,sage,sname,gender,score,birthday) values(null,23,'lisi','男',56.99,'1990-02-09');
insert into student (id,sage,sname,gender,score,birthday) values(null,24,'王五','女',75.99,'1988-01-01');
insert into student (id,sage,sname,gender,score,birthday) values(null,25,'趙六','男',80.99,'1980-11-12');
練習:
1、查詢表中所有學生的信息
2、查詢表中所有學生的姓名和對應的成績
3、過濾表中重複數據 (查詢成績,排除完全相同重複數據)
給數據表中插入相同的成績:
insert into student (id,sage,sname,gender,score,birthday) values(null,18,'田七','女',80.99,'1980-11-12');
5.2、語法二 :
select 表達式(列名執行運算) from 表名;
練習:
1、在查詢到的所有學生分數上加10分
這裡僅僅是在查詢的成績上加了10分,而數據表中的成績是沒有變化的。上述lisi的成績發現小數位數很多,這時因為在設計學生表的時候成績字段我們使用的double,double表示的小數是一個近似值,需要表示精確數據,可以使用numeric(數據長度,小數長度)
在上述顯示的結果中顯示的成績列名為score+10這樣很不友好,可以使用給SQL語句中的列,表等信息起別名。
格式:使用 as 關鍵字就可以完成。
select 列名 as 別名 from 表名;
在對列起別名時,as可以省略。
問題:下面2個sql語句有啥區別:
1)select sname , score from student; 查詢sname 和math兩列的值
2)select sname score from student; 查詢sname 列的值,並給這列起的別名為score。
5.3、語法三 :
select 列名 from 表名 where條件語句;
練習:
1、查詢姓名為zhangsan的學生信息
2、查詢成績大於80分的學生信息
5.4、運算符:
1) 相等= 不等 <>
2) between ...and... 在兩者之間取值 between 70 and 80 等價於 >=70 <=80 ----- 注意前面那個數要比後面那個數要小
3) in(值,值,值) 在指定值中任取一個 in(70,80,90) 值可以是70、80或者90
4) like '模糊查詢pattern' 進行模糊查詢 ,表達式有兩個佔位符 % 任意字符串 _ 任意單個字符 例如: name like '張%' 所有姓張學員
name like '張_' 所有姓張名字為兩個字學員
5) is null 判斷該列值為空
6) and 邏輯與 or 邏輯或 not 邏輯非
練習:
1、查詢成績在80~100之間的學生信息
上述的查詢語句也可以使用:
select * from student where score between 80 and 100;
2、查詢年齡為18,23,25的同學信息
3、查詢所有姓趙的學生信息
4、查詢年齡>23,成績>80的同學信息
insert into student (id,sage,sname,gender,score,birthday) values(null,null,'秋香','女',84,null);
查詢沒有生日學員信息
在SQL中不能使用=null,或者<>null ,判斷是否為null 需要使用 is null, 不是null,需要使用 is not null
查詢有年齡學員信息
5.5、語法四 :
select * from 表名 order by 列名 asc|desc ;
asc是升序排列,desc是降序排列
練習:
1、對成績排序後輸出
2、對年齡排序按從高到低(降序)的順序輸出
3、對學生成績按照降序排序,成績相同按照年齡降序
【第五階段】
【目標:SQL中的函數】
聚集函數:也成為分組函數,主要是指SQL語句中的內置函數。用於分組統計。
1、count函數:
統計查詢結果記錄條數,語法:select count(*)|count(列名) from 表名;
練習:
1、統計一個班級共有多少學生?
2、統計成績大於80的學生有多少個?
2、sum函數:
統計某一列數據的和,語法:select sum(列名) from 表名;
練習:
1、統計一個班級成績和
2、分別統計年齡和成績的和
3、統計年齡和成績和值
上述這個結果是錯誤,在第2題目中分別統計出來的年齡和成績和值明顯比現在統計的值大。
這是因為在表中秋香的年齡為null 在把秋香的年齡和成績 計算到一起的時候,在mysql中所有的null與其他數據運算的結果還是一個null,導致秋香的成績沒有加到總和中。
在mysql中有ifnull函數可以判斷某列是否為null,當為null時,可以給這裡設置一個值。
上述的ifnull(sage,0) 意思就是當某個學生的sage為null時,給其設置值為零。
3、avg函數:
統計某一列平均值 語法: select avg(列名) from 表名;
練習:
求一個班級平均分
可以使用計算出班級成績總和,除以總人數,也可以使用avg函數
4、max,min函數:
max統計一列最大值
min 統計一列最小值
5、group by分組函數
select 分組函數 from exam group by 列名; 按照某列進行分組統計
分組操作,就是具有相同數據記錄分到一組中,便於統計
創建訂單表,演示分組函數。
create table orders(
id int,
product varchar(20),
price float
);
insert into orders(id,product,price) values(1,'電視',900);
insert into orders(id,product,price) values(2,'洗衣機',100);
insert into orders(id,product,price) values(3,'洗衣粉',90);
insert into orders(id,product,price) values(4,'桔子',9);
insert into orders(id,product,price) values(5,'洗衣粉',90);
練習:
1、對訂單表中商品歸類後,顯示每一類商品的總價
需要先對商品進行歸類,按照商品的名稱分組,這樣相同的名稱商品就可以歸納為一組。然後在計算這些商品的價格和值。
2、查詢購買了幾類商品,並且每類總價大於100的商品
需要對商品按照名稱歸類,然後在計算商品的價格和值,最後需要按照價格和值進行過濾。
由於在sql語句中,分組函數不能使用where關鍵字之後,這裡需要使用having關鍵字。
having關鍵字和where關鍵字作用是一致的,都可以對查詢的數據進行過濾,區別在於having關鍵字後面的過濾條件中可以使用分組函數。
6、面試題目:
where 和 having 條件語句的區別 ?
where 是在分組前進行條件過濾,having 是在分組後進行條件過濾
使用where地方都可以用 having替換 , 但是having可以使用分組函數,而where後不可以用分組函數
select語句的小節:
S-F-W-G-H-O 組合 select ... from ... where ... group by... having... order by ... ; 順序不能改變
解析順序 : from - where - group by - having - select - order by
【第六階段】
【目標:MySQL 數據庫的備份和恢復】
在實際開發中經常需要對數據庫中的數據進行備份和恢復。
Mysql提供相應的備份和恢復的機制。
1、備份命令
在mysql的安裝目錄的bin目錄下有mysqldump命令,可以完成對數據庫的備份。
語法:mysqldump -u 用戶名 -p 數據庫名 > 磁盤SQL文件路徑
由於mysqldump命令不是sql命令,需要在dos窗口下使用。
演示:
1、重新開啟一個新的dos窗口。
2、導出數據。
這樣在c盤下面就會有一個day12.sql文件:
這個文件中保存這day12數據庫中的所有所有表和數據。
2、恢復命令
上述已經把數據進行了備份,因此就可以刪除day12數據庫了。
再次查詢已經沒有day12數據庫
還原數據庫使用的是mysql安裝目錄下的mysql命令
語法:
mysql -u 用戶名 -p 數據庫名 < 磁盤SQL文件路徑
還原數據庫的時候,是不會創建數據庫的,只會還原數據庫中的表和數據,因此需要在還原數據庫之前,手動的創建數據庫。
還原數據:
查詢恢復的數據表:
補充:
在還原數據的時候,也可以在已經切換到需要還原的數據庫中使用Source命令;
mysql>語法:source 備份文件路徑
再次查詢day12數據庫中的所有數據表:
【第七階段】
【目標:外鍵約束和表設計】
外鍵約束介紹
現在假設有2個表,分別是部門表和員工表。
在一個公司中擁有多個部門,而每一個員工都屬於某個部分。也就是員工表中的員工和部門表中的部分之間存在一定的關係。
這樣我們一般在設計這2張表的時候,會在員工表中增加一列,用於表示當前員工屬於那個部門。而這一列經常會用部門表中的主鍵。
而把員工表引用的部門表的主鍵稱為員工表中的外鍵。
外鍵的語法:
constraint 約束鍵名 foreign key (列名) references 外鍵對應的表名(列名)
演示創建上述兩張表
create table emp(
id int primary key auto_increment,
name varchar(20),
salary double,
dept_id int,
constraint dept_id_FK foreign key(dept_id) references dept(id)
);
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values(null,'財務部');
insert into dept values(null,'技術研發部');
insert into dept values(null,'市場部');
insert into emp values(null,'張三','3500',1);
insert into emp values(null,'李四','3500',1);
insert into emp values(null,'老唐','10000',2);
insert into emp values(null,'老張','10000',2);
insert into emp values(null,'老王','8500',3);
insert into emp values(null,'老劉','8500',3);
使用mysql的圖形化工具,這時可以看到兩張表之間的關係:
當我們要刪除dept部門表中的信息時,就無法刪除
上述出錯的原因是在刪除部門表彙總的市場部時,由於這個部門的id在員工表中有員工引用,因此在刪除之前,必須先把員工表引用市場部id的員工安排到其他的部分中,然後保證部門表中的市場部id沒有其他表引用,才可以刪除掉。
這就是外鍵約束的作用。外鍵約束是保證數據完整性和有效性。
更多優質內容推薦:
體驗別樣旅遊感受 中公教育推出全新互聯網+北京遊,零利潤體驗10天9晚帝都風情:
http://www.ujiuye.com/zt/qgsqxly/?wt.bd=lgh23524
有錢任性,某公司豪擲500萬幫助20左右年輕人找工作,起因是做善良的人:
http://www.ujiuye.com/zt/jyfc/?wt.bd=lgh23524