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/