mysql基础知识

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

可以修改表的存储引擎，但是需要注意：外键不能跨越存储引擎，因为外键是为了保证参照完整性。