Mysql
数据库概述
数据库：文件系统，通过命令SQL去操作这个文件系统
数据库的作用：储存数据，数据的仓库，带有访问权限，限制不同的人可以有不同的操作
mysql:开源免费，适用于中小型企业的免费数据库，SUN收购mysql，oracle收购SUN，java
mariadb: 由mysql创始人做出的mysql开源版本的分支
oracle: 甲骨文公司，商业收费软件，适用于大型电商网站。
db2: IBM公司，thinkpad 解决方案：软件和硬件，服务器架构，银行系统大多采用db2
sqlserver: windows里面，图形化工具比较好
sybase: 被淘汰的数据库
NOSQL非关系型数据库key:value
mongodb
redis
关系型数据库:
主要用来描述实体与实体之间的关系
实在的实物：员工与部门
E-R关系图：实体-联系图
实体：方框
属性：椭圆
关系：菱形
Mysql：数据库服务器
服务器：一台电脑，安装相关的服务器软件，这些软件会监听不同的端口，根据用户访问的不同端口，提供不同的服务
Mysql的安装与卸载：
卸载：控制面板，删除mysql安装目录下的所有文件，mysql数据存放文件,Program Data(隐藏文件)中的mysql
安装：服务器配置：Include bin directory to windows path 端口号：3306 密码：root
SQL:structure Query Language: 结构化查询语言
Mysql的SQL语句
DDL:数据定义语言：定义数据库，数据表的结构 create(创建);drop(删除);alter(修改)
DML:数据操纵语言，主要是用来操作数据 insert(插入);update(更新);delete(删除)
DCL:数据可控制语言，定义访问权限，定义访问权限，安全设置 grant
DQL:数据查询语言，select(查询);from(字句);where（字句）
数据库的CRUD的操作
登陆数据库服务器：mysql -uroot -proot -p后面接密码 -u后面接用户名
创建数据库：
create database 数据库的名字;
创建数据库时指定字符集和较对规则：
create database 数据库名字 character set 字符集（utf8,gbk);
create database 数据库名字 character set 字符集 collate 校对规则;
例：create database datatest character set utf8 collate uft8_bin;
查看数据库
查看数据库定义的语句：
show create database 数据库的名字
查看所有的数据库：show databases;
information_schema
mysql
performance_shcema
修改数据库的操作
修改数据的字符集;
alter database 数据库的名字 character set 字符集;
删除数据库
drop database 数据库名字;
其他数据库操作的命令
切换数据库
use 数据库名字;
查看当前正在使用的数据库
select database();
表的CRUD的操作
创建表
create database 数据库的名字
create table 表名(
列名 列的类型 约束
列名 列的类型 约束);
列的类型
java
int
char/String
double
float
boolean
date
sql
int
char/varchar(char:固定长度,varchar:可变长度,char(3),varchar(3):3个字符)
double
float
boolean
date:YYYY-MM-DD time:hh-mm-ss
text:存放文本
存放二进制
blob:datetime:YYYY-MM-DD hh-mm-ss(默认为空) timestamp:YYYY-MM-DD hh-mm-ss(默认使用当前时间)
列的约束
主键约束:primary key
唯一约束:unique
非空约束:not null
create table student( $id int primary key, $name varchar(31), sex int, age int );
查看表
查看所有的表格
show tables;
查看表的创建过程
show create table 表名;
查看表的结构
desc 表名;
修改表
添加列(add) alter table 表名 add 列名 列的类型 列的约束
例：alter table student add grade int not null
修改列(modify) alter table 表名 modify 列名 列的类型
例：alter table student modify sex varchar(2);
修改列名(change) alter table 表名 change 原列名 现列名 列的类型
例：alter table student change sex gender varchar(2);
删除列(drop) alter table 表名 drop 列名
例：alter table student drop gender;
修改表名(rename) rename table 原表名 to 现表名
修改表的字符集(alter) alter table 表名 character set 字符集
删除表
drop table 表名;
SQL完成对表中数据的CRUD的操作
插入数据
insert into 表名(列名，列名，列名) values(值，值，值)
例： insert into student($id,$name,sex,age) values(12,'skdjf',234,34);
简单写法：insert into student values(2,'wangwang',87,87);(必须是插入全部列，插入部分列不能省略)
批量插入：insert into 表名 values(值，值，值，...),(值，值，值，...),(值，值，值，...)...;
insert into 表名(列名，列名，列名) values(值，值，值，...),(值，值，值，...),(值，值，值，...)...;
例： insert into student values(7, 'ksdjf', 8, 9),(8, 'sdjf', 9, 10),(9, 'jdk',34, 234),(10, 'fjdk', 478, 89);
查看表中的数据：select * from student;
命令行输入中文乱码的问题
暂停mysql的服务
在mysql安装路径中找到my.ini配置文件
在57行的编码方式改为gbk
保存文件并退出
启动mysql服务
删除记录
delete from 表名 [where 条件]
delete from student：没有指定条件，删除表中全部的数据
例：delete 删除数据和truncate输出数据有什么差别
delete：DML 一条一条删除表中的数据
truncate：DDL 先删除表再重建表
执行效率：数据少delete高效，数据多truncate高效
更新表记录
update 表名 set 列名=列的值,列名=列的值 [where 条件]
例：update student set $name='xiaojingzi' where $id=5;
update 表名 set 列名=列的值 表中所有的数据全部被更新
查询记录
select [distinct] [*] [列名，列名] from 表名 [where 条件]
distinct:去除重复的数据
案例分析：
商品分类：1. 分类的ID，2. 分类的名称，3. 分类的描述
create table category( cid int primary key auto_increment, cname varchar(10), cdesc varchar(10) ); insert into category values(null, '手机数码','质量可靠'); insert into category values(null, '鞋靴箱包','一个字，贵'); insert into category values(null, '酸奶饼干','好吃又好喝'); insert into category values(null, '馋嘴零食','瓜子，花生，饼干'); select * from category; select canme,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, '锤子', 2998, null, 1); insert into product values(null, '阿迪王', 298, null, 2); insert into product values(null, '老村长', 28, null, 2); insert into product values(null, '金酒', 88, null, 3); insert into product values(null, '小熊饼干', 8, null, 3); insert into product values(null, '卫龙', 0.1, null, 4); insert into product values(null, '旺旺', 0.3, null, 4); --简单查询：select * from product; --查询商品名称和价格: select pname,price from product; --别名查询 as 可以省略 --表别名：select p.pname,p.price from product as p;(用在多表查询) select p.pname,p.price from product as p; --列别名: select pname as 商品名称.price as 商品价格 from product select pname as 商品名称,price as 商品价格 from product;(不需要引号) --查询商品所有的价格 select price from product; --去重：select distinct price from product; --select 运算查询：只是再查询结果上做运算 select *,price*1.5 as 打折价 from product; --条件查询[where关键字] --指定条件，确定要操作的记录 --查询商品价格大于60元的所有商品信息： select * from product where pirce > 60 --where 后的条件写法 --关键运算符: > >=, <, <=, =, !=, <> -- <>:不等于：标准的sql语法 -- !-:不等于:非标准sql语法 --逻辑运算：and, or, not; --查询商品的价格在100-10000之间的商品 select * from product where price > 10 and price < 10000; select * from product where price between 10 and 10000; --like:模糊查询 -- _:代表一个字符 -- %:代表多个字符 --查询名字中带有小字的所有商品 select * from product where pname like '%小%'; --查询第二个名字是米的所有商品 select * from product where pname like '_米%'; --in:在某个范围内获取值 --查询商品的分类ID在1，3里面的商品 select * from product where cno in (1, 3); --排序查询：order by 关键字 --asc:ascend 升序 --desc:descend 降序 --查询所有商品，按照价格进行排序 select * from product order by price desc; --查询带有小字的商品按照价格排序 select * from product where pname like '%小%' order by price asc; --聚合函数 -- sum():求和 -- avg():求均值 -- count():统计数量 -- max():最大值 -- min():最小值 -- 获得所有商品价格的总和 select sum(price) as 价格的总和 from product; --获得所有商品的均值 select avg(price) as 价格的均值 from product; --获得所有商品的个数 select count(*) as 商品的个数 from product; -- 注意：where后面不能接聚合函数 select * product where price>ave(price); --查询商品价格大于平均价格的商品 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 cno having 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创建多表以及多表之间的关系
外键约束：foreign key
例：alter table product add foreign key(cno) references category(cid);
delete from category where cid = 4;// 删除失败
首先得去product 删除cno为5的商品
建数据库的原则：通常情况下，一个项目或者应用建立一个数据库
多表之间的建表原则
一对多：商品和分类
建表原则：在多的一方添加一个外键，指向一的主键
多对多：老师和学生，学生和课程
建表原则：多建一张中间表，中间表至少要有两个外键，这两个外键分别指向原来的两张表
一对一：班级和班长，公民的身份证，国家和国旗
建表原则：方案一：将一对一的情况，当作是一对多的情况进行粗粒
方案二：直接将两个表合并成一张表
方案三：将两张表的主键建立连接，让两张表的主键相等
实际用途：用的不多（拆表操作）
网上商城案例分析：
1. 用户表(用户ID，密码，手机号)
create table user( uid int primary key auto_increment, username varchar(31), password varchar(31), phone varchar(31) ); insert into user values(1, 'zhangsan', '123', '13547855478');
2. 订单表(订单编号，总价，订单时间，地址，外键用户ID)
create table orders( oid int primary key auto_increment, sum int, otime timestamp, address varchar(100), uno int, foreign key(uno) references user(uid) ); insert into orders values(1, 200, null, 'sichuanUniversity', 1); insert into orders values(2, 200, null, 'hahaUniversity', 1);
3. 商品表（商品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, '锤子', 2998, 1); insert into product values(null, '阿迪王', 298, 2); insert into product values(null, '老村长', 28, 2); insert into product values(null, '金酒', 88, 3); insert into product values(null, '小熊饼干', 8, 3); insert into product values(null, '卫龙', 0.1, 4); insert into product values(null, '旺旺', 0.3, 4);
4. 订单项：中间表（订单ID，商品ID，商品数量，订单项总价）
create table oderitem( ono int, pno int, foreign key(ono) references orders(oid), foreign key(pno) references product(pid), ocount int, subsum double ); --给一号订单添加商品 insert into oderitem values(1, 9, 100, 99800); insert into oderitem values(1, 10, 100, 299800); --给二号订单添加商品 insert into oderitem values(2, 11, 3, 900); insert into oderitem values(2, 12, 3, 90);
5. 商品分类表（分类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, '馋嘴零食','瓜子，花生，饼干');
多表之间的查询
-- 交叉连接查询 笛卡儿积 select * from product select * from category; -- 笛卡尔积 select * from product,category; -- 查询的结果没有意义 -- 过滤出有意义的数据 select * from product as p,category as c where p.cno = c.cid; --内连接查询 --隐式内连接查询：先查询后过滤 select * from product p, category c where p.cno = c.cid; --显式内连接查询：带着条件去查询，效率更高 select * from product p inner join category c on p.cno = c.cid; 两种方法的查询结果一致 --外连接 --左外连接：将左表中的所有数据都查询出来，如果右表中没有对应数据，就用Null代替 select * from product p left outer join category c on p.cno = c.cid; --右外连接：将右表中的所有数据都查询出来，如果左表中没有对应数据，就用Null代替 select * from product p right outer join category c on p.cno = c.cid; --分页查询 --每页数据为10，起始索引为0，第一页0，第二页5,从0开始，只会显示5条数据 select * from product limit 0, 5; --子查询： --查询出分类名称为手机数码的所有商品 select * from product where cno = (select cid from category where cname = '手机数码'); --查询出（商品名称，商品分类名称）信息 --左连接 select p.pname,c.cname from product as p left outer join category c on p.cno = c.cid; --子查询 select pname , (select cname from category where cno = cid) as 商品分类名称 from product;
