MySQL语法

sql(structure query language)结构化查询语言
ansi iso/iec协会制订
ddl(data definition language) 数据定义语言
dml(manipulation) 数据操纵语言
dcl(control) 数据控制语言

select 从数据库中找寻音讯(select … as …)
distinct 关键字去除所有重复的行
select distinct depart from staff;

limit 限制重临的行数
select name from staff limit 3;
select * from emp order by ID limit 3,4;
order by desc, order by asc

全然限定表名:在其它数据库中查询某个表
select staff.name from enterprise.staff;

select user();
select user,host,password from mysql.user;
select database();查看当前的数据库
select bin(f1) from t4;
select last_insert_id();
select length(f2) from t1;
select concat(f1, ‘+’), concat(f2, ‘+’) from t1;连接
select concat(‘ab’,’cd’);
select f1 – f2 from t2;
select count(1) from emp
select * from emp order by ID(desc);
select user,host,password from mysql.user;
select * from cj where (class,math)=(select distinct class,math from cj
where name=’xxx’);
sqlite3 /home/wusl/medispan.sqlite “select NDC,DDI, count(distinct NDC)
from medndc where Change_Date >= ‘20140101’ group by NDC” >ndc
sqlite3 /home/wusl/medispan.sqlite “select
NDC,DDI,group_concat(distinct NDC) from medndc where Change_Date >=
‘20140101’ group by NDC” >ndc

order by 数据检索排序
desc:按降序排序

where 过滤数据
between and
and,or操作符
()
in
通配符过滤 like

create
表,null值, 主键、外键, auto_increment
create index
create table t2 like t1; 急忙建表

alter 修改(对表内参数操作)
添加、删除列(add,drop)
alter table t1 add (f1 date);
alter table emp add primary key (f1); 添加主键
alter table emp add constraint fk1 foreign key (deptno) references
dept(deptno) ON DELETE CASCADE ON UPDATE CASCADE(级联操作); 添加外键
alter table t2 drop age;
alter table emp drop primary key;删除主键(无自动编号)
alter table employees drop foreign key fk1;删除外键
alter table tb_name add address varchar(30) not null after phone;
在指定地方扩张字段

修改列属性(modify)
alter table t1 modify age int(4) zerofill;
alter table t1 modify f1 set(‘a’,’b’,’c’);
alter table employees modify empName varchar(16) first;

列的重命名(change)
alter table t5 change addr address;

表的重命名(rename,to)
alter table t1 rename t2;
alter table t1 to t2;

发动机转换
ALTER TABLE mytable ENGINE=innodb;

drop 删除数据库中的库、表、索引、函数、存储过程等
drop database if exists ds;删除数据库(先要到其他数据库)
drop table if exists emp;删除表
drop user ‘tom’@’%’;删除用户

insert 插入到表中
insert into emp(id, name, age) values (1, tom, 20);
insert into t1 values (now(), now());
insert into t1 select 1, 2;
insert into emp (age,sex) select age,sex from dept;
insert ignore into代表,如若中已经存在一样的记录,则忽略当前新数据;

update 更新
update t1 set ID=1.2 where ID=2;
update emp set salary = salary*1.1 where ID>=50;

delete 删除表中的数码
delete from emp where id=2;

truncate 删除表中的保有数据
truncate table sales;复位(自动编号从1先河)

select user,host,password from mysql.user;

grant 为用户授权
grant all on *.* to ‘root’@’10.1.1.135’ identified by ‘aixocm’ with
grant option;
grant select,insert on ds.* to ‘tom’@’%’ identified by ‘aixocm’;
flush privileges;刷新权限
GRANT … WITH MAX_USER_CONNECTIONS 2;
GRANT ALL PRIVILEGES ON shandong.* TO ‘demo’@’%’WITH GRANT OPTION;
GRANT REPLICATION SLAVE ON *.* TO ‘slave001’@’192.168.0.99’ IDENTIFIED
BY ‘123456’;—flush privileges;

revoke 撤销特定的权柄
revoke select on ds.* from ‘tom’@’%’;

show 查看数据库中的信息
show databases;
show create table emp; \G
show * from dept;
show engines;
show variables like ‘%..%’;
show table status like ‘%t1%’\G
show grants for ‘tom’@’%’;
show procedure status\G
SHOW COLUMNS
show grants; show grants for ‘user’@’host’
SHOW TABLE STATUS from mytest where Name=’test’;
show engine innodb status; 看看InnoDB所有的数据都早就联名到磁盘上去了
show table status from libra where engine=’innodb’ \G 查看innodb的表
show grants for root@localhost \G 查看用户的权柄
show global variables like “%genera%”;

set
set password =password(‘你的密码’);
set foreign_key_checks = 0;
set time_zone = ‘+9:00’;

set global auto_increment_offset=2;
set global auto_increment_increment=2;
set session auto_increment_increment=2;
set session auto_increment_offset=2;

flush
flush tables with read lock;
施加锁,表示把放在内存上的表统统都同步到磁盘上去,然后施加读锁
flush logs; 滚动下日志

replace
除非表有一个PRIMARY KEY或UNIQUE索引,否则,使用一个REPLACE语句没有意义
update candidate set education = replace(education,’科’,’学’) where
education like ‘%科%’;
把数量中出现”科”的都替换为”学”。

EXPLAIN
EXPLAIN table_name或EXPLAIN SELECT select_options
前者可以汲取一个表的字段结构等等,后者重倘使交给相关的一部分索引音讯

delimiter // 改变为止符
call sp1() 调用sp1函数

分组聚合
select deptno,count(1) from emp group by deptno;
select deptno,count(*) from emp group by deptno having count(*) >
1;
group
by子句要放在where子句之后。假若想取某个年龄段人数超越1的,不可以用where
count(*) > 1 ,因为聚合函数不可能放在where子句之后。要用having子句

多表查询
select a.empName, b.deptName from emp a, dept b where a.deptno =
b.deptno;

内链接,左外,右外

help contents;

活动增长復苏
alter table xxx AUTO_INCREMENT 1;

=any 等同于in
!=all 等同于 not in
Some 和any同义
All,any ,some能够利用除了=,!=之外运算符,比in强大

网站地图xml地图