SQL常用语句

ALTER TABLE student1 RENAME AS student2
ALTER TABLE student2 ADD age INT(11)
ALTER TABLE student2 MODIFY age VARCHAR(11)
ALTER TABLE student2 CHANGE age age1 INT(1)

ALTER TABLE student2 DROP age1

DROP TABLE IF EXISTS student2

CREATE TABLE grade(
gradeid INT(10) NOT NULL AUTO_INCREMENT COMMENT ‘年级id’,
name VARCHAR(50) NOT NULL COMMENT ‘年纪名称’,
PRIMARY KEY (gradeid)
)ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO grade (name) VALUES(‘大三’),(‘大二’)

CREATE TABLE IF NOT EXISTS student(
id INT(4) NOT NULL AUTO_INCREMENT COMMENT ‘学号’,
name VARCHAR(30) NOT NULL DEFAULT ‘匿名’ COMMENT ‘姓名’,
pwd VARCHAR(20) NOT NULL DEFAULT ‘123456’ COMMENT ‘密码’,
sex VARCHAR(2) NOT NULL DEFAULT ‘女’ COMMENT ‘性别’,
birthday DATETIME DEFAULT NULL COMMENT ‘出生日期’,
PRIMARY KEY (id)

)ENGINE=INNODB DEFAULT CHARSET=utf8

– DML
INSERT INTO student(name) VALUES(‘张三’)
INSERT INTO student(name,pwd,sex) VALUES(‘李四’,‘aaa’,‘男’)

UPDATE student SET name=‘nice’ WHERE id=1
UPDATE student SET name=‘nice’ ,sex=‘男’ WHERE id=1

DELETE FROM test

TRUNCATE test

CREATE TABLE test(
id INT(4) NOT NULL AUTO_INCREMENT,
coll VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
)ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO test (coll) VALUES (‘1’),(‘2’),(‘3’)

– DQL
SELECT * FROM student

SELECT StudentNo,StudentName FROM student

SELECT StudentNO AS 学号, StudentName AS 姓名 FROM studnet AS a

SELECT CONCAT(‘姓名’,StudentsName)AS 新名字 FROM student

SELECT DISTINCT Student FROM student – 去除重复数据

SELECT VERSION()

SELECT 100*3-1 AS 计数结果

SELECT @@auto_increment_increment

SELECT id+1 FROM student

SELECT studentNo,studentresult FROM result

SELECT studentNo,studentresult FROM result
WHERE studentresult>=95 AND studentresule<=100

SELECT studentNo,studentresult FROM result
WHERE studentresult BETWEEN 95 AND 100

SELECT studentNo,studentresult FROM result
WHERE studentno!=1000

SELECT studentNo,studentresult FROM result
WHERE NOT studentno=1000

– 刘后面所有
SELECT studentNo,studentname FROM student
WHERE Studentname LIKE ‘刘%’

– 刘后面一个字
SELECT studentNo,studentname FROM student
WHERE Studentname LIKE ‘刘_’

– 刘后面二个字
SELECT studentNo,studentname FROM student
WHERE Studentname LIKE ‘刘__’

– 名字中有刘的
SELECT studentNo,studentname FROM student
WHERE Studentname LIKE ‘%刘%’

– 查询1001,1002,1003号学员
SELECT studentNo,studentname FROM student
WHERE StudentNo IN(1001,1002,1003)

SELECT studentNo,studentname FROM student
WHERE address=’’ OR address IS NULL

SELECT s.studentNo,studentName,studnetResult,SubjectName
FROM student AS s
RIGHT JOIN result AS r
ON r.studentNo=s,s.studnetNo
INNER JOIN subjiect AS sub
ON r.subjectNo=sub.subjectNo
WHERE subjectName=‘数据库结构’
ORDER BY studentResult ASC – 升序
LIMIT 0,10 – 0起始页 10页面大小

– 查询 ＪＡＶＡ第一学年　课程成绩排名前十的学生，并且分数要大于８０的学生信息（学号，姓名，课程名称，分数）
SELECT s.studentNo,studentName,SubjectName,studentResult
FROM student AS s
INNER JOIN result AS r
ON s.studentNo=r.studnetNo
INNER JOIN suject AS sub
ON r.Subject=sub.subject
WHERE SubjectName=‘JAVA第一学年’ AND studentresult>80
ORDER BY studentResult DESC
LIMIT 0,10

– 排序
ORDER BY