mysql数据库学习
1、 数据库的基本操作
a) 查看已有的所有数据库：show databases;如果创建的数据库已存在，则创建出错
b) 显示tests数据库定义：show create database tests;
c) 使用某数据库：use tests;
d) 删除数据库：drop database tests;如果删除的数据库不存在，则删除出错。
使用drop删除数据库时要注意，在执行此命令时，mysql不会给出任何的提醒确认信息，并且用此语句删除数据库时，也将连数据库中存储的数据一起删除。
2、 数据库表的基本操作
a) 创建数据库表，需要指定：数据库表名，字段名、字段类型。（mysql中的表不区分大小写，并且不能使用sql的关键字）
create table tb_empl(id int primary key,namevarchar(25),deptId int,salary float)
create table tb_empl2(id int,name varchar(25),deptIdint,salary float,primary key (id)) ##id为主键
create table tb_empl3(id int,name varchar(25),deptIdint,salary float,primary key(id,name)) ##id，name为联合主键
create table tb_empl4(id int,name varchar(25),deptIdint,salary float,constraint fk_emp_dept1 foreign key (deptId) referencetb_dept(id))##外键
b) 非空约束
create table tb_empl3(id int,name varchar(25) not null,deptIdint,salary float,primary key(id,name))
c) 唯一性约束
create table tb_empl3(id int,name varchar(25),deptId int unique ,salary float,primary key(id,name))
create table tb_empl3(id int primary key,namevarchar(25),deptId int,salary float,constraint dept_Id unique(deptId))
d) 默认约束
create table tb_empl3(id int,name varchar(25),deptId intdefault 1111,salary float)
e) 设置表属性自动增加
create table tb_empl3(id int primary key auto_increment,
name varchar(25),deptId int,salary float)
f) 查看数据库表结构：desc tb_empl
g) 查询表的详细结构：show create table tb_emp或者show create tabletb_emp\G,显示更加规范的结果
h) 修改数据库表名字：alter table tb_empl4 rename tb_empl
i) 修改数据库表的字段数据类型：alter table tb_empl modify salary int
j) 修改表的字段名：alter table tb_empl change name changename int;
k) 添加字段：alter table tb_empl add sex varchar(2);
alter table tb_empl add sex varchar(2) not null; ##添加不能为空的字段
alter table tb_empl add sex varchar(2) first; ##在表的第一列添加一个字段
alter table tb_empl add sex2 varchar(2) after sex;
l) 删除字段：alter table tb_empl drop sex2;
m) 修改字段的排列位置：alter table tb_empl modify sex varchar(2) first;##将字段修改为表的第一个字段
alter table tb_empl modify sex varchar(2) after salary;
n) 删除表的外键约束：alter table tb_empl drop foreign key fk_deptId
o) 删除表：drop table table_name; ##如果表中包含另一个表的外键，则应该先删除外键约束，在删除此表。
3、 数据查询
创建表fruits表：
create table fruits(f_id char(10) primary key,s_id int notnull,f_name char(25) not null,f_price decimal(8,2));并插入相应的数据。
a) 查询表中所有数据
select * from fruits;
select f_id,s_id,f_name ,f_price from fruits;
两个语句的效果相同，不要轻易地使用通配符*，使用那个通配符会查找到不需要的列，造成了资源的浪费。
b) 查询特定的列
select f_id from fruits;
select f_id,f_name fron fruits; ##查询多列
c) 查询具有限定条件的记录
select f_name,f_price from fruits where f_price =10.2; ##查询价格为10.2的水果的名字与价格
select f_price from fruits where f_name=’apple’;##查询名字为apple的水果的价格
select f_name from fruits where f_price<10;##查询价格小于10的水果的名称
d) 带有in关键字的查询
select s_id ,f_name,f_price from frutis where s_idin(101,102);##查询s_id为101,102的记录
Select s_id,f_name,f_price from fruits where s_id not in(101,102);#查询s_id不是101，也不是102的记录
e) between and 查询
select f_name,f_price from fruits where f_price between 2and 10.2;#查询价格在2-10.2之间的水果
select f_name,f_price from fruits where f_price not between2 and 10.2;#查询价格不在2-10.2之间的水果
f) like字符匹配
select f_id,f_name from fruits where f_name like ‘b%’;##查询以b开头的水果
select f_id,f_name from fruits where f_name like ‘%g%’;##查询f_name中包含g的记录
select f_name from fruitswhere f_name like ‘b%y’;##查询以b开头以y结尾的水果
g) 查询空值: select f_id from fruits where f_price is null;
select f_id from fruits where f_price is not null;
h) 带有and的多条件查询
select f_id,f_name,f_price from fruits where s_id=’101’ andf_price >=5;
i) 带有or的多条件查询
select s_id,f_name,f_price from fruits where s_id =101 ors_id=102;
j) 查询不重复结果
select distinct s_id from fruits;
k) 排序order by
select f_name from fruits order by f_name;
select f_name from fruits order by f_name,s_id; ##多列排序
select f_name from fruits order by f_name desc; ##指定排序
select f_name from fruits order by f_name asc; ##默认为asc排序
l) 分组group by 和过滤分组
select s_id ,count(*) as total from fruits group by s_id;
select * from fruits group by s_id,f_name;##多字段分组
select s_id,group_concat(f_name) as names fron fruits groupby s_id having count(f_name)>1;
m) 使用limit查询
select * from fruits limit 4,3;##第一个参数表示从第一行开始，第二个参数表示返回多少行
n) 使用集合函数
Select count(*) fron fruits;##count（*）包含为空的记录，count（字段名）不包含为空的记录；
max()计算最大值
min()计算最小值
sum()计算总和
avg()计算平均值
o) case用法
select case when f_price >10.2 then ‘more’ whenf_price<10.2 then ‘less’ else ‘equals’ end as new_price from fruits;
4、 索引
a) 索引是一个单独的、存储在磁盘上的数据结构，在mysql中索引依赖于存储引擎。Mysql中最常用的存储引擎是MyISAM与InnoDB，这两种存储引擎的索引都是通过btree实现的。
索引虽然会占据大量的磁盘空间，但是索引可以加快查询速度。如果数据量较少、需要频繁的更改数据等情况下最好不要使用索引。
b) 索引分类
索引有普通索引、唯一索引unique、单列索引、组合索引、全文索引fulltext、空间索引spatial。（下面的实验主要以普通索引为例）
c) 为新表创建索引
create table index_table1(id int,name varchar(10),index(id));
d) 为已存在的表添加索引
create table index_table2(id int,name varchar(10));
alter table index_table2 add index table2_indedx(id);
e) 使用create创建索引
create table index_table3(id int,name varchar(10));
create index table3_index on index_table3(id);
show create table index_table3;
f) 删除索引
drop index table3_index on index_table3 ;
g) 删除索引
alter table index_table2 drop index table2_indedx;
5、 存储过程与函数
a) 变量的使用
declare var1,var2,var3 int; ##变量的定义
set var1=10,var2=10;
set vae3=var1+var2;
select s_id into var1 from fruits where f_id=’a1’;
b) 存储过程的创建、调用、删除
delimiter //
create procedure avgPrice()
begin
select avg(f_price) fromfruits ;
end //
delimiter ;
注意：delimiter 是设定结束符号的，为了与begin and中的结束符区分开来
创建带参数的存储过程
delimiter //
create procedure countPro(OUT param int)
begin
select count(*) intoparam from fruits;
end//
delimiter ;
c) 创建函数
delimiter //
create function NameByZip()
returns char(25)
returns (select f_name from fruits where f_id=’a1’)
//
delimiter;
6、 存储引擎
a) 显示支持的存储引擎：show engines;
b) 修改默认的存储引擎：找到mysql的配置文件my.ini，修改default-storage-engine，将其值由InnoDB改为MyISAM，重新启动数据库即可。
c) 修改表的存储引擎：alter table tb_empl engine=MyISAM
可以修改表的存储引擎，但是需要注意：外键不能跨越存储引擎，因为外键是为了保证参照完整性。