Basic knowledge of MySQL database

The following articles mainly introduce the basic knowledge of the MySQL database, including the creation of the MySQL database and the description of some related data types, which also involves the creation of tables, the following is a detailed description of the article. I hope you will find some gains.

MySQL database installation is best to change the character set UTF8

Create a database

 
 

  
  
create database mydata;  
  
  
use mydata; 
 
 

Data Type int double char varchar datetime longtext

Create table dept

 
 

  
  
(  
  
  
deptno int primary key,  
  
  
dname varchar(14),  
  
  
loc varchar(13)  
  
  
);  
  
  
create table emp  
  
  
(  
  
  
empno int primary key,  
  
  
ename varchar(10),  
  
  
job varchar(15),  
  
  
mgr int,  
  
  
hiredate datetime,  
  
  
sal double,  
  
  
deptno int,  
  
  
foreign key (deptno) references dept(deptno)  
  
  
);  
 
 

MySQL database execution script file. SQL \. file path or source file path

?

SQL file -- Comment

MySQL administrator, toad for MySQL

View database show databases;

View the table show tables;

View the table structure desc dept;

Insert data

 
 

  
  
intsert into dept values(1,'a','a');  
  
  
commit; 
 
 

Select * from dept order by deptno desc limit 3, 2; count two after the third one)

Auto-increment create table article

 
 

  
  
(  
  
  
id int primary key auto_increment,  
  
  
title vachar(255)  
  
  
);  
  
  
insert into article values(null,'a');  
  
  
insert into article(title) values('c'); 
 
 

Date Processing

Obtain the current date select now ();

Convert string select date_format (now (), '% Y-% m-% d % H: % I: % s ');

 
 

  
  
Jdbc connection to MySQL
  
  
Connection conn = null;
  
  
Statement stmt = null;
  
  
ResultSet rs = null;
  
  
Try {
  
  
Class. forName ("com. MySQL. jdbc. Driver"). newInstance ();
  
  
Conn = DriverManager. getConnection ("jdbc: MySQL: // localhost/test? User = root & password = root ");
  
  
Stmt = conn. createStatement ();
  
  
Rs = stamt.exe cuteQuery (SQL );
  
  
}
  
  
Catch (Exception e ){}
  
  
Finally {
  
  
Try {
  
  
If (rs! = Null) {rs. close; rs = null ;}
  
  
If (stat! = Null) {stat. close; stat = null ;}
  
  
If (conn! = Null) {conn. close; conn = null ;}
  
  
}
  
  
Catch (SQLException e ){
  
  
E. printStackTrace ();
  
  
}
  
  
}
 
 

The above content is an introduction to the MySQL database knowledge. I hope you can gain some benefits.