資料庫
建立資料庫:create database name;
檢視資料庫:show datables;
建立指定字元集的資料庫:create database name charset=utf-8;
建立資料庫前進行判斷是否存在:create database if not exists name cahrset=8;
選擇資料:use name;
檢視目前所在資料庫:select database();
檢視目前資料庫編碼格式:show variable like 'character_set_database';
修改資料庫(修改其參數):alter databale name charset =utf8;
删除資料庫:drop datable name;
引擎
三大引擎 Innodb MYSIAM(二者硬碟存儲) MEMARY(基于顯存 記憶體存儲)
檢視mysql支援的引擎:show engines;
檢視預設引擎:show variables like 'default_storang_engines'
修改預設存儲引擎:
表
建立表:create table if not exists table(id int not null auto_increment,age int not null default 0,primary key(id));
檢視表:show tables;
檢視表詳情資訊(具體的表):show create table name;
産看表結構 全部:DESC name;
産看表結構 列:DESC name age;
更新表(能更新不推薦):alter table;
添加新字段:alter table name add(id,int);
修改字段名:alter table name change name name1 int;
删除字段名:alter table name drop age;
修改表名: alter table bm name as bm1;
重命名表: alter table name bm to bm1;
删除表: drop table name;
複制表(注意順序):create table name like name1;
表内容操作
增
插入完整行 完全插入:insert into name values(id,age);
查詢插入行:select * from name;
不完全插入:insert into name(age,num) values(18,88)
多行插入:insert into name(age,num) values(77,66),(33,11)
一個表插入到另一個表:insert into name (age,num) select age,num from name1 ;
删除
删除指定一行:delete from name where id=11;
删除所有:delete from name;
改
改單列:update name set age=22 where id =1;
改多列:update name set age=22,name=''xx where id =3;
删除某個列的值,可以設定這個列的值為空:
update name set age=null where id =5;
MYSQL檢索資料
檢索單列:select age from name;
檢索多列:select age,num from name;
檢索所有列:select * from name;
檢索不同的行,去重:select distinct(age) from name;
使用完全限定的表名(同時使用表名和列名):select name.age from name;
完全限定名使用場景:如果隻在自己内部使用,那麼完全限定名基本上是不用的,這種情況太簡單了。那麼什麼時候去使用呢?比如村裡有兩家分别叫王a和王b,并且兩家都有個孩子叫王小三,這兩個王小三并且在一個班級學習上課,那麼老師叫王小三,這兩個孩子就不知道叫誰了。說王a家王小三,那麼王a家王小三就會知道是他。是以當在單表内查詢時一般不用完全限定名,隻有多個表涉及到相同的列名時,才會使用到完全限定名。比如使用者表有個列叫name,商品表有個字段叫name,當查詢時隻使用name,那麼資料庫是不知道叫使用者的name,還是商品的name,此時完全限定名就可以展現出來。使用完全限定名的作用就是防止歧義的發生!
= 等于:select * from name where age=10;
<> 不等于:select * from name where age<>10;
!= 不等于:select * from name where age!=10;
< 小于 (一般情況下後面會跟數字):select * from name where age<10;
<= 小于等于:select * from name where age<=10;
> 大于:select * from name where age>10;
>= 大于等于:select * from name where age>=10;
BETWEEN .. AND ..在指定的兩個值之間:select * from name where age between 10 and 20;
空值檢查(IS NULL):select * from name where age is null;
組合WHERE子句
AND:select * from name age=10 and num=1;
OR:select * from name where age=10 or num=2;
IN:select * from name where age in(10,20);
NOT:select * from name where age not in (10,20);
通配符:
%:select * from bm where name like '李%';
_:select name from bm where name like '__';
mysql正則:select * from bm where name regexp '^李'
排序
單列
升序:select age from bm order by age asc;
降序:select age from bm order by age desc;
多列
升序:select age,num from bm order by age asc,num asc;
降序:select age,num from bm order by age desc,num desc;
條件排序:
限制查詢
指定傳回行數:select age from bm limit 1;
指定從幾行起,傳回幾行:select age from bm limit 1,5;
order by和limit的組合使用
取最小值:select age from bm order by age asc limit 1;
取最大值:select age from bm order by age desc limit 1;
聚合函數
傳回某列平均值:avg
傳回某列行數:count
傳回某列最大值:max
傳回某列最小值:min
傳回某列之和:sum
聚集不同值:select sum(disdinct age) from bm ;
組合聚集函數:select sun(age),min(nun) from bm;
分組
分組(單獨用無意義)+聚合函數:select count(*)age from bm group by age ;
group by + group_concat():select count(*)age,group_concat(num) from bm group by age ;
增加顯示字段分組結果
group by + group_concat+聚合函數:select count(*)age,group_concat(num) from bm group by age
group by + group_with rollup:
select count(*)age from bm group by age with rollup;
HAVING
組合
分組和排序:select count(*)age from bm where age>1 group by age having age>2 order by age desc;
分組和排序、限定查詢:select count(*)age from bm where age>1 group by age having age>1 order by age desc limit 2;
![](https://img.laitimes.com/img/9ZDMuAjOiMmIsIjOiQnIsICM0ITMvw1dvwlMvwlM3VWaWV2Zh1WaDdTJwlmc0N3LcRnbllmcv1yb0VXYvwlMyd2bNV2Zh1Wa-cmbw5iNzEjZzcTNiFDMhBzMkJDZtYDMzYzN1ETMvw1cldWYtl2XkF2bsBXdvw1bp5SdoNnbhlmauMXZnFWbp1CZh9GbwV3Lc9CX6MHc0RHaiojIsJye.png)