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' 仅拥有最低权限 ;