1500字范文 > MySQL数据库入门与实战

MySQL数据库入门与实战

时间：2020-01-06 08:58:02

相关推荐

MySQL数据库入门与实战

常见数据库

关系型数据库

常见的关系型数据库有 MySQL、Oracle、SQL Server、JDBC、DB2 等；主要是用来描述实体与实体之间的关系，实实在在存在的事物。例如：男生和女生、学生和班级、员工和部门。

非关系型数据库

常见的非关系型数据库有 Redis、MongoDB、NoSQL 等；非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

数据库与服务器

数据库

数据库管理软件。通常一个应用对应一个数据库。

服务器

服务器就是一台电脑，这台安装相关的服务器软件，这些软件监听不同的端口号，根据用户访问的端口号，提供不同的服务。

MySQL 的 SQL 语句

SQL：Structure Query Languages （结构化查询语言）

数据定义语言(DDL)

定义数据库，数据表的结构：create(创建)、drop(删除)、alter(修改)。

数据操纵语言(DML)

主要是用来操作数据：insert(插入)、update(修改)、delete(删除)。

数据控制语言(DCL)

定义访问权限，取消访问权限，安全设置 grant。

数据查询语言(DQL)

select(查询) from 子句 where 子句。

数据库的 CRUD 的操作

前置知识

登录数据库：mysql –uroot –proot

退出数据库：exit、quit、\q、Ctrl+c

退出当前命令行：\c

复制命令行的内容：标记->选择内容->回车，不用Ctrl+C

导出数据库文件：

进入 MySQL 的安装目录的 bin 目录，如：D:\Program Files (x86)\MySQL\MySQLServer5.0\bin用 cmd 打开输入命令：mysqldump –h localhost –u root –p 数据库名字>导出路径

mysqldump –h localhost –u root –p worker>e:worker.sql

启动MySQL服务：net start mysql关闭MySQL服务：net stop mysql

创建数据库

create database 数据库名字;

create database test;

创建数据库的时候，指定字符集create database 数据库的名字 character set 字符集;

create database test character set utf8;

create database 数据库的名字 character set 字符集 collate 校对规则;

create database test character utf8 collate utf8_bin;

查看数据库

show create database 数据库名字;

查看所有数据库

show databases;

注意：三个文件不可删
information_schema 相关信息约束
performance_schema 相关表现约束
mysql

修改数据库的操作

修改数据库的字符集

alter database 数据库的名字 character set 字符集

alter database test character set gbk;

删除数据库

drop database 数据库名字;

切换数据库(选中数据库)

use 数据库名字;

查看当前正在使用的数据库

select database ();

表的 CRUD 操作

创建表

create database 数据库名字 create table 表名( 列名列的类型(长度) 约束, 列名2 列的类型(长度) 约束 );

列的类型

列的约束

主键约束：primary key

唯一约束：unique

非空约束：not null

create table student(sid int primary key,sname varchar(10),sex int,age int);

查看表

查看所有的表

show tables;

查看表的创建过程

show create table 表名;

show create table student;

查看表的结构

desc student;

修改表

添加(add)，修改列(modify)，修改列名(change)，删除列(drop)，修改表名(rename)，修改表的字符集

添加列(add)

alter table 表名 add 列名列的类型列的约束

alter table student add score int not null;

修改列(modify)

alter table student modify sex varchar(2);

修改列名(change)

alter table student change sex gender varchar(2);

删除列(drop)

alter table student drop score;

修改表名(rename)

rename table student to students;

修改表的字符集

alter table student character set gbk;

删除表

drop table students;

移动列的顺序

alter table 表名 modify 字段名字段类型 after 字段

alter table student modify sex int(11) after sname;(移动到sname后面)

alter table student modify sex int(11) first;(移动到最前面)

SQL 表中的 CRUD 的操作

插入数据

insert into 表名(列名1,列名2,列名3) values(值1,值2,值3);

insert into student(sid,sname,sex,age) values(1,'zhangsan',1,22);

简单写法(插入部分列列名不可省略)

insert into student values(2, 'zhangsan',1,22);

批量插入

insert into student values(3, 'zhangsan',1,22),(4, 'zhangsan',1,22),(5, 'zhangsan',1,22);

查看表中数据

select * from student;

解决表中插入数据中文乱码的问题
1、设置数据库的表字符编码为 jbk；
2、停止 MySQL 服务(net stop mysql)；
3、找到 MySQL 的安装路径，找到 my.ini 文件，把 default-character-set=utf8 修改为 default-character-set=gbk；
4、启动 MySQL 服务(net start mysql)，重新登录数据库。

删除记录

delete from 表名 [where 条件]

delete from student where sid=5;

把表中数据全部删除

delete from student;

问题：
delete 删除数据和 truncate 删除数据有什么差别？
delete:DML 一条一条删除表中的数据
truncate:DDL 先删除表再重建表
关于哪条执行效率高，具体要看表中的数据
如果数据比较少，delete 比较高效
如果数据比较多，truncate 比较高效

更新表记录

update 表名 set 列名=列的值，列名2=列名2的值 [where 条件]

如果参数是字符串、日期要加上单引号

update student set sname= '张三' where sid=5;

update student set sname= '李四' ,sex='1';

查询记录

select [distinct] [ * ] [列名,列名2] from 表名 [where 条件]

说明：
distinct：去除重复的数据
select：选择显示哪些列的内容

简单查询

查询所有

select * from 表名

查询姓名和性别

select sname,sex from student;

别名查询

as 的关键字，as 关键字可以省略

表别名

select s.sname,s.sex from student as s;(主要用在多表查询)

列别名

select sname as 姓名,age as 年龄 from student;

去掉重复的值

select age from student;

select distinct age from student;

说明：
select：运算查询：仅在查询结果上做了运算

select * ,age*2 from student;

select * ,age*2 as 两倍年龄 from student;

条件查询[where关键字]

查询年龄大于 20 的学生

select * from student where age > 20;

where 后的条件写法

说明：
关系运算符：> >= < <= != <>
<> 不等于标准SQL语法
!= 不等于非标准SQL语法

查询学生年龄不等于 20 的所有学生

select * from student where age <> 20;

select * from student where age != 20;

查询年龄在 20 到 30 之间

select * from student where age > 20 and age < 30;

使用 between… and… 查询

select * from student where age between 20 and 30;

逻辑运算：and , or , not

查询年龄小于 15 或者年龄大于 20

select * from student where age < 15 or age > 20;

模糊查询

like：

_ ：代表的是一个字符

% ：代表的是多个字符

查询姓名中带有张的所有学生 ‘%张%’

select * from student where sname like '%张%';

查询第二名字是三的所有学生 ‘_张%’

select * from student where sname like '_张%';

in：在某个范围获得值

查询年龄在 20，21，22 里面的所有学生

select * from student where age in (20,21,22);

排序查询

order by 关键字

asc :ascend 升序(默认的排序方式)

desc:descend 降序

查询所有学生的年龄，按照年龄进行排序

select * from student order by age;

查询所有学生的年龄，按照年龄进行降序排序

select * from student order by age desc;

查询学生姓名有“张”的学生，按年龄排序

查询学生姓名有“张”的所有学生

select * from student where sname like '%张%';

进行排序得出结果

select * from student where sname like '%张%' order by age asc;

复杂查询

聚合函数

sum(): 求和

avg(): 求平均值

count(): 统计数据

max(): 最大值

min(): 最小值

获得所有商品价格的总和

select sum(价格名称) from 表名;

select sum(price) from product;

获得所商品的平均价格

select avg(price) from product;

获得所有商品的个数

select count(*) from product;

注意：where 条件后面不能接聚合函数

查出商品价格大于平均的所有商品

查出所有商品

select from product;

大于平均价格

select avg(price) from product;

如平均价格为 100

select * from product where price > 100;

或者(子查询)

select * from product where price > (select avg(price) from product);

分组

group by

根据 cno 字段分组，分组后统计商品的个数

select cno,count(*) from product group by cno;

根据 cno 分组，分组统计每组商品的平均价格，并且商品平均价格 > 60

select cno,avg(price)from product group by cnohaving avg(price) > 60;

说明：
having 关键字可以接聚合函数出现在分组之后
where 关键字它是不可以接聚合函数出现在在分组之前

编写顺序

S…F…W…G…H…O

select..from..where..group by..having..order by

执行顺序

F…W…G…H…S…O

from..where..group by..having..select..order by

SQL 多表操作

需求：

分类表和商品表之间是不是有关系？如果有关系，在数据库中如何表示这种关系？

create table category(cid int primary key auto_increment,cname varchar(10),cdesc varchar(31));

insert into category values(null,'手机数码','电子产品,');insert into category values(null,'鞋靴箱包','江南皮鞋厂倾情打造');insert into category values(null,'香烟酒水','黄鹤楼,茅台,二锅头');insert into category values(null,'酸奶饼干','娃哈哈,蒙牛酸酸乳');insert into category values(null,'馋嘴零食','瓜子花生,八宝粥,辣条');

select * from category;

select cname,cdesc from category;

所有商品
1.商品ID
2.商品名称
3.商品的价格
4.生产日期
5.商品分类ID

商品和商品分类：所属关系

create table product(pid int primary key auto_increment,pname varchar(10),price double,pdate timestamp,cno int);

insert into product values(null,'小米mix4',998,null,1);insert into product values(null,'锤子',2888,null,1);insert into product values(null,'阿迪王',99,null,2);insert into product values(null,'老村长',88,null,3);insert into product values(null,'劲酒',35,null,3);insert into product values(null,'小熊饼干',1,null,4);insert into product values(null,'卫龙辣条',1,null,5);insert into product values(null,'旺旺大饼',1,null,5);

多表之间的关系如何维护

外键约束：foreign key

给 product 中的这个 cno 添加一个外键约束

alter table product add foreign key(cno) references category(cid);

删除的时候，先删除外键关联的所有数据，再才能删除分类的数据

建数据库原则

通常情况下，一个项目/应用建一个数据库

建表原则

一对一：常用于拆表操作。

建表原则：

1、将两张表合并成一张表。

2、将两张表的主键建立起关系。

3、将一对一的关系当作一对多的关系去处理。

一对多：

建表原则：在多的一方增加一个外键，指向一的一方。

多对多：

建表原则：将多对多转成一对多的关系，创建一张中间表。

主键约束：

1、默认就是不能为空，唯一。

2、外键都是指向另外一张表的主键。

3、主键一张表只能有一个。

唯一约束

列面的内容，必须是唯一，不能出现重复情况，为空；唯一约束不可以作为其它表的外键，可以有多个唯一约束。

一对一建表原则：合并一张表，将主键建立关系，将它当作一对多的情况来处理。

一对多建表原则：在多的一方添加一个外键,指向一的一方。

多对多建表原则：拆成一对多，创建一张中间表，至少要有两个外键，指向原来的表。

网上商城表实例（复杂查询）

用户购物流程(用户表、订单表、商品表、商品分类表)

用户表 (用户的ID,用户名,密码,手机)

create table user(uid int primary key auto_increment,username varchar(31),password varchar(31),phone varchar(11));

插入数据

insert into user values(1,'zhangsan','123','13811118888');

订单表 (订单编号,总价,订单时间 ,地址,外键用户的ID)

create table orders(oid int primary key auto_increment,sum int not null,otime timestamp,address varchar(100),uno int,foreign key(uno) references user(uid));

批量插入数据

insert into orders values(1,200,null,'广州',1);insert into orders values(2,250,null,'深圳',1);

商品表 (商品ID, 商品名称,商品价格,外键cno)

create table product(pid int primary key auto_increment,pname varchar(10),price double,cno int,foreign key(cno) references category(cid));

批量插入数据

insert into product values(null,'小米mix4',998,1);insert into product values(null,'锤子',2888,1);insert into product values(null,'阿迪王',99,2);insert into product values(null,'老村长',88,3);insert into product values(null,'劲酒',35,3);insert into product values(null,'小熊饼干',1,4);insert into product values(null,'卫龙辣条',1,5);insert into product values(null,'旺旺大饼',1,5);

订单项: 中间表(订单ID,商品ID,商品数量,订单项总价)

create table orderitem(ono int,pno int,foreign key(ono) references orders(oid),foreign key(pno) references product(pid),ocount int,subsum double);

给1号订单添加商品 200 块钱的商品

insert into orderitem values(1,7,100,100);insert into orderitem values(1,8,101,100);

给2号订单添加商品 250 块钱的商品

insert into orderitem values(2,5,1,35);insert into orderitem values(2,3,3,99);

商品分类表(分类ID,分类名称,分类描述)

create table category(cid int primary key auto_increment,cname varchar(15),cdesc varchar(100));

批量插入数据

insert into category values(null,'手机数码','电子产品,黑马生产');insert into category values(null,'鞋靴箱包','江南皮鞋厂倾情打造');insert into category values(null,'香烟酒水','黄鹤楼,茅台,二锅头');insert into category values(null,'酸奶饼干','娃哈哈,蒙牛酸酸乳');insert into category values(null,'馋嘴零食','瓜子花生,八宝粥,辣条');