1. 库、表创建

CREATE DATABASE MYDATABASE;   /*创建一个数据库*/
USE MYDATABASE;  /*使用一个数据库*/
DROP DATABASE webexample;/*删除某个数据库*/CREATE TABLE Student/*学生表*/(  Sno CHAR(5)  NOT NULL, /*学号为主键*/Sname  CHAR(20) NOT NULL ,/*姓名*/      Ssex   CHAR(10) NOT NULL,/*性别*/Sage   INT  NOT NULL, /*年龄*/Sdept  CHAR(15)  NOT NULL, /*专业*/PRIMARY KEY(Sno)/*设置主键*/
);
CREATE TABLE SC(/*学生选课表*/
Sno CHAR (5) NOT NULL,/*学生号*/
Cno CHAR (5) NOT NULL,/*课程号*/
Grade INT NOT NULL,/*课程成绩*/
PRIMARY KEY(Sno,Cno)/*主键是学生号和课程号*/
);
CREATE TABLE Course(
Cno CHAR (5) NOT NULL,/*课程号*/
Cname CHAR(20) NOT NULL,/*课程名称*/
PRIMARY KEY(Cno)/*主键是课程号*/
);

SHOW TABLES;-- 显示已经创建的表

DESC Student;-- 显示学生表的结构

2.增加数据

INSERT INTO student VALUES(1001,'Mike','M',18,'Math');-- 向学生表中插入数据
INSERT INTO student VALUES(1002,'Tom','M',20,'English');
INSERT INTO student VALUES(1003,'Lili','F',19,'Science');
INSERT INTO student VALUES(1004,'Sara','F',22,'Computer');
INSERT INTO student VALUES(1005,'Bob','M',20,'History');
INSERT INTO student VALUES(1006,'Rose','F',17,'Math');

3.无条件查询

SELECT * FROM student;-- 选择学生表中的所有数据

4.加上where的查询

SELECT *  FROM student where student.Sage>=20;-- 选择查询，查询年龄大于等于20的学生

5.删除表中的数据

DELETE from student where student.Sage=17; -- s删除年龄为17的学生，如果不加where条件，将会删除学生表里面的所有数据
SELECT *  FROM student;

从运行结果里面可以看到年龄为17的学生信息已经被删除

6.更新表中的数据

UPDATE student SET Sage=19 WHERE Sname='Mike';-- 将Mike的年龄改为19
SELECT * FROM student where Sname='Mike';

7.Like匹配

SELECT * FROM student where Sdept like '%h';

8.对数据进行排序

按年龄升序，不加asc dec默认就是升序

SELECT * FROM student ORDER BY student.Sage;

按年龄降序

SELECT * FROM student ORDER BY student.Sage DESC;

group by

• 分组并查询每组的数量

SELECT count(*) FROM student GROUP BY student.Sage;

• 分组查询分组列以及sum
  注意：查询的列只能是用来分组的列

SELECT Sage ,SUM(Sage),AVG(Sage) FROM student GROUP BY Sage;

• group与where一起用

SELECT Sage ,SUM(Sage),AVG(Sage) FROM student WHERE Sage>=20 GROUP BY Sage;