mysql基础（附具体操作代码）

-- 创建测试用数据库 test
CREATE DATABASE IF NOT EXISTS test DEFAULT CHARACTER SET ‘UTF8‘;

-- 打开test

USE test;

-- 用户表 test_user

CREATE TABLE IF NOT EXISTS test_user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM(‘男‘,‘女‘,‘保密‘)
)ENGINE=INNODB CHARSET=UTF8;

-- 查看test_user表的表结构
DESC test_user;

DESCRIBE test_user;

SHOW COLUMNS FROM test_user;

-- 删除test_user表

DROP TABLE IF EXISTS test_user;

-- 查看当前数据库下的数据表

-- 查看test_user表的详细信息

-- 通过三种方式查看指定数据表的表结构

-- 最后删除test_user数据表

数据库增加数据与查询数据

-- 用户表 test_user

CREATE TABLE IF NOT EXISTS test_user(
id INT,
username VARCHAR(20),
password CHAR(32),
email VARCHAR(50),
age TINYINT,
card CHAR(18),
tel CHAR(11),
salary FLOAT(8,2),
married TINYINT(1),
addr VARCHAR(100),
sex ENUM(‘男‘,‘女‘,‘保密‘)
)ENGINE=INNODB CHARSET=UTF8;

-- INSERT [INTO] tbl_name(id,username,...) VALUES(1,‘KING‘,...);

-- 向test_user表插入一条记录
INSERT test_user(id,username,password,email,age,card,tel,salary,married,addr,sex)
VALUES(1,‘king‘,‘king‘,‘965794175@qq.com‘,24,‘xxxxxxxxxxxxxxxxxx‘,‘132xxxxxxxx‘,88888.68,0,‘宁波‘,‘男‘);

-- 查询表中所有记录 SELECT * FROM tbl_name;

SELECT * FROM test_user;

INSERT test_user(id,username,password,email,age,card,tel,salary,married,addr,sex)

VALUES(-5,‘king‘,‘123456‘,‘965794175@qq.com‘,190,‘xxxxxxxxxxxxxxxxxx‘,‘132xxxxxxxx‘,2345.68,1,‘宁波‘,‘女‘);

数据类型测试-测试整型+无符号+零填充

-- 测试整型
CREATE TABLE test_int(
    a tinyint,
    b smallint,
    c mediumint,
    d int,
    e bigint
);
INSERT test_int(a) VALUES(-128);
-- 如果超出数据的范围，会产生截断现象
INSERT test_int(a) VALUES(-129);
INSERT test_int(a) VALUES(1270);

-- 测试无符号

CREATE TABLE test_unsigned(
a tinyint,
b tinyint UNSIGNED
);

INSERT test_unsigned(a,b) VALUES(-12,-12);
INSERT test_unsigned(a,b) VALUES(0,0);
INSERT test_unsigned(a,b) VALUES(0,256);

-- 测试零填充 ZEROFILL
CREATE TABLE test_int1(
    a tinyint ZEROFILL,
    b smallint ZEROFILL,
    c mediumint ZEROFILL,
    d int ZEROFILL,
    e bigint ZEROFILL
);

INSERT test_int1(a,b,c,d,e) VALUES(1,1,1,1,1);

CREATE TABLE test_int2(
    a tinyint(2),
    b smallint(2)
);
INSERT test_int2(a,b) VALUES(123,45678);

数据类型测试-测试浮点型

-- 测试浮点型
CREATE TABLE test_float(
a FLOAT(6,2),
b DOUBLE(6,2),
c DECIMAL(6,2)
);

INSERT test_float(a,b,c) VALUES(4.143,4.146,4.149);

CREATE TABLE test_float1(
a FLOAT,
b DOUBLE,
c DECIMAL
);
INSERT test_float1(a,b,c) VALUES(4.143,4.146,4.649);
INSERT test_float(a,b,c) VALUES(1234567.89,2345678.9,567890.89);

数据类型测试-测试char和varchar+text+ENUM+set

-- 测试char 和 varchar
CREATE TABLE test_str(
    a CHAR(5),
    b VARCHAR(5)
);
INSERT test_str(a,b) VALUES(‘‘,‘‘);
INSERT test_str(a,b) VALUES(‘a‘,‘a‘);
INSERT test_str(a,b) VALUES(‘ab‘,‘ab‘);
INSERT test_str(a,b) VALUES(‘abc‘,‘abc‘);
INSERT test_str(a,b) VALUES(‘abcd‘,‘abcd‘);
INSERT test_str(a,b) VALUES(‘abcde‘,‘abcde‘);
INSERT test_str(a,b) VALUES(‘abcdef‘,‘abcdef‘);

INSERT test_str(a,b) VALUES(‘ 123 ‘,‘ 123 ‘);

SELECT CONCAT(‘*‘,a,‘*‘),CONCAT(‘*‘,b,‘*‘) FROM test_str;

-- 测试TEXT不能有默认值
CREATE TABLE test_str1(
 content TEXT DEFAULT ‘THIS IS A TEST‘
);


-- 测试ENUM
CREATE TABLE test_enum1(
sex ENUM(‘男‘,‘女 ‘,‘保密 ‘)
);
INSERT test_enum(sex) VALUES(‘男‘);
INSERT test_enum(sex) VALUES(‘男1‘);
INSERT test_enum(sex) VALUES(NULL);
INSERT test_enum(sex) VALUES(1);
INSERT test_enum(sex) VALUES(3);
INSERT test_enum(sex) VALUES(5);

-- 测试SET
CREATE TABLE test_set(
    a SET(‘A‘,‘B‘,‘C‘,‘D‘,‘E‘,‘F‘)
);

INSERT test_set(a) VALUES(‘A‘);
INSERT test_set(a) VALUES(‘C‘);
INSERT test_set(a) VALUES(‘C,D,E‘);
INSERT test_set(a) VALUES(‘C,F,A‘);
INSERT test_set(a) VALUES(‘C,F,A,H‘);
INSERT test_set(a) VALUES(2);

数据类型测试-测试时间类型

-- HH:MM:SS [D HH:MM:SS] D表示天数 0~34
-- 测试TIME类型
CREATE TABLE test_time(
a TIME
);
INSERT test_time(a) VALUES(‘12:23:45‘);
INSERT test_time(a) VALUES(‘2 12:23:45‘);
INSERT test_time(a) VALUES(‘22:22‘);
INSERT test_time(a) VALUES(‘22‘);
INSERT test_time(a) VALUES(‘2 22‘);
-- HHMMSS
INSERT test_time(a) VALUES(‘121212‘);
INSERT test_time(a) VALUES(‘0‘);
INSERT test_time(a) VALUES(0);
INSERT test_time(a) VALUES(‘787878‘);

INSERT test_time(a) VALUES(NOW());
INSERT test_time(a) VALUES(CURRENT_TIME);

-- 测试DATE类型 YYYY-MM-DD YYYYMMDD
CREATE TABLE test_date(
    a DATE
);
INSERT test_date(a) VALUES(‘2017-03-04‘);
INSERT test_date(a) VALUES(‘2017-2-13‘);
INSERT test_date(a) VALUES(‘4007-03-23‘);
INSERT test_date(a) VALUES(‘40071212‘);
INSERT test_date(a) VALUES(‘4007@12@12‘);
INSERT test_date(a) VALUES(‘4008#13#13‘);
INSERT test_date(a) VALUES(‘4009.8.14‘);


-- YY-MM-DD YYMMDD
-- 70~99 转换成1970~1999 00~69 2000~2069
INSERT test_date(a) VALUES(‘780902‘);
INSERT test_date(a) VALUES(‘650902‘);
INSERT test_date(a) VALUES(‘880902‘);
INSERT test_date(a) VALUES(NOW());
INSERT test_date(a) VALUES(CURRENT_DATE);

-- 测试DATETIME

CREATE TABLE test_datetime(
a DATETIME
);
INSERT test_datetime(a) VALUES(‘1004-09-12 13:24:56‘);
INSERT test_datetime(a) VALUES(‘720305121212‘);
INSERT test_datetime(a) VALUES(NOW());
INSERT test_datetime(a) VALUES(DATETIME);

-- 测试TIMESTAMP
CREATE TABLE test_timestamp(
    a TIMESTAMP
);
INSERT test_timestamp(a) VALUES(‘1978-10-23 12:12:12‘);

-- 插入CURRENT_TIMESTAMP
INSERT test_timestamp VALUES(CURRENT_TIMESTAMP);

-- 插入NULL
INSERT test_timestamp VALUES(NULL);

-- 什么也不写 得到当前系统日期和时间
INSERT test_timestamp VALUES();


-- 测试YEAR

CREATE TABLE test_year(
    a YEAR
);

INSERT test_year(a) VALUES(1901);

-- 00~69 2000~2069 70~99 1970~1999
-- 0 插入的结果是0000
-- ‘0‘ 插入的结果是2000

数据类型测试-测试主键+复合主键+自增

-- 测试主键
CREATE TABLE test_primary_key(
    id INT UNSIGNED PRIMARY KEY,
    username VARCHAR(20)
);
INSERT test_primary_key(id,username) VALUES(1,‘king‘);
INSERT test_primary_key(id,username) VALUES(1,‘king123‘);
INSERT test_primary_key(username) VALUES(‘QUEEN‘);

CREATE TABLE test_primary_key1(
    id INT UNSIGNED KEY,
    username VARCHAR(20)
);

CREATE TABLE test_primary_key2(
    id INT UNSIGNED,
    username VARCHAR(20),
    PRIMARY KEY(id)
);

-- CREATE TABLE test_primary_key3(
--     id INT UNSIGNED PRIMARY KEY,
--     courseId INT UNSIGNED PRIMARY KEY,
--     username VARCHAR(20),
--     email VARCHAR(50)
-- );

-- 复合主键
CREATE TABLE test_primary_key3(
    id INT UNSIGNED,
    courseId VARCHAR(20),
    username VARCHAR(20),
    email VARCHAR(50),
    PRIMARY KEY(id,courseId)
);
-- 1-a
INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,‘a‘,‘king‘,‘965794175@qq.com‘);

INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,‘b‘,‘king‘,‘965794175@qq.com‘);

INSERT test_primary_key3(id,courseId,username,email)
VALUES(2,‘a‘,‘king‘,‘965794175@qq.com‘);

INSERT test_primary_key3(id,courseId,username,email)
VALUES(1,‘a‘,‘1king‘,‘965794175@qq.com‘);

-- 测试AUTO_INCREMENT

CREATE TABLE test_auto_increment(
id INT UNSIGNED KEY AUTO_INCREMENT,
username VARCHAR(20)
);

INSERT test_auto_increment(username) VALUES(‘A‘);
INSERT test_auto_increment(username) VALUES(‘B‘);
INSERT test_auto_increment(username) VALUES(‘C‘);

INSERT test_auto_increment(id,username) VALUES(NULL,‘E‘);
INSERT test_auto_increment(id,username) VALUES(DEFAULT,‘F‘);
INSERT test_auto_increment(id,username) VALUES(15,‘G‘);

数据类型测试-测试非空+默认值+唯一键

-- 测试非空
CREATE TABLE test_not_null(
a varchar(20),
b varchar(20) not null
);

INSERT test_not_null(a,b) VALUES(‘‘,‘‘);
INSERT test_not_null(a,b) VALUES(NULL,NULL);
INSERT test_not_null(a,b) VALUES(NULL,‘abc‘);
INSERT test_not_null(a) VALUES(‘TEST‘);

-- 测试默认值
CREATE TABLE test_default(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL,
age TINYINT UNSIGNED DEFAULT 18,
email VARCHAR(50) NOT NULL DEFAULT ‘965794175@qq.com‘
);
INSERT test_default(username) VALUES(‘A‘);
INSERT test_default(username,age,email) VALUES(‘B‘,30,‘965794175@qq.com‘);
INSERT test_default(username,age,email) VALUES(‘C‘,NULL,‘965794175@qq.com‘);
INSERT test_default(username,age,email) VALUES(‘D‘,NULL,NULL);
INSERT test_default(username,age,email) VALUES(‘D‘,NULL,DEFAULT);

CREATE TABLE test_default1(
id INT UNSIGNED AUTO_INCREMENT KEY,
sex ENUM(‘a‘,‘b‘,‘c‘) NOT NULL DEFAULT ‘a‘
);

-- 测试UNIQUE KEY
CREATE TABLE test_unique(
id INT UNSIGNED AUTO_INCREMENT KEY,
username VARCHAR(20) NOT NULL UNIQUE KEY,
email VARCHAR(50) UNIQUE,
card CHAR(18) UNIQUE
);

INSERT test_unique(username,email,card) VALUES(‘A‘,‘A@QQ.COM‘,‘1‘);
INSERT test_unique(username,email,card) VALUES(‘A‘,‘1A@QQ.COM‘,‘12‘);
INSERT test_unique(username,email,card) VALUES(‘B‘,NULL,NULL);
INSERT test_unique(username,email,card) VALUES(‘C‘,NULL,NULL);

数据类型测试-测试布尔类型

-- 用户表test_user
CREATE TABLE IF NOT EXISTS `test_user`(
`id` INT UNSIGNED AUTO_INCREMENT KEY COMMENT ‘用户编号‘,
`username` VARCHAR(20) NOT NULL UNIQUE COMMENT ‘用户名‘,
`password` CHAR(32) NOT NULL COMMENT ‘密码‘,
`email` VARCHAR(50) NOT NULL UNIQUE COMMENT ‘邮箱‘,
`age` TINYINT UNSIGNED NOT NULL DEFAULT 18 COMMENT ‘年龄‘,
`sex` ENUM(‘男‘,‘女‘,‘保密‘) NOT NULL DEFAULT ‘保密‘ COMMENT ‘性别‘,
`tel` CHAR(11) NOT NULL UNIQUE COMMENT ‘电话‘,
`addr` VARCHAR(50) NOT NULL DEFAULT ‘北京‘ COMMENT ‘地址‘,
`card` CHAR(18) NOT NULL UNIQUE COMMENT ‘身份证号‘,
`married` TINYINT(1) NOT NULL DEFAULT 0 COMMENT ‘0代表未结婚，1代表已结婚‘,
`salary` FLOAT(8,2) NOT NULL DEFAULT 0 COMMENT ‘薪水‘
)ENGINE=INNODB DEFAULT CHARSET=UTF8;

-- 测试布尔类型
CREATE TABLE test_bool(
id int,
married BOOL
);
CREATE TABLE test_bool1(
id int,
married BOOLEAN
);

对数据表字段的增删改

-- 测试添加和删除字段
CREATE TABLE IF NOT EXISTS user1(
    id INT UNSIGNED AUTO_INCREMENT KEY
);

-- 添加用户名字段 username VARCHAR(20)
ALTER TABLE user1
ADD username VARCHAR(20);

-- 添加密码字段 password CHAR(32) NOT NULL
ALTER TABLE user1
ADD password CHAR(32) NOT NULL;

-- 添加邮箱字段 email VARCHAR(50) NOT NULL UNIQUE 加到username之后
ALTER TABLE user1
ADD email VARCHAR(50) NOT NULL UNIQUE AFTER username;

-- 添加测试字段 test TINYINT(1) NOT NUL