MySQL 常用SQL语句

一、登录数据库

1. 命令行登录使用的主要参数：

mysql -h{host ip} -u{username} -p{password} -P{port} -S{sock}

例：
    mysql -uroot -p -h localhost -P 3306 -S /var/lib/mysql/mysql.sock 

2. 不用输入密码登录数据库
    在用户家目录新建 .my.cnf文件（修改权限为600），文件内容如下

[client]
user=root
password=xxxxxxxxx
host=127.0.0.1
default-character-set=utf8mb4

免密码登录效果

二、用户和用户权限管理

1. 修改用户密码

 ① 在数据库外修改（只有数据库的root用户能用mysqladmin）：

有密码：
    mysqladmin -uroot -p老密码 password '新密码'
无密码：
    mysqladmin -uroot password '新密码'

 ② 数据库内修改：

修改当前登录用户密码：
    set password=password('新密码')

修改其它用户密码（root用户修改mysql.user表中的密码字段）：
    MySQL 5.7以前：
        update mysql.user set password=password('新密码') where User='用户名' and Host='xxx';
    MySQL 5.7：
        update mysql.user set authentication_string=password(“新密码”) where User='xxxx' and Host='xxxx'; 
也可用以下方式
    set password for 'root'@'%' = password("123456")
刷新权限
    flush privileges；

 ③ root用户忘记密码

1. 修改配置文件/etc/my.cnf
    [msyqld]
        skip-grant-tables
2. 重启mysql服务
3. 用update的方式修改root用户密码

2. 创建用户及分配权限

 ① 创建用户不进行授权操作

create user 用户名@'主机名（IP）' identified by '密码';
    例：
        create user lcs@'localhost' identified by '123456';

 ② 创建用户并进行授权操作

grant 授予的权限 数据库.表 用户名@'主机名（IP）' identified by '密码';
    例：
        grant all on mydb.* to zhangsan@'172.16.1.1' identified by '123456';
    grant授予权限的种类：
        all：设置除了grant option之外的所有简单权限
        alter：允许使用alter table
        create：允许使用create table
        create user：允许使用create user、drop user、rename user、revoke all privilege
        create view：允许使用create view
        delete：允许使用delete
        drop：允许使用drop
        execute：允许用户运行已存储的子程序
        file：允许使用select ... into outfile 和 load data infile
        index：允许使用create index 和drop index
        insert：允许使用insert
        lock tables：允许对您拥有select权限的表使用lock tables（锁表）
        process：允许使用show full processlist
        reload：允许使用flush
        replication client：允许用户询问从属服务器或主服务器的地址
        replication slave：复制型从服务器
        select：允许使用select
        show databases：show databases显示所有数据库
        shutdown：允许使用mysqladmin shutdown
        update：允许使用update
        usage：无权限
        grant option：允许使用授予权限（只能授予本用户拥有的权限）

  FILE, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHOW DATABASES,SHUTDOWN和SUPER权限是管理性权限，只能进行全局授权（使用ON .语法）
   对于一个表，可以指定SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, GRANT OPTION, INDEX和ALTER
 ③ 查看和收回赋予用户的权限

查看用户的权限：
    show grants for 用户名@'主机名（IP）';

查看用户权限

收回授权： revoke 权限1，权限2…… on 数据库.数据表 from 用户@'IP';
    例：
        revoke create,drop,delete on *.*  from zhangsan@'localhost';

三、创建和删除数据库（表）

创建数据库
    create database test character set utf8mb4 collate utf8mb4_general_ci;
删除数据库
    drop database test;

创建数据表
    create table 表名（表结构）;
删除数据表
    drop table 表名;

示例建表语句

create table books(
    id int unsigned not null auto_increment primary key comment "自增主键",
    book_name char(30) not null comment "书名",
    author char(20) not null default 'unknow' comment "作者",
    price int(4) unsigned not null default '0' comment "价格",
    info char(60) null comment "简介"
)ENGINE=InnoDB DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_general_ci;

查看建表语句

show create table {tablename}

复制表结构

create table {new_table_name} like {exists_table};

四、修改表属性

1.修改表名

alter table {table_name} rename {new_table_name};

2. 修改列名和列属性

alter table {table_name} change {field_name} {new_field_name} {attribute};
例：
    alter table book change info infomation char(50) default "暂无简介信息" comment "简介";

3. 添加属性列

# first 是添加到第一列，after field_name 是添加到某一属性列后面
alter table {table_name} add {field_name} {attribute} [first | after field_name] ; 
例：
    alter table book add date char(20) not null default '1970-01-01 00:00:00' first;

4. 删除属性列

alter talble {table_name} drop {field_name}

5. 主键和索引相关的操作

删除主键
    alter table {table_name} drop primary key；
添加主键
    alter table {table_name} add constraint primary key ({field_name})；
添加索引
    alter table {table_name} add index {index_name} (field_name);
添加唯一限制条件索引
    alter table {table_name} add unique {unique_name} (field_name);
删除索引
    alter table {table_name} drop index emp_name;

五、MySQL 系统变量

显示MySQL的系统变量
    show variables；（比较多 ，大约200多个）
            auto_increment_increment ：表示自增变量的增长步长
        auto_increment_offset ：表示自增变量的起始增长数值
        set @@auto_increment_increment=10 ：设置自增变量的增长步长为10
        set @@auto_increment_offset=5 ：设置自增数值起始为5
        div_precision_increment ：设置进行除法操作的小数点后精确的位数

显示MySQL的状态变量：
    show status;
显示insert语句执行了多少次
    show status like 'Com_insert'
显示MySQL运行状态（写监控脚本用）
    mysql -e "show global status;"|egrep "Com_insert|Com_update|Com_select|Com_delete"