[MySQL] Manipulate database, field attributes, create database

Operate the database "Operate the table in the database" Operate the data of the table in the database
2.1 Operate the database (understand)
1. Create

CREATE DATABASE IF NOT EXISTS westos

2. Delete

DROP DATABASE IF EXISTS hello

  1. Use database
USE `school`
  1. View database
SHOW DATABASES--查看所有的数据库

2.2 The column type of the database

  • Numerical value
    tinyint
    smallint
    mediumint
    int Standard integer 4 bytes
    bigint 8 bytes
    float 4 bytes
    double 8 bytes
    decimal Floating-point numbers in the form of strings In financial calculations, generally use decimal
  • String
    char fixed length 0-255
    varchar variable string 0-65535
    tinytext micro text 2^8-1
    text text string 2^16-1
  • Time and date
    date YYYY-MM-DD, date format
    time YYYY-MM-DD Time format
    datetime YYYY-MM-DD YYYY-MM-DD The most commonly used
    timestamp timestamp, the number of milliseconds from 1970.1.1 to the present
    year is expressed by year
  • Null
    has no value, unknown.
    Note: Do not use NULL for calculations, and the result is NULL.
    2.3 Field properties of the database (master)
    Unsigned: Unsigned
    integer, declare that the column cannot be a negative number.
    Zerofill: 0
    Increment of insufficient digits : Generally understood In order to automatically +1 on the basis of the previous record, it is usually used to set a unique primary key, which must be of integer type, and the increment and starting value can be customized.
    Not null not null: Assuming it is set to not null, if you don't assign a value to it, an error will be reported.
    Default: set the default value, such as gender, the default value is male
Insert picture description here


2.4 Create a database table (master)

CREATE TABLE IF NOT EXISTS `student`(
`id` INT(4) NOT NULL AUTO_INCREMENT COMMENT '学号',
`name` VARCHAR(30) NOT NULL DEFAULT '匿名' COMMENT '姓名',
`pwd` VARCHAR(20) NOT NULL DEFAULT '123456' COMMENT '密码',
`sex` VARCHAR(2) NOT NULL DEFAULT '女' COMMENT '性别',
`birthday` DATETIME DEFAULT NULL COMMENT '出生日期',
PRIMARY KEY(`id`)



)ENGINE=INNODB DEFAULT CHARSET=utf8

format:

CREATE TABLE [IF NOT EXISTS] `表明`(
`字段名` 列类型 [长度][默认值][是否为空][注释],
)[表类型][字符集设置][注释]

Commonly used commands:

SHOW CREATE DATABASE school -- 查看数据库语句
SHOW CREATE TABLE math -- 查看表语句
DESC math -- 查看表的结构

2.5 Types of data tables
-About the database engine
/*
Myisam:
INNODB used in the early years : used by default

*/

Location in physical space

Myisam: Save space, faster
Innodb: High security, transaction processing, multi-table and multi-user operation The
essence of the database is the storage of files.
The difference between the MySQL engine in physical files
InnoDB: There is only one .frm file
MYISAM:.
frm: table structure definition file
. MYD data file (data)
.MYI index file (index)

Set the character set encoding of the database table. If
not set, it will be the default character set encoding of mysql, which does not support Chinese

2.6 Modify the delete table
modify

delete

-- 修改表
-- 新的表名
ALTER TABLE student RENAME AS stu
-- 增加表的字段
ALTER TABLE stu ADD yanzhi INT(3)
-- 修改表的字段
ALTER TABLE stu MODIFY age VARCHAR(11)  -- 修改约束
ALTER TABLE stu CHANGE age age1 INT(1) -- 字段重命名

-- 删除表的字段
ALTER TABLE stu DROP age1

-- 删除表(如果表存在就删除)
DROP TABLE IF EXISTS stu

Note:

  • 1. Note
    2. ``
    3. Case is not sensitive, lower case is recommended
    4. All symbols are in English