文章目录
基本操作
创建数据库
-- CREATE DATABASE 新数据库名;
create database bookstore;
-- 设置字符集
alter database bookstore character set utf8;
-- 文件存放路径
show variables like ‘datadir’;
-- 查看数据库的相关信息
show create database bookstore;
-- 选择数据库
use bookstore;
-- 删除数据库
drop database bookstore;
-- 创表
/*
CREATE TABLE [IF NOT EXISTS] 表名
( 字段名1 数据类型 [约束]]
[ ,字段名2 数据类型 [约束]]
……
[ ,字段名n 数据类型 [约束]]
[其他约束条件]
)[其他选项];
*/
create table users
(
uid int not null primary key auto_increment,
name varchar(20) not null unique,
pwd varchar(20) not null,
sex char(2)
)engine= InnoDB;
-- 常用的关键字
/*
AUTO_INCREMENT:用于设置整数类型字段的自动增量属性。AUTO_INCREMENT字段必须被索引,而且必须为NOT NULL。每个表最多只能有一个字段具有AUTO_INCREMENT属性
DEFAULT:表中添加新行时给表中某一字段指定的默认值。使用DEFAULT定义,一是可以避免NOT NULL值的数据错误;二是可以加快用户的输入速度
NOT NULL:指定NOT NULL属性的字段,不能有NULL值
UNSIGNED:表示该值不能为负数
PRIMARY KEY,UNIQUE,FOREIGN KEY(仅InnoDB)
*/
-- 查看数据库表结构
describe users;
desc users;
-- 修改表
-- ALTER TABLE 表名 ACTION [,ACTION]… 支持一条ALTER TABLE语句带多个动作,以逗号分隔
/*
添加、修改、删除表属性字段
添加、修改、删除表约束条件
删除索引
修改表名
修改表的存储引擎
*/
-- DROP TABLE [IF EXISTS] 表名1 [,表名2,…];
drop table A
管理表记录
每个字段都有数据类型 —— 数据存储格式、约束、有效范围
合适的type —— 节省空间,提升性能
数值类型(包括整数类型和小数类型)、字符串类型、日期时间类型、复合类型以及二进制类型
整数
type | length |
---|---|
TINYINT | 1字节 |
SMALLINT | 2字节 |
MEDIUMINT | 3字节 |
INT(INTEGER) | 4字节 |
BIGINT | 8字节 |
默认int正负数都可以表示
若只表达非负整数,可使用unsigned
修饰
age tinyint unsigned
可指示宽度
int(8) -- 若数值宽度< 8 在前方用空格填满宽度
数字位数不够时需要用“0”填充时,则可以使用关键字zerofill
int(8) zerofill -- 若数值宽度< 8 在前方用0填满宽度
大于或等于指定显示宽度,按数的本来大小展示
create的应用
CREATE DATABASE type_test;
USE type_test;
create table int_test(
int_field1 int(6), -- 组内分割用逗号
int_field2 int(8) UNSIGNED,
int_field3 int(8) ZEROFILL -- 最后一个属性请不要加逗号
);
desc int_test;
插入数据后可查询
insert into int_test values(1, 2, 3);
select * from int_test;
可以利用alter table .. modify
进行类型转换
ALTER TABLE int_test MODIFY int_field1 INT ( 6 ) ZEROFILL;
ALTER TABLE int_test MODIFY int_field2 INT ( 3 ) ZEROFILL;
SELECT
*
FROM
int_Test;
大于或等于指定显示宽度
AUTO_INCREMENT
作用:产生 唯一标识符 or 顺序值, 可用此熟悉 (仅适用整数类型)
要求
- 一个表最多一个
AUTO_INCREMENT
字段 - AUTO_INCREMENT字段应为
NOT NULL
定义为PRIMARY KEY
或UNIQUE
小数
小数类型有两种:浮点数和定点数
浮点数:
FLOAT DOUBLE
定点数:
DECIMAL
- 字符串形式存放,更精确
- 表示货币等高精度数据
可在类型后加上(M, D)
表示
- M 代表一共显示M位数字
- D 表示小数点后的位数
- 在 D 后的数值需要按照 D 来四舍五入
- 不指定(M, D)
- 浮点数: 实际值存储
- 定点数:整数位默认10,小数位默认0
create table number_test(
float_field float,
double_field double,
decimal_field decimal
);
insert into number_test values(1234.56789, 1234.56789, 1234.56789);
insert into number_test values(1.234, 1.234, 1.234);
select * from number_test;
FLOAT 和 DOUBLE的转换
float类型默认大小为24位数字,精度大约7位数字。当设置M大小大于24时,自动转换为DOUBLE类型;同时设置M和D时不进行自动转换
alter table number_test modify float_field float(5,1);
alter table number_test modify double_field double(5,1);
alter table number_test modify decimal_field decimal(5,1);
字符串
CHAR、VARCHAR、TINYTEXT、TEXT、MEDIUMTEXT和LONGTEXT
type | length |
---|---|
CHAR(n) | 0-255 |
VARCHAR(n) | 0-255 或者 0-65535 |
TINYTEXT | 0-255 |
TEXT | 0-65535 |
MEDIUMTEXT | 0-16777215 |
LONGTEXT | 0-4294967295 |
区别
- CHAR 定长
- 剩下的都是 变长
- CHAR存储时删除尾部空格
- VARCHAR会保留尾部空格
create table string_test(char_field char(8),varchar_field varchar(8));
insert into string_test values('test ','test ');
select * from string_test;
update string_test set char_field=concat(char_field,'+'),varchar_field=
concat(varchar_field,'+');
日期时间
日期时间类型包括:DATE、TIME、DATETIME、TIMESTAMP和YEAR。
- DATE表示日期,默认格式为YYYY-MM-DD;
- TIME表示时间,默认格式为HH:MM:SS;DATETIME和
- TIMESTAMP表示日期和时间,默认格式为YYYY-MM-DD HH:MM:SS;
- YEAR表示年份
type | min | max |
---|---|---|
DATE | 1000-01-01 | 9999-12-31 |
TIME | -838:59:59 | 838:59:59 |
DATETIME | 1000-01-01 00:00:00 | 9999-12-31 23:59:59 |
TIMESTAMP | 1970-01-01 08:00:01 | 2037年某时刻 |
YEAR | 1901 | 2155 |
PS:
- YEAR可以表示2位or四位,默认四位
- 在4位格式中,允许值的范围为1901~2155。
- 在2位格式中
- 取值为70~99时,表示从1970年~1999年
- 取值为01~69时,表示从2001年~2069年
-
DATETIME与TIMESTAMP都包括日期和时间两部分
- TIMESTAMP类型与时区相关
- 而DATETIME则与时区无关
-
一个表中定义了两个类型为TIMESTAMP的字段
- (注意,这个特性是某些版本特有的)
- First one 默认为 CURRENT_TIMESTAMP
- Second one 默认 0000-00-00 00:00:00
create table year_test(year_field year);
insert into year_test values(2155);
insert into year_test values(69);
select * from year_test;
create table datetime_test(
datetime_field datetime,
timestamp_field1 timestamp default CURRENT_TIMESTAMP,
timestamp_field2 timestamp default "0000-00-00 00:00:00");
desc datetime_test;
insert into datetime_test values(now(), now(), now());
select * from datetime_test;
复合类型
ENUM枚举类型 和 SET集合类型
ENUM
- 只允许从集合中取得某一个值
- 最多可以包含65535个元素
SET
- 允许从集合中取得多个值
- 最多可以包含64个元素
create table enum_test(sex enum('男','女'));
insert into enum_test values('女');
insert into enum_test values('男');
insert into enum_test values(NULL);
insert into enum_test values(1);
select * from enum_test;
create table set_test(hobby set('旅游','听音乐','看电影','上网','购物'));
insert into set_test values('看电影,听音乐');
insert into set_test values('上网');
insert into set_test values(NULL);
select * from set_test;
二进制类型
BINARY、VARBINARY、BIT(按位为单位存储)、TINYBLOB、MEDIUMBLOB和LONGBLOB
除了BIT其他都是按字节为单位存储
type | length |
---|---|
BINARY(n) | 0-255 |
VARBINARY(n) | 0-65535 |
BIT(n) | 0-63 |
TINYBLOB | 0-255 |
BLOB | 0-65535 |
MEDIUMBLOB | 0-16777215 |
LONGBLOB | 0-4294967295 |
MySQL运算符
算术运算符
+ − ∗ / % + - *\ /\ \% +−∗ / %
补充
DIV
\
,前者表示整除,后者返回实际运算
MOD
%
,前者返回整数,后者符号可正可负
比较运算符
数字/日期/字符串用
- =
- != / <>
- <=> (NULL也可以使用)
- <
- >
- >=
NULL用
- <=>
- is null
- is not null
范围
- between a and b 可比较范围内
- in 在集合内
- like 通配符匹配
- regexp 正则表达式匹配
example
- varchar_field like '%Li’表示当varchar_field中的字符串以“Li”结尾时,则返回值为1,否则返回值为0
- varchar_field regexp 'Li$'表示当varchar_field中的字符串以“Li”结尾时,则返回值为1,否则返回值为0。
- varchar_field regexp '^Mr’表示当varchar_field中的字符串以“Mr”开头时,则返回值为1,否则返回值为0
逻辑运算符
- NOT !
- AND &&
- OR ||
- XOR
操作数有NULL的时候,所有的返回值是NULL
例外:
- 1 or null 返回 1
- 0 and null 返回 0
¤ 位运算符
按位
- &
- |
- ~
- ^
移位
- << 整型数据在内存中的二进制补码向左移出指定的位数,向左移出的位数丢弃,右侧添0补位
- >> 将整型数据在内存中的二进制补码向右移出指定的位数,向右移出的位数丢弃,左侧添0补位
字符集
mySQL默认latin1(西欧ISO_8859_1字符集的别名)
支持 utf8、gbk和big5等
排序规则命名方法:
- 开头:字符排序规则对应的字符集
- 中间:国家名(或general)
- 结尾:ci cs bin
- ci :大小写不敏感
- cs :大小写敏感
- bin:按二进制编码值进行比较
show character set
查看例支持的字符集、字符集的默认排序规则、字符集占用的最大字节长度等信息
show variables like 'character%'
可以查看
当前服务实例使用的字符集信息
修改字符集的方法
MySQL服务并生成服务实例后
character_set_server
将使用my.ini
配置文件中[mysqld]选项组中character_set_server
参数的值character_set_client character_set_connection character_set_results
的字符值将使用my.ini
配置文件中[mysqld]
选项组中default_character_set
参数的值
- 修改my.ini配置文件并重启
set
命令设置set names
设置
- (一次性将设置好character_set_client、character_set_connection以及character_set_results的参数值)
- 连接MySQL服务器时指定字符集
mysql --default-character-set=utf8 -h 127.0.0.1 -u root -p
相当于连接服务器后执行命令set names=utf8
增添表记录
INSERT
/*
INSERT [INTO] table_name [(column_name,…)]
VALUES ({expr|DEFAULT}, ...), (...), …
|SET column_name={expr|DEFAULT},
…
*/
-- table_name 表名
-- column_name 字段名
# 省略表示给全部字段插入数据
# 只插入部分需要指出字段名
# 未指定字段,优先 defalut, 然后null, 两者都没有报错
# 自增型会自动生成编号
# timestamp 会自动填充当前日期时间
-- values 包含字段需要插入的数据清单
# 顺序要和字段对应
# 省略字段 所有字段都要给出对应值
# 常量/变量/表达式/null,类型要一致
-- set 给指定字段赋值
例子
create table course(
course_id int auto_increment primary key,
course_name varchar(20),
course_hours int default 72,
teacher_id char(10) not null,
constraint course_teacher_fk foreign key(teacher_id) references teacher(teacher_id)
);
insert into course values(NULL,'C程序设计',default,'0412893402');
insert into course(course_name,teacher_id) values('Java程序设计','0412893401');
insert into course values(NULL,'MySQL数据库',60,'0412893403');
- auto_increment字段插入数据,可以插入NULL或省略这个字段,插入值自动填充下一个自增值
- 向默认值约束字段插入数据时,字段值可以使用default关键字或省略该字段,此时插入的值为该字段的默认值。
constraint .. foriegn key(..) references ..(..)
是外键约束关系。 course表中的teacher_id字段值要来自于表teacher中teacher_id字段
INSERT语句一次向表中插入多条记录
truncate course;
insert into course(course_name,course_hours,teacher_id) values
('C程序设计',default,'0412893402'),
('Java程序设计',default,'0412893401'),
('MySQL数据库',60,'0412893403')
注意,删除了先前的数据,再加入新数据的时候,AUTO_INCREMENT不会填被删除的数据而是继续加
SELECT
/*
REPLACE [INTO] table_name [(column_name,…)]
VALUES ({expr|DEFAULT}, ...), (...), …
|SET column_name={expr|DEFAULT},
*/
/*
REPLACE [INTO] table_name[(column_name,…)]
SELECT (column_name,…) from source_table_name where conditions
*/
与 INSERT的区别
- 使用REPLACE语句添加记录时,if 新记
录的主键值或者唯一性约束的字段值与已有记录相同时,then 删除后再添加新记录。 - INSERT则直接报错 —— 插入失败
修改表记录
UPDATE
UPDATE table_name
SET column_name={expr|DEFAULT},…
[where condition]
-- 省略where对所有记录修改
-- set子句用于指出记录中需要修改的字段及其取值
删除表记录
DELETE或TRUNCATE语句
/*
DELETE from table_name
[WHERE condition]
*/
-- TRUNCATE语句清空表记录
TRUNCATE [table] table_name
-- 类似DELETE from table_name
turncate 会 重新设置 自增型字段起始值,delete不会
select auto_increment from information_schema.tables where
table_name='course_copy';
-- 语句的作用是查询course_copy表中自增字段的起始值。