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

MySQL查询语句

程序员文章站 2024-01-18 15:18:58
DDL进入数据库mysql -u root -p1.数据库的创建create database student --创建student数据库drop database studentshow databases --显示所有的数据库use student --进入student数据库2.创建表create table test1_01 (sid int not nu......

DDL

进入数据库
mysql -u root -p

1.数据库的创建

create database student  --创建student数据库
drop database student

show databases --显示所有的数据库

use student  --进入student数据库

2.创建表

create table test1_01
  (sid int not null,
   name varchar(10),
   sorce numeric(12,2) default 0, --默认值
   primary key (sid,name)
   foreign key (name,sorce) references course);

如果主码只有一个的话,可以直接在相应的属性后面加上primary key

create table test1_01
  (sid int  not null primary key,
   name varchar(10),
   sorce numeric(12,2),
   foreign key (name,sorce) references course
   );

3.查看表/修改表/删除表

desc test1_01;   --查看表的信息
show create table test1_01 \G   --查看表的详细信息(包含engine,charset,collate)    
								-- /G 是为了按照字段竖着排列,便于显示
								
drop table test1_01;    --表的关系和数据都会丢失
delete from  test1_01;  --只会删除数据,表的关系依然存在

alter table test1_01 modify name varchar(20) first
alter table test1_01 modify name varchar(20) after sid

alter table test1_01 add gender varchar(20) first
alter table test1_01 add gender varchar(20) after name
alter table test1_01 drop gender
alter table test1_01 change name name1 varchar(10) first
alter table test1_01 change name name1 varchar(10) after gender
alter table test1_01 rename test1_02  --修改表明

###补充一下MySQL的基本类型

  • char(n)
  • varchar(n) :此处的n为最大长度
  • int
  • smallint
  • numeric(p,d) :numeric(3,1) 可以储存44.3
  • real \ double precision
  • float(n)
  • date :包括年(四位)月日
  • time :包括小时,分钟和秒 time§表示秒的小数点后的数字位数
  • timestamp :date和 time的组合 timestamp( p)表示秒的小数点后的数字位数

日期的赋值方式

date '2010-04-05'
time '09:30:01'
timestamp '2010-04-05 09:30:01'

注:

  1. 如果MySQL中的varchar没有指定大小,编译器会报错,但是在其他数据库中有些会默认为1。
  2. 可以指定time中的time with timezone以及timesamp中with timezone将时区的信息也加进去。
  3. extract(field from d),从date或time的值d中取出相应的单独信息。field可以为year,month,day,hour,minute,second,时区信息可以用timezone_hour,timezone_minute来提取。
  4. cast e as t可以将字符串(或字符串表达式)e转化为时间类型t,t可以是time,date,timestamp中的一种。
  5. SQL函数-获取当前时间
    (1) current_date
    (2) current_time 带区时
    (3) local_time 不带区时
    (4) current_timestamp 带区时
    (5) localtimestamp 不带区时

###补充一下完整性约束
单个关系上的约束(空值不等于其他任何值

  • not null 非空约束,直接加在属性的后边即可
  • unique
    create table test1_01
    (sid int not null,
    name varchar(10),
    sorce numeric(12,2),
    primary key (sid,name)
    unique (sid,name,sorce)
    foreign key (name,sorce) references course(name,sorce);
    
    unique 中的属性可以构成一个超码,保证对于所有实体(元组)在unique中属性不都相同
  • check(<predicate>)
    check中的谓词表示每个元组都必须满足此谓词,如
    create table test1_01
    (sid int not null,
    name varchar(10),
    sorce numeric(12,2),
    primary key (sid,name)
    unique (sid,name,sorce)
    check(sorce>=0)
    foreign key (name,sorce) references course);
    
    其中的check表示所有元组的成绩都需要大于等于0

DML

4.插入数据

insert into test1_01
  values(10001,"Trump",99.9);

每次插入的时候,都需要写insert into test1_01,不能只写values
以下形式也可进行多条插入

insert into test1_01
  values(10001,"Trump",99.9),
  		(10002,"Tom",70.3),
  		(10003,"Jhon",80)

在插入数据的时候,也可以指定属性插入,而不一定必须遵循原来的顺序。

insert into test1_01(name,sid,sorce)
  values("Trump",10001,99.9);

--或者
insert into test1_01(name,sorce,sid)
  values("Trump",99.9,10001);

插入数据的时候也可以通过子查询语句执行插入操作

insert into test1_01
  select sid,name,sorce
  from student
  where sorce>=90

bulk loader 可以从格式化的文件中输入大量数据,快速高效

5.更新语句

update test1_01 set sorce=100 
				where name="Trump";

--同时更新多个表中的内容
update test1 a,test2 b set a.sorce=b.sorce where a.name=b.name

6.删除数据

删除元组

没有where条件的删除是删除所有的数据,但是保留表的基本关系,仅仅是删除数据。

delete from test1_01

删除指定的元组,此处即为姓名属性为Trump的元组

delete from test1_01 where name="Trump"

where条件中也可以有子查询,此处为删除大于平均成绩的学生

delete from test1_01
where sorce>(select avg(sorce)
             from student);

同时删除多个表

delete a,b from test1 a,test2 b where a.name = b.name;  --其中a,b 为别名  delete后面必须加上别名a,b

删除关系

drop table test1_01

此时表中的关系结构也不存在了,数据元组当然也不存在了。

7.查询语句

select name 
from test1_01;
  1. 去除重复
select distinct name 
from test1_01;
  1. 带有运算符 + - * /
select score*3
from test1_01;
  1. 显示所有属性
select *
from test1_01;
  1. 条件查询
select name
from test1_01
where score>=60 and sid>0;
  1. 查询中where子句谓词 between
select name
from test1_01
where score between 90 and 100;

--not between
where score not between 90 and 100;
  1. where中的元组
select sid
from test1_01
where (name,score)=("Trump",90);
--等价于
where name="Trump" and score=90;
  1. 排列元组的显示次序
select name
from test1_01
where score>=60
order by name;
--order by 默认升序
--desc 降序   asc升序

select *
from test1_01
order by score desc,name asc;
--成绩降序,名字升序

--排序之后显示部分数据
select *
from test1_01
order by score desc,name asc;
limit 16-- 1 是起始偏移量,6是显示的行数
  1. 多关系查询-笛卡儿积

先student和course相乘获得笛卡儿积,然后根据where中的条件谓词,从笛卡儿积中选出满足条件的元组。

-- 笛卡尔积形式会产生很大的表,具体应用中一般不会用
select name,score,credit
from student,course   --用逗号即为笛卡儿积
where student.cid=course.cid  --两者课程号相同

--推荐使用内连接形式
--也可以改为 join on(在内连接中,其和where语句是一样的)
select name,score,credit
from student join course on student.cid=course.cid
  1. 多关系查询-自然连接
    自然连接只考虑那些所有相同的属性取值相同的元组
select name,score,credit
from student natural join course
--为了解决自然连接是考虑所有相同的属性,引入了join using 语句
select name,title
from (instructor natural join teaches) join course using (course_id)
--即使前面括号中的关系和course有多行属性相同,但是只根据using中的course_id属性进行分类。
  1. 多关系查询-外连接
--左外连接
select *
from student natural left outer join takes;

--右外连接
select *
from takes natural right outer join students;

-- 全外连接
select *
from takes natural full outer join students;

--也可以把natural 改为 on  (唯一不同是这个on的id会出现两次)
select *
from takes right outer join students on takes.id=students.id

内连接只会连接两个集合中都有的元素
左外连接会包含左边集合中含有的元素,如果右边集合没有则右边集合对应元素为null
右外连接会包含右边集合中含有的元素,如果左边集合没有则左边集合对应元素为null
全外连接会包含左右集合中所有的元素,如果某边集合没有则该边集合对应元素为null

附加运算

更名 as

--select 更名
select name as student_name
from test1_01
where score>=60;

--from 更名
select name
from test1_01 as T
where score>=60;

字符串运算

MySQL用双引号“”表示字符串

  1. like操作
  • 百分号%:匹配任意字符串
  • 下划线_:匹配任意一个字符
select score
from test1_01
where name like "%ump%"  --包含ump的字符串
--匹配只有三个字符的字符串
where name like "_ _ _"
--匹配至少含有三个字符的字符串
where name like "_ _ _%"

--如果like中需要用到% , \等转义字符,可以用escape

like "ab\%cd%" escape "\";  --即为所有以ab%cd开头的字符串
like "ab\\cd%" escape "\";  --即为所有以ab\cd开头的字符串

--not like操作
select score
from test1_01
where name not like "%ump%"  --不包含ump的字符串

集合运算

并 union

(select name
  from test1_01
  where score between 90 and 100)
  union
  (select name
    from test1_01
    where score between 60 and 70);

--union 会自动去除两个集合中的重复
--union all 会保存所有的重复

交 intersect

(select course_id
from section
where semester="Fall" and year=2009)
intersect
(select course_id
from section
where semester="Spring" and year=2010)

--intersect 会自动去除两个集合中的重复
--intersect all 会保存所有的重复

差 except/minus

(select course_id
from section
where semester="Fall" and year=2009)
except
(select course_id
from section
where semester="Spring" and year=2010)

--except 会自动去除两个集合中的重复
--except all 会保存所有的重复

空值

空值不等于任何值

name is null
name is not null

聚集函数

  • avg
  • sum
  • min
  • max
  • count
--统计全班的平均成绩
select avg(score)
from test1_01;

--统计名字不同的学生的个数
select count(distinct name)
from test1_01;

不能在count(*)中使用distinct

分组聚类 group by

--计算每个系的平均工资
select dept_name,avg(salary) as avg_salary
from instructor
group by dept_name

除聚集函数外,只有出现在group by中的才能出现在select中。(因为每个分组只能输出一个元组)

having 子句

having 对分组进行限定

select dept_name,avg(salary)
from instructor
where age>30
group by dept_name
having avg(salary)>9000;
--having  语句在group by之后才执行,所以可以使用聚集函数
--having 子句和select一样,其属性(除聚集函数外)都必须被聚类过

having 是在聚合之后再根据条件进行过滤
where 是在聚合之前就对记录进行过滤

聚集函数除了count(*) 外所有的聚集函数都会忽略掉空值。

嵌套子查询

子查询关键字: in , not int , = , != , exists , not exists
in测试元组中是否是集合中的成员 not in 测试元组中是否不是集合中的成员

select course_id
from section
where semester ="Spring" and year=2010
      and course_id in(select course_id
                       from section
                       where semester="Fall" and year=2009)

如果子查询的结果是唯一的,可以用=替代in

集合的比较

  • >some
  • <some
  • >=some
  • <=some
  • <>some 不等价于not in
  • =some 等价于 in
--至少要比一个要大

select name
from instructor
where salary >some(select salary
                  from instructor
                  where dept_name="Biology");
  • >all
  • <all
  • >=all
  • <=all
  • <>all 等价于 not in
  • =all 不等价于 in
--比所有的都大

select name
from instructor
where salary >all(select salary
                  from instructor
                  where dept_name="Biology");

空关系测试 exists

select course_id
from section as S
where semester="Fall" and year=2009 and
       exists(select *
              from section as T
              where semester="Spring" and year=2010 and
                    S.course_id=T.course_id); --相关子查询
--exists如果子查询非空,即返回true
--not exists如果子查询非空,返回false

重复元组测试

unique 如果没有重复元组,返回true
not unique 如果有重复元组,返回true

--最多开课一次的课程
select T.course_id
from course as T
where unique(select R.course_id
            from section as R
            where T.course_id = R.course_id
                   R.year=2009);
--最少开课两次的课程
select T.course_id
from course as T
where not unique(select R.course_id
            from section as R
            where T.course_id = R.course_id
                   R.year=2009);

from子句的子查询

--和之前的having子句效果一样

select dept_name,avg_salary
from (select dept_name,avg(salary) as avg_salary
      from instructor
      group by dept_name)
where avg_salary>90000;

lateral可以使得from子查询中使用from子句中的其他相关变量,相关子查询

with子句

with子句提供定义临时关系的方法,只有with子句中的查询有效

with dept_total(dept_name,value) as
     (select dept_name,sum(salary)
      from instructor
       group by dept_name
     )
     dept_total_avg(value) as
     (select avg(value)
      from dept_total)  --可以用上方创建的表

     select dept_name
     from dept_total,dept_total_avg
     where dept_total.value>=dept_total_avg.value;

数据库的更新

update test1_01
set score=score*1.2
where score<60;

数据更新的次序问题

update test1_01
set score=case
          when score<60 then score*1.2
          else score*1.1
          end--这样可以保证,对刚好小于60的分数乘两次的问题。

视图

create view department_total_salary(dept_name,total_salary) as
  select dept_name,sum(salary)
  from instructor
  group by dept_name

视图更新的条件:

  • from子句中只有一个数据库关系
  • select子句中只包含关系的属性名,不包含任何表达式、聚集或distinct声明
  • 任何没有出现在select子句中属性都可以为空值
  • 不含有group by , having子句

创建索引

create index studentID_index on student(ID);

DCL

授权

grant select on department to 某用户

grant update(name) on department to 某用户

grant<权限列表> on <关系名,视图名>  to <用户,角色>
revoke<权限列表> on <关系名,视图名>  from <用户,角色>

--将名为DATABASE数据库的select   insert 权限授给USER1用户,USER1用户的密码为123
grant select,insert on Database_name to 'USER1'@'loaclhost' identified by '123';

--收回insert权限
revoke insert on Database_name from 'USER1'@'loaclhost';

本文地址:https://blog.csdn.net/baidu_41560343/article/details/85951616