MySQL常用命令

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的结果进行筛选显示