文章目录
数据库
DDL语句:数据库定义语句
1.库的增删查改
1.查询当前mysql下所有的数据库
mysql8.0 自带的 跟5.5自带的不一样
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema | mysql默认的一些配置
| mysql | mysql库里面包含user表用户表 (权限用户)
| performance_schema | mysql性能相关的库
| sakila | 提供其他三个库 sakila sys world 提供了一些例库 练习sql语句
| sys |
| world |
| zy | 自定义的库(开发者自定义)
+--------------------+
7 rows in set (0.00 sec)
2.创建数据库
create database 库名 ;
方式1
mysql> create database mydb_01;
Query OK, 1 row affected (0.02 sec)
create database if not exists 库名;
方式2
mysql> create database if not exists mydb_02;
Query OK, 1 row affected (0.02 sec)
3.查看创建数据库的默认的字符集(了解)
show create database 库名;
mysql> show create database mydb_01;
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
| mydb_01 | CREATE DATABASE `mydb_01` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.修改数据库的字符集(了解)
alter database 库名 (default)可以省略 character set=字符集;
mysql> alter database mydb_01 default character set gbk ;
Query OK, 1 row affected (0.01 sec)
mysql> show create database mydb_01;
+----------+----------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+----------------------------------------------------------------------------------------------------+
| mydb_01 | CREATE DATABASE `mydb_01` /*!40100 DEFAULT CHARACTER SET gbk */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
5.删除库
drop database 库名;
drop database if exists 库名;
mysql> drop database mydb_02;
Query OK, 0 rows affected (0.02 sec)
mysql> drop database if exists mydb_03;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mydb_01 |
| mysql |
| performance_schema |
| sakila |
| sys |
| world |
| zy |
+--------------------+
6.模糊查询mysql服务中所有的带character字符集的全局变量
show variables like '%character%' ;
mysql> show variables like '%character%' ;
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.01 sec)
如果出现中文乱码:
character_set_client 客户端 utf8
character_set_results 结果集 utf8
改动字符集:临时解决中文乱码
mysql> set character_set_client=utf8;
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> set character_set_results=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> show variables like '%character%' ;
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | utf8mb3 |
| character_set_connection | gbk |
| character_set_database | ut
f8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb3 |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
2.表的增删改查
1.创建表
create table 表明(
列名1 字段类型1,
列名2 字段类型2,
...
列名n 字段类型n
) ;
数据库常用的字段类型
int------>默认int(11) 取的是当前实际长度 (推荐)
id int,---编号 1
int(长度):指定长度
id int(3) 1---->001
varchar(字符长度):字符串类型数据 '' "" 不写 引号 ,习惯去使用''或者双引号
double(值1,值2):小数类型
举例
double(3,2) ----小数类型,3位数,小数点后保留2位
123.56
日期类型
date 仅仅表示日期,不表示时分秒
"2021-8-12"
datatime 不仅表示日期具体的时间
"2021-8-12 17:31:00"
timestap:时间戳
给表中插入数据, 显示当前插入/修改/删除/查询数据那一刻时间 (具体到秒"2021-8-12 18:00:01")
注意事项:就是给那一个库中创建表 使用哪个库(选择数据库名)
use 库名;
mysql> use mydb_01;
Database changed
mysql>
mysql> create table student(
-> id int,
-> name varchar(20),
-> gender varchar(10),
-> address varchar(50),
-> age int
-> )
-> ;
Query OK, 0 rows affected (0.05 sec)
2.查询当前数据库中有哪些表
show tables ;
mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| student |
+-------------------+
1 row in set (0.01 sec)
3.查询当前表的结构
desc 表名 ;
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
修改表
4.给student表添加一列
alter table 表名 add 字段名称 字段类型;
mysql> alter table student add email varchar(50) ;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| address | varchar(50) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
5.修改表中的字段类型
alter table 表名 modify 字段名称 更改后的字段类型;
mysql> alter table student modify address varchar(30) ;
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| gender | varchar(10) | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
6.修改表中的字段名称
alter table 表名 change 旧列名 新列名 字段类型;
mysql> alter table student change gender sex varchar(10) ;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
| age | int | YES | | NULL | |
| email | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
7.删除某一列字段
alter table 表名 drop 字段名称;
mysql> alter table student drop email;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc student;
+---------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| id | int | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| sex | varchar(10) | YES | | NULL | |
| address | varchar(30) | YES | | NULL | |
| age | int | YES | | NULL | |
+---------+-------------+------+-----+---------+-------+
5 rows in set (0.01 sec)
8.修改表的表名
alter table 旧表名 renameto 新表名;
mysql> alter table student rename to stu;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| stu |
| teacher |
+-------------------+
2 rows in set (0.01 sec)
9.复制一张表结构和以前表的结果一样
create table 新表名 like 旧表名;
mysql> create table teacher like student; //字段复制
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| student |
| teacher |
+-------------------+
2 rows in set (0.01 sec)
10.删除表
drop tabble 表名;
drop table if exists 表名;
mysql> drop table if exists teacher;
Query OK, 0 rows affected (0.03 sec)
mysql> show tables;
+-------------------+
| Tables_in_mydb_01 |
+-------------------+
| stu |
+-------------------+
1 row in set (0.00 sec)
DML语句:操作表的记录
11.插入语句
1）**insert into 表名 values(值1,值2,值3…) ;**插入全表数据 :那么每一个参数值必须和字段类型匹配! (不会报错,警告)
mysql> insert into stu values(1,'高圆圆','女','西安市',30) ;
Query OK, 1 row affected (0.01 sec)
插入全表:一次性插入多条数据
2）insert into 表名 values(值1,值2…),(值1,值2…),(值1,值2…) ;
INSERT INTO
stu
VALUES
(3,'文章','男','西安市',35),
(4,'马伊琍','女','上海市',42),
(5,'德玛西亚','男','艾欧尼亚',25) ;
插入部分字段
3） insert into 表名(部分字段1,字段2…) values(值1,值2…)
INSERT INTO
stu(NAME,sex,address) VALUES('王宝强','男','咸阳市') ;
-- 注意:插入部分字段,必须字段数量和插入值的数量必须匹配
-- 不管插入部分字段还全部字段,那么必须要和当前类型一致
12.最基本的查询:查全表数据
select * from 表名;
mysql> insert into stu values(1,'高圆圆','女','西安市',30) ;
Query OK, 1 row affected (0.01 sec)
mysql> select * from stu ;
+------+--------+------+---------+------+
| id | name | sex | address | age |
+------+--------+------+---------+------+
| 1 | 高圆圆 | 女 | 西安市 | 30 |
+------+--------+------+---------+------+
1 row in set (0.01 sec)
如果中文乱码
改动下面的字符集编码即可
mysql> show variables like '%character%' ;
+--------------------------+---------------------------------------------------------+
| Variable_name | Value |
+--------------------------+---------------------------------------------------------+
| character_set_client | gbk |
| character_set_connection | gbk |
| character_set_database | gbk |
| character_set_filesystem | binary |
| character_set_results | gbk |
| character_set_server | utf8mb4 |
| character_set_system | utf8mb3 |
| character_sets_dir | C:\Program Files\MySQL\MySQL Server 8.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)
mysql> set character_set_client = utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> set character_set_results = utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)
13.修改表的数据
语法:按条件修改 update
1）update 表名 set 字段名称 = 值 where 字段名称= 值;
-- 修改name是王宝强的将他的id设置为7
UPDATE stu SET id = 7 WHERE NAME = '王宝强' ;
-- 修改id为7的age设置为37
UPDATE stu SET age = 37 WHERE id = 7 ;
一次修改多个字段
2）update 表名 set 字段名称1 =值1 ,字段名称2 =值2 where 条件;
-- 将id为的6的姓名修改为 '张三丰' ,性别修改为'男'
UPDATE
stu
SET
NAME = '张三丰',
sex = '男'
WHERE
id = 6 ;
3）如果不带条件修改:就是批量修改
-- 将修改name为 '高圆圆'UPDATE stu SET NAME = '高圆圆' ; -- 一般都是带条件进行修改!
14.删除表中数据
1）带条件来删除
delete from 表名 where 字段名称 = 值; //删除指定的记录
-- 删除id = 6的这条记录
DELETE FROM stu WHERE id = 6 ;
-- 删除id = 6的这条记录
DELETE FROM stu WHERE id = 6 ;
2）删除全表数据
delete from 表名;
DELETE FROM stu ;
truncate table 表名;
TRUNCATE TABLE stu ;
删除全表的语法
delete from 表名;
truncate table 表名; 两个区别
1)delete from 表名:只是删除全表数据;表的结构还存在,
如果表中 存在主键并且自增长约束,那么不会受影响,下一次在插入数据
继续在之前的基础上继续自增长!
2)truncate table 表名 ;
将表中数据删除的同时删除了表,然后在创建一张一模一样空表,
肯定影响自增长主键的值,再次插入数据,自增长从1开始...
等价于
drop table my_use;
创建一个当前一模一样的表结构
-- 创建一张新的表 my_user表
CREATE TABLE my_user(
id INT PRIMARY KEY AUTO_INCREMENT, -- 用户编号 主键加增长
NAME VARCHAR(20),
age INT ,
gender VARCHAR(5)
) ;
INSERT INTO my_user(NAME ,age ,gender)
VALUES('张三',20,'男'),('李四',22,'女'),('文章',25,'男') ;
INSERT INTO my_user(NAME,age,gender) VALUES('王五',18,'男') ;
-- delete from my_user where id = 8 ;
-- 删除全表数据
DELETE FROM my_user ;
-- 删除全部数据
TRUNCATE TABLE my_user ;
SELECT * FROM my_user;
3.查询表的数据
DQL之基本的查询语句
1）查询全表数据
SELECT * FROM 表名 ;
实际开发中,* (通配符),一般查询全表数据,需要带全部字段
2）select 字段名称列表 from 表名 ;
SELECT -- 指定全部字段
id ,
NAME,
sex,
address,
age
FROM stu ;
-- 查询指定的字段
-- select 字段名称列表 from 表名;
SELECT
id ,
NAME ,
address,
age
FROM stu ;
3）查询指定字段时可以通过as 关键字指定别名,as可以省略
SELECT
id AS '学生编号',
NAME AS '姓名',
address AS '地址',
age AS '年龄'
FROM stu ;
-- as 可以省略
SELECT
id '学生编号',
NAME '姓名',
address '地址',
age '年龄'
FROM stu ;
1.where条件查询
DQL带条件查询
where条件查询
可以基本运算符:比较运算符(<,>,<=,>=,!=)/逻辑运算符(|| && /and /or)/赋值运算符 =
where后面多个or in(集合数据) 在两个值之间 between 值1 and 值2
mysql 判断某个字段为null , is null /is not null
模糊查询 like
聚合函数 count(列名)/max(列名)/min(列名)/sum(列名)/avg(列名)
排序查询 order by
分组查询 group by
筛选查询 having
分页查询limit
-- 创建一个学生表
CREATE TABLE student (
id INT, -- 编号
NAME VARCHAR(20), -- 姓名
age INT, -- 年龄
sex VARCHAR(5), -- 性别
address VARCHAR(100), -- 地址
math INT, -- 数学
english INT -- 英语
);
INSERT INTO student(id,NAME,age,sex,address,math,english) VALUES
(1,'马云',55,'男',' 杭州',66,78),
(2,'马化腾',45,'女','深圳',98,87),
(3,'马景涛',55,'男','香港',56,77),
(4,'柳岩 ',20,'女','湖南',76,65),
(5,'柳青',20,'男','湖南',86,NULL),
(6,'刘德华',57,'男','香港 ',99,99),
(7,'马德',22,'女','香港',99,99),
(8,'德玛西亚',18,'男','南京',56,65);
-- 条件查询
-- 查询年龄大于20岁的人的全部信息
SELECT
id '编号',
NAME '姓名',
age '年龄',
sex '性别',
address '地址',
math '数学成绩',
english '英语成绩'
FROM
student
WHERE
age > 20 ;
-- 查询年龄在20岁和30岁之间的学生信息
SELECT
*
FROM
student
WHERE
age >= 20 && age <=30 ; -- &&:Java的逻辑运算符
-- 另一种语法
SELECT
*
FROM
student
WHERE
age >= 20 AND age <=30 ;-- and mysql表示并列关系
-- 另一种语法:mysql 在两个值之间 between 值1 and 值2
SELECT -- 查询部分字段 并且年龄在20-30
NAME '姓名',
age '年龄',
address '地址',
math '数学成绩',
english '英语成绩'
FROM
student
WHERE
age
BETWEEN 20 AND 30 ;
-- 查询年龄是18或者20或者30岁的学生的编号,姓名,年龄以及地址 (|| 或者 or)
SELECT
id '编号',
NAME '姓名',
age '年龄',
address '地址'
FROM
student
WHERE
age = 18 OR age = 20 OR age = 30 ;
-- mysql另一种语法 where 字段名称 in(多个值)
SELECT
id '编号',
NAME '姓名',
age '年龄',
address '地址'
FROM
student
WHERE
age IN(18,20,30) ;
查询null
-- 查询英语成绩为null的学号编号,姓名,性别,地址.数学成绩信息
/*
select
id ,
name,
sex,
address,
math
from
student
where
english == null ; -- java中的用法可以这样用 == = (都不行)
*/
SELECT
id ,
NAME,
sex,
address,
math,
english
FROM
student
WHERE
english IS NULL ; -- mysql用法
-- 查询英语成绩不为null的人 ,is not null 的用法
SELECT
id ,
NAME,
sex,
address,
math,
english
FROM
student
WHERE
english IS NOT NULL ;
-- 查询英语和数学成绩总分的学生新
SELECT
id 编号 ,
NAME 姓名 ,
sex 性别 ,
address '地址',
-- (math+english) as '总分'
(math+IFNULL(english,0)) '总分'
FROM
student ;
**mysql 内置函数ifnull(值1,值2) ; **
IFNULL(english,0) :
-- 如果当前英语成绩为null,给默认值0ql
字段去重 distinct
-- 查询字段,对字段去重 (distinct)
SELECT
DISTINCT address 地址
FROM student ;
-- 查询年龄不是20岁的学生信息
SELECT
*
FROM student
WHERE age != 20 ; -- != Java中这种语法 !=
-- mysql中的不等于 <>
SELECT
*
FROM
student
WHERE
age <> 20 ;
2.模糊查询
糊查询mysql服务中带字符集相关的变量 show variables like '%character%' ;
模糊查询 like
select 字段列表 from 表名 where 字段名称 like '%字符%' ;
% :包含的指定的字符 使用'%字符值%' 模糊查询包含指定字符的信息
_ :代表单个字符(一个_下划线代表一个字符)
两个相结合使用: '_%字符值%_' 三个字符:中间字符包含指定的值进行模糊查询
-- 查询当前学生表中姓名包含马的学生信息
SELECT
*
FROM
student
WHERE
NAME
LIKE
'%马%' ;
-- 查询第二个字符包含化的学生信息
SELECT
*
FROM
student
WHERE
NAME
LIKE
'_化%' ;
-- 查询姓名是三个字符的人
SELECT
*
FROM
student
WHERE
NAME
LIKE
'___' ;
-- 应用场景: 搜索框中输入关键字查询---使用到模糊查询
3.聚合函数查询
聚合函数查询---- >查询结果:单行单列的数据
count(列名) :总记录数 count(列名)查询总记录数的时候,一般都使用非业务字段查询
max(列名): 最大值
min(列名字段):最小值
sum(字段名称):求和
avg(列名):平均分
select 聚合函数(列名) from 表名;
查询当前student这个表的全部总记录数
-- 如果使用english字段查询总记录数
SELECT
COUNT(english) -- 使用业务字段查询(可能某个值为null,不会进行记录)
FROM
student ; -- 7条记录
-- 可以使用函数设置如果当前某个值null,给默认值
SELECT
COUNT(IFNULL(english,0)) 总记录数
FROM
student;
-- 建议id来查询
SELECT
COUNT(id) 总条数
FROM
student ;
-- 查询数学的平均成绩 avg(列名)
SELECT
AVG(math) '数学平均成绩'
FROM
student ; -- 79.5000
-- 查询英语成绩---总成绩(求和 sum(列名))
SELECT
SUM(IFNULL(english,0)) 英语总分
FROM
student ;
-- 查询英语成绩的最高成绩
SELECT
MAX(IFNULL(english,0)) 英语最高分
FROM
student ;
-- 查询数学成绩最低分
SELECT
MIN(math) 数学最低分
FROM
student ;
-- 聚合函数使用最多的:count函数,avg函数
4.order by
排序查询:order by 字段名称 asc/desc (升序/降序)
select 字段列表 from 表名 order by 字段名 排序规则; -- 单个字段进排序
-- 数学成绩安装升序排序
SELECT
*
FROM
student
ORDER BY math ; -- 如果字段名称后面没有带排序规则:则默认升序排序
-- 英语降序排序
SELECT
NAME ,
age,
sex,
address,
IFNULL(english,0) 英语成绩
FROM
student
ORDER BY english DESC ; -- 降序
-- 针对多个字段同时排序,当前第一字段值相等,则按照第二个字段的排序规则执行
-- select 字段列表 from 表名 order by 字段名称1 升序1 ,字段名称2 升序2;
-- 查询全表数据,数学成绩降序,英语成绩升序
SELECT
*
FROM
student
ORDER BY
math DESC,
english ASC ;
5.group by
分组查询:group by
分组group by 分组字段;
查询的时候可以查询分组字段,
group by 后面不能使用聚合函数
-- 现在需要按照性别分组-----分组之后查询出总人数
-- 性别-- 男/女
SELECT -- 查询分组字段
sex '性别',
COUNT(id) '人数'
FROM
student
GROUP BY
sex ; -- 性别
-- 现在需要按照性别分组-----分组之后查询出总人数,数学的平均分
-- 条件:数学成绩不大于70分的人不参与分组;
SELECT
sex '性别', -- 查询分组字段
COUNT(id) '总人数',
AVG(math) '数学平均成绩'
FROM
student
WHERE
math > 70 -- 条件:数学成绩大于70分人参与分组
GROUP BY
sex ; -- 性别分组
6.HAVING
筛选 having
having 必须置于group by 之后,where 置于 group by 之前
group by不能聚合函数,但是having后面可以聚合函数
-- 现在需要按照性别分组-----分组之后查询出总人数,数学的平均分
-- 条件:数学成绩不大于70分的人不参与分组
-- 筛选条件:总人数大于2的一组
SELECT
sex '性别', -- 查询分组字段
COUNT(id) '总人数',
AVG(math) '数学平均成绩'
FROM
student
WHERE
math > 70 -- 条件:数学成绩大于70分人参与分组
GROUP BY
sex -- 性别分组
HAVING
COUNT(id) > 2 ;
-- 优化
SELECT
sex 性别, -- 查询分组字段
COUNT(id) 总人数,
AVG(math) 数学平均成绩
FROM
student
WHERE
math > 70 -- 条件:数学成绩大于70分人参与分组
GROUP BY
sex -- 性别分组
HAVING
总人数 > 2 ;
having 与 where 的区别
where 子句
1) 对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，即先过滤再分组。
2) where 后面不可以使用聚合函数
having 子句
1) having子句的作用是筛选满足条件的组，即在分组之后过滤数据，即先分组再过滤。
2) having后面可以使用聚合函数
7.分页查询limit
select 字段列表 from 表名 limit 起始行数,每页显示多少条;
limit 起始行数=(当前页码数-1)*每页显示的条数,每页显示条数;
-- 每页显示3条记录
-- 查询第一页的数据
-- limit 起始行数=(当前页码数-1)*每页显示的条数,每页显示条数;
SELECT * FROM student LIMIT 0,3 ;
-- 查询第二页的数据
SELECT * FROM student LIMIT 3 ,3 ;
-- 查询第三页数据
SELECT * FROM student LIMIT 6,3 ;
-- 第四页数据
SELECT * FROM student LIMIT 9,3 ;
-- 查询全表
SELECT * FROM student ;
4.数据库约束
约束用户操作表的一种行为
1)默认约束
默认约束：防止出现非法数据null(没有插入造成null值)
当前没有给那个字段设置值的时候,此时默认约束就会起作用
gender VARCHAR(2) DEFAULT '女'
如果没有默认约束：
可能用户操作数据库的时候,插入非法数据(没有意义的数据)
如果没有给某个字段赋值,默认值null
-- 创建一个新的表
CREATE TABLE test(
id INT , -- 编号
NAME VARCHAR(10) , -- 姓名
gender VARCHAR(2) DEFAULT '女' -- 性别 -- 默认约束 防止出现非法数据null(没有插入造成null值)
) ;
INSERT INTO test VALUES(1,'张三','男') ;
-- 可能用户操作数据库的时候,插入非法数据(没有意义的数据)
-- 通过sql语句修改表的类型,删除默认约束
ALTER TABLE test MODIFY gender VARCHAR(2) ;
-- 修改表加入默认约束
ALTER TABLE test MODIFY gender VARCHAR(2) DEFAULT '女' ;
DELETE FROM test WHERE id = 3 ;
INSERT INTO test VALUES(1,'张三','男') ;
-- 可能用户操作数据库的时候,插入非法数据(没有意义的数据)
-- 通过sql语句修改表的类型,删除默认约束
ALTER TABLE test MODIFY gender VARCHAR(2) ;
-- 修改表加入默认约束
ALTER TABLE test MODIFY gender VARCHAR(2) DEFAULT '女' ;
DELETE FROM test WHERE id = 3 ;
2)非空约束
DROP TABLE test ;
CREATE TABLE test(
id INT ,
NAME VARCHAR(10) NOT NULL -- 非空约束
);
INSERT INTO test VALUES(1,NULL) ; -- 直接插入null值
-- insert into test (id) values(1) ; 没有给姓名赋值
INSERT INTO test VALUES(1,'') ; -- 存在值,只是空字符 和null不一样
-- 删除非空约束
ALTER TABLE test MODIFY NAME VARCHAR(10) ;
UPDATE test SET NAME = '高圆圆' WHERE id = 1 ;
INSERT INTO test VALUES(2,NULL) ; -- Column 'NAME' cannot be null
-- 修改表,加入非空约束
ALTER TABLE test MODIFY NAME VARCHAR(10) NOT NULL ;
DELETE FROM test WHERE id = 2 ;
3)唯一约束
nuique
DROP TABLE test;
CREATE TABLE test(
id INT ,
NAME VARCHAR(10),
phone VARCHAR(11) UNIQUE -- 唯一约束 :可以有null值,不能重复
) ;
INSERT INTO test VALUES(1,'张三','13666668888') ;
-- INSERT INTO test VALUES(1,'张三',null) ;
INSERT INTO test VALUES(2,'李四','13666668889') ;
-- INSERT INTO test VALUES(2,'李四','13666668888') ;
-- 通过语句删除唯一约束
-- alter table test modify phone varchar(11) ; 错误语法
-- 删除唯一约束的sql alter table test drop index 字段名称;
ALTER TABLE test DROP INDEX phone ;
INSERT INTO test VALUES(4,'赵六','13666668878') ;
DELETE FROM test WHERE id = 4 ;
-- 添加唯一约束
ALTER TABLE test MODIFY phone VARCHAR(11) UNIQUE ;
4)主键约束
primary key
主键约束 (非空+唯一特点) primary key
都会给当前非业务字段去设置主键(xxid)
DROP TABLE test ;
CREATE TABLE test(
id INT PRIMARY KEY , -- 非业务字段
NAME VARCHAR(10),
gender VARCHAR(2)
) ;
INSERT INTO test VALUES(1,'洪学佳','男'),(2,'马三奇','男') ;
-- insert into test values(1,'马嘉钰','男') ; id值重复 -- Duplicate entry '1' for key 'test.PRIMARY'
-- insert into test values(null,'雷郁','男') ;-- id直接插入null值 Column 'id' cannot be null
-- 删除test 表的主键
alter table test drop primary key;
-- 添加主键
alter table test add primary key(id);
5.外键约束
foreign key
-- 单独创建一个部门表,来存储部门
-- 一般一个表中:都会id字段(非业务字段:自增的主键约束都会设置在这个上面)
CREATE TABLE dept( -- 部门表
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
dept_name VARCHAR(20) -- 部门名称
);
-- 插入数据
INSERT INTO dept VALUES(1,'开发部'),(2,'测试部'),(3,'运维部') ;
-- 创建表:员工表
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(20), -- 员工姓名
gender VARCHAR(10), -- 性别
dept varchar(20), -- 部门
) ;
-- 创建表:员工表
-- 创建员工表的时候:员工表的dept_id应该关于部门表主键id
-- 创建员工表的时候就需要添加外键(外键所在表称为"从表")
CREATE TABLE emp(
id INT PRIMARY KEY AUTO_INCREMENT, -- 员工编号
NAME VARCHAR(20), -- 员工姓名
gender VARCHAR(10), -- 性别
dept_id INT, -- 部门编号
CONSTRAINT fk_demp_emp -- 声明 外键名称
FOREIGN KEY (dept_id) -- 作用在指定从表的指定字段
REFERENCES -- 关联
dept(id) -- 部门表的主键id
) ;
-- 插入员工数据
INSERT INTO emp VALUES(1,'高圆圆','女',1),
(2,'赵又挺','男',1),
(3,'文章','男',2),
(4,'马伊琍','女',3),
(5,'姚笛','女',2),
(6,'马三奇','男',1) ;
-- 插入一个不存员工,而且插入一个不存在部门编号
INSERT INTO emp (NAME,gender,dept_id) VALUES('王宝强','男',4) ;
-- 非法数据了,因为部门表中并没有4号部门
针对员工表(从表:外键所在的表)的插入/修改/删除不能直接操作 从表
部门表:主表
可以先删除外键在进行操作
外键的删除与设置
外键约束(两张表设置一个外键):两种设置方式
1)创建表的直接添加外键
2)通过alter table 表名 add .... 方式添加外键
-- 删除外键约束 ALTER TABLE emp DROP FOREIGN KEY 外键名称
ALTER TABLE emp DROP FOREIGN KEY fk_demp_emp ;
-- 删除id为8的员工
DELETE FROM emp WHERE id = 8 ;
-- 通过修改表添加外键
ALTER TABLE emp ADD
CONSTRAINT -- 声明
fk_demp_emp -- 外键名称
FOREIGN KEY (dept_id) -- 作用在指定从表的指定字段
REFERENCES -- 关联
dept(id) ; -- 部门表的主键id
有了外键有关联关系,所以不直接操作主表;修改/删除 (很麻烦)
1)修改或者从表的数据,让这个数据和主表没有关联
2)修改/删除 主表的数据
级联操作
级联操作:CASCADE
级联修改 ON UPDATE CASCADE
当前修改主表的数据,那么从表和主表相关关联的数据一会被随着更改掉
级联删除 ON DELETE CASCADE,
当删除主表的数据,那么从表和主表关联的数据一会被随着删除掉
-- 先sql语句将外键约束删除,然后添加外键并且添加级联操作
ALTER TABLE emp DROP FOREIGN KEY fk_demp_emp ;
ALTER TABLE emp ADD
CONSTRAINT fk_dept_emp
FOREIGN KEY (dept_id)
REFERENCES dept(id)
ON UPDATE CASCADE -- 添加级联修改
ON DELETE CASCADE ; -- 添加级联删除
-- 直接修改主表的数据:
-- 将开发部的部门编号更改为4
UPDATE dept SET id = 4 WHERE id = 1 ;
-- 删除主表的数据,从表的随之删除
-- 删除id为4号部门的开发部
DELETE FROM dept WHERE id = 4;
5.数据库的备份与还原
数据库的备份和还原
两种方式:
1）图形界面话:简单直观
选择数据库---右键----备份---->选择导出的路径(结构以及数据都保存)
将之前存在库,然后选择 执行指定sql脚本----> 选择指定的sql脚本---进行执行即可!
2）命令行方式
备份:mysqldump -uroot -p密码 备份数据库名称 > 保存的本地地址
还原:
将原来删除,新建库,使用库, source 将保存的本地地址
6.表与表的关系
一对一的关系:是一种特例 (应用场景不多)
举例: 人和身份证
一个人对应一张身份证
一个身份证属于某个人的
一对多的关系
举例: 员工表和部门表
一部门可以有多个员工,一个员工属于某一个部门的(员工表中有一个外键)
多对多的关系
7.数据库的三大范式
1NF
1NF :数据库表的每一列都是不可分割的原子数据项 不能出现复合项
2NF
2NF:第二范式就是在第一范式的基础上所有列(所有字段)完全 依赖于主键列(主键字段)。
3NF
3NF:在第二范式2NF基础上,非主键字段不能传递依赖于主键(外键解决)
8.多表查询
-- 部门表
CREATE TABLE dept(
id INT PRIMARY KEY AUTO_INCREMENT, -- 部门编号
NAME VARCHAR(20) -- 部门名称
);
INSERT INTO dept (NAME) VALUES ('开发部'),('市场部'),('财务部');
-- 员工表
CREATE TABLE emp (
id INT PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(10),
gender CHAR(1), -- 性别
salary DOUBLE, -- 工资
join_date DATE, -- 入职日期
dept_id INT,
FOREIGN KEY (dept_id)
REFERENCES dept(id) -- 外键，关联部门表(部门表的主键)
);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)VALUES('孙悟空','男',7200,'2013-02-24',1);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)VALUES('猪八戒','男',3600,'2010-12-02',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id)VALUES('唐僧','男',9000,'2008-08-08',2);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('白骨精','女',5000,'2015-10-07',3);
INSERT INTO emp(NAME,gender,salary,join_date,dept_id) VALUES('蜘蛛精','女',4500,'2011-03-14',1);
-- 需求:查询员工表和部门表的所有信息
SELECT *
FROM emp , dept ;
-- 查询多张表:员工表5条记录,部门表有3条记录 (A*B=15)
-- 出现迪尔卡乘积的原因:没有连接条件
-- 通过外键解决字段冗余问题 员工表的部门dept_id关联与部门表的主键id
1.内连接
1）隐式内连接
隐式内连接-----通过where 语句作为连接条件来查询(按照外键关联关系)
操作步骤:
1)查询哪张表
2)要查询的是指定表中的哪个字段
3)表和表之间的关联关系问题
-- 1)员工表和部门表
-- 2)查询员工表的中name,gender,salary以及部门表的部门名称
-- 3)连接条件:dept_id= 部门表的id
SELECT
e.name '员工姓名' ,
e.`gender` '性别',
e.`salary` '工资',
e.`dept_id` ,
d.id ,
d.`name` '部门名称'
FROM
emp e,dept d -- 员工表的别名e ,部门表的别名d
WHERE
e.`dept_id` = d.`id` ;
2）显示内连接
(实际开发中,多去使用where条件查询,查询速度快 ,sql优化的一种方式)
select <字段列表>
from 表名1
(inner) join
表名2
on 关联关系(连接条件);
SELECT
t1.*,-- 员工表中的所有字段 (实际开发中,* 不建议使用!)
t2.`name` -- 部门表的部门名称
FROM
emp t1
-- inner 可以省略,不写
JOIN dept t2
ON
t1.`dept_id` = t2.`id` ;
2.外连接
-- 给员工表插入一条数据
INSERT INTO emp(NAME,gender,salary,join_date)
VALUES('高圆圆','女',12000,'2021-8-14');
-- 要查询所有的员工表信息的同时关联查询部门表信息(部门名称即可)
-- 即使员工没有部门信息,也得查询出来...
SELECT
t1.`name` '员工名称',
t1.`gender` '性别',
t1.`salary` '工资',
t1.`join_date` '入职日期',
t2.`name` '部门名称'
FROM
emp t1
INNER JOIN dept t2
ON
t1.`dept_id` = t2.`id` ; -- 交集的条件
-- 通过内连接查询存在问题 :只是查出了交集部分的数据,没有部门编号的员工并没有查询出来
-- 内连接就不能使用了
-- 使用 多表查询之外连接查询
1）左外连接
使用 多表查询之外连接查询
左外连接 left (outer) join: 通用
将两种表中左表的数据全部进行查询并且以及他们交集部分的数据全部查询
右外连接 right (outer) join
语法格式：
select
字段列表
from
表名1 -- 左表
left outer join 表名2
on 连接条件 ;
SELECT
t1.`name` '姓名',
t1.`gender` '性别',
t1.`salary` '工资',
t1.`join_date` '入职日期',
t1.`dept_id` ,
t2.`name` '部门名称'
FROM
emp t1 -- 左表(见图中的A表)
LEFT
-- outer
JOIN
dept t2 -- (图中的B表)
ON
t1.`dept_id` = t2.`id` ; -- 交集部分数据
SELECT
*
FROM
dept t1
RIGHT OUTER JOIN emp t2
ON
t2.`dept_id` = t1.`id` ;
3.子查询
1）单行单列
-- 子查询
-- 情况1:单行单列的情况:通用使用运算符进行操作
-- 需求:查询最高工资的员工信息
-- 1)查询最高工资是多少 : 聚合函数
/*
select
max(salary)
from
emp ; -- 12000
*/
-- 2) 查询等于最高工资的员工信息
/*
select
emp.`name`,
emp.`salary`,
emp.`gender`,
emp.`join_date`
from
emp
where
salary = 12000 ;
*/
-- 一步走: 将12000的sql语句代入过来即可
SELECT
emp.`id`,
emp.`name`,
emp.`salary`,
emp.`gender`,
emp.`join_date`
FROM
emp
WHERE
salary = (SELECT MAX(salary) FROM emp ) ;
-- 查询工资小于平均工资的员工信息
-- 1)查询平均工资是多少
/*
select
avg (salary)
from emp ;
*/
-- 2)查询员工工资小于 6883.333....的员工信息
8
SELECT
*
FROM
emp
WHERE
salary < (SELECT AVG (salary) FROM emp ) ;
2）多行多列
-- 子查询的第二种情况 :多行多列的情况 ---使用关键字in(集合数据)
-- 需求:查询 在财务部或者市场部的员工信息
-- 分步骤
-- 1)查部门表中财务部和市场部的id是多少
/*
select
id
from
dept
where
name = '市场部' or name = '财务部' ;-- 2 , 3
*/
-- 2)在员工表中查 id为 财务部 (3) 或者市场部(2)的员工信息
-- 优化 步骤)sql语句 or---->in(集合数据)
-- 在xxx数据内 /not in exists /not exists /any(sql)
SELECT
t.`name` '姓名',
t.`gender` '性别',
t.`salary` '工资',
t.`join_date` '入职日期',
t.`dept_id` '部门编号'
FROM
emp t
WHERE
t.`dept_id` IN(
SELECT
id
FROM
dept
WHERE
NAME = '市场部'
OR
NAME = '财务部') ;
3）虚表
-- 子查询的情况3:
-- 使用select语句查询的结果作为一个虚表,使用这个虚表然后继续和其他表进行查询
-- 查询入职日期大于'2011-3-14号的员工信息以及所在的部门信息
-- 入职日期 join_date > '2011-03-14' 将查询作为虚表 和部门表继续进行查询
SELECT * FROM emp WHERE join_date > '2011-03-14';
-- 可以使用左外连接
SELECT
t2.*, -- 员工的所有信息
t1.`name` '部门名称'
FROM
dept t1 -- 部门表
LEFT OUTER JOIN
(SELECT * FROM emp WHERE join_date > '2011-03-14') t2
ON
t1.`id` = t2.`dept_id` ;
-- 最基本隐式内连接
SELECT
t1.* , -- 员工所有信息
t2.`name` -- 部门名称
FROM
emp t1 ,
dept t2
WHERE
t1.`dept_id` = t2.`id`
AND
t1.`join_date` > '2011-03-14' ;
9.mysql事务
事务:在业务操作过程中,一次性可能同时操作多个sql语句,防止
操作多个sql语句时候出现问题,将这整个业务操作看成一个整体,进行处理
这些多个sql要么一次性全部执行成功,要么同时失败!
后期所有业务操作: 增删改 都需要使用事务进行管理
增删改:一次性执行多张表的多个sql语句(同时操作)------>SpringAOP 面向切面编程 基于OOP(面向对象)
事务的特点:ACID
原子性:就是事务操作业务的中sql,要么同时执行成功,要么同时失败!
一致性:操作sql语句的前后,总数量保持不变
转账之前:1000 1000
转账之后:500 1500
隔离性:
将业务使用事务管理,业务和业务之间分离的,
事务和事务之间不能相互影响---->事务是独立的
持久性: 如果一旦事务被提交了,是永久存储的,即使关机也还存在!
-- 转账
-- 创建一张账户表
CREATE TABLE account(
id INT PRIMARY KEY AUTO_INCREMENT, -- 账户编号
NAME VARCHAR(20), -- 账户名称
balance INT -- 账户余额
) ;
INSERT INTO account(NAME ,balance) VALUES('zhangsan',1000),('lisi',1000) ;
SELECT * FROM account ;
-- 没有开启事务,需要管理多个操作(一次执行多个sql语句),可以将多个操作
-- 使用事务管理,开启事务
-- 没有使用事务之前
-- zhangsan- 500
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan' ;
出问题了
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi' ;
-- 将转账业务操作看成整体,开启事务
START TRANSACTION ; -- 开启手动提交事务(默认自动提交)
-- 执行业务 操作
UPDATE account SET balance = balance - 500 WHERE NAME = 'zhangsan' ;
-- 中间出问题了
UPDATE account SET balance = balance + 500 WHERE NAME = 'lisi' ;
-- 事务回滚:回滚到在操作语句之前的状态 (余额都是1000)
ROLLBACK ;
-- 如果转账中没问题,将手动事务进行提交
COMMIT;
10.隔离级别
read uncommitted ; 读未提交 安全性最差 不能有效防止脏读
read committed ;读已提交 安全相对第一个高一些,能够有效脏读,不能够防止可重复读的问题
repeatable read :可重复读 能够有效防止脏读,可重复读(MySQL默认隔离界别)
serializable 串行话 安全性最高，两个事物，当一个事务没有提交，这个事务不能操作其他事情
查看全局的隔离级别的命令
查询隔离级别
select @@tx_isolation; mysql5.5/5.7都可以
SELECT @@transaction_isolation; mysql8.0
设置隔离级别
set global transaction isolation level 级别字符串;
SELECT @@transaction_isolation;
隔离级别不同,会出现不同的问题
脏读:一个事务读取另一个没有提交的事务(最严重的问题)
不可重复读: 一般都是update语句影响,两个事务中,一个事务读取的前后的内容不一致!
幻读:一般insert/delete :影响两个事务中,前后数量不一致!
1）第一种隔离级别：Read uncommitted(读未提交)
如果一个事务已经开始写数据，则另外一个事务不允许同时进行写操作，但允许其他事务读此行数据，该隔离级别可以通过“排他写锁”，但是不排斥读线程实现。这样就避免了更新丢失，却可能出现脏读，也就是说事务B读取到了事务A未提交的数据
解决了更新丢失，但还是可能会出现脏读
2）第二种隔离级别：Read committed(读提交)
如果是一个读事务(线程)，则允许其他事务读写，如果是写事务将会禁止其他事务访问该行数据，该隔离级别避免了脏读，但是可能出现不可重复读。事务A事先读取了数据，事务B紧接着更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
解决了更新丢失和脏读问题
3）第三种隔离级别：Repeatable read(可重复读取)
可重复读取是指在一个事务内，多次读同一个数据，在这个事务还没结束时，其他事务不能访问该数据(包括了读写)，这样就可以在同一个事务内两次读到的数据是一样的，因此称为是可重复读隔离级别，读取数据的事务将会禁止写事务(但允许读事务)，写事务则禁止任何其他事务(包括了读写)，这样避免了不可重复读和脏读，但是有时可能会出现幻读。(读取数据的事务)可以通过“共享读镜”和“排他写锁”实现。
解决了更新丢失、脏读、不可重复读、但是还会出现幻读
4）第四种隔离级别：Serializable(可序化)
提供严格的事务隔离，它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行，如果仅仅通过“行级锁”是无法实现序列化的，必须通过其他机制保证新插入的数据不会被执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也是最高的，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻读
103
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
