Mysql语句大全(以自己的一个数据库为例)

–表示的是问题，每行语句没有添加";"故应该每条语句单独执行

mysql会使用太多的语句，而且查找起来比较麻烦，就差不多集合了常见的sql语句，可以快速查找并使用

– 创建数据库
CREATE DATABASE student
– 删除数据库
DROP DATABASE student

– 结构化查询语言
– 1.DML 数据操作语言 insert delete update
– 2.DOL 数据查询语言 select
– 3.DDL 数据定义语言 create drop alter
– 4.DCL 数据控制语言 grant commit rollback

– 创建20191229db数据库
CREATE DATABASE 20191229db

– 创建学生表student(stuno,name,sex,age,birthday)
CREATE TABLE student(
stuno INT ,
NAME VARCHAR(20),
sex VARCHAR(4),
age INT,
birthday TIMESTAMP)

– 删除student表
DROP TABLE student

– student表增加一列tel
ALTER TABLE student ADD COLUMN tel VARCHAR(20)

– 修改tel列名为phone varchar(15)
ALTER TABLE student CHANGE tel phone VARCHAR(15)

– 删除student表的sex列
ALTER TABLE student DROP sex

– 修改表名student表为teacher表
ALTER TABLE student RENAME TO teacher

– 插入数据
– 1. 张三 18 2001-1-1
– 2. 李四 22 1998-5-1
– 3. 王五 30 1989-7-1
– 4. 赵六 16 2003-8-1
– 5. 麻七 23 1997-10-1

INSERT INTO teacher(stuno,NAME,age,birthday)
VALUES(1,‘张三’,18,‘2001-1-1’);
INSERT INTO teacher(stuno,NAME,age,birthday,phone)
VALUES(2,‘李四’,22,‘1998-5-1’,‘10081’);
INSERT INTO teacher(stuno,NAME,age,birthday,phone)
VALUES(3,‘王五’,30,‘1987-7-1’,‘10082’);
INSERT INTO teacher(stuno,NAME,age,birthday,phone)
VALUES(4,‘赵六’,16,‘2003-8-1’,‘10083’);
INSERT INTO teacher(stuno,NAME,age,birthday)
VALUES(5,‘麻七’,23,‘1997-10-1’);

– 修改stuno编码为2的年龄修改为30岁
UPDATE teacher SET age=30 WHERE stuno = 2

– 删除studo编号为3的信息
DELETE FROM teacher WHERE stuno=3

– char定长（定义20长度就是20） varchar不定长（定义20，占2字节就是2字节）

– 修改teacher表为student表
ALTER TABLE teacher RENAME TO student
– 删除age字段
ALTER TABLE student DROP age
– 增加address字段
ALTER TABLE student ADD COLUMN address VARCHAR(30)
– 增加stuno主键约束
ALTER TABLE student ADD CONSTRAINT PK_student_no PRIMARY KEY PK_student_no(stuno)
– 增加name唯一约束
ALTER TABLE student ADD CONSTRAINT UK_student_name UNIQUE KEY UK_student_name(NAME)
– 增加电话号码的唯一约束
ALTER TABLE student ADD CONSTRAINT UK_student_pho UNIQUE KEY UK_student_pho(phone)
– 删除电话号码的唯一约束
ALTER TABLE student DROP INDEX UK_student_pho
– 删除主键约束
ALTER TABLE student DROP PRIMARY KEY

– 创建一个成绩表grade（gid,stuno,score,cid）
CREATE TABLE grade(gid INT PRIMARY KEY AUTO_INCREMENT,
stuno INT,
score DOUBLE,
cid INT)

– 创建课程表course（cid,courname)
CREATE TABLE course(cid INT PRIMARY KEY AUTO_INCREMENT,
coursename VARCHAR(20)
)
– 将grade表的cid设置为course表的cid的外键
ALTER TABLE grade ADD CONSTRAINT fk_grade_cid FOREIGN KEY(cid) REFERENCES course(cid)
– 将grade表的stuno设置为student表的cid的外键
ALTER TABLE grade ADD CONSTRAINT fk_grade_stuno FOREIGN KEY(stuno)REFERENCES student(stuno)

– 查询所有的学生信息
SELECT * FROM student

– 修改stuno为1的address为北京
UPDATE student SET address = ‘北京’ WHERE stuno=‘1’ ;
– 2 为广东省深圳市
UPDATE student SET address = ‘广东省深圳市’ WHERE stuno=‘2’;
– 4为江苏省南京市
UPDATE student SET address = ‘江苏省南京市’ WHERE stuno=‘4’;
– 5为上海市
UPDATE student SET address = ‘上海市’ WHERE stuno=‘5’;

– 添加课程信息(因为cid是自增，可不设)
INSERT INTO course (coursename) VALUES(‘java’)
INSERT INTO course (coursename) VALUES(‘数据结构’);
INSERT INTO course (coursename) VALUES(‘高等数学’);
INSERT INTO course (coursename) VALUES(‘大学英语’);

– 添加学生成绩信息
INSERT INTO grade(stuno,score,cid) VALUES (1,99,1);
INSERT INTO grade(stuno,score,cid) VALUES (1,89,2);
INSERT INTO grade(stuno,score,cid) VALUES (1,59,3);
INSERT INTO grade(stuno,score,cid) VALUES (2,88,1);
INSERT INTO grade(stuno,score,cid) VALUES (2,78,2);
INSERT INTO grade(stuno,score,cid) VALUES (4,70,1);
INSERT INTO grade(stuno,score,cid) VALUES (5,80,1);

– 统计课程id为1的平均成绩
SELECT AVG(score) FROM grade WHERE cid=1;
– 计算课程id为1的最高成绩
SELECT MAX(score) FROM grade WHERE cid=1;
– 统计选修了课程id为2的学生人数
SELECT COUNT() FROM grade WHERE cid=2;
– 统计每科课程的学生人数
SELECT cid AS ‘课程id’,COUNT() ‘学生人数’ FROM grade GROUP BY cid ;
SELECT cid ,COUNT(*) FROM grade GROUP BY cid;
– 查询课程id为1的学生成绩倒序
SELECT score FROM grade WHERE cid =1 ORDER BY score DESC;
– 查询所有的学生信息 前2条学生信息数据
SELECT * FROM student LIMIT 0,2;

– 分组查询 group by
– 使用分组查询时select后面只能是聚合函数和group by 后面的字段
– 常见的聚合函数 sum、max、min、avg、count