Scribd
Upload
94 views25 pages

MySQL CommandLine

The document provides instructions for connecting to a MySQL database, creating databases and tables, inserting and modifying data, and creating stored procedures. It includes SQL syntax examples for connecting to a database, creating tables, inserting data, and creating stored procedures for inserting, updating, deleting, and retrieving data from tables.

Uploaded by

cuongdihoc
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd
94 views25 pages

MySQL CommandLine

The document provides instructions for connecting to a MySQL database, creating databases and tables, inserting and modifying data, and creating stored procedures. It includes SQL syntax examples for connecting to a database, creating tables, inserting data, and creating stored procedures for inserting, updating, deleting, and retrieving data from tables.

Uploaded by

cuongdihoc
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOC, PDF, TXT or read online on Scribd

1. Vo MySQL Command Line Client 2. G password: root (ng nhp vo h thng local MySQL server) 3.

Kt ni n database s dng hay chuyn database s dng Syntax: Connect [databasename]; Example: Connect dbstudent 4. Chuyn database s dng: Syntax: use [db name]; Example: use dbstudent; 5. To database Syntax: create database [databasename]; Example: Create database dbtest; 6. To table: Systax: CREATE TABLE [table name] ( firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35), suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp time, pgpemail VARCHAR(255) ); Example: CREATE TABLE tb_student ( firstname VARCHAR(20), middleinitial VARCHAR(3), lastname VARCHAR(35),

suffix VARCHAR(3), officeid VARCHAR(10), userid VARCHAR(15), username VARCHAR(8), email VARCHAR(35), phone VARCHAR(25), groups VARCHAR(15), datestamp DATE, timestamp time, pgpemail VARCHAR(255) ); create table tb_person ( personid int(50) not null auto_increment primary key, firstname varchar(35), middlename varchar(50), lastname varchar(50) default 'bato' ); 7. Thm d liu vo tables: Syntax: INSERT INTO [table name] (firstname, middlename, lastname) VALUES ('Nguyen', 'Tan', 'Dung'); Example: INSERT INTO tb_person (firstname, middlename, lastname) VALUES ('Nguyen', 'Tan', 'Dung'); 8. To Stored procedures: Example: /* delete procedure before to create the created-store */ DROP PROCEDURE IF EXISTS `person_insert`; DELIMITER ;; CREATE PROCEDURE `person_insert` (p_firstname varchar(35), p_middlename varchar(50), p_lastname varchar(50)) BEGIN INSERT INTO tb_person ( firstname, middlename, lastname ) VALUES ( p_firstname , p_middlename , p_lastname

); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `person_update`; DELIMITER ;; CREATE PROCEDURE `person_update` (p_personid int, p_firstname varchar(35), p_middlename varchar(50), p_lastname varchar(50)) BEGIN UPDATE tb_person SET firstname = p_firstname, middlename = p_middlename, lastname = p_lastname WHERE personid = p_personid; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `person_delete`; DELIMITER ;; CREATE PROCEDURE `person_delete` (p_personid int) BEGIN DELETE FROM tb_person WHERE personid = p_personid; END;; DELIMITER ; /* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `person_gets`; DELIMITER ;; CREATE PROCEDURE `person_gets` () BEGIN SELECT personid , firstname, middlename, lastname FROM tb_person; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */ DROP PROCEDURE IF EXISTS `person_get_by_id`;

DELIMITER ;; CREATE PROCEDURE `person_get_by_id` (p_personid int) BEGIN SELECT personid , firstname, middlename, lastname FROM tb_person WHERE personid = p_personid; END;; DELIMITER ;

Tao bang cho XOMS:


DROP TABLE IF EXISTS `OrganizationType`; create table OrganizationType ( ID int not null auto_increment primary key, Code varchar(10) not null COMMENT 'ghi chu', Name varchar(50) not null ); DROP TABLE IF EXISTS `Organization`; create table Organization ( ID int not null auto_increment primary key COMMENT 'The identifier of the table. It is auto generated.', Code varchar(10) not null COMMENT 'Each organization has a code. This code is unique. It is formatted by end-user', Name varchar(100) not null COMMENT 'The name of a organization. It is entered by end-user', ID_OrganizationType int not null COMMENT 'The ID of org. type ex: each org. belongs to a org. type. It is foreign key, linked to the ID field of OrganizationType table', ContactZipcode varchar(10) not null COMMENT 'Zip code of a organization. It is used for contact. Zip code of NL is formatted as ABCDXX', ContactStreet varchar(70) DEFAULT '' comment 'Street name of a organization located. It is used for contact.', ContactHousenumber varchar(15) not null comment 'House Number of a organization on street. It is used for contact.', ContactHousenumberExt varchar(15) not null comment 'House Number Ext of House Number of a organization on street. It is used for contact.', ContactCity varchar(50) default '' comment 'The city name that a organization locates. It is used for contact.', ContactPhone1 varchar(30) default '' comment 'The phone number of a organization. It is used for contact', ContactPhone2 varchar(30) default '' comment 'Other phone number of a organization. It is used for contact',

ContactEmail varchar(30) default '' comment 'The email of a organization. It is used for contact', BillingZipcode varchar(10) not null comment 'Zip code of a organization. It is used for billing. Zip code of NL is formatted as ABCDXX', BillingStreet varchar(70) default '' comment 'Street name of a organization located. It is used for billing.', BillingHousenumber varchar(15) not null comment 'House Number of a organization on street. It is used for billing.', BillingHousenumberExt varchar(15) not null comment 'House Number Ext of House Number of a organization on street. It is used for billing.', BillingCity varchar(50) default '' comment 'The city name that a organization locates. It is used for billing.', BillingPhone1 varchar(30) default '' comment 'The phone number of a organization. It is used for billing', BillingPhone2 varchar(30) default '' comment 'Other phone number of a organization. It is used for billing', BillingEmail varchar(30) default '' comment 'The email of a organization. It is used for billing', BankAccount varchar(15) not null comment 'The bank account number of a organzation. It is provided by end-user such as Reseller', BankName varchar(50) not null comment 'The bank account name of a organzation. It is provided by end-user such as Reseller' ); DROP TABLE IF EXISTS `Function`; create table Function ( ID int auto_increment primary key, Code varchar(25) not null, Name varchar(50) not null, Parent varchar(25), Type char(1) default 'E', ObjectName varchar(30), Parameter varchar(30), IsReport bit(1) ); DROP TABLE IF EXISTS `User`; create table User ( ID int auto_increment primary key, UserName varchar(15) not null, FirstName varchar(50) not null, LastName varchar(50) not null, Password varchar(200) default '', IsUser bit(1),

ID_Organization int, Email varchar(50) default '', IsSupperUser bit(1), LastIPAddress varchar(20), Active bit(1) ); DROP TABLE IF EXISTS `UserGroup`; create table UserGroup ( ID_User int not null comment'The ID of the User table. It is a PK, and also a FK, linked to the ID field of the User table', ID_Group int not null comment 'The ID of the User table, but type of the user is group. It is a PK, and also a FK, linked to the ID field of the User table', PRIMARY KEY (`ID_User`,`ID_Group`) ); DROP TABLE IF EXISTS `UserFunction`; create table UserFunction ( ID_User int not null comment 'The ID of the User table. It is a PK, and also a FK, linked to the ID field of the User table', ID_Function int not null comment 'The ID of the Function table. It is a PK, and also a FK, linked to the ID field of the Function table', `View` bit(1) comment '1: can view, 0: cannot view', `Add` bit(1) comment '1: can add, 0: cannot add', `Edit` bit(1) comment '1: can edit, 0: cannot edit', `Delete` bit(1) comment '1: can delete, 0: cannot delete', PRIMARY KEY (`ID_User`,`ID_Function`) ); DROP TABLE IF EXISTS `UserOrgazation`; create table UserOrgazation ( ID_User int not null comment 'The ID of the User table. It is a PK, and also a FK, linked to the ID field of the User table', ID_Organization int not null comment 'The ID of the Organization table. It is a PK, and also a FK, linked to the ID field of the Organization table', PRIMARY KEY (`ID_User`,`ID_Organization`) ); -- Phase II: DROP TABLE IF EXISTS `Addresses`; create table Addresses ( id int not null auto_increment primary key,

postal_code CHAR(6) not null, house_number VARCHAR(5) not null, house_number_extension VARCHAR(4), room VARCHAR(10), street VARCHAR(24) not null, city VARCHAR(24) not null, country VARCHAR(24) ); -- --------- DROP TABLE IF EXISTS `Customers`; create table Customers ( id int not null auto_increment primary key, contact_name VARCHAR(70) not null, company_name VARCHAR(70) not null, contact_phone_1 VARCHAR(10) not null, contact_phone_2 VARCHAR(10), delivery_address_id int not null, post_address_id int, billing_address_id int ); -- ------------DROP TABLE IF EXISTS `ConnectionStates`; create table ConnectionStates ( id int not null auto_increment primary key, name VARCHAR(15) not null UNIQUE KEY ); -- ------------DROP TABLE IF EXISTS `Connections`; create table Connections ( id int not null auto_increment primary key, connection_id CHAR(10) not null, customer_id int not null, connection_state_id int not null, date_planned DATETIME, date_started DATETIME, date_ended DATETIME, address_id int not null, technology_id int not null, service_type_id int not null, carrier_type int not null, service_level int not null, access_service_id VARCHAR(16), xdf_access_service_id VARCHAR(20),

service_instance_id VARCHAR(26), wap_area VARCHAR(12) ); DROP TABLE IF EXISTS `Suppliers`; create table Suppliers ( id int not null auto_increment primary key, name VARCHAR(70) not null UNIQUE KEY ); DROP TABLE IF EXISTS `AccessClasses`; create table AccessClasses ( id int not null auto_increment primary key, code VARCHAR(10) not null UNIQUE KEY, name VARCHAR(100) not null, supplier_id int not null ); DROP TABLE IF EXISTS `TechnologyTypes`; create table TechnologyTypes ( id int not null auto_increment primary key, name VARCHAR(15) not null unique key, access_class_id int not null ); DROP TABLE IF EXISTS `ServiceClasses`; create table ServiceClasses ( id int not null auto_increment primary key, code VARCHAR(10) not null unique key, name VARCHAR(70) not null, access_class_id INT not null ); DROP TABLE IF EXISTS `CarrierTypes`; create table CarrierTypes ( id int not null auto_increment primary key, name VARCHAR(15) not null unique key, technology_type_id INT not null );

DROP TABLE IF EXISTS `Products`; create table Products ( id int not null auto_increment primary key, product_code int not null unique key, product_name VARCHAR(150), pix VARCHAR(10), length INT not null, max_pir_down INT not null, max_pir_up INT not null, max_mir_down INT not null, max_mir_up INT not null, ov INT not null, qos VARCHAR(20), matching_line_max_1INT, matching_line_max_2INT, matching_line_min_1 INT, matching_line_min_2 INT, technology_type_id INT not null, is_active BIT ); DROP TABLE IF EXISTS `ProductParameters`; create table ProductParameters ( id int not null auto_increment primary key, service_class_id int not null, product_id int not null, wap_id VARCHAR(9), wap_vpi int not null, wap_vci int not null, eap_vpi int not null, eap_vci int not null, transport_instance_id VARCHAR(16), eap_vlan_id VARCHAR(4), inp_type VARCHAR(10) not null, vlan_tag VARCHAR(5) ); DROP TABLE IF EXISTS `OrderScenarios`; create table OrderScenarios ( id int not null auto_increment primary key, name VARCHAR(20) not null UNIQUE KEY );

DROP TABLE IF EXISTS `OrderTypes`; create table OrderTypes ( id int not null auto_increment primary key, name VARCHAR(25) not null UNIQUE KEY ); DROP TABLE IF EXISTS `ServiceLevels`; create table ServiceLevels ( id int not null auto_increment primary key, name VARCHAR(15) not null UNIQUE KEY ); DROP TABLE IF EXISTS `Portfolios`; create table Portfolios ( id int not null auto_increment primary key, name VARCHAR(15) not null UNIQUE KEY ); DROP TABLE IF EXISTS `MigrationScenarios`; create table MigrationScenarios ( id int not null auto_increment primary key, name VARCHAR(20) not null UNIQUE KEY ); DROP TABLE IF EXISTS `OrderStates`; create table OrderStates ( id int not null auto_increment primary key, name VARCHAR(30) not null UNIQUE KEY ); DROP TABLE IF EXISTS `SubOrderStates`; create table SubOrderStates ( id int not null auto_increment primary key, name VARCHAR(20) not null UNIQUE KEY ); DROP TABLE IF EXISTS `Orders`; create table Orders ( id int not null auto_increment primary key,

order_nr CHAR(26) not null UNIQUE KEY, reseller_order_nr VARCHAR(30) not null, cust_wish_date DATETIME, order_type_id int not null, note VARCHAR(100), customer_id int not null, order_scenario_id int not null, line_test_and_label BIT, isra_specs VARCHAR(85), outlet_required BIT not null, appointment_id VARCHAR(50), max_nl_type SMALLINT, access_instance_id VARCHAR(16), portfolio_id int not null, access_class_id int not null, technology_type_id int not null, carrier_type_id int not null, service_level_id int not null, product_class VARCHAR(50), product_type_id VARCHAR(5), reseller_id int not null, order_state int not null, connection_id int not null, order_date DATETIME, plan_date DATETIME , delivery_date DATETIME , nr_of_ip_addr DATETIME , migration_scenario_id int ); DROP TABLE IF EXISTS `DslSubOrders`; create table DslSubOrders ( id int not null auto_increment primary key, suborder_nr VARCHAR(30) not null UNIQUE KEY, foreign_suborder_nr VARCHAR(30) not null UNIQUE KEY, order_id int not null, order_nr CHAR(26), wish_date DATETIME not null, address_id int not null, order_type_id int not null, product_id int not null, suborder_state int not null, note VARCHAR(100), order_scenario_id int not null, line_test_and_label BIT,

isra_specs VARCHAR(85), outlet_required BIT not null, appointment_id VARCHAR(50), max_nl_type SMALLINT, access_instance_id VARCHAR(16), portfolio_id int not null, access_class_id int not null, technology_type_id int not null, carrier_type_id int not null, service_level_id int not null, product_class VARCHAR(50), product_type_id VARCHAR(5), connection_id int not null, issue_date DATETIME, plan_date DATETIME , delivery_date DATETIME , error_code int , migration_scenario_id int, planned_tcd DATETIME, planned_ocd DATETIME , access_service_id VARCHAR(20), xdf_access_service_id VARCHAR(20), service_instance_id VARCHAR(26), wap_area VARCHAR(12), wap_vlan_id VARCHAR(4), atm_topology_type VARCHAR(10), product_para_id int not null ); DROP TABLE IF EXISTS `IpSubOrders`; create table IpSubOrders ( id int not null auto_increment primary key, suborder_nr VARCHAR(30) not null UNIQUE KEY, order_id int not null, order_nr CHAR(26), start_ip_addr VARCHAR(32) not null, subnet_mask VARCHAR(32) not null, suborder_state int not null, nr_of_ip_addr INT not null, username VARCHAR(30) not null unique key, password VARCHAR(30) ); DROP TABLE IF EXISTS `ErrorDefinitions`; create table ErrorDefinitions

( error_code int not null auto_increment primary key, error_message VARCHAR(100) not null );

DROP PROCEDURE IF EXISTS `organizationtype_insert`; DELIMITER ;; CREATE PROCEDURE `organizationtype_insert` ( p_Code varchar(10), p_Name varchar(50) ) BEGIN INSERT INTO organizationtype ( Code, Name ) VALUES ( p_Code , p_Name ); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `organizationtype_update`; DELIMITER ;; CREATE PROCEDURE `organizationtype_update` ( p_ID int, p_Code varchar(10), p_Name varchar(50) ) BEGIN UPDATE organizationtype SET Code = p_Code, Name = p_Name WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `organizationtype_delete`; DELIMITER ;;

CREATE PROCEDURE `organizationtype_delete` (p_ID int) BEGIN DELETE FROM organizationtype WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `organizationtype_gets`; DELIMITER ;; CREATE PROCEDURE `organizationtype_gets` () BEGIN SELECT ID, Code, Name FROM organizationtype; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */ DROP PROCEDURE IF EXISTS `organizationtype_get_by_id`; DELIMITER ;; CREATE PROCEDURE `organizationtype_get_by_id` (p_ID int) BEGIN SELECT ID, Code, Name FROM organizationtype WHERE ID = p_ID; END;; DELIMITER ; ----------------- --------------DROP PROCEDURE IF EXISTS `organization_insert`; DELIMITER ;; CREATE PROCEDURE `organization_insert` ( p_Code varchar(10), p_Name varchar(100), p_ID_OrganizationType int, p_ContactZipcode varchar(10), p_ContactStreet varchar(70), p_ContactHousenumber varchar(15) , p_ContactHousenumberExt varchar(15), p_ContactCity varchar(50) , p_ContactPhone1 varchar(30) , p_ContactPhone2 varchar(30) , p_ContactEmail varchar(30) , p_BillingZipcode varchar(10) , p_BillingStreet varchar(70) , p_BillingHousenumber varchar(15) , p_BillingHousenumberExt varchar(15) , p_BillingCity varchar(50) , p_BillingPhone1 varchar(30) , p_BillingPhone2 varchar(30) , p_BillingEmail varchar(30) , p_BankAccount varchar(15) , p_BankName varchar(50) )

BEGIN INSERT INTO organization ( Code , Name , ID_OrganizationType , ContactZipcode , ContactStreet , ContactHousenumber , ContactHousenumberExt , ContactCity , ContactPhone1 , ContactPhone2 , ContactEmail , BillingZipcode , BillingStreet , BillingHousenumber , BillingHousenumberExt , BillingCity , BillingPhone1 , BillingPhone2 , BillingEmail , BankAccount , BankName ) VALUES ( p_Code , p_Name , p_ID_OrganizationType , p_ContactZipcode , p_ContactStreet , p_ContactHousenumber , p_ContactHousenumberExt , p_ContactCity , p_ContactPhone1 , p_ContactPhone2 , p_ContactEmail , p_BillingZipcode , p_BillingStreet , p_BillingHousenumber , p_BillingHousenumberExt , p_BillingCity , p_BillingPhone1 , p_BillingPhone2 , p_BillingEmail , p_BankAccount , p_BankName ); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `organization_update`; DELIMITER ;; CREATE PROCEDURE `organization_update` ( p_ID int, p_Code varchar(10), p_Name varchar(100), p_ID_OrganizationType int, p_ContactZipcode varchar(10), p_ContactStreet varchar(70), p_ContactHousenumber varchar(15) ,p_ContactHousenumberExt varchar(15), p_ContactCity varchar(50) , p_ContactPhone1 varchar(30) , p_ContactPhone2 varchar(30) , p_ContactEmail varchar(30) , p_BillingZipcode varchar(10) , p_BillingStreet varchar(70) , p_BillingHousenumber varchar(15) , p_BillingHousenumberExt varchar(15) , p_BillingCity varchar(50) , p_BillingPhone1 varchar(30) , p_BillingPhone2 varchar(30) , p_BillingEmail varchar(30) , p_BankAccount varchar(15) , p_BankName varchar(50) ) BEGIN UPDATE organization SET Code = p_Code, Name = p_Name, ID_OrganizationType = p_ID_OrganizationType, ContactZipcode = p_ContactZipcode, ContactStreet =p_ContactStreet,

ContactHousenumber = p_ContactHousenumber, ContactHousenumberExt = p_ContactHousenumberExt, ContactCity = p_ContactCity, ContactPhone1 =p_ContactPhone1 , ContactPhone2 = p_ContactPhone2, ContactEmail = p_ContactEmail, BillingZipcode = p_BillingZipcode, BillingStreet = p_BillingStreet , BillingHousenumber =p_BillingHousenumber , BillingHousenumberExt = p_BillingHousenumberExt , BillingCity = p_BillingCity , BillingPhone1 = p_BillingPhone1, BillingPhone2 = p_BillingPhone2, BillingEmail = p_BillingEmail, BankAccount = p_BankAccount, BankName = p_BankName WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `organization_delete`; DELIMITER ;; CREATE PROCEDURE `organization_delete` (p_ID int) BEGIN DELETE FROM organization WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `organization_gets`; DELIMITER ;; CREATE PROCEDURE `organization_gets` () BEGIN SELECT ID, Code , Name , ID_OrganizationType , ContactZipcode , ContactStreet , ContactHousenumber , ContactHousenumberExt , ContactCity , ContactPhone1 , ContactPhone2 , ContactEmail , BillingZipcode , BillingStreet , BillingHousenumber , BillingHousenumberExt , BillingCity , BillingPhone1 , BillingPhone2 , BillingEmail , BankAccount , BankName FROM organization; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */

DROP PROCEDURE IF EXISTS `organization_get_by_id`; DELIMITER ;; CREATE PROCEDURE `organization_get_by_id` (p_ID int) BEGIN SELECT ID, Code , Name , ID_OrganizationType , ContactZipcode , ContactStreet , ContactHousenumber , ContactHousenumberExt , ContactCity , ContactPhone1 , ContactPhone2 , ContactEmail , BillingZipcode , BillingStreet , BillingHousenumber , BillingHousenumberExt , BillingCity , BillingPhone1 , BillingPhone2 , BillingEmail , BankAccount , BankName FROM organization WHERE ID = p_ID; END;; DELIMITER ; -- --------------------------------DROP PROCEDURE IF EXISTS `function_insert`; DELIMITER ;; CREATE PROCEDURE `function_insert` ( p_Code varchar(25), p_Name varchar(50) , p_Parent varchar(25), p_Type char(1), p_ObjectName varchar(30), p_Parameter varchar(30), p_IsReport bit(1) ) BEGIN INSERT INTO function ( Code , Name , Parent , Type , ObjectName , Parameter , IsReport ) VALUES ( p_Code , p_Name , p_Parent , p_Type , p_ObjectName , p_Parameter , p_IsReport ); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `function_update`; DELIMITER ;;

CREATE PROCEDURE `function_update` ( p_ID int, p_Code varchar(25), p_Name varchar(50) , p_Parent varchar(25), p_Type char(1), p_ObjectName varchar(30), p_Parameter varchar(30), p_IsReport bit(1) ) BEGIN UPDATE function SET Code = p_Code, Name =p_Name , Parent = p_Parent, Type =p_Type , ObjectName =p_ObjectName, Parameter = p_Parameter, IsReport = p_IsReport WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `function_delete`; DELIMITER ;; CREATE PROCEDURE `function_delete` (p_ID int) BEGIN DELETE FROM function WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `function_gets`; DELIMITER ;; CREATE PROCEDURE `function_gets` () BEGIN SELECT ID, Code , Name , Parent , Type , ObjectName , Parameter , IsReport FROM function; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */ DROP PROCEDURE IF EXISTS `function_get_by_id`; DELIMITER ;; CREATE PROCEDURE `function_get_by_id` (p_ID int) BEGIN SELECT ID, Code , Name , Parent , Type , ObjectName , Parameter , IsReport FROM function WHERE ID = p_ID; END;;

DELIMITER ; ----------- --------------DROP PROCEDURE IF EXISTS `user_insert`; DELIMITER ;; CREATE PROCEDURE `user_insert` ( p_UserName varchar(15), p_FirstName varchar(50), p_LastName varchar(50), p_Password varchar(200), p_IsUser bit(1), p_ID_Organization int, p_Email varchar(50), p_IsSupperUser bit(1), p_LastIPAddress varchar(20), p_Active bit(1) ) BEGIN INSERT INTO user ( UserName , FirstName , LastName , Password , IsUser , ID_Organization , Email , IsSupperUser , LastIPAddress , Active ) VALUES ( p_UserName , p_FirstName , p_LastName , p_Password , p_IsUser , p_ID_Organization , p_Email , p_IsSupperUser , p_LastIPAddress , p_Active ); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `user_update`; DELIMITER ;; CREATE PROCEDURE `user_update` ( p_ID int, p_UserName varchar(15), p_FirstName varchar(50), p_LastName varchar(50), p_Password varchar(200), p_IsUser bit(1), p_ID_Organization int, p_Email varchar(50), p_IsSupperUser bit(1), p_LastIPAddress varchar(20), p_Active bit(1) ) BEGIN UPDATE user SET UserName = p_UserName, FirstName = p_FirstName, LastName = p_LastName, Password = p_Password, IsUser = p_IsUser, ID_Organization = p_ID_Organization, Email = p_Email,

IsSupperUser =p_IsSupperUser, LastIPAddress = p_LastIPAddress , Active = p_Active WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `user_delete`; DELIMITER ;; CREATE PROCEDURE `user_delete` (p_ID int) BEGIN DELETE FROM user WHERE ID = p_ID; END;; DELIMITER ; /* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `user_gets`; DELIMITER ;; CREATE PROCEDURE `user_gets` () BEGIN SELECT ID, UserName , FirstName , LastName , Password , IsUser , ID_Organization , Email , IsSupperUser , LastIPAddress , Active FROM user; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */ DROP PROCEDURE IF EXISTS `user_get_by_id`; DELIMITER ;; CREATE PROCEDURE `user_get_by_id` (p_ID int) BEGIN SELECT ID, UserName , FirstName , LastName , Password , IsUser , ID_Organization , Email , IsSupperUser , LastIPAddress , Active FROM user WHERE ID = p_ID; END;; DELIMITER ;

-- --------------------DROP PROCEDURE IF EXISTS `usergroup_insert`; DELIMITER ;; CREATE PROCEDURE `usergroup_insert` ( p_ID_User int, p_ID_Group int ) BEGIN INSERT INTO usergroup ( ID_User , ID_Group ) VALUES ( p_ID_User , p_ID_Group ); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `usergroup_update`; DELIMITER ;; CREATE PROCEDURE `usergroup_update` ( p_ID_User int, p_ID_Group int ) BEGIN UPDATE usergroup SET ID_User = p_ID_User, ID_Group = p_ID_Group WHERE ID_User = p_ID_User and ID_Group = p_ID_Group; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `usergroup_delete`; DELIMITER ;; CREATE PROCEDURE `usergroup_delete` (p_ID_User int, p_ID_Group int ) BEGIN DELETE FROM usergroup WHERE ID_User = p_ID_User and ID_Group = p_ID_Group; END;; DELIMITER ;

/* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `usergroup_gets`; DELIMITER ;; CREATE PROCEDURE `usergroup_gets` () BEGIN SELECT ID_User , ID_Group FROM usergroup; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */ DROP PROCEDURE IF EXISTS `usergroup_get_by_id`; DELIMITER ;; CREATE PROCEDURE `usergroup_get_by_id` (p_ID_User int, p_ID_Group int) BEGIN SELECT ID_User , ID_Group FROM usergroup WHERE ID_User = p_ID_User and ID_Group = p_ID_Group; END;; DELIMITER ; -- ------------------------DROP PROCEDURE IF EXISTS `userfunction_insert`; DELIMITER ;; CREATE PROCEDURE `userfunction_insert` ( p_ID_User int , p_ID_Function int , p_View bit(1) , p_Add bit(1) , p_Edit bit(1) , p_Delete bit(1) ) BEGIN INSERT INTO userfunction ( ID_User , ID_Function , `View` , `Add` , `Edit` , `Delete` ) VALUES ( p_ID_User , p_ID_Function , p_View , p_Add , p_Edit , p_Delete ); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `userfunction_update`;

DELIMITER ;; CREATE PROCEDURE `userfunction_update` ( p_ID_User int , p_ID_Function int , p_View bit(1) , p_Add bit(1) , p_Edit bit(1) , p_Delete bit(1) ) BEGIN UPDATE userfunction SET ID_User =p_ID_User , ID_Function = p_ID_Function , `View` = p_View , `Add` = p_Add , `Edit`= p_Edit , `Delete` = p_Delete WHERE ID_User = p_ID_User and ID_Function = p_ID_Function; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `userfunction_delete`; DELIMITER ;; CREATE PROCEDURE `userfunction_delete` ( p_ID_User int , p_ID_Function int ) BEGIN DELETE FROM userfunction WHERE ID_User = p_ID_User and ID_Function = p_ID_Function; END;; DELIMITER ; /* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `userfunction_gets`; DELIMITER ;; CREATE PROCEDURE `userfunction_gets` () BEGIN SELECT ID_User , ID_Function , `View` , `Add` , `Edit` , `Delete` FROM userfunction; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */ DROP PROCEDURE IF EXISTS `userfunction_get_by_id`; DELIMITER ;; CREATE PROCEDURE `userfunction_get_by_id` (p_ID int) BEGIN SELECT ID_User , ID_Function , `View` , `Add` , `Edit` , `Delete` FROM userfunction WHERE ID_User = p_ID_User and ID_Function = p_ID_Function;

END;; DELIMITER ; -- ------------------DROP PROCEDURE IF EXISTS `userorgazation_insert`; DELIMITER ;; CREATE PROCEDURE `userorgazation_insert` ( p_ID_User int, p_ID_Organization int ) BEGIN INSERT INTO userorgazation ( ID_User , ID_Organization ) VALUES ( p_ID_User , p_ID_Organization ); END;; DELIMITER ; /* delete procedure before to create the updated-store */ DROP PROCEDURE IF EXISTS `userorgazation_update`; DELIMITER ;; CREATE PROCEDURE `userorgazation_update` ( p_ID_User int, p_ID_Organization int ) BEGIN UPDATE userorgazation SET ID_User = p_ID_User, ID_Organization = p_ID_Organization WHERE ID_User = p_ID_User and ID_Organization = p_ID_Organization; END;; DELIMITER ; /* delete procedure before to create the deleted-store */ DROP PROCEDURE IF EXISTS `userorgazation_delete`; DELIMITER ;; CREATE PROCEDURE `userorgazation_delete` (p_ID_User int, p_ID_Organization int ) BEGIN

DELETE FROM userorgazation WHERE ID_User = p_ID_User and ID_Organization = p_ID_Organization; END;; DELIMITER ; /* delete procedure before to create the gets-store */ DROP PROCEDURE IF EXISTS `userorgazation_gets`; DELIMITER ;; CREATE PROCEDURE `userorgazation_gets` () BEGIN SELECT ID_User , ID_Organization FROM userorgazation; END;; DELIMITER ; /* delete procedure before to create the get_by_id-store */ DROP PROCEDURE IF EXISTS `userorgazation_get_by_id`; DELIMITER ;; CREATE PROCEDURE `userorgazation_get_by_id` (p_ID_User int, p_ID_Organization int) BEGIN SELECT ID_User , ID_Organization FROM userorgazation WHERE ID_User = p_ID_User and ID_Organization = p_ID_Organization; END;; DELIMITER ; -- ------------------------------

You might also like