Introduction to Databases

Database

MYSQL Port 3306

Oracle Port 1521

SQL Server Port 1433

Divided into

DDL definition class statements such as building table libraries to alter TABLE structure

DML Operation class statement data additions and deletions, etc.

Control of the DCL control statement permission control transaction

Ddl:

Build Library Create database name

Delete Library drop database name

Switch database Use library name

CREATE TABLE Table name (

Column list Type,

Column list Type

)

CREATE TABLE MyUser (

UID INT,

Uname VARCHAR (20),

Sex varchar (2)

);

Delete tables drop table myuser;

Modify table structure ALTER TABLE myuser ADD age int;

DML Operational Data

Add insert into table (column, column) values (value, value);

Insert into MyUser (uid,uname,sex,age) VALUES (1, ' Xiao Qiang ', ' Male ', 22);

Delete delete from table where condition

Delete from MyUser where uid>=3;

Delete from myuser where Uname= ' Zhang Sanfeng ' or uid=2;

Change

Update table Set column = value, column = value where condition

Update myuser set sex= ' female ', age=18 where uid=3 and Uname= ' Zhang Sanfeng '

Check

Select column, column, column from table where condition

* Represents all Columns

SELECT * FROM MyUser where age>18;

Column type

Numeric type

tinyint smallint int integer bigint

Float double

Character type

Char 0~255 fixed-length characters

Name Char (10) Space change time in ___

Insufficient right to fill the space on the query will be automatically removed

varchar 0~65535 bytes If it is utf-8 a Chinese character to handle 21,844 characters by 3 bytes

Name varchar (9) Accounts for three bytes time-swap space

Text 65,535 characters

Longtext 4G

Date type

DateTime Date Time is the day of the month

Timestamp date time by seconds storage

Large size segment stores some file XML text information

Blob 65535 byte

Longlob 4g

Query statements

Where condition

L column = value and and OR or > < >= <=! = is not equal to <> not equal to >< not established

The value of column in (value, value, value) column is one of the parentheses

SELECT * from MyUser where age in (18,20,23,500)

L column between value and value 1 column value >= value and <= value 1

SELECT * from MyUser where age between and 499;

L-column like fuzzy query% any character _ any one of the characters

SELECT * from MyUser where uname like '% sheet% '

L sort order BY column ASC Ascending (default) desc descending on the last side of SQL

SELECT * FROM MyUser ORDER BY uid DESC, age ASC;

Introduction to Databases