mysql操作及语法

置顶 Network porter

已于 2024-05-15 16:47:00 修改

阅读量541

点赞数

分类专栏：数据库文章标签： mysql 数据库 sql

于 2022-05-27 09:43:56 首次发布

本文链接：https://blog.csdn.net/qq_45438019/article/details/119863337

版权

数据库专栏收录该内容

10 篇文章 0 订阅

订阅专栏

文章目录

- 操作日常记录

操作日常记录

1、创建数据库

CAEATE DATABASE    sql  创建数据库

CAEATE DATABASE  SQL CHARSET  ' utf8mb4' COLLATE   'utf8_general_ci'
创建数据库   字符集 是utf8mb4       排序规则是	utf8_general_ci

Mb4编码介绍参考文章作者：太阳晒屁股了

2、删除数据库

DROP DATABASE      databaseName       删除数据库

3、选择数据库

USE 数据库    （选择数据库）

该语句可以通知 MySQL 把<数据库名>所指示的数据库作为当前数据库。该数据库保持为默认数据库，直到语段的结尾，或者直到遇见一个不同的 USE 语句。只有使用 USE 语句来指定某个数据库作为当前数据库之后，才能对该数据库及其存储的数据对象执行操作。

4、创建表

CREATE TABLE 表名   （创建表）

CREATE TABLE 表名（
 
 列名 数据类型长度 是否允许为空 是否主键 是否自增
 列名	数据类型	主键 自增
 stu_id int primary key auto_increment, 
 stu_idcrard varchar(20) unique,     unique唯一性
 stlu_address varchar(20) default '河南',     default默认
）

5、删除表

DROP 表名   （删除表）

6、外键

	-- fk_classid约束名 ，
	-- 随便写  foreign key (classid ) 本表外键 tab_class(class_id)连接表 连接字段
	constraint fk_classid foreign key(classid) REFERENCES tab_class(class_id)

7、修改列

修改列    alter table 表名 change 字段名  新字段名   新数据类型 新约束

8、修改列约束

-- 修改列约束  not null 自增 默认
alter table  grade change student_price price varchar(200) not null;
-- 修改grade 表的 student_id 为int类型
alter table grade change student_id  student_id int ;

9、添加列

添加列 alter table 表名 add 字段名 ， 数据类型，默认值，  约束
alter table grade add remark varchar(200) DEFAULT '默认';

10、删除列

alter table grade drop remark;
-- 删除grad表的 remark列

11、删除主键

alter table grade drop primary key;
-- 删除主键

12、添加主键

alter table grade add constraint pkss primary key (grade_id);
-- 给grade 表 的grade_id设置为主键

13、添加复合主键

alter table grade add constraint pksss primary key (student_km,student_id)
 -- 添加复合主键

14、删除外键

alter table grade drop foreign key fk_stuid;
 -- 删除外键

15、添加外键


alter table grade add constraint fk_stuid foreign key (student_id) references tab_student(stu_id);
 添加外键

16、新增数据

-- 新增命令
 insert into 表名 （列名，列名 ，列名) values （列值，列值，列值）
 ‘’做字符串
 
insert into tab_class(class_id,class_name) VALUES(1,'QY111');
--  添加

 --  自增列 ，可以触发自增
insert into tab_class(class_id,class_name) values(null,'qy121')
 -- 不写主键列默认赋值就null 可以触发自增
 insert into tab_class(class_name) values ('QY121');

如果给的值容纳了该表的所有列—列名可以不写

insert into tab_class values (null ,'qy145')

17、删除数据

--  删除语句  整行  一次删除一行 一次多行
delete  from  tab_class;


  -- 删除class_name自动下面所有为QY121的行。
delete from tab_class where class_name='QY121';
 
 
 delete from 表名 where 条件： 如果不写条件就是全表删除。
 

修改：update 表名 set 列名=列值，列名=列值，    where 条件 ，不写条件   全表修改

18、修改数据

修改：update 表名 set 列名=列值，列名=列值，    where 条件 ，不写条件   全表修改


 所有行 的 class_name全部修改为 new
 update tab_class set class_name='new';
 
 
  将 class_id为1的class-name 修改为 aaa
update tab_class set class_name='aaa' where class_id=1
 
 
 id=2所在的那一行的classname 更改为bbb id为99.
update tab_class set class_name='bbb',class_id=99 where class_id=2;

19、查看数据库引擎

 show engines;

20、查看表

select * from tab_class;
 查询表

21、查看数据排重 distinct

-- distinct 排重 ： 结果集排重  如果结束中有行 只保留一个
-- 如果查询集中有主键就基本没用
select distinct gender,classid from studentinfo

22、查看数据分页 limit

 -- limit : 指定条数
  -- 查询前两个数据
 select * from studentinfo limit 2

23、查看数据查询关键字 between and

– 查询关键字： between and-区间检索

 -- 查询关键字 ： between and-区间检索

 select * from studentinfo where age>= 20 and age <=21

like 查询

24、查看数据 like 模糊查询


select * from studentinfo where studentname like '刘%'

-- %通配符：任意多个字符
select * from studentinfo where studentname like '刘_'

-- _任意一个字符
-- 身份证以 410 和 412 开头
select * from studentinfo where StudentNO like '410%' or '412%'

-- 查询结果为 163邮箱
select * from studentinfo where Email like '%163%'

25、查看数据排序升序

 -- 默认升序
 
select * from Studentinfo order by age

25、查看数据排序降序

-- desc 降序

select * from Studentinfo order by age desc

25、查看数据排序先排第一个在拍第二个


-- 首先按照第一个排序，第一个排序 在在第一个基础上排第二个

select * from studentinfo order by age,birthday desc

26、删除

delete from 表  where 条件  不写条件就全表删除

27、清空表数据

MySQL 提供了 DELETE 和 TRUNCATE 关键字来删除表中的数据


-- truncate table 表命：删除表重建，只能做全表
truncate table grade ;

28、截取字符串（SUBSTR）


-- 截取字符串：从1开始截取1个

select SUBSTR(studentname,1,1) from studentinfo;

29、系统当前时间

select now() ;
-- 系统当前时间

– 级联：

– 系统函数：

30、拼接字符串

 -- 拼接字符串
 select concat('A',studentname,'B') from studentinfo;

31、生日

SELECT ROUND(DATEDIFF(CURDATE(), @birthday)/365.2422)

32、mysql查看当前密码安全策略

mysql> select @@validate_password_policy;

33、mysql修改密码

alter user 'root'@'localhost' identified by '密码';

34、经典行转列（行月数据，转成一行）


	case when  START_DATE like '%01' then plan_qty else null end as qtyD01,
		case when START_DATE like '%02' then plan_qty else null end as qtyD02,
		case when START_DATE like '%03' then plan_qty else null end as qtyD03,
		case when START_DATE like '%04' then plan_qty else null end as qtyD04,
		case when START_DATE like '%05' then plan_qty else null end as qtyD05,
		case when START_DATE like '%06' then plan_qty else null end as qtyD06,
		case when START_DATE like '%07' then plan_qty else null end as qtyD07,
		case when START_DATE like '%08' then plan_qty else null end as qtyD08,
		case when START_DATE like '%09' then plan_qty else null end as qtyD09,
		case when START_DATE like '%10' then plan_qty else null end as qtyD10,
		case when START_DATE like '%11' then plan_qty else null end as qtyD11,
		case when START_DATE like '%12' then plan_qty else null end as qtyD12,
		case when START_DATE like '%13' then plan_qty else null end as qtyD13,
		case when START_DATE like '%14' then plan_qty else null end as qtyD14,
		case when START_DATE like '%15' then plan_qty else null end as qtyD15,
		case when START_DATE like '%16' then plan_qty else null end as qtyD16,
		case when START_DATE like '%17' then plan_qty else null end as qtyD17,
		case when START_DATE like '%18' then plan_qty else null end as qtyD18,
		case when START_DATE like '%19' then plan_qty else null end as qtyD19,
		case when START_DATE like '%20' then plan_qty else null end as qtyD20,
		case when START_DATE like '%21' then plan_qty else null end as qtyD21,
		case when START_DATE like '%22' then plan_qty else null end as qtyD22,
		case when START_DATE like '%23' then plan_qty else null end as qtyD23,
		case when START_DATE like '%24' then plan_qty else null end as qtyD24,
		case when START_DATE like '%25' then plan_qty else null end as qtyD25,
		case when START_DATE like '%26' then plan_qty else null end as qtyD26,
		case when START_DATE like '%27' then plan_qty else null end as qtyD27,
		case when START_DATE like '%28' then plan_qty else null end as qtyD28,
		case when START_DATE like '%29' then plan_qty else null end as qtyD29,
		case when START_DATE like '%30' then plan_qty else null end as qtyD30,
		case when START_DATE like '%31' then plan_qty else null end as qtyD31,

区间日期判断

  select PRODUCT_NAME as "name",
        AVG(EXPECTED_PASS) as "qty",
        FACTORY_NAME as 'facname',
        DATE as 'date',
        SUBSTR(date, 7, 2) as 'date2',
        STOP_DATE as "stop",
        AVG(case
        when SUBSTR(date, 7, 2)<= '01' and SUBSTR(STOP_DATE, 7, 2) >= '01' then EXPECTED_PASS
        else null end) as '01',
        AVG(case
        when SUBSTR(date, 7, 2)<= '02' and SUBSTR(STOP_DATE, 7, 2) >= '02' then EXPECTED_PASS
        else null end) as '02',
        AVG(case
        when SUBSTR(date, 7, 2)<= '03' and SUBSTR(STOP_DATE, 7, 2) >= '03' then EXPECTED_PASS
        else null end) as '03',
        AVG(case
        when SUBSTR(date, 7, 2)<= '04' and SUBSTR(STOP_DATE, 7, 2) >= '04' then EXPECTED_PASS
        else null end) as '04',
        AVG(case
        when SUBSTR(date, 7, 2)<= '05' and SUBSTR(STOP_DATE, 7, 2) >= '05' then EXPECTED_PASS
        else null end) as '05',
        AVG(case
        when SUBSTR(date, 7, 2)<= '06' and SUBSTR(STOP_DATE, 7, 2) >= '06' then EXPECTED_PASS
        else null end) as '06',
        AVG(case
        when SUBSTR(date, 7, 2)<= '07' and SUBSTR(STOP_DATE, 7, 2) >= '07' then EXPECTED_PASS
        else null end) as '07',
        AVG(case
        when SUBSTR(date, 7, 2)<= '08' and SUBSTR(STOP_DATE, 7, 2) >= '08' then EXPECTED_PASS
        else null end) as '08',
        AVG(case
        when SUBSTR(date, 7, 2)<= '09' and SUBSTR(STOP_DATE, 7, 2) >= '09' then EXPECTED_PASS
        else null end) as '09',
        AVG(case
        when SUBSTR(date, 7, 2)<= '10' and SUBSTR(STOP_DATE, 7, 2) >= '10' then EXPECTED_PASS
        else null end) as '10',
        AVG(case
        when SUBSTR(date, 7, 2)<= '11' and SUBSTR(STOP_DATE, 7, 2) >= '11' then EXPECTED_PASS
        else null end) as '11',
        AVG(case
        when SUBSTR(date, 7, 2)<= '12' and SUBSTR(STOP_DATE, 7, 2) >= '12' then EXPECTED_PASS
        else null end) as '12',
        AVG(case
        when SUBSTR(date, 7, 2)<= '13' and SUBSTR(STOP_DATE, 7, 2) >= '13' then EXPECTED_PASS
        else null end) as '13',
        AVG(case
        when SUBSTR(date, 7, 2)<= '14' and SUBSTR(STOP_DATE, 7, 2) >= '14' then EXPECTED_PASS
        else null end) as '14',
        AVG(case
        when SUBSTR(date, 7, 2)<= '15' and SUBSTR(STOP_DATE, 7, 2) >= '15' then EXPECTED_PASS
        else null end) as '15',
        AVG(case
        when SUBSTR(date, 7, 2)<= '16' and SUBSTR(STOP_DATE, 7, 2) >= '16' then EXPECTED_PASS
        else null end) as '16',
        AVG(case
        when SUBSTR(date, 7, 2)<= '17' and SUBSTR(STOP_DATE, 7, 2) >= '17' then EXPECTED_PASS
        else null end) as '17',
        AVG(case
        when SUBSTR(date, 7, 2)<= '18' and SUBSTR(STOP_DATE, 7, 2) >= '18' then EXPECTED_PASS
        else null end) as '18',
        AVG(case
        when SUBSTR(date, 7, 2)<= '19' and SUBSTR(STOP_DATE, 7, 2) >= '19' then EXPECTED_PASS
        else null end) as '19',
        AVG(case
        when SUBSTR(date, 7, 2)<= '20' and SUBSTR(STOP_DATE, 7, 2) >= '20' then EXPECTED_PASS
        else null end) as '20',
        AVG(case
        when SUBSTR(date, 7, 2)<= '21' and SUBSTR(STOP_DATE, 7, 2) >= '21' then EXPECTED_PASS
        else null end) as '21',
        AVG(case
        when SUBSTR(date, 7, 2)<= '22' and SUBSTR(STOP_DATE, 7, 2) >= '22' then EXPECTED_PASS
        else null end) as '22',
        AVG(case
        when SUBSTR(date, 7, 2)<= '23' and SUBSTR(STOP_DATE, 7, 2) >= '23' then EXPECTED_PASS
        else null end) as '23',
        AVG(case
        when SUBSTR(date, 7, 2)<= '24' and SUBSTR(STOP_DATE, 7, 2) >= '24' then EXPECTED_PASS
        else null end) as '24',
        AVG(case
        when SUBSTR(date, 7, 2)<= '25' and SUBSTR(STOP_DATE, 7, 2) >= '25' then EXPECTED_PASS
        else null end) as '25',
        AVG(case
        when SUBSTR(date, 7, 2)<= '26' and SUBSTR(STOP_DATE, 7, 2) >= '26' then EXPECTED_PASS
        else null end) as '26',
        AVG(case
        when SUBSTR(date, 7, 2)<= '27' and SUBSTR(STOP_DATE, 7, 2) >= '27' then EXPECTED_PASS
        else null end) as '27',
        AVG(case
        when SUBSTR(date, 7, 2)<= '28' and SUBSTR(STOP_DATE, 7, 2) >= '28' then EXPECTED_PASS
        else null end) as '28',
        AVG(case
        when SUBSTR(date, 7, 2)<= '29' and SUBSTR(STOP_DATE, 7, 2) >= '29' then EXPECTED_PASS
        else null end) as '29',
        AVG(case
        when SUBSTR(date, 7, 2)<= '30' and SUBSTR(STOP_DATE, 7, 2) >= '30' then EXPECTED_PASS
        else null end) as '30',
        AVG(case
        when SUBSTR(date, 7, 2)<= '31' and SUBSTR(STOP_DATE, 7, 2) >= '31' then EXPECTED_PASS
        else null end) as '31'
        FROM ${platSchema}.TMCBSPMEP02 WHERE 1=1
        where 1 = 1
        <isNotEmpty prepend=" AND " property="productType">
            PRODUCT_TYPE = #productType#
        </isNotEmpty>
        <isNotEmpty prepend=" AND " property="factortCode">
            FACTORT_CODE = #factortCode#
        </isNotEmpty>
        <isNotEmpty prepend=" AND " property="materialType">
            MATERIAL_TYPE = #materialType#
        </isNotEmpty>
        <isNotEmpty prepend=" AND " property="date">
            left(DATE, 6) = #date#
        </isNotEmpty>
        <isNotEmpty prepend=" AND " property="productName">
            PRODUCT_NAME = #productName#
        </isNotEmpty>
        group by FACTORT_CODE   ;

35、时间格式化yymmdd （date_format）

 date_format(now(), '%Y%m%d') = 20001101
 date_format(now(), '%Y%m') = 200011

select date_format('2023/04/05 00:00','%Y%m%d%H%i%S');   =20230405000000

36、字符串长度限制匹配

LEFT()函数是一个字符串函数，它返回具有指定长度的字符串的左边部分。

and  LEFT(str,length) = 什么什么;

LEFT()函数接受两个参数：

str是要提取子字符串的字符串。
length是一个正整数，指定将从左边返回的字符数。

参考文章作者：茅坤宝骏氹

37、保留小数

SQL中字段保留两位小数：

使用 Round() 函数，如 Round(number,2) ，其中参数2表示保留两位有效数字，四舍五入到两位小数
例如 ROUND(3.141592653, 2) 结果为3.14；
使用 cast(number as decimal(10,2)) 实现转换，其中参数2表示保留两位有效数字例如cast(3.1415 as decimal(10,2)) 结果为3.14；
备注：CAST与CONVERT都可以执行数据类型转换，且都默认实现了四舍五入

————————————————
版权声明：本文为CSDN博主「hyfstyle」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/hyfstyle/article/details/90054298

38、日累加数据

select DATE                                  as "date",
       ORIVALUE                              as "orivalue",
       ROUND((@csum := @csum + ORIVALUE), 6) AS "sum_consumed"
from chem_cockpit.tmcbszzpl09,
     (select @csum := 0) as it
where 1 = 1
order by DATE;

按照正常编程思路，a=a+1,a是求的累加变量，1是初始值，sql里同样也可以用这种方法，@表示定义变量–>@csum，然后用select查做一个表来初始化变量—>(select @csum:=0) ；然后再进行联合查询
参考文章https://blog.csdn.net/yiye2017zhangmu/article/details/102832912

39、转译字符小于号

 <![CDATA[<=]]>

40、动态行转动态列

该学习来自gpt4

SET @sql = NULL;

-- 获取所有不同的字段名，按照某个列构建 CASE 语句
SELECT GROUP_CONCAT(DISTINCT
  CONCAT(
    'MAX(CASE WHEN field_name = ''',
    field_name,
    ''' THEN field_value END) AS `',
    field_name, '`'
  )
) INTO @sql
FROM yourTable;

-- 构建完整的 SELECT 语句
SET @sql = CONCAT('SELECT ', @sql, ' FROM yourTable GROUP BY identifier_column');

-- 执行 SQL 语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

GROUP_CONCAT 和 DISTINCT 用来获取数据集中不同的field_name值，并构建适用于所有这些值的 CASE 表达式。
CONCAT 用来生成 SQL 查询语句。
@sql 变量用来存储动态构建的 SQL 查询字符串。
最后使用 PREPARE、EXECUTE 和 DEALLOCATE PREPARE 语句执行动态创建的查询。

请注意，yourTable 应该替换为你的实际表名，field_name 和 field_value 应该替换为实际列名。上面的 GROUP BY identifier_column 是假设你有另外一列可以用来分组数据，如果没有这样的列，你需要相应调整这部分 SQL 语句，以适应你的数据结构。
动态 SQL 可以非常灵活，但也容易出错，尤其是需要确保所有的字段名是有效的 SQL 标识符，并且对于SQL注入攻击有一定的风险。在实际编写动态 SQL 时务必仔细检查和测试。