数据库MySQL常用命令以及教程

数据库简介

什么是DB

• DataBase数据库，代表保存到磁盘中的文件集

什么是DBMS

• DataBaseManagementSystem 数据库管理系统， 是用于管理数据库文件的软件系统，常见的DBMS：MySQL、Oracle、DB2、SQLServer、SQLite等

数据库的分类 （了解）

1. 关系型数据库：经过数学理论验证可以保存现实生活中存在的任何关系，关系型数据库以表为存储单位。
2. 非关系型数据库：用于处理某些特殊需求，比如：解决数据缓存问题，redis数据库就是一款解决数据缓存的数据库，保存数据的方式是以键值对

SQL

• 什么是SQL：Structured Query Language 结构化查询语言，用于程序员和数据库管理系统进行交流的语言

在终端或命令行中和数据库软件建立连接

mysql -uroot -p

数据库相关SQL

1. 查看所有数据库

• 格式: show databases;

2. 创建数据库

• 格式：create database 数据库名;

create database db1;

3. 查看数据库详情

• 格式：show create databse 数据库名;

show create database db1;

4. 创建数据库指定字符集

• 格式：create database 数据库名 character set utf8/gbk;

create database db2 character set gbk;

5. 删除数据库

• 格式： drop database 数据库名;

drop database db1;

6. 使用数据库

• 格式： use 数据库名;

use db1;

表相关的SQL 前提需要使用了某个数据库

1. 查询所有表 show tables;
2. 创建表

• 格式： create table 表名(字段1名 字段1类型,字段2名 字段2类型);

create table person(name varchar(10),age int);

• 练习：创建一个学生表student有学号id 姓名name 语文chinese数学math英语english

create table student(id int,name varchar(10),chinese int,math int,english int);

3. 查看表详情
   show create table person;

• 表引擎：
  1. innodb：支持数据库的高级操作包括：事务、外键等
  2. myisam：只支持数据基础的增删改查操作

4. 创建表指定引擎和字符集

• 格式： create table 表名(字段1名 字段1类型,字段2名 字段2类型) engine=myisam/innodb charset=utf8/gbk;

create table t1(name varchar(10),age int) engine=myisam charset=gbk;

• 练习：创建2个数据库分别是 mydb1和mydb2 在第一个数据库里面创建员工表emp 里面有姓名name年龄age工资sal字段，在mydb2里面创建英雄表hero，字段有名字name 年龄age 英雄类型type 字符串类型 并且指定此表的引擎为myisam 字符集为gbk

create database mydb1;
	use mydb1;
	create table emp(name varchar(10),age int,sal int);
	create database mydb2;
	use mydb2;
	create table hero(name varchar(10),age int,type varchar(10))engine=myisam charset=gbk;

5. 查看表字段

• 格式：desc 表名;
  desc hero;

6. 删除表

• 格式： drop table 表名;
  drop table hero;

7. 修改表名

• 格式： rename table 原名 to 新名;

create table t1(name varchar(10));

rename table t1 to t2;

8. 修改表引擎和字符集

• 格式： alter table 表名 engine=myisam/innodb charset=utf8/gbk;

alter table t2 engine=myisam charset=gbk;

9. 添加表字段

• 最后面添加格式： alter table 表名 add 字段名 字段类型;
• 最前面添加格式： alter table 表名 add 字段名 字段类型 first;
• xxx后面添加格式： alter table 表名 add 字段名 字段类型 after xxx;

    alter table t2 add age int;
	alter table t2 add sal int first;
	alter table t2 add id int after name;

10. 删除表字段

• 格式： alter table 表名 drop 字段名;

alter table t2 drop id;

11. 修改字段名和类型

• 格式： alter table 表名 change 原名 新名 新类型;

alter table t2 change sal salary varchar(10);

12. 修改字段类型和位置

• 格式： alter table 表名 modify 字段名 新类型 first/after xxx;

alter table t2 modify salary int after age;

数据相关的SQL

create table person(id int,name varchar(10),age int);

1. 插入数据

• 全表插入格式： insert into 表名 values (值1,值2,值3);

insert into person values(1,'Tom',20);

• 指定字段格式： insert into 表名 (字段1,字段2) values(值1,值2);

insert into person (id,name) values(2,'Jerry');

• 批量插入：
  insert into 表名 values (值1,值2,值3),(值1,值2,值3),(值1,值2,值3),(值1,值2,值3);
  insert into 表名 (字段1,字段2) values(值1,值2),(值1,值2),(值1,值2);

insert into person values(3,'a1',25),(4,'a2',26);
insert into person (id,name) values(5,'b1'),(6,'b2');

2. 查询数据

• 格式：select 字段信息 from 表名 where 条件;

    select * from person;
	select name from person;
	select name,age from person;
	select * from person where name='Tom';

3. 修改数据

• 格式：update 表名 set 字段名=值,字段名=值 where 条件;

update person set age=18 where name='Jerry';

-修改id大于4的年龄为35

update person set age=35 where id>4;

4. 删除数据

• 格式：delete from 表名 where 条件;

delete from person where name='a2';

-删除年龄大于25岁的数据

delete from person where age>25;

-删除所有数据

delete from person;

主键约束

• 什么是主键：用于表示数据唯一性的字段称为主键
• 什么是约束： 就是创建表的时候给字段添加的限制条件
• 主键约束： 插入数据必须是唯一且非空的
• 格式：

      create table t1(id int primary key,name varchar(10));
	  insert into t1 values(1,'刘备');
	  insert into t1 values(1,'关羽');   //报错 不能重复
	  insert into t1 values(null,'关羽');//报错 不能为null

主键约束+自增

• 自增数值只增不减
• 从历史最大值基础上+1
• 格式：

      create table t2(id int primary key auto_increment,name varchar(10));
	   insert into t2 values(null,'悟空');   //1
	   insert into t2 values(null,'八戒');  //2
	   insert into t2 values(10,'八戒');   //10
	   insert into t2 values(null,'沙僧');//11
	   delete from t2 id>=10;
	   insert into t2 values(null,'沙僧');//12

注释

• 对表的字段进行描述

  create table t3(id int primary key auto_increment comment '主键字段',name varchar(10) comment '这是姓名');

'和`的区别

• ’ 是用来修饰字符串的
• ` 是用来修饰表名和字段名的 可以省略

  create table `t4`(`id` int,`name` varchar(10));

数据冗余

• 如果数据库中的表设计不够合理，随着数据量的增长出现大量的重复数据，这种重复数据的现象称为数据冗余，通过拆分表的形式解决此问题

事务

• 什么是数据库中的事务？
  事务是数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL语句全部执行成功或者全部执行失败
• 事务相关指令：

1. 开启事务 begin;
2. 提交事务 commit;
3. 回滚事务 rollback;

      create table user(id int primary key auto_increment,name varchar(10),money int,state varchar(5));

	   insert into user values(null,'钢铁侠',5000,'正常'),(null,'绿巨人',500,'正常'),(null,'超人',100,'冻结');

• 钢铁侠给绿巨人转账1000

     update user set money=money-1000 where id=1 and state='正常';
	 update user set money=money+1000 where id=2 and state='正常';

• 钢铁侠给超人转账1000

      update user set money=money-1000 where id=1 and state='正常';
	  update user set money=money+1000 where id=3 and state='正常';

• 在事务保护下执行:钢铁侠给超人转账1000

      begin; //开启事务
	  update user set money=money-1000 where id=1 and state='正常';

-在这个时间点再开一个窗口检查数据库里面的数据是否改变

      update user set money=money+1000 where id=3 and state='正常';
	  rollback;  //转账失败 回滚事务

• 在事务保护下执行:钢铁侠给绿巨人转账1000

      begin;  //开启事务
	  update user set money=money-1000 where id=1 and state='正常';
	  update user set money=money+1000 where id=2 and state='正常';
	  commit; //转账成功  提交事务

• savepoint; 保存回滚点

      begin;
	  update user set money=2001 where id=1;
	  savepoint s1;
	  update user set money=2002 where id=1;
	  savepoint s2;
	  update user set money=2003 where id=1;
	  rollback to s2;

SQL分类

1. DDL： Data Definition Language
   数据定义语言，包括：create 、drop 、alter、 truncate ,不支持事务

• truncate table 表名：删除表并创建新表 自增数值清零

2. DML： Data Manipulation Language
   数据操作语言，包括：insert、delete、update、select(DQL)，支持事务
3. DQL: Data Query Language
   数据查询语言，包括：select
4. TCL： Transaction Control Language
   事务控制语言，包括：begin、commit、rollback、savepoint xxx、rollback to xxx
5. DCL： Data Control Language
   数据控制语言，分配用户权限相关SQL

数据类型

1. 整数：常用类型int(m) 和 bigint(m), m代表显示长度，需要结合zerofill关键字使用
   create table t_int(id int(5) zerofill);
   insert into t_int values(18);
2. 浮点数：常用类型 double(m,d) m代表总长度 d代表小数长度 25.321 m=5 d=3 decimal超高精度浮点数，当涉及超高精度运算时使用
3. 字符串： char(m)固定长度 执行效率高 最大长度255 varchar(m)可变长度 节省资源 最大65535 超高255建议使用text， text可变长度 最大65535
4. 日期：date 只能保存年月日 ，time 只能保存时分秒 ，datetime 最大值 9999-12-31 默认值为null，timestamp 最大值2038-1-19 默认值 当前的系统时间

    create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
	insert into t_date values('2019-2-20',null,null,null);
	insert into t_date values(null,'16:32:20','2019-2-20 16:32:22',null);

5. 其它类型

导入*.sql文件

1. windows系统 把下载的文件放在d盘下面
   在终端中执行以下指令
   source d:/tables.sql;
2. linux 系统 把文件放在桌面
   在终端中执行以下指令
   source /home/soft01/桌面/tables.sql;

• 导入后执行show tables; 查看是否有四张表 有说明搞定

is null 和 is not null

1. 查询奖金为null的员工信息

select * from emp where comm is null;

2. 查询mgr不为null值得员工姓名

select ename from emp where mgr is not null;

别名

    select ename as '姓名',sal as '工资' from emp;
	select ename '姓名',sal '工资' from emp;
	select ename 姓名,sal 工资 from emp;

去重 distinct

select distinct job from emp;

比较运算符 > < = >= <= !=和<>

1. 查询工资高于2000的所有员工编号empno,姓名ename，职位job，工资sal

   select empno,ename,job,sal from emp where sal>2000;

2. 查询工资小于等于1600的所有员工的编号，姓名，工资

  select empno,ename,sal from emp where sal<=1600;

3. 查询部门编号是20的所有员工姓名、职位、部门编号deptno

  select ename,job,deptno from emp where deptno=20;

4. 查询职位是manager的所有员工姓名和职位

 select ename,job from emp where job='manager';

5. 查询不是10号部门的所有员工编号，姓名，部门编号（两种写法）

       select empno,ename,deptno from emp where deptno!=10;
	   select empno,ename,deptno from emp where deptno<>10;

6. 查询t_item表单价price等于23的商品信息

       select * from t_item where price=23;
	   select * from t_item where price=23 \G;

7. 查询单价不等于8443的商品标题title和商品单价

 select title,price from t_item where price!=8443;

and和or

• and 并且&& 需要同时满足多个条件时使用
• or 或|| 需要满足多个条件中的某一个条件时使用

1. 查询20号部门工资大于2000的员工信息

   select * from emp where deptno=20 and sal>2000;

2. 查询10号部门奖金为null的员工信息

select * from emp where deptno=10 and comm is null;

3. 查询有上级领导mgr并且职位是manager的员工信息

select * from emp where mgr is not null and job='manager';

4. 查询20号部门或者工资小于1000的员工信息

 select * from emp where deptno=20 or sal<1000;

5. 查询名字为king和james的员工信息

select * from emp where ename='king' or ename='james';

in 和 not in

1. 查询工资为5000，950，3000的员工信息

   select * from emp where sal=5000 or sal=950 or sal=3000;
	   select * from emp where sal in (5000,950,3000);

2. 查询James、king、ford的工资和奖金

  select sal,comm from emp where ename in('james','king','ford');

3. 查询工资不是5000，950，3000的员工信息

   select * from emp where sal not in (5000,950,3000);

between x and y 包括xy

1. 查询工资在2000到3000之间的员工信息

select * from emp where sal>=2000 and sal<=3000;
select * from emp where sal between 2000 and 3000;

2. 查询商品表单价在50到100之间的商品名称和商品单价

select title,price from t_item where price between 50 and 100; 

3. 查询工资小于2000并且大于3000的员工信息

select * from emp where sal not between 2000 and 3000;

模糊查询 like

• _代表单个未知字符
• %代表0或多个未知字符
  -举例：
  以a开头 a%
  以b结尾 %b
  包含c %c%
  第一个字符是a 倒数第二个字符是b a%b_
  匹配163邮箱 %@163.com
  任意邮箱 %@%.com
• 案例：

1. 查询员工姓名以k开头的员工信息

select * from emp where ename like 'k%';

2. 查询标题包含记事本的商品标题和商品单价

select title,price from t_item where title like '%记事本%';

3. 查询单价低于100的记事本

select * from t_item where price<100 and title like '%记事本%';

4. 查询有赠品的dell商品(卖点sell_point包含赠字,标题包含dell)

select * from t_item where sell_point like '%赠%' and title like '%dell%';

5. 查询单价在100到200之外的联想商品

select * from t_item where price not between 100 and 200 and title like '%联想%';

6. 查询分类category_id为238和917的齐心商品

select * from t_item where category_id in(238,917) and title like '%齐心%';

7. 查询商品标题中不包含得力的商品信息

select * from t_item where title not like '%得力%';

8. 查询员工姓名包含a并且工资低于3000的员工姓名和工资

select ename,sal from emp where ename like '%a%' and sal<3000;

9. 查询员工姓名不是以k开头并且有奖金的员工信息

select * from emp where ename not like 'k%' and comm>0;

10. 查询30号部门职位包含man的员工姓名、职位、部门编号

select ename,job,deptno from emp where deptno=30 and job like '%man%';

排序

• order by 字段名 asc/desc;

1. 查询员工姓名和工资降序

  select ename,sal from emp order by sal desc;

2. 查询30号部门的员工信息 工资降序排序

select * from emp where deptno=30 order by sal desc;

3. 查询名字中包含a并且工资大于1000的员工信息按照工资升序排序

 select * from emp where ename like '%a%' and sal>1000 order by sal; 

4. 查询所有员工信息按照部门编号升序排序

 select * from emp order by deptno,sal desc;

5. 查询带燃字的商品单价升序排序

  select * from t_item where title like '%燃%' order by price;

6. 查询所有dell商品标题，分类category_id，单价 按照分类升序排序，单价降序排序

   select title,category_id,price from t_item where title like '%dell%' order by category_id,price desc;

分页查询

• limit 跳过的条数,请求的条数(每页的条数)

1. 查询员工表工资最高的前五条数据

 select * from emp order by sal desc limit 0,5;

-以上数据的第二页数据

 select * from emp order by sal desc limit 5,5;

2. 查询商品表单价升序第三页每页四条数据

   select * from t_item order by price limit 8,4;

• limit (页数-1)*每页的条数,每页的条数

• 子查询(嵌套查询)

1. 查询emp表中工资最高的员工信息

        select max(sal) from emp;
	    select * from emp where sal=5000;

-把上面两条嵌套到一起

  select * from emp where sal=(select max(sal) from emp);

关联查询

• 同时查询多张表的数据的查询方式称为关联查询
• 关联查询时必须写关联关系，如果不写会得到两张表的乘积，这个乘积称为笛卡尔积。这是一个错误的查询结果切记工作中不要出现

1. 查询每个员工的姓名和所属部门的名字

        select  e.ename,d.dname
	    from emp e,dept d
	    where e.deptno=d.deptno;

等值连接和内连接

• 关联查询的两种查询方式：

1. 等值连接： select * from A,B where A.x=B.x and A.age=18;
2. 内连接：select * from A join B on A.x=B.x where A.age=18;
3. 外链接（左外和右外）： select * from A left/right join B on A.x=B.x where A.age=18;

• 查询每个员工的姓名和对应的部门名称（使用的内连接）如果查询的是两张表的交集数据使用等值连接或内连接（推荐）

        select e.ename,d.dname
	    from emp e join dept d on e.deptno=d.deptno;

• 查询所有的部门名称和对应的员工姓名(使用外链接)如果查询的数据是一张表的全部数据和另外一张表的交集数据使用外链接

        select d.dname,e.ename
	    from emp e right join dept d
	    on e.deptno=d.deptno;

• 查询工资低于2000的员工姓名、工资和部门信息

       select e.ename,e.sal,d.*
	   from emp e join dept d
	   on e.deptno=d.deptno
	   where e.sal<2000;

视图

• 什么是视图：视图和表都是数据库中的对象，视图可以理解成是一张虚拟的表，视图本质就是取代了一段SQL查询语句。

• 为什么使用视图：使用视图可以起到SQL语句重用的作用，提高开发效率，还可以隐藏敏感信息

• 创建视图格式：
  create view 视图名 as (子查询);

约束

• 什么是约束：约束就是给字段添加的限制条件

非空约束 not null

• 字段的值不能为null

 create table t1(id int,age int not null);

-测试：

       insert into t1 values(1,20); //成功
	   insert into t1 values(2,null); //失败 不能为null值

唯一约束 unique

• 字段的值不能重复

  create table t2(id int,age int unique);

-测试：

       insert into t2 values(1,20);//成功
	   insert into t2 values(2,20);//失败 不能重复

默认约束 default

• 给字段设置默认值

  create table t3(id int,age int default 20);

-测试：

       insert into t3 (id) values(1); //触发默认值生效
	   insert into t3 values(2,50); //不触发
	   insert into t3 values(3,null);//不触发

主键约束 primary key

• 主键：表示数据唯一性的字段称为主键
• 主键约束：唯一且非空

外键约束

• 外键：用于建立关系的字段
• 外键约束： 为了保证两张表之间建立正确的关系，外键字段的值可以为null，可以重复，不能是另外一张表中不存在的数据，建立好关系后被依赖的数据不能先删除，被依赖的表不能先删除
• 如何使用外键约束
  1. 先创建部门表

  create table dept(id int primary key auto_increment,name varchar(10));

2. 创建员工表

create table emp(id int primary key auto_increment,name varchar(10),deptid int,constraint fk_dept foreign key(deptid) references dept(id));

-格式介绍：

   constraint 约束名称 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名)  

-测试：

        insert into dept values(null,'神仙'),(null,'妖怪');
	    insert into emp values(null,'悟空',1);//成功
	    insert into emp values(null,'赛亚人',3);//报错
	    delete from dept where id=1;//报错 因为有依赖数据
	    drop table dept;//报错 因为有依赖数据
	    drop table emp;//成功
	    drop table dept;//成功 再次删除成功因为没有依赖表和数据了

索引

• 什么是索引：索引是数据库中用于提高查询效率的技术，工作原理类似于目录

• 为什么使用索引：如果不使用索引，数据会零散的保存到每一个磁盘块中，查找数据时需要逐个遍历每一个磁盘块 直到找到数据为止，使用索引后磁盘块会以树桩结构保存，查找数据时可以大大减低磁盘块的访问量，从而提高查询效率（选择的作为索引的字段最好是重复出现比较少的字段）

• 有索引就一定好吗？
  不是，如果数据量比较小，有索引反而会降低查询效率
  -测试没有索引的查询效率

select * from item2 where title='100'; //1.17秒

创建索引

• 格式： create index 索引名 on 表名(字段名(?字段长度));

  create index i_item_title on item2(title);

-再次测试：

   select * from item2 where title='100'; //0.03秒

• 索引是越多越好吗？
  不是，只针对常用的查询字段创建索引,因为索引会占磁盘空间

查看索引

• 格式：show index from 表名;

   show index from item2;

删除索引

• 格式: drop index 索引名 on 表名;

   drop index i_item_title on item2;

复合索引

• 通过多个字段创建的索引称为复合索引

 create index i_item_title_price on item2(title,price);

事务

• 数据库中执行同一业务多条SQL语句的工作单元，可以保证多条SQL全部执行成功或全部执行失败
• 事务的ACID特性，此特性是保证事务正确执行的四大基本要素

1. Atomicity：原子性，最小不可拆分，保证全部成功或全部失败
2. Consistency：一致性，保证从一个一致状态到另一个一致状态
3. Isolation：隔离性，多个事务直接互不影响
4. Durability：持久性，事务提交后数据持久保存到磁盘中

• 事务相关指令： begin,commit,rollback,savepoint xxx,rollback to xxx;