数据库操作的基本语法大全

转载自：https://www.cnblogs.com/rqy0526/p/11015943.html

 

1. 操作数据库：CRUD
1. C(Create):创建
* 创建数据库：
* create database 数据库名称;
* 创建数据库，判断不存在，再创建：
* create database if not exists 数据库名称;
* 创建数据库，并指定字符集
* create database 数据库名称 character set 字符集名;
* 练习： 创建db4数据库，判断是否存在，并制定字符集为gbk
* create database if not exists db4 character set gbk;
2. R(Retrieve)：查询
* 查询所有数据库的名称:
* show databases;
* 查询某个数据库的字符集:查询某个数据库的创建语句
* show create database 数据库名称;
3. U(Update):修改
* 修改数据库的字符集
* alter database 数据库名称 character set 字符集名称;
4. D(Delete):删除
* 删除数据库
* drop database 数据库名称;
* 判断数据库存在，存在再删除
* drop database if exists 数据库名称;
5. 使用数据库
* 查询当前正在使用的数据库名称
* select database();
* 使用数据库
* use 数据库名称;
2. 操作表
1. C(Create):创建
1. 语法：
create table 表名(
列名1 数据类型1,
列名2 数据类型2,
....
列名n 数据类型n
);
* 注意：最后一列，不需要加逗号（,）
* 数据库类型：
1. int：整数类型
* age int,
2. double:小数类型
* score double(5,2)
3. date:日期，只包含年月日，yyyy-MM-dd
4. datetime:日期，包含年月日时分秒 yyyy-MM-dd HH:mm:ss
5. timestamp:时间错类型 包含年月日时分秒 yyyy-MM-dd HH:mm:ss
* 如果将来不给这个字段赋值，或赋值为null，则默认使用当前的系统时间，来自动赋值
 
6. varchar：字符串
* name varchar(20):姓名最大20个字符
* zhangsan 8个字符 张三 2个字符
 
 
* 创建表
create table student(
id int,
name varchar(32),
age int ,
score double(4,1),
birthday date,
insert_time timestamp
);
* 复制表：
* create table 表名 like 被复制的表名;
2. R(Retrieve)：查询
* 查询某个数据库中所有的表名称
* show tables;
* 查询表结构
* desc 表名;
3. U(Update):修改
1. 修改表名
alter table 表名 rename to 新的表名;
2. 修改表的字符集
alter table 表名 character set 字符集名称;
3. 添加一列
alter table 表名 add 列名 数据类型;
4. 修改列名称 类型
alter table 表名 change 列名 新列别 新数据类型;
alter table 表名 modify 列名 新数据类型;
5. 删除列
alter table 表名 drop 列名;
4. D(Delete):删除
* drop table 表名;
* drop table if exists 表名 ;
 
3.增删改表中数据
1. 添加数据：
* 语法：
* insert into 表名(列名1,列名2,...列名n) values(值1,值2,...值n);
* 注意：
1. 列名和值要一一对应。
2. 如果表名后，不定义列名，则默认给所有列添加值
insert into 表名 values(值1,值2,...值n);
3. 除了数字类型，其他类型需要使用引号(单双都可以)引起来
2. 删除数据：
* 语法：
* delete from 表名 [where 条件]
* 注意：
1. 如果不加条件，则删除表中所有记录。
2. 如果要删除所有记录
1. delete from 表名; -- 不推荐使用。有多少条记录就会执行多少次删除操作
2. TRUNCATE TABLE 表名; -- 推荐使用，效率更高 先删除表，然后再创建一张一样的表。
3. 修改数据：
* 语法：
* update 表名 set 列名1 = 值1, 列名2 = 值2,... [where 条件];
 
* 注意：
1. 如果不加任何条件，则会将表中所有记录全部修改。
4.查询表中的记录
* select * from 表名;
1. 语法：
select
字段列表
from
表名列表
where
条件列表
group by
分组字段
having
分组之后的条件
order by
排序
limit
分页限定
2. 基础查询
1. 多个字段的查询
select 字段名1，字段名2... from 表名；
* 注意：
* 如果查询所有字段，则可以使用*来替代字段列表。
2. 去除重复：
* distinct
select distinct * from table;

3. 计算列
* 一般可以使用四则运算计算一些列的值。（一般只会进行数值型的计算）
* ifnull(表达式1,表达式2)：null参与的运算，计算结果都为null
* 表达式1：哪个字段需要判断是否为null
* 如果该字段为null后的替换值。
4. 起别名：
* as：as也可以省略
 
3. 条件查询
1. where子句后跟条件
2. 运算符
* > 、< 、<= 、>= 、= 、<>
* BETWEEN...AND
* IN( 集合)
* LIKE：模糊查询
* 占位符：
* _:单个任意字符
* %：多个任意字符
* IS NULL
* and 或 &&
* or 或 ||
* not 或 !
例子：
-- 查询年龄大于20岁
 
SELECT * FROM student WHERE age > 20;
 
SELECT * FROM student WHERE age >= 20;
 
-- 查询年龄等于20岁
SELECT * FROM student WHERE age = 20;
 
-- 查询年龄不等于20岁
SELECT * FROM student WHERE age != 20;
SELECT * FROM student WHERE age <> 20;
 
-- 查询年龄大于等于20 小于等于30
 
SELECT * FROM student WHERE age >= 20 && age <=30;
SELECT * FROM student WHERE age >= 20 AND age <=30;
SELECT * FROM student WHERE age BETWEEN 20 AND 30;
 
-- 查询年龄22岁，18岁，25岁的信息
SELECT * FROM student WHERE age = 22 OR age = 18 OR age = 25
SELECT * FROM student WHERE age IN (22,18,25);
 
-- 查询英语成绩为null
SELECT * FROM student WHERE english = NULL; -- 不对的。null值不能使用 = （!=） 判断
 
SELECT * FROM student WHERE english IS NULL;
 
-- 查询英语成绩不为null
SELECT * FROM student WHERE english IS NOT NULL;
 
 
 
-- 查询姓马的有哪些？ like
SELECT * FROM student WHERE NAME LIKE '马%';
-- 查询姓名第二个字是化的人
 
SELECT * FROM student WHERE NAME LIKE "_化%";
 
-- 查询姓名是3个字的人
SELECT * FROM student WHERE NAME LIKE '___';
 
 
-- 查询姓名中包含德的人
SELECT * FROM student WHERE NAME LIKE '%德%';
 

--子查询
--查询平均工资最低的部门id
--注意：mysql里的聚合函数不能嵌套，oracle可以
select dept_id from employee group by dept_id 
having avg(salary)=(select min(avg_sal) from
(select avg(salary) as avg_sal from employees
group by depart_id) t_avg_sal)

--in 任意一个
--any 任一
--all 所有值比较
--some 同any

简便写法，其他方法：
select dept_id from employee group by dept_id 
having avg(salary) <= all (select avg(salary) from employee group by dept_id)

--查询最低工资大于110号部门最低工资的部门ID和其最低工资
select dept_id,min(salary) from employees
where dept_id is not null
group by dept_id
having min(salary) >(select min(salary) from employee
where dept_id=110)


--查询本周下单最多的用户及他的最大订单金额、并且统计他的所有订单总金额
    select username,max(price),sum(price) from orders group by username

having count(*)=(select max(ordernum) from (select username,count(*) as ordernum

from orders where date=xx group by username) tmp)


--相关子查询
--查询员工中工资大于本部门平均工资的员工name，salary，dept_id
#方式1：使用相关子查询
select name,salary,dept_id from employee e1
where salary>(select avg(salary) from employee e2
where e2.dept_id=e1.dept_id)

#在from中声明子查询
select e.name,e.salary,e.dept_id
from employee e,
(select dept_id,avg(salary) avg_sal from employee group by dept_id) t_dept_avg_sal
where e.dept_id=t_dept_avg_sal.dept_id
and e.salary >t_dept_avg_sal.avg_sal

--查询员工的ID，salary，按照dept_name排序(可用子查询，可用left join)
select id,salary from employee e,dept d
where e.dept_id=d.dept_id
order by d.dept_name

 

 

高级查询

表链接查询
1.表链接查询（笛卡尔积：）两个分别为n列s行和m列k行关系的R和S的笛卡尔积是一个n+m的列s*k行的表。(查询出来后只有一条有效数据)
查询语句：

select * from 表名1，表名2......;

2.内连接查询（子表与主表建立表间关系，子表主键和主表外键建立表间关系）
查询语句：

 select 
 *（不建议写*会将子表写在前面，主表写在后面）
 from 表名1 as 别名（子表）
 inner join 表名2 as 别名 （主表）

#实际上就是主外键
#子表的外键 = 主表的主键

 on 表1.列=表2.列（子表和主表建立关系）
 where 表1.列名=数据

使用in和not in（范围查找，查找in中的范围，或者不在in中的范围）
查询语句：

select 
*/列名
from 表名
where in/not in （数据1，数据2，......）

即表示表中的某列中 in中有或没有的数据

3.左查询（左边表为参考，左边表中所有表数据会显示，左边表没有的数据，右边表有会进行填充到左边表）
*distinct:(去除重复的数据)*
查询语句：

select 
*/列名
from 表1名
left join 表2名
on 表1.列=表2.列

*已left左边的表为参考，查询出左边表中所有的数据，右边表中没有的数据已NULL填充。 *

3.右查询（右边表为参考，右边表中所有表数据会显示，右边表没有的数据，左边表有会进行填充到右边表）
查询语句：

select 
*/列名
from 表1名
right join 表2名
on 表1.列=表2.列

*已right右边的表为参考，查询出右边表中所有的数据，左边表中没有的数据已NULL填充。 *

原文链接：https://blog.csdn.net/temdian/article/details/118723709

1.给一个订单表--orders

select * from orders

2.查看2010年的订单

select * from orders where year(ordersDate)=2010;
 

--查看2010年之前的订单
select * from orders where year(ordersDate)<2010;

--查看2010之前的1~10月之间的订单
-- 年份<2010
-- 月份 1~10
select * from orders
where year(ordersDate)<2010
and month(ordersDate) between 1 and 10;

--查看客户名字超过2个字的订单记录
select * from orders where len(customer)>2;

-- 聚合函数

------------count() 计算次数---------------------------

--计算订单的数目，并且取中文别名
select count(*) as '订单总数' from orders;

--查看布鲁士的订单数目
select count(*) as '布鲁士的订单数' from orders
where customer='布鲁士';

------------sum() 求和---------------------------

--查看订单的总价,并且取中文别名
select sum(ordersPrice) as '总价' from orders;

--查看订单的总单数与销售总额,并且取中文别名
select count(*) as '数量',sum(ordersPrice) as '总价' from orders;

------------avg() 求平均值---------------------------

--求订单平均金额,并且取中文别名
select avg(ordersPrice) from orders;

--求订单的订单数，总和，平均值,并且取中文别名
select count(*),sum(ordersPrice),avg(ordersPrice) from orders;

--求Bush的订单总和与订单数和平均值
select count(*),
       sum(ordersPrice),
       round(avg(ordersPrice),2)
from orders
where customer='布鲁士';

------------max()最大值,min()最小值---------------------------

--求最高金额的订单
select max(ordersPrice) from orders;

--求最小金额的订单
select min(ordersPrice) from orders;

--求Bush的最大订单
--求Bush的最小订单
select max(ordersPrice),
       min(ordersPrice)
from orders where customer='布鲁士';

-----------聚合函数和分组函数group by结合使用--------------

--查看订单表中的客户

select  customer from orders;

-- 去重复 distinct
select distinct customer from orders;

-- 分组 group by
-- select 的后面只能出现聚合函数和分组依据
-- 原因:其他数据存在多个值  无法判断该取值哪一个值
select customer from orders group by customer;

--查看每个客户对应的订单数目
select customer,count(*) from orders
group by customer;

--查看每个客户对应的订单数目与总金额
select customer,
       count(*),
       sum(ordersPrice)
from orders
group by customer;

--查看每个客户对应的订单数目与总金额与平均值
select customer,
       count(*),
       sum(ordersPrice),
       avg(ordersPrice)
from orders
group by customer;

--查看订单数目最多的客户与总数，总金额
-- 先分组，取出数量
-- 再根据数量排序
-- 取第一个

查询本周下单最多的用户及他的最大订单金额、并且统计他的所有订单总金额

select username,max(price),sum(price) from orders where date between xx and xx group by username

having count(*)=(select max(ordernum) from (select username,count(*) as ordernum

from orders where date=xxx group by username)tmp

--查看订单平均值最高的客户
select customer,
       avg(ordersPrice)
from orders
group by customer --分组
order by avg(ordersPrice) desc;  --排序

-- avg 平均
-- sum 求和
-- max 最大值
-- min 最小值
-- count 次数
-- group by 分组

------------------联表查询----------------------

create table tb_stu
(
    SCode int not null primary key,
    SName char(10) not null,
    SAddress nvarchar(50),
    SBirth datetime,
    SGrade varchar(2) default('S1'),
    SEmail nvarchar(50) check(SEmail like '%@%'),
    sex char(10)
);

insert into tb_stu(SCode, SName, SAddress, SBirth, sex)
select 1, '曾敏华', '株洲', '1990-01-01', '女' union
select 2, '杨勇', '株洲', '1990-01-01', '男' union
select 3, '陈世军', '长沙', '1990-01-01', '男' union
select 4, '张葎', '长沙', '1990-01-01', '男' union
select 5, '张玉桂', '长沙', '1990-01-01', '男' union
select 6, '刘年富', '长沙', '1990-01-01', '男' union
select 7, '刘欢', '湘潭', '1990-01-01', '男' union
select 8, '叶振溪', '湘潭', '1990-01-01', '男' union
select 9, '罗青', '湘潭', '1990-01-01', '男' union
select 10, '全乐', '衡阳', '1990-01-01', '男' union
select 11, '郑联涛', '衡阳', '1990-01-01', '男' union
select 12, '周玲芳', '衡阳', '1990-01-01', '女';

create table tb_score
(
    ScoreID int not null primary key identity(1, 1),
    StudentID int not null references tb_stu(SCode),
    Course varchar(10) not null,
    Score float
);

insert into tb_score
select 1,'JAVA',89 union
select 1,'HTML',80 union
select 2,'JAVA',92 union
select 2,'HTML',74 union
select 3,'JAVA',76 union
select 3,'HTML',95 union
select 4,'JAVA',NULL union
select 4,'HTML',NULL union
select 5,'JAVA',48 union
select 5,'HTML',79 union
select 6,'JAVA',NULL union
select 6,'HTML',NULL union
select 7,'JAVA',66 union
select 7,'HTML',88;

-- 学生表
select * from tb_stu;

-- 成绩表
select * from tb_score;

--计算1989年之后的出生的人的数目
select count(*) from tb_stu where year(SBirth)>1989;

--查看班上的地址分布
select SAddress from tb_stu group by SAddress;

--查看每个地址的人数
select SAddress,
       count(*)
from tb_stu group by SAddress;

--查看性别与人数配比
select sex,
       count(*)
from tb_stu group by sex;

--查看人数大于2的地址
-- 如果需要对分组之后的结果进行过滤
-- 请使用 having + 聚合函数
select SAddress,
       count(*)
from tb_stu group by SAddress having count(*)>2;

--查看每个地区的男女配比
select SAddress,sex,count(*) from tb_stu
group by SAddress,sex;

--查询班上出生人数大于2人的年份
select year(SBirth),count(*)
from tb_stu
group by year(SBirth) having count(*)>2;

--求参加考试学生的学号，姓名，分数

--左连接: 左表的数据都出来  left join
select * from tb_stu a left join tb_score b
on a.SCode=b.StudentID;

--右连接: 右表的数据都出来  right join
select * from tb_stu a right join tb_score b
on a.SCode=b.StudentID;

--全连接: 全部出来 full join
select * from tb_stu a full join tb_score b
on a.SCode=b.StudentID;

--内连接: 两张表都有的数据才会出来 inner join
select * from tb_stu a inner join tb_score b
on a.SCode=b.StudentID;

原文链接：https://blog.csdn.net/liujunxa/article/details/125245966

 

https://blog.csdn.net/LauvMiracle/article/details/123597096

Course数据库：
•Students表（学生表）：sid整型自增主键，sname字符串64位，gender字符串12位，dept_id整型并外键到dept表的id字段
•Dept表（系表）：id整型自增主键，dept_name字符串64位
•Course表（课程表）：id整型自增字段主键，course_name字符串64位，teacher_id整型外键到teacher表的id字段
•Teacher表（老师表）：id整型自增字段主键，name字符串64位，dept_id整型外键到dept表的id字段


1.清空表数据的SQL语句有哪几种？有什么区别？
delete from 表名 #删除速度慢，可以回滚
Truncae  table 表名 #删除速度快，不能回滚

2.在课堂练习创建的course数据库里，已知我们已经创建的students, dept, teacher ,course四个表，需要查看每个系里面所有的老师name和对应的学生的sname，结果按照dept_id升序排序，请写出SQL
Select a.name ,b.sname from teacher a inner join students b on a.dept_id=b.dept_id
Order by a.dept_id


3.用两个SQL分别求出每个系的学生个数和每个系里的老师个数？如果只用一个SQL语句实现怎么写？
Select dept_id,count(*) from students group by dept_id
Select dept_id,count(*) from teachers group by dept_id

Select a.dept,count(distinct b.sid),count(distinct a.id) from teacher a inner join students b on a.dept_id=b.dept_id


4.通过老师表和课程表求出每个老师的课程个数，并且需要保证如果老师没有课程的时候则要显示课程为0
    Select a.name,count(*),count(b.id) from teacher a left join course b on b.teacher_id=a.id
    Group by a.name

5.查看学生信息表里按照sid升序排序后的第7到第15行的学生数据
Select * from students order by sid limt 6,9  (从第几行开始取，取多少行)

6.Union 和union all的区别是什么？
union是最后的合并结果剔除重复行
Union all是显示所有的结果

select  column,......from table1
union [all]
select  column,...... from table2
.....

SELECT aid,title FROM article UNION SELECT bid,title FROM blog

 

7.举例常用的聚合函数 Max() min() count() sum() avg() 

8.使用select into outfile将teacher表里的数据导出，注意字段之间用;号隔开，字符串用”号隔开 

9.select * from Teacher into outfile '/路径/文件名' fields terminated by ';' 
enclosed by '"' lines terminated by '\r\n' 

10.请创建一个包含每个老师姓名和对应的课程个数，且只显示所教课程个数在2个以上的视图
view Create view v_course as select a.name,count(*) count1 from teacher a inner join 
Course b on a.id=b.teacher_id Group by a.name having count(*) >1

 

 

视图 view
view本身不存储查询结果，只是一个定义
1、视图是一个虚拟表（逻辑表）；

2、视图中的行和列的数据来自一到多张物理表，也可以来源自其他视图；

3、可以通过视图进行增删改查；

4、如果通过视图进行增删改，那么物理表的数据也会随之做出同样的增删改；反之亦然。

5、视图的使用比较少，起码相对于一般SQL来说，不在一个数量级上；

6、视图是一种”虚表”，所以不能与已经存在的表重名。


创建视图：
CREATE [OR REPLACE] [{FORCE|NOFORCE}] VIEW view_name
 
AS 
 
SELECT查询
 
[WITH READ ONLY CONSTRAINT]

1. OR REPLACE：如果视图已经存在，则替换旧视图。

2. FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用。

3. NOFORCE：如果基表不存在，无法创建视图，该项是默认选项。

4. WITH READ ONLY：默认可以通过视图对基表执行增删改操作，但是有很多在基表上的限制(比如：基表中某列不能为空，但是该列没有出现在视图中，则不能通过视图执行insert操作)，WITH READ ONLY说明视图是只读视图，不能通过该视图进行增删改操作。现实开发中，基本上不通过视图对表中的数据进行增删改操作。

例子：
CREATE
OR REPLACE
VIEW `VU_STUDENT` AS select
    `STUDENT`.`ID` AS `ID`,
    `STUDENT`.`NAME` AS `NAME`,
    `STUDENT`.`GENDER` AS `GENDER`,
    `STUDENT`.`AGE` AS `AGE`,
    `STUDENT`.`CLASS_ID` AS `CLASS_ID`,
    `STUDENT`.`ADDRESS` AS `ADDRESS`
from
    `STUDENT`
where
    `STUDENT`.`ID` > 2

1、视图的查询操作，可以直接在客户端使用，但是增删改却是不行的；

2、视图和真实的物理表还是有一些区别的。

修改视图：
ALTER VIEW VU_STUDENT AS SELECT * FROM STUDENT WHERE ID > 10; 

删除视图：
DROP VIEW IF EXISTS VU_STUDENT;