MySQL基础

目录

一、SQL分类

1.DDL

2.DML

3.DQL

4.DCL

二、函数

1.字符串函数

2.数值函数

3.日期函数

4.流程函数

三、约束

1.分类

2.外键约束   

四、多表查询

1.多表关系

2.多表查询

3.多表查询分类

（1）连接查询

（2）子查询

（3）联合查询

四、事务

（1）事务操作

（2）事务隔离

一、SQL分类

1.DDL

数据定义语言，用来定义数据库对象（数据库，表，字段）

1.数据库操作：
  1)查询

①查询所有数据库：show databases;

②查询当前数据库：select database();

 2)创建

   create database[if not exists] 数据库名 [default charset 字符集] [collate 排序规则];

 3)删除

   drop database[if exists] 数据库名;

 4）使用

use 数据库名;

2.表操作---查询

    1）查询当前数据库所有表：show tables;

     2）查询表结构：desc 表名;

     3）查询指定表的建表语句：show create table 表名;

3.表操作--创建

CREATE TABLE 表名(

     字段1  字段1类型[COMMENT 字段1注释],

     字段2  字段2类型[COMMENT 字段2注释],

     字段3  字段3类型[COMMENT 字段3注释],

      .......

     字段n  字段n类型[COMMENT 字段n注释]

)[COMMENT 表注释];

4.表操作--数据类型

①数值类型：tinyint,smallint,mediumint,int/integer,bigint,float,double（使用时要指定两个参数--整体长度与小数位数）,decimal(若声明数值无符号，在类型后加UNSIGNED),

eg.   age TINGINT UNSINGED，   score double(4,1)(假定分数最大为100.0，此时长度为4，小数保留一位，故后面参数值为1)

②字符串类型：char(定长字符串,即无论输入几个字符占用空间都为最大字符数占用的空间，缺失的由空格补齐，性能比varchar好),varchar(变长字符串，即在最大数量字符数内，输入几个字符就占几个字符空间),tinyblob,tinytext(短文本字符串),blob(二进制形式长文本数据),text(长文本数据),mediumblob,mediumtext,longblob(二进制形式的极大文本数据),longtext(极大文本数据)

注意：char与varchar使用时要标明最大字符长度，例如char(10),varchar(10)

③日期类型：date(YYYY-MM-DD，日期值),time(HH-MM-ss，时间值或持续时间),year(YYYY，年份值),datetime(YYYY-MM-DD HH-MM-ss，混合日期和时间值),timestamp(YYYY-MM-DD HH-MM-ss，混合日期和时间值，时间戳)

5.表操作--修改

①添加字段：alter table 表名 add 字段名 类型(长度) [comment 注释] [约束];

②修改数据类型：alter table 表名 modify 字段名 新数据类型（长度）;

③修改字段名和字段类型：alter table 表名 change 旧字段名 新字段名 类型（长度） [comment 注释] [约束];

④删除字段：alter table 表名 drop 字段名;

⑤修改表名：alter table 表名 rename to 新表名;

6.表操作--删除

  ①删除表：drop table [if exists] 表名;

  ②删除指定表(表中数据全部删除），并重新创建该表：truncate table 表名;

2.DML

数据操作语言，用来对数据库表中的数据进行增删改

1.添加数据：insert

①给指定字段添加数据：insert into 表名(字段名1，字段名2......) values(值1，值2 ....)

②给全部字段添加数据：insert into 表名 values(值1，值2...)

③批量添加数据：

insert into 表名(字段名1，字段名2......) values (值1，值2 ....)，(值1，值2 ....);

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

注意：字符串与日期型数据包含在引号中，字段与值要一 一对应

2.修改数据：update

update 表名 set 字段名1=值1，字段名2=值2，....[where 条件];

注意：修改的条件可以有也可以没有，若没有，则会修改整张表该字段的所有数据

3.删除数据：delete

delete from 表名[where 条件]

注意：若没有条件，会删除表中所有数据，但delete不能删除某一个字段的值（可以用update）

3.DQL

全称为Data Query Language,数据查询语言，用来查询数据库中表的记录

编写顺序：select>from>where>group by>having>order by>limit

执行顺序：from>where>group by>having>select>order by>limit

①基本查询

   1）查询多个字段

select 字段1，字段2，...from 表名;

select * from 表名;--------查询所有字段

   2）设置别名

      select 字段1[as 别名1]，字段2[as 别名2]....from 表名;

   3）去除重复记录

select distinct 字段列表 from 表名；

②条件查询（where）：

  1)语法：select 字段列表 from 表名 where 条件列表；

  2)条件：

比较运算符：> ,>= ,< ,<= ,= ,<>或!= ,between...and...(在某个范围内，含最小、最大值),in(...)(在in之后的列表中的值，多选一),like 占位符（模糊匹配，_匹配单个字符，%匹配任意个字符，无论什么数据类型都要用单引号），is null(判断字段是否为null)

逻辑运算符：and或&&，or或||，not或！

eg1.统计年龄为18或25或30的人数：select * from emp where age in(18,25,30);

eg2. 统计年龄为两个字的人数：select * from emp where name like ‘__’;(两个下划线)

eg3.统计身份证号(假设为18位)最后一位为x的人数：select * from emp where idcard like ‘%x’;或select * from emp where idcard like ‘_________________x’;(17个下划线)

eg4.统计年龄个位为0的人数:select * from emp where age like ‘%0’;

③聚合查询（count,max,min,avg,sum）:

   1)语法：select 聚合函数（字段列表） from 表名;(null值不参与计算)

eg1. select count(*) from emp;

eg2.select avg(age) from emp;

eg3.求西安地区员工年龄之和：select sum(age) from emp where workaddress=’西安’;

  2)聚合函数：将一列数据作为一个整体进行纵向运算

常见聚合函数：count:统计数量，max最大值，min最小值，avg平均值，sum求和

④分组查询（group by）

  1)语法：select 字段列表 from 表名[where 条件] group by 分组字段名 [having 分组后过滤条件];

   select后一般为分组字段与聚合函数，若还有其他字段。则显示分组字段的第一个对应的

   2)where与having 区别：

执行时间不同：where是对分组之前进行过滤，不满足where条件，不参与分组；而having是分组以后对结果进行过滤

判断条件不同：where不能对聚合函数进行判断，having可以

eg1.根据性别分组，统计男性员工和女性员工数量：select gender,count(*) from emp group by gender;

eg2.根据性别分组，统计男性员工和女性员工的平均年龄：

select gender,avg(age) from emp group by gender;

eg3.查询年龄小于25岁的员工数量，并根据工作地址分组：select workaddress,count(*) from emp where age<25 group by workaddress;

eg4.查询年龄小于25岁的员工数量，并根据工作地址分组，获取员工数量大于3的工作地址：

select workaddress,count(*) from emp where age<25 group by workaddress having count(*)>3;

⑤排序查询（order by）

   1）语法：select 字段列表 from 表名 order by 字段1 排序方式1，字段2 排序方式2；

    2）排序方式：asc:升序（默认值，使用是可省略不写），desc:降序

      eg1.根据年龄升序排序：select * from emp order by age asc;(由于升序是默认的，asc可省略不写)

      eg2.根据年龄升序排序,若年龄相同，按照入职时间降序排序：select * from emp order by age asc,entrydate desc;

      eg3.查询所有年龄小于35岁员工的姓名和年龄，并对查询结果按年龄升序排序，若年龄相同，按入职时间降序排序：

select name,age from emp where age <35 order by age,entrydate desc;

⑥分页查询（limit）

  1）语法：select 字段列表 from 表名 limit 起始索引，查询记录数;

  2）起始索引从0开始，起始索引=（查询页码-1）*每页显示记录数

  3）若查询的是第一页数据，起始索引可省略，直接写为limit 10（10为查询记录数）；

 eg1.查询年龄在20到40岁（含）之间的前5位男性员工信息，并按年龄升序排序，若年龄相同，按入职时间升序排序：

select * from emp where gender=’男’ and age between 20 and 40 order by age asc,entrydate asc limit 5;

4.DCL

数据控制语言，用来创建数据库用户、控制数据库的访问权限

1.用户管理

①查询用户:

use mysql;

select * from user;

②创建用户

   create user ‘用户名’ @ ‘主机名’ identified by ‘密码’;

若主机名为%表示任意主机均可访问

③修改用户密码

   alter user ‘用户名’ @ ‘主机名’ identified with mysql_native_password by ‘新密码’;

④删除用户

drop user ‘用户名’ @  ‘主机名’;

2.权限控制

all或all privileges(所有权限)，select(查询数据)，insert(插入数据)，update(修改数据)，delete(删除数据)，alter(修改表)，drop(删除数据库/表/视图)，create(创建数据库/表)

①查询权限：show grants for ‘用户名’@’主机名’;

②授予权限：grant 权限列表 on 数据库名.表名 to ‘用户名’ @ ‘主机名’;

③撤销权限：revoke 权限列表 on 数据库名.表名 from ‘用户名’ @’主机名’;

注意：多个权限之间用逗号分隔；授权时，数据库名和表名可以用*进行通配，代表所有

  eg.  grant all on itcast.* to ‘heima’ @ ‘%’;

二、函数

1.字符串函数

函数	功能
concat(s1,s2...sn)	字符串拼接，将s1,s2...sn拼接成一个字符串
lower(str)	将字符串str全部转换成小写
upper(str)	将字符串str全部转换成大写
lpad(str,n,pad)	左填充，用字符串pad对str左边进行填充，达到n个字符串长度
rpad(str,n,pad)	右填充，用字符串pad对右边进行填充，达到n个字符串长度
trim(str)	去掉字符串头部和尾部的空格
substring(str,start,len)	返回从字符串str位置起的len个长度的字符串(索引从1开始)

语法：select 函数(参数)

eg. select concat(‘hello’,’MySQL’);  select lower(‘HELLO’);   select lpad(‘01’,5,’-’);

select trim(‘  0 ha  ‘);  select substring(‘Hello MySQL’,2,6);

eg.将表peo中的worknum用0左填充至长度为5：update peo set worknum=lpad(worknum,5,’0’);

注意：lpad和rpad只能修改表中的字符型数据

2.数值函数

函数	功能
ceil(x)	向上取整
floor(x)	向下取整
mod(x,y)	返回x/y的余数
rand()	返回0~1内的随机数
round(x,y)	求参数x四舍五入的值，保留y位小数

语法：select 函数(参数)

eg.随机生成六位数：select  rpad(round(rand()*1000000,0),6,'0');

3.日期函数

函数	功能
curdate()	返回当前日期
curtime()	返回当前时间
now()	返回当前日期和时间
year(date)	获取指定date的年份
month(date)	获取指定date的月份
day(date)	获取指定date的日期
date_add(date,interval expr type)	返回一个日期/时间值加上一个时间间隔expr后的时间值
datediff(date1,date2)	返回起始时间date2和结束时间date1之间的天数

语法：select 函数(参数)

eg.select year(now());  select date_add(now(),interval 70 day);

select date_add(now(),interval 70 month);  

select datediff(‘2021-12-01’,’2021-11-12’);

按入职时间降序查询员工信息：select *,datediff(curdate(),entrydate) from emp order by datediff(curdate(),entrydate) desc;

4.流程函数

函数	功能
if(value,t,f)	若valued的值为true，则返回t，否则返回f
ifnull(value1,value2)	若value1不为null，返回value1，否则返回value2
case when[val1]then[res1]...else[default] end	若val1为true，返回res1..否则返回default默认值
case[expr]when[val1]then[res1]...else[default] end	若expr等于val1,返回res1,...否则返回defaul默认值

语法：select 函数(参数)

eg1. select if(2=3,’yes’,’no’); select ifnull(‘’,’default);(返回的为value1的值)

eg2. select ifnull(‘null’,’default’);(返回null)   select ifnull(null,’default’);(返回default)

eg3. 查询emp表的员工姓名和工作地址，若为上海/北京，则展示为一线城市，其他的为二线城市：select name,case workaddress when  '上海'  then  '一线城市'  when '北京' then  '一线城市'   else  '二线城市'  end from emp ;

eg4. 查询学生成绩：若大于85分为优秀，大于60为及格，其他为不及格

select  id,name,
      ( case when chinese>=85 then '优秀' when chinese>=60 then '及格' else '不及格' end)'语文',
       (case when math>=85 then '优秀' when math>=60 then '及格' else '不及格' end)'数学',
       (case when eng>=85 then '优秀' when eng>=60 then '及格' else '不及格' end)'英语'  from stu;

三、约束

是作用于表中字段上的规则，用于限制存储在表中的数据（保证数据库中的数据正确、有效性、完整性）

1.分类

约束	描述	关键字
非空约束	限制该字段的数据不能为null	not null
唯一约束	保证该字段的所有数据都是唯一、不重复的	unique
主键约束	主键是一行数据的唯一标识，要求非空且唯一	primary key
默认约束	保存数据时，若未指定该字段的值，则采用默认值	default
检查约束	保证字段值满足某个条件	check
外键约束	用来让两张表的数据之间建立联系，保证数据的一致性和完整性	foreign key

注意：约束时作用于表中字段上的，可以在创建/修改表时添加约束，约束之间用空格分开

eg1.根据需求创建表的结构

字段名	字段含义	字段类型	约束条件	约束关键字
id	ID唯一标识	int	主键，并且自动增长	primary key auto_increment
name	姓名	varchar(10)	不为空，并且唯一	not null unique
age	年龄	int	大于0，且小于等于120	check
status	状态	char(1)	若没有指定该值，默认为1	default
gender	性别	char(1)	无

create table user(
    id int primary key auto_increment,
    name varchar(10) not null unique,
    age int check(age>0 &&age<=120),
    status char(1) default '1',
    gender char(1)
);
insert into user (name, age,status, gender) values('Tom1',19,'1','男'),('Tom2',25,'0','女');

2.外键约束   

（1）添加外键：

create table 表名(

      字段名 数据类型,

      .......

     [constraint][外键名称] foreign key(外键字段名)references 主表(主表列名)

);

或者：

alter table 从表表名 add constraint 外键名称 foreign key(外键字段名)references 主表(主表列名);

eg.  alter table emp add constraint fk_emp_dept_id foreign key(dept_id)references dept(id);

   （2）删除外键

alter table 从表表名 drop foreign key 外键名称;

eg.   alter table emp drop foreign key fk_emp_dept_id;
  （3）删除/更新行为

行为	说明
no action	当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则不允许该操作（与restrict一致）
restrict	当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则不允许该操作（与no action一致）
cascade	当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则也删除/更新外键在子表中的记录
set null	当在父表中删除/更新数据时，先检查该数据是否有对应外键，若有，则设置子表中该外键值为null（这要求该外键允许为null）
set default	父键有变更时，子健将外键列设置成一个默认值（innodb不支持）

cascade语法：alter table 表名 add constraint 外键名 foreign key(外键字段) references 主表名(主表字段名) on update cascade on delete cascade;

set null 语法：alter table 表名 add constraint 外键名 foreign key(外键字段) references 主表名(主表字段名) on update set null on delete set null;

四、多表查询

1.多表关系

（1）一对多（多对一）：在多的一方建立外键，指向一的一方的主键，

如一个部门有多个员工

alter table emp add constraint fk_emp_dept_id foreign key(dept_id)references dept(id);

（2）多对多：建立第三张中间表，中间表至少包含两个外键，分别关联两方主键，如一个学生可选修多门课程，一门课程可以被多个学生选择

create table student_course(
    id int auto_increment comment'主键' primary key,
    studentid int not null comment '学生ID',
    courseid int not null comment '课程ID',
    constraint fk_courseid foreign key (courseid) references course(id),
    constraint fk_studentid foreign key (studentid) references course(id)
)comment '学生课程中间表';

id	name
1	Java
2	PHP

   （3）一对一：在任意一方加入外键，关联另一方的主键，并设置外键为唯一的（unique），如用户与用户详情

id	name	gender	phone
1	天天	男	1234
2	向上	女	5678

 用户信息表

id	degree	major	address	university	userid
1	本科	英语			1
2	硕士	舞蹈			2

用户教育信息表

2.多表查询

从多张表中查询数据

语法：select (字段1，字段2，..)from 表1，表2，..where 表1.外键名=表2.主键名

eg. select * from emp,dept where emp.dept_id=dept.id;（顺序按照dept.id的顺序）

3.多表查询分类

（1）连接查询

1）内连接：查询两表交集部分的数据

  ①隐式内连接：select 字段列表 from 表1，表2，.. where 条件...;

eg.查询所有员工所在部门

select emp.name,dept.name fron emp,dept where emp.dept_id=dept.id;

给表起别名进行查询（起了别名就不能再用表名，只能用别名）：

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

  ②显式内连接：select 字段列表 from 表1[inner] join on表2 on连接条件...;

select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;(inner可略不写)

2）外连接

①左外连接：查询左表所有数据，以及两表交集部分数据

select 字段列表 from 表1 left [outer] join 表2 on 条件..;

eg.查询emp表所有数据及对应的部门信息

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

②右外连接：查询右表所有数据，以及两表交集部分数据

select 字段列表 from 表1 right [outer] join 表2 on 条件..;

eg.查询dept表 所有数据及对应员工的信息

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

    3）自连接：当前表与自身的连接查询，自连接必须使用表别名(自连接查询可以是内连接也可以是外连接)

①内连接：select 字段列表 from 表1 别名A join 表1 别名B where 条件..;

eg.一张emp表包含结构为id,name,managerid(员工表，包含员工id，姓名，员工的上司，员工上司也属于员工，故可以用id将表自连接)

select a.name,b.name from emp a join emp b where a.managerid=b.id;

②外连接：select

上述例子中，老板没有上司，故查询时没有查询到老板，先也要把老板查询到可使用外连接

左外连接：select 字段列表 from 表1 别名A  left join 表1 别名B on 条件

select a.name,b.name from emp a left join emp b on a.managerid=b.id;

（2）子查询

SQL语句中嵌套select语句称为嵌套查询，又叫子查询

语法：select * from t1 where column1 =(select column from t2);

子查询外部语句可以是insert/update/delete/select中的任一按照查询结果分类：

    1）标量子查询：子查询结果为单个值（数字、字符串、日期等）

常用操作符：>,<,<>,<=,>=,!=,=

eg1.查询销售部所有员工信息

分为两步：a.查询销售部id（为4）：selsect id from dept where name=’销售部’;

          b.根据销售部id查询员工信息：select * from emp where dept_id=4;

          c.合并：select * from emp where dept_id=(selsect id from dept where name=’销售部’);

   eg2.查询在李欣欣之后入职的员工的信息

select * from emp where entrydate>(select entrydate from emp where name=’李欣欣’);

    2）列子查询：子查询结果为一列（可以是多行）

   常用操作符：in,not in,any(子查询返回列表中有任意一个满足即可),some(与any等同，可以用some的地方也可以用any),all(子查询返回列表的所有值都必须满足)

     3）行子查询：子查询结果为一行

eg.查询与李欣欣的工资和直属领导相同的员工信息

select * from emp where (salary,managerid)=(select salary,managerid from emp where name=’李欣欣’);

    4）表子查询：子查询结果为多行多列

 eg1.查询与李欣欣，数学书的职位和工资相同的员工信息

select * from emp where (job,salary)in(select job,salary from emp where name=’李欣欣’ or name=’数学书’);

 eg2.查询入职日期是”2020-9-1”之后的员工信息及其部门

    a.查询入职日期是其之后的员工信息：

select * from emp where entrydate>’2020-09-01’;

    b.查询在步骤a中查询后所得表上的员工，对应的部门信息（因为有可能有员工没有部门，在查询时会查询不到，所以采用左外连接查询，将该特殊与员工也包含在最后的查询列表中）

       select e.*,d.* from(select * from emp where entrydate>’2020-09-01’) e left join dept d on e.dept_id=d,id;

（3）联合查询

把多次查询的结果合并起来，形成一个新的查询结果集：union,union all

语法：select 字段列表 from 表1...

      union [all]

      select 字段列表 from 表2....;

注意：若使用union all，相当与将两次查询结果直接合并，若两次查询均有同一个人，则该人出现两次，但若使用union可以去重

      对于联合查询的多张表的列数和字段类型要保持一致

eg.查询薪资大于5000的人和年龄小于30岁的人

select * from emp where salary>5000

union

select * from emp where age<30;

若员工A薪资即大于5000年龄又小于30，使用union all时他会出现两次，若使用union会出现一次

四、事务

（1）事务操作

   1.方法1

 1）查看/设置事务提交方式

select @@autocommit;------自动提交，autocommit默认值为1

set @@autocommit=0;-------手动提交

2）提交事务

commit;

3）回滚事务

rollback;

2.方法2

 1）开启事务：start transaction或begin

 2）提交事务：commit;

3）回滚事务：rollback;

（2）事务隔离

   1）查看事务隔离级别：select  @@transaction_isolation

   2）设置事务隔离级别：

select [session | global] transaction isolation level {read uncommitted |read committed |repeatable read |serializable}