MySQL（2-常用命令）

DDL（定义数据库、表结构）

操作数据库

创建数据库

创建数据库：create database 数据库名称;
创建数据库，判断是否存在：create database if not exists 数据库名称;
创建数据库，判断是否存在，并指定字符集：create database 数据库名称 character set 字符集;

查询数据库

查询所有数据库的名称：show databases;
查询某个数据库的字符集：show create database 数据库名称;

修改数据库

修改数据库的字符集：alter database 数据库名称 character set 字符集;

删除数据库

删除数据库：drop database 数据库名称;
判断数据库是否存在，存在则删除：drop database if exists 数据库名称;

使用数据库

查询当前正在使用的数据库名称：select database();
使用数据库：use 数据库名称;

操作表

创建表

create table 表名(
	列名1  数据类型1，
	列名2  数据类型2，
	........
	列名n  数据类型n 
);

注意：最后一列不需要加逗号

复制表

只复制表结构：create table 表名 like 被复制的表名;
复制表结构及数据：create table 表名 select * from 被复制的表名;

查询表

查询某个数据库中所有的表名称：show tables;
查询表结构：desc 表名;
查看建表语句：show create table 表名;

修改表

修改表名：alter table 表名 rename to 新表名;
修改表的字符集：alter table 表名 character set 字符集名称;
添加一列：alter table 表名 add 列名数据类型;
修改列名称和类型：

alter table 表名 change 列名新列名新数据类型;
alter table 表名 modify 列名新数据类型;

删除列：alter table 表名 drop 列名;

删除表

删除表：drop table 表名;
如果存在表，则删除：drop table if exists 表名;

约束

主键约束（primary key）

创建表时添加约束

create table 表名 (
	字段名 数据类型 primary key,
	......
);

创建表完后删除约束：alter table 表名 drop primary key;
创建表完后添加约束

alter table 表名 add primary key(字段名);
alter table 表名 modify 字段名数据类型 primary key;
alter table 表名 change 旧字段名新字段名数据类型 primary key;
alter table 表名 add constraint 约束名称 primary key(字段名);

注意

非空且唯一（等于唯一约束+非空约束），一张表只能有一个字段为主键
表需有该字段才能添加约束，有自动增长时需先删除自动增长，然后才能删除主键，否则会报错

自动增长（auto_increment）

如果某一列是数值类型的，使用auto_increment可以来实现自动增长

创建表时添加自动增长

create table 表名(
	字段名 数据类型 primary key auto_increment,
	......
);

创建表完后删除自动增长

alter table 表名 modify 字段名数据类型;
alter table 表名 change 旧字段名新字段名数据类型;

创建表完后添加自动增长

alter table 表名 modify 字段名数据类型 auto_increment;
alter table 表名 change 旧字段名新字段名数据类型 auto_increment;

注意

一张表只能有一个自增列，并且该列必须定义了约束
必须为主键才可以设置为递增，并且只能在数字类型中使用，否则会报错

非空约束（not null）

创建表时添加约束

create table 表名(
	字段名 数据类型 not null,
	.......
);

创建表完后删除约束

alter table 表名 modify 字段名数据类型;
alter table 表名 change 旧字段名新字段名数据类型;

创建表完后添加约束

alter table 表名 modify 字段名数据类型 not null;
alter table 表名 change 旧字段名新字段名数据类型 not null;

默认值约束（default）

创建表时添加约束

create table 表名(
	字段名 数据类型 default 值,
	......
);

创建表完后删除约束

alter table 表名 modify 字段名数据类型;
alter table 表名 change 旧字段名新字段名数据类型;

创建表完后添加约束

alter table 表名 modify 字段名数据类型 default 值;
alter table 表名 change 旧字段名新字段名数据类型 default 值;

唯一约束（unique）

创建表时添加约束

create table 表名(
	字段名 数据类型 unique,
	......
);

创建表完后删除约束：alter table 表名 drop index 字段名;
创建表完后添加约束

alter table 表名 add unique(字段名);
alter table 表名 add unique key(字段名);
alter table 表名 add constraint 约束名 unique(字段名);
alter table 表名 add constraint 约束名 unique key(字段名);
alter table 表名 modify 字段名数据类型 unique;
alter table 表名 change 旧字段名新字段名数据类型 unique;

注意

唯一约束可以有null值，null值只能有一个

外键约束（foreign key）

foreign key：让表与表之间产生联系，保证数据的正确性

创建表时添加约束

create table 表名(
	......,
	外键列,
	constraint 外键名称(自定义) foreign key(外键列名称) references 主表名称(主表列名称)
);

创建表完后删除约束：alter table 表名 drop foreign key 外键名称;
创建表完后添加约束：alter table 表名 add constraint 外键名称(自定义) foreign key (外键列名称) references 主表名称(主表列名称);
级联操作：alter table 表名 add constraint 外键名称 foreign key (外键字段名称) references 主表名称(主表列名称) on update cascade on delete cascade;

级联更新：on update cascade
级联删除：on delete cascade

注意

从表的外键关联的必须是主表的主键，且主键和外键的类型必须一致

索引

创建索引

创建表的时候创建索引

#唯一索引
CREATE TABLE t1(
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  UNIQUE INDEX UniqIdx(id)
);
#普通索引
CREATE TABLE t1(
  id INT NOT NULL,
  name CHAR(30) NOT NULL,
  INDEX UniqIdx(id)
);
#全文索引
CREATE TABLE t1(
  id INT NOT NULL,
  name TEXT NOT NULL,
  FULLTEXT INDEX UniqIdx(id)
);
#多列索引
CREATE TABLE t1(
  id INT NOT NULL,
  name TEXT NOT NULL,
  key INDEX UniqIdx(id,name)
);

在已存在的表上建立索引

#在已存在的表上建立索引
ALTER TABLE mytable ADD UNIQUE INDEX UniqIdx(id);
ALTER TABLE mytable ADD INDEX index_name(name(10),city,age);
ALTER TABLE mytable ADD FULLTEXT INDEX UniqIdx(id);
ALTER TABLE mytable ADD INDEX UniqIdx(id);
#组合索引，相当于分别建立了下面三组索引：
username,city,age
username,city
username


#只能增加普通索引INDEX和UNIQUE INDEX索引这两种，不能创建PRIMARY KEY索引
CREATE INDEX index_name ON mytable(username);
CREATE UNIQUE INDEX index_name ON mytable(username);
CREATE FULLTEXT INDEX index_name ON mytable(username);
CREATE INDEX index_name ON mytable(username,password);

查看索引

查看表的索引：show index from 表名;
指定数据库查看表的索引：show index from 表名 from 数据库名;

删除索引

删除表的索引：drop index 索引名 on 表名;
删除表的索引：alter table 表名 drop index 索引名;

DML（增删改表中数据）

添加数据

指定列名添加数据：insert into 表名(列名1,列名2,…列名n) values (值1,值2,…值n);
给所有列添加值：insert into 表名 values (值1, 值2,…值n);
从表2查询向表1添加数据：insert into 表名1(列名1,列名2,…列名n) select 字段1,字段2,…字段n from 表名2;

注意

列名和值要一一对应
除了数字类型，其他类型需要使用引号（单双引号）都可以

删除数据

删除符合条件的数据：delete from 表名 where 条件;
删除所有数据：delete from 表名;
删除表结构和数据：truncate table 表名;（不可以加条件语句）

修改数据

更新符合条件的数据：update 表名 set 列名1=值1,列名2=值2, … where 条件;
更新所有数据：update 表名 set 列名1=值1,列名2=值2, …

DQL（查询表中的数据）

语法结构

select 字段列表
from 表名列表
where 条件列表
group by 分组列表
having 分组之后的条件
order by 排序方式
limit 分页限定

注意：执行顺序如下

from
where
group by
having
select
order by
limit

基础查询

多字段查询

查询指定字段数据：select 字段名1,字段名2 … from 表名;
查询所有数据：select * from 表名;

去除重复值（distinct）

select distinct 字段名1,字段名2 … from 表名;
select distinct 字段名1,distinct 字段名2 … from 表名;

计算列（ifnull）

select 字段名1,字段名2 … 字段名1+字段名2 from 表名;（一般只会进行数值型的计算）

注意

ifnull(表达式1, 表达式2)：null参与的运算，计算结果都为null，所以要使用ifnull

表达式1：哪个字段需要判断是否为null
表达式2：如果该字段为null后的替换值

起别名（as）

select 字段名1 as 别名, 字段名2 … from 表名 as 别名;（注意：as也可以省略）
select 字段名1 别名, 字段名2 … from 表名别名;

条件查询

运算符：

>、<、<=、>=、=、<>表示不等于
BETWEEN…AND
LIKE：模糊查询（占位符如下）

_：单个任意字符
%：任意字符
[]：用来指定一个字符集，它必须匹配指定位置（通配符的位置）的一个字符，可以用前缀字符^来否定

IS NULL
and或&&
or或||
not或！
IN( 集合 )

排序查询

order by 字段1 排序方式1, …字段n 排序方式n

排序方式：

ASC：升序，默认的
DESC：降序

聚合函数

count（计算个数）
max（计算最大值）
min（计算最小值）
sum（计算和）
avg（计算平均值）

注意：聚合函数的计算，要排除null值时，可以使用下面解决方法

选择不包含非空的列进行计算
IFNULL函数

分组查询

group by 字段1,字段2…

注意：

分组之后查询的字段必须是：分组字段、聚合函数
where和having的区别：

where在分组之前进行限定，如果不满足条件，则不参与分组
where后不可以跟聚合函数
having在分组之后进行限定，如果不满足条件，则不会被查询出来
having可以进行聚合函数的判断

分页查询

limit 开始的索引,每页查询的条数

公式：开始的索引=（当前的页码-1）* 每页显示的条数

多表查询

自然连接

select … from 表名1 natural join 表名2

注意：自然连接是一种特殊的等值连接，他要求两个关系表中进行连接的必须是相同的属性列（名字相同），无须添加连接条件，并且在结果中消除重复的属性列

内连接查询

隐式内连接（使用where条件消除无用数据）

1
2
3

select 别名.字段名,.....
from 表名 别名,......
where 别名.字段名=别名.字段名,.....;

显式内连接

select 字段列表 from 表名1 inner(可选) join 表名2 on 条件;

外连接查询

全外连接（看两边表）

select 字段列表 from 表名1 full outer(可选) join 表2 on 条件;

左外连接（看左边表）：查询的是左表所有数据以及其交集部分

select 字段列表 from 表名1 left outer(可选) join 表2 on 条件;

注意：若在左表的某行在右表中没有匹配的行，则在相关联的结果集行中右表的所有选择列均为空值

右外连接（看右边表）：查询的是右表所有数据以及其交集部分

select 字段列表 from 表名1 right outer(可选) join 表2 on 条件;

注意：若在右表的某行在左表中没有匹配的行，则在相关联的结果集行中左表的所有选择列均为空值

子查询

子查询的结果是单行单列的

子查询可以作为条件，使用运算符去判断，运算符：>、<、>=、<=、=

子查询的结果是多行单列的

子查询可以作为条件，使用运算符in来判断

子查询的结果是多行多列的

子查询可以作为一张虚拟表

1 2	例子：查询工资最高的员工信息 select * from emp where emp.'salary'=(select max(salary) from emp);

DCL（用户和权限管理）

用户管理

添加用户

create user ‘用户名’@‘主机名’ identified by ‘密码’;

删除用户

drop user ‘用户名’@‘主机名’;

修改用户密码

update user set password=password(‘新密码’) where user=‘用户名’;
set password for ‘用户名’@‘主机名’=password(‘新密码’);

MySQL中忘记了root用户的密码时

停止MySQL服务（需要管理员运行cmd）：net stop mysql
使用无验证方式启动MySQL服务：mysqld --skip-grant-tables
打开新的cmd窗口，直接输入mysql命令，敲回车，就可以登录成功
use mysql;
update user set password = password( ‘新密码’ ) where user = ‘root’;
关闭两个窗口；
打开任务管理器，手动结束mysqld.exe的进程
启动mysql服务
使用新密码登录

查询用户

切换到MySQL数据库：use mysql;
查询user表：select * from user;

权限管理

查询权限

show grants for ‘用户名’@‘主机名’;

授予权限

grant 权限列表 on 数据库名.表名 to ‘用户名’@‘主机名’;

1 2	例子：给张三用户授予所有权限，在任意数据库任意表上 grant all on . to 'zhangsan'@'localhost';

撤销权限

revoke 权限列表 on 数据库名.表名 from ‘用户名’@‘主机名’;