Mysql基础学习笔记

本博客只作为个人学习笔记使用。

数据库操作

以创建名为”my”的数据库为例子：
创建数据库：create database my;
查看已有的数据库：show databases;
查看数据库：show create database my;
修改数据库编码：alter database my character set utf8;
创建数据库的同时指定编码：create database my character set utf8;
删除数据库：drop database [if exists] my;
使用数据库：use my;
查看当前使用的数据库：select database();

表操作

创建表(以创建表名为course的表为例)：

查看数据库中有哪些表：show tables 或show tables from database_name
查看表的结构： desc table_name或 show columns from table_name
插入数据：insert into course values(‘lcl’,23,’boy’,1001);
插入数据指定字段： insert into course(name,age) values(‘hh’,22);
字段不允许为空：name varchar(20) not null
设置主键为自增的：alter table course modify id int(11) auto_increment; 还可以设置自增起始，设置表course主键从10000开始：alter table course auto_increment=10000;
给name字段添加唯一约束：alter table course modify name varchar(20) unique key;
默认约束：alter table course modify description varchar(50) default ‘无’;

外键

创建表stu，建立与表course关联的外键course_id

在表B中建立与父表A关联的外键后：当在建立外键时使用如下语句：
constraint foreign key(c_id) references course(id) on delete cascade ,那么删除父表的同时会删除子表中外键列与父表中参照列相等的记录。

cascade：对父表操作会更新字表中匹配的行
set null：对父表删除或更新，字表对应字段设置为null(前提是字表对应字段允许为null)
no action、restrict：拒绝对父表删除或更新

实际开发中很少使用物理的外键约束，很多都去使用逻辑的外键约束，因为物理的外键约束只有INNODB这种引擎才会支持，像另外的存储引擎如MYISAM则不支持。

表字段操作

表增加列：alter table stu add column name varchar(20) after c_id;
表增加多列：alter table stu add column(grade varchar(5),age int);
删除列：alter table stu drop column age;
删除多列：alter table stu drop name,drop grade;
删除外键约束：alter table stu drop foreign key ‘外键名’;
删除主键约束：alter table stu drop primary key;
修改列定义：alter table table_name modify column_name column_definition;
表更名：rename table old_name to new_name;
删除表：drop table table_name;

SQL查询

查询时使用字段别名：select id as stu_id,c_id as course_id from stu;
查询时分组：select * from table_name group by column_name(默认排序是递增的)
SELECT * from course group by age desc;
分组条件having : select name,age from course group by name having age > 22
排序order by (默认是升序的):查看按age升序，name降序排列:select name,age from course order by age ,name desc;
查询插入： insert into course(name) select name from stu where id > 1

子查询

建立商品表 tdb_goods,表结构如下：

查询所有价格高于平均价格的商品：

select goods_name,goods_price from tdb_goods where goods_price > ALL(select AVG(ROUND(goods_price,2)) from tdb_goods);

ANY、SOME、ALL（ANY、SOME效果差不多）

ANY：就是比最小的大
ALL ：比最大的大
< ANY：比最大的小
< ALL：比最小的小

现表tdb_goods中有字段goods_cate,想把tdb_goods中的不同的goods_cate插入到另一个专门存商品类别的表tdb_goods_cates中去，可以用查询插入：

tdb_goods_cates的表结构如下：

insert into tdb_goods_cates(cate_name) select goods_cate from tdb_goods group by goods_cate;

内连接(A且B)：inner join| join | cross join 仅仅显示符合连接条件的记录

外连接：左连接|右连接 left join和right join

多表删除
表A：

表B：

在B中删除与A中重复的记录（即A.id = B.id 且 A.name = B.name）

delete t2 from B as t2 left join A as t1 on t1.id = t2.id and t1.name = t2.name 

如何理解：

select * from B as t2 left join A as t1 on t1.id = t2.id where t1.name=t2.name;

Left join之后是这样的表：

然后在B (t2) 表中删除符合这张表的记录：delete t2

on 与where区别？
on是用来作连接条件的关键词，on将符合条件的记录连接，而不符合条件的记录对应的字段会用NULL表示，where是条件过滤，如上：
select * from B as t2 left join A as t1 on t1.id = t2.id where t1.name=t2.name;
把where改为and，即把t1.name = t2.name作为连接条件,name结果为：

可以看出使用where就相当于条件过滤，使用where的时候第三条记录就不会出现。

运算符和函数

字符函数

concat：concat(‘A’,’B’)：联系函数
concat_ws(): select concat_ws(‘|’,’a’,’b’,’c’),指定分隔符连接多个字符
format：format(123.45,1)，数字格式化到小数点后一位
lower、upper：转为小写、大写字母
left:left(‘mysql’,2) :,取左边2位
length：length(‘mysql’)：去字符长度
ltrim、rtrim：去掉左右空格
trim：去掉前后空格
substring：substring(‘mysql’,2,4)：从第二位开始截取4位， substring(‘mysql’,2):从第二位开始截取到最后
replace：replace(‘mayasql’,’a’,’’)：字符串替换
like：模糊匹配 ,’%’代表任意字符，’_’代表任意一个字符，需要转义使用’\’;

数字运算函数

ceil：向上取整
div：整数除法
floor()：向下取整
mod(5%3)：取余
power(3,2)：幂运算
round:四舍五入
truncate(123,456,2)：123.45,数字截取

比较运算符

[not] between… and…
[not] in
is [not] null

日期时间函数

now()：当前精确时间
curdate()：当前日期
curtime()：当前（仅）时间
date_add：date_add(‘2017-11-12’,interval 365 day)：日期‘2017-11-12’加上365天后的日期
datediff：datediff(‘A’,’B’)：两个日期相差天数，返回值为A-B
date_format：date_format(‘2017-11-12’,’%d/%m/%Y’)：日期格式化，Y是返回4位的年份，y返回两位

信息函数

connection_id()：用户连接数据库分配的ID
database()：查看当前使用的数据库
user()：当前登录数据库的用户
version()：查看数据库版本
last_insert_id()：返回插入数据后的自增ID

聚合函数

avg()：求平均值
max()、min()：求最大、最小值
sum()：求和
count()：求记录条数，如count(id)

加密函数

md5()、password()

自定义函数

例1、创建不带参数的函数，创建一个函数，返回当前自定义格式的日期

create function f2() returns varchar(30)return date_format(now(),'%Y年%m月%d日');

2、带参数的函数，定义一个函数返回两个数的平均值

create function f3(num1 int unsigned,num2 int unsigned) returns float(8,2)return (num1+num2)/2;

3、创建带参数的复杂自定义函数，给一张表添加一条记录

• (1) 创建测试表test，表中只有id和name两个字段
• (2) 由于函数中会有多条以’;’结尾的语句，所以要修改sql语句结束符，这里修改为“//”：delemiter //
• (3) 创建自定义函数：

     mysql> create function addTest(name varchar(20)) returns int    -> begin    -> insert into test(name) values(name);    -> return last_insert_id();    -> end-> //

可以看到函数中有两条语句是已分号结尾，所以在创建函数前修改默认的sql结束符,多条语句可以用begin和end包裹，函数结尾使用“//”来结束，最后“delimiter ;”将sql结束符修改回来。

存储过程

优点：
1、增强SQL语句功能和灵活性
2、把第一次编译好的结果存在内存中，下次调用直接获取编译好的结果，速度快
3、减少网络流量

创建存储过程的例子

delimiter //mysql> create procedure sp_1(IN t_id int)    -> begin    -> delete from test where id = t_id;    -> end//delimiter ;

使用这个存储过程：call sp_1(1);

注意存储过程中的参数的名字不要和表中字段重名.

创建一个具有一个输入，多个输出的存储过程

mysql> delimiter //mysql> create procedure delAndRet(IN t_id int,out delNums int,out remNums int)    -> begin    -> delete from test where id > t_id;    -> select row_count() into delNums;    -> select count(id) from test into remNums;    -> end//mysql> delimiter ;

调用存储过程：

call delAndRet(3,@a,@b); select @a,@b;

注意在定义存储过程的时候，变量后面要跟变量的类型，如out delNums int , 这里int不能省略

存储过程与自定义函数的区别

1、存储过程一般实现的功能复杂一些，函数针对性更强
2、存储过程可返回多个值，函数只能有一个返回知
3、存储过程一般独立执行，而函数可以作为其他SQL语句的组成部分来实现。

存储引擎