Sql语句--常用语句

/*新建数据库*/ 
CREATE DATABASE STUDENT_INFO;
/*删除数据库*/
DROP DATABASE STUDENT_INFO;
USE root;
GO

/*创建表*/
CREATE TABLE IF NOT EXISTS STUDENT_INFO 
(
		S_ID INT AUTO_INCREMENT,
		S_NAME VARCHAR (10) NOT NULL,
		S_AGE INT NOT NULL,
		S_CLASS INT,
		S_DATE DATE,
		ASSOCIATED_ID INT,
		PRIMARY KEY (S_ID) 
) ENGINE = INNODB;

/*删除表*/
DROP TABLE STUDENT_INFO;

/*插入数据*/
INSERT INTO STUDENT_INFO (S_NAME,S_AGE,S_CLASS,S_DATE,ASSOCIATED_ID) 
VALUES('张明',20,1,'2018-05-11',01)

/*查询表*/
SELECT * FROM STUDENT_INFO WHERE /*条件*/;
select s_id ID,s_age AGE from student_info  where s_id = 1

/*更新字段*/
UPDATE STUDENT_INFO SET S_NAME = '啸天' WHERE S_ID = '1';

/*删除字段*/
DELETE FROM STUDENT_INFO WHERE S_ID <> 3;

/*使用truncate table也可以清空mysql表中所有内容。
效率上truncate比delete快，但truncate删除后不记录mysql日志，不可以恢复数据。*/

TRUNCATE  TABLE STUDENT_INFO ;

/*like查询*/
SELECT * FROM STUDENT_INFO WHERE S_NAME LIKE '张%';


/*排序 （倒叙）DESC ASC*/
SELECT * FROM STUDENT_INFO  ORDER BY S_ID DESC;

/*分页查询*/
SELECT top 10 *  FROM
	STUDENT_INFO
WHERE
	ID NOT IN ( SELECT top 30 ID FROM STUDENT_INFO ORDER BY ID ASC ) 
ORDER BY
	ID


INSERT INTO STUDENT_INFO (S_NAME,S_AGE,S_CLASS,S_DATE,ASSOCIATED_ID) VALUES 
('小明',20,1,'2016-04-22 15:25:33',55),
('小王',20,3,'2016-04-20 15:25:47',1), 
('小丽',11,2,'2016-04-19 15:26:02',2), 
('小王',15,4,'2016-04-07 15:26:14',2), 
('小明',19,4,'2016-04-11 15:26:40',3), 
('小明',19,2,'2016-04-04 15:26:54',3);
COMMIT;


/*
按照姓名来统计
COUNT-- 返回指定列的值的数目
GROUP BY table_name -- 统计
WITH ROLLUP --可以实现在分组统计数据基础上再进行相同的统计（SUM,AVG,COUNT…）。
*/

SELECT S_NAME, COUNT(S_NAME) AS '统计出现的次数'  FROM STUDENT_INFO GROUP BY S_NAME DESC WITH ROLLUP;

SELECT SUM(S_CLASS) FROM STUDENT_INFO;

/*按照姓名分组统计，再统计但是字段是null*/
SELECT S_NAME, SUM(S_CLASS) as '班级' FROM  STUDENT_INFO GROUP BY S_NAME WITH ROLLUP;

/*按照姓名分组统计，再统计但是字段是可以赋值COALESCE(S_NAME,'赋值的字段')*/
SELECT COALESCE(S_NAME,'次数') AS '按照姓名统计', SUM(S_CLASS) as '班级' FROM  STUDENT_INFO GROUP BY S_NAME WITH ROLLUP;

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------*/



/*创建老师表*/
CREATE TABLE IF NOT EXISTS TEACHER_INFO 
(
		T_ID INT AUTO_INCREMENT COMMENT 'ID',
		C_ID INT NOT NULL COMMENT '班级',
		T_NAME VARCHAR (10) NOT NULL COMMENT '姓名',
		T_AGE INT NOT NULL COMMENT '年龄',
		T_URRICULUM VARCHAR(100) COMMENT '课程',
		T_REMARKS VARCHAR(300) COMMENT '备注',
		PRIMARY KEY (T_ID)
)ENGINE = INNODB COMMENT '老师表';


DROP TABLE TEACHER_INFO; --删除表

INSERT INTO TEACHER_INFO (C_ID,T_NAME,T_AGE,T_URRICULUM,T_REMARKS)
VALUES (3,'王俊辉',40,'化学','班主任'),(3,'夏天',28,'语文','xx'),
(6,'李鹏',30,'体育','xx'),(6,'王杰',25,'数学','xx')


UPDATE teacher_info 
SET t_name = '王晓杰' 
WHERE t_id = 4;

INSERT INTO teacher_info ( C_ID, T_NAME, T_AGE, T_URRICULUM, T_REMARKS ) 
VALUE( 3, '王俊辉', 40, '化学', '班主任' );



/*多表查询 WHERE的使用*/
SELECT T.T_NAME,T.T_URRICULUM,S.S_CLASS,S.S_NAME FROM teacher_info T, student_info S WHERE T.C_ID = S_CLASS;

/*多表查询 INNER JOIN 的使用等同于WHERE */
SELECT *  FROM teacher_info T INNER JOIN student_info S ON T.C_ID = S.S_CLASS;


/*多表查询 LEFT JOIN --左关联*/
SELECT * FROM  student_info S  LEFT JOIN teacher_info T ON T.C_ID = S.S_CLASS;


/*多表查询 RIGHT JOIN --左关联*/
SELECT * FROM  student_info S  RIGHT JOIN teacher_info T ON T.C_ID = S.S_CLASS IS NOT NULL;


/*返回指定的行数，注意limit是MySQL的独有用法*/
SELECT * FROM student_info LIMIT 3;

SELECT * FROM student_info WHERE s_name LIKE '%明';

SELECT * FROM teacher_info  WHERE S_NAME LIKE '_明';



INSERT INTO teacher_info(C_ID,T_NAME,T_AGE,T_URRICULUM,T_REMARKS) values (6,'张杰',30,'Google','外教'),(6,'李杰',28,'Facebook','外教');


/*MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式*/
SELECT * FROM teacher_info WHERE T_URRICULUM REGEXP '^[A-H]'; /*以A-H开头*/

SELECT * FROM teacher_info WHERE T_URRICULUM REGEXP '^[^A-H]';/*不以A-H开头*/


SELECT * FROM teacher_info WHERE C_ID BETWEEN 1 AND 4;

SELECT * FROM teacher_info WHERE (C_ID NOT BETWEEN 1 AND 4) AND T_URRICULUM IN ('数学','Google');

SELECT * FROM teacher_info T,student_info S WHERE T.C_ID = S.S_ID

SELECT * FROM teacher_info T WHERE c_ID IN (SELECT S_ID FROM student_info)

--查询重复列数据
SELECT a.* FROM table a,(
SELECT code,name
FROM table
GROUP BY code,name
HAVING COUNT(1)>1
) AS b
WHERE a.code=b.code AND a.name=b.name

--多表更新
UPDATE a SET a.column1 = b.column1,a.column2 = b.column2 table1 a,table2 b
WHERE a.id = b.id;

--聚合函数
SELECT column,count(*) FROM table GROUP BY column;
--注意：使用了group by 后面的列。 在select后面部分只能存在分组(group by)后列名，其他的列名必须需要带聚合函数使用。
--eg:max(column2)、min(column3)
SELECT column,count(*) FROM table WHERE column1 > 0 GRUOP BY column;
SELECT column,count(*) FROM table GRUOP BY column WHERE column  > 0;--错误WHERE出现在子语句中
--注意：聚合函数不能出现在where子语句中(可以在聚合函数之前)，除非该聚合语句位于having 子语句中或者选择列表所包含子车查询中。

--结合以上问题，处理方法是having
SELECT column ,count(*) FROM table 
GROUP BY column HAVING --(条件)column > 10;
--注意：having后面的条件部分只能是select 后面中存在列名，如果出现其他列名将出现错误。
--可以这样理解为：having 是通过group by 分组后(本组)的数据集，再来过滤。

--过滤列重复值
SELECT DISTINCT(column) FROM table;

--union合并结果集
SELECT (column) FROM table1
UNION
SELECT (column) FROM table2
--注意：UNION中相同列数值，会自动合并可能会导致数据丢失。
--UNION ALL 添加了ALL就不合并相同数据值，不会导致数据丢失。

--数据类型转换：cast(value as date_Type)\convert(data_type,value)
SELECT cast('123' as int),convert(varchar(50),123) 
FROM table

--case 语句：
  --方式一:
    SELECT 
    (
        CASE(column)
        WHEN type_number1 THEN value1
        WHEN type_number2 THEN value2
        ELSE def_value
        END
    )
    FROM table;
  --方式二:
    SELECT 
    (
        CASE
        WHEN 条件?真:假 THEN value1
        WHEN 条件1?真:假 AND 条件2 THEN value2
        WHEN 条件3?真:假 OR  条件4 THEN value3
        ELSE def_value
        END
    )
    FROM table;