1. 登录和登出数据库
登录数据库:
输入下面命令:
|
1 2 |
mysql -uroot -p |
登出(退出)数据库:
|
1 2 |
quit 或 exit 或 ctrl + d |
2. 数据库操作的SQL语句
- 查看所有数据库
12show databases;
- 创建数据库
1234create database 数据库名 charset=utf8;例：create database python charset=utf8;
- 使用数据库
12use 数据库名;
- 查看当前使用的数据库
12select database();
- 删除数据库-慎重
1234drop database 数据库名;例：drop database python;
3. 表结构操作的SQL语句
- 查看当前数据库中所有表
12show tables;
- 创建表
12345678create table students(id int unsigned primary key auto_increment not null,name varchar(20) not null,age tinyint unsigned default 0,height decimal(5,2),gender enum('男','女','保密'));
说明:
123456create table 表名(字段名称 数据类型 可选的约束条件,column1 datatype contrai,...); - 修改表-添加字段
1234alter table 表名 add 列名 类型 约束;例：alter table students add birthday datetime;
- 修改表-修改字段类型
1234alter table 表名 modify 列名 类型 约束;例：alter table students modify birthday date not null;
说明:
- modify: 只能修改字段类型或者约束，不能修改字段名
-
修改表-修改字段名和字段类型
1234alter table 表名 change 原名 新名 类型及约束;例：alter table students change birthday birth datetime not null;说明:
- change: 既能对字段重命名又能修改字段类型还能修改约束
-
修改表-删除字段
1234alter table 表名 drop 列名;例：alter table students drop birthday; - 查看创表SQL语句
1234show create table 表名;例：show create table students;
- 查看创库SQL语句
1234show create database 数据库名;例：show create database mytest;
- 删除表
1234drop table 表名;例：drop table students;
4. 表数据操作的SQL语句
- 查询数据
123456789-- 1. 查询所有列select * from 表名;例：select * from students;-- 2. 查询指定列select 列1,列2,... from 表名;例：select id,name from students;
- 添加数据
1234567891011121314151617-- 1. 全列插入：值的顺序与表结构字段的顺序完全一一对应insert into 表名 values (...)例:insert into students values(0, 'xx', default, default, '男');-- 2. 部分列插入：值的顺序与给出的列顺序对应insert into 表名 (列1,...) values(值1,...)例:insert into students(name, age) values('王二小', 15);-- 3. 全列多行插入insert into 表名 values(...),(...)...;例:insert into students values(0, '张飞', 55, 1.75, '男'),(0, '关羽', 58, 1.85, '男');-- 4. 部分列多行插入insert into 表名(列1,...) values(值1,...),(值1,...)...;例：insert into students(name, height) values('刘备', 1.75),('曹操', 1.6);
说明:
- 主键列是自动增长，但是在全列插入时需要占位，通常使用空值(0或者null或者default)
- 在全列插入时，如果字段列有默认值可以使用 default 来占位，插入后的数据就是之前设置的默认值
-
修改数据
1234update 表名 set 列1=值1,列2=值2... where 条件例：update students set age = 18, gender = '女' where id = 6; - 删除数据
1234delete from 表名 where 条件例：delete from students where id=5;
问题:
上面的操作称之为物理删除，一旦删除就不容易恢复，我们可以使用逻辑删除的方式来解决这个问题。
12345-- 添加删除表示字段，0表示未删除 1表示删除alter table students add isdelete bit default 0;-- 逻辑删除数据update students set isdelete = 1 where id = 8;说明:
- 逻辑删除，本质就是修改操作
5. 关键字
- as 关键字可以给表中字段 或者 表名起别名
- distinct 关键字可以去除重复数据行。
6. where条件查询的介绍
使用where条件查询可以对表中的数据进行筛选，条件成立的记录会出现在结果集中。
where语句支持的运算符:
- 比较运算符
- 逻辑运算符
- 模糊查询
- 范围查询
- 空判断
where条件查询语法格式如下:
|
1 2 3 4 |
select * from 表名 where 条件; 例： select * from students where id = 1; |
7. 比较运算符查询
- 等于: =
- 大于: >
- 大于等于: >=
- 小于: <
- 小于等于: <=
- 不等于: != 或 <>
例1：查询编号大于3的学生:
|
1 2 |
select * from students where id > 3; |
例2：查询编号不大于4的学生:
|
1 2 |
select * from students where id <= 4; |
例3：查询姓名不是“黄蓉”的学生:
|
1 2 |
select * from students where name != '黄蓉'; |
例4：查询没被删除的学生:
|
1 2 |
select * from students where is_delete=0; |
8. 逻辑运算符查询
- and
- or
- not
例1：查询编号大于3的女同学:
|
1 2 |
select * from students where id > 3 and gender=0; |
例2：查询编号小于4或没被删除的学生:
|
1 2 |
select * from students where id < 4 or is_delete=0; |
例3：查询年龄不在10岁到15岁之间的学生:
|
1 2 |
select * from students where not (age >= 10 and age <= 15); |
说明:
- 多个条件判断想要作为一个整体，可以结合‘()’。
9. 模糊查询
- like是模糊查询关键字
- %表示任意多个任意字符
- _表示一个任意字符
例1：查询姓黄的学生:
|
1 2 |
select * from students where name like '黄%'; |
例2：查询姓黄并且“名”是一个字的学生:
|
1 2 |
select * from students where name like '黄_'; |
例3：查询姓黄或叫靖的学生:
|
1 2 |
select * from students where name like '黄%' or name like '%靖'; |
10. 范围查询
- between .. and .. 表示在一个连续的范围内查询
- in 表示在一个非连续的范围内查询
例1：查询编号为3至8的学生:
|
1 2 |
select * from students where id between 3 and 8; |
例2：查询编号不是3至8的男生:
|
1 2 |
select * from students where (not id between 3 and 8) and gender='男'; |
11. 空判断查询
- 判断为空使用: is null
- 判断非空使用: is not null
例1：查询没有填写身高的学生:
|
1 2 |
select * from students where height is null; |
注意:
- 不能使用 where height = null 判断为空
- 不能使用 where height != null 判断非空
- null 不等于 ” 空字符串
12. 排序查询语法
排序查询语法：
|
1 2 |
select * from 表名 order by 列1 asc|desc [,列2 asc|desc,...] |
语法说明:
- 先按照列1进行排序，如果列1的值相同时，则按照 列2 排序，以此类推
- asc从小到大排列，即升序
- desc从大到小排序，即降序
- 默认按照列值从小到大排列（即asc关键字）
例1：查询未删除男生信息，按学号降序:
|
1 2 |
select * from students where gender=1 and is_delete=0 order by id desc; |
例2：显示所有的学生信息，先按照年龄从大–>小排序，当年龄相同时 按照身高从高–>矮排序:
|
1 2 |
select * from students order by age desc,height desc; |
13. 分页查询的语法
|
1 2 |
select * from 表名 limit start,count |
说明:
- limit是分页查询关键字
- start表示开始行索引，默认是0
- count表示查询条数
例1：查询前3行男生信息:
|
1 2 3 4 |
select * from students where gender=1 limit 0,3; 简写 select * from students where gender=1 limit 3; |
14. 聚合函数
聚合函数又叫组函数，通常是对表中的数据进行统计和计算，一般结合分组(group by)来使用，用于统计和计算分组数据。
常用的聚合函数:
- count(col): 表示求指定列的总行数
- max(col): 表示求指定列的最大值
- min(col): 表示求指定列的最小值
- sum(col): 表示求指定列的和
- avg(col): 表示求指定列的平均值
|
1 2 3 4 5 6 7 |
-- 返回非NULL数据的总行数. select count(height) from students; -- 返回总行数，包含null值记录; select count(*) from students; -- 求男生的平均身高, 包含身高是null的 ifnull函数: 表示判断指定字段的值是否为null，如果为空使用自己提供的值。 select avg(ifnull(height,0)) from students where gender = 1; |
15. 分组查询
分组查询就是将查询结果按照指定字段进行分组，字段中数据相等的分为一组。
分组查询基本的语法格式如下：
GROUP BY 列名 [HAVING 条件表达式] [WITH ROLLUP]
说明:
- 列名: 是指按照指定字段的值进行分组。
- HAVING 条件表达式: 用来过滤分组后的数据。
- WITH ROLLUP：在所有记录的最后加上一条记录，显示select查询时聚合函数的统计和计算结果
-
group by的使用
group by可用于单个字段分组，也可用于多个字段分组
|
1 2 3 4 5 |
-- 根据gender字段来分组 select gender from students group by gender; -- 根据name和gender字段进行分组 select name, gender from students group by name, gender; |
- group by + group_concat()的使用
group_concat(字段名): 统计每个分组指定字段的信息集合，每个信息之间使用逗号进行分割
|
1 2 3 |
-- 根据gender字段进行分组， 查询gender字段和分组的name字段信息 select gender,group_concat(name) from students group by gender; |
- group by + 聚合函数的使用
|
1 2 3 4 5 |
-- 统计不同性别的人的平均年龄 select gender,avg(age) from students group by gender; -- 统计不同性别的人的个数 select gender,count(*) from students group by gender; |
- group by + having的使用
having作用和where类似都是过滤数据的，但having是过滤分组数据的，只能用于group by
|
1 2 3 |
-- 根据gender字段进行分组，统计分组条数大于2的 select gender,count(*) from students group by gender having count(*)>2; |
- group by + with rollup的使用
with rollup的作用是：在最后记录后面新增一行，显示select查询时聚合函数的统计和计算结果
|
1 2 3 4 5 |
-- 根据gender字段进行分组，汇总总人数 select gender,count(*) from students group by gender with rollup; -- 根据gender字段进行分组，汇总所有人的年龄 select gender,group_concat(age) from students group by gender with rollup; |
16. 连接查询
连接查询可以实现多个表的查询，当查询的字段数据来自不同的表就可以使用连接查询来完成。
连接查询可以分为:
- 内连接查询
- 左连接查询
- 右连接查询
- 自连接查询
内连接查询语法格式:
|
1 2 |
select 字段 from 表1 inner join 表2 on 表1.字段1 = 表2.字段2 |
左连接查询语法格式:
|
1 2 |
select 字段 from 表1 left join 表2 on 表1.字段1 = 表2.字段2 |
右连接查询语法格式:
|
1 2 |
select 字段 from 表1 right join 表2 on 表1.字段1 = 表2.字段2 |
自连接查询的用法: 左表和右表是同一个表，根据连接查询条件查询两个表中的数据。
|
1 2 |
select c.id, c.title, c.pid, p.title from areas as c inner join areas as p on c.pid = p.id where p.title = '山西省'; |
17. 子查询的介绍
在一个 select 语句中,嵌入了另外一个 select 语句, 那么被嵌入的 select 语句称之为子查询语句，外部那个select语句则称为主查询.
查询大于平均年龄的学生:
|
1 2 |
select * from students where age > (select avg(age) from students); |
18. 外键约束作用
外键约束:对外键字段的值进行更新和插入时会和引用表中字段的数据进行验证，数据如果不合法则更新和插入会失败，保证数据的有效性
- 对于已经存在的字段添加外键约束
|
1 2 3 |
-- 为cls_id字段添加外键约束 alter table students add foreign key(cls_id) references classes(id); |
- 在创建数据表时设置外键约束
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
-- 创建学校表 create table school( id int not null primary key auto_increment, name varchar(10) ); -- 创建老师表 create table teacher( id int not null primary key auto_increment, name varchar(10), s_id int not null, foreign key(s_id) references school(id) ); |
- 删除外键约束
|
1 2 3 4 5 6 |
-- 需要先获取外键约束名称,该名称系统会自动生成,可以通过查看表创建语句来获取名称 show create table teacher; -- 获取名称之后就可以根据名称来删除外键约束 alter table teacher drop foreign key 外键名; |
- 添加外键约束: alter table 从表 add foreign key(外键字段) references 主表(主键字段);
- 删除外键约束: alter table 表名 drop foreign key 外键名;
TIPS: 数据库设计之三范式的介绍
范式: 对设计数据库提出的一些规范，目前有迹可寻的共有8种范式，一般遵守3范式即可。
- 第一范式（1NF）: 强调的是列的原子性，即列不能够再分成其他几列。
- 第二范式（2NF）: 满足 1NF，另外包含两部分内容，一是表必须有一个主键；二是非主键字段 必须完全依赖于主键，而不能只依赖于主键的一部分。
- 第三范式（3NF）: 满足 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
