MySQl操作整理01

MySQl操作整理01

• 连接数据库：

mysql -u [useename] -pmysql -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;