Personal notes of database (2) – Basic

Time:2020-2-12

**Table creation and operation**

-Interface operation table

>To create a table:
>Step 1: open the database, expand Database in object explorer, right-click the table option under the database menu of pxscj, and select the new table menu item in the shortcut menu, as shown in the figure below
>

 

 

>Step 2: in the window of table designer, enter or select the name of each column, data type, allow null value and other attributes according to and designed XSB table structure. You can fill in the column Properties tab as needed.
>Step 3: right click the student number column, select the set primary key menu option, and select the set as primary key option, as shown in the figure.
>

 

 

>Fill in the default value and description of each column in the default value and binding and description items in the column Properties tab. The design result is as shown in the figure
>

 

 

>Step 4: after editing all column attributes of the table, click save in the toolbar to open the select Table Name dialog box. Fill in the table name XSB in the dialog box, and click OK to create the XSB table

 

>Modify table structure
>In the panel, click the tools main menu, select the options submenu, and in the options dialog box, select the table designer and database designer tab under designers to clear the check box before the prevent saving changes requiring re creation of tables, as shown in the figure below

 

 

>Next, you can modify the table, for example, change the table name: directly find the mark, right-click and select Rename.
>Column modification: add a column, find the table, right-click and choose the design menu option to open the table designer window. Delete. Similarly, right-click to select Delete.
>Modification needs attention
>

 

 

Delete table
>Find the table you want to delete and right-click to delete it

-Command mode operation table

Create table

“`
For example:
USE PXSCJ
go
CREATE TABLE XSB
(
Student number char (6) not null primary key, primary key
Name char (8) not null,
Gender bit null default 1,
Date of birth null,
Specialty char (12) null,
Total credits int null,
Remark varchar (500) null
)

CREATE TABLE PJCJ
(
Course number char (3) primary key,
Total score real not null,
Number of people int not null,
Average score as total score / number of people
)
“`

Alter table

“`
Add
ALTER TABLE XSB
Add scholarship level tinyint null

alter table xsb
add tel char(11) null
Case insensitive
“`

“`
Modify (Alter)
ALTER TABLE XSB
Alter column name char (10)

ALTER TABLE XSB
Alter column birth time smalldatetime
“`

“`
Delete (drop)
alter table xsb
drop column tel
“`
* * data * *

“`
Add data (insert into)
insert into xsb
Values (‘091101 ‘,’ Zhang San ‘,’ female ‘, 30,’ klajgklj ‘,’ big data ‘)

insert into xsb(xh)
values(‘091102’)

Update data
update xsb
Set XM = Li Si

update xsb
Set XM = ‘Zhao Wu’
where xh=’091101′

Delete data
delete
from xsb
where xh=’091102′

trancate from xsb
“`

 

Conclusion:

—Create table
— modifying tables
–Alter table
– add
– alter column
– drop column

–Modify data
– insert into
– update set
-Delete (fast truncate)