MySQL表的增删改查

MySQL的数据类型

数据库中的表，每一个列都是带有类型的（所有行的对应列存的数据类型必须是一致的）

• 常用类型：

bit/ tinyint /smallint /int/ bigint/ float /double /decimal/ numeric）

单精度/双精度浮点数（float(M,D), double(M,D)）：
指定类型带参数的：M表示有效数字的位数，D表示小数点后保留几位。
（内存模型就决定了无法精确表示数据：存在误差)
decimal(M,D) 精确的表示浮点数的：牺牲了存储空间，牺牲了运算速度，换来的是更精确的表示方式。

• 字符串类型：
  VARCHAR(SIZE)：最常用的表示字符串的类型，带有一个参数，约定了存储的最大空间。
  varchar(128) 这个列最多存储128个字符, 根据实际需求，来决定设置多长合适。

TEXT 长文本数据
MEDIUMTEXT 中度长度文本数据
BLOB：主要存储二进制数据

• 日期类型
  DATETIME：8个字节
  TIMESTAMP：4个字节，范围从1970年到2038年，自动检索当前时区并进行转换。
  （时间戳）

MySQL表的增删改查

要想进行表操作，务必先选中数据库（先use数据库名）

1.创建表
同一个数据库中，不能有两个名字相同的表
表名和列名不能和MySQL中的关键字名相同（如果实在想搞，可以给表名/列名 加上反引号引起来）

create table 表名(列名 类型，列名 类型....);

2.查看指定数据库下的所有表
选中哪个数据库，就能看到哪些数据表~

show tables;

3.查看指定表的结构

desc 表名;

4.删除表

drop table 表名;

• 新增 Create

insert into student (100, 103, 'yuan');
insert into student (id, sn, name) values(101, 104, 'bobo' );

批量插入比多次插入的效率更高, 由于网络请求和响应的时间开销引起的, 数据库服务器是把数据保存在硬盘上,MySQL关系型数据库，每次进行一个sql操作，内部都会开启一个事务，每次开启事务也有一定的开销。 因此在进行IO操作的时候,数据量会对效率有影响较小, 影响更大的是IO操作的次数。

• 查询 Retrieve

select * from student;  --全列查询
select id, name, english from exam_result;    -- 指定列查询
select id, name, chinese+math+english 总分 from exam_result;    --别名
select distinct math from exam_result;      -- 去重查看                                   
select name, qq_mail from student order by qq_mail desc;        --排序(降序)

• 条件查询: 根据查询结果，按行进行筛选

select name, english from exam_result where english < 60 or english is null;
select * from exam_result where chinses > 80 and english < 90;
select name from exam_result where chinese between 80 and 90;
select * name, math exam_result where math in (58,59,60,61);
select name from exam_result where name like '孙%'   --模糊查询
select name from exam_result where name like '孙_'
select name qq_mail form exam_result where qq_mail is not null;

1.进行表达式查询的时候,查询结果是一个"临时表", 这个临时表, 并不是要写入硬盘中的,临时表的类型也不是和原始的表完全一致的。 select 只是查询，不会修改硬盘中的数据。
2.MySQL中的数据排序order by子句， MySQL 优化器会根据查询的特性和可用资源选择最优的排序算法，以提供最高效的查询性能；
3.order by 排序时， NULL值为最小值；SQL中，NULL值和任何值进行运算，结果还是NULL；
4.模糊查询，对于数据库来说，查询开销比较大。

• 修改 Update

upda exam_result set math = 80 where name = '孙悟空';

• 删除 Delete

delete from exam_result where name = '孙悟空';

MySQL数据库约束

关系型数据库的一个重要功能。需要保证数据的“完整性”，正确的数据~可以通过人工的方式来观察确认数据的正确性（但是不合适，可能会由于人的疏忽，把一些错误没检查出来）
约束，其实就是让数据库帮助程序员更高的检查数据是否正确。
约束，是可以组合在一起来使用的，

• 常见约束

not null
unique
default
primary key
foreign key --针对两个表，产生的约束；保证一个表中的数据匹配另一个表中的值的参照完整性
check

主键 primary key 相当于 unique 加上 not null
主键也同样是在插入记录的时候，需要先查询，再进行真正的插入~
正因为 主键 和 unique都有先查询的过程，MySQL就会默认给primary key 和 unique这样的列，自动添加索引，来提高查询的速度。
1）实际开发中，大部分的表，一般都会带有一个主键，主键往往是一个整数表示的id~
2）在MySQL中，一个表中，只能有一个主键。
3）虽然主键不能有多个，MySQL允许把多个列放到一起共同作为一个主键（联合主键）
4）主键另外一个非常常用的用法，就是使用MySQL自带的“自增主键”作为主键的值。
（主键需要保证不重复，MySQL数据库帮助我们自动来生成）
（auto_increment 每次插入数据的时候，MySQL就会自动找到上一条记录的id，在这个基础上进行自增）
自增主键并不会重复利用中间的空隙，是依照之前的最大值往后累加。

表设计

1.明确项目中需要使用的数据库，数据库表，表的字段
2.先明确实体，再明确实体之间的关系；（一对一，一对多，多对多(引入关联表) ），根据关系进行建表

新增

insert into test_user(name, email) select name, qq_mail from student;  -- 插入查询结果

查询

• 聚合查询: 本质上是针对 行和行之间 进行运算
  聚合函数：针对某个列的所有行进行运算

select count(qq_mail) from student;
select sum(math) from exam_result where math > 60;
select avg(chinese+math+english) 平均总分 from exam_result;
select max(english) from exam_result;
select min(math) from exam_result math > 70;

• group by子句：分组查询

select role,max(salary),min(salary),avg(salary) from emp group by role;

分组查询，是可以指定条件的

   1.分组之前，指定条件；先筛选，再分组， where
    select role, avg(salary) form emp where name != "马云" group by role;
    2.分组之后，指定条件；先分组，再筛选，having
    select role, avg(salary) from emp group by role having avg(salary)<1500;
    3.分组之前和之后，都指定条件；
    select role, avg(salary) from emp where name != '马云' group by role having role != '程序员';

HAVING子句

select role,max(salary),min(salary),avg(salary) from emp group by role having avg(salary)<1500;

• 联合查询：多表查询
  多表查询的基本执行过程：笛卡尔积：分别取出第一张表的每一行，和第二张表的每一行，配对得到一个新的记录；
  笛卡尔积是通过排列组合来的，笛卡尔积得到一个更大的表，列数就是两个表列数之和，行数就是两个表行数之积；
  将两个表进行笛卡尔积：

1.先计算笛卡尔积
select * from student, score;
2.引入连接条件
select * from student, score where student.id = class.student_id;
3.再根据需求条件,加入必要的条件
select * from student, class where student.id = class.sudent_id and student.name = '张三';

内连接：针对某个列的所有行进行运算

select * from student inner join score on student.id = score.student.id and student.name = '许仙';

外连接：

左外连接：会把左表的结果尽量列出来，右表中没有对应的记录，就是用null填充
select * from student left join score on student.id = score.student_id;
右外连接：会把右 表的结果尽量列出来，右表中没有对应的记录，就是用null填充
select * from student right join score on student.id = score.student_id;

自链接：自己和自己进行笛卡尔积，把行转成列

select * from score as s1, score as s2 where s1.student_id = s2.student_id and s1.course_id=3 and s2.course_id=1;

• 子查询：嵌套查询

单行子查询
select * from student where class_id=(select classes_id from student where name='许仙')；
多行子查询
select * from score where course_id in (select id from course where name='语文' or name='英语');

• 合并查询：取得两个结果集的并集
  union :自动去掉结果集中的重复行

select * from course where id<3 union select * from course where name='英文';
select * from course where id<3 or name='英文';