MySql数据库学习笔记

use test;
show tables;
create table tb1(
    id int,
    name varchar(16) -- 长度等于16的字符串类型
);
create table tb2(
    id int,
    name varchar(16) not null, -- 不允许为空
    email varchar(32),
    age int
);
create table tb3(
    id int,
    name varchar(16) not null,
    email varchar(32) null,  -- 允许为空
    age int default 3
);
create table tb4(
    id int primary key, -- 以id为主键（不允许为空，不允许重复）
    name varchar(16) not null,
    email varchar(32) null,  -- 允许为空
    age int default 3
);
create table tb5(
    id int not null auto_increment primary key, -- 中间加一行在不填的时候自动按照增序填充
    name varchar(16) not null,
    email varchar(32) null,  -- 允许为空
    age int default 3
); -- 注意，一个表只能有一个自增列，一般都是主键

alter table tb1 add k int;  -- 添加列k为列名，int为类型
delete from tb1; -- 清空表内的内容
desc tb5; -- 查看表的列名
alter table tb4 change name newname int not null;  -- name 改为 newname并设定新的类型为int not null
desc tb4;
-- int 表示有符号
-- int unsigned 表示无符号
-- int(5)zerofill 仅用于显示，当不足5位时自动补0

insert into tb5(id,name,email,age) values (1,2,3,4); -- 向表中插入数据

select * from tb5;  -- 查看表内的所有内容
-- 整型 (从小到大)tinyint, int, bigint
create table L1(
    id int not null auto_increment primary key,
    name varchar(16) not null,
    email decimal(8,2), -- 表示整数可接收8位，小数最多接收2位，小数超位数会四舍五入,整数部分超出会报错
    age int default 3);
-- 表示小数也有float和double，但是平常不使用

create table L2(
    name char(3)
);  -- char定长接收三个字符，不够三个字符也需要三个字符存储空间，超出默认情况下会报错


create table L3(
    name varchar(5)
);  -- 不定长的字符串，最大是5个字符，超出报错
-- 此外也有text, mediumtext, longtext, datetime,timestamp


create table L4(
    dt datetime,
    tt timestamp
); -- timestamp内部默认存储时间戳(会随时区变化),datetime内部默认存储时间(不会变)
-- 此外还有date(datetime不包含时间),time(只包含时间)

insert into L4(dt, tt) values("2025-12-12 11:11:44", "2025-12-12 11:11:44");

insert into tb5(name) values("22"),("63");  -- 插入多行数据

delete from tb5 where email="3";  -- 删除行数据(根据条件)

select * from tb5;

update tb5 set name = "0" where id = 2;  -- 前方是修改数据，where后是条件

select * from tb5;

update tb5 set age = age + 1;  -- 把所有人年龄加一

select * from tb5;

update tb5 set name = concat(name,"sb") where id = 2;  -- 在字符串后方拼接字符串

use test;
create table user_info(
    id int not null auto_increment primary key,
    name varchar(255) not null,
    email varchar(255) not null,
    age int,
    depart_id int
);

create table depart(
    id int not null auto_increment primary key,
    title varchar(255) not null
);
insert into depart(title) values('开发部'),('技术部');
insert into user_info(name,email,depart_id) values("1", "@63630","1");
insert into user_info(name,email,depart_id) values("22","@555","2");
select * from depart;
select * from user_info;
update user_info set age=3;


select * from user_info where id between 2 and 3; -- 选2和3的列
select * from user_info where id in (1, 2); -- id为1，2的选出来
select * from user_info where id in (select id from depart);  -- 把depart表中的id在info中搜索
select * from user_info where exists (select * from depart where id=5);  -- 查id=5是否存在
select * from (select * from user_info where id > 5) as T where T.age > 10;
-- 在表中选id大于5的组成一张表使其命名为T，在T中继续进行查询


-- 通配符,模糊查询
select * from user_info where name like "%2"; -- 百分号表示前面有n个字符
select * from user_info where name like "_2"; -- 一个_表示一个字符
select name,age from user_info;
-- 以后少写select *
select (select max(id) from user_info) as mid;  -- 把id最大值作为mid输出
select id,name,
       (select title from depart where depart.id=user_info.id) as x1,
       (select title from depart where depart.id=user_info.depart_id) as x2
from user_info;  -- 效率比较低，用处很少

select
    id,
    name,
    case depart_id when 1 then "第一部门" end v1
from user_info;  -- 如果查询到部门为1在其后边加一行v1，并加上第一部门


select
    id,
    name,
    case depart_id when 1 then "第一部门" else "其他" end v1
from user_info;  -- 变为else语句

select id,
       name,
       case depart_id when 1 then "第一部门" when 2 then "第二部门" else "其他部门" end v1
from user_info;  -- 连续的条件判断


select * from user_info order by id desc; -- 倒序排列
select * from user_info order by id asc; -- 正序排列（默认）
select * from user_info order by age,id desc; -- 先按照age排，如果相等，再按照id排列
select * from user_info order by id desc LIMIT 3;  -- 取前三条
select * from user_info LIMIT 3 offset 2; -- 从第2条开始,向后取三条数据，offset表示从第n行开始，实现分页功能
-- 分组
select age,max(id),min(id),count(id),sum(id),avg(id) from user_info group by age;
-- 一般都是使用count(id)查看人数
select depart_id,count(id) from user_info group by depart_id;
-- 根据部门人数计数
select depart_id,count(id) from user_info group by depart_id HAVING count(id) > 2;
-- 分组后使用having语句进行条件判断



-- 到目前为止网页的优先级排序
-- where
-- group by
-- having
-- order by
-- limit

select age,count(id) from user_info where id > 2 group by age having count(id) > 1 order by age desc limit 1;
-- 要查询的表
-- 条件 id>2
-- 根据age分组
-- 对分组后的数据再根据聚合条件过滤
-- 根据age从大到小排序
-- 获取第1条


-- 左右连表,与pandas中的merge类似
select * from user_info left outer join depart on user_info.depart_id = depart.id;
-- user_info 在这里为主表，depart为从表


-- 仅连接指定的列
select user_info.id, user_info.name, user_info.email,depart.title from user_info left outer join depart on user_info.depart_id = depart.id;
-- 主表数据为主，从表为辅
-- outer可以省略

-- 到目前为止网页的优先级排序
-- join
-- on
-- where
-- group by
-- having
-- order by
-- limit

-- 外键
-- 保证某一列的值必须是其他表中已经存在的值
create table new_depart (
    id int not null auto_increment primary key,
    title varchar(16) not null
);
create table info(
    id int not null auto_increment primary key,
    name varchar(16) not null,
    email varchar(32) not null,
    age int,
    depart_id int not null,
    constraint fk_info_depart foreign key (depart_id) references new_depart(id)
); -- 有外键约束创建表
-- 对于已经创建好的表
alter table user_info add constraint fk_indo_depart foreign key info(depart_id) references depart(id)
-- 在已有的表上建立外键 fk_indo_depart是外键的名字

alter table user_info drop foreign key fk_indo_depart;
-- 在已有外键的表上删除外键

show databases

select user,authentication_string,host from mysql.user;
desc mysql.user;


-- 创建和删除用户
create user new_account identified by 'password';  -- 前面用户名,后边密码
drop user new_account;

-- 可以不对ip地址进行限制
create user 'new_account'@'%' identified by 'password';


-- 修改用户名
rename user 'new_account'@'%' to "change"@"%";


-- 修改密码
set password for 'change'@'%' = PASSWORD ('new_password');

-- 授权管理
-- grant 权限 on 数据库,表 to '用户和ip'

-- 所有权限
grant all privileges on *.* TO 'change'@'%';


-- 数据库授权
grant all privileges on test TO 'change'@'%';

-- 数据表授权
grant all privileges on test.depart TO 'change'@'%';

-- 只给访问权限
grant select on test.depart TO 'change'@'%';

-- 只给查询和插入权限
grant select, insert on test.depart TO 'change'@'%';

-- 立即生效
flush privileges;