Mysql数据库常用语句

DESC class;

-- 删除主键
ALTER TABLE class DROP PRIMARY KEY;

-- 删除字段
ALTER TABLE class DROP COLUMN id;

-- 添加主键约束
ALTER TABLE class ADD PRIMARY KEY(NAME);

-- 添加字段
ALTER TABLE student ADD COLUMN class VARCHAR(10);

-- 添加外键约束,使student的class引用class的name;
ALTER TABLE student ADD FOREIGN KEY fk_student_class(class) REFERENCES class(NAME);

-- 添加default
ALTER TABLE student ALTER COLUMN class SET DEFAULT '未报名';
ALTER TABLE student ALTER COLUMN age SET DEFAULT 00;

-- 添加数据  表名(字段) value(数据)
INSERT INTO student(id,NAME,age,class) VALUE(1,'卡卡西',34,'三班');
INSERT INTO student(id,NAME,age,class) VALUE(2,'李逵',45,NULL);
INSERT INTO student(id,NAME,class) VALUE(3,'宋江','四班');
INSERT INTO student(id,NAME,age,class) VALUE(4,'张飞',47,'三班');
-- 一次录入多行数据
INSERT INTO student(id,NAME,age,class) VALUE(5,'孙悟空',500,'一班'),(6,'武松',38,'二班'),(7,'赵云',75,'一班'),(8,'柯南',10,'三班'),(9,'盖伦',33,'五班');

-- 更新表数据
UPDATE student SET class='四班' WHERE NAME='李逵';
UPDATE student SET age=55 WHERE NAME='宋江';
-- 查询

-- 查询表中所有字段
SELECT id,NAME,age,class FROM student;
-- 等价于
SELECT * FROM student;

-- 查询表中的四班的student
SELECT * FROM student WHERE class='四班';

-- 查询每个班有多少个学生
SELECT class,COUNT(*) FROM student GROUP BY class;

-- 查询学生数目不足3的班级
SELECT class,COUNT(*) FROM student GROUP BY class HAVING COUNT(*)<3;

-- 按年龄从大到小显示student表
SELECT * FROM student ORDER BY age DESC;

-- 按年龄从小到大显示student表
SELECT * FROM student ORDER BY age ASC;

-- 笛卡尔积(总行数=student行数*class行数)
SELECT * FROM student,class;

-- 内连接
SELECT * FROM student INNER JOIN class ON student.class=class.name;

-- 左外连接
SELECT * FROM student LEFT JOIN class ON student.class=class.name;

-- 右外连接
SELECT * FROM student RIGHT JOIN class ON student.class=class.name;


-- DEMO
CREATE TABLE tba(a1 INT,a2 INT);
CREATE TABLE tbb(b1 INT,b2 INT);

INSERT INTO tba(a1,a2) VALUES(1,2),(3,4);
INSERT INTO tbb(b1,b2) VALUES(1,5),(8,10);
-- 笛卡尔积
SELECT * FROM tba,tbb
-- 内连接
SELECT * FROM tba INNER JOIN tbb ON tba.a1=tbb.b1;
-- 左外连接
SELECT * FROM tba LEFT JOIN tbb ON tba.a1=tbb.b1;
-- 右外连接
SELECT * FROM tba RIGHT JOIN tbb ON tba.a1=tbb.b1;
-- 全外连接
SELECT * FROM tba LEFT JOIN tbb ON tba.a1=tbb.b1;
UNION
SELECT * FROM tba RIGHT JOIN tbb ON tba.a1=tbb.b1;



-- 根据学生的年龄以5结尾查询
SELECT * FROM student WHERE student.age LIKE '%5';

-- 根据学生的年龄以5结尾,班级是四班查询
SELECT * FROM student WHERE student.age LIKE '%5' AND student.class='四班';

-- 使用子查询
SELECT * FROM student WHERE NAME IN (SELECT NAME FROM student WHERE age>40 AND age<100);
SELECT NAME FROM student WHERE age>40 AND age<100 ;
-- 子查询作为条件
SELECT * FROM student WHERE age> ANY (SELECT age FROM student WHERE age>40 AND age<100);
SELECT * FROM student WHERE age > ALL (SELECT age FROM student WHERE age>40 AND age<100);

-- 子查询作为操作数
SELECT (SELECT NAME FROM student WHERE NAME='李逵') FROM DUAL;

-- 多列比较()
SELECT (1,6,4)<(8,3,5) FROM DUAL;


-- 更新数据
UPDATE student SET age=88 WHERE NAME="张飞";

-- 删除数据
DELETE FROM student WHERE NAME='赵云';

-- 删除表
DROP TABLE teacher;

创建的库class

创建的库student