MySQLのまとめ
20562 ワード
ソース:https://blog.csdn.net/feifeiyechuan/article/details/84204378
1、データベース操作
2、データベースタイプ
3、フィールド制約
4、DDL(データ定義言語)---表の添削
(1)表の作成
(2)テーブルの削除
(3)表構造の変更
5、DML(データ操作言語)------データ添削調査
(1)追加
(2)クエリー
(3)削除
(4)修正
6、比較演算子
7、論理演算子
8.ファジイ照合と正規照合
9、並べ替え
10、重量除去
11、null、および空の文字列
12、between ... and ...
13、in
14、groupbyグループクエリー ,with rollup , coalesce------100疑問 select後に*またはパケットフィールドと一致しない場合でも実行可能であることを検出
------100文実行順序のまとめ
15、サブクエリ および ネストされたサブクエリ
16、サブクエリとdelete、update、
17、existsキーワード: サブクエリが少なくとも1行のデータ(すなわち、検出行の存在)を返すかどうかを確認し、trueまたはfalseの値を返します.
18、リンククエリは主に等値接続、内部接続、左外部接続、右外部接続、完全接続である.
18、日付関数
19、文字関数
20、数学関数
21、索引(物が多く、現在は見ないが、一般的に検索性能の向上に用いられる)
22、ビュー
1、データベース操作
create database person charset utf8; --
show DATABASES; --
drop database person; --
use person; --
2、データベースタイプ
date #-- 2014-09-18
time -- 08:42:30
datetime -- 2014-09-18 08:42:30
timestamp --
year --
tinyint -- 1byte (-128~127)
smallint -- 2byte (-32768~32767)
mediumint -- 3byte (-8388608~8388607)
int -- 4byte (-2147483648~2147483647)
bigint -- 8byte (+-9.22*10 18 )
float(m,d) -- 4byte ，m ，d
double(m,d) -- 8byte ，m ，d
decimal(m,d) -- decimal
char(n) -- ， 255
varchar(n) -- ， 65535
tinytext -- ， 255
text -- ， 65535
mediumtext -- ， 2 24 -1
longtext -- ， 2 32 -1 3、フィールド制約
not null --
unique -- ， unique key
primary key -- ，
foreign key references --
auto_increment -- ，
DEFAULT null -- :null
COMMENT ' ' -- ： ： ：
： ：
-- sid stu sid， fk_stu_score1_sid
CONSTRAINT fk_stu_score1_sid FOREIGN KEY(sid) REFERENCES stu(sid)
--
FOREIGN KEY(sid) REFERENCES stu(sid)
：
-- score1 sid stu sid， fk_sid
ALTER TABLE score1 ADD CONSTRAINT fk_sid FOREIGN KEY(sid) REFERENCES stu(sid)4、DDL(データ定義言語)---表の添削
(1)表の作成
create table t_stu(
sid int primary key auto_increment,
sname varchar(20) unique not null,
sage int not null
)auto_increment = 001;
create table if not exists t_score(
scid int primary key auto_increment,
sid int not null,
yuwen int,
shuxue int,
yingyu INT
)auto_increment=1;
--
alter table t_score add constraint stuscore_sid foreign key(sid) references t_stu(sid)
--
desc t_stu;
desc t_score;(2)テーブルの削除
--
create table gaofei(id int primary key auto_increment) --
drop table gaofei; -- ，
(3)表構造の変更
-- ========================= ===========================
create table test(
id int primary key auto_increment,
name varchar(20)
)auto_increment=1
-- ================== ====================
1、alter table rename ;
//
alter table test rename test1;
-- ================== ====================
2、alter table modify ;
//
alter table test1 modify name char(20);
-- ------- ------------
3、alter table change ;
//
//
alter table test1 change name names varchar(50);
-- ------- -------------------
4、alter table modify after /first
//
alter table test1 modify name varchar(50) after id;
-- ================== ====================
5、alter table add （ ‘ ：not null’）（ ‘first’）;
//
alter table test1 add age int(20);
6、alter table add （ ） after ；
//
alter table test1 add places VARCHAR(20) after id;
-- ================== ===================
7、alter table drop ；
//
alter table test1 drop places;5、DML(データ操作言語)------データ添削調査
(1)追加
-- t_stu
-- null
insert into t_stu values(sid,'zss',21);
insert into t_stu values(null,'l5',21);
insert into t_stu(sname,sage) values('w5',21);
insert into t_stu(sname,sage) values('z7',22),('s8',22); -- ，
select * from t_stu; -- 110 -- t_score
insert into t_score values(scid,1,11,12,13);
insert into t_score values(scid,5,11,16,63);
insert into t_score values(scid,3,11,82,73);
insert into t_score values(scid,4,11,92,99);
select * from t_score; -- 110 (2)クエリー
-- select from where
select * from t_score; -- 110
select * from t_stu; -- 110 (3)削除
--
create table gaofei(id int primary key auto_increment) --
truncate table gaofei; -- ，
delete from gaofei; -- ，
delete from gaofei where id=1; -- (4)修正
--
update t_stu set sage=90; --
update t_stu set sage = 99,sname='donghao' where sid > 5 --
6、比較演算子
-- > < >= <= = (!= <> )
select * from t_score;
select * from t_score where yingyu > 60;
select * from t_score where shuxue >= 60;
select * from t_score where shuxue < 50;
select * from t_score where shuxue <= 50;
select * from t_score where shuxue = 50;
select * from t_score where shuxue != 50;
select * from t_score where shuxue <> 50;7、論理演算子
-- and: or: not:
select * from t_score where yingyu > 60 and shuxue > 60;
select * from t_score where yingyu > 60 or shuxue >90;
select * from t_score where not yingyu = 73; -- !=
select * from t_score where not (yingyu > 60 and shuxue > 60);8.ファジイ照合と正規照合
-- like
-- ：
-- _ :
-- % : 0
-- escape ： ，
select * from t_stu where sname like '_aofe_';
select * from t_stu where sname like binary '%a%'; -- binary
select * from t_stu where sname like '%g%'; -- a
select * from t_stu where sname like 'a%'; -- a
select * from t_stu where sname like '/_%/%%' escape '/'; -- /
-- ：rlike regexp
-- . ：
-- + ：
-- * : 0
-- []： []
-- ^ ：
-- $ ：
-- {n}： n
-- []:
-- [^]:
-- a|b ： a b
select * from t_stu where sname rlike '^[gaofei]';
select * from t_stu where sname rlike 'a+';
-- select * from t_stu where sname REGEXP '\\w+'; --
select * from t_stu where sname binary REGEXP 'a+'; -- binary
-- like relike、regexp ：
-- ：
-- ， ， where binary
-- ：
-- ， ：
-- - SQL LIKE ； REGEXP 。
-- - LIKE 。REGEXP ， 。
9、並べ替え
-- order BY
-- asc （ ）
-- desc
select * from t_stu order by sid;
select * from t_stu order by sid desc;10、重量除去
-- distinct :
-- ：（1）distinct ，
-- ， 。
-- distinct ， ，
-- （2）distinct ，
select distinct sage from t_stu;
select distinct sage,sid from t_stu; -- （sage + sid） ， 11、null、および空の文字列
-- null
-- null : is null is not NULL
-- ：
-- -- ： null ， is null ， ，
select * from t_score where shuxue is NULL;
select * from t_score where shuxue = '90';
select * from t_score where shuxue != 0 and shuxue is not null;
select * from t_score; -- 110 12、between ... and ...
-- between A and B
select * from t_score where shuxue between 50 and 90;
select * from t_score where shuxue >= 50 and shuxue <= 90;
-- 13、in
-- in ： ， or
select * from t_score where shuxue in (90,82,30);
select * from t_score where shuxue = 90 or shuxue = 82 or shuxue = 30;
-- 14、groupbyグループクエリー ,with rollup , coalesce------100疑問 select後に*またはパケットフィールドと一致しない場合でも実行可能であることを検出
------100文実行順序のまとめ
-- ===============group by======================
-- group by ， by
-- ：group by ， ，
-- select 。
-- ，
--
-- having : ， where
-- ： having ， where ！！
--
--
-- where: ， ，where ！！！
-- ：
where ------> group by -----> having
select sname,count(1) from t_stu GROUP BY sname;
select count(1) from t_stu;
select *,count(1) from t_score GROUP BY shuxue;
-- ，where
-- !!!select *,count(1) from t_score where count(1)=1 GROUP BY shuxue;
-- ===================rollup====================
-- group by 。
select *,count(1) from t_score GROUP BY shuxue with ROLLUP;
-- ===================coalesce==================
-- rollup coalesce
-- coalesce(a,b,c);
： a==null, b； b==null, c； a!=null, a； a b c null ， null（ ）。
-- ：https://www.cnblogs.com/phpper/p/9384614.html
select coalesce(shuxue,' ') as ' ',count(1) from t_score GROUP BY shuxue with rollup;
15、サブクエリ および ネストされたサブクエリ
-- ================== =================
/* ?
1、 。
2、
，
=， in
：
in
not in
= !=
> < <>
1 in
*/
select * from t_stu where sid = (select sid from t_score where shuxue = 92);
select * from t_stu where sid in (select sid from t_score where shuxue = 12);
select * from t_stu where sid in (select sid from t_score where shuxue in (92,12));
select * from t_stu where sid not in (select sid from t_score where shuxue in (92,12));
-- ============== ================
/*
，
。
*/
select * from t_stu where sid in (select sid from t_score where shuxue in (select shuxue from t_score where yingyu=13))
16、サブクエリとdelete、update、
-- delete,update,
delete from t_score where sid in (select sid from t_stu where sname='gaofei');
update t_stu set sname='feifei' where sid in (select sid from t_score where shuxue = 12);17、existsキーワード: サブクエリが少なくとも1行のデータ(すなわち、検出行の存在)を返すかどうかを確認し、trueまたはfalseの値を返します.
-- =100 ，
select * from t_stu where not exists (select * from t_score where shuxue = 100);
-- ~：
select * from t_stu where sid not in (select sid from t_score where shuxue = 100);
18、リンククエリは主に等値接続、内部接続、左外部接続、右外部接続、完全接続である.
-- ==================== ========================
/*
：
select ， ，
from ，
where ， = ， 。
。
*/
select s.sid ,s.sname from t_stu s,t_score r where s.sid = r.sid and r.shuxue = 12;
select * from t_stu s,t_score r where s.sid = r.sid and r.shuxue = 12;
-- ：
select e1.* from emp e1,(select d.dname ' ',avg(e.epay) 'avg' ,e.did from dept d,emp e where d.did=e.did group by d.dname) e2
where e1.did=e2.did and e1.epay>e2.avg; -- e2, ，e1 ， 。
)
-- ====================== =======================
-- inner join...on， ， 2 ， 。
select * from t_stu s inner join t_score r on s.sid = r.sid;
-- ===================== ======================
-- left join, ， ， ， null
select * from t_stu s left join t_score r on s.sid = r.sid;
-- ===================== =======================
-- right join, ， ， ， null
select * from t_stu s right join t_score r on s.sid = r.sid;
-- ===================== =======================
-- union, select 。 ， 2 ， ， null
-- select 、 、
select * from t_stu where sid in (1,2)
union
select * from t_stu where sid in (5,6)18、日付関数
-- ==============================================================
--
select now(); --
select curdate(); --
select curtime(); --
-- ==============================================================
select date_add( ,interval );
select date_sub( ,interval );
。
select DATE_ADD(CURDATE(),INTERVAL 1 month); --
select DATE_ADD(CURDATE(),INTERVAL 1 quarter); --
select date_add(curdate(),interval 1 year); --
select date_add(curdate(),interval 1 day); --
select DATE_sub(CURDATE(),INTERVAL 1 month); --
select DATE_sub(CURDATE(),INTERVAL 1 quarter); --
select date_sub(curdate(),interval 1 year); --
select date_sub(curdate(),interval 1 day); --
select date_add(curdate(),interval 10 day); -- 10
select date_add('2018-1-1',interval 356 day); -- 2018-1-1 356
-- ==============================================================
datediff()
/*
：datediff( , )
。
*/
select DATEDIFF('2019-1-4',curdate())
select datediff(curdate(),'2019-1-5')
-- !!!select datediff(CURTIME(),'00:29:10') -- 101 ， ，
-- 110
-- ==============================================================
date()
/*
：date( )
。
*/
select date(curdate());
select date(now());
select date(curtime()); -- Null, date
select date('2018-11-18 00:34:45');
-- ==============================================================
dayofweek(date)
/* date (1= ,2= ,……7= ) */
select dayofweek(curdate());
select concat(' ：',dayofweek(now())-1) 'dayofweek';
-- ==============================================================
dayofmonth(date)
/* date ( 1 31 )*/
select DAYOFMONTH(now());
select DAYOFMONTH(CURTIME()); -- Null
-- ==============================================================
dayofyear(date)
/* date ( 1 366 )*/
select dayofyear(now());
-- ==============================================================
month(date)
/* date */
day(date)
/* date */
year(date)
/* date ( 1000 9999)*/
quarter(date)
/* date */
select month(now());
select day(now());
select year(now());
select quarter(now());
-- ==============================================================
week(date,first)
/* date (first 0,first 1 ,0 )*/
select week(now(),1);
-- ==============================================================
DATE_FORMAT(date,format)
/* format date */
select date_format(now(),'%y=%m=%d=%h=%m=%s');
-- ==============================================================
extract()
/* / ， 、 、 、 、 、 。*/
select extract(year from now())；
select extract(month from now());
select extract(day from now());
select extract(quarter from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());
-- ==============================================================
timestampdiff() ：
/*select timestampdiff( , ， );*/
select timestampdiff(day,'2017-4-9','2017-8-9');
select datediff('2017-8-9','2017-4-9');
select timestampdiff(year,'2017-4-9','2018-8-9');
-- ==============================================================
last_day() ：
select last_day('2017-7-8');
select last_day(now());
-- =========================== =============================
--
select dayofweek(curdate() - 1);
select dayofweek(curdate()) - 1; -- ， 0
--
select quarter(curdate());
--
select day(last_day(curdate()));
--
select dayofweek(date_sub(curdate(),interval day(curdate())-1 day)) - 1;
--
select date_sub(curdate(),interval dayofweek(curdate()-1)-1 day); 19、文字関数
-- ==============================================================
concat()
/* concat(str1,str2,…) concat
concat_ws(x,s1,s2,...) concat(s1,s2,...) ， x
*/
select concat('a','b','c');
select concat_ws('*','a','b','c'); --
-- ==============================================================
left（str, length）
/*
：left（ ， ）
*/
select left('abc',2);
-- ==============================================================
right (str , length)
/*
：left（ ， ）
*/
select right('abc',2);
-- ==============================================================
substring
/*
substring（str, pos） pos
substring（str, pos, length） pos length
：substring（ ， ）
substring（ ， ， ）
*/
select substring('abcdefg',2);
select substring('abcdefg',2,2);
-- ==============================================================
char_length(s)
/*
s
*/
select char_length('abcdefg');
-- ==============================================================
insert(s1,x,len,s2)
/*
s2 s1 x len
*/
select insert('abcdefg',2,2,'123'); -- 2 123
-- ==============================================================
upper(s)
/*
upper(s): s
*/
select upper('abcDEfgH');
-- ==============================================================
lower(s)
/*
lower(s): s
*/
select lower('abcDEfgH');
-- ==============================================================
trim(s) ：
/*
s
， ：
：
UPDATE tablename SET field = REPLACE(REPLACE(field, CHAR(10), ''), CHAR(13), '');
char(10):
char(13):
MySQL trim ， ， MySQL replace ，
：
--100
：
UPDATE `tran`
SET `status` = '1'
WHERE trim( trim(
BOTH '\r
'
FROM content ) ) = ' '
trim， ，
， php trim 。
*/
select trim(' abc ');
reverse(s) ：
/*
s
*/
select reverse('abcdefg');20、数学関数
abs(1) --
ceil(x) --
floor(x) --
rand() -- 0-1 ， 0 1
pi() -- （3.141593）
round(x,y) -- x y ，
truncate(x,y) -- x y （ round ）
pow(x,y) power(x,y) -- x y
sqrt(x) -- x
mod(x,y) -- x y
select sqrt(4);
select mod(5.5,3);
select mod(5,2);
select pow(2,4);
select power(2,4);21、索引(物が多く、現在は見ないが、一般的に検索性能の向上に用いられる)
/*
：create index on ( );
alter table add index ( );
*/
--
create index suoyin on t_stu(sid);
/*
： drop index on ；
*/
drop index suoyin on t_stu;
/* */
show index from tblname;
22、ビュー
(
/*
， 。
， 。
， ， 。
， ， 。
， 。
， ， 。
：
1. ， ，
2. ， ， 。
3. ， 。
。
， 。
：
order by
compute
compute by
into
*/
/*
：create view as
*/
create view view_a as select s.sid,sname,shuxue,yingyu from t_stu s,t_score r where s.sid=r.sid;
/*
：select * from ;
*/
select * from view_a;
/*
：drop view ;*/
drop view view_a;
/*
：insert into ( 1， 2，...) values ( 1， 2，....);
,
， ，
*/
insert into view_a(shuxue) values(66);
SELECT * FROM T_SCORE;
/*
,
update set 1= 1 where 2= 2;
*/
update view_a set shuxue = 100 where sid = 1;
select * from view_a;
/*
with check option
：create view as with check option;
-- ， with check option ，
-- ， where
1. update, with check option， update ，
2. delete, with check option
4. insert, with check option， insert ，
5. where ， with check option
*/
create view view_b as select s.sid,s.sname,r.shuxue,r.yingyu from t_stu s,t_score r with check option;
drop view view_b;
/*
： alter view as
*/
alter view view_b as select * from t_stu with check option;
select * from view_b;