mysql入門學習

標籤：des   io   使用   ar   for   檔案   資料   sp   問題   

一、操作資料庫
1.建立資料庫
CREATE DATABASE [IF NOT EXISTS] db_name   [create_specification [, create_specification] ...]
create_specification:   
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

~建立一個名稱為mydb1的資料庫。
CREATE DATABASE mydb1;
~建立一個使用utf8字元集的mydb2資料庫。
create database mydb2 character set utf8;
~建立一個使用utf8字元集，並帶校對規則的mydb3資料庫。
create database mydb3 character set utf8 collate utf8_bin ;
2.查看資料庫
顯示資料庫語句：查看當前資料庫伺服器中所有的資料庫
SHOW DATABASES
顯示資料庫建立語句：
SHOW CREATE DATABASE db_name
~查看當前資料庫伺服器中的所有資料庫 show databases;
~查看前面建立的mydb2資料庫的定義資訊show create database mydb2;
3.刪除資料庫
DROP DATABASE [IF EXISTS] db_name

~刪除前面建立的mydb1資料庫 drop database mydb1;
drop database mydb1;

4.修改資料庫
ALTER DATABASE [IF NOT EXISTS] db_name  [alter_specification [, alter_specification] ...]
alter_specification:   
[DEFAULT] CHARACTER SET charset_name | [DEFAULT] COLLATE collation_name

查看伺服器中的資料庫，並把其中某一個庫的字元集修改為gbk;
5.切換資料庫
選擇資料庫：use db_name;
查看所選的資料庫: select database();

二、動作表
1.建立表
CREATE TABLE table_name
(
field1 datatype,
field2 datatype,
field3 datatype,
)character set 字元集 collate 校對規則

~建立員工資訊表
create table employee(
id int primary key auto_increment,
name varchar(30) unique,
gender bit not null,
birthday date,
entry_date date,
job varchar(255),
salary double,
resume text
);

2.查看錶
查看錶結構: desc tab_name;
查看錶的建立語句: show create table tab_name;
查看當前資料庫中的所有表：show tables;

3.修改表
ALTER TABLE table ADD/MODIFY/DROP/character set/change (column datatype [DEFAULT expr] [, column datatype]...);
修改表名：rename table 表名 to 新表名;

~在上面員工表的基本上增加一個image列。
alter table employee add image blob;
~修改job列，使其長度為60。
alter table employee modify job varchar(60);
~刪除gender列。
alter table employee drop gender;
~表名改為user。
rename table employee to user;
~修改表的字元集為gbk
alter table user character set gbk;
~列名name修改為username
alter table user change name username varchar(30);
4.刪除表
drop table tabName;
~刪除user表
drop table user;

三、動作表記錄

MySql亂碼：
用戶端在發送資料時使用的編碼和伺服器認為的用戶端是的編碼不相同，則發送時用的編碼和接受時用的編碼不同，產生亂碼
解決方案：用戶端一上來就可以使用set names gbk;命令通知伺服器當前用戶端發送資料時使用的編碼是什麼，這樣伺服器在和當前用戶端互動時就會使用指定的編碼從而可以解決亂碼問題。
我們也可以修改mysql安裝目錄下的my.ini檔案，通過修改default-character-set值，可以設定伺服器預設認為的用戶端的編碼。

1.增加記錄
INSERT INTO table [(column [, column...])] VALUES (value [, value...]);
~向員工資訊表中插入三條記錄
insert into employee (id,name,gender,birthday,entry_date,job,salary,resume) values (null,‘張飛‘,1,‘1990-09-09‘,‘1990-10-01‘,‘打手‘,998.00,‘真的很能打‘);

insert into employee values (null,‘關羽1‘,0,‘1980-08-08‘,‘1980-10-10‘,‘耍大刀的‘,9000.00,‘ceo的親弟弟，雖然只會耍刀，但是掙得的很多‘);

insert into employee values (null,‘劉備‘,1,‘1970-08-08‘,‘1970-10-10‘,‘公司ceo‘,900000.00,‘公司老總基本很閑‘), (null,‘趙雲‘,1,‘1999-08-08‘,‘1999-10-10‘,‘保安隊長‘,90.00,‘老總貼身保鏢‘);

2.修改記錄
UPDATE tbl_name  SET col_name1=expr1 [, col_name2=expr2 ...] [WHERE where_definition]   

~將所有員工薪水修改為5000元。
update employee set salary=5000;
~將姓名為’張飛’的員工薪水修改為3000元。
update employee set salary=3000 where name=‘張飛‘;
~將姓名為’關羽’的員工薪水修改為4000元,job改為ccc。
update employee set salary=4000,job=‘ccc‘ where name=‘關羽‘;
~將劉備的薪水在原有基礎上增加1000元。
update employee set salary=salary+1000 where name=‘劉備‘;

3.刪除記錄
delete from tbl_name  [WHERE where_definition]

~刪除表中名稱為’張飛’的記錄。
delete from employee where name=‘張飛‘;
~刪除表中所有記錄。
delete from employee;
~使用truncate刪除表中記錄。
truncate employee;

4.查詢記錄
建立表 ：
create table exam(
id int primary key auto_increment,
name varchar(20) not null,
chinese double,
math double,
english double
);

insert into exam values(null,‘關羽‘,85,76,70);
insert into exam values(null,‘張飛‘,70,75,70);
insert into exam values(null,‘趙雲‘,90,65,95);

（1）SELECT [DISTINCT] *|{column1, column2. column3..} FROM table;

~查詢表中所有學生的資訊。
select * from exam;
~查詢表中所有學生的姓名和對應的英語成績。
select name,english from exam;
~過濾表中重複資料。
select distinct english from exam;
~在所有學生分數上加10分特長分顯示。
select name,english+10,chinese+10,math+10 from exam;
~統計每個學生的總分。
select name ,english+chinese+math from exam;
~使用別名表示學生總分。
select name as 姓名,english+chinese+math as 總成績 from exam;
select name 姓名,english+chinese+math 總成績 from exam;
select name english from exam;

（2）使用Where子句進行過濾查詢
~查詢姓名為張飛的學產生績
select * from exam where name=‘張飛‘;
~查詢英語成績大於90分的同學
select name,english from exam where english > 90;
~查詢總分大於230分的所有同學
select name 姓名 ,math+english+chinese 總分 from exam where math+english+chinese>230;
~查詢英語分數在 80－100之間的同學。
select name,english from exam where english between 80 and 100;
~查詢數學分數為75,76,77的同學。
select name,math from exam where math in(75,76,77);
~查詢所有姓張的學產生績。
select * from exam where name like ‘張%‘;
select * from exam where name like ‘張__‘;
~查詢數學分>70，語文分>80的同學。
select * from exam where math>70 and chinese > 80;
(3)排序查詢
SELECT column1, column2. column3..
FROMtable
order by column asc|desc

~對英語成績排序後輸出。
select name,english from exam order by english;
~對總分排序按從高到低的順序輸出
select name 姓名,math+english+chinese 總分 from exam order by 總分 desc;
~對姓張的學產生績排序輸出
select name 姓名,math+english+chinese 總分 from exam where name like ‘張%‘ order by 總分 desc;descend
(4)彙總函式
~1.count()返回某一列，行的總數
~統計一個班級共有多少學生？
select count(*) from exam;
~統計數學成績大於70的學生有多少個？
select count(*) from exam where math > 70;
~統計總分大於250的人數有多少？
select count(*) from exam where math+english+chinese>230;
~2.Sum函數返回滿足where條件的行的和
~統計一個班級數學總成績？
select sum(math) from exam;
~統計一個班級語文、英語、數學各科的總成績
select sum(chinese),sum(english) ,sum(math) from exam;
~統計一個班級語文、英語、數學的成績總和
select sum(ifnull(chinese,0)+ifnull(english,0)+ifnull(math,0)) from exam;
~統計一個班級語文成績平均分
select sum(chinese)/count(*) from exam;
~3.Avg求合格某一列的平均值
~求一個班級數學平均分？
select avg(math) from exam;
~求一個班級總分平均分？
select avg(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;
~4.Max/min函數返回滿足where條件的一列的最大/最小值
~求班級最高分和最低分
select max(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;
select min(ifnull(math,0)+ifnull(chinese,0)+ifnull(english,0)) from exam;

(5)分組查詢

建立表：
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);

SELECT column1, column2. column3.. FROMtable group by column having ...

~對訂單表中商品歸類後，顯示每一類商品的總價
select product,sum(price) from orders group by product;
~查詢購買了幾類商品，並且每類總價大於100的商品
select product,sum(price) from orders group by product having sum(price)>100;

where 和 having的區別：
where用來在分組之前進行過濾，having用來在分組之後進行過濾
having子句中可以使用彙總函式，where子句中是不可以使用彙總函式
在很多情況下having可以替代where

~查詢單價小於100但是總價大於150的商品的名稱
select product,sum(price) from orders where price<100 group by product having sum(price)>150;

!!!!!SQL語句的書寫順序：
select from where groupby having orderby
!!!!!SQL語句的執行順序：
from where select group by having order by
四、備份/恢複資料庫
備份：
在cmd視窗中，mysqldump -u root -p db_name>c:/1.sql
恢複：
建立資料庫
回到cmd視窗，mysql -u root -p db_name<c:/1.sql

或

建立資料庫
在mysql命令列下，進入資料庫，source c:/1.sql

五、多表設計
外鍵約束：
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values(null,‘財務部‘),(null,‘人事部‘),(null,‘銷售部‘),(null,‘行政部‘);

create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int,
foreign key(dept_id) references dept(id)
);
insert into emp values(null,‘奧巴馬‘,1),(null,‘哈利傳輸速率‘,2),(null,‘本拉登‘,3),(null,‘樸乾‘,3);

1對多 ： 在多的一方設計外鍵儲存一的一方的主鍵
1對1 ： 在任意一方設計外鍵儲存另一方的主鍵
多對多 ： 設計第三方關係表儲存兩張表的主鍵的對應關係

六、多表查詢
create table dept(
id int primary key auto_increment,
name varchar(20)
);
insert into dept values(null,‘財務部‘),(null,‘人事部‘),(null,‘銷售部‘),(null,‘行政部‘);

create table emp(
id int primary key auto_increment,
name varchar(20),
dept_id int
);
insert into emp values(null,‘奧巴馬‘,1),(null,‘哈利傳輸速率‘,2),(null,‘本拉登‘,3),(null,‘樸乾‘,3);

笛卡爾積查詢：
將兩張表中的記錄進行相乘的操作得出的結果，如果左表中有n條記錄，右表中有m條記錄，則最終查處n*m條記錄。笛卡爾積查詢往往包含著大量錯誤的資料，所以通常我們是不會使用它的。
select * from dept,emp;

內串連查詢：只查處左邊表和右邊表都能找到對應關係的記錄
select * from dept,emp where emp.dept_id = dept.id;
select * from dept inner join emp on emp.dept_id =dept.id;
外串連查詢：
左外串連查詢：在內串連的基礎上增加左邊表有而右邊表找不到對應記錄的記錄
select * from dept left join emp on emp.dept_id=dept.id;
右外串連查詢：在內串連的基礎上增加上右邊表有而左邊表沒有的記錄
select * from dept right join emp on emp.dept_id = dept.id;
全外串連查詢：在內串連的基礎上增加上左邊表有而右邊表沒有的記錄和右邊表有而左邊表沒有的記錄
select * from dept full join emp on emp.dept_id=dept.id; -- MySql不支援全外串連

select * from dept left join emp on emp.dept_id=dept.id
union
select * from dept right join emp on emp.dept_id=dept.id;

mysql入門學習