MySQL2>常用命令汇集
部分内容转自:http://www.cnblogs.com/wupeiqi/articles/5713315.html
★数据库的安装
服务端:1,初始化 --> mysqld --initialize-insecure
2,启动服务端 --> mysqld
- 把服务端变成Windows服务:
2.1 绝对路径下:mysqld --install
2.2 启动服务 --> net start MySQL
停止服务 --> net stop MySQL
会自动创建:用户名:root 密码:空
客户端:3,连接服务端 --> mysql -u root -p
4,发送指令(相关操作)
★转储SQL文件
备份数据库:
mysqldump -u root db5> db5.sql -p # 把db5备份到【命令行的当前目录】
数据表结构
mysqldump -u root -d db5 > db5.sql -p # 把db5备份到【命令行的当前目录】
数据表结构+数据
导入数据库:
# 先提前创建数据库 create database db5
mysql -uroot -p<密码> db5 < /root/db5.sql
★用户管理
创建用户 create user '用户名'@'IP地址' identified by '密码'; # '%'代指任意地址 删除用户 drop user '用户名'@'IP地址'; 修改用户 rename user '用户名'@'IP地址'; to '新用户名'@'IP地址'; 修改密码 set password for '用户名'@'IP地址' = Password('新密码') PS:用户权限相关数据保存在mysql数据库的user表中,所以也可以直接对其进行操作(不建议)
★授权管理
查看权限 show grants for '用户'@'IP地址' 授权 grant 权限 on 数据库.表 to '用户'@'IP地址' # all privileges --> 所有权限 取消权限 revoke 权限 on 数据库.表 from '用户'@'IP地址'
★操作数据库
查看数据库 show databases; 使用数据库 use <数据库>; 创建数据库 create database <数据库> default charset utf8; # 指定字符编码为utf8 删除数据库 drop database <数据库>;
★操作表
◇创建表
create table 表名( 列名 类型 是否可以为空, 列名 类型 是否可以为空 )engine=innodb default charset=utf8 # 指定引擎为innodb,支持事务;指定字符编码为utf8
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
一种特殊的唯一索引,不允许有空值; 如果主键使用单个列,则它的值必须唯一; 如果是多列,则其组合必须唯一。 关键字:primary key create table test2( id int auto_increment primary key, # 主键 username char(20), age int )engine=innodb default charset=utf8; ) 或 create table test4( nid int, num int, primary key(nid,num) # 联合主键 )
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
一个特殊的索引,只能是指定内容 关键字:constraint...foreign key...references create table color( nid int auto_increment primary key, name char(16) not null ) create table fruit( fid int auto_increment primary key, smt char(32), color_id int not null, constraint fk_cc foreign key (color_id) references color(nid) # 外键 )
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
如果为某列设置自增列,插入数据时无需设置此列,默认将自增(表中只能有一个自增列) 关键字:auto_increment create table test6( nid int auto_increment primary key, # 是主键 num int null ) 或 create table test7( nid int auto_increment, # 是索引 num int null, index(nid) ) 注意:对于自增列,必须是索引(或主键)。 设置自增的步长和起始值: - 会话级 --> 仅仅当前会话有效 show session variables like 'auto_inc%'; 查看会话级步长和起始值 set session auto_increment_increment = 2; 设置步长 show session auto_increment_offset = 10; 设置起始值 - 全局级 --> 所有会话有效 show global variables like 'auto_inc%'; 查看全局级步长和起始值 set global auto_increment_increment = 2; 设置步长 show global auto_increment_offset = 10; 设置起始值
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
null 可空 not null 不可空 ▶创建普通字段时:默认 null ▶创建主键字段时:默认 not null ▶创建自增字段时:默认 not null
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
创建列时可以指定默认值,当插入数据时如果未主动设置,则自动添加默认值 create table test8( nid int not null default 2, num int not null )
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
unique uql (num) 单列唯一 unique uql (num,xx) 联合唯一 特点:唯一约束 (可含null) create table test10( id int auto_increment primary key, username char(32), password char(32), unique uni1 (username) ) 或 create table test9( id int auto_increment primary key, username char(32), password char(32), unique uni1 (username,password) )
◇查看表
show tables;
◇删除表
drop table 表名;
◇补充
desc 表名; 看看每个字段的属性 show create table 表名 \G; 看怎么创建的 alter table 表名 auto_increment=1 设置从哪里开始自增 alter table score default character set utf8; 设置数据库的编码 alter table app01_userinfo change name name varchar(32) character set utf8; 将相应的字段设置成utf8
★操作数据
◇增
insert into test1(username,age) values('sun',19); insert into test1(username,age) values('sun',19),('ni',22); insert into test1(username,age) select username,age from test2; # 从另外一张张表的数据复制过来
◇删
delete from test2; # 清空表 truncate table t2 # 清空表 delete from test1 where age > 23; # 符合条件则删除
◇改
update test1 set username='s1' where username='sq';
◇查
select * from test2; select username from test1; select username from test1 where age < 18;
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select username as name from test1; # as select username from test1 where age < 18 and name = 'sun'; # and select id,username from test1 where age in(18,19); # in select id,username from test1 where age between 18 and 20; # between
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from test1 where username like 's%'; # 通配符%表示任意数量的字符 select * from test1 where username like 's_'; # 通配符_表示一个字符
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from test1 where age > 18 limit 2; # 只取符合条件的2条记录 select * from test1 where age > 18 limit 2,3; # 从第2条开始取后面的3条记录
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from test1 order by id desc; # 倒序(从大到小)查看记录 select * from test1 order by id asc; # 正序(从小到大)查看记录【默认】 select * from test1 order by id asc,sid desc; # 先按id从小到大排,再按sid从大到小排 select * from test1 order by id desc limit 3; # 倒序(从大到小)查看3条记录 select * from test1 where age > 18 order by id desc limit 4; # 倒序(从大到小)查看符合条件的4条记录
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select count(tid),tname from teacher group by tname; # 聚合tname 返回tname出现的次数 by tname 意思根据tname分组,只要tname重复的就合成一条 count(tid) 这里列就返回出现的次数 select max(tid),tname from teacher group by tname; # 聚合tname by tname 意思根据tname分组,只要tname重复的就合成一条 max(tid) 意思谁的tid大我就返回谁 select count(tid),tname from teacher group by tname having count(tid) > 1; # 返回出现次数大于1的 对于聚合函数结果进行二次筛选时,必须使用having 补充: 对于聚合函数结果进行二次筛选时,必须使用having, 1,但排序不用having 2,分组前可以写where条件,分组后不可以写where条件 聚合函数: count 计数 max 最大 min 最小 sum 和 avg 平均值
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from score left join student on student.sid = score.student_id left join course on course.cid = score.course_id left join teacher on teacher.tid = course.teacher_id 关键字: left join 左边全部显示 (主表全显示) right join 右边全部显示 (被连接的表全显示) inner join 没有None才显示 (有None的值不显示)
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select * from user where user.id in (子查询) select * from (子查询) select users.id,users.name,(子查询) from users
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select id,username,(case when age > 18 then 'old' else 'young' end ) as ret from test1 # 如果age大于18返回'old',否则返回'young' select id,name,(if(isnull(score),0,score)) from test2 # 如果score为空,返回0,否则返回score本身