目录
一、数据库的基本操作
二、表的基本操作
1. 表结构操作
2. 表数据操作(CRUD增删查改)
一、数据库的基本操作
●
创建数据库:
create database [数据库名];
●
切换数据库:
use [数据库名];
●
删除数据库:
drop database [数据库名];
●
查询所有数据库:
show databases;
●
查询当前所在数据库:
select database();
二、表的基本操作
命名⼤⼩写和规范的问题:
MySQL 忽略⼤⼩写匹配,但创建数据库和表的时候⼀定要注意,默认采⽤全部⼩写的⽅式来操
作,防⽌编写的 sql 不通⽤。
表名采⽤下划线连接,⽐如学⽣成绩表,可使⽤ student_score,⽽⾮ java 的中的 studentScore。
1. 表结构操作
1.1 创建表:
create table 表名(
属性1,
属性2,
属性3
);
可以使⽤
comment
增加字段说明。
示例:
create table stu_test (
id int, name varchar(20) comment '姓名',
password varchar(50) comment '密码',
age int,
sex varchar(1),
birthday timestamp,
amout decimal(13,2),
resume text
);
1.2 删除表 :
drop table 表名;
或者
drop table if exists 表名;
1.3 查看表结构 :
desc 表名 ;
如果desc
查看不到说明信息,可以使⽤另⼀种查询表结构的⽅法:
show create table student;
或者使⽤
show full columns from 表名;
查询⼀张表的所有字段信息,效果如下:
查看所有表:
show tables;
1.4 修改表结果 :
1.4.1 添加列 :
alter table 表名 add column 列名 数据类型;
1.4.2 删除列:
alter table 表名 drop column 列名;
1.4.3 修改列 :
列已经有数据了,不能删除,只能修改,可以使⽤ change 语句,具体语法如下:
alter table 表名 change 原字段名 新字段名 类型 [约束];
1.4.4 修改表名:
alter table 旧表名 rename 新表名;
1.4.5 修改某个表编码格式(⽀持中⽂):
alter table table_name convert to character set utf8mb4;
2. 表数据操作(CRUD增删查改)
2.1 新增 :
2.1.1 单行数据 + 全列插入 :
insert into score values(1,'刘备','85.5','20','80');
2.1.2 多行数据 + 指定列插入 :
insert into score(id,name,math) values(5,'⼤乔',60), (6,'张⻜',70);
扩展:表和表数据的存储⽬录:
show variables like '%dir%';
也可以使⽤:
select @@datadir;
2.2 查询 :
2.2.1 全列查询:
-- 通常情况下不建议使用 * 进行全列查询
-- 1. 查询的列越多,意味着需要传输的数据量越大
-- 2. 可能会影响到索引的使用。
select * from exam_result;
2.2.2 指定列查询:
-- 指定列的顺序不需要按定义表的顺序来
select id, name, english from exam_result;
2.2.3 表达式查询:
-- 表达式不包含字段
select id, name, 10 from exam_result;
-- 表达式包含一个字段
select id, name, english + 10 from exam_result;
-- 表达式包含多个字段
select id, name, chinese + math + english from exam_result;
2.2.4 别名:
为查询结果中的列指定别名,表示返回的结果集中,以别名作为该列的名称,语法:
-- 结果集中,表头的列名=别名
select id, name, chinese + math + english 总分 from exam_result;
2.2.5 去重:
使用
DISTINCT
关键字对某列数据进行去重:
select distinct math from exam_result;
2.2.6 排序:
-- ASC 为升序(从小到大)
-- DESC 为降序(从大到小)
-- 默认为 ASC
1.
没有
ORDER BY
子句的查询,返回的顺序是未定义的,永远不要依赖这个顺序
2. NULL
数据排序,视为比任何值都小,升序出现在最上面,降序出现在最下面
--
查询同学姓名和
qq_mail
,按
qq_mail
排序显示
select name, qq_mail from student order by qq_mail;
select name, qq_mail fromstudent order by qq_mail desc;
3.
使用
表达式
及
别名
排序
--
查询同学及总分,由高到低
select
name, chinese + english + math
from
exam_result
order by
chinese + english + math
desc
;
select
name, chinese + english + math total
from
exam_result
order by
total
desc
;
4.
可以对多个字段进行排序,排序优先级随书写顺序
--
查询同学各门成绩,依次按 数学降序,英语升序,语文升序的方式显示
select
name, math, english, chinese
from
exam_result
order by
math
desc
, english, chinese;
2.2.7 条件查询 :where
比较运算符:
逻辑运算符:
注
:
1. WHERE
条件可以使用表达式,但不能使用别名。
2. AND
的优先级高于
OR
,在同时使用时,需要使用小括号
()
包裹优先执行的部分
● 基本查询:
--
查询英语不及格的同学及英语成绩
( < 60 )
SELECT
name, english
FROM
exam_result
WHERE
english <
60
;
--
查询语文成绩好于英语成绩的同学
SELECT
name, chinese, english
FROM
exam_result
WHERE
chinese > english;
--
查询总分在
200
分以下的同学
SELECT
name, chinese + math + english
总分
FROM
exam_result
WHERE
chinese + math + english <
200
;
● AND与OR:
--
查询语文成绩大于
80
分,且英语成绩大于
80
分的同学
SELECT
*
FROM
exam_result
WHERE
chinese >
80
and
english >
80
;
--
查询语文成绩大于
80
分,或英语成绩大于
80
分的同学
SELECT
*
FROM
exam_result
WHERE
chinese >
80
or
english >
80
;
--
观察
AND
和
OR
的优先级:
SELECT
*
FROM
exam_result
WHERE
chinese >
80
or
math>
70
and
english >
70
;
SELECT
*
FROM
exam_result
WHERE
(chinese >
80
or
math>
70
)
and
english >
70
;
● 范围查询:
1. BETWEEN ... AND ...
查询语文成绩在
[80, 90]
分的同学及语文成绩
SELECT
name, chinese
FROM
exam_result
WHERE
chinese
BETWEEN
80
AND
90
;
--
使用
AND
也可以实现
SELECT
name, chinese
FROM
exam_result
WHERE
chinese >=
80
AND
chinese
<=
90
;
2. IN
--
查询数学成绩是
58
或者
59
或者
98
或者
99
分的同学及数学成绩
SELECT
name, math
FROM
exam_result
WHERE
math
IN
(
58
,
59
,
98
,
99
);
--
使用
OR
也可以实现
SELECT
name, math
FROM
exam_result
WHERE
math =
58
OR
math =
59
OR
math =
98
OR
math =
99
;
● 模糊查询:LIKE
-- %
匹配任意多个(包括
0
个)字符
SELECT
name
FROM
exam_result
WHERE
name
LIKE
'
孙
%'
;
--
匹配到孙悟空、孙权
-- _
匹配严格的一个任意字符
SELECT
name
FROM
exam_result
WHERE
name
LIKE
'
孙
_'
;
--
匹配到孙权
● NULL 的查询:IS [NOT] NULL
--
查询
qq_mail
已知的同学姓名
SELECT
name, qq_mail
FROM
student
WHERE
qq_mail
IS NOT
NULL
;
--
查询
qq_mail
未知的同学姓名
SELECT
name, qq_mail
FROM
student
WHERE
qq_mail
IS
NULL;
2.2.8 分页查询 :LIMIT
--
第
1
页
SELECT
id, name, math, english, chinese
FROM
exam_result
ORDER BY
id
LIMIT
3
OFFSET
0
;
--
第
2
页
SELECT
id, name, math, english, chinese
FROM
exam_result
ORDER BY
id
LIMIT
3
OFFSET
3
;
--
第
3
页,如果结果不足
3
个,不会有影响
SELECT
id, name, math, english, chinese
FROM
exam_result
ORDER BY
id
LIMIT
3
OFFSET
6
;
2.3. 修改 :
--
将孙悟空同学的数学成绩变更为
80
分
UPDATE
exam_result
SET
math =
80
WHERE
name =
'
孙悟空
'
;
--
将曹孟德同学的数学成绩变更为
60
分,语文成绩变更为
70
分
UPDATE
exam_result
SET
math =
60
, chinese =
70
WHERE
name =
'
曹孟德
'
;
--
将总成绩倒数前三的
3
位同学的数学成绩加上
30
分
UPDATE
exam_result
SET
math = math +
30
ORDER BY
chinese + math + english
LIMIT
3
;
--
将所有同学的语文成绩更新为原来的
2
倍
UPDATE
exam_result
SET
chinese = chinese *
2
;
2.4. 删除:
--
删除孙悟空同学的考试成绩
DELETE FROM
exam_result
WHERE
name =
'
孙悟空
'
;
--
删除整张表数据
--
准备测试表
DROP TABLE IF EXISTS
for_delete;
CREATE TABLE
for_delete (
id
INT
,
name
VARCHAR
(
20
)
);
--
插入测试数据
INSERT INTO
for_delete (name)
VALUES
(
'A'
), (
'B'
), (
'C'
);
--
删除整表数据
DELETE FROM
for_delete;