1.mysql基本操作
1.1 启动数据库
启动时可以在cmd命令中可以直接采用:
net start mysql
1.2 连接数据库
命令行输入:
mysql -uroot -p******
其中,root为用户名,******为密码;
1.3 注释
mysql中的注释采用在语句后标注"--*"或者"#**"的方式执行,例如:
alter table user change password pwd varchar(20);--改变user表格中的password列名为pwd,字符允许长度不得超过20;
2. 数据定义语言(DDL)
数据定义语言操作对象是:数据库和表,关键词是:create,alter, drop
2.1 数据库常用命令格式
创建:
create database 数据库名称;
删除:
drop database 数据库名称;
查看所有数据库:
show databases;
切换或者进入数据库:
use 数据库名称;
2.2 常用表格操作命令
创建表格:
create table 表名(字段描述,字段描述);
字段描述:
字段名称 字段类型 [约束]
例如:
create table user(
id int primary key auto_increment,
username varchar(20)
);
查看当前数据库下的所有表:
show tables;
查看表结构:
desc 表名;
查看建表语句:
show create table 表名;
修改表格:
alter table 表名......
修改表名:
alter table 旧表名 rename to 新表名;
添加字段:
alter table 表名 add [column] 字段描述;
如:alter table 表名 add password varchar(20);
修改字段名:
alter table 表名 change 字段名称 新字段描述;
例如:
alter table user change password pwd varchar(20);
修改字段描述:
alter table 表名 modify 字段名称 字段类型 [约束];
例如:
alter table user modify pwd int;
删除字段
alter table 表名 drop 字段名;
例如:
alter table user drop pwd;
删除表格:
drop table 表名;
3. DML(数据操作语言)
操作对象:记录(行);
关键词:insert update delete
3.1 插入全部数据
格式:
insert into 表名 values(字段值1,字段值2,...,字段值n);
注意:
默认插入全部字段,必须保证values后面的内容的类型和顺序与表格结构中的完全一致,不允许存在漏项,错项等;
若字段类型为数字,可以省略引号,但是,建议还是全部用单引号进行引用;
例如:
insert into user values(1,'tom');
3.2插入指定列的数据
格式:
insert into 表名(字段名,字段名,...,字段名) values(字段值,字段值,..,字段值);
注意:
插入的字段必须保证与values前面的内容的类型和顺序与表格结构中的完全一致,不允许存在漏项,错项等;
3.3 修改
格式:
update 表名 set 字段名=字段值,字段名1=字段值1,...[where 条件];
例如:
update user set username = 'jerry' whre username = 'jack';
3.4 删除
格式:
delete from 表名 [where 条件];
例如:
ddelete from user where id='2';
3.5 查询语句及案例分析
CREATE TABLE products(
pid INT PRIMARY KEY AUTO_INCREMENT,
pname VARCHAR(20),
price DOUBLE,
pnum INT,
cno INT,
pdate TIMESTAMP
);
INSERT INTO products VALUES(NULL,'泰国榴莲',98,12,1,NULL);
INSERT INTO products VALUES(NULL,'新疆大枣',38,123,1,NULL);
INSERT INTO products VALUES(NULL,'新疆切糕',68,50,2,NULL);
INSERT INTO products VALUES(NULL,'十三香',10,200,3,NULL);
INSERT INTO products VALUES(NULL,'老干妈',38,123,1,NULL);
INSERT INTO products VALUES(NULL,'豌豆黄',38,123,1,NULL);
3.5.1查询所有商品
SELECT * FROM products;
3.5.2查询商品名和商品价格
SELECT pname,price FROM products;
3.5.3查询商品都有哪些价格
采用distinct可以去除重复的数据,格式为:
SELECT price FROM products;
SELECT DISTINCT price FROM products;
3.5.4.将所有价格+10显示
如果直接采用第一条查询语句,那么price列的列名就会变成price+10,因此需要对列起别名的方式;
格式为:
select price+10 from products;
给列起别名 格式:字段名 [AS] 别名
SELECT price+10 '新价格' FROM products;
3.5.5.查询商品名曾为“十三香”的商品所有信息;
SELECT * FROM products WHERE pname = '十三香';
3.5.6.查询商品价格>60的所有商品信息;
SELECT * FROM products WHERE price>60;
3.5.7.查询商品名称中包含"新"的商品
该种查询采用模糊匹配方式,格式为:字段名 like "匹配原则",其中匹配内容格式如下:
“*” 值为*
“%*” 值以*结尾
“*%” 值以*开头
“%*%”值中包含*
--匹配个数 “————”表示占有两个位置
SELECT * FROM products WHERE pname LIKE "%新%";
3.5.8.查询价格为38,68,98的商品
SELECT * FROM products WHERE price IN(38,68,98);
3.5.9.查询价格在38到98之间的商品,并且包含该数据
SELECT * FROM products WHERE price BETWEEN 38 AND 98;
3.5.10 排序查询
查询名称带有“新”的商品的信息并且按照价格降序排序(asc:升序,desc降序):
select * from products where pname like '%新%' order by price desc;
3.5.11 聚合函数
对一列进行计算,返回值只有一个,计算时会忽略null值;
获得所有商品的价格总和:sum()
select sum(price) from products;
获得商品表中价格的平均数avg()
select avg(price) from products;
当需要制定保留多少位小数时,需要使用round命令:
select round(avg(price),2) from products;
获取商品表中有多少条记录:
select count(*) from products;
3.5.12 分组
使用group by实现分组功能。
根据cno字段分组,分组后,统计商品的个数:
select cno,count(*) from products group by cno;
根据cno分组,分组统计每组商品的总数量,并且总数量>200:
select cno,sum(pnum) from products group by cno;
select cno,sum(pnum) from products group by cno having sum(pnum)>200;
注意:where和having之间的区别:
1)where是对分组前的数据进行过滤,having是对分组后的数据进行过滤;
2)where后面不能使用聚合函数,但是having可以。
4.多表
4.1 外键(一对多)
在开发中,我们将一对多的关系,一方称之为主表或者一表,多方称之为多表或者从表,为了表示一对多的关系,一般会在多表添加一个字段,字段名称自定义(建议:主表名称_id)。字段类型一般和主表的主键的类型保持一致,我们称这个字段为外键。
为了保证数据的有效性和完整性,在多表的一方添加外键约束,格式如下:
alter table 多表名称 add foreign key(外键名称) references 一表名称(主键);
如用户和订单的关系中,描述如下:
alter table orders add foreign key(user_id) references user(id);
添加了外键约束之后有如下的特点:
1)主表中不能删除从表中已经引用的数据;
2)从表中不能添加主表中不存的数据;
4.2 多对多数据库
多对多,在开发中一般引入一张中间表,在中间表中村昂两张表的主键,一般还会降这俩呢主键设置层中间表哥的联合主键。
为了保证数据的有效性和完整性,在中间表上添加两个外键即可。
例如商品与订单的关系:
商品表:
create table product(
id int primary key auto_increment,
name varchar(20),
price double
);
订单表:
create table orders(
id int primary key auto_increment,
price double
);
中间表:
create table orderitem(
oid int,
pid int
);
添加外键约束:
alter table orderitem add foreign key(oid) references orders(id);
alter table orderitem add foreign key(pid) references product(id);
5. 多表查询
通过下面的案例对多表查询进行描述:
--创建数据库
create database multitable;
use multitable;
--用户表(user)
create table user(
id int auto_increment primary key,
username varchar(50)
);
--订单表(orders)
create table orders(
id int auto_increment primary key,
price double,
user_id int
);
--给订单添加外键约束
alter table orders add constraint user_fk foreign key (user_id) references user(id);
--向user表中添加数据
insert into user values(3,'张三');
insert into user values(4,'李四');
insert into user values(5,王五'');
insert into user values(6,赵六'');
--向orders表中插入数据
insert into orders values(1,1314,3);
insert into orders values(2,1314,3);
insert into orders values(3,15,4);
insert into orders values(4,315,5);
insert into orders values(5,1014,null);
练习题目如下:
1.查询用户订单,没有订单的用户不显示;
2.查询所有用户的订单详情;
3.查询所有订单的用户详情。
5.1 笛卡尔积
多张表无条件的联合查询,没有任何特殊含义。格式为:
select a.*, b.* from a,b;
5.2 内连接
内连接共有两种形式,其中显示的内连接最为常用。
显示的内连接:
select a.*,b.* from a [inner] join b on ab的连接条件;
隐藏的内连接:
select a.*,b.* from a,b where ab的连接条件;
因此,对于查询用户订单,没有订单的用户不显示,语句为:
--隐藏的内连接
select user.*, orders.* from user, orders where user.id=orders.user_id;
--显示的内连接
select user.*,orders.* from user join orders on user.id=orders.user_id;
5.3 外连接
外连接分为左外连接和右外连接,其中左外连接:
select a.*,b.* from a left [outer] join b on 连接条件;
--含义为:
--先展示join左侧的(a)表的所有数据,根据条件关联查询join右侧的表(b),符合条件则展示出来,不符合则以null展示。
右外连接:
select a.*,b.* from b right [outer] join a on 连接条件;
--含义为:
--先展示join右侧的(a)表的所有数据,根据条件关联查询join左侧的表(b),符合条件则展示出来,不符合则以null展示。
因此,"查询所有用户的订单详情"采用左外连接的查询语句为:
select user.*,orders.* from user left join orders on user.id=orders.user_id;
因此,“查询所有订单的用户详情”采用右外连接的查询语句为:
select orders.*,user.* from user right join orders on user.id=orders.user_id;
5.4 子查询
一个查询语句依赖于另一个查询语句的结果。以案例的形式进行说明。
问题一:查询用户为张三的订单详情
不采用子查询,方式为:
1.先查询张三的id
select id from user where username='张三';
2.select * from orders where user_id = 3;
采用子查询方式为:
select * from orders where user_id=(select id from user where username='张三');
问题二:查询出订单的价格大于300的所有用户信息
不采用子查询:
1.先查询订单价格>300的用户的id
select user_id from orders where price>300;//(3,3,5,null)
2.select * from user where id in (3,3,5,null);
采用子查询:
select * from user where id in(select user_id from orders where price>300);
问题三:查询订单价格大于300的订单信息及相关用户的信息;
内连接方式:
select orders.*,user.* from orders,user where user.id=orders.user_id and orders.price>300;
子查询方式:
select user.*,tmp.* from user,(select * from orders where price>300) as tmp where user.id=tmp.user_id;