mysql基本操作笔记
以下是本人在学习过程中接触过的mysql基本命令，仅做参考
-- 删除表，然后再创建一个一模一样的表。比delete命令效率更高 TRUNCATE TABLE stu; -- 创建联系的数据库 CREATE TABLE student1 ( id INT, -- 编号 NAME VARCHAR(20), -- 姓名 age INT, -- 年龄 sex VARCHAR(5), -- 性别 address VARCHAR(100), -- 地址 math INT, -- 数学 english INT -- 英语 ); INSERT INTO student1(id,NAME,age,sex,address,math,english) VALUES (1,'马云',55,'男','杭州',66,78), (2,'马化腾',45,'女','深圳',98,87), (3,'马景涛',55,'男','香港',56,77), (4,'柳岩',20,'女','湖南',76,65), (5,'柳青',20,'男','湖南',86,NULL), (6,'刘德华',57,'男','香港',99,99), (7,'马德',22,'女','香港',99,99), (8,'德玛西亚',18,'男','南京',56,65); DROP TABLE student1; SELECT * FROM student1; -- 查询姓名和年龄 SELECT `name`, -- 姓名 age -- 年龄 FROM student1;-- 学生表 -- 去除重复结果集 SELECT DISTINCT address FROM student1; -- 去重要求查询的结果集完全一致 SELECT DISTINCT `name`,address FROM student1; -- 计算math和english的分数之和 -- 有null参与计算，结果都为null SELECT `name`,math,english,math+english FROM student1; -- 解决方案IFNULL(表达式1，表达式2) 如果表达式1为null则显示表达式2，反之显示表达式1 SELECT `name`,math,english,math+IFNULL(english,0) FROM student1; -- 起别名，可省略AS关键字 SELECT `name`,math,english,math+IFNULL(english,0) AS 总分 FROM student1; SELECT `name` 姓名,math 数学,english 英语,math+IFNULL(english,0) 总分 FROM student1; -- 查询年龄大于20 SELECT * FROM student1 WHERE age >=20; -- 查询年龄不等于20 SELECT * FROM student1 WHERE age !=20; SELECT * FROM student1 WHERE age <>20; -- 查询年龄大于等于20 小于等于30 SELECT * FROM student1 WHERE age >=20 && age<=30; SELECT * FROM student1 WHERE age >=20 AND age<=30; SELECT * FROM student1 WHERE age BETWEEN 20 AND 30; -- 查询年龄为22,18,25岁的信息 SELECT * FROM student1 WHERE age = 22 || age=18 || age=25; SELECT * FROM student1 WHERE age = 22 OR age=18 OR age=25; SELECT * FROM student1 WHERE age IN (22,18,25); -- 查询没有英语成绩的信息 SELECT * FROM student1 WHERE english = NULL; -- 不正确的语句，不能使用等号判断null SELECT * FROM student1 WHERE english IS NULL; -- 查询有英语成绩的信息 SELECT * FROM student1 WHERE english IS NOT NULL; -- 查询姓马的 SELECT * FROM student1 WHERE `name` LIKE "马%"; -- 查询第二字是化的人 SELECT * FROM student1 WHERE `name` LIKE "_化%"; -- 查询姓名是三个字的人 SELECT * FROM student1 WHERE `name` LIKE "___"; -- 三个_ -- 查询姓名中包含德的人 SELECT * FROM student1 WHERE `name` LIKE "%德%"; -- 排序 SELECT * FROM student1 ORDER BY math; -- 默认升序 SELECT * FROM student1 ORDER BY math DESC; -- 降序 SELECT * FROM student1 ORDER BY math ASC; -- 升序 SELECT * FROM student1 ORDER BY math ASC , english ASC; -- 第二排序条件，只有当第一条件相同是第二条件才生效 -- 所有聚合函数的计算都排除了null值 SELECT COUNT(`name`) 人数 FROM student1; -- 结果为8 SELECT COUNT(english) 人数 FROM student1; -- 结果为7 SELECT COUNT(IFNULL(english,0)) 人数 FROM student1; -- 结果为8 SELECT MAX(math) FROM student1; SELECT MIN(math) FROM student1; SELECT SUM(math) FROM student1; SELECT SUM(english) FROM student1; SELECT AVG(math) FROM student1; SELECT AVG(english) FROM student1; -- 按照性别分别查询男、女同学的平均分 SELECT sex 性别,AVG(math+english) 总分 FROM student1 GROUP BY sex; SELECT sex 性别,AVG(math) 平均分,COUNT(id) 人数 FROM student1 GROUP BY sex; -- 按照性别分别查询男、女同学的平均分但不计算分数低于70的 SELECT sex 性别,AVG(math) 平均分,COUNT(id) 人数 FROM student1 WHERE math> 70 GROUP BY sex; -- 按照性别分别查询男、女同学的平均分但不计算分数低于70的并且不显示人数小于等于2人的 SELECT sex 性别,AVG(math) 平均分,COUNT(id) 人数 FROM student1 WHERE math> 70 GROUP BY sex HAVING COUNT(id) > 2; -- 分页公式：开始的索引=(当前的页码-1)*每页显示的条数 SELECT * FROM student1 LIMIT 0,3; SELECT * FROM student1 LIMIT 3,6; SELECT * FROM student1 LIMIT 6,9; -- 不满一页按一页计 -- limit是mysql的方言，其他数据库不一定通用 -- 约束 -- 非空约束 -- 创建表示添加约束 CREATE TABLE stu( id INT, `name` VARCHAR(20) NOT NULL -- name非空 ); ALTER TABLE stu MODIFY `name` VARCHAR(20); -- 创建完成后，添加非空约束 ALTER TABLE stu MODIFY `name` VARCHAR(20) NOT NULL; SELECT * FROM stu; DROP TABLE stu; -- 唯一约束 CREATE TABLE stu( id INT , phone_number VARCHAR(20) UNIQUE -- 添加了唯一约束 ); -- 注意：mysql中唯一约束限定的列的值可以有多个null SELECT * FROM stu; -- 删除唯一约束 -- alter table stu modify phone_number varchar(20);此命令不能删除唯一约束 ALTER TABLE stu DROP INDEX phone_number; -- 创建表后添加唯一约束 ALTER TABLE stu MODIFY phone_number VARCHAR(20) UNIQUE; DROP TABLE stu; -- 主键约束 CREATE TABLE stu( id INT PRIMARY KEY, -- 给id添加主键约束 `name` VARCHAR(20) ); SELECT * FROM stu; -- 删除主键 -- alter table stu modify id int; 此命令不能清除主键约束 ALTER TABLE stu DROP PRIMARY KEY; -- 创建完成后，添加非空约束 ALTER TABLE stu MODIFY id INT PRIMARY KEY; DROP TABLE stu; -- 主键自增 CREATE TABLE stu( id INT PRIMARY KEY AUTO_INCREMENT, -- 给id添加主键约束并令其自增 `name` VARCHAR(20) ); SELECT * FROM stu; INSERT INTO stu VALUES(NULL,'ccc'); -- 删除自动增长 ALTER TABLE stu MODIFY id INT; -- 添加自动增长 ALTER TABLE stu MODIFY id INT AUTO_INCREMENT; -- 注意：自动增长可以不和主键一起使用 -- 外键约束 -- 创建表 CREATE TABLE emp ( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(30), age INT, dep_name VARCHAR(30), dep_location VARCHAR(30) ); -- 添加数据 INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('张三', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('李四', 21, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('王五', 20, '研发部', '广州'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('老王', 20, '销售部', '深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('大王', 22, '销售部', '深圳'); INSERT INTO emp (NAME, age, dep_name, dep_location) VALUES ('小王', 18, '销售部', '深圳'); SELECT * FROM emp; -- 数据有冗余 -- 进行表的拆分 -- 解决方案：分成 2 张表 -- 创建部门表(id,dep_name,dep_location) -- 一方，主表 CREATE TABLE department( id INT PRIMARY KEY AUTO_INCREMENT, dep_name VARCHAR(20), dep_location VARCHAR(20) ); -- 创建员工表(id,name,age,dep_id) -- 多方，从表 CREATE TABLE employee( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(20), age INT, dep_id INT ,-- 外键对应主表的主键 CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES department(id) ); -- 添加 2 个部门 INSERT INTO department VALUES(NULL, '研发部','广州'),(NULL, '销售部', '深圳'); SELECT * FROM department; -- 添加员工,dep_id 表示员工所在的部门 INSERT INTO employee (NAME, age, dep_id) VALUES ('张三', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('李四', 21, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('王五', 20, 1); INSERT INTO employee (NAME, age, dep_id) VALUES ('老王', 20, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('大王', 22, 2); INSERT INTO employee (NAME, age, dep_id) VALUES ('小王', 18, 2); SELECT * FROM employee; DROP TABLE emp; DROP TABLE department; DROP TABLE employee; -- 删除外键 ALTER TABLE employee DROP FOREIGN KEY emp_dept_fk; -- 在表创建之后添加外键 ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES department(id); -- 级联操作 -- 级联更新 ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON UPDATE CASCADE; -- 级联删除 ALTER TABLE employee ADD CONSTRAINT emp_dept_fk FOREIGN KEY(dep_id) REFERENCES department(id) ON DELETE CASCADE;
