一. 基本命令

1. 启动服务

windows: net start mysql

linux: service mysqld start

mac: /usr/local/mysql/support-files/mysql.server start (gz解压包方式安装,路径按照解压安装时的目录查找)

brew services start mysql (brew install mysql方式安装启动方式)

为了方便操作,可以自定义启动命令,修改~/.bash_profile文件,添加以下内容:

# mysql快捷命令

alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start' # 启动服务

alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop' # 停止服务

alias mysqlstatus='sudo /usr/local/mysql/support-files/mysql.server status' # 查看状态

alias mysqlrestart='sudo /usr/local/mysql/support-files/mysql.server restart' # 重启服务

添加完成后,可以直接执行mysqlstart,mysqlstop命令启动停止服务.

2. 停止服务

windows: net stop mysql

linux: service mysqld stop

mac: /usr/local/mysql/support-files/mysql.server stop

brew services stop mysql

3. 连接数据库

mysql -u root -p

4. 退出登录

exit;

5. 查看数据库版本

select version();

6. 查看当前时间

select now();

7. 远程链接

mysql -h 远程ip -u 用户名 -p 密码

8. 更改密码

下面几种方法都可以：

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass');

mysqladmin -u root password "newpass"; # 更改前root密码为空时使用此命令

mysqladmin -u root password "oldpass" "newpass"; # 更改前root已经设置过密码

update mysql.user set authentication_string=password('新密码') where User='root';

UPDATE mysql.user SET Password = PASSWORD('newpass') WHERE user = 'root';

二. 数据库操作

1. 创建数据库

格式: create database 数据库名 charset=utf8;

2. 删除数据库

格式: drop database 数据库名;

3. 切换数据库

格式: use 数据库名;

4. 查看当前选择的数据库

格式: select database();

三. 表操作

1. 查看数据库中所有表

show tables;

2. 创建表

格式: create table 表名(列及类型);

示例: create table student(id int auto_increment primary key, name varchar(20) not null, age int not null, gender bit default 1, address varchar(64), isDelete bit default 0);

1) 创建表时,直接引用其他数据库中的表结构及数据:

create table tablename select * from otherdb.othertable;

2) 创建表时,直接引用其他数据库中的表结构,不引入表中数据:

reate table tablename select * from otherdb.othertable where 1>2; # 指定一个为假的条件,则只引用表结构

3. 删除表

格式: drop table 表名;

示例: drop table student;

4. 查看表结构

desc 表名;

desc student;

5. 查看建表语句

格式: show create table 表名;

示例: show create table student;

6. 重命名表名

格式: rename table 原表名 to 新表名;

示例: rename table student to newstudent;

7. 修改表结构

格式: alter table 表名 add|change|drop 列名 类型;

alter table 表名 add|change|drop 列名 类型 default 默认值; (有默认值方式)

四. 数据操作

1. 增

a. 全列插入

格式: insert into 表名 values(...);

说明: 主键列是自动增长的,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准

示例: insert into student values(0,"jason",20,1,"BJ",0);

b. 缺省插入

格式: insert into 表名(列1,列2...) values(值1,值2...);

示例: insert into student(name,age,address) values("tom",18,"上海");

c. 同时插入多条数据

格式: insert into 表名 values(...),(...),(...)...

示例: insert into student values(0,"jackson",22,1,"SH",0), (0,"lily",20,0,"GZ");

2. 删

格式: delete from 表名 where 条件;

示例: delete from student where id=2;

注意: 没有条件是全部删除,谨慎使用

3. 改

格式: update 表名 set 列1=新值,列2=新值 where 条件;

示例: update student set age=25 where name="jason";

注意: 没有条件是全部列都修改,谨慎使用

4. 查

说明:查询表中的全部数据

格式: select * from 表名;

示例: select * from student;

五. 查

1. 基本语法

格式: select * from 表名;

说明:

1) from关键字后面是表名,表示数据来源于这张表

2) select后面写表中的列名,如果是*表示在结果集中显示表中的所有列

3) 在select后面的列名部分,可以使用as为列名起别名,这个别名显示在结果集中

4) 如果要查询多个列,中间使用逗号分隔

示例:

select * from student;

select name, age from student;

select name, address as addr from student;

2. 消除重复行

在select后面列前面使用distinct可以消除重复的行

示例:

select gender from student;

select distinct gender from student;

3. 条件查询

a. 语法

格式: select * from 表名 where 条件;

b. 比较运算符

等于 =

大于 >

小于 <

大于等于 >=

小于等于 <=

不等于 != 或 <>

需求: 查询id大于5的所有学生

示例: select * from student where id>5;

c. 逻辑运算符

and 并且

or 或

not 非

需求: 查询id大于5的男同学

示例: select * from student where id>5 and gender=1;

d. 模糊查询

like

% 表示任意多个任意字符

_ 表示任意一个任意字符

e. 范围查询

in 表示在一个非连续的范围内

between...and... 表示在一个连续的范围内

需求: 查询编号为8,10,12的学生

示例: select * from student where id in (8,10,12);

需求: 查询编号为6到10的学生

示例: select * from student where id between 6 and 10;

f. 空判断

注意: null与""不同

判断空: is null

判断非空: is not null

g. 优先级

小括号, not 比较运算符, 逻辑运算符

and比or优先级高,如果同时出现并希望先执行or,需要配合小括号使用

4. 聚合

为了快速得到统计数据,提供了5个聚合函数

a. count(\*) 表示计算总行数,括号中可以写*或列名

b. max(列) 表示求此列的最大值

c. min(列) 表示求此列的最小值

d. sum(列) 表示求此列的和

e. avg(列) 表示求此列的平均值

需求: 查询学生总数

示例: select count(*) from student;

需求: 查询女生编号的最大值

示例: select max(id) from student where gender=0;

需求: 查询所有学生的年龄和

示例: select sum(age) from student;

需求: 查询所有学生的年龄平均值

示例: select avg(age) from student();

5. 分组

按照字段分组,表示此字段相同的数据会放到一个集合中.

分组后,只能查询出相同的数据列,对于有差异的数据列无法显示在结果中.

可以对分组后的数据进行统计,做聚合运算

语法: select 列1,列2,聚合... from 表名 group by 列1,列2...

需求: 查询男女生总数

示例: select gender,count(*) from student group by gender;

分组后的数据筛选,使用having,表示对分组后的结果再过滤.

示例: select gender,count(*) from student group by gender having gender;

where与having区别:

where是指对from后面指定的表进行筛选,属于对原始数据的筛选;

having是对group by的结果进行筛选

6. 排序

语法: select * from 表名 order by 列1 asc|desc, 列2 asc|desc, ...

说明:

a. 将数据按照列1进行排序,如果某些列1的值相同,则按照列2进行排序

b. 默认按照从小到大的顺序排序

c. asc 升序

d. desc 降序

需求: 将没有被删除的数据按照年龄排序

示例:

select * from student where isDelete=0 order by age desc;

select * from student where isDelete=0 order by age desc, id desc;

7. 分页

语法: select * from 表名 limit start,count;

说明: start 索引从0开始; count 结果集中显示个数

示例:

select * from student limit 0,3;

select * from student limit 3,3;

select * from student where gender=0 limit 0,3;

六. 关联

一对多示例

建表语句:

1. create table class(id int auto_increment primary key, name varchar(20) not null, stuNum int not null);

2. create table students(id int auto_increment primary key, name varchar(20) not null, gender bit default 1, classid int not null, foreign key(classid) references class(id));

# 使用外键关联班级表的主键. 注: 表的外键必须是另一张表的主键

插入一些数据:

insert into class values(0, "python01", 45), (0, "python02", 50), (0, "python03", 60);

insert into students values(0, "jason", 1, 1);

insert into students values(0, "lily", 1, 10); # 此条语句报错

insert into students values(0, "curry", 1, 2);

关联查询:

select students.name,class.name from class inner join students on class.id=students.classid;

select students.name,class.name from class left join students on class.id=students.classid;

分类:

1. 表A inner join 表B

表A与表B匹配的行会出现在结果集中

2. 表A left join 表B

表A与表B匹配的行会出现在结果集中,外加表A中独有的数据,未对应的数据使用null填充

3. 表A right join 表B

表A与表B匹配的行会出现在结果集中,外加表B中独有的数据,未对应的数据使用null填充

七. 数据备份,恢复

1. 数据备份

1) 备份表结构+数据

mysqldump -u root -p test > test.dump # 备份test数据库

2) 只备份表结构

mysqldump --no-data --databases db1 db2 eb3 > test.dump # 备份db1,db2,db3的表结构

或

mysqldump -u root -p -d test > test.dump # 备份test的表结构

3) 备份所有数据库

mysqldump --all-databases > test.dump

2. 数据恢复

1) 系统命令行恢复

mysqldump -u root -p test > test.dump # 执行这条语句备份(有问题,暂时情况是终端没报错,但数据没有恢复到db2中)

mysqldump -uroot -p -d db2 < test.dump # 将备份的数据恢复到本地的db2数据库(db2已经存在且为空,新建的即可)

2) mysql命令行恢复

mysqldump -u root -p test > test.dump # 执行这条语句备份

mysql> use db1;

Database changed

mysql> show tables;

Empty set (0.00 sec)

mysql> source test.dump; # source后可以接绝对路径,如果不用绝对路径,那么要先切换到test.dump所在的目录

mysql> show tables; # 可以看到备份文件中的表已经恢复

+---------------+

| Tables_in_db1 |

+---------------+

| class |

| juniorStus |

| student |

+---------------+

3 rows in set (0.00 sec)

八. 补充内容

1. 重置密码

1)windows

net stop mysql # 停止服务

mysqld --skip-grant-tables # 以跳过授权表的方式启动服务

mysql -uroot -p # 直接回车登录不需要输入密码

update mysql.user set authentication_string =password('新密码') where User='root'; # 设置新密码

2)linux/mac

./mysqld_safe --skip-grant-tables # 安装mysql的bin目录下执行

mysql -uroot -p # 直接回车登录

update mysql.user set authentication_string =password('新密码') where User='root';

2. 创建用户and授权

root身份登录,然后进入mysql数据库下操作

mysql> use mysql

Database changed

1)新用户的增删改

新增: create user '用户名'@'ip地址' identified by '用户密码';

删除: drop user '用户名'@'ip地址';

修改:

rename user '用户名'@'IP地址' to '新用户名'@'IP地址';

set password for '用户名'@'IP地址'=Password('新密码');

示例:

# 指定允许ip：192.118.1.1的jason用户登录

create user 'jason'@'192.168.1.10' identified by '123';

# 指定允许ip：192.118.1.开头的jason用户登录

create user 'jason'@'192.168.1.%' identified by '123';

# 指定允许任何ip的jason用户登录

create user 'jason'@'%' identified by '123';

2)用户权限管理

新增用户默认是没有任何权限的,不能查看数据库,表...

查看权限: show grants for '用户名'@'ip地址';

授予权限: grant operation on dbname.tablename to '用户名'@'ip地址';

取消权限: revoke operation on dbname.tablename from '用户名'@'ip地址';

示例:

# 授权jason用户仅对test.students文件有查询、插入和更新的操作

grant select,insert,update on test.students to "jason"@'%';

# 表示有所有的权限,除了grant这个命令,这个命令是root才有的. jason用户对test下的students文件有任意操作

grant all privileges on test.students to "jason"@'%';

# jason用户对test数据库中的文件执行任何操作

grant all privileges on test.* to "jason"@'%';

# jason用户对所有数据库中文件有任何操作

grant all privileges on *.* to "jason"@'%';

# 取消权限

# 取消jason用户对test的students文件的任意操作

revoke all on test.students from 'jason'@"%";

revoke all on test.* from 'jason'@"%";

revoke all on *.* from 'jason'@"%";