文章详情

短信预约信息系统项目管理师 报名、考试、查分时间动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MySQL-基础语法

2022-01-22 13:04

关注

MySQL-基础语法

MySQL客户端连接,系统自带的命令行工具执行指令: mysql [-h 127.0.0.1] [-P 3306] -u root -p

SQL

SQL通用语法

[ ext{SQL分类} egin{cases} ext{DDL} o 数据定义语言,用来定义数据库对象(数据库,表,字段) \ ext{DML} o 数据操作语言,用来对数据库表中的数据进行增删改 \ ext{DQL} o 数据查询语言,用来查询数据库中表的记录 \ ext{DCL} o 数据控制语言,用来创建数据库用户/控制数据库的访问权限 end{cases} ]

DDL

DDL-数据库操作 具体内容 SQL语句
查询 查询所有数据库 show databases;
查询当前数据库 select database();
创建 创建数据库 create database [if not exists] 数据库名 [default charset 字符集] [collate 排序规则];
删除 删除数据库 drop database [if exists] 数据库名;
使用 使用/切换数据库 use 数据库名;
DDL-表操作 具体内容 SQL语句
查询 查询当前数据库所有表
查询表结构 desc 表名;
查询指定表的建表语句 show create table 表名;
创建 创建表 create table 表名(字段名 字段类型,字段名 字段类型);
修改 添加字段 alter table 表名 add 字段名 数据类型(长度) [COMMENT 注释] [约束];
修改指定字段的数据类型 alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型 alter table 表名 change 旧字段名 新字段名 新数据类型(长度) [COMMENT 注释] [约束];
删除指定字段 alter table 表名 drop 字段名;
重命名 修改表名 alter table 表名 rename to 新表名;
删除 删除指定表 drop table [if exists] 表名;
删除指定表,并重新创建该表 truncate table 表名;
create table 表名(
    字段名1 字段1类型[COMMENT 字段1注释],
    字段名2 字段2类型[COMMENT 字段2注释],
    ……
    字段名n 字段n类型[COMMENT 字段n注释]
)[COMMENT 表注释];

MySQL中的数据类型有很多,主要分为三类:数值类型、字符串类型、日期时间类型

数值类型
类型 大小/字节 描述 有符号范围(SIGNED) 无符号范围(UNSIGNED)
tinyint 1 小整数值 (-128 , 127) (0 , 255)
smallint 2 大整数值 (-32768 , 32767) (0 , 65535)
mediumint 3 大整数值 (-8388608 , 8388607) (0 , 16777215)
int或integer 4 大整数值 (-2147483648 , 2147483647) (0 , 4294967295)
bigint 8 极大整数值 (-2^63 , 2^63 -1) (0 , 2^64 -1)
float 4 单精度浮点数 (-3.402823466 E+38 , 3.402823466351 E+38) 0和(1.175494351 E-38,3.402823466 E+38)
double 8 双精度浮点数 (-1.7976931348623157 E+308 , 1.7976931348623157 E+308) 0和(2.2250738585072014 E-308 , 1.7976931348623157 E+308)
decimal 小数值(精确定点数) 依赖于M(精度)和D(标度)的值 依赖于M(精度)和D(标度)的值
字符串类型
类型 大小/字节 描述
char 0-255 定长字符串
varchar 0-65535 变长字符串
tinyblob 0-255 不超过255个字符的二进制数据
tinytext 0-255 短文本字符串
blob 0-65535 二进制形式的长文本数据
text 0-255 长文本数据
mediumblob 0-65535 二进制形式的中等长度文本数据
mediumtext 0-16777215 中等长度文本数据
longblob 0-16777215 二进制形式的极大文本数据
longtext 0-4294967295 极大文本数据
日期类型
类型 大小/字节 范围 格式 描述
date 3 1000-01-01 至 9999-12-31 YYYY-MM-DD 日期值
time 3 -838:59:59 至 838:59:59 HH:MM:SS 时间值或持续时间
year 1 1901 至 2155 YYYY 年份值
datetime 8 1000-01-01 00:00:00 至 9999-12-31 23:59:59 YYYY-MM-DD HH:MM:SS 混合日期和时间值
timestamp 4 1970-01-01 00:00:01 至 2038-01-19 03:14:07 YYYY-MM-DD HH:MM:SS 混合日期和时间值,时间戳

DML

DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增删改操作

DML操作分类 具体内容 SQL语句
添加数据 给指定字段添加数据 insert into 表名(字段名1,字段名2,…) values (值1,值2,…);
给全部字段添加数据 insert into 表名 values (值1,值2,…);
批量添加数据 insert into 表名(字段名1,字段名2,…) values (值1,值2,…),(值1,值2,…),(值1,值2,…);
批量添加数据 insert into 表名 values (值1,值2,…),(值1,值2,…),(值1,值2,…);
修改数据 修改指定字段的数据 update 表名 set 字段名1=值1,字段名2=值2 [where 条件];
修改整张表的数据 update 表名 set 字段名1=值1,字段名2=值2;
删除某个字段的值 update 表名 set 字段名=null;
删除数据 删除指定字段的数据 delete from 表名 [where 条件];
删除整张表所有数据 delete from 表名;

DQL

DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录
语法:select 字段列表 from 表名 where 条件列表;

[{Large ext{DQL查询分类} egin{cases} ext{基本查询} \ ext{条件查询(where)} \ ext{聚合函数(count/max/main/sum/avg)} \ ext{分组查询(group by)} \ ext{排序查询(order by)} \ ext{分页查询(limit)} \ end{cases}} ]
select 字段列表
from 表名列表
where 条件列表
group by 分组字段列表
having 分组后条件列表
order by 排序字段列表
limit 分页参数

DQL执行顺序:from → where → group by → select → order by → limit

DQL基本查询 SQL语句
查询多个字段的数据 select 字段1,字段2 from 表名;
查询全部字段的数据 select * from 表名;
设置别名 select 字段1 [as] 别名1,字段2 [as] 别名2 from 表名;
去除重复记录 select distinct 字段名 from 表名;
条件查询的运算符
条件列表 说明
> 大于
>= 大于等于
< 小于
<= 小于等于
= 等于
<> 或 != 不等于
between…and… 在某个范围之内(含最小,最大值)
in(…) 在in之后的列表中的值,多选一
like 占位符 模糊匹配(_匹配单个字符,%匹配任意个字符)
is null 或 is not null 是空值null / 不是空值
and 或 && 并且(多个条件同时成立)
or 或 || 或者(多个条件任意一个成立)
not 或 ! 非,不是

DQL聚合查询:将一列数据作为一个整体,进行纵向计算.语法:select 聚合函数(字段列表) from 表名;
注意:null值不参与所有聚合函数运算

[{Large 常见的聚合函数 egin{cases} ext{count} o 统计数量 \ ext{max} o 最大值 \ ext{min} o 最小值 \ ext{sum} o 求和 \ ext{avg} o 平均值 end{cases}} ]
具体操作 语法
统计指定表的记录总量 select count(*) from 表名;
统计指定表的指定字段记录的总数量 select count(指定字段) from 表名;
统计指定表的指定字段的最大值 select max(指定字段) from 表名;
统计指定表的指定字段的最小值 select min(指定字段) from 表名;
统计指定表的指定字段的求和 select sum(指定字段) from 表名;
统计指定表的指定字段的平均值 select avg(指定字段) from 表名;

分组查询: select 字段列表 表名 [where 条件] group by 分组字段名 [having 分组后的过滤条件];

执行顺序:

  1. where是分组之前进行过滤,不满足where条件,不参与分组
  2. 执行聚合函数
  3. having是分组之后对结果进行过滤

注意:where不能对聚合函数进行判断,而having可以;分组之后查询的字段一般为聚合函数和分组字段,查询其他字段无任何意义

-- 查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select workaddress,count(*) from emp where age<45 group by workaddress having count(*)>=3;
select workaddress,count(*) address from emp where age<45 group by workaddress having address>=3;

排序查询: select 字段列表 from 表名 order by 字段名1 排序方式,字段名2 排序方式;

排序方式有两种:升序ASC(默认值);降序desc

注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序

分页查询: select 字段列表 from 表名 limit 起始索引,查询记录数;

-- 查询第一页的员工数据,每页展示10条记录
select * from emp limit 0,10;
select * from emp limit 10;
-- 查询第二页的员工数据,每页展示10条记录
select * from emp limit 10,10;

注意

DCL

DCL英文全称是Data ControlLanguage(数据控制语言),用来管理数据库用户,控制数据库的访问权限

DCL-管理用户 SQL语法
查询用户 use mysql;
select * from user;
创建用户 create user "用户名"@"主机名" identified by "密码";
修改用户密码 alter user "用户名"@"主机名" identified with mysql_native_password by "新密码";
删除用户 drop user "用户名"@"主机名";

主机名可以使用 % 通配

-- 创建用户zhangsan,只能在当前主机localhost访问,密码为123456
create user "zhangsan"@"localhost" identified by "123456";
-- 创建用户lisi,可以在任意主机访问该数据库,密码为123456
create user "lisi"@"%" identified by "123456";
-- 修改用户lisi的访问密码为1234
alter user "lisi"@"%" identified with mysql_native_password by "123456";
-- 删除zhangsan@localhost用户
drop user "zhangsan"@"localhost";

MySQL中定义了很多种权限,但是常用的就以下几种:

权限 说明
ALL, ALL PRIVILEGES 所有权限
SELECT 查询数据
INSERT 插入数据
UPDATE 修改数据
DELETE 删除数据
ALTER 修改表
DROP 删除数据库/表/视图
CREATE 创建数据库/表

注意:

DCL-权限控制 SQL语法
查询权限 show grants for "用户名"@"主机名";
授予权限 grant 权限列表 on 数据库名.表名 to "用户名"@"主机名";
撤销权限 revoke 权限列表 on 数据库名.表名 from "用户名"@"主机名";
-- 查询权限
show grants for "lisi"@"%";
-- 授予权限
grant all on *.* to "lisi"@"%";
-- 撤销权限
revoke all on test.* from "lisi"@"%";

函数

函数是指一段可以直接被另一段程序调用的程序或代码

使用函数: select 函数(参数);

字符串函数
函数 说明
concat(str1,str2,…) 字符串拼接,将str1/str2等拼接成一个字符串
lower(str) 将字符串str全部转为小写
upper(str) 将字符串str全部转为大写
lpad(str,n,pad) 左填充;用字符串pad对str的左边进行填充,达到n个字符串长度
rpad(str,n,pad) 右填充;用字符串pad对str的右边进行填充,达到n个字符串长度
trim(str) 去掉字符串头部和尾部的空格
substring(str,start,len) 返回从字符串str自start位置起的len个长度的字符串
数值函数
函数 说明
ceil(x) 向上取整
floor(x) 向下取整
mod(x,y) 返回x/y的模
rand() 返回0-1内的随机数
round(x,y) 求参数x的四舍五入的值,保留y位小数
日期函数
函数 说明
curdate() 返回当前日期
curtime() 返回当前时间
now() 返回当前日期和时间
year(date) 获取指定date的年份
month(date) 获取指定date的月份
day(date) 获取指定date的日期
datediff(date1,date2) 返回起始时间date1和结束时间date2之间的天数(date1-date2)
date_add(date,interval expr type) 返回一个日期值加上一个时间间隔expr后的时间值
流程函数
函数 说明
if(value,t,f) 如果value为true,则返回t,否则返回f
ifnull(value1,value2) 如果value1不为空,返回value1,否则返回value2
case when [val1] then [res1] … else [default] end 如果val1为true,返回res1,…否则返回default默认值
case [expr] when [val1] then [res1] … else [default] end 如果expr的值等于val1,返回res1,…否则返回default默认值
点击查看代码
-- 生成一个六位数的随机验证码
select lpad(round(rand()*1000000,0),6,"0");

select curdate();
select curtime();
select now();
select year(now());
select month(now());
select day(now());
select date_add(now(),interval 70 day);
select datediff("2022-12-01","2022-11-20");

select if(true,"success","error"); -- success
select if(false,"success","error"); -- error
select ifnull("str","default"); -- str
select ifnull("","default"); -- 空
select ifnull(null,"default"); -- default


-- case when then else end
-- 需求:查询emp表的员工姓名和工作地址(北京/上海——一线城市,其他——二线城市)
select
       name,
       ( case workaddress when "北京" then "一线城市" when "上海" then "一线城市" else "二线城市" end) as "工作地址"
from emp;


select
       id,
       name,
       (case when math >= 85 then "优秀" when math >= 60 then "及格" else "不及格" end)       "数学",
       (case when chinese >= 85 then "优秀" when chinese >= 60 then "及格" else "不及格" end) "语文",
       (case when english >= 85 then "优秀" when english >= 60 then "及格" else "不及格" end) "英语"
from score;

约束

约束是作用于表中字段上的规则,用于限制存储在表中的数据;从而保证数据库中数据的正确、有效性和完整性

约束 说明 关键字
主键约束 主键是一行数据的唯一标识,要求非空且唯一 primary key
外键约束 用来让两张表的数据之间建立连接,保证数据的一致性和完整性 foreign key
非空约束 限制该字段的数据不能为null not null
唯一约束 保证该字段的所有数据都是唯一/不重复的 unique
默认约束 保存数据时,如果未指定该字段的值,则采用默认值 default
检查约束 保证字段值满足一个条件 check
create table user(
    id int primary key auto_increment comment "主键且自动增长",
    name varchar(10) not null unique comment "姓名",
    age int check ( age > 0 && age <= 120 ) comment "年龄",
    status char(1) default "1" comment "状态",
    gender char(1) comment "性别"
) comment "用户表";

外键约束语法

-- 添加外键:创建表的同时添加外键
create table 表名(
    字段名 数据类型,
    …
    [constraint] [外键名称] foreign key (外键字段名) references 主表(主表列名)
);

-- 添加外键
alter table 表名 add constraint 外键名称 foreign key (外键字段名) references 主表(主表列名);

-- 添加外键的同时指定删除或更新的行为
alter table 表名 add constraint 外键名称 foreign key(外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

-- 删除外键
alter table 表名 drop foreign key 外键名称;
删除/更新行为 说明
no action 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与restrict一致)
restrict 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有则不允许删除/更新(与no action一致)
cascade 当在父表中删除/更新对应记录时,首先检查该记录是否有对应外键,如果有,则也删除/更新外键在子表中的记录
set null 当在父表中删除对应记录时,首先检查该记录是否有对应外键,如果有则设置子表中该外键值为null(这就要求该外键允许取null)
set default 父表有变更时,子表将外键列设置成一个默认的值(Innodb不支持)

多表查询

多表关系

笛卡尔积:笛卡尔乘积是指在数学中,两个集合A集合和B集合的所有组合情况。(在多表查询时,需要消除无效的笛卡尔积)

连接分类:

多表连接分类 具体分类 SQL语法
内连接(两张表交集部分) 隐式内连接 select 字段列表 from 表1,表2 where 条件;
显式内连接 select 字段列表 from 表1 [inner] join 表2 on 连接条件;
外连接 左外连接 select 字段列表 from 表1 left [outer] join 表2 on 条件;
右外连接 select 字段列表 from 表1 right [outer] join 表2 on 条件;
自连接 select 字段列表 from 表A 别名A join 表A 别名B on 条件;
联合查询 select 字段列表 from 表A
union [all]
select 字段列表 from 表B;
子查询/嵌套查询 select * from t1 where column1 = (select column1 from t2);
子查询的代码案例
-- 标量子查询
-- 需求1:查询"销售部"的所有员工信息
select * from emp where dept_id = (select id from dept where name = "销售部");
-- 需求2:查询指定入职日期之后入职的员工信息
select * from emp where entrydate > (select entrydate from emp where name="张三");

-- 列子查询
-- 需求1:查询销售部和市场部的部门所有员工信息
select * from emp where dept_id in (select id from dept where name="销售部" or name="市场部");
-- 需求2:查询比财务部所有人工资都高的员工信息
select * from emp where salary > all (select salary from emp where dept_id = (select id from dept where name = "财务部"));
-- 需求3:查询比研发部其中任意一人工资高的员工信息
select * from emp where salary > any (select salary from emp where dept_id = (select id from dept where name = "研发部"));
select * from emp where salary > some (select salary from emp where dept_id = (select id from dept where name = "研发部"));

-- 行子查询
-- 需求:查询与"张三"的薪资及直属领导相同的员工信息
select * from emp where (salary,managerid) = (select salary,managerid from emp where name = "张三");

-- 表子查询
-- 需求1:查询与"张三","李四"的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job,salary from emp where name = "张三" or name = "李四");
-- 需求2:查询入职日期"2022-01-01" 之后的员工信息及其部门信息
select e.*,d.* from (select * from emp where entrydata > "2022-01-01" e left join dept d on e.dept_id = d.id);

事务

事务是一组操作的集合,它是一个不可分割的工作单位,事务会把所有的操作作为一个整体一起向系统提交或撤销操作请求,即这些操作要么同时成功,要么同时失败
默认MySQL的事务是自动提交的,也就是说,当执行一条DML语句,MySQL会立即隐式的提交事务

事务操作 SQL语法
查看事务提交方式(1为自动提交;0为手动提交) select @@autocommit;
设置事务提交方式 set @@autocommit=0;
提交事务 commit;
回滚事务 rollback;
开启事务 start transaction; 或 begin;

事务四大特性

事务四大特性

并发事务问题 描述
脏读 一个事务读到另外一个事务还没有提交的数据
不可重复读 一个事务先后读取同一条记录,但两次读取的数据不同,称之为不可重复读
幻读 一个事务按照条件查询数据时,没有对应的数据行,但是在插入数据时,又发现这行数据已经存在,好像出现了幻影
事务隔离级别 脏读 不可重复读 幻读
read uncommitted 存在 存在 存在
read committed(oracle默认) 解决了 存在 存在
repeatable read(MySQL默认) 解决了 解决了 存在
serializable 解决了 解决了 解决了

注意:事务隔离级别越高,数据越安全,但是性能越低

-- 查看事务隔离级别
select @@transaction_isolation;

-- 设置事务隔离级别(session是当前会话有效;global是全部会话都有效)
set [session|global] transaction isolation level {read uncommitted | read committed | repeatable read | serializable};

本文来自云海天,作者:不二橘子酱,转载请注明原文链接:https://www.cnblogs.com/marmaladeHY/p/15824658.html

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯