mysql CRUD筆記

mysql常用命令:

檢視資料庫.

	1.show databases;
	2.use 資料庫名稱;
	3.show create database 資料庫名稱; --檢視資料庫的建立資訊.
	4.select database();	--檢視當前使用的資料庫.

刪除資料庫(重點)

1.drop database 資料庫名稱; --刪除資料庫

修改資料庫

1.語法:alter database 資料庫名稱 character set 'gbk' collate '校對規則';

表結構
建立表

1.語法:

create table 表名稱(
	欄位1 型別(長度) 約束,
	欄位2 型別(長度) 約束,
	欄位3 型別(長度) 約束
);

2.注意.

• 建立表的時候,後面用小括號，後面分號.
• 編寫欄位，欄位與欄位之間用逗號，最後一個欄位不使用逗號.
• 如果宣告字串型別的型別，長度必須指定的.
• 如果不指定資料的長度，有預設值。int 型別的預設長度是11.

資料庫資料型別(重點)
字串型別(重點)

VARCHAR(用的比較多)：長度是可變的。例子：name varchar(8),存進去'hello', 存入進去之後，
	欄位長度自動變成5.
CHAR:長度不可變。
大資料型別(不常用)
BLOB : 位元組(電影 MP3)
TEXT : 字元(文字的內容)

資料值(重點)

TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE

邏輯性
BIT
在Java中是true或者false
在資料庫bit型別(1或者0)

日期型(重點)

DATE ：只包含日期(年月日)
TIME : 只包含時間(時分秒)
DATATIME : 包含日期和時間。如果插入資料的時候, 字元值為空,欄位的值就是空了.
TIMESTAMP : 包含日期和時間.如果插入資料的時候,設定欄位的值為空,預設獲取當前的系統的時間，
	把時間儲存到欄位中.

4.練習建立一張表結構.

create table employee(
	id int,
	name varchar(30),
	gender char(5),
	birthday date,
	entry_date date,
	job varchar(50),
	salary double,
	resume text
);

5.檢視資料庫表結構.

1).查詢所有的表機構
	show tables;
2).檢視錶結構的詳細的資訊.
	desc 表名稱;
3)
	show create table 表名稱;

6.刪除表
	drop table 表名稱;

7.修改表(掌握)

	1.新增一個新的欄位.
		alter table 表名稱 add 欄位名稱 資料型別(長度) 約束;
	2.修改欄位的資料型別, 長度或者約束.
		alter table 表名稱 modify 欄位名稱 資料型別(長度) 約束;
	3.刪除某一個欄位.
		alter table 表名稱 drop 欄位名稱 ;
	4.修改欄位的名稱.
		alter table 表名稱 change 舊欄位 新欄位 資料型別(長度) 約束;
	5.修改表的名稱.
		rename table 舊錶名 to 新表名;

8.資料庫的資料(CRUD)

插入資料(insert)
1.新增資料,使用insert來新增資料
	insert into 表(欄位1,欄位2,欄位3) values(欄位1,欄位2, 欄位3);
	insert into 表 values(欄位1,欄位2, 欄位3...);
2.注意實現
	插入的資料與欄位資料的型別相同.
	資料的大小應該在規定的範圍內.
	資料中的資料的列的位置和欄位位置的相同
	字串和日期型別的資料,必須要使用單引號括起來
3.
	insert into user (username, job) values ('tom', 'it')
	insert into user values (2, 'tom2', ...)

4.插入中文資料亂碼.
1.插入中文,會產生亂碼的問題.
方案:修改mysql客戶端的編碼就可以了.改成GBK.

9.修改資料的語句(update).

修改資料,使用update關鍵字來完成修改資料.
	* update 表名稱 set 欄位1=值1, 欄位2=值2, where 條件;
	* 修改欄位1和欄位2的值
		*如果沒有where的關鍵字,說明修改的預設所有的記錄.
		*如果有where的關鍵字,修改的是符合條件的記錄.

10.刪除資料(delete)

1.刪除語句的語法
	* delete from 表 where 條件;

2.刪除所有的資料
	* delete from 表;
		* 一行一行的資料
		* 支援事務的操作.事務是資料庫中的特性.
	*truncate 表;
		* 先把整個表刪除掉(資料也刪除了),建立一個與原來一摸一樣的表.
		* 不支援事務.
3.練習.
	* delete from user where username='tom'
	* 刪除表中的所有記錄
		新增事務
			* start transaction;
			* delete from user where username ='tom2';
			* rollback;
	* 使用truncate刪除表記錄.
		truncate user;

11. 查詢資料select(重點)

1.查詢語句的語法
	select * from 表;
	select 欄位1,欄位1,欄位3 from 表;
	DISTINCT
	可以對查詢的列進行運算.

	*查詢語句中可以使用as的關鍵字,起別名.
	* 別名的真正的用法,採用的多表的查詢,為了區分每張表,表起個別名.
	* as的關鍵字可以省略不寫.中間需要使用空格
	*一般都是給表來起別名
		* select s.username, s.math from stu s;
		* select s.username, u.username from stu s, user u
	使用where的條件語句,進行查詢條件的過濾.

2.練習 
	create table stu(
		id int,
		username varchar(20),
		math int,
		english int,
		chinese int
	);
	insert into stu values (1, '美美',18, 97, 35);
	insert into stu values (2, '小鳳',98, 97, 95);
	insert into stu values (3, '小花',88, 90, 77);
	insert into stu values (4, '熊大',79, 59, 95);
	insert into stu values (5, '熊二',15, 11, 89);

1.查詢表中的所有資料.
	select * from stu;
2.查詢表中的所有學生的姓名和對應的英語成績.
	select username, english from stu;
3.過濾表中的重複資料.
	select DISTINCT english from stu;
4.在所有學生分數加上10分特長分.
	select username, math+10, english+10, chinese+10 from stu;
5.統計每個學生的總分.
	select username,(math+english+chinese) from stu;
6.使用別名表示學生分數.
	select username,(math+english+chinese) as t from stu;
7.查詢姓名為美美的學生成績.
	select * from stu where username = '美美';
8.查詢英語成績大於90分的同學.
	select username, english from stu where english > 90;
9.查詢總分大於200分的同學.
	select username,(math+english+chinese) from stu where (math+english+chinese) > 150;

where子句後可以使用的符號.
1.常用的符號
	>	<	 <=	>=	=	<>
	in --代表的範圍
		select * from stu where math = 88;
		select * from stu where math in (18,88,90); --表示english可以是18, 88,90這幾個值.
	like	--模糊查詢
		寫法:
			select * from stu where username like '張_';	--使用_佔位符,結果是姓張的,張飛,張三, 張翼德(不符合)
			select * from stu where username like '張%';	--使用%佔位符,結果是姓張的,只要姓張的都可以.
		_:一個位置.
		%:多個位置.

		and:與
		or:或
		not:非
		between ... and

2.總結查詢的語句
	select 欄位1,欄位2, | * from 表 where 條件的過濾;

12.使用order by對結果進行排序.

1.語法
	order by 欄位 asc | desc;
		asc: 升序(預設值).
		desc: 降序.
2.注意.
	order by 自己放在select的語句末尾.
	select * from xx where xx order by xx;

3.練習.
	select username, math from stu order by math asc;
	select username, math from stu order by math desc;

13.聚集函式.

1.什麼是聚集函式:Excel表格.求數量,求和,平均值,最大值,最小值.
2.聚集函式操作的是某一列的資料.
3.聚集函式.
	count()	--求數量
		select count(*) | count(列名) from 表;

	練習:
		統計一個班級共有多少學生?
			select count(*) from stu;
			select count(id) from stu;
		統計數學成績大於90的學生有多少個?
		select count(math) from stu where math > 60;
		select username, count(math) from stu where math > 60; --不會這樣查詢
		統計總分大於220的人數有多少?
		select count(*) from stu where (math+english+chinese) > 220;

	sum()	--求某一列資料的和
		注意:沒有sum(*), 求的某一列, sum只對數值型別起作用.

	練習
		統計一個班級數學總成績?
			select sum(math) from stu;

	avg() -- 求平均分
		select avg(欄位) from stu;
	max() -- 求最大值
	min() -- 求最小值

14.分組查詢.

group by 欄位 根據欄位進行分組.

create table orders(
	id int,
	product varchar(20),
	price float
);
insert into orders values(1, '電視', 900);
insert into orders values(2, '洗衣機', 100);
insert into orders values(3, '電視', 90);
insert into orders values(4, '電視', 900);
insert into orders values(5, '洗衣粉', 90);
insert into orders values(6, '洗衣粉', 90);

練習:
對訂單表中商品歸類後,顯示每一類商品的總價?
	select product, sum(price) from orders group by product;
		--已經把原來的一組的資料,現在分為兩組.在統計資料的時候,以組為單位.

having和where類似,但是隻能放在group by後面
where後面不能使用聚集函式,having可以.

15.單表的約束(主鍵)

1.可以把某一列欄位宣告主鍵,這一列的資料有如下特點.
	非空
	唯一
	被引用	--當前主鍵的列,作為一條記錄的標識.
2.宣告主鍵
	使用關鍵字primary key 宣告某一個欄位為主鍵.

3.測試主鍵
	建立新的資料
		create database day06;
		use day06;
		create table person(
			id int primary key,
			username varchar(20)
		);
	新增資料
		insert into person values(1, '美美')
		insert into person values(2, '小鳳')
4.主鍵的自動增長.
	主鍵的值特點, 可以把主鍵的值交給資料庫去維護.
	自動增長只能使用int 和bigint型別
	通過關鍵字auto_increment


唯一和非空約束
1.唯一的約束.
	* 可以把某一個欄位宣告成唯一的值.
	* 使用該關鍵字 unique
2.非空
	* 可以把某一個欄位宣告成非空的
	* 值是不能空值
	* 使用關鍵字 not null
練習:
	create table person(
		id int primary key auto_increment,
		username varchar(30) unique,
		email varchar(30) not null
	);

16.多表約束(外來鍵)

外來鍵的約束:目的保證表結構中資料的完整性!
外來鍵欄位的取值只能從主鍵獲取值.
1.模擬的過程
2.有一個部門,還有一個員工表.一個部門有多個員工.
3.建立表結構.
	create table dept(
		did int primary key auto_increment,
		dname varchar(30)
	);

	create table emp(
		eid int primary key auto_increment,
		ename varchar(30),
		sal double,
		dno int,
		foreign key (dno) references dept (did)
	);

17.多表的設計.

一對一(瞭解):
	公司表		地址表
  id 名稱   時間     id 名稱 xxx
	  主鍵對主鍵
	可以合成一張表.


一對多(重點):一般兩張表
主表(一方),從表(多方)
建表原則,在多方表中新增欄位,把該欄位作為外來鍵,指向一方表的主鍵.


多對多(重點):
場景:
1..	訂單表		商品表
2.使用者與角色.一個使用者擁有多個角色.一個角色被多個使用者所擁有.
其實有三張表,有一張中間表,儲存資料關係.
	使用者表			角色表
	id 主鍵			id 主鍵
	name			rname

	 
		使用者角色中間表	
	 	至少要包含2個欄位
		uid	外來鍵->使用者id
		rid	外來鍵->角色id
eg:			
			 中間表
	使用者表		uid	rid	角色表
	1  張三		1	1	1.管理員
	2.趙四		1	2	2.遊客
			2	1

多對多拆可以分成2個1對多的場景.