1. 语法:
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
2. 基础查询
1. 多个字段的查询
select 列1, 列2....列n from 表名称;
* 注意:
* 如果查询所有字段, 则可以使用*来代替字段列表.
select * from 表名称;
2. 去除重复查询
* distinct -- 去除重复(结果及完全相同, 才会去除)
select distinct 列1, 列2....列n from 表名称;
案例:
-- 去除所有重复的数据
select distinct * from student;
3. 计算列
* 一般可以使用四则运算计算一些列的值. (一般只会进行数值型的计算)
* ifnull(表达式1, 表达式2): null参与的计算结果都为null
* 表达式1: 那个字段需要去判断是否为null
* 表达式2: 如果该字段为null后的替换值
select 列1, 列2, 列3, 列2 + ifnull(列3, 0) from student; -- 列3的值为null, 把null替换为0, 列3不为null, 就用原来值进行计算
案例:
-- 查询 姓名 数学成绩 英语成绩 数学成绩和英语成绩之和 如果英语成绩为null把它当做0处理, 不是就用原来的值
select name, math, english, math + ifnull(english, 0) from student;
4. 起别名
* as: as也可以省略
3. 条件查询
1. where字句后跟条件
select * from 表名称 where 列1 >= 条件值1 and 列2 <= 条件之2;
案例:
-- 20岁 到 30岁之间有哪些人
select * from student where age >= 20 and age <= 30;
2. 运算符
* > < >= <= = != <>
* 大于: >
* 小于: <
* 大于等于: >=
* 小于等于: <=
* 等于: =
* 不等于: !=
* 不等于: <>
* between...and
select * from 表名称 where 列 between 条件1 and 条件2;
案例:
-- 20岁 到 30岁之间有哪些人
select * from student where age between 20 and 30;
* in(集合)
select * from 表名称 where 列 in (条件1, 条件2, 条件3);
案例:
-- 查询年龄22岁, 18岁, 25岁的信息
select * from student where age in (22, 18, 25);
* not in(集合)
select * from 表名称 where 列 not in (条件1, 条件2, 条件3);
案例:
-- 查询年龄不为22岁, 18岁, 25岁的信息
select * from student where age not in (22, 18, 25);
* like: 模糊查询
* 占位符:
_: 任意一个字符(必须有一位)
select * from 表名称 where 字段 like '_';
%: 任意多个字符(0或者多个)
select * from 表名称 where 字段 like '%';
案例:
-- 查询姓马有哪些人? like
select * from student where name like '马%';
-- 查询姓名中第二个字为化的人
select * from student where name like '_化%';
-- 查询姓名是三个字的人
select * from student where name like '___';
-- 查询姓名中包含马的人
select * from student where name like '%马%';
* is null
select * from 表名称 where 列 is null;
案例:
-- 查询英语成绩为null
select * from student where english is null;
* is not null
select * from 表名称 where 列 is not null;
案例:
-- 查询英语成绩不为null
select * from student where english is not null;
* and 或 &&
select * from 表名称 where 列 >= 20 and 列 <= 30;
案例:
-- 20岁 到 30岁之间有哪些人
select * from student where age >= 20 and age <= 30;
* or 或 ||
select * from 表名称 where 列 = 22 or 列 = 18 or 列 = 25;
案列:
-- 查询年龄22岁, 18岁, 25岁的信息
select * from student where age = 22 or age = 18 or age = 25;
* not 或 !
4. 排序查询
* 语法: order by 字句
order by 排序字段1 排序方式1, 排序字段2 排序方式2....
* 排序方式:
ASC: 升序, 默认的.
DESC: 降序
* 注意:
* 如果有多个排序条件, 则当前边的条件值一样时, 才会判断第二条件.
案例:
-- 数学成绩升序排序
select * from student order by math;
select * from student order by math asc;
-- 数学成绩降序排序
select * from student order by math desc;
-- 按照数学成绩升序排名, 如果数学成绩一样, 则按照英语成绩升序排名
select * from student order by math asc, english asc;
5. 聚合函数: 将一列数据作为一个整体, 运行纵向的计算.
1. count: 计算个数
select count(字段名) from 表名称
1. 一般选择非空的列: 主键
-- id为主键, 非空列
select count(id) from student;
2. count(*): 一条数据中有一个不为null, 它就会算一条数据
-- 一条数据中有一个不为null, 它就会算一条数据
select count(*) from student;
2. max: 计算最大值
select max(列) from 表名称;
案列:
-- 查询数学成绩的最大值
select max(math) from student;
3. min: 计算最小值
select min(列) from 表名称;
案列:
-- 查询数学成绩的最小值
select min(math) from student;
4. sum: 计算和
select sum(列) from 表名称;
案列:
-- 查询数学成绩的和
select sum(math) from student;
5. avg: 计算平均值
select avg(列) from 表名称;
案列:
-- 查询数学成绩的平均值
select avg(math) from student;
* 注意: 聚合函数的计算, 排除null值.(值为null是不会计算进去)
解决方案:
1. 选择不包含非空的列进行计算
2. ifnull函数
-- 当英语成绩为null, 把它当做0, 不为0是就用原数据, 所以不存在null了
select count(ifnull(english, 0)) from student;
6. 分组查询
1. 语法: group by
2. 注意:
1. 分组之后查询的字段: 要么分组字段、聚合函数
2. where 和 having的区别?
1. where 在分组之前进行限定, 如果不满足条件, 则不参与分组. having在分组之后进行限定, 如果不满足结果, 则不会被查询出来
2. where 后不可以跟聚合函数, having可以进行聚合函数的判断
案列:
-- 按照性别分组, 分别查询男, 女同学的数学平均分
select sex, avg(math) from student group by sex;
-- 按照性别分组, 分别查询男, 女同学的数学平均分, 分别的人数
select sex, avg(math), count(id) from student group by sex;
-- 按照性别分组, 分别查询男, 女同学的数学平均分, 分别的人数 要求: 分数低于70分的人, 不参与分组
select sex, avg(math), count(id) from student where math >= 70 group by sex;
-- 按照性别分组, 分别查询男, 女同学的数学平均分, 分别的人数 要求: 分数低于70分的人, 不参与分组, 分组之后, 人数要大于2个人
select sex, avg(math), count(id) from student where math >= 70 group by sex having count(id) > 2;
7. 分页查询
1. 语法: limit 开始的索引, 每页查询的条数;
2. 公式: 开始的索引 = (当前的页码 - 1) * 每页显示的条数
案列:
-- 每页显示3条记录
select * from student limit 0, 3; -- 第一页
select * from student limit 3, 3; -- 第二页
select * from student limit 6, 3; -- 第三页
3. limit是一个MySQL"方言"(就是limit只是mysql的操作)
约束
* 概念: 对表中的数据进行限定, 保证数据的正确性、有效性和完整性.
* 分类:
1. 主键约束: primary key
2. 非空约束: not null
3. 唯一约束: unique
4. 外键约束: foreign key
* 非空约束: not null, 之不能为null
1. 创建表时, 添加约束
create table stu(
id int,
name varchar(20) not null -- name为非空
);
2. 创建表后, 添加非空约束
-- 创建表完成, 添加非空约束
alter table stu modify name varchar(20) not null;
-- 创建表完成, 添加非空约束
alter table stu change name name varchar(20) not null;
3. 删除非空约束
-- 删除name的非空约束
alter table stu change name name varchar(20);
-- 删除name的非空约束
alter table stu modify name varchar(20);
* 唯一约束: unique, 之不能重复
1. 创建表时, 添加唯一约束
create table stu(
id int,
mobile varchar(11) unique -- mobile 唯一约束
)
* 注意mysql中, 唯一约束限定的列的值可以有多个null
2. 删除唯一约束
alter table stu drop index mobile;
3. 在创建表后, 添加唯一约束
-- 在创建表后, 添加唯一约束
alter table stu change mobile mobile varchar(11) unique;
-- 在创建表后, 添加唯一约束
alter table stu modify mobile varchar(11) unique;
* 主键约束: primary key
1. 注意:
1. 含以: 非空且唯一
2. 一张表只能有一个字段为主键
3. 主键就是表中记录的唯一标识
2. 创建表时, 添加主键约束
create table stu(
id int primary key, -- 主键约束
name varchar(20)
)
3. 删除主键
-- 错误方式: alter table stu modify id int;
alter table stu drop primary key;
4. 创建完表后, 添加主键
-- 创建完表后, 添加主键
alter table stu change id id int primary key;
-- 创建完表后, 添加主键
alter table stu modify id int primary key;
5. 自动增长:
1. 概念: 如果某一列是数值类型, 使用auto_increment 可以来完成值的自动增长(一般配合数值, 主键用)
2. 在创建表后, 添加主键约束
create table stu(
id int primary key auto_increment, -- 主键约束, 自动增长
name varchar(20)
)
3. 删除自动增长
-- 删除自动增长
alter table stu modify id int;
-- 删除自动增长
alter table stu change id id int;
4. 添加自动增长
-- 添加自动增长
alter table stu modify id int auto_increment;
-- 添加自动增长
alter table stu change id id int auto_increment;
* 外键约束: foreign key, 让标语表产生关系, 从而保证数据的正确性
1. 语法:
create table 表名(
....
外键列
constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称)
)
create table department( -- 部门表
id int primary key auto_increment,
dep_name varchar(20),
dep_location varchar(20)
);
create table employee( -- 员工表 部门对员工: 一对多, 所以在employee表中添加外键
id int primary key auto_increment,
name varchar(20),
age int,
dep_id int, -- 外键对应主表的主键
constraint emp_dep_id foreign key (dep_id) references department(id) -- 设置外键关系
)
2. 删除外键
alter table 表名称 drop foreign key 外键名称;
-- 删除外键
alter table employee drop foreign key emp_dep_id;
3. 创建表之后, 添加外键
alter table 表名称 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称);
-- 添加外键
alter table employee add constraint emp_dep_id foreign key (dep_id) references department(id);
4. 级联操作
1. 添加级联操作
语法:
1. 设置外键, 并添加级联更新--(把主表--外键关联字段的修改了, 从表也会自动更新)
alter table 表名称 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade;
-- 设置外键, 并添加级联更新
alter table employee add constraint emp_dep_id foreign key (dep_id) references department(id) on update cascade;
2. 设置外键, 并添加级联更新, 级联删除--(把主表--外键关联字段的修改 或 删除了, 从表也会自动更新)
alter table 表名称 add constraint 外键名称 foreign key (外键列名称) references 主表名称(主表列名称) on update cascade on delete cascade;
-- 设置外键, 并添加级联更新, 级联删除
alter table employee add constraint emp_dep_id foreign key (dep_id) references department(id) on update cascade on delete cascade;
2. 分类:
1. 级联更新: on update cascade
2. 级联删除: on delete cascade
* SQL分类:
1. DDL: 操作数据库和表
2. DML: 增删改表中的数据
3. DQL: 查询表中的数据
4. DCL: 管理用户, 并对用户进行授权
* DBA: 数据库管理员
* DCL: 管理用户, 并对用户进行授权
1. 管理用户
1. 添加用户:--->(主机名, 在mysql数据库下的user表里有一个host字段, 这个字段的值就是主机名)
* 语法:
create user '用户名'@'主机名' identified by '密码';
案列:
-- 只能在本机上使用用户登录数据库
create user 'zhangsan'@'localhost' identified by '123456';
-- 可以在任意主机使用用户登录数据库
create user 'lisi'@'%' identified by '123456';
2. 删除用户:
* 语法:
-- 删除用户
drop user '用户名'@'主机名';
案列:
drop user 'zhangsan'@'localhost';
3. 修改用户密码:
* 语法:
1. 老版本的改密码方式:
update user set password = password("新密码") where user = "用户名";
案列:
-- lisi的密码修改为abc
update user set password = password("abc") where user = "lisi";
set password for "用户名"@"主机名" = password("新密码");
案列:
-- lisi的密码修改为abc
set password for "lisi"@"%" = password("abc");
2. 新版本修改密码的方式:
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
案列:
-- lisi的密码修改为abc
ALTER USER 'lisi'@'%' IDENTIFIED WITH mysql_native_password BY 'abc';
* mysql中忘记了root用户密码?
0. 打开终端
1. 关闭mysql服务器
* linux: service mysql stop
* mac: mysql.server stop
* window: net stop mysql
2. 使用无验证方式启动mysql服务:
mysqld --skip-grant-tables
3. 在开一个终端直接连接mysql服务:
mysql (直接输入mysql, 不需要用户名和密码)
4. 切换到mysql表:
use mysql
5. 修改root用户密码:
1. 老版本的改密码方式:
update user set password = password("新密码") where user = "用户名";
案列:
-- lisi的密码修改为abc
update user set password = password("abc") where user = "root";
set password for "用户名"@"主机名" = password("新密码");
案列:
set password for "root"@"localhost" = password("abc");
2. 新版本修改密码的方式:
* 注意新版本在mysqld --skip-grant-tables服务下进入的mysql, 在修改root用户前需要先执行:
flush privileges;
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
案列:
-- lisi的密码修改为abc
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'abc';
6. 查看mysqld进程, 杀死进程
1. mac:
ps -a | grep mysqld
kill -9 mysqld的PID
2. linux:
ps -aux | grep mysqld
kill -9 mysqld的PID
3. window:
1. 打开任务管理器
2. 找到mysqld进程, 结束进程
7. 开启mysql服务:
1. mac:
mysql.server start
2. linux:
service mysql start
3. window:
net start mysql
8. 用新密码登入mysql
mysql -uroot -pabc;
4. 查询用户:
-- 1. 切换到mysql数据库
use mysql;
-- 2. 查询user表
select * from user;
* 通配符: % 表示可以在任意主机使用用户登录数据库
2. 权限管理:
1. 查询权限:
-- 查询权限
show grants for "用户名"@"主机名";
-- 查询lisi权限
show grants for "lisi"@"%";
-- 查询root权限
show grants for "root"@"localhost";
2. 授予权限:
-- 授予权限
grant 权限列表 on 数据库.表名 to "用户名"@"主机名";
案列:
-- 给lisi授予查询的权限
grant select on test3.account to "lisi"@"%";
-- 给lisi授予查询, 删除的权限
grant select, delete on test3.account to "lisi"@"%";
-- 给张三用户授予所有权限, 在任意数据库任意表上-->(all代表所有权限 *.*代表所有数据库下的所有表)
grant all on *.* to "zhangsan"@"localhost";
3. 撤销权限:
-- 撤销权限:
revoke 权限列表 on 数据库.表名 from "用户名"@"主机名";
案列:
-- 撤销lisi的删除权限
revoke delete on test3.account from "lisi"@"%";
-- 撤销lisi的查询和删除权限
revoke select, delete on test3.account from "lisi"@"%";
-- 撤销张三的所有权限-->(all代表所有权限 *.*代表所有数据库下的所有表)
revoke all on *.* from "zhangsan"@"localhost";
6228
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
