sql筆記 重要提醒：每行語句結(jié)尾都要;
w3school學習網(wǎng)址http://www.w3school.com.cn/sql/index.asp
數(shù)據(jù)庫安裝我這就不介紹了，我用的是MySQL5.6版本陷猫。學習過程推薦按照w3c的教程過一遍秫舌，學習的時候多動腦子思考問題的妖，其實MySQL也不過如此。
單詞：Constraint 約束 ? primary 主要的 ? references 參考 ? alter 改變 ? duplicates 副本 ? Records 記錄 ? affected 受影響的 ? Warnings 警告 ? truncate 縮短/清空
? ? 連接 musql -u root -p
創(chuàng)建 create database mypig;
刪除 drop database mypig;
使用表 use pig;
顯示所有數(shù)據(jù)庫 show databases;
創(chuàng)建表 create table pig_tbl( ? ? pig_id int not null auto_increment, ? ? pig_name varchar(100) not null, ? ? pig_age int not null, ? ? pig_author varchar(50) not null, ? ? create_time date, ? ? primary key(pig_id) ? ? ); create table layui( ? ? id int not null auto_increment, ? ? name varchar(10) not null, ? ? primary key(id) ? ? );
刪除表 DROP TABLE layui; 插入數(shù)據(jù) insert into pig_tbl ? ? (pig_name,pig_age,pig_author,create_time) ? ? values ? ? ("parper",26,"your",NOW());
查詢數(shù)據(jù) 1.select pig_name,pig_id from pig_tbl where(pig_age>22);
2.select * from pig_tbl where pig_author="xiaoxi";
關(guān)鍵字 binary 區(qū)分大小寫 select * from pig_tbl where binary pig_author="Xiaoxi";
更新數(shù)據(jù)
update pig_tbl set pig_name="top" where pig_id =4;
取唯一的值 select distinct pig_author from pig_tbl; 用多個作為唯一條件 select distinct pig_author,pig_id,pig_name from pig_tbl;
打包作者為一組 select pig_id,pig_name,pig_author from pig_tbl group by pig_author;
AND 運算符實例 select pig_id,pig_name from pig_tbl where pig_author='xiaoxi' and pig_id=5;
按年齡排序 select pig_name,pig_age from pig_tbl where(pig_age>=24) order by pig_age;
規(guī)定返回的記錄的數(shù)目 select pig_name from pig_tbl limit 3;
模糊查詢 select * from pig_tbl where pig_name like '%o%';
模糊使用not不包括 select * from pig_tbl where pig_name like '%o%';
使用 _ 通配符 select *? from pig_tbl where pig_name like 'p_r_e_';
WHERE子句中規(guī)定多個值足陨，表中選取xx和xx的值： select *? from pig_tbl where pig_name in ('book','parper');
BETWEEN ... AND... 取兩個值之間的數(shù)據(jù)范圍 select *? from pig_tbl where pig_age between 24 and 30;
not between取兩個值之間的數(shù)據(jù)之外 select *? from pig_tbl where pig_age not between 24 and 30;
使用as 設(shè)置列別名
select pig_id as id,pig_name as name,pig_age as age? from pig_tbl where pig_age not between 24 and 30;
引用兩個表嫂粟，以id為主鍵關(guān)聯(lián) select pig_id, pig_tbl.pig_name,layui.name from pig_tbl,layui where pig_tbl.pig_id=layui.p_id;
使用別名查詢 select p.pig_id, p.pig_name,l.name from pig_tbl as p,layui as l where p.pig_id=l.p_id;
使用inner join 內(nèi)聯(lián)接，id 倒敘 select p.pig_id as id, p.pig_name,l.name from pig_tbl as p inner join layui as l where p.pig_id=l.p_id order by id;
左聯(lián)left join xxx on select p.pig_id as id, p.pig_name,l.name from pig_tbl as p left join layui as l on p.pig_id=l.p_id order by id;
union合并多條select結(jié)果集 合并兩個集合的name select name from layui union select name from layui2;
UNION ALL 列出所有的值
select name from layui union all select name from layui2;
一個表中選取數(shù)據(jù)墨缘，然后把數(shù)據(jù)插入另一個表中
create table new_pig (select pig_id,pig_name from pig_tbl where(pig_age>25));
刪庫
drop database my_db;
SQl建表時添加的約束------- NOT NULL 約束強制列不接受 NULL 值星虹。 UNIQUE 約束唯一標識數(shù)據(jù)庫表中的每條記錄零抬。
PRIMARY KEY 約束唯一標識數(shù)據(jù)庫表中的每條記錄。 ? 添加UNIQUE 約束命名,以及為多個列定義 UNIQUE 約束
create table perfire( ? ? id int(10) not null, ? ? firsetName varchar(255), ? ? lastName varchar(255), ? ? city varchar(100), ? ? address varchar(100), constraint uc_persionId unique(id,firsetName) );
添加 UNIQUE 約束
alter table layui2 add unique (id);
撤銷 UNIQUE 約束
alter table layui2 drop unique (id);
如果已有主建會報錯Multiple primary key defined宽涌，先刪除再添加 添加主鍵約束
alter table perfire add constraint primary key (id,city);
刪除主鍵約束
alter table perfire drop primary key; FOREIGN KEY 外鍵約束 --外鍵必須是另一個參考表的主鍵
create table orders( ? ? id_o int not null, ? ? order_no int not null, ? ? id_p int(10) not null, ? ? primary key (id_o), ? ? constraint fk_per foreign key (id_p) ? ? references layui2(id) ? ? );
刪除foreign key外鍵約束
alter table orders drop foreign key fk_per;
添加外鍵
alter table orders add foreign key (id_o) references layui2(id);
設(shè)置default約束
alter table layui alter name set default 'liuzhou';
撤銷default約束
alter table layui alter name drop default;
添加索引
create index p_sion on layui (p_id desc);
降序索引某個列中的值
create index p_sion_2 on layui (p_id,id);
多個用逗號隔開 撤銷索引
alter table layui drop index p_sion;
僅刪除表內(nèi)的數(shù)據(jù)
truncate table lay2;
給表添加新列 alter table layui2 add birthday date; alter table layui2 add passtest int(4) default '0';
修改列的數(shù)據(jù)類型 alter table layui2 modify column pt varchar(50) default 'beijing';
修改列的名字平夜，數(shù)據(jù)類型 alter table layui2 change column pts city varchar(66);
刪除表的某列 alter table layui2 drop column datree;
每次插入新記錄時，自動地創(chuàng)建主鍵字段的值卸亮。 在建表時給主鍵添加字段--AUTO INCREMENT CREATE TABLE Product( P_Id int NOT NULL AUTO_INCREMENT, City VARCHAR(255), PRIMARY KEY (P_Id) );
視圖是基于 SQL 語句的結(jié)果集的可視化的表 create view view_pig as select * from pig_tbl;
日期時間 函數(shù) 描述
NOW() 返回當前的日期和時間 select now()忽妒；
CURDATE() 返回當前的日期 select curdate();
CURTIME() 返回當前的時間 select CURTIME();
DATE() 提取日期或日期/時間表達式的日期部分
select date('2008-12-29 16:25:46.635');
EXTRACT() 返回日期/時間按的單獨部分 select extract(year from '2008-12-29 16:25:46.635');
DATE_ADD() 給日期添加指定的時間間隔 select date_add('2019-04-01',interval 6 year);
DATE_SUB() 從日期減去指定的時間間隔 select pig_id,pig_name,date_sub(create_time,interval 2 day) as date from pig_tbl;
DATEDIFF() 返回兩個日期之間的天數(shù) select datediff('2019-04-01','2019-04-04');
DATE_FORMAT() 用不同的格式顯示日期/時間 select date_format(now(),'%Y-%m-%d %h:%i') as format;
來個日期用法查詢集合
SELECT
now() AS now,
curtime() AS curtime,
curdate() AS curtime,
date_format(now(), '%Y-%m-%d %h:%i') AS format,
date_add('2019-04-01', INTERVAL 6 YEAR) AS date_add,
datediff('2019-04-01', '2019-04-04') AS datediff,
extract(
YEAR
FROM
'2008-12-29 16:25:46.635'
) AS extract,
date('2008-12-29 16:25:46.635') AS date,
pig_id,
pig_name,
date_sub(create_time, INTERVAL 2 DAY) AS date_sub
FROM
pig_tbl
LIMIT 1;
+---------------------+----------+------------+------------------+------------+----------+---------+------------+--------+-----------+------------+ | now? ? ? ? ? ? ? ? | curtime? | curtime? ? | format? ? ? ? ? | date_add? | datediff | extract | date? ? ? | pig_id | pig_name? | date_sub? | +---------------------+----------+------------+------------------+------------+----------+---------+------------+--------+-----------+------------+ | 2019-04-04 14:10:33 | 14:10:33 | 2019-04-04 | 2019-04-04 02:10 | 2025-04-01 |? ? ? -3 |? ? 2008 | 2008-12-29 |? ? ? 1 | pig_green | 2019-03-30 | +---------------------+----------+------------+------------------+------------+----------+---------+------------+--------+-----------+------------+ tyep參數(shù)可選值
MICROSECOND
SECOND
MINUTE
HOUR
DAY
WEEK
MONTH
QUARTER
YEAR
SECOND_MICROSECOND
MINUTE_MICROSECOND
MINUTE_SECOND
HOUR_MICROSECOND
HOUR_SECOND HOUR_MINUTE
DAY_MICROSECOND DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
選取某列中帶有 NULL 值的記錄 select * from layui2 where pt is null;
IFNULL() 函數(shù)，如果為空兼贸，可以返回0方便計算
select p_id+(id+ifnull(passtest,0)) as sum from layui2; coalesce的段直，作用是將返回傳入的參數(shù)中第一個非null的值
select p_id+(id+coalesce(count,0)) as sum from layui2; select coalesce (null,null,1);
函數(shù) avg()平均值 select avg(p_id) as avg from layui2; count() 返回指定列的數(shù)目 select count(*) from layui2;
max()返回該列最大值 select max(p_id) from layui2;
min()返回該列最小值 select min(p_id) from layui2;
sum()返回該列總數(shù) select sum(p_id) from layui2;
GROUP BY 語句--根據(jù)一個或多個列對結(jié)果集進行分組 select pig_name ,sum(pig_age),pig_author from pig_tbl group by pig_author;
查找sum()年齡大于50 的作者 select pig_name,pig_author as author,sum(pig_age) from pig_tbl group by author having sum(pig_age)>50;
UCASE 函數(shù)把字段的值轉(zhuǎn)換為大寫 select ucase(name) from layui2;
LCASE 函數(shù)把字段的值轉(zhuǎn)換為小寫。 select lcase(name) from layui2;
MID 函數(shù)用于從文本字段中提取字符 select mid(name,1,3) as c_name from layui2;
length()返回該列值得字符長度
select length(name) as len from layui2;
ROUND 函數(shù)用于把數(shù)值字段舍入為指定的小數(shù)位數(shù)溶诞。
select name, round(p_id,1) as unit from layui2;?
