mysql 数据库
数据库的操作
五个单位
- 数据库服务器 Linux或者 windows
- 数据库
- 数据表
- 数据字段
- 数据行
连接数据库
1.windows win+R 打开cmd
mysql -u root -p
-u 用户名
-p 密码
linux 也是 mysql -u root -p
2. 可视化工具 navicat
创建数据库 database
mysql> create database python06; #分号结束 如果不写代表一直输入 直到 遇到分号
Query OK, 1 row affected (0.01 sec) ok表示创建成功
查看数据库
show databases; #查看数据库
databases 全部数据库 这里是复数
选中数据库
use 数据库名字;
mysql> use python1806;
Database changed #表示被选中
查看数据库中的数据表 table 表
1.先选中数据库
2.show tables;#列出所有的数据表
删除数据库
drop database 数据库名;
mysql> drop database python06;
Query OK, 0 rows affected (0.06 sec
数据表的操作
创建表
create table 表名(字段名 类型(长度),字段1 类型(长度));
mysql> create table users(id int(11),name char(64),age int(4));
Query OK, 0 rows affected (0.02 sec)
查看表结构
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(64) | YES | | NULL | |
| age | int(4) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
删除表
mysql> drop table users;
Query OK, 0 rows affected (0.07 sec)
创建表 指定 引擎 和 字符集
mysql 引擎 myisam innodb
字符集 utf-8
create table 表名(id int(11))engine=MyISAM default charset=utf8;
mysql> create table user(id int(11),name varchar(64),age int(4))engine=Myisam default charset=utf8;
Query OK, 0 rows affected (0.22 sec)
mysql> show create table user; #查看表的创建过程
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
| user | CREATE TABLE `user` (
`id` int(11) DEFAULT NULL,
`name` varchar(64) DEFAULT NULL,
`age` int(4) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show create table info;#如果说创建表 不指定引擎 mysql5.7的默认引擎是 innodb 5.7以前是myisam
+-------+---------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------------------------------+
| info | CREATE TABLE `info` (
`id` int(11) DEFAULT NULL,
`name` varchar(64) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
数据字段的操作 alter table 表名
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| name | varchar(64) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
field 字段名
type 字段类型
null 是否为空
key 是否为主键
default 默认值
extra 额外信息 备注信息
desc 表名;查看表结构
增加表字段 add column
mysql> alter table info add column age int(4);
alter table 表名 add column 字段名 类型(长度);
增加表字段的时候 控制字段顺序 after
alter table 表名 add 字段名 字段类型(长度) after 指定字段名;
mysql> alter table info add sex int(1) after name; 将新增加的字段 添加到 name后边
新增字段 放到第一个位置
mysql> alter table info add infoid int(11) first; #不加first 会添加到最后一行
删除表字段 drop column
alter table 表示 drop column 字段名;
mysql> alter table info drop column infoid;
表字段改名 change
mysql> alter table info change infos infomations char(64);
alter table 表名 change 原字段名 新字段名 类型(长度);
修改表字段类型 modify 修改类型
alter table 表名 modify 字段名 新类型(长度);
mysql> alter table info modify name char(32);
调整字段的顺序 modify
mysql> alter table info modify infomations char(64) first; #没有second last
alter table 表名 modify 字段名 类型(长度) first;
#这里的类型长度不需要非得原来的类型和长度 可以自己重新定义 但是不要瞎自定义
修改表名 rename
alter table 表名 rename 新名字;
mysql 数据类型
- 数值类型(整型 浮点型)
- 字符串
- 日期时间
- 符合类型
- 空间类型 (用来科学计算)
整型
类型 | 所占字节 | 范围 |
---|---|---|
int | 4 | |
tinyint | 1 | -128~127 |
smallint | 2 | -32768-32767 |
mediumint | 3 | |
bigint | 8 |
在开发过程中 性别 不存男女 存 0 男 1 女 2 未知
年龄 没有负数
浮点型
类型 | 所占字节 | 范围 |
---|---|---|
float(m,d) | 4 | 单精度 m 表示长度 d 小数点位数 |
double(m,d) | 8 | 双精度 m表示长度 d表示小数点位数 |
decimal | ‘123.456789’ 存储字符串的浮点数 |
decimal定点数 如果某个字段 精度要求比较高 比如银行金额 就选用decimal
字符类型
类型 | 所占字节 | 长度 |
---|---|---|
char | 0-255 | 定长 |
varchar | 0-255 | 变长 |
tinyblob | 0-255 | 二进制短文本字符串 |
blob | 0-65535 | 二进制形式的长文本数据 |
text | 0-65535 | 长文本数据 |
tinytext | 0-255 | 短文本数据 |
mediumtext | 中等长度文本 | |
mediumblob | 中等二进制形式文本 | |
longtext | 极长文本 | |
longblob | 极长二进制文本 | |
char 定长 占据0-255 字节超过 被忽略 不足255 用空格填补上
varchar 也是占据0-255个字节 超过255 个字节被忽略 不足255个字节 不用空格补 能够节约磁盘空间 提高效率
可以根据实际内容 动态的修改 存储的长度
blob 图像 声音 二进制类型 可以使用blob 来存储 blob 里边内容严格区分大小写
text 不区分大小
时间类型
数据类型 | 所占字节 | 范围 |
---|---|---|
date | 3 | 2018-09-04 |
time | 3 | 11:35:43 |
datetime | 8 | 2018-09-04 11:35:43 |
timestamp | 4 | 自动存储记录修改的时间 |
year | 1 | 年份 |
一般存储时间 不存储 2018-09-04 11:35:43 存时间戳
土豪 也可以用 datetime 直接存储
复合类型
类型 | 说明 | 举例 |
---|---|---|
set | 集合类型 | set(“member1”,”member2”,”member3”) |
enum | 枚举类型 |
enum只能从集合中取 一个值 单选框
set 允许从集合中取任意多个值
类型的使用
unsigned 无符号的意思 表示该字段 只能是整数 比如 id age id int(11) unsigned
zerofill age int(4) zerofill
not null id int(11) not null
null 精确说法是无 不是空字符串 0 一般在创建整型或者浮点型字段的时候 声明 not null 即可
字符集
中文 英语 法语 俄语 德语 日语 需要对常用的符号进行编码 这个编码就是字符集 字符集就相当于 计算机中 人类的语言
比如 说的英文 用英文存储 说过说的是中文 用英文来存储的话 看不懂
- 常用的字符集
- 数据库用什么字符集即可
ASCII码 单字节
GBK 双字节
Unicode 4个字节 万国码 容纳世界上 所有文字和符号的字符编码方案 能够跨语言 跨平台
utf-8 1-6分字节 针对 Unicode 可变字符编码
实际工作用 需要的 编码
- gbk_chinese_ci 简体中文 不区分大小写
- utf8_general_ci unicode 多语言 不区分大小写
表 引擎
引擎名称 | 说明 |
---|---|
myisam | 常用引擎 读的效率高 不支持事务 表锁 支持全文索引 |
innodb | 支持事务 数据安全性高 行锁 |
如果你这张表 用来 读 写 那 选择myisam 引擎
如果你这张表 经常更新 删除 选择 innodb 引擎
索引
- 普通索引 最基本的索引 没有任何限制
- 唯一索引 要求该字段对应的列 不能有重复值 年龄 性别 不能添加唯一索引
- 主键索引 特殊的唯一索引 要求这一列不能有重复值 不能为空
- 复合索引 所谓的复合索引就是 对多个字段同时添加索引
- 全文索引 需要对数据全局搜索 需要添加全文索引
普通索引
alter table 表名 add index(字段);
mysql> alter table user add index(name);
show index from 表名;查看数据表所有的索引
mysql> alter table user drop index name; #删除索引
mysql> alter table user add index in_name(name); #添加索引的同时 起名字
mysql> alter table user drop index in_name;
唯一索引
alter table user add unique(name);
mysql> alter table user add unique un_name(name); un_name 是索引的名字
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
show index from 表名\G \G表示最佳阅读体验阅读 写了\G就不要写;
主键索引
alter table user drop primary key; #删除主键索引 如果说 主键自动递增 会删不掉
需要先 消除主键自增
mysql> alter table user modify id int(11) unsigned not null;#消除自增
mysql> alter table user drop primary key; #再次删除 ok
mysql> alter table user add primary key(id); #添加主键索引
mysql> alter table user modify id int(11) unsigned not null auto_increment;#让主键自动递增
auto_increment 要放到 unsigned 后面
全文索引
mysql> alter table user add column contents text;
alter table user add fulltext(字段名);
mysql> alter table user add fulltext(contents);
mysql> alter table user drop index contents;
创建表的时候直接声明索引
create table test(
id int(11) unsigned not null,
username varchar(20) not null,
password char(32) not null,
content text,
primary key(id),
index pw(password),
unique un_name(username),
fulltext quanwen(content)
)engine=myisam default charset=utf8;
语句的操作 增删改查
增 insert
1.insert into 表名 values(值1,值2,值3,值4); 有多少个字段 就必须插入多少个值 一个不能多 一个不能少 如果有默认值 你不想传 写null 即可
2.insert into 表名(字段1,字段2,字段3,字段4) values(值1,值2,值3,值4);
本来有5个字段 其中一个字段有默认值 可以只写 4个字段 对应的值 写4个即可
mysql> insert into test values(1,'hahahaha','123abc','太阳出来东方亮');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test(id,password) values(2,'123321');
Query OK, 1 row affected (0.00 sec)
批量插入
create table users(id int(11) unsigned not null primary key auto_increment,username char(64) not null,password char(60),email char(64) not null,sex tinyint not null)engine=innodb default charset=utf8;
mysql> insert into users(username,email,sex) values
('canglaoshi','cang@126.com',1),
('sicong','cong@wanda.com',1),
('dongge','dong@jd.com',1),
('mochameimei','mocha@126.com',1);
查询 select
select 'zelinx'; #类似于Linux中的 echo 'zelinx';
create table money(
id int(11) unsigned not null primary key auto_increment,
username varchar(50) not null,
balance float(8,2) not null,
province varchar(20) not null,
age tinyint unsigned not null,
sex tinyint not null
)engine=Myisam default charset=utf8;
INSERT INTO `money` (`id`, `username`, `balance`, `province`, `age`, `sex`) VALUES ('9', '薛之谦', '88888.12', '上海', '30', '0');
基础查询
select * from 表名;
* 正则表达式写法 匹配所有 显示所有字段的信息
mysql> select * from money;
+----+--------------+-----------+-----------+-----+-----+
| id | username | balance | province | age | sex |
+----+--------------+-----------+-----------+-----+-----+
| 1 | 泽林兄 | 88888.12 | 辽宁 | 19 | 0 |
| 2 | 秋林兄 | 66666.45 | 黑龙江 | 18 | 0 |
| 3 | 王思聪 | 99999.99 | 北京 | 30 | 0 |
| 4 | 刘强东 | 99999.66 | 江苏 | 40 | 0 |
| 5 | 抹茶妹妹 | 77777.66 | 浙江 | 30 | 1 |
| 6 | 范冰冰 | 77777.31 | 山东 | 40 | 1 |
| 7 | 李晨 | 666666.12 | 北京 | 30 | 0 |
| 8 | 魏缨络 | 7777.13 | 上海 | 29 | 1 |
| 9 | 薛之谦 | 88888.12 | 上海 | 30 | 0 |
+----+--------------+-----------+-----------+-----+-----+
9 rows in set (0.00 sec)
指定字段查询
mysql> select id,username,balance from money;
+----+--------------+-----------+
| id | username | balance |
+----+--------------+-----------+
| 1 | 泽林兄 | 88888.12 |
| 2 | 秋林兄 | 66666.45 |
| 3 | 王思聪 | 99999.99 |
| 4 | 刘强东 | 99999.66 |
| 5 | 抹茶妹妹 | 77777.66 |
| 6 | 范冰冰 | 77777.31 |
| 7 | 李晨 | 666666.12 |
| 8 | 魏缨络 | 7777.13 |
| 9 | 薛之谦 | 88888.12 |
+----+--------------+-----------+
9 rows in set (0.00 sec)
2000条 及以内数据 查询 不需要添加索引 添加索引反而累赘
2000条以上的数据要是提高查询速度 需要添加索引
查询 单个字段 不重复记录
mysql> select distinct age from money;
+-----+
| age |
+-----+
| 19 |
| 18 |
| 30 |
| 40 |
| 29 |
+-----+
5 rows in set (0.00 sec)
mysql> select distinct age,id,username from money; #不重复失效
+-----+----+--------------+
| age | id | username |
+-----+----+--------------+
| 19 | 1 | 泽林兄 |
| 18 | 2 | 秋林兄 |
| 30 | 3 | 王思聪 |
| 40 | 4 | 刘强东 |
| 30 | 5 | 抹茶妹妹 |
| 40 | 6 | 范冰冰 |
| 30 | 7 | 李晨 |
| 29 | 8 | 魏缨络 |
| 30 | 9 | 薛之谦 |
+-----+----+--------------+
9 rows in set (0.00 sec)
条件查询
mysql> select * from money where age=30;
+----+--------------+-----------+----------+-----+-----+
| id | username | balance | province | age | sex |
+----+--------------+-----------+----------+-----+-----+
| 3 | 王思聪 | 99999.99 | 北京 | 30 | 0 |
| 5 | 抹茶妹妹 | 77777.66 | 浙江 | 30 | 1 |
| 7 | 李晨 | 666666.12 | 北京 | 30 | 0 |
| 9 | 薛之谦 | 88888.12 | 上海 | 30 | 0 |
+----+--------------+-----------+----------+-----+-----+
4 rows in set (0.00 sec)
where 条件
符号 | 说明 |
---|---|
> | |
< | |
= | |
<= | |
!= | |
= | |
or | 或者 |
and | 并且 |
mysql> select id,username from money where id<9 and province='北京';
+----+-----------+
| id | username |
+----+-----------+
| 3 | 王思聪 |
| 7 | 李晨 |
+----+-----------+
结果集排序 order by
- desc 倒序排列
- asc 正序排列
mysql> select id,username,balance,province from money order by balance desc;
+----+--------------+-----------+-----------+
| id | username | balance | province |
+----+--------------+-----------+-----------+
| 7 | 李晨 | 666666.12 | 北京 |
| 3 | 王思聪 | 99999.99 | 北京 |
| 4 | 刘强东 | 99999.66 | 江苏 |
| 1 | 泽林兄 | 88888.12 | 辽宁 |
| 9 | 薛之谦 | 88888.12 | 上海 |
| 5 | 抹茶妹妹 | 77777.66 | 浙江 |
| 6 | 范冰冰 | 77777.31 | 山东 |
| 2 | 秋林兄 | 66666.45 | 黑龙江 |
| 8 | 魏缨络 | 7777.13 | 上海 |
+----+--------------+-----------+-----------+
9 rows in set (0.00 sec)
多字段 排序
mysql> select id,username,balance,age,province from money order by balance desc,age asc;
+----+--------------+-----------+-----+-----------+
| id | username | balance | age | province |
+----+--------------+-----------+-----+-----------+
| 7 | 李晨 | 666666.12 | 30 | 北京 |
| 3 | 王思聪 | 99999.99 | 30 | 北京 |
| 4 | 刘强东 | 99999.66 | 40 | 江苏 |
| 1 | 泽林兄 | 88888.12 | 19 | 辽宁 |
| 9 | 薛之谦 | 88888.12 | 30 | 上海 |
| 5 | 抹茶妹妹 | 77777.66 | 30 | 浙江 |
| 6 | 范冰冰 | 77777.31 | 40 | 山东 |
| 2 | 秋林兄 | 66666.45 | 18 | 黑龙江 |
| 8 | 魏缨络 | 7777.13 | 29 | 上海 |
+----+--------------+-----------+-----+-----------+
9 rows in set (0.00 sec)
上面 按照 余额 降序 年龄升序
如果 第一个字段 已经拍好了 那么第二个字段就失效
如果第一个字段 排不好 第二个就生效
限制结果集 limit 数据行 行数
mysql> select id,username,balance,age,province from money order by balance desc,age asc limit 5;
+----+-----------+-----------+-----+----------+
| id | username | balance | age | province |
+----+-----------+-----------+-----+----------+
| 7 | 李晨 | 666666.12 | 30 | 北京 |
| 3 | 王思聪 | 99999.99 | 30 | 北京 |
| 4 | 刘强东 | 99999.66 | 40 | 江苏 |
| 1 | 泽林兄 | 88888.12 | 19 | 辽宁 |
| 9 | 薛之谦 | 88888.12 | 30 | 上海 |
+----+-----------+-----------+-----+----------+
5 rows in set (0.00 sec)
将结果限制为 5行