MySql数据库——数据表操作

-- 查看数据库内的所有表(得先进入待查询的数据库）
show tables;

--查看表数据类型
desc tb_name;

-- 查看tb_name表的所有数据
select * from tb_name;

一、数据表插入数据

-- 插入
insert into tb_name[(column[,column…])] values (val[,val… ])

-- 创建表并设置属性
create table t_user(
	id int,
	name varchar(20),
	age int,
	gender enum('male','female')
	);

insert into t_user values(1,'张三',18,'male');
insert into t_user (name,id) values ('赵四',2);   
insert into t_user (id,name,age) values (3,'王五'40),(4,'燕小六',35);

-- 创建另一张表
create table user(
	id int,
	name varchar(20),
	age int,
	gender enum('male','female')
	);

insert into user(select * from t_user);    # 可以整张表进行插入

二、修改表数据

-- 修改
update tb_name set col_name = val[, col_name = val …] [where 子句]

update t_user set age = 18;     # 修改该表所有的age为18，有可能会提示安全隐患
update t_user set age = 18,gender = 'femal' where name = '张三';

三、删除表数据

-- 删除
-- 1、删除的基本单位时记录，如果不想删除记录而只想删除这条记录某个字段的值，可以用update将该字段的值置为null
-- 2、如果没有where子句，就会删除表中所有的记录
-- 3、delete只会删除记录，不会删除表，如果删除整张表，就可以使用drop table。
delete from tb_name [where 子句];

# 练习1、删除名为为张三的所有记录
delete from t_user where name = '张三';
# 练习2、清空t_user表中所有记录
delete from t_user;

四、查询表数据

1、模糊查询

-- 1、like 与任何通配符搭配使用，进行模糊查询
-- 2、%:匹配任何数目的字符，包括零个字符
-- 3、_:匹配一个字符，也就说_必须占一个字符

# 练习1、查询所有姓名里包含“太”字的名字
 slect name from heros where name like '%太%';
# 练习2：除第一字外，其余字中包含“太”的英雄有哪些
 select name from heros where name like '_%太%';
 

2、简单查询

-- a、计算表达式和函数值
 select 2*3;
 select 2 * 3;
 select now();
 select year(now());
 select concat('ab', 'cd', 'xyz');      # 合并字符串
 select concat('"', trim(' abc  xyz  '), '"');
 select abs(-100);

-- b、查询表中的字段
# 练习1：查询heros表中所有英雄的姓名
 select name from heros;
-- 查询多个字段的值，多个字段之间用','分隔
 select name,hp_max,mp_max from heros;
-- 查询表中所有信息
 select * from heros;

3、过滤查询

-- 使用where设置查询过滤条件
 # 查询heros表中姓名为花木兰的英雄
 select * from heros where name='花木兰';
 

4、排序查询

-- 给字段起别名，不建议使用
 select name,hp_max as hp,mp_max as mp from heros;
 select name,hp_max hp,mp_max mp from heros;      # as可以省略

-- 排序
'order by'可以对结果集进行排序，'asc'表升序，'desc'表降序，默认情况为升序

# 练习1：查询所有英雄，并按照最大生命值排序
 select name,hp_max from heros order by hp_max;
 select name,hp_max from heros order by hp_max asc;
 select name,hp_max from heros order by hp_max desc;
 
# 练习2：可以对多个字段进行排序，先按照第一个字段排序，当第一个字段相同时，再按照第二个字段排序，依此类推。
 select name,hp_max,mp_max from heros order by hp_max,mp_max;
 select name,hp_max,mp_max from heros order by hp_max asc,mp_max desc;

# 练习3：可以对非选择字段能进行排序，也就是说排序的字段不一定要在结果集中
 select name,hp_max from heros order by hp_max asc,mp_max desc;

# 练习4：可以将字段选算的结果集进行排序
 select name,hp_max from heros order by (hp_max+mp_max) desc;

-- 计算字段 计算字段并不实际存在于数据库表中，它是由表中的其它字段计算而来的。一般我们会给计算字段起一个的别名。
SELECT name, hp_max + mp_max FROM heros;
SELECT name, hp_max + mp_max AS total_max FROM heros;
 

5、限制结果集

-- lemit 可以限制结果集的数量
-- 其中：LIMIT offset, nums (offset 表示偏移量，nums 表示记录行的最大数)
--      LIMIT nums OFFSET offset


# 练习1：检索数据表前五行
 select * from db_name limit 0,5
 select * from db_name limit 5 offset 0;
 select * from db_name limit 5;          // 因为第一条记录的偏移量为0所以可以省略
 
# 练习2：检索数据表第6行到第15行
 SELECT * FROM table LIMIT 5,10;    // 检索记录行 6-15   
  
# 练习3：为了检索从某一个偏移量到记录集的结束所有的记录行，可以指定第二个参数为 -1：    
 SELECT * FROM table LIMIT 95,-1;    // 检索记录行 96-last.   
  

6、聚合函数

-- count() 统计函数
select count(*) from heros;      // 统计heros表中所有记录个数

-- sum() 统计某个字段(具体字段)非null值的和
select sum(hp_max) from heros;

-- avg() 统计某个字段非null值的平均值
select round(avg(hp_max), 2) from heros;      // 将平均值保留两位小数

-- max() 求字段非null的最大值
-- min() 求字段非null的最小值

-- distinct 去重判断
 select count(distinct hp_max) from heros;      

7、分组查询

group by 可以对记录进行分组

1、搭配聚合函数使用

# 按照主要角色定位进行分组，并统计每一组的英雄数目
 SELECT COUNT(*) as '总数',role_main FROM heros GROUP BY role_main;

2、统计详细内容

# 列出某属性的所有对象
select role_main, group_concat(name) from heros group by role_main;

3、多字段分组

# 我们可以对多个字段进行分组。也就是说，每个字段的值都相同的记录为一组。
SELECT COUNT(*) AS num, role_main, role_assist 
FROM heros 
GROUP BY role_main, role_assist 
ORDER BY num DESC;

4、HAVING 过滤分组

# 我们想要按照英雄的主要角色定位，次要角色定位进行分组，并且筛选分组中英雄数目大于 5 的组，最后根据每组的英雄数目从高到低进行排序。
SELECT COUNT(*) AS num, role_main, role_assist 
FROM heros 
GROUP BY role_main, role_assist 
HAVING num > 5
ORDER BY num DESC;

tips: 常用运算符

-- a. 算术运算符：+ - * / %
-- b. 比较运算符：=, <>(!=), >, <, >=, <=, <=>, is null, is not null
               between and, in, not in, like
-- c. 逻辑运算符：NOT(!)  AND(&&)  OR(||)
-- d. 位运算符：& | ~  ^ << >>
# = 和 <=> 表示安全的等于（null不能用'=' '< >'比较）

# 练习1：查询hp_max大于等于7000，小于等于8050的英雄有哪些？
 select name,hp_max from heros where hp_max >= 7000 and hp_max <= 8050;
 select name,hp_max from heros where hp_max between 7000 and 8050;
 
# 练习2：主要角色定义是战士和法师的英雄有哪些？ 
 select name, role_main from heros where role_main = '战士' or role_main = '法师';
 select name, role_main from heros where role_main in ('战士', '法师');

五、查询总结

1、select书写顺序

 SELECT ... 
 FROM ... 
 WHERE ... 
 GROUP BY ...
 HAVING ... 
 ORDER BY ... 
 LIMIT ...

2、 语句执行顺序

 FROM --> 
 WHERE --> 
 GROUP BY --> 
 HAVING --> 
 SELECT --> 
 DISTINCT --> 
 ORDER BY -->
 LIMIT

详细解释一下 SQL 的执行顺序：

1. 首先是从 FROM 开始执行的。在这个阶段，如果是多表连接查询，还会经历以下几个步骤：
   1.1 通过 CROSS JOIN 求得笛卡尔乘积，得到虚拟表 vt1-1;
   1.2 通过 ON 进行连接，在 vt1-1 的基础上进行筛选，得到虚拟表 vt1-2;
   1.3 如果是外连接，还会在 vt1-2 的基础上添加外部行，得到虚拟表 vt1-3;
   1.4 如果连接的表不止两张，还会重复上面步骤，直到所有表都处理完成。这个过程完成之后，我
   们就得到了虚拟表 vt-1，也就是我们的原始数据。
2. WHERE 会在 vt-1 的基础上进行筛选，得到虚拟表 vt-2。
3. GROUP BY 会在 vt-2 的基础上进行分组，得到虚拟表 vt-3。
4. HAVING 会在 vt-3 的基础上对分组进行筛选，得到虚拟表 vt-4。
5. SELECT 会在 vt-4 的基础上提取想要的字段，得到虚拟表 vt-5。
6. DISTINCT 会在 vt-5 的基础上，去掉重复行，得到虚拟表 vt-6。
7. ORDER BY 会按照指定的字段对 vt-6 进行排序，得到虚拟表 vt-7。
8. LIMIT 会在 vt-7 的基础上提取指定的记录，得到虚拟表 vt-8。