Mysql学习笔记二

一、数据库表基本操作

1、创建表

create table (属性名 数据类型 [完整性约束],属性名 数据类型 [完整性约束],;

例子：

create table t_booktype(

id int primary key auto_increment,

bookTypeName varchar(20),

bookTypeDesc varchar(200)

);

create table t_book(

id int primary key auto_increment,

bookName varchar(20),

author decimal(6,2),

bookTypeId int,

constraint `fk` foreign key (`bookTypeId`) references `t_booktype`(`id`)   //注意fk那里的符号是esc键下面的那个键按出来的

);

2、查看表结构

desc t_bookType;

show create t_bookType; 

3、修改表

修改表名 alter table 旧表名 rename 新表名;

修改字段 alter table 表名 change 旧属性名 新属性名 新数据类型

增加字段 alter table 表名 add 属性名1 数据类型 [完整性约束条件][first | alter 属性名2]

删除字段 alter table 表名 drop 属性名

4、删除表

drop table 表名;

二、单表查询

第一种：select age,gradeName,id,sex,stuName from t_student;可以改变查找结果的显示顺序。

第二种：select * from t_student;

where指定条件查找：

select * from t_student where id=1;

范围查找：

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

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

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、or的多条件查询：

SELECT * FROM t_student WHERE gradeName='一年级' AND age=23

SELECT * FROM t_student WHERE gradeName='一年级' OR age=23

去重复查询（不显示重复的）：

SELECT DISTINCT gradeName FROM t_student;

排序查询：

SELECT * FROM t_student ORDER BY age ASC;递增

SELECT * FROM t_student ORDER BY age DESC;递减

分组查询（group by）：

常用的：

与group_concat()函数数一起使用

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;

limit分页查询：

语法：select 字段1，字段2 from 表名 limit 初始位置，记录数；

SELECT * FROM t_student LIMIT 0,5;

SELECT * FROM t_student LIMIT 5,5;

SELECT * FROM t_student LIMIT 10,5;

三、聚合函数查询

 1、COUNT();

2、SUM();

3、Max();

4、MIN();

SELECT COUNT(*) FROM t_grade;

SELECT COUNT(*) AS total FROM t_grade;

SELECT stuName,COUNT(*) FROM t_grade GROUP BY stuName;

SELECT stuName,SUM(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,SUM(score) FROM t_grade GROUP BY stuName;

SELECT stuName,AVG(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,AVG(score) FROM t_grade GROUP BY stuName;

SELECT stuName,course,MAX(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,MAX(score) FROM t_grade GROUP BY stuName;

SELECT stuName,course,MIN(score) FROM t_grade WHERE stuName="张三";

SELECT stuName,MIN(score) FROM t_grade GROUP BY stuName;

用这张图来进行辅助理解group by跟聚合函数

group by对选择的结果进行初步过滤，也就是第一列的stuName

avg，sum等函数对过滤之后的结果再进行过滤，也就是对第二列进行处理

四、连接查询

常用的是内连接

SELECT * FROM t_book,t_bookType;

SELECT * FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

SELECT bookName,author,bookTypeName FROM t_book,t_bookType WHERE t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id;(别名)

SELECT * FROM t_book LEFT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb LEFT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

SELECT * FROM t_book RIGHT JOIN t_bookType ON t_book.bookTypeId=t_bookType.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb RIGHT JOIN t_bookType tby ON tb.bookTypeId=tby.id;

SELECT tb.bookName,tb.author,tby.bookTypeName FROM t_book tb,t_bookType tby WHERE tb.bookTypeId=tby.id AND tb.price>70;

五、子查询

in not in all any exists(逻辑判断)

in是范围查询

SELECT * FROM t_book WHERE booktypeId IN (SELECT id FROM t_booktype);

SELECT * FROM t_book WHERE booktypeId NOT IN (SELECT id FROM t_booktype);

SELECT * FROM t_book WHERE price>=(SELECT price FROM t_pricelevel WHERE priceLevel=1);

SELECT * FROM t_book WHERE EXISTS (SELECT * FROM t_booktype);

SELECT * FROM t_book WHERE NOT EXISTS (SELECT * FROM t_booktype);

SELECT * FROM t_book WHERE price>= ANY (SELECT price FROM t_pricelevel);

SELECT * FROM t_book WHERE price>= ALL (SELECT price FROM t_pricelevel);

六、合并查询

UNION合并结果，去除相同的结果

UNION ALL不会去除相同的结果

select id from t_book union select id from t_booktype;

select id from t_book union all select id from t_booktype;

七、为表和字段取别名

表：表 别名

字段：字段 as 别名（不要as也可以）