MySQL常用命令
一、用户管理
当IP地址为一段时是可使用通配符%,即'192.1638.1.%'
创建用户
create user '用户名'@'IP地址' identified by '密码';
删除用户
drop user '用户名'@'IP地址';
修改用户
remane user '用户名'@'IP地址';to '新用户名'@'IP地址';;
修改密码
set password for '用户名'@'IP地址'=password('新密码');
二、用户权限:
默认,什么都没有
show grants for '用户名'@'IP地址' #查看权限
grant 权限 on 数据库.表 to '用户名'@'IP地址' #授权
remove 权限 on 数据库.表 to '用户名'@'IP地址' #取消授权
allprivileges #除了grant外的所有权限即除了不能给其他用户设置权限外,其他所有root具有的权限它都有
select #査权限
数据库.表
test.tb1 #只允许査test数据库中的tb1表
test.* #允许査test数据库中所有表
*.* #允许查看所数据库和数据库里的所有表
select,insert #査和插入数据权限
三、SQL语句
数据库级别
show databases ; #查看当前Mysql都有哪些数据
create database 数据库名; #创建数据库
create database 数据库名 default charset utf8 collate utf8_general_ci;
use 数据库名; #使用该数据库
drop database 数据库名; #删除数据库
表级别
show tables; #查看数据库中所有表
desc 表名; #查看数据表的数据结构
create table 表名(列名 数据类型,列名 数据类型)#创建表
eg:create table td1(id int,name varchar(20));
eg:create table 表名(
列名 类型 是否可以为空
列名 类型 是否可以为空
)engine=InnoDB default charset=utf8;
a.默认值
b.是否可以为空,为空设为null,不能为空设为not null
c.自增列(一张表只能有一个,数字,必须是索引-主键)
d.主键索引:
一张表只能有一个主键,唯一不能重复,不能为null,一般情况下,自增列设置主键
唯一索引:
可以为null,一张表可以有多个唯一列
--约束
--索引,加速查找
主键:
不能为null、不能重复、一张表只有一个主键(可以多列组成主键)
#一般用法:
id int auto_increment primary key,
e、外键:
alter table 被限制表的表名 add constraint 外键名 foreign key 被限制表(被限制的列名) references 另一张表的表名(提供限制范围的列名)。
其作用为:两张表建立约束,实现一对多
f、数据类型:数值、时间和字符串
数值 :
bit 二进制
整数:
tinyint
smallint
int
bigint --上面四个的区别为各自的范围不一样
小数:
decimal --精确的,保存的是原值
float --最不精确
double --不太精确
字符串:
char(m) --定长字符串,保存时是以固定长度储存,比较占空间,查找速率快
varchar(m) --变长字符串,保存时最多可存储设定的值,查找速率慢
text --
mediumtext
longtext
二进制数据:
tinyblob,blob,mediumblob,longblob
时间:
DATE:
YYYY-MM-DD(1000-01-01/9999-12-31)
TIME:
HH:MM:SS('-838:59:59'/'838:59:59')
YEAR:
YYYY(1901/2155)
DATETIME:
YYYY-MM-DD HH:MM:SS(1000-01-01 00:00:00/9999-12-31 23:59:59)
TIMESTAMP:
YYYYMMDD HHMMSS(1970-01-01 00:00:00)
枚举和集合的区别:
枚举是单选,集合是多选
drop table 表名; #直接删除表
delete from td1; #清空表内容,自增时会接着删除前的数值增加
truncate table 表名; #清空表内容,速度快,自增回到原点
修改表:
添加列:
alter table 表名 add 列名 类型;
删除列:
alter table 表名 drop column 列名;
修改列:
alter table 表名 modify column 列名 类型; #只能改类型
alter table 表名 change 原列名 新列名 类型; #既能改列名又能改类型
添加主键:
alter table 表名 add primary key(列名);
删除主键:
alter table 表名 drop primary key;
alter table 表名 modify 列名 int,drop primary key;
添加外键:
alter table 表名 add constraint 外键名(形如:FK_从表_主表) foreign key 从表(外键列名)references 主表(主键字段);
删除外键:
alter table 表名 dropforeign key 外键名;
数据行级别:
a、增
insert into 表名(列名,列名) values('值1','值2'); #一次性增加一条数据
insert into 表名(列名,列名) values('值1','值2'),('值3','值4’);#一次性增加多条数据
insert into 表1名(列名,列名) select 列名,列名 from 表2名; #将表2中的数据插入表1
eg:insert into tb1(name,age)select class,people from tb2;#将tb2中class和people列中的数据插入到tb1中的name和age列
注意:插入数据时一定注意对应两列的数据类型要一致
insert into 表名(列名,列名) select 列名,列名 from 数据所在的表名 where 条件; #将表2中符合要求的数据插入表1
eg:insert into tb1(name,age)select class,people from tb2 where id>2; #将tb2中id大于2的class和people列中的数据插入到tb1中的name和age列
b、删
delete from 表名 where 条件; #删除满足条件的数据行
delete from 表名 where 条件1 and 条件2; #删除满足条件1和条件2的数据行
delete from 表名 where 条件1 or 条件2; #删除满足条件1或条件2的数据行
c、改
update 表名 set 列名=值; #将该表中指定列的值全部改为指定值
update 表名 set 列名=值 where 条件; #将该表中满足条件的指定列的值全部改为指定值
d、査
select * from 表名 ; #查看该表内所有数据
select 列名1,列名2 from 表名; #查看该表中列名1和列名2的数据,显示时是以列名1和列名2的顺序显示,而非建表是的顺序
select 列名1,列名2 from 表名 where 条件; #查看该表中满足条件的列名1和列名2的数据,显示时是以列名1和列名2的顺序显示,而非建表是的顺序
e、其他
1、条件
select * from 表名 where 条件;#查看该表满足条件的所有数据
select * from 表名 where 条件1 and 条件2; #查看该表同时满足条件和条件2的所有数据
select * from 表名 where 条件1 or 条件2;#查看该表满足条件或条件2的所有数据
select * from 表名 where 列名 between 值1 and 值2; #查看该表中指定列的值在值1和值2之间的所有数据
select * from 表名 where id in(1,4,5); #查看该表中id是1,4,5的所有值
select * from 表名 where id not in(1,4,5); #查看该表中id不是1,4,5的所有值
select * from 表名 where id in(select nid from 表2);#查看该表中id是表2中nid的所有值
2、通配符
select * from 表名 where user like 'sdf%'; #查看该表中user列中以sdf开头的所有值(多字符串)
select * from 表名 where user like 'sdf_'; #查看该表中user列中以sdf开头的所有值(一个字符串)
3、限制
select * from 表名 limit 5; #前5行
select * from 表名 limit 6,5; #从第6行开始的5行(不建议使用)
select * from 表名 limit 5 offset 6; #从第6行开始的5行(推荐使用)
4、排序
select * from 表名 order by 列名 asc ; #根据列从小到大排列
select * from 表名 order by 列名 desc ; #根据列从大到小排列
select * from 表名 order by 列1 desc ,列2 asc; #根据列1从大到小排列,如果相同则按列2从小到大排列
5、分组
将数据以某一列的相同值相互合并成一条数据。
select * from 表名 group by 列名; #以指定列将该表数据进行分组
使用聚合函数:
select age ,max(part),min(part),sum(part),count(part)from tb3 group by age; #以age列分组,max(part)中显示该分组中part列中的最大值,min(part)中显示该分组中part列中的最小值,sum(part)中显示该分组中又有part值相加的和,count(part)中显示该组是由几条数据合并的分组,
select age ,max(part) as a,min(part) as b from tb3 group by age; #以age列分组,a中显示该分组中part列中的最大值,b中显示该分组中part列中的最小值,
select age ,count(part) from tb3 group by age having count(part)>2; #显示出count(part)>2的以age为分组标准的数据
6、组合
select 列1 from 表1 union select 列2 from 表2; #将表2中列2的数据在表1中列1的数据后面显示,默认会进行去重操作,如不想进行去重,则在union后面加上all
7、连表
select * from 表1 ,表2 where 表1.列1=表2.列2; #将表2中的数据根据表1.列1=表2.列2匹配后对应的显示在表1的右侧,以表1为基准
select * from 表1 left join 表2 on 表1.列1=表2.列2 ;#将表2中的数据根据表1.列1=表2.列2匹配后对应的显示在表1的右侧,以表1为基准
select * from 表1 inner join 表2 on 表1.列1=表2.列2;#两张表的数据根据表1.列1=表2.列2,匹配显示后,将包含null的行清除,实际上就是将left join的结果进行筛选显示