出售 购买mysql语句_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

编辑整理 丨李思澄