SQL语句学习

SQL基础

数据库基础操作

MYSQL数据库操作

--创建库
create database 库名;

--创建库时判断库是否存在，不存在则创建
create database if not exists 库名;

--查看所有数据库
show databases;

--切换到指定的数据库
use 库名;

--查看当前数据库包含的表
show tables;

--查看数据库的结构定义信息
show create database 库名;

--删除数据库
drop database 库名;

--修改数据库的字符集为utf8
alter database 库名 character set utf8;

MYSQL数据库表操作

--创建表
create table 表名(
字段1 类型1,
字段2 类型2,
...........);

例：创建一个学生表，其中表字段
create table student(
  sno int,           #int 整数型
  mno int,
  sname varchar(10), #varchar字符串类型
  sex bit,           #Bit称为位数据类型，其数据有两种取值：0和1，长度为1位。在输入0以外的其他值时，系统均把它们当1看待。这种数据类型常作为逻辑变量使用，用来表示真、假或是、否等二值选择。
  grade decimal(5,2), #DECIMAL是MySQL中存在的精准数据类型，以字符串的形式保存精确的原始数值，而float，double则是非精准类型，在DB中保存的是近似值。在此示例中，grade列最多可以存储5位数字，小数位数为2位;
  primary key(sno),   #primary key主键唯一
  foreign key(mno) references B表(mno)    #外键需要关联另外一个表的主键；第一个mno是本表的，具体建表时看是否关联
  )

--查看表结构
desc 表名;

--查看创建表的SQL语句
show create table 表名;

--修改表名
alter table 表名 rename to 新的表名;

--修改字段名
alter table 表名 rename column 字段名 to 新的字段名;

--删除表
drop table 表名;

--删除表时判断表是否存在,若存在则删除
drop table if exists 表名;

--删除列
ALTER TABLE 【表名】 DROP 【列名】

--增加列
ALTER TABLE 【表名】 ADD 【列名】 【类型】
举例：alter table student add transactor varchar(10) not Null; 

--重命名列
ALTER TABLE 【表名】 CHANGE 【列名】【新名】 

--修改表字段
alter table 表名 modify column 字段名 新的字段类型;
alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];
alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

--创建索引
ALTER TABLE tbl_name ADD INDEX index_name (column list);

--删除索引
ALTER TABLE tbl_name DROP INDEX index_name (column list);

添加用户
create user '用户名'@'主机名' identified by '密码';
删除用户
drop user '用户名'@'主机名';

权限管理
查询权限
show grants for '用户名'@'主机名';

授予权限
--语法
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';

--授予faker用户所有权限，在任意数据库任意表上
grant all on *.* to 'faker'@'localhost';

撤销权限
--语法
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
--撤销faker用户对test数据库中city数据表的权限
revoke update on test.city from 'faker'@'localhost';

增删改查-基本适用于sqlserver、mysql、oracle数据库

INSERT-增

insert插入单行数据
语法：insert [into] <表名> [列名] values <列值>
例：insert into Students (姓名,性别,出生日期) values ('张三','男','1983/6/15')
注意：默认只有表名，将依次插入所有列
例：insert into Students values ('张三','男','1983/6/15','null','null')

insert,select语句将现有表中的 数据添加到已有的新表中
语法：insert into <已有的新表> <列名> select <原表列名> from <原表名>
例：insert into newstudent('张三','男','1983/6/15') select name,sex,birthday from strdent
列：全部数据插入新表中 insert into newstudent from student
注意：查询得到的数据个数、顺序、数据类型等，必须与插入的项保持一致

DELETE-删

DELETE 语句来删除表的一行或者多行数据
语法： DELETE FROM <表名> [WHERE 子句] [ORDER BY 子句] [LIMIT 子句]
<表名>：指定要删除数据的表名
ORDER BY 子句：可选项。表示删除时，表中各行将按照子句中指定的顺序进行删除
WHERE 子句：可选项。表示为删除操作限定删除条件，若省略该子句，则代表删除该表中的所有行
LIMIT 子句：可选项。用于告知服务器在控制命令被返回到客户端前被删除行的最大值
例： delete from student where grade ='' order by age limit 10   --删除学生表中某个年级下前10个年龄的学生

注意： delete删除别的表有外键需要先将别的这个字段置为null
例：B表中有外键num = 1；A表中这个外键num =1，删除B表外键报错，需将A表中的这个字段num置为空
先 update A set num = NULL where num =1  
再删除 delete B where num = 1

#也可truncate使用删除
truncate table 表名

UPDATE-改

用途：更新表中原有数据，set后面，更新字段值，既可以一次一项，也可以一次多项，使用where匹配字段
语法： UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
       UPDATE 表名称 SET 列名称1 = 新值,列名称2 = 新值,列名称n = 新值 WHERE 条件

与select联合使用
update用来更新用select选择出的数据
update 表名 set 字段名='值' select .....

SELECT-查

--查询表中所有数据
select *from 表名;

--查询表中年龄在某区间内的
--方式1 between..and..
select *from 表名 where age between 12 and 22;
--方式2 &&
select *from 表名 where age>=12 && age<=22;
--方式3 and
select *from 表名 where age>=12 and age<=22;

指定条件查询
①单个条件(or / in)
--查询表中年龄为2,12,22岁的记录
--方式1 or
select *from 表名 where age=2 or age=12 or age=22;
--方式2 in
select *from 表名 where age in(2,12,22);
②多个条件(and)
--查询users表中年龄为23，性别为女，名字为小楠的记录
select *from users where age=23 and gender='女' and name='小楠'; 

--查询表中序号不为空的记录
select *from 表名 where 表字段 is not null;
--查询user表中序号为空的记录
select *from 表名 where 表字段 is null;

模糊查询（like）
_:单个任意字符
%:多个任意个字符
--查询表中姓名第一个字为李的记录
select *from 表名 where name like '李%';
--查询表中姓名第二个字为李的记录
select *from 表名 where name like '_李%';
--查询表中姓名含有李字的记录
select *from 表名 where name like '%李%';
--查询表中姓名是两个字的记录
select *from 表名 where name like '__';

去除重复记录查询（distinct）
--查询表中不相同的记录
--select distinct 字段 from 表名;
select distinct 表字段 from 表名;

排序查询（order by）
--查询表中记录，并以升序排序
select *from 表名 order by 字段; --默认升序
 
--查询表中记录，并以降序排序
select *from 表名 order by 字段 desc;   --desc降序

--多个排序条件时，只有当第一个排序条件值一样，才会执行第二个排序条件，以此类推
--查询users表中记录，并体育成绩降序，年龄降序
select *from users order by PE desc,age desc;

聚合函数（其中as后面将字段作为新的别名，方便区分）
①计算和（sum）
select sum(字段) (as sumvalue) from 表名;
②计算最大值（max）
select max(字段) (as maxvalue) from 表名;
③计算最小值（min）
select min(字段) (as minvalue) from 表名;
④计算平均值（avg）
select avg(字段) (as avgvalue) from 表名;
⑤计算个数（count）
select count(字段) (as totalcount) from 表名;

分组查询（group by）
select 字段 from 表名 where 条件 group by  字段
或者
select 字段 from 表名 group by 字段 having  过滤条件
例：--查询users表中的记录,按照性别分组,分别查询男、女的体育成绩平均分,人数 要求:分数高于80分的人,分组之后,人数要大于2个人
select gender,avg(PE),count(id) from users where PE > 80 group by gender having count(id)>2;

分页查询（limit）
注意：第一条记录的索引是0
--查询表中的前10行条记录
select *from 表名 limit 10;
--查询表中第2~11条记录 (从第2条记录开始累加10条记录，查询结果是从第3行开始后的11条数据，即总数还是11条)
select *from 表名 limit 1,10;

子查询
select *from 表1 where 表1字段 = (select 表2字段 from 表2 where 表2字段2='AA')   --表1字段和表2字段一样
列：select *from studentinfowhere class = (select class from classinfo where className='A')

相关子查询
子级查询不能独立运行，要依赖主查询数据。
select (select name from studentinfo where studentId=e.studentId),(select courseName from courseinfo where courseId = e.courseId),score from examinfo e

将子级查询作为一个表查询
select s.name,e.courseId,e.score
from studentInfo s
join examinfo e
on s.studentId =e.studentId
join (select courseId , max(score) max_score from examInfo group by courseId) as c on e.courseId = c.courseId and e.score = c.max_score

内连接

内连接查询的是两张表的并集，也就是A表和B表都必须有数据才能查询出来；三种查询方式（看自己熟悉习惯那种）
-- join
select * from A  join B on A.id = B.id 

-- inner join
select * from A inner join B on A.id = B.id 

-- 逗号的连表方式就是内连接
select * from A , B where A.id = B.id   

左外连接和左连接

左外连接和左连接是以左表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将左表所有的查询信息列出，而右表只列出ON后条件与左表满足的部分。左连接全称为左外连接，是外连接的一种。下面2个查询的结果是一样的
-- left join
select * from A left join B on  A.id = B.id 

-- left outer join
select * from A left outer join B on  A.id = B.id 

右外连接和右连接

右外连接和右连接是以右表为基础，根据ON后给出的两表的条件将两表连接起来。结果会将右表所有的查询信息列出，而左表只列出ON后条件与右表满足的部分。右连接全称为右外连接，是外连接的一种。下面2个查询的结果是一样的
-- right join
select * from A right join B on  A.id = B.id 

-- right outer join
select * from A right outer join B on  A.id = B.id 

全连接

全连接显示两侧表中所有满足检索条件的行
select * from a left join b on a.id = b.id union select * from a right join b on a.id = b.id

1.内连接与外连接的区别是什么？
内连接和外连接的区别：
内连接：inner join(等值连接) 只返回两个表中联结字段相等的数据。
外连接：返回包括左/右表中的所有记录和右/左表中联结字段相等的记录。

2.左外连接和右外连接的区别是什么？
左外连接和右外连接的区别：
左外连接也称左连接。以左表为基表，在FROM子句中使用关键字“LEFT OUTER JOIN”或关键字“LEFT JOIN”来连接两张表。
右外连接也称右连接。以右表为基表，在FROM子句中使用关键字“RIGHT OUTER JOIN”或关键字“RIGHT JOIN”来连接两张表。

3.概括
1.内连接就是取交集的部分。
2.左连接就是左表全部的数据加上交集的数据。
3.右连接就是右表全部的数据加上交集的数据。
4.交叉连接就是数据全都要。

视图

从SQL的角度来看，视图就是一张表，存在表名、字段列
视图和实体表的区别就在与：是否保存了实际数据。
实体表中保存实际数据，使用实体表创建视图后，实体表中的数据发生变化，视图查询出的数据就会发生变化。
从视图中读取数据时，视图会在内部执行对应的SELECT语句，并创建出一张临时表。

视图的优点：简化用户操作
1.将频繁使用的查询语句保存成视图，这样就不用每次都重新写。在创建好视图后，可以将视图和其他实体表一样，在SELECT语句中进行调用
2.对于常用的联表查询，可将联表的结果创建为视图，后续使用联表结果时，不用重新写select语句，直接调用视图即可

创建视图

create view v_表名 as select * from 表名
select * from v_表名

视图数据增和改

视图也可以使用INSERT,UPDATE进行数据的增和改，但是需要符合以下条件：
（1）视图FROM子句只有一张表；
（2）未使用GROUP BY子句，未使用HAVING子句

视图新增和修改数据后，视图对应表中的数据同步修改

删除视图

drop view v_表名