MySQL基础知识-刘宇

Brycen Liu

已于 2022-03-10 19:09:43 修改

阅读量460

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 MySQL基础

于 2021-12-27 14:23:46 首次发布

本文链接：https://blog.csdn.net/liuyu973971883/article/details/122066554

版权

MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

MySQL基础知识-刘宇

一、数据库简单介绍
二、数据库简单使用
三、DQL语言
四、DDL语言
五、DML语言
六、数据库事务

作者：刘宇
CSDN博客地址：https://blog.csdn.net/liuyu973971883
有部分资料参考，如有侵权，请联系删除。如有不正确的地方，烦请指正，谢谢。

一、数据库简单介绍

1.1、基本概念

DB：数据库（Database），存储数据的“仓库”。
DBMS：数据库管理系统（ Database Management System ），主要用来对数据库（DB）进行创建以及管理
SQL：结构化查询语言（ Structure Query Language ）专门用来与数据库通信的语言。

1.2、数据库存储数据的特点

1、数据存放到表中，表再放到库中
2、一个数据库容器中可以存放多张表，每张表具有唯一的表名用来标识自己
3、表由列组成，我们也称为字段，就和java中的属性差不多
4、表中的数据都是一行一行存储的，每一行都相当于java中的“对象”

1.3、MySQL的优点

成本低：开放源代码，一般可以免费试用
性能高：执行很快
简单：很容易安装和使用

二、数据库简单使用

2.1、MySQL登录和退出

#本地登录
mysql -u用户名 -p密码
#远程登录
mysql -h主机名 -P端口号 -u 用户名 -p密码

退出

exit 或 ctrl+C

2.2、MySQL的常见命令

查看当前MySQL中所有的数据库

show databases;

打开指定的库

use 数据库名称

查看当前库的所有表

show tables;

不切换库，查看其它库的所有表

show tables from 库名;

创建表

create table 表名(
	字段名1 类型,
	字段名2 类型，
	......
);

查看表结构

desc 表名;

查看服务器的版本

#成功登录进去，使用函数进行查询
select version();
#未登录
mysql --version
mysql --V

2.3、MySQL的语法规范

SQL语句可以单行或多行书写，以分号结尾；
可以用空格和缩进来来增强语句的可读性；
关键字不区别大小写，建议使用大写；
MySQL注释分为三种：
- 单行注释：#注释内容
- 单行注释：- 注释内容
- 多行注释：/* 注释内容 */

2.4、SQL的语言分类

DDL（Data Definition Language）：数据定义语言，用来定义数据库对象：库、表、列等；

如：create、drop、alter

DML（Data Manipulation Language）：数据操作语言，用来定义数据库记录（数据）；

如：insert、update、delete

DCL（Data Control Language）：数据控制语言，用来定义访问权限和安全级别；

如：create user ‘用户名’@’允许其登录的地址’ identified by ‘密码’;

DQL（Data Query Language）：数据查询语言，用来查询记录（数据）。

如：select

TCL（Transaction Control Language）：事务控制语言

如：commit、rollback

三、DQL语言

3.1、基本查询语句

查询单个字段

select 字段名 from 表名;

查询多个字段

select 字段名，字段名 from 表名;

查询所有字段

select * from 表名;

查询常量

# 注：字符型和日期型的常量需要用单引号括起来，数值类型的则不需要
select 常量值;

查询函数

# 如 select version()
select 函数名();

查询表达式

select 10/2;

给结果列起别名

select name as '别名' from user;
select name '别名' from user;
select name as 别名 from user;
select name 别名 from user;
# 以上起别名都是一样的效果，什么时候必须要加引号，如：别名中间有空格的时候是必须要加引号的，否则报错。

去重

select distinct 字段名 from 表名;

加法运算符

select 数值+数值;  # 直接运算
select 字符+数值;  # MySQL会先将字符根据编码转换成数值，如果转换成功，则相加；否则转换成0，再相加
select null+值;  # 结果都为null

concat函数拼接字符

select concat(字符1，字符2，字符3,...);

ifnull函数，判断是否为null

#如果commission_pct字段为null，则输出0，否则输出commission_pct本身的值
select ifnull(commission_pct,0) from employees;

isnull函数

#如果commission_pct字段为null，则输出1，否则输出0
select isnull(commission_pct) from employees;

3.2、条件查询语句

3.2.1、语法

select 查询列表 from 表名 where 筛选条件

例如：

# 查询工资>20000的员工姓名
select last_name,first_name from employees where salary > 20000;

执行顺序：

from子句
where子句
select子句

3.2.2、条件查询分类

按关系表达式筛选：

关系运算符：>、<、>=、<=、=、<>

案例1：查询部门编号不是100的员工

select * from employees where department_id<>100;

案例2：查询工资<15000的姓名、工资

select last_name,salary from employees where salary<15000;

按逻辑表达式筛选：

逻辑运算符：and、or、not

案例1：查询部门编号不是50~100之间的员工姓名、部门编号、邮箱

select last_name,department_id,email from employees where not(department_id>=50 and department_id<=100);

案例2：查询奖金率>0.03 或者员工编号在60~110之间的员工信息

select * from employees where commission_pct>0.03 or (department_id>=60 and department_id<=110);

模糊查询：

like：一般和通配符搭配使用，对字符型数据进行部分匹配查询**

案例1：查询姓名中包含a的员工信息

select * from employees where last_name like '%a%';

案例2：查询姓名中最后一个字符为e的员工信息

select * from employees where last_name like '%e';

案例3：查询姓名中第一个字符为e的员工信息

select * from employees where last_name like 'e%';

案例4：查询姓名中第三个字符为x的员工信息

select * from employees where last_name like '__x%';

案例5：查询姓名中第二个字符为_的员工信息

select * from employees where last_name like '_\_%';
select * from employees where last_name like '_$_%' escape '$';

in/not in：查询某字段的值是否属于指定的列表之内

案例1：查询部门编号是30、50、90的员工名、部门编号

select last_name,department_id from employees where department_id in(30,50,90);

案例2：查询工种编号不是ST_CLERK或IT_PROG的员工信息

select * from employees where job_id not in('ST_CLERK','IT_PROG');

between in/not between in：判断某个字段的值是否处于xx之间

案例1：查询部门编号是30~90的员工名、部门编号

select last_name,department_id from employees where department_id between 30 and 90;

案例2：查询部门编号是30~90的员工名、部门编号

select last_name,department_id from employees where department_id not between 30 and 90;

is null/is not null：判断某个字段的值是否为null

案例1：查询没有奖金的员工信息

select * from employees where commission_pct is null;

案例2：查询有奖金的员工信息

select * from employees where commission_pct is not null;

<=>：安全等于，既能判断普通内容，又能判断NULL值

案例1：查询没有奖金的员工信息

select * from employees where commission_pct <=> null;

案例2：查询工资等于10000的员工信息

select * from employees where salary <=> 10000;

3.3、排序查询语句

3.3.1、语法

select 查询列表 from 表名 [where 筛选条件] order by 排序列表

例如：

# 筛选工资>20000的员工姓名、工资，按照工资排序
select last_name,salary from employees where salary>20000 order by salary;

执行顺序：

from子句
where子句
select子句
order by子句

3.3.2、排序查询特点

排序列表可以是单个字段、多个字段、表达式、函数、别名、列的索引、以及以上的组合。
排序方式：升序：asc，默认；降序：desc
通常order by都是放在查询语句的最后的（除limit语句之外）

3.3.3、排序查询分类

按单个字段排序：

案例1：将员工编号>120的员工信息进行工资的升序

select * from employees where employee_id>120 order by salary;

案例2：将员工编号>120的员工信息进行工资的降序

select * from employees where employee_id>120 order by salary desc;

按表达式排序：

案例1：对有奖金的员工，按年薪降序

select * ,salary*12*(1+commission_pct) from employees where commission_pct is not null order by salary*12*(1+commission_pct) desc;
# 如果嫌上面的年薪表达式太长，我们可以使用别名
select * ,salary*12*(1+commission_pct) 年薪 from employees where commission_pct is not null order by 年薪 desc;

按函数排序：

案例1：按姓名的字数长度进行升序

select length(last_name),last_name from employees order by length(last_name);

按多个字段排序：

案例1：查询员工的姓名、工资、部门编号，先按工资升序，随后再按部门编号降序

select last_name, salary, department_id from employees order by salary asc, department_id desc;

3.4、函数调用

3.4.1、函数介绍

类似于java中学过的“方法”，为了解决某个问题，将编写的一系列的命令集合封装在一起，对外仅仅暴露一个方法名，供外部调用。

3.4.2、字符函数

拼接字符：

select concat('hello,', first_name,last_name) 备注 from employees;

获取字节长度：

select length('hello,刘宇');
# 输出：12

获取字符长度：

select char_length('hello,刘宇');
# 输出：8

截取字符：

# 使用substring和substr都可以
select substr('hello,刘宇',3,3);
# 输出：llo
select substr('hello,刘宇',7);
# 输出：刘宇

# 使用left和right截取
select left('hello,刘宇',3);
# 输出：hel
select right('hello,刘宇',2);
# 输出：刘宇

获取字符第一次出现的索引：

select instr('你好，刘宇是个大帅哥，刘宇太自恋了，沃日','刘宇');
# 输出：4

去除前后指定字符：

# 默认去除空格
select trim('   刘  宇     ');
# 输出：刘  宇

# 去除指定字符
select trim('x' from 'xxx刘xx宇xxxxxx');
# 输出：刘xxx宇

左/右填充：

# 左填充
select LPAD('刘宇',10,'a');
# 输出：aaaaaaaa刘宇

# 右填充
select RPAD('刘宇',10,'a');
# 输出：刘宇aaaaaaaa

大小写转换：

# 转大写
select UPPER('aAa');
# 输出：AAA

# 转小写
select LOWER('aAa');
# 输出：aaa

比较两个字符大小：

# 第一个比第二个大输出1，小的话就输出-1，相等就输出0
select strcmp('abc','aaa');
# 输出：1

3.4.3、数学函数

绝对值：

select ABS(-2.3);
# 输出：2.3

向上取整：

>=该参数的最小整数

select CEIL(1.09);
# 输出：2

向下取整：

<=该参数的最大整数

select CEIL(-1.09);
# 输出：-2

四舍五入：

select ROUND(1.97429);
# 输出：2

select ROUND(1.97429,2);
# 输出：1.97

保留几位小数：

select TRUNCATE(1.97429,1);
# 输出：1.9

select ROUND(1.97429,0);
# 输出：1

取余：

select MOD(-10,3);
# 输出：-1

3.4.4、日期函数

当前时间+时间：

select NOW();
# 输出：2021-12-22 21:12:33

当前日期：

select CURDATE();
# 输出：2021-12-22

当前时间：

select CURTIME();
# 输出：21:13:58

计算日期差：

select DATEDIFF('1997-10-23','1996-12-21');
# 输出：306

日期转换：

# DATE_FORMAT：将日期类型转换为字符串
select DATE_FORMAT('1997-10-23','%Y年%m月%d日 %H小时%i分钟%s秒');
# 输出：1997年10月23日 00小时00分钟00秒

# STR_TO_DATE：将日期类型转换为字符串
select STR_TO_DATE('10/23 1997','%m/%d %Y');
# 输出：1997-10-23

提取年、月、日、时、分、秒：

select year('1997-10-23')
# 输出：1997

select month('1997-10-23')
# 输出：10

select day('1997-10-23')
# 输出：23

select hour('1997-10-23 08:01:02')
# 输出：8

select minute('1997-10-23 08:01:02')
# 输出：1

select second('1997-10-23 08:01:02')
# 输出：2

3.4.5、流程控制函数

IF函数：

select IF(100>9,'true','false');
# 输出：true

CASE函数：

方式一：
CASE 表达式
WHEN 值1 THEN 结果1
WHEN 值2 THEN 结果2
…
ELSE 结果n
END

方式二：
CASE
WHEN 表达式 THEN 结果1
WHEN 表达式 THEN 结果2
…
ELSE 结果n
END

方式一案例：
部门标号是30，工资显示为2倍；部门标号是40，工资显示为3倍；部门标号是50，工资显示为4倍


select department_id,salary,
CASE department_id
WHEN 30 THEN salary*2
WHEN 40 THEN salary*3
WHEN 50 THEN salary*4
ELSE salary
END newSalary
FROM employees;

方式二案例：
如果工资大于20000，显示A；如果工资大于15000，显示B；如果工资大于10000，显示C；否则显示D

select salary,
CASE
WHEN salary>20000 THEN 'A'
WHEN salary>15000 THEN 'B'
WHEN salary>10000 THEN 'C'
ELSE 'D'
END grade
FROM employees;

3.4.6、聚合函数

聚合函数往往用于实现将一组数据进行统计计算，最终得到一个值。

求和：

# 求所有员工工资的总和
select sum(salary) from employees;

求平均数：

# 求平均工资
select avg(salary) from employees;

求最大值：

# 求最大工资
select max(salary) from employees;

求最小值：

# 求最小工资
select min(salary) from employees;

计算非空字段值的个数：

# 求拿到工资的员工数
select count(salary) from employees;

# 获取employees表的总行数
select count(*) from employees;
select count(1) from employees;

3.5、分组查询

3.5.1、语法

select 查询列表 from 表名 where 筛选条件 group by 分组列表 having 分组后筛选; order by 排序列表;

注：查询列表往往是分组函数和被分组的字段

执行顺序：

from子句
where子句
group by子句
having子句
select子句
order by子句

3.5.2、简单的分组

案例1：

查询每个工种的员工平均工资

select AVG(salary),job_id from employees group by job_id;

案例2：

查询每个领导的手下人数

select count(*),manager_id from employees where manager_id is not null group by manager_id;

3.5.2、分组的筛选

案例1：

查询邮箱中包含a字符的每个部门的最高工资

select max(salary) 最高工资,department_id from empl
oyees where email like '%a%' group by department_id;

案例2：

查询每个领导手下有奖金的员工的平均工资

select avg(salary) 平均工资,manager_id from empl
oyees where commission_pct is not null group by manager_id;

案例3：

查询哪个部门的员工个数>5

select count(*) 员工个数,department_id from employees where 员工个数>5 group by department_id;
# 报错，因为where的查询顺序在select前面

# 正确用法：使用having
select count(*) 员工个数,department_id from employees group by department_id having 员工个数>5;

案例4：

查询每个工种有奖金的员工的最高工资>12000的工种编号和最高工资

select job_id,max(salary) from employees where commission_pct is not null group by job_id having max(salary)>12000;

筛选对比：

前后筛选	使用关键字	筛选的表	位置
分组前筛选	where	原始表	group by的前面
分组后筛选	having	分组后的结果	group by 的后面

3.5.3、分组的排序

案例1：

查询没有奖金的员工的最高工资>6000的工种编号和最高工资，按最高工资升序

select job_id,max(salary) from employees where commission_pct is not null group by job_id having max(salary)>6000 order by max(salary) asc;

3.5.4、按多个字段分组

案例1：

查询每个工种每个部门的最低工资，并按最低工资降序
提示：工种和部门要一样成属于一组

select job_id,department_id,min(salary) from employees group by job_id,department_id order by min(salary) desc;

3.6、内连接

3.6.1、语法

SQL92内连接语法：

select 查询列表 from 表名1,表名2,表名3 where 等值连接的连接条件;

SQL99内连接语法：

注：其中inner可以省略

select 查询列表 from 表名1 别名 inner join 表名2 别名 on 等值连接的条件;

注：

为了解决多表中字段名重名的问题，往往为表起别名，提高语义性
表的顺序无要求

3.6.2、等值连接练习

简单的两表连接：

案例1：查询员工名和部门名

#SQL92内连接
select e.last_name,d.department_name from employees e,departments d where e.department_id = d.department_id;

#SQL99内连接
select e.last_name,d.department_name from employees e inner join departments d on e.department_id = d.department_id;

添加筛选条件：

案例1：查询部门编号>100的部门名所在的城市名

#SQL92内连接
select l.city,d.department_name from locations l,departments d where l.location_id = d.location_id and d.department_id>100;

#SQL99内连接
select l.city,d.department_name from locations l inner join departments d on l.location_id = d.location_id where d.department_id>100;

案例2：查询城市名中第二个字符为o的部门名和城市名

#SQL92内连接
select l.city,d.department_name from locations l,departments d where l.location_id = d.location_id and l.city like '__o%';

#SQL99内连接
select l.city,d.department_name from locations l inner join departments d on l.location_id = d.location_id where l.city like '__o%';

添加分组、筛选：

案例1：查询每个城市的部门个数

#SQL92内连接
select l.city,d.department_name from locations l,departments d where l.location_id = d.location_id group by l.city;

#SQL99内连接
select l.city,d.department_name from locations l inner join departments d on l.location_id = d.location_id group by l.city;

添加分组筛选、筛选、排序：

案例1：查询哪个部门的员工个数>5，并按员工个数进行降序

#SQL92内连接
select d.department_name,count(*) 员工个数 from employees e,departments d where e.department_id = d.department_id group by d.department_id having 员工个数>5 order by desc;

#SQL99内连接
select d.department_name,count(*) 员工个数 from employees e inner join departments d on e.department_id = d.department_id group by d.department_id having 员工个数>5 order by desc;

3.6.3、非等值连接练习

案例：

查询部门编号在10-90之间的员工的工资级别，并按级别进行分组

select count(*) 个数,g.grade from employees e join sal_grade g on e.salary between g.min_salary and g.max_salary where e.department_id between 10 and 90 group by g.grade;

3.6.4、自连接练习

案例：

查询员工名对应的领导名

select e.last_name,m.last_name from employees e join employees m on e.manager_id = m.employee_id;

3.7、外连接

将两张表连接起来，分为主表和从表，查询结果显示主表中的所有记录，如果从表有匹配项，则显示所有匹配项；如果从表没有匹配项，则显示null

注：

外连接分主从表，两表的顺序不能任意调换
左连接时，左边的表为主表
右连接时，右边的表为主表

3.7.1、语法

select 查询列表 from 表1 别名 left right [outer] join 表2 别名 on 连接条件 where 筛选条件;

3.7.2、练习

案例1：

查询所有女神记录，以及对应的男朋友的名字，如果没有对应的男朋友则显示为null

# 左连接
select b.*,bo.boyName from beauty b left join boys bo on b.boyfriend_id = bo.id;

# 右连接
select b.*,bo.boyName from boys bo right join beauty b on b.boyfriend_id = bo.id;

案例2：

查询哪个女神没有男朋友

# 左连接
select b.name from beauty b left join boys bo on b.boyfriend_id = bo.id where bo.id is null;

# 右连接
select b.name from boys bo left join beauty b on b.boyfriend_id = bo.id where bo.id is null;

3.8、内、外连接总结

在这里插入图片描述

3.9、子查询

当一个查询语句中又套嵌了另一个完整的select语句，则被套嵌的select语句称为子查询或内查询，外面的select语句称为主查询或外查询。

子查询放在条件中，要求必须放在条件的右侧
子查询一般放在小括号中
子查询的执行优先于主查询
单行子查询对应了单行操作符：> < >= <= = <>
多行子查询对应了多行子查询：any/some all in

注：

子查询不一定必须出现在select语句内部，只是出现在select语句内部的时候较多！

分类:

select后面：子查询的结果为单行单列（标量子查询）
from后面：子查询的结果可以为多行多列
where或having后面：子查询结果必须为单列，可以是单行或者多行
exists后面：子查询结果必须为单列（相关子查询）

3.9.1、单行子查询

案例1：

查询和Zlotkey相同部门的员工姓名和工资

select last_name,salary from employees where department_id = (select department_id from employees where last_name = 'Zlotkey');

案例2：

查询工资比公司平均工资高的员工的员工号，姓名和工资

select employee_id,last_name,salary from employees where salary > (select avg(salary) from employees);

3.9.2、多行子查询

案例1：

返回location_id是1400或1700的部门中的所有员工姓名

select last_name from employees where department_id in (select department_id from departments where location_id in (1400,1700));

案例2：

返回其他部门中比job_id为IT_PROG部门任意工资低的员工的员工号、姓名、job_id以及salary。

# 方式一
select emplyee_id,last_name,job_id,salary from employees where salary <any(select distinct(salary) from employees where job_id = 'IT_PROG');

#方式二
select emplyee_id,last_name,job_id,salary from employees where salary <(select max(salary) from employees where job_id = 'IT_PROG');

案例3：

返回其他部门中比job_id为IT_PROG部门所有工资都低的员工的员工号、姓名、job_id以及salary。

# 方式一
select emplyee_id,last_name,job_id,salary from employees where salary <all(select distinct(salary) from employees where job_id = 'IT_PROG');

# 方式二
select emplyee_id,last_name,job_id,salary from employees where salary <(select min(salary) from employees where job_id = 'IT_PROG');

3.9.3、select后的子查询

案例1：

查询部门编号是50的员工个数

select (select count(*) from employees where department_id = 50) 个数;

3.9.4、from后的子查询

案例1：

查询每个部门的平均工资的工资级别。

select s.department_id,s.ag,g.grade 
from sal_grade g,(select avg(salary) ag,department_id from employees group by department_id) s 
where g.ag between g.min_salary and g.max_salary;

3.9.5、exists后的子查询

案例1：

查询有无名字叫“张三丰”的员工信息

select exists(select * from employess where last_name = '张三丰') 有无张三丰;
# 如果存在输出1，否则输出0

3.10、分页查询

3.10.1、语法

select 查询列表 from 表1 别名 join 表2 别名 on 连接条件 where 筛选条件 group by 分组 having 分组后筛选 order by 排序列表 limit 起始条目索引,显示的条目数;

起始条目索引从0开始，如果不写，默认是0
limit后面支持两个参数，参数1：起始索引；参数2：显示条数

执行顺序：

from子句
join子句
on子句
where子句
group by子句
having子句
select子句
order by子句
limit子句

3.10.2、简单练习

案例1：

查询员工信息表的前5条

select * from employees limit 0,5;
select * from employees limit 5;

案例2：

查询有奖金的，且工资较高的第11到第20名

select * from employees where commission_pct is not null order by salary desc limit 10,10;

3.11、联合查询

3.11.1、简单介绍

当查询结果来自于多张表，但多张表之间没有关联，这时候往往使用联合查询，也称为union查询

语法：

select 查询列表 from 表1 where 筛选条件 union select 查询列表 from 表2 where 筛选条件;

多条待联合查询语句的查询列数必须一致，查询类型、字段意义最好一致。
union实现去重查询，union all实现查询全部，包含重复项。

注：

union是自动会去除重复的行的，如果不想去重则需要使用union all

3.11.2、简单练习

案例1：

查询所有国家的年龄>20的用户信息

# 注：显示的列名是第一个查询表的列名
select * from chinese where age > 20 
union
select * from usa where age > 20;

错误案例2：

查询所有国家的用户姓名和年龄

# 下面语句会报错，因为查询的两张表列数不一样
select uname,uage from usa
union
select `name` from chinese;

# 下面语句没有意义，显示结果usa的正常，但是chinese的年龄和名字反了，所以没有意义
select uname,uage from usa
union
select age,`name` from chinese;

案例3：

union自动去重

select 1,'刘宇'
union
select 1,'刘宇'
union
select 1,'刘宇';
# 上面这个语句只会输出一行1，刘宇

案例4：

union all不自动去重

select 1,'刘宇'
union all
select 1,'刘宇'
union all
select 1,'刘宇';
# 上面这个语句只会输出三行1，刘宇

四、DDL语言

4.1、库的管理

4.1.1、创建数据库

# 重复创建会报错
CREATE DATABASE stuDB;
# 重复创建不会报错，会有警告
CREATE DATABASE IF NOT EXISTS stuDB;

4.1.2、删除数据库

# 重复删除会报错
DROP DATABASE stuDB;
# 重复删除不会报错，会有警告
DROP DATABASE IF EXISTS stuDB;

4.2、表的管理

4.2.1、创建表

4.2.1.1、语法

CREATE TABLE 表名(
	字段名 字段类型 [字段约束],
	字段名 字段类型 [字段约束],
	字段名 字段类型 [字段约束]
);

4.2.1.2、案例

创建无约束表

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid int,
	stuname varchar(20),
	stugender char,
	email varchar(20),
	borndate datetime
);

创建有约束表

CREATE TABLE IF NOT EXISTS stuinfo(
	stuid int primary key,# 添加主键
	stuname varchar(20) unique not null,# 添加唯一和非空约束
	stugender char default '男',# 默认值
	email varchar(20) not null,# 非空
	age int check(age between 1 and 100),# 添加了检查约束，mysql不支持
	majorid int,
	constraint fk_stuinfo_major foreign key (majorid) references major(id) #给majorid添加了外键约束，引用major表的id字段
);

4.2.1.3、常用类型

tinyint / smaillint / int / bigint：整型
double/float：浮点型，例如double(5,2)表示最多5位，其中必须有2位小数，即最大值为999.99；如果第二个参数不写则不约束小数位。
decimal：浮点型，在表示钱方面使用该类型，因为不会出现精度缺失问题
char：固定长度为字符串类型；char(4)范围是0-255;
varchar：可变长度字符串类型；
text：字符串类型；表示存储较长文本
blob：字节类型；可用于存储jpg ma3 avi等等
date：日期类型，格式为yyyy-MM-dd
time：时间类型，格式为hh:mm:ss
timestamp：时间戳类型；日期+时间；保存范围：1900-1-1~2038-12-31；占4字节
datetime：时间戳类型；日期+时间；保存范围：1900-1-1~xxxx年；占8字节

4.2.1.4、常见约束

用于限制表中字段的数据的，从而进一步保证数据表的数据是一致的、准确的、可靠的。

NOT NULL：非空，表示该字段不能为空
DEFAULT：默认，用于限制该字段没有显式插入值的时候，给定的默认值
PRIMARY KEY：主键，用于限制该字段的值不能重复，该字段默认不能为空，可将多个字段组合成一个主键。
UNIQUE：唯一，用于限制该字段的值不能重复，字段可以为空
CHECK：检查，用于限定该字段的值必须满足指定条件；如：check(age between 1 and 100)，但是mysql不支持该约束
FOREIGN KEY：外键，用于限制两个表的关系

4.2.2、修改表

4.2.2.1、语法

alter table 表名 add|modify|change|drop column 字段名 字段类型 [字段约束];

4.2.2.2、案例

修改表名

alter table stuinfo rename to students;

添加字段

alter table students add column borndate timestamp not null;

修改字段

# 修改字段名
alter table students change column borndate birthday datetime not null;

# 修改字段类型
alter table students modify column birthday timestamp;

删除字段

alter table students drop column birthday;

4.2.3、删除表

drop table if exists students;

4.2.4、复制表

#仅仅复制表结构
create table newtable like stuinfo;

#复制表结构+数据
create table newtable select * from 表名;

五、DML语言

5.1、数据的插入

5.1.1、语法

insert into 表名(字段1,字段2,…) values (值1,值2,…);

注：

字段列表和值列表必须一一对应
字段的类型、约束等必须匹配
数值型的值，不用单引号，非数值型的值，必须使用单引号
字段顺序无要求

5.1.2、设置字段自增长

create table gradeinfo(
	gradeID int primary key auto_increment,
	gradeName varchar(20)
);

5.1.3、案例

案例1：

要求字段和值列表必须一一对应，且遵循类型和约束的限制

insert into stuinfo(stuid,stuname,stugender,email,age,majorid) values(1,'刘宇1','男','xxxx@qq.com',23,1);

案例2：

可以为空的字段如何插入

# 方式一
insert into stuinfo(stuid,stuname,stugender,email,age,majorid) values(2,'刘宇2',NULL,'xxxx@qq.com',23,1);
# 方式二：去除该字段
insert into stuinfo(stuid,stuname,email,age,majorid) values(3,'刘宇3','xxxx@qq.com',23,1);

案例3：

默认字段如何插入

# 方式一
insert into stuinfo(stuid,stuname,stugender,email,age,majorid) values(4,'刘宇4',DEFAULT,'xxxx@qq.com',23,1);

# 方式二：去除该字段
insert into stuinfo(stuid,stuname,email,age,majorid) values(5,'刘宇5','xxxx@qq.com',23,1);

案例4：

省略字段列表，默认所有字段

insert into values(6,'刘宇6','男','xxxx@qq.com',23,1);

案例5：

插入数据，自增列如何插入

# 自增列给NULL即可
insert into values(NULL,'刘宇6','男','xxxx@qq.com',23,1);

5.2、数据的修改

5.2.1、语法

update 表名 set 字段名=新值,字段名=新值,… where 筛选条件;

5.2.2、案例

案例1：

修改年龄<20的专业编号为3号，且邮箱更改为xx@qq.com

update stuinfo set majorid = 3,email='xx@qq.com' where age<20;

5.3、数据的删除

5.3.1、语法

select语句：select from 表名 where 筛选条件
truncate语句：truncate table 表名

两种区别：

select可以添加where条件，truncate不能添加where条件，它是一次性删除所有数据
truncate的效率较高
如果删除带自增长列的表，使用delete删除后，重新插入数据，记录从断点开始，使用truncate删除后，重新插入数据，记录从1开始
delete删除数据，会返回受影响的行数，truncate删除数据，不返回受影响的行数
delete删除数据，可以支持事务回滚，truncate删除数据，不支持事务回滚

5.3.2、案例

案例1：

删除姓李的所有信息

delete from stuinfo where stuname like '李%';

案例2：

删除表中所有信息

truncate table stuinfo;

六、数据库事务

6.1、什么是事务

一个事务是由一条或者多条sql语句构成，这一条或者多条sql语句要么全部执行成功，要么全部执行失败！

6.2、事务的四大特性（ACID）

原子性（Atomicity）：事务中所有操作是不可再分割的原子单位。事务中所有操作要么全部执行成功，要不全部执行失败。
一致性（Consistency）：事务执行后，数据库状态与其他业务规则保持一致。如转账业务，无论事务执行成功与否，参与转账的两个账号余额之和应该是不变的。
隔离性（Isolation）隔离性是指在并发操作中，不同事务之间应该隔离开来，使每个并发中的事务不会互相干扰。
持久性（Durability）：一旦事务提交成功，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须能保证通过某种机制恢复数据。

6.3、事务的练习

事务的分类：

隐式事务：没有明显的开启和结束标记，如DML语句的insert、update、delete语句本身就是一条事务
显式事务：具有明显的开始和结束标记

演示事务的使用步骤：

取消数据库中事务的自动提交设置

set autocommit = 0;

开启事务

start transaction;

编写事务的sql语句

# 将张三的钱-5000
update stuinfo set balance=balance-5000 where stuid=1;
# 将刘宇的钱+5000
update stuinfo set balance=balance+5000 where stuid=2;

结束事务

# 正常提交
commit;
# 出现异常，回滚
rollback;

Brycen Liu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL基础知识-刘宇

MySQL基础知识-刘宇一、数据库简单介绍1.1、基本概念1.2、数据库存储数据的特点1.3、MySQL的优点二、数据库简单使用2.1、MySQL登录和退出2.2、MySQL的常见命令2.3、MySQL的语法规范2.4、SQL的语言分类三、DQL语言3.1、基本查询语句3.2、条件查询语句3.2.1、语法3.2.2、分类：3.2.2.3、模糊查询：3.3、排序查询语句一、数据库简单介绍1.1、基本概念DB：数据库（Database），存储数据的“仓库”。DBMS：数据库管理系统（ Database
复制链接

扫一扫

专栏目录