|
#创建数据库
create database if not exists test01
#删除数据库
drop database if exists test01
#查看所有数据库
show databases
#使用
use test
#创建数据库
create database if not exists demo1
#删除表
drop table student
#创建表
create table if not exists demo(
uid VARCHAR(20) not null COMMENT ‘用户id’,
logincode VARCHAR(20) not null COMMENT ‘登录账号’,
password VARCHAR(20) not null COMMENT ‘登录密码’,
email VARCHAR(10) not null COMMENT ‘邮箱’
)
#查看库定义
show create DATABASE demo1
#查看表定义
show create table demo
#查看表结构
DESC demo
查看mysql所支持的引擎类型
show ENGINES
#修改字段
alter table demo CHANGE email sex VARCHAR(5) COMMENT ‘性别’
#修改表名
alter table demo RENAME AS newdemo
#添加字段
alter table demo add mobile VARCHAR(50) not null COMMENT ‘电话’
#查看表结构
desc newdemo
#删除字段
alter table newdemo drop sex
#删除数据表
drop table newdemo
#适用 MyISAM : 节约空间及相应速度
#适用 InnoDB : 安全性 , 事务处理及多用户操作数据表
alter table newdemo rename as demo
desc demo
#添加数据
insert into demo (uid,logincode,password)values(‘123456’,‘12346’,‘101010’)
select * from demo
#更新数据
update demo set uid = ‘987654321’ where logincode = ‘12346’
#删除数据
delete from demo where uid = ‘987654321’
insert into demo (uid,logincode,password)VALUES(‘101010’,‘74128’,‘101010’)
update demo set uid = ‘qweqwq’ where logincode = ‘74128’
delete from demo where password = ‘101010’
create TABLE test(
uid VARCHAR(20) not null COMMENT’id’,
username varchar(20) not null ,
phone VARCHAR(20) not null,
qqid VARCHAR(20) not null
)
insert into demo (uid,logincode,password)VALUES(‘10001’,‘15592547688’,‘789456’),(‘10002’,‘2018390448’,‘123654’)
insert into test(uid,username,phone,qqid)VALUES(‘10001’,‘小王’,‘0917qwqwq’,‘101’),(‘10002’,‘小李’,‘asas0922’,‘plm’)
select * from test
select DISTINCT * from demo
select * from test where uid = ‘10001’;
#like 模糊查询 _ 一个字符 %多个字符
select * from test where username like ‘小%’
#in
select * from test where uid in (‘10002’,‘10001’)
#null 查询id 没有填写的 同学
select * from demo where uid is null
select * from demo where uid is not null
#连接查询
#根据 uid 查询两张表用户数据
#等值连接
select d.uid ,d.logincode,d.password,t.username,t.phone,t.qqid
from demo d , test t
where d.uid = t.uid
#左连接
select d.uid ,d.logincode,d.password,t.username,t.phone,t.qqid
from demo d
left join test t
ON d.uid = t.uid
select d.uid , d.logincode,t.username,t.phone,t.qqid
from demo d
left join test t
on d.uid = t.uid
where d.uid = ‘10001’
select t.username , t.phone , t.qqid , d.uid , d.logincode
from test t
left join demo d
on t.uid = d.uid
where t.qqid=’’
select username from test where qqid is null
select * from test
select * from demo
select username as 姓名,logincode as 登录账号,phone as 手机号
from test,demo
where test.uid = demo.uid
select username logincode,phone,qqid
from demo
RIGHT JOIN test
on demo.uid = test.uid
where test.qqid = ‘’
select login_code , user_name , mobile , sex
from js_sys_user
left join qyc_shop_order
on js_sys_user.user_code = qyc_shop_order.uid
select id as 订单号, true_name as 真实姓名,user_phone as 手机号,user_address,order_data
from qyc_shop_order
LEFT JOIN js_sys_user
on qyc_shop_order.uid = js_sys_user.user_code
#查询 所有用户的所有订单
select id as 订单号, true_name as 真实姓名,user_phone as 手机号,user_address,order_data,uid ,user_code
from qyc_shop_order ,js_sys_user
where qyc_shop_order.uid = js_sys_user.user_code
#查询 用户id 1427924504375508992 的 member 表 数据
select *
from qyc_base_member
LEFT JOIN js_sys_user
ON qyc_base_member.user_code = js_sys_user.user_code
where js_sys_user.user_code = ‘1427924504375508992’
#查询该订单用户信息
select *
from qyc_base_member
left join qyc_shop_order
on qyc_shop_order.uid = qyc_base_member.user_code
where qyc_shop_order.id = ‘1452541689412759552’
用户id 查询该用户订单,并通过支付金额降序
select *
from qyc_shop_order
left join js_sys_user
on qyc_shop_order.uid = js_sys_user.user_code
where js_sys_user.user_code = ‘1427924504375508992’
ORDER BY qyc_shop_order.pay_price DESC
#查询那个商品的排序比Redis商品高 子查询
#1、先查询redis商品的排序
select sort
from qyc_shop_product
where name = ‘Redis’
#2、查询所有商品的排序，满足 1
select *
from qyc_shop_product
where sort > (
select sort
from qyc_shop_product
where name = ‘Redis’
)
ORDER BY qyc_shop_product.sort desc
#查询那些商品比Redis商品的价格低，价格降序排列
#1 查询redis的价格
select name,price
from qyc_shop_product_attr_value
left join qyc_shop_product
ON qyc_shop_product_attr_value.pid = qyc_shop_product.id
where qyc_shop_product.id = ‘1428186874920550400’
#2
select name , price
from qyc_shop_product_attr_value
left join qyc_shop_product
on qyc_shop_product_attr_value.pid = qyc_shop_product.id
where price > (
select price
from qyc_shop_product_attr_value
left join qyc_shop_product
ON qyc_shop_product_attr_value.pid = qyc_shop_product.id
where qyc_shop_product.id = ‘1434806746621358080’
)
ORDER BY qyc_shop_product_attr_value.price aSC
select CURRENT_DATE();
SELECT YEAR(NOW());
#查询所有商品价格的平均值、总和
select MAX(price) , AVG(price),MIN(price),SUM(price)
from qyc_shop_product_attr_value
left join qyc_shop_product
ON qyc_shop_product_attr_value.pid = qyc_shop_product.id
select * from test
UPDATE test set phone = MD5(phone) where uid = ‘10001’
insert into test(uid,username,phone,qqid)values(‘1003’,‘安咯am’,‘asdad111111’,‘123123123’)
desc demo
desc test
|