1一. 基本命令 21. 启动服务 3 windows: net start mysql 4 linux: service mysqld start 5 mac: /usr/local/mysql/support-files/mysql.server start (gz解压包方式安装,路径按照解压安装时的目录查找) 6 brew services start mysql (brew install mysql方式安装启动方式) 7 为了方便操作,可以自定义启动命令,修改~/.bash_profile文件,添加以下内容: 8 9 # mysql快捷命令 10 alias mysqlstart='sudo /usr/local/mysql/support-files/mysql.server start' # 启动服务 11 alias mysqlstop='sudo /usr/local/mysql/support-files/mysql.server stop' # 停止服务 12 alias mysqlstatus='sudo /usr/local/mysql/support-files/mysql.server status' # 查看状态 13 alias mysqlrestart='sudo /usr/local/mysql/support-files/mysql.server restart' # 重启服务 14 添加完成后,可以直接执行mysqlstart,mysqlstop命令启动停止服务. 152. 停止服务 16 windows: net stop mysql 17 linux: service mysqld stop 18 mac: /usr/local/mysql/support-files/mysql.server stop 19 brew services stop mysql 203. 连接数据库 21 mysql -u root -p 224. 退出登录 23 exit; 245. 查看数据库版本 25 select version(); 266. 查看当前时间 27 select now(); 287. 远程链接 29 mysql -h 远程ip -u 用户名 -p 密码 308. 更改密码 31 下面几种方法都可以： 32 SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpass'); 33 mysqladmin -u root password "newpass"; # 更改前root密码为空时使用此命令 34 mysqladmin -u root password "oldpass" "newpass"; # 更改前root已经设置过密码 35 update mysql.user set authentication_string=password('新密码') where User='root'; 36 UPDATE mysql.user SET Password = PASSWORD('newpass') WHERE user = 'root'; 37 38 39二. 数据库操作 401. 创建数据库 41 格式: create database 数据库名 charset=utf8; 422. 删除数据库 43 格式: drop database 数据库名; 443. 切换数据库 45 格式: use 数据库名; 464. 查看当前选择的数据库 47 格式: select database(); 48 49 50三. 表操作 511. 查看数据库中所有表 52 show tables; 532. 创建表 54 格式: create table 表名(列及类型); 55 示例: 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); 56 1) 创建表时,直接引用其他数据库中的表结构及数据: 57 create table tablename select * from otherdb.othertable; 58 2) 创建表时,直接引用其他数据库中的表结构,不引入表中数据: 59 reate table tablename select * from otherdb.othertable where 1>2; # 指定一个为假的条件,则只引用表结构 603. 删除表 61 格式: drop table 表名; 62 示例: drop table student; 634. 查看表结构 64 desc 表名; 65 desc student; 665. 查看建表语句 67 格式: show create table 表名; 68 示例: show create table student; 696. 重命名表名 70 格式: rename table 原表名 to 新表名; 71 示例: rename table student to newstudent; 727. 修改表结构 73 格式: alter table 表名 add|change|drop 列名 类型; 74 alter table 表名 add|change|drop 列名 类型 default 默认值; (有默认值方式) 75 76 77四. 数据操作 781. 增 79 a. 全列插入 80 格式: insert into 表名 values(...); 81 说明: 主键列是自动增长的,但是在全列插入时需要占位,通常使用0,插入成功后以实际数据为准 82 示例: insert into student values(0,"jason",20,1,"BJ",0); 83 b. 缺省插入 84 格式: insert into 表名(列1,列2...) values(值1,值2...); 85 示例: insert into student(name,age,address) values("tom",18,"上海"); 86 c. 同时插入多条数据 87 格式: insert into 表名 values(...),(...),(...)... 88 示例: insert into student values(0,"jackson",22,1,"SH",0), (0,"lily",20,0,"GZ"); 892. 删 90 格式: delete from 表名 where 条件; 91 示例: delete from student where id=2; 92 注意: 没有条件是全部删除,谨慎使用 933. 改 94 格式: update 表名 set 列1=新值,列2=新值 where 条件; 95 示例: update student set age=25 where name="jason"; 96 注意: 没有条件是全部列都修改,谨慎使用 974. 查 98 说明:查询表中的全部数据 99 格式: select * from 表名; 100 示例: select * from student; 101 102 103五. 查 1041. 基本语法 105 格式: select * from 表名; 106 说明: 107 1) from关键字后面是表名,表示数据来源于这张表 108 2) select后面写表中的列名,如果是*表示在结果集中显示表中的所有列 109 3) 在select后面的列名部分,可以使用as为列名起别名,这个别名显示在结果集中 110 4) 如果要查询多个列,中间使用逗号分隔 111 示例: 112 select * from student; 113 select name, age from student; 114 select name, address as addr from student; 1152. 消除重复行 116 在select后面列前面使用distinct可以消除重复的行 117 示例: 118 select gender from student; 119 select distinct gender from student; 1203. 条件查询 121 a. 语法 122 格式: select * from 表名 where 条件; 123 b. 比较运算符 124 等于 = 125 大于 > 126 小于 < 127 大于等于 >= 128 小于等于 <= 129 不等于 != 或 <> 130 需求: 查询id大于5的所有学生 131 示例: select * from student where id>5; 132 c. 逻辑运算符 133 and 并且 134 or 或 135 not 非 136 需求: 查询id大于5的男同学 137 示例: select * from student where id>5 and gender=1; 138 d. 模糊查询 139 like 140 % 表示任意多个任意字符 141 _ 表示任意一个任意字符 142 e. 范围查询 143 in 表示在一个非连续的范围内 144 between...and... 表示在一个连续的范围内 145 需求: 查询编号为8,10,12的学生 146 示例: select * from student where id in (8,10,12); 147 需求: 查询编号为6到10的学生 148 示例: select * from student where id between 6 and 10; 149 f. 空判断 150 注意: null与""不同 151 判断空: is null 152 判断非空: is not null 153 g. 优先级 154 小括号, not 比较运算符, 逻辑运算符 155 and比or优先级高,如果同时出现并希望先执行or,需要配合小括号使用 1564. 聚合 157 为了快速得到统计数据,提供了5个聚合函数 158 a. count(\*) 表示计算总行数,括号中可以写*或列名 159 b. max(列) 表示求此列的最大值 160 c. min(列) 表示求此列的最小值 161 d. sum(列) 表示求此列的和 162 e. avg(列) 表示求此列的平均值 163 需求: 查询学生总数 164 示例: select count(*) from student; 165 需求: 查询女生编号的最大值 166 示例: select max(id) from student where gender=0; 167 需求: 查询所有学生的年龄和 168 示例: select sum(age) from student; 169 需求: 查询所有学生的年龄平均值 170 示例: select avg(age) from student(); 1715. 分组 172 按照字段分组,表示此字段相同的数据会放到一个集合中. 173 分组后,只能查询出相同的数据列,对于有差异的数据列无法显示在结果中. 174 可以对分组后的数据进行统计,做聚合运算 175 语法: select 列1,列2,聚合... from 表名 group by 列1,列2... 176 需求: 查询男女生总数 177 示例: select gender,count(*) from student group by gender; 178 179 分组后的数据筛选,使用having,表示对分组后的结果再过滤. 180 示例: select gender,count(*) from student group by gender having gender; 181 182 where与having区别: 183 where是指对from后面指定的表进行筛选,属于对原始数据的筛选; 184 having是对group by的结果进行筛选 1856. 排序 186 语法: select * from 表名 order by 列1 asc|desc, 列2 asc|desc, ... 187 说明: 188 a. 将数据按照列1进行排序,如果某些列1的值相同,则按照列2进行排序 189 b. 默认按照从小到大的顺序排序 190 c. asc 升序 191 d. desc 降序 192 需求: 将没有被删除的数据按照年龄排序 193 示例: 194 select * from student where isDelete=0 order by age desc; 195 select * from student where isDelete=0 order by age desc, id desc; 1967. 分页 197 语法: select * from 表名 limit start,count; 198 说明: start 索引从0开始; count 结果集中显示个数 199 示例: 200 select * from student limit 0,3; 201 select * from student limit 3,3; 202 select * from student where gender=0 limit 0,3; 203 204 205六. 关联 206 一对多示例 207 建表语句: 208 1. create table class(id int auto_increment primary key, name varchar(20) not null, stuNum int not null); 209 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)); 210 # 使用外键关联班级表的主键. 注: 表的外键必须是另一张表的主键 211 212 插入一些数据: 213 insert into class values(0, "python01", 45), (0, "python02", 50), (0, "python03", 60); 214 insert into students values(0, "jason", 1, 1); 215 insert into students values(0, "lily", 1, 10); # 此条语句报错 216 insert into students values(0, "curry", 1, 2); 217 关联查询: 218 select students.name,class.name from class inner join students on class.id=students.classid; 219 select students.name,class.name from class left join students on class.id=students.classid; 220 221 分类: 222 1. 表A inner join 表B 223 表A与表B匹配的行会出现在结果集中 224 2. 表A left join 表B 225 表A与表B匹配的行会出现在结果集中,外加表A中独有的数据,未对应的数据使用null填充 226 3. 表A right join 表B 227 表A与表B匹配的行会出现在结果集中,外加表B中独有的数据,未对应的数据使用null填充 228 229 230七. 数据备份,恢复 2311. 数据备份 232 1) 备份表结构+数据 233 mysqldump -u root -p test > test.dump # 备份test数据库 234 2) 只备份表结构 235 mysqldump --no-data --databases db1 db2 eb3 > test.dump # 备份db1,db2,db3的表结构 236 或 237 mysqldump -u root -p -d test > test.dump # 备份test的表结构 238 3) 备份所有数据库 239 mysqldump --all-databases > test.dump 2402. 数据恢复 241 1) 系统命令行恢复 242 mysqldump -u root -p test > test.dump # 执行这条语句备份(有问题,暂时情况是终端没报错,但数据没有恢复到db2中) 243 mysqldump -uroot -p -d db2 < test.dump # 将备份的数据恢复到本地的db2数据库(db2已经存在且为空,新建的即可) 244 2) mysql命令行恢复 245 mysqldump -u root -p test > test.dump # 执行这条语句备份 246 mysql> use db1; 247 Database changed 248 mysql> show tables; 249 Empty set (0.00 sec) 250 mysql> source test.dump; # source后可以接绝对路径,如果不用绝对路径,那么要先切换到test.dump所在的目录 251 mysql> show tables; # 可以看到备份文件中的表已经恢复 252 +---------------+ 253 | Tables_in_db1 | 254 +---------------+ 255 | class | 256 | juniorStus | 257 | student | 258 +---------------+ 259 3 rows in set (0.00 sec) 260 261 262八. 补充内容 263 1. 重置密码 264 1)windows 265 net stop mysql # 停止服务 266 mysqld --skip-grant-tables # 以跳过授权表的方式启动服务 267 mysql -uroot -p # 直接回车登录不需要输入密码 268 update mysql.user set authentication_string =password('新密码') where User='root'; # 设置新密码 269 2)linux/mac 270 ./mysqld_safe --skip-grant-tables # 安装mysql的bin目录下执行 271 mysql -uroot -p # 直接回车登录 272 update mysql.user set authentication_string =password('新密码') where User='root'; 273 2. 创建用户and授权 274 root身份登录,然后进入mysql数据库下操作 275 mysql> use mysql 276 Database changed 277 1)新用户的增删改 278 新增: create user '用户名'@'ip地址' identified by '用户密码'; 279 删除: drop user '用户名'@'ip地址'; 280 修改: 281 rename user '用户名'@'IP地址' to '新用户名'@'IP地址'; 282 set password for '用户名'@'IP地址'=Password('新密码'); 283 示例: 284 # 指定允许ip：192.118.1.1的jason用户登录 285 create user 'jason'@'192.168.1.10' identified by '123'; 286 # 指定允许ip：192.118.1.开头的jason用户登录 287 create user 'jason'@'192.168.1.%' identified by '123'; 288 # 指定允许任何ip的jason用户登录 289 create user 'jason'@'%' identified by '123'; 290 2)用户权限管理 291 新增用户默认是没有任何权限的,不能查看数据库,表... 292 查看权限: show grants for '用户名'@'ip地址'; 293 授予权限: grant operation on dbname.tablename to '用户名'@'ip地址'; 294 取消权限: revoke operation on dbname.tablename from '用户名'@'ip地址'; 295 示例: 296 # 授权jason用户仅对test.students文件有查询、插入和更新的操作 297 grant select,insert,update on test.students to "jason"@'%'; 298 # 表示有所有的权限,除了grant这个命令,这个命令是root才有的. jason用户对test下的students文件有任意操作 299 grant all privileges on test.students to "jason"@'%'; 300 # jason用户对test数据库中的文件执行任何操作 301 grant all privileges on test.* to "jason"@'%'; 302 # jason用户对所有数据库中文件有任何操作 303 grant all privileges on *.* to "jason"@'%'; 304 # 取消权限 305 # 取消jason用户对test的students文件的任意操作 306 revoke all on test.students from 'jason'@"%"; 307 revoke all on test.* from 'jason'@"%"; 308 revoke all on *.* from 'jason'@"%"; 刷新权限： flush privileges;
grant all privileges on *.* to "root"@'%' indenttified by '123456'; # 远端登录用123456就算用户主机改了密码，远端登录也是123456
indenttified by password;