SQL语句

INSERT INTO t1 VALUES('x1',111)  -- 往里面添加name=x1  ，密码111

INSERT INTO t1(userName) VALUES('x2')-- 代表只给 userName里输入字段


SELECT * FROM t1 WHERE userPass IS NULL   -- 找到密码为空的

SELECT * FROM t1 WHERE userPass IS NOT NULL -- 找到密码不为空的

INSERT INTO t1 VALUES('y1','yyy')

SELECT * FROM t1 WHERE userName LIKE 'x_' OR userName LIKE 'y%'-- lick是进行模糊查询  查询userName为x的userName  _下划线相当于？ 表示任意一个字符

SELECT * FROM t1 WHERE userName='x1' 或者 userName='y1'-- 查找userName是x1 和y1的

SELECT * FROM t1 WHERE userName IN('x1','y1')-- 查找userName是x1 和y1的

SELECT * FROM t1 WHERE userName!='x1' AND userName!='y1'-- userName不是x1 和y1的

SELECT * FROM t1 WHERE userName NOT IN('x1','y1')-- userName不是x1 和y1的

SELECT * FROM t1 ORDER BY userName ASC -- 按照升序排序

SELECT * FROM t1 ORDER BY userName DESC -- 降序

UPDATE t1 SET userPass='yyy' WHERE userName='x2'  -- 修改 userName x2 密码为yyy

UPDATE t1 SET userPass='yyy' WHERE userPass IS NULL  -- 修改 密码为空的 密码为yyy

SELECT * FROM t1 ORDER BY userPass DESC,userName -- 以密码为主的降序排列

SELECT * FROM t1 ORDER BY userName DESC,userPass -- 以密码为主的降序排列


-- 聚合函数 SUM(求和)  AVG(平均值)  COUNT(计数)  MIN(最小值)  MAX(最大值)



-- 聚合函数 SUM(求和)  AVG(平均值)  COUNT(计数)  MIN(最小值)  MAX(最大值)
-- ------------------------------------COUNT()-----------------------------------------------
SELECT COUNT(*) FROM t1  -- 是以整行来计数的，行里有内容就算数

SELECT COUNT(1) FROM t1  -- 显示全部

SELECT COUNT(userName) FROM t1

SELECT *,1 FROM t1  -- 字段  username  suerPass  age 后面增加一个字段

SELECT COUNT(1) FROM t1 WHERE age IS NULL OR age=0 OR age=''

SELECT city,COUNT(1)AS COUNT FROM AUTHORS GROUP BY city  //统计每个城市作者的总人数

--  -----------------------------------分类汇总--------------------------------------

SELECT userPass,COUNT(1) FROM t1  GROUP BY userPass  -- select 的非计算字段必须加载  group by 的后面

SELECT userPass,COUNT(1) FROM t1  GROUP BY userPass  HAVING COUNT(1)>1
-- having  在分类的基础上找到大于1的

SELECT userPass,age,COUNT(1)AS cnt FROM t1  GROUP BY userPass,age  HAVING COUNT(1)>1
-- 首先先以userPass排序 然后再以年龄排序   as cnt就是改字段的名字
-- as 是修改名字
SELECT userName,COUNT(1)AS cnt ,SUM(age),MAX(age),MIN(age),AVG(age)AS 平均值 FROM t1 GROUP BY userName

SELECT * FROM t1 WHERE age>=5 AND age<=7

===========================动态SQL=========================

DELIMITER $$

DROP PROCEDURE IF EXISTS my_proc $$

CREATE PROCEDURE my_proc()
BEGIN
  DECLARE mysql VARCHAR(500);
  SET mysql='select * from authors';
  SET  @ms:=mysql;
 PREPARE s1 FROM @ms; -- 预编译动态SQl
 EXECUTE s1;   -- 执行动态Sql
 DEALLOCATE PREPARE s1; --释放
END$$


call my_proc()

=============================游标================================

DELIMITER $$

DROP PROCEDURE IF EXISTS p3 $$

CREATE   PROCEDURE `pubs`.`p3`(aid VARCHAR(15))
    BEGIN
    DECLARE tid VARCHAR(10);
    DECLARE  dan INT DEFAULT 0;
    DECLARE  he INT DEFAULT 0;        
    DECLARE cnt INT;
    DECLARE n INT DEFAULT 1;    
    DECLARE yb CURSOR FOR SELECT title_id FROM titleauthor WHERE au_id=aid;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET n=0;
     OPEN yb;
    FETCH FROM yb INTO tid;-- 游标取下一条记录
    WHILE n=1 DO
       SELECT  COUNT(1) INTO cnt FROM titleauthor WHERE title_id=tid;
       IF cnt=1 THEN
          SET dan=dan+1;
       ELSEIF cnt>1 THEN
          SET he=he+1;
        END IF;
       FETCH FROM yb INTO tid;  
     END WHILE;
     -- SELECT CONCAT(aid,'**',dan+he,'**',dan,'**',he);ss
     INSERT INTO t1 VALUES(aid,dan+he,he,dan);
     SELECT * FROM t1;
     DELETE FROM t1;
    END$$
    
DELIMITER ;


今天刚写的  有图===========================================================

以下图为例 ，只显示分数大于等于80的学生姓名；

表的名字叫bb（随便起的别介意）

SELECT userName FROM bb WHERE fenshu>=80; 

以上图为例子  将上图查询后显示的结果为

SELECT userName, (SELECT kecheng FROM bb m WHERE m.kecheng='1'AND m.userName=bb.userName) AS m1 FROM bb   GROUP BY userName