Pe4ywka
Database Lab To allow access to the information in a database, SQL defines the reserved words(operations, functions and commands) of the language which indicates the actions to be performed on the database. These words are described by Data Definition Language (DDL) and Data Manipulation Language (DML).1.2.4 DDL CommandsCREATE : is used to create new tables, fields and indexes.DROP : is used to delete tables and indexes from the databases.ALTER : is used to modify tables by adding fields or changing field definitions.1.2.5 DML CommandsSELECT : is used to query the database.INSERT : is used to load data into the database.UPDATE :is used to change the values of particular records and fields.DELETE :is used to remove records from a database.*Difference between Alter and Update SQL :Alter : is used to update the structure of the table (add/remove field/index etc.). UPDATE: is used to update data.*Difference between DROP and DELETE and Truncate for tables :DROP : deleting the table and its structure from the data base.DROP TABLE tbl_name ;DELETE : used for deleting the records from the table, and it removing the table space which is allocated by the data base, and returns number of rows deleted.DELETE FROM tbl_name WHERE id = 1 ;TRUNCATE : is also delete the records but it doesn't delete the table space which is created by the database, and does not return number of deleted rows.TRUNCATE TABLE tbl_name ;1.2.7 Why choose MySQL?1. Speed. Developers claims it's the fastest(http//:benchmark.html)2. Ease of Use Simple administrations and installations3. Its free4. Query Language Support Supports SQL5. Capability6. Connectivity and Security Accessed from where ever you are and hasaccess control7. Portability Run on Linux, Unix, Solaris , FreeBSD , Windows , etc.8. Open sourceNote:If you installed the MySQL in the PC thatyou are currently using, then we called it "LOCALLY HOSTED" or if the MySQL isinstalled in a machine other than you are currently working on, it's called "REMOTELYHOSTED " .1.3.2 Step 2: Starting the server Open a command prompt and change your directory toC:\wamp\mysql\bin\mysql5.6.17\bin1.3.3 Step 3: Connecting to MySQL*Find The server machine IPWe can find it from Wi-Fi status then choose details *Turn off Firewall in server *Create a user in server *Ping from client machine to server machineping id (For server)*ConnectCommand MySQL –h hostname –u username –p is used to connect todatabase where usernames and password has been assigned to you.Host name = Id for server Username for server p= password for server 2.3 Column TypesGenerally there are 3 categories of column type supported by MySQL which are :1. Numeric TypeMySQL understands integers, floating points and scientific notations. Numbers can bepreceded by a minus sign to indicate negative value. Following is the column typeavailable (commonly used in MySQL).INT, TINYINT, SMALLINT, INT, FLOAT, DOUBLE, DECIMAL,Usage example:TINYINT[(M)] [UNSIGNED] [ZEROFILL]DOUBLE [(M,D)] [ZEROFILL]2. String (Character ) TypeCHAR,VARCHAR,TINYBLOB,BLOB,MEDIUMBLOB,LONGBLOB,TINYTEXT,TEXT,MEDIUMTEXT,LONGTEXT,ENUM,SETexample:name VARCHAR(20) NOT NULL,In Numeric Column Type, the number inside the parenthesis indicates display number,but in over here it is not. It explicitly defined the length of the character. Only CHAR andVARCHAR s' length can be defined. Strings are actually a 'generic' type in a sensebecause it can be used to hold any values. For example it can be used to hold binarydata or to store compressed data. For all string types, values that are too long will bechopped to fit. It ranges from small to big, with the largest type can hold nearly 4GB ofdata.VARCHAR and CHAR are the most common type. CHAR is fixed length type while theVARCHAR is variable length type. BLOB is a binary large object which can hold anythingyou throw into it. The variation indicates different maximum number of informationcan be stored. TEXT type is identical to BLOB, but different in the sense that BLOB iscase sensitive while TEXT is case insensitive. (This matters when sorting andcomparing is used) .3. Date and Time TypeDATE, TIME, DATETIME, TIMESTAMP, YEARValues of TIME type are elapsed time. A 12:30:00 means 12 hours, and 30 minutesand not 12.30 am which means that TIME can hold values like 50:13:00 but not50:65:00.If TIMESTAMP column is set to "NULL" then automatically it will be set to current timeand date.M in TIMEFORMAT indicates display formats#ENUM and SETENUM and SET are special type for which column values must be chosen from a fixedset of strings. Difference is ENUM column values must have at least exactly onemember of the set while SET may contain any or all members of the#AttributesA. Numeric Column Type1. AUTO_INCREMENT Indicates that the value is automatically increased by 1from the previous value. For any column to be used as auto_increment mustbe declared as NOT NULL and PRIMARY KEY / UNIQUE KEY2. UNSIGNED Disallows negative numbersB. String Column Types1. Binary Specified for CHAR or VARCHAR, which causes the column values to betreated as binary strings.2. NULL or NOT NULL3. DEFAULT To specify a default value for CHAR or VARCHAR only.MYSQL Commands :- create database Dbname ; ????? ????? ?????? ?????-show databases; ??? ???? ????? ???????? ????????-show create table r ;-use Dbname ; ????? ??? ????? ?????? ?????-create table r (ID int not null auto_increment primary key, name varchar(30) not null, age int(3) not null,birth_day date);????? ???? ?? ????? ?????? ?????? ???? ???????? ??? ???-describe)desc)Tname ; ???? ?????? ??????-show tables ; ??? ?? ??????? ???????? ?????? ????????-alter table G add doc longblob;?????? ??? ???? ??? ?????? ???? ????? ????? ???? ????? ????????alter table G add ID (??? ????? ?????? ??????) int not null auto_increment key first ;-alter table G add f3 (??? ????? ?????? ??????) int(6) not null first ; ?????? ??? ???? ??? ?????? ???? ????? ????? ???? ????? ????????-alter table G add f2 (??? ????? ?????? ??????) int not null after age; ?????? ??? ???? ??? ?????? ??? ?????? ???????-alter table G drop f2, drop f6 ;???? ??? ???? -change the LastName column from CHAR(30) to CHAR(40) withoutrenaming the column, you'd do this :ALTER TABLE HeadOfState CHANGE LastName LastName CHAR(40) NOTNULL;-To change the name as well (for example, to Surname), provide the new namefollowing the existing name :ALTER TABLE HeadOfState CHANGE LastName Surname CHAR(40) NOTNULL;-alter table G change f2 ((????? ??????? f6 (??? ????? ??????)date not null;?????? ??? ????? ????? -alter table G modify f3 float not null; ?????? ??? ????? ??? - ALTER TABLE HeadOfState MODIFY ID BIGINT UNSIGNED NOT NULL ; -Drop table Tname ;???? ???? ?? ????? ????????(?????? ?????? ?? ???????)-Drop database Dbname ;???? ????? ????????-Renaming a TableRenaming a table changes neither a table's structure nor its contents. The followingstatement renames table t1 to t2 :ALTER TABLE t1 RENAME TO t2;Another way to rename a table is by using the RENAME TABLE statement :RENAME TABLE t1 TO t2;RENAME TABLE has an advantage over ALTER TABLE in that it can perform multiple table renames in a single operation. One use for this feature is to swap the names of two tables:RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2;For TEMPORARY tables, RENAME TABLE does not work. You must use ALTER TABLEinstead >>ALTER TABLE HeadOfState MODIFY ID BIGINT UNSIGNED NOT NULL;- - - - - - - - - - - - - - - - - -- - - - - - - -- - - - - - - - - - - -- - - - - - - - - - -- - - - - - - - - - -- - - - - - -- --insert into r set name="Qais", age="59",f3="1",birth_day="19571215",doc="student"; ?????? ????????-insert into r (name,age,f6,birth_day,doc)values("Ghofran","22","7","19940111", "student"); ???? ????? ?????? ???????? -select *from r ;???? ?????? ??????-select name, age from r;-In general : SELECT "column1" [,"column2", etc.] FROM tbl_name [where"condition"];-Ex) select name(????? ?????? ????) from r (??? ??????) where age=22 (?????) ; ???? ??? ???? ?????? ???-Ex2) SELECT id,name FROM gg where name = "Qais" ;-SELECT id,name FROM gg ;-create table copy1 select *from r; ????? ???? ???? ???? ???? ?? ?????? ?????? ????? ??? ??? ?????? ????????? ???????? ??????? ??????-select * from copy 1 ;-create table copy4 like r;???? ?????? ?????? ?????? (?????? ???)-desc copy 4 ;-desc r ;-select * from gg where name = 'Qais' ;???? ?? ?????? ????? ??? ??? = ???-SELECT * FROM gg WHERE name='Qais' AND age='54';Note :Expression in WHERE clause can use arithmetic operators, comparison operators andlogical operators as below :Arithmetic Operators : + , - , * and /Comparison Operators : = , > , >= , =! ) <> ) , >= and >Logical Operator: AND, OR, NOTOthers: BETWEEN, Like-select name,age,f6,birth_day,doc from G order by name; asc ??? ?? ???? ???? ??? ??? ????? ?????? ??????? -select name,age,f6,birth_day,doc from G order by name asc;asc ???? ?????????? ???? ??????? ??????? -select ID,name,age,f6,birth_day from G order by name desc;????? ??????? ??????? desc- select ID,name,age,f6,month(birth_day),doc from G order by month(birth_day);??? ?????? ?????? ??????? ??? ????? ?? ??? ???????-select ID,name,age,f6,day(birth_day),doc from G order by day(birth_day);??? ?????- select ID,name,age,f6,year(birth_day),doc from G order by year(birth_day);??? ?????- select *from gg limit 1;??? ??? ??? ?? ?????? ? ????? ????? ??? ???????-select *from gg limit 0,2;??? ????? -select *from gg order by ID ( ????? ??? ????) limit 2 ;????? ?????? ??? ??? ???? ???? ???? ????? -select *from gg order by ID limit 0,2 ;????? ?????? ??? ??? ???? ???? ??? ?????-select distinct doc from Tname?? ??? ???? ???? ??????? ???? ???? ??? ?????? ???? ????? -select distinct name,doc from gg; ????? ?? ??? -delete from gg where name="Qais" ;??? ?? ??? ????? ??? ????? ??????? ??????-delete from gg ; ???? ?????? ?????? ??? In general : UPDATE Tname SET Cname = value [, Cname = value] ...WHERE ... ;update gg set f3=20 (????????? ???????) where name="Qais" ;?? ??? = ??? ??? ???? Examples 1 : Examples 2 :Examples 3 :Examples 4 :________________________________________________________________2.4 Practice: Creating and Modifying Tables2.4.1 Step 1:Create a student's table include the following fields using the appropriate types:1. name2. age3. address4. phone5. average time of arrival6. birthday date7. photo in GIF format8. comments9. record creation time10. last record update time11. a record IDAns : create table r(name varchar(20)not null , age int not null , address varchar(80) not null , phone int not null , average time of arrival int not null , birth_day date,2.4.2 Step 2:Use the table you have created above to create a new table "student2" with the samefields of the first table.Ans : create table student2 like r ;2.4.3 Step 3:Add new column in as the second column in the table, the new column should be thefamily name.Ans : alter table r add family_name varchar(20) after name ;2.4.4 Step 4:Rename the family name column to phone_2 and change the column type also toBIGINT.Ans : alter table r change Family_name phone_2 BIGINT UNSIGNED NOT NULL ;2.4.5 Step 5:Rename the table then explore its definition using the DESCRIBE command; finallymove this table to a temporary table.Answer :rename table r to gg ; >> (rename the table)desc gg ; >> (Explore its definition)ALTER TABLE gg MODIFY ID BIGINT UNSIGNED NOT NULL; (move this table to tmp table) 2.4.6 Step 6:Use phpmyadmin to find out the SQL statement of creating a table with one or moreENUM and SET columns.3.1 Practice: Inserting, modifying, deleting and querying records.3.1.1 Step 1:Create a new table called 'personnel' with first name, last name, gender, title, salaryage, employee id and branch number, employee id is integer with auto increment, notnull and primary key.3.1.2 Step 2:Add 5 records in the table you just create in step 1, in three of them don't provide theemployee id value.3.1.3 Step 3:Delete every record that has employee id below 2.3.1.4 Step 4:Update all the records to have the same age of 30, then modify the record with thelargest employee id to have an age of 35.3.1.5 Step 5:Provide the below reports:1. All the information about every employ where his/her age is more than 30years.2. The names of all the females in the company.3. The name and branch number of each male with a salary more than 1000$ andhis age is less than 25.4. All the employee names ordered from A to Z.4.8 Practice:4.8.1 Step 1:Prepare the below reports using the appropriate sql commands and the worlddatabase:1. The number of countries in each Continent.2. The average area of the country in each Continent and order the results usingthe continent name.3. the independent year of the first independent country in each region.4. All the continents with more than 1000000000 citizens.5. All the continents with more than 500000000 citizens and has an independentcountry before 1930. ................
................
In order to avoid copyright disputes, this page is only a partial summary.
To fulfill the demand for quickly locating and searching documents.
It is intelligent file search solution for home and business.
Related download
- lab 2 data definition language ddl eng alaa o shama
- class notes semester it iv sem subject name dbms
- data base management systems lab manual lendi
- sql tutorial learn transact sql language
- database programming with sql
- guide to using sql synonyms and the rename
- renaming columns and constraints
- oracle 9i streaming the fast information sharing
- ceg computer science dept home
- exercises home yeshiva university