Shulou(Shulou.com)06/01 Report--

Review the MySQL creation table statement users (id integer / name string / birthday date type, default)

Drop table if exists users;create table users (id int (5) auto_increment primary key, name varchar (4) not null, birthday date default '2015-4-27')

Using oracleSQL, create the user table users (id integer / name string / birthday date / sal integer, default today)

Create table users (id number (5) primary key, name varchar2 (8) not null unique, sal number (6) not null, birthday date default sysdate)

Enter the Recycle Bin

Drop table users

Query objects in the Recycle Bin

Show recyclebin

Flashback, about to restore the Recycle Bin

Flashback table table name to before drop;flashback table table name to before drop rename to new table name

Completely delete the users table

Drop table users purge

Empty the Recycle Bin

Purge recyclebin

Test the following types

(1) number (5):

Insert into users (id,name,sal) values

Insert into users (id,name,sal) values

Insert into users (id,name,sal) values (111 majored AAAAAAAAI Magi 6666.66)

Insert into users (id,name,sal) values (1111 maths AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (999999 and AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (1000000 people, AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

5 means to save up to 99999.

(2) number (6. 2):

Col sal for 9999.99

Insert into users (id,name,sal) values

Insert into users (id,name,sal) values

Insert into users (id,name,sal) values (111 Personnal AAAAAAAAAI Magi 666.6666)

Insert into users (id,name,sal) values (1111 maths AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (11111 Magnum, AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Number (6Pol 2)

Among them, 2 indicates that up to 2 decimal places are displayed, rounding is used, less than 0 digits are added, and col is set at the same time. For...

Where 6 represents no more than 6 decimal + integer digits

The number of integer digits must not be more than 4 digits, which can be equal to 4 digits

(3) varchar2 (8):

Insert into users (id,name,sal) values

Insert into users (id,name,sal) values (2)

Insert into users (id,name,sal) values (3)

Insert into users (id,name,sal) values (4)

Insert into users (id,name,sal) values (5 mages AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (6 Magnum AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (7 recordsAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (8) AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (9) AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Insert into users (id,name,sal) values (1m'ha', 7777.77)

Insert into users (id,name,sal) values (2je '', 7777.77)

Insert into users (id,name,sal) values (3) '', 7777.77)

Insert into users (id,name,sal) values (4Jing '', 7777.77)

Insert into users (id,name,sal) values (5m'ha', 7777.77); wrong

Varchar2 (8)

8 represents byte

GBK 2 bytes of one Chinese character

UTF-8 3 bytes of a Chinese character

(4) date: default format is:'27-April-15'

(5) CLOB [Character Large OBject]: large text objects, that is, objects with more than 65565 bytes of data, which can store up to 4G

(6) BLOB [Binary Large OBject]: large binary objects, that is, pictures, audio, video, up to 4G storage

Add p_w_picpath column to users table, alter table table name add column name type (width)

Alter table usersadd p_w_picpath blob

Modify the length of the name column to 20 bytes, alter table table name modify column name type (width)

Alter table usersmodify name varchar2 (20)

Delete p_w_picpath column, alter table table name drop column column name

Alter table usersdrop column p_w_picpath

Duplicate column name name is username,alter table table name rename column original column name to new column name

Alter table usersrename column name to username

Rename the users table myusers,rename original table name to new table name

Rename users to myusers

Note: when you modify the table, the original data in the table will not be affected.

Written questions: there are [100 billion] member records, how to use the most efficient way to zero the salary field, the contents of other fields remain the same?

First: delete the sal field from the emp table

Alter table emp drop column sal

Second: add the sal field to the emp table, and the content defaults to 0

Alter table emp add sal number (6) default 0

Modified table cannot be rolled back

Create tables customers (single) and orders (multiple), using primary key/not null/unique/default/foreign key constraints

To embody [on delete cascade/on delete set null]

Requirements: delete the customer, cascade delete all his orders

Delete from customers where id = 1

Requirements: delete the customer, delete all his orders without cascading, just set the external key to NULL

Delete from customers where id = 1

Create table customers (id number (3) primary key, name varchar2 (4) not null unique); insert into customers (id,name) values (1); insert into customers (id,name) values (2) Create table orders (id number (3) primary key, isbn varchar2 (6) not null unique, price number (3) not null, cid number (3),-- constraint cid_FK foreign key (cid) references customers (id) on delete cascade constraint cid_FK foreign key (cid) references customers (id) on delete set null); insert into orders (id,isbn,price,cid) values Insert into orders (id,isbn,price,cid) values (3) insert into orders (id,isbn,price,cid) values (4)

Create table students, including id,name,gender,salary fields, using check constraints [gender can only be male or female, salary between 6000 and 8000]

Create table students (id number (3) primary key, name varchar2 (4) not null unique, gender varchar2 (2) not null check (gender in ('male', 'female'), salary number (6) not null check (salary between 6000 and 8000))

Insert into students (id,name,gender,salary) values (1) '', 'medium', 6000); wrong

Insert into students (id,name,gender,salary) values (2) 'hehe', 'male', 5000); wrong

Insert into students (id,name,gender,salary) values (3) 'hee hee', 'female', 7000)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.