小杨的mysql笔记哦

/DDL/
#数据库创建
create database j210303; -- 直接创建数据库
create database if not exists j210303; -- 避免重复创建报错
create databese character set gbk; -- 创建时设置数据库字符集
#数据库查询语句
show databases; -- 查询数据库
show create database j210303; -- 查询数据库定义
#修改数据库字符集
alter database j210303 character set utf8;
#删除数据库
drop database if exists j210303；
#查询当前使用的数据库
select database();

创建表

create table student(
id int,
name varchar(20),
age int,
score double(4,1),
birthday data,
insert_time timestamp
);
#复制表
create table student1 like studen;
#查询表
show tables;
#查看表结构
desc student;
#修改表名
alter table student_1 rename to student_rename;
#修改表的字符集
alter table student_rename character set utf8;
#添加一列
alter table student_rename add sex varchar(2);
#修改列名称类型
alter table student_2 change name ename varchar(20);
alter table student_2 modify ename varchar(10);
#删除一列
alter table studen_2 drop sex;
#删除表
drop table if exists student_2;
/DML/
#插入数据
insert into student(id,name,age) values(1,'niuniu',25);
insert into student values(2,'wen',29,90.8,'1992-01-31',null);
#删除数据
delete from student where id=1;
delete from student;-- 不推荐使用,表中有多少数据就删除多少条，效率低
truncate table student; --推荐使用，先删除表，再创建空表，效率高
#修改表数据
update student set name='zxf',score=99.9 where id=2;

/DQL/
select * from student3;
select id,name from student3;
select distinct address from student3; #去重复
select id,name,math,english,math+english from student3;
select id,name,math,english,math+ifnull(english,0) from student3;
select id,name,math,english,math+ifnull(english,0) as score from student3;
select * from student3 where age>=20&&age<=30;
select * from student3 where age>=20 and age<=30;
select * from student3 where between 20 and 30;
SELECT * FROM student3 WHERE english IS NOT NULL;
SELECT * FROM student3 WHERE age in (20,22);

select * from student3 order by math ASC;#升序
select * from student3 order by math DESC;#降序
select * from student3 order by math DESC,english ASC;#当第一个条件一致时，以第二个条件排序

聚合查询(使用不含空的列,一般选主键，如果有空，使用ifnull函数处理)

select count(id) from student3;
select count(*) from student3;

select MAX(math) from student3;
select min(math) from student3;
select avg(math) from student3;
select sum(math) from student3;

SELECT id,NAME,MAX(math) FROM student3;
SELECT id,NAME,MIN(math) FROM student3;#最值可以加多余字段，其他的不能加

select avg(ifnull(english,0)) from student3;#如果有空不使用ifnull函数，计算结果是错误的

select sex,avg(math) from student3 group by sex;
select sex,avg(math),count(id) from student3 group by sex;
select sex,avg(math),count(id) from student3 where math>70 group by sex;
select sex,avg(math),count(id) 人数 from student3 where math>70 group by sex having 人数>2;

select * from student3 limit 0,3; #第一页
select * from student3 limit 3,3; #第二页
select * from student3 limit 6,3; #第三页 (n-1)page:page

create table stu(
id int,
phone_number varchar(20) unique -- 添加唯一约束
);
insert into stu values(1,'12345678');
insert into stu values(2,'12345678');
alter table stu drop index phone_number; -- 删除唯一约束
alter table stu modify phone_number varchar(20) unique; --创建表后添加唯一约束（空值可以添加多个）

-- 主键约束primary key
/*
1.含义：非空且唯一
2.一张表只能有一个字段为主键
3.主键是表中记录唯一的标识，id为主键
*/
create table stu(
id int primary key, -- 主键
name varchar(20)
);
alter table stu drop primary key; -- 删除主键
alter table stu modify id int primary key; -- 添加主键

create table stu(
id int primary key auto_increment, -- 主键自增
name varchar(20)
);

alter table stu modify id int;

-- 添加外键。constraint 主表缩写_从表缩写_fk foreign key (从表字段) references 主表名(主表字段)

alter table employee drop foreign key emp_dep_fk; -- 删除外键
alter table employee add constraint emp_dep_fk foreign key (de_id) references depaetment(id); -- 添加外键

-- 添加外键是加上 on update cascade on delete cascade; 级联操作

create table c_t(
c_id int unique not null,
information varchar(100),
constraint m_fk foreign key (c_id) references main_t(id)
on update cascade on delete cascade
);

insert into c_t values(1,'666');
insert into main_t values(1,'张三');

/*
1FN （原子性）
2FN 没有部分依赖，所有的非主键都依赖于主键
3FN 没有传递依赖，所有的其他键完全依赖于唯一的主键
(bCFN/4FN/5FN)
*/

-- 备份
mysqldump -uroot -ppassword J210303 > d://a.sql
-- 还原
mysql -u root -ppassword
drop database db1;
create database db1;
use db1;
source d://a.sql
show tables;

-- 不使用内连接会参数笛卡尔积
-- 隐式内连接和显式内连接
-- 隐式
select * from dept,emp where emp.dept_id=dept.id;
-- 显式
select * from emp inner join dept on emp.dept_id=dept.id;

-- 外连接：左外连接和右外连接
-- 查询所有员工信息，如果员工有部门，则查询部门名称，没有部门，则不显示部门名称
-- 左外连接查询左表和交集部分
-- 右外连接查询右表和交集部分左右查询的区别是：
/*
left join 第一张表的连接列在第二张表中没有匹配是,第二张表中的值返回null
right join 第二张表的连接列在第一张表中没有匹配是,第一张表中的值返回null
(左连接时，左表会显示全表；右连接时，右表会显示全表)
/
select
t1.,t2.name
from
emp t1 left outer join dept t2 on t1.dept_id=t2.id; -- 左外连接

select
t1.*,t2.name
from
emp t1 right outer join dept t2 on t1.dept_id=t2.id; -- 右外连接

select
t1.*,t2.name
from
dept t2 right outer join emp t1 on t1.dept_id=t2.id; -- 右外连接（交换左右表）

-- 子查询：查询中嵌套查询，又叫嵌套查询
-- 查询工资最高的员工信息
-- 1.查询最高的工资
select max(salary) from emp;
-- 2.查询员工信息，并且工资是最高的
select * from emp where emp.salary=(select max(salary) from emp);

-- 子查询的不同种情况
-- 1.子查询的结果是单行单列的：
-- 子查询可以作为条件，使用运算符去判断运算符：< <= > >= != <> =
-- 查询员工工资小于平均工资的人
select avg(salary) from emp;
select * from emp where salary<(select avg(salary) from emp);
-- 2.子查询的结果是多行单列的：
-- 子查询可以作为条件，使用运算符in来判断
-- 查询财务部和市场部所有的员工信息
select id from dept where name='财务部' or name='市场部';
select * from emp where dept_id=2 or dept_id=3;
select * from emp where dept_id in(select id from dept where name='财务部' or name='市场部');
-- 3.子查询的结果是多行多列的：
-- 子查询可以作为一张虚拟表参与查询
-- 查询员工入职日期是2011-11-11日之后的员工信息和部门信息
select * from emp where join_date>'2011-11-11';
select * from dept t1,(select * from emp where join_date>'2011-11-11') t2
where t1.id=t2.dept_id;

-- 开启事务
start transaction;
-- 回滚
rollback;
-- 提交
commit;

select @@autocommit; -- 1自动提交 0手动提交
set @@autocommit=0;

/*
事务的四大特征
1.原子性：是不可分割的最小操作单位，要么全部成功，要么全部失败
2.持久性：当事务提交或回滚后，数据库会持久化的保存数据
3.隔离性：多个事务之间。相互独立
4.一致性：事务操作前后，数据总量不变
*/

-- 事务的隔离级别
-- 存在的问题
/*
1.脏读：读到其他事务未提交的
2.不可重复读（虚读）:同一个事务中，两次读到的数据不一样
3.幻读：
*/

/*
隔离级别
1.read uncommitted：读未提交 > 脏读，不可重复读，幻读
2.read commit:读已提交 > 不可重复读，幻读
3.repeatable read:可重复读 > 幻读
4.serializable：串行化 > 无
*/
select @@global.transaction_isolation;-- 高版本
select @@global.tx_isolation;-- 低版本

set global transaction isolation level repeatable read;
-- 创建用户 create user '用户名'@'主机名' identified by '密码';
create user 'lisi'@'127.0.0.1' identified by '1234';
-- 删除用户 drop user '用户名'@’主机名';
-- 修改密码
/*
SET PASSWORD FOR '用户名'@'主机名' = PASSWORD('新密码');
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('123');(mysql5.0版本)
UPDATE USER SET PASSWORD = PASSWORD('新密码') WHERE USER = '用户名';
UPDATE USER SET PASSWORD = PASSWORD('abc') WHERE USER = 'lisi';(mysql5.5版本)
update user set authentication_string='123456' where user='lisi';(mysql5.7版本)
alter user 'lisi'@'localhost' identified by '123';(mysql8.0版本)
/
FLUSH PRIVILEGES; -- 刷新权限
/*DCL/
-- grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
grant create,select,update,drop,insert on j210303.'account' to 'lisi'@'localhost';
grant all on . to 'lisi'@'localhost';
-- 撤销权限
revoke update on . from 'lisi'@'localhost';
-- 主机名填入 '%'代表任意主机通配符
-- 权限查询
show grants for 'lisi'@'localhost';
-- show grants for '用户名'@'主机名';

-- 创建索引（添加主键、添加唯一键、添加普通索引、添加textfull索引）
create index indeax_name on table_name(key_name);

-- 删除索引
drop index indeax_name on table_name;
-- 查询索引
show index from city;

-- 视图创建
create view view_name as sql_st;
#sql_st:select * from table_name
-- 通过show tables可以查询出来，但是并不存在该表
-- 查看视图定义
show create view view_name;
-- 删除视图
drop view view_name;

-- 查询存储引擎
show engines;

-- 查询当前存储引擎
show variables like '%storage_engine%';