MySql数据库
Mysql的sql语句
SQL :Stucture Query Language 结构化查询语言
SQL分类
- DDL:数据定义语言 :定义数据库 数据表他们的结构 create(创建) drop(删除) alter（修改）
- DML:数据操纵语言:主要是用来操作数据 insert(插入) update(修改) delete(删除)
- DCL:数据控制语言:定义访问权限，取消访问权限，安全设置 grant
- DQL:数据查询语言:select(查询) from字句 where字句
首先登陆MySql
登陆MySql：mysql -uroot -proot
创建数据库有下面3种方式
create database 数据库的名字
create database demo;创建数据库的时候制定字符集
create database 数据库名字 character set 字符集;
create database demo character set utf8;创建数据库的时候制定字符集和校对规则
create database 数据库名字 character set 字符集 collate 校对规则;
create database demo character set utf8 collate utf8_bin;查看数据库
查看数据库定义的语句
show create database 数据库名字;
show create database demo;查看所有数据库
show databases;修改数据库的操作
修改数据库的字符集
alter database 数据库的名字 character set 字符集;
alter database demo character set gbk;删除数据库
drop database 数据库名字;
drop database demo;其他数据库操作命令
切换数据库（选中数据库）
use 数据库名字;
use demo;查看当前正在使用的数据库
select database();数据库中表的创建
列的类型
java sql
int int
char/string char/varchar
char 固定长度
varchar 可变长度
char(3) 1空格空格 空格补满
varchar(3) 1 不用补满 比较省空间
double double
float float
boolean boolean
date date YYYY-MM-DD
time hh:mm:ss
datetime YYYY-MM-DD hh:mm:ss 默认是null
timestamp YYYY-MM-DD hh:mm:ss 默认是当前时间
text 主要用来存放文本
blob 存放二进制列的约束
主键约束 primary key
唯一约束 unique
非空约束 not null创建表
create table 表的名字(
列名 列的类型(可以指定长度) 约束，
列名2 列的类型 约束) ；
1分析实体 学生
2 学生ID
3 姓名
4 性别
5 年龄
create table student(
sid int primary key,
sname varchar(31),
sex int,
age int
);查看表
查看所有的表
show tables;查看表的定义
show create table 表名;
show create table student;查看表的结构
desc 表名；
desc student;修改表
添加列(add)
alter table 表名 add 列名 列的类型 列的约束;
alter table student add chengji 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 chengji;
修改表名(rename) 通常不会动的
rename table 旧表名 to 新表名;
rename table student to person;
修改表的字符集 通常不会动的
alter table 表名 character set 字符集；
alter table person character set gbk;删除表
drop table 表名;
drop table person;
SQL完成对表中数据的CRUD的操作
插入数据
insert into 表名(列名1，列名2，列名3) values (值1，值2，值3) ;
insert into person(sid,sname,age)values(1,'zhangsan',3);
-------简单写法---- 必须是全列名 并且是按照顺序插入
insert into person values(2,'lisi',11);
-------批量插入-----
insert into person values(3,'haha',12 ),(5,'haha',16 );
---单条插入效率不如批量插入的效率高喔 但是批量插入一条失败导致全部失败----命令行下插入中文问题 insert into person values(11，'啦啦'，12)；
- 临时解决方案:set name gbk; 相当于告诉mysql服务器软件,我们当前在命令行下输入的内容是GBk编码 当命令窗后关闭后,它再输入中文依旧存在问题
- 永久解决方法:修改my.ini 配置(在mysql软件安装路径里)
1 暂停mysql服务
2 在mysql安装路径中找到my.ini配置文件
3 将 my.ini配置文件中的编码修改成gbk
4 保存文件退出
5 启动mysql服务删除数据
delete from 表名 (where 条件);
delete from person where sid =10;
delete from person; 如果没有指定条件将会将表中的数据全部删除
---面试问题 请说一下 delete 删除数据和 truncate 删除数据有什么区别
delete ：DML 一条一条删除表中的数据
truncate:DDL 先删除表再重新建表
关于哪条sql执行效率高,具体看表中的数据量
如果数据比较少,delete 效率比较高
如果数据比较多,truncate效率比较高更新数据
update 表名 set 列名=列值,列名2=列值2 (where 条件);
---将sid 为5 的名字改为李四---
---如果参数是字符串,或者日期要加上单引号---
update person set sname='李四' where sid =5;
--更新多列数据--
update person set sname='李四',sex=0 where sid =5;
-- 更新数据不加where字句 表中的所有字段都会修改--
update person set sname='李四',sex=0；查询数据
--[] 代表可选参数--
select [distinct] [*] [列名,列名2] from 表名 [where 条件]；
distinct:去除重复的数据
select: 选择显示那些列的内容具体查询的实例
===商品分类 手机数码,鞋靴箱包
1 分类ID
2 分类名称
3 分类描述
-- auto_increment 自增长属性
create table category(
cid int primary key auto_increment,
cname varchar(10),
cdesc varchar(35)
);
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）
---商品和商品分类:所属关系
-- cno 类型对应商品分类的cid
create table product(
pid int primary key auto_increment,
pname varchar(20),
price double,
pdate timestamp,
cno int
);
insert into product values (null,'小米',999,null,1)；
insert into product values (null,'锤子',1299,null,1)；
insert into product values (null,'阿迪达斯',1299,null,2)；
insert into product values (null,'老村长',19,null,3)；
insert into product values (null,'小熊饼干',1,null,4)；
-- 查询所有的商品
select * from product;
---查询商品名称和商品价格
select pname,price from product;
---别名查询 as 关键字 ,as 关键字是可以省略的
-- 表别名 ： select p.pname,p.price from product 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 pname 商品名称,price 商品价格 from product; // as 可以省略
--- 去掉重复的值 distinct
-- 查询商品的所有价格
select price from product;
select distinct price from product;
---select 运算查询 仅仅在查询结果上做运算 数据库中的保存的值不变
select * ,price*1.5 from product;
select * ,price*0.89 as 折后价 from product;
----条件查询 [where 字句] 指定条件 确定要操作的数据列
select * from product where price>60;
---where 后的条件写法
---关系运算符 > >= < <= = != <>
<>:不等于 标准sql语法
！=：不等于 非标准sql语法
---查询商品价格不等于88的所有商品
select * from product where price <> 88;
--- 查询商品价格在10-100之间的
select * from product where price>10 and price<100;
---between...and... 什么之间
select * from product where price between 10 and 100 ;
----逻辑运算 and , or ,not
---查询商品价格小于100或者商品价格大于900
select * from product where price<30 or price>900;
---like :模糊查询
_ : 代表的是一个字符
% ：代表多个字符
---查询出名字中带有饼的所有商品 %饼%
select * from product where pname like '%饼%';
---查询第二个字是熊的所有商品'_熊'
select * from product where pname like '_熊%'；
---in 在某个范围中获得值---
---查询出商品分类id 在1 ，4 里面的所有商品
select * from product where cno in(1,4);
**排序查询** ：order by 关键字
asc:ascend 升序 (默认的排序方式)
desc:descend 降序
---0 查询所有的商品 按照价格排序
select * from product order by price；
---1 查询所有的商品按照价格进行降序排序(asc ==升序 desc ==降序)
select * from product order by price desc;
---2 查询所有名称有小的商品按照价格降序排序
1查询名称有小的商品
select * from product where pnem like '%小%';
2进行排序得出结果 order by desc;
select * from product where pnem like '%小%' order by desc;查询中的聚合函数
sum() 求和
avg() 求平均值
count（） 统计数量
max（） 最大值
min（） 最小值
---1 获得所有商品的价格的总和
select sum(price) from product
---2获得所有商品的平均价格
select avg(price) from product;
---3 获得所有的商品的个数
select count(*) from product;
---4 查出商品价格大于平均价格的商品
select * from product where price >(select avg(price) from product);
---注意 where 条件后面不能接聚合函数查询中的分组查询 group by
1 根据cno字段分组 分组后统计商品的个数
select cno,count(*) from product group by cno;
2 根据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
- 创建数据库的原则
通常情况下 一个项目对应一个数据库MySql中多表操作
技术分析
- 多表之间的关系怎么维护
外键约束:foreign key
给product 中的这个cno 添加一个外键约束
alter table product add foreign key(cno) references category(cid);- 多表之间的建表原则
- 一对多 ：商品和分类
- 建表原则:在多的一方添加一个外键，指向一的一方的主键
- 多对多 ：老师和学生，学生和课程
- 建表原则:多建一张中间表将多对多的关系拆成一对多的关系,中间表至少有两个外键，这两个外键分别指向原来的那两张表
- 一对一：班级和班长，公民和身份证，国家和国旗
- 建表原则:
- 将一对一的情况当做是一对多的情况处理，在任意一张表中添加一个外键，并且这个外键要唯一，指向另一张表
- 直接将两张表合并成一张表
- 将两张表的主键建立连接，让两张表里面主键相等
- 实际用途:用的不是很多(拆表操作)
- 相亲网站
- 个人信息： 姓名，性别，年龄，身高，体重，兴趣爱好，（年收入，特长，学历，职业等)
- 拆表操作：将个人信息的常用信息和不常用信息拆开，减少表的臃肿,
网上商城表实例分析 ：用户购物流程
- 用户表（用户的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','13651141556');- 订单表(订单编号,总价,订单时间，地址，外键用户的id)
create teble orders(
oid int primary key auto_increment,
sum double not null,
otime timestamp,
address varchar(100),
uno int ,
foreign(uno) key reference user(uid) // 将uno 字段指向user表中的uid
);
insert into orders values(1,200,null,'放回去老家',1);
insert into orders values(2,300,null,'放回去老家11',1);- 商品表(商品ID，商品名称，商品价格，外键cno)
create table product(
pid int primary key auto_increment,
pname varchar(10),
price double,
cno int,
foregin key(cno) reference category(cid)
);
insert into product values(null,'小米手机',999,1);
insert into product values(null,'锤子手机',1999,1);
insert into product values(null,'阿迪达斯',999,2);
insert into product values(null,'老村长',9,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,'雪饼',215,5);- 订单项：中间表（订单id，商品id，商品数量，订单项总价）
create table orderitem(
ono int,
pno int,
foregin key(ono) reference oders(oid),
foregin key(pno) reference product(pid),
ocount int,
subsum double
);
---给1号订单添加商品200元的商品
insert into orderitem values(1,7,100,100);
insert into orderitem values(1,8,100,100);
---给2号订单添加商品 250块钱的商品
insert into orderitem values(2,5,1,35);
insert into orderitem values(2,8,1,215);- 商品分类表（分类id ，分类名称，分类描述）
create table category(
cid int primary key auto_increment,
cname varchar(20),
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,'馋嘴零食','瓜子花生，八宝粥');多表查询
- 交叉连接查询 笛卡尔积
- 隐式内连接
select *from product,category;
交叉查询查出来的是两张表的成绩查出来的结果没有意义
--过滤有意义的数据
select * from product ,category where cno=cid;
-- 为了更明确可以用as 写别名
select * from product as p,category as c where p.cno=c.cid;
--as 关键字可以省略
select * from product p,category c where p.cno=c.cid;- 显示内连接
select * from product p, inner join category c on p.cno = c.cid;隐式内连接：在查询出结果的基础上去做的where条件过滤
显示内连接：带着条件去查询结果，执行效率高
- 左外连接
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;
会将右表所有数据全出查询出来，如果左表没有对应数据全部用null填充- 内连接查询出来的是交集
分页查询
关键字 limit
- 每页数据10条
- 起始索引从0开始
- 第一页起始索引0
- 第二页起始索引10
起始索引用算法去做：index：代表显示第几页 页数从1开始但是表里面的数据是用0开始的
每页显示3条数据
startindex = （index-1）*3;
select * from product limit 0,10;子查询
- 查询分类名称为手机数码的所有商品
- 查询出分类名称为手机数码的id
select cid from category where cname='手机数码'; //找到cid=1
- 得出ID为1的结果
select * from product where cno=1;
- 改进为子查询
select * from product where cno =(select cid from category where cname='手机数码');- 查询出（商品名称，商品分类名称）信息
--左连接实现
select p.pname,c.cname from product p left outer join category c on cno=cid;
--子查询操作
select pname,cno from product;
select pname,(select cname from category c where p.cno=c.cid) as 商品分类名称 from product p;本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
