MySql 笔记

库结构操作

登录数据库：mysql -uroot -p
查看数据库列表：show databases;
创建数据库：create database ‘databaseName’ character set utf8;
删除数据库：drop database ‘databaseName’;
切换数据库：use ‘databaseName’;
查看数据库结构：show create database ‘databaseName’;
修改数据库字符集：alter database ‘databaseName’ character set utf8;
character set 与 charset 功能一致,后面的是简写

表结构基础操作

1、查看表结构信息
show create table ‘表名称’;

2、查看表字段信息
describe ‘表名称’;
describe可简写为desc

3、查看表字段信息（详细）
show full columns from ‘表名称’;

4、修改表字符集
alter table ‘表名称’ charset utf8;

5、创建表
create table ‘test’ (
‘id’ int(11) not null auto_increment,
‘area’ varchar(255) not null default ‘’ comment ‘地区’,
primary key (‘id’)
) engine = innodb auto_increment = 1 default charset = utf8;

6、删除表
drop table ‘表名称’;

7、新增表字段
alter table ‘表名称’ add column ‘字段名’ varchar(50) not null default ‘默认值’ comment ‘注释’;

8、修改字段属性
alter table ‘表名称’ modify ‘字段名’ varchar(50) not null default ‘默认值’ comment ‘注释’;

9、修改字段名
alter table ‘表名称’ change ‘旧字段名’ ‘新字段名’ varchar(50) not null default ‘默认值’ comment ‘注释’;

10、删除字段
alter table ‘表名称’ drop colunm ‘字段名’;

11、添加主键约束primary key
alter ‘表名称’ add constraint ‘约束名’ primary key(‘字段名’);

12、添加唯一约束unique
alter ‘表名称’ add constraint ‘约束名’ unique(‘字段名’,‘字段名’);

13、添加检查约束check
alter ‘表名称’ add constraint ‘约束名’ check(‘字段名’ > 0);

14、添加默认约束default for
alter ‘表名称’ add constraint ‘约束名’ default ‘默认值’ for ‘字段名’;

15、删除约束
alter ‘表名称’ drop index ‘主键名’;

16、复制备份数据
select *（查询出来的结果） into newtable（新的表名）form where （后续条件）
insert into (准备好的表) select *（或者取用自己想要的结构）frome 表名 where 各种条件

–第一种写法
SELECT * FROM (SELECT * FROM Table1 WHERE Name LIKE ‘One%’) a LEFT JOIN Table2 b ON a.ID=b.ID

–第二种写法(跟第一种效率是一样的)
SELECT * FROM Table1 a LEFT JOIN Table2 b ON a.ID=b.ID WHERE a.Name LIKE ‘One%’

–第三种写法(如果 Table1,Table2 都是大表，而且第1个select条件过滤后记录很少，效果显著 )
SELECT * INTO #tmp FROM Table1 WHERE Name LIKE ‘One%’
SELECT * FROM #tmp a LEFT JOIN Table2 b ON a.ID=b.ID

17、清空表数据
truncate table 表名

表数据进阶操作

1、Limit 获取查询出来结果的前X条数据
select * from table_name limit n,m;
n：从第几行开始，第一行是0
m：获取几个
只填一个参数，n默认是0

2、通配符
%替代一个或多个字符
_仅替代一个字符
[]字符列中的任何一个字符
[^]或[!]不在字符列中的字符
select * from table_name where column_name like ‘NE%’; 查找表中某字段以’NE’开头的数据
select * from table_name where column_name like ‘%NE%’; 查找表中某字段包含’NE’的数据
select * from table_name where column_name like ‘%NE%’; 查找表中某字段包含’NE’的数据
select * from table_name where column_name like ‘_NE’; 查找表中某字段第一字符之后是’NE’的数据
select * from table_name where column_name like ‘[NE]%’; 查找表中某字段开头是’N’或’E’的数据
select * from table_name where column_name like ‘[!NE]%’; 查找表中某字段开头不是’N’或’E’的数据

3、Between…and 等同与 >= <=
select * from table_name where column_name between n and m;
select * from table_name where column_name >= n and column <= m;

4、联表查询
假设
表’table_a’字段有’id’,‘name’,‘classId’,‘score’ 自增id，学生名，学科id，分数
表’table_b’字段有’classId’,‘className’ 学科id，学科名
需要查出学生名，学科名，学科分数信息

4.1、一般写法
select table_a.name,table_a.score,table_a.className from table_a,table_b where table_a.classId = table_b.classId;
select a.id,a.name,b.scores from table_a as a,table_b as b where a.classId = b.classId;

4.2、inner join 双方匹配到的才会返回
select table_a.name,table_a.score,table_a.className from table_a inner join table_b on table_a.classId = table_b.classId;

4.3、left join 返回左表查询字段所有的行，即使右表中没有匹配
4.3、right join 返回右表查询字段所有的行，即使左表中没有匹配
4.4、full join 返回查询字段所有的行
4.5、查询条件放在on和where后的区别
on是在生成连接表的起作用的，where是生成连接表之后对连接表再进行过滤
table1 table2
id size size name
1 10 10 AAA
2 20 20 BBB
3 30 30 CCC

例1：
select * from tab1 left join tab2 on (tab1.size = tab2.size) where tab2.name=‘AAA’

(1)中间表 on条件: tab1.size = tab2.size
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 20 BBB
2 20 20 CCC
3 30 NULL NULL

(2)再对中间表过滤 where 条件： tab2.name=‘AAA’
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA

例2：
select * from tab1 left join tab2 on (tab1.size = tab2.size and tab2.name=‘AAA’)
(1)中间表 on条件: tab1.size = tab2.size and tab2.name=‘AAA’ (条件不为真也会返回左表中的记录)
tab1.id tab1.size tab2.size tab2.name
1 10 10 AAA
2 20 NULL NULL
3 30 NULL NULL

5、union，union all合并多个select的结果集
union 并集（结果重复的只会显示一个）
INTERSECT 交集
EXCEPT 差集
select classId from ‘table_a’ union select classId from ‘table_b’;

函数

1、avg 平均值

2、count 统计行

3、first 首条记录

4、last 末尾记录

5、max 最大值

6、min 最小值

7、sum 总和

8、order by 排序

9、group by 分组

10、having

11、ucase 转换大写

12、lcase 转换小写

13、mid 截取字符串
mid(字段名,开始位置,长度)
起始位置为1
长度不填默认选择剩余的长度

14、len 文本长度

15、round 数值舍入
round(字段名,舍入的小数位)

16、now() 时间
select now() as time from tableName;

17、format 格式化字段
select format(now(),‘YYYY-MM-DD’)as time from tableName;

存储过程

一条或多条可编程的（设置参数、条件判断）语句集
1、无参数
delimiter $
create procedure pd()
begin
select * from t1;
end $

– 运行
call pd()

2、有参数
delimiter $
create procedure pdIn(in param_id int)
begin
select * from t1 where id = param_id;
end $

– 运行
call pd(1)

3、输出参数
delimiter $
create procedure pdOut(out val varchar(20))
begin
select name from t1 where id = 1;
set val = name;
end $

– 运行
call pdOut(@val);
select @val

4、inout、into
– inout 代表这个参数即时输入参数也是输出参数
– into 把查询结果赋值给参数
delimiter $
create procedure pdInout(inout val int)
begin
select size into val from t1 where id = val;
end $

– 运行
set @val=1;
call pdInout(@val);
select @val

5、判断
if num=1 then – 开始
set str=‘星期一’;
elseif num=2 then
set str=‘星期二’;
elseif num=3 then
set str=‘星期三’;
else
set str=‘输入错误’;
end if; – 结束

6、循环
while i<= param do
– 操作
end while;

7、定义变量
declare num int default 1;

性能优化

1、is null 和 is not null
where 列 is null，该列建立了索引，若列中有null值，索引会不起作用

2、联接列
不使用索引：
select * from employss where first_name||"||last_name =‘Beill Cliton’;

使用索引
select * from employss where first_name =‘Beill’ and last_name =‘Cliton’;
like ‘c%’;

3、通配符
不使用索引：
like ‘%cliton%’;

使用索引
like ‘c%’;

最左原则

联合索引 A、B、C
1、A、B、C三个条件同时存在，顺序可以不一样，因为查询优化器会自动优化成最适合的查询条件
2、A、C 或 A、B性能会下降、但依旧用到的索引
3、B、C会全表搜索
所以最左原则就是条件必须含有索引的最左侧字段

执行计划 explain

在select语句前面加explain，可查看查询语句的执行情况
select_type：查询的类型，SIMPLE表示简单的select,没有union和子查询
table：输出结果集的表
type：表的连接类型，all是全表，range是范围，const索引一次就找到
possible_keys：查询时，可能使用的索引
key：实际使用的索引
key_len：索引字段的长度
ref：列与索引的比较
rows：查询结果的行数
Extra：执行情况的描述和说明