Mysql

基本命令使用

1. 连接到数据库(本地）
   mysql -hlocalhost -u 账户 -p 密码

2. ** 查看mysql数据库服务器中的所有实例**
   show databases;

3. 创建mysql实例
   create database mydb;

4. 查看创建实例的命令
   show create database mydb;

5. 显示创建表的命令
   show create table emp;

6. 使用数据库实例
   use mydb;

7. 当输入了错误命令，需要取消时## DQL（查询语句）
   show databaes xxx \\c
   \\c表示取消当前的输入

8. 显示当前数据库实例的编码信息
   show variables like ‘%char%’;
   ‘%char%’ ：通配符，表示匹配包含了’char’字符的数据

9. 查看帮助信息
   \\h

10. 修改默认的分隔符
    delimiter $

11. 退出mysql命令行
    #方式一
    exit；
    #方式二
    quit；

12. 查看mysql服务器状态信息
    \\s

13. 显示实例下的所有表(前提先use过了)
    show tables;

SQL语句入门

1. DDL语句（数据定义语句）**

   数据定义语句主要应用于对数据表的结构操作：比如建表，删除表，修改表的结构等；DDL语句包含以下命令：

   create
   • drop
   • alter
   • add
   • mofidy

2. DML语句（数据操作语句）

   数据操作语句一般用于对的语句数据库表中数据进行更新操作，比如添加，删除，修改数据等，DML语句包含以下命令:

   insert
   • update
   • delete

3. DQL语句（数据查询语句）

   数据查询语句一般用于对数据库表中数据进行查询操作，命令主要包含：select

4. DCL语句（数据控制语句）

   数据控制语句一般用于对于数据库用户的权限管理，事务管理，DCL包含以下命令:

   grant
   • revoke
   • commit
   • rollback

DDL语句

创建一张数据库表

create table emp		     -- 创建表，名称叫emp(eno int,				 --创建eno列，类型是intename varchar(30),		 --创建ename列，类型是varchar长度是30字符job varchar(30),sex char(2) default \'1\', --创建sex列，类型是charsal double,birth date);

关于mysql中字符长度问题：**

如果是utf8编码下，默认的中文字符占3个字节；如果是gbk编码，默认的中文占2个字节

关于char类型和varchar类型：

• char类型是定长字符串，类似于java中String；长度一旦定义则无法改变，并且无论数据是否够指定长度，都会占满，不够的用空格填充；char类型一般应用于长度确定的字符串类型，比如：性别，手机号，身份证号等；
• varchar类型是可变长度字符串，类似java中StringBuffer；长度定义之后会根据实际填充的内容，选择占用多大空间，比较节省空间；varchar类型一般使用在长度不确定的字符串类型，比如:姓名，邮箱地址，评论信息等。

修改表结构

语法：
alter table 表名 [add/drop/modify/change] 列名称`

1. 新增一个列

   alter table emp add hiredate date;

2. 删除列

   alter table emp drop column birth;

3. 修改列的类型

   alter table emp modify sal varchar(20);

4. 修改列名称

   alter table emp change job  ejob varchar(30);

5. 修改表名称

DML语句

insert语句（插入）

语法：

insert into 表名(列名1，列名2...) values(列值1,列值2...)

1. 向表中添加数据(所有列)

   insert into tbemp values(1,\'james\',\'manager\',\'1\',8796,\'2018-01-22\',28);

2. 向某一些列插入值

   insert into tbemp(eno,ename,ejob,sal) values(4,\'softeem\',\'CEO\',1500000);

3. 同时插入多条数据

   insert into tbemp(eno,ename) values(5,\'来福\'),(6,\'狗蛋\'),(7,\'王二狗\');

   插入数据出现中文乱码时解决方案：

   由于安装mysql使用的是UTF8编码，但是cmd命令行中默认为GBK编码，因此在命令行中

   使用中文数据是会出现乱码；解决方式只需要在打开cmd命令行时输入以下命令：

   set names gbk；

   然后再进行插入操作即可解决（但是以上修改只对一次会话生效；如果只会再次使用cmd需要重新设置）

update语句（更新）

语法：

update 表名 set 列名1=值1,列名2=值2... where 条件

1. 修改表中的一条记录

   update tbemp  set hiredate=\'2006-03-01\',age=45 where eno=4;

2. 修改数据(将员工姓名叫旺财的人的月薪提高2000)

   update tbemp set sal=sal+2000 where ename=\'旺财\';

3. 修改数据(将员工生日为null的人的生日全部设置为2020-01-01)

   update tbemp set hiredate=\'2020-01-01\' where hiredate is null;

   注意事项：

   数据库中是否为空（null）的判断不能使用“=”或“!=”应该使用 is null或者 is not null

delete语句（删除）

语法：

delete from 表名 where 条件

1. 删除一行指定id的数据

   delete from tbemp where eno=5;

2. 删除所有月薪为null的员工

   delete from tbemp where sal is null;

   注意事项:

   实际开发中，数据积累不容易，一般不会轻易使用物理删除；大多数时候会选择使用逻辑删除；所谓逻辑删除实际就是在表中增加一个标识列，通过该列的取值标记该列是否应该被查询到

   因此针对删除需求，可以在表中新增一列用于标记该列是否被删除

   alter table tbemp add isdel tinyint;

DQL语句

SQL查询的语法：

selectdistinct查询列from表名称where查询条件group by分组依据having分组的查询条件order by排序字段limit结果限制

查询所有列

select * from emp;

查询部分列信息（查询员工的姓名，职位，薪资）

select ename,job,sal from emp;

insert into emp(eno,ename,job,age) values(21,\'孙悟空\',\'骨干员工\',33);-- 查询所有select * from emp;-- 查询指定列select ename,job,IFNULL(sal,0) from emp;-- 查询时使用运算操作select ename,IFNULL(sal,0)-1000 from emp;-- 显示所有职位,不能重复select distinct job from emp;/* 聚合函数count 统计函数sum		求和avg		求平均max		求最大min		求最小*/-- 查询表中一共有多少员工select count(*) FROM emp;-- 查询所有员工的总薪资select sum(sal) from emp;-- 查询所有员工的月薪平均值select avg(sal) from emp;-- 查询工资最低的员工薪资select min(sal) from emp;-- 查询工资最高的员工的薪资select max(sal) from emp;/*条件查询*/-- 查询年龄超过30的员工select COUNT(*) from emp where age>=30;-- 查询所有薪资超过3500 小于10000的员工信息select * from emp where sal >= 3500 and sal<10000;select * from emp where sal >= 3500 && sal<10000;select * from emp where sal between 3500 and 10000;-- 查询所有在 3，5，6三个部门的员工select * from emp where dno = 3 or dno = 5 or dno = 6;select * from emp where dno = 3 || dno = 5 || dno = 6;select * from emp where dno in (3,5,6);-- 查询所有不是经理的员工select * from emp where job != \'经理\';select * from emp where job <> \'经理\';-- 查询名字中带有\"卡\"的员工(模糊查询)select * from emp where ename like \'%卡%\';-- 查询姓\"李\"的所有员工select * from emp where ename like \'李%\';-- 查询只有三个字姓 \'卡\' 的员工select * from emp where ename like \'卡__\';/*\"%\"和\"_\" ：都是占位符，%用于匹配多个字符,“——”用于匹配一个字符*/-- 查询名字只包含两个字的员工select * from emp where ename like \'__\';-- 查询所有员工不是姓李的员工select * from emp where ename not like \'李%\';/*分组：group by*/-- 统计每一种职位的员工各有多少人select job,count(*) from emp group by job;-- 统计每个部门分别有多少人select dno,count(*) from emp group by dno;-- 查询每个部门月薪超过3500的员工有多少人,要求显示部门号，人数以及平均薪资？select dno,count(*) as\'总人数\',AVG(sal) as \'平均薪资\'from emp where sal>3500 group by dno;-- 使用别名select e.ename n,e.sal s,e.hiredate h from emp e;/*排序: order byasc 升序desc 降序*/-- 查询所有员工信息，并且按照月薪从高到低拍寻显示select * from emp order by sal desc-- 查询每个部门的平均薪资，并且按照平均薪资从高到低排序(显示：部门号，平均薪资)select dno,AVG(sal)  from emp  group by dno order by AVG(sal) desc;-- 在以上基础上要求显示平均薪资超过6000的部门号和平均薪资select dno,avg(sal) from emp group by dno having avg(sal)>=6000 order by avg(sal);/*分页：分页需求一般分为假分页(逻辑分页)和真分页(物理分页)这里需要使用到真分页,可以节省内存空间，直接在数据库里面对数据分页limit 一般后面带两个整数参数1：起始的查询位置2：本次查询的数据行数*/-- 显示结果中的前五条数据select * from emp limit 5;-- 以上操作等同于select * from emp limit 0,5;

/*多表联合查询1.等值连接2.内连接3.左外连接4.右外连接*/-- 显示所有员工和所在部门的信息（emp,dept）-- 笛卡尔积select * from emp,dept;-- 等值连接select * from emp,dept where emp.dno = dept.dno;select * from emp e,dept d where e.dno = d.dno;-- 查询所有员工的工号，姓名，职位，月薪和所在部门名称select e.eno,e.ename,e.job,e.sal,d.dname from emp e,dept d where e.dno = d.dno;-- 在以上基础上，显示员工薪资等级select e.eno,e.ename,e.job,e.sal,d.dname,s.level from emp e,dept d,sallevel s where e.dno = d.dno and e.sal between s.lowsal and s.hisal;-- 查询所有T8等级薪资的员工来自哪些部门，显示部门名和员工姓名，薪资select d.dname,e.ename,e.sal from emp e,dept d,sallevel s where e.dno = d.dno and e.sal between s.lowsal and s.hisal and s.`level` = \'T8\';-- 内连接(根据连接条件获取相交的部分，与等值连接结果一致)-- 显示所有员工的基本信息包含部门信息select * from emp e inner join dept d on e.dno = d.dno;-- 左外连接-- 以左表为基准去连接右表，无论左表是否存在与右表关联的数据，左表始终完全显示-- 查询出所有员工信息包括部门信息，同时要求显示不属于任何部门的员工select * from emp e left join dept d on e.dno = d.dno;-- 查询出所有员工和部门的信息，要求显示没有员工的部门信息select * from dept d left join emp e on e.dno = d.dno;-- 右外连接-- 以右表为基准去连接左表，无论右表是否存在与左表关联的数据，右表始终完全显示select * from emp e right join dept d on e.dno = d.dno;-- 查询与猪八戒同一个部门的其他员工信息select * from emp where emp.dno = (select dno from emp where emp.ename = \'猪八戒\');-- 自连接select e1.* from emp e1,emp e2 where e1.dno = e2.dno and e2.ename = \'猪八戒\';-- 使用内连接实现以上需求select e1.* from emp e1 inner join emp e2  on e1.dno = e2.dno and e2.ename = \'猪八戒\';-- 查询在研发部和行政部的所有员工select e.*,d.dname from emp e INNER JOIN dept d  on  e.dno = d.dno and(d.dname = \'研发部\' or d.dname = \'行政部\');select * from emp where dno in (select dno from dept where dname in(\'研发部\',\'行政部\'));-- 查询与猪八戒同一个部门且同一职位的员工信息select e.* from emp e,(select dno,job from emp where ename =\'猪八戒\') twhere e.dno = t.dno and e.job = t.job and e.ename !=\'猪八戒\';-- 查询研发部中比行政部中任何一个员工工资都高的员工信息-- sal一定要是数值型select e.*  from emp e,dept d  where e.sal >(select max(e.sal) from emp e,dept d where e.dno = d.dno and d.dname = \'研发部\')and d.dname = \'行政部\' and e.dno = d.dno ;-- 找出部门1中所有经理，部门中所有普通员工以及既不是经理又不是普通员工-- 但其薪金大于或等于6000的所有员工的详细资料select * from emp where dno=20 and job=\'普通员工\'UNION  -- 联合其他查询结果select * from emp where dno=10 and job=\'经理\'unionselect * from emp where job not in (\'普通员工\',\'经理\') and sal > 6000;

查询注意事项:

• 对于任何查询，明确几个目标：
• 查询列
• 查询目标表
• 查询条件
• 查询方式：

多表查询:等值连接内连接自连接外连接(左外连接，右外连接)子查询单行子查询多行子查询多列子查询（虚拟表）

对于同一个查询需求可以使用多种手段实现，但是需要考虑效率查询语句优化：尽量避免子查询避免使用“*”对查询结果尽量使用limit显示

维护数据完整性之约束

在数据库中维护数据完整性的解决方案有两种：

1. 约束（constraint）
2. 触发器（trigger）

约束（Constraint）

约束是通过对数据表中的字段使用以一些特殊用途的关键字进行限定，从而使得该列的数据不能随意填写，以此来保障数据的完整性；数据库中一共包含以下5种约束：

1. 主键约束（primary key）
2. 外键约束（foreign key）
3. 唯一约束（unique）
4. 检查约束（check） Mysql暂时不生效
5. 不为空约束（not null）

主键约束（primary key）

主键约束一般用于一张表中的标识列（该列数据唯一且不为空）；每一张表都应该存在一个主键，主键可以用于一个列，也可以应用于多个列

设置主键的方式有三种：

1. 直接在建表时，在的字段后使用：

create table tbuser(id int primary key   --设置主键....)

1. 在建表时，所有字段的末尾使用

create table tbuser(id int auto_increment,username varchar(20),password varchar(30),primary key(id))

1. 表结构已经创建完成，通过DDL语句设置

alter table tbuser add constraint pk_id primary key(id);

注意事项：1. 主键列一般用于标识列（不能重复，且不为空）2. 尽量避免使用联合主键（设置多个列同时为主键）3. 任何表都应该存在主键列

外键约束（foreign key）

外键约束一般用于对一个表与另一个表进行关联时的依据，通常会在表中使用foreign key建立外键；外键必然是另一张表的主键，而另一张就称之为主表，添加外键的表称之为从表。

设置外键的方式有两种：

1. 在建表时设置：

create table tbuser(id int primary key auto_increment,username varchar(30) unique not null,password varchar(64) not null default \'123456\',sex int check(sex in(0,1)),tid int,foreign key(tid) REFERENCES team(id) -- 外键设置)

2.表已经创建完成之后，通过DDL设置

alter table tbuser add constraint fk_tid foreign key(tid) references team(id);

唯一约束（unique）

唯一约束用于设置表中指定列是唯一的（不可重复）；常见于用于表中的用户名列，分类表中类别名列等，使用方式：

username varchar(30) unique not null,

不为空约束（not null）

设置表中指定列必须给定值，不允许为null

检查约束（check）

检查约束在mysql中还未生效，如果需要对字段进行检查约束，可以考虑使用enum类型。

mysqldump与mysqladmin

mysqldump(备份)

1. 备份指定实例到指定目录中

mysqldump -uroot -p123456 mydb > d:/mydb.sql

1. 从指定的sql文件中恢复备份数据

source d:/mydb.sql

mysqladmin

1. 使用mysqladmin创建一个数据库实例

   mysqladmin -uroot -p123456 create mydb2

2. 使用mysqladmin删除一个数据库实例

   mysqladmin -uroot -p123456 drop mydb2

3. 修改密码

   mysqladmin -uroot -p password root

   将root用的密码改为\”root\”

MySql数据类型

mysql数据库中支持的数据类型分为以下几个大类：

• 数值类型整型
• 浮点型

数值类型

mysql中数值类型主要包含以下两大类

• 整型
• 浮点型

取值范围：

常用数值类型主要包含以下几个:

1. int
2. double
3. decimal

字符串类型

注意事项：

在使用中文字符串时，如果数据库编码是gbk,则每个中文字符占2个字节；如果是utf8编码，则每个中文字符占3个字节

关于取值返回:

char(m)

varchar(m)

其中m表示的字符个数

常见的字符串类型：

• char
• varchar
• text

blog和clob

blob（Binary Large object）二进制大对象（视频，图片，音频）

clob（Character Large Object）字符大对象（大文本）

enum类型

枚举类型，用于限定该列只能选择枚举中其中一个值

日期时间类型

获取当前的系统时间

select now()

常用日期类型：

• date 用于表示日期 如：2020-01-01
• datetime 用于表示日期时间 如：2020-01-01 10:11:12
• timestamp 用于表示时间戳，格式等同datetime，支持默认值 CURRENT_TIMESTMP

​ 另外该类型也支持自动更新（on update CURRENT_TIMESTAMP）当前行 数据更新时，该列也会自动更新为最新的时间

注意事项：

• 一张表中只能有一个字段的timestamp可以设置默认值