MySQL基本命令总结
一. 基本命令 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;
