MySQL查询语句

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

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐