3.22 Database Basics: ① data Types ② Basic rules ③ Database basic operations, additions and deletions to the library, table

Database: ① data type ② basic rules ③ Database basic operations, additions and deletions to the library, table

Database databases
relational database-relational databases
Column row rows
Primary key PRIMARY key Foreign key foreign key
DBMS Database Management System
RDBNS-relational database management system
SQL Structured Query Language structured queries language
SQL is divided into: DDL DQL DML DCL

First, the data type:
1, Integer type: INT BIGINT
2. Float type: float double decimal (m,n) total length, number of decimal digits
3. String: Char varchar
Char can be used as a Boolean type, yes/no True/false 1/0, and so on are directly represented by Char
varchar (16) 16 characters can represent a little longer, not more than 8KB;
4. Time Type: Date datetime

Second, the basic rules:
Database name: Lowercase, cannot have Chinese
' Variable name ' is expanded to avoid keywords such as name.
Table name, variable name is not case sensitive
The main class cannot be deleted directly, because there is a reference to it from the table, unless there is no reference to it from the table, so there is no link to delete it.
SQLyog inside a single comment--multiline comment/**/
SQLyog inside Select a section run can run the section alone

Third, the basic operation of the database: additions and deletions to the library, table

--Create a library
CREATE DATABASE Database_demo;
--Link library
Use Database_demo;
--Delete Library
DROP DATABASE Database_demo;

--Create a table
ID with integer better than bigint, good compatibility with Java, small error rate
Finally set the default encoding to UTF8
You can add a default value behind each
CREATE TABLE Student (
ID INTEGER PRIMARY KEY not NULL,
NAME VARCHAR (+) DEFAULT ' Zhang San ',
Age INT
) Charset=utf8;

--Delete Table
DROP TABLE student;

--Copy table
--Copy only the structure:
CREATE TABLE student1 SELECT *student1 from student WHERE 1 = 0;
-WHERE writing is never a condition of existence;
--can copy data, but cannot copy constraints
CREATE TABLE student1 SELECT *student1 from student;

--Add columns
ALTER TABLE Student ADD address VARCHAR (+) DEFAULT ' Chengdu ';

--Modify Columns
ALTER TABLE Student Change Address address VARCHAR (+) DEFAULT ' Sichuan ';

--Delete Columns

ALTER TABLE student DROP COLUMN address;

The benefits of creating indexes that are faster and more efficient when data is large
--Create an index
CREATE INDEX Name_index on student (name,age);
--Delete Index
ALTER TABLE student DROP INDEX Name_index;

3.22 Database Basics: ① data Types ② Basic rules ③ Database basic operations, additions and deletions to the library, table