mysql-增删改

DQL select
DML insert into  delete update
DDL create  drop alter
DCL grant revoke
TPL commit rollback


创建数据库-删除数据库
create database 'database_1' charactor set utf8;
drop database database_1;
使用数据库
use database_1 ；


创建用户,
create user 'username'@'ip地址' identified by '密码';
create user 'new_user'@'%' identified by 'new_user_password' ;
删除用户
drop user 'user_name'@'ip_addrees';
修改用户名字
rename user 'user_name'@'ip_addrees' to 'new_user_name'@'ip_addr';


修改密码（忘记密码）：
windows mysql忘记密码，
在命令行操作
net stop mysql
mysqld --skip-grant-tables
进入mysql后操作
use mysql
update user set authentication_string = password('密码') where user='root'
flush privileges;


linux mysql忘记密码
在命令行操作
#/etc/my.ini
#skip-grant-tables
#systemctl restart mysqld
进入mysql后操作
use mysql
update user set authentication_string = password('密码') where user='root'
flush privileges;
方法一：
#mysql 5.7之前
update user set password = password('new_pwd') where user='user_name';
#mysql 5.7之后
update user set authentication_string = password('new_pwd') where user='user_name'
方法二：
set password for 'wzx'@'%'=password('new_pwd');






授权
grant all privileges on *.* to 'user_name'@'ip_addrees' identified by 'user_password' with grant option;
回收权限
revoke create on *.* from 'user_name'@'ip_addrees';
刷新权限
flush privileges;


创建表
create table text(
No int not null auto_increment,
name varchar(20) not null,
age tinyint not null,
sex  tinyint not null,
primary key (No)
)ENGINE=InnoDB default CHARSET=utf8;
删除表
drop table text;


删除表数据
delete from 表名 where 删除条件；
delete from student where name="张三";
清除表数据，保存表结构
truncate table 表名;
truncate table student;


插入数据
INSERT INTO `table_name`(field1,field2,...)
values(valueA1,valueA2,...),(valueB1,valueB2,...)(...);


增加新字段
alter table `tt1` add `time` date;
修改字段的属性
alter table `tt1` change `旧字段名字` `新字段名字` 字段类型
alter table `tt1` change `time` `time` datetime;


更新数据
update `表名` set `字段名`=`值` where 更新条件;
update `tt1` set `time`=now() where  tno=2;
update `tt1` set `age`=age+1
批量替换原来的数据
update '表名' set '字段'=replace(字段,'原来的值','新的值')
update `tt1` set  `sex`=replace(sex,'man','男') 


对字段属性设置成unique，后使用insert ignore into 可避免重复数据
select*,count(*) as repetition from `tablename` group by `field` having repetition>1; 检查重复数据
distinct | group by(field)  能在select 的时候过滤掉重复数据


导入导出（备份）：
1：允许出数据权
grant file on *.* to user_name@'%'
#注意路径地址分隔符为‘/ ’
2：导出数据(    默认地址为secure_file_priv， #( show variables like '%secure%')   )
select * from table_name into outfile 'C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/new_file.txt'  
field terminated by ',' optionally enclosed by '''' lines terminated by '\n'；
#导出数据时候记得把格式也一起备份，以后导入数据的时候有用
3：导入数据（导入的时候要把格式一起复制，否则会导入会出错）
load data infile '' into table table_name 
field terminated by ',' optionally enclosed by '''' lines terminated by '\n';


######下面内容和上面内容大多一样，但用法与例子更多，方便自己查看#######
insert into `table_name`(field1,field2) values (valueA1,valueA2),(valueB1,valueB2)
insert into `taget_table` select * from `source_table`;


delete from `table_name` where condition; truncate table 'table_name'
update `table_name` set `field1`='values' where condition;
update `table_name` set `field`=replace(`field`,'old_string','new_for_old');

update `mysql` set authenication_string=password('new_pwd') where user='root'; 

也可以【set password for `username`@`ip_addr`=password('new_pwd');】

create database `database_name`;
create user `username`@`%` identified by 'new_pwd';
create table `table_name`(field property,..,primary key(),index [index_name] (column_name(length)) )engine=InnoDB default charset=utf8；
create temporary table  `table_name`(...);
create table target_table like source_table;
create table table_a (id int not null,...,) as (select * from table_b)
create index index_name on table_name(column_name(length))
create unique index index_name on table_name(column_name(length)) 不允许空值
create index tablename_columnA_columnB on tablename(columnA,columnB)
drop database `database_name`
drop user `username`@`%`;
drop table `table_name`
drop index index_name on `table_name`
alter table `table_name` drop index index_name
alter table `table_name` add `new_field` property [first | after `field`]; 
alter table `table_name` drop `field_name`;
alter table `table_name` change `old_field` `newfield` property;
alter table `table_name` modify `field` new_property;
alter table `table_name` alter field set default ֵ;
alter table `table_name` alter field drop default;
alter table `table_name` engine=myisam;
alter table `o_table_name` rename to `n_table_name`;
alter table `table_name` drop foreign key foreign_key_name;
alter table `table_name` add index index_name  (column_name);
alter table `table_name` add unique index_name  (column_name);
alter table `table_name` add primary key (column_name);
alter table `table_name` add fulltext index_name(column_name)
alter table `table_name` auto_increment = 100;


grant all privileges on *.* to `username`@`ip_addr` identified by `pwd` with grant option;
show grants for `username`@`ip_addr`
revoke privileges on *.* from `username`@`ip_addr`