MySQL数据库——SQL语言

文章目录

MySQL数据库——SQL语言

前言

结构化查询语言（Structured Query Language），简称SQL，是数据库的标准语言，可以通过DBMS对数据库进行定义数据，操纵数据，查询数据，数据控制等。

一、数据定义语言（DDL）

• Data Dafinitaon Language

• 如何创建表——create

• 删除表——drop

• 修改表——alter

• 清空表——truncate，彻底清空，无法找回

show databases;                                     #查看所有数据库
show tables;                                     #查看所有表
drop database 库名;                                #删除数据库
create database 库名 default character set utf8;    #创建数据库
use 库名;                                            #选择数据库
create table 表名(id int(3),name varchar(20));    #创建表
desc 表名;                                        #查看表
insert into 表名 values(1,'user1');                #插入数据
alter table 表名 add(age int(3));                    #添加表字段语句
alter table 表名 drop id;                            #删除表字段语句
alter table 表名 modify age varchar(2);            #修改表字段类型格式
alter table 表名 change age plage int(3);            #修改表字段名称
alter table 表名1 rename 新表名;                      #修改表名
truncate table 表名;                                #清空表结构
drop table 表名;                                    #删除表结构

二、数据操纵语言（DML）

• insert：向表中插入数据

• delete：删除表中数据，格式——

delete from 表名 [where 条件]

• update:修改表中的数据，格式——

update 表名 set 字段1=值1[,字段2=值2] [where 条件]

• where:对表中的数据增加条件限制，起到过滤的作用

格式：where 字段名 关系运算符 值 [or|and 条件2]

关系运算符：>,>=,<,<=,=,不等于!=或<>

• null值操作：比较null时，不能使用=或者!=和<>，而是使用 is或者is not，在select子句中，使用关系运算符。

三、事务控制语言（TCL）

事务控制语言(Transation Control Language)有时可能需要使用DML进行批量数据的删除、修改、增加。比如，在一个员工系统中，想删除一个人的信息，除了删除这个人的基本信息外，还应该删除与此人的其他信息，如邮箱、地址等，那么从开始执行到结束，就会构成一个事务，对于事物，要保证事务的完整性，要么成功，要么撤回。

事务要符合四个条件（ACID）：

1.原子性(Atomicity)：事务要么成功，要么撤回，不可切割性。

2.一致性(Consistency)：事务开始前和结束后，要保证数据的一致性。转账前账号A和账号B的钱的总数为1000，转账后账号A和账号B的钱总数还是1000。

3.隔离性(Isolation)：当涉及到多用户操作同一张表时，数据库会为每一个用户开启一个事务，那么当其中一个事务正在进行时，其他事务应该处于等待状态，保证食物之间不会受影响。

4.持久性(Durability)：当一个事务被提交后，我们要保证数据库里的数据是永久改变的。即使数据库崩溃了，我们也要保证事务的完整性。

commit：提交
rollback：撤回，回滚
savepoint：保存点

只有DML操作会触发一个事务。存储引擎（Engine）：就是指表类型，当存储引擎为innodb时，才会支持事务；Myisam引擎不支持事务。

事务的验证：第一步：start transaction（交易）；第二步：savepoint 保存点名称；第三步：DML；第四步：commit/rollback。

四、数据查询语言（DQL）

数据查询语言(Data Query Language)

1.select/for 基本查询语句

格式：select 子句 from 子句 
select 字段名[,字段名2……] from 表名;

2.给列起别名（临时）

格式：select 列名1 as '要起的名' [,列名 as '要起的名',……] from 表名;
例：select name as 'xingming',job as 'gongzuo' from test1;

as也可以用于克隆表，复制表。

create table 新表名 as (select * from 被克隆表名);

#将结果集做为一张表进行查询的时候，我们也需要用到别名，示例：

需求：从info表中的id和name字段的内容做为"内容"输出id的部分

mysql>select id from (select id,name from info);

ERROR 1248 (42000):Every derived table must have its own alias

此时会报错，原因为：

select * from表名，此为标准格式，而以上的查询语句，“表名"的位置其实是一个完整结果集，mysql并不能直接识别，而此时给与结果集设置一个别名，以"select a.id from a"的方式查询将此结果集视为一张"表”，就可以正常查询数据了，如下：

select a.id from (select id,name from info)a;
相当于
select info.id,name from info;
select 表.字段，字段from表;

3.where子句

作用：在增删改查时，起到条件限制的作用。

多条件写法：in | not in （集合元素，使用逗号分开）;注意同一个字段有多个值的情况下使用。in相当于or，not in相当于and。

all | any与集合连用，此时集合中的元素不能是固定的必须是从表中查询到的数据。

范围查询：colname between val1 and val2——查询val1到val2范围中的数据。

模糊查询：like；通配符：%表示0或0个以上的字符，_表示匹配一个字符；格式：colname like value。

#查询test表中部门号为1和2的员工编号，姓名，年龄
select id,name,age from test where deptid=1 or deptid=2;
或select id,name,age from test were deptid in(1,2);

#查询test表中部门号不是1和2的员工的所有信息
select * from test where deptid<>1 and deptid<>2;
或select * from test where deptid not in (1,2);

#查询年龄大于员工a,b,c三人的员工的信息(all|any与集合的连用)
select * from test where age>all(select age from test where name in ('a','b','c'));

#查询年龄在40到50范围之间的员工
select * from test where age between 40 and 50；

#查询名字中有a和s的员工
select * from test where name like '%a%' and name like '%s%';
或select * from test where name like '%a%s%' or name like '%s%a%';

4.排序查询 Order By子句

当在查询表中数据时，记录比较多，有可能需要进行排序，此时使用order by子句。

语法：select 字段名 from 表名 [where 子句] [order by 子句]

注意：可以通过一个或多个字段排序，多字段的时候先执行左边的排序，当左边的排序结果一致时，再执行往右一个逗号的排序，以此类推。

order by 字段名 [ASC | DESC][,字段名2 [ASC | DESC]];

排序规则：ASC——升序；DESC——降序。如果不输入则默认是升序排序。

5.Distinct去重

有的时候我们需要对重复的记录进行去重操作，比如，查询表中有哪些IP地址，此时一个IP地址只需要显示一条记录就够。

位置：必须在select关键字后。

select distinct 字段名 from 表名
#查test表中所有的名字并去重
select distinct name from test

6.分组查询 Group By子句

分组查询与分组函数（聚合函数）：有的时候，我们可能需要查询表中的记录总数，或者查询表中每个部门的总工资，平均工资，总人数。这种情况需要对表中的数据进行分组统计，需要group by子句。

#位置
select …… from name [where 条件] [group by子句] [order by子句]
#用法
group by Field1[,Firld2]
#注意：在分组查询时，select子句中的字段，除了聚合函数外，只能写分组字段。
#例：求每个部门的平均工资
select bumen,avg(gongzi) as pingjungongzi from test group by bumen;

聚合函数：

1.count(Filed)：统计FIled字段的记录数。

2.sum(Filed)：统计Filed字段的和。

3.avg(Filed)：统计Filed字段的平均值。

4.max(Filed)：统计Filed字段中的最大值。

5.min(Filed)：统计Filed字段中的最小值。

聚合函数会忽略null的值。因此有时候需要使用函数：ifnull（field，value）（如果field字段对应的值不是null，就使用field原来的值，如果是null，就使用value默认值）。

7.分组查询添加条件Having子句

在分组查询时，有的时候可能需要再次使用条件进行过滤，这个时候不能where子句，应该使用having子句。having子句后面可以使用聚合函数。

#位置在group by之后
#例：查询以员工平均年龄大于20的部门
select bumen,avg(age) as page from test group by bumen having avg(age)>20;

8.Exists判断

EXISTS这个关键字在查询时，主要用于判断子查询的结果集是否为空。如果不为空，则返回TRUE；反之，则返回FALSE

#查询如果存在分数等于80的记录则计算t1表中的字段数
select count(*) from t1 where exists(select id from t1 where score=80)；

成功则显示：

如果不成立中没有则显示：

9.Limit分页

limit offset,size

offset表示：行数据索引；size表示：取多少条数据。即从sffset行开始，取size行数据。

9.基本查询总结

基本的查询语句包含的子句有：select子句，from子句，group by子句，having子句，order by子句。一个完整的查询语句：

select … from … [where …][group by …][having …][order by…]

执行顺序：先执行from子句，再执行where子句，然后group by子句，再次having子句，之后是select子句，最后order by子句。

10.高级关联查询

有的时候，查询的数据以简单的查询语句满足不了，并且使用的数据在表中不能直观体现出来，而是需要预先经过一次查询才会有所体现。子查询嵌入到的查询语句称之为父查询。

子查询返回的数据特点：

1.可能是单行单列的数据

2.可能是多行单列的数据

3.可能是单行多列的数据

4.可能是多行多列的数据

子查询可以在where、from、having、select子句中，在select子句中时相当于外连接的另外一种写法。

子查询也被称作内查询或者嵌套查询，是指在一个查询语句里面还嵌套着另一个查询语句。子查询语句是先于主查询语句被执行的，其结果作为外层的条件返回给主查询进行下一步的查询过滤。

PS:子语句可以与主语句所查询的表相同，也可以是不同表

#查询表中各部门人员中年龄大于平均值的人
select name，age，a.bumen,b.page from test a,
(select id,avg(ifnull(age,0)) as av from test group by id) b
where a.id=b.id and a.age>b.page
order by id ASC;

五、数据控制语言（DCL）

数据控制语言Data Control Language，作用是用来创建用户，给用户授权，撤销权限，删除用户。

1.创建用户

create user '用户名'@'ip' identified by 新密码;
例：
create user 'user1'@'192.168.1.1' identified by '111';

2.显示用户权限

show grants for '用户名'@'ip';

3.授权

grant 权限1，权限2… on 库名.* to '用户名'@'ip';
例：
grant select,drop,insert on test.* to 'user1'@'192.168.1.%';
#192.168.1.%——整个网段

DML权限：insert，delete，update

DQL权限：select

DDL权限：create，alter，drop…

（8.0版本不能直接通过授权的方式创建用户）

4.撤销权限

revoke 权限1，权限2…… on 库名.* from 用户名@ip;
revoke drop on test.* from [email protected].%;

5.删除用户

drop user 用户名@ip/主机名;    #删除用户一定要加ip/主机名
flush privileges;            #刷新

六、Mysql操作实例

1.创建表

语法

create table 表名(
    字段名 列类型 [可选的参数],    #记住加逗号
    字段名 列类型 [可选的参数],    #记住加逗号
    字段名 列类型 [可选的参数],    #最后一行不加逗号
)charset=utf8;                   #后面加分号

列约束

• auto_increment：自增 1，注入数据的时候不输入值的话自动增加1

• primary key：主键索引，加快查询速度，列的值不能重复

• not null：标识该字段不能为空

• default：为该字段设置默认值

实例

mysql> create table test(                 #创建test表
id int(2) auto_increment primary key,    #id值为整数，主键，自增1，不超过2个字节
name char(5) not null,                    #名字不为空
age varchar(3) not null default''        #年龄不为空，不输入则填入默认值
);

2.查看表结构

此时的表数据为空：

3.插入数据

语法

insert into 表名 (列1,列2) values (值1,'值2');

4.数据查询

此时我们再查看表发现已经有数据了：

select 列1,列2（*代表所有） from 表名；

我们可以对表中数据进行查询，比如查看年龄小于80岁的姓名：

5.修改表名

alter table 旧表名 rename 新表名;

6.增加字段

添加到末尾

alter table 表名 add 字段名1 列类型 [可选参数],add 字段名2 列类型 [可选参数];

添加到开头

alter table 表名 add 字段名 列类型 [可选参数] first;

添加到指定字段后面

alter table 表名 add 字段名 列类型 [可选参数] after 指定字段名;

7.删除字段

alter table 表名 drop 字段名;

8.修改字段

修改字段类型格式

alter table 表名 modify 字段名 数据类型 [完整性的约束条件..];

修改字段名

alter table 表名 change 旧字段名 新字段名 数据类型 [完整性的约束条件..];

9.删除表

drop table 表名;

10.复制表结构

create table 新表名 like 被复制的表名;

11.操作表数据行

增加数据

insert into 表名 (列1,列2) values (值1,'值2')

#暴力复制
insert into 被添加数据的表名 (字段) select 字段 from 被复制的表名;

删除数据

delete from 表名 where 字段名[条件]

truncate 表名;

delete与truncate删除的区别：

delete之后，插入的数据从上一次主键自增加1开始，truncae则是从1开始。

delete删除，是一行一行的删除，truncate是全选删除之后再重新创建结构属性一致的表。

所以truncate删除的速度是高于delete的，truncate删除的数据无法恢复。

七、多表操作

概念：上述的操作中，基本都是在单表中进行操作，那当在查询时，所需要的数据不在一张表中，可能在两张或多张表中。此时需要同时操作这些表，即关联查询。

等值连接：在做多张表查询时，这些表中应该存在着有关联的两个字段，使用某一张表中的一条记录与另外一张表通过相关联的两个字段进行匹配，组合成一条记录。

笛卡尔积：在做多张表查询时，使用某一张表中的每一条记录都与另外一张表的所有记录进行组合。比如A表有x条，B表有y条，最终组合数为x*y，这个值就是笛卡尔积，通常没有意义。

内连接：只要使用了join on，就是内连接，查询效果与等值连接一样。

用法：
select 字段n from 表A inner join 表B on 关联条件
或
select 字段n from 表A，表B where 关联条件

外连接：在做多张表查询时，所需要的数据，除了满足关联条件的数据外，还有不满足关联条件的数据，此时需要使用外连接。

1.驱动表（主表）：除了显示满足条件的数据，还需要显示不满足条件的数据的表。

2.从表（副表）：只显示满足关联条件的数据的表。

3.外连接分为三种：
种类指令含义左外连接表A left [outer] join 表B on 关联条件表A是驱动表，表B是从表右外连接表A rihgt [outer] join 表B on 关联条件表B是驱动表，表A是从表全外连接表A full [outer] join 表B on 关联条件两张表数据不管满不满足条件都显示
自连接：是内连接查询中一种特殊的等值连接，所谓的自连接就是指表与其自己当前表进行连接。自己和自己做连接。

外键：MySQL中“键”和“索引”的定义相同，所以外键和主键一样也是索引的一种。不同的是MySQL会自动为所有表的主键进行索引，但是外键字段必须由用户进行明确的索引。用于外键关系的字段必须在所有的参照表中进行明确地索引，InnoDB不能自动地创建索引。

使用条件：

① 两个表必须是InnoDB表，MyISAM表暂时不支持外键。

② 外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以。

外键可以是一对一的，一个表的记录只能与另一个表的一条记录连接，或者是一对多的，一个表的记录与另一个表的多条记录连接。例：

1.一对多，外键约束

constraint 自定义外键名 foreign key (被约束的字段) references 约束的表(约束的字段)
#可以在 create 和 alter 时使用，如果不指定constraint 外键名，MYSQL会自动生成一个名字

2.多对多

例：先创建一个男生表一个女生表，在进行左链接查询

#创建男生表，并插入数据
mysql> create table boy (id int auto_increment primary key,bname varchar(5) not null default '');
mysql> insert into boy (bname) values('jim'),('jack'),('tom');
#创建女生表，并插入数据
mysql> create table girl (id int auto_increment primary key,gname varchar(5) not null default '');
mysql> insert into girl values (1,'May'),(2,'Jessice'),(3,'Monice');
#创建关联表，并插入数据
mysql> create table class(id int auto_increment primary key,boyid int not null default '1',girlid int not null default '0',constraint fk_class_boy foreign key (boyid) references boy(id),constraint fk_class_girl foreign key (girlid) references girl(id));
mysql> insert into class(boyid,girlid) values (1,1),(1,2),(2,3),(3,3),(2,2);
#用left join左连接查询
mysql> select * from boy left join class on boy.id=class.boyid left join girl on girl.id=class.girlid;

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作。保持数据一致性，完整性，主要目的是控制存储在外键表中的数据。 使两张表形成关联，外键只能引用外表中的列的值！可以使得两张表关联，保证数据的一致性和实现一些级联操作。

END