第二章数据库操作

DDL语言 数据库操作

创建数据库

1	直接创建	
create database 数据库名;		
2	如果不存在创建 
create database if not exists 数据库名;
3	创建数据库并设置字符集	
create database 数据库名 character set 字符集;

查看服务器中所有数据库

show databases;

删除数据库

drop database 数据库名;

选择数据库

use 数据库名;

查看正在使用的数据库

select database();

数据表操作

1	创建一个简单的数据表
create table 学生(
	学号	varchar(20),
	姓名	varchar(20),
	性别	varchar(20),
	年龄	int,
	学校	varchar(20)
) 

2	如果不存在则创建数据表
create table if not exists 学生(
	学号	varchar(20),
	姓名	varchar(20),
	性别	varchar(20),
	年龄	int,
	学校	varchar(20)
) 
3 	在2的基础上加主键约束
create table if not exists 学生(
	学号	varchar(20)	primary key,
	姓名	varchar(20),
	性别	varchar(20),
	年龄	int,
	学校	varchar(20)
) 
create table 课程(
	课程号	varchar(20)	primary key AUTO_INCREMENT,
	课程名	varchar(20)
) 


4创建选课表设置外键约束
create table 选课(
	学号		varchar(20),
	课程号		varchar(20),
	成绩		double，
	primary key(学号,课程号),
	foreign key(学号) references 学生(学号),
	foreign key(课程号) references 课程(课程号)

)

查看数据库中所有表

show tables;

删除表

drop table 表名;

修改表结构

1	增加属性
alter table 表名 add 列名 类型(长度) [约束];	
2 	更改属性
alter table 表名 change 旧列名 新列名 类型(长度) 约束; 
3	删除属性
alter table 表名 drop 列名;	
4	修改属性名名
rename table 表名 to 新表名;	
6	删除主键
ALTER TABLE 表名 DROP PRIMARY KEY;
扩展：默认AUTO_INCREMENT 的开始值是 1，如果希望修改起始值，请使用下列 SQL 语法：	
ALTER TABLE Persons AUTO_INCREMENT=100

DML语言 数据操作语言

插入数据

INSERT INTO 选课(课程号,课程名) VALUES('a11','语文'); 
INSERT INTO 选课 VALUES('a22','数学'); 
INSERT INTO 选课 VALUES
('a33','英语'),
('a44','JAVA'),
('a55','Python'),
('a66','C++'),
('a77','数据库'); 

INSERT INTO 学生 VALUES
('01','张三','男',18,'北京大学'),
('02','李四','女',17,'钱江学院'),
('03','王麻子','男',18,'中国人大'),
('04','哈皮','女',19,'杭州师范'),
('04','哈皮2','女',19,null),
('05','冷森森','男',22,'加里东');


INSERT INTO 选课 VALUES
('01','a11',88),
('02','a33',99),
('03','a55',88),
('04','a77',66),
('05','a66',77);

插入子查询

create table 学生总人数表
(
	男生 int,
	女生 int
);
insert into 学生总人数表(男生,女生)
select 性别,count(性别) 人数 from 学生 group by 性别;

修改表数据

update 表名 set 字段名=值,字段名=值,... where 条件;

在这里插入代码片删除表数据

delete from 表名 [where 条件];
或者
truncate table 表名;
delete 一条一条删除，不清空auto_increment记录数。
truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始

简单查询查询

1.查询所有的学生信息.

select *  from 学生;

2.查询学生名和年龄.

select 姓名,年龄 from 学生;

3.别名查询.使用的关键字是as（as可以省略的）.

3.1表别名:

select * from 学生 as p;

3.2列别名：

select 姓名 as pn from 学生; 

4.去掉重复值.

select distinct 年龄 from 学生;

查询年龄在20到30之间所有学生

SELECT * FROM 学生 WHERE 年龄 >= 20 AND 年龄 <=30;
SELECT * FROM 学生 WHERE 年龄 BETWEEN 20 AND 30;

查询年龄是22或23的所有商品

SELECT * FROM 学生 WHERE 年龄 = 22 OR 年龄 = 23;
SELECT * FROM 学生 WHERE 年龄 IN (22,23);

查询姓名含有’森’字的所有学生

SELECT * FROM product WHERE 姓名 LIKE '%森%';

查询姓王的学生

SELECT * FROM product WHERE 姓名 LIKE '王%';

查询第二个字为’麻’的所有学生

SELECT * FROM product WHERE 姓名 LIKE '_麻%';

查询没入学的学生

SELECT * FROM product WHERE 学校 IS NULL;

查询入学了的学生信息

SELECT * FROM product WHERE 学校 IS NOT NULL;

排序查询

 升序
SELECT * FROM 表名 ORDER BY 排序字段;/*默认升序*/
SELECT * FROM 表名 ORDER BY 排序字段 ASC;
 降序
SELECT * FROM 表名 ORDER BY 排序字段 DESC;
排序嵌套
在价格排序(降序)的基础上，以分类排序(降序)
SELECT * FROM product ORDER BY price DESC,category_id DESC;
 显示商品的价格(去重复)，并排序(降序)
SELECT DISTINCT price FROM product ORDER BY price DESC;

聚合函数

#1 查询商品的总条数
SELECT COUNT(*) FROM product;
#2 查询价格大于200商品的总条数
SELECT COUNT(*) FROM product WHERE price > 200;

#3 查询分类为'c001'的所有商品的总和
SELECT SUM(price) FROM product WHERE category_id = 'c001';
#4 查询分类为'c002'所有商品的平均价格
SELECT AVG(price) FROM product WHERE category_id = 'c002';
#5 查询商品的最大价格和最小价格
SELECT MAX(price),MIN(price) FROM product;

分组查询

分组查询是指使用group by字句对查询信息进行分组。
格式：
SELECT 字段1,字段2… FROM 表名 GROUP BY分组字段 HAVING 分组条件;
分组操作中的having子语句，是用于在分组后对数据进行过滤的，作用类似于where条件。
having与where的区别:
1).having是在分组后对数据进行过滤.,where是在分组前对数据进行过滤
2).having后面可以使用分组函数(统计函数),where后面不可以使用分组函数。

实例:
#1 统计各个分类商品的个数
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id ;
#2 统计各个分类商品的个数,且只显示个数大于1的信息
SELECT category_id ,COUNT(*) FROM product GROUP BY category_id HAVING COUNT(*) > 1;
#查询最多人口的省
select province from  t_customer_area group by  province
having  count(customer_id) >=all
        (select  count(customer_id) from  t_customer_area group by  province)

标题分页查询

SELECT 字段1，字段2... FROM 表明 LIMIT M,N
M: 整数，表示从第几条索引开始，计算方式 （当前页-1）*每页显示条数
N: 整数，表示查询多少条数据
SELECT 字段1，字段2... FROM 表明 LIMIT 0,5
SELECT 字段1，字段2... FROM 表明 LIMIT 5,5

多表操作

连接查询

内连接

#两种本质一样只是写法不一样
#隐式内连接
select * from A,B where 条件;
#显式内连接
select * from A inner join B  on 条件;

外连接

#一张表为全部 另一张表有则查无则补 null
#左外连接：left outer join
select * from A left outer join B on 条件;
#右外连接：right outer join
select * from A right outer join B on 条件;

嵌套查询

SELECT * FROM products p 
 WHERE p.category_id = 
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='化妆品'
 );

in关键字

SELECT * FROM products p 
 WHERE p.category_id in
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='%品%'
 );
 SELECT * FROM products p 
 WHERE p.category_id not in
 ( 
  SELECT c.cid FROM category c 
   WHERE c.cname='%品%'
 );

any关键字

#查询非计算机学科中比计算机学科任何一个人年龄小的学生 = <max
select Sname,Sage
	from Student
where Sage < any(
	select Sage from Student where Sdept = 'CS'
and Sdept != 'CS';

all关键字

#查询非计算机学科中比计算机学科所有人年龄都小的学生	= <min
select Sname,Sage
	from Student
where Sage < all(
	select Sage from Student where Sdept = 'CS'
	)
and Sdept != 'CS';

exists关键字

#选修1，2号课程名单 exists 存在 not exists  不存在
select Sname
	from Student
where exists(
	select * from SC where sno = Student.Sno and Cno in('1','2');

集合查询

##并 查询计算机系和不大于19的学生
select * from Student where Sdept = 'CS'
union
select * from Student where Sage <= 19;

##交 查询计算机系并且不大于19的学生
select * from Student where Sdept = 'CS'
union
select * from Student where Sage <= 19;
##差 查询计算机系并且大于19的学生
select * from Student where Sdept = 'CS'
union
select * from Student where Sage <= 19;

用户权限

授权

#把查询Student授予u1
grant select
on table Student
to u1;
#把Student 和Course 表的所有权限受于 u2 u3用户
grant all privileges
on table Student ,Course 
to u2,u3;

#把查询Student授予所有用户
grant select
on table Student
to public;

#把修改Student学号的权限授予u4
grant update(Sno),select
on table Student
to u4;

收回

#收回u4修改Student学号的权限
revoke update(Sno)
on table Student
from u4;
#收回所有用户的查询权限
revoke select
on table Student
from public;
#收回u4修改Student学号的权限 级联收回
revoke update(Sno)
on table Student
from u4 cascade;

本文地址：https://blog.csdn.net/weixin_43838444/article/details/107237050