winone1208

BazyLab

Mar 20th, 2021 (edited)
375
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Nie księgarnia, wypożyczalnia płyt, biblioteka
  2. =====================================================
  3.  
  4. mysql.exe -p -u root /*logowanie*/
  5.  
  6. show databases; /*wyświetlanie dostępnych baz danych*/
  7. create database wspa; /*tworzenie bazy danych*/
  8. show databases; /*wyświetlanie dostępnych baz danych*/
  9. use wspa /*wybieranie bazy danych*/
  10. show tables; /*wyświetlanie dostępnych tabeli w bazie danych*/
  11. create table kontakty (id INT, numer_tel INT, mail VARCHAR(40), numer_domu SMALLINT); /*tworzenie tabeli*/
  12. insert into kontakty (id,numer_tel,mail,numer_domu) values ('1','652478524','asystent@wspa.pl','52'); /*wpisywanie danych do tabeli*/
  13. describe kontakty; /*właściwości tabeli*/
  14. select * from kontakty; /*wyświetlanie danych z tabeli*/
  15.  
  16. select user(), current_date; /* Wyświetla nazwę użytkownika bazy danych i aktualną datę */
  17.  
  18. create table student (id INT, firstname VARCHAR(40), country VARCHAR(40));  /*tworzenie tabeli*/
  19. insert into student values('1','jack','Poland');  /*wpisywanie danych do tabeli*/
  20. insert into student values('2','mary','Great Britan');
  21. insert into student values('3','nichole','Indie');
  22. insert into student values('4','angie','Ukraina');
  23. insert into student values('5','jones','Germany');
  24. insert into student values('6','newperson','NULL');
  25. insert into student values('7','kamil','Poland');
  26. insert into student values('7','janusz','Poland');
  27. insert into student values('9','wolfgang','Germany');
  28. insert into student values('10','roksana','Ukraina');
  29. insert into student values('11','adam','Great Britan');
  30. insert into student values('12','alex','Indie');
  31. insert into student values('13','ingi','Ukraina');
  32. insert into student values('14','adolf','Germany');
  33. insert into student values('15','andrzej','Poland');
  34. insert into student values('16','wojtek','Poland');
  35. insert into student values('17','marcin','Poland');
  36. insert into student values('18','agnieszka','Poland');
  37.  
  38. select * from student; /*wyświetla tabele*/
  39. SELECT COUNT(country) FROM student WHERE country='Poland'; /*liczy wystąpienia Poland w tabeli country*/
  40.  
  41. SELECT country, COUNT(*) AS liczba_studentów FROM student GROUP BY country ORDER BY COUNT(*); /*liczy studentów z każdego kraju*/
  42.  
  43. SELECT country, COUNT(*) AS liczba_studentów FROM student GROUP BY country ORDER BY COUNT(*) DESC; /*liczy studentów z każdego kraju i sortuje malejąco*/
  44.  
  45. CREATE DATABASE testDB;
  46. DROP DATABASE testDB;
  47. CREATE DATABASE testDB;
  48. use testDB
  49. CREATE TABLE Persons (
  50.     PersonID int,
  51.     LastName varchar(255),
  52.     FirstName varchar(255),
  53.     Address varchar(255),
  54.     City varchar(255)
  55. );
  56. DROP TABLE Persons;
  57. CREATE TABLE Persons (
  58.     PersonID int,
  59.     LastName varchar(255),
  60.     FirstName varchar(255),
  61.     Address varchar(255),
  62.     City varchar(255)
  63. );
  64. ALTER TABLE Persons ADD Email varchar(255); /*dodawanie kolumny*/
  65. CREATE TABLE Persons2 (
  66.      ID int NOT NULL,
  67.      LastName varchar(255) NOT NULL,
  68.     FirstName varchar(255) NOT NULL,
  69.     Age int
  70. );
  71. CREATE TABLE Persons3 (
  72.      ID int NOT NULL PRIMARY KEY,
  73.      LastName varchar(255) NOT NULL,
  74.      FirstName varchar(255) NOT NULL,
  75.      Age int
  76. );
  77. SELECT * FROM Persons3;
  78. SELECT DISTINCT Age FROM Persons3;
  79. INSERT INTO Persons3 VALUES('1', 'Nowak', 'Jacek', '25');
  80. SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
  81.  
  82.  
  83.  
  84.  
  85.  
  86.  
  87.  
  88.  
  89. show databases;
  90. use wspa;
  91. show tables;
  92. CREATE TABLE countries(
  93. COUNTRY_ID varchar(2),
  94. COUNTRY_NAME varchar(40),
  95. REGION_ID decimal(10,0)
  96. );
  97. show tables;
  98. DESC countries;
  99. CREATE TABLE IF NOT EXISTS dup_countries LIKE countries;
  100. DESC dup_countries;
  101.  
  102. CREATE TABLE IF NOT EXISTS departments (
  103. DEPARTMENT_ID decimal(4,0) NOT NULL DEFAULT 0,
  104. DEPARTMENT_NAME varchar(30) NOT NULL,
  105. MANAGER_ID decimal(6,0) NOT NULL DEFAULT 0,
  106. LOCATION_ID decimal(4,0)  DEFAULT NULL,
  107. PRIMARY KEY (DEPARTMENT_ID, MANAGER_ID)
  108. );
  109. DESC departments;
  110.  
  111. CREATE TABLE IF NOT EXISTS employees (
  112. EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
  113. FIRST_NAME varchar(20) DEFAULT NULL,
  114. LAST_NAME varchar(25) NOT NULL,
  115. EMAIL varchar(25) NOT NULL,
  116. PHONE_NUMBER varchar(20) DEFAULT NULL,
  117. HIRE_DATE date NOT NULL,
  118. JOB_ID varchar(10) NOT NULL,
  119. SALARY decimal(8,2) DEFAULT NULL,
  120. COMMISSION_PCT decimal(2,2) DEFAULT NULL,
  121. MANAGER_ID decimal(6,0) DEFAULT NULL,
  122. DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
  123. FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID)
  124. REFERENCES  departments(DEPARTMENT_ID,MANAGER_ID)
  125. );
  126. desc employees;
  127. DROP TABLE EMPLOYEES;
  128.  
  129. CREATE TABLE IF NOT EXISTS jobs (
  130. JOB_ID integer NOT NULL UNIQUE PRIMARY KEY,
  131. JOB_TITLE varchar(35) NOT NULL DEFAULT ' ',
  132. MIN_SALARY decimal(6,0) DEFAULT 8000,
  133. MAX_SALARY decimal(6,0) DEFAULT NULL
  134. )ENGINE=InnoDB;
  135. desc jobs;
  136.  
  137. CREATE TABLE IF NOT EXISTS employees (
  138. EMPLOYEE_ID decimal(6,0) NOT NULL PRIMARY KEY,
  139. FIRST_NAME varchar(20) DEFAULT NULL,
  140. LAST_NAME varchar(25) NOT NULL,
  141. JOB_ID INTEGER NOT NULL,
  142. SALARY decimal(8,2) DEFAULT NULL,
  143. FOREIGN KEY(JOB_ID)
  144. REFERENCES  jobs(JOB_ID)
  145. ON DELETE CASCADE ON UPDATE RESTRICT
  146. )ENGINE=InnoDB;
  147. DESC employees;
  148.  
  149. DROP table countries;
  150. CREATE TABLE IF NOT EXISTS countries (
  151. COUNTRY_ID varchar(2) NOT NULL UNIQUE PRIMARY KEY,
  152. COUNTRY_NAME varchar(40) NOT NULL,
  153. REGION_ID decimal(10,0) NOT NULL
  154. );
  155. INSERT INTO countries VALUES('C1','India',1001);
  156. SELECT * FROM countries;
  157.  
  158. CREATE TABLE IF NOT EXISTS country_new AS SELECT * FROM countries;
  159. SHOW COLUMNS FROM country_new;
  160. SELECT * FROM country_new;
  161.  
  162. INSERT INTO jobs VALUES(1001,'OFFICER',8000);
  163.  
  164. CREATE TABLE IF NOT EXISTS JOBS2 (
  165. JOB_ID integer NOT NULL UNIQUE ,
  166. JOB_TITLE varchar(35) NOT NULL,
  167. MIN_SALARY decimal(6,0)
  168. );
  169. INSERT INTO JOBS2 VALUES(1001,'OFFICER',8000);
  170. SELECT * FROM JOBS2;
  171.  
  172. CREATE TABLE IF NOT EXISTS departments (
  173. DEPARTMENT_ID integer NOT NULL UNIQUE,
  174. DEPARTMENT_NAME varchar(30) NOT NULL,
  175. MANAGER_ID integer ,
  176. LOCATION_ID integer DEFAULT NULL,
  177. PRIMARY KEY (DEPARTMENT_ID,MANAGER_ID)
  178. );
  179.  
  180. INSERT INTO departments VALUES(60,'SALES',201,89);
  181. INSERT INTO departments VALUES(61,'ACCOUNTS',201,89);
  182. INSERT INTO departments VALUES(80,'FINANCE',211,90);
  183.  
  184. SELECT * FROM departments;
  185.  
  186. CREATE TABLE IF NOT EXISTS departments3 (
  187. DEPARTMENT_ID integer NOT NULL UNIQUE,
  188. DEPARTMENT_NAME varchar(30) NOT NULL,
  189. MANAGER_ID integer ,
  190. LOCATION_ID integer DEFAULT NULL,
  191. PRIMARY KEY (DEPARTMENT_ID,MANAGER_ID)
  192. );
  193.  
  194. INSERT INTO departments3 VALUES(60,'SALES',201,89);
  195. INSERT INTO departments3 VALUES(61,'ACCOUNTS',201,89);
  196. INSERT INTO departments3 VALUES(80,'FINANCE',211,90);
  197.  
  198. SELECT * FROM departments3;
  199.  
  200. CREATE TABLE IF NOT EXISTS employees3 (
  201. EMPLOYEE_ID integer NOT NULL PRIMARY KEY,
  202. FIRST_NAME varchar(20) DEFAULT NULL,
  203. LAST_NAME varchar(25) NOT NULL,
  204. JOB_ID varchar(10) NOT NULL,
  205. SALARY decimal(8,2) DEFAULT NULL,
  206. MANAGER_ID integer NOT NULL,
  207. DEPARTMENT_ID integer NOT NULL,
  208. FOREIGN KEY(DEPARTMENT_ID,MANAGER_ID)
  209. REFERENCES  departments3(DEPARTMENT_ID,MANAGER_ID)
  210. );
  211.  
  212. INSERT INTO employees3 VALUES(510,'Alex','Hanes','CLERK',18000,201,60);
  213. INSERT INTO employees3 VALUES(511,'Kim','Leon','CLERK',18000,211,80);
  214.  
  215.  SELECT * FROM employees3;
  216.  
  217.  
  218.  
  219.  
  220. SOURCE C:/temp/sakila-db/sakila-schema.sql;
  221. SOURCE C:/temp/sakila-db/sakila-data.sql;
RAW Paste Data

Adblocker detected! Please consider disabling it...

We've detected AdBlock Plus or some other adblocking software preventing Pastebin.com from fully loading.

We don't have any obnoxious sound, or popup ads, we actively block these annoying types of ads!

Please add Pastebin.com to your ad blocker whitelist or disable your adblocking software.

×