MySQL数据库学习

数据库学习

创建数据库

语法规则：create database 数据库名

create database user;

创建数据库并且设置它的字符集

语法规则：create database 数据库名 character set 字符集

create database user character set utf8;

删除数据库

语法规则：drop database 数据名

drop database user;

创建表

语法规则：

create table 表名(

​ 字段1 数据类型，

​ 字段2 数据类型，

​ 字段3 数据类型

)

create table user(
	ID int(10) auto_increment primary key,
	user varchar(20) not null unique,
	passwrod varchar(20) not null
)

删除表

语法规则：drop table 表名

drop table user;

​ 对表进行添加、删除、修改、查询

往指定的表中插入数据

语法规则：insert into 表名 (字段1，字段2…) values(字段1值，字段2值…

insert into user(username,password)  values('wanyuan','123456')

同时插入多条信息

insert into user(username,password)	  values('wanyuan','123432'),('yuan','23432'),('pan','4324')

插入数据方式二

语法规则：insert into 表名 set 字段1=字段1值 …

insert into user set username='wanyuan',password='2234314'

简化插入数据(可以省略掉表后面的字段名，但前提是：values关键字后面的字段值数量必须与表字段数据保持一致)

insert into  values('wanyuan','123432')

删除表中的数据

语法规则：delete from 表名 where 条件

delete	from  user  where username='wanyuan'

注意： “=” 放在set关键字后面是表示赋值，“=” where关键字后面是表示关系

在 mysql 中的关系运算符：=、>、>=、<、<=、!=

在 mysql 中的逻辑运算符: 与(and)、或(or)、非(not)

插入表中的数据

语法规则：select 字段1，字段2… from 表名 [where 条件]

select * from user;
select username,password  from user;
select username,password  from user   where ID>=3;

给查询的数据字段取别名

select username as 姓名,password as 密码  from user where  ID>=1;    (给字段取别名)
select username 姓名,password 密码 from user where ID>=1;    (as可以省略)
select s.username,s.password from user s;           (给表取别名)

select username,password  from  user   where user='wanyuan' and   password='123456'

更新表中的数据

语法规则：update 表名 set 字段1=字段值1，字段2=字段值2… where id=1;

update user  set  username='panjianhao',password='2343243'   where id=1;

删除表中的数据

语法规则：delete from 表名 where 条件

delete from user  where  id=1;

​ 对sql语句进行分类

**数据库查询语言(DQL)：**对表的查询语句：select

**数据库定义语言(DDL)：**create database、drop database、create table、drop table等

**数据库操作语言(DML)：**update、insert、delete

查询指定库的详细信息： (DDL)

(1) 查看创建库的信息：show create database 库名

​ show create database mine;

(2)查看：当前用户是哪个数据库：

​ select database();

(3) 查看mysql服务器软件下所有的库

​ show databases;

(4)查看指定的数据库下有哪些表：

​ show tables;

修改指定库的编码 ： alter database 库名 character set 新编码名；

​ alter database mine character set utf8;

查询：

(1)查询指定表结构: desc 表名;

​ desc user;

(2)打印某一张表的创建信息: show create table 表名;

​ show create table shangpin;

修改表:

(1) 对已经存在的表进行重命名：rename table 旧表名 to 新表名；

​ rename table shangpin to mydemo;

(2)往已经存在的表中添加字段信息：alter table 表名 add 字段名 数据类型;

​ alter table shangpin add gender varchar(20);

(3)删除某一张表中的字段: alter table 表名 drop 被删除的字段名;

​ alter table shangpin drop gender;

(4)对表中的字段名进行重命名: alter table shangpin change 就字段名 新字段名 新字段名的数据类型;

  alter	table	shangpin	change	username	userrrname    varchar(30) ;

(5)修改某一字段的数据类型长度：alter table shangpin change username username varchar(40);

对某一数据库的备份与还原

第一种通过命令：mysqldump -uroot -p 密码 需要备份的数据库名>备份后的sql脚本名；

mysqldump -uroot -p mine>C:\mine_back.sql;

还原备份的文件数据：首先需要进入mysql环境—>创建一个库—>在库下还原数据

--------------->source 备份的数据库脚本

source C:\mine_back.sql;

第二种通过工具(Navicat for MySQL)

​ 数据类型的属性

mysql中常见的数据类型：varchar(20)、float(n)、int(n)、bigint(n)、date、datetime、text

默认值(default )

非空(not null)(如果某字段被not null修饰后，添加数据时，此字段必须填写)

主键(primary key)(主键必须唯一)(不能够重复，一张表中只有一个字段可以作为主键)

唯一键(unique)

自动增长(auto_increment)(尽量作用在Int类型字段上)

注释(comment)

例如：

create table student(

​ id int(20) auto_increment primary key comment ‘学生编号’,

​ stuName varchar(20) not null comment ‘学生姓名’,

​ gender varchar(2) default ‘男’ comment ‘姓名’

)

如果要删除一整表中的数据，使用truncate。使用truncate删除后，如果字段时自增的,则从1开始

如果要用使用delete删除整张表，但是删除数据后，自增列不会从1开始

​ 排序(order by 字段 降序/升序)

在mysql中函数使用select关键字调用：select 函数名(字段) 【from 表名】

升序(ASC)：select * from shangpin order by money ASC;

降序(DESC)：select * from shangpin order by money DESC;

​ 聚合函数

找出最大值：max(字段名)

select max(money) from shangpin;

找出最小值：min(字段名)

select min(money) from shangpin;

求平均数：avg(字段名)

select avg(money) from shangpin;

求和：sum(字段名)

select sum(money) from shangpin;

统计记录：count(字段名)(如果字段的值为NULL,则此字段对应的数据条数不在统计之内)

select count(ID) from shangpin;

获取当前系统时间(年月日时分秒)

select now();

只获取系统的时分秒

select CURTIME();

只获取系统的年月日

select CURDATE();

向上取舍

select CEIL(2.3);

向下取舍

select FLOOR(2.3);

随机数

select RAND()*100;

同时查询多条记录

select * from shangpin where id in(1,2,4);

select * from shangpin where id not in(2) order by money ASC;

​ 分组查询(group by 分类字段)

查询shangpin表中的商品的种类

select * from shangpin group by type;

注意点：如果一个查询语句中使用了group by,则后面的条件需要使用having关键字

select * from shangpin group by type having type=‘玩具’;

​ 分页查询(limit 起始下标，每页显示的数据量)

例如:每页显示3条

select * from shangpin limit (pageNo-1)*pageSize,pageSize;

第一页

select * from shangpin limit 0,3;

第二页

select * from shangpin limit 3,3;

​

同时查询多张表

select * from 表1 别名1，表2 别名2… where 条件;

select d.deptName,p.empName from detp d,emp p where d.id=p.deptID;

例如：

查询两张表中的“开发部”和"测试部"

第一种方式

select d.deptName,p.empName from detp d,emp p where d.id=p.deptID and deptName in (“开发部”，“测试部”)；

第二种方式

select d.deptName,p.empName from detp d,emp p where d.id=p.deptID and (deptName=“开发部” or deptName=“测试部”)；

第三种方式

(union 、union all：可以将两个查询语句的结果进行合并,合并的前提是两个查询语句的数据类型一样)

union：可以自动去除重复

union all：不能够去除重复

select d.deptName,p.empName from detp d,emp p where d.id=p.deptID and deptName=“开发部” ；

union

select d.deptName,p.empName from detp d,emp p where d.id=p.deptID and deptName=“测试部”；

select d.deptName,p.empName from detp d,emp p where d.id=p.deptID and deptName=“开发部” ；

union all

select d.deptName,p.empName from detp d,emp p where d.id=p.deptID and deptName=“测试部”；

多表查询语法1：select * from 表1 别名1，表2 别名2… where

多表查询语法2：

​ 内链接（inner join)(满足条件才显示)

​ 外连接(左外连接，右外连接)

内链接：表1 别名1 inner join 表2 别名2 on 条件 （on只跟关联的表有关系) where 条件

select d.deptName,p.empName from detp d inner join emp p;

两个效果一样(表越多，下面的那种方式越直观)

select d.deptName,p.empName from detp d,emp p;

select d.deptName,p.empName from detp d inner join emp p on d.id=p.deptID and d.deptName=“测试部”； (and 后面的条件只与detp表有关系)

select d.deptName,p.empName from detp d inner join emp p on d.id=p.deptID where d.deptName=“测试部”； (where 后面的条件跟前面的表没有关系)

左外连接 (left join)(以左边的表为主)

左外连接当条件不满足时，以左边的表为主

select d.deptName,p.empName from detp d left join emp p on d.id=p.deptID ;

右外连接 (right join)

右外连接当条件不满足时，以右边的表为主

select d.deptName,p.empName from detp d right join emp p on d.id=p.deptID;

模糊查询

语法规则：

select * from user where username like ‘王%’; （模糊查询以‘王’开头的名字）

select * from user where username like ‘%豪’; （模糊查询以‘豪’结尾的名字）

视图 (在一张真实的表上构建一张虚表)(对视图的操作与对表的操作是一样的)(对虚表进行数据修改时，真实的表也被进行修改)

语法规则：create view view_table as 表；

例如：

create view view_table as select * from user;

事务

​ 什么是事务？

​ 事务的4大特性：

​ 原子性（automic)：多组操作为一个整体，不能分割

​ 一致性（consistent)：操作前后最终的总量一样

​ 隔离性（isolation)：多个事务之间相互隔离互不干扰(级别越高，性能越差)

​ 脏读

​ 虚读与幻读

​ 不可重复读

​ 在mysql中事务有4中隔离级别：read uncommited(只读未提交)、read committed(只读提交)、repeatable read、serial

​ read uncommitted 模式会产生脏读，克服脏读用read committed；

​ read committed 模式会产生不可重复读，克服不可重复读用repeatable read;

​

​ 持久性(durable)：数据进入到数据库中后，数据需要持久存在

​ 查看mysql软件的事务隔离级别：select @@tx_isolation;

​ 修改mysql软件默认的隔离级别：set global transaction isolation level 隔离级别

​ set global transaction isolation level read committed;

开启事务

start transaction

回滚事务

rollback

提交事务

commit

存储过程

语法规则：

create procedure 存储过程名(参数名1 参数类型1，参数名2，参数类型2…)

begin

​ 代码块

end

书写一个加薪的存储过程

delimiter // (分割符)

create procedure addSalary(money float,idd bigint)

begin

​ update emp set salary=salary+money where id=idd;

end //

delimiter ;

调用存储过程：call 存储过程名();

call addSalary(1000,10);

有返回值的存储过程

语法规则：

delimiter //

create procedure 存储过程名(in 参数名1 参数类型1，in 参数名2 参数类型2,out 参数名 参数类型)

begin

​ 代码块

end //

delimiter ;

例子:

delimiter //

create procedure add(in j int,in i int,out sum int)

begin

​ set sum=i+j;

end //

delimiter ;

call add(10,20,@result);

select @result;

带if的存储过程

delimiter //

create procedure add(in i int)

begin

​ if i>0 then

​ set sum=i+j;

​ end if;

end //

delimiter ;

带if…else的存储过程

delimiter //

create procedure add(in i int)

begin

​ if i>10 then

​ set sum=i+j;

​ elseif i>5 then

​ select ‘输入的数值不能为负数’ as ‘友情提示’；

​ end if;

end //

delimiter ;

带while的存储过程

delimiter //

create procedure add(in i int,out total int)

begin

​ declare a int default 1;

​ set total=0;

​ while a<i do

​ set total=total+a;

​ set a=a+1;

​ end while;

end //

delimiter ;

call add(10,20,@result);

select @result;

带case的存储过程

delimiter //

create procedure add(in a int)

begin

​ case i

​ when 1 then

​ select ‘星期一’ as ‘日期’;

​ when 2 then

​ select ‘星期2’ as ‘日期’;

​ else

​ select ‘今天不是星期一或星期二’ as ‘日期’;

​ end case;

end //

delimiter ;

call add(10,20,@result);

select @result;

​

​