SQL ALTER增加、刪除、更改表單的欄目

常用軟體,程式語言的筆記和疑問
Forum rules
盡量做到:
每一個貼子都精心編輯;
每一個疑問都得到解答。
ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

SQL ALTER增加、刪除、更改表單的欄目

Post by ejsoon » 2016 May 02, 09:17

來自w3schools.com的教程

The ALTER TABLE Statement

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name

To change the data type of a column in a table, use the following syntax:

My SQL / Oracle (prior version 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype

rename a culumn in mysql:
ALTER TABLE `xyz` CHANGE `manufacurerid` `manufacturerid` INT;

rename a table:
RENAME TABLE tbl_name TO new_tbl_name
[, tbl_name2 TO new_tbl_name2] ...

delete a table:
drop table tbl_name;

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

SQL INSERT INTO

Post by ejsoon » 2016 May 02, 13:44

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

INSERT INTO Users SET id=1, weight=160, desiredWeight=145

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

ORDER BY

Post by ejsoon » 2016 Jun 27, 16:22

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

MYSQL LIMIT

Post by ejsoon » 2016 Jun 27, 16:23

SELECT column_name(s)
FROM table_name
LIMIT number;

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

SQL UPDATE and REPLACE

Post by ejsoon » 2016 Jun 29, 15:08

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

update my_table
set path = replace(path, 'oldstring', 'newstring')

Code: Select all

update `jfhuashi_archives` set `litpic` = replace(litpic, '/jfhuashi', '')

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

SQL SELECT

Post by ejsoon » 2016 Jun 30, 17:44

SELECT column_name,column_name FROM table_name;

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

SQL 語句起頭

Post by ejsoon » 2016 Aug 01, 16:47

Code: Select all

id INT(8) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
pw VARCHAR(100) CHARACTER SET utf8 COLLATE utf8_bin,
credit INT(3),
birth DATE,
sex BOOLEAN

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

CREATE TABLE

Post by ejsoon » 2016 Aug 12, 14:48

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

SQL UNIQUE

Post by ejsoon » 2016 Aug 17, 15:29

The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created:

MySQL:

Code: Select all

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

ejsoon
Site Admin
Posts: 3858
Joined: 2016 Jan 10, 22:15

SQL DEFAULT

Post by ejsoon » 2016 Aug 17, 15:35

The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:

My SQL / SQL Server / Oracle / MS Access:

Code: Select all

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)

Post Reply