mysql数据库详解（一）：sql语句

作为一个稀有的Java妹子，所写的所有博客都只是当作自己的笔记，留下证据自己之前是有用心学习的～哈哈哈哈（如果有不对的地方，也请大家指出，不要悄悄咪咪的不告诉我）

一.语句分类

1.DDL（Data Definition Languages）

数据库的定义语句，用来创建数据库中的表、索引、视图、存储过程、触发器等，常用的语句关键字有：CREATE,ALTER,DROP,TRUNCATE,COMMENT,RENAME。增删改表的结构。

2.DML（Data Manipulation Language）

数据库的操作语句，用来查询、添加、更新、删除等，常用的语句关键字有：SELECT,INSERT,UPDATE,DELETE,MERGE,CALL,EXPLAIN PLAN,LOCK TABLE,包括通用性的增删改查。增删改表的数据。

3.TCL（Transaction Control Language）

数据库的事务控制语句，用于控制事务，常用的关键字有:COMMIT，ROLLBACK，SAVEPOINT，TRANSCATION等。

二、各个关键字详解

DDL语句

1.创建表：create table 表名

CREATE TABLE tb_test_user (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(30) DEFAULT NULL,
sex varchar(4) DEFAULT ‘F’ COMMENT ‘F:女 M:男’,
age int(5) DEFAULT NULL,
gmt_create timestamp NULL DEFAULT NULL,
gmt_modified timestamp NULL DEFAULT NULL,
PRIMARY KEY (id)
)

字段的约束有：
1.NOT NULL 非空
2.NULL 可以为NULL
3.DEFAULT 默认值，如果不设置的话默认为NULL
4. AUTO_INCREMENT自动递增
5. COMMENT 注释
6. PRIMARY KEY 主键
7. unique key 唯一键
8. unsigned 无符号类型，即>1

2.修改表

1.添加字段
alter table 表名 add 字段名 类型 约束 after 列名 comment 注释
默认是添加到表的最后一列，如果是想在某一列后面，可以使用after关键字
alter table tb_test_user add phone varchar(11) NULL after age comment ‘手机号’

2.修改字段
alter table tb_test_user modify name varchar(50)

3.删除字段
alter table tb_test_user drop phone

4.重新命名表名
alter table tb_test_user rename to tb_test_user_rename

5.添加索引
5.1主键索引
alter table tb_test_user_rename add PRIMARY KEY （列名）
5.2 唯一索引
alter table tb_test_user_rename add UNIQUE （列名）
5.3 普通索引，可以创建联合索引
alter table tb_test_user_rename add index 索引名 （列名…）

3.删除表

1.删除表数据及表结构
drop table if exists 表名
2.只是删除表数据，表结构不删除
有两种方式：delete和truncate
两者的区别在于truncate不仅会删除数据，还会把自动递增字段初始化到0
delete from tb_user_test_rename
TRUNCATE table tb_test_user_rename

4.表注释

建表的时候给表加注释
CREATE TABLE student(
id INT PRIMARY KEY AUTO_INCREMENT COMMENT ‘学号’,
name VARCHAR(200) COMMENT ‘姓名’,
age int COMMENT ‘年龄’
) COMMENT=‘学生信息’

如果已经建好表再给表加注释
alter table 表名 comment 注释

DML语句

1.insert添加记录 insert into 表名(字段名…) values(值…)

如果所有字段都会赋值，则字段名可以省略，主键为自动递增时，可以写null，如果字段有默认值可以写default。
添加一条数据：insert into tb_test_user values(null,“里斯”,‘M’,25,NOW(),NOW())
批量添加数据：insert into tb_test_user values(null,“王五”,‘M’,28,NOW(),NOW()),(null,“白云”,default,28,NOW(),NOW())…

2.update修改记录 update 表名 set 字段名=值 where 条件

如果更新多个字段时，用逗号隔开
update tb_test_user set name = “测试修改”,age = 30 where id = 1

3.delete删除记录 delete from 表名 where 条件

delete from tb_test_user where id = 1

4.复杂查询

其实mysql的DML语句比较难的就是查询，修改和删除比较简单，查询往往会配合许多其他的关键字来搜索出想要的数据。

4.1 联表查询

1.内连接、左连接、右连接、全连接
内连接：inner join on where，将涉及到的表根据外建连接起来，然后根据条件过滤出想要的数据。
左连接：left join on where，将涉及到的表以左表为基础表，根据外建连接起来，即就算左表的某条数据跟右表没有连接起来也会显示左表的数据，右表的数据显示null
右连接：同左连接一样，只是基础表是右表
全连接：左右两张表都是基础表，所有的数据全连接起来，但mysql不支持

举例：现在有三张表，学生表，课程表，选课表

现在查询以下数据：
1.有选课的学生姓名和所选课程：

select t.name,c.course from tb_test_student as t 
inner join tb_test_select_course as s on t.student_id = s.student_id 
inner join tb_test_course as c on s.course_id = c.course_id

inner join 是把满足条件的交集查询出来。

2.查询所有的学生，如果有选课的把选课信息也显示出来
select t.name,c.course from tb_test_student as t
left join tb_test_select_course as s on t.student_id = s.student_id
left join tb_test_course as c on s.course_id = c.course_id

3.查询所有的课程，如果有学生选则把学生姓名显示出来

select t.name,c.course from  tb_test_course as c
left join tb_test_select_course as s on  s.course_id = c.course_id
left join tb_test_student as t on t.student_id = s.student_id

左右连接其实是可以互换的，把涉及的表做并集

4.2 分组查询与聚合函数

分组查询一般来讲是为了统计信息，与聚合函数一起使用，可以更方便快捷的获取到想要的数据。
语法：group by 字段 注意group by后面的字段一定也要在select 后面跟上

聚合函数

1.avg(column)：求平均数
2.max(column):求最大值
3.min(column):求最小值
4.sum(column):求和
5.count(column)：求和
6.group_concat(column):连接字符串，这个是最近发现的一个很好用的函数
注意：
1.其他的聚合函数不会对null值进行计算，count会。
2.count如果没有查询出结果,返回0，sum返回null。

举例：
1.用刚才上面的一个例子，查询所有的学生，如果有选课的把选课信息也显示出来，如果一个学生选了多个课程，则合并显示，一个学生只有一条记录

select t.name,group_concat(c.course) from  tb_test_student as t 
left join tb_test_select_course as s on t.student_id = s.student_id 
left join tb_test_course as c on s.course_id = c.course_id
group by t.name

新添加一张成绩表，在选课表里多添加几条数据：


2.查询所有学生的最低分，没有选课的也要显示

select u.student_id,u.`name`,c.course,max(g.grade) from tb_test_student as u 
left join tb_test_grade as g on u.student_id= g.student_id
left join tb_test_course as c on c.course_id = g.course_id 
group by u.student_id

3.查询每个学生的平均分

select u.student_id,u.`name`,c.course,avg(g.grade) from tb_test_student as u 
left join tb_test_grade as g on u.student_id= g.student_id
left join tb_test_course as c on c.course_id = g.course_id 
group by u.student_id

4.查询班级一共有多少个学生

select count(id) from tb_test_student

having和where

1、 where和having都可以使用的场景：
select goods_price,goods_name from goods where goods_price > 100
select goods_price,goods_name from goods having goods_price > 100
上面的having可以用的前提是我已经筛选出了goods_price字段，在这种情况下和where的效果是等效的。
但是如果没有select goods_price 就会报错！！因为having是从前筛选的字段再筛选，而where是从数据表中的字段直接进行的筛选的。

2、只可以用where，不可以用having的情况:
select goods_name,goods_number from goods where goods_price > 100
select goods_name,goods_number from goods having goods_price > 100
解释：第二个sql语句报错，这是因为前面并没有筛选goods_price 造成的，更能看出having是针对结果集发挥作用。

3、只可以用having，不可以用where情况：
查询每种category_id商品的价格平均值，获取平均价格大于1000元的商品信息。
select category_id , avg(goods_price) as ag from goods group by category_id having ag > 1000
select category_id , avg(goods_price) as ag from goods where ag>1000 group by category_id
解释：第二个sql语句报错，这是因为from goods 这张数据表里面没有ag这个字段。

所以如果group by要对聚合的字段进行过滤就需要使用having

举例：
1.查询平均分大于80的学生

select u.student_id,u.`name`,c.course,avg(g.grade) as ag from tb_test_student as u 
left join tb_test_grade as g on u.student_id= g.student_id
left join tb_test_course as c on c.course_id = g.course_id 
group by u.student_id having ag>=80

上述语句如果把having换成where就会报错。

4.3 子查询

子查询其实还是查询语句，就是因为是在查询里嵌套查询，所以嵌套的查询被叫做子查询。
举例：
1.查询没有选课的学生姓名

 select `name` from tb_test_student where student_id not in 
 (select student_id from tb_test_select_course)

5.其他常用的关键字

1、DISTINCT column：去掉重复数据
2、ORDER BY DESC/AES column:根据字段排序，默认是升序，倒序使用DESC，多个字段排序使用逗号隔开。
3、BETWEEN … AND …：比较的时候使用
4、LIKE %param%：模糊查询
5、LIMIT x,y ：分页查询

6.mysql的常用内置函数

1、ABS(X):返回结果的绝对值
2、CONCAT(s1,s2,…)：将字符串合并为一个字符串
3、UPPER(s)：将结果里的字符转为大写，LOWER(s)：转为小写
4、TRIM(s)：去掉头尾空格
5、REVERSE(s)：将字符串倒序
6、CURDATE(),CURRENT_DATE()：返回当前日期的年月日
7、CURTIME(),CURRENT_TIME():返回当前日期的时分秒
8、NOW()：返回当前的日期，年月日 时分秒
9、IF(expr,v1,v2)：根据表达式的结果，正确输出v1，错误输出v2
10、IFNULL(v1,v2)：如果v1为null,输出v2，否则输出v1
11、
CASE
　　WHEN e1
　　THEN v1
　　WHEN e2
　　THEN e2
　　…
　　ELSE vn
END

CASE expr
　　WHEN e1 THEN v1
　　WHEN e1 THEN v1
　　…
　　ELSE vn
END

12、DATE_FORMAT(column,’%Y-%m-%d %H:%i:%s’):将日期转换为指定格式字符串

7.mysql的数据类型

1、字符型: char varchar text blob
char和varchar比较：
长度：char最大255，varchar最大65535。
效率：char效率高，varchar相对低。
空间：char定长，varchar长度可变动。

2、数值型
精确数值型： int，tinyint，smallint， mediumint，bigint，decimal（decimal无精度损失，所以为精确数值型，常用于金融系统中）
近似数值型：float，double
float和double只是数值范围的区别，即double表示的范围更大而已。

3、日期型：date(年月日)，time(时分秒)，datetime(年月日 时分秒)，timestamp:自1970年到现在的秒数
4、NULL
5、内置类型：
ENUM，枚举，如定义性别 ENUM(‘F’,‘M’)，只能是其中一个。
SET，集合，只能是集合内一个或几个值的组合。