创建数据库
– 如果指定的数据库不存在，就创建并设置默认的字符集以及排序方式
CREATE DATABASE IF NOT EXISTS db_name CHARACTER SET utf8 COLLATE utf8_general_ci;
– 显示当前存在的所有数据库
SHOW DATABASES;
– 查看数据库的创建形式(编码)
SHOW CREATE DATABASE db_name;
– 修改数据的字符编码
ALTER DATABASE db_name CHARACTER SET utf8;
– 如果数据库存在，则删除
DROP DATABASE IF EXISTS db_name;
– 应用数据库，指定 SQL 语句影响的是谁
USE db_name;
创建表
– 创建表，创建的时候必须指定列
CREATE TABLE stu_info(
info_id INT,
info_name VARCHAR(20),
info_sex BOOL,
info_class VARCHAR(20),
info_date DATETIME
) CHARACTER SET utf8 COLLATE utf8_general_ci;
– 查看表的字段信息(名称，类型，约束)
DESC stu_info;
– 显示指定数据库下的所有表
SHOW TABLES
– 显示表创建语句
SHOW CREATE TABLE table_name;
– 删除表
DROP TABLE table_name;
添加与修改字段
– 在指定表的结尾添加新的字段
ALTER TABLE table_name ADD column_name INT;
– 在指定表的开始添加新的字段
ALTER TABLE table_name ADD column_name INT FIRST;
– 在指定表的某列之后添加新的字段
ALTER TABLE table_name ADD column_name2 INT AFTER column_name;
– 修改指定表中字段的类型
ALTER TABLE table_name MODIFY column_name2 CHAR;
– 修改指定表中字段的类型和名称
ALTER TABLE table_name CHANGE column_name2 column_name2_new VARCHAR(30);
– 删除指定表中的字段
ALTER TABLE table_name DROP column_name;
– 向目标表中的所有字段添加一条记录
INSERT INTO stu_info VALUE(1, “小明”, ‘男’, ‘一班’, “2002/10/1”);
– 向目标表中的指定字段添加一条记录
INSERT INTO stu_info(info_name, info_class, info_date) VALUE( “小刚”, ‘二班’, now());
– 向目标表中的所有字段添加多条记录
INSERT INTO stu_info VALUES
(1, “中白”, ‘男’, ‘一班’, “2002/1/1”),
(2, “小青”, ‘男’, ‘二班’, “2003/4/1”),
(3, “大红”, ‘保密’, ‘一班’, “2022/1/1”),
(4, “小黄”, ‘女’, ‘二班’, “1999/1/1”),
(5, “中绿”, ‘女’, ‘一班’, “2222/1/1”),
(6, “小黑”, ‘保密’, ‘二班’, “2989/1/1”),
(7, “中紫”, ‘男’, ‘三班’, “1002/1/1”);
– 更新所有记录中指定字段的值
UPDATE stu_info SET info_sex=’女’;
– 带条件的更新符合条件的记录中指定字段的值
UPDATE stu_info SET info_sex=’男’ WHERE info_name=”小黄”;
– 删除满足条件的记录
DELETE FROM stu_info WHERE info_name=’小黄’;
– 删除所有的记录
DELETE FROM stu_info;
– 以存在约束的形式创建一张表
CREATE TABLE stu_info(
– 将 id 设置为主键(非空且唯一)，并且自增
info_id INT PRIMARY KEY AUTO_INCREMENT,
– 唯一且非空的名字
info_name VARCHAR(32) UNIQUE NOT NULL,
– 由于 mysql 没有检查约束，可以使用 ENUM 替代
info_sex ENUM(‘男’,’女’,’保密’) DEFAULT ‘保密’,
– 班级设置为另外一个表的主键(添加的时候必须是存在)
info_class INT NOT NULL,
– 生日，默认可以为空
info_day DATE DEFAULT NULL
);
– 需要被添加外键的表
CREATE TABLE stu_class(
class_id INT PRIMARY KEY AUTO_INCREMENT,
class_name VARCHAR(32) NOT NULL
);
– 为指定表中的字段添加外键为另一张表的指定字段，在添加外键的时候必须保证通过外键关联的两个字段的类型完全相同
ALTER TABLE stu_info ADD FOREIGN KEY(info_class) REFERENCES stu_class(class_id);
INSERT INTO stu_class(class_name) VALUES
(‘一班!!!!’), (‘二班!!!!’), (‘三班!!!!’);
INSERT INTO stu_info(info_name, info_sex, info_class, info_day) VALUES
(“中白”, ‘男’, 1, “2002/1/1”),
(“小青”, ‘男’, 3, “2003/4/1”),
(“大红”, ‘保密’, 2, “2022/1/1”),
(“小黄”, ‘女’, 2, “1999/1/1”),
(“中绿”, ‘女’, 1, “2222/1/1”),
(“小黑”, ‘保密’, 2, “2989/1/1”),
(“中紫”, ‘男’, 3, “1002/1/1”);
查询
– 查询一张表中的所有字段
SELECT * FROM stu_info;
– 查询一张表中的指定字段
SELECT info_name FROM stu_info;
– 对查询的内容进行去重
SELECT DISTINCT info_class FROM stu_info;
– 对查询到的内容进行运算
SELECT info_score*0.6 FROM stu_info;
– 为查询到的列设置别名
SELECT info_score*0.6 as 成绩 FROM stu_info;
– 带比较运算符的查询
SELECT info_name FROM stu_info WHERE info_class>1;
– 搜索某一个区间内内容
SELECT info_name, info_class FROM stu_info WHERE info_class BETWEEN 1 AND 2;
– 搜索在指定集合内的数据
SELECT info_name, info_class FROM stu_info WHERE info_class IN(1, 3);
– 使用字符串的模糊查询 %(任意长度的任意字符) _(单个任意字符)
SELECT * FROM stu_info WHERE info_day LIKE “____-01-01”;
– 带有多个条件的查询语句
SELECT * FROM stu_info WHERE info_sex=’女’ and info_name LIKE “xiao %”;
– 通过 ORDER BY 可以以指定列的顺序对结果进行排序，可以用于猜测字段的数量
SELECT * FROM stu_info ORDER BY 6 DESC(ASC);
内置函数
– 内置函数的使用: COUNT 求查询结果的数量
SELECT COUNT(*) FROM stu_info;
– 内置函数的使用: MIN\MAX 求查询结果的最大\最小值
SELECT COUNT(info_score) FROM stu_info;
– 内置函数的使用: NOW 获取当前的时间
SELECT NOW();
– 内置函数的使用: DM5 计算 MD5
SELECT MD5(‘123’);
联合查询
– 联合查询: 同时执行多条查询语句，将结果一起输出 union
SELECT info_name FROM stu_INFO WHERE INFO_ID=[-1 UNION SELECT VERSION()] LIMIT 0, 1;
– 联合查询是自动去重的，通过 ALL 可以获取所有数据
SELECT 1 UNION ALL SELECT 1;
– 分组查询
SELECT * FROM stu_info;
分组查询
– 分组查询，在这个例子中，先查询到每个组，再执行 COUNT 操作
SELECT info_class, COUNT(*) FROM stu_info GROUP BY info_class;
– 这里获取了每一组的内容
SELECT DISTINCT info_class FROM stu_info;
SELECT COUNT() FROM stu_info WHERE info_class=1 UNION ALL
SELECT COUNT() FROM stu_info WHERE info_class=2 UNION ALL
SELECT COUNT(*) FROM stu_info WHERE info_class=3;
– 带条件的分组查询，查询班级号大于 2 的班级有多少人
SELECT info_class FROM stu_info GROUP BY info_class HAVING info_class > 2;
– 分页显示查询结果（从第0条开始，取3条）
SELECT * from table_name LIMIT 0, 3;
– 交叉连接，产生的结果是笛卡尔积
SELECT * FROM stu_info, stu_class;
– 内连接，带条件的多表查询
SELECT * FROM stu_info, stu_class WHERE stu_info.info_class = stu_class.class_id;
– 即使右边的表不存在左边的字段，也会以 null 的方式打印出来
select * from stu_info left join stu_class on stu_info.info_class = stu_class.class_id;
– 如果这个班级位于下面的搜索结果中，就输出所有信息
SELECT * FROM stu_class WHERE class_id in (
– 如果有同学是男生，就输出它的班级号
SELECT info_class FROM stu_info WHERE info_sex = “男”
);
– 如果有同学的分数大于80，就输出前三名的信息和对应的班级信息
SELECT * FROM stu_class, stu_info WHERE EXISTS(
– 搜索目标成绩大于 80 分的学生，如果有就是 TRUE，否则是 FALSE
SELECT info_score FROM stu_info WHERE info_score > 80
) AND stu_class.class_id = stu_info.info_class
ORDER BY info_score DESC LIMIT 0, 3;
– 如果有男生的成绩大于任何一个女生，并且存在对应的班级信息，就输出这个男生的信息
SELECT * FROM stu_info, stu_class WHERE
info_score <ANY(
SELECT info_score FROM stu_info WHERE info_sex=”女”
) AND
info_sex=’男’ AND stu_info.info_class = stu_class.class_id;
– 如果有男生的成绩大于所有女生，并且存在对应的班级信息，就输出这个男生的信息
SELECT * FROM stu_info, stu_class WHERE
info_score <ANY(
SELECT info_score FROM stu_info WHERE info_sex=”女”
) AND
info_sex=’男’ AND stu_info.info_class = stu_class.class_id;
数据备份
– 数据库备份
mysqldump -u root -p tankwar_net > d:/tankwar_net_10_15.sql
– 数据库还原 一
mysqldump -u root -p tankwar_net < d:/tankwar_net_10_15.sql
– 数据库还原 二
mysql> use tankwar_net
Database changed
mysql> source d:/tankwar_net_10_15.sql;
