MySQL5.5.21 tutorial 2, mysql5.5.21 tutorial

MySQL5.5.21 tutorial 2, mysql5.5.21 tutorial

Now let's take a look at the basic table operations! It is mainly to create tables and basic constraints. We will continue to explain the index issue later!

# Columns-also known as attribute columns. When creating a table, you must specify the column name and data type. # index-refers to the sequence in which the column is created based on the specified database list, provides a quick way to access data # ------ you can monitor the data in a table so that the data in the column to which the index is directed does not repeat # Trigger -- a set of user-defined commands, this command is automatically executed when you insert, update, or delete data in a table # -------- it can be used to ensure data integrity and security. create database company; show databases; + -------------------- + | Database | + -------------------- + | information_schema | company | mysql | performance_schema | test | + ---------------- + use company; create table t_dept (Deptno INTEGER, dname VARCHAR (20), loc VARCHAR (40); describe t_dept; + -------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ------- + | deptno | int (11) | YES | NULL | dname | varchar (20) | YES | NULL | loc | varchar (40) | YES | NULL | + -------- + ------------- + ------ + ----- + --------- + ------- + show c Reate table t_dept \ G **************************** 1. row *************************** Table: t_deptCreate Table: create table 't_ dept' ('deptno' int (11) default null, 'dname' varchar (20) default null, 'loc 'varchar (40) default null) ENGINE = InnoDB default charset = utf8drop table t_dept; show tables; Empty set (0.00 sec) alter table t_dept rename tab_dept; show tables; + --------------- + | Tables_in_comp Any | + ------------------- + | tab_dept | + ------------------- + alter table tab_dept rename t_dept; desc t_dept; + -------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ------- + | deptno | int (11) | YES | NULL | dname | varchar (20) | YES | NULL | loc | varchar (40) | YES | NULL | + -------- + ------------ -+ ------ + ----- + --------- + ------- + Alter table t_dept add descri VARCHAR (20); # add a field in the table header # alter table t_dept add descri VARCHAR (20) first; # add a field after a field in the table # alter table t_dept add descri VARCHAR (20) after deptno; desc t_dept; + -------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ------- + | deptno | int (11) | YE S | NULL | dname | varchar (20) | YES | NULL | loc | varchar (40) | YES | NULL | descri | varchar (20) | YES | NULL | + -------- + ------------- + ------ + ----- + --------- + ------- + alter table t_dept drop descri; desc t_dept; + -------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ------- + | deptno | I Nt (11) | YES | NULL | dname | varchar (20) | YES | NULL | loc | varchar (40) | YES | NULL | + -------- + ------------- + ------ + ----- + --------- + ------- + alter table t_dept modify deptno VARCHAR (20); desc t_dept; + -------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ------- + | deptno | varchar (20) | YES | NULL | dname | varchar (20) | YES | NULL | loc | varchar (40) | YES | NULL | + -------- + ------------- + ------ + ----- + --------- + ------- + alter table t_dept modify deptno INTEGER; desc t_dept; + -------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ------- + | deptno | int (11) | YES | NULL | dname | Varchar (20) | YES | NULL | loc | varchar (40) | YES | NULL | + -------- + ------------- + ------ + ----- + --------- + ------- + # alter table table_name change old property name new property name old data type # alter table table_name change old property name new attribute name new data type alter table t_dept change loc location VARCHAR (40 ); desc t_dept; + ---------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ---------- + --- ---------- + ------ + ----- + --------- + ------- + | Deptno | int (11) | YES | NULL | dname | varchar (20) | YES | NULL | location | varchar (40) | YES | NULL | + ---------- + ------------- + ------ + ----- + --------- + ------- + alter table t_dept modify location VARCHAR (40) first; desc t_dept; + ---------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ---------- + ------------- + ------ + ----- + --------- + ------- + | Location | varchar (40) | YES | NULL | deptno | int (11) | YES | NULL | dname | varchar (20) | YES | NULL | + ---------- + ------------- + ------ + ----- + --------- + ------- + integrity constraints supported by MySQL software not null -- set the constraint field cannot be blank DEFAULT -- set the field the default value is unique key. The value of the constraint field is the unique primary key. The constraint field is the primary key of the table, it can be used as the unique constraint of the table Record AUTO_INCREMENT -- the value of the constraint field is automatically added foreign key -- the constraint field is the foreign key of the table alter table t_dept mo Dify deptno integer not null; + ---------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ---------- + ----------- + ------ + ----- + --------- + ------- + | location | varchar (40) | YES | NULL | deptno | int (11) | NO | NULL | dname | varchar (20) | YES | NULL | + ---------- + ------------- + ------ + ----- + --------- + ------- + alter table t_dept modify location VARCHAR (40) default 'nwpu '; + ---------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ---------- + ----------- + ------ + ----- + --------- + ------- + | location | varchar (40) | YES | NWPU | deptno | int (11) | NO | NULL | dname | varchar (20) | YES | NULL | + ---------- + ------------- + ------ + ----- + --------- + ------- + alter table t_dept modify dname VAR CHAR (20) unique; desc t_dept; + ---------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ---------- + ----------- + ------ + ----- + --------- + ------- + | location | varchar (40) | YES | NWPU | deptno | int (11) | NO | NULL | dname | varchar (20) | YES | UNI | NULL | + ---------- + ------------- + ------ + ----- + --------- + ------- + drop table t_dept; show tables; as shown in Figure If you want to set a name for the UK constraint on the field dname, you can run the SQL statement constraint to create the t_dept statement: create table t_dept (deptno INTEGER, dname VARCHAR (20 ), loc VARCHAR (40), constraint uk_dname unique (dname); when you set a primary key constraint, the value of the primary key field must be unique and not empty. Because the primary key can be a single field or multiple fields, it can be divided into single-field primary keys and multi-field primary key create table t_dept (deptno INTEGER primary key, dname VARCHAR (20 ), loc VARCHAR (40), constraint uk_dname unique (dname); set the multi-field primary key create table t_dept (deptno INTEGER, dname VARCHAR (20), loc VARCHAR (40 ), constraint uk_dname unique (dname), constraint initialize primary key (deptno, dname); show tables; desc t_dept; + -------- + ------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ------- + | deptno | int (11) | NO | PRI | 0 | dname | varchar (20) | NO | PRI | loc | varchar (40) | YES | NULL | + -------- + ------------- + ------ + ----- + --------- + ------- + drop table t_dept; create table t_dept (deptno INTEGER auto_increment, dname VARCHAR (20), loc VARCHAR (40), constraint uk_dname unique (Dname), constraint pk_dname_depno primary key (deptno, dname); desc t_dept; + -------- + ------------- + ------ + ----- + --------- + ---------------- + | Field | Type | Null | Key | Default | Extra | + -------- + ------------- + ------ + ----- + --------- + ---------------- + | deptno | int (11) | NO | PRI | NULL | auto_increment | dname | varchar (20) | NO | PRI | loc | varchar (40) | YES | NULL | + -------- + ----------- -- + ------ + ----- + --------- + ---------------- + Foreign key constraints can usually indicate the integrity constraints of the reference between multiple tables, that is, the two tables that are built on the foreign key constraint between the two fields of the two tables have a parent-child relationship, that is, the value range of a field in the child table is determined by the parent table, represents a department-employee relationship, that is, the number of employees in each part. There should be two tables: department table and employee table. The employee table has a field indicating the Department number field deptno, which depends on the primary key of the Department table, in this way, the deptno field is the foreign key of the employee table. The deptno field in this field is dependent on the primary key of the Department table. In this way, the deptno field is the foreign key of the employee table. Create table table_name (attribute name data type, attribute name data type ,...... constraint foreign key constraint name foreign key (attribute name 1) references indicates (attribute name 2); create table t_employee (empno INTEGER primary key, ename VARCHAR (20 ), job VARCHAR (20), mgr integer, Hiredate date, sal double (10, 2), comm double (10, 2), deptno INTEGER, constraint fk_deptno foreign key (deptno) references t_dept (deptno); show tables; + ------------------- + | Tables_in_company | + ----------------- + | t_dept | t_employee | + --------------------- + desc t_employee; + ---------- + -------------- + ------ + ----- + --------- + ------- + | Field | Type | Null | Key | Default | Extra | + ---------- + ------------ + ------ + ----- + --------- + ------- + | empno | int (11) | NO | PRI | NULL | ename | varchar (20) | YES | NULL | job | varchar (20) | YES | NULL | MGR | int (11) | YES | NULL | Hiredate | date | YES | NULL | sal | double (10, 2) | YES | NULL | comm | double (10, 2) | YES | NULL | deptno | int (11) | YES | MUL | NULL | + ---------- + -------------- + ------ + ----- + --------- + ------- +