金沙官网线上【Mysql数据库】学习笔记

非空约束(NOT NULL)

如果某个字段的值不能为null 需要使用此约束

create table t2(id int,age int not null,name varchar(10));
-以下两种都不可以
insert into t2 (id,name)values(2,'a');
insert into t2 values(3,null,'b');

 

      mysqld install      // 安装MySQL数据库服务

索引总结

1. 索引不是越多越好 
2. 数据量小的时候不需要用到索引
3. 经常出现在 where、order by、 distinct的字段添加索引,效果会更好
4. 不要在经常做增删改的表中添加索引

      mysql -u username -p  // 登录MySQL管理系统  

MySQL 约束

    非空约束: 字段名 数据类型 not null

事务案例

转账案例:

1.创建user表
    create table user(id int,name varchar(20), money int);
    insert into user values(1,'苍老师',2000),(2,'刘老师',100);
2.转账sql
    update user set money=money+500 where id=2;
    update user set money=money-500 where id=1;

 

课程回顾:

1.什么是视图 实际上视图就是一段sql语句

2.视图中数据实际上都是原表中的数据,如果修改视图中的数据 原表数据跟着更改

3.避免出现数据污染 可以通过添加 with check option 避免出现数据污染

4.视图通常只进行查询操作

5.视图作用:子查询复用、限制数据的访问

6.索引index 需要掌握 创建命令和删除命令

7.约束:unique、not null、primary key、default foreign key,check

8.事务 背下来四大特性: 原子性、一致性、隔离性、持久性

day01:

数据库相关的sql:show databases create database db1 character set utf8 show create database db1 drop database db1;

表相关: create table show tables; show create table t1; desc t1; rename table t1 to t2; alter table t1 change age age2 int; alter table t1 modify age long first/after id; add age int; drop age; drop table t1;

insert update delete select

day02:

1.主键 自增 not null 注释:comment

2.去重 distinct

3.事务

4.sql分类 ddl 数据定义语言 create alter drop truncate:删除表内所有数据,内部实现原理:先删除表再创建一个新表 执行效率高 dml 数据操作语言 insert update delete select 支持事务 dql 数据查询语言:select tcl 事务控制语言:begin commit rollback dcl 数据控制语言:控制权限

5.数据类型 整数: int bigint 浮点数: double decimal(m,d)

6.字符串: char varchar text

7.日期:date time datetime timestamp

day03:

聚合函数:sum count avg min max

字符串:charlength instr(x,y) locate(x,y) insert(str,begin,length,newStr) lower upper left right substring(str,begin,length) trim() replace(str,old,new); repeat(str,2); reverse

日期相关:now date(now()) time(now()) extract(year from now()) month day time hour second minute dateformat(now(),'%Y-%m-%d %H-%i-%s') strtodate('','')

数学:+ - * / % mod(2,3)
数学函数: floor  round() rand()

    for insert|delete|update

默认约束(DEFAULT)

如果需要让某个字段插入数据时有默认值时使用此约束
create table t3 (id int,age int default 0,name varchar(10));

 

      show databases;    // 显示所有数据库

外键约束(FOREIGN KEY)

  1. 工作中,除非特殊情况,一般不使用外键,使用代码通过逻辑进行限制
  2. 外键约束是保证一个或两个表之间数据的一致性和完整性的
  3. 表的外键通常使用的是另一张表的主键
  4. 外键可以重复、可以是null、但不可以是另一张表不存在的数据
  5. 使用外键约束的条件 -必须保证两张表使用相同的存储引擎 -存储引擎必须是innodb,myisam不支持外键约束 -外键和关联字段必须有相似的数据类型,数字长度必须相同 -外键约束对应的字段必须创建索引,如果不存在索引,mysql会自动创建索引

       mysql -u root -p123 database_name<d:myDBdemo.sql

主键约束(PRIMARY KEY)

1. 非空、唯一
2.如何添加主键
-创建表时添加主键
    create table t4(id int primary key,age int );
    create table t5(id int,age int,primary key(id));
-创建表之后添加主键
    alter table t6 add primary key(id);
    alter table t6 modify id int primary key
-删除主键约束
    alter table t3 drop primary key;
-自增约束 auto_increment
1.当字段赋值为null的时候,字段自动增长 
2.如果删除了某一条数据 自增的数值不回减少
3.如果插入数据时指定了一个特别大的数,下次自增则从这个数基础上+1

 

    外键约束: foreign key(字段名) references 表名(字段名);

唯一约束(UNIQUE)

如果数据库中的数据需要保证唯一性则添加此约束
CREATE TABLE T1( ID INT, AGE INT UNIQUE, NAME VARCHAR(20));

 

      mysqldump -u root -p123 database_name>d:myDBdemo.sql 

索引原理

    b. 恢复

什么是事务

事务是一组原子性的 SQL 查询, 或者说是一个独立的工作单元. 在事务内的语句, 要么全部执行成功, 要么全部执行失败

    on student        

MySQL事务

1.查看事务是否是自动提交
    show variables like '%autocommit%';
2.关闭自动提交
    set autocommit=0;

begin 开始事务
commit 提交事务
rollback 回滚事务

 

    自增量: 字段名 数据类型 auto_increment  //默认初始值为1,增量为1;如需从100开始,可以设置为auto_increment=100

CHECK约束

mysql语法支持,但是没有效果
create table t7(id int,name varchar(10),
age int,check(age>20));

 

      primary key(stu_num),  //主键

课程回顾

  1. 一对一关联 案例1:查询每个员工的名字和主管领导的名字

    select e.ename 员工姓名,m.ename 领导姓名
    from emp e join emp m
    on e.mgr=m.empno;
    

     

    案例2: 查询主管领导名字为blake的所有员工名字 

    select e.ename 员工姓名,m.ename 领导姓名 from emp e join emp m on e.mgr=m.empno where m.ename='blake'
    

     

    案例3:查询有商品的分类信息及上级分类信息 往titemcategory表内部插入以下数据 id 162 name 办公用品 id 229 name 文具 id 913 name 户外用品 -插入数据

     insert into titemcategory (id,name) values(162,'办公用品'),(229,'文具'),(913,'户外用品');
    

     

     -查询所有有商品的分类id

    SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL
    

     

    * -查询分类详情*

    select * from titemcategory where id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL)
    

     

     -查询上级分类

     select n.*,c.name 上级分类名称 from (select * from titemcategory where id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL)) n join titemcategory c on n.parentid=c.id
    

     

    -查询有商品的分类信息及上级分类信息 实现步骤:1.通过子查询查到有商品的分类id 2.把当前分类和上级分类通过内连接建立关系 3.然后把分类的id作为筛选条件 -简单写法 

    select c.*,m.name 上级分类 from titemcategory c join titemcategory m on c.parentid=m.id where c.id in (SELECT DISTINCT categoryid FROM titem WHERE categoryid IS NOT NULL);
    

     

  2. 一对多关联

    部门 员工 分类 商品 用户 地址 userid -一对多的表中 在多的表中添加关系字段

    案例1:查询每种分类下所对应的所有商品

    select *
    from t_item_category c left join t_item i
    on c.id=i.category_id
    

     

    案例2:查询部门平均工资 大于 所有员工的平均工资的所有部门下的所有员工

    SELECT * FROM emp 
    WHERE deptno IN (SELECT deptno FROM emp
    WHERE deptno IS NOT NULL
    GROUP BY deptno
    HAVING AVG(sal)>(SELECT AVG(sal) FROM emp))
    

     

    案例3:上题结果上再多显示出 部门的名字

     select n.*,d.dname from(SELECT * FROM emp WHERE deptno IN (SELECT deptno FROM emp WHERE deptno IS NOT NULL GROUP BY deptno HAVING AVG(sal)>(SELECT AVG(sal) FROM emp)))n join dept d on n.deptno=d.deptno
    

     

  3. 多对多关联 -多对多需要单独通过新建的关系表保存数据

    create table teacher(id int primary key auto_increment,name varchar(20));
    
    insert into teacher values(null,'唐僧'),(null,'赵本山'),(null,'刘老师'); create table student(id int primary key autoincrement,name varchar(10)); insert into student values(null,'八戒'),(null,'悟空'),(null,'宋小宝'),(null,'小沈阳'),(null,'小明'),(null,'小红'); create table ts(tid int,sid int); insert into t_s values(1,1),(1,2),(2,3),(2,4),(3,5),(3,6),(1,5),(2,5); 案例1:查询每个老师对应的所有学生信息 1. 子查询 select name from student where id in (select sid from ts where tid in(select id from teacher)) 2. 内连接 SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN ts ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id
    

     

    案例2:查询 小明 对应的所有老师 

    SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN t_s ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id where s.name='小明'
    

     

    案例3:刘老师 对应的所有学生信息 

    SELECT n.name tname,s.name FROM (SELECT * FROM teacher t JOIN t_s ts ON t.id=ts.tid)n JOIN student s ON n.sid=s.id where n.name='刘老师';
    

     

    (

索引概述

  1. 索引是用来提高查询速度的技术
  2. 如果不使用索引mysql会从第一条数据依次往后查询,如果数据量很大,非常耗时
  3. 添加索引可以提交查询性能,但是如果数据量很小,添加索引可能会降低效率
  4. 索引的生成是数据库内部生成,如果查询数据时发现某个字段已经添加过索引会自动使用

  5. 复合索引

    创建索引时如果设置多个索引字段 则称为复合索引

  6. 创建表的时候直接添加索引

    create table t11 (id int,name varchar(10), age int, index index_name(name(10)));
    

     

  7. 更改表的索引 

    alter table t11 add index index_age(age);
    

     

  8. 删除索引

    drop index index_age on t11;
    

     

    as <select查询语句>

事务的 ACID 性质

**必须背下以下4点 面试常考**
-原子性:最小的单元,不可分割
-一致性: 保证sql执行的一致 要么都成功,要么都失败,保证执行前后数据一致
-隔离性:多个事务并发时,互不影响
-持久性:commit提交之后,数据保存到数据库中

 

      show create table table_name;  //显示表的创建信息

视图概述

-数据库中存在多种对象,表和视图都是数据库中的对象 **表和视图不能重名**
-视图是张虚拟的表,是通过sql的查询语句查询的结果集的表保存成了一个视图 
-视图中的数据 会随着真实表中的数据改变而改变
-视图实际上就是代表了一部分sql语句

-创建视图

-格式:create view 视图名 as 子查询;

create view v_emp_10 as (select * from emp where deptno=10); 

案例:创建emp表的部门是20 工资小于3000的 视图
    只保存名字 工资 部门id

create view v_emp_20 as (select ename,sal,deptno from emp
    where deptno=20 and sal<3000);

案例:创建emp表 每个部门的工资平均值 的视图

create view v_emp_dept_avg as (select deptno,avg(sal) from emp group by deptno)

 

-如果创建视图的时候使用了别名,那视图以后的操作只认识别名

-视图的使用方式 和 table 一样 -格式:select * from 视图名

 select * from v_emp_10;

 

  • 修改视图 -格式:create or replace view 原视图名 as 新的子查询

    create or replace view v_emp_10 as
    (select * from emp where deptno=10 and sal<3000);
    

     

  • 视图的分类 -视图分为:简单视图和复杂视图 -简单视图:在创建视图的子查询中 不包含:关联查询、去重、函数、分组的视图称为简单视图 -复杂视图:和简单视图相反

  • 对视图进行dml操作,只针对简单视图可以使用 因为复杂查询通常情况只是为了浏览数据的,不需要也不能进行增删改的操作

视图的字段 遵循原表字段的约束 - 视图的数据污染 如果往视图中插入一条在视图中不现实 但是在原表中显示的数据,称之为数据污染,不建议这样操作 只有insert命令才会出现数据污染,因为update和delete命令只能操作视图中有的数据

  • with check option create view vemp10 as select * from emp where deptno=10 with check option;

  • 视图的作用

    1. 重用子查询 提高开发效率
    2. 限制数据的访问 比如:可以创建一个视图把原表的敏感信息过滤掉即可 
      create view v_emp as select empno,ename,deptno,comm,job from emp
      
  • 工作中对视图一般只进行DQL,不使用DML

  • 删除视图

    drop view vemp10;
    

     

    -案例:创建一个复杂视图:显示部门的平均工资,最高工资,最低工资,工资总和,部门员工人数;

    create view v_emp_deptinfo as
    select deptno,avg(sal),max(sal),min(sal),sum(sal),count(*) 
    from emp 
    group by deptno;
    

     

       

视图

五、事务

创建外键约束

1. mysql支持外键的列级语法,但是没有效果,是为了保证和sql的规范的一致性
2. 表级约束的添加方式
-格式:CONSTRAINT 约束名 FOREIGN KEY(当前表添加约束的字段名) REFERENCES 关联表表名(关联表的字段名)
create table classes(id int primary key auto_increment,name varchar(20));
insert into classes values(null,'一班'),(null,'二班');
CREATE TABLE student(
id INT AUTO_INCREMENT PRIMARY KEY,         
name VARCHAR(20),                                
class_id INT,
CONSTRAINT fk_class_id FOREIGN KEY(class_id) REFERENCES classes(id)
);
1.创建班级(classes)表 id name
2.创建学生表(student)id name classid
给classid添加外键约束 
3.先往班级表中插入数据 然后往学生表插入数据,

 

测试插入错数据 和删除 被关联的班级数据

    drop view stu_sub;  //删除视图

练习

1.案例:创建一张表customer2,id number(4),
name varchar2(50),password varchar2(50)
,age number(3),address varchar2(50),修改
customer2表的时候设置主键约束
pk_id_name_cus2修饰id和name列。




2.案例:创建一张book3表,id number(4),
name varchar2(50),author varchar2(50),
pub varchar2(50),numinput number(10)。
修改book3的时候,设置主键约束
pk_id_name_b3修饰id和name列,设置唯一约束uq_author_pub_b3修饰author和pub列



3.案例:删除temp中的唯一约束uk_name_pwd

4.案例:在book表中author和pub列上添加索引index_author和index_pub


5.案例:删除book中在pub和author上的索引


6.案例:创建一个视图emp_view1,查询emp表中所有的数据,查询语句作为视图emp_view1


7.案例:创建一个视图dept_view,查询dept表中所有的数据,查询语句作为视图dept_view


8.案例:创建一个视图emp_view2,查询emp表中所有员工的编号,姓名,职位,工资,上级领导的编号以及工资的等级,该等级的最低工资和最高工资,查询语句作为emp_view2


9.案例:查询emp表中10,20号部门员工的编号,姓名,职位,工资,所属部门的编号,使用查询语句来修改视图emp_view1


10.案例:删除视图emp_view1,emp_view2

  5.1 事务四大特性

三、视图的创建

    2* read commited :避免赃读。不可重复读,虚读都有可能发生;

    1.1.2 数据表操作命令

      begin

    不可重复读:一个事务A读取另一个事务B提交后的数据(update);

    以上情况均为事务A已开启,但未提交时发生!

    create table 表名

    as <sql_statement>(sql语句)

    alter table 表名 drop 字段名  //删除字段

      主键约束: primary key(字段名);  //或在定义字段时直接在后面追加 primary key

                net start mysql 或 net stop mysql     // 启动或停止MySQL服务

    四个级别:

一、数据库的创建

      stu_name char(10) not null,

    8* serializable :避免赃读,不可重复读、虚读;

    on table(表名)

        select  t1.name,t1.age,t2.score  from table1_name t1 , table2_name t2 where t1.id=t2.id

      from student;

    1.1.1 数据库命令

    持久性。

    设置事务隔离级别:set transaction isolation level 四个级别之一;  //dos命令

    create trigger trigger_name  //创建触发器

        from inserted

    (

      source d:myDBdemo.sql

  2.3 修改表

    4* repeatable read :避免赃读,不可重复读。虚读有可能发生;

    );

      foreign key(stu_name) references anotherTable_name(stu_name)  //外键

    级别越高,安全性越高,性能越差!

  例如:

    alter table 表名 change 旧字段名 新字段名 新数据类型  //修改字段名

      use database_name;  // 引用数据库

   1.4 数据库备份和恢复

    隔离性;

  例如:

    for insert,delete,update

    

    end

四、触发器

    默认约束: 字段名 数据类型 default 默认值

      end

  drop database database_name;  //删除数据库

      字段名2 数据类型,

    as

本文由金沙官网线上发布于数据库,转载请注明出处:金沙官网线上【Mysql数据库】学习笔记

您可能还会对下面的文章感兴趣: