Mysql在项目中的基本操作
数据库
19 分钟 读完 (大约 2890 个字)

Mysql在项目中的基本操作

Mysql

数据库：

关系型数据库
非关系型数据库，NOSQL数据库
关系型数据库：Oracle Mysql(SUN Java) DB2(IBM)

DDL(数据定义语言): CREATE、 DROP、 ALTER

表的设计

范式

  • 第一范式
    强调数据表的原子性 表中字段的值是不能拆分的
  • 第二范式
    消除了非主属性对于码的部分函数依赖，即完全依赖
    表中非主字段必须完全依赖主键字段
  • 第三范式
    消除了非主属性对于码的传递函数依赖
    依赖不能传递

避免表中数据冗余

创建表语法:
create table 表名(
字段名1 数据类型(长度) 约束,
字段名1 数据类型(长度),
字段名1 数据类型(长度)
);

  • 表名，字段名
    • 只能由字母，数字，下划线，$ 组成
    • 不能由数字开头
    • 不能是SQL中的关键字

删除表
drop table 表名;

MYSQL常用的数据类型

int(6)
double(10,2) //浮点型，加小数10位，小数部分占2位
varchar:可变长字符类型 //name varchar(10) Jack
char:不可变长字符类型 //idcode char(18) 210
date:日期类型
timestamp:时间戳类型

约束
约束是用来完备性和正确性

  • 主键约束

    开发中，通常都会为一张表设计一个字段，该字段用来唯一标识记录。将该字段添加主键约束，添加了主键约束的字段，称为主键字段。
    添加主键字段防止出现张冠李戴的现象。
    主键约束的特点：非空、唯一
    一个表只能有一个主键

  • 外键约束
    *注意:
    外键字段必须引用于主键字段
    被引用的表称为主表，引用的表称为从表
    外键的三种添加方式(如: 在主表员工表 emp 中给字段 deptno 设置外键,外键为从表 dept 的主键 deptno )

    • 方式一
      [ 列名 数据类型() references 外键所属表(该表主键) ]

      1
      2
      3
      4
      create table emp(
      	.......,
      	deptno int(2) references dept(deptno)
      );

    • 方式二
      不在列名后面写，而是在最后表级添加
      [ foreign key(外键列名) references 外键所属表(该表主键) ]

      1
      2
      3
      4
      5
      create table emp(
      	.......,
      	deptno int(2),
      	constraint t_emp_dept_fk foreign key(deptno) references dept(deptno)
      );

    • 方式三
      前两张方式的局限性：只能先建 才能引入外键
      可以在创建表时先不添加外键
      待表建好以后，通过修改表的方式添加外键

      1
      2
      3
      4
      5
      6
      create table emp(
      	.......,
      	deptno int(2)
      );

      alter table emp add foreign key(deptno) references dept(deptno);

  • 唯一约束
    唯一性约束 unique

    1
    2
    3
    4
    5
    6
    create table emp(
    	.......,
     	email varchar(50) unique,
     	phone varchar(20) unique,
     	......
    );

  • 非空约束
    not null

    1
    2
    3
    4
    5
    create table emp(
    	.......,
     	password varchar(20) not null,
     	......
    );

  • 检查约束
    check

    1
    2
    3
    ......
    created timestamp,
    check(created>'2019-1-1')

  • 设置一个字段默认值

    1
    2
    3
    4
    5
    6
    drop table if exists t_user;
    create table t_user(
    	id int() primary key auto_increment,
    	gender char() default '0',
    	......
    	);

主键约束：primary key,主键字段的值非空唯一
外键约束：foreign key references t1(id);外键字段值引用其他主键字段的值

  • 创建数据库

    1
    create database mydb;

  • 选择数据库

    1
    use mydb;

  • 查看数据库中的表

    1
    show tables;

  • 创建表 Demo

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    create table t_user(
    	id int(6) auto_increment primary key,
    	username varchar(20),
    	password varchar(20),
    	email varchar(50),
    	phone varchar(20),
    	type char(1),
    	created timestamp,
    	updated timestamp
    );

  • 先删除再创建表

    1
    2
    drop table if exists t_user;
    create table t_user(.....);

  • 查看表结构

    1
    desc 表名;

sql 语句中的常用操作细节:

细节操作:

  1. 设置主键并让主键自增
    auto_increment primary key
    auto_increment,自动增长，用来生成主键字段的值
  2. mysql 解决乱码：
    创建数据库时:
    create database my_db default charset=utf8;
    创建表设置字符集:
    creaate table tablename(
    ……
    )default charset=utf8;
  3. mysql 中的时间
    • date
    • datetime
    • timestamp
1
2
3
4
设置系统当前时间demo:
commited date default now(),
starttime datetime default now(),
paytime timestamp default currene_timestamp(),

根据如下需求创建表,并进行相关操作：

创建部门表和员工表，员工表中有部门号字段为外键字段，引用部门表中部门号字段
部门表字段：部门号，部门名，部门地址
员工表字段：员工号，员工姓名，职位，薪水，入职日期，经理编号，部门号

部门表

1
2
3
4
5
create table dept(
	deptno int(2) auto_increment primary key,
	dname varchar(30),
	loc varchar(50)      	
);

员工表

1
2
3
4
5
6
7
8
9
10
11
create table emp(
	empno int(4) auto_increment primary key,
	ename varchar(20),
	job   varchar(20),
	sal double(10,2),
	hiredate date,
	mgr int(4),
	deptno int(2),
	foreign key(mgr) references emp1(empno),
	foreign key(deptno) references dept1(deptno)
);

创建表设置字符集

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table dept(
	deptno int(2) auto_increment primary key,
	dname varchar(30),
	loc varchar(50)      	
) default charset=utf8;       
create table emp(
	empno int(4) auto_increment primary key,
	ename varchar(20),
	job   varchar(20),
	sal double(10,2),
	hiredate date,
	mgr int(4),
	deptno int(2)
)default charset=utf8; 
alter table emp add foreign key(mgr) references emp(empno);  
alter table emp add foreign key(deptno) references dept(deptno);

表的管理

DML（INSERT UPDATE DELETE SELECT）

  • INSERT:添加数据

    • 需求：创建管理员账号

      1
      insert into t_user values(NULL,'admin','admin','admin@gavino.com','15699782000','2',now(),now());

    • 需求：创建普通用户账号

      1
      insert into t_user values(NULL,'user01','user01','user01@gavino.com','15699782111','1',now(),now());

    • 需求：
      1、创建4个部门分别为10,20,30,40,部门名为研发部，教学部，人事部，财务部

      1
      2
      3
      4
      insert into dept values(10,'研发部','理工本部综合楼301');
      insert into dept values(20,'教学部','理工本部综合楼302');
      insert into dept values(30,'人事部','理工本部综合楼303');
      insert into dept values(40,'财务部','理工本部综合楼304');

      2、向员工表中添加如下员工：

      • 一个总裁

        1
        insert into emp values(7000,'KING','总裁',20000,'2010-1-1',null,10);

      • 三个部门（研发部，教学部，人事部）经理

        1
        2
        3
        insert into emp values(7001,'汪三四','经理',18000,'2013-2-12',7000,10);
        insert into emp values(7002,'马五六','经理',22000,'2014-3-10',7000,20);
        insert into emp values(7003,'周七八','经理',10000,'2012-5-29',7000,30);

      • 每个部门中添加5个员工

        • 研发部

          1
          2
          3
          4
          5
          insert into emp values(7004,'王啊','CLERK',15000,'2012-09-19',7001,10); 
          insert into emp values(7005,'张吖','CLERK',12500,'2013-02-12',7001,10);
          insert into emp values(7006,'白嗄','CLERK',11000,'2017-12-12',7001,10);
          insert into emp values(7007,'王阿','CLERK',9000,'2018-06-06',7001,10);
          insert into emp values(7008,'曹咦','CLERK',19000,'2019-05-12',7001,10);

        • 教学部

          1
          2
          3
             insert into emp values(7010,'楚王','讲师',22000,'2016-09-19',7002,20); 
          insert into emp values(7011,'腾非','讲师',18000,'2013-02-12',7002,20);
          ......

        • 人事部 

          1
          2
          insert into emp values(7015,'张六','人事专员',6000,'2017-09-19',7003,30);
          ......

  • UPDATE

    • 需求：将公司中30号部门员工涨薪1000
      1
      2
      update emp set sal=sal+1000 where deptno=30;
      update emp set sal=sal+600,hiredate='2016-05-12' where empno=7019;

  • DELETE

    1
    2
    insert into emp(empno,ename) values(8000,'aaaa');  
    delete from emp where empno=8000;
  • SELECT:
    DML（INSERT,UPDATE,DELETE）
    DQL（SELECT）
    DML（INSERT,UPDATE,DELETE,SELECT 增删改查 CRUD）

sql 更多练习：

  • 基本查询
    select 字段1,字段2… from 表名;
    • 需求：列出员工姓名，薪水，职位，入职日期
      select ename,sal,job,hiredate from emp;
    • 需求：列出员工表中所有数据
      select * from emp; (开发中，尽可能不使用*)
      select empno,ename,job,sal,hiredate,mgr,deptno from emp;
  • 过滤查询
    • 需求：列出10号部门员工的姓名，工资，入职日期
      select ename,sal,hiredate from emp where deptno=10;
    • 需求：列出薪水低于10000的员工姓名，薪水，职位，部门号
      select ename,sal,job,deptno from emp where sal<10000;
    • 需求：列出10号部门16年之后入职的员工姓名，薪水，职位，入职日期
      select ename,sal,job,hiredate from emp where deptno=10 and hiredate>’2016-1-1’;
    • 需求：列出10号部门或16年之后入职的员工姓名，薪水，职位，入职日期，部门号
      select ename,sal,job,hiredate,deptno from emp where deptno=10 or hiredate>’2016-1-1’;
    • 需求：列出10，20号部门中的员工姓名，薪水，职位，部门号
      select ename,sal,job,deptno from emp where deptno in(10,20);
    • 需求：列出’王’姓的员工姓名，职位，入职日期
      (模糊查询：like % _)
      %:任意位任意字符
      _:一位任意字符
      select ename,job,hiredate from emp where ename like ‘王%’;
    • 需求：列出名字中第二个字是’浩’的员工姓名，薪水，职位，入职日期，部门号
      select ename,sal,job,hiredate,deptno from emp where ename like ‘_浩%’;
  • 排序查询
    order by asc(升序) desc(降序)
    • 需求：列出员工的姓名，薪水，职位入职日期，根据薪水降序排序
      select ename,sal,hiredate from emp order by sal desc;
    • 需求：列出员工的姓名，薪水，职位,入职日期，根据薪水降序排序,入职日期升序排序
      select ename,sal,hiredate from emp order by sal desc,hiredate asc;
    • 需求：列出10号部门员工的姓名，薪水，职位，入职日期，根据薪水降序排序
      select ename,sal,job,hiredate from emp where deptno=10 order by sal desc;
  • 分组查询
    group by
    (统计运算：max min sum avg count)
    • 需求：列出公司中每个部门员工薪水总和
      select deptno, sum(sal) from emp group by deptno;
    • 需求：列出公司中每个部门的最大工资，最小工资，平均工资，工资总和
      select deptno,max(sal),min(sal),avg(sal),sum(sal) from emp group by deptno;
    • 需求：列出公司中有多少个员工
      select ename,count(*) from emp;–语法错误
      (注意：分组查询，只能查询分组字段和分组之后的函数运算结果)
  • 多表查询（联合查询，内连接，外连接查询）
    ( 查询的数据分布在多张表里 )
    • 需求：列出员工姓名，薪水，职位，入职日期，部门名，部门地址
      等值查询(等价于内连接)
      select ename,sal,job,hiredate,dname,loc from emp e,dept d
      where e.deptno=d.deptno;
      内连接查询只查询满足连接条件的数据
      inner join … on…
      select ename,sal,job,hiredate,dname,loc from emp e inner join dept d
      on e.deptno=d.deptno;
      (等值查询或内连接查询，查询的结果都必须满足连接条件)
      注意：多表查询一定要指定连接条件，条件的数量最少为 n-1
      否则会出现笛卡尔乘积现象
      外连接查询 :
      左外连接、右外连接、全外连接
      左外连接
      select ename,sal,job,hiredate,dname,loc from emp e left outer join dept d
      on e.deptno=d.deptno;
      右外连接
      select ename,sal,job,hiredate,dname,loc from dept d right outer join emp e
      on e.deptno=d.deptno;
      右外连接
      select ename,sal,job,hiredate,dname,loc from emp e right outer join dept d
      on e.deptno=d.deptno;
      全外连接（oracle支持）
      select ename,sal,job,hiredate,dname,loc from emp e full outer join dept d
      on e.deptno=d.deptno;
  • mysql分页查询
    select * from emp limit 5,5;

其它操作

HAVING 对分组后的结果再过滤

  • HAVING 是分组后的筛选条件,WHERE 则是在分组前筛选
  • WHERE子句在聚合前先筛选记录。也就是说作用在GROUP BY 子句和HAVING子句前
  • HAVING 在分组和聚集之后选取分组的行。因此，WHERE 子句不能包含聚集函数；
  • HAVING 子句总是包含聚集函数。（也可以写不使用聚集的 HAVING 子句， 但这样做没有意义。同样的条件可以更有效地用于 WHERE ）

子查询

普通子查询

执行顺序 先查子查询

相关子查询

先执行主查询

IN (NOT IN)

EXISTS (NOT EXISTS)

联合(多表)查询

UNION UNION ALL

UNION 对多个结果集去重的连接操作
UNION ALL 对多个结果集不去重的连接操作

视图(view)

一个命名的查询 ,可以通过视图隐藏表结构
可以简化SQL语句
创建视图

1
2
3
create or replace view myview
as
......

使用视图查询:

1
select * from myview;

事务

  1. 原子性(Atomicity)
    一个不可分割的操作单元，保证所有的操作要么都成功，要么都失败
  2. 一致性(Consistency)
    事务前后数据的完整性必须保持一致
  3. 隔离性(Isolation)
    并发访问数据库期间，一个用户的事务不会被其他事务所干扰，多个并发事务的数据相互隔离
  4. 持久性(Durability)
    一旦事务被提交，它对数据的改变就是永久的。
# Mysql

喜欢这篇文章？打赏一下作者吧

JDBC连接数据库基本操作
JDK1.5 相关特性总结

评论

关注我

分类

最新文章

链接

标签云

Ajax Cookie ES6 IDEA JDBC JS JVM Java Linux Markdown Mybatis Mysql Session Sublime jQuery spring springMVC 前端 命令 框架 设计模式

归档

标签

Ajax 1
Cookie 1
ES6 1
IDEA 1
JDBC 1
JS 1
JVM 1
Java 19
Linux 1
Markdown 1
Mybatis 2
Mysql 2
Session 1
Sublime 1
jQuery 1
spring 3
springMVC 1
前端 1
命令 1
框架 6
设计模式 2