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

一种特殊的唯一索引，不允许有空值；
    如果主键使用单个列，则它的值必须唯一；
    如果是多列，则其组合必须唯一。
关键字：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)                # 联合主键
)

主键

一个特殊的索引，只能是指定内容
关键字：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)    # 外键
        )

外键

如果为某列设置自增列，插入数据时无需设置此列，默认将自增（表中只能有一个自增列）
关键字：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;     设置起始值

自增

null          可空
not null    不可空

▶创建普通字段时：默认 null
▶创建主键字段时：默认 not null
▶创建自增字段时：默认 not null

是否为空

创建列时可以指定默认值，当插入数据时如果未主动设置，则自动添加默认值
 create table test8(
    nid int not null default 2,
    num int not null
)

默认值

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;

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

其他

select * from test1 where username like 's%';        # 通配符%表示任意数量的字符
select * from test1 where username like 's_';        # 通配符_表示一个字符

通配符

select * from test1 where age > 18 limit 2;        # 只取符合条件的2条记录
select * from test1 where age > 18 limit 2,3;    # 从第2条开始取后面的3条记录

分页limit

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条记录

排序

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        平均值

分组

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的值不显示）

连表

select * from user where user.id in (子查询)
select * from (子查询)
select users.id,users.name,(子查询) from users

临时表（子查询）

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本身

特殊条件判断