mysql常用语句

1.创建表

表是数据库存储数据的基本单位。个一个表包含若干字段或记录；
语法：
CREATE TABLE 表名( 属性名数据类型[完整性约束条件],
            属性名数据类型[完整性约束条件],
.
.
属性名数据表格[完整性约束条件]
);

一、查询

SELECT id,stuName,age,sex,gradeName FROM t_student ;

SELECT stuName,id,age,sex,gradeName FROM t_student ;

SELECT * FROM t_student;

SELECT stuName,gradeName FROM t_student;

二、where条件查询

SELECT * FROM t_student WHERE id=1;

SELECT * FROM t_student WHERE age>22;

三、带in关键字查询

   SELECT * FROM t_student WHERE age IN (21,23);
SELECT * FROM t_student WHERE age NOT IN (21,23);

四、带between and 关键字查询

SELECT * FROM t_student WHERE age BETWEEN 21 AND 24;
SELECT * FROM t_student WHERE age NOT BETWEEN 21 AND 24;

五、带like的模糊查询

SELECT * FROM t_student WHERE stuName LIKE '张三';
SELECT * FROM t_student WHERE stuName LIKE '张三%';
SELECT * FROM t_student WHERE stuName LIKE '张三__';
SELECT * FROM t_student WHERE stuName LIKE '%张三%';只要含有张三的
六、空值查询

SELECT * FROM t_student WHERE sex IS NULL;
SELECT * FROM t_student WHERE sex IS NOT NULL;
七、带AND的多条件查询

  SELECT * FROM t_student WHERE gradeName='一年级' AND age=23
SELECT * FROM t_student WHERE gradeName='一年级' OR age=23
八、带OR的多条件查询

九、DISTINCT去重复查询

SELECT DISTINCT gradeName FROM t_student;
11.对查询结果排序

SELECT * FROM t_student ORDER BY age ASC;
SELECT * FROM t_student ORDER BY age DESC；降序，默认升序。
12.guoup BY分组查询

SELECT * FROM t_student GROUP BY gradeName;

SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName;


SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName;


SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName HAVING COUNT(stuName)>3;


SELECT gradeName,COUNT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
SELECT gradeName,GROUP_CONCAT(stuName) FROM t_student GROUP BY gradeName WITH ROLLUP;
13.LIMIT分页查询

SELECT * FROM t_student LIMIT 0,5;
SELECT * FROM t_student LIMIT 5,5;
SELECT * FROM t_student LIMIT 10,5;