sql

use sysstu


create TABLE student(


stuid int primary key auto_increment,
stucode varchar(12) unique,
NAME varchar(30) NOT NULL,
sex char(2) default '男',
address varchar(30)


)


/**更改表结构***/
ALTER table student ADD phone VARCHAR(11)
ALTER TABLE student DROP phone


/**插入语句***/
INSERT INTO student (stucode,NAME,sex,address) VALUES('102','李','男','济南大学')
INSERT INTO student VALUES(2,'105','王','男','济南大学')
INSERT INTO student VALUES(103,'106','丁','女','济南大学')
INSERT INTO student VALUES(201,'108','宋','男','济南大学')


/**更新语句***/
UPDATE student SET stuid=101 WHERE NAME ='李'
UPDATE student SET stuid=102 WHERE NAME ='王'


/**删除语句***/
DELETE FROM student WHERE stuid=102


/****查询语句*****/
/***/
SELECT *FROM student WHERE stuid >102
SELECT *FROM student WHERE stuid BETWEEN  100 AND 200




SELECT * FROM student WHERE sex='男' or NAME='李'
SELECT * FROM student WHERE sex IN ('男')
SELECT * FROM student WHERE sex NOT IN ('男')


SELECT * FROM student WHERE stucode LIKE '%2' 


/**常用函数***/


/**
count (*/columname):统计记录的行数
sum(数值类型的列名)：对指定字段进行汇总求和
**/


SELECT address,COUNT(*)totalStuid FROM student WHERE address='济南大学'
SELECT address,SUM(stuid)totalStuid FROM student WHERE address='济南大学'




SELECT * FROM student WHERE address='济南大学' ORDER BY stuid


/**
GROUP BY
没有where,用having 
**/
SELECT sex,COUNT(*)totalStuid FROM student GROUP BY sex
SELECT sex,COUNT(*)totalStuid FROM student GROUP BY sex HAVING totalStuid >=2


/*
limit statrindex,rows:限制查询结果集，用于做分页
DESC:降序
不加是升序
**/
SELECT * FROM student ORDER BY stuid DESC LIMIT 0,3
SELECT * FROM student ORDER BY stuid  LIMIT 0,3