数据库笔记

2020-04-30  吴水荣 

数据库笔记果然被误删除了,当年存在电脑上的文档只剩个0Kb的空文档,简直想让我吐血。。。。。

对于数据库,大学时学的是Oracle,我的老天,那位教授大人说的话,我貌似听懂了,但一到实操,完全白瞎,当年在实验室,我连搭环境都搭不起来,最后那门课是61分过的,貌似。应该是教授看我每次课都坐第一排,还次次认真做笔记,给我的平时印象分,不然我是要重考的----------总而言之,我数据库学的不好。

出来工作之后,前面几家公司,开发的人根本没给我机会接触过数据库,因此就一直不会,直到几年前来到现在这家公司,测试大牛太多,人人都是高手的感觉,她们让我无地自容,并且数据库测试也是必做的工作,因此我被逼学会了------虽然早几年前就因为要面试而背会了很多SQL语句,但那完全是用来装样子的

使用navicat之后,发现mySQl总算比Oracle好学多了。

------回忆起来当年给别的微信账号传过一份数据库的笔记,虽然不全,但也先在这里存着:

1、概念
  数据库的作用,把数据以表的形式存储起来,方便查询

2、RDBMS 关系型数据库系统
   核心是以二维表存储数据
   行:一条记录,一个事物的信息
   列:字段,一个事物的某一个属性
   表:一个表中的所有的行是一类事物
   库:一个数据由很多表组成

3、SQL  结构化查询语言,用于关系型数据库
     不区分大小写  select  SELECT sELECt
      
4、Mysql  开源免费数据库、支持多平台

5、约束

6、备份和恢复

一、数据库增删查改

7、查询语句 select
   注释:ctrl+/          ctrl+shift+/取消
   select * from stu

8、创建表 create
   create table 表名(
       字段名 类型 约束,
        .....

CREATE TABLE students(
  id int unsigned primary key auto_increment,            //主键,无符号、自动递增
  NAME VARCHAR(10),
  age INT unsigned,
  heght decimal(5,2)             //小数      总长度为5,小数点后是2位,前面是3位
)

9、删除表 drop
 drop table students2
 drop table if exists students          //一般创建一个表之前先删除掉这个表---------那如果是之前创建的表忘记了,一下子删除了怎么办

unsigned是无符号的,int 无须写明长度

10、插入数据  insert into
 insert into 表名 values(....)
   insert into students(0,'无名',20,175.20)            //id那里写0是占位符,也可以写defualt和null;因为之前是设计自增长的

方式一:给所有字段传值
insert into students2 values(

0,'你好',20,132323,189.11
)

insert into students2 values(0,'人',11,4232,140),(0,'从',44,23232,189.98)

方法一:给特定字段传值
insert into students2(name) values('吴xx')
insert into students2(name,age) values('好人',20)

insert into students2(name,age) values('好人2',20),('坏人',33)

11、修改 update
update 表名 set 列1=值1,列2=值2... where 条件

 update students2 set age=18 where id=5
 update students2 set name='狄仁杰',age=20 where id=5

12、删除 delete
 delete from 表名 where 条件
  delete from students2 where id=1

13、逻辑删除    一般重要数据不能直接删除,只逻辑删除
1)先设计表,给表添加字段isdelete    1代表删除  0代表没有删除
   alter table studnets2 add isdelete int
2)把所有的数据isdelete都改为0
3)要删除某一条数据时,更新它的值为1
4) 当要查询数据时,只查询isdelete=0的数据

  update students2 set isdelete=0
  update students set isdelete=1 where id=1
  select * from students2 where isdelete=0

14、查询

 select * from 表名
 select * from students          查询出students表的所有列
 select name,sex from students;  查询名和性别列
 select name as 姓名,sex as 性别 from students;   给列起别名显示出来
 
 查询出来的结果按中文排序
 select * from students order by convert(name using gbk)

去重:查询学生的性别有哪几种


select distinct sex from students;
select distinct sex,class from students;  多项重复才去除
select distinct * from students      因为id都不一致,因此不会有重复的

14.1 比较运算

 select * from students where name='wusr'
 select card from students where studentNo='002'
  select * from students where class!='1班'
  select name,sex,age from students where age>22

14.2 逻辑运算符
and  or not

select * from students where age<20 and sex='女'
select * from students where age<20 or class='1班'
select * from students where not hometown='北京'

14.3模糊查询        like  %表示任意多个任意字符  _表示一个任意字符
  select * from students  where name like '孙%'  孙后面有任意多个字
  select * from students where name like'孙_'    孙后面只有一个字    两个下划线表示2个字
  select * from students where name like'%白%'   不管白字在哪个位置都查询出来
  select * from students where name like '__'  两个字的名字
  select * from students where name like 'w%' and age>20   名字以w开头并且年龄大于20
  select * from students where card like '%9'  卡号以9结尾

14.4 查找范围   in表示在一个非连续的范围内   between   and 数字范围
  select * from students where hometown in('北京','上海','湛江')
  select * from students where age between 18 and 21;   大于等于18并且小于等于21
  select * from students where age in(18,19,22) and sex='女';  年龄为18或19或22的女生
  select * from students where not age between 20 and 21;   年龄在20到21岁之外   把not放在整个条件前面

14.5 空判断  null是空  ''是空字符串                 判断空 is null
查询没有填写身份证的学生
select * from students where card is null;   假设其中一行清空card的内容,另一行设置值为null,则此时查出来的是设置值为null的那一行
select * from students where not card is null;   这里查出来的是其他有值的,以及被清空card内容的那一行        非空
select * from students where card=' '   查出来的是那行清空card内容的

插入空字符串
insert into students(studentno,name) values(222,' ');
插入数据时,给某个字段没填东西
insert into students(studentno) values(222)  其他字段是Null
insert into students(name,age) values('z',null)  名字为z,年龄为Null,其他没写的字段也是Null


15 排序   order by
 将行数据按照列1进行排序,如果某些行列1的值相等,则按列2排序,以此类推
 默认按照列值从小到大排序
 asc从小到大排序,即升序
 desc从大到小排序,即降序

 select * from 表名 order by 列1 asc|desc,列2 asc|desc,...
 查询所有学生信息,按年龄从小到大排序
 select * from students order by age asc;      asc可以省略
 查询所有学生信息,按年龄从大到小排序,年龄相同时,按学号从小到大排序
 select * from students order by age desc,studentNo asc;

16 聚合函数
为了快速得到统计数据,经学会用到以下5个聚合函数
count(*)表示计算总行数,括号中写*与列名,结果是相同的
聚合函数不能在where中使用
16.1 查询学生总数  统计出一个总数
select count(*) from students;
如果有某一列有一行没有填数据,Null的,按列名统计则会少1


16.2 查询女生的最大年龄                  max(列)       min(列)
select max(age) from students where sex='女'

16.3 查询1班的最小年龄
select min(age) from students where class='1班'

16.4 求和 sum(列)
查询 北京的学生的年龄总和
select sum(age) from students where hometown='北京'

16.5查询女生的年龄平均值 avg(列名)
select avg(age) from students where sex='女'

查询1班年龄小于18岁的同学有几个
select count(*) from students where class='1班' and age<18
查询所有学生的最大年龄、最小年龄、平均年龄
select max(age),min(age),avg(age) from students;
select max(age) as 最大年龄,min(age) as 最小年龄,avg(age) as 平均年龄 from students;

17 分组 group by
按照字段分组,表示此字段相同的数据会被放到一个组中
分组后,分组的依据会显示在结果集中,其他列不会显示在结果集中
可以对分组后的数据进行统计,做聚合运算
select 列1,列2,聚合... from 表名 group by 列1,列2...

查询各种性别、年龄的人数
select sex, count(*) from students group by sex;
select age, count(*) from students group by age;

查询各个班级学生的平均年龄、最大和最小年龄
select class, max(age),min(age),avg(age) from students group by class
select class,sex, count(*) from students group by class,sex

统计1班男女各有多少人
select sex,count(*) from students where class-'1班' group by sex;

统计每个班级中每种性别的学生人数,并按照班级升序排序
select sex,class,count(*) from students group by class,sex order by class;

18 分组之后的过滤  having关键字只能用在group by后面
查询男生总人数
select sex,count(*) from students group by sex having sex='男'
查询1班以外的其他班级学生的平均年龄、最大、最小年龄
select class,max(age),min(age),avg(age) from students where class!='1班' group by class; 先过滤再分组
select class,max(age),min(age),avg(age) from students group by class having class!='1班';  先分组再过滤

对比where与having
where是对from后面指定的表进行数据筛选,属于对原始数据的筛选
having是对group by 的结果进行筛选

19  分页      获取部分行
limit 7相当于limit 0,7

查询前3条数据
select * from students limit 0,3;    0是第一条,3是一共3条,显示第1到3行
select * from students order by age limit 0,3;
它们显示的内容不一样

分页:
已知:每页显示m条数据,求:显示第n页的数据
select * from students limit (n-1)*m,m
求总页数:
查询总条数P1,
使用P1除以m得到P2,
如果整除则p2为总页数,如果不整除则p2+1为总页数

每页显示5条数据,显示每一页的数据
首先求总页数
select count(*) from students              总条数,假设是22
22/5=4余4   应该显示5页
第一页:
select * from students limit(1-1)*5,5          //limit 0,5  表示第1页显示的5行

第二页
select * from students limit(2-1)*5,5          //limit 1*5,5  表示第2页显示的5行,从第6行开始,显示5行

第三页
select * from students limit(3-1)*5,5          //limit 2*5,5  表示第3页显示的5行,从第11行开始,显示5行

第四页
select * from students limit(4-1)*5,5          //limit 3*5,5  表示第4页显示的5行

第五页
select * from students limit(5-1)*5,5          //limit 4*5,5  表示第5页显示的5行,但是最后一页不足5行,只显示最后2行

查询年龄最小的学生的全部信息:
select * from students order by age limit 1;


20、连接查询      多表查询
当查询结果的列来源于多张表时,需要将多张表连接成一个大的数据集,再选择合适的列返回

查询学生信息及学生的成绩
等值连接
方法一:select * from 表1,表2 where 表1.列=表2.列                                    //会产生临时表,存放在内存里
select * from students as stu,scores as sc where stu.stuNo=sc.studentNo;

方法二:内连接
select * from 表1 inner join 表2 on 表1.列=表2.列             //不会产生临时表,效率更高,建议使用这个
select * from students as stu inner join scores as sc on stu.stuNo=sc.studentNo;


查询课程信息及课程的成绩
select * from courses inner join scores on courses.courseNo=scores.courseNo;

三表连接
查询学生信息,以及学生课程以及对应的成绩
select
*
from students,courses,scores
where
students.stuNo=scores.studentNo
and
courses.courseNo=scores.courseNo


select
*
from students inner join scores
on
students.stuNo=scores.studentNo
inner join courses
on
courses.courseNo=scores.courseNo;

查询吴xx的成绩,要求显示姓名、课程名、成绩
select
students.name,courses.name as 课程名,scores.score
from students inner join scores
on
students.stuNo=scores.studentNo
inner join courses
on
courses.courseNo=scores.courseNo
where students.name='吴xx'

21、连接查询后过滤
例1:查询吴xx的数据库成绩,要求显示姓名、课程名、成绩

SELECT
    students. NAME,
    courses. NAME,
    scores.score
FROM
    students
INNER JOIN scores ON students.stuNo = scores.studentNo
INNER JOIN courses ON courses.courseNo = scores.courseNo
WHERE
    students. NAME = '吴xx'
AND courses. NAME = '数据库'

例2:查询所有学生的数据库成绩,要求显示姓名、课程名、成绩
SELECT
    students. NAME,
    courses. NAME,
    scores.score
FROM
    students
INNER JOIN scores ON students.stuNo = scores.studentNo
INNER JOIN courses ON courses.courseNo = scores.courseNo
WHERE courses. NAME = '数据库'

例3:查询男生中最高成绩、要求显示姓名、课程名、成绩
SELECT
    students. NAME,
    courses. NAME,
    scores.score
FROM
    students
INNER JOIN scores ON students.stuNo = scores.studentNo
INNER JOIN courses ON courses.courseNo = scores.courseNo
WHERE
students.sex = '男'
ORDER BY
    score DESC
LIMIT 1;

22、左连接  left
左连接,join前面的表称为左表,join后面的表称为右表
首先显示左边表所有的记录,然后匹配右边,如果匹配成功则拼接显示为一行,继续匹配成功则继续拼接显示一行,如果匹配不成功,则左边显示信息,右边以Null填充

例1:显示所有学生的成绩,包括没有成绩的学生也显示出来
SELECT
    *
FROM
    students
LEFT JOIN scores ON students.stuNo = scores.studentNo

例2:显示所有学生的成绩,包括没有成绩的学生也显示出来,需要显示课程名
SELECT
*
FROM
    students
LEFT JOIN scores ON students.stuNo = scores.studentNo
LEFT JOIN courses ON courses.courseNo = scores.courseNo;

23、右连接 right
join前面的表称为左表,join后面的表称为右表,右连接是把右边的表全部显示出来

例1:查询所有课程的成绩,包含没有成绩的课程
select * from scores right join courses
on scores.courseNo=courses.courseNo;

例2:查询所有课程的成绩,包含没有成绩的课程,包括学生信息
SELECT
    *
FROM
    scores
RIGHT JOIN courses ON scores.courseNo = courses.courseNo
LEFT JOIN students ON students.stuNo = scores.studentNo

连接查询总结:
等值连接查询:查询的结果 为2表匹配到的数据
左连接:结果为2表匹配到的数据加左表特有的数据,对于右表中不存在的数据使用Null填充
右连接:结果为2表匹配到的数据加右表特有的数据,对于左表中不存在的数据使用Null填充

24、自关联
同一个表(数据源)可以查多次,但是要起别名

例1:查询河南省的所有市
SELECT
    *
FROM
    areas AS sheng,
    areas AS shi
WHERE
    sheng.aid = shi.pid
AND sheng.atitle = '河南省'

例2:查询郑州市的所有区
SELECT
    *
FROM
    areas AS sheng,
    areas AS shi
WHERE
    sheng.aid = shi.pid
AND sheng.atitle='郑州市'

例3:查询河南省的所有区
SELECT
    *
FROM
    areas AS sheng,
    areas AS shi,
    areas AS qu
WHERE
    sheng.aid = shi.pid
AND sheng.atitle='河南省'
AND shi.aid = qu.pid;


25、子查询
在一个select语句中,嵌入了另一个select语句,那么被嵌入的select语句就是子查询语句
主查询:主要查询的对象,第一条select语句
主查询和子查询的关系:1)子查询是嵌入到主查询中
2)子查询是辅助主查询的,要么充当条件,要么充当数据
3)子查询是可以独立存在的语句,是一条完整的select语句

子查询分类:
1)标量子查询:子查询返回的结果是一个数据(一行一列)
2)列子查询:返回的结果是一列(一列多行)
3)行子查询:返回的结果是一行(一行多列)
4)表级子查询:返回的结果是多行多列

1) 例1:查询大于平均年龄的学生
select * from students where age>(select avg(age) from students)
后面的select语句要用小括号包起来

例2:查询最小年龄的学生
select * from students where age=(select min(age) from students)

例3:查询吴xx的成绩,要求只显示成绩
select score from scores where studentNo=(select stuNo from students where name='吴xx')

例4:查询吴xx的数据库成绩,要求只显示成绩
SELECT
    score
FROM
    scores
WHERE
    studentNo = (
        SELECT
            stuNo
        FROM
            students
        WHERE
            NAME = '吴xx'
    )
AND courseNo = (
    SELECT
        courseNo
    FROM
        courses
    WHERE
        NAME = '数据库'
)

2)列子查询   in
例1:查询18岁的学生的成绩,要求显示成绩
select score from scores where studentNo in(select stuNo from students where age=18)

3)行子查询(少用)  子查询返回的信息只能是一行多列
例1:查询男生中年龄最大的学生信息
SELECT
    *
FROM
    students
WHERE
    (sex, age) = (
        SELECT
            sex,
            age
        FROM
            students
        WHERE
            sex = '男'
        ORDER BY
            age DESC
        LIMIT 1
    )

4)表级子查询   结果作为数据来使用
例子:查询数据库和Linux的课程成绩
SELECT
    *
FROM
    scores
INNER JOIN (
    SELECT
        *
    FROM
        courses
    WHERE
        NAME IN ('数据库', 'Linux')            //查询 出来的结果当作数据源使用
) AS c ON scores.courseNo = c.courseNo;      //子查询的表需要起一个别名

26、查询关键字
any 等于 in  等于some

27、查询练习
原来准备的数据:
-- create table goods(
-- id int unsigned PRIMARY key auto_increment,
-- name varchar(150),
-- cate varchar(40),
-- brand_name varchar(40),
-- price decimal(10,3) default 0,
-- is_show bit default 1,
-- is_saleoff bit default 0
-- );
--
-- insert into goods values(0,'r510c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'y400n 14.0英寸笔记本','笔记本','联想','4999',default,default);
-- insert into goods values(0,'g150th 15.6英寸游戏本','游戏本','雷神','8499',default,default);
-- insert into goods values(0,'x240 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'r510c 超级本','超级本','联想','3399',default,default);
-- insert into goods values(0,'r510c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'r550c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'r30c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'y510c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'u510c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'o510c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'ru10c 一体机','一体内机','华硕','3399',default,default);
-- insert into goods values(0,'r510c 15.6英寸笔记本','笔记本','华硕','3399',default,default);
-- insert into goods values(0,'r515c 15.6英寸笔记本','笔记本','华硕','3399',default,default);

PS:后来在例子中时,实际数据库里的数值有所调整
例1:查询 所有价格大于平均价格(保留2位小数)的商品,并且按价格降序排序
select * from goods where  price>(select round(avg(price),2) from goods) order by price desc;

例2:查询价格大于或等于“超级本”价格的商品,并且按价格降序排序(超级本有2个以上的商品,不能直接用>=,需要用any或some,如果是要大于所有超级本的价格,则需要用all)
select * from goods where  price>=any(select price from goods where cate='超级本') order by price desc;

ps:当之前已创建了一个goods表,并且cate列含有很多种重复的类型,会造成数据冗余,这时将该表的该列另起一个表(goods_cate),
专门存放这个类型,不重复,goods表只用goods_cate表的id即可;

create table goods_cate(
id int unsigned primary key auto_increment,
cate_name varchar(10)
);

//把查询的结果直接插入到另一个表中:
insert into goods_cate(cate_name) select distinct cate from goods;

一步到位创建表和插入数据,还是把goods表里的brand_name(品牌)拿出来创建一个表
create table ... select ...
create table goods_back select * from goods            //相当于复制了一个goods表

create table goods_brands(
id int unsigned primary key auto_increment,
brand_name varchar(10)
)
select distinct brand_name from goods;              //查询出来的列必须对应表中的字段名,如果 表中找不到同名字段,会新建一个字段

此时将原goods表里的类型名称改为类型表里的id
先查询goods表里的cate名称对应goods_cate表里的id是什么,此时需要用连接查询
select * from goods inner join goods_cate where goods.cate=goods_cate.cate_name

然后需要将临时表里的id列更新到原来的cate列里
update goods inner join goods_cate on goods.cate=goods_cate.cate_name
set goods.cate=goods_cate.id


二、数据库设计
1、E-R模型

三、命令行客户端
命令行:
1、连接
mysql -uroot -p 回车,然后输入密码
2、查看所有仓库
show databasees;
3、使用某个仓库
use 仓库名
4、查看所有的表
show tables;
5、查看表结果
desc students;
show create table students;
6、如果出现中文乱码
set charset gbk;

备份和恢复

以管理员身份运行命令行客户端
运行mysqldump命令
cd C:\Program Files(x86)\MySQL\MySQL Server 5.1\bin
mysqldump -uroot -p 数据库名>ceshi.sql         //不写路径,创建在当前目录下

#按提示输入mysql密码

恢复:
先创建新的数据库
mysql -uroot -p 新数据库名 <ceshi.sql
#根据提示输入mysql密码

四、函数
1、字符串函数
1)拼接字符串 concat(str1,str2...)
select concat(12,34,'ab');


2)包含字符个数 length(str)
select length('abc');
select length('中');//如果是中文则是一个文字的长度为3,其他的都是1;
select * from students where length(name)=6;//查询所有名字是2个字的学生信息
3)截取字符串
left(str,len)返回字符串str的左端len个字符
select name,sex,concat(left(name,1),'**') from students;//截取姓,并将名字改成**
right(str,len)
substring(str,pos,len)返回字符串str的位置pos起len个字符

select substring('abc123',2,3);

4)去除空格
ltrim(str) 返回删除了左空格的字符串str
rtrim(str)右

select ltrim(rtrim('   abc   ')); //先去掉右边空格再用去左空格包起来运行,可去2边空格,但是不用 这么麻烦,
直接:select trim('  abc   ');可达成同样的效果
但是select trim('a   bc')的中间空格去不掉

5)转换大小写
lower(str);
upper(str);

2、数学函数
1)求四舍五入值 round(n,d) n表示原数,d表示小数位置,默认为0
select round(1.62377,3) ;//返回1.624

2)求x的y次幂  pow(x,y)
select pow(2,4);//返回16

3)获取圆周率
select PI(); //3.141593  

4)随机数 rand(),值为0-1.0的浮点数
select RAND();//第一次随机拿到的结果 是:0.894339572539869,第二次是:0.103550892419695
想要一个0到10的随机数:round(rand()*10); 随机数*10后再取整

随机的抽取一条学生的信息:
select *,rand() from students order by rand() limit 1;

3、时间日期函数
1)当前日期
select CURRENT_DATE();
2)当前时间
select CURRENT_TIME();
3)当前时间日期
select NOW();
4)日期格式化
date_format(date,format);
select DATE_FORMAT(NOW(),'%Y');//查完整的年
//%Y完整的年  y简写年     m月  d日   H24小时制的小时数   h12小时制的小时数  i分  s秒
select DATE_FORMAT(NOW(),'%Y%m%d-%H%i%s');//20200319-073842

select DATE_FORMAT(NOW(),'%Y/%m/%d-%H/%i/%s');//2020/03/19-07/40/11

4、流程控制
select
case 1
when 1 then 'one'
when 2 then 'two'
else 'zoro'

end as result;

例子:把学生表里的名字和性别查出来,如果是男的就显示为某先生,女的显示为某女士,性别为空等其他情况用xx代称
select name,sex,
case sex
when '男' then concat(left(name,1),'先生')
when '女' then concat(left(name,1),'女士')
else concat(left(name,1),'xx')
end as 别名
from students;


name  sex  别名
吴xx  男  吴先生

五、自定义函数
除了mysql自带的,还可以自己创建函数,显示在数据库的函数库里,需要的时候直接使用

语法:
delimiter $$
create function 函数名称(参数列表) returns 返回类型
begin
sql语句
end
$$
delimiter;
说明:delimiter用于设置分割符,默认为分号
在“sql语句”部分编写的语句需要以分号结尾,此时回车会直接执行,所以要创建存储过程前需要指定其
它符号作为分割符,此处使用$$,也可以使用其他字符

示例:创建函数my_trim,用于删除字符串左右两侧的空格
step1:设置分割符  为了在命令行输入;号时不运行
delimiter $$  

step2:创建一个函数到数据库的函数库里
create function my_trim(str varchar(100)) returns varchar(100)
BEGIN
return ltrim(rtrim(str));
END

step3  输入$$来执行后
step4 再次delimiter ;来还原分号作为分隔符

step5 可以直接调用该函数
select my_trim('  abc         ');


六、存储过程  跟函数差不多,仍然放在函数库里,使用call调用,函数是使用select来调用的
例子:创建一个查询所有学生信息的存储过程,然后调用
delimiter //
create procedure pro_stu()
begin
select * from students;
end
//
delimiter ;

call pro_stu();

使用函数和存储过程可以减少网络传输的损耗;不过现在开发人员已经很少在数据库这里使用函数和存储过程了

七、视图        其实就是个查询语句,用以重复使用,可以当成一个表来使用;

CREATE VIEW v_score_course AS SELECT
    stu.name, cs.courseNo,
    cs. NAME courseName,
    sc.score
FROM
    students stu
INNER JOIN scores sc ON stu.stuNo = sc.studentNo
INNER JOIN courses cs ON cs.courseNo = sc.courseNo;

查看视图:查看表会将所有的视图也列出来
show tables;

删除视图:
drop view v_score_course;

使用视图:视图的用途就是查询
select * from v_score_course;

结果:
name      courseNo     courseName  score
吴xx    1        数据库        90
张三丰    2        Linux        60
陈同学    3        接口测试    96
...


视图可以隐藏一些安全数据的字段,比如说students表里,只有名字和年龄可以显示出来,假设学号等信息是类似银行的密码之类的数据,不能给普通开发人员查看到,就由签了保密协议的关键
开发人员创建一个视图给普通开发人员来调用:
create view v_students as
select name 姓名,age 年龄 from students;

调用:select * from v_students;


八、事务
完成多步才能达成一件事情;要么全成功,要么全失败;
例如将students表里的某一学生的年龄转一点给另一个学生,假装是转帐
先查出来:
select * from students where name in('吴xx','张晨');//假设吴xx是18岁,张晨是25岁
然后运行下面的语句:
begin;
update students set age=age-5 where name='张晨';
update students set age=age+5 where name='吴xx';
commit;

然后再执行上面的查询语句,发现吴xx是23岁,张晨是20岁了。

假设上面有其中一句执行失败了,比如张晨扣了5岁,吴xx去没有加5岁,此时需要:
rollback;

九、索引         使查询更快了,但是更新表的速度会慢,所以要谨慎使用
创建表的时候给字段创建索引:只要字段后面带着primary key\unique\或者直接用key()来指定某一字段的索引
都会给它们建一个索引方便查询

create table testIndex2(
id int primary key,
name varchar(10) unique,
age int,
key(age)
);

create index 索引名称 on 表名(字段名称(长度))

drop index 索引名称 on 表名                  删除索引

十、外键   控制表中的数据符合另一个表的范围。例如一个学生表一个班级表,班级表里只有1班、2班,那学生表里的班级id不能写3班的
约束
在实际开发中很少使用,因为会极大的降低表更新的效率
alter table 从表名 add foreign key (从表字段) references 主表名(主表字段);
alter table stu add foreign key (class_id) references class(id);

删除外键:
需要先查出外键名称:
show create table stu;
找出 stu_ibkf_1这样的名称
然后删除掉:
alter table 表名 drop foreign key 外键名称;
alter table stu drop foreign key stu_ibkf_1;

十一、修改密码
使用root登录,修改mysql数据库的user表
使用password()函数进行密码加密码
注意修改完成后需要刷新权限

use mysql;
update user set password=password('新密码') where user='用户名';

eg: update user set password=password('123') where user='root';

刷新权限:flush privileges;

之后再登录数据库和在navicat打开库连接都要重新输入密码123才能登录了


十二、忘记root账户密码怎么办
1、配置mysql登录时不需要密码,修改配置文件
Centos中:配置文件位置为/etc/my.cnf
Windows中:C:\Program Files(x86)\MySQL\MySQL Server 5.1\my.ini

修改,找到mysqld ,在它的下一行,添加skip-grant-tables          (跳过权限检查)
要用管理权限打开来改,然后【保存】
重启mysql

进入数据库里,再看user表里root的密码

然后用”十一“点上的修改密码来将密码重置
完成后将配置文件里的skip-grant-tables删除掉
再重启,重新输入新密码

60°|607 人阅读|0 条评论
登录 后发表评论