mySQL

Создание пользователя. Например для phpmyadmin

mysql> CREATE USER 'miltorg'@'localhost' IDENTIFIED BY '33333333333';
Query OK, 0 rows affected (0.03 sec)

mysql> GRANT ALL PRIVILEGES ON * . * TO 'miltorg'@'localhost';
Query OK, 0 rows affected (0.02 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.03 sec)

-------------

-- create
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL,
chis int
);

-- insert
INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales', 5);
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting', 10);
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales', 15);

-- fetch
SELECT dept, sum(chis) FROM EMPLOYEE GROUP BY dept;

https://onecompiler.com/mysql/3zhwufuxh
=======================

ALTER USER 'miltorg'@'localhost'
IDENTIFIED WITH mysql_native_password
BY 'uuuuuuuuuuuuu';

GRANT CREATE, ALTER, SELECT, INSERT, UPDATE, DELETE, DROP ON * . * TO 'miltorg'@'localhost';

GRANT * ON * . * TO 'miltorg'@'localhost';

INSERT, UPDATE, DELETE, DROP

------------

CREATE TABLE new3 SELECT * FROM new1 where sovpalo=10;

select v.* from ( SELECT new8.* , count(1) over(partition by d1) as cnt FROM new8 ) as v where cnt > 4;

SELECT id, MAX(d1), COUNT(*) FROM new8 GROUP BY d1 HAVING COUNT(*) > 2;

SELECT MAX(d1),MAX(st), MIN(st), COUNT(*) FROM new8 GROUP BY d1 HAVING COUNT(*) > 2;

----------------
CREATE TABLE iz209bez12
(
,
Model VARCHAR(20)
);

ALTER TABLE iz209bez12 DROP COLUMN Id; - удаление

ALTER TABLE iz209bez12 ADD Id INT PRIMARY KEY AUTO_INCREMENT;

ALTER TABLE iz209bez12 MODIFY `con_id` column_definition AFTER sta_id;

CREATE TABLE `iz209_tmp` SELECT DISTINCT d1, myData1, myData2, str1, str2, sovpalo FROM iz209;

Select max(`id`) as `maxid` from iz209;

select v.* from ( SELECT iz209.* , count(1) over(partition by d1) as cnt FROM iz209 ) as v where cnt > 2;

---------

CREATE TABLE new2010t4 LIKE new2010t2;

ALTER IGNORE TABLE `new2010t4` ADD UNIQUE INDEX(d1, myData1, myData2);

CREATE TABLE `table_tmp` SELECT DISTINCT * FROM `new2010t4`;

select v.* from ( SELECT table_tmp.* , count(1) over(partition by d1) as cnt FROM table_tmp ) as v where cnt > 4;

DROP TABLE `table_tmp`;

CREATE TABLE `table_tmp` SELECT DISTINCT d1, myData1, myData2, str1, str2, sovpalo FROM `new2010t4`;

CREATE TABLE `table_tmp` SELECT DISTINCT * FROM `table`;
DROP TABLE `table`;
RENAME TABLE `table_tmp` TO `table`;

----- ---------- -------

TRUNCATE TABLE new2010t3;

CREATE TABLE new2010t3 LIKE new2010t2;

CREATE TABLE new2010t2 LIKE new2010;

INSERT INTO `new2010t2` SELECT * FROM `new2010`;

DELETE FROM `new2010t2` where id=3;

select v.*
from (
SELECT new2010t2.*
, count(1) over(partition by d1) as cnt
FROM new2010t2
) as v
where cnt > 2;

---------

WITH MyScope AS (
SELECT id, d1, myData1, myData2, str1, str2, sovpalo, COUNT(1) OVER (PARTITION BY d1) AS Cnt
FROM new2010 )
SELECT * FROM MyScope WHERE Cnt>1;

SELECT id, MAX(d1), MIN(myData1), COUNT(*) FROM new2010 GROUP BY d1 HAVING COUNT(*) > 1;

select v.*
from (
SELECT new2010.*
, count(1) over(partition by d1) as cnt
FROM new2010
) as v
where cnt > 1;

CREATE TABLE new2010
(
id int(11) PRIMARY KEY auto_increment,
d1 VARCHAR(100),
myData1 VARCHAR(12),
myData2 VARCHAR(12),
str1 int(7),
str2 int(7),
sovpalo int(3)
);

DROP TABLE new2010;

INSERT INTO new2010 VALUES (null, '2_21_24_28_37_38_39_50_55_68', '21.08.2066', 7777, 8888, 10);

SELECT * FROM new2010;

------------

SELECT id MAX(dannye), MIN(myData) FROM new2010 GROUP BY dannye HAVING COUNT(*) > 1

sudo cpan DBI

sudo apt-get install libdbi-perl

sudo apt-get install libdbd-mysql-perl

------------

select * from new2010 ORDER BY dannye;

DROP TABLE new2010;

TRUNCATE TABLE tabiz2010;

INSERT INTO tabiz2010 VALUES (null, "444", "555");

CREATE USER 'miltorg'@'localhost' IDENTIFIED BY '3333333333';

GRANT ALL PRIVILEGES ON * . * TO 'miltorg'@'localhost';

---------

CREATE DATABASE dbiz2010;
USE dbiz2010;

CREATE TABLE tabiz2010
(
id int(11) PRIMARY KEY auto_increment,
Dannye VARCHAR(40),
myData VARCHAR(12)
);

DROP TABLE new2010;

WITH MyScope AS (
SELECT id, Dannye, myData, COUNT(1) OVER (PARTITION BY Dannye) AS Cnt
FROM tabiz2010 )
SELECT * FROM MyScope WHERE Cnt>1;