回顾

  1. mysql -uroot -p
  2. 123456
  3. 显示数据库
  4.     show databases；
  5.     create database java2204
  6.     use java2204
  7.     create table person(id int,name varchar(32));
  8. 表里插入数据
  9.     insert into peroson values(1,"朱志伟");
  10. 删除数据
  11.     delete from person where id = 1;
  12. 修改数据
  13.     update person ser name = "博儿" where id = 1;
  14. alter 修改

一、MySQL查询

在开发中，查询比增删改都重要

网站页面显示就是查询，所以查询偏多，查询将是一个重点

1.查询【重点】别名

select语句用于从表中查询选取的数据

结果被储存在一个结果表中，称为结果集。

语法格式：select 列明 from 表名； 查询指定字段的数据

或者：select * from 表明； 查询所有字段的数据

  1. #select 列明 from 表名；
  2. select id,name from person;
  4. #按照字段进行查询的时候，还可以对字段起别名
  5. #select 字段1 as "别名1",字段2 as "别名2" from 表明;
  6. select id as "编号",name as "名字" from person;

where子句

运算符

如需有条件的从表中选取数据，可将where子句添加到select语句

语法格式：

  1. select 列明 from 表明 where 列名 运算符 值;
  3. 运算符：=  >  >=  <  <=  !=(<>)  &&(and)  ||(or)

案例

  1. #查询年龄等于24岁的；
  2. select * from person where age =24;
  3. #查询年龄大于30岁的
  4. select * from person where age > 30;
  5. #查询年龄不等于50的
  6. select * from person where age != 50;
  8. #查询年龄等于23岁的且性别等于0的
  9. select * from person where age = 23 && grnder = 0;
  10. select * from person where age = 23 and grnder = 0;
  12. #查询年龄等于23岁的或者年龄等于50的
  13. select * from person where age = 23 || age = 50;
  14. select * from person where age = 23 or age = 50;

排序order by

order by 只能放在where后面

  1. #按照薪资排序，默认是升序 asc省略了没写
  2. select * from person order by salary
  4. #降序
  5. select * from person order by salary desc;
  7. #年龄小于40岁的，然后降序排列
  8. select * from person where id < 40 order by age desc;
  10. #先按照薪资升序排列，如果薪资一样，在按年龄降序排列
  11. select * from person order by salary asc,age desc;

限制limit

分页的时候需要使用，限制输出 只能放在where后面

语法格式

  1. limit 数字1
  2. limit 数字1，数字2

案例

  1. #输出打印3条数据
  2. select * from person limit 3;
  4. #略过3个数据(从第四个开始)，展示三条数据
  5. select * from person limit 3,3;
  7. #查找年龄小于40的，打印出来三个
  8. select * from person where age < 40 limit 3;

分页

  1. limit在做分页的时候有规律 偏移量
  2. 当前页数 pageNo = 1 pageSize = 3
  3. limit (pageNo-1)*pageSize,pageSize;

2.SQL一些内置函数（看官方手册）

  1. #SQL的内置函数
  2. #一般不建议使用，
  3. #语法格式：select sql函数 from person;
  5. #获取年龄这一列的最大值 max
  6. select max(age) from person;
  8. #平均值 avg
  9. select avg(salary) from person;
  11. #求和 sum
  12. select sum(salary) from person;
  14. #统计数据库中有多少条数据 count(*) null不算数据
  15. select count(*) from person;
  16. select count(age) from person;
  18. #获取当前时间 now
  19. select now() from person;

SQL语句的嵌套

  1. #找出年龄最大的这条数据 方式1 如果最大年龄的不是一个人 只打印一个，有漏洞
  2. select * from person order by age desc limit 1;
  4. #找出年龄最大的这条数据 方式2 
  5. #一个sql语句的结果当成另一个sql语句的条件
  6. select * from person where age = (select max(age) from person);

模糊查询like

大量查询的时候使用。

关键字 like

语法格式

  1. select * from 表明 where 字段 like 模糊的语法格式

模糊的语法格式：

  1. %或者_

案例

  1. select * from person where name like "朱_";
  2. #打印朱航
  4. select * from person where name like "朱__";
  5. #打印朱志伟
  7. #一个下划线是一个占位 
  9. select * from person where name like "_小_";
  11. #用%%，只要包含朱的
  12. select * from person where name like "%朱%";
  13. #最后一个字是天的，前面随意
  14. select * from person where name like "%天";
  15. #第一个字是朱的，后面随意
  16. select * from person where name like "朱%";

分组统计 group by

group by

  1. #按照性别进行分组统计
  2. #注意事项 select和from中间必须写要分组统计的字段(SQL内置函数得到的结果)
  3. select gender from person group by gender;
  4. +--------+
  5. | grnder |
  6. +--------+
  7. |   NULL |
  8. |      1 |
  9. +--------+
  10. #统计出 null有几个 1有几个。
  11. select gender,count(*) from person group by gender;
  12. +--------+----------+
  13. | grnder | count(*) |
  14. +--------+----------+
  15. |   NULL |        3 |
  16. |      1 |        7 |
  17. +--------+----------+
  19. #按照性别进行分组，统计个数大于6的性别组
  20. #用关键字having
  21. #错误形式如下：
  22. select gender,count(*) from person where count(*) > 6 group by gender;  #错误的
  23. #注意事项：where在分组之前执行，分组以后的条件在进行操作用having
  24. select gender,count(*) from person group by gender having count(*) > 6;
  25. +--------+----------+
  26. | grnder | count(*) |
  27. +--------+----------+
  28. |      1 |        7 |
  29. +--------+----------+
  31. #找出年龄大于20的信息，然后按照性别分组，统计结果个数大于3的数据
  32. select gender,count(*) from person where age > 20 group by gender having count(*) > 3;
  33. +--------+----------+
  34. | grnder | count(*) |
  35. +--------+----------+
  36. |      1 |        5 |
  37. +--------+----------+

需要自己练习的：

distinct 去重

  1. #去除重复的id
  2. select distinct id from person;

between 取介于两个值之间范围的值

  1. select * from person where age between 20 and 25;

二、数据的约束问题

在创建表的时候，咱们一定要对字段进行设置约束，这样咱们在插入数据的时候，才是比较规范的。

所以数据约束是创建表的时候都i字段进行约束的。

一个完整的数据表必须加约束

2.1默认值 default

default

  1. #用户在插入数据的时候，如果没有给当前字段赋值，会给一个默认值
  2. create table person1(
  3.     -> id int,
  4.     -> name varchar(25),
  5.     -> country char(20) default "PRC");    #默认值PRC

2.2非空 not null

如果给字段设置了非空，在添加数据的时候，就必须给当前字段赋值

  1. create table person2(
  2.     -> id int not null,  #必须进行赋值
  3.     -> name varchar(32));

2.3唯一 unique

设置字段的值是唯一的，如果添加数据的时候数据重复的话会报错

空不算重复，可以为空

unique

  1. create table person3(
  2.     -> id int,
  3.     -> name varchar(25) unique);  #唯一字段
  5. #案例
  6. mysql> insert into person3 values(1,"朱志伟");
  7. Query OK, 1 row affected (0.40 sec)
  9. mysql> insert into person3 values(2,"朱志伟");
  10. ERROR 1062 (23000): Duplicate entry '朱志伟' for key 'name'

2.4主键【重点】primary key

主键是唯一和非空的组合。

在设计数据库的时候，一定要有一个字段是主键，而且主键的字段一般和业务逻辑无关，比如name和age不能设置为主键，一般id作为主键

primary key

  1. create table person4(
  2.     -> id int primary key,
  3.     -> name varchar(32));

2.5自增长auto_increment

在插入数据的时候，有些字段是可以时间自增长的，自己增加1

auto_increment 一般开发中，主键设置为自增长

加入了自增长的，就不用给赋值了，默认从1开始，每加一个数据 自增1

在删除最后一条数据的时候，然后在插入一条时，这条数据的还是在原来基础上增加1的

如果设置了主键值，下一条自动从设置的主键值开始自增

  1. create table person5(
  2.     -> id int primary key auto_increment,
  3.     -> name varchar(32));

完整的表创建方式

text 不能设置默认值

  1.  create table person6(
  2.     -> id int primary key auto_increment,
  3.     -> name varchar(32) not null,
  4.     -> age int not null,
  5.     -> info text);

三、外键的约束

一个表中的某一个字段是另一个表中的主键

案例

  1. #创建一个员工表 id 员工名 部门 时间
  3. mysql> create table employee(
  4.     -> id int primary key auto_increment,
  5.     -> empName varchar(32) not null,
  6.     -> deptName varchar(32) not null,
  7.     -> regTime timestamp default current_timestamp
  8.     -> );
  9. Query OK, 0 rows affected (0.64 sec)
  11. mysql> insert into employee(empName,deptName) values("谷小天","董事长");
  12. Query OK, 1 row affected (0.45 sec)
  14. mysql> insert into employee(empName,deptName) values("朱航","大股东");
  15. Query OK, 1 row affected (0.05 sec)
  17. mysql> insert into employee(empName,deptName) values("朱志伟","打工仔");
  18. Query OK, 1 row affected (0.13 sec)
  20. mysql> insert into employee(empName,deptName) values("张桂园","技术部");
  21. Query OK, 1 row affected (0.20 sec)
  23. mysql> insert into employee(empName,deptName) values("王梦哲","技术部");
  24. Query OK, 1 row affected (0.04 sec)
  26. mysql> insert into employee(empName,deptName) values("刘子贤","大神");
  27. Query OK, 1 row affected (0.13 sec)
  29. mysql> select * from employee;
  30. +----+-----------+-----------+---------------------+
  31. | id | empName   | deptName  | regTime             |
  32. +----+-----------+-----------+---------------------+
  33. |  1 | 谷小天    | 董事长    | 2022-04-24 15:36:12 |
  34. |  2 | 朱航      | 大股东    | 2022-04-24 15:36:23 |
  35. |  3 | 朱志伟    | 打工仔    | 2022-04-24 15:36:32 |
  36. |  4 | 张桂园    | 技术部    | 2022-04-24 15:37:09 |
  37. |  5 | 王梦哲    | 技术部    | 2022-04-24 15:37:20 |
  38. |  6 | 刘子贤    | 大神      | 2022-04-24 15:37:35 |
  39. +----+-----------+-----------+---------------------+
  40. 6 rows in set (0.00 sec)

部门数据会出现冗余，

所以要建两个表，并且设计外键的约束

完善后的案例

  1. #部门表
  2. create table dept(
  3.     -> id int primary key auto_increment,
  4.     -> deptName varchar(32) not null);
  6. #数据表
  7.  create table employee(
  8.     -> id int primary key auto_increment,
  9.     -> empName varchar(32) not null,
  10.     -> deptId int not null,
  11.     -> regTime timestamp default current_timestamp,
  12.      #constraint 约束的意思
  13.      #fk_emp_dept 外键的名字可以随便起，一般fk_一张表名_另一张表名
  14.      #foreign key(deptId) 外键 本表中的一个字段，去关联另个表的字段
  15.      #references dept(id) 关联的另一个表中的字段
  16.     -> constraint fk_emp_dept foreign key(deptId) references dept(id)
  17.     -> );
  19. #插入数据
  20. insert into employee(empName,deptId) values("谷小天",1);
  22. #如果插入一个部门id中没有的数据 会报错 没有部门8
  23. insert into employee(empName,deptId) values("朱志伟",8);
  24. ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`java2204`.`employee`, CONSTRAINT `fk_emp_dept` FOREIGN KEY (`deptId`) REFERENCES `dept` (`id`))

问题1

关联以后，增加数据和删除数据容易报错

总结：

  1. 如果两张表添加了外键约束，这两张表就有关系了，
  3. 添加数据：先添加主表【部门表】，在添加从表【员工表】
  5. 删除数据：先删除从表【员工表】，在删除主表【部门表】
  7. 修改数据：先修改从表【员工表】，在修改主表【部门表】

级联操作

on delete cascade

on update cascade

可以直接对主表和从表的内容进行修改，没有限制

  1. mysql> create table employee(
  2.     -> id int primary key auto_increment,
  3.     -> empName varchar(32) not null,
  4.     -> deptId int not null,
  5.     -> regTime timestamp default current_timestamp,
  6.     -> constraint fk_emp_dept foreign key(deptId) references dept(id)
  7.     -> on delete cascade
  8.     -> on update cascade
  9.     -> );
  10. Query OK, 0 rows affected (0.32 sec)
  12. mysql> insert into employee(empName,deptId) values("谷小天",1);
  13. Query OK, 1 row affected (0.04 sec)
  15. mysql> insert into employee(empName,deptId) values("朱航",2);
  16. Query OK, 1 row affected (0.08 sec)
  18. mysql> insert into employee(empName,deptId) values("刘子贤",4);
  19. Query OK, 1 row affected (0.06 sec)
  21. mysql> insert into employee(empName,deptId) values("朱志伟",5);
  22. Query OK, 1 row affected (0.04 sec)
  24. mysql> select * from employee;
  25. +----+-----------+--------+---------------------+
  26. | id | empName   | deptId | regTime             |
  27. +----+-----------+--------+---------------------+
  28. |  1 | 谷小天    |      1 | 2022-04-24 16:25:14 |
  29. |  2 | 朱航      |      2 | 2022-04-24 16:25:16 |
  30. |  3 | 刘子贤    |      4 | 2022-04-24 16:25:18 |
  31. |  4 | 朱志伟    |      5 | 2022-04-24 16:25:20 |
  32. +----+-----------+--------+---------------------+
  33. 4 rows in set (0.00 sec)
  35. mysql> delete from dept where id = 4;
  36. Query OK, 1 row affected (0.13 sec)
  38. mysql> select * from employee;
  39. +----+-----------+--------+---------------------+
  40. | id | empName   | deptId | regTime             |
  41. +----+-----------+--------+---------------------+
  42. |  1 | 谷小天    |      1 | 2022-04-24 16:25:14 |
  43. |  2 | 朱航      |      2 | 2022-04-24 16:25:16 |
  44. |  4 | 朱志伟    |      5 | 2022-04-24 16:25:20 |
  45. +----+-----------+--------+---------------------+
  46. 3 rows in set (0.00 sec)

重点 级联操作

  1. #部门表 主表
  2. create table dept(
  3.     -> id int primary key auto_increment,
  4.     -> deptName varchar(32) not null);
  6. #员工表 从表
  7. mysql> create table employee(
  8.     -> id int primary key auto_increment,
  9.     -> empName varchar(32) not null,
  10.     -> deptId int not null,
  11.     -> regTime timestamp default current_timestamp,
  12.     -> constraint fk_emp_dept foreign key(deptId) references dept(id)
  13.     -> on delete cascade
  14.     -> on update cascade
  15.     -> );
  17. #修改主表和从表内容不受限制，主表内容变化，从表也会跟随变化