MySQL的基本用法和常用命令

1. 连接mysql服务器

打开命令行（开始菜单 + R ，输入cmd ，回车 ），输入：

（如果报错，可以将mysql的bin目录加入windows系统的环境变量path中或切换至mysql的bin目录）

mysql -u用户名 -p密码

或

mysql -u用户名 -p

Enter Password:*****输入密码

或

mysql -h主机名称 -u用户名 -p密码

或

mysql -h主机名称 -u用户名 -p密码 -P3306

或

mysql -u用户名 -p密码 数据库名称

或

mysql -u用户名 -p密码 -D数据库名称

连接成功之后，可以看到命令提示符 mysql>

1. 退出mysql

exit 或 \q 或 quit 或 Ctrl+C

1. 清屏

退出mysql后，输入cls，可以清屏

1. 查看mysql的字符集（假设用户名为root，密码为root）

mysql -uroot -proot

show variables like “character%”;

1. 统一mysql客户端和服务器端的字符集

需要修改my.ini配置文件。

如果是单独装的mysql服务器，找到客户端配置 [client] ，在下面添加：

default-character-set=utf8

找到服务器端配置 [mysqld] ，在下面添加：

default-character-set=utf8

如果是装的wampserver集成环境，找到客户端配置 [client] ，在下面添加：

default-character-set=utf8

找到服务器端配置 [wampmysqld] ，在下面添加：

character_set_server = utf8

保存后，重启mysql服务器和客户端。

1. 临时设置客户端的字符集

打开命令行，连接mysql，输入：

set names gbk;

关闭mysql连接后，会恢复到默认的配置文件中的字符集

1. mysql中的注释

-- 注释内容

# 注释内容

1. 查看mysql中所有的数据库名

show databases;

或

show schemas;

1. 选择数据库（假设mysql中有一个数据库的名称为blog）

use blog;

1. 查看选定的数据库中的所有数据表

SHOW TABLES;

1. 查看数据表的语法结构（假设数据库中有一张数据表的名称为blog_user）

SHOW CREATE TABLE blog_user;

1. 查看数据表的表结构

DESC blog_user;

或

DESCRIBE blog_user;

或

SHOW COLUMNS FROM blog_user;

1. SQL语句的语法

SQL语句以分号结尾，默认分隔符。

SQL语句不区分大小写。

遵循系统命令、函数、关键字需要大写。

自己起的名字、数据库名、表名、字段名称需要小写。

当自己起的名称和mysql的关键字（保留字）冲突时，可以使用反引号``，将名称括起来。

1. 创建数据库

CREATE DATABASE|SCHEMA [IF NOT EXISTS] db_name;

说明：| 表示二选一，[] 表示可选

CREATE DATABASE IF NOT EXISTS blog;

或

CREATE DATABASE IF NOT EXISTS blog DEFAULT CHARACTER SET =’utf8’;

1. 查看指定数据库的字符集

SHOW CREATE DATABASE blog;

1. 修改数据库的编码方式

ALTER DATABASE blog DEFAULT CHARACTER SET ‘utf8’;

1. 得到已打开的数据库的名称

SELECT DATABASE();

1. 删除指定的数据库

DROP DATABASE | SCHEMA [IF EXISTS] blog;

1. 创建数据表

CREATE TABLE if not exists blog_test(

id int unsigned not null auto_increment primary key comment ‘主键id’,

username char(16) not null unique default “” comment ‘用户名’,

password char(32) not null default “” comment ‘密码’

)engine=InnoDB default charset=utf8 auto_increment=1;

1. 查看MySQL中的帮助手册

?或者help

如：? tinyint 或 help tinyint

1. 字段的数据类型

整型：

tinyint -128 to 127 0 to 255

smallint -32768 to 32767 0 to 65535

mediumint -8388608 to 8388607 0 to 16777215

int -2147483648 to 2147483647 0 to 4294967295

bigint -9223372036854775808 to 9223372036854775807 0 to 18446744073709551615

浮点型：

float(M,D) M代表浮点数的总长度，D代表小数点后几位 如：float(10,2) 12345678.99

decimal(M,D) decimal(6) 等同于 decimal(6,0) numeric 和 decimal 是同一种类型

字符型：

char 定长字符型，必须指定长度 字符个数（长度） 0 到 255 如：char(32)，固定长度为32个字符。

varchar 变长字符型，必须指定长度 字符个数（长度） 如：varchar(300)，最大长度限制为300个字符。

注意：

varchar数据类型比较特殊，它所支持的字节数范围是0到65535，它的最大有效长度受限于整个数据行的最大字节数（65535个字节，所有的列共享这些字节）和所使用的字符集。存储varchar数据时，会使用1个或2个字节存储前缀，后面的字节来存储数据。如果数据的大小不大于255个字节，就使用1个字节存储前缀；反之，使用2个字节存储前缀。在utf8字符集下，一个汉字占3个字节，此时，varchar能够设置的最大长度是21844个字符。

定义char或varchar数据类型时，必须指定长度，这个长度指的是字符数。如果插入的数据长度超过指定的长度，就会自动截短为指定的长度。

文本型（变长，text同blob）：

tinytext 0 到 255 字节

text 0 到 65535 字节

mediumtext 0 到 16777215 字节

longtext 0 到 4294967295 字节

枚举型（变长）：

enum(“值1”,”值2” …) 最多列举65535个值

日期时间型：

date ‘YYYY-MM-DD’ 只包含日期 如：’2016-03-05’

time ‘HHSS’ 只包含时间 如：’08:20:03’

datetime ‘YYYY-MM-DD HHSS’ 包含日期和时间 如：’1926-03-05 08:20:03’

timestamp ‘YYYY-MM-DD HHSS’ ‘1970-01-01 00:00:00’ UTC to ‘2038-01-19 03:14:07’ UTC

注意：所有和日期时间相关的数据，都可以用整型 int 来存储，存储的是时间戳，方便计算和存储。

1. 清空数据表中的所有记录

truncate blog_user; # 如果数据表中包含主键字段，主键的自增值会恢复到初始状态1

1. 查看当前数据库的状态

\s 或 status

1. 删除数据表

drop table blog_test;

或

drop table tbl_name1,tbl_name2 …; # 删除多个表

1. 字段约束

unsigned 无符号的（也就是只能为正数），只能限制数值型。

zerofill 零填充，整型的后面的小括号中的数值表示长度，当显示长度达不到指定长度的时候，会以0在前面补齐。

not null 非空。 尽量指定非空。

default 默认值，一般配合 not null 使用。 插入值时，如果不给这个字段分配值，将使用默认值。 尽量指定默认值。

[primary] key 主键，被标志成主键的字段自动非空，而且具有唯一性。 可以快速搜索定位某条记录，一般把主键定义在无意义的字段上（如：编号）。

注意：一张数据表只能有一个主键

auto_increment 自动增长，只能标志在数值列。 被标志为自动增长的字段，必须是主键。

unique [key] 唯一性，被标志为唯一约束的字段的值不能重复，一张表中可以有多个唯一字段。

注意：被标志为自动增长的字段一定是主键，但主键不一定是自动增长。

1. 字段注释

comment 给字段添加注释，便于理解该字段。 如：comment “用户名”

1. 添加字段

alter table blog_test add email char(30) not null default ‘user@qq.com’ after id;

1. 删除字段

ALTER TABLE tbl_name DROP 字段名称;

1. 修改字段的类型、属性

ALTER TABLE tbl_name MODIFY 字段名称 新类型 新属性;

1. 修改字段的名称、类型、属性

ALTER TABLE tbl_name CHANGE 原字段名称 新字段名称 新类型 新属性;

1. 设置字段的默认值

ALTER TABLE tbl_name ALTER 字段名称 SET DEFAULT ‘值’;

1. 删除字段的默认值

ALTER TABLE tbl_name ALTER 字段名称 DROP DEFAULT;

1. 添加主键

alter table blog_test add primary key(id);

1. 删除主键 （注意：如果主键是自动增长，必须先删除自动增长，再来删除主键）

alter table blog_test modify id int;

alter table blog_test drop primary key;

1. 添加唯一索引

alter table blog_test add unique key uname(username);

uname是唯一索引的名称，如果不给名称，则索引名称默认和字段名称相同

或

alter table blog_test add unique key(username);

1. 删除唯一索引

alter table blog_test drop index uname;

或

alter table blog_test drop key uname;

1. 添加普通索引

alter table blog_test add key uname(username);

或

alter table blog_test add index uname(username);

1. 删除普通索引

alter table blog_test drop key uname;

或

alter table blog_test drop index uname;

1. 查询数据表中的所有索引

show index from blog_test;

1. 修改数据表的表名

ALTER TABLE tbl_name RENAME [TO|AS] new_tbl_name;

或

RENAME TABLE src_name TO new_tbl_name;

1. 修改自增长的值

ALTER TABLE tbl_name AUTO_INCREMENT=值;

1. 给数据表添加记录

INSERT [INTO] tbl_name VALUES(值, …);

表中的所有字段（自增字段可以不给值）都要给值，而且按照创建表时的字段顺序来写。如果字段类型非数值，则用引号括起来

INSERT [INTO] tbl_name(字段名称, …) VALUES(值, …);

给表中的某些字段添加值，没给值的字段取默认值（如果没有默认值，则为空NULL）

INSERT [INTO] tbl_name(字段名称, …) VALUES(值, …),(值, …);

1. 更新记录

UPDATE tbl_name SET 字段名称=值,字段名称=值… [WHERE 条件]

注意：如果不给条件，表中的所有记录都会被更新

1. 删除记录

DELETE FROM tbl_name [WHERE 条件]; # 注意：如果没有条件，表中的所有记录都会被删除

1. 彻底清空表中的记录

TRUNCATE [TABLE] tbl_name; # 清空表，可以将自增值恢复至初始状态。

1. 查询记录

SELECT * FROM tbl_name; # 不推荐使用select * , 因为效率较低，还不如将查询的字段都列出来。

select id,username from blog_test;

1. 取消当前的命令

\c

1. 给字段取别名

SELECT 字段名称 [AS] 别名名称 ,字段名称 [AS] 别名名称 … FROM tbl_name;

1. 给数据表取别名

SELECT 字段名称… FROM tbl_name [AS] 别名;

1. 查询记录时，附带字段的表名

select blog_test.id,blog_test.username from blog_test;

或 select b.id,b.username from blog_test b;

1. WHERE 条件（可用于删、改、查）

条件的类型：

= > >= < <= != <>

注意：由于空值null非常特殊，故这些比较运算符不能对null进行比较。

<> 代表不等于，和!=相同

如：select id,username from blog_test where id <> 2;

<=> 也是表示等于，但是，它还可以对是否等于null进行判断

如：select * from blog_test where username <=> null;

is null

如：select * from blog_test where username is null;

等价于 select * from blog_test where username <=> null;

is not null

如：select * from blog_test where username is not null;

BETWEEN … AND …

如：select * from blog_test where id between 2 and 5;

NOT BETWEEN … AND …

如：select * from blog_test where id not between 2 and 5;

IN(值1,值2, …)

如：select * from blog_test where id in(1,2,6);

NOT IN(值1,值2, …)

如：select * from blog_test where id not in(1,2,6);

LIKE 可以实现模糊查询

有以下几个匹配符：

% 代表0个1个或多个任意字符

_ 代表1个任意字符

如：select * from blog_test where username like “%ab%”;

逻辑运算符:

AND、OR

如：select * from blog_test where (username like “%ab%” or id!=3) and id<9;

注意：and 的优先级高于 or ，但我们可以使用小括号指定优先级。

1. 排序 ORDER BY （指定记录按照字段的排序规则，默认是升序排列）

select id,username,password from blog_test order by id;

等价于

select id,username,password from blog_test order by id asc;

select id,username,password from blog_test order by id desc;

还可以根据多个字段进行排序，如：

select id,username,password from blog_test order by id desc,username asc;

1. 限制记录数 LIMIT

select id,username,password from blog_test limit 3; # 只显示前3条记录

select id,username,password from blog_test limit 5,3; # 跳过前5条记录后，显示3条记录

update blog_test set password=’456789’ where id>2 limit 3;

注意：当limit用于删除或更新记录的时候，只能给一个值。

1. 分组 GROUP BY （通常与聚合函数一起使用）

select id,username,password from blog_test group by username;

username字段值相同的被分为同一个组，不同的被分为不同的组

1. 二次筛选 HAVING (二次筛选的条件，通常配合group by使用，表示对分组后的结果再进行筛选）

select id,username,password from blog_test group by username having id>3;

1. 聚合函数

count() 统计记录数

select count(id) from blog_test;

select count(username) from blog_test; # 如果username中有 null ，则不计算在内

select count(*) from blog_test; # 就算某个字段值中有 null ，也会计算在内

MIN() 查询某个字段的最小值

select min(id) from blog_test;

MAX() 查询某个字段的最大值

select max(id) from blog_test;

SUM() 求和

select sum(id) from blog_test;

GROUP_CONCAT() 显示分组详情

select username,count(id) as ids,group_concat(id) as id_list from blog_test group by username;

1. concat() 合并多个字段，将多个字段的值作为字符串连接起来。

select id,username,password from blog_test where concat(username,password) is null;

select id,username,password from blog_test where concat(username,password) like “%a%”;

select id,concat(username,”-“,password) from blog_test;

注意：null 和任何字符串相连的结果为null。

1. rand() 产生一个 0 到 1 之间的随机数

select rand();

select rand(3);

产生固定的随机数，数字相同，随机数就相同

select id,username from blog_test order by rand();

可实现随机排序，但比较影响性能，尽量少用。

1. distinct 去除重复的值

select distinct username from blog_test;

1. union 合并多个查询的结果集

select id,username from blog_test union select id,password from blog_user;

注意：多个查询的查询的列数（字段数）必须相同，合并后，会去除重复的记录数。

1. 子查询

将select语句的结果，作为其他语句（select,delete,update,insert) 的操作对象，称为子查询。子查询需要用小括号括起来。

SELECT id,username FROM blog_test WHERE id in (SELECT id FROM blog_user);

由于子查询效率较低，一般不推荐使用，而是用多表关联查询替代。

1. 多表查询

select a.id,a.username,b.age,b.sex from blog_user a,blog_profile b;

多表查询，如果没有条件，会产生笛卡尔积效果。这种数据记录基本没有意义。

select a.id,a.username,b.age,b.sex from blog_user a,blog_profile b where a.id = b.uid;

这种情况下，给了条件，就是正常的多表查询

1. 内连接 [inner] join

select a.id,a.username,b.age,b.pic from blog_user a join blog_profile b where a.id = b.uid;

select a.id,a.username,b.age,b.pic from blog_user a join blog_profile b on a.id = b.uid;

select a.id,a.username,b.age,b.pic from blog_user a inner join blog_profile b on a.id = b.uid;

select a.id,a.username,b.age,b.pic from blog_user a inner join blog_profile b where a.id = b.uid;

以上几条sql语句都是等价的，内连接（多表联表查询）时，where 可以用 on 替代。但外链接的条件，只能用on

1. 外连接 [outer] join

左外连接： left outer join （outer 可省略）

select a.id,a.username,b.age,b.pic from blog_user a left outer join blog_profile b on a.id = b.uid;

select a.id,a.username,b.age,b.pic from blog_user a left join blog_profile b on a.id = b.uid;

右外连接： right join

select a.id,a.username,b.age,b.pic from blog_user a right join blog_profile b on a.id = b.uid;

全外连接： full join

暂时不支持

join通常有下面几种类型，不同类型的join操作会影响返回的数据结果：

INNER JOIN: 内连接，多个表中都匹配到了条件，就返回匹配的记录，也就是说只返回匹配的记录。 同join，使用的频率最多。

LEFT JOIN: 左外连接，无论是否有匹配，以左表为主，返回左表中的所有记录，右表中如果没有相关的记录，查询的字段的值就为null。

RIGHT JOIN: 右外连接，无论是否有匹配，以右表为主，返回右表中的所有记录，左表中如果没有相关的记录，查询的字段的值就为null。

FULL JOIN: 全外连接，无论是否有匹配，返回左表和右表中的所有记录，如果没有相关的记录，查询的字段的值就为null。暂时不支持。

1. 外键 foreign key

外键的作用：保证记录的一致性和完整性。

注意：只有InnoDB存储引擎的表才有外键的特性。

外键字段如果没有指定索引，会自动创建索引。

如果要删除父表（如分类表）中的记录，必须先删除子表（带外键的表，如文章表）中的相应记录，否则会出错。

创建表的时候，可以给字段设置外键，如：foreign key(cId) references cms_cate(id)

由于外键的效率并不是很好，因此并不推荐使用外键，但我们要使用外键的思想来保证数据的一致性和完整性。

1. 数据表的复制

复制表结构：

create table t2 like t1; # 仅仅是复制表结构，也就是说t2表和t1表具有相同的表结构。

复制表数据

insert t2 select * from t1;

或

insert into t2(username,password) select username,password from t1;