基础数据库(二)

一,基础查询select

● DQL （ Data Query Language ）数据查询语言查询是使用频率最高的一个操作，

可以从一个表中查询数据，也可以从多个表中查询数据。

● 基础查询

● 语法：

select 查询列表 from 表名 ;

● 特点：

查询列表可以是：表中的字段、常量、表达式、函数

查询的结果是一个虚拟的表格

● 查询结果处理：

特定列查询 :select column1,column2 from table

全部列查询 : select * from table

算数运算符 :+ - * /

排除重复行 : select distinct column1,column2 from table

查询函数： select 函数 ; / 例如 version()

● 函数 : 类似于 java 中的方法，将一组逻辑语句事先在数据库中定义好 , 可以直接调

用

分类：

单行函数：如 concat 、 length 、 ifnull 等

分组函数：做统计使用，又称为统计函数、聚合函数、组函数

● 单行函数

● 字符函数

length() ：获取参数值的字节个数

char_length() 获取参数值的字符个数

concat(str1,str2,.....) ：拼接字符串

upper()/lower() ：将字符串变成大写 / 小写

substring(str,pos,length) ：截取字符串 位置从 1 开始

instr(str, 指定字符 ) ：返回子串第一次出现的索引，如果找不到返回 0

trim(str) ：去掉字符串前后的空格或子串 ,trim( 指定子串 from 字符串 )

lpad(str,length, 填充字符 ) ：用指定的字符实现左填充将 str 填充为指定长度

rpad(str,length, 填充字符 ) ：用指定的字符实现右填充将 str 填充为指定长度

replace(str,old,new) ：替换，替换所有的子串

• 逻辑处理

case when 条件 then 结果1 else 结果2 end; 可以有多个when

ifnull(被检测值,默认值)函数检测是否为null，如果为null，则返回指定的值，否则返回

原本的值

if函数：if else的 效果 if(条件,结果1,结果2)

● 数学函数

round( 数值 ) ：四舍五入

ceil( 数值 ) ：向上取整，返回 >= 该参数的最小整数

floor( 数值 ) ：向下取整，返回 <= 该参数的最大整数

truncate( 数值 , 保留小数的位数 ) ：截断，小数点后截断到几位

mod( 被除数 , 除数 ) ：取余，被除数为正，则为正；被除数为负，则为负

rand() ：获取随机数，返回 0-1 之间的小数

日期函数

now() ：返回当前系统日期 + 时间

curdate() ：返回当前系统日期，不包含时间

curtime() ：返回当前时间，不包含日期

可以获取指定的部分，年、月、日、小时、分钟、秒

YEAR( 日期列 ),MONTH( 日期 列 ),DAY( 日期 列 ) ,HOUR( 日期 列 ) ,MINUTE( 日期 列 )

SECOND( 日期 列 )

str_to_date( 字符串格式日期 , 格式 ) ：将日期格式的字符转换成指定格式的日期

date_format( 日期列 , 格式 ) ：将日期转换成字符串

datediff(big,small) ：返回两个日期相差的天数

● 分组函数

功能：用作统计使用，又称为聚合函数或统计函数或组函数

分类： sum 求和、 avg 平均值、 max 最大值、 min 最小值、 count 计数

（非空）

1.sum ， avg 一般用于处理数值型 max ， min ， count 可以处理任何类型

2. 以上分组函数都忽略 null 值

3.count 函数的一般使用 count （ * ）用作统计行数

4. 和分组函数一同查询的字段要求是 group by 后的字段

二,实例

CREATE TABLE SC(
     sno VARCHAR(10) NOT NULL,
     con VARCHAR(10) NOT NULL,
     grade SMALLINT
)
CREATE INDEX idx_student_sno ON student(sno);
-- 外键约束
ALTER TABLE SC ADD CONSTRAINT fk_student_sno FOREIGN KEY (sno) REFERENCES student(sno);
-- 外键约束
ALTER TABLE SC ADD CONSTRAINT fk_course_con FOREIGN KEY (con) REFERENCES course(con);


SELECT s.sno, AVG(c.grade) AS average_grade
FROM student s
-- 左外关联`course`
LEFT JOIN SC c ON s.sno = c.sno
GROUP BY s.sno;

UPDATE course SET ccredit=4 WHERE cname='数据库'

UPDATE SC sc
SET sc.grade = sc.grade + 5
WHERE sc.sno = (SELECT s.sno FROM student s WHERE s.sname = '雪豹')
AND sc.con = (SELECT c.con FROM course c WHERE c.cname = '数据库');

UPDATE SC
SET grade = NULL
WHERE sno IN (SELECT sno FROM student WHERE sdept = '人文');

DELETE 
FROM sc
WHERE grade<40

DELETE
FROM sc
WHERE sc.con=1

DELETE
FROM sc
WHERE sc.sno=5

DELETE 
FROM student
WHERE student.sno=2100001

drop table sc

create table sc(-- 创建表
number int primary key auto_increment,-- 数量
name varchar(5) not null ,-- 姓名
tel int not null unique check(tel>10),-- 电话
sex char(1) check (sex in ('男','女'))-- 性别
)
insert into sc (name,tel,sex)values('张三',11111,'男')-- 填入值
insert into sc (name,tel,sex)values('李四',2222,'女')
delete from sc where name='李四'-- 删除表中某内容
update  sc set name='刘能', tel=10086, sex='女' where number=1   -- 修改表的数据
rename table sc to sscc   -- 修改表名
create table bigbig like sscc   -- 复制表结构给另一个表

select * from sscc
insert into sscc (name,tel,sex)values('  张三',4444,'男')
insert into sscc (name,tel,sex)values('张四',3333,'男')
insert into sscc (name,tel,sex)values('冲击波',2222,'男')
update sscc set name='张三' where number=4
select * from sscc
select distinct name from sscc -- distinct作用是去重
select concat (name,':',tel,':',sex)from sscc  -- concat 的作用是连接查询的结果
insert into sscc (name,tel,sex)values('Kobe',911,'男') -- 变为大写
select upper(name),lower(name) from sscc where number=6 -- 变为小写
select substring(name,2,3) from sscc  -- 截取部分开始查询
select instr(name,'o') from sscc -- 查询指定字符并返回位置,查询不到返回0
select trim(name),name from sscc -- 默认情况删除查询内容前的空格
select trim('a' from name ),name from sscc -- 加上 XX from 列名 则删除对应的XXneir
SELECT lpad('a', 6 ,NAME ),NAME FROM sscc  -- 左填充至指定长度
SELECT replace(NAME,'o','O' ),NAME FROM sscc  -- 替换内容
-- case when的用法
select
  name,
  case
    when sex = '女'
    then '女性'
    else '男性'
  end as sexx
from
  sscc
  
  CREATE TABLE Course(
     con VARCHAR(10) PRIMARY KEY NOT NULL,
     cname VARCHAR(16) NOT NULL,
     ccredit SMALLINT NOT NULL,
     cpno VARCHAR(10)
)

  create table arknights(
  name varchar(6),
  pnumber int primary key,
  sumD int,
  parents varchar(10),
  sex char(1) check(sex in ('男','女'))
  
  )
  insert into arknights (name,pnumber,sumD,sex)values ('布鲁斯',10,10,'男')
  select name,parents, ifnull(parents,'未查询到相关信息')as '浮木' from arknights
  update arknights set name = '布鲁斯.韦恩' where name='布鲁斯'
  
  select now(),curtime(),curdate() from sscc
  
  -- union 将多个查询结果合在一起
  select * from sscc where sex='女'
  union
  select * from sscc where sex='男'
  
  -- asc 升序 desc 降序
  select * from sscc where number>0 order by name asc
  SELECT * FROM sscc WHERE number>0 ORDER BY NAME desc
  
  insert into sscc (name,tel,sex) values('阿珍',10010,'女')
  INSERT INTO sscc (NAME,tel,sex) VALUES('李梅',10011,'女')
  
  select count(*),sex from sscc group by sex
  select avg(tel) ,sex from sscc group by sex
  -- alter 修改属性结构