mysql复习整理

mysql

1. 对数据库操作

create

create database NAME
character set UTF8
collate XXX;

alter

alter database NAME
character set UTF8
collate XXX;

drop

drop database NAME;

show

show databases;

2. 对表操作

create

create table TABLENAME(
VARNAME_1 datatype not null ,
name varchar(10) not null,
gender tinyint(1) not null default 1,
primary key(varname_1)
)engine=innodb default charset=gbk;

复制表：

create table employee_1 like employee;//复制结构没有数据
create table employee_1 as (select* from employee);//复制结构和数据

alter

alter table TABLENAME
[选项];
alter table dbem add userid int（10）null;
alter table 用于更改表的结构而不是数据

add（添加列）
直接 set charset =
change（列名重定义）

alter table department change departname nameafter tinyint(2);
modify（修改数据类型）

alter table department modify COLNAME char(2);
drop（删除列）

alter table department
drop COL_1,
drop COL_2;
rename（重命名表）

alter table department rename to NAME_2;
也可以直接修改表名：
rename table department to NAME_2;
order by（排序）

drop

drop table department;

3. 对数据操作

insert

insert into TABELNAME values(10002,"张三",1);（插入新记录）
insert into TABLENAME select*from TABLE2 where id=1001;（从已有的表选择数据插入）

update

update TABLENAME 
set id=1001
where name="张三";（基础用法）

update TABLENAME  set dpartid=case empid
when 1001 then 5
when 1002 then 4
end
where empid in(1001,1002,1003);

delete

delete from TABELNAME
where id=1001;

4. 关系运算（select）

基本：select ...from...where...

select name,gender
from employee
where salary>10000;

消除重复行：distinct

select distinct depart,gender
from employee;

修改显示名：as

select address as 地址
from employee;

聚和函数

COUNT求组中项数，返回 int 类型整数
MAX求最大值
MIN求最小值
SUM返回表达式中所有值的和
AVG求组中值的平均值
STD或STDDEV返回给定表达式中所有值的标准差
VARIANCE返回给定表达式中所有值的方差
GROUP_CONCAT返回由属于一组的列值连接组合而成的结果BIT_AND逻辑或
BIT_OR逻辑与
BIT_XOR逻辑异或

count

select gender ,count(gender) as num
from employee
group by gender;

max

select max(income-outcome),min(income-outcome)
from salary
where employeeid in (
select employeeid from employee where departmentid=1);

查询方式

子查询：select嵌套

select *from employee
where departmentid in(
select departmentid 
from department 
where departname="财务部");

连接查询：join on
```
select* from employee join salary on employee.employeeid=salary.employeeid
where id =1001;
```
- 内连接
  
  关键字：inner join on
  语句：select * from a_table a inner join b_table b on a.a_id = b.b_id;
  说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。
- 外连接
  - 左外连接
    
    关键字：left join on / left outer join on
    
    语句：SELECT * FROM a left join b ON a.a_id = b.b_id;
    
    说明： left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
  - 右外连接
    
    关键字：right join on / right outer join on
    
    语句：SELECT * FROM a right outer join b on a.a_id = b.b_id;
    
    说明：right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
- 全连接
  
  关键字：union /union all
  
  语句：(select colum1,colum2...columN from tableA ) union (select colum1,colum2...columN from tableB )
  
  或 (select colum1,colum2...columN from tableA ) union all (select colum1,colum2...columN from tableB )；
  
  union语句注意事项：
  
  1.通过union连接的SQL它们分别单独取出的列数必须相同；
  
  2.不要求合并的表列名称相同时，以第一个sql 表列名为准；
  
  3.使用union 时，完全相等的行，将会被合并，由于合并比较耗时，一般不直接使用 union 进行合并，而是通常采用union all 进行合并；
  
  4.被union 连接的sql 子句，单个子句中不用写order by ，因为不会有排序的效果。但可以对最终的结果集进行排序；
  
  (select id,name from A order by id) union all (select id,name from B order by id); //没有排序效果
  
  (select id,name from A ) union all (select id,name from B ) order by id; //有排序效果
```
	union 会删除重复列，union all 会显示所有列，包括重复的
```

分组：group by

排序：order by

having

select departname,count (employee.departmentid) as num
where employee join department on employee.departmentid=department.departmentid
group by employee.departmentid
having num>2;

其它

limite

5. 视图view

（用户使用简单）为用户集中数据，简化用户的数据查询和处理。有时用户所需要的数据分散在多个表中，定义视图可将它们集中在一起，从而方便用户的数据查询和处理
（数据库使用简单）屏蔽数据库的复杂性。用户不必了解复杂的数据库中的表结构，并且数据库表的更改也不影响用户对数据库的使用
（安全）简化用户权限的管理。只需授予用户使用视图的权限，而不必指定用户只能使用表的特定列，也增加了安全性
便于数据共享。各用户不必都定义和存储自己所需的数据，可共享数据库的数据，这样同样的数据只需存储一次
可以重新组织数据以便输出到其他应用程序中

不可更新视图

如果视图包含下述结构中的一种，则视图不可更新（就是不能通过更改视图修改原表，可更新视图可以通过insert,update,delete等命令改动原表的数据）
（1）聚合函数；
（2）（2）DISTINCT关键字；
（3）（3）GROUP BY子句；
（4）ORDER BY子句；
（5）HAVING子句；
（6）UNION运算符；
（7）位于选择列表中的子查询；
（8）FROM子句中包含多个表；
（9）SELECT语句中引用了不可更新视图；
（10）WHERE子句中的子查询，引用FROM子句中的表；

使用create view v as select* from table创建视图其实是create view v as select id as id,name as name;所以视图修改表结构是看不到修改的，但后台确实改了。

create

create view v_dp as
select name,gender from department;

drop

6. 索引index

InnoDB 是系统的默认引擎，支持可靠的事务处理。
使用下面的语句可以修改数据库临时的默认存储引擎

InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

普通索引

作用：加速查找

create index name on s1(name); #添加普通索引

唯一索引

主键索引：primary key ：加速查找+约束（不为空且唯一）

唯一索引：unique ：加速查找+约束（唯一）

alter table s1 add primary key(id); #添加住建索引，也就是给id字段增加一个主键约束
create unique age on s1(age);添加唯一索引

联合索引

primary key（id，name）：联合主键索引
unique （id，name）：联合唯一索引
index（id，name）：联合普通索引
create index name on s1(id,name); #添加普通联合索引

全文索引fulltext

用于搜索一篇很长的文章的时候效果最好

create,drop

7.参照完整性

MySQL参照完整性一般是通过MySQL外键(foreign key)实现的。
外键（仅innoDB支持）所引用表的列必须是主键。
外键声明包括三个部分：
A、哪个列或列组合是外键
B、指定外键参照的表和列
C、参照动作[cascade（级联操作），restrict（拒绝操作），set null（设为空），no action，set default]。
如果外键约束指定了参照动作，主表记录做修改，删除，从表引用的列会做相应修改，或不修改，拒绝修改或设置为默认值。
引用表的列名必须是主键，且在删除主表时必须删除引用关系或者删除当前表。
-- 多对多关系

-- 创建teacher表格

CREATE TABLE teacher (
    id INT,
    NAME VARCHAR (20) NOT NULL,
    gender CHAR (2) NOT NULL,
    PRIMARY KEY (id)
);

-- 创建学生表格

CREATE TABLE student (
    id INT,
    NAME VARCHAR (20) NOT NULL,
    age INT NOT NULL
);
ALTER TABLE student ADD PRIMARY KEY (id);

-- 第三张关系表

CREATE TABLE tch_std (
    teacher_id INT,
    student_id INT,
    CONSTRAINT fk_teacher FOREIGN KEY (teacher_id) REFERENCES teacher (id),
    CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES teacher (id)
);

8. 存储过程procedure

delimiter $$
create procedure emnum
begin
declare num int;
select count(*) into num from employee;
select num;
end $$
delimiter :

create

delimiter $$
create procedure comp(in id1 int, in id2 int)
begin
declare num1 double;
declare num2 double;
declare comp int;
select income-outcome into num1 from salary where employeeid=id1;
select income-outcome into num2 from salary where employeeid=id2;
if(num1>num2)
then set comp=0;
else set comp=0;
select comp;
end if;
end $$
delimiter ;

call

call comp(1001,1002);

drop

drop procedure comp;

9. 触发器trigger

在MySQL触发器中的SQL语句可以关联表中的任意列。但不能直接使用列的名称去标志，那会使系统混淆，因为激活触发器的语句可能已经修改、删除或添加了新的列名，而列的旧名同时存在。因此必须用这样的语法来标志：“NEW.column_name”或者“OLD.column_name”。NEW.column_name用来引用新行的一列，OLD.column_name用来引用更新或删除它之前的已有行的一列。

对于INSERT语句，只有NEW是合法的；对于DELETE语句，只有OLD才合法；而UPDATE语句可以与NEW或OLD同时使用。

create

delimiter $$
create trigger emdelete after delete on employee for each row
begin
delete from salary where employeeid =old.employeeid;
end $$
delimiter ;

drop

drop trigger emdelete;

10. 导入和备份

导入load

LOAD DATA LOCAL INFILE 'dump.txt' INTO TABLE mytbl
  -> FIELDS TERMINATED BY ':'
  -> LINES TERMINATED BY '\r\n';
使用load
$ mysqlimport -u root -p --local mytbl dump.txt
password *****

使用mysqlimport

备份select into

SELECT a,b,a+b INTO OUTFILE '/tmp/result.text'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM test_table;

11. 权限管理

创建用户

create user 'username'@'localhost' identified by '123456';

修改用户

use mysql;
select user from mysql.user;
update user set user='user_3' where user='user_2';
alter user 'user_3'@'localhost' identified by'111111';

权限赋予

grant select,insert,update, delete on employee to user_3@localhost;
flush privileges;
show grants for user_3@localhost;
grant grant option on tablename to user_3@localhost;

收回权限

revoke select on employee from user_1@localhost;

12. 并发控制

并发控制问题

丢失修改：两个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，因此最后的更新将重写由其他事务所做的更新，导致数据丢失
脏读：一个事务正在访问数据，而其他事务正在更新该数据，但尚未提交，此时会发生脏读问题，即第一个事务所读取的数据是“脏”的
不可重复读：在一个事务内多次读同一数据，在该事务还没有结束时，另外的事务也访问该数据并对其做修改，从而导致第一个事务两次读取的数据不一样
幻读：当一个事务对某行执行了插入或删除操作时，由于该行在另外的事务读取的范围内，从而导致两次读取多出了一行或者消失了一行

隔离

四级隔离级别：定义用户之间隔离和交互的程度

读未提交(read uncommitted)：可以读到其他事务还没有提交的数据，仅避免丢失修改，会导致大量数据变化，一级封锁协议
读已提交(read committed)：可以看到其他事务添加的新纪录，而且其他事务对现存记录做出的修改一旦被提交，也可以看到，避免丢失修改和脏读，二级封锁协议
可重复读(repeatable read)：当前在执行的事务的变化无法被看到，同一事务中执行select数次结果都相同，避免丢失修改、脏读和不可重复读，增强的二级封锁协议
可串行化(serializable)：用户之间一个接一个执行事务，避免所有并发控制问题，最大限度的隔离，三级封锁协议

事务

正确提交
begin;
insert into tablename...;
insert into tablename...;
commit;
回滚
begin;
insert ...
insert...
rollback;

原子性：一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

posted @ 2021-11-04 16:33 zero-ng 阅读(103) 评论(0) 收藏举报

刷新页面返回顶部

zero-ng

mysql复习整理

mysql

1. 对数据库操作

create

alter

drop

show

2. 对表操作

create

alter

drop

3. 对数据操作

insert

update

delete

4. 关系运算（select）

基本：select ...from...where...

消除重复行：distinct

修改显示名：as

聚和函数

查询方式

分组：group by

排序：order by

having

其它

5. 视图view

不可更新视图

create

drop

6. 索引index

普通索引

唯一索引

联合索引

全文索引fulltext

create,drop

7.参照完整性

8. 存储过程procedure

create

call

drop

9. 触发器trigger

create

drop

10. 导入和备份

导入load

备份select into

11. 权限管理

创建用户

修改用户

权限赋予

收回权限

12. 并发控制

并发控制问题

隔离

事务

公告