mysql 常用语句

Mysql增加主键或者更改表的列为主键的sql语句
添加表字段

alter table table1 add transactor varchar(10) not Null;

alter table   table1 add id int unsigned not Null auto_increment primary key

修改某个表的字段类型及指定为空或非空

alter table 表名称 change 字段名称 字段名称 字段类型 [是否允许非空];

alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

alter table 表名称 modify 字段名称 字段类型 [是否允许非空];

修改某个表的字段名称及指定为空或非空

alter table 表名称 change 字段原名称 字段新名称 字段类型 [是否允许非空

删除某一字段

ALTER TABLE mytable DROP 字段 名;

添加唯一键

ALTER TABLE `test2` ADD UNIQUE ( `userid`)

修改主键

ALTER TABLE `test2` DROP PRIMARY KEY ,ADD PRIMARY KEY ( `id` )

增加索引

ALTER TABLE `test2` ADD INDEX ( `id` )

ALTER TABLE `category ` MODIFY COLUMN `id`  int(11) NOT NULL AUTO_INCREMENT FIRST ,ADD PRIMARY KEY (`id`);

 

修改主键的sql语句块如下:

mailbox 表新增字段

DROP PROCEDURE IF EXISTS mailbox_column_update;
CREATE PROCEDURE mailbox_column_update() BEGIN
    -- 新增删除标志列
    IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='cbs' AND table_name='mailbox' AND COLUMN_NAME='delete_flag') THEN
            ALTER TABLE mailbox ADD   delete_flag  int  DEFAULT 2   NOT NULL;
    END IF;
    -- 新增删除日期列
    IF NOT EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='cbs' AND table_name='mailbox' AND COLUMN_NAME='delete_date') THEN
            ALTER TABLE mailbox ADD  delete_date  int  DEFAULT 0   NOT NULL;
    END IF;
    -- 如果存在字段account_mail，则修改字段长度
    IF EXISTS(SELECT 1 FROM information_schema.COLUMNS WHERE TABLE_SCHEMA='cbs' AND table_name='mailbox' AND COLUMN_NAME='email_account')
    THEN
        alter table mailbox modify column email_account varchar(320);
    END IF;
    -- 如果不存在主键列，则设置双主键
    IF ((SELECT count(*) FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA ='cbs' AND table_name='mailbox' AND CONSTRAINT_NAME ='PRIMARY' AND (COLUMN_NAME ='email_account' OR COLUMN_NAME = 'company_id'))=0)THEN
                ALTER TABLE mailbox ADD primary key (company_id,email_account);
    -- 如果只存在一个主键列
    ELSEIF ((SELECT count(*) FROM information_schema.KEY_COLUMN_USAGE WHERE TABLE_SCHEMA ='cbs' AND table_name='mailbox' AND CONSTRAINT_NAME ='PRIMARY' AND (COLUMN_NAME ='email_account' OR COLUMN_NAME = 'company_id'))<2)THEN
                ALTER TABLE mailbox  DROP PRIMARY KEY,ADD primary key (company_id,email_account);
    END IF;
        
END;
CALL mailbox_column_update();
DROP PROCEDURE IF EXISTS mailbox_column_update;

 

 

select UNIX_TIMESTAMP();

select FROM_UNIXTIME(1597240337);

CREAT TABLE `task` (
        `task_id` int(8),
        `name` varchar(50),
        `username` varchar(50),
        `creattime` TIMESTAMP(0),
        `endtime` TIMESTAMP(0)
)

CREATE TABLE `task` (
  `task_id` int(20),
  `name` varchar(10) DEFAULT NULL COMMENT '接入码',
  `username` varchar(10) DEFAULT NULL COMMENT '局向',
  `creattime` TIMESTAMP,
  `endtime` TIMESTAMP
)

CREATE TABLE `case` (
  `case_id` int(8),
    `task_id` int(8),
    `res_status` VARCHAR(10) 
)

SELECT task_id,name,username,DATE_FORMAT(FROM_UNIXTIME(creattime), '%Y-%m-%d') FROM task WHERE name = 'auto';

INSERT INTO task (task_id,name,username,creattime,endtime) VALUES(1125,'auto','hsdfsdf',1597240338,1597240338);

ALTER TABLE task MODIFY creattime VARCHAR(50);

ALTER TABLE task MODIFY endtime VARCHAR(50);

ALTER TABLE `CASE` MODIFY res_status int(50);


SELECT task.name AS '任务名称', `case`.res_status AS '执行结果', '成功率' AS rate FROM task LEFT outer JOIN `case` on task.task_id = `case`.task_id AND task.name = 'auto';

INSERT INTO `case` (case_id,task_id,res_status) VALUES(22323,1125,'0');


CREATE TABLE `student`(
    `name` VARCHAR(50),
    `sex` VARCHAR(10),
    `age` int(10),
    `createtime` VARCHAR(50)
);

INSERT INTO student (name,sex,age,createtime) VALUES('张山','0','10','1597240338');
INSERT INTO student (name,sex,age,createtime) VALUES('李四','1','12','1597240338');


select name,if(sex=0,'女','男') as sex from student;

INSERT INTO `case` (case_id,task_id,res_status) VALUES(22324,1125,'0');
INSERT INTO `case` (case_id,task_id,res_status) VALUES(22325,1125,'1');
INSERT INTO `case` (case_id,task_id,res_status) VALUES(22326,1125,'-1');

SELECT
    task. NAME AS '任务名称',
    if(`case`.res_status=1,'成功','失败') AS '执行结果',
    SUM(CASE WHEN `case`.res_status=1 THEN 1 ELSE 0 END) AS '成功次数',
    SUM(CASE WHEN `case`.res_status=0 OR `case`.res_status=1 OR `case`.res_status=-1 THEN 1 ELSE 0 END) '执行总次数',
    concat ( left (SUM(CASE WHEN `case`.res_status=1 THEN 1 ELSE 0 END) / SUM(CASE WHEN `case`.res_status=0 OR `case`.res_status=1 OR `case`.res_status=-1 THEN 1 ELSE 0 END) *100,2),'%') AS '成功率'
FROM
    task
LEFT OUTER JOIN `case` ON task.task_id = `case`.task_id WHERE task.NAME = 'auto';

CREATE TABLE IF NOT EXISTS `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `class` int(11) NOT NULL,
  `score` varchar(10) NOT NULL,
  `txt` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

INSERT INTO `score` (`id`, `name`, `class`, `score`, `txt`) VALUES
(1, '张三', 1, '60', ''),
(2, '李四', 2, '80', ''),
(3, '张三1', 1, '20', ''),
(4, '李四2', 2, '40', ''),
(5, '张三2', 1, '50', ''),
(6, '李四3', 2, '90', ''),
(7, '王五', 1, '90', ''),
(8, '李六', 2, '85', '');

SELECT `class`,
SUM(CASE WHEN score>=60 THEN 1 ELSE 0 END) / SUM(CASE WHEN score>=60 THEN 0 ELSE 1 END),
SUM(CASE WHEN score>=60 THEN 0 ELSE 1 END)
FROM score GROUP BY 1;


SELECT task_id,name,username,DATE_FORMAT(FROM_UNIXTIME(creattime), '%Y-%m-%d') AS '创建时间(年月日)' FROM task WHERE name = 'auto';


select Department.Name AS 'Department', Employee.Name AS 'Employee', (select Salary from Employee order by Salary desc limit 1 offset 1) AS 'Salary' from Employee  left outer join Department on Employee.DepartmentId = Department.Id


SELECT d.name AS Department,
    e.name AS Employee,
    e.salary AS Salary
FROM employee e
JOIN department d
    ON e.departmentid = d.id
WHERE e.salary IN (
    SELECT salary 
    FROM(
        SELECT DISTINCT salary
        FROM employee
        WHERE departmentid = d.id
        ORDER BY salary DESC
        LIMIT 3
    ) AS a
)

select Department.Name As 'Department', Employee.Name As 'Employee', Salary from Employee join Department on Employee.DepartmentId = Department.Id where Employee.Salary in (
    select Salary from (
        select distinct Salary from Employee where DepartmentId = Department.Id order by Salary desc limit 3
    ) AS a
)


CREATE TABLE `employee_tbl` (
  `id` int(11) NOT NULL,
  `name` char(10) NOT NULL DEFAULT '',
  `date` datetime NOT NULL,
  `singin` tinyint(4) NOT NULL DEFAULT '0' COMMENT '登录次数',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `employee_tbl` VALUES ('1', '小明', '2016-04-22 15:25:33', '1'), ('2', '小王', '2016-04-20 15:25:47', '3'), ('3', '小丽', '2016-04-19 15:26:02', '2'), ('4', '小王', '2016-04-07 15:26:14', '4'), ('5', '小明', '2016-04-11 15:26:40', '4'), ('6', '小明', '2016-04-04 15:26:54', '2');

select * from employee_tbl;

SELECT name, count(*) FROM employee_tbl group by NAME;
查找name字段中以'st'为开头的所有数据：
SELECT * FROM task WHERE name REGEXP '^au';
查找name字段中以'ok'为结尾的所有数据：
SELECT *  from task where name regexp 'to$';
查找name字段中包含'mar'字符串的所有数据：
select * from task WHERE username regexp 'a';
查找name字段中以元音字符开头或以'ok'字符串结尾的所有数据：
select * from task where username regexp '^z|son$';


create table test_table1(`id` int(50),`name` VARCHAR(100));

alter table test_table1 drop name;

select * from test_table1;

alter table test_table1 add name varchar(100);

alter table test_table1 add age int(10) first;

alter table test_table1 add address VARCHAR(200) after id;

alter table test_table1 modify id int(50);

alter table test_table1 change id id int(10);

alter table test_table1 modify name BIGINT not null default 100;

alter table test_table1 alter name set default 20;

alter table test_table1 alter name drop default;

alter table test_table1 rename to test_table2;

第一、只复制表结构到新表

create table 新表 select * from 旧表 where 1=2

或者

create table 新表 like 旧表 

第二、复制表结构及数据到新表

create table新表 select * from 旧表 


alter table test_table2 ADD INDEX indexname(id);

create table mytable(
    ID INT NOT NULL,
    username varchar(16) NOT NULL,
    INDEX name_index(username(6))
);

drop INDEX name_index on mytable;

alter table mytable ADD UNIQUE name_index(username(6));

CREATE TABLE mytable1(
    ID INT NOT NULL,
    username VARCHAR(16) NOT NULL,
    UNIQUE name_index(username(10))
);

alter table mytable1 modify uasername INT NOT NULL;

alter table mytable1 add PRIMARY KEY (ID);

ALTER TABLE mytable1 drop PRIMARY key;

show index from mytable1; 


create table `runoob_tb1`(
    `runoob_id` int(11) not null auto_increment,
    `runoob_title` varchar(100) not null default '',
    `runoob_author` varchar(40) not null default '',
    `sunmission_date` date default null,
    primary key (`runoob_id`),
    UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE = INNODB;

SHOW CREATE TABLE runoob_tb1;

create table `clone_tb1`(
    `runoob_id` int(11) not null auto_increment,
    `runoob_title` varchar(100) not null default '',
    `runoob_author` varchar(40) not null default '',
    `sunmission_date` date default null,
    primary key (`runoob_id`),
    UNIQUE KEY `AUTHOR_INDEX` (`runoob_author`)
) ENGINE = INNODB;

另一种完整复制表的方法:
CREATE TABLE clone_tb2 LIKE clone_tb1;

CREATE TABLE task2 LIKE task;

INSERT INTO task2 select * from task;


create table task3 
(
    select task_id, name from task
)

create table task4
(
    id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY
)
AS
(
    SELECT * FROM task
)


CREATE table task5 SELECT * from task where 1=2;

create table task6 select * from task;

select user();

select VERSION();

SELECT database();

show VARIABLES;

统计重复数据
SELECT count(*) AS counts, task_id, name FROM task2 GROUP BY task_id, name having counts>1;

过滤重复数据
如果你需要读取不重复的数据可以在 SELECT 语句中使用 DISTINCT 关键字来过滤重复数据
SELECT DISTINCT task_id, name from task2;

你也可以使用 GROUP BY 来读取数据表中不重复的数据：
SELECT task_id, name from task2 group by task_id, name;

当然你也可以在数据表中添加 INDEX（索引） 和 PRIMAY KEY（主键）这种简单的方法来删除表中的重复记录。方法如下：
ALTER IGNORE TABLE task2 ADD PRIMARY KEY (task_id, name);

sql注入，通过恒真表达式实现
select * from task2 WHERE 2=2;

MySQL 导出数据
以下实例中我们将数据表 runoob_tbl 数据导出到 /tmp/runoob.txt 文件中:
select * from task into outfile '/tmp/runoob.txt'
你可以通过命令选项来设置数据输出的指定格式，以下实例为导出 CSV 格式：
select * from task into outfile '/tmp/runoob.txt' fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

LOAD DATA INFILE
MySQL可以支持从文本文件里面直接load文本数据到表中，但是也需要指定分割符
load data infile "/data/outputdata/2.txt" into table test.t2 fields terminated by ',';

返回 x 的绝对值
select abs(-1);
求 x 的反余弦值(参数是弧度)
SELECT ACOS(0.25);
求反正弦值(参数是弧度)
SELECT ASIN(0.25);
求反正切值(参数是弧度)
SELECT ATAN(2.5);
求反正切值(参数是弧度)
select ATAN2(-0.8, 2);
返回一个表达式的平均值，expression 是一个字段
SELECT AVG(Price) AS AveragePrice FROM Products;
整除，n 为被除数，m 为除数
SELECT 10 DIV 5 AS xxx;

返回小于或等于 x 的最大整数　
SELECT floor(2.22);
返回列表中的最大值
SELECT GREATEST(1,2,10);
返回列表中的最小值
SELECT LEAST(1,2,10);
返回数字的自然对数，以 e 为底
SELECT LN(2);
返回自然对数(以 e 为底的对数)，如果带有 base 参数，则 base 为指定带底数
SELECT LOG(2,4);
返回以 10 为底的对数
SELECT log10(100);
返回以 2 为底的对数
SELECT LOG2(4);
返回字段 expression 中的最大值
SELECT MAX(task_id) AS max_id FROM task;
返回字段 expression 中的最小值
SELECT MIN(task_id) AS min_id FROM task;
返回 x 除以 y 以后的余数
SELECT MOD(5,2);
返回圆周率(3.141593）
SELECT PI();
返回 x 的 y 次方
SELECT POW(2,3);
SELECT POWER(2,3);
将角度转换为弧度
SELECT RADIANS(180);
返回 0 到 1 的随机数
SELECT RAND();
返回离 x 最近的整数
SELECT ROUND(1.232);
返回 x 的符号，x 是负数、0、正数分别返回 -1、0 和 1
SELECT SIGN(-10);
求正弦值(参数是弧度)
SELECT SIN(RADIANS(30));
返回x的平方根
SELECT SQRT(25);
返回指定字段的总和
SELECT SUM(task_id) FROM task;
求正切值(参数是弧度)
SELECT TAN(1.75);
返回数值 x 保留到小数点后 y 位的值（与 ROUND 最大的区别是不会进行四舍五入）
SELECT TRUNCATE (1.23456,2);

MySQL 日期函数
计算起始日期 d 加上 n 天的日期
SELECT ADDDATE('2020-08-25',INTERVAL 10 DAY);
时间 t 加上 n 秒的时间
SELECT ADDTIME('2020-08-25 11:11:11', 5);
返回当前日期
SELECT CURDATE();
SELECT CURRENT_DATE();
返回当前时间
SELECT CURRENT_TIME();
返回当前日期和时间
SELECT CURRENT_TIMESTAMP();
返回当前时间
SELECT CURTIME();
从日期或日期时间表达式中提取日期值
SELECT DATE('2020-08-25');
计算日期 d1->d2 之间相隔的天数
SELECT DATEDIFF('2020-08-01', '2020-08-20');
计算起始日期 d 加上一个时间段后的日期
SELECT ADDDATE('2020-08-25 11:11:11', 1);
SELECT ADDDATE('2020-08-25 11:11:11',INTERVAL 5 MINUTE);
按表达式 f的要求显示日期 d
SELECT DATE_FORMAT('2020-08-25 11:11:11','%Y-%m-%d %r');
函数从日期减去指定的时间间隔
SELECT DATE_SUB('2020-08-25 11:11:11',INTERVAL 1 DAY);
返回当前日期和时间
SELECT LOCALTIME();
SELECT LOCALTIMESTAMP();

MySQL 高级函数
返回 x 的二进制编码
SELECT BIN(16);

SELECT * FROM task ORDER BY task_id;
SELECT * FROM task partition by task_id;


ALTER TABLE student ADD `score` INT(10);
ALTER TABLE student modify age INT(200);
ALTER TABLE student modify sex INT(200);
ALTER TABLE student modify score INT(200);

ALTER TABLE student ADD id INT UNSIGNED NOT NULL auto_increment PRIMARY KEY FIRST;

ALTER table student DROP id;

ALTER table student drop score;

ALTER TABLE student ADD score int(100) NOT NULL  AFTER age;

delete from student;

ALTER TABLE student MODIFY COLUMN id int NOT NULL auto_increment;
ALTER TABLE student MODIFY id PRIMARY KEY;


insert into student VALUES('1', '李四', '男', 13, 100, '1597240338'),('2','张三', '男', 22, 100, '1597240338'),('3','王五', '男', 20, 100, '1597240338'), ('4','麻子', '男', 18, 99, '1597240338'), ('5','小红', '女', 16, 98, '1597240338'), ('6','李军', '男', 20, 96, '1597240338');

ALTER TABLE student ADD class VARCHAR(50) AFTER score;

SELECT *,rank() over ( PARTITION BY class ORDER BY score DESC)  AS ranking FROM student; 

20200826---sql学习
SELECT *,rank() over ( PARTITION BY class ORDER BY score DESC)  AS ranking FROM student;

SELECT (@i:=@i+1) AS ranking, t.* FROM student t, (select @i:=0) AS j;

SELECT (@i:=@i+1), p.* FROM (SELECT t2.name, t2.sex, t2.age, t2.score, t2.class FROM student t2 order by age desc)p, (SELECT @i:=0) k;

-- 按分数排序，且需要相同成绩的具有相同的排名，输出排名
SELECT 
    tmp.id, tmp.name, tmp.sex, tmp.age, tmp.score, tmp.class, 
    -- 顺序一直在变大
    @j:=@j+1 as j,
    -- 只有在前后二次排序值不同时才会使用顺序号
    @k:=(case WHEN @pre_score=tmp.score THEN @k ELSE @j END) as rank,
    @pre_score:=tmp.score as pre_score
FROM
(
-- 成绩排序
SELECT * FROM student ORDER BY score DESC
) tmp,
-- @k 表示最终的排名(相同值时序号相同） 
-- @j 表示顺序排名 
-- @pre_score上一次排序值
(SELECT @k:=0, @j:=0, @pre_score:=0) xscore;

-- mysql排行查询
-- 如何计算id=1的user的排名？利用子查询，统计其”值”比该id的”值”大的记录总数
SELECT count(1) +1 FROM student WHERE score > (SELECT score FROM student WHERE id = 1);


SELECT
    obj.id,
    obj.score,
    CASE
WHEN @rowtotal = obj.score THEN
    @rownum
WHEN @rowtotal := obj.score THEN
    @rownum := @rownum + 1
WHEN @rowtotal = 0 THEN
    @rownum := @rownum + 1
END AS rownum
FROM
    (
        SELECT
            id,
            score
        FROM
            student
        ORDER BY
            score DESC
    ) AS obj,
    (SELECT @rownum := 0, @rowtotal := NULL) r;

-- 需求：获取分类平均值的名次？ 比如10个班级的平均分，按照班级名称排序，后面跟着名次。

SELECT class, AVG(score) AS avgs FROM student GROUP BY class ORDER BY avgs DESC;

SELECT A.*, @rank:=@rank+1 AS pm FROM (SELECT class, AVG(score) AS avgs FROM student GROUP BY class ORDER BY avgs DESC) A, (SELECT @rank:=0) B;

SELECT
    A.*, @rank :=@rank + 1 AS pm
FROM
    (
        SELECT
            class,
            AVG(score) AS avgs
        FROM
            student
        GROUP BY
            class
        ORDER BY
            avgs DESC
    ) A,
    (SELECT @rank := 0) B;

SELECT
    obj.id,
    obj.score,
    @rownum := @rownum + 1 AS rownum
FROM
    (
        SELECT
            id,
            score
        FROM
            student
        ORDER BY
            score DESC
    ) AS obj,
    (SELECT @rownum := 0) r;
    
    
在表中，一个列可能会包含多个重复值，有时您也许希望仅仅列出不同（distinct）的值。

DISTINCT 关键词用于返回唯一不同的值。

select distinct country from websites;

Like用法

select country from websites WHERE country LIKE 'US%';

in用法
select country from websites WHERE country in('USA');

between...and用法
select alexa from websites WHERE alexa between 10 and 20;

oorder by和limit的联合使用
select * from websites order by alexa desc limit 2;

使用 SQL _ 通配符：SQL 语句选取 name 以一个任意字符开始，然后是 "oogle" 的所有客户：
select * from websites where name like '_oogle';
select * from websites where name like 'G_o_le';

使用 SQL [charlist] 通配符：MySQL 中使用 REGEXP 或 NOT REGEXP 运算符 (或 RLIKE 和 NOT RLIKE) 来操作正则表达式。

下面的 SQL 语句选取 name 以 "G"、"F" 或 "s" 开始的所有网站：
select * from websites where name REGEXP '^[Gfs]';

下面的 SQL 语句选取 name 以 A 到 H 字母开头的网站：
select * from websites where name REGEXP '^[A-H]';

between and和in联合使用
select * from websites where (alexa between 1 and 20) and country not in ('USA', 'IND');

下面的 SQL 语句选取 name 以介于 'A' 和 'H' 之间字母开始的所有网站：
select * from websites where name BETWEEN 'A' AND 'H';

下面的 SQL 语句选取 name 不介于 'A' 和 'H' 之间字母开始的所有网站：
select * from websites where name not between 'A' and H'';

 

本文地址：https://blog.csdn.net/bang152101/article/details/108581517