一、DDL:操作数据库
1、C(Create):创建
- 1.1 创建数据库,先判断是否存在,再创建
create database if not exists DB;
- 1.2 创建数据库,先判断是否存在,再设定字符编码
create database if not exists DB2 character set GBK;
2、R(Retrieve):查询
- 2.1 查询所有数据库的名称
show databases;
- 2.2 查询单个数据库的名称
show create database 数据库名称;
3、U(Update):更新(修改)
- 3.1 给数据库重新设置编码
alter database 数据库名 character set utf8;
4、D(Delete):删除
- 4.1 删除数据库
drop database 数据库名称 ; - 4.2 删除数据库(先判断是否存在)
drop database if exists 数据库名称;
5、使用数据库
- 5.1 查询当前正在使用的数据库名称
select database(); - 5.2 使用数据库
use 数据库名称
二、操作表
1、C(Create):创建
-
1.1 语法
create table 表名(
列名1 数据类型,
列名2 数据类型,
…
列名n 数据类型); -
1.2 常用数据类型
int、double、date(只包含年月日)、datetime(年-月-日 时-分-秒)、datestamp(年-月-日 时-分-秒-----如果不给时间赋值,则默认使用当前系统时间)、varchar -
1.3 举例
name varchar(20)---- 括号中代表字符长度
score double(3,2)---- 小数一共为3位,小数点后保留2位小数
age int
time datetime(yyyy-MM-dd HH:mm:ss)
create table msgtable(name varchar(20),age int,sex varchar(2),birthday date,time timestamp,work varchar(10));
- 1.4复制一张表
create table 表名 like 已存在的表名;
2、R(Retrieve):查询 - 2.1 查询某个数据库中所有的表
先进入该数据库,再进行表的查询
use 数据库名称;
show tables;
- 2.2 查询表的结构
- desc 表的名称;
3、U(Update):更新(修改)
- 3.1 修改表名
alter table rename 表名 to 新的表名 ;
alter table rename students to students;
- *3.2 修改表的编码
*alter table 表名 character set 编码;
alter table students character set gbk;
- 3.3 修改表中某一列的名称 类型
alter table 表名 列名 数据类型;
alter table students change sex gender varchar(4);
- 3.4 增加一列
alter table 表名 add column 列名 varchar(30);
alter table students add column name varchar(20);
- 3.5 删除一列
alter table 表名 drop 列名;
alter table students drop work;
4、D(Delete):删除
- 4.1 删除一张表
drop table if exists 表的名称;
三、DML:增删改表中的数据
1. 添加数据到表中
- 语法:insert into 表名(列表名1,列表名2…列表名n) values(值1,值2…值n) ------ 注意:列表名要和值一一对应
insert into students(id,name,age) values("0001","XXX",20);
2.删除表中的数据
- 语法::delete from 表名 where 条件;
delete from students where id = "0001";
- 推荐使用(直接删除表,并重新创建一张一样的表,效率更高)
TRUNCATE TABLE students;
3.修改表中的数据
- 语法:update 表名 set 列名=值 where 条件;
update students set age = 20 where name="小明";
四、DQL:数据查询语言
4.1 基础查询
- 一次查询多个
select 列表名1,列表名2…列表名n from 表名;
select name,age,home from students;
- 去除查询结果中重复的内容
select distinct 列表名 from 表名;
select distinct age from students;
- 两个列表的值相加,另外起名(多用于数值运算)
select IFNULL(列表名1,0)+IFNULL(列表名2,0) as 别名 from 表名;-------因为在当有数值为NULL的时候会默认两数相加为NULL,这显然不符合我们的要求,IFNULL这样一个函数使得在获取到为NULL时,默认设置为0
select IFNULL(score1,0)+IFNULL(score2,0) as total_score from students;
4.2 条件查询
- 查询两数之间的信息(between and)
SELECT * FROM students WHERE age>=20 AND age<=50;
// 等同于SELECT * FROM students WHERE age BETWEEN 20 AND 50;
- 举例:查询年龄为10岁或20岁的人的信息
select * from students where age=10 or age=20;
- 查询数据为空的信息
select * from students where monry is null;
4.3 模糊查询
- 查询表中姓X的人的信息
(使用like关键字,占位符“_”表示占一个位置,“%”表示占多个位置)
select * from students where name like "X__";
// 查询的是姓X某某的人的信息
select * from students where name like "%X%";
// 查询的是某X某的人的信息
五、(DQL查询2)
5.1排序查询
select 信息 from 表名 order by 需排序字段(升序—ASC、降序—DESC);-----默认为升序
select name,math_score from students order by math_score desc;
排序方式:升序,降序、第二条件排序
*语法:*select 查询信息 from 表名 order by 字段1(升序、降序),字段2(升序,降序);
select * from students order by math_score ASC,english_score DESC;
* 5.2 常用函数
sum、max、min、avg、count
select sum(math_score) as math_total,sum(english_score) as english_total from students ;
select max(math_score) from students;
select min(min_score) from students;
select avg(math_score) from students;
select count(name) from students; // 统计name有多少个
* 5.3 分组查询(分组查询的字段,要么为分组字段,要么为聚合函数)
语法:select 信息 from 表名 order by 需分组字段;
SELECT sex, AVG(math_score) FROM students GROUP BY sex;
分组前对条件进行限定
select sex,avg(english_score) from students where english_score>70 and english_score <90 group by sex;
5.4分页查询
语法:limit 开始的索引,每页搜索的条数;
公式:开始的索引 = (当前的页码-1)*每页显示的条数
select * from students limit 0,3;
六、约束
6.1 非空约束
- 创建表时添加非空约束
create table students(id int not null,name varchar(20),age int);
- 创建完后添加非空约束
alter table students modify id int not null;
- 删除非空约束
alter table students modify id int;
6.2 唯一约束
- 创建表时添加唯一约束
create table students(id int unique,name varchar(20),age int);
- 创建完后添加唯一约束
alter table students modify id int unique;
- 删除表的唯一约束
alter table students drop index id;
6.3 主键约束
- 创建表时添加主键约束
create table students(id int primary key,name varchar(20),age int);
- 创建完后添加主键约束
alter table students modify id int primary key;
- 删除主键约束
alter table students drop primary key;
- 自动增长-----一般配合主键使用,如果某一列是数据类型的值,使用auto_increment来实现主键的自动增长
- 创建表时添加自动增长
create table students(id int auto_increment,name varchar(20),age int);
- 创建表后添加自动增长
alter table students modify id int auto_increment;
- 删除自动增长
alter table students modify id int;
6.4 外键约束(foreign key)
为什么有外键约束?
下图中,重庆,成都出现多次,数据冗余,而且修改也很不方便,因此使用外键(表的拆分),便于查看和修改
- 语法(显式指定外键约束的名字):
constraint 外键名 foreign key (外键列)references 主表(主表列名称)
实例:
SHOW TABLES;
# 创建单表
CREATE TABLE mytable(NAME VARCHAR(20),home VARCHAR(20));
INSERT INTO mytable VALUES("aaa","重庆"),("bbb","重庆"),("ccc","重庆"),("ddd","成都"),("eee","成都");
# 创建一个教师表(主表) 添加教师信息
CREATE TABLE teacher(
t_id INT AUTO_INCREMENT PRIMARY KEY,
t_name VARCHAR(20),
t_course VARCHAR(10)
);
INSERT INTO teacher VALUES(NULL,"蔡蔡","java"),(NULL,"喆喆","通识课"),(NULL,"星星","python");
# 创建一个学生表关联主表(从表)
CREATE TABLE stu(
s_id INT AUTO_INCREMENT PRIMARY KEY,
s_name VARCHAR(20),
s_sex VARCHAR(4),
s_age INT,
# 这是外键列,关联主表的主键
# 格式:constraint 外键名 foreign key (外键列) references 主表(主键)
s_fk INT,CONSTRAINT teacher_stu_fk FOREIGN KEY(s_fk) REFERENCES teacher(t_id)
);
# 添加4个学生信息
INSERT INTO stu VALUES(NULL,"学生一","男",20,1),(NULL,"学生二","男",21,1),
(NULL,"学生三","女",18,2),(NULL,"学生四","男",19,3);
SELECT * FROM stu;
SELECT * FROM teacher;
使用外键约束时,如果要删除主表的内容,需要先将从表中的数据删除,可以预防删错表
- 删除外键
alter table stu drop foreign key teacher_stu_fk;
- 创建完后添加外键
alter table stu add constraint teacher_stu_fk foreign key(s_fk) references teacher(t_id);
- 级联跟新 / 级联删除
# 级联更新:在创建外键后添加
on update cascade
# 级联删除:在创建外键后添加
on delete cascade
七、数据库的备份与还原
- 备份
mysqldump -u用户名 -p密码 数据库名称 > 保存路径 - 还原
1.登录数据库
2.创建数据库
3.使用数据库
4.执行文件:source 文件路径
八、多表查询
分类:
1.内连接查询
- 隐式内连接:
SELECT
t1.t_id,
t1.t_course,
t1.t_name,
t2.s_id,
t2.s_name
FROM
teacher t1,stu t2
WHERE
t1.t_id = t2.s_fk;
- 显式内连接:
语法:select 查询字段 from 表名1 inner join 表名2 on 条件
SELECT t1.s_id,t1.s_name,t1.s_sex,t2.t_id,t2.t_course,t2.t_name FROM stu t1 INNER JOIN teacher t2 ON t1.s_fk=t2.t_id;
2.外连接查询
左外连接:
SELECT t1.s_id,t1.s_name,t2.t_id,t2.t_course FROM stu t1 LEFT JOIN teacher t2 ON t1.s_fk=t2.t_id;
右外连接:
SELECT t1.s_id,t1.s_name,t2.t_id,t2.t_course FROM stu t1 RIGHT JOIN teacher t2 ON t1.s_fk=t2.t_id;
3.子查询
- 把子查询当成where条件中的值:·
SELECT * FROM stu t1 WHERE t1.s_id = (SELECT t1.s_id FROM stu t1 WHERE t1.s_age = 20);
- 把子查询当成数据表:
SELECT * FROM stu t1,(SELECT * FROM teacher) t2 WHERE t1.s_fk=t2.t_id;
- 子查询的结果返回为多个值,可以使用 in \ all \ any:
select * from stu where stu.id = any(select t.id from teacher);
# in\all 用法与之类似
八、事务
8.1 基本概念:
当一个操作有多个步骤,如果它被事务管理,该操作中出现错误,那么所有的操作都不会生效(要么同时成功要么同时失败)
8.2 操作:
1.开启事务:start transaction;
2.回滚:rollback;
3.提交:commit;
MySQL中默认开启自动提交
可以使用select @@autocommit; 查询(1为开启,0为关闭)
修改:set @@autocommit =1 (0);
8.3 事务的四大特征(面试)及隔离级别(了解)
- 四大特征:原子性(同时成功,同时失败)、持久性(操作后持久保存数据在硬盘)、隔离性(事务间相互独立)、一致性(事务操作前后,数据总量不会改变)
查询隔离级别:select @@tx_isolation;
设置隔离级别:set global transaction isolation level 隔离级别;
九、DCL:管理用户
1查询用户:
use mysql; -- 使用mysql数据库
select * from user; --查询user表中用户
# 主机名使用 % 表示可以在任意主机上登录
2.添加用户:
create user '用户名'@'主机名' identified by '密码';
3.删除用户:
drop user '用户名'@'主机名';
4.修改密码(两种方式):
update user set password = password('新密码') where user = '用户名';
set password for '用户名'@'主机名' = password('新密码');
MySQL中忘记root用户密码怎么办?
1.cmd(管理员身份运行): net stop mysql --停止mysql服务器
2.使用无验证方式启动mysql服务:mysqld --skip-grant-tables
3.使用新的cmd:mysql +回车直接登录
4.修改root用户密码
5.关闭cmd窗口
6.任务管理器:关闭mysqld.exe进程
7重新设置密码成功
5.权限管理:
- 查询权限
show grants for ‘用户名’@‘主机名’; - 授予权限
grant 权限 on 数据库名.表名 to ‘用户名’@‘主机名’; - 撤销权限
revoke 权限 on 数据库名.表名 from ‘用户名’@‘主机名’;