本文详细介绍了MySQL的基础语法，包括登录、数据类型、DDL操作、DML语句、DQL查询、DCL控制，以及函数、约束、多表查询、事务处理等内容，适合初学者快速上手。
mysql入门
提示：sql关键字不区分大小写，但建议使用大写，为了更好的可读性，本文全部采用小写
1. sql基本语法
结构：数据库服务器—多个数据库–每个数据库又有多个数据表----每个表又有多个字段
1.1 登录数据库服务器
mysql -uroot -p密码
如果提示'mysql' 不是内部或外部命令，也不是可运行的程序 或批处理文件。
参考：配置环境变量
登录成功
1.2 数据类型
- 数值类型
SIGNED(signed) UNSIGNED(unsigned)
TINYINT 1byte 小整数值 (-128，127) (0，255)
SMALLINT 2byte 大整数值 (-32 768，32 767) (0，65 535)
MEDIUMINT 3byte 大整数值 (-8 388 608，8 388 607) (0，16 777 215)
INT 4byte 大整数值 (-2 147 483 648，2 147 483 647) (0，4 294 967 295)
BIGINT 8byte 极大整数值 (-9,223,372,036,854,775,808，9 223 372 036 854 775 807) (0，18 446 744 073 709 551 615)
FLOAT 4byte 单精度浮点数值
DOUBLE 8byte 双精度浮点数值
DECIMAL 依赖于精度M和标度D的值 123456.789 精度M为9，标度为3
在评估用哪种整数类型的时候，你需要考虑 存储空间 和 可靠性 的平衡问题：一方 面，用占用字节数少的整数类型可以节省存储空间；另一方面，要是为了节省存储空间， 使用的整数类型取值范围太小，一旦遇到超出取值范围的情况，就可能引起 系统错误 ，影响可靠性。举个例子，商品编号采用的数据类型是 INT。原因就在于，客户门店中流通的商品种类较多，而且，每天都有旧商品下架，新商品上架，这样不断迭代，日积月累。如果使用 SMALLINT 类型，虽然占用字节数比 INT 类型的整数少，但是却不能保证数据不会超出范围65535。相反，使用 INT，就能确保有足够大的取值范围，不用担心数据超出范围影响可靠性的问题。你要注意的是，在实际工作中，系统故障产生的成本远远超过增加几个字段存储空间所产生的成本。因此，我建议你首先确保数据不会超过取值范围，在这个前提之下，再去考虑如何节省存储空间
- 字符串类型
CHAR 0-255 bytes 定长字符串
VARCHAR 0-65535 bytes 变长字符串
TINYBLOB 0-255 bytes 不超过 255 个字符的二进制字符串
BLOB 0-65 535 bytes 二进制形式的长文本数据
MEDIUMBLOB 0-16 777 215 bytes 二进制形式的中等长度文本数据
LONGBLOB 0-4 294 967 295 bytes 二进制形式的极大文本数据
TINYTEXT 0-255 bytes 短文本字符串
TEXT 0-65 535 bytes 长文本数据
MEDIUMTEXT 0-16 777 215 bytes 中等长度文本数据
LONGTEXT 0-4 294 967 295 bytes 极大文本数据
- 时间类型
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:00/2038
结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07
YYYYMMDD HHMMSS 混合日期和时间值，时间戳
1.3 DDL语法（用于定义数据库对象-数据库，表，字段）
1.3.1 对数据库相关的操作
-查看所有数据库-
show databases；
-选择某个数据库-
use test01;
-显示当前连接数据库-
select database();
-创建一个新的数据库-
create database if not exists test02;
-删除一个数据库-
drop database if exists test02;
1.3.2 对表相关的操作
-查看数据库下的所有表-
show tables；
-查询指定表的表结构-
desc users;
-创建表-
/*
create table 表名(
字段1 字段类型 [comment 注释],
字段2 字段类型 [comment 注释]
)[comment 表注释];
*/
create table department(
id int comment '唯一表示',
name varchar(50) comment '部门名称'
)comment '部门表';
-查询指定表的建表语句-
show create table department;
-删除指定表-
drop table department;
-给表中添加字段-
alter table department add manager varchar(10) comment '部门经理';
-修改表中字段的数据类型-
alter table department modify manager varchar(11);
-修改表中字段名，并修改数据类型-
alter table department change name dep_name varchar(25);
-删除表中字段-
alter table department drop nikename;
-修改表名-
alter table department rename to department1;
1.4 DML语句（对表中数据，增删改）
1.4.1 添加数据（INSERT）
-给指定字段添加数据-
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,值3),(值1,值2,值3)
1.4.2 修改数据（UPDATE）
update 表名 set 字段1=值1,字段2=值2,...[where 条件]
1.4.3 删除数据（DELETE）
delete from 表名 where 条件
1.5 DQL语言（数据查询语言）
- 基本查询 --------------
selset - 条件查询 --------------
where - 聚合函数 --------------
count max min avg sum - 分组查询 --------------
group by - 排序查询 --------------
order by - 分页查询 --------------
limit
1.5.1 基本查询
select * from 表名
select 字段1, 字段2, from 表名
# 设置表名
select 字段1 as 别名1, 字段2 as 别名2 from 表名
# 去除重复记录
select distinct 字段 from 表名
1.5.2 条件查询
select 字段 from 表名 where 条件
# 模糊匹配
select * from users where name like '_三'
select * from users where name like '%三'
select * from users where between 20 and 40
select * from users where age in(10, 20, 30)
1.5.3 聚合函数
将一列数据作为一个整体，进行纵向计算
# 统计数据总数量（null不做聚合运算）
select count(*) from users
# 计算平均数 avg
select avg(age) from uers
# 计算最大值
select max(age) from uers
# 计算最小值
select min(age) from uers
# 计算合
select sum(age) from uers
1.5.4 分组查询
select 字段 from 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
select count(*) from users group by sex
SELECT sex, COUNT(*) FROM users GROUP BY sex
select sex, avg(age) from users group by sex
1.5.5 排序查询
select * from users order by sex desc(降序), id asc(升序)
1.5.6 分页查询
select * from limit 起始索引, 查询记录数
# 起始索引 =（要查询的页码 - 1） * 查询记录数
1.6 DCL 数据控制语言
# 查询用户
use mysql
select * from user
# 创建用户
create user 'lhw'@'localhost' identified by 'lhw'
# 创建用户，在任意主机上都能访问
select user 'lhw_all'@'%' identified by 'lhw'
# 修改用户密码
alter user 'lhw'@'localhost' identified with mysql_native_password by '123456'
# 删除用户
drop user 'lhw'@'%'
# 查询权限
show grants for '用户名'@'主机名'
# 新增权限
grant 权限列表(所有权限all) on 数据库名.表名(数据库.* --- 数据库下所有表) to '用户名'@'主机名'
# 撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名'
2 函数
2.1 字符串函数
concat(a1, a2, a3, ...) --------------- 字符串拼接
lower(str) --------------- 大写转小写
upper(str) --------------- 小写转大写
lpad(str, n, pad) --------------- 左填充，用字符串pad对str进行填充，达到n个字符串长度
rpad(str, n, pad) --------------- 右填充，用字符串pad对str进行填充，达到n个字符串长度
trim(str) --------------- 去除字符串头尾空格
substring(str, trat, length) --------------- 返回字符串str从start位置开始起，长度为length的字符串
select concat('hello', ' mysql')
select upper('hello')
select lower('MYSQL')
select lpad('love', 10, '哈哈')
select rpad('love', 10, '哈哈')
select trim(' hello world ')
select substring('hello world', 1, 5)
# 对工号进行补位，不足五位补零
update users set work_no = lpad(work_no, 5, '0')
2.2 数值函数
ceil(x) --------------- 向上取整
floor(x) --------------- 向下取整
mod(x, y) --------------- 返回x/y的模
rand() --------------- 返回0-1的随机数
round(x, y) --------------- 对x进行四舍五入，保留y位小数
# 生成6位随机验证码
select rpad(floor(rand() * 1000000), 6, '0')
2.3 日期函数
curdate() --------------- 获取当前日期
curtime() --------------- 获取当前时间
now() --------------- 获取当前日期和时间
year(date) --------------- 获取指定date 的 年份
month(date) --------------- 获取指定date 的 月份
day(date) --------------- 获取指定date 的 日期
datediff(date1, date2) --------------- 返回时间差
date_add(date, interval expr type)) --------------- 返回日期加上间隔时间后的日期
select concat(curdate(), ' ', curtime())
# 2022-07-19 16:49:26
select now()
# 2022-07-19 16:49:38
select year(now())
# 2022
select month(now())
# 7
select day(now())
# 19
select datediff(19920727, now())
# 10949
select date_add(now(), interval 50 year)
# 2072-07-19 16:56:54
2.4 流程函数
# if
select if(true, 'success', 'error')
# ifnull
select ifnull('success', 'error')
# case xx when xx then else xx end
select name, case sex when '男' then '帅哥' when '女' then '美女' else '不男不女' end from users;
3 约束
CREATE TABLE teacher(
id int PRIMARY KEY AUTO_INCREMENT AUTO_INCREMENT COMMENT '主键',
name VARCHAR(10) NOT NULL UNIQUE COMMENT '姓名',
age INT CHECK( age > 0 AND age <= 120) COMMENT '年龄',
status CHAr(1) DEFAULT '1' COMMENT '状态',
sex CHAR(1) COMMENT '性别'
)
3.1 外键约束
# 外键约束名称--fk_teacher_dept_id
# 主表--dept
# 从表--teacher
# 外键名-- dept_id
alter table teacher add constraint fk_teacher_dept_id foreign key (dept_id) references dept(id)
create table person(
nid int not null auto_increment primary key,
part_nid int,
constraint fk_teacher_dept_id foreign key (dept_id) references dept(id)
)
# 删除外键
alter table teacher drop foreign key fk_teacher_dept_id
alter table teacher add constraint fk_teacher_dept_id foreign key (dept_id) references dept(id) on update cascade on delete cascade
alter table teacher add constraint fk_teacher_dept_id foreign key (dept_id) references dept(id) on update set null on delete set null
4 多表查询
4.1 内连接
查询两个表交集的部分
# 隐式内连接
select * from 表名1, 表名2 where 条件
# 显示内连接
select * from 表名1 [inner] join 表名2 on 条件
select users.name, department.name from users, department
where users.dept_id = department.id;
select * from users inner join department on users.dept_id = department.id
4.2 外连接
# 左外连接
select * from 表名1 left [outer] join 表名2 on 条件
select users.name, department.name from users
left outer join department on users.dept_id = department.id;
# 右外连接
select * from 表名1 right [outer] join 表名2 on 条件
select department.name, users.name from users
right outer join department on users.dept_id = department.id
4.3 自连接
select * from 表名1 as 别名A join 表名1 as 别名B on 条件
select a.name, b.name from users as a, users as b where a.id = b.man_id
select a.name, b.name from users a inner join users b on a.id = b.man_id
select a.name, b.name from users a left outer join on a.id = b.man_id
4.4 联合查询
union 和union all 联合查询
- union会对合并的数据进行去重
- union all 会直接合并数据，不会去重。
select * from 表A union select * from 表B
select * from users where age> 30
union
select * from users where sex = '男'
order by id
等价于
select * from users where age>30 or sex='男'
4.5 子查询
select * from 表A where column1 = ( select column1 from 表2)
子查询外部的语句可以是 insert / update / delete / select 中的任何一个
4.5.1 标量子查询
子查询结果为一个单值
select * from users where dept_id = (select id from department where name = '开发部')
# 练习
select users.*, department.name from users
inner join department on users.dept_id = department.id
where users.dept_id = (select id from department WHERE name = '开发部')
order by age
4.5.2 列子查询
子查询的结果为一列
select * from users where dept_id in (select id from department where name = '开发部' or name = '人事部');
# 练习
select * from users
inner join department on users.dept_id = department.id
where dept_id in (select id from department where name = '开发部' or name = '人事部');
4.5.3 行子查询
子查询的结果为一行
select * from users where (age, dept_id) = (select age, dept_id from users where name = '小王')
4.5.4 表子查询
子查询的结果为多行多列
select * from users where (age, dept_id) = (select age, dept_id from users where name = '小王')
5 事务
事务是一组操作的集合，他是一个不可分割的工作单位。事务会把所有的操作当做一个整体一起想系统提交或者撤销操作请求。要么同时成功，要么同时失败
5.1 事务操作
5.1.1 方式一
# 查看/设置事务提交方式
select @@autocommit
# 1是自动提交 0是关闭自动提交
set @@autocommit = 0
# 提交事务
commit
# 回滚事务
rollback
完整流程
# 查看提交方式
select @@autocommit;
# 将提交方式改为手动提交
set @@autocommit = 0;
# 业务sql
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四'
# 手动提交
commit
# 回滚事务
rollback
5.1.2 方式二
# 开启事务
start transaction 或 begin
# 提交事务
commit
# 回滚事务
rollback
完整流程
tart transaction;
select * from account where name = '张三';
update account set money = money - 1000 where name = '张三';
update account set money = money + 1000 where name = '李四';
commit;
rollback;
5.2 事务的特性
- 原子性：事务是不可分割的最小操作单元，要么全部成功，要么全部失败。
- 一致性：事务完成时，必须使用所有的数据都保持一致状态。
- 隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。
- 持久性：事务一旦达成或者回滚，他对数据库中的数据的改变就是永久的。
5.3 并发事务引发的问题
- 脏读：一个事务读到另外一个事务还没有提交的数据
- 可不重复读： 一个事务先后读取同一条记录，但是两次读取的数据不同，称之为不可重复读
- 幻读： 一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了幻影。
5.4 事务的隔离级别
# 查看事务隔离级别
select @@transaction_isolation
# 设置事务隔离级别
set [session | global] transaction isolation level [ read uncommitted | read committed | repeatable read | serializable ]
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
