Mysql基础（3）DML 、DQL、DCL

1、DML

# 1、添加数据 -- insert ;

insert into my_table(id, name, type, description)  values (111,'100','100','100');

insert into my_table  values (111,'100','100','100');

# 2、添加数据 -- 批量添加 ;

insert into my_table(id, name, type, description)  values (111,'100','100','100'),(111,'100','100','100'),(111,'100','100','100'),(111,'100','100','100');

insert into my_table  values (111,'100','100','100'),(111,'100','100','100'),(111,'100','100','100'),(111,'100','100','100');


# 3、修改数据 -- 按条件修改 ;
update my_table set my_column = 'book' where 1=1;

# 4、删除数据 -- 按条件删除 ;
delete from my_table where 1=1;

2、DQL

（1）基本查询

（2）条件查询（WHERE）

（3）聚合函数（COUNT / MAX / MIN / AVG / SUM）

（4）分组查询（GROUP BY / HAVING）

（5）排序查询（ORDER BY）

（6）分页查询（LIMIT）

#    DQL ;

#    1、查询字段 ;

select my_column_1 , my_column_2 from my_table ;

#    2、查询所有字段 -- 不建议使用*,影响查询效率而且不够直观;

select * from my_table ;

#    3、查询字段 -- 启用别名 ;

select my_column_1 as '测试字段' from my_table ;

select my_column_1 '测试字段1', my_column_2 '测试字段2' from my_table ;

#    4、查询字段 -- 过滤重复的字段数据 ;

select distinct my_column_1 from from my_table ;

#    5、条件查询 ;
#    where ;
#    >    <     >=     <=     <>    !=    =    ;
#    between ... and ...    in(...)     like 'abc_abc'    like 'abc%abc' ;
#    and    or    is null    not    &&    ||    !    ;

select host from accounts 
where host > '182.149.198.082' && host < '222.209.234.182' ;

select host from accounts where host like '%2' ;

select host from accounts where host not like '_' ;

select host from accounts where host is null ;

select host from accounts where host in ('182.149.198.82' , '222.209.234.182') ;

#    6、聚集函数 -- count / max / min / avg / sum ;

select count(host) from accounts
where host > '182.149.198.082' && host < '222.209.234.182' ;

#    7、分组查询 -- group by / having ;

select  host from accounts
where host > '182.149.198.082' && host < '222.209.234.182'
group by host ;


select  host from accounts
where host > '182.149.198.082' && host < '222.209.234.182'
having host = '182.149.198.222';


#    8、分页查询 -- order by ... desc / asc  ;

select  host,user from accounts
where host > '182.149.198.082' && host < '222.209.234.182'
order by host desc
limit 2,3;

3、DCL

（1）用户管理

#    1、查看密码策略 ;

SHOW VARIABLES LIKE 'validate_password%';

#    validate_password_policy,MEDIUM              安全等级   low/medium/strong  ;
#    validate_password_length,6                   密码长度          6       low ; 
#    validate_password_mixed_case_count,1         含有的大小写个数   1    medium ;
#    validate_password_number_count,1             含有的数字个数     1    medium ;
#    validate_password_special_char_count,1       含特殊字符个数     1    medium ;
#    validate_password_dictionary_file,""         字典文件         ''    strong ;

#    validate_password_check_user_name,OFF        用户名查重      off   ;  



#    2、新增指定主机用户 ;

create user 'my_user'@'localhost' identified by 'My_021419';

#    3、查看新用户的权限 ;

select * from mysql.user;

#    4、新增任意主机用户 ;

create user 'my_user'@'%' identified by 'My_021419';

#    5、修改用户密码 ;

alter user 'my_user'@'%'identified with mysql_native_password by 'Us_021419';

#    6、删除用户 ;

drop user 'my_user'@'%';

（2）权限管理

#    1、查询用户权限

show grants for 'my_user'@'localhost' ;

#    GRANT USAGE ON *.* TO 'my_user'@'localhost'  仅拥有最低权限 ;

#    2、授予用户指定数据库权限

grant all on student.* to 'my_user'@'localhost';

#    GRANT ALL PRIVILEGES ON `student`.* TO 'my_user'@'localhost' ;


#    3、撤销用户指定数据库权限

revoke all on student.* from 'my_user'@'localhost';

#    GRANT USAGE ON *.* TO 'my_user'@'localhost'  仅拥有最低权限 ;