SQL入门基础语句

 

 

 

 基本操作增删改查

 

 

 

 

 

SQL学习

 

 

/

 

区别

select * from 就是显示所有的字段
select 字段 from 就是只显示指定的字段

 

 

查 SELECT 

        查询所有内容      SELECT * FROM user  //user 为已存在表单

        查询大于某值      SELECT * FROM user WHERE score > 80;            // score 为user的一个字段

        查询等于某值      SELECT * FROM user WHERE sex = '男';

        查询开头字符      SELECT students FROM user WHERE students LIKE '张%'; //like 模糊查询

        查询非开头字符   SELECT * FROM user WHERE students NOT LIKE '张%';

        查询模糊搜索       SELECT * FROM user WHERE students LIKE '%聪%';

        查询字段等于多值   SELECT * FROM user WHERE score IN (98,60,92);

        查询或者(OR)         SELECT * FROM user WHERE score>95 OR sex='女';

        查询合并表            SELECT * FROM user, user_ext WHERE user.id = user2.id;

        查询记录数              SELECT COUNT(*) FROM user WHERE score > 60;

         查询平均值             SELECT AVG(score) FROM user;

         查询总数值            SELECT SUM(score) AS sumvalue FROM user;         //as 改变字段名字

        查询最大值              SELECT MAX(score) AS MAXVALUE FROM user;

        查询最小值              SELECT MIN(score) AS MINVALUE FROM user;

        查询设置字段名       SELECT DISTINCT(age) AS 年龄 FROM user_ext;   //distinct 会合并字段值相同的项 

        查询排序                 SELECT * FROM user_ext ORDER BY weight DESC;//desc降序，asc升序

        查询左连接            

SELECT t1.id, t1.students, t2.age, t2.weight FROM user AS t1 LEFT JOIN user_ext AS t2 ON t2.id = t1.id WHERE t2.age > 9;   //通过左连接 获取表 user(别名t1) 和表 user_ext(别名t2) 中字段 id 相同的数据，其中字段 age 大于9，并仅返回 id、students、age、weight 这几个字段的数据

 

增  INSERT INTO

INSERT INTO user (students, score,gender) VALUES ('小小', 100,'男');

 

改 UPDATE

UPDATE user SET score=30 WHERE students='小明';

 

 

 

删 DELETE

 

DELETE FROM user WHERE students='小明';

 

 

创建表单  CREAT

CREATE TABLE test (id ,students VarChar(8),score Integer,gender VarChar(2)); //字段，ID student score gender

 

删除表单  DROP

 

 

DROP TABLE test；

 

 

 

 

 

http://www.xuesql.com/Course/