Mysql

 0、数据库基础介绍

数据库管理系统　　DBMS(data base management system )，包括Oracle，mysql，SQLserver，MongoDB,db2等

数据库/仓库　　　  DB

SQL　　　　　　　结构化查询语言

Mysql　　　　　　一种数据库软件，使用sql语言管理数据

数据库中事务的四大特性（ACID）：原子性（Atomicity）；一致性（Consistency）；隔离性（Isolation）；⑷ 持久性（Durability）

SQL语句分类：

1、DQL：数据查询语句，select　　　　　　　　 （Data Query Language）

2、DML：数据操作语句，insert/delete/update　　（Data Manipulation Language）

3、DDL：数据库定义语句，create/drop/alter　　  （Data Denifition Language）

4、TCL：事务控制语言，commit/rollback　　　　（Trasactional Control Language）

5、DCL:  数据控制语言，grant ,revoke　　　　　（Data Control Language）

创建数据库：

create database bjpowernode;

导入数据库脚本

数据库脚本：以.sql后缀结尾的文件

导入数据库脚本：

1.    选定数据库

2.    source命令

mysql> use bjpowernode;

Database changed

mysql> source C:\Users\copywang\Desktop\bjpowernode.sql

数据库表的介绍：

mysql>show tables;

mysql>desc dept;

1、查询字段

select ename,sal,job from emp;

select * from emp;　　不建议使用，效率比前一个方法要低，因为要把*转成所有具体字段

2、进行数学运算，并对字段重命名

select ename,sal*12 as yearsal from emp;

3、条件查询

select ename,sal from emp where sal=5000;

MySQL支持自动类型转换，比如（但是其他数据库不支持，所以不要这么写）：

select ename,sal from empwhere sal=’5000’;

select job,ename from empwhere job=’MANAGER’;

这里使用单引号是SQL的标准写法，MySQL也支持使用双引号，开发中只使用单引号

select ename,sal from emp where sal!=5000;

select job,ename from emp where job<>’MANAGER’;

select ename,sal from empwhere sal>=1600;

select ename,sal from empwhere sal between 1600 and 3000;

select ename,sal from empwhere sal in (800,1600);

select ename,sal from empwhere job not in (‘MANAGER’,SALESMAN’);

select ename,comm fromemp where comm is null;

null不是数字，是空值，不能进行数学计算，因此不能用=号

select ename from empwhere ename like ‘%s%’;　　包含s

select ename from empwhere ename like ‘s%’;　　　s开头

select ename from empwhere ename like ‘_d%’;　　 第二位为d

select ename from empwhere ename like ‘%n_’;　　 倒数第二位为n

4、排序

select ename,sal from emporder by sal;　　　　　　默认asc升序

select ename,sal from emporder by sal desc;　　　  降序

select deptno,ename,salfrom emp order by deptno,sal desc;　　　　　　 多字段排序：先按照deptno升序排列，deptno相同的记录按照sal降序排列

select job,ename,sal fromemp where job=’MANAGER’ order by 3 asc;　　通过字段下标排序

5、数据处理函数

注意：数据处理函数有些是Mysql特有的，可能在其他数据库不起作用

select lower(ename) as lowername from emp;　　 lower转小写

select upper(ename) as uppername from emp;　　upper转大写

select substr(ename,1,1)as firstchar from emp;　　截取首字母

select substr(ename,3,3)as ename from emp;　　  取3,4,5个字符

select length(ename) as enameLength from emp;　求字符长度

select * from emp where ename=trim(‘     king     ‘);　　去掉前后空格（MySQL会自动去掉后面的空格）

select round(123.56);　　　　  输出是124

select round(123.56,0);　　　   输出是124

select round(123.56,1);　　　　输出是123.6

select round(123.56,-1);　　　  输出是120

select rand();　　　　　　　　随机数：输出【0,1】区间的随机数

select round(rand()*122);　　　生成【0,122】区间的任意整数（round用于取整）

case_when_then_else_end函数

示例：匹配工作岗位，为MANAGER时，薪水上调10%，为SALESMAN的时候，薪水上调50%

select ename,job,sal,
    (case job 
        when ‘manager‘ then sal*1.1
        when ‘salesman‘ then sal*1.5
        else sal
    end) as newsal
from emp;

注意：经测试，空格多了有时会报错

ifnull函数

select ename,(sal+comm)*12 as yearsal from emp;

select ename,(sal+ifnull(comm,0))*12 as yearsal from emp;

MySQL日期格式：

%Y：代表4位的年份　　　　　　　　　　%y：代表2位的年份

%m：代表月, 格式为(01……12)  　　  　　%c：代表月, 格式为(1……12)

%d ：代表日

%H：代表24小时制,格式为(00……23)  　　%h： 代表12小时制,格式为(01……12)  

%i  ： 代表分钟, 格式为(00……59) 

%S或%s：代表秒,格式为(00……59)  　　  　

%r：代表 时间,格式为12 小时(hh:mm:ss [AP]M)  　　%T：代表 时间,格式为24 小时(hh:mm:ss) 

select ename,hiredate from emp  where hiredate=str_to_date(‘12-03-1981‘,‘%m-%d-%Y‘);　　str_to_date字符串转日期：将字符串按照“月-日-年”的读取方式转换成1981-12-03日期

select ename,date_format(hiredate,‘%Y/%m/%d‘) as hiredate from emp;　　　　　　　　　   date_format日期转字符串：以特定格式展示

6、分组/聚合/多行处理函数

select distinct deptno,job from emp order by deptno;　　多字段去重

select job,max(sal) as maxsal from emp group by job order by maxsal desc;　　group by分组：计算每个工作岗位的最高薪水，并按从高到低排序

select deptno,job,max(sal) as maxsal from emp group by deptno,job order by deptno,maxsal desc;　　计算不同部门不同岗位的最高薪水，并按deptno由低到高、maxsal 由高到低排序

select job,max(sal) as maxsal from emp where job!=‘manager‘ group by job;  　　分组之前过滤：找出除manager以外的工作岗位的最高薪水

 select job,avg(sal) as avgsal from emp group by job having avgsal>2000;  　　having分组之后筛选：求每个岗位的平均薪水，要求显示平均薪水＞2000的岗位。having必须在group by后面出现，先分组再筛选

6、跨表查询

left join 　　：以左边为准，返回 左边的所有内容+右边共同内容

right join　  ：同理，以右边为准

(inner ) join ：返回共同的内容

年代分类：
　　SQL92
　　　　select ename,dname from emp as e,dept as d where e.deptno=d.deptno;
    SQL99（掌握）
        select 
            e.ename,d.dname 
        from 
            emp e
        join
            dept d
        on 
            e.deptno=d.deptno
        where
            数据过滤条件;
    

连接方式分类：
    内连接：省略了inner关键字
        等值连接
            查询员工所对应的部门名称
            select e.ename,d.dname from emp as e (inner) join dept as d on e.deptno=d.deptno;
        非等值连接
            查询员工薪水对应的薪水等级
            select ename,sal,grade from emp as e join salgrade as s on e.sal between s.losal and s.hisal;
        自连接:把一张表当两张表用
            查询员工所对应的领导名称：显示员工和领导的姓名（king没有领导，所以不显示）
            select a.ename,b.ename as leaderName from emp as a join emp as b on a.mgr=b.empno;
            
    外连接：省略了outer关键字
        左连接/右连接
            显示所有员工对应的领导名称（king没有领导，以null填充其领导）
            select a.ename,b.ename as leaderName from emp as a left/right (outer) join emp as b on a.mgr=b.empno;    
            
    全连接
            查询员工的部门名称，领导名称和薪水等级
            select 
                e.ename,d.dname,b.ename as leaderName,s.grade 
            from 
                emp e
            join
                dept d
            on e.deptno=d.deptno
            join
                emp b
            on e.mgr=b.empno
            join
                salgrade s
            on e.sal between s.losal and s.hisal;

等值连接

非等值连接

自连接

左连接

全连接

7、子查询

显示比平均薪资高的员工姓名及薪资

 select ename,sal from emp where sal>(select avg(sal) from emp);

找出每个部门的平均薪水，并且显示平均薪水的薪水等级

select deptno,avgsal,grade from
    (select deptno,avg(sal) as avgsal from emp group by deptno) as t
join salgrade as s    
on t.avgsal between s.losal and s.hisal;

8、其他查询（union、limit）

union：查询岗位为manager和salesman的员工

select ename,job from emp where job=‘manager‘ or job=‘salesman‘;
select ename,job from emp where job in(‘manager‘,‘salesman‘);
select ename,job from emp where job=‘manager‘ 　　union　　 select ename,job from emp where job=‘salesman‘;

limit：只在MySQL中起作用

9、表

MySQL常用数据类型

DDL数据定义语言:create、drop、alter

1、创建表

create table student(
    no int(4),
    name varchar(32),
    gender char(1) default ‘1‘,
    birth date,
    email varchar(128)
);

create table emp_bak as select * from emp;　　复制表

2、删除表

drop table student;
drop table if exists student;　　　　　　　　　 MySQL特有

3、修改表

create table student(
    no int(4),
    name varchar(32)
);

alter table student add email varchar(128);                //添加字段
alter table student modify no int(8);                    //修改字段数据类型
alter table student drop email;                            //删除字段
alter table student change name username varchar(32);    //修改字段名称

alter table student add (c1 char(1),c2 char(1));                //添加多列字段
alter table student change c1 c3 int(1),change c2 c4 char(2);    //修改多列字段
alter table student drop c3,drop c4;                            //删除多列字段

DML数据操控语言：insert,update,delete

3、插入语句

insert into student(no,name,gender,birth,email) values (1,‘zhangsan‘,‘1‘,‘1949-10-1‘,‘zhangsan@163.com‘);
insert into student(no,name,gender,birth,email) values (2,‘lisi‘,‘0‘,str_to_date(‘1949-10-1‘,‘%Y-%m-%d‘),‘lisi@163.com‘);
insert into student(no,name,gender,birth,email) values (3,‘wangwu‘,‘0‘,str_to_date(‘10-1-1949‘,‘%m-%d-%Y‘),‘wangwu@163.com‘);
insert into student(no,name,gender) values (4,‘lilei‘,‘1‘);
insert into student values (5,‘Jerry‘,‘1‘,‘1959-11-21‘,‘Jerry@163.com‘);    mysql特有，不建议用

4、修改语句

update student set birth=‘1994-05-10‘,email=‘lilei@163.com‘ where no=4;

5、删除语句

delete from student where no=4;
delete from student;                //删除表中所有记录

10、约束

常见的约束

a)        非空约束，not null

b)        唯一约束，unique

c)        主键约束，primary key

d)        外键约束，foreign key

e)        自定义检查约束，check（不建议使用）(在mysql中现在还不支持)

create table user(
       id int(4) PRIMARY key AUTO_INCREMENT,    //主键，自增（mysql特有）
       name varchar(32) not null,               //非空
       email varchar(128) unique                //唯一，不可重复
);

需求：设计数据库表用来存储学生和班级信息

需求分析：

　　a）学生表student包含:sno,sname,classno,cname

　　b）一个班级对应多个学生，一对多关系

create table student(
    id int(4) primary key auto_increment
    sno int(4) unique,
    sname varchar(32),
    classno int(4),
    cname varchar(32)
);

这样建表会造成数据冗余

create table student(
    sno int(4) primary key auto_increment,
    sname varchar(32),
    classno int(4),
    constraint fk foreign key(classno) references class(cno)    //constraint fk:给这个约束起名fk，不需要时可删除。该语句可省略。    字段classno源自class表中的cno
);

create table class(
    cno int(4) primary key,
    cname varchar(32)
);

Mysql

原文：https://www.cnblogs.com/little-monkey/p/11312333.html