mysql5.5数据库操作_数据库(mysql5.5)的一些基本的操作

CREATE DATABASE school;

//查看所有数据库

SHOW DATABASES;

USE school;

//创建第一个表 qy97;

CREATE TABLE qy97(id INT(10) PRIMARY KEY AUTO_INCREMENT,sname VARCHAR(10));

//查询创建的表

SELECT * FROM qy97;

DESC qy97;

SHOW TABLES;

//向表中添加字段

ALTER TABLE qy97 ADD age INT(20);

ALTER TABLE qy97 ADD addr VARCHAR(20);

SELECT * FROM qy97;

DESC qy97;

//修改表中字段名

ALTER TABLE qy97 CHANGE addr grade VARCHAR(10);

DESC qy97;

//修改字段类型

ALTER TABLE qy97 MODIFY grade INT(10);

DESC qy97;

//删除主键 首先把主键自增删除

ALTER TABLE qy97 MODIFY id INT NOT NULL;

DESC qy97;

ALTER TABLE qy97 DROP PRIMARY KEY ;

DESC qy97;

//添加主键

ALTER TABLE qy97 MODIFY id INT PRIMARY KEY;

DESC qy97;

//添加id自增

ALTER TABLE qy97 MODIFY id INT AUTO_INCREMENT;

DESC qy97;

//重命名表名

RENAME TABLE qy97 TO class1;

DESC class1;

ALTER TABLE class1 RENAME class2;

SHOW TABLES;

DESC class2;

//删除表中的字段

ALTER TABLE class2 DROP grade;

//向表中添加数据

INSERT INTO class2 VALUES (1,'韩高峰',20);

SELECT * FROM class2;

INSERT INTO class2 (sname,age) VALUES('杨蒙蒙',19);

INSERT INTO class2 (sname, age) VALUES ('胡歌',30);

INSERT INTO class2 (sname ,age) VALUES ('贾玲',35);

INSERT INTO class2 (sname,age) VALUES ('胡歌',35);

INSERT INTO class2 (sname,age) VALUES ('李易峰',30);

INSERT INTO class2 (sname, age) VALUES(NULL,NULL);

//修改表中数据

UPDATE class2 SET age = 40;

SELECT * FROM class2;

UPDATE class2 SET age = 19 WHERE sname ='杨蒙蒙';

UPDATE class2 SET age = 20 WHERE id = 1;

UPDATE class2 SET age = 30 WHERE age BETWEEN 35 AND 45;

UPDATE class2 SET sname= '李易峰'WHERE age = 30 AND sname = '胡歌';

//查空 IS NULL /IS NOT NULL;

SELECT * FROM class2 WHERE sname OR age IS NULL;

SELECT * FROM class2 WHERE (sname AND age) IS NULL;

SELECT * FROM class2 WHERE (sname AND age)IS NOT NULL;

//去重查询 SELECT DISTINCT 字段名 FROM 表名

SELECT DISTINCT sname FROM class2;

//别名查询 AS

SELECT sname AS '姓名' FROM class2;

SELECT id AS '编号' FROM class2;

SELECT age AS '年龄' FROM class2;

//模糊查询 LIKE

SELECT * FROM class2 WHERE sname LIKE '%李%';

SELECT * FROM class2 WHERE sname LIKE '___';

//排序 降序DESC 升序 ASC

SELECT *FROM class2 ORDER BY id DESC;

SELECT * FROM class2 ORDER BY id ASC;

SELECT * FROM class2 ORDER BY age ASC;

SELECT * FROM class2 ORDER BY id DESC;

//聚合函数 总行数

SELECT COUNT(*)AS '总行数' FROM class2;

SELECT * FROM class2;

//MIN AS别名(可写可不写)

SELECT id 'id', sname '姓名',MIN(age) '年龄最小' FROM class2;

//MAX 最大值

SELECT id AS 'id',sname AS '姓名', MAX(age) AS '年龄最大' FROM class2;

//AVG 平均值

SELECT id ,sname AS '姓名',AVG(age) AS '平均年龄' FROM class2;

// SUM 总和

SELECT id , sname AS '姓名', SUM(age) AS '年龄总和' FROM class2;