一. 基本命令
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'@"%"; 刷新权限: flush privileges;
grant all privileges on *.* to "root"@'%' indenttified by '123456'; # 远端登录用123456就算用户主机改了密码,远端登录也是123456
indenttified by password;