一利诺、基本命令
1富蓄、啟動服務(wù)
說明:以管理員身份運行cmd
格式:net start 服務(wù)名稱
示例:net start mysql57
#在Linux啟動和關(guān)閉mysql、MongoDB慢逾、Redis
sudo service mysql start
mysql -u root -p
sudo service mysql stop
2立倍、停止服務(wù)
說明:以管理員身份運行cmd
格式:net stop 服務(wù)名稱
示例:net stop mysql57
3灭红、連接數(shù)據(jù)
mysql -u 用戶名 -p
示例:mysql -u root -p
4、退出登錄(斷開連接)
quit或exit
5口注、查看版本
示例:select version();(連接后可以執(zhí)行)
6变擒、顯示當(dāng)前時間
示例:select now();(連接后可以執(zhí)行)
7、遠(yuǎn)程連接
格式:mysql -h ip地址 -u 用戶名 -p
輸入對方mysql密碼
二寝志、數(shù)據(jù)庫操作
創(chuàng)建數(shù)據(jù)庫
create database 數(shù)據(jù)庫名 charset=utf8;
刪除數(shù)據(jù)庫
drop database 數(shù)據(jù)庫名;
切換數(shù)據(jù)庫
use 數(shù)據(jù)庫名;
查看當(dāng)前選擇的數(shù)據(jù)庫
select database();
三娇斑、表操作
查看當(dāng)前數(shù)據(jù)庫中所有表
show tables;
查看表結(jié)構(gòu)
desc 表名;
查看建表語句:
show create table 表名 \G;
雖然 desc 命令可以查看表定義,但是其輸出的信息還是不夠全面材部,毫缆,為了查看更全面的表定義信息,有時就需要通過查看創(chuàng)建表的 SQL 語句來得到乐导。
可以看到表定義苦丁、表的 engine(存儲引擎)和 charset(字符集)等信息。
\G”選項的含義是使得記錄能夠按照字段豎著排列物臂,對于內(nèi)容比較長的記錄更易于顯示芬骄。
重命名表名
格式:rename table 原表名 to 新表名;
修改表結(jié)構(gòu)
格式:alter table 表名 add|change|drop 列名 類型;
示例:alter table newcar add isDelete bit default 0;
注意:刪除的時候不用指定類型。
使用change的時候也有不同:change緊跟著的是你要修改的字段名鹦聪,然后指定新字段名及類型:
alter table username change isDelete isDelete bit default 0;
另:使用modify(v.調(diào)整账阻,改進(jìn))可直接修改字段類型。但不能修改列名泽本。
alter table username modify isDelete bit default 0;
查詢表中的全部數(shù)據(jù)
select * from 表名;
select * from student; select后面寫表中的列名淘太,如果是*表示在結(jié)果集中顯示表中的所有列
select name, age from student; 如果要查詢多個列,之間使用逗號分隔
select name as a, age from student; 可以使用as為列起別名规丽,這個別名顯示在結(jié)果中蒲牧。
刪除表
drop table 表名;
創(chuàng)建表
#格式:
create table 表名(列及類型);
#說明
auto_increment表示自增長
primary key表示主鍵
not null表示不為空
bit 二進(jìn)制,通常來表示布爾類型
default 默認(rèn)值
if not exists 若不存在
#示例:
create table if not exists st(id int auto_increment primary key, name varchar(20) not null, age int not null, gender bit default 1, address varchar(20), isDelete bit default 0);
四赌莺、數(shù)據(jù)操作
增加數(shù)據(jù)
a冰抢、全列插入
格式:insert into 表名 values(...);
說明:主鍵列是自動增長,但是在全列插入時需要占位艘狭,通常使用0挎扰,一般第一行作為主鍵。
示例:insert into student values(0,"tom",19,1,"北京",0);
b巢音、缺省插入(插入部分列內(nèi)容)
格式:insert into 表名 (列1,列2,……) values (值1,值2,……);
示例:insert into student(name,age,address) values("lilei",19,"上海");
注意:不能為空的列和沒有設(shè)置默認(rèn)值的列也全部要輸入內(nèi)容遵倦。
c、同時插入多條數(shù)據(jù)(不能缺省數(shù)據(jù))
格式:insert into 表名 values(...),(...),……
示例:insert into student values(0,"hanmeimei",18,0,"北京",0),(0,"poi",22,1,"海南",0),(0,"wenli",20,0,"石家莊",0);
按條件查刪改
1.刪(沒有條件是全部刪除官撼,慎用
格式:delete from 表名 where 條件;
示例:delete from student where id=4;
2.改(沒有條件是全部列都修改梧躺,慎用
格式:update 表名 set 列1=值1,列2=值2,…… where 條件;
示例:update student set age=16 where id=7;
3.條件查詢
語法:select * from 表名 where 條件
示例:select * from student where id>7 and gender=0;
注意優(yōu)先級:小括號>not>比較運算符>and>or
a、比較運算符
等于 =
大于 >
小于 <
大于等于 >=
小于等于 <=
不等于 !=或<>
b傲绣、邏輯運算符
and 并且
or 或者
not 非
優(yōu)先級:
not>and>or
c掠哥、模糊查詢
語法:列名 like "_"/"%";
%表示任意多個任意字符
_表示一個任意字符
select * from student where name like "習(xí)__"; 查詢名字以習(xí)開頭的三個字的同學(xué)
select * from student where name like "%蛋" and gender != '男' and gender != '1'and age = 18;查詢名字以蛋結(jié)尾的巩踏,并且不是男生,年齡大于18的學(xué)生续搀。
d塞琼、范圍查詢
in/not in 表示在一個非連續(xù)的范圍內(nèi)
between...and... 表示在一個連續(xù)的范圍內(nèi)
示例:select * from student where id in (8,10,12);查詢編號為8、10目代、12的學(xué)生
?select * from student where id between 6 and 8;查詢編號為6到8的學(xué)生
e、空判斷
注意:null為空嗤练,""只是空字符串榛了。
判斷空: is null
判斷非空:is not null
示例:select * from student where address is null;
4.刪除重復(fù)行:
在正常查詢的列名前加distinct
語法:select distinct 列名 from 表名 where 條件;
5.聚合函數(shù)
例如:
查詢學(xué)生總數(shù)
select count(*) from student;
查詢女生的編號最大值
select max(id) from student where gender=0;
查詢所有學(xué)生的年齡和
select sum(age) from student;
查詢所有學(xué)生的年齡平均值
select avg(age) from student;
a、count() 表示計算總行數(shù)煞抬,括號中可以寫【*】或列名
?b霜大、max(列) 表示求此列的最大值
?c、min(列) 表示求此列的最小值
?d革答、sum(列) 表示求此列的和
?e战坤、avg(列) 表示求此列的平均值
6.分組
按照列名字段分組,表示此字段相同的數(shù)據(jù)會被放到一個集合中并被聚合計算.
分組后残拐,只能查詢出相同的數(shù)據(jù)列途茫,對于有差異的數(shù)據(jù)列無法顯示在結(jié)果集中,可以對分組后的數(shù)據(jù)進(jìn)行統(tǒng)計,做聚合運算.
語法:
select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,……[with rollup][having 條件查詢]
# WITH ROLLUP 是可選語法溪食,表明是否對分類聚合后的結(jié)果進(jìn)行再匯總
# HAVING 關(guān)鍵字表示對分類后的結(jié)果再進(jìn)行條件的過濾囊卜。
分組后的數(shù)據(jù)篩選:
where與having的區(qū)別:
1、where是對from后面指定的表進(jìn)行篩選错沃,屬于對原始數(shù)據(jù)的篩選
2栅组、having是對group by分類后的結(jié)果再進(jìn)行條件的過濾
select 列1,列2,聚合…… from 表名 group by 列1,列2,列3,…… having 列1,……聚合……;
例1,
要 emp 表中統(tǒng)計公司的總?cè)藬?shù):
select count(1) from emp;
在此基礎(chǔ)上枢析,要統(tǒng)計各個部門的人數(shù):
select deptno,count(1) from emp group by deptno;
更細(xì)一些玉掸,既要統(tǒng)計各部門人數(shù)琅坡,又要統(tǒng)計總?cè)藬?shù):
select deptno,count(1) from emp group by deptno with rollup;
統(tǒng)計人數(shù)大于 1 人的部門:
select deptno,count(1) from emp group by deptno having count(1)>1;
最后統(tǒng)計公司所有員工的薪水總額傀蚌、最高和最低薪水:
select * from emp;查看薪水是哪個列名
select sum(sal),max(sal),min(sal) from emp;
示例2:#將數(shù)據(jù)表按性別進(jìn)行分組亮曹,并統(tǒng)計每個性別有多少條記錄
select gender,count(*) from student group by gender;
select name,gender,count(*) from student group by gender,age;
7.排序
語法:select * from 表名 order by 列1 asc|desc咏尝,列2 asc|desc , ……;
說明:
? a市袖、將數(shù)據(jù)按照列1進(jìn)行排序田盈,如果某些列1的值相同败徊,則按照列2進(jìn)行排序
? b谴古、默認(rèn)按照從小到大的順序排序
? c智政、asc升序
? d认罩、desc降序
? f、如果只有一個排序字段续捂,則這些字段相同的記錄將會無序排列
需求:將沒有被刪除的數(shù)據(jù)按年齡排序
select * from student where isDelete=0 order by age desc;
select * from student where isDelete=0 order by age desc, id desc;
8.分頁
語法:
select * from 表名 limit start,count;
select * from student limit 3; 只寫一個則start索引默認(rèn)從0開始
?select * from student limit 3,3;表示從第四條記錄開始垦垂,顯示3條記錄
?select * from student where gender=1 limit 0,3;
表連接
表連接分為內(nèi)連接和外連接
最主要區(qū)別:
是內(nèi)連接僅選出兩張表中互相匹配的記錄
而外連接會選出其他不匹配的記錄宦搬。我們最常用的是內(nèi)連接。參考鏈接:
例如:查詢出所有雇員的名字和所在部門名稱劫拗,因為雇員名稱和部門分別存放在表 emp 和dept 中间校,因此,需要使用表連接來進(jìn)行查詢:
mysql> select ename,deptname from emp,dept where emp.deptno=dept.deptno;
- 內(nèi)連接:inner join on
- 組合兩個表中的記錄页慷,返回關(guān)聯(lián)字段相符的記錄憔足,也就是返回兩個表的交集部分。
- 外連接
- 左連接:left join on / left outer join on
- 左表的記錄將會全部表示出來酒繁,而右表只會顯示符合搜索條件的記錄滓彰。右表記錄不足的地方均為NULL。
- 右連接:right join on / right outer join on
- 左表只會顯示符合搜索條件的記錄州袒,而右表的記錄將會全部表示出來揭绑。左表記錄不足的地方均為NULL。
- 左連接:left join on / left outer join on
A_id | A_name | A_part |
---|---|---|
1 | 張三 | 經(jīng)理部 |
2 | 李四 | 秘書部 |
3 | 王二 | 設(shè)計部 |
4 | 趙五 | 運營部 |
B_id | B_name | B_part |
---|---|---|
2 | 李四 | 秘書部 |
3 | 王二 | 設(shè)計部 |
5 | 老劉 | 人事部 |
6 | 老黃 | 銷售部 |
現(xiàn)有兩表A_table 和 B_table
1郎哭、內(nèi)連接:select * from A_table inner join B_table on A_table.A_id = B_table.B_id;
A_id | A_name | A_part | B_id | B_name | B_part |
---|---|---|---|---|---|
2 | 李四 | 秘書部 | 2 | 李四 | 秘書部 |
3 | 王二 | 設(shè)計部 | 3 | 王二 | 設(shè)計部 |
2他匪、select * from A_table left join B_table on A_table.A_id = B_table.B_id;
A_id | A_name | A_part | B_id | B_name | B_part |
---|---|---|---|---|---|
2 | 李四 | 秘書部 | 2 | 李四 | 秘書部 |
3 | 王二 | 設(shè)計部 | 3 | 王二 | 設(shè)計部 |
1 | 張三 | 經(jīng)理部 | NULL | NULL | NULL |
4 | 趙五 | 運營部 | NULL | NULL | NULL |
3、select * from A_table right outer join B_table on A_table.A_id = B_table.B_id;
A_id | A_name | A_part | B_id | B_name | B_part |
---|---|---|---|---|---|
2 | 李四 | 秘書部 | 2 | 李四 | 秘書部 |
3 | 王二 | 設(shè)計部 | 3 | 王二 | 設(shè)計部 |
NULL | NULL | NULL | 5 | 老劉 | 人事部 |
NULL | NULL | NULL | 6 | 老黃 | 銷售部 |
子查詢
某些情況下夸研,當(dāng)我們查詢的時候邦蜜,需要的條件是另外一個select 語句的結(jié)果,這個時候亥至,就要用到子查詢畦徘。
關(guān)鍵字主要包括 in、not in抬闯、=井辆、!=、exists溶握、not exists 等杯缺。
《深入淺出MYSQL第二版》P49
mysql> select * from emp;
ename | hiredate | sal | deptno |
---|---|---|---|
zzx | 2000.00 | 1 | |
lisa | 4000.00 | 2 | |
bjguan | 5000.00 | 1 | |
bzshen | 4000.00 | 3 | |
dony | 2000.00 | 4 |
mysql> select * from dept;
deptno | deptname |
---|---|
1 | tech |
2 | sale |
3 | hr |
5 | fin |
例如,從 emp 表中查詢出所有部門在 dept 表中的所有記錄:
select * from emp where deptno in (select deptno from dept);
__________________________
ename hiredate sal deptno |
zzx 1 |
lisa 2 |
bjguan 1 |
bzshen 3 |
___________________________
一睡榆、如果子查詢記錄數(shù)唯一萍肆,還可以用=代替 in:
mysql> select * from emp where deptno = (select deptno from dept);
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from emp where deptno = (select deptno from dept limit 1);
__________________________
ename hiredate sal deptno |
zzx 1 |
bjguan 1 |
___________________________
二、某些情況下胀屿,子查詢可以轉(zhuǎn)化為表連接塘揣,例如:
select * from emp where deptno in (select deptno from dept);
轉(zhuǎn)換為表連接后:
mysql> select emp.* from emp ,dept where emp.deptno=dept.deptno;
__________________________
ename hiredate sal deptno |
zzx 1 |
lisa 2 |
bjguan 1 |
bzshen 3 |
___________________________
注意:表連接在很多情況下用于優(yōu)化子查詢
記錄聯(lián)合
我們經(jīng)常會碰到這樣的應(yīng)用,將兩個表的數(shù)據(jù)按照一定的查詢條件查詢出來后宿崭,將結(jié)果合并到一起顯示出來亲铡,這個時候,就需要用 union 和 union all 關(guān)鍵字來實現(xiàn)這樣的功能,具體語法如下:
SELECT * FROM t1
UNION|UNION ALL
SELECT * FROM t2
……
UNION|UNION ALL
SELECT * FROM tn;
區(qū)別:
UNION ALL 是把結(jié)果集直接合并在一起
UNION 是將UNION ALL 后的結(jié)果進(jìn)行一次 DISTINCT奖蔓,去除重復(fù)記錄后的結(jié)果赞草。
例如:將 emp 和 dept 表中的部門編號的集合顯示出來:
mysql> select deptno from emp
-> union all
-> select deptno from dept;
|dept|
——————
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 2 |
| 5 |
——————
如果希望將結(jié)果去掉重復(fù)記錄后顯示:
mysql> select deptno from emp
-> union
-> select deptno from dept;
|dept|
——————
| 1 |
| 2 |
| 4 |
| 5 |
——————