mysql基础篇
mysql简介:关系型数据库管理系统，免费开源，支持多语言[访问使用方便],跨平台[移植性好],高效[mysql核心程序采用多线程编程],支持大量数据库查询和存储[可承受大量的并发]
连接登录mysql:mysql -u 用户 -p密码
显示所有数据库：show databases;
选定默认数据库：use 数据库名;
显示默认数据库中所有表：show tables;
放弃正在输入的命令：\c
显示命令清单：\h
退出mysql程序：\q 或者 exit
查看mysql服务器状态信息：\s
创建数据库：create database 数据库名 CHARSET=utf8; ---RSET=utf8定义字符集，不会产生乱码 --前提条件:没有创建该数据库，并拥有创建条件
显示数据库结构：show create database 数据库名;
删除数据库：drop database 数据库名; ---删除数据库会删除数据库所有表和数据、索引等
常用存储引擎： ---存储引擎指表的类型,数据库存储引擎决定表的存储方式
InnoDB存储引擎：行级锁
MyLSAM:表级锁---查询效率高
MEMORY:直接存内存，不存磁盘----临时表
查看支持存储引擎:show engines;
指定表的存储引擎:create table tmp(…) engine=存储引擎;
设置默认存储引擎:set default_storage_engine=c存储引擎;
---临时修改，重启后恢复默认，永久需修改mysql配置文件
创建表：create table 表名（
列名1 列类型 [<列的完整性约束>],
列名1 列类型 [<列的完整性约束>],
...
);
create table sudents(
Id int(10) primary key not null auto_increment,
Name varchar(20) not null,
Sex varchar(4),
age int(10),
class varchar(20) not null,
Addr varchar(50)
);
常见约束:
primary key 主键约束 唯一标示一条记录，不能重复，不能为空，主键只能一个，可以多个字段联合为主键
foreign key 外键约束
unique 唯一约束
not null 非空约束
auto_increment 整数列默认自增1
default 默认值 默认值约束
查看表结构：
desc 表名;
show create table 表名；
修改表： ---不需要重新加载数据，不会影响正在进行的服务
修改表名：alter table 旧表名 rename[to] 新表名；
修改字段数据类型：
alter table 表名 modify 属性名 数据类型;
alter table 表名 change 旧属性名 新属性名 新数据类型;
增加字段：alter table 表名 add 属性名1 数据类型[完整性约束条件] [first|after 属性名2];
增加外键: alter table 表名 add constraint FK_ID foreign key(外键字段名) references 外表表名(对应表主键字段名);
删除表的外键约束：alter table 表名 drop foreign key 外键别名;
修改字段的排列位置：alter table 表名 modify 属性名1 数据类型 first|after 属性名2; --after 属性名2 参数指定 属性名1 插入在 属性名2之后
删除字段:alter table 表名 drop 字段名;
清空表:truncate 表名;
tuncate和delete区别:
truncate列的id也从建表时设置的起始id开始
delete数据会清空,但是自增长列的id不会从起始id开始，可以回滚
删除表：drop table 表名;
更改表的存储引擎:alter table 表名 engine=存储引擎名;
insert插入记录
为表的所有字段插入数据:insert into 表名 values(所有字段对应值，并按字段显示顺序插入);
为表的指定字段插入数据:insert into 表名(属性列1，属性列2,...，属性列n) values (值1,值2,...，值n);
同时插入多条数据:insert into 表名[(属性列)] values (取值列表1),(取值列表2)...,(取值列表n);
将查询结果插入到表中:insert into 表名1(属性列1) select 属性列2 from 表名2 where 条件表达式;
复制表结构：create table score_new like score; -
备份表: create table score_bk as select * from score;-
注：1.value和values区别：没有区别
2.字符串：单双引号都可以
3.不指定需要写全字段，包含自增长
4.Mysql默认自动提交
5.set autocommit=0;---设置不自动提交
replace插入记录
replace into 表名[(字段列表)] values(值列表);
replace [into] 目标表名[(字段列表1)] select (字段列表2) from 源表 where 条件表达式
replace [into] 表名 set 字段1=值1,字段2=值2
insert和replace区别:replace向表插入新记录，如果新记录的主键值或者唯一性约束字段值与已有记录相同，则已有记录先删除，然后再插入新记录.
更新数据:
update 表名 set 属性名1=取值1,属性名2=取值2,...,属性名n=取值n where 条件表达式;
删除数据:
delete from 表名 [where条件表达式];
查询数据:
select 属性列表 from 表名和视图列表 [where 条件表达式1] [group by 属性名1 [having 条件表达式2]] [order by 属性名2 [asc |desc]] ---asc:升序，默认升序 --desc降序
多表连查:join
左连接:left join --以左边表的数据匹配右边表中的数据，如果左边表中的数据在右边表中没有，会显示左边表中的数据
右连接:right join --以右边表的数据匹配左边表中的数据，如果右边表中的数据在左边表中没有，会显示右边表中的数据
内连接:inner join --显示匹配数据
带in关键字的查询:[not] in (元素1,,元素2,…,元素n)
Between and范围查询:[not] between 取值1 and 取值2
带like的字符匹配查询:[not] like '字符串' ---%零个或多个字符组成的任意字符串 ---_任意一个字符
空值查询:is [not] null
select * from ningxin where age is null or age=’’;---空字符串
查询结果剃重:distinct
限制查询条数:limit
1.limit 5 前5行
2.limit 1,5 从1行到5行
聚合函数:
统计记录条数:count()
字段的值总和:sum()
字段的值平均值：avg()
字段的最大值:max()
字段的最小值:min()
合并结果集:
select 字段列表1 from table1
union [all]
select 字段列表2 from table2
注:字段列表1与字段列表2的字段个数必须相同，相同的数据类型。合并的新结果集字段名与字段列表1中字段名对应.
union与union all区别:
union:会筛选掉select结果集中重复记录[结果集合并会对新产生的结果集进行排序，效率稍低]。
union all：直接合并两个结果集，效率高于union.
Group by子句
select s.*,c.c_name from students s inner join new_score c on s.id=c.stu_id where c.stu_id in (Select c.stu_id from new_score c where c.c_name in ('英语','计算机') group by c.stu_id having count(c.stu_id)>=2);
子查询
update students set name='天才' where name in (select name from (select s.name from students s,new_score c where s.id=c.stu_id group by s.name having sum(c.grade)<100) a);
备份：mysqldump –uroot –p123456 data > data.sql --非sql命令
恢复：mysql –uroot –p123456 data < data.sql --非sql命令
索引
创建普通索引:
create index 索引名称 on 表名(列)
alter table 表名 add index 索引名称(列)
创建唯一索引:
create unique index 索引名称 on 表名(列名)
alter table 表名 add unique index 索引名称(列)
删除索引:drop index 索引名 on 表名；
视图
创建视图:create view 视图名称(视图列1,视图列2) as select
create view st_view_a as select s.name,sum(c.grade)as sum_grade from students s,new_score c where s.id=c.stu_id group by s.name having sum(c.grade)>160;
create view st_view_b(class,c_name,grade,name,sex) as select s.class,c.c_name,c.grade,s.name,s.sex from students s,new_score c,st_view_a a where s.id=c.stu_id and s.name=a.name ;
查看视图:
desc 视图名称;
show table status;
show create view 视图名称
select * form 视图名称;
修改视图:
create or replace view 视图名称 (列1，列2) as select语句
alter view 视图名称(列1,列2) as select语句
删除视图:drop view [if exists] 视图名列表
存储过程
delimiter $;
create procedure 名称(参数列表)
begin
sql语句块
end
$;
delimiter ;
if语句判断格式:
if 条件 then
语句
elseif 条件 then
语句
else
语句
end if；
case条件判断格式:
case value
when 条件 then
sql语句
when 条件2 then
sql语句
else --上面条件都不满足执行
sql语句
end case
SELECT class,
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) as 及格人数,
SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) as 不及格人数
FROM tb1 GROUP BY class;
while循环格式:
while 条件 do
sql语句
end while；
delimiter $;
create procedure add_students(s_count int)
begin
declare Name varchar(20);
declare class varchar(20);
declare i int;
set i=0;
set class= '乌索布' ;
while i<s_count do
set Name=CONCAT('宁欣',i);
insert into students(name,class) values(Name,class);
set i=i+1;
end while;
end
$;
delimiter ;
repeat循环格式:
repeat
sql语句
until 条件
end repeat；
delimiter $;
create procedure add_score(s_count int)
begin
declare i int;
set i=0;
repeat
insert into new_Score (stu_id) values(i);
set i=i+1;
until i>s_count
end repeat;
end
$;
delimiter ;
查看存储过程
show status语句:show procedure status；
show create语句:show create procedure 存储过程名称;
