Bjb Code

表操作

在 MySQL 中，你可以执行各种表操作来创建、修改和删除表。

创建表

CREATE TABLE employee(id INT PRIMARY KEY AUTO_INCREMENT COMMENT '记录id', --主键约束，并指定自动增长（必须是 int 型才可以设置自动增长）
code VARCHAR(36) UNIQUE NOT NULL COMMENT '代码', --唯一非空约束
name VARCHAR(150) COMMENT '姓名',
mobile_phone VARCHAR(55) COMMENT '手机号',
salary DOUBLE(5,2) COMMENT '薪水',
dept_id INT COMMENT '所属部门',
sex ENUM('1','0') NOT NULL DEFAULT '1' COMMENT '性别', --用枚举类型限定 sex 字段的值
create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间', --设置默认值
update_time DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
UNIQUE(name,mobilephone), --联合唯一约束
FOREIGN KEY(deptno) REFERENCES dept(id) ON DELETE [CASCADE|SET NULL|NO ACTION|RESTRICT] 或者 ON UPDATE [CASCADE|SET NULL|NO ACTION|RESTRICT] --外键约束（不能直接在列的后面新建，否则无效）
--PRIMARY key(id,code) --也可以使用联合主键
)COMMENT='雇员表';

创建表解析

• ON DELETE：定义了当主表中的记录被删除时应该采取的操作。常见的选项包括：
  • CASCADE：删除主表中的记录时，同时删除外键相关的从表记录。
  • SET NULL：删除主表中的记录时，将外键列设置为NULL。
  • RESTRICT：拒绝删除主表中的记录，只要从表中有引用该主键的记录存在。
• ON UPDATE：定义了当主表中的主键值更新时应该采取的操作，可选项与ON DELETE相同。

删除表

DROP TABLE table_name;

查看表

DESC table_name; --查看表的字段信息
SHOW INDEX FROM table_name; --查看表中所有索引

修改表

ALTER TABLE student ADD PRIMARY KEY(id); --给 id 列添加主键约束 
ALTER TABLE t_product ADD FOREIGN KEY(f_turnover_id) references t_turnover(id); --给表中字段添加外键
ALTER TABLE table_name ADD colname datatype; --增加字段，并指定字段的数据类型
ALTER TABLE table_name DROP colname; --删除字段
ALTER TABLE table_name MODIFY colname datatype UNIQUE NOT NULL COMMENT '注释'; --这一套必须写全，否则没写的内容会被重置
ALTER TABLE table_name.colname COMMENT='最近游戏列表'; --修改字段注释
RENAME TABLE table_name to table_name; --重命名表

索引

ALTER TABLE table_name ADD INDEX(colname); --添加普通索引
ALTER TABLE table_name ADD FULLTEXT(colname); --添加全文索引（表类型必须支持全文索引）
ALTER TABLE table_name ADD INDEX(colname1,colname2,...); --添加多列索引（联合索引）
ALTER TABLE table_name DROP INDEX idx_name; --删除索引（idx_name无需加单引号）
SHOW INDEX FROM table_name; --查看表的所有索引信息（Key_name 字段为索引名）

插入数据

INSERT INTO table_name(colname1,colname2,...) VALUES(valu1,valu2,...); --列和值一一对应
INSERT INTO table_name VALUES(colname1,colname2,...); --必须按顺序插完所有列
INSERT INTO tabblename VALUES(colname1,colname2,DEFAULT,...); --DEFAULT 指用该字段的默认值填充（不能不写）

删除数据

DELETE FROM table_name; --删除表中所有记录
DELETE FROM table_name WHERE condition; --删除指定记录

更新数据

UPDATE table_name SET colname1=valu1,colname2=valu2 WHERE conditon;
						
-- 更新前 100 条
UPDATE table_name SET colname1=valu1,colname2=valu2 WHERE conditon order by colname3 limit 100;

查询数据

去重

SELECT DISTINCT name FROM users;

NULL 查询

SELECT IFNULL(salary,0) FROM users; --如果薪水为 null，则转成 0
SELECT * FROM users WHERE name IS NULL; --查询 name 为 NULL 的数据
SELECT * FROM users WHERE name IS NOT NULL; --查询 name 不为 NULL 的数据

模糊查询

SELECT * FROM users WHERE name LIKE '_J'; --查询 name 的第一个字符不限，第二个字符是 J 的数据
SELECT * FROM users WHERE name LIKE '%A%'; --查询 name 包含 A 的数据
SELECT * FROM users WHERE name LIKE lower('%A%'); --不区分大小写的模糊查询
SELECT * FROM table_name WHERE name LIKE 'A%BC_'; --查询 name 已 A 开头，中间有 BC，结尾字符不限的数据

排序

SELECT * FROM users ORDER BY salary ASC; --按照薪水升序排序（可以不加 ASC，默认升序）
SELECT * FROM users ORDER BY salary DESC; --按照薪水降序排序
SELECT * FROM users ORDER BY salary DESC,id ASC; --按照薪水降序排序、按照 id 升序排序

分组查询

SELECT COUNT(*) FROM users; --查询 users 表中的行数
SELECT COUNT(salary) FROM users; --查询 users 表中有薪水的人（为空的不统计）
SELECT COUNT(salary),COUNT(mgr) FROM users; --查询 users 表中有薪水数据行数（为空的不统计），有领导的数据行数（为空的不统计）
SELECT SUM(salary) FROM users; --查询所有人的薪水总和
SELECT AVG(salary) FROM users; --查询所有人的平均薪水
SELECT MAX(salary) FROM users; --查询出最高薪水
SELECT MIN(salary) FROM users; --查询出最低薪水
SELECT SUM(salary) FROM users GROUP BY deptno; --按照部门分组，查询出每个部门的薪水总和
SELECT COUNT(*) FROM users WHERE salary>1500 GROUP BY deptno; --按照部门分组，查询出每个部门中薪水高于 1500 的人数
SELECT deptno,AVG(salary) FROM users GROUP BY deptno HAVING AVG(salary)>1500; --按照部门分组，查询出平均薪水高于 1500 的部门

分页查询

SELECT * FROM users LIMIT 0,5; --从第 0 行开始，往后查询 5 条记录
SELECT * FROM users LIMIT 3,10; --从第 3 行开始，往后查询 10 条记录

多表查询

SELECT * FROM users u,deptd WHERE u.deptno=d.deptno;
SELECT * FROM users u JOIN dept p ON u.deptno=d.deptno;
SELECT * FROM users u INNER JOIN dept p ON u.deptno=d.deptno;
SELECT * FROM users u RIGHT JOIN dept p ON u.deptno=d.deptno;
SELECT * FROM users u LEFT JOIN dept p ON u.deptno=d.deptno;