MySQL的增删改查（进阶）

约束

1.NULL约束

mysql> create table student(
    -> sn int not null,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.36 sec)

mysql> insert into student values(null,'s1');
ERROR 1048 (23000): Column 'sn' cannot be null

这里sn在创建时约束不能为空，添加时为空编译报错

2.UNIQUE约束（唯一）

mysql> drop table if exists student;
Query OK, 0 rows affected (0.49 sec)

mysql> create table student(
    -> sn int unique,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.34 sec)

mysql> insert into student values(1,'s1');
Query OK, 1 row affected (0.36 sec)

mysql> insert into student values(1,'s1');
ERROR 1062 (23000): Duplicate entry '1' for key 'sn'

3.默认值约束DEFAULT

mysql> create table student(
    -> sn int unique,
    -> name varchar(20) default '不知道'
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql>
mysql> insert into student(sn) values(null);
Query OK, 1 row affected (0.03 sec)

结果：

select * from student;
+------+-----------+
| sn   | name      |
+------+-----------+
| NULL | 不知道    |
+------+-----------+

5主键约束
primary (唯一并且不为空）

mysql> drop table if exists student;
Query OK, 0 rows affected (0.51 sec)

mysql> create table student(
    -> id int primary key auto_increment,--primary（唯一且不为空，auto_increment 自增）
    -> sn int unique,--唯一
    -> name varchar(20) default '不知道'   -- 默认值
    -> );
Query OK, 0 rows affected (0.33 sec)

mysql>
mysql> insert into student(sn,name) values(23,'s1');--因为有上面的自增，所以id那里就不管
Query OK, 1 row affected (0.35 sec)

mysql> insert into student(sn,name) values(24,'s1');
Query OK, 1 row affected (0.03 sec)

mysql> select * from student;
+----+------+------+
| id | sn   | name |
+----+------+------+
|  1 |   23 | s1   |
|  2 |   24 | s1   |
+----+------+------+
2 rows in set (0.00 sec)

6.外键约束
一个班级有多个学生，多个同学对应一个班级

drop table if exists classes;
create table classes(
	id int primary key auto_increment,
	name varchar(20)
);


drop table if exists student;
create table student(
	id int primary key auto_increment,
	sn int unique,
	name varchar(20) default '不知道',
	classes_id int,
	foreign key (classes_id) references
	classes(id) --外键的语法，一个班级对应多个学生
);

insert into classes(id,name) values(1,'2020届5班');

insert into student(sn,name,classes_id) values(23,'s1',1);
insert into student(sn,name) values(24,'s1');

表的设计

多对多
我们知道一个学生有多个课程，一个课程又有多个学生，为了建立联系
我们需要一个学生课程中间表

drop table if exists classes;
create table classes(
	id int primary key auto_increment,
	name varchar(20)
);


drop table if exists student;
create table student(
	id int primary key auto_increment,
	sn int unique,
	name varchar(20) default '不知道',
	classes_id int,
	foreign key (classes_id) references classes(id)
);

drop table if exists course;
create table course(
	id int primary key auto_increment,
	name varchar(20)
);

drop table if exists student_course;
create table student_course(
	id int primary key auto_increment,
	student_id int,
	course_id int,
	foreign key (student_id) references student(id)， --一个学生上多个课程
	foreign key (course_id) references course(id) --一个课程有好多学生
);

新增：

将一个表的结果集，插入到另一个表

drop table if exists student;
create table student(
	id int primary key auto_increment,
	sn int unique,
	name varchar(20)
);

insert into student(sn,name) values(23,'s1');
insert into student(sn,name) values(24,'s1');


mysql> create table for_insert(
    -> id int primary key auto_increment,
    -> sn int unique,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.32 sec)

mysql> insert into for_insert(sn,name) select sn,name from student

结果

mysql> select * from for_insert;
+----+------+------+
| id | sn   | name |
+----+------+------+
|  1 |   23 | s1   |
|  2 |   24 | s1   |
+----+------+------+
2 rows in set (0.00 sec)

也可以这样插入

insert into for_insert(sn,name) select sn+2,'s2' from student;

查询

一.聚合查询
先创建这么一个表

create table emp(
	id int primary key auto_increment,
	name varchar(20) not null,
	role varchar(20) not null,
	salary numeric(11,2)
);



insert into emp(name,role,salary) values
('马云','服务员',1000.20),
('马化腾','游戏陪玩',2000.99),
('孙悟空','游戏角色',999.11),
('猪八戒','游戏角色',333.5),
('沙和尚','游戏角色',700.33),
('隔壁老王','董事长',12000.66);

函数
1.count([DISTINCT] expr) 返回到查询数据的数量

mysql> select count(*) from emp;//select count(0) from emp求总行数
+----------+
| count(*) |
+----------+
|        6 |
+----------+

mysql> select count(name) from emp;
+-------------+
| count(name) |
+-------------+
|           6 |
+-------------+
1 row in set (0.00 sec)

2.SUM([DISTINCT] expr)

select sum(salary) from emp;
+-------------+
| sum(salary) |
+-------------+
|    17034.79 |
+-------------+
1 row in set (0.00 sec)

3.AVG([DISTINCT] expr)返回数据的平均值
4.MAX([DISTINCT] expr)返回数据的最大值
5.MIN([DISTINCT] expr)返回数据的最小值

group by 子句
select中使用 group by 子句可以对指定列进行分组查询。需要满足：使用group by进行分组查询时，select 指定的字段必须是“分组依据字段”，其他字段若想出现在select中则必须包含在聚合函数中。

select sum(salary),role from emp group by role;
//例如这里，这个语句就是查询金额总数，如何role时重的，就当作一组重合
+-------------+--------------+
| sum(salary) | role         |
+-------------+--------------+
|     1000.20 | 服务员       |
|     2032.94 | 游戏角色     |
|     2000.99 | 游戏陪玩     |
|    12000.66 | 董事长       |
+-------------+--------------+
4 rows in set (0.00 sec)

group by
先分组在查询

mysql> select sum(salary),role from emp group by role having sum(salary)>5000;
+-------------+-----------+
| sum(salary) | role      |
+-------------+-----------+
|    12000.66 | 董事长    |
+-------------+-----------+
1 row in set (0.00 sec)

联合查询

--班级表
drop table if exists classes;
create table classes(
	id int primary key auto_increment,
	name varchar(20),
	dest varchar(100)
);


--学生表
drop table if exists student;
create table student(
	id int not null primary key,
	sn int unique,
	name varchar(20) default 'unkown',
	qq_mail varchar(20),
	classes_id int,
	foreign key(classes_id) references classes(id)
);

--课程表
drop table if exists course;
create table course(
	id int primary key auto_increment,
	name varchar(20)
);

--创建一个学生中间表：成绩表
create table if exists score;
create table score(
	id int primary key auto_increment;
	score decimal(3,1),
	student_id int;
	course_id int,
	foreign key (student_id) references student(id),
	foreign key (course_id) references course(id)
);

内连接
适用于多表查询

select 字段 from 表1 别名1 [inner] join 表2 别名2 on 连接条件 and 其他条件；
select 字段 from 表2 别名1 ，表2 别名2 where 连接条件 and 其他条件

select * from classes,student where classes.id=1;
select cls.name,stu.name from classes cls ,student stu where cls.id=1;
这里cls.name,student.name都是表的别名