MySql SQL[A]
MySQL
MySQL 1: MySQL Intro
- installation
- MYSQL_HOME: D:\Program Files\mysql
- Path: %MYSQL_HOME%\bin
- compmgmt.msc
- navicat
- create my.ini file under installed path
[mysql]
# set mysql default user char
default-character-set= utf8
[mysqld]
# set 3306 port
port= 3306
# set base directory
basedir= D:\\program files\\mysql
# set data directory
datadir= D:\\program files\\mysql\\data
# max allowed connections
max_connections= 20
# server char 8 bit set
character-set-server= utf8
# default storage for new table
default-storage-engine= INNODB
# create mode
sql_mode= NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLE
# oracle port=1521 sqlserver=1433
- clean data folder before installation (delete everything in it)
mysqld -install
mysqld -initialize -user=mysql --console
mysql [-h localhost] -u root -p
set password = password('abcde');
- for installation automatic: change my.ini file: latin1->utf8
MySQL 2: MySQL DB & Table
-
Design, Manipulate, Query, Control
-
binary operations: & | << >>
-
conditions: || &&
-
varchar, char, timestamp, datetime, blob
-
timestamp: 20010101010101 00~69: 2000; 70 ~99: 1900
# query
D:\Program Files\mysql\bin>mysql -u root -p
Enter password: ***************
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.19 |
+-----------+
mysql> select now() 'now';
+---------------------+
| now |
+---------------------+
| 2020-06-05 02:18:10 |
+---------------------+
mysql> drop database [if exists] dbName
mysql> create database [if not exists] dbName;
mysql> truncate table name;
mysql> delete table name;
mysql> create table [if not exists] 'tbName'(
-> 'id' int(11) unsigned comment 'number')comment='test';
mysql> select * from tbName;
mysql> select now();
mysql> delete from tbName where;
mysql> update tbName set varName = 'something';
mysql> select * from tbName where varName= 'something';
mysql> insert into tbName(name) values('values');
- Alter Table
- rename as; add; modify; change; drop
- unsigned: no negative
- zerofill: int(3), 5 = 005
- auto_increment:+1
- Null / not null: not null must have initial value
- default: default value ( primary key)
- Create Student table
cd...
sbin/hadoop...sh start name node
- example student
mysql> show databases;
mysql> use myschool;
mysql> create table subject
-> (subjectNo int primary key,
-> subjectName varchar(50),
-> classHour int,
-> gradeID int);
mysql> show tables;
# mysql> show create table subject;
mysql> show create table subject \g;
+---------+---------------------------------------------
------------------------------------------------------+
| Table | Create Table |
+---------+---------------------------------------------
------------------------------------------------------+
| subject | CREATE TABLE `subject` (
`subjectNo` int NOT NULL,
`subjectName` varchar(50) DEFAULT NULL,
`classHour` int DEFAULT NULL,
`gradeID` int DEFAULT NULL,
PRIMARY KEY (`subjectNo`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+---------------------------------------------
------------------------------------------------------+
mysql> use myschool;
Database changed
mysql> create table test (id int(11) unsigned comment 'id') comment='test';
mysql> alter table test rename tbl_test;
mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| student |
| subject |
| tbl_test |
+--------------------+
mysql> drop table tbl_test;
mysql> show tables;
+--------------------+
| Tables_in_myschool |
+--------------------+
| student |
| subject |
+--------------------+
/*STUDENT**/
mysql> create table student(
studentNo varchar(20) not null comment 'studentNo',
loginPwd varchar(20),
studentName varchar(20) comment 'studentName',
sex tinyint comment 'sex',
gradeID int comment 'grade'
);
mysql> describe student;
Current database: myschool
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| studentNo | varchar(20) | NO | | NULL | |
| loginPwd | varchar(20) | YES | | NULL | |
| studentName | varchar(20) | YES | | NULL | |
| sex | tinyint | YES | | NULL | |
| gradeID | int | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
mysql> alter table student add phone varchar(50);
mysql> alter table student change phone Phone varchar(50);
mysql> desc student;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| studentNo | varchar(20) | NO | | NULL | |
| loginPwd | varchar(20) | YES | | NULL | |
| studentName | varchar(20) | YES | | NULL | |
| sex | tinyint | YES | | NULL | |
| gradeID | int | YES | | NULL | |
| Phone | varchar(50) | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
6 rows in set (0.01 sec)
mysql> alter table student add Address varchar(255);
mysql> alter table student add is_deleted int;
mysql> desc student;
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| studentNo | varchar(20) | NO | | NULL | |
| loginPwd | varchar(20) | YES | | NULL | |
| studentName | varchar(20) | YES | | NULL | |
| sex | tinyint | YES | | NULL | |
| gradeID | int | YES | | NULL | |
| Phone | varchar(50) | YES | | NULL | |
| Address | varchar(255) | YES | | NULL | |
| is_deleted | int | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
- Engines: MyISAM, InnoDB, Heap, Bob, CSV, etc;
#comments
/*coomments*/
show engines \g;
mysql> show engines;
+--------------------+---------+------------------------------------------------
| Engine | Support | Comment
| Transactions | XA | Savepoints |
+--------------------+---------+------------------------------------------------
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables
| NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables
| NO | NO | NO |
| CSV | YES | CSV storage engine
| NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine
| NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema
| NO | NO | NO |
| MyISAM | YES | MyISAM storage engine
| NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys
| YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears)
| NO | NO | NO |
| ARCHIVE | YES | Archive storage engine
| NO | NO | NO |
+--------------------+---------+------------------------------------------------
9 rows in set (0.00 sec)
MySQL 3: MySQL DB Managment
- DML: Insert, Update, Delete
- example grade
mysql> create table Grade
-> (gradeID int primary key,
-> gradeName varchar(20)
-> );
Query OK, 0 rows affected (0.58 sec)
mysql> insert into grade(gradeID, gradeName)
-> values(1,'first');
mysql> select * from grade;
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 1 | first |
+---------+-----------+
1 row in set (0.00 sec)
mysql> insert into grade values (2,'second'),
-> (3,'third'), (4,'fouth');
mysql> select * from grade;
Current database: myschool
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 1 | first |
| 2 | second |
| 3 | third |
| 4 | fouth |
+---------+-----------+
4 rows in set (0.02 sec)
mysql> alter table grade modify gradeID int auto_increment;
mysql> desc grade;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| gradeID | int | NO | PRI | NULL | auto_increment |
| gradeName | varchar(20) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+
#duplicate existing elements from table, autoincremented gradeID
mysql> insert into grade(gradeName) select gradeName from grade;
#mysql> insert into grade(gradeName) select gradeName from grade
#mysql> where gradeName like 'first';
mysql> select* from grade;
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 1 | first |
| 2 | second |
| 3 | third |
| 4 | fouth |
| 5 | first |
| 6 | second |
| 7 | third |
| 8 | fouth |
+---------+-----------+
#update
mysql> update grade set gradeName= 'fifth';
mysql> select* from grade;
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 1 | fifth |
| 2 | fifth |
| 3 | fifth |
| 4 | fifth |
| 5 | fifth |
| 6 | fifth |
| 7 | fifth |
| 8 | fifth |
+---------+-----------+
mysql> update grade set gradeName='sixth' where gradeID='2' or gradeID='4';
mysql> select* from grade;
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 1 | fifth |
| 2 | sixth |
| 3 | fifth |
| 4 | sixth |
| 5 | fifth |
| 6 | fifth |
| 7 | fifth |
| 8 | fifth |
+---------+-----------+
mysql> update grade set gradeName='sixth' where gradeID between 6 and 8;
mysql> select* from grade;
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 1 | fifth |
| 2 | sixth |
| 3 | fifth |
| 4 | sixth |
| 5 | fifth |
| 6 | sixth |
| 7 | sixth |
| 8 | sixth |
+---------+-----------+
# delete
# delete from grade; delete all include structure
# truncate table grade; delete data only, reset autoincrement
mysql> delete from grade where gradeid=1;
mysql> select * from grade;
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 2 | sixth |
| 3 | fifth |
| 4 | sixth |
| 5 | fifth |
| 6 | sixth |
| 7 | sixth |
| 8 | sixth |
+---------+-----------+
- example update subject
mysql> desc subject;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| subjectNo | int | NO | PRI | NULL | |
| subjectName | varchar(50) | YES | | NULL | |
| classHour | int | YES | | NULL | |
| gradeID | int | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
mysql> alter table subject modify subjectNo int auto_increment;
mysql> desc subject;
+-------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+----------------+
| subjectNo | int | NO | PRI | NULL | auto_increment |
| subjectName | varchar(50) | YES | | NULL | |
| classHour | int | YES | | NULL | |
| gradeID | int | YES | | NULL | |
+-------------+-------------+------+-----+---------+----------------+
mysql> insert into subject(subjectName, classHour, gradeID) values('Math1', 120, 1),('Math2', 100, 3),
('Math3', 80, 5),('Math4', 60, 7);
mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 120 | 1 |
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
#decrease value of classhour by 10, update column
mysql> update subject set classHour=classhour-10 where classhour>10 and gradeID=1;
mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 110 | 1 |
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
#give math1 math4 classhour value
mysql> update subject set classhour=
-> (select classHour from (select classHour from subject
-> where subjectNo=4)as ch) where subjectNo=1; #as ch= as temp table
mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
mysql> update subject set classhour=
-> (select classhour from (select classhour from subject
-> where subjectNo=4)as classhour) where subjectNo=2; #temp table
mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 2 | Math2 | 60 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
- inner join xx a.id=b.id;
MySQL 4: Transaction & Data Integrity
- CRUD=Create, Read, Update and Delete ( persistent storage)
- ACID =Atomic+Consistency+Isolation+Durability
- Atomic: transaction all successful or all fail
- Consistency: same datatype
- Isolation: transactions do not affect each other;
- Durability: Data kept in DB, no deletion of data
- InnoDB & Berkeley DB type
- transaction_isolation: 1 read-uncommitted; 2 read-committed; 3 repeatable-read;
-
4 **serialisable**: add lock, synchronized; - dirty read: read uncommitted data: set autocommit =0 extra data if transaction is rolled back, set isolation = read-uncommitted, when uncommitted data is read
- phantom read different queries executed in a single transaction: unexpected result
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
| 1 |
+--------------+
mysql> select @@transaction_isolation; #default
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ |
+-------------------------+
mysql> select @@global.transaction_isolation;
+--------------------------------+
| @@global.transaction_isolation |
+--------------------------------+
| REPEATABLE-READ |
+--------------------------------+
mysql> select @@session.transaction_isolation;
+---------------------------------+
| @@session.transaction_isolation |
+---------------------------------+
| REPEATABLE-READ |
+---------------------------------+
- dirty read : uncommitted
#window1
mysql> set autocommit=0;
mysql> begin;
mysql> select * from student;
+-----------+----------+-------------+------+---------+-------+---------+------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | is_deleted |
+-----------+----------+-------------+------+---------+-------+---------+------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 34 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 23 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+----------+-------------+------+---------+-------+---------+------------+
mysql> insert into student(studentNo) values(222);
mysql> select* from student;
+-----------+----------+-------------+------+---------+-------+---------+------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | is_deleted |
+-----------+----------+-------------+------+---------+-------+---------+------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 34 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 23 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 222 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+----------+-------------+------+---------+-------+---------+------------+
#window2
mysql> set global transaction_isolation='read-uncommitted';
mysql> set session transaction_isolation='read-uncommitted';
mysql> set transaction_isolation='read-uncommitted';
mysql> select * from student;
+-----------+----------+-------------+------+---------+-------+---------+------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | is_deleted |
+-----------+----------+-------------+------+---------+-------+---------+------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 34 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 23 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 222 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+----------+-------------+------+---------+-------+---------+------------+
- dirty read committed
#window1
mysql> set global transaction_isolation='read-committed';
mysql> set session transaction_isolation='read-committed';
mysql> set transaction_isolation='read-committed';
mysql> begin;
mysql> update student set studentNo=234 where studentNo=1;
mysql> select* from student;
+-----------+----------+-------------+------+---------+-------+---------+------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | is_deleted |
+-----------+----------+-------------+------+---------+-------+---------+------------+
| 234 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 34 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 23 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+----------+-------------+------+---------+-------+---------+------------+
#window2
mysql> select* from student;
+-----------+----------+-------------+------+---------+-------+---------+------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | is_deleted |
+-----------+----------+-------------+------+---------+-------+---------+------------+
| 1 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 2 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 5 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 34 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 23 | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
+-----------+----------+-------------+------+---------+-------+---------+------------+
- example shop
mysql> create database shop;
mysql> use shop
mysql> create table account
-> (id int not null primary key auto_increment,
-> name varchar(32) not null,
-> cash decimal(9,2) not null);
mysql> show tables;
+----------------+
| Tables_in_shop |
+----------------+
| account |
+----------------+
mysql> select * from account;
+----+------+----------+
| id | name | cash |
+----+------+----------+
| 1 | A | 2000.00 |
| 2 | B | 10000.00 |
+----+------+----------+
# take away 500 from A, give 500 to B, then undo the change;
mysql> begin;
mysql> update account set cash= cash-500 where id=1;
mysql> update account set cash=cash+500 where id=2;
mysql> select * from account;
+----+------+----------+
| id | name | cash |
+----+------+----------+
| 1 | A | 1500.00 |
| 2 | B | 10500.00 |
+----+------+----------+
mysql> rollback;
mysql> select * from account;
+----+------+----------+
| id | name | cash |
+----+------+----------+
| 1 | A | 2000.00 |
| 2 | B | 10000.00 |
+----+------+----------+
mysql> set transaction_isolation='read-uncommitted';
mysql> select @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| READ-UNCOMMITTED |
+-------------------------+
- errors:
- ERROR 1054: unknown column
- ERROR 1051: unknown table
- ERROR 1146: table doesn’t exist
- ERROR 1052: union table column size different
- ERROR 1222: view and select column size different
- ERROR 1353: view select and field column size different
- ERROR 1288: cannot update
MySQL 5: DQL
- do not use ‘.’ or ‘*’ as data
- select , distinct
select * from student;
+-----------+----------+-------------+------+---------+---------+------------+-----------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+-----------------------+
| 1 | 123 | Tom | 1 | 32 | 1234567 | abc st 123 | 1999-12-21 22:22:22 |
| 2 | 222 | Sam | 2 | 3234 | 1234567 | abc st 123 | 1999-12-21 22:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-21 22:22:22 |
| 34 | 444 | Olam | 1 | 323234 | 1234567 | abc st 123 | 1999-12-21 22:22:22 |
| 23 | 555 | Ellen | 2 | 3233 | 1234567 | abc st 123 | 1999-12-21 22:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+-----------------------+
select studentName, phone, address from student;
+-------------+---------+------------+
| studentName | phone | address |
+-------------+---------+------------+
| Tom | 1234567 | abc st 123 |
| Sam | 1234567 | abc st 123 |
| Bob | 1234567 | abc st 123 |
| Olam | 1234567 | abc st 123 |
| Ellen | 1234567 | abc st 123 |
+-------------+---------+------------+
#alias use as
mysql> select studentName as sn from student;
+-------+
| sn |
+-------+
| Tom |
| Sam |
| Bob |
| Olam |
| Ellen |
+-------+
mysql> select stu.address from student as stu;
+------------+
| address |
+------------+
| abc st 123 |
| abc st 123 |
| abc st 123 |
| abc st 123 |
| abc st 123 |
+------------+
#add to data
mysql> select phone+123 from student;
+-----------+
| phone+123 |
+-----------+
| 1234690 |
| 1234690 |
| 1234690 |
| 1234690 |
| 1234690 |
+-----------+
#distinct
mysql> select distinct sex, phone from student;
+------+---------+
| sex | phone |
+------+---------+
| 1 | 1234567 |
| 2 | 1234567 |
+------+---------+
mysql> insert into student(studentNo,birthDate) values(1,now());
mysql> select studentNo, birthdate from student;
+-----------+---------------------+
| studentNo | birthdate |
+-----------+---------------------+
| 1 | 1999-12-21 22:22:22 |
| 2 | 1999-12-21 22:22:22 |
| 5 | 1999-12-21 22:22:22 |
| 34 | 1999-12-21 22:22:22 |
| 23 | 1999-12-21 22:22:22 |
| 1 | 2020-06-05 02:29:26 |
+-----------+---------------------+
#concat
mysql> select concat(studentname, '@123.com')as email from student;
+---------------+
| email |
+---------------+
| Tom@123.com |
| Sam@123.com |
| Bob@123.com |
| Olam@123.com |
| Ellen@123.com |
| NULL |
+---------------+
- select II
mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
mysql> select subjectName 'sub', classhour 'chour', classhour/30 'hours/day' from subject;
+-------+-------+-----------+
| sub | chour | hours/day |
+-------+-------+-----------+
| Math1 | 60 | 2.0000 |
| Math2 | 100 | 3.3333 |
| Math3 | 80 | 2.6667 |
| Math4 | 60 | 2.0000 |
+-------+-------+-----------+
mysql> select * from subject where classhour between 80 and 100;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
+-----------+-------------+-----------+---------+
mysql> select * from subject where classhour>=80 and classhour<=100;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
+-----------+-------------+-----------+---------+
mysql> select studentname from student where loginpwd is not null;
+-------------+
| studentname |
+-------------+
| Tom |
| Sam |
| Bob |
| Olam |
| Ellen |
+-------------+
mysql> select * from student where studentname like 'e%'; # % all _ number of letters
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| 23 | 555 | Ellen | 2 | 3233 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
mysql> select * from student where studentname like '%o%';
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| 1 | 123 | Tom | 1 | 32 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 34 | 444 | Olam | 1 | 323234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
mysql> select * from student where studentname like '_o_';
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| 1 | 123 | Tom | 1 | 32 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
mysql> select * from student where studentNo in (2,5); # 2&3
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
| 2 | 222 | Sam | 2 | 3234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+--------------------+
mysql> select * from student where gradeid>loginpwd;
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
| 2 | 222 | Sam | 2 | 3234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 34 | 444 | Olam | 1 | 323234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 23 | 555 | Ellen | 2 | 3233 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
- inner join: join: and condition
# inner join
mysql> select * from subject; select * from grade;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 2 | sixth |
| 3 | fifth |
| 4 | sixth |
| 5 | sixth |
| 6 | sixth |
| 7 | sixth |
| 8 | sixth |
+---------+-----------+
mysql> select * from subject s,grade g where s.gradeid=g.gradeid; # multiple tables inner equivalent
+-----------+-------------+-----------+---------+---------+-----------+
| subjectNo | subjectName | classHour | gradeID | gradeID | gradeName |
+-----------+-------------+-----------+---------+---------+-----------+
| 2 | Math2 | 100 | 3 | 3 | fifth |
| 3 | Math3 | 80 | 5 | 5 | sixth |
| 4 | Math4 | 60 | 7 | 7 | sixth |
+-----------+-------------+-----------+---------+---------+-----------+
# inner join
mysql> select * from subject s inner join grade g on s.gradeid=g.gradeid;
+-----------+-------------+-----------+---------+---------+-----------+
| subjectNo | subjectName | classHour | gradeID | gradeID | gradeName |
+-----------+-------------+-----------+---------+---------+-----------+
| 2 | Math2 | 100 | 3 | 3 | fifth |
| 3 | Math3 | 80 | 5 | 5 | sixth |
| 4 | Math4 | 60 | 7 | 7 | sixth |
+-----------+-------------+-----------+---------+---------+-----------+
mysql> select * from subject s inner join grade g on s.gradeid=g.gradeid
where gradename='fifth';
+-----------+-------------+-----------+---------+---------+-----------+
| subjectNo | subjectName | classHour | gradeID | gradeID | gradeName |
+-----------+-------------+-----------+---------+---------+-----------+
| 2 | Math2 | 100 | 3 | 3 | fifth |
+-----------+-------------+-----------+---------+---------+-----------+
- outer join: left join, right join
#left join: subject as main table: display all main table
mysql> select * from subject s left join grade g on s.gradeid=g.gradeid;
+-----------+-------------+-----------+---------+---------+-----------+
| subjectNo | subjectName | classHour | gradeID | gradeID | gradeName |
+-----------+-------------+-----------+---------+---------+-----------+
| 1 | Math1 | 60 | 1 | NULL | NULL |
| 2 | Math2 | 100 | 3 | 3 | fifth |
| 3 | Math3 | 80 | 5 | 5 | sixth |
| 4 | Math4 | 60 | 7 | 7 | sixth |
+-----------+-------------+-----------+---------+---------+-----------+
#right join: grade as main table: display all main table
mysql> select * from subject s right join grade g on s.gradeid=g.gradeid;
+-----------+-------------+-----------+---------+---------+-----------+
| subjectNo | subjectName | classHour | gradeID | gradeID | gradeName |
+-----------+-------------+-----------+---------+---------+-----------+
| 2 | Math2 | 100 | 3 | 3 | fifth |
| 3 | Math3 | 80 | 5 | 5 | sixth |
| 4 | Math4 | 60 | 7 | 7 | sixth |
| NULL | NULL | NULL | NULL | 2 | sixth |
| NULL | NULL | NULL | NULL | 4 | sixth |
| NULL | NULL | NULL | NULL | 6 | sixth |
| NULL | NULL | NULL | NULL | 8 | sixth |
+-----------+-------------+-----------+---------+---------+-----------+
- category table example
mysql> create table if not exists category(
-> catID int(10) auto_increment primary key,
-> catName varchar(32) not null,
-> pid int(10)
-> );
mysql> select * from category;
+-------+----------+------+
| catID | catName | pid |
+-------+----------+------+
| 1 | notices | NULL |
| 2 | news | NULL |
| 3 | stories | NULL |
| 4 | tinynews | 3 |
+-------+----------+------+
mysql> select c.catid, c.catname, c.pid from category c, category c2 where c.catid=c2.pid
+-------+---------+------+
| catid | catname | pid |
+-------+---------+------+
| 3 | stories | NULL |
+-------+---------+------+
- order by: desc; asc (default)
mysql> select * from subject order by classhour;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 4 | Math4 | 60 | 7 |
| 3 | Math3 | 80 | 5 |
| 2 | Math2 | 100 | 3 |
+-----------+-------------+-----------+---------+
mysql> select * from subject order by classhour desc;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 1 | Math1 | 60 | 1 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
mysql> select * from subject order by classhour, gradeid;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 4 | Math4 | 60 | 7 |
| 3 | Math3 | 80 | 5 |
| 2 | Math2 | 100 | 3 |
+-----------+-------------+-----------+---------+
- limit
- sql: limit 0,5; in Java pageIndex: 1(0,5),2(5,5),3(10,5); pagesize 5
- total items: select* from student limit pageIndex*pagesize;
- total page: int totPage= tot%pageSize==0? tot/pageSize: tot/pageSize+1;
mysql> select * from subject limit 2;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 2 | Math2 | 100 | 3 |
+-----------+-------------+-----------+---------+
mysql> select * from subject limit 1,2; # from position 1, count 2
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
+-----------+-------------+-----------+---------+
- child query
mysql> select * from subject; select * from grade;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 2 | sixth |
| 3 | fifth |
| 4 | sixth |
| 5 | sixth |
| 6 | sixth |
| 7 | sixth |
| 8 | sixth |
+---------+-----------+
mysql> select subjectName from subject where gradeid in (select gradeid from grade);
+-------------+
| subjectName |
+-------------+
| Math2 |
| Math3 |
| Math4 |
+-------------+
mysql> select subjectName from subject where
gradeid in (select gradeid from grade where gradeid=3);
+-------------+
| subjectName |
+-------------+
| Math2 |
+-------------+
- Count(): select count(); Sum(); Avg(); Max(); Min()
mysql> select studentno 'snum' from student;
+------+
| snum |
+------+
| 1 |
| 2 |
| 5 |
| 34 |
| 23 |
+------+
mysql> select count(studentNo) 'tot' from student;
+-----+
| tot |
+-----+
| 5 |
+-----+
mysql> select sum(studentno) 'sum' from student;
+------+
| sum |
+------+
| 65 |
+------+
mysql> select max(studentno) 'max' from student; # this happened b/c studentno is varchar
+------+
| max |
+------+
| 5 |
+------+
mysql> select min(studentno) 'min' from student;
+------+
| min |
+------+
| 1 |
+------+
mysql> select avg(studentno) 'avg' from student;
+------+
| avg |
+------+
| 13 |
+------+
- group by, having
mysql> select * from student group by sex;
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
| 1 | 123 | Tom | 1 | 32 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 2 | 222 | Sam | 2 | 3234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
mysql> select sex, count(sex) from student group by sex;
+------+------------+
| sex | count(sex) |
+------+------------+
| 1 | 3 |
| 2 | 2 |
+------+------------+
mysql> select sex, count(sex) from student group by sex with rollup; #total
+------+------------+
| sex | count(sex) |
+------+------------+
| 1 | 3 |
| 2 | 2 |
| NULL | 5 |
+------+------------+
/*
java code
map1 put male 3
map1 put female 2
map2 put total ( map1.get male + map1.get female ) 5
map2.get total 5
*/
mysql> select group_concat(studentname), count(sex) from student group by sex;
+---------------------------+------------+
| group_concat(studentname) | count(sex) |
+---------------------------+------------+
| Tom,Bob,Olam | 3 |
| Sam,Ellen | 2 |
+---------------------------+------------+
mysql> select sex, count(sex) from student group by sex having count(sex) >2;
+------+------------+
| sex | count(sex) |
+------+------------+
| 1 | 3 |
+------+------------+
- Union: can only union tables with same number of columns
mysql> create table category2( cid int primary key auto_increment,
-> cname varchar (32), pid int default null);
mysql> insert into category2(cname,pid) select catName, pid from category;
mysql> select * from category union select * from category2; #union same data
+-------+----------+------+
| catID | catName | pid |
+-------+----------+------+
| 1 | notices | NULL |
| 2 | news | NULL |
| 3 | stories | NULL |
| 4 | tinynews | 3 |
+-------+----------+------+
mysql> select * from category union all select * from category2; #union all data repeats
+-------+----------+------+
| catID | catName | pid |
+-------+----------+------+
| 1 | notices | NULL |
| 2 | news | NULL |
| 3 | stories | NULL |
| 4 | tinynews | 3 |
| 1 | notices | NULL |
| 2 | news | NULL |
| 3 | stories | NULL |
| 4 | tinynews | 3 |
+-------+----------+------+
- query child: where…
- exists
- any, all
mysql> select* from grade;
+---------+-----------+
| gradeID | gradeName |
+---------+-----------+
| 2 | sixth |
| 3 | fifth |
| 4 | sixth |
| 5 | sixth |
| 6 | sixth |
| 7 | sixth |
| 8 | sixth |
+---------+-----------+
mysql> select * from subject;
+-----------+-------------+-----------+---------+
| subjectNo | subjectName | classHour | gradeID |
+-----------+-------------+-----------+---------+
| 1 | Math1 | 60 | 1 |
| 2 | Math2 | 100 | 3 |
| 3 | Math3 | 80 | 5 |
| 4 | Math4 | 60 | 7 |
+-----------+-------------+-----------+---------+
mysql> select subjectName, gradeid from subject
-> where gradeid in (select gradeid from grade);
+-------------+---------+
| subjectName | gradeid |
+-------------+---------+
| Math2 | 3 |
| Math3 | 5 |
| Math4 | 7 |
+-------------+---------+
#exists true false
mysql> select subjectName, gradeid from subject where not exists
(select * from grade where gradeid=888);
+-------------+---------+
| subjectName | gradeid |
+-------------+---------+
| Math1 | 1 |
| Math2 | 3 |
| Math3 | 5 |
| Math4 | 7 |
+-------------+---------+
mysql> select subjectName, gradeid from subject where exists
(select * from grade where gradeid=888);
Empty set (0.00 sec)
mysql> select subjectName, gradeid from subject
-> where gradeid>=any (select gradeid from grade);
+-------------+---------+
| subjectName | gradeid |
+-------------+---------+
| Math2 | 3 |
| Math3 | 5 |
| Math4 | 7 |
+-------------+---------+
mysql> select subjectName, gradeid from subject
-> where gradeid>=all (select gradeid from grade);
Empty set (0.00 sec)
- select varName from tableName where… group by… having…order… limit…
MySQL 6: Index
- rowlock ( while true { if (null) break;delete from table where id between 1 and 1000 limit 100 sleep(10)})
- InnoDB MyISAM (select count(*) from table
- primary key, unique, index, fulltext, cluster, (spatial) geometry
- BTree: multi variable index: index index_name (studentNo, studentName, sex, birth); ( search with first variable: studentNo) : explain select * from student;
- jdk switch case
- dictionary
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+-----+------+----------+---
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-----+------+----------+---
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------------+----------+---
- index, key
mysql> create table index_test
(id int auto_increment primary key, name varchar(20));
# if ip address 192.168.226.199 use int when indexing
# (change from string to int and back)
mysql> insert into index_test(name) values('Tom'), ('Bob'), ('Sam');
mysql> insert into index_test(name) select name from index_test; #repeat!
mysql> select name from i_test limit 2;
+------+
| name |
+------+
| tom |
| bob |
+------+
mysql> alter table i_test add index itest(name); #add index
mysql> explain select * from i_test \g
+----+-------------+--------+------------+-------+---------------+-------+---------+------
| id | select_type | table | partitions | type | possible_keys| key | key_len | ref| rows | filtered |
Extra |
+----+-------------+--------+------------+-------+---------------+-------+---------+------
| 1 | SIMPLE | i_test | NULL | index | NULL | itest | 63 | NULL| 24216 | 100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+-------+---------+------
mysql> explain select * from i_test where name='bob' \g
+----+-------------+--------+------------+------+---------------+-------+---------+-------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref
| rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+-------+---------+-------
| 1 | SIMPLE | i_test | NULL | ref | itest | itest | 63 | const
| 8192 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+-------+---------+-------
mysql> create index itest2 on i_test(id); # not standard operation, error in counting
mysql> explain select * from i_test where id='100' \g
+----+-------------+--------+------------+-------+------------------------+---------+----
| id | select_type | table | partitions | type | possible_keys | key | key
len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+------------------------+---------+----
| 1 | SIMPLE | i_test | NULL | const | PRIMARY, itest2 | PRIMARY | 4
| const | 1 | 100.00 | NULL |
+----+-------------+--------+------------+-------+------------------------+---------+----
/*CREATE TABLE `i_test` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `itest` (`name`),
KEY `itest2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=32755 DEFAULT CHARSET=utf8;
*/
mysql> create table tble_t
-> (id int, key key_id(id)) ;
mysql> explain select * from tble_t where id='1';
+----+-------------+--------+------------+------+---------------+--------+---------+-----
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref
| rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-----
| 1 | SIMPLE | tble_t | NULL | ref | key_id | key_id | 5 | cons
| 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+--------+---------+-----
- view : virtual table: encapsulation, safe
- replace
- cannot update: count(), sum(), group by, select child
mysql> select * from student;
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
| 1 | 123 | Tom | 1 | 32 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 2 | 222 | Sam | 2 | 3234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 34 | 444 | Olam | 1 | 323234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 23 | 555 | Ellen | 2 | 3233 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+---------------------+
mysql> create view s_view as select * from student;
mysql> show create table student;
+---------+-------------------------------------------------------------------------------
| Table | Create Table |
+---------+-------------------------------------------------------------------------------
| student | CREATE TABLE `student` (
`studentNo` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '1'
COMMENT 'studentNo',
`loginPwd` varchar(20) DEFAULT NULL,
`studentName` varchar(20) DEFAULT NULL COMMENT 'studentName',
`sex` tinyint DEFAULT NULL COMMENT 'sex',
`gradeID` int DEFAULT NULL COMMENT 'grade',
`Phone` varchar(50) DEFAULT NULL,
`Address` varchar(255) DEFAULT NULL,
`birthDate` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+---------+-------------------------------------------------------------------------------
mysql> show create view s_view;
-----------------------------------+----------------------+----------------------+
| View | Create View| character_set_client | collation_connection |
-----------------------------------+----------------------+----------------------+
| s_view | CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW
`s_view` AS select `student`.`studentNo` AS `studentNo`,`student`.`loginPwd` AS `loginPwd
`,`student`.`studentName` AS `studentName`,`student`.`sex` AS `sex`,`student`.`gradeID` AS
`gradeID`,`student`.`Phone` AS `Phone`,`student`.`Address` AS `Address`,`student`.`birthD
ate` AS `birthDate` from `student` | utf8 | utf8_general_ci |
-----------------------------------+----------------------+----------------------+
mysql> select * from s_view;
+-----------+----------+-------------+------+---------+---------+------------+-----------
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+-----------
| 1 | 123 | Tom | 1 | 32 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 2 | 222 | Sam | 2 | 3234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 34 | 444 | Olam | 1 | 323234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 23 | 555 | Ellen | 2 | 3233 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+-----------
mysql> create view s_view2(studentNo, studentName, address) as # create view
-> select studentNo, studentName, address from student;
mysql> select * from s_view2 ;
+-----------+-------------+------------+
| studentNo | studentName | address |
+-----------+-------------+------------+
| 1 | Tom | abc st 123 |
| 2 | Sam | abc st 123 |
| 5 | Bob | abc st 123 |
| 34 | Olam | abc st 123 |
| 23 | Ellen | abc st 123 |
+-----------+-------------+------------+
# alter view
mysql> alter view s_view2(studentNo, studentName, address, phone) as
-> select studentNo, studentName, address, phone from student;
mysql> select * from s_view2;
+-----------+-------------+------------+---------+
| studentNo | studentName | address | phone |
+-----------+-------------+------------+---------+
| 1 | Tom | abc st 123 | 1234567 |
| 2 | Sam | abc st 123 | 1234567 |
| 5 | Bob | abc st 123 | 1234567 |
| 34 | Olam | abc st 123 | 1234567 |
| 23 | Ellen | abc st 123 | 1234567 |
+-----------+-------------+------------+---------+
mysql> create or replace view s_view2(sNo, sName, address, phone) as
-> select studentNo, studentName, address, phone from student;
mysql> select * from s_view2;
+-----+-------+------------+---------+
| sNo | sName | address | phone |
+-----+-------+------------+---------+
| 1 | Tom | abc st 123 | 1234567 |
| 2 | Sam | abc st 123 | 1234567 |
| 5 | Bob | abc st 123 | 1234567 |
| 34 | Olam | abc st 123 | 1234567 |
| 23 | Ellen | abc st 123 | 1234567 |
+-----+-------+------------+---------+
# drop view s_view2;
mysql> use myschool;
mysql> update s_view2 set sname='Samson' where sNo=1;
mysql> select * from student;
+-----------+----------+-------------+------+---------+---------+------------+-----------
| studentNo | loginPwd | studentName | sex | gradeID | Phone | Address | birthDate |
+-----------+----------+-------------+------+---------+---------+------------+-----------
| 1 | 123 | Samson | 1 | 32 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 2 | 222 | Sam | 2 | 3234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 5 | 333 | Bob | 1 | 32344 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 34 | 444 | Olam | 1 | 323234 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
| 23 | 555 | Ellen | 2 | 3233 | 1234567 | abc st 123 | 1999-12-2122:22:22 |
+-----------+----------+-------------+------+---------+---------+------------+-----------
mysql> create view s_view3 (sNo, name) as select studentNo, studentName
-> from student group by sex;
mysql> select * from s_view3;
+-----+--------+
| sNo | name |
+-----+--------+
| 1 | Samson |
| 2 | Sam |
+-----+--------+
mysql> select count(sex) from student group by sex;
+------------+
| count(sex) |
+------------+
| 3 |
| 2 |
+------------+
mysql> create or replace view s_view3 (name, genderCount) as select studentName, count(sex
) from student group by sex;
mysql> select * from s_view3;
+--------+-------------+
| name | genderCount |
+--------+-------------+
| Samson | 3 |
| Sam | 2 |
+--------+-------------+
mysql> create view s_view4 as select * from s_view2;
mysql> select* from s_view4;
+-----+--------+------------+---------+
| sNo | sName | address | phone |
+-----+--------+------------+---------+
| 1 | Samson | abc st 123 | 1234567 |
| 2 | Sam | abc st 123 | 1234567 |
| 5 | Bob | abc st 123 | 1234567 |
| 34 | Olam | abc st 123 | 1234567 |
| 23 | Ellen | abc st 123 | 1234567 |
+-----+--------+------------+---------+
mysql> select* from s_view2;
+-----+--------+------------+---------+
| sNo | sName | address | phone |
+-----+--------+------------+---------+
| 1 | Samson | abc st 123 | 1234567 |
| 2 | Sam | abc st 123 | 1234567 |
| 5 | Bob | abc st 123 | 1234567 |
| 34 | Olam | abc st 123 | 1234567 |
| 23 | Ellen | abc st 123 | 1234567 |
+-----+--------+------------+---------+
# drop multiples
mysql> drop view s_view3, s_view4;
- mysql : user
- Md5 (Message Digest 5) is a cryptographic function that allows you to make a 128-bits (32 caracters) “hash” from any string taken as input, no matter the length (up to 2^64 bits). This function is irreversible, you can’t obtain the plaintext only from the hash
mysql> select md5('root');
+----------------------------------+
| md5('root') |
+----------------------------------+
| 63a9f0ea7bb98050342b649e85481845 |
+----------------------------------+
- information_schema: table, view
use information_schema;
show create table tables;
select * from tables;
select * from tables where table_name like '%stu%';
select * from views;
select * from information_schema.views;
- trigger
/*CREATE TRIGGER trigger_name
ON table_name
[WITH ENCRYPTION]
FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
AS
T-SQL语句
GO
--with encryption
--delete,insert,update*/
mysql> create table tbl_tri(
-> action varchar(20),
-> action_time datetime);
mysql> desc tbl_tri;
+-------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| action | varchar(20) | YES | | NULL | |
| action_time | datetime | YES | | NULL | |
+-------------+-------------+------+-----+---------+-------+
mysql> use myschool;
mysql> create trigger trig1 before insert on student for each row
-> insert into tbl_tri(action, action_time) values('insert', now());
mysql> insert into student(studentName) values('Ian');
mysql> select * from tbl_tri;
+--------+---------------------+
| action | action_time |
+--------+---------------------+
| insert | 2020-06-07 23:49:26 |
+--------+---------------------+
mysql> show create trigger trig1;
+---------+--------------------------------------------+----------------------------------
| Trigger | sql_mode | SQL Original Statement
| character_set_client | collation_connection | Database C
ollation | Created |
+---------+--------------------------------------------+----------------------------------
| trig1 | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION | CREATE DEFINER=`root`@`localhost`
TRIGGER `trig1` BEFORE INSERT ON `student` FOR EACH ROW insert into tbl_tri(action, actio
n_time) values('insert', now()) | utf8 | utf8_general_ci | utf8_gener
al_ci | 2020-06-07 22:55:36.99 |
+---------+--------------------------------------------+----------------------------------
mysql> delimiter // # multilines
-> create trigger trig2 after update on student for each row
-> begin
-> update tbl_tri set action='update';
-> insert into tbl_tri values( 'modify',now());
-> end//
mysql> update student set studentname='Sara' where studentname='Ian'; # add Ian 3 times
mysql> select * from tbl_tri;
+--------+---------------------+
| action | action_time |
+--------+---------------------+
| update | 2020-06-07 23:49:26 |
| modify | 2020-06-08 00:28:32 |
+--------+---------------------+
4 rows in set (0.00 sec)
# mysql> create trigger trig1 before/after/
# -> insert/update/delete on student for each row
# -> begin insert into tbl_tri(action, action_time) values('insert', now()); end
mysql> select * from information_schema.triggers;
mysql> show create trigger sys.sys_config_update_set_user;
/*java code
select...user action: login
insert sys_log...
login record
delete...
insert sys_log...
query record
admin
xxxx delete 2020...*/
MySQL 7: Backup & Recovery
- mysqldump.exe: bin
mysql> quit
-> mysqldump -?
d:\Program Files\mysql\bin>mysqldump -u root -p myschool>D:\testMySQL\myschool.txt
Enter password: ***************
d:\Program Files\mysql\bin>mysqldump -u root -p myschool grade > D:\testMySQL\grade.sql
Enter password: ***************
- –add-drop-table ( --skip-add-drop-table); DropTableIfExits
- –add-lock (insert: locktable, unlocktable)
- –no-create-info (createtable)
- -c --complete-insert (insert)
- -d --no-data
- –where"where-condition", -w"where-condition"
- –opt ( --add-drop-tables, --add-locking, --create-option: --disable-keys-extended-insert, --lock-table, --quick
- source
- into outfile
- fields terminated by
- optionally enclosed by
- lines terminated by
mysql> create database myschool_bu;
mysql> use myschool_bu;
mysql> source D:\\myschool.txt;
mysql> show tables from myschool_bu;
+-----------------------+
| Tables_in_myschool_bu |
+-----------------------+
| category |
| category2 |
| grade |
| i_test |
| s_view |
| s_view2 |
| student |
| subject |
| tbl_tri |
| tble_t |
+-----------------------+
mysql> drop table grade;
mysql> quit
D:\Program Files\mysql\bin>mysql -u root -p myschool_bu < D:\\testMySQL\grade.sql
Enter password: **********
mysql> show variables like '%secure%';
mysql -u root -p myschool_backup < D:\\myschool.txt;
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| require_secure_transport | OFF |
| secure_file_priv | NULL |
+--------------------------+-------+
mysql> show variables like '%secure%';
+--------------------------+---------------+
| Variable_name | Value |
+--------------------------+---------------+
| require_secure_transport | OFF |
| secure_file_priv | D:\testMySQL\ |
+--------------------------+---------------+
mysql> load data infile 'D:\\subject.sql' into table subject;
mysql> select * from subject into outfile 'D:/testMySQL/subject.sql' ; # MUST USE '/'!
*/
- add in ini file
# secure-file-priv
secure-file-priv= "D:/testMySQL"
- restart mysql
# open cmd as admin
C:\Windows\system32>net stop mysql
C:\Windows\system32>net start mysql
- ERROR Can’t connect to MySQL server on ‘localhost’ (10061)
- result
D:\Program Files\mysql\bin>mysql -u root -p
Enter password: *****
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.19 MySQL Community Server - GPL
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
SQL
- Data Definition Lang
- Data Manipulation Lang
- Data Control Lang
- Transaction Control Lang
- Data Query Lang
SQL 1: Oracle Intro
- 9i internet; 10g llg grid (ds); 12c cloud;
- sql 19c installation
- sql 19c installation II
C:\Users\Administrator>dbca
- restart following
- if cannot login: enter following then use sys as username and orcl as password, change later
C:\Users\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on 星期五 6月 26 07:17:13 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter user sys identified by newpassword;
SQL> alter user system identified by newpassword;
- create tablespace
- create tablespace tablespace_name
- datafile ‘path’ size numM
- [autoextend on next numM]
- [maxsize numM]
C:\Users\Administrator>sqlplus "/as sysdba"
SQL*Plus: Release 19.0.0.0.0 - Production on 星期五 6月 26 10:35:05 2020
SQL> create tablespace test datafile 'd:\Database\data\test_data.dbf' size 20M;
SQL> create tablespace test2 datafile 'd:\Database\data\test_data2.dbf' size 20M autoextend on next 5M maxsize 500M;
select user_id,username, default_tablespace from dba_users order by user_id;
SQL> alter database default tablespace test;
SQL> select * from dba_data_files;
SQL> select tablespace_name, file_name from dba_data_files;
SQL> alter tablespace test rename to test_data;
- cannot delete tablespace when in use
SQL> drop tablespace test_data including contents and datafiles;
drop tablespace test_data including contents and datafiles
*
第 1 行出现错误:
ORA-12919: 不能删除默认永久表空间
SQL> alter database default tablespace users;
数据库已更改。
SQL> drop tablespace test_data including contents and datafiles;
表空间已删
- sql developer
- table
- sql plus case sensitive
- create table table_name(column1 type,… datatype) tablespace tablespace_name;
SQL> create table student1
(
stu_id number not null,
stu_name varchar2(20),
stu_age number,
status varchar(3),
version number default 0
)
tablespace users;
SQL> select * from dba_data_files where tablespace_name= 'USERS';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------------------------------------
BYTES BLOCKS STATUS RELATIVE_FNO AUTOEX MAXBYTES
---------- ---------- ------------------ ------------ ------ ----------
MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS LOST_WRITE_PRO
---------- ------------ ---------- ----------- -------------- --------------
D:\SQL\ORACLE\ORADATA\STS2\USERS01.DBF
7 USERS
5242880 640 AVAILABLE 7 YES 3.4360E+10
4194302 160 4194304 512 ONLINE OFF
SQL> select table_name, tablespace_name from user_tables where table_name='STUDENT1';
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
STUDENT1
USERS
SQL> select table_name, tablespace_name from user_tables where lower(table_name)='student1';
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
STUDENT1
USERS
SQL> desc STUDENT1;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
STU_ID NOT NULL NUMBER
STU_NAME VARCHAR2(20)
STU_AGE NUMBER
STATUS VARCHAR2(3)
VERSION NUMBER
--comments
- alter table
alter table student1 add(class_id number);
desc STUDENT1;
名称 空值? 类型
-------- -------- ------------
STU_ID NOT NULL NUMBER
STU_NAME VARCHAR2(20)
STU_AGE NUMBER
STATUS VARCHAR2(3)
VERSION NUMBER
CLASS_ID NUMBER
alter table student1 modify(class_id varchar2(20));
desc STUDENT1;
名称 空值? 类型
-------- -------- ------------
STU_ID NOT NULL NUMBER
STU_NAME VARCHAR2(20)
STU_AGE NUMBER
STATUS VARCHAR2(3)
VERSION NUMBER
CLASS_ID VARCHAR2(20)
- delete column in table
SQL> alter table STUDENT1 drop column CLASS_ID;
alter table STUDENT1 drop column CLASS_ID
*
第 1 行出现错误:
ORA-12988: 无法删除属于 SYS 的表中的列
- create another user and delete table from there
SQL> create tablespace test datafile 'd:\Database\data\test_data.dbf' size 20M;
SQL> create user c##bob identified by "xxx12345" default tablespace test;
SQL> conn system/manager as sysdba
SQL> revoke sysdba from c##bob ;
SQL> grant connect, resource to c##bob;
SQL> grant dba to c##bob;
SQL> exit
C:\Users\Administrator>sqlplus
请输入用户名: sys as sysdba
输入口令:
连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table student( stu_id number not null, stu_name varchar2(20), stu_age
number, status varchar(3), version number default 0) tablespace test;
SQL> alter table student add(class_id number);
SQL> alter table student drop column class_id;
SQL> desc student;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
STU_ID NOT NULL NUMBER
STU_NAME VARCHAR2(20)
STU_AGE NUMBER
STATUS VARCHAR2(3)
VERSION NUMBER
SQL> drop table student cascade constraints ;
表已删除。
- dual tables:
SQL> select * from dual;
DU
--
X
SQL> select 3*4+5 from dual;
3*4+5
----------
17
SQL> select sysdate from dual;
SYSDATE
--------------
28-6月 -20
CREATE TABLE "SYS"."STUDENT1"
( "STU_ID" NUMBER NOT NULL ENABLE,
"STU_NAME" VARCHAR2(20 BYTE),
"STU_AGE" NUMBER,
"STATUS" VARCHAR2(3 BYTE),
"VERSION" NUMBER DEFAULT 0,
"CLASS_ID" VARCHAR2(20 BYTE)
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ;
SQL 2: Oracle Query
-
ORA-12560 username problem cannot login
-
List Oracle “databases” visible to the current user: equivalent to show databases
SQL> SELECT username AS schema_name
FROM all_users
ORDER BY username;
- List Oracle “databases” owned by the current user:
SQL> SELECT username AS schema_name
FROM user_users
ORDER BY username;
- Get the name of the current database schema you are connected to in Oracle:
SQL> SELECT user AS schema_name
FROM dual;
- Show all tables in Oracle (requires privileges on dba_tables):
SQL> SELECT table_name
FROM dba_tables
ORDER BY table_name;
- List tables in Oracle that the current user has access to:
SQL> SELECT table_name
FROM all_tables
ORDER BY table_name;
- Show tables owned by the current user:
SQL> SELECT table_name
FROM user_tables
ORDER BY table_name;
- Show table
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('1', 'a', '22', 'aa', '11', 'aaa')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('2', 'b', '33', 'bb', '22', 'bbb')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('3', 'c', '44', 'cc', '33', 'ccc')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('4', 'd', '55', 'dd', '44', 'ddd')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('5', 'e', '66', 'ee', '55', 'eee')
SQL> select * from student1;
STU_ID STU_NAME STU_AGE STATUS VERSION CLASS_ID
---------- ---------------------------------------- ---------- ------ -----------------------------------------------
1 a 22 aa 11 aaa
2 b 33 bb 22 bbb
3 c 44 cc 33 ccc
4 d 55 dd 44 ddd
5 e 66 ee 55 eee
- where
SQL> select * from student1 where stu_age=22;
STU_ID STU_NAME STU_AGE STATUS VERSION CLASS_ID
---------- ---------------------------------------- ---------- ------ -----------------------------------------------
1 a 22 aa 11 aaa
- order by
SQL> select * from student1 where stu_age>22 order by class_id desc;
STU_ID STU_NAME STU_AGE STATUS VERSION CLASS_ID
---------- ---------------------------------------- ---------- ------ -----------------------------------------------
5 e 66 ee 55 eee
4 d 55 dd 44 ddd
3 c 44 cc 33 ccc
2 b 33 bb 22 bbb
- select column
SQL> select stu_id from student1 ;
STU_ID
----------
1
2
3
4
5
- group by
SQL> select stu_id, count(stu_name) headcount from student1 group by stu_id ;
STU_ID HEADCOUNT
---------- ----------
1 1
2 1
4 1
5 1
3 1
SQL> select stu_id, max(stu_age)stu_age_max from student1 group by stu_id order by stu_id
desc ;
STU_ID STU_AGE_MAX
---------- -----------
5 66
4 55
3 44
2 33
1 22
- emp table & dept table
create table emp(empno number not null,
ename varchar2(20),
job VARCHAR2(20),
mgr number,
hiredate date,
sale INT,
comm int,
deptno number)tablespace users;
insert all
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7369,'smith', 'clerk', 7902, '17-DEC-80',800, null, 20)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7499,'allen','salesman', 7902, '20-FEB-81',1600, 300, 30)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7521,'ward','salesman' ,7698, '22-FEB-81',1250,500,30)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7566,'jones','manager', 7839, '02-APR-81',2975, null,20)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7654,'martin','salesman', 7698, '28-SEP-81',1250,1400,30)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7698,'blake','manager', 7839, '01-MAY-81',2850, null, 30)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7782,'clark','manager', 7839, '09-JUN-81', 2450, null,10)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7839,'king','president', null, '17-NOV-81',5000,null,10)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7844,'turner','salesman', 7698, '08-SEP-81',1500,0 ,30)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7900,'james','clerk', 7782, '03-DEC-81',950, null, 30)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7902,'ford','analyst', 7566, '03-DEC-81',3000, null, 20)
into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(7934,'miller','clerk', 7782, '23-JAN-82', 1300, null, 10)
select * from dual;
create table dept(deptno number not null,
dname varchar2(20),
loc VARCHAR2(20)
)tablespace users;
insert all
into dept(deptno, dname, loc) values
(10, 'accounting', 'new york')
into dept(deptno, dname, loc) values
(20, 'research', 'dallas')
into dept(deptno, dname, loc) values
(30, 'sales', 'chicago')
into dept(deptno, dname, loc) values
(40, 'operations' , 'boston')
select * from dual;
- page search 分页查询
- no order
select * from emp;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7902 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10
7839 king president 17-NOV-81 5000 10
7844 turner salesman 7698 08-SEP-81 1500 0 30
7900 james clerk 7782 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10*/
select * from ( select Rownum as rowno, t.*
from emp t
where hiredate between to_date('19800101','yyyymmdd')
and to_date('19820101','yyyymmdd')
and rownum<=10) table_alias
where table_alias.rowno >=5;
/*
ROWNO, EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO
5 7654 martin salesman 7698 28-SEP-81 1250 1400 30
6 7698 blake manager 7839 01-MAY-81 2850 30
7 7782 clark manager 7839 09-JUN-81 2450 10
8 7839 king president 17-NOV-81 5000 10
9 7844 turner salesman 7698 08-SEP-81 1500 0 30
10 7900 james clerk 7782 03-DEC-81 950 30
*/
- page search 分页查询
SELECT * FROM
(SELECT a.*, ROWNUM rn
FROM (SELECT * FROM table_name) a
WHERE ROWNUM <= 40)
WHERE rn >= 21
- ordered
select * from ( select Rownum as rowno, tt.*
from (select t.* from emp t
where hiredate between to_date('19800101','yyyymmdd')
and to_date('19820101','yyyymmdd')
order by t.ename desc, empno)tt
where rownum<=10) table_alias
where table_alias.rowno >=5;
/*
ROWNO, EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO
5 7839 king president 17-NOV-81 5000 10
6 7566 jones manager 7839 02-APR-81 2975 20
7 7900 james clerk 7782 03-DEC-81 950 30
8 7902 ford analyst 7566 03-DEC-81 3000 20
9 7782 clark manager 7839 09-JUN-81 2450 10
10 7698 blake manager 7839 01-MAY-81 2850 30*/
- sub-queries (child-query) (nested query) 子查询
select empno, ename, sale from emp where sale= (select max(sale) from emp);
/*
EMPNO, ENAME, SALE
7839 king 5000
*/
- sub-query: create
create table emp2 as select empno, ename from emp;
- sub-query: update
select * from emp2;
/*
EMPNO, ENAME
7369 smith
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
update emp2 set empno=(select empno from emp where empno=7499) where empno=7369;
select * from emp2;
/*
EMPNO, ENAME
7499 smith
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
- sub-query: VIEW
create or replace view empview as select empno, ename from emp;
select * from empview;
/*
EMPNO, ENAME
7369 smith
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
- join : cross join (3*5=15)
select * from emp, dept;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO, DEPTNO_1, DNAME, LOC
7369 smith clerk 7902 17-DEC-80 800 20 10 accounting new york
7499 allen salesman 7902 20-FEB-81 1600 300 30 10 accounting new york
7521 ward salesman 7698 22-FEB-81 1250 500 30 10 accounting new york
7566 jones manager 7839 02-APR-81 2975 20 10 accounting new york
7654 martin salesman 7698 28-SEP-81 1250 1400 30 10 accounting new york
7698 blake manager 7839 01-MAY-81 2850 30 10 accounting new york
7782 clark manager 7839 09-JUN-81 2450 10 10 accounting new york
7839 king president 17-NOV-81 5000 10 10 accounting new york
7844 turner salesman 7698 08-SEP-81 1500 0 30 10 accounting new york
7900 james clerk 7782 03-DEC-81 950 30 10 accounting new york
7902 ford analyst 7566 03-DEC-81 3000 20 10 accounting new york
7934 miller clerk 7782 23-JAN-82 1300 10 10 accounting new york
7369 smith clerk 7902 17-DEC-80 800 20 20 research dallas
7499 allen salesman 7902 20-FEB-81 1600 300 30 20 research dallas
7521 ward salesman 7698 22-FEB-81 1250 500 30 20 research dallas
7566 jones manager 7839 02-APR-81 2975 20 20 research dallas
7654 martin salesman 7698 28-SEP-81 1250 1400 30 20 research dallas
7698 blake manager 7839 01-MAY-81 2850 30 20 research dallas
7782 clark manager 7839 09-JUN-81 2450 10 20 research dallas
7839 king president 17-NOV-81 5000 10 20 research dallas
7844 turner salesman 7698 08-SEP-81 1500 0 30 20 research dallas
7900 james clerk 7782 03-DEC-81 950 30 20 research dallas
7902 ford analyst 7566 03-DEC-81 3000 20 20 research dallas
7934 miller clerk 7782 23-JAN-82 1300 10 20 research dallas
7369 smith clerk 7902 17-DEC-80 800 20 30 sales chicago
7499 allen salesman 7902 20-FEB-81 1600 300 30 30 sales chicago
7521 ward salesman 7698 22-FEB-81 1250 500 30 30 sales chicago
7566 jones manager 7839 02-APR-81 2975 20 30 sales chicago
7654 martin salesman 7698 28-SEP-81 1250 1400 30 30 sales chicago
7698 blake manager 7839 01-MAY-81 2850 30 30 sales chicago
7782 clark manager 7839 09-JUN-81 2450 10 30 sales chicago
7839 king president 17-NOV-81 5000 10 30 sales chicago
7844 turner salesman 7698 08-SEP-81 1500 0 30 30 sales chicago
7900 james clerk 7782 03-DEC-81 950 30 30 sales chicago
7902 ford analyst 7566 03-DEC-81 3000 20 30 sales chicago
7934 miller clerk 7782 23-JAN-82 1300 10 30 sales chicago
7369 smith clerk 7902 17-DEC-80 800 20 40 operations boston
7499 allen salesman 7902 20-FEB-81 1600 300 30 40 operations boston
7521 ward salesman 7698 22-FEB-81 1250 500 30 40 operations boston
7566 jones manager 7839 02-APR-81 2975 20 40 operations boston
7654 martin salesman 7698 28-SEP-81 1250 1400 30 40 operations boston
7698 blake manager 7839 01-MAY-81 2850 30 40 operations boston
7782 clark manager 7839 09-JUN-81 2450 10 40 operations boston
7839 king president 17-NOV-81 5000 10 40 operations boston
7844 turner salesman 7698 08-SEP-81 1500 0 30 40 operations boston
7900 james clerk 7782 03-DEC-81 950 30 40 operations boston
7902 ford analyst 7566 03-DEC-81 3000 20 40 operations boston
7934 miller clerk 7782 23-JAN-82 1300 10 40 operations boston
*/
- join : inner join
select * from emp inner join dept on dept.deptno= emp.deptno and emp.deptno=10;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO, DEPTNO_1, DNAME, LOC
7782 clark manager 7839 09-JUN-81 2450 10 10 accounting new york
7839 king president 17-NOV-81 5000 10 10 accounting new york
7934 miller clerk 7782 23-JAN-82 1300 10 10 accounting new york
*/
select * from emp, dept where dept.deptno= emp.deptno;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO, DEPTNO_1, DNAME, LOC
7369 smith clerk 7902 17-DEC-80 800 20 20 research dallas
7499 allen salesman 7902 20-FEB-81 1600 300 30 30 sales chicago
7521 ward salesman 7698 22-FEB-81 1250 500 30 30 sales chicago
7566 jones manager 7839 02-APR-81 2975 20 20 research dallas
7654 martin salesman 7698 28-SEP-81 1250 1400 30 30 sales chicago
7698 blake manager 7839 01-MAY-81 2850 30 30 sales chicago
7782 clark manager 7839 09-JUN-81 2450 10 10 accounting new york
7839 king president 17-NOV-81 5000 10 10 accounting new york
7844 turner salesman 7698 08-SEP-81 1500 0 30 30 sales chicago
7900 james clerk 7782 03-DEC-81 950 30 30 sales chicago
7902 ford analyst 7566 03-DEC-81 3000 20 20 research dallas
7934 miller clerk 7782 23-JAN-82 1300 10 10 accounting new york
*/
- join : outer join
select * from emp full join dept on dept.deptno= emp.deptno and dept.deptno=10;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO, DEPTNO_1, DNAME, LOC
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7902 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10 10 accounting new york
7839 king president 17-NOV-81 5000 10 10 accounting new york
7844 turner salesman 7698 08-SEP-81 1500 0 30
7900 james clerk 7782 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10 10 accounting new york
40 operations boston
30 sales chicago
20 research dallas
*/
select * from emp full join dept on dept.deptno= emp.deptno ;
/*
7369 smith clerk 7902 17-DEC-80 800 20 20 research dallas
7499 allen salesman 7902 20-FEB-81 1600 300 30 30 sales chicago
7521 ward salesman 7698 22-FEB-81 1250 500 30 30 sales chicago
7566 jones manager 7839 02-APR-81 2975 20 20 research dallas
7654 martin salesman 7698 28-SEP-81 1250 1400 30 30 sales chicago
7698 blake manager 7839 01-MAY-81 2850 30 30 sales chicago
7782 clark manager 7839 09-JUN-81 2450 10 10 accounting new york
7839 king president 17-NOV-81 5000 10 10 accounting new york
7844 turner salesman 7698 08-SEP-81 1500 0 30 30 sales chicago
7900 james clerk 7782 03-DEC-81 950 30 30 sales chicago
7902 ford analyst 7566 03-DEC-81 3000 20 20 research dallas
7934 miller clerk 7782 23-JAN-82 1300 10 10 accounting new york
40 operations boston
*/
- join outer join: left join
select * from emp left join dept on dept.deptno= emp.deptno and dept.deptno=10;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO, DEPTNO_1, DNAME, LOC
7782 clark manager 7839 09-JUN-81 2450 10 10 accounting new york
7839 king president 17-NOV-81 5000 10 10 accounting new york
7934 miller clerk 7782 23-JAN-82 1300 10 10 accounting new york
7499 allen salesman 7902 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7844 turner salesman 7698 08-SEP-81 1500 0 30
7900 james clerk 7782 03-DEC-81 950 30
7369 smith clerk 7902 17-DEC-80 800 20
7566 jones manager 7839 02-APR-81 2975 20
7902 ford analyst 7566 03-DEC-81 3000 20
*/
- join outer join: right join
select * from emp right join dept on dept.deptno= emp.deptno and dept.deptno=10;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO, DEPTNO_1, DNAME, LOC
7782 clark manager 7839 09-JUN-81 2450 10 10 accounting new york
7839 king president 17-NOV-81 5000 10 10 accounting new york
7934 miller clerk 7782 23-JAN-82 1300 10 10 accounting new york
30 sales chicago
40 operations boston
20 research dallas
*/
- collection operation : Union (merge), Union All (no merge), intersect, minus
update emp2 set empno=5555 where empno=7499;
select * from emp2;
/*
EMPNO, ENAME
5555 smith
5555 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
select * from emp;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7902 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10
7839 king president 17-NOV-81 5000 10
7844 turner salesman 7698 08-SEP-81 1500 0 30
7900 james clerk 7782 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10
*/
select empno, ename from emp union select empno, ename from emp2;
/*
EMPNO, ENAME
5555 allen
5555 smith
7369 smith
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
select empno, ename from emp2 union all select empno, ename from emp;
/*
EMPNO, ENAME
5555 smith
5555 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
7369 smith
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
select empno, ename from emp2 intersect select empno, ename from emp;
/*
EMPNO, ENAME
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
select empno, ename from emp2 minus select empno, ename from emp;
/*
EMPNO, ENAME
5555 allen
5555 smith
*/
- index create and use (use rowid if no index made) (save time for search for single index)
create table indexTest (id int, sex char(1), name char(10));
begin for i in 1..1000000
loop insert into indexTest values (i, 'a', 'abc');
end loop;
commit;
end;
select id, sex, name from indextest where id=23456;
/*
ID, SEX, NAME
23456 a abc
//.051 sec
*/
create index iTest1 on indexTest(id);
select id, sex, name from indextest where id=23456;
/*
ID, SEX, NAME
23456 a abc
//.032 sec
*/
create index iTest2 on indexTest(upper(name));
alter index iTest1 rebuild;
drop index itest2;
- reset user
SQL> drop user C##BOB;
用户已删除。
SQL> create user c##bob identified by "xxx12345" default tablespace test;\
用户已创建。
SQL> conn system/manager as sysdba;
已连接。
SQL> grant connect, resource to c##bob;
授权成功。
SQL> grant dba to c##bob;
授权成功。
- JDBC oracle connection
- for java.sql.SQLException: Io 异常: Got minus one from a read call, add following:
- add \SQL\oui\jlib\classes12.jar and \SQL\jdbc\lib\ojdbc8.jar files into WEB-INF folder
- add \SQL\oui\jlib\classes12.jar;\SQL\jdbc\lib\ojdbc8.jar; to classpath
- add \SQL\oui\jlib\classes12.jar and \SQL\jdbc\lib\ojdbc8.jar to \WEB-INF\lib
- add \SQL\oui\jlib\classes12.jar and \SQL\jdbc\lib\ojdbc8.jar to \tomcat\lib
- for java.sql.SQLException: ORA-28040, add “ SQLNET.ALLOWED_LOGON_VERSION=8 ” to D\SQL\network\admin\sqlnet.ora
package oracle.jdbc.connection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
public class ConnectTest {
public static void main(String[] args) {
Connection connection = null;
PreparedStatement ps = null;
ResultSet result = null;
try {
System.out.println("begin GetConnection");
Class.forName("oracle.jdbc.driver.OracleDriver");
System.out.println("begin Driver");
String url = "jdbc:oracle:thin:@127.0.0.1:1521:sts2";
connection = DriverManager.getConnection(url, "c##bob", "xxx12345");
System.out.println("Connected");
String sql = "select * from emp where empno=?";
ps = connection.prepareStatement(sql);
ps.setInt(1, 7900);
result = ps.executeQuery();
while (result.next()) {
System.out.println(result.getInt("empno") + "Name:" + result.getString("ename"));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (ClassNotFoundException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} finally {
try {
if (result != null) {
result.close();
}
if (ps != null) {
ps.close();
}
if (connection != null) {
connection.close();
}
System.out.println("sql closed");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
//begin GetConnection
//begin Driver
//Connected
//7900Name:james
//sql closed
SQL 3: Oracle Add, Delete, Update
- Insert
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('1', 'a', '22', 'aa', '11', 'aaa')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('2', 'b', '33', 'bb', '22', 'bbb')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('3', 'c', '44', 'cc', '33', 'ccc')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('4', 'd', '55', 'dd', '44', 'ddd')
SQL> INSERT INTO "SYS"."STUDENT1" (STU_ID, STU_NAME, STU_AGE, STATUS, VERSION, CLASS_ID) VALUES ('5', 'e', '66', 'ee', '55', 'eee')
SQL> select * from student1;
STU_ID STU_NAME STU_AGE STATUS VERSION CLASS_ID
---------- ---------------------------------------- ---------- ------ -----------------------------------------------
1 a 22 aa 11 aaa
2 b 33 bb 22 bbb
3 c 44 cc 33 ccc
4 d 55 dd 44 ddd
5 e 66 ee 55 eee
SQL> create table student2( stu_id number not null, stu_name varchar2(20), stu_age number, status varchar(3), version number default 0) tablespace users;
表已创建。
SQL> select table_name, tablespace_name from user_tables where table_name='STUDENT2';
TABLE_NAME
--------------------------------------------------------------------------------
TABLESPACE_NAME
------------------------------------------------------------
STUDENT2
USERS
SQL> insert into student2(stu_id, stu_name)
select stu_id, stu_name
from student1;
已创建 7 行。
SQL> select * from student2;
STU_ID STU_NAME STU_AGE STATUS VERSION
---------- ---------------------------------------- ---------- ------ ----------
2 Bob 0
1 a 0
2 b 0
3 c 0
4 d 0
5 e 0
2 Bob 0
已选择 7 行。
-
Update
select * from students;
update student1 set status = 0 where stu_id=2;
update student1 set status = 0, stu_age=18 where stu_id=2;
- delete: keep original structure DML
delete from student1 where stu_id=1;
- truncate: faster DDL
SQL> truncate table student2;
表被截断。
SQL 4: Oracle function, View, Save
- format
- to_char : use 0 for filling space, 9 for filling number, use ‘,’ to separate
select to_char(11.11,'000.000') from dual
# 011.110
select to_char(11.11,'900.009') from dual
# 11.110
select to_char(11.11,'999.999') result from dual;
# result
# 11.110
select to_char(123456,'999,999,999.00') result from dual;
# result
# 123,456.00
- FM format mask get rid of 0s
- add $
select to_char(11.11,'FM999.999') from dual;
# 11.11
select to_char(11.11,'FM$999.999') from dual;
# $11.11
- FMY, FM…C(currency)
select to_char(11.11,'FML999.999') result from dual;
# ￥11.11
select to_char(11.11,'FM999.999C') result from dual;
# 11.11CNY
- Dec to Hex
select to_char(255,'XX') result from dual;
select to_char(255,'XXX') result from dual;
# FF
- pad: lpad or rpad
select lpad('2',5,0) result from dual;
# 20000
select rpad('2',5,0) result from dual;
# 20000
- initcap
select initcap('abc') result from dual;
# Abc
select initcap('abc_cba') result from dual;
# Abc_Cba
- Date
select sysdate from dual;
#22-FEB-21
select current_date from dual;
#22-FEB-21
select current_timestamp from dual;
#22-FEB-21 03.56.49.223000000 PM ASIA/SHANGHAI
- add month
select add_months(sysdate,1) from dual;
#23-MAR-21
- last day
select last_day(sysdate) from dual;
#28-FEB-21
- trunc
select trunc(sysdate, 'YEAR') from dual;
#01-JAN-21
select trunc(sysdate, 'MONTH') from dual;
#01-FEB-21
- to_char
- sysdate only accurate to seconds
select to_char(sysdate, 'YYYY-MM-DD') from dual;
#2021-02-24
select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF') from dual;
#2021-02-24 11:27:35.673000
select to_char(sysdate, 'YEAR') from dual;
#TWENTY TWENTY-ONE
select to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS.FF') from dual;
#2021-02-24 11:32:00.530000
- decode
select instr(job, 'salesman') from emp;
#INSTR(JOB,'SALESMAN')
#0
#1
#1
#0
#1
#0
#0
#0
#1
#0
#0
#0
select decode(instr(job,'salesman'), 1, 'sales', 'tech') from emp;
/*DECODE(INSTR(JOB,'SALESMAN'),1,'SALES','TECH')
tech
sales
sales
tech
sales
tech
tech
tech
sales
tech
tech
tech*/
- nyl : 999 replaces null
select ename, nvl(comm,999) from emp;
/*
ENAME NVL(COMM,999)
smith 999
allen 300
ward 500
jones 999
martin 1400
blake 999
clark 999
king 999
turner 0
james 999
ford 999
miller 999
*/
- rownum
select ename, rownum from emp;
/*ENAME, ROWNUM
smith 1
allen 2
ward 3
jones 4
martin 5
blake 6
clark 7
king 8
turner 9
james 10
ford 11
miller 12*/
select empno,ename, rownum from emp order by ename desc;
/*
EMPNO, ENAME, ROWNUM
7521 ward 3
7844 turner 9
7369 smith 1
7934 miller 12
7654 martin 5
7839 king 8
7566 jones 4
7900 james 10
7902 ford 11
7782 clark 7
7698 blake 6
7499 allen 2
*/
select empno,ename, rownum from emp where rownum <=3 order by ename desc;
/*
EMPNO, ENAME, ROWNUM
7521 ward 3
7369 smith 1
7499 allen 2
*/
select * from emp order by ename desc;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO
7521 ward salesman 7698 22-FEB-81 1250 500 30
7844 turner salesman 7698 08-SEP-81 1500 0 30
7369 smith clerk 7902 17-DEC-80 800 20
7934 miller clerk 7782 23-JAN-82 1300 10
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7839 king president 17-NOV-81 5000 10
7566 jones manager 7839 02-APR-81 2975 20
7900 james clerk 7782 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7782 clark manager 7839 09-JUN-81 2450 10
7698 blake manager 7839 01-MAY-81 2850 30
7499 allen salesman 7902 20-FEB-81 1600 300 30
*/
select e.*, rownum from emp e where rownum <= 3;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO, ROWNUM
7369 smith clerk 7902 17-DEC-80 800 20 1
7499 allen salesman 7902 20-FEB-81 1600 300 30 2
7521 ward salesman 7698 22-FEB-81 1250 500 30 3
*/
select e.*, rownum from (select empno, ename from emp order by ename desc) e where rownum <= 3;
/*
EMPNO, ENAME, ROWNUM
7521 ward 1
7844 turner 2
7369 smith 3
*/
- cast …as： change type
select cast(empno as varchar2(20)) from emp;
/*CAST(EMPNOASVARCHAR2(20))
7369
7499
7521
7566
7654
7698
7782
7839
7844
7900
7902
7934*
- count
select count (empno) from emp where sale >=5000;
//COUNT(EMPNO)
//1
- PLSQL : Procedural Language (Declare, Begin, End, Exception)…
- pl/sql if
set serveroutput on
begin
if 1=1 then
dbms_output.put_line('equal');
end if;
end;
//PL/SQL procedure successfully completed.
//equal
set serveroutput on
declare emp_count number;
begin
select count(empno) into emp_count from emp where sale>=5000;
if emp_count=1 then
dbms_output.put_line('1 higher than 5000');
elsif emp_count>1 then
dbms_output.put_line('more than 1 higher than 5000');
else
dbms_output.put_line('0 higher than 5000');
end if;
end;
//PL/SQL procedure successfully completed.
//1 higher than 5000
- pl/sql case when
- case var/expr when val1 … when val2 …else default end case
- case when boolexpr1 … when boolexpr2 … else default end case
declare emp_count1 number;
begin
select count(empno) into emp_count1 from emp where sale>=5000;
case emp_count1
when 1 then
dbms_output.put_line('1 higher than 5000');
when 0 then
dbms_output.put_line('0 higher than 5000');
else
dbms_output.put_line('more than 1 higher than 5000');
end case;
end;
//PL/SQL procedure successfully completed.
//1 higher than 5000
declare emp_count2 number;
begin
select count(empno) into emp_count2 from emp where sale>=5000;
case
when emp_count2=1 then
dbms_output.put_line('1 higher than 5000');
when emp_count2=0 then
dbms_output.put_line('0 higher than 5000');
else
dbms_output.put_line('more than 1 higher than 5000');
end case;
end;
//PL/SQL procedure successfully completed.
//1 higher than 5000
- view create use
请输入用户名: sys as sysdba
输入口令:
连接到:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> grant create view to c##bob;
//授权成功。
create view view_emp as select empno, ename from emp;
//View VIEW_EMP created.
select * from view_emp;
/*
EMPNO, ENAME
7369 smith
7499 allen
7521 ward
7566 jones
7654 martin
7698 blake
7782 clark
7839 king
7844 turner
7900 james
7902 ford
7934 miller
*/
create view view_emp_dept as select empno, ename, dname from emp e
inner join dept d on e.deptno=d.deptno;
select * from view_emp_dept;
/*
EMPNO, ENAME, DNAME
7369 smith research
7499 allen sales
7521 ward sales
7566 jones research
7654 martin sales
7698 blake sales
7782 clark accounting
7839 king accounting
7844 turner sales
7900 james sales
7902 ford research
7934 miller accounting
*/
- find view
select view_name, text from user_views where view_name=upper('view_emp_dept');
/*
VIEW_NAME, TEXT
VIEW_EMP_DEPT "select empno, ename, dname from emp e inner join dept d on e.deptno=d.deptno"*/
- alter (replace), update view
create or replace view view_emp as select empno, ename, job from emp;
select * from view_emp;
/*
EMPNO, ENAME, JOB
7369 smith clerk
7499 allen salesman
7521 ward salesman
7566 jones manager
7654 martin salesman
7698 blake manager
7782 clark manager
7839 king president
7844 turner salesman
7900 james clerk
7902 ford analyst
7934 miller clerk
*/
create view view_dept(dept_name) as select dname from dept where deptno=30;
select * from view_dept;
/*
DEPT_NAME
sales
*/
update view_dept set dept_name='!SALES';
/*
DEPT_NAME
!SALES
*/
create view view_dept_count as select count(deptno) total from dept;
select * from view_dept_count;
/*
TOTAL
4
*/
update view_dept_count set total=40;
//SQL Error: ORA-01732: data manipulation operation not legal on this view
- delete (drop) view
drop view view_dept_count ;
//View VIEW_DEPT_COUNT dropped.
- trigger: create trigger (name) on (Object) before | after (event) as (command)
create or replace trigger t_dept before insert on dept
begin
if USER!='ADMIN' then
raise_application_error(-20001,'not accessible, cannot insert');
end if;
end;
//Trigger T_DEPT compiled
insert into dept values(50, 'admin', 'london');
//ORA-20001: not accessible, cannot insert
- inserting, updating, deleting trigger log
create table emp_log
(
user_name varchar2(20),
action varchar2(20),
log_time date
)
//Table EMP_LOG created.
create or replace trigger trig_emp_log
after insert or update or delete
on emp
begin
if inserting then
insert into emp_log values(user, 'insert', sysdate);
end if;
if updating then
insert into emp_log values(user, 'update', sysdate);
end if;
if deleting then
insert into emp_log values(user, 'delete', sysdate);
end if;
end;
//Trigger TRIG_EMP_LOG compiled
insert all into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(1111,'smith', 'clerk', 7902, '17-DEC-80',800, null, 20) select * from dual;
update emp set ename='sammy' where empno=1111;
delete from emp where empno=1111;
select * from emp_log;
//USER_NAME, ACTION, LOG_TIME
//C##BOB insert 09-MAR-21
//C##BOB update 09-MAR-21
//C##BOB delete 09-MAR-21
alter trigger trig_emp_log disable;
alter trigger trig_emp_log enable;
- find trigger
select * from user_objects where lower(object_type)='trigger';
//OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID
//T_DEPT 104267 TRIGGER 09-MAR-21 09-MAR-21 2021-03-09:12:00:01 VALID N N N 3 NONE Y N N USING_NLS_COMP N N
//TRIG_EMP_LOG 104269 TRIGGER 09-MAR-21 09-MAR-21 2021-03-09:12:19:01 VALID N N N 3 NONE Y N N USING_NLS_COMP N N
select * from user_triggers where trigger_name=upper('trig_emp_log');
//TRIGGER_NAME, TRIGGER_TYPE, TRIGGERING_EVENT, TABLE_OWNER, BASE_OBJECT_TYPE, TABLE_NAME, COLUMN_NAME, REFERENCING_NAMES, WHEN_CLAUSE, STATUS, DESCRIPTION, ACTION_TYPE, TRIGGER_BODY, CROSSEDITION, BEFORE_STATEMENT, BEFORE_ROW, AFTER_ROW, AFTER_STATEMENT, INSTEAD_OF_ROW, FIRE_ONCE, APPLY_SERVER_ONLY
// TRIG_EMP_LOG AFTER STATEMENT INSERT OR UPDATE OR DELETE C##BOB TABLE EMP REFERENCING NEW AS NEW OLD AS OLD ENABLED "trig_emp_logafter insert or update or deleteon emp" PL/SQL "beginif inserting theninsert into emp_log values(user, 'insert', sysdate);end if;if updating theninsert into emp_log values(user, 'update', sysdate);end if;if deleting theninsert into emp_log values(user, 'delete', sysdate);end if;end;" NO NO NO NO NO NO YES NO
- delete drop trigger
drop trigger trig_emp_log;
- sequence create and use
create sequence emp_seq;
select * from user_objects where object_type=upper('sequence');
//OBJECT_NAME, OBJECT_TYPE
//EMP_SEQ SEQUENCE
select * from user_sequences;
select * from user_sequences where sequence_name=upper('emp_seq');
- currval, nextval: empty sequnce use nextval
select emp_seq.currval from dual;
//ORA-08002: sequence EMP_SEQ.CURRVAL is not yet defined in this session
select emp_seq.nextval from dual;
//NEXTVAL
//1
select emp_seq.currval from dual;
//CURRVAL
//1
insert all into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(emp_seq.nextval,'abc', 'clerk', 7902, '1-DEC-88',111, null,22) select * from dual;
select * from emp;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7902 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10
7839 king president 17-NOV-81 5000 10
7844 turner salesman 7698 08-SEP-81 1500 0 30
7900 james clerk 7782 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10
2 abc clerk 7902 01-DEC-88 111 22
*/
drop sequence emp_seq;
create sequence emp_seq start with 8;
insert all into emp(empno , ename , job , mgr ,hiredate ,sale,comm ,deptno) values
(emp_seq.nextval,'abc', 'clerk', 7902, '1-DEC-88',111, null,22) select * from dual;
select * from emp;
/*
EMPNO, ENAME, JOB, MGR, HIREDATE, SALE, COMM, DEPTNO
7369 smith clerk 7902 17-DEC-80 800 20
7499 allen salesman 7902 20-FEB-81 1600 300 30
7521 ward salesman 7698 22-FEB-81 1250 500 30
7566 jones manager 7839 02-APR-81 2975 20
7654 martin salesman 7698 28-SEP-81 1250 1400 30
7698 blake manager 7839 01-MAY-81 2850 30
7782 clark manager 7839 09-JUN-81 2450 10
7839 king president 17-NOV-81 5000 10
7844 turner salesman 7698 08-SEP-81 1500 0 30
7900 james clerk 7782 03-DEC-81 950 30
7902 ford analyst 7566 03-DEC-81 3000 20
7934 miller clerk 7782 23-JAN-82 1300 10
2 abc clerk 7902 01-DEC-88 111 22
8 abc clerk 7902 01-DEC-88 111 22*/
- procedure: create conditions
- create procedure | procedure name {data [in/out] datatype} a s Begin …End procedure name
create or replace procedure updateStatus as
begin
update emp set status=0;
end updateStatus;
/
create or replace procedure addEmp(v_name in varchar2, v_no in number) as
begin
begin
if ( v_name is null or length(v_name)=0) then return;
end if;
if ( v_no>30) then return;
end if;
insert into emp values (emp_seq.nextval, v_name,null, v_no, null, null, null, null);
end;
end addEmp;
create or replace procedure empCount(v_count out number)
as begin
begin
select count(empno) into v_count from emp;
end;
end empCount;
///
set serveroutput on
declare emp_count number;
begin
empCount(emp_count);
dbms_output.put_line('number:' || emp_count);
end;
// number:14
select * from user_objects where object_type= upper('procedure');
/*
OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID
UPDATESTATUS 104272 PROCEDURE 09-MAR-21 09-MAR-21 2021-03-09:13:47:40 INVALID N N N 1 NONE Y N N USING_NLS_COMP N N
ADDEMP 104273 PROCEDURE 09-MAR-21 09-MAR-21 2021-03-09:13:42:29 VALID N N N 1 NONE Y N N USING_NLS_COMP N N
EMPCOUNT 104274 PROCEDURE 09-MAR-21 09-MAR-21 2021-03-09:13:45:55 VALID N N N 1 NONE Y N N USING_NLS_COMP N N
*/
select * from user_source where type= upper('procedure');
/*
NAME, TYPE, LINE, TEXT, ORIGIN_CON_ID
ADDEMP PROCEDURE 1 "procedure addEmp(v_name in varchar2, v_no in number) as
" 1
ADDEMP PROCEDURE 2 "begin
" 1
ADDEMP PROCEDURE 3 "begin
" 1
ADDEMP PROCEDURE 4 "if ( v_name is null or length(v_name)=0) then return;
" 1
ADDEMP PROCEDURE 5 "end if;
" 1
ADDEMP PROCEDURE 6 "if ( v_no>30) then return;
" 1
ADDEMP PROCEDURE 7 "end if;
" 1
ADDEMP PROCEDURE 8 "insert into emp values (emp_seq.nextval, v_name,null, v_no, null, null, null, null);
" 1
ADDEMP PROCEDURE 9 "end;
" 1
ADDEMP PROCEDURE 10 end addEmp; 1
EMPCOUNT PROCEDURE 1 "procedure empCount(v_count out number)
" 1
EMPCOUNT PROCEDURE 2 "as begin
" 1
EMPCOUNT PROCEDURE 3 "begin
" 1
EMPCOUNT PROCEDURE 4 "select count(empno) into v_count from emp;
" 1
EMPCOUNT PROCEDURE 5 "end;
" 1
EMPCOUNT PROCEDURE 6 end empCount; 1
UPDATESTATUS PROCEDURE 1 "procedure updateStatus as
" 1
UPDATESTATUS PROCEDURE 2 "begin
" 1
UPDATESTATUS PROCEDURE 3 "update emp set status = 0;
" 1
UPDATESTATUS PROCEDURE 4 "commit;
" 1
UPDATESTATUS PROCEDURE 5 end updateStatus; 1
*/
select * from user_source where name= upper('updateStatus');
/*
NAME, TYPE, LINE, TEXT, ORIGIN_CON_ID
UPDATESTATUS PROCEDURE 1 "procedure updateStatus as
" 1
UPDATESTATUS PROCEDURE 2 "begin
" 1
UPDATESTATUS PROCEDURE 3 "update emp set status = 0;
" 1
UPDATESTATUS PROCEDURE 4 "commit;
" 1
UPDATESTATUS PROCEDURE 5 end updateStatus; 1
*/
drop procedure updateStatus;
- function: create or replace function func_name(param_name type) return type as begin sql end func_name
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
