mVqKLjMB

1
2USE test;
3
4CREATE DATABASE newdb;
5USE newdb;
6
7CREATE TABLE table1
8(
9	id INT
10);
11table_task
12INSERT INTO table1 (id) VALUES(1);
13SELECT * FROM table1;
14INSERT INTO table2 (id) VALUES(NULL);
15
16CREATE TABLE table2
17(
18	id INT NOT NULL
19);
20
21
22CREATE TABLE table3
23(
24	id INT NOT NULL PRIMARY KEY
25);
26
27INSERT INTO table3 (id) VALUES(2);
28SELECT * FROM table3;
29INSERT INTO table3 (id) VALUES(NULL);
30INSERT INTO table3 () VALUES();
31
32CREATE TABLE table4
33(
34	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
35);
36
37INSERT INTO table4 (id) VALUES(7);
38SELECT * FROM table4;
39INSERT INTO table4 (id) VALUES(NULL);
40INSERT INTO table4 () VALUES();
41
42SELECT UUID() FROM dual;
43
44CREATE TABLE table5
45(
46	id CHAR(36) NOT NULL PRIMARY KEY
47);
48
49INSERT INTO table5 (id) VALUES(UUID());
50SELECT * FROM table5;
51INSERT INTO table5 (id) VALUES(NULL);
52INSERT INTO table5 () VALUES();
53SELECT last_insert_id();
54
55SHOW TABLES;
56SHOW CREATE TABLE table5;
57
58CREATE TABLE `table6` (
59  `id` char(36) NOT NULL,
60  PRIMARY KEY (`id`)
61  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
62
63SHOW CREATE TABLE new_table;
64
65CREATE TABLE `new_table` (
66 `ID` int(11) NOT NULL,
67 `TEKST` varchar(45) COLLATE utf8_polish_ci DEFAULT NULL,
68 PRIMARY KEY (`ID`)
69 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
70 
71 INSERT INTO new_table VALUES(1,'ASFFSA');
72 SELECT * FROM new_table;
73 
74 SELECT UTC_DATE();
75 SELECT NOW()+10000;
76 
77 CREATE TABLE table_task
78(
79	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
80    user_name varchar(20),
81    user_surname varchar(30),
82    birthdate DATE
83)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;
84
85SELECT * FROM table_task;
86SELECT * FROM table_task WHERE user_name='MARK';
87INSERT INTO table_task VALUE(4,'ANDREW','LOU','1901-01-02');
88SELECT * FROM table_task ORDER BY user_name;
89SELECT user_name, count(*), min(id) FROM table_task GROUP BY user_name HAVING count(*) > 1;
90
91
92
93
94
95/* LABY 2 */
96
97USE newdb;
98
99CREATE TABLE parent
100(
101id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
102parent_description VARCHAR(20)
103);
104
105
106drop table parent;
107SHOW TABLES LIKE '%PARENT%';
108
109CREATE TABLE child
110(
111id INT NOT NULL primary key auto_increment,
112child_description VARCHAR(20),
113id_parent INT,
114constraint fk_parent_child foreign key (id_parent) references parent(id)
115);
116
117insert into parent  values (1,'parent 1');
118insert into parent values (2, 'parent 2');
119insert into parent values (3, 'parent 3');
120
121SELECT * FROM parent;
122SELECT * FROM child;
123
124insert into child  values (NULL, 'dziecko 4', 2);
125
126delete from parent where id = 2;
127delete from parent where id = 1;
128delete from child where id = 1;
129
130insert into child values(NULL, 'dziecko 3', NULL);
131
132select * from parent, child;
133select * from parent p, child c where p.id=c.id_parent;
134select * from parent p left join child c on p.id=c.id_parent;
135
136SELECT 2
137union
138select 3
139union all
140select 2
141union all
142select 2;
143
144select c.id, id_parent from parent p right join child c on p.id=c.id_parent
145union
146select c.id, id_parent from parent p left join child c on p.id=c.id_parent;
147
148############################################################################################################
149############################################################################################################
150############################################################################################################
151
152-- MySQL Script generated by MySQL Workbench
153-- Thu Oct 10 10:19:55 2019
154-- Model: New Model    Version: 1.0
155-- MySQL Workbench Forward Engineering
156
157SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
158SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
159SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
160
161-- -----------------------------------------------------
162-- Schema mydb
163-- -----------------------------------------------------
164
165-- -----------------------------------------------------
166-- Schema mydb
167-- -----------------------------------------------------
168CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
169USE `mydb` ;
170
171-- -----------------------------------------------------
172-- Table `mydb`.`parent_g`
173-- -----------------------------------------------------
174CREATE TABLE IF NOT EXISTS `mydb`.`parent_g` (
175  `idtable1` INT NOT NULL,
176  `parent_g_des` VARCHAR(45) NULL,
177  PRIMARY KEY (`idtable1`))
178ENGINE = InnoDB;
179
180
181-- -----------------------------------------------------
182-- Table `mydb`.`child_g`
183-- -----------------------------------------------------
184CREATE TABLE IF NOT EXISTS `mydb`.`child_g` (
185  `idchild_g` INT NOT NULL AUTO_INCREMENT,
186  `child_g_des` VARCHAR(45) NULL,
187  `parent_g_idtable1` INT NOT NULL,
188  PRIMARY KEY (`idchild_g`),
189  INDEX `fk_child_g_parent_g_idx` (`parent_g_idtable1` ASC),
190  CONSTRAINT `fk_child_g_parent_g`
191    FOREIGN KEY (`parent_g_idtable1`)
192    REFERENCES `mydb`.`parent_g` (`idtable1`)
193    ON DELETE cascade
194    ON UPDATE NO ACTION)
195ENGINE = InnoDB;
196drop table child_g;
197
198insert into parent_g values(1,'r 1');
199insert into child_g values(NULL, ' d 1',1);
200delete from parent_g where idtable1=1;
201select * from child_g;
202select * from parent_g;
203
204
205
206SET SQL_MODE=@OLD_SQL_MODE;
207SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
208SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
209
210############################################################################################################
211############################################################################################################
212############################################################################################################
213
214-- MySQL Script generated by MySQL Workbench
215-- Thu Oct 10 10:20:56 2019
216-- Model: New Model    Version: 1.0
217-- MySQL Workbench Forward Engineering
218
219SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
220SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
221SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
222
223-- -----------------------------------------------------
224-- Schema mydb
225-- -----------------------------------------------------
226
227-- -----------------------------------------------------
228-- Schema mydb
229-- -----------------------------------------------------
230CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
231USE `mydb` ;
232
233-- -----------------------------------------------------
234-- Table `mydb`.`parent_g`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `mydb`.`parent_g` (
237  `idtable1` INT NOT NULL,
238  `parent_g_des` VARCHAR(45) NULL,
239  PRIMARY KEY (`idtable1`))
240ENGINE = InnoDB;
241
242
243-- -----------------------------------------------------
244-- Table `mydb`.`child_g`
245-- -----------------------------------------------------
246CREATE TABLE IF NOT EXISTS `mydb`.`child_g` (
247  `idchild_g` INT NOT NULL AUTO_INCREMENT,
248  `child_g_des` VARCHAR(45) NULL,
249  `parent_g_idtable1` INT NOT NULL,
250  PRIMARY KEY (`idchild_g`, `parent_g_idtable1`),
251  INDEX `fk_child_g_parent_g_idx` (`parent_g_idtable1` ASC) VISIBLE,
252  CONSTRAINT `fk_child_g_parent_g`
253    FOREIGN KEY (`parent_g_idtable1`)
254    REFERENCES `mydb`.`parent_g` (`idtable1`)
255    ON DELETE NO ACTION
256    ON UPDATE NO ACTION)
257ENGINE = InnoDB;
258
259
260SET SQL_MODE=@OLD_SQL_MODE;
261SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
262SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
263
264
265############################################################################################################
266############################################################################################################
267############################################################################################################
268
269create table tst(
270id INT(10),
271text_ text,
272small_tx varchar(3),
273const_tx char(3),
274float_ float,
275decim DECIMAL(4,2)
276);
277insert into tst values(1,'asasfasdsdsdsd','231','1',2.53,12.4242);
278
279select * from tst;
280
281############################################################################################################
282############################################################################################################
283############################################################################################################
284
285
286
287
288############################################################################################################
289############################################################################################################
290############################################################################################################