一、DDL(Data Definition Language)
数据定义语言,用来定义数据库对象:库、表、列等;
- create
创建数据库
create database database-name;
创建表
create table 'user' (
'id' int(10) unsigned not null auto_increment primary key comment '用户id',
'name' varchar(20) not null comment '用户名',
'email' varchar(50) not null comment '用户邮箱',
'age' tinyint(3) unsigned not null comment '用户年龄',
'fee' decimal(10,2) not null default '0.00' comment '用户余额',
'time' timestamp not null comment '注册时间'
);
查看创建表语句
show create table table-name;
查看表结构
desc table-name;
- drop
删除数据库
drop database database-name;
删除表
drop table table-name;
- alter
添加表字段
alter table table-name add 列名 列类型;
如:在名字后面加电话号码字段
alter table user add phone char(11) after name;
修改表类型
alter table table-name modify 列名 列类型;
alter table table-name change 列名 新列名 列类型;
删除表的某一列
alter table table-name drop 列名;
修改表名
alter table table-name rename 新表名;
修改表搜索引擎
alter table table-name engine=myisam;
二、DML(Data Manipulation Language)
数据操作语言,用来定义数据库记录(数据);
- insert
表中插入数据
insert into table-name value(column1,column2,column3); //必须填写所有字段的数据
insert into table-name(column1,column2) value('value1','value2');
- update
更新表数据
update table-name set column1 = value1, column2 = value2 where (... 条件);
- delete
删除表数据
delete from table-name where (... 条件);
清空表
truncate table-name;
三、DCL(Data Control Language)
数据控制语言,用来定义访问权限和安全级别;
- 限制用户登录IP
设置root用户的登录IP
update user set host='127.0.0.1' where user='root';
update user set host='%' where user='root'; //不限制IP
修改后,刷新权限:
flush privileges;
- 创建用户
创建用户
create user user-name @ 'IP' identified by 'password';
用户授权
grant select,insert,... on database.table to user @ 'IP';
grant all on *.* to user1 @'%'; //给user1所有权限
创建用户并授权
grant select,insert on database-name.* to user1 @ '127.0.0.1' identified by 'new password';
撤消权限
revoke insert,update,... on database.table to user @ 'IP';
revoke all on *.* to user1 @ '%'; //撤消user1所有权限
修改后,刷新权限:
flush privileges;
- 查看用户权限
查看用户权限
show grants for 'user-name';
四、DQL(Data Query Language)
数据查询语言,用来查询记录(数据);
- distinct、concat、as、like
distinct
过滤掉重复的值
select distinct column1 from table-name;
concat
拼接查询结果
select concat (column1,column2) from table-name;
select concat_ws('---',column1,column2) from table-name; //设置拼接字符为'---'
as
起别名
select column1 as other-name from table-name;
like
模糊查找
select column1 from table-name where column1 like '%name%';
- 排序与聚合函数
排序
select * from table-name order by column1 asc;(默认升序,可以不加asc)
select * from table-name order by column1 desc; (降序)
聚合函数
select count(*) from table-name; //查询表的记录数
select sum(column1) from table-name; //求和
select avg(column1) from table-name; //求平均
select max(column1) from table-name; //最大值
select max(column1) from table-name; //最大值
- 分组
排序
select * from table-name group by column1;(按照column1分组)
- 连接查询
内连接
select table1.column1,table2.column1 from table1 inner join table2 where table1.column3 = table2.column3;
左连接
select table1.column1,table2.column1 from table1 left join table2 where table1.column3 = table2.column3;
右连接
select table1.column1,table2.column1 from table1 right join table2 where table1.column3 = table2.column3;
- 子查询、联合查询
子查询
select * from table-name where column1 in (select avg(column2) from table-name);
联合查询
select * from table-name where column1 = xxx union all select * from table-name where column2 = xxx;
- limit
限制条数
select * from table-name order by column1 desc limit 1;(取column1中的最后一行数据)