MYSQL基本操作指令

基本要求

1.关键词之间有空格；
2.sql中可以有多个换行，以“;”结尾，并且关键词不区分大小写；

基本操作之数据库创建与删除

1.查询所有数据库：

show databases;

2.创建数据库：

create database 数据库名称（例：create database user;）；

3.查看创建的数据库：

show create database 创建的数据库名称（例：show create database user;）；

4.如要设置数据库字符编码：

create database 数据库名称 character set utf8/gbk;

5.使用数据库：

use 数据库名称;

6.删除数据库：

drop database 数据库名称（例：drop database user;）；

基本操作之表的创建与删除

1.创建表：

create table 表名(字段名1 字段 类型,字段名2 字段2 类型…);
（例:create table student(name varchar(20),age int);）

2.展出所有表：

show tables;

3.表引擎：

innodb（默认）：支出数据库的高级操作（外键，事务等）；
创建表时制定引擎和字符集，格式：create table 表名(字段名1 字段1 类型，字段名2
字段2 类型…)engine=innodb/myisam(default?)charset=utf8/gbk;

4.查看表字段：

格式：desc 表名;（例：desc mytable;）；

5.删除表：

格式：drop table 表名;（例：drop table mytable;）；

6.修改表名：

格式：rename table 原名 to 新名;

7.修改引擎和字符集

格式:alter table表名engine=innodb/myisam charset=utf8/gbk;
例如: alter table emp engine=myisam charset=gbk;

表字段的基本操作

1.给表添加字段:

最后添加:ater table 表名 add 字段名 字段类型;
最前面添加:alter table 表名 add 字段名 字段类型 first;
xxx的后面;alter table 表名 add 字段名 字段类型 after xxx;

2.删除表字段

格式:alter table 表名 drop 字段名;
如:alter table users drop age;

3.修改字段名和类型

格式:alter table 表名 change 原名 新名 新类型;
例如:alter table users change age theage int;

4.修改字段类型和位置

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

基本操作之数据的插入、查询与删除

1.添加数据

全表插入格式: insert into 表名 values(值1,值2);values里面的值数量
和顺序一定要和表字段一致
指定字段插入格式:insert into 表名(字段名1,字段名2) values(值1,值2);

2.批量插入

insert into users values(‘小明’,12,48574),(‘小红’,19,468451),(‘小李’,30,54554);
insert into users (name) values(‘小张’),(‘小白’),(‘小黑’);
SQL语句中出现中文报错
由于终端的编码字符集合数据库服务器的不一样导致

3.查询数据

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

4.修改数据

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

5.删除数据

格式:delete from 表名 where 条件;

约束、注释、事务

1.主键约束

约束:创建表时给表字段添加的限制条件
主键:表示数据唯一的字段称为主键
主键约束:保证字段的值唯一且非空
如何使用:
create table t0(id int primary key,name varchar(10));
insert into t0 values(1,‘aaa’);//成功
insert into t0 values(1,‘bbb’);//报错

2.非空约束 not null

create table t1(id int,age int not null);
insert into t1 values(1,20);成功
insert into t1 values(2,null);失败

3.唯一约束 unique

create table t2(id int,age int unique);
insert into t2 values(1,20);//成功
insert into t2 values(2,20);//失败

4.默认约束 default

create table t3(id int,age int default 20);
insert into t3 values(1,10);
insert into t3 values(2,null);//没用
insert into t3 (id) values(2);//默认值生效
主键约束 primary key
唯一且非空

5.外键约束

外键:用来建立关系的字段称为外键
外键约束:值可以为null,可以重复,但不能是关系表中不存在的数据,如果建立好关系后
被依赖的数据不能先删除,被依赖的表不能先删除
如何使用:
1.创建部门表
create table mydept(id int primary key auto_increment,name varchar(10));
create table myemp(id int primary key auto_increment,name varchar(10),dept_id int,
constraint fk_dept foreign key(dept_id) references mydept(id));
格式介绍:
constraint 约束名 foreign key(外键字段名) references 被依赖的表名(被依赖的字段名);

外键约束工作中除非特定场景 一般使用外键的机会较少,因为添加外键后影响测试效率

6.主键+自增

让主键自己增长
自增数值只增不减,从历史最大值基础上+1
如何使用:
create table t1(id int primary key auto_increment,name varchar(10));
insert into t1 values(null,‘aaa’);

7.注释

给表字段添加注释
如何使用:
create table t3(id int primary key auto_increment coment ‘这是一个主键’,name
varchar(10) comment ‘这是名字…’);

8.数据冗余

由于表设计不够合理导致的大量重复数据称为数据冗余,可以通过拆分表的形式降低或避免冗余的出现

9.事务

事务是数据库中执行同一行业务多条SQL语句的工作单元,可以保证这多条SQL语句全部执行成功或失败,
不会出现部分成功部分失败的情况.
1.创建表
create table user(id int primary key auto_increment,name varchar(10),money int,status varchar(5));
insert into user values(1,‘小明’,20000,‘正常’),(2,‘老王’,50,‘冻结’);
2.小明给老王转账SQL
update user set money=money-2000 where id=1 and status=‘正常’;
update user set money=money+2000 where id=2 and status=‘正常’;
开启事务:
begin;
回滚事务:把内存中的错误操作修改回去
rollback;
提交事务:
commit;

事务相关指令
1.开启事务 begin
2.提交事务 commit
3.回滚事务 rollback
4.保存回滚点 savepoint xxx;
5.回滚到指定回滚点 rollback to xxx
begin;
update user set money=1000 where id=1;
savepoint s1;
update user set money=2000 where id=2;
savepoint s2;

10.SQL分类

1.DDL (数据定义语言)
包括:create,drop,alter,truncate,不支持事务
2.DML (数据操作语言)
包括:insert,update,delete,select(DQL),支持事务
3.DQL (数据查询语言)
只包括:select
4.TCL (事务控制语言)
包括:begin commit rollback savepoint xxx,rollback to xxx;
5.DCL (数据控制语言)
分配用户权限相关的SQL

truncate
格式:truncate table 表名;
删除表并创建新表 自增数值清零

数据类型、日期、时间

1.数据类型

int(m) bigint(m) m代表的是显示长度 需要结合zerofill关键字使用
creat table t_int(id int,age int(10) zerofill);
insert into t_int values(1,18);

2.浮点数

常用:double(m,d)m代表总长度 d代表小数长度
decimal(m,d)超高精度浮点数 当超高精度运算时使用

3.字符串

常用:char(m) 不可变长度 ,最大长度255,varchar(m)可变长度 节省资源,最大长度为65535
text(m)可变长度,长度超过255建议使用text ,最大长度也是65535

4.日期

date:只能保存年月日
time:只能保存时分秒
datetime:年月日时分秒,默认值为null,最大值9999-12-31
timestamp:时间戳,年月日时分秒,默认值为当前系统时间,最大时间2038-1-19
create table t_date(t1 date,t2 time,t3 datetime,t4 timestamp);
insert into t_date values(‘2019-03-20’,null,null,null);
insert into t_date values(null,‘16:33:30’,‘2019-10-15 15:00:05’,null);

5.获取当前的系统时间(年月日时分秒)

select now();
获取当前的年月日
select curdate();
获取当前的时分秒
select curtime();

6.从年月日时分秒中提取年月日和提取时分秒

select date(now());
select time(now());
举例:select time(birthday) from user;

7.提取时间分量 extract;

select extract(year from now());
select extract(month from now());
select extract(day from now());
select extract(hour from now());
select extract(minute from now());
select extract(second from now());

8.日期格式化

格式:date_format(时间,格式)
%Y 四位年 yyyy
%y 2位年 yy
%m 两位月 06
%c 1位月 6
%d 日
%H 24小时
%h 12小时
%i 分钟
%s 秒

9.把非标准时间格式转成标准格式 str_to_date

select str_to_date("15.10.2019 15:43:28","%d.%m.%Y %H:%i:%s");

10.导入*.sql文件

1.Windows系统 把文件解压出来 把tables.sql文件放到c盘目录
source c:/tables.sql;
2.Linux系统放到桌面

模糊查询与分页查询

别名

select myname 姓名 from users;

去重

去掉重复的数据 ,distinct

模糊查询 like

%代表0或多个未知字符
_ 代表1个未知字符
举例:
以a开头: a%

排序

格式:order by 字段名 asc/desc 升序/降序
多字段排序 在order by后面写多个字段

分页查询

格式:limit 跳过的条数,请求的条数
请求第5页的8条数据 limit(5-1)*8,8

聚合函数

ifnull()函数

age=ifnull(x,y) 如果x值为null则age=y 不为null age=x

聚合函数

对多条数据进行统计查询: 平均值,最大值,最小值,求和,统计数量
1.平均值 avg(字段名)
2.最大值 max(字段名)
select max(sal) from emp where deptno=20;
3.最小值 min(字段名)
查询最小年龄是多少
select min(age) from users;
4.求和 sum(字段名);
5.统计数量 count(字段名)
查询20号部门的人数
select count(*) from users where age=20;

MYSQL中字符串

1.字符串拼接 concat(s1,s2)

查询emp表中员工姓名和工资 工资后面有单位:元;

2.获取字符串的字符长度 char_length(str)

select ename,char_length(ename) from emp;

3.获取字符串在另一个字符串中出现的位置

格式 instr(str,substr);
格式 locate(substr,str);两个方法效果一样

4.转大写 转小写

select upper(‘nba’),lower(‘AFg’);

5.截取字符串

左边截取: left(str,count);
右边截取: right(str,count);//count为截取的数值个数
自由截取: substring(str,star,length);

6.去空白

trim(str)

7.重复

repeat(str,count);
select repeat(‘ab’,3);//重复3次

8.替换

replace(str,old,new)

9.反转

reverse(str)

10.查看注释

show create table 表名;

查询

1.分组查询

group by;
多字段分组在group by后面写多个字段名

where 后面只能写普通字段的条件,不能写聚合函数的条件
把聚合函数的条件写在having后面.
各个关键字的顺序
select…from…where…group by…having…order…by…limit…;

2.子查询(嵌套查询)

例如：查询emp表中工资最高的员工信息
(1) select max(sal) from emp;
(2)select * from emp where sal=(select max(sal) from emp);

子查询总结

可以嵌套无数层
子查询可以写的位置:
1.把子查询写在where或having的后面,当成查询条件的值
2.写在创建表的时候
3.写在from后面,当成一张虚拟的表
select ename from(select * from emp where deptno=20) newtable;//必须有别名,这边取newtable

关联查询

同时查询多张表的数据的查询方式称为关联查询
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 [inner] join B on A.x=B.x where A.age=18;

外连接

外连接查询到的是一张表的全部数据和另外一张表的交集数据
格式:select * from A left/right [outer] join B on A.x=B.x where A.age=18;

关联查询总结

1.查询方式有三种:等值连接,内连接,外连接
2.如果查询的数据是两张表的交集数据使用等值或内连接(推荐);

select deptno from emp group by deptno;
select count(*) 每个部门人数 from emp where deptno in(select deptno from emp group by deptno);

表设计之关联关系

一对一

应用场景:用户表和用户信息扩展表

一对多

什么是一对多:有AB两张表,A表中一条数据对应B表中的多条数据；
如何建立关系:在多的表中添加外键指向另外一张表的主键；

create table t_emp(id int primary key auto_increment,name varchar(10),dept_id int);
create table t_dept(id int primary key auto_increment,name varchar(10));

insert into t_dept values(null,‘神仙’),(null,‘妖怪’);
insert into t_emp values(null,‘悟空’,1),(null,‘八戒’,1),(null,‘蜘蛛精’,2),(null,‘白骨精’,2);

多对多

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

如何建立关系,通过单独的关系表保存两张表的主键

创建表
create table teacher(id int primary key auto_increment,name varchar(10));
create table student(id int primary key auto_increment,name varchar(10));
create table t_s(tid int,sid int);

视图view

create view 视图名 as(子查询)

什么是视图:

数据库中表和视图都是其内部的对象,视图可以理解成一张虚拟的表.
数据来自原表,视图本质其实就是取代了一段SQL查询语句

为什么使用视图:

视图可以起到SQL重用的作用,提高开发效率.可以隐藏敏感信息
create view v_emp_nosal as(select empno,ename,job,mgr,comm from emp);

视图分类

1.简单视图:创建视图的子查询中不包含:去重,函数,分组,关联查询 的视图称为简单视图,
可以对视图的数据进行增删改查操作.
2.复杂视图:只能对复杂视图中的数据进行查询操作.

对简单视图进行增删改操作

1.插入数据
insert into emp_10(empno,ename) values(10010,‘Tom’)
数据污染:往视图中插入一条视图中不可见,但是在原表中可见的数据称为数据污染.
如果不希望出现数据污染可以使用with check option解决
2.修改和删除
只能对视图中存在的数据进行操作

修改视图
格式 create or replace view 视图名 as (子查询);

删除视图
格式:drop view 视图名;
drop view v_emp_30;

视图别名
如果创建视图的子查询中使用了别名,那么对视图进行操作时只能使用别名;

索引

索引是数据库中提高查询效率的技术,类似字典中的目录

为什么使用索引:

提高查询效率，
如果不使用索引,查询数据时会挨个遍历每一个磁盘块查询数据,

创建索引格式:

create index 索引名 on 表名(字段(字段长度));

索引是越多越好吗?

不是,因为索引会占用空间,只针对常见的查询字段创建索引;

索引分类:

聚集索引:

给表添加主键约束后自动创建的索引,索引的树状结构中保存着数据；

非聚集索引:

通过非主键字段创建的索引,索引的树状结构中没有数据,保存的是主键值,
通过非聚集索引找到主键值后还需要去聚集索引中查询具体数据

删除索引:

drop index 索引名 on 表名;
drop index i_item_title on item2;

以上为MYSQL的基本操作，请勿转载

																	作者：韩蒙福