mysql数据库操作指令大全

1. 查询所有数据库

格式: show databases;

1. 创建数据库

格式: create database 数据库名 charset=utf8/gbk;

1. 查询数据库信息

格式: show create database 数据库名;

1. 删除数据

格式: drop database 数据库名;

1. 使用数据库

执行表相关和数据相关的 SQL 语句之前必须使用了某个数据库

格式: use 数据库名;

表相关

1. 创建表: create table t1(name varchar(20),age int) charset=utf8/gbk;

2. 查询所有: show tables;

3. 查询表信息: show create table t1;

4. 查询表字段: desc t1;

5. 删除表: drop table t1;

6. 修改表名: rename table t1 to t2;

7. 添加表字段: alter table t1 add 字段名 类型 first/after xxx;

8. 删除表字段 : alter table t1 drop 字段名;

9. 修改表字段: alter table t1 change 原名 新名 新类型;

数据相关

1. 插入数据(增)

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

 指定字段插入格式: insert into 表名(字段 1,字段 2) values(值 1,值 2);

 批量插入数据:

insert into person values('liubei',38),('guanyu',50),('zhaoyun',40);

insert into person(name) values('lilei'),('hanmeimei');

 插入中文:

insert into person values('刘德华',17);

如果执行上面的 SQL 语句报错,错误中包含 16 进制的提示信息 则执行 set names gbk;解决

1. 查询数据

 格式: select 字段信息 from 表名 where 条件;

1. 修改数据

 格式: update 表名 set 字段名=值,字段名=值 where 条件;

1. 删除数据

 格式: delete from 表名 where 条件;

关闭时间戳自动更新:

alter table product change created created timestamp not null default current_timestamp;

打开时间戳自动更新:

alter table product change created created timestamp not null default current_timestamp on update current_timestamp;

主键约束+自增 primary key auto_increment

id int primary key auto_increment;

between x and y 包含x和y

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

select * from emp where sal between 2000 and 3000;

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

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

having

• where 后面只能写普通字段条件, 不能写聚合函数条件

• having关键字专门用来写聚合函数条件的, 并且需要和分组group by结合使用, 写在分组的后面

查询每个部门的平均工资,要求平均工资大于2000;

select dept_id,avg(sal) a from emp group by dept_id having a>2000;

子查询(嵌套查询)

• 把一条SQL语句嵌套到另外一条SQL语句中,称为子查询

1. 查询工资高于2号部门平均工资的员工信息

select avg(sal) from emp where dept_id=2;

select * from emp where sal>(select avg(sal) from emp where dept_id=2);

数值计算

1. 查询每个人的姓名,工资和年终奖(5个月的工资)

   select name,sal,5*sal 年终奖 from emp;

2. 给每个2号部门的员工涨薪5块钱

   update emp set sal=sal+5 where dept_id=2;

   in

   • 查询某个字段的值为多个值的时候使用in

   1.查询工资等于3000,1500和5000的员工信息

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

   2.查询人事和销售的信息

   select * from emp where job in('人事','销售');

   3.查询工作不是人事也不是销售的信息

   select * from emp where job not in('人事','销售');

   去重distinct

   1.查询1号部门中出现了哪几种不同的工作

   select distinct job from emp where dept_id=1;

   2.查询员工表中出现了哪几种不同的部门id

   select distinct dept_id from emp;

   模糊查询 like

   • _ : 代表1个未知字符

   • %: 代表0或多个未知字符

   • 举例:

   • • 以x开头 x%

     • 以x结尾 %x

     • 以x开头y结尾 x%y

     • 包含x %x%

     • 第二个字符是x _x%

     • 第三个是x倒数第二个是y _x%y

   1.查询姓孙的员工姓名

   select name from emp where name like "孙%";

   排序 order by

   • 格式: order by 字段名 asc升序(默认)/desc降序

   1.查询每个员工的姓名和工资,并按照工资升序排序

   select name,sal from emp order by sal;

   select name,sal from emp order by sal asc;

   select name,sal from emp order by sal desc; //降序

   2.查询工资高于2000的员工姓名和工资, 按照工资降序排序

   select name,sal from emp where sal>2000 order by sal desc;

   3.查询每个员工的姓名,工资和部门id并且按照部门id升序排序,如果部门id一致则按照工资降序排序

   select name,sal,dept_id from emp order by dept_id,sal desc;

   分页查询 limit

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

   • 跳过的条数=(请求的页数-1)*每页的条数

   • 举例:

   • • 第1页的5条数据 limit 0,5

     • 第2页的5条数据 limit 5,5

     • 第3页的5条数据 limit 10,5

     • 第4页的10条数据 limit 30,10

     • 第8页的7条数据 limit 49,7

     • 第3页的9条数据 limit 18,9

   1. 查询id,姓名,工资和工作,按照工资升序排序 请求第1页的5条数据

   select id,name,sal,job from emp order by sal limit 0,5;

   关联关系

   • 创建表时,表和表之间存在的业务关系

   • 存在哪几种关系?

     • 一对一:有AB两张表, A表中的一条数据对应B表中的一条数据, 同时B表中的一条数据也对应A表中的一条数据.

     • 一对多:有AB两张表, A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据对应A表中的一条数据.

     • 多对多:有AB两张表, A表中的一条数据对应B表中的多条数据, 同时B表中的一条数据也对应A表中的多条数据.

   • 如何建立关系:

     • 一对一: 在AB两张表中任意一张表里面添加一个建立关系的字段指向另外一张表的主键

     • 一对多: 在AB两张表中,表示多的表中添加建立关系的字段指向另外一张表的主键

     • 多对多: 在单独的关系表中添加两个建立关系的字段指向另外两张表的主键

   等值连接

   • 格式: select 字段信息 from A,B where A.x=B.x(关联关系) and 其它条件

   1. 查询每个员工的姓名和对应的部门名

   select e.name,d.name from emp e,dept d 
   where e.dept_id=d.id;

   内连接

   • 内连接和等值连接作用一样, 查询到的是两个表之间的交集数据(有关系的数据)

   insert into emp(name,sal) values('灭霸',5);

   • 内连接格式: select 字段信息 from A join B on A.x=B.x(关联关系) where 其它条件;

   1. 查询每个员工的姓名和对应的部门名

   select e.name,d.name
   from emp e join dept d on e.dept_id=d.id;

   外连接

   • 内连接和等值连接作用一样, 查询到的是两个表之间的交集数据

   • 外连接查询的是一张表的全部和另外一张表的交集

   • 格式: select 字段信息 from A left/right join B on A.x=B.x where 其它条件;

   1. 查询所有员工姓名和对应的部门名

   insert into emp(name,sal) values('灭霸',5);

   select e.name,d.name
   from emp e left join dept d on e.dept_id=d.id;