DDL-数据库操作
查询
查询所有数据库
show databases;
查询当前数据库
select database();
创建
create database [if not exists] 数据库名 [default charset 字符集] [collate排序顺序];
删除
drop database [if exists]数据库名;
使用
use 数据库名;
DDL-表操作-查询
查询当前数据库所有表
show tables;
查询表结构
desc 表名;
查询指定表的建表语句
show create table 表名;
DDL-表操作-创建
create table 表面(
字段1 字段1类型 [comment 字段1注释]
字段2 字段2类型 [comment 字段2注释]
字段3 字段3类型 [comment 字段3注释]
...
字段n 字段n类型 [comment 字段n注释]
) [comment 表注释];
例如:
create table tb_user(
id int comment '编号',
name varchar(50) comment '姓名',
age int comment '年龄',
gender varchar (1) comment '性别',
)comment '用户表';
DDL-表操作-数据类型
数值类型
tinyint 1byte
smallint 2bytes
mediumint 3bytes
int或integer 4bytes
bigint 8bytes
float 4bytes
double 8bytes
decimal 依赖于m(精度)和d(标度)的值
signed有符号,unsigned无符号
字符串类型
char定长字符串 0-255bytes
varchar变长字符串 0-65535bytes
tinyblob不超过255个字符的二进制数据
0-255bytes
tinytext短文本字符串 0-255bytes
blob二进制形式的长文本数据
0-65535bytes
text长文本数据 0-65535bytes
mediumblob二进制形式的中等长度文本数据
mediumtext中等长度文本数据
longblob二进制形式的极大文本数据
longtext极大文本数据
...................................
char(10)就是性能高,但是是定长
性别之类的定长用char
varchar(10)就是性能较差,变长
用户名之类的变长用varchar
日期时间类型
date日期 格式YYYY-MM-DD
time时间值或持续时间
格式HH:MM:SS
year年份值 格式YYYY
datetime混合日期和时间值
YYYY-MM-DD HH:MM:SS
例如:
设计一张员工信息表,要求如下:
1.编号(纯数字)
2.员工工号(字符串类型,长度不超过10位)
3.员工姓名(字符串类型,长度不超过10位)
4.性别(男或女,存储一个汉字)
5.年龄(正常人年龄,不可以存储负数)
6.身份证号(二代身份证均为18位)
7.入职时间(取值年月日即可)
create table emp(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '年龄',
idcard char(18) comment '身份证号',
entrydate date comment '入职时间'
)comment '员工表';
DDL-表操作-修改
添加字段
alter table 表名 add 字段名 类型(长度)[comment 注释] [约束];
案例: 为emp表添加一个字段“昵称”为Nickname,类型为varchar(20)
alter table emp add nickname varchar(20);
修改数据类型
alter table 表名 modify 字段名 新数据类型(长度);
修改字段名和字段类型
alter table 表名 change 旧字段名 新字段名 类型(长度)[comment注释] [约束];
案例:将emp表中的Nickname字段修改为username,类型为varchar(30):
alter table emp change nickname username varchar(30) comment '用户名';
删除字段
alter table 表名 drop 字段名;
案例:将emp表的字段username删除
alter table emp drop username;
修改表名
alter table 表名 rename to 新表名;
案例:将emp表的表名修改为employee
alter table emp rename to employee;
DDL-表操作-删除
删除表
drop table [if exists] 表名;
删除指定表,并重新创建该表
truncate table 表名;
注意:在删除表时,表中的全部数据也会被删除。
DML-介绍
DML英文全称是Data Manipulation Lan(数据操作语言),用来对数据库中表的数据记录进行增删改操作
添加数据(insert)
修改数据(update)
删除数据(delete)
DML-添加数据
1.给指定字段添加数据
insert into 表名(字段名1,字段名2,...),(值1,值2,...);
2.给全部的字段添加数据
insert into 表名 values(值1,值2,...);
3.批量添加数据
insert into 表名(字段名1,字段名2,...)values,(值1,值2,...),(值1,值2,...),(值1,值2,...);
insert into 表名 values(值1,值2,...),(值1,值2,...),(值1,值2,...);
注意:
插入数据时,指定的字段顺序需要与值的顺序是一一对应的。
字符串和日期型数据应该包含在引号中。
插入的数据大小,应该在字段的规定范围内。
DQL-介绍
DQL的全称为Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的数据
查询关键字:SELECT
DQL-语法
select -- 基本查询
字段列表
from
表名列表
where --条件查询
条件列表
(+一个额外的知识点聚合函数(count,max,min,avg,sum))
group by -- 分组查询
分组字段表
having
分组后条件列表
order by --排序查询
排序字段列表
limit - -分页查询
分页参数
DQL-基本查询
1.查询多个字段
select 字段1,字段2,字段3...from 表名;
2.设置别名
select 字段1 [as 别名1],字段12[as 别名2],字段3 [as 别名3]... from 表名;
3.去除重复记录
select distinct 字段列表 from 表名;
例子
-- 基本查询 -- 1.查询指定字段 name,age,workno 返回、 select name,age,workno from emp; -- 2.查询所有字段返回 select id, workno, name, gender, age, idcard, workaddress, entrydate from emp; select * from emp; -- 3.查询所有员工表的工作地址起别名 select workaddress as '工作地' from emp; select workaddress '工作地' from emp; -- 4.查询公司员工的上班地址不要重复 select distinct workaddress from emp;
DQL-条件查询
1.语法
select 字段列表 from 表名 where 条件列表;
2.条件
比较运算符
>,<,==,>=,<=,<>或!=
between...and...,在某个范围之内(含最小值和最大值),
in(...),在in之后的列表中的值,多选1,
like占位符,模糊匹配(_匹配多个字符,%匹配任意字符),
is null,是null
逻辑运算符
and或&&,并且(多个条件同时成立)
or或||,或者(多个条件任意一个成立)
not或!,非,不是
例子
-- 条件查询 -- 1.查询年龄等于88的员工 select * from emp where age = 88; -- 2.查询年龄小于28的员工信息 select * from emp where age < 28; -- 3.查询年龄小于等于20 的员工信息 select * from emp where age <= 20; -- 4.查询没有身份证号的员工的信息 select * from emp where idcard is null; -- 5.查询有身份证号的员工的信息 select * from emp where idcard is not null; -- 6.查询年龄不等于88的员工的信息 select * from emp where age != 88; select * from emp where age <> 88; -- 7.查询年龄在15岁(包含)到28岁(包含)之间的员工的信息 select * from emp where age between 15 and 28; select * from emp where age >=15 && age <=28; -- 8.查询性别为女且年龄小于25岁的员工信息 select * from emp where gender = '女' and age < 25; -- 9.查询年龄等于18或20或10的员工的信息 select * from emp where age = 18||age = 28 ||age = 40; select * from emp where age in(18,28); -- 10.查询姓名为两个字的员工 select * from emp where name like '__'; -- 11.查询最后一位身份证号为x的员工 select * from emp where idcard like '%X';
DQL-聚合函数
1.介绍
将一列数据作为一个整体,进行纵向运算
2.常见的聚合函数
count统计数量,max最大值,min最小值,avg平均值,sum求和。
3.语法
select 聚合函数(字段列表)from 表名;
4.例子
-- 聚合函数 -- 1.统计该企业员工的数量--null值不参与聚合函数的计算 select count(*) from emp; select count(idcard) 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-分组查询
1.语法
select 字段列表 表名 [where 条件] group by 分组字段名 [having 分组后过滤条件]
2.where和having的区别
执行时机不同:where是分组之前进行过滤,不满足where条件,不参与分组, 而having是分组后对结果进行过滤
判断条件不同:where不能对复合函数进行判断,而having可以.
3.注意
执行顺序:where-》聚合函数-》having
分组之后,查询的字段一般为聚合函数和分组字段,查询其他的字段无意义
4.例子
-- 分组查询 -- 1.根据性别分组,统计男性员工和女性员工的数量信息 select gender,count(*) from emp group by gender; -- 2.根据性别分组,统计男性员工和女性员工的平均年龄 select gender,avg(age) from emp group by gender; -- 3.查询年龄小于45的员工,并根据工作地址分组,获取员工数量小于等于三的工作地址 select workaddress,count(*) as 'count_address' from emp where age < 45 group by workaddress having count_address <= 3;
DQL-排序查询
1.语法
select 字段列表 from 表名 order by 字段1 排序方式1,字段2 排序方式2;
2.排序方式
asc 升序(默认值)
desc 降序
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序
3.例子
-- 排序查询 -- 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-分页查询
1.语法
select 字段列表 from 表名 limit 起始索引, 查询记录数;
2.注意
起始索引从0开始,起始索引 = (查询页码 - 1)*每页展示的记录数
分页查询是数据库的方言,不同的数据库有不同的实现,mysql当中是limit
如果查询的是第一页的数据,起始索引可以省略,直接简写为limit10
3.例子
-- 分页查询 -- 1.查询第一页员工的数据,每页展示十条数据 select * from emp limit 0,10; select * from emp limit 10; -- 2.查询第二页员工的数据,每页展示十条数据------------》(页码-1)*页展示记录数 select * from emp limit 10,10;
DQL-按需实践
按照需求完成如下DQL语句的编写
-- 1.查询年龄为20,22,23岁的员工的信息 select * from emp where gender = '女' and age in (20,22,23,88); -- 2.查询性别为男,并且年龄在20-40岁含以内的姓名为三个字的员工 select * from emp where gender = '男' and (age between 20 and 40 )and name like '___'; -- 3.统计员工表中,年龄小于60岁的,男性员工和女性员工的人数 select gender,count(*) from emp where age < 60 group by gender; -- 4.查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序 select name,age,entrydate from emp where age < 35 order by age asc,entrydate desc; -- 5.查询性别为男,且年龄在20-60岁含以内的前五个员工的信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序 select * from emp where gender = '男' and (age between 20 and 60 ) order by age asc,entrydate desc limit 5;
DQL-执行顺序
1.from
表名列表
2.where --条件查询
条件列表
(+一个额外的知识点聚合函数(count,max,min,avg,sum))
3.group by -- 分组查询
分组字段表
having
分组后条件列表
4.select -- 基本查询
字段列表
5.order by --排序查询
排序字段列表
6.limit - -分页查询
DCL-介绍
DCL的英文全称为Data Control Language(数据控制语言),用来管理数据库用户,控制数据库的访问权限。
DCL-管理用户
1.查询用户
use mysql;
select * from user;
2.创建用户
create user '用户名'@'主机名' identified by 密码;
3.修改用户密码
alter user '用户名'@'主机名' identified with mysql_native_password by '新密码';
4.删除用户
drop user '用户名'@'主机名';
例子
-- 创建用户itcast,只能够在当前主机localhost访问,密码123456 create user 'itcast'@'localhost' identified by '123456'; -- 创建用户heima,可以在任意主机方位该数据库,密码123456 create user 'heima'@'%' identified by '123456'; -- 修改用户heima的访问密码为1234 alter user 'heima'@'%' identified with mysql_native_password by '1234'; -- 删除itcast的@localhost用户 drop user 'itcast'@'localhost';
DCL-权限控制
1.mysql中定义了很多种权限,但是常用的就以下几种:
all,all privileges -所有权限
select -查询数据
INSERT -插入数据
update -修改数据
delete -删除数据
alter -修改表
drop -删除数据库、表、视图
create -创建数据库、表
2.语法
查询权限
show grants for '用户名'@'主机名';
授予权限
grant 权限列表 on 数据库名.表名 to '用户名'@'主机名';
撤销权限
revoke 权限列表 on 数据库名.表名 from '用户名'@'主机名';
3.注意
多个权限之间,使用逗号进行分隔
授权时,数据库名和表名可以使用*进行通配,代表所有。
4.例子
-- 查询权限 show grants for 'heima'@'%'; -- 授予权限 grant all on itcast.* to 'heima'@'%'; -- 撤销权限 revoke all on itcast.* from 'heima'@'%';