MySQL簡介
創(chuàng)始人芬蘭人，2009年以10億美金MySql賣給Sun公司
1年后，Sun被Oracle收購
MySql不被Oracle重視，開發(fā)社區(qū)被收縮，開發(fā)進度緩慢
開源社區(qū)認(rèn)為MySql存在閉源風(fēng)險
MySql創(chuàng)始人，在MySql源碼基礎(chǔ)上，開了一個新的分支 MariaDB
1.mysql客戶端
1.連接本機服務(wù)器，登陸服務(wù)器
mysql -uroot -p
[Enter password]
2.查看數(shù)據(jù)庫
show databases;
3.進入數(shù)據(jù)庫
use 數(shù)據(jù)庫名;
4.查看數(shù)據(jù)庫表
show tables;
5.查看表結(jié)構(gòu)
desc 表名;
6.退出登錄\斷開連接
exit;
quit;
\q
2.建庫、建表
建庫
-- 刪除db1庫
drop database if exists db1;
-- 重新創(chuàng)建db1庫
create database db1 charset utf8;
-- 查看、進入db1庫
show databases;
use db1;
建表
-- 刪除stu學(xué)生表
drop table if exists stu;
-- 創(chuàng)建stu表
create table stu(
id int,
name varchar(20),
gender char(1),
birthday date
);
-- 查看表結(jié)構(gòu)
desc stu;
3.數(shù)據(jù)類型
數(shù)字
| 數(shù)據(jù)類型 | 相關(guān)描述 |
|---|---|
| unsigned | 無符號，只有正數(shù) |
| zerofill | 配合顯示位數(shù)，不足補0 |
| tinyint | |
| smallint | |
| int | 查詢時不足位數(shù)按規(guī)定位數(shù)顯示 |
| bigint | |
| float | 單精度 |
| double | 雙精度，運算不精確 |
| descimal |
字符串
| 數(shù)據(jù)類型 | 相關(guān)描述 |
|---|---|
| char | 1.定長字符串，存儲訪問效率高 2.字符串長度不足，補空格 3.超出部分根據(jù)數(shù)據(jù)庫設(shè)置，可能出錯也可能截斷4.最長255個字符 |
| varchar | 1.變長字符串，存儲訪問效率比char低 2.最長不超過65535個字節(jié) 3.一般超過255個字節(jié)，使用test類型 |
| test | 長文本類型，最長65535字節(jié) |
日期時間
| 日期類型 | 相關(guān)描述 |
|---|---|
| date | 格式(年月日) |
| time | 格式(時分秒) |
| datatime | 格式(年月日時分秒) |
| timestamp | 1.時間戳 2.最大表示2038年 3.在插入數(shù)據(jù)、修改數(shù)據(jù)時，自動更新系統(tǒng)當(dāng)前時間 |
4.sql入門
sql:Structured Query Language是一種結(jié)構(gòu)化的查詢語言
| sql類型 | 描述 | 作用 |
|---|---|---|
| DDL | 數(shù)據(jù)定義語言 | CREATE,DROP,ALTER |
| DML | 數(shù)據(jù)操作語言 | INSERT,UPDATE,DELETE |
| DQL | 數(shù)據(jù)查詢語言 | SELECT |
| DCL | 數(shù)據(jù)控制語言 | GRANT,REMOVE ... |
| TCL | 事務(wù)控制語言 | COMMIT,ROLLBACK... |
中文編碼
-- 把客戶端編碼告訴服務(wù)器，這樣服務(wù)器可以做正確的編碼轉(zhuǎn)換(按自己PC編碼設(shè)置)
set names gbk;
Insert:插入數(shù)據(jù)
-- 插入完整表數(shù)據(jù)
insert into stu values(1,'張三','男','1996-11-23');
-- 插入部分表數(shù)據(jù)
insert into stu (id,name) values(2,'李四');
--插入多條表數(shù)據(jù)
insert into stu (id,name) values(3,'王五'),(4,'趙六'),(5,'錢七');
-- 查詢表數(shù)據(jù)
select * from stu;
Update:更新,修改數(shù)據(jù)
-- 把id為4,趙六的性別和生日修改成'nv','1998-8-8'
update stu set gender ='女',birthday ='1998-8-8';
Delete:刪除數(shù)據(jù)
-- 刪除id>4的數(shù)據(jù)
delete from stu where id>8;
Select:查詢數(shù)據(jù)
-- 查詢所有字段
select * from stu;
-- 查詢指定字段
select name,gender from stu;
4.sql進階
-- 準(zhǔn)備測試數(shù)據(jù)
-- hr_mysql.sql(sql腳本數(shù)據(jù),文末可獲取相關(guān)資源)
-- 執(zhí)行這個文本中的sql代碼
source 拖曳腳本到dos窗口(腳本路徑)
-- 查看表
show tables;
-- 員工表結(jié)構(gòu)
desc emps;
-- 員工表數(shù)據(jù)
select * from emps;
select * fname,sal,dept_id from emps
where子句
| 過濾條件 | 相關(guān)描述 |
|---|---|
| = | 等值過濾 |
| <> | 不等過濾 |
| > >= < <= | |
| between 小 and 大 | >= 小 并且 <= 大 |
| in(7,2,9,4) | 在指定的一組值中取值 |
| is null\is not null | 是null\不是null |
| like | 字符串模糊匹配(%,_ ,%,_,\) |
| not | not between and,not in(...),is not null,not like |
| and | 并且 |
| or | 或者 |
-- 員工id是122
select id,fname,sal,dept_id from emps where id=122;
-- 部門編號dept_id是30
select id,fname,sal,dept_id from emps where dept_id=30;
-- 工作崗位代碼job_id是'IT_PROG'
select id,fname,sal,dept_id,job_id from emps where job_id='IT_PROG';
-- 部門編號dept_id 不是 50
select id,fname,sal,dept_id from emps where dept_id<>50;
-- 工資sal>5000
select id,fname,sal,dept_id from emps where sal>5000;
-- 工資范圍[5000,8000]
select id,fname,sal,dept_id from emps where sal>=5000 and sal<=8000;
--
select id,fname,sal,dept_id from emps where sal between 5000 and 8000;
--id 是(120,122,100,150)
select id,fname,sal,dept_id from emps where id in (120,122,100,150);
--
select id,fname,sal,dept_id from emps where id=120 or id=122 or id=100 or id=150;
-- 沒有部門的員工,dept_id 是 null
select id,fname,sal,dept_id from emps where dept_id is null;
-- 有提成的員工,com_pct 不是 null
select id,fname,sal,dept_id from emps where com_pct is not null;
-- fname中包含en
select id,fname,sal,dept_id from emps where fname like '%en%';
-- fname第3,4個字符是en
select id,fname,sal,dept_id from emps where fname like '__en%';
distinct:去重
- select distinct a from ... (去除a字段重復(fù)值)
- select distinct a,b from ...(去除a,b字段組合的重復(fù)值)
-- 查詢所有部門id
select distinct dept_id from emps where dept_id is not null;
order by 子句
排序(asc 升序[默認(rèn)],desc 降序)
-- 查詢50部門員工,按工資降序
select id,fname,sal,dept_id from emps where dept_id=50 order by sal desc;
--所有員工按部門升序，相同部門按工資降序
select id,fname,sal,dept_id from emps order by dept_id, sal desc;
5.查詢執(zhí)行順序
select 字段
from
where
order by
- where 過濾
- 選取字段
- order by 排序
6.單引號
字符串內(nèi)容中的單引號,用兩個單引號去轉(zhuǎn)義
'I'm ABC'
'I''m ABC'
use db1;
insert into stu(id,name) values(6422,'I''m Xxx');
select * from stu;
-
sql注入攻擊
通過正在sql語句中,注入單引號,改變sql語句結(jié)構(gòu)
select * from user where username='張三' and password = '1' or '1'='1';
select * from user where username='chenzs'#' and password='';
防止sql和注入攻擊(用戶填寫的內(nèi)容中,所有單引號都變成兩個單引號)
7.函數(shù)
字符串函數(shù)
| 函數(shù) | 相關(guān)描述 |
|---|---|
| char_length(str) | 獲取str字符數(shù) |
| length(str) | 獲取str字節(jié)數(shù) |
| left(str,length) | 獲取左側(cè)字符 |
| substring(str,start,length) | 截取字符串str |
| instr(str,substr) | 查詢子串位置 |
| concat(s1,s2,s3...) | 字符串連接 |
| lpad(str,8,'*') | 左側(cè)填充* |
-- fname和lname首字母相同
select id,fname,lname,sal,dept_id from emps where left(fname,1)=left(lname,1);
--
select id,fname,lname,sal,dept_id from emps where substring(fname,1,1)=substring(lname,1,1);
-- fname和lname連接起來,并對其中間空格
select concat(lpad(fname,20,' '),' ',lname);
數(shù)字函數(shù)
| 函數(shù) | 相關(guān)描述 |
|---|---|
| ceil(數(shù)字) | 向上取整到個位 |
| floor(數(shù)字) | 向下取整到個位 |
| round(數(shù)字,2) | 四舍五入到小數(shù)點2位 |
| truncate(數(shù)字,2) | 舍棄到小數(shù)點2位 |
| rand() | 隨機數(shù)[0,1) |
-- 工資上漲11，31%,向上取整到10位
select id,fname,sal,ceil(sal*1.1131/10)*10 from emps;
-- 所有員工隨機排序
select id,fname,sal,dept_id from emps order by rand();
日期函數(shù)
| 函數(shù) | 相關(guān)描述 |
|---|---|
| now() | 當(dāng)前日期時間 |
| curdate() | 當(dāng)前日期 |
| curtime() | 當(dāng)前時間 |
| extract(字段 from 日期) | 抽取指定字段的值 |
| date_add(日期, interval 字段 值) | 在指定字段上加一個值 |
| datediff(日期1,日期2) | 兩個日期之間相差的天數(shù) |
-- 查詢系統(tǒng)當(dāng)前時間
select now();
-- 1997年入職員工
select id,fname,hdate from emps where extract(year from hdate)=1997;
-- 員工已入職多少年,并按入職時長升序排序
select id,fname,hdate,dateiff(now(),hdate)/365 y from emps order by y;
null值函數(shù)
ifnull(a,b)
a不是null返回a,a是null返回b
-- 所有員工年收入降序排序（年薪*提成,部分部門無提成項）
select id,fname,sal,sal*12*(1+ifnull(com_pct,0)) t from emps order by t desc;
多行函數(shù),聚合函數(shù)
| 函數(shù) | 相關(guān)描述 |
|---|---|
| sum() | 和 |
| avg() | 平均 |
| max() | 最大 |
| min() | 最小 |
| count() | 行數(shù) |
- 多行函數(shù)不能和其他普通字段一起查詢
- 多個多行函數(shù)可以一起查詢
- ==多行函數(shù)會忽略null值==
- count(*) 記行數(shù)
- count(distinct a) 去除重復(fù)再計數(shù)
-- 最低工資值
select min(sal) from emps;
8.group by
- 按指定字段中相同的值進行分組
- 分組后分別求多行函數(shù)
- 分組字段，可以查詢
- group by a （按a字段相同值分組）
- group by a,b（按a,b組合的相同值分組）
-- 每個部門的平均工資
select dept_id,avg(sal) from emps where dept_id is not null group by dept_id;
-- 每個工作崗位job_id的人數(shù)
select job_id,count(*) from emps group by job_id;
