MySQL的基础语法
SQL分类
DDL:用来操作数据库,表,列等。
DML:用来操作数据库中表的数据进行增删改。
DQL:用来查询数据库中表的记录(数据)。
DCL:用来定义数据库的访问权限和安全级别及创建用户。
DDL操作数据库或数据表
查询或者创建数据库
查询所有数据库
SHOW DATABASES;
查询某个数据库的创建语句
-- SHOW CREATE DATABASE 数据库名称;
SHOW CREATE DATABASE mysql;
创建数据库
-- CREATE DATABASE 数据库名称;
CREATE DATABASE testDB1;
创建数据库(添加判断条件,如果不存在则创建)
-- CREATE DATABASE IF NOT EXISTS 数据库名称;
CREATE DATABASE IF NOT EXISTS testDB2;
创建数据库,并指定字符集
-- CREATE DATABASE 数据库名称 CHARACTER SET 字符集名称;
CREATE DATABASE testDB3 CHARACTER SET UTF8;
创建数据库,如果不存在则创建,指定字符集为gbk
CREATE DATABASE IF NOT EXISTS testDB4 CHARACTER SET gbk;
修改、删除、使用数据库
修改数据库(修改字符集)
-- ALTER DATABASE 数据库名称 CHARACTER SET 字符集名称;
ALTER DATABASE testDB3 CHARACTER SET gbk;
删除数据库
-- DROP DATABASE 数据库名称;
DROP DATABASE testDB1;
删除数据库(添加判断条件:存在即删除)
-- DROP DATABASE IF EXISTS 数据库名称;
DROP DATABASE IF EXISTS testDB2;
使用数据库
-- USE 数据库名称;
USE testDB3;
查询当前使用的数据库
SELECT DATABASE();
查询数据表
查询所有数据表
SHOW TABLES;
查询表结构
-- DESC 表名;
DESC db;
查询数据表的字符集
-- SHOW TABLE STATUS FROM 数据库名称 LIKE '表名';
SHOW TABLE STATUS FROM mysql LIKE 'db';
创建数据表
CREATE TABLE 表名(
列名 数据类型 约束,
列名 数据类型 约束,
...
列名 数据类型 约束
);
举例:
-- 创建一个product商品表(商品编号、商品名称、商品价格、商品库存、上架时间)
CREATE TABLE product(
id INT,
`name` VARCHAR(20),
price DOUBLE,
stock INT,
insert_time DATE
);
数据类型:
数据类型 | 字节长度 | 范围或用法 |
---|---|---|
Bit | 1 | 无符号[0,255],有符号[-128,127],天缘博客备注:BIT和BOOL布尔型都占用1字节 |
TinyInt | 1 | 整数[0,255] |
SmallInt | 2 | 无符号[0,65535],有符号[-32768,32767] |
MediumInt | 3 | 无符号[0,224-1],有符号[-223,2^23-1]] |
Int | 4 | 无符号[0,232-1],有符号[-231,2^31-1] |
BigInt | 8 | 无符号[0,264-1],有符号[-263 ,2^63 -1] |
Float(M,D) | 4 | 单精度浮点数。天缘博客提醒这里的D是精度,如果D<=24则为默认的FLOAT,如果D>24则会自动被转换为DOUBLE型。 |
Double(M,D) | 8 | 双精度浮点。 |
Decimal(M,D) | M+1或M+2 | 未打包的浮点数,用法类似于FLOAT和DOUBLE,天缘博客提醒您如果在ASP中使用到Decimal数据类型,直接从数据库读出来的Decimal可能需要先转换成Float或Double类型后再进行运算。 |
Date | 3 | 以YYYY-MM-DD的格式显示,比如:2009-07-19 |
Date Time | 8 | 以YYYY-MM-DD HH:MM:SS的格式显示,比如:2009-07-19 11:22:30 |
TimeStamp | 4 | 以YYYY-MM-DD的格式显示,比如:2009-07-19 |
Time | 3 | 以HH:MM:SS的格式显示。比如:11:22:30 |
Year | 1 | 以YYYY的格式显示。比如:2009 |
Char(M) | M | 定长字符串。 |
VarChar(M) | M | 变长字符串,要求M<=255 |
Binary(M) | M | 类似Char的二进制存储,特点是插入定长不足补0 |
VarBinary(M) | M | 类似VarChar的变长二进制存储,特点是定长不补0 |
Tiny Text | Max:255 | 大小写不敏感 |
Text | Max:64K | 大小写不敏感 |
Medium Text | Max:16M | 大小写不敏感 |
Long Text | Max:4G | 大小写不敏感 |
TinyBlob | Max:255 | 大小写敏感 |
Blob | Max:64K | 大小写敏感 |
MediumBlob | Max:16M | 大小写敏感 |
LongBlob | Max:4G | 大小写敏感 |
Enum | 1或2 | 最大可达65535个不同的枚举值 |
Set | 可达8 | 最大可达64个不同的值 |
Geometry | ||
Point | ||
LineString | ||
Polygon | ||
MultiPoint | ||
MultiLineString | ||
MultiPolygon | ||
GeometryCollection |
修改数据表
修改表名
-- ALTER TABLE 旧表名 RENAME TO 新表名;
ALTER TABLE product RENAME TO product2;
修改表的字符集
-- ALTER TABLE 表名 CHARACTER SET 字符集名称;
ALTER TABLE product2 CHARACTER SET utf8;
给表添加列
-- ALTER TABLE 表名 ADD 列名 数据类型;
ALTER TABLE product2 ADD color VARCHAR(10);
修改表中列的数据类型
-- ALTER TABLE 表名 MODIFY 列名 数据类型;
ALTER TABLE product2 MODIFY color INT;
修改表中列的名称和数据类型
-- ALTER TABLE 表名 CHANGE 旧列名 新列名 数据类型;
ALTER TABLE product2 CHANGE color address VARCHAR(200);
删除表中的列
-- ALTER TABLE 表名 DROP 列名;
ALTER TABLE product2 DROP address;
修改表结构大多以ALTER TABLE 开头 后面根据不同的功能选择不同的关键字。
删除数据表
删除表
-- DROP TABLE 表名;
DROP TABLE product2;
删除表(判断:如果存在即删除)
-- DROP TABLE IF EXISTS 表名;
DROP TABLE IF EXISTS product2;
DML表数据增删改
新增表数据
给指定列添加数据
列名和值的数量以及类型要对应,除了数字类型,其他数据类型的数据都需要加引号(单双引号都可以),推荐单引号。
-- INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...);
INSERT INTO product (id,name,price,stock,insert_time) VALUES(1,'手机',1999.99,100,'2021-05-16');
-- INSERT INTO 表名 (列名1,列名2,...) VALUES(值1,值2,...);
INSERT INTO product (id,name,price) VALUES(2,'电脑',3999.99);
给全部列添加数据
-- INSERT INTO 表名 VALUES(值1,值2,...);
INSERT INTO product VALUES(3,'家电',1500,50,'2021-05-16');
批量添加所有列数据
-- INSERT INTO 表名 VALUES(值1,值2,...),(值1,值2,...),(值1,值2,...),...
INSERT INTO product VALUES(4,'微波炉',600,10,'2021-05-17'),(5,'电磁炉',900,30,'2021-05-18');
批量添加指定列数据
INSERT INTO product (id,name,price) VALUES(6,'洗衣机',3999.99),(7,'冰箱',3999.99);
修改和删除表数据
修改或删除语句必须添加条件,如果不添加条件,则会影响所有数据。
-- UPDATE 表名 SET 列名1=值1,列名2=值2,... [WHERE 条件];
UPDATE product SET price=3500 WHERE NAME = '手机';
UPDATE product SET price=1800,stock=36 WHERE NAME = '电脑';
删除表数据
-- DELETE FROM 表名 [where 条件];
DELETE FROM product where name='冰箱';
DELETE FROM product WHERE stock=10;
DQL表数据查询
查询语法
SELECT
字段列表
FROM
表名字段
WHERE
条件列表
GROUP BY
分组字段
HAVING
分组后的过滤条件
ORDER BY
排序
LIMIT
分页
DQL表数据查询—查询全部
查询全部数据
-- SELECT * FROM 表名;
SELECT * FROM product;
查询指定列
-- SELECT 列名1,列名2,列名3 from product;
SELECT name,price,band from product;
去除重复查询
-- SELECT DISTINCT 列名1,列名2 FROM 表名;
SELECT DISTINCT band FROM product;
计算列的值
-- SELECT 列名1 (+ - * /) 列名2 from 表名;
SELECT name,stock+10 from product;
-- IFNULL(想替换的列,想替换的值)
SELECT name,IFNULL(stock,0)+10 from product;
起别名
-- SELECT 列名1,列名2,... AS 别名 from 表名;
SELECT name,IFNULL(stock,0)+10 AS getSum from product;
SELECT name,IFNULL(stock,0)+10 getSum from product;
DQL表数据查询—条件查询
查询条件分类
符号 | 功能 |
---|---|
> | 大于 |
< | 小于 |
>= | 大于等于 |
<= | 小于等于 |
= | 等于 |
<> 或 != | 不等于 |
BETWEEN … AND … | 在某个范围之内(都包含) |
IN(…) | 多选一 |
LIKE 占位符 | 模糊查询 _单个任意字符 %多个任意字符 |
IS NULL | 是NULL |
IS NOT NULL | 不是NULL |
AND 或 && | 并且 |
OR 或 || | 或者 |
NOT 或 ! | 非,不是 |
条件查询语法
SELECT 列名列表 FROM 表名 WHERE 条件
条件查询
-- 查询库存大于20的商品信息
SELECT * FROM product WHERE stock > 20;
-- 查询品牌为华为的商品信息
SELECT * FROM product WHERE band='华为';
-- 查询金额在4000~6000之间的商品信息
SELECT * FROM product WHERE price >=4000 AND price <=6000;
SELECT * FROM product WHERE price BETWEEN 4000 AND 6000;
-- 查询库存为18、23、50的商品信息
SELECT * FROM product WHERE stock = 18 OR stock = 23 OR stock = 50;
SELECT * FROM product WHERE stock IN(18,23,50);
-- 查询库存为NULL的商品信息
SELECT * FROM product WHERE stock IS NULL;
-- 查询库存不为NULL的商品信息
SELECT * FROM product WHERE stock IS NOT NULL;
模糊查询
-- 查询名称以小米为开头的商品信息 %代表多个任意字符
SELECT * FROM product WHERE NAME LIKE '小米%'
-- 查询名称第二个字是为的商品信息 _表示单个任意字符
SELECT * FROM product WHERE NAME LIKE '_为%';
-- 查询名称为四个字符的商品信息
SELECT * FROM product WHERE NAME LIKE '____';
-- 查询名称中包含电脑的商品信息
SELECT * FROM product WHERE NAME LIKE '%电脑%';
DQL表数据查询—聚合函数查询
聚合函数:将一列数据作为一个整体,进行纵向计算
函数名 | 功能 |
---|---|
count(列名) | 统计数量(一般选用不为null的列) |
max(列名) | 最大值 |
min(列名) | 最小值 |
sum(列名) | 求和 |
avg(列名) | 平均值 |
聚合函数查询语法
SELECT 函数名(列名) FROM 表名 [WHERE 条件]
-- 查询product表中的总记录条数
SELECT COUNT(*) FROM product;
-- 获取最高价格
SELECT MAX(price) FROM product;
-- 获取最低库存
SELECT MIN(stock) FROM product;
-- 获取总库存数量
SELECT SUM(stock) FROM product;
-- 获取品牌为苹果的总库存数量
SELECT SUM(stock) FROM product WHERE band='苹果';
-- 获取品牌为小米的平均商品价格
SELECT AVG(price) FROM product WHERE band='小米';
DQL表数据查询—排序查询
排序查询语法
SELECT 列名列表 FROM 表名 [WHERE 条件] ORDER BY 列名 排序方式,列名 排序方式,...
排序方式:ASC-升序,DESC降序
如果多个排序条件,只有当前边的条件值一样时,才会计算第二个。
有条件先过滤条件
-- 按照库存升序进行排序
SELECT * FROM product ORDER BY stock ASC;
-- 查询名称中包含手机的商品信息。按照金额降序排序
SELECT * FROM product WHERE NAME LIKE '%手机%' ORDER BY price DESC;
-- 按照金额升序排序,如果金额相同,按照库存降序排列
SELECT * FROM product ORDER BY price ASC,stock DESC;
DQL表数据查询—分组查询
分组查询语法
SELECT 列名列表 FROM 表名 [WHERE条件] GROUP BY 分组列名
[HAVING 分组后的条件过滤]
[ORDER BY 排序列名 排序方式]
有条件一定要先过滤条件!!!
-- 根据品牌分组,获取每组商品的总金额
SELECT band,SUM(price) FROM product GROUP BY band;
-- 对金额大于4000元的商品,按照品牌分组,获取每组商品的总金额
SELECT band,SUM(price) FROM product WHERE price>=4000 GROUP BY band;
-- 对金额大于4000元的商品,按照品牌分组,获取每组商品的总金额
SELECT band,SUM(price) FROM product WHERE price>4000 GROUP BY band;
-- 对金额大于4000元的商品,按照品牌分组,获取每组商品的总金额,只显示总金额大于7000元的
SELECT band,SUM(price) getSum FROM product WHERE price>4000 GROUP BY band HAVING getSum>7000 ;
-- 对金额大于4000元的商品,按照品牌分组,获取每组商品的总金额,只显示总金额大于7000元的,并按照总金额的降序排列
SELECT band,SUM(price) getSum FROM product WHERE price>4000 GROUP BY band HAVING getSum > 7000 ORDER BY getSum DESC;
DQL表数据查询—分页查询
分页查询语法
SELECT 列名列表 FROM 表名
[WHERE条件]
[GROUP BY 分组列名]
[HAVING 分组后的条件过滤]
[ORDER BY 排序列名 排序方式]
LIMIT 当前页数,每页显示的条数
当前页数=(当前页数-1)*每页显示的条数
-- 每页显示3条数据,第一页
SELECT * FROM product LIMIT 0,3;
-- 每页显示3条数据,第二页
SELECT * FROM product LIMIT 3,3;
-- 每页显示3条数据,第三页
SELECT * FROM product LIMIT 6,3;
约束
约束分类
约束 | 说明 |
---|---|
PRIMARY KEY | 主键约束 |
PRIMARY KEY AUTO_INCREMENT | 主键、自动增长 |
UNIQUE | 唯一约束 |
NOT NULL | 非空约束 |
FOREIGN KEY | 外键约束 |
FOREIGN KEY ON UPDATE CASCADE | 外键级联更新 |
FOREIGN KEY ON DELETE CASCADE | 外键级联删除 |
主键约束
默认包含非空且唯一
一张表只能有一个主键
主键一般用于表中数据的唯一标识
建表时添加主键约束
语法:
CREATE TABLE IF NOT EXISTS 表名(
列名 数据类型 PRIMARY KEY,
...
列名 数据类型 约束
);
举例:
-- 创建一个学生表(编号、姓名、年龄) 编号设置为主键
CREATE TABLE IF NOT EXISTS student(
id INT PRIMARY KEY,
NAME VARCHAR(20),
age INT
);
删除主键约束
语法:
ALTER TABLE 表名 DROP PRIMARY KEY;
举例:
-- 删除主键
ALTER TABLE student DROP PRIMARY KEY;
建表后单独添加主键约束
语法:
ALTER TABLE 表名 MODIFY 列名 数据类型 PRIMARY KEY;
举例:
-- 建表后单独添加主键约束
ALTER TABLE student MODIFY id INT PRIMARY KEY;
主键自增约束
主键约束在添加数据时不能为NULL,但是主键自增约束可以为NULL。
MySQL中的自增约束,必须配合键的约束一起使用。
建表时添加主键自增约束
语法:
CREATE TABLE 表名 (
列名 数据类型 PRIMARY KEY AUTO_INCREMENT,
...
列名 数据类型 约束
);
举例:
-- 创建学生表(编号、姓名、年龄) 编号设置为主键自增
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
age INT
);
删除主键自增约束
语法:
ALTER TABLE 表名 MODIFY 列名 数据类型;
举例:
-- 删除自增约束
ALTER TABLE student MODIFY id INT;
建表后单独添加主键自增约束
语法:
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
举例:
-- 建表后单独添加主键自增约束
ALTER TABLE student MODIFY id INT AUTO_INCREMENT;
唯一约束(约束数据不能重复)
建表时添加唯一约束
语法:
CREATE TABLE 表名 (
列名 数据类型 UNIQUE,
...
列名 数据类型 约束
);
举例:
-- 创建学生表(编号、姓名、年龄) 编号设置为主键自增,年龄设为唯一
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30),
age INT UNIQUE
);
删除唯一约束
语法:
ALTER TABLE 表名 DROP INDEX 列名;
举例:
-- 删除唯一约束
ALTER TABLE student DROP INDEX age;
建表后单独添加唯一约束
如果此时表中已经存在数据不满足唯一约束,会报错
语法
ALTER TABLE 表名 MODIFY 列名 数据类型 UNIQUE;
举例:
-- 建表后单独添加唯一约束
ALTER TABLE student MODIFY age INT UNIQUE;
非空约束
建表时添加非空约束
语法:
CREATE TABLE 表名 (
列名 数据类型 NOT NULL,
...
列名 数据类型 约束
);
举例:
-- 创建学生表(编号、姓名、年龄) 编号设置为主键自增,年龄设为唯一,姓名设为非空
CREATE TABLE student (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
age INT UNIQUE
);
删除非空约束
语法:
ALTER TABLE 表名 MODIFY 列名 数据类型;
举例:
-- 删除非空约束
ALTER TABLE student MODIFY name VARCHAR(30);
建表后单独添加非空约束
如果,此时的表中要添加非空约束的列数据中已经存在NULL的数据,会将NULL值清空。
语法:
ALTER TABLE 表名 MODIFY 列名 数据类型 NOT NULL;
举例:
-- 建表后单独添加非空约束
ALTER TABLE student MODIFY name VARCHAR(30) NOT NULL;