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

/**
外连接
A LEFT JOIN B…ON…
A RIGHT JOIN B…ON…
A FULL JOIN B…ON…
**/

SELECT a.,b. FROM student AS a LEFT JOIN teacher AS b ON a.stuid=b.teaid

SELECT * FROM student LEFT JOIN teacher ON student.stuid=teacher.teaid