Create a database
creat table Test (
#整数通常使用int
test_id int,
#小数通常使用decimal
Test_price Decimal,
#普通文本通常使用, and use default to specify defaults
Test_name varchar (255) Default "Xxx",
#大文本类型使用test
Test_desc text,
#图片使用blob
Test_img Blob,
#日期类型使用DateTime
Test_date datetime,
);
-----------------------------------------------------------------
Types of columns supported by MySQL
1.tinyint,smallint,mediumint,int,bigint
2.float,double
3.decimal (DEC)
4.date
5.time
6.datetime
7.timestamp
8.year
9.char
10.varchar
11.binary (fixed-length binary string type, saving strings in binary form)
12.varbinary
13.tinyblob,blob,mediumblob,longblob
14.tinytext,text,mediumtext,longtext
15.enum (' value1 ', ' value2 ' ...) Enum type (only one of them)
16.set (' value1 ', ' value2 ' ...) Collection type (which can be several)
--------------------------------------------------------------------
#创建数据表, the data table is exactly the same as the user_info and the data is exactly the same
CREATE TABLE hehe
As
SELECT * from User_info;
---------------------------------------------------------------------
#修改表的结构的语法
Alert Table Table Name
Add
#可以定义多个列定义
Colum_name datatype [Default expr],
...
);
---------------------------------------------------------------------
#为hehe数据表增加一个hehe_id字段, the field type is int
ALTER TABLE hehe
add hehe_id int;
#为hehe数据包增加aaa, BBB field, two field types are varchar (25)
ALTER TABLE hehe
Add AAA varchar (+), BBB varchar (25);
----------------------------------------------------------------------
#将hehe表的hehe_id列修改为varchar (255) type
ALTER TABLE hehe
Modify hehe_id varchar (255);
#将hehe表的bbb列修改为int类型
ALTER TABLE hehe
modify BBB int;
----------------------------------------------------------------------
#删除指定的列
ALTER TABLE hehe
Drop column_name
#重命名数据表
ALTER TABLE hehe
Rename to Wawa;
----------------------------------------------------------------------
#将wawa表的字段bbb字段重命名为ddd
ALTER TABLE Wawa
change BBB DDD int;
#删除表
DROP table Name
----------------------------------------------------------------------
Database constraints
NOT NULL
Unique
Primary key
FOREIGN key
Check
#not NULL constraint
CREATE TABLE hehe (
#建立了非空约束, which means that hehe_id is not nullable
hehe_id int NOT NULL,
#mysql为空约束不能指定名字
Hehe_name varchar (+) Default ' xyz ' NOT NULL,
#下面列可以为空, the default value is NULL
Hehe_gender varchar (+) NULL
);
---------------------------------------------------------------------
#增加非空约束
ALTER TABLE hehe
Modify Hehe_gender varchar (+) NOT NULL
#取消非空约束
ALTER TABLE hehe
Modify Hehe_name varchar (3) null;
#取消非空约束 and specify a default value
ALTER TABLE hehe
Modify Hehe_name varchar (255) Default ' abc ' null;
-------------------------------------------------------------------
Unique constraint
#建立表时创建唯一约束, establishing constraints using column-level constraint syntax
CREATE TABLE Unique_test (
#建立了非空约束, meaning that test_id is not null.
test_id int NOT NULL,
#建立了unique约束
Test_name varchar (a) unique
);
#创建表时, establishing constraints using table-level constraint syntax
CREATE TABLE Unique_test (
test_id int NOT NULL,
Test_name varchar (30),
Test_pass varchar (30),
#使用表级约束创建唯一约束
Unique (test_name),
Constraint Test_uk unique (test_pass)
#constrain test1_uk Unique (test_name,test_pass)
);
#修改唯一约束
ALTER TABLE Unique_test
Add unique (Test_name,test_pass);
#为表增加约束
ALTER TABLE Unique_test
Modify Test_name varchar (a) unique;
-------------------------------------------------------------------
PRIMARY KEY constraint
CREATE TABLE Primarykey_test (
primarykey_id int PRIMARY KEY,
Test_name varchar (255)
);
CREATE TABLE Primarytest (
primary_id int NOT NULL,
Primary_name varchar (29),
Constraint Pk_test primary KEY (PRIMARY_ID)
);
#删除主键约束
ALTER TABLE test
Drop primary key;
#使用表级语法增加主键约束
ALTER TABLE test
Add primary key (test_id, test_name);
#使用列级约束语法增加主键约束
ALTER TABLE test
Modify Test_name varchar (+) primary key;
------------------------------------------------------------------
#为了保证从表参照的主表存在, the main table should usually be established first
CREATE TABLE Teacher_table (
#auto_increment
teacher_id int Auto_increment,
Teacher_name varchar (255),
Primary KEY (TEACHER_ID)
);
CREATE TABLE Student_table (
student_id int Auto_increment primary KEY,
Student_name varchar (255),
#指定java_teacher参照到teacher_table的teacher_id的列
Java_teacher int references teacher_table (teacher_id)
#java_teacher int
#foreign Key (Java_teacher) references teacher_table (teacher_id)
#constraint STUDENT_TEACHER_FK foreign KEY (Java_teacher) references
Teacher_table (teacher_id)
);
---------------------------------------------------------------------------------------------
#check约束
CREATE TABLE Test (
test_id int Auto_increment primary KEY,
Test_age int NOT NULL,
Check (test_age>0 and test_age<120)
)
MySQL Basic knowledge Review
