Mysql Basic

create table cats(
    id int primary key, 
    name varchar(100), 
    age int);

create table cats(
    id int,
    name varchar(100),
    age int,
    primary key(id)
);

SELECT  b.table_name, b.table_comment, a.column_name, a.column_type,a.column_comment
FROM information_schema.columns a
JOIN information_schema.tables b
ON a.table_schema =b.table_schema AND a.table_name =b.table_name
WHERE a.table_name='pets';

SELECT 1 + 1 FROM DUAL;
SELECT 1 + 1;
-- FROM DUAL could be used when you only SELECT computed values, but require a WHERE clause, perhaps to test that a script correctly handles empty resultsets:
SELECT 1 FROM DUAL WHERE FALSE;

--查询变量设置
show variables like '%connect_timeout%'; --默认 session 变量
show local variables like '%connect_timeout%';
show session variables like '%connect_timeout%';
show global variables like '%connect_timeout%';

--全局变量设置
SET GLOBAL log_bin_trust_function_creators=1;
SET @@global.log_bin_trust_function_creators=1;
--sesseion 变量设置
SET log_bin_trust_function_creators=1; --默认 session 变量
SET SESSION log_bin_trust_function_creators=1;
SET @@session.log_bin_trust_function_creators=1;

--自定义变量
set @test="test";
select @test;

insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)

mysql> select concat('My', NULL, 'SQL');
+---------------------------+
| concat('My', NULL, 'SQL') |
+---------------------------+
| NULL                      |
+---------------------------+
1 row in set (0.00 sec)

mysql> select concat('My', '', 'SQL');   
+-------------------------+
| concat('My', '', 'SQL') |
+-------------------------+
| MySQL                   |
+-------------------------+
1 row in set (0.00 sec)

mysql> select concat_ws(',', 'name', 'age', 'height'); 
+-----------------------------------------+
| concat_ws(',', 'name', 'age', 'height') |
+-----------------------------------------+
| name,age,height                         |
+-----------------------------------------+
1 row in set (0.00 sec)

mysql> select length('你好'),char_length('你好'); 
+----------------+---------------------+
| length('你好') | char_length('你好') |
+----------------+---------------------+
|              4 |                   2 |
+----------------+---------------------+

MySQL root@localhost:book_shop> select curdate(), curtime(), now();
+------------+-----------+---------------------+
| curdate()  | curtime() | now()               |
+------------+-----------+---------------------+
| 2024-04-19 | 16:30:51  | 2024-04-19 16:30:51 |

MySQL root@localhost:book_shop>  CREATE TABLE `parent` (
                            ->    `children` tinyint(3) unsigned DEFAULT NULL
                            ->  );
Query OK, 0 rows affected
Time: 0.484s
MySQL root@localhost:book_shop> insert into parent values (-3);
(1264, "Out of range value for column 'children' at row 1")

create table parent (
   children number, 
   constraint parent_unsigend_tinyint check (children between 0 and 255)
);

insert into parent values (-3); --ORA-02290: check constraint (APPS.PARENT_UNSIGEND_TINYINT) violated

CREATE TEMPORARY TABLE emp(
   empno  varchar(10),
   ename  varchar(100),
   deptno varchar(10)
);

insert into emp values ('10', 'Babb', '10');
insert into emp values ('20', 'Julian', '10');
insert into emp values ('30', 'Owen', '10');
insert into emp values ('40', 'Yoyo', '20');
insert into emp values ('50', 'Sunny', '20');

select deptno, group_concat(ename), count(*)
  from emp
 group by deptno;

+--------+---------------------+----------+
| deptno | group_concat(ename) | count(*) |
+--------+---------------------+----------+
| 10     | Babb,Julian,Owen    |        3 |
| 20     | Yoyo,Sunny          |        2 |
+--------+---------------------+----------+
2 rows in set (0.00 sec)

WITH cte (col1, col2) AS
(
  SELECT 1, 2
  UNION ALL
  SELECT 3, 4
)
SELECT col1, col2 FROM cte;


with cte as
(
	select 1 as col1, 2 as col2
    union all
    select 3, 4
)
select col1, col2 from cte;

with cte1 as (select 1)
select * from (with cte2 as (select 2) select * from cte2 join cte1) as dt;


with recursive cte(n) as
(
	select 1
    union all
    select n + 1 from cte where n < 5
)
select * from cte;

-- Error Code: 1406. Data too long for column 'str' at row 1
with recursive cte as
(
	select 1 as n, 'abc' as str
    union all
    select n + 1, concat(str, str) from cte where n < 3
)
select * from cte;


with recursive cte as
(
	select 1 as n, cast('abc' as char(20)) str
    union all
    select n + 1, concat(str, str) from cte where n < 3
)
select * from cte;


with recursive fibonacci(n, fib_n, next_fib_n) as
(
	select 1, 0, 1
    union all
    select n + 1, next_fib_n, fib_n + next_fib_n from fibonacci where n < 10
)
select * from fibonacci where n = 8;



with recursive dates (date) as
(
	select sysdate()
    union all
    select date + interval 1 day from dates
    where date <= sysdate() + interval 10 day

)
select * from dates;


use test;
CREATE TABLE employees (
  id         INT PRIMARY KEY NOT NULL,
  name       VARCHAR(100) NOT NULL,
  manager_id INT NULL,
  INDEX (manager_id),
FOREIGN KEY (manager_id) REFERENCES employees (id)
);
INSERT INTO employees VALUES
(333, "Yasmina", NULL),  # Yasmina is the CEO (manager_id is NULL)
(198, "John", 333),      # John has ID 198 and reports to 333 (Yasmina)
(692, "Tarek", 333),
(29, "Pedro", 198),
(4610, "Sarah", 29),
(72, "Pierre", 29),
(123, "Adil", 692);

select * from employees;

with recursive employee_paths (id, name, path)  as
(
	select id, name, cast(id as char(200))
    from employees
    where manager_id is null
    union all
    select e.id, e.name, concat(ep.path, ',', e.id)
    from employee_paths as ep join employees as e
    on ep.id = e.manager_id
)
select * from employee_paths order by path;

create table cats(
    id int primary key auto_increment,
    name varchar(100),
    age int
);

insert into
    cats (name, age)
values
    ('Kitty', 3);

insert into
    cats (name, age)
values
    ('Tom', 3);