分类 | 全称 | 说明 |
---|---|---|
DDL | Data Definition Language | 数据定义语言,用来定义数据库对象(数据库,表,字段) |
DML | Data Manipulation Language | 数据操作语言,用来对数据库表中的数据进行增删改 |
DQL | Data Query Language | 数据查询语言,用来查询数据库中表的记录 |
DCL | Data Control Language | 数据控制语言,用来创建数据库用户、控制数据库的访问权限 |
查询所有数据库
show database;
查询当前数据库
select database();
数据库创建语法。
CREATE DATABASE [IF NOT EXISTS] 数据库名 [DEFAULT CHARSET 字符集] [COLLATE 排序规则];
创建数据库,以默认方式。
create database my_db;
创建数据库,如果存在就不创建。
create database if not exists my_db;
创建数据库指定数据库默认字符集。
create database my_test default charset utf8mb4;
数据库删除语法。
DROP DATABASE [IF EXISTS] 数据库名;
删除数据库。
drop database my_test;
删除数据库,如果存在就删除。
drop database if exists my_test;
数据库使用语法。
USE 数据库名;
使用数据库。
use my_db;
此时可以通过上述查看选择的数据库命令来查看当前选择的数据库。
select database();
查询当前数据库所有表。
show tables;
这里可以在后面的创建表命令后操作。
查询表结构。
DESC 表名;
desc tb_user;
查询指定表的建表语句
SHOW CREATE TABLE 表名;
show create table tb_user;
基本语法。
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 '用户表';
类型 | 大小 | 有符号(SIGNED)范围 | 无符号(UNSIGNED)范围 | 描述 |
---|---|---|---|---|
TINYINT | 1 byte | (-128,127) | (0,255) | 小整数值 |
SMALLINT | 2 bytes | (-32768,32767) | (0,65535) | 大整数值 |
MEDIUMINT | 3 bytes | (-8388608,8388607) | (0,16777215) | 大整数值 |
INT或INTEGER | 4 bytes | (-2147483648,2147483647) | (0,4294967295) | 大整数值 |
BIGINT | 8 bytes | (-263,263-1) | (0,2^64-1) | 极大整数值 |
FLOAT | 4 bytes | (-3.402823466 E+38,3.402823466351 E+38) | 0 和 (1.175494351 E-38,3.402823466 E+38) | 单精度浮点数值 |
DOUBLE | 8 bytes | (-1.7976931348623157 E+308,1.7976931348623157 E+308) | 0 和 (2.2250738585072014 E-308,1.7976931348623157 E+308) | 双精度浮点数值 |
DECIMAL | 依赖于M(精度)和D(标度)的值 | 依赖于M(精度)和D(标度)的值 | 小数值(精确定点数) |
类型 | 大小 | 描述 |
---|---|---|
CHAR | 0-255 bytes | 定长字符串 |
VARCHAR | 0-65535 bytes | 变长字符串 |
TINYBLOB | 0-255 bytes | 不超过255个字符的二进制数据 |
TINYTEXT | 0-255 bytes | 短文本字符串 |
BLOB | 0-65 535 bytes | 二进制形式的长文本数据 |
TEXT | 0-65 535 bytes | 长文本数据 |
MEDIUMBLOB | 0-16 777 215 bytes | 二进制形式的中等长度文本数据 |
MEDIUMTEXT | 0-16 777 215 bytes | 中等长度文本数据 |
LONGBLOB | 0-4 294 967 295 bytes | 二进制形式的极大文本数据 |
LONGTEXT | 0-4 294 967 295 bytes | 极大文本数据 |
类型 | 大小 | 范围 | 格式 | 描述 |
---|---|---|---|---|
DATE | 3 | 1000-01-01 至 9999-12-31 | YYYY-MM-DD | 日期值 |
TIME | 3 | -838:59:59 至 838:59:59 | HH:MM:SS | 时间值或持续时间 |
YEAR | 1 | 1901 至 2155 | YYYY | 年份值 |
DATETIME | 8 | 1000-01-01 00:00:00 至 9999-12-31 23:59:59 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值 |
TIMESTAMP | 4 | 1970-01-01 00:00:01 至 2038-01-19 03:14:07 | YYYY-MM-DD HH:MM:SS | 混合日期和时间值,时间戳 |
案例:设计一张员工信息表,要求如下:
1.编号(纯数字)
2.员工工号(字符串类型,长度不超过10位)
3.员工姓名(字符串类型,长度不超过10位)
4.性别(男/女,存储一个汉字)
5.年龄(正常人年龄,不可能存储负数)
6.身份证号(二代身份证号均为18位,身份证中有X这样的字符)
7.入职时间(取值年月日即可)
创建表SQL
create table emp(id int comment '编号',work_no varchar(10) comment '工号',name varchar(10) comment '姓名',gender char(1) comment '性别',age tinyint unsigned comment '年龄',id_card char(18) comment '身份证号',entry_date date comment '入职时间'
)comment '员工表';
查看表结构
desc emp;
添加字段语法格式。
ALTER TABLE 表名 ADD 字段名类型(长度) [COMMENT 注释] [约束];
案例:为emp表增加一个新的字段”呢称”为nickname,类型为varchar(20)
alter table emp add nickname varchar(20) comment '昵称';
查看表。
desc emp;
修改数据类型
ALTER TABLE 表名 MODIFY 字段名新数据类型(长度);
修改字段名和字段类型
ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型(长度) [COMMENT 注释] [约束];
案例:将emp表的nickname字段修改为username,类型为varchar(30)
alter table emp change nickname username varchar(30) comment '用户名';
查看表。
desc emp;
删除字段
ALTER TABLE 表名 DROP 字段名;
案例:将emp表的字段username删除
alter table emp drop username;
查看表。
desc emp;
ALTER TABLE 表名 RENAME TO 新表名;
案例:将emp表的表名修改为employee
alter table emp rename to employee;
查看数据库中表。
show tables;
删除表
DROP TABLE [IF EXISTS] 表名;
案例:删除用户表。
drop table if exists tb_user;
删除指定表,并重新创建该表。(清空表中数据)
TRUNCATE TABLE 表名;
清空员工表中的数据。
truncate table employee;
DML英文全称是Data Manipulation Language(数据操作语言),用来对数据库中表的数据记录进行增删改操作。
给指定字段添加数据
INSERT INTO 表名 (字段名1,字段名2,...) VALUES(值1,值2,...);
给全部字段添加数据
INSERT INTO 表名 VALUES(值1,值2,...);
批量添加数据
INSERT INTO 表名(字段名1,字段名2,...) VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...);
注意:
案例:给员工表插入一条记录。
insert into employee(id, work_no, name, gender, age, id_card, entry_date)
values (1,'1','张三','男',23,'510145200011225413','2011-11-26')
或者
insert into employee values(4, '4', '赵六', '男', 24, '510145200011225413', '2011-10-24');
插入多条记录
insert into employee
values (5, '5', '赵六', '男', 24, '510145200011255413', '2015-10-25'),(6, '6', '赵六', '男', 24, '510145200011265413', '2016-10-26'),(7, '7', '赵六', '男', 24, '510145200011275413', '2017-10-27');
UPDATE 表名 SET 字段名1=值1,字段名2=值2,...[WHERE 条件];
注意:修改语句的条件可以有,也可以没有,如果没有条件,则会修改整张表的所有数据。
案例:
修改id为1的数据,将name修改为"张小明"
update employee set name ='张小明' where id = 1;
修改id为1的数据,将name修改为"小昭",gender修改为"女"
update employee set name ='小昭',gender='女' where id = 1;
将所有的员工入职日期修改为"2008-01-01"
update employee set entry_date = '2008-01-01';
注意:若使用DataGrip执行上述SQL报错Unsafe query: 'Update' statement without 'where' updates all table rows at once
,这是DataGrip的安全检查,这时我们点击Execute即可。
若点击后报错You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.
,这是由于mysql开启了安全检查。
查看sql_safe_updates
是否开启。
show variables like 'sql_safe_updates';
可以看见当前已启用,这时可以使用以下语句进行修改。
-- 会话级别(0表示关闭,1表示开启)
set sql_safe_updates=0;-- 全局(0表示关闭,1表示开启)
set global sql_safe_updates=0;
这时修改后再次尝试,就不会报错了。
DELETE FROM 表名 [WHERE 条件];
注意:
这里测试操作,建议先备份原始数据。不需要备份,可以直接跳转到案例。
选择自己需要的,我这里选择insert。
我这里复制存储备份的数据。
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (1, '1', '张小明', '男', 23, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (2, '2', '李四', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (3, '3', '王五', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (4, '4', '赵六', '男', 24, '510145200011225413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (5, '5', '赵六', '男', 24, '510145200011255413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (6, '6', '赵六', '男', 24, '510145200011265413', '2008-01-01');
INSERT INTO my_db.employee (id, work_no, name, gender, age, id_card, entry_date) VALUES (7, '7', '赵六', '男', 24, '510145200011275413', '2008-01-01');
案例:
删除gender为"女"的员工
delete from employee where gender = '女';
删除所有员工
delete from employee;
此时若出现问题,解决方法同上。
DQL英文全称是Data Query Language(数据查询语言),数据查询语言,用来查询数据库中表的记录。
基本语法
SELECT字段列表
FROM表名列表
WHERE条件列表
GROUP BY分组字段列表
HAVING分组后条件列表
ORDER BY排序字段列表
LIMIT分页参数
查询多个字段
-- 查询指定字段
SELECT 字段1,字段2,字段3... FROM 表名;-- 查询所有字段
SELECT * FROM 表名;
设置别名
SELECT 字段1 [AS 别名1],字段2 [AS 别名2]... FROM 表名;
去除重复记录
SELECT DISTINCT字段列表FROM表名;
案例:
准备查询数据。
drop table if exists employee;
create table employee
(id int comment '编号',work_no varchar(10) comment '工号',name varchar(10) comment '姓名',gender char(1) comment '性别',age tinyint unsigned comment '年龄',id_card char(18) comment '身份证号',work_address varchar(50) comment '工作地址',entry_date date comment '入职时间'
) comment '员工表';INSERT INTO employee (id, work_no, name, gender, age, id_card, work_address, entry_date)
values (1, '1', '柳岩', '女', 20, '123456789012345678', '北京', '2000-01-01'),(2, '2', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01'),(3, '3', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01'),(4, '4', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01'),(5, '5', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01'),(6, '6', '杨道', '男', 28, '12345678931234567X', '北京', '2006-01-01'),(7, '7', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01'),(8, '8', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01'),(9, '9', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01'),(10, '10', '陈友凉', '男', 53, '123456789012345670', '上海', '2011-01-01'),(11, '11', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01'),(12, '12', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01'),(13, '13', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01'),(14, '14', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01'),(15, '15', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01'),(16, '16', '周芷若', '女', 18, null, '北京', '2012-06-01');
基本查询
1.查询指定字段name,work_no,age
select name,work_no,age from employee;
2.查询所有字段
select * from employee;
3.查询所有员工的工作地址,起别名
-- as关键字可以省略
select work_address as '工作地址' from employee;
4.查询公司员工的上班地址(不要重复)
select distinct work_address from employee;
基本语法
SELECT 字段列表 FROM 表名 WHERE 条件列表;
条件–>运算符
比较运算符 | 功能 |
---|---|
> | 大于 |
>= | 大于等于 |
< | 小于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN…AND… | 在某个范围之内(含最小、最大值) |
IN(…) | 在in之后列表中的值,多选一 |
LIKE 占位符 | 模糊匹配(_匹配单个字符,%匹配任意个字符) |
IS NULL | 是NULL |
逻辑运算符 | 功能 |
---|---|
AND 或 && | 并且(多个条件同时成立) |
OR 或 || | 或者(多个条件任意一个成立) |
NOT 或 ! | 非,不是 |
案例:
查询年龄等于88的员工
select * from employee where age = 88;
查询年龄小于20的员工信息
select * from employee where age < 20;
查询年龄小于等于20的员工信息
select * from employee where age <= 20;
查询没有身份证号的员工信息
select * from employee where id_card is null;
查询有身份证号的员工信息
select * from employee where id_card is not null;
查询年龄不等于88的员工信息
select * from employee where age != 88;
或者
select * from employee where age <> 88;
查询年龄在15岁(包含)到20岁(包含)之间的员工信息
select * from employee where age >= 15 && age <= 20;
或者
select * from employee where age >= 15 and age <= 20;
或者
select * from employee where age between 15 and 20;
查询性别为女且年龄小于25岁的员工信息
select * from employee where gender = '女' && age < 25;
查询年龄等于18或20或40的员工信息
select * from employee where age = 18 or age = 20 or age = 40;
或者
select * from employee where age in(18, 20, 40);
查询姓名为两个字的员工信息
select * from employee where name like '__';
查询身份证号最后一位是X的员工信息
select * from employee where id_card like '_________________X';
或者
select * from employee where id_card like '%X';
将一列数据作为一个整体,进行纵向计算。
常见聚合函数
函数 | 功能 |
---|---|
count | 统计数量 |
max | 最大值 |
min | 最小值 |
avg | 平均值 |
sum | 求和 |
基本语法
SELECT 聚合函数(字段列表) FROM 表名;
注意:所有的聚合函数,null不参与计算。
案例:
统计该企业员工数量
select count(id) from employee;
统计该企业员工工作地址总数量
-- 用distinct关键字去除重复的工作地址
select count(distinct work_address) from employee;
统计该企业员工的平均年龄
select avg(age) from employee;
统计该企业员工的最大年龄
select max(age) from employee;
统计该企业员工的最小年龄
select min(age) from employee;
统计西安地区员工的年龄之和
select sum(age) from employee where work_address = '西安';
基本语法
SELECT 字段列表 FROM 表名 [WHERE 条件] GROUP BY 分组字段名 [HAVING 分组后过滤条件];
where与having区别
注意
案例:
根据性别分组,统计男性员工和女性员工的数量
select gender,count(gender) '数量' from employee group by gender;
根据性别分组,统计男性员工和女性员工的平均年龄
select gender,avg(age) '平均年龄' from employee group by gender;
查询年龄小于45的员工,并根据工作地址分组,获取员工数量大于等于3的工作地址
select work_address,count(work_address) from employee where age < 45 group by work_address having count(work_address) >= 3 ;
语法
SELECT 字段列表 FROM 表名 ORDER BY 字段1 排序方式1,字段2 排序方式2,...;
排序方式
注意:如果是多字段排序,当第一个字段值相同时,才会根据第二个字段进行排序。
案例:
根据年龄对公司的员工进行升序排序
-- asc可以省略,默认就是升序
select * from employee order by age asc ;
根据入职时间,对员工进行降序排序
select * from employee order by entry_date desc ;
根据年龄对公司的员工进行升序排序,年龄相同,再按照入职时间进行降序排序
select * from employee order by age asc, entry_date desc;
基本语法
SELECT 字段列表 FROM 表名 LIMIT 起始索引,查询记录数;
注意:
案例:
查询第1页员工数据,每页展示10条记录
select * from employee limit 0, 10;
或者
-- 查询结果是第一页数据,可以省略起始索引
select * from employee limit 10;
查询第2页员工数据,每页展示10条记录
select * from employee limit 10, 10;
查询年龄为20,21,22,23岁的女性员工信息。
select * from employee where gender = '女' and age in(20,21,22,23);
查询性别为男,并且年龄在20-40岁(含)以内的姓名为三个字的员工。
select * from employee where gender = '男' and age between 20 and 40 and name like '___';
统计员工表中,年龄小于60岁的,男性员工和女性员工的人数。
select gender,count(gender) '员工人数' from employee where age < 60 group by gender;
查询所有年龄小于等于35岁员工的姓名和年龄,并对查询结果按年龄升序排序,如果年龄相同按入职时间降序排序。
select name,age from employee where age <= 35 order by age,entry_date desc;
查询性别为男,且年龄在20-40岁(含)以内的前5个员工信息,对查询的结果按年龄升序排序,年龄相同按入职时间升序排序。
select * from employee where gender = '男' and age between 20 and 40 order by age,entry_date limit 5;
DCL英文全称是Data Control Language(数据控制语言),用来管理数据库用户、控制数据库的访问权限。
查询用户
USE mysql;
SELECT FROM user;
创建用户
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码';
修改用户密码
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新密码';
删除用户
DROP USER '用户名'@'主机名';
注意:
%
通配。MySQL中定义了很多种权限,但是常用的就以下几种
权限 | 说明 |
---|---|
ALL, ALL PRIVILEGES | 所有权限 |
SELECT | 查询数据 |
INSERT | 插入数据 |
UPDATE | 修改数据 |
DELETE | 删除数据 |
ALTER | 修改表 |
DROP | 删除数据库/表/视图 |
CREATE | 创建数据库/表 |
查询权限
SHOW GRANTS FOR '用户名'@'主机名';
授予权限
GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'主机名';
撤销权限
REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';
注意: