mysql-削除変更-操作
8732 ワード
----》 ----》
create alter drop （ DDL）
： insert update delete
grant *.* revoke -- DCL
：database
create database character set = utf8
drop database
：table
create table （ [ ], [ ], [ ]）
: drop table
: alter table add [ ]
alter table drop
SQL ： DQL， DML， DDL， DCL。
1. DQL
DQL SELECT ，FROM ，WHERE
：
SELECT
FROM
WHERE
2 . DML
DML ：
1) ：INSERT
2) ：UPDATE
3) ：DELETE
3. DDL
DDL ----- 、 、
、 、 ：
CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
drop
Alter
DDL ！ rollback（ ）
4. DCL
DCL ，
， 。 ：
1) GRANT： 。
2) ROLLBACK [WORK] TO [SAVEPOINT]： 。
---ROLLBACK
。 ：
SQL>ROLLBACK;
3) COMMIT [WORK]：
。
ALTER TABLE tab_name ADD PRIMARY KEY (col_name) ;
sql：
alter table tab_name add primary key(id);
alter table tab_name change id id int(10) not null auto_increment;
：
alter table tab_name change id id int(10);
9.3
alter table tab_name drop primary key;
tab_name address varchar(20)
alter table tab_name add address varchar(20);
tab_name address
alter table tab_name drop address;
show tables;
auto_increment
create table ( );
：
create table students(
id int auto_increment primary key,
sname varchar(10) not null
);
alter table add|change|drop ;
：
alter table students add birthday datetime;
drop table ;
desc ;
rename table to ;
show create table ' ';
select * from
：insert into values(...)
：insert into ( 1,...) values( 1,...)
：insert into values(...),(...)...;
insert into ( 1,...) values( 1,...),( 1,...)...;
， ， 0，
：
1.
insert into 1
select * from 2
2. （ ）
insert into 1 ( 1, 2, 3)
select 1, 2, 3 from 2
update set 1= 1,... where
:
delete from where
select * from ;
from ，
select ， *
select ， as ，
，
select distinct
select distinct gender from students;
where ， true
：
select * from where ;
=
>
>=
<
<=
!= <>
3
select * from students where id>3;
4
select * from subjects where id<=4;
“ ”
select * from students where sname!=' ';
select * from students where isdelete=0;
and
or
not
3
select * from students where id>3 and gender=0;
4
select * from students where id<4 or isdelete=0;
like
%
_
select * from students where sname like ' %';
select * from students where sname like ' _';
select * from students where sname like ' %' or sname like '% %';
in
1 3 8
select * from students where id in(1,3,8);
between ... and ...
3 8
select * from students where id between 3 and 8;
3 8
select * from students where id between 3 and 8 and gender=1;
：null ''
is null
select * from students where hometown is null;
is not null
select * from students where hometown is not null;
select * from students where hometown is not null and gender=0;
，not， ，
and or ， or， ()
，
：
select * from
order by 1 asc|desc, 2 asc|desc,...
1 ， 1 ， 2 ，
asc ，
desc ，
，
select * from students
where gender=1 and isdelete=0
order by id desc;
，
select * from subject
where isdelete=0
order by stitle;
，
select * from
limit start,count
start ， count
start 0
：
： m ， n
： python
p1
p1 m p2
p2
p2+1
n
select * from students
where isdelete=0
limit (n-1)*m,m
， 5
count(*) ， ，
select count(*) from students;
max( )
select max(id) from students where gender=0;
min( )
select min(id) from students where isdelete=0;
sum( )
select sum(id) from students where gender=1;
avg( )
select avg(id) from students where isdelete=0 and gender=0;
:（ ）
ABS(x) x
BIN(x) x (OCT ，HEX )
EXP(x) e( ) x
GREATEST(x1,x2,…,xn)
LEAST(x1,x2,…,xn)
LN(x) x
LOG(x,y) x y
MOD(x,y) x/y ( )
PI() pi ( )
RAND() 0 1 , ( ) RAND() 。
FLOOR(x) x ，( )
CEILING(x) x ，( )
ROUND(x,y) x y ，( )
TRUNCATE(x,y) x y
SIGN(x) x ( 1， -1，0 0)
SQRT(x)
( GROUP BY SELECT )
—— NULL
AVG(col)
COUNT(col) NULL / ( * )
MIN(col)
MAX(col)
SUM(col)
GROUP_CONCAT(col)
ASCII(char) ASCII
BIT_LENGTH(str)
CONCAT(s1,s2…,sn) s1,s2…,sn
CONCAT_WS(sep,s1,s2…,sn) s1,s2…,sn ， sep
INSERT(str,x,y,instr) str x ，y instr，
FIND_IN_SET(str,list) list ， str， str list
LCASE(str) LOWER(str) str
UCASE(str) UPPER(str) str
LEFT(str,x) str x
RIGHT(str,x) str x
LENGTH(str) str
POSITION(substr,str) substr str
QUOTE(str) str
REPEAT(str,srchstr,rplcstr) str x
REVERSE(str) str
LTRIM(str) str
RTRIM(str) str
TRIM(str)
DATE_ADD(date,INTERVAL int keyword) date int (int ), ：SELECTDATE_ADD(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_SUB(date,INTERVAL int keyword) date int (int ), ：SELECTDATE_SUB(CURRENT_DATE,INTERVAL 6 MONTH);
DATE_FORMAT(date,fmt) fmt date
FROM_UNIXTIME(ts,fmt) fmt ， UNIX ts
MONTHNAME(date) date ( ， October)
DAYNAME(date) date ( ， Saturday)
NOW() ：2016-10-08 18:57:39
CURDATE() CURRENT_DATE()
CURTIME() CURRENT_TIME()
QUARTER(date) date (1~4)
WEEK(date) date (0~53)
DAYOFYEAR(date) date (1~366)
DAYOFMONTH(date) date (1~31)
DAYOFWEEK(date) date (1~7)
YEAR(date) date (1000~9999)
MONTH(date) date (1~12)
DAY(date) date
HOUR(time) time (0~23)
MINUTE(time) time (0~59)
SECOND(time) time (0-59)
DATE(datetime) datetime
TIME(datetime) datetime
AES_ENCRYPT(str,key) key str ， AES_ENCRYPT ， BLOB
AES_DECRYPT(str,key) key str
DECODE(str,key) key str
ENCRYPT(str,salt) UNIXcrypt() ， salt( ， ) str
ENCODE(str,key) key str， ENCODE() ， BLOB
MD5() str MD5
PASSWORD(str) str ， ， UNIX 。
SHA() str (SHA)
DATE_FORMAT(date,fmt) fmt date
FORMAT(x,y) x ，y
INET_ATON(ip) IP
INET_NTOA(num) IP
TIME_FORMAT(time,fmt) fmt time
FORMAT() ， 。
:
https://blog.csdn.net/sinat_38899493/article/details/78710482
，
， ，
，
：
select 1, 2, ... from group by 1, 2, 3...
select gender as ,count(*)
from students
group by gender;
select hometown as ,count(*)
from students
group by hometown;
：
select 1, 2, ... from
group by 1, 2, 3...
having 1,... ...
having where
select count(*)
from students
where gender=1;
-----------------------------------
：
select gender as ,count(*)
from students
group by gender
having gender=1;
where having
where from ，
having group by
select
select distinct *
from
where ....
group by ... having ...
order by ...
limit star,count
：
from
where ....
group by ...
select distinct *
having ...
order by ...
limit star,count
， ，