【mysql语句】

基本sql语句

# 连接本地数据库：
mysql -h localhost -P 3306 -uroot -pwxf

show databases;
show tables;
desc tblUser; #查看表结构
show create table tblUser;#查看表的相信信息，包括comment

# 删除索引
alter table table_name drop index `uk_name`;

# 添加索引
alter table table_name add unique key `new_uk_name` (`col1`,`col2`);

修改表主键自增从10000开始
alter table tblStudent AUTO_INCREMENT=10000;


# 批量插入用户
insert into tblUser (uid,age,user_name,py_name) values 
(1111,21,"1019测试1","1019ceshi1"),
(222,21,"李先生","lixiansheng");

-- 批量更新----
update tblOrderInfo set name= REPLACE(name,'白象','方便面') where name like "白象"; -- 批量替换

#-----添加字段-----
alter table tblUser ADD sale_uid bigint(20) unsigned NOT NULL Default 0 COMMENT '销售uid';

# -- 删除数据---
delete from tblWxf where uid=2135385251; 

#-----修改字段-----
alter table tblArticle modify source VARCHAR(10) NOT NULL DEFAULT '' COMMENT '测试修改' 
#-----修改字段的类型---
alter table tblArticle change source_old source_new VARCHAR(10) NOT NULL DEFAULT '' COMMENT '测试修改' #修改字段的名称，此时需要重新指定该字段的类型

#-----删除字段-----
alter table tblUser drop grade_id,drop phone

mysql必知必会笔记

-- 1、注释方法：# 、--、/**/
#  方法1
-- 方法2
/* 方法3 */

-- 2、select、count
select uid tblUser;
select * tblUser;
select count(*) tblUser;

-- 3、distinct
select distinct uid tblUser;
select distinct uid,user_name tblUser;

-- 4、order by 、desc、asc（注意order by 要在where之后）
select * from tblUser order by uid limit 1 offset 2				-- 从第2行之后的检索3行出来
select * from tblUser order by uid limit 2,1; 					-- 与上一行等价，可省略关键字offset
select * from tblUser order by grade_id asc,id desc;			-- 按照多字段排序，正或反排序
select * from tblUser order by 2,3;								-- 按照列的相对位置排序，即uid和user_name

-- 5、where、and、or、in、not、limit、offset、\G
select * from tblUser where id<50 and grade_id>0;			-- 复合条件检索，使用and连接即可
select * from tblUser where id<30 or grade_id>=7;			-- or
select * from tblUser where id in (122,23,32,567);			-- in
select * from tblUser where not id=1;									-- not
select * from tblUser where (id>30 or user_name='wuwuwu') and grade_id>=7;-- 组合使用时注意符号的优先级
select * from tblUser where grade_id between 5 and 7;					-- 检索grade_id在5和7之前的值，包括5和7
select * from tblUser where grade_id>0 order by uid limit 1 offset 2;	-- 从第2行之后的检索3行出来
select * from tblUser where grade_id>0 order by updated_at desc limit 1 \G;	-- \G 横着显示信息，可展示
/*------------------------where字句操作符---------------------------------------------------------------
操作符   =    <>     !=	    <      <=      !<       >      >=	    !>     DETWEEN            IS NULL
说明   等于	不等于  不等于  小于  小于等于  不小于   大于   大于等于  不大于  在指定的两个值之间   为NULL值
------------------------------------------------------------------------------------------------------*/

-- 6、通配符：like、%、_、[] （通配符查询时一般比较耗时）
select * from tblUser where user_name like '吴%';		-- %通配符
select * from tblUser where user_name like '%小芳%';
select * from tblUser where user_name like '吴%01';
select * from tblUser where user_name like '吴_01';	 -- _
select * from tblUser where py_name like '[wu]%';		-- []，不太好用
/*------------------------通配符说明----------------------------------
通配符		   说明
%			表示任何字符出现任意次数
_			只匹配单个字符
[]			用来指定一个子集，它必须匹配指定位置（通配符的位置）的一个字符
--------------------------------------------------------------------*/

-- 7、创建计算字段：拼接(concat)、使用别名(as)
select concat(uid,':\t', user_name,"") from tblUser; -- 2135359701: 赵老六
select concat(uid,'(', user_name,")") from tblUser;  -- 2135359701(赵老六)
select concat(uid,'(', user_name,")") as newUidName from tblUser order by newUidName;-- 返回的值的字段名命名为newUidName，并且可以根据新字段排序
select uid,user_name,concat(uid,'(', user_name,")") as newUidName from tblUser; -- 返回三列，uid、user_name、newUidName
select 2*3;				 -- 返回值：6
select trim('abc');-- 返回值：abc
select curdate();	 -- 返回当前时间，格式为：2022-02-15

-- 8、使用函数处理数据：upper()、lower()、length()、year()、month()、day()、pi()、abs()、cos()、sin()、tan()、exp()、sqrt()、
select py_name,upper(py_name) as py_name_upper from tblUser; -- zhangsan	ZHANGSAN
select py_name,lower(py_name) as py_name_lower from tblUser  where uid=2135618804; -- MOMO	momo
select py_name,length(py_name) as py_name_length from tblUser;-- zhangsan	8
select uid,length(uid) as uid_length from tblUser;		-- 236540712	9
select * from tblUser  where year(created_at)=2022 and month(created_at)=2 and day(created_at)=15;-- 从时间中提取年、月、日
select pi();			-- 3.1415926
select abs(-5);		-- 5
select cos(pi());	-- -1

-- 9、汇总数据
/*------------9.1 聚集函数------------------------
avg()	 count()      max()        min()		 sum()
求平均  求某列的行数	 某列最大值	 某列最小值		某列之和	
------------------------------------------------*/
select avg(grade_id) from tblUser where uid in (2135447236,2135594505,2135508703)；-- 14.6667
select max(grade_id) from tblUser where uid in (2135447236,2135594505,2135508703);	-- 16
select min(grade_id) from tblUser where uid in (2135447236,2135594505,2135508703);	-- 14
select sum(grade_id) from tblUser where uid in (2135447236,2135594505,2135508703);	-- 44
select count(grade_id) from tblUser where uid in (2135447236,2135594505,2135508703);	-- 3
-- count(字段名): 	对特定列具有值的行进行计数，忽略null值
-- count(*) : 		对表中行的数目进行计数，不管列中是否为null值
/*------------9.1 聚集不同的值------------------------
叠加使用distict和avg()
------------------------------------------------*/
select avg(distinct grade_id) from tblUser where uid in (2135447236,2135594505,2135508703);-- 只将grade_id不同的值进行平均

-- 10、分组数据: group by 、having
-- -- group by 必须在where之后，order by 之前
-- -- where和having区别：where过滤行，having过滤分组；where会在数据分组前进行过滤，having会在数据分组后进行过滤
select grade_id,count(*) from tblUser group by grade_id;-- 按照grade_id分组，并获得个数
select DATE_FORMAT(create_time,'%H') as hour,count(*) as cnt from tblOrderInfo where id=8 group by hour;-- 统计下单时间
select DATE_FORMAT(create_time,'%H') as hour,count(*) as cnt from tblOrderInfo where id=8 group by hour order by cnt desc;-- 统计下单时间，按照订单量倒序排序
select DATE_FORMAT(create_time,'%D') as day,count(*) as cnt from tblOrderInfo where id=8 group by day order by cnt desc;
select user_id,count(*) as num from tblOrderInfo group by user_id having num>2 order by num desc; -- 查询订单数量大于2的uid和下单量
SELECT id,sales_uid, count(*) AS cnt FROM `tblClue` WHERE `id` = 1456 AND sales_uid in (301028,241008,10100120004,10000060001) GROUP BY `id`,`sales_uid`; -- 分别获得id, sales_uid下线索数量
-- -- select字句顺序: select、from、where、group by、having、order by

-- 11、子句查询：select嵌套子句（子查询总是从内向外处理；作为子查询的select语句只能查询单个列）
select min(date_day),sku_id from tblSkuRelation where sku_id in (select sku_id from tblSku where sku_type in (1,2)  and start_date>20220228 and start_date<20220302) group by sku_id;

-- 12、联表查询：直接多张表；inner join 表名 on 表名
select user_name,order_id from tblUser,tblUserAgreement where tblUser.uid=tblUserAgreement.uid; -- 展示名字和其订单id
select user_name,order_id from tblUser inner join tblUserAgreement on tblUser.uid=tblUserAgreement.uid; -- 展示名字和其订单id（和上述命令功能一样）

-- 13、创建高级联结：使用别名（as）（联表时比较好用）
select concat(uid,user_name) as uid_name from tblUser where grade_id>0; -- 组合列使用别名
select uid,user_name from tblUser as u where u.grade_id>0; -- 表使用别名
-- 自联结：通常作为外部语句，用来替代从相同表中检索数据的使用子查询语句
select u2.uid,u2.age from tblUser as u1,tblUser as u2 where u1.uid=2135385251 and u2.age=u1.age; -- 查询表中跟uid=2135385251在同一个年龄的用户
-- 自然联结: 对一个表使用通配符即可输出所有值
select agr.*,agrDetail.status from tblUserAgreement as agr,tblUserAgreementDetail as agrDetail where agr.status in (4,5) and agrDetail.date>'2022-02-28' and agr.order_id=agrDetail.order_id and agr.sku_id=agrDetail.sku_id and agr.status!=agrDetail.status;
-- 外联结：包含了那些在相关表中没有关联行的行(right右边的表，left左边的表)
select tblOrderInfo.user_id,tblOrderInfo.order_id,tblOrderInfo.status,tblUserAgreement.sku_type from tblUserAgreement right outer join tblOrderInfo on tblUserAgreement.uid= tblOrderInfo.user_id and tblUserAgreement.order_id=tblOrderInfo.order_id; 

-- 14、组合查询：union，union all
-- 下面两个语句，union语句获得值与or获得值相同（使用union，重复的行默认 会被删除）,只允许使用一条order by
select * from tblUser where uid=236540712 union select * from tblUser where age=4;
select * from tblUser where uid=236540712 or age=4 order by age desc;
select * from tblUser where uid=236540712 union all select * from tblUser where age=4; -- 返回的值有重复项

-- 15、插入数据
-- insert into : 直接插入
-- insert into ... select... : 插入检索出的数据
-- create select : 从一个表复制到另一个表
insert into tblUser (uid,name,age) values(236540712,'lqc',22);-- 直接插入值
insert into tblUser (uid,name,age) select uid,name, age from tblUser where uid in (2135385251,2135492756); -- 插入检索出的数据（insert into ... select...）
create table tblNewUser as select * from tblUser;-- 复制一个表到另一个表
create table tblWxf like tblUser; -- 创建一个相似表

-- 16、更新和删除数据：update...set...、delete from
update tblWxf set id=1234,user_name='cecece' where uid=2135385251; -- 更新数据
delete from tblWxf where uid=2135385251; -- 删除数据

-- 17、创建和操纵表
-- 创建表
CREATE TABLE tblWxf1 (
	id bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
	uid bigint(20) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户uid',
	name varchar(64) NOT NULL DEFAULT '' COMMENT '用户姓名',
	grade_id int(3) UNSIGNED NOT NULL DEFAULT 0 COMMENT '用户年级',
	PRIMARY KEY (`id`)
) ENGINE = InnoDB CHARSET = utf8mb4 COMMENT '测试表';

CREATE TABLE `tblUser` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL COMMENT '用户姓名',
  `sex` varchar(10) NOT NULL COMMENT '性别：男，女',
  `age` int(3) NOT NULL COMMENT '年龄',
  `pass_word` varchar(64) NOT NULL DEFAULT '0' COMMENT '用户登录密码',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='用户表' 


-- 更新表
alter table tblWxf add phone varchar(64) not null default '' comment '用户手机号'; -- 添加phone字段
alter table tblWxf drop column test; -- 删除test字段
drop table tblTest;	-- 删除表

-- 20、管理事务：begin、commit、rollback（回退）、savepoint（保留点，支持部分回退）
-- 事务处理用来管理insert、update、delete语句
-- 事务不能回退select（也没有必要）、create、drop语句
-- 下面开启了一个事务：
begin;
update tblWxf set name='测试事务step1' where id=2;
insert into tblWxf (uid,name) values (99999,'测试事务step2');
commit;