SQL学习(重要)
SQL
SQL是一种特殊目的的编程语言,是一种数据库查询和程序设计语言,用于存取数据以及查询、更新和管理关系数据库系统。(用人话说就是通过程序能看懂的方式告诉mysql程序怎么做)
一、SQL语句分类(认识下,认清着重点)
DCL(Data Control Language):数据控制语言,用来定义访问权限和安全级别。
DDL(Data Definition Language):数据定义语言,用来定义数据库对象:库、表、列等。功能:创建、删除、修改库和表结构。
DML(Data Manipulation Language):数据操作语言,用来定义数据库记录:增、删、改表记录。
DQL(Data Query Language):数据查询语言,用来查询记录。也是本章学习的重点。
我们前面下载了数据库的管理软件navicat,这个软件可以帮我们进行上面语句的执行,像我们可以在软件中给用户分配权限,设置账户和密码(这个属于DCL),我们也可以通过右键点击操作创建数据库,创建表,创建列等等,同时我们也能在上面执行一些修改操作,以及查询的语句。这个软件本质上还是执行的语句,所以我们要重点学习这些语句,尤其是DML和DQL(因为赋予权限的操作用的没那么多,可能设置了一次很久都不会变,这个时候用可视化操作会方便得多)。
二、DCL(数据控制语言)语法(不重要)
该语言用来定义【访问权限和安全级别】,理解即可,直接使用命令控制权限的场景不多,更多情况是使用图形化界面进行操作。
mysql中的权限无非是针对不同的用户而言,不同的用户的权限提现在以下几点:可否链接mysql服务 、可否访问数据库 、可否访问某张数据库表 、可否对表进行一些操作等。
1、创建用户
因为以后我们的数据库连接不一定是和本机进行连接,也有可能是远程连接,和我们不在一个主机的主机对我们发起的,所以要配置公网IP。
创建一个用户,该用户只能在指定ip地址上登录mysql:
create user 用户名@IP地址 identified by ‘密码’;
创建一个用户,该用户可以在任意ip地址上登录mysql:
create user 'ydl'@'%' identified by 'root';
修改密码:
-- 5.7版本需要使用password对密码进行加密
set password for zn@'%' = password('newpwd');
-- 8.0直接赋值新的密码即可
set password for zn@'%' = 'newpwd';
如果直接修改表,也是可以创建用户修改密码的,【mysql数据库下的user表】但是通过修改数据库创建用户,修改密码,都需要刷新权限:(很好理解,对于这种配置,都是启动程序的时候,进行的加载,所以要让mysql重新读取下这个配置)
flush privileges;
2、给用户授权
给【指定用户】在【指定数据库】上赋予【指定权限】,权限有很多,列举几个常用的:
create:可以常见数据库
select:可以查询数据
delete:可以删除数据
update:可以更新数据
insert:可以插入数据
数据库.* 即赋予用户该数据库下的所有表的所有权限
-- 语法:`grant 权限1,…,权限n on 数据库.* to 用户名@IP地址;
grant all on `ydlclass`.`user` to 'ydl'@'%';
也可以指定赋予哪些权限
grant select,insert,update,delete,create on `ydlclass`.`user` to 'ydl'@'%';
3、撤销授权
撤销【指定用户】在【指定数据库】上的【指定权限】:
-- 语法:revoke 权限1,…,权限n on 数据库.* from 用户名@ ip地址;
revoke all on `ydlclass`.`user` from 'ydl'@'%';
revoke select,insert,update,delete,create on `ydlclass`.`user` from 'ydl'@'%';
4、查看权限
查看指定用户的权限:
-- 语法:show grants for 用户名@ip地址;
show grants for 'ydl'@'%';
5、删除用户
-- 语法:drop user 用户名@ip地址;
drop user 'ydl'@'%';
上面内容虽然不是重点,但也要按照流程操作一下,有个基本认识,后续我们直接在navicat上面操作即可(要用语句在单独看看)。
三、DDL(数据定义语言)语法
DDL主要是用在定义或改变表(TABLE)的结构,主要的命令有CREATE、ALTER、DROP等:
1、创建表的基本语法
create table 表名 (
字段名1(列名) 类型(宽度) 约束条件,
字段名2 类型(宽度) 约束条件,
字段名3 类型(宽度) 约束条件,
.......
);
2、常用的数据类型
整形
MySQL数据类型 | 含义(有符号) |
---|---|
tinyint | 1字节,范围(-128~127) |
smallint | 2字节,范围(-32768~32767) |
mediumint | 3字节范围(-8388608~8388607) |
int | 4字节,范围(-2147483648~2147483647) |
bigint | 8字节,范围(±9.22*10的18次方) |
在整形中我们默认使用的都是【有符号】的,当然了,我们也可以加上unsigned关键字,定义成无符号的类型,那么对应的取值范围就会发生改变:
比如:tinyint unsigned的取值范围为0~255,
宽度n的作用需要配合zerofill进行使用: 如:int(4) UNSIGNED zerofill 查询结果: 0001 0002 1000
浮点型
MySQL数据类型 | 含义 |
---|---|
float(m, d) | 4字节,单精度浮点型,m总长度,d小数位 |
double(m, d) | 8字节,双精度浮点型,m总长度,d小数位 |
decimal(m, d) | decimal是存储为字符串的浮点数,对应我们java的Bigdecimal |
我们使用一个例子来判断小括号内参数的含义,针对数据类型为float(5, 3)的列,做了以下试验:
插入123.45678,最后查询得到的结果为99.999;
插入12.34567,最后查询结果为12.346;
所以,在使用浮点型的时候,还是要注意陷阱的,要以插入数据库中的实际结果为准。
字符串数据类型
char(n) | 固定长度,最多255个字符 |
---|---|
varchar(n) | 可变长度,最大容量65535个字节 |
tinytext | 可变长度,最大容量255个字节 |
text | 可变长度,最大容量65535个字节 |
mediumtext | 可变长度,最大容量2的24次方-1个字节 16MB |
longtext | 可变长度,最大容量2的32次方-1个字节 4GB |
(1)char和varchar的区别:
- char类型是【定长】的类型,即当定义的是char(10),输入的是"abc"这三个字符时,它们占的空间一样是10个字符,包括7个空字节。当输入的字符长度超过指定的数时,char会截取超出的字符。而且,当存储char值时,MySQL是自动删除输入字符串末尾的空格。
- char是适合存储很短的、一般固定长度的字符串。例如,char非常适合存储密码的MD5值,因为这是一个定长的值。对于非常短的列,char比varchar在存储空间上也更有效率。
- varchar(n)类型用于存储【可变长】的,长度最大为n个字符的可变长度字符数据。比如varchar(10), 然后输入abc三个字符,那么实际存储大小为3个字节。除此之外,varchar还需要使用1或2个额外字节记录字符串的长度,如果列的最大长度小于等于255字节(是定义的最长长度,不是实际长度),则使用1个字节表示长度,否则使用2个字节来表示。n表示的是最大的
- char类型每次修改的数据长度相同,效率更高,varchar类型每次修改的数据长度不同,效率更低。
转载至 https://blog.csdn.net/Mengxinxiaobaia/article/details/124043185
1、最大长度:
char最大长度是255字符,varchar最大长度是65535个字节。
2、定长:
char是定长的,不足的部分用隐藏空格填充,varchar是不定长的。
3、空间使用:
char会浪费空间,varchar会更加节省空间。
4、查找效率:
char查找效率会很高,varchar查找效率会更低。
5、尾部空格:
char插入时可省略,vaechar插入时不会省略,查找时省略。
IO操作多了 自然更慢了 毕竟io操作是很费时的
(2)varchar和text
text不能设置默认值,varchar可以,这个我们在后边再看。
text类型,包括(MEDIUMTEXT,LONGTEXT)也受单表 65535 最大行宽度限制,所以他支持溢出存储,只会存放前 768 字节在数据页中,而剩余的数据则会存储在溢出段中。虽然 text 字段会把超过 768 字节的大部分数据溢出存放到硬盘其他空间,看上去是会更加增加磁盘压力。但从处理形态上来讲 varchar 大于 768 字节后,实质上存储和 text 差别不是太大了。因为超长的 varchar 也是会用到溢出存储,读取该行也是要去读硬盘然后加载到内存(为什么要这么做呢,试想下,如果我们无脑把数据加载到内存中,如果存取了大量文本,数据量太大了,可能内存都放不下了,所以我们可以只加载少量数据,等真正要的时候,再去磁盘读取完整数据,也能减轻内存的压力),基本认为是一样的。
根据存储的实现:可以考虑用 varchar 替代 text,因为 varchar 存储更弹性,存储数据少的话性能更高。
如果存储的数据大于64K,就必须使用到 mediumtext,longtext,因为 varchar 已经存不下了。
如果 varchar(255+) 之后,和 text 在存储机制是一样的,性能也相差无几。
日期和时间数据类型
MySQL数据类型 | 含义 |
---|---|
date | 3字节,日期,格式:2014-09-18 |
time | 3字节,时间,格式:08:42:30 |
datetime | 8字节,日期时间,格式:2014-09-18 08:42:30 |
timestamp | 4字节,自动存储记录修改的时间 |
year | 1字节,年份 |
3、建表约束
(Create Table Constraints)
目前我们已经学会了如何创建表,但是,一张表不止有一列,数据库中的表不止有一个,建表约束说的就是,我们应该如何规范表中的数据以及表和表的关系。
(1)MySQL约束类型
约束名称 | 描述 |
---|---|
NOT NULL | 非空约束 |
UNIQUE | 唯一约束,取值不允许重复, |
PRIMARY KEY | 主键约束(主关键字),自带非空、唯一、索引 |
DEFAULT | 默认值(缺省值) |
FOREIGN KEY | 外键约束(外关键字) |
(2)[NOT] NULL约束
非空约束指的是,如果我们要在这张表中添加数据,设定了非空约束的列必须赋值,不能为空:
create table `ydlclass`.`author`(
`aut_id` int ,
`aut_name` varchar(50) not null, # 这就是非空约束
`gander` char(1) default '男',
`country` varchar(50),
`brithday` datetime
primary key(aut_id,aut_name)
);
(3)UNIQUE约束
唯一约束指的是,如果我们要在这张表中添加数据,设定了唯一约束的列中的值不能重复,不能为空:
实现方法1(表的定义最后施加)
-- 创建图书表
create table `ydlclass`.`book`(
`id` int primary key auto_increment,
`name` varchar(50) not null,
`bar_code` varchar(30) not null,
`aut_id` int not null,
unique(bar_code) -- 这就是唯一约束的定义
)
实现方法2(字段定义的最后施加)
-- 创建图书表
create table `ydlclass`.`book`(
`id` int primary key auto_increment,
`name` varchar(50) not null,
`bar_code` varchar(30) not null unique, -- 这就是唯一约束的定义
`aut_id` int not null,
)
(4)DEFAULT约束
默认约束指的是,如果我们要在这张表中添加数据,如果为给设定了默认约束的列赋值,该列会自动填充默认值:
-- 创建作者表
create table `ydlclass`.`author`(
`aut_id` int ,
`aut_name` varchar(50) not null,
`gander` char(1) default '男', -- 这就是默认约束
`country` varchar(50),
`brithday` datetime
primary key(aut_id,aut_name)
);
(5)PRIMARY KEY约束
主键(PRIMARY KEY)的完整称呼是“主键约束”,是 MySQL中使用最为频繁的约束。一般情况下,为了便于 DBMS 更快的查找到表中的记录,都会在表中设置一个主键。
主键分为单字段主键和多字段联合主键,本节将分别讲解这两种主键约束的创建、修改和删除。
使用主键应注意以下几点:
- 每个表只能定义一个主键。
- 主键值必须唯一标识表中的每一行,且不能为 NULL,即表中不可能存在有相同主键值的两行数据。这是唯一性原则。
- 一个字段名只能在联合主键字段表中出现一次。
- 【联合主键】不能包含不必要的多余字段。当把联合主键的某一字段删除后,如果剩下的字段构成的主键仍然满足唯一性原则,那么这个联合主键是不正确的。这是最小化原则。
单个字段作为主键(方法1)
-- 创建作者表
create table `ydlclass`.`author`(
`aut_id` int primary key, -- 这就是主键约束
`aut_name` varchar(50) not null,
`gander` char(1) default '男',
`country` varchar(50),
`brithday` datetime
);
单个字段作为主键(方法2)
-- 创建作者表
create table `ydlclass`.`author`(
`aut_id` int ,
`aut_name` varchar(50) not null,
`gander` char(1) default '男',
`country` varchar(50),
`brithday` datetime,
primary key(aut_id) -- 这就是主键约束
);
联合主键:多个字段同时作为主键
-- 创建作者表
create table `ydlclass`.`author`(
`aut_id` int ,
`aut_name` varchar(50) not null,
`gander` char(1) default '男',
`country` varchar(50),
`brithday` datetime,
primary key(aut_id,aut_name) -- 这就是联合主键
);
(6)AUTO_INCREMENT约束
需要配合主键使用,有个这个约束,我们在向表中插入数据时,不需要额外关心主键的数据,他会自动帮我们维护一个递增的主键:
-- 创建图书表
create table `ydlclass`.`book`(
`id` int primary key auto_increment, -- 这就是自动递增
`name` varchar(50) not null,
`bar_code` varchar(30) not null unique,
`aut_id` int not null,
foreign key (aut_id) references author(aut_id)
)
(7)FOREIGN KEY约束
外键维护的表与表之间的关系,他规定了当前列的数据必须来源于一张其他表的某一列中的主键:
外键会产生的效果
1、删除表时,如果不删除引用外键的表,被引用的表不能直接删除
2、外键的值必须来源于引用的表的主键字段
#语法:
FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);
-- 创建作者表
create table `ydlclass`.`author`(
`aut_id` int ,
`aut_name` varchar(50) not null,
`gander` char(1) default '男',
`country` varchar(50),
`brithday` datetime
primary key(aut_id,aut_name)
);
-- 创建图书表
create table `ydlclass`.`book`(
`id` int primary key auto_increment,
`name` varchar(50) not null,
`bar_code` varchar(30) not null unique,
`aut_id` int not null,
foreign key (aut_id) references author(aut_id) -- 这就是外键约束
)
4、对表的修改操作
查看当前数据库中所有表:SHOW TABLES;
查看表结构:DESC 表名;
desc authors;
修改表有5个操作,但前缀都是一样的:ALTER TABLE 表名…(不重要)
- 修改表之添加列:ALTER TABLE 表名 add (列名 列类型,…,列名 列类型);
alter table author add (hobby varchar(20),address varchar(50));
- 修改表之修改列类型:ALTER TABLE 表名 MODIFY 列名 列的新类型;
alter table author modify address varchar(100);
- 修改表之列名称列类型一起修改:ALTER TABLE 表名 CHANGE 原列名 新列名 列名类型;
alter table author change address addr varchar(60);
修改表之删除列:ALTER TABLE 表名 DROP 列名;
alter table author drop addr;
- 修改表之修改表名:ALTER TABLE 表名 RENAME TO 新表名
alter table author rename authors;
- 删除表:
drop table if exists 表名;
上述操作使用比较少 后续需要使用再查也是可以的
四、DML(数据操作语言)语法(重要)
该语言用来对表记录进行操作(增、删、改),不包含查询。
1、插入数据
insert into `authors` (aut_name,gander,country,brithday,hobby) values ('罗曼罗兰','女','漂亮国','1969-1-14','旅游');
insert into `authors` values (2,'罗曼罗兰2','女','漂亮国','1969-1-14','旅游');
说明:
在数据库中所有的字符串类型,必须使用单引号。
(列名1,列名2,列名3)可省略,表示按照表中的顺序插入。但不建议采取这种写法,因为降低了程序的可读性。
当然我们还可以批量插入:
insert into `authors` (aut_name,gander,country,brithday,hobby) values ('罗曼罗兰','女','漂亮国','1969-1-14','旅游'),('海明威','男','老人与海','1969-1-3','看书');
2、修改记录
修改某列的全部值:update 表名 set 列名1=列值1(,列名2=列值2);
UPDATupdate `authors` set aut_name = '吴军';
update `authors` set aut_name = '吴军',country='中国';
上边的语句会讲改表中所有的数据全修改,因此我们可以使用where语句进行限制,如下:
UPDATE author set aut_name='lucy' where aut_id = 1;
where是一个很关键的关键字,我们可以使用where关键字实现丰富的筛选,他很像我们的if语句,可以使用各种条件运算:
可使用的逻辑运算符如下:=、!=、<>、<、>、>=、<=、between…and、in(…)、is null、not、or、and,其中in(…)的用法表示集合。
where aut_id >1
where aut_id in (1,3,5)
where aut_id between 1 and 4
where aut_id >1 and aut_name=‘xxx’
where aut_name is null
where aut_name is not null
-- 字符串也使用=比较,不是 ==也不是equals
update `authors` set aut_name = '王五',
-- 使用in关键字
update `authors` set aut_name = '张三',country='中国' where aut_id in (7,9);
-- 逻辑运算都可以
update `authors` set aut_name = '王五',country='中国' where gander = '男' and country = '中国';
-- 使用between关键字
update `authors` set aut_name = '玉帝',country='中国' where aut_id between 7 and 9
-- 空值使用is null \ is not null
update `authors` set aut_name = '王五',country='中国' where birthday is not null;
3、删除数据
delete from 表名 (where 条件);
不加where条件时会删除表中所有的记录,所以为了防止这种失误操作,很多数据库往往都会有备份。
delete from author where auth_id = 1;
还有一个关键字:truncate,truncate是DDL语言,操作立即生效,原数据不会放到rollback segment中,不能回滚。
当表被truncate后,表和索引的所占空间会恢复到初始大小,delete操作不会减少表和索引的所占空间。
truncate和delete只删除数据,drop则删除整个表(结构和数据)。
truncate速度快,效率高,可以理解为先把表删除了,再重新建立。
truncate和delete均不会使表结构及其列、约束、索引等发生改变。