MySQl操作整理01
- 连接数据库:
mysql -u [useename] -p
mysql -u [username] -h [host] -p
- 用户管理:
create user 'spring'@'192.168.1.1' identified by '123';
create user 'spring'@'192.168.1.%' identified by '123';
create user 'spring'@'%' identified by '123';
drop user 'spring'@'%';
rename user 'spring'@'%' to 'new_spring'@'%';
set password for 'spring'@'%' = password('ab123');
- 权限管理:
grant select,insert,update on db1.t1 to 'spring'@'%';
grant all privileges on db1.t1 to 'spring'@'%';
revoke all privileges on db1.t1 from 'spring'@'%';
- 数据库操作:
create database db1 default charset=utf8;
create database db1 default charset utf8;
drop database db1;
show databases;
–
对数据表的操作:
show tables;
create table t1(
列名 类型 null,
列名 类型 not null,
列名 类型 not null auto_increment primary key,
id int,
name char(10)
)engine=innodb default charset=utf8;
# innodb 支持事物,原子性操作 ****
# myisam
not null:是否为空
auto_increment 表示自增
primary key 主键表示约束(不能重复且不能为空);加速查找;加速查找
一个表只能有一个主键
主键可以由多列组成
create table t2(
nid int(11) not null auto_increment,
pid int(11) not null,
num int(11),
primary key(nid,pid)
)engine=innodb default charset=utf8;
create table t3(
id int auto_increment primary key,
name char(10),
id1 int,
id2 int,
constraint fk_t3_t2 foreign key(id1,id2) reference t2(nid,pid)
)engine=innodb default charset=utf8;
外键:
create table department(
id bigint auto_increment primary key,
title char(15)
)engine=innodb default charset=utf8;
create table userinfo(
uid bigint auto_incremant peimary,
name varchar(32),
department_id int,
constraint fk_user_depar foreign key (department_id) reference color(id)
)engine=innodb default charset=utf8;
清空表:
delete from t1;
truncate table t1; #清除auto_increment自增
删除表:
drop table t1;
修改表:
添加列: 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 表名 drop foreign key 外键名称
修改默认值:
alter table 表名 auto_increment=20;
查看表结构:
desc t1;
查看创建表信息:
show create table t1;
数据类型:
数据类型:
数字、字符串、时间类型
数字:
tinyint
int
bigint
float
double
decimal
字符串:
char(10) 速度快 (10)示10个字符
varchar(10) 节省空间
PS:创建数据表长列往前放
时间类型
datetime
enum
set
create table t1(
id int signed auto_increment primary key,
num decimal(10,5),
name char(10)
)engine=innodb default charset=utf8;
#拿tinyint字段来举例,unsigned后,字段的取值范围是0~255,而signed的范围是-128~127
对数据行进行操作:
insert into t1(name,age) values('autumn',18),('spring',19);
insert into t1(name,age) select name,age from tb2;
delete from t1 where id >6;
delete from t1 where id != 6;
truncate table t1; #清除auto_increment自增
update t1 set name='root' where id=5;
update t1 set name ='autumn' where id=6 and age=18;
select * from t1;
select id,name from t1;
查询操作
- 临时表:
select num,course_id from (select num,course_id from score where num >60) as B;
select * from t1;
select * from t1 where id=1;
select * from t1 where id!=2;
select * from t1 where id >2;
select * from t1 where id >=2 and name='spring';
select name,age from t1;
select * from t1 where id in (1,2,3);
select * from t1 where id not in (1,2,3);
select * from t1 where id in (select id from t2);
select * from t1 where id between 5 and 12;包括2和12
通配符:
select * from t1 where name like "a%"
select * from t1 where name like "a_"
分页:
select * from t1 limit 10;
select * from t1 limit 0,10;
select * from t1 limit 10,10;
select * from t1 limit 2 offset 4; 从第5个开始往后2个
排序:
select * from t1 order by id desc;从大到小
select * from t1 order by id asc; 从小到大
select * from t2 order by id desc, age asc;
distinct:
select DISTINCT student_id from score where num < 60
取最后5条数据
select * from t1 order by id desc limit 5;
分组:
select count(1),max(id),part_id from userinfo group by part_id;
count()
max()
min()
sum()
avg()
*******如果对聚合函数进行二次筛选时,必须使用having******
select count(1),max(id),part_id from userinfo group by part_id having count(id) > 1;
select count(id),part_id from userinfo where id >0 group by part_id having count(id) > 1;
连表操作:
select * from userinfo,department;笛卡尔基
select * from userinfo,department where userinfo.uid =department.id
select * from userinfo left join department on userinf.uid = department.id(在之前版本的myqsl中 这两种方式是有性能差别,但是在现在版本的mysql中性能是一样的,推荐用 left join 的方法)
select * from userinfo right join department on userinfo.uid = department.id
left:左边表全显示,right:右边全显示
select * from userinfo innder join department on userinfo.uid = department.id
将出现null的一行隐藏
select * from
department5
left join userinfo5 on userinfo5.part_id =department5.id
left join userinfo6 on userinfo5.part_id =department5.id
select
score.sid,
student.sid
from
score
left join student on score.student_id = student.sid
left join course on score.course_id = course.cid
left join class on student.class_id = class.cid
left join teacher on course.teacher_id=teacher.tid
select count(id) from userinfo;