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;

2. 带有运算符 + - * /

select score*3
from test1_01;

3. 显示所有属性

select *
from test1_01;

4. 条件查询

select name
from test1_01
where score>=60 and sid>0;

5. 查询中where子句谓词 between

select name
from test1_01
where score between 90 and 100;

--not between
where score not between 90 and 100;

6. where中的元组

select sid
from test1_01
where (name,score)=("Trump",90);
--等价于
where name="Trump" and score=90;

7. 排列元组的显示次序

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 1，6；    -- 1 是起始偏移量，6是显示的行数

8. 多关系查询-笛卡儿积

先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

9. 多关系查询-自然连接
   自然连接只考虑那些所有相同的属性取值相同的元组

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属性进行分类。

10. 多关系查询-外连接

--左外连接
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