9 establishment, addition, deletion and modification of table

9.1 table creation (table creation)

Syntax format of table creation: (table creation belongs to DDL statement, and DDL includes: create drop alter)

create table Table name(Field name 1 data type, Field name 2 data type, Field name 3 data type);

create table Table name(
	Field name 1 data type, 
	Field name 2 data type, 
	Field name 3 data type
);

create table Table name(
	Field name 1 data type default value, 
	Field name 2 data type, 
	Field name 3 data type
); -- utilize default Set defaults

Naming instructions

Table name: t is recommended_ Or tbl_ Start, readable. See the name and know the meaning.
Field name: see the meaning of the name.
Both table and field names are identifiers.

Create a student table

Student information: student number, name, age, gender, email address

create table t_student(
		no int,
		name varchar(32),
		sex char(1),
		age int(3),
		email varchar(255)
	);

Delete table

drop table t_student; -- An error will be reported when this table does not exist!

Therefore, the following methods are recommended
-- If this table exists, delete it
drop table if exists t_student;

-- Delete multiple tables
drop tables t1,t2,t3;

9.2 insert data (DML)

9.2.1 insert a single record

Syntax format:
insert into table name (field name 1, field name 2, field name 3...) values (value 1, value 2, value 3);

Note: field names and values should correspond to each other. What is one-to-one correspondence?
The quantity should correspond. The data type should correspond to.

insert into t_student(no,name,sex,age,email) values(1,'zhangsan','m',20,);
insert into t_student(email,name,sex,age,no) values('lisi@123.com','lisi','f',20,2);
insert into t_student(no) values(3);

Note: if the insert statement is executed successfully, there must be one more record.
If no value is specified for other fields, the default value is NULL.

Can the "field name" in the insert statement be omitted?
Yes, insert into t_student values(2); // FALSE
Note: if the preceding field name is omitted, it is written! So write down all the values!

insert into t_student values(2, 'lisi', 'f', 20, 'lisi@123.com');

9.2.2 insert multiple records

You can insert more than one record at a time:

insert into t_user(id,name,birth,create_time) values
		(1,'zs','1980-10-11',now()), 
		(2,'lisi','1981-10-11',now()),
		(3,'wangwu','1982-10-11',now());

Syntax: insert into t_ User (field name 1, field name 2) values(), (), (), ();

9.2.3 insert query results into the table

create table dept_bak as select * from dept; -- Establish a and dept Same table
insert into dept_bak select * from dept;  -- Insert query results into the table

9.3 insert date

9.3.1 formatting of string and date

Two functions are required:

​ str_to_date: converts the string varchar type to date type
​ date_format: converts the date type to a varchar string type with a certain format.

Create a table with data

create table t_user(
	id int,
	name varchar(32),
	birth date -- Birthday can also be used date Date type
);

create table t_user(
	id int,
	name varchar(32),
	birth char(10) -- Birthday can use string, no problem.
);

For example, birthday: October 11, 1990 (10 characters)

Note: there is a naming convention in the database:
All identifiers are all lowercase, and words are connected with underscores.

Insert date data

insert into t_user(id,name,birth) values(1, 'zhangsan', '01-10-1990'); -- 1990 October 1
-- Something went wrong: the reason was a type mismatch. database birth yes date Type, a string is given here varchar. 

What should I do? You can use str_ to_ The date function performs type conversion.
str_ to_ The date function can convert a string to the date type date?
Syntax format:
str_to_date('string date ',' date format ')

Date format of mysql:
%Y ear% m month
%d day% h hour
%i minutes% s seconds

insert into t_user(id,name,birth) values(1, 'zhangsan', str_to_date('01-10-1990','%d-%m-%Y'));

str_ to_ The date function can convert the string varchar into date type data. It is usually used in insert, because a date type data is required during insert, and the string needs to be converted into date through this function.

If the date string you provide is in this format, str_ to_ The date function is not needed!!!
%Y-%m-%d
insert into t_user(id,name,birth) values(2, 'lisi', '1990-10-01');

Can the query be displayed in a specific date format?
date_format
This function converts the date type to a string in a specific format.

​ select id,name,date_format(birth, '%m/%d/%Y') as birth from t_user;

​ date_ How to use the format function?
​ date_ Format (date type data, 'date format')
This function is usually used to query dates. Format the displayed date.

If the format is not specified, the SQL statement will perform the default date format and automatically convert the date type in the database to varchar type.
The default date format of mysql is'% Y-%m-%d '

select id,name,birth from t_user;  -- Displays the default date format

9.3.2 difference between date and datetime

Date is a short date: only the date information is included.
datetime is a long date: including the information of year, month, day, hour, minute and second.

mysql short date default format:% Y-%m-%d
mysql long date default format:% Y -% m -% d% H:% I:% s

insert into t_user values(1,'zhangsan','1990-10-01','2020-03-18 15:49:50');

How to get the current system time in mysql?
now() function, and the obtained time has: hour, minute and second information!!!! Is of type datetime.

9.4 modify update (DML)

Syntax format:

update table name set field name 1 = value 1, field name 2 = value 2, field name 3 = value 3... where condition;

Note: no restrictions will cause all data to be updated.

update t_user set name = 'jack', birth = '2000-10-11' where id = 2;
update t_user set name = 'jack', birth = '2000-10-11', create_time = now() where id = 2;

Update all?

update t_user set name = 'abc';

9.5 delete data (DML)

9.5.1 basic grammar

Syntax format?
delete from table name where condition;
Note: if there are no conditions, all the data in the whole table will be deleted!

delete from t_user where id = 2;
insert into t_user(id) values(2);
delete from t_user; -- Delete all!

9.5.2 quickly delete data in the table

[truncate is important and must be mastered]

-- delete dept_bak Data in table
delete from dept_bak; //This method of deleting data is slow.

How does the delete statement delete data? (delete belongs to DML statement!!!)
The data in the table has been deleted, but the real storage space of this data on the hard disk will not be released!!!
The disadvantage of this deletion is that the deletion efficiency is relatively low.
The advantage of this deletion is that it supports rollback, and you can recover the data if you regret!!!

How does the truncate statement delete data?
This deletion is more efficient. The table is truncated once and physically deleted.
Disadvantage of this deletion: rollback is not supported.
The advantage of this deletion: fast.

Usage: truncate table dept_bak; (This operation belongs to DDL Operation.)

The large table is very large, with hundreds of millions of records:
When deleting, use delete. It may take 1 hour to delete! Low efficiency.
You can choose to use truncate to delete the data in the table. The deletion ends in less than 1 second. High efficiency.
However, before using truncate, you must carefully ask the customer if you really want to delete it, and warn that it cannot be recovered after deletion!

truncate is to delete the data in the table. The table is still there!

Delete table?
drop table name// This is not to delete the data in the table, it is to delete the table.

9.6 addition, deletion and modification of table structure

What is the modification of table structure?
Add a field, delete a field, modify a field!!!

To modify the table structure, you need to use: alter
Belongs to DDL statement

DDL includes: create drop alter

Reasons why adding, deleting, and modifying table structures are not commonly used

First: in the actual development, once the requirements are determined and the table is designed, the table structure is rarely modified. Because the cost of modifying the table structure is relatively high when development is in progress. To modify the structure of the table, the corresponding java code needs to be modified a lot. The cost is relatively high. This responsibility should be borne by the designer!

Second: since there are few operations to modify the table structure, we don't need to master it. If one day
If you really want to modify the table structure, you can use tools!!!!

The operation of modifying the table structure does not need to be written to the java program. In fact, it is not the category of java programmers.