sql 语句学习

所有语句尾部要使用分号来结束语句

创建数据库

-- 注释
-- CREATE DATABASE `test`, 标准写法，关键字大写，自定义名字用单引号，方式提示使用关键字，报错
create database test;

查询所有数据库

show databases;

删除数据库

drop database test;

使用数据库

use test;

数据表所有数据格式

类型	备注
INT	整数
DECIMAL(m,n)	小数
VARCHAR(n)	字符串
BLOB	二进制数据
DATE	YYYY-MM-DD日期
TIMESTAMP	YYYY-MM-DD HH:MM:SS时间

创建表格

根据以下图片，创建表格

-- constraints 限制 约束：以下关键字
-- primary key    主键
-- foreign key    外键
-- not null       非空
-- unique         唯一
-- detault '123'  不填时默认值为 123
-- auto increment 自增 可用于 primary key
create table student
(
  student_id int primary key,
  name varchar(20),
  major varchar(20),
  manager_id int,
  foreign key (manager_id) references 数据库名(对应的 primary key)
);

查看表格状态

describe student;

删除表格

drop table student;

表格中新增一列属性

alter table student add age int;

表格中删除一列属性

alter table student drop age;

向表格中插入数据

-- 没有数据可以写 NULL
-- 填写属性值的顺序要跟表格的顺序一致
insert into student values(1, "张三", "计算机");

-- 也可以自定义填写数据顺序
insert into student (name, major, student_id) values("张三", "计算机", 1);
-- 下面二个示例都是可以的，没填某个属性相当于这个属性填写了 null
insert into student (name, major, student_id) values(null, "计算机", 1);
insert into student (major, student_id) values("计算机", 1);

查询表格数据

-- 查询表格所有数据
select * from student;
-- 查询表格指定数据
select major, student_id from student;
-- 查询表格数据，并排序，order by 根据 student_id 升序排序
-- 升序：asc，降序：desc，默认不写是 asc
select * from student order by student_id;
-- 查询固定数量数据，前十条数据
select * from student limit 10;
-- 添加查询条件
select * from student where student_id = 1;
-- 条件判断，where in 用来查询多个属性
select * from student where student_id in (1, 2, 3);
-- 结果去重
select distinct stident_id from student;

更新表格中数据

update student set name = "李四" where student_id = 1;
-- where 可以多个判断条件 or是或，and是且
update student set name = "李四" where student_id = 1 or major = "张三";
-- set 可以修改多个属性
update student set name = "李四", major = "计算机" where student_id = 1;
-- 如果不写 where 则修改的是整个表格数据
update student set major = "计算机";

表格删除一组数据

-- and 是且运算符，or 是或运算符
-- <> 不等于  = 等于
delete from student where student_id = 1 and major = "张三";

数据库安全更新模式

安全更新模式是一种MySQL数据库的配置选项，旨在提供额外的安全保护措施。具体而言，安全更新模式可以帮助我们避免意外更新或删除数据库中的数据。它通过在执行更新或删除操作时添加一些限制条件来实现这一点。 使用安全更新模式的好处是，它可以减少人为错误对数据库的影响。如果我们在执行更新或删除操作时意外地忽略了WHERE子句，安全更新模式将阻止我们对整个表进行更改。这有助于防止数据丢失或意外更改。

-- 0 关闭安全更新模式
-- 1 开启安全更新模式
set sql_safe_updates = 0;
set sql_safe_updates = 1;

聚合函数（aggregate functions）

-- 1. 取得员工人数
select count(student_id) from employee;

-- 2. 取得所有出生年龄 1970-01-01 之后的女性员工人数
select count(*) 
from employee 
where birth_date > '1970-01-01' and sex = 'F';

-- 3. 取得所有员工的平均薪水
select avg(salary) from employee;

-- 4. 取得所有员工薪水的总和
select sum(salary) from employee;

-- 5. 取得薪水最高的员工
select max(salary) from employee;

-- 6. 取得薪水最低的员工
select min(salary) from employee;

万用字元（wildcards）

% 代表多个字元, _ 代表一个字元

-- 获取手机后尾号后三位为 335 的全部用户
select * from client where phone like '%335';

-- 获取生日在12月的员工
select * from employee where birth_date like '____12%';

联集（union）

⚠️注意：联集每个sql查询的数量是一样的，并且类型要相同

-- 1. 员工的名字 union 客户的名字
select `name` as lhc_name from employee
union
select client_name from client;

连接 （join）

-- 查询所有部分经理的名字
select emp_id, `name`, branch_name
from employee
join branch
on emp_id = manager_id;

-- 如果二个表格中出现一样的字段名称可以这样解决
select employee.emp_id, employee.name
from employee
join branch
on employee.emp_id = client.manager_id;

-- left join 如果条件成立也会返回左边表格的信息
-- right join 与之相反 
select employee.emp_id, employee.name
from employee
left join branch
on employee.emp_id = client.manager_id;

子查询（subquery）

-- 找出研发部门经理的名字
select `name` from employee where emp_id = (
  select manager_id from branch where branch_name = '研发部'
)

-- 找出销售额大于 50000 的员工名字
-- 这里使用 in 的原因是查出的数据是多条的
select `name` from employee where emp_id in (
  select emp_id from works_with where total_sales > 50000
)

on delete

-- on delete cascade 为找不对应的 primary key 则删掉当前整条数据
create table works_with(
	emp_id int,
    client_id int,
    total_sales int,
    primary key(emp_id, client_id),
    foreign key (emp_id) references employee (emp_id) on delete cascade,
    foreign key (client_id) references `client` (client_id) on delete cascade
);

-- on delete set null 为找不到当前数据把当前数据设置为 null
create table branch(
	branch_id int primary key not null,
    branch_name varchar(10),
    manager_id int,
    foreign key (manager_id) references employee (emp_id) on delete set null
);