MYSQL学习笔记（2）

DML 语句：表内信息的插入，修改，删除
一、 插入语句
插入方式1
Insert into 表名（列名，…） values(值1，值2，…)
(1)插入的值的类型要与列的类型一致 或兼容
Insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
Values(13,’唐艺昕’,’1990-4-23’,’18946546565’,’null’,2)

(2)不可以为null的列必须插入值
方式1： Insert into beauty(id,name,sex,borndate,phone,photo,boyfriend_id)
Values(13,’唐艺昕’,’1990-4-23’,’18946546565’,’null’,2)
方式2： Insert into beauty(id,name,sex,borndate,phone ,boyfriend_id)
Values(13,’唐艺昕’,’1990-4-23’,’18946546565’,2)

(3)顺序可颠倒
Insert into beauty(name,d,sex,borndate,phone ,boyfriend_id)
Values(’唐艺昕’，13,’1990-4-23’,’18946546565’,2)
(4) 列数和值必须个数一样
(5)可以省略列名，此时默认所有列，默认顺序。
插入方式2
Insert into 表名
Set 列名=值，列名=值…
Insert into beauty
Set id=14,name=’刘涛’…

两种方式pk
方式1可以插入多行
Insert into beauty(id,name,sex,borndate,phone ,boyfriend_id)
Values(13,’唐艺昕’,’1990-4-23’,’18946546565’,2),
Values(15,’唐艺昕’,’1990-4-23’,’18946546565’,2),
Values(16,’唐艺昕’,’1990-4-23’,’18946546565’,2),
Values(17,’唐艺昕’,’1990-4-23’,’18946546565’,2);
方式1支持子查询（一般用于表导入另一个表）
Insert into beauty (id, name, phone)
Select 26,’宋茜’,1849846646

二、修改语句：
修改单表记录
Update 表名
Set 列=新的值,列=新的值…
Where 筛选条件;

例：将姓唐的女神电话改为1388888888
Update beauty
Set phone=’138888888’
Where name like ‘唐%’;
修改多表记录【补充】
例：修改张无忌女友的手机号为13456
Update boys inner join beauty b on boy.id=b.boyfriend_id
Set b.phone=’13456’
Where boys.’boyname’=’张无忌’;

例：修改没有男朋友的女神的男朋友编号都为2
Update boys right join beauty b on boy.id=b.boyfriend_id
Set b.boyfriend_id=2
Where b.boyfriend_id is null;

删除语句
方式1 delete
单表删除
Delete from 表名 where 筛选条件;
案例 删除手机号最后一位是9的女神
Delete from beauty where phone like ‘%9’;
多表删除【补充】
Delete 表1，表2
From 表1 join 表2 on 连接条件
Where 筛选条件
案例 删除张无忌的女朋友的信息
Delete b from beauty b join boys on (b.boyfriend_id=boys.id)
Where boys.boyname=’张无忌’;
案例 删除黄晓明和他女朋友的信息
Delete b,boys from beauty b join boys on boy.id=b.boyfriend_id
Where boys.name=’黄晓明’;

方式2 truncate
Truncate table boys ; #直接删除一个表的所有内容，表还在;

Delete删除后,再插入数据，自增长列从断点开始
Truncate删除后 ，再插入数据，自增长列从1开始
Truncate 删除没有返回值，delete有
Truncate 删除不能回滚，delete可以

DDL（数据定义语言）语言： 库和表的管理操作
一、 库的管理： 创建 修改 删除
Create alter drop
二、 表的管理： 创建 修改 删除 复制 约束 标识列
Create alter drop
库的创建
Create database [if not exists] 库名;
例子： 创建一个 books库
Create database books;

库的修改 (一般不修改)
重命名：一般直接在文件夹的地方修改，因为使用该语句有可能会让数据丢失，就当不存在这个命令
Rename database books to 新库名;
修改库的字符集：
alter database books character set gbk;


库的删除:
Drop database [If exists] books;

表的创建：
Create table 表名(
列名 列的类型[(长度） 列的约束],
列名 列的类型[(长度） 列的约束],
列名 列的类型[(长度） 列的约束],
);
DESC book #查看表结构

例子：
创建表book
CREATE TABLE book (
id INT, #编号
bName VARCHAR(20), #20个字符,最大长度
Price DOUBLE , #价格
authorId VARCHAR(20),#作者
publishDate DATETIME #出版日期
);
创建表 author
CREATE TABLE [if not exists] author (
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);

表的修改 change,modify, add,drop,rename to
修改列名 change
例子 将 publishdate改为 pubdate
ALTER TABLE book CHANGE COLUMN publishDate pubDate DATETIME;
Desc book;（查看表的结构）

修改类型或约束， modify
ALTER TABLE book MODIFY COLUMN pubDate TIMESTAMP;

增加列， add
例子：author增加一个年薪列
ALTER TABLE author ADD COLUMN annual DOUBLE;

删除列 drop
Alter table author drop column aunual;
Desc author;

修改表名 rename to
ALTER TABLE author RENAME TO book_author;
SHOW TABLES;

表的删除
Drop table book_author;
Show tables;

表的复制
#先给表里加记录
CREATE TABLE IF NOT EXISTS author (
id INT,
au_name VARCHAR(20),
nation VARCHAR(10)
);

INSERT INTO author VALUES
(1,‘村上春树’,‘日本’),
(2,‘莫言’,‘中国’),
(3,‘冯唐’,‘中国’),
(4,‘金庸’,‘中国’);

CREATE TABLE IF NOT EXISTS book_author like author;
SELECT *FROM author;

SELECT *FROM book_author;
仅仅复制了表的结构
复制结构和内容：
Create table book_author Select from author;
查看表内容：select * from book_author1;

复制部分内容就在查询语句里筛选，也可以只复制部分列。
CREATE TABLE book_author2
SELECT id ,nation
FROM author
WHERE nation=‘中国’;
SELECT FROM book_author2;

只复制部分列，不要数据：让筛选条件恒不成立即可
CREATE TABLE book_author3
SELECT * FROM author WHERE 0;
SELECT *FROM book_author3

可以跨库 ： from 库.表

常见数据类型：
数值：
整型tinyint <small int <dediumint<int<bigint
默认有符号，设置无符号：unsigned
超出长度用临界值

浮点 float<double
定点 decimal
Float（M,D）,Double(M,D),DECIMAL(M,D): M表示整数部分和小数部分位数的和，D表示小数部分的位数
（M,D）可以省略 DECIMAL: M默认为10，D默认为0
定点型精度更高，常用于货币计算。
选择的类型越简单越好

字符型：
较短的文本： char(M) 固定长度字符，效率高 ，M默认1，可省略
Varchar(M) 可变字符长度，效率低，M不能省略
M代表最大字符数
Binary,varbinary：保存较短的二进制

较长的文本： text
blob(较大的二进制)
enum：保存枚举，规定可选的字符,一次选一个
set:保存集合，规定可选字符（可以多个）

CREATE TABLE mm (abc ENUM(‘a’,‘b’,‘c’) );
INSERT INTO mm VALUES (‘a’);
INSERT INTO mm VALUES (‘b’);
INSERT INTO mm VALUES (‘c’);
INSERT INTO mm VALUES (‘d’);
INSERT INTO mm VALUES (‘A’);
SELECT *FROM mm;

CREATE TABLE nn (abc SET(‘a’,‘b’,‘c’) );
INSERT INTO nn VALUES (‘a’);
INSERT INTO nn VALUES (‘a,b’);
INSERT INTO nn VALUES (‘c,d’);
INSERT INTO nn VALUES (‘A’);
SELECT *FROM nn;

日期型：date，time ，year， datetime， timestamp
Datetime 比timestamp 占空间大
timestamp受版本，时区影响较大

常见约束：用于限制表中数据，保证数据准确性，一个字段可以加多个约束
①not null 不能为空，默认是可以为空的
②default 保证该字段有默认值
③primary key主键，保证字段值有唯一性，且非空，一个表只能一个主键，允许组合
④Unique 唯一，与主键类似，但可以为空，一个表可以多个unique，允许组合唯一键
例子：unique(name,phonenumber)
⑤check 检查【mysql不支持，但不报错】 比如年龄，性别
⑥foreign key ,references外键 限制两个表的关系，用于保证该字段的值必须来源于主表的关联列的值，从表添加外键约束，用于引用主表某列的值，如学生表的专业编号，主从表关联列的类型要一样，关联列必须是个key（一般是主键，唯一键也行），插入数据时先插入主表再插入从表，删除数据时先删除从表再删除主表
面试题：主键和唯一键的区别？
添加约束：
添加约束时机：
创建表时
修改表时

约束的添加分类：
列级约束 六大约束语法都支持，但外键约束没效果
表级约束 除了not null、default， 都支持，外键有效果

建表时添加约束：
添加列级约束：
Create 表名(
字段名 字段类型 约束
字段名 字段类型 约束
字段名 字段类型 约束
)
例：
CREATE TABLE student(
id INT PRIMARY KEY ,
stuName VARCHAR(20) NOT NULL,
gender CHAR(1) CHECK(gender ='男’OR gender=‘女’),
seat INT UNIQUE ,
age INT DEFAULT 18,
majorid INT REFERENCES major(id)
)

SHOW INDEX FROM student; #查看索引

添加表级约束： 【constraint 约束名】 约束类型(字段名)
例：CREATE TABLE student(
id INT,
stuName VARCHAR(20),
gender CHAR(1),
seat INT ,
age INT,
majorid INT,

CONSTRAINT pk PRIMARY KEY(id) ,
CONSTRAINT uq UNIQUE(seat),
 CHECK(gender='男'OR gender='女'),
 FOREIGN KEY(majorid) REFERENCES major(id) 

)

SHOW INDEX FROM student

修改表时添加约束
增加非空约束（只支持列级）
例 ALTER TABLE student MODIFY COLUMN stuName VARCHAR(10) NOT NULL;
增加默认约束（只支持列级）
例 ALTER TABLE student MODIFY COLUMN age int default;
增加主键（列，表均可）
例 ALTER TABLE student MODIFY COLUMN id int primary key;
例 ALTER TABLE add primary key(id);
添加唯一键（列，表均可）
例 ALTER TABLE student MODIFY COLUMN seat int unique;
例 ALTER TABLE add unique(seat);
添加外键 （列，表均可，列级没效果）
例 ALTER TABLE add foreign key(majorid) references major(id);
Check约束不讨论了，没效果。

修改表删除约束
删除非空约束
ALTER TABLE student MODIFY COLUMN stuName VARCHAR(10) NULL;
#也可以不写null，因为默认就是null
删除default默认约束
ALTER TABLE student MODIFY COLUMN age VARCHAR(10) int;
删除主键约束
ALTER TABLE student drop primary key;
删除唯一键
ALTER TABLE student drop index seat; #删除seat 的索引
删除外键
ALTER TABLE student drop foreign key majored
标识列
自增长 auto_increment，
可以和主键，唯一键，外键搭配，
但一个表只能有一个自增长列，
而且只能和数值型搭配，一般是和int搭配

创建表时设置标识列：
Create table tab_identity(
id int premiary key auto_increment,
name varchar(20)
);
Insert into tab_identity (id,name) values(null,’john’);
Insert into tab_identity (name) values(‘lucy’);
Select *from tab_identity;

Show variables like ‘auto_increment%’; #查看自增长步长和起始值。
可以通过更改变量的值设置步长，但是不能设置起始值,但可在插入值的时候设置
Set auto_increment_increment=3; #将步长设置为3

修改表时设置标识列：
Alter table tab_identity modify column id int primary key auto_increment;
修改表时删除标识列：
Alter table tab_identity modify column id int;

TCL语句 事务控制语句 (transaction control language)
一个或一组sql语句组成一个执行单元，要么全部执行要么都不执行，当系统发生意外而执行了一半时，撤销先前的执行（回滚），不至于使数据出错
Mysql 三种存储引擎：innodb ，myisam，memory
不是所有的存储引擎都支持事务，innodb支持
ACID属性：（面试题）
原子性（atomicity）：要么都发生，要么都不发生
一致性（consistency）：数据库从一个一致状态变到另一个一致状态，比如张三丰和郭襄的钱的和转账后还是2000
隔离性（isolation）；事物之间互不干扰，跟隔离级别有关。很多情况下是多个事务同时进行，要保证不互相干扰
持久性（durability）：事务提交后，永久改变数据库数据

事务的创建：
隐式事务：
没有明显开启和结束的标记，比如delete insert update
例：
Delete from 表 where id=1;
显示事务：
有明显开启和结束标记。前提：必须设置自动提交功能为禁用: Set autocommit=0;
Show variables like ‘autocom%’;可以查看当前自动提交功能是开启还是关闭，on
是开启。关闭只对当前事务有效，以后每次用事务还要设置。
形式：
步骤1：开启事务
Set autocommit=0;
Start transaction; #可写可不写
步骤2：编写事务中的sql语句(select insert update delete,不包含drop,create alter，因为这三个是DDL语言)
语句1;
语句2;
…
步骤3：结束事务
Commit;提交事务
Rollback;回滚事务
结束事务之前，以上语句可以理解为都是在内存中进行，rollback使得步骤2中的语句不发生作用。这样很好了保护了原来的数据，免受意外事故影响执行中断后弄不清具体的数据。

Mysql支持四种隔离级别： 事务的并发，假设事务1为T1，事务2为T2
脏读：T1还没提交，T2就读了T1执行步骤2后的结果，T1结束后（回滚或者提交），T2再读就是结束后的结果，即在T2中的两次读取结果不一样
不可重复读：T1还没提交，T2读了T1执行步骤2前的结果，T1结束后（回滚或者提交），T2再读就是结束后的结果，即在T2中的两次读取结果不一样
幻读：T1本来有两个记录，T2查看时也是两个，T1又插入一条并且结束事务，T2更改数据，是对T1结束后的做更改，即更改了三条记录。

Read uncommitted时，什么都不能避免
Read committed ，可以避免脏读，避免不了其他的
Repeatable read , 可以避免脏读和不可重复读，避免不了幻读
Serializable ,可以避免所有的，T1不结束事务,T2就不执行语句。但性能低下。

查看当前隔离级别：select @@tx_isolation;
设置隔离级别：set session transaction isolation level read uncommitted;(以read uncommitted为例子)
设置数据库系统的全局隔离级别：set global transaction isolation level read uncommitted;

Savepoint:保存点，可以和rollback使用
Savepoint a;
Rollback to a;
回滚到某个节点

视图：
虚拟表，临时性，只保存sql逻辑,封装
Mysql 5.1出现的新东西，通过表动态生成的数据
使用时当成一个表就可以，不占用内存，因为本身只是sql语句，并不是真实的表
比如：学校的普通班挑同学组成的舞蹈班
特点： 重用sql语句，简化使用，提高安全性。

查询姓张的学生和专业名
Select name ,majorname from stu
join major on (stu.majorid=major.id)
where stu.name like ‘张%’;

create view v1
as
Select name ,majorname from stu
join major on (stu.majorid=major.id)
where stu.name like ‘张%’;

select * from v1 ;

一、 创建视图
Create view 名字 as
比较复杂的查询语句;
查询各部门的平均工资级别：
① 创建视图各部门平均工资
USE myemployees;
CREATE VIEW myv2 AS
SELECT AVG(salary) ag,department_id
FROM employees GROUP BY department_id
② 查询等级
SELECT ag,job_grades.grade_level
FROM myv2 JOIN job_grades ON myv2.ag BETWEEN lowest_sal AND highest_sal;
二、修改视图
Cerate or replace view myv3
As
Select avg(salary),job_id
From employees
Group by job_id

Alter view myv3
As
Select * from employees;

删除视图
Drop view myv1,myv2,myv3;
查看视图
Desc myv3
Show create view myv3;
更新视图 （会更改原始表）基本没怎么用
1， 插入 insert
2， 修改 update
3， 删除 delete
具备以下特点的视图不能更新：
1,包含group by，having,union,distinct
2,常量视图
3,select中包含子查询
4,join
5,from一个不能更新的视图
6,where包含的子查询中的表和from是一个表

索引网站：
https://www.cnblogs.com/nananana/p/10387720.html
索引是一种数据结构
目的在于提高查找效率
排好序的快速查找数据结构， 排序和 快速
影响select 和order
Btree结构,hash结构，b+树结构
Hash：取模查
B树：类似三叉树，不包含端点
B+树：包含端点，所以所有元素在最下层 ，
索引类型：单值索引，组合索引，全文索引，空间索引
展示索引
Show index from table;
创建索引
建表时：
Create table book(
Id int not null primary key,
Name varchar(50) not null,
Author varchar(20) not null,
Info varchar(255) null,
Index(author));
其他方式详情见网站，在这部分内容的前面
Show create table book; 查看表的创建

修改表时：

Alter table book add index bknameidx(name(30));
Create index bknameixd on book(info(10))

删除索引
drop indix bknameidx on book;
alter table book drop index bknameidx;

数据库权限管理，用户创建等
见学校里的暑期短学期的作业。