SQL语法

# 一、数据定义语言DDL

-- 创建数据库
-- CREATE DATABASE 数据库名;
-- CREATE DATABASE IF NOT EXISTS 数据库名;
-- CREATE DATABASE 数据库名 CHARACTER SET 字符集;
CREATE DATABASE mydb_01;
CREATE DATABASE IF NOT EXISTS mydb_02;
CREATE DATABASE mydb_03 CHARACTER SET gbk;

-- 查看所有数据库
SHOW DATABASES;
-- 查看某个数据库的定义信息
SHOW CREATE DATABASE mydb_01;
SHOW CREATE DATABASE mydb_02;
SHOW CREATE DATABASE mydb_03;

-- 修改数据库
-- 修改数据库默认的字符集
-- ALTER DATABASE 数据库名 DEFAULT CHARACTER SET 字符集;
ALTER DATABASE mydb_03 DEFAULT CHARACTER SET utf8mb4;

-- 删除数据库
-- DROP DATABASE 数据库名;
DROP DATABASE my_db03;

-- 使用数据库
-- USE 数据库名;
-- 查看正在使用的数据库
-- SELECT DATABASE(); 全局函数
USE mydb_01;
SELECT DATABASE();

-- 创建表
-- CREATE TABLE 表名 (
-- 		字段名1 字段类型,
-- 		字段名2 字段类型,
-- 		字段名3 字段类型
-- 		);				
CREATE TABLE student (
		id INT,
		name VARCHAR(10),
		gender VARCHAR(5),
		age INT,
		birthday date,
		address VARCHAR(50)
		);

-- 查看当前使用数据库中所有的表
SHOW TABLES;
-- 查看表结构
-- DESC 表名;
DESC student;
-- 查看创建表的sql语句
SHOW CREATE TABLE student;

-- 创建一个与旧表节后相同的新表
-- CREATE TABLE 新表名 LIKE 旧表名;
CREATE TABLE teacher LIKE student;
DESC teacher;

-- 删除表
-- DROP TABLE 表名;
-- DROP TABLE IF EXISTS 表名;
DROP TABLE teacher;
DROP TABLE IF EXISTS teacher;

-- 修改表结构
-- 添加字段
-- ALTER TABLE 表名 ADD 列名 字段类型;
ALTER TABLE student ADD score INT;
-- 修改字段类型
-- ALTER TABLE 表名 MODIFY 列名 新的字段类型;
ALTER TABLE student MODIFY score VARCHAR(20);
ALTER TABLE student MODIFY score INT;
-- 修改字段名,顺带可以修改字段的类型
-- ALTER TABLE 表名 CHANGE 旧字段名 新字段名 类型;
ALTER TABLE student CHANGE gender sex VARCHAR(10);
-- 删除列
-- ALTER TABLE 表名 DROP 列名;
ALTER TABLE student DROP score;
-- 修改表名
-- RENAME TABLE 表名 TO 新表名;
RENAME TABLE student TO person;
-- 修改表的字符集
-- ALTER TABLE 表名 CHARACTER SET 字符集;
ALTER TABLE person CHARACTER SET gbk;

# 二、数据操作语言DML
-- 插入数据
-- INSERT INTO 表名 (字段名1, 字段名2, 字段名3) VALUES (值1, 值2	, 值3); -- 全部字段或者部分字段
-- INSERT INTO 表名 VALUES (值1, 值2, 值3); -- 不写字段，添加所有字段
INSERT INTO person (id, name, sex, age, birthday, address) VALUES
		(1, '张三', '男', 18, '2003-10-22', '湖北省'), 
		(2, '李四', '男', 28, '19930620', '山西省'), 
		(3, '王五', '女', 30, '1991@1@28', '东北省');
INSERT INTO person VALUES (4, '刘六', '女', 28, '1993-10-30', '河南省');
SELECT * FROM person;
-- 如果插入数据出现乱码，则可能是字符集不匹配
-- 查看包含CHARACTER开头的全局变量
SHOW VARIABLES LIKE '%character%';

-- 蠕虫复制
-- 将一张已经存在的表中的数据复制到另一张表中
-- INSERT INTO 表名1 SELECT * FROM 表名2; -- 将表名2中的所有字段数据复制到表名1中
-- INSERT INTO 表名1 (字段1, 字段2) SELECT 字段1, 字段2 FROM 表名2; -- 将表名2中的部分字段数据复制到表名1中
CREATE TABLE student LIKE person;
INSERT INTO student (name, age) SELECT name, age FROM person;
INSERT INTO student SELECT * FROM person;

-- 更新表记录
-- UPDATE 表名 SET 字段名=值; -- 修改字段名类所有数据
-- UPDATE 表名 SET 字段名=值 WHERE 字段名=值; -- 带条件修改字段名类数据
UPDATE person SET sex='男';
UPDATE person SET sex='女' WHERE age<30;

-- 删除表记录
-- DELETE FROM 表名; -- 不带条件删除数据，不删除表的结构
-- DELETE FROM 表名 WHERE 字段名=值; -- 带条件删除数据
-- TRUNCATE TABLE 表名; -- 相当于删除表的结构再创建一张表
DELETE FROM student WHERE age>20;
DELETE FROM student;
TRUNCATE TABLE student;

# 三、数据查询语言DQL
-- 查询不会对数据库中的数据进行修改，只是一种显示数据的方式
-- SELECT * FROM 表名; SELECT * FROM 表名 WHERE 条件表达式; -- *表示所有字段, 查询表中的所有字段的数据
-- SELECT 字段1, 字段2, 字段3 FROM 表名; SELECT 字段1, 字段2, 字段3 FROM 表名 WHERE 条件表达式; -- 查询表中部分字段的数据
SELECT * FROM person;
SELECT * FROM person WHERE age>25;
SELECT name, address FROM person;
SELECT name, address FROM person WHERE age=30;

-- 指定字段来查询数据，显示使用新的名字，并不修改表的结构
-- SELECT 字段名1 AS 别名, 字段名2 AS 别名 FROM 表名;
-- SELECT 字段名1 AS 别名, 字段名2 AS 别名 FROM 表名 AS 别名; 
SELECT name AS 姓名, address AS 地址 FROM person;
SELECT name AS 姓名, address AS 地址 FROM person AS 客户;

-- 消除重复值
-- SELECT DISTINCT 字段名 FROM 表名; -- 指定查询的字段并且显示不重复的字段数据
SELECT DISTINCT age FROM person;

-- 查询结果参与运算，参与运算的必须是数值类型
-- SELECT 字段名+固定值 FROM 表名;
-- SELECT 字段名1+字段名2 FROM 表名;
SELECT age+10 FROM person;
SELECT age+id FROM person;

-- 条件查询，指定查询条件对数据进行过滤
-- SELECT 字段名 FROM 表名 WHERE 条件表达式;
SELECT * FROM person WHERE age>20;
SELECT name, address FROM person WHERE age>20;

-- 比较运算符不等于<>、!=
SELECT * FROM person WHERE age<>18;

-- BETWEEN...AND在范围之间,闭区间
SELECT * FROM person WHERE age BETWEEN 20 and 30;

-- IN(集合)集合表示多个值，使用逗号分隔
SELECT * FROM person WHERE age IN(28, 18);

-- LIKE'%值%' 模糊查询，%表示匹配任意多个字符，_表示匹配一个字符
SELECT * FROM person WHERE name LIKE'%__%';
SELECT * FROM person WHERE name LIKE'_三';
SELECT * FROM person WHERE age LIKE'28';

-- IS NULL 查询某个字段为NULL的值，查询NULL值时不能写=NULL这样使用，错误语法
SELECT * FROM person WHERE name IS NULL;

-- AND、&& 与，建议使用AND
-- OR、|| 或，建议使用OR
-- NOT、! 非，建议使用NOT
SELECT * FROM person WHERE sex='女' AND age<30;
SELECT * FROM person WHERE sex='女' OR age>25;
SELECT * FROM person WHERE age NOT IN(28,30);

-- 排序：ORDER BY 默认升序，ASC:升序、DESC:降序
-- 单列排序：只按照某一个字段进行排序
-- SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 [ASC|DESC];
SELECT * FROM person WHERE sex='女' ORDER BY age;
SELECT * FROM person WHERE sex='女' ORDER BY age DESC;

-- 组合排序：同时对多个字段进行排序
-- SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名1 [ASC|DESC], 字段名2 [ASC|DESC];
SELECT * FROM person ORDER BY age, id DESC;

-- 聚合函数：
-- max(字段名)：求此字段中数据的最大值
-- min(字段名)：求此字段中数据的最小值
-- avg(字段名)：求此字段中数据的平均值
-- count(字段名)：统计该字段有多少条记录，一般用来id统计
-- sum(字段名)：对这一列求总和
-- 语法:SELECT 聚合函数(字段名) FROM 表名;
SELECT MAX(age) 最大年龄 FROM person;
SELECT MIN(age) 最小年龄 FROM person;
SELECT AVG(age) 平均年龄 FROM person;
SELECT COUNT(id) 人数FROM person;
SELECT SUM(age) 年龄之和FROM person;

-- IFNULL(字段名,默认值)：如果字段数据不为NULL，返回此字段的值。若为NULL，则返回默认值
SELECT IFNULL(age,0) FROM person;
SELECT COUNT(IFNULL(id,0)) FROM person;

-- 分组查询：GROUP BY
-- SELECT 字段1, 字段2... FROM 表名 GROUP BY 分组字段[HAVING 条件];
SELECT sex, AVG(age) FROM person GROUP BY sex;
SELECT id, COUNT(*) from person GROUP BY id;
SELECT sex, COUNT(*) FROM person WHERE age>25 GROUP BY sex HAVING COUNT(*)>1;

-- LIMIT语句,限制的意思，限制查询数据的条数
-- SELECT 字段名 FROM 表名 LIMIT OFFSET LENGTH;
-- OFFSET:起始行数，从0开始，如果省略，默认就是0
-- LENGTH: 返回的行数
SELECT * FROM person LIMIT 2;
SELECT * FROM person LIMIT 2;
SELECT * FROM person LIMIT 2,6;

-- 数据库约束
-- 主键：PRIMARY KEY  -- 通常给非业务字段id作为主键，不重复、非空
-- 唯一：UNIQUE
-- 非空：NOT NULL
-- 外键：FOREIGN KEY
-- 检查约束：CHECK:mysql不支持
SELECT * FROM student;
ALTER TABLE student MODIFY id INT PRIMARY KEY;
INSERT INTO student VALUES
		(1, '关羽', '男', 29, '1999-10-29', '陕西省'),
		(2, '韩信', '男', 29, '1999-10-29', '陕西省'),
		(3, '赵云', '男', 29, '1999-10-29', '陕西省'),
		(11, '周瑜', '男', 29, '1999-10-29', '陕西省');
-- 删除主键
ALTER TABLE student DROP PRIMARY KEY; 
-- 添加主键
ALTER TABLE student ADD PRIMARY KEY(id);
-- 主键自增 AUTO_INCREMENT开始值是1，可以自定义起始值
ALTER TABLE student MODIFY id INT PRIMARY KEY AUTO_INCREMENT;
-- 修改起始值
ALTER TABLE student AUTO_INCREMENT = 2000;
-- 唯一约束
-- 字段名 字段类型 UNIQUE;
ALTER TABLE student MODIFY name VARCHAR(20) UNIQUE;

-- 非空约束
-- 字段名 字段类型 NOT NULL;
ALTER TABLE student MODIFY age int NOT NULL;

-- 默认值
-- 字段名 字段类型 DEFAULT 默认值;
ALTER TABLE student MODIFY address varchar(50) DEFAULT('中国');

-- 外键约束