MySql基础语法

黑马MySql

DDL数据库操作

show databases；展示所有的数据库

create database 数据库名；创建数据库

drop database 数据库名；删除数据库

1、基础sql-DDL数据类型及案例

数值类型

字符串类型

日期类型

案例

create table emp(
    id int comment '编号'，
    workno varchar(10) comment '员工工号'，
    name varchar(10) comment '员工姓名'，
    gender char(1) comment '性别'，
    age tinyint unsigned comment '年龄'，
    idcard char(18) comment '身份证号'，
    entrydate date comment '入职时间'
)

DDL-表操作-修改

添加字段

alter table 表名 add 字段名 类型(长度) [comment 注释][约束];
例如：在emp表中添加一个新的字段“昵称” 类型为varchar(20)
alter table emp add nickname varchar(20) comment '昵称'

修改字段

修改字段的类型
alter table 表名 modify 字段名 新数据类型(长度)
修改字段的字段名和数据类型
alter table 表名 change 旧字段名 新字段名 数据类型 [comment 注释][约束]
例：将emp表的nickname字段修改为username，类型为varchar(30),
alter table emp nickname username varchar(30) comment '昵称'；

删除字段

alter table 表名 drop 字段名；
例：将emp表的username字段删除掉；
alter table emp drop username；

修改表名

alter table 表名 rename to 新表名；
例：将emp修改employer
alter table rename to employer

DDL-表操作-删除

删除表

drop table [if exit] 表名
删除指定表，并重新创建新的表
truncate table 表名；

2、DML-用于对数据库表中的数据进行增删改操作

DML-添加数据

1、给指定字段添加数据

insert into 表名(字段1，字段2，字段3，...) Vallue(值1，值2，值3，...)

2、给全部字段添加数据

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

3、批量添加数据

insert into 表名(字段1，字段2，字段3，...) Vallue(值1，值2，值3，...)，(值1，值2，值3，...)，(值1，值2，值3，...)；
insert into 表名 values(值1，值2，值3，...),(值1，值2，值3，...),(值1，值2，值3，...)

注意：插入数据时，指定的字段顺序要和值的顺序一致。

​ 字符串和日期型数据应包括在引号中。

​ 插入的数据大小，应该在字段规定的范围之内。

DML-修改数据

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

注意：where条件可以有也可以没有，没有条件将修改整张表的数据。

DML-删除数据

delete from 表名 [where]

delete语句的条件可以有也可以没有，如果没有则删除整张表

delete不能删除某一个字段的值

3、DQL-数据查询语言，用来查询数据库中表的记录

DQL-基本查询

1、查询多个字段

select 字段1，字段2，字段3，...from 表名；
select * from 表名

2、设置别名

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

3、去除重复记录

select distinct 字段列表 from 表名

DQL-条件查询

例：创建一张员工表，字段有，id，员工工号，姓名，性别，年龄，身份证号，工作地址，入职时间，完成以下查询。

create table emp(
    id int comment '编号'，
    workno varchar(10) comment '员工工号'，
    name varchar(10) comment '员工姓名'，
    gender char(1) comment '性别'，
    age tinyint unsigned comment '年龄'，
    idcard char(18) comment '身份证号'，
    workaddress varchar(30) comment '工作地址'，
    entrydate date comment '入职时间'
)

1、查询年龄等于88的员工
select * from emp where age=88；
2、查询年龄小于20岁的员工
select * from emp where age<20
3、查询没有身份证号的员工
select * from emp where idcard is null;
4、查询有身份证号的员工
select * from emp where idcard not null;
5、查询年龄不等于88的员工信息
select * from emp where age!=88;
select * from emp where age<>88;
6、查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from emp where age>=15 and age<=20;
select * from emp where age between 15 and 20;
7、查询性别为女，且年龄小于25岁的员工信息
select * from emp where gender='女' and age<25;
8、查询年龄等于18或20或40 的员工信息
select * from emp where age=18 or age=20 or age=40;
select * from emp where age in(18,20,40);
9、查询姓名为2个字的员工信息
select * from emp where name='_ _';
10、查询身份证号最后一位是X的用户
select * from emp where idcad='%_';

DQL-聚合函数

将一列数据作为一个整体，进行纵向计算

语法

select 聚合函数(字段列表) from 表名
注意：所有的null值不参与聚合函数的运算

例子

1、统计该企业员工数量
select count(*) from emp;
select count(id) from emp;
2、统计该企业员工的平均年龄
select avg(age) from emp;
3、统计该企业员工最大年龄
select max(age) from emp;
4、统计该企业员工最小年龄
select min(age) from emp;
5、统计西安地区员工的年龄之和
select sum(age) from emp where workaddress='西安';

DQL-分组查询

语法

select 字段名 from 表名 [where添加] group by 分组字段名 [having分组后的过滤条件]
where与having的区别：
执行时机不同，where是分组之前进行过滤，不满足where条件不参与分组；而having是分组之后对结果进行过滤
判断条件不同，where不能对聚合函数进行判断，而having可以
注意：执行书序where>聚合函数>having
	 分组之后，查询的字段一般为聚合函数和分组字段，查询其他字段毫无意义。

例子：

1、根据性别进行分组，统计男性员工和女性员工的数量
select gender count(*) from emp group by gender
2、根据性别分组，统计男性员工和女性员工的平均年龄
select gender avg(age) from emp group by gender
3、查询年龄小于45岁的员工，并根据工作地址分组，获取员工数量大于等于3的工作地址
select workaddress count(*) as address_count from emp where age<45 group by workaddress having address_count>=3;

DQL-排序查询

语法：

select * from emp order by 字段1 排序方式，字段2 排序方式;

排序方式：

​ ASC升序（默认值）

​ DESC降序

注意：如果是多字段排序，当第一个字段值相同时，才会根据第二个字段进行排序。

例子：

1、根据年龄对公司的员工进行升序排序
select * from emp order by age asc
2、根据入职时间，对员工进行降序排序
select * from emp order by entrydate desc
3、根据年龄对公司员工进行升序排序，年龄相同，在根据入职时间进行降序排序
select * from emp order by age asc，entrydate desc;

DQL-分页查询

语法：

select * from emp limit 起始索引，查询记录数;

注意：

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

分页查询是数据库的方言，不同的数据库有不同的实现，mysql中是linit

如果查询的是第一页数据，起始索引可以省略，直接简写为limit 10；

例子：

1、查询第一页数据记录，每页展示10条记录
select * from emp limit 0,10;
2、查询第二页数据，每页展示10条数据记录
select * from emp limit 10,10;

DQL-案例练习

1、查询年龄为20,21,22,23岁的女性员工信息
select * from emp where gender='女' and age in(20,21,22,23);
2,、查询性别为男，并且年龄在20-40岁(含)以内的姓名是三个字的员工
select * from emp where gender='男' and( age between 20 and 40 )and name like='_ _ _'
3、统计员工表中，年龄小于60的，男性员工和女性员工的人数
select gender count(*) as account from emp where age<60 group by gender
4、查询所有年龄小于等于35岁员工姓名和年龄，并对查询结果按年龄进行升序排序，如果年龄相同则按入职时间降序排列
select name,age from emp where age<=35 order by age asc,entrydate desc
5、查询性别为男且年龄在20-40岁(含)内的5个员工信息，对查询结果按年龄进行升序排序，年龄相同按照入职时间升序排序
select * from emp where gender='男'and age between 20 and 40 order by age，entrydate asc limit 0,5;

DQL-执行顺序

编写顺序

执行顺序

4、约束

概念：约束是对表中字段进行限制的一种规则

目的：保证表中数据的准确，有效和完整性

分类：

案例：根据需求完成表结构的创建

create table user(
	id int primary key auto_increment comment '主键'，
    name varchar(10) not null unique comment '姓名'，
    age int check(age>0 && age<=122) comment '年龄'，
    status char(1) default '1' comment '状态'，
    gender char(1) comment '性别'
)comment '用户表'

外键约束

概念：用来让两张表之间的数据建立连接，从而保证数据的一致性和完整性

语法：

创建表时添加外键
create table 表名(
	字段名 数据类型
		...  ,
	[constraint][外键名称] foreign key(外键字段名) references 主表(主表列名)
)
修改表添加外键
alter table 表名 add constrtint 外键名称 foreign key(外键字段名) references 主表(主表列名)

删除外键
alter table 表名 drop foreign key 外键名称

外键约束的删除/更新行为

重新设置外键约束行为，只需在添加约束的时候在最后加上对应的行为即可
语法示例如下：
alter table 表名 add constraint 外键名称 foreign key(外键字段名) references 主表(主表列名) on update cascade on delete cascade 

约束总结

1、非空约束：not null

2、唯一约束：unique

3、主键约束：primary key（自增：auto_increment）

4、默认约束：default

5、检查约束：check

6、外键约束：foreign key

5、多表查询

创建两张表emp，dept

员工表
create table emp(
    id int primary key comment '编号'.
    name varchar(10) comment '员工姓名'，
    age tinyint unsigned comment '年龄'，
    job varchar(10) comment '工作'，
    salary int comment '薪水'，
    entrydate date comment '入职时间'，
    managerid int ，
    dept_id int 
    constraint fk_emp_dept foreign key(dept_id) references主表(列名)
)
部门表
create table dept(
id int primary key,
name varchar(10)
)

多表查询-多表关系介绍

一对多

案例：部门与员工关系

关系：一个部门对应多个员工，一个员工只能是对应一个部门

实现：在多的一方建立外键，，指向一的一方的主键

多对多

案例：学生与课程关系

关系：一个学生可以选多门课程，一个课程也可以有多个学生

实现：建立第三张中间表中间表至少包含两个外键，分别关联两方主键

一对一

案例：用户与用户详情之间的关系

关系：一对一关系，多用于单表拆分，将一张表的基础字段放在一张表中，其他详情字段放在另一张表中，以提升操作效率。

实现：在任意一方加入外键，关联另一方的主键，并且设置外键为唯一的（unique）

多表查询概述

连接查询-内连接

内连接查询的是两张表交集的部分

语法：

隐式内连接
select 字段名 from 表1，表2 where 条件;
显示内连接
select 字段名 from 表1 [inner] join 表2 on 条件

例子：

查询每一个员工姓名，及关联的部门名称
隐式
select e.name,d.name from emp e,dept d where e.dept_id=d.id;
显示
select e.name,d.name from emp e inner join dept d on e.dept_id=d.id;

连接查询-外连接

语法：

做外连接
select 字段列表 from 表1 left join 表2 on 条件
相当于查询表1（左表）的所有数据，包含表1和表2交集部分的数据
右外连接
select 字段列表 from 表1 right join 表2 on条件
相当于查询表2的所有数据，包含表1和表2交集部分的数据

例子：

1、查询emp表的所有数据，和对应部门信息
select e.*,d.name from emp e left join dept d on e.dept_id=d.id 
2、查询dept的所有数据，和对应员工信息。
select d.*,e.*from emp e right join dept d on e.dept.id=d.id

连接查询-自连接

语法：

select 字段列表 from 表A 别名A join 表A 别名B on 条件
自连接查询，可以是内连接查询，也可以是外连接查询

例子：

1、查询员工及其所属领导的名字】
select e.*,b.name from emp e join emp b on e.managerid=b.id
2、查询所有员工及其领导的名字，如果员工没有领导，也需要查询出来
select e.*,b.* from emp e left join emp b on e.managerid=b.id

联合查询-unique/unique all

对于unique查询，就是把多次查询结果合并起来，形成一个新的查询结果集

语法：

select 字段列表 from 表A...
unique/unique all
select 字段列表 from 表B...

unqiue是将结果去重
unique all是将两次查询结果直接合并，不去重
对于联合查询的多张表的列数必须保持一致，字段类型也需要保持一致

例子：

1、将薪资低于5000的员工，和年龄大于50岁的员工全部查询出来
select * from emp e where e.salary<5000
unique
select * from emp e where e.age>50

多表查询-子查询

子查询：子查询返回的结果是单个值（数字，字符串，日期）

常用的操作符:= <> > >= < <=

例子：

1、查询销售部的所有员工信息
selece * from emp where dept_id=(select id from dept where name='销售部');
2、查询在‘方东白’入职之后的员工信息
select * from emp where entrydate>(select entrydate from emp where name ='方东白');

多表查询-列子查询

列子查询：子查询返回的结果是一列

常用的操作符：IN ,NOT IN,ANY,SOME,ALL

例子：

1、查询“销售部”和“市场部”所有员工的信息
select * from emp where dept_id in(select id from dept where name='销售部' or name='市场部')
2、查询比财务部所有人工资都高的员工信息
select * from emp where salary>all(select salary from emp where dept_id=(select id from dept where name='财务部'));
3、查询比'研发部'任意一人员工高的员工信息
select * from emp where salary>any(select salary from emp where dept_id=(select id from dept where name='研发部'));

多表查询-行子查询

行子查询：子查询返回的结果是一行（可以是多行）

常用的操作符：=,<>,IN,NOT IN

例子：

查询与张无忌的薪资及其所属领导相同的员工信息
select * from emp where(salary,managerid)=(select salary,managerid from emp where name='张无忌')

多表查询-表子查询

表子查询：子查询返回的结果是多行多列

常用的操作符：IN

1、查询与"鹿杖客",“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job, salary from emp where name='鹿杖客' or name='宋远桥')
2、查询入职信息是“2006-01-01”之后的员工信息，及其部门信息
select * from(select * from emp where entrydate>'2006-01-01')e left join dept d on e.dept_id=d.id

多表查询-练习

elect id from dept where name=‘研发部’));

### 多表查询-行子查询

行子查询：子查询返回的结果是一行（可以是多行）

常用的操作符：=,<>,IN,NOT IN

例子：

```sql
查询与张无忌的薪资及其所属领导相同的员工信息
select * from emp where(salary,managerid)=(select salary,managerid from emp where name='张无忌')

多表查询-表子查询

表子查询：子查询返回的结果是多行多列

常用的操作符：IN

1、查询与"鹿杖客",“宋远桥”的职位和薪资相同的员工信息
select * from emp where (job,salary) in (select job, salary from emp where name='鹿杖客' or name='宋远桥')
2、查询入职信息是“2006-01-01”之后的员工信息，及其部门信息
select * from(select * from emp where entrydate>'2006-01-01')e left join dept d on e.dept_id=d.id

多表查询-练习