欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

第二章数据库操作

程序员文章站 2022-11-30 17:27:02
DDL语言 数据库操作创建数据库1直接创建create database 数据库名;2如果不存在创建 create database if not exists 数据库名;3创建数据库并设置字符集create database 数据库名 character set 字符集;查看服务器中所有数据库show databases;删除数据库drop database 数据库名;选择数据库use 数据库名;查看正在使用的数据库select database();...

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)
) 
32的基础上加主键约束
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),
	成绩		doubleprimary 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