MySQL常用命令(慢慢补全)

以下纪录了mysql常用命令，实际工作中会慢慢补全。。。留坑待填。比较乱，凑合看吧。

#创建数据库
create database mbook DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
create database recruitmentwebsite DEFAULT CHARSET utf8 COLLATE utf8_general_ci;

#sFTP上传文件 本地 -- 目标
put /home/hao/Desktop/recruitmentwebsite.sql /usr/local/java/
#下载文件
get /var/www/fuyatao/index.php  /home/fuyatao/ 


#修改数据库编码
alter database db_dorm CHARACTER SET GBK COLLATE gbk_chinese_ci;

#授权ssh用户拥有mbook数据库 远程和本地链接 的 所有权限：
grant all privileges on `mbook`.* to 'ssh'@'localhost' identified by 'ssh'; 
grant all privileges on `mbook`.* to 'ssh'@'%' identified by 'ssh';

#授予用户在所有数据库上的所有权限
grant all privileges on *.* to 'ssh'@'localhost' identified by 'ssh'; 
grant all privileges on *.* to 'ssh'@'%' identified by 'ssh';

#执行sql脚本,可以有2种方法:在命令行下(未连接数据库),输入 ：
mysql -h localhost -u root -proot < /itoffer_new.sql 

#在命令行下(已连接某个账户，且选择了use database或者创建了数据库,此时的提示符为 mysql> ),输入：
source /itoffer_new.sql 

#创建表
create table login(
id int(10) auto_increment not null primary key,
name varchar(50),
password varchar(50),
role varchar(50) DEFAULT 'False'
);

#插入数据
insert into login values('1','a','a','True');
insert into login values('2','观海先生','a','True');
insert into login values('3','衣知世','a','True');
insert into login values('4','田松石','a','False');

#修改某一条数据
update student set sex='男' where id=4;

#删除数据
delete from student where id=5;


select * from login where name=a and password=a


mysqldump -ussh -pssh --all-databases > /backup/mbook_$(date +%Y%m%d%H%M%S).sql
mysqldump -ussh -pssh --all-databases | gzip > /backup/mbook_$(date +%Y%m%d%H%M%S).sql.gz

find /backup/mbook -name $mbook"*.sql.gz" -type f -mmin +1 -exec rm -rf {} ; > /dev/null 2>&1
find /backup/mbook -name $mbook"*.sql" -type f -mmin +1 -exec rm -rf {} ; > /dev/null 2>&1


create table student(
readerId int(10) auto_increment not null primary key,
name varchar(50),
sex bit,
bron datetime,
spec varchar(50),
num int(20) DEFAULT 0,
photo varbinary(256)
);
insert into student(name,sex,bron,spec) values('温良恭',1,now(),'计算机科学与技术');
insert into student(name,sex,bron,spec) values('谢知非',1,now(),'民族音乐学');
insert into student(name,sex,bron,spec) values('季青临',1,now(),'古汉语文学专业');


create table book(
ISBN varchar(50) primary key,
bookName varchar(50),
author varchar(50),
publisher varchar(50),
price float(5,2),
cnum int(20),
snum int(20),
summary varchar(500),
photo varbinary(256)
);
#alter table book add column publisher varchar(50);

insert into book(ISBN,bookName,author,publisher,price,cnum,snum,summary) 
values('978-7-121-31698-2','Qt5开发及实例（第3版）','陆文周','电子工业出版社',99.00,10,10,'本书以Qt 5.8为平台，结合丰富的实例介绍Qt开发的基础知识。');
insert into book(ISBN,bookName,author,publisher,price,cnum,snum,summary) 
values('978-7-121-31883-2','Android实用教程','郑阿奇','电子工业出版社',45.00,10,10,'本书以Android Studio 2.x作为平台，系统介绍Android平台APP开发。');
insert into book(ISBN,bookName,author,publisher,price,cnum,snum,summary) 
values('978-7-121-26623-2','SQL Server实用教程（第4版）','郑阿奇','电子工业出版社',49.00,10,10,'本书以Microsoft SQL Server 2014中文版为平台，系统地介绍SQL Server  2014基础、实验和综合应用等内容。');
insert into book(ISBN,bookName,author,publisher,price,cnum,snum,summary) 
values('978-7-121-30634-1','Visual C++实用教程（第5版）','郑阿奇','电子工业出版社',59.00,10,10,'本书仍然采用Visual C++ 6.0（中文版）平台，在第4版的基础上进行增减、修改和完善，同时兼顾C++等级考试的内容，从而进一步方便教和学。');

create table lend(
bookId int(10) primary key,
readerId varchar(50),
ISBN varchar(50),
LTime datetime
);


create table userTable(
id int(10) auto_increment not null primary key,
username varchar(50),
password varchar(50)
);

insert into userTable (username,password) values('田松石','a24352');
insert into userTable (username,password) values('jerry','toms suger');
insert into userTable (username,password) values('a','a');
insert into userTable (username,password) values('郑阿奇','Visual');


create table xs(
id int(10) auto_increment not null primary key,
xb varchar(50),
xm varchar(50),
bir timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
update_bir timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
xsType varchar(50),
reserchResult varchar(50),
ky varchar(50)
);

insert into xs(xb,xm,xsType,ky) values('男','办的你挺挺的','bks','想考研');
insert into xs(xb,xm,xsType,ky) values('女','发如雪','bks','就是不考研');
insert into xs(xb,xm,xsType,reserchResult) values('男','昂？','yjs','荒古就是好啊');
insert into xs(xb,xm,xsType,reserchResult) values('女','不办事啊','yjs','恍惚套天下第一');


MySQL查看用户状态，root用户下：
select host,user from mysql.user;
创建用户：
CREATE USER 'jdbc'@'localhost'  IDENTIFIED BY 'jdbc'; #本地登录 
CREATE USER 'jdbc'@'%'  IDENTIFIED BY 'jdbc'; #远程登录 
更改用户密码：
set password for jdbc@localhost = password('jdbc'); 
切换到root用户创建数据库springtest：
create database springtest DEFAULT CHARSET utf8 COLLATE utf8_general_ci;
授权jdbc用户拥有springtest数据库的所有权限：
grant all privileges on `springtest`.* to 'jdbc'@'localhost' identified by 'jdbc'; 
grant all privileges on `springtest`.* to 'jdbc'@'%' identified by 'jdbc';
flush privileges; #刷新系统权限表

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `uid` tinyint(2) NOT NULL AUTO_INCREMENT,
  `uname` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `usex` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


#Java实现邮箱验证
create table `user`(
    id int(11) primary key auto_increment comment '用户id',
    username varchar(255) not null comment '用户名',
    email varchar(255) not null comment '用户邮箱',
    password varchar(255) not null comment '用户密码',
    state int(1) not null default 0 comment '用户激活状态：0表示未激活，1表示激活',
    code varchar(255) not null comment '激活码'
)engine=InnoDB default charset=utf8;
insert into user (username,password,email,state,code) values('田松石','田松石','2er5243@aomc.com',0,'a24352');