MySQL--02
MySQL 字段类型
查看表的结构: desc 表名;
description -> desc
mysql 三种基本数据类型： 数字 字符 日期
id 可以存成数字 每一条记录的编号
小数：浮点数(float单精度浮点，double )，定点数
钱 17.23 定点数 decimal
常用文本类型： char() 定长字符 / varchar 变长字符(255)
text 很长文字
longtext 长文本
blob 备注类型，可以存二制
date 2019-03-24
datetime 2019-03-24 12:34:25
timestamp '12345678921' 1970-01-01 00:00:00
特殊类型
tinyint 特别小的整数 -127-127 unsigned tinyint 0-255
enum('黑色','白色') tinyint
enum('男','女','保密') 3
create table student( id int(6), name varchar(50), age tinyint, gender enum('男','女','保密'), enroll datetime, salary decimal(11,2) );
类型后面的（）里的长度叫做显示宽度
int 默认长度 11 位
enum('男','女') # 枚举
datetime '2018-12-01 09:30:00'
salary decimal(5,2) 999.99 salary decimal(7,2) 99999.99
MariaDB [baobao2]> create table jingdong( -> id int, -> goods varchar(255), -> price decimal(8,2), -> amount int, -> add_time datetime, -> color enum('black','gray','green'), -> -> category varchar(30) -> ); Query OK, 0 rows affected (0.02 sec) MariaDB [baobao2]> desc jingdong; +----------+------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------+------------------------------+------+-----+---------+-------+ | id | int(11) | YES | | NULL | | | goods | varchar(255) | YES | | NULL | | | price | decimal(8,2) | YES | | NULL | | | amount | int(11) | YES | | NULL | | | add_time | datetime | YES | | NULL | | | color | enum('black','gray','green') | YES | | NULL | | | category | varchar(30) | YES | | NULL | | +----------+------------------------------+------+-----+---------+-------+
修改已创建的表名：
命令格式：
alter table 原表名 rename to 新表名;
退出数据库命令：
查看进程 命令
windows 下
tasklist|findStr mysql
linux 下的命令
ps -ef|grep mysql
修改密码
早期版本修改：
alter user `要修改的帐户`@`数据库地址` identified by '新密码';
alter ： 修改
update mysql.user set password = password('密码') where user='要修改的用户'; flush privileges; exit;
flush privileges; 刷新mysql 权限
查看 mysql 版本：
select version();
查询时间
MariaDB [(none)]> select now(); +---------------------+ | now() | +---------------------+ | 2019-03-29 14:46:43 | +---------------------+ 1 row in set (0.00 sec)
默认引擎
1 进入mysql的命令是____mysql -h ip地址 -uroot -p_____ 2 安装mysql服务的命令是_____mysqld --install____ 3 查看进程中是否存在mysql服务使用的命令是___tasklist|findStr mysqld___________ 4 退出mysql登录的用___exit____或____quit___ 5 停止mysql服务用____ net stop mysql___
启动mysql服务用 net start mysql
条件查询
基本查询
select [要查询的字段，如果是所有字段，就是*;如果单个，写字段名] from 【表名】 where 【条件】;
例如，选择部分字段显示查询
select name,exp from employe;
加 where 条件查询
MariaDB [employe]> select name,exp from employe where exp >200; +-----------+------+ | name | exp | +-----------+------+ | sunwukong | 255 | | 猪八戒 | 255 | | 沙和尚 | 255 | +-----------+------+ 3 rows in set (0.00 sec) MariaDB [employe]> select * from employe where name='沙和尚'; +----+--------+------+ | id | name | exp | +----+--------+------+ | 3 | 沙和尚 | 255 | +----+--------+------+ 1 row in set (0.00 sec)
比较运算符：
> < >= <= !=
insert into department(id, dep, master, amount, kpi, foundation, groups, status)values(1,'教务','张四丰',8,80,'2019-02-20',1,1),(1,'教务','张五丰',8,80,'2019-03-20',1,1),(1,'教务','张六丰',8,80,'2019-04-20',1,1);
-
select * from department where foundation between '2019-02-01' and '2019-04-01';
命令格式： select [字段] from 表名 where 字段名 between 开始 and 结束
-
in ( ) 在 ... 里 not in
-
select * from department where id in (1,2,3)
and or
and 一假即假
not
模糊查询
select * from department where master like '张%丰';
MariaDB [employe]> select * from department where master like '黄_'; +------+--------+--------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +------+--------+--------+--------+------+------------+--------+--------+ | NULL | 学生处 | 黄山 | 10 | 8.00 | 2019-04-01 | | 2 | +------+--------+--------+--------+------+------------+--------+--------+ 1 row in set (0.00 sec) MariaDB [employe]> select * from department where master like '黄__'; +------+--------+--------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +------+--------+--------+--------+------+------------+--------+--------+ | NULL | 学生处 | 黄玉石 | 10 | 8.00 | 2019-04-01 | | 2 | +------+--------+--------+--------+------+------------+--------+--------+ 1 row in set (0.00 sec) MariaDB [employe]> select * from department where master like '%山'; +------+--------+--------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +------+--------+--------+--------+------+------------+--------+--------+ | NULL | 学生处 | 黄山 | 10 | 8.00 | 2019-04-01 | | 2 | +------+--------+--------+--------+------+------------+--------+--------+ 1 row in set (0.00 sec) MariaDB [employe]> select * from department where master like '%拉斯%'; +------+--------+-----------------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +------+--------+-----------------+--------+------+------------+--------+--------+ | NULL | 学生处 | 黄尼古拉斯*赵四 | 10 | 8.00 | 2019-04-01 | | 2 | +------+--------+-----------------+--------+------+------------+--------+--------+ 1 row in set (0.00 sec) MariaDB [employe]> select * from department where master like '_山'; +------+--------+--------+--------+------+------------+--------+--------+ | id | dep | master | amount | kpi | foundation | groups | status | +------+--------+--------+--------+------+------------+--------+--------+ | NULL | 学生处 | 黄山 | 10 | 8.00 | 2019-04-01 | | 2 | +------+--------+--------+--------+------+------------+--------+--------+
mysql 除了个别引擎之外，所有数据都是文件读写，也就是数据实际是是存在硬盘上的文件 - 默认端口 3306 - 默认引擎 default-storage-engine=InnoDB - 默认字符集 default-character-set=utf8 - mysql程序目录 basedir=pathtomysql - 放数据目录 datadir= data目录 - 最大连接数 max_connections=200 [mysql] default-character-set=utf8 # 客户端字符集 > 特别备注：如果，修改了my.ini(my.cnf)必须重启数据库**服务**，才会有效。
