MySQL各种姿势操作

1. 改表列的顺序

ALTER TABLE teacherInfo  MODIFY sex VARCHAR(4) AFTER birthday;

2. 修改列名

ALTER TABLE teacherInfo CHANGE COLUMN num t_id INT(10);

3. 删除列

ALTER TABLE teacherInfo DROP COLUMN address;

4. 添加字段

ALTER TABLE teacherInfo ADD COLUMN wages FLOAT;

5. 修改表名

ALTER TABLE teacherInfo RENAME teacherInfoInfo;

6. 创建表时定义外键

CREATE TABLE worker (
	id INT(4) PRIMARY KEY NOT NULL,
	num INT(10) NOT NULL UNIQUE,
	d_id INT(4) ,
	name VARCHAR(20) NOT NULL,
	sex VARCHAR(4) NOT NULL,
	birthday DATE ,
	address VARCHAR(50),
	FOREIGN KEY(d_id) REFERENCES department(d_id)
	);

7. 查看创建数据库时的语句

SHOW CREATE TABLE worker;

8. 删除外键约束

外键约束名可以通过上一条语句查看

alter table worker drop foreign key worker_ibfk_1;

8. 修改数据

UPDATA TABLE teacherInfo SET birthday='1982-11-08' WHERE num=1003;

10. 删除数据

DELETE FROM teacherInfo  WHERE num=1002;

11. 查询指定行的记录

查询 bumen 表中的第 4、5 条数据。LIMIT 第一个参数表示查询开始的下标（从 0 开始），第二个参数表示查询多少条数据。

SELECT * FROM bumen LIMIT 3,2;

12. 查询指定范围内的数据

查询 yuangong 表中年龄在 25 - 30 之间的数据。

SELECT * FROM yuangong WHERE birthday >= 25 AND birthday <= 30;

13. 分组查询，统计每组的数据数目

SELECT d_id, COUNT(d_id) FROM yuangong GROUP BY d_id;

14. 分组查询每个组最大值

根据部门查询每个部门的最高工资

SELECT d_id,MAX(salary) FROM yuangong GROUP BY d_id;

15. 左连接查询

以左边的数据表为准进行联合查询

SELECT * FROM bumen LEFT JOIN yuangong ON bumen.d_id=yuangong.d_id;

16. 分组查询并计算和

按部门分组，并计算每个部门的工资和

SELECT d_id, SUM(salary) FROM yuangong GROUP BY d_id;

17. 查询并按指定列降序排序

DESC 参数表示降序，去掉参数升序排序

SELECT * FROM yuangong ORDER BY salary DESC;

18. 删除索引

DROP INDEX index_name ON table_name;

ALTER TABLE table_name DROP INDEX index_name;

ALTER TABLE table_name FROP primary KEY;

19. 查看表结构

DESC table_name;

20. 创建视图

CREATE VIEW info_view AS SELECT id, name, sex, address FROM work_info;

21. 查看视图

DESC view_name;

22. 更新视图

UPDATE vire_name SET sex='男' WHERE id=4;

23. 删除视图

DROP VIEW view_name;

24. 创建存储过程

DELIMITER //

CREATE PROCEDURE Pfood_price_count( IN price_info1 FLOAT,IN price_info2 FLOAT, OUT count)
BEGIN
	SELECT COUNT(*), SUM(price) INTO count,@sm FROM food WHERE price BETWEEN price_info1 AND price_info2; 
END;//

DELIMITER ;
CALL pfood_price_count(2, 18, @cnt)
SELECT cnt;

25. 创建、调用函数

DELIMITER //

CREATE FUNCTION Pfood_price_count(price_info1 FLOAT,price_info2 FLOAT)
RETURNS INT
DETERMINISTIC
BEGIN
	DECLARE cnt INT(4);
	DECLARE sm FLOAT;
	SELECT COUNT(*), SUM(price) INTO cnt,sm FROM food WHERE price BETWEEN price_info1 AND price_info2; 
	RETURN cnt;
END;//

DELIMITER ;
select pfood_price_count(2, 18)

26. 创建触发器

DELIMITER //
CREATE TRIGGER 触发器名字 AFTER|BEFORE 类型(insert|delete|update|...)
ON 表名 FOR EACH ROW
BEGIN
	触发器执行的sql语句
END;//
DELIMITER ;

27. 用mysqldump备份数据表

导出 sql 类型文件，可以通过改后缀名来改变文件类型

mysqldump -u root -p dbname tablename > D:\backup\student.sql

28. 使用mysql还原数据库

mysql -u root -p dbname < D:\backup\student.sql

---

冬杨 - 微信公众号「大学IT圈」