CREATE TABLE student(
sid INT,
sname VARCHAR(10),
ssex CHAR(2)
)
/*插入数据*/
INSERT INTO student VALUES(1,'袁一鸣','男');
/*给定字段插入数据*/
INSERT INTO student(sid,sname) VALUES(2,'小明');
/*查询表中所有数据*/
SELECT * FROM student;
/*修改数据（一定带条件）*/
UPDATE student SET sname='小蓝' WHERE sid=2;
/*删除数据*/
DELETE FROM student;
TRUNCATE student;
/*删除某条数据*/
DELETE FROM student WHERE sid=2;
/*查询表中所有字段的值*/
SELECT * FROM student;
/*查询表中的指定字段*/
SELECT sid,ssex FROM student;
INSERT INTO student VALUES(2,'熊大','男');
INSERT INTO student VALUES(3,'熊二','女');
INSERT INTO student VALUES(4,'琪琪国王','男');
INSERT INTO student VALUES(5,'蜘蛛侠','男');
/*查询时添加常量列*/
SELECT sid,sname,ssex,'java' AS 'class' FROM student;
/*查询时给字段起别名*/
SELECT sid AS '编号',sname AS '姓名',ssex FROM student;
/*新建成绩表*/
CREATE TABLE score(
sid INT,
sname VARCHAR(10),
jsp DOUBLE,
html DOUBLE,
js DOUBLE);
INSERT INTO score VALUES(1,'钢铁侠',12,66,100);
INSERT INTO score VALUES(2,'浩克',66,88,11);
INSERT INTO score VALUES(3,'美国队长',0,70,80);
INSERT INTO score VALUES(4,'小辣椒',100,50,60);
INSERT INTO score VALUES(5,'灭霸',66,70,93);
/*查询时合并列*/
SELECT * FROM score;
SELECT sname,(jsp+html+js) AS '总成绩' FROM score;
/*查询学生的性别*/ /*DISTINCT去除重复*/
SELECT DISTINCT ssex FROM student;
/*条件查询*/
SELECT * FROM student WHERE sid=1 AND ssex='男';
SELECT * FROM student WHERE sid=1 OR ssex='男';
//
SELECT * FROM score WHERE jsp>60 AND html>60 AND js<60
SELECT sname FROM score WHERE jsp<>100
SELECT * FROM score WHERE html>=60 AND html<=70
SELECT * FROM score WHERE html BETWEEN 60 AND 70
INSERT INTO student(sid,sname) VALUES(6,'灭霸')
INSERT INTO student(sid,sname) VALUES(7,'')
SELECT * FROM student
/*判断空值*/ /*IS NOT*/
SELECT * FROM student WHERE ssex IS NULL
SELECT * FROM student WHERE sname<>''
/*模糊查询*/
SELECT * FROM student WHERE sname='熊'
SELECT * FROM student WHERE sname LIKE '%熊%'
SELECT * FROM student WHERE sname LIKE '蜘__'