Basic operations of mysql (1)

Create database

CREATE DATABASE `school` 

Create table

CREATE TABLE `student` ( #Table name
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number', #Details of the elements of the column, column self increment
  `name` varchar(4) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT 'password',
  `sex` varchar(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
  `birthday` datetime DEFAULT NULL COMMENT 'date of birth',
  `email` varchar(50) DEFAULT NULL COMMENT 'mailbox',
  `age` int(3) NOT NULL COMMENT 'Age',
  PRIMARY KEY (`id`)#Set primary key
) ENGINE=InnoDB DEFAULT CHARSET=utf8 #Set the encoding of database engine and database

Look up the structure of the table

DESC `student`

ALTER

Modify the name of the table

ALTER TABLE `student` RENAME `student1`

Add table columns

ALTER TABLE `student` ADD `aoligei` INT(10)

Modify the data type of the table's columns

ALTER TABLE `student` MODIFY `aoligei` VARCHAR(11)

Modify the column name of the table

ALTER TABLE `student` CHANGE `aoligei` `address` VARCHAR(20)

Delete table fields

ALTER TABLE `student` DROP `address`

Set foreign keys

What is a foreign key: if a public key is the primary key in a relationship, the public key is called the foreign key of another relationship. Thus, the foreign key represents the correlation between the two relationships. A table with a foreign key of another relationship as the primary key is called the master table, and a table with this foreign key is called the slave table of the master table. Foreign keys are also called foreign keywords.

This may be difficult to understand. Let's explain it in another way: when one of your tables needs to reference the data in other tables, you can introduce foreign keys as references to other tables.

The foreign keys we usually create in the database are called physical foreign keys. We usually use logical foreign keys instead of physical foreign keys in our work, which is convenient in the management of the database.

We will delete the first student table created

drop table `student`

Create a grade table

CREATE table `grade`(
 `gradeid` INT(10) NOT NULL auto_increment COMMENT 'Age id',
 `gradename` varchar(50) NOT NULL COMMENT 'Age name',
 PRIMARY KEY(`gradeid`)
)ENGINE = INNODB DEFAULT CHARACTER SET utf8

Create a table with gradeid in the grade table as the foreign key

CREATE TABLE `student` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'Student number',
  `name` varchar(4) NOT NULL DEFAULT 'anonymous' COMMENT 'full name',
  `pwd` varchar(20) NOT NULL DEFAULT '123456' COMMENT 'password',
  `sex` varchar(2) NOT NULL DEFAULT 'female' COMMENT 'Gender',
  `birthday` datetime DEFAULT NULL COMMENT 'date of birth',
  `gradeid` INT(10) NOT NULL COMMENT 'Student grade',
  `email` varchar(10) DEFAULT NULL COMMENT 'mailbox',
  `age` INT(3) NOT NULL DEFAULT '12' COMMENT 'Age',
  PRIMARY KEY (`id`),
  KEY `FK_gradeid` (`gradeid`),#Set foreign keys
  CONSTRAINT `FK_gradeid` FOREIGN KEY (`gradeid`) REFERENCES `grade` (`gradeid`)#Set reference
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8#Add the foreign key constraint after the table is created successfully

If there is no foreign key relationship when creating a table, you want to add a foreign key

ALTER TABLE `student`ADD CONSTRAINT `FK_gradeid` FOREIGN KEY(`gradeid`) REFERENCES `grade`(`gradeid`)
#ALTER TABLE table name ADD CONSTRAINT constraint name 	 Foreign key (column as foreign key) REFERENCES that table (which field)

The above operations are physical foreign keys (not recommended to avoid the trouble caused by too many databases. You must delete the referenced tables first, and then delete the referenced tables

dml language

dml is adding, deleting, checking and modifying

insert

Structure of insert statement insert into table name ([field name 1, field 2, field 3])VALUE('value 1 ',' value 2 ',' value 3 ')

#Insert statement 
    #insert into indicates ([field name 1, field 2, field 3])VALUE('value 1 ',' value 2 ',' value 3 ')
     INSERT into `grade`(`gradename`) value ('Senior')  #Insert an attribute on a table
     INSERT INTO `student`(`name`,`sex`,`age`)VALUES ('Scheming','male',15)
     INSERT INTO `student`(`name`,`sex`,`age`)   #Insert multiple attributes into a table at the same time
     VALUES ('Shinji','male',14) , ('Tomorrow fragrance','female',14)

Only one or more data can be inserted into one table at a time

update

The problem of UPDATE is to modify who (condition) set the original value = the new value
Structure of UPDATE statement update table name set colnum_name = value WHERE [condition]

#UPDATE = original condition value
#UPDATE indicates set colnum_name = value WHERE [condition]
UPDATE `student` SET `name` = 'Who' WHERE `name`= 'Origi'
UPDATE `student` SET `name` = 'Origi',`email` = '114514@163.com' WHERE name = 'Who'
UPDATE `student` SET `name` = '114' WHERE id BETWEEN 1 AND 5
UPDATE `student` SET `name` = '155' WHERE id BETWEEN 1 AND 6 AND `age` = 12

#colnum_name is a database column. Try to bring it with you``
#Criteria, filter criteria. If not specified, all columns will be modified
#Value is a specific value or a variable
UPDATE `student` SET `birthday` = CURRENT_TIME WHERE `name` = '155' #Where CURRENT_TIME is a variable
#The attributes of multiple settings are separated by English commas

delete and TRUNCATE

The same point between delete and truncate: data can be deleted
The difference between delete and TRUNCATE: TRUNCATE resets the auto increment counter to zero. TRUNCATE will not affect transactions

DELETE `student` #Delete data for the entire table
truncate `student` #Delete data for the entire table
delete `student` where id='115' #Delete data with id 115