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;
数据库权限管理,用户创建等
见学校里的暑期短学期的作业。