MySQL的使用
所有的sql语句必须以分号;
结尾
进入数据库
mysql -u用户名 -p密码
退出登录
exit/ctrl+d/quit
一. 库操作
1. 查看所有数据库
show databases;
2. 查看当前使用的数据库
select database();
3. 显示当前数据库时间
select now();
4. 切换数据库
use db_name;
5. 创建新数据库
create database db_name charset=utf8;
create database python charset=utf8;
创建python数据库
6. 查看数据库是怎么创建的
show create database db_name
7. 删除数据库(慎用!)
drop database db_name;
drop database python;
删除python数据库
8. 查看数据库版本
select version();
二. 数据的完整性
- 一个数据库就是一个完整的业务单元,可以包含多张表,数据被存储在表中
- 在表中为了更加准确的存储数据,保证数据的正确有效,可以在创建表的时候,为表添加一些强制性的验证,包括数据字段的类型、约束
1. 数据类型
- 可以通过查看帮助文档查阅所有支持的数据类型
- 使用数据类型的原则是:够用就行,尽量使用取值范围小的,而不用大的,这样可以更多的节省存储空间
- 常用数据类型如下:
- 整数:int,bit
- 小数:decimal
- 字符串:varchar,char
- 日期时间: date, time, datetime
- 枚举类型(enum)
- 特别说明的类型如下:
- decimal表示浮点数,如decimal(5,2)表示共存5位数,小数占2位
- char表示固定长度的字符串,如char(3),如果填充'ab'时会补一个空格为
'ab '
- varchar表示可变长度的字符串,如varchar(3),填充'ab'时就会存储'ab'
- 字符串text表示存储大文本,当字符大于4000时推荐使用
- 对于图片、音频、视频等文件,不存储在数据库中,而是上传到某个服务器上,然后在表中存储这个文件的保存路径
- enum: 插入数据时,只能枚举出来的选项中选择
- 更全的数据类型可以参考http://blog.csdn.net/anxpp/article/details/51284106
2. 约束
- 主键
primary key
:物理上存储的顺序 - 非空
not null
:此字段不允许填写空值 - 惟一
unique
:此字段的值不允许重复 AUTO_INCREMENT
自动增长unsigned
无符号- 默认default:当不填写此值时会使用默认值,如果填写时以填写为准
- 外键foreign key:对关系字段进行约束,当为关系字段填写值时,会到关联的表中查询此值是否存在,如果存在则填写成功,如果不存在则填写失败并抛出异常
- 说明:虽然外键约束可以保证数据的有效性,但是在进行数据的crud(增加、修改、删除、查询)时,都会降低数据库的性能,所以不推荐使用,那么数据的有效性怎么保证呢?答:可以在逻辑层进行控制
数值类型(常用)
类型 | 字节大小 | 有符号范围(Signed) | 无符号范围(Unsigned) |
---|---|---|---|
TINYINT | 1 | -128 ~ 127 | 0 ~ 255 |
SMALLINT | 2 | -32768 ~ 32767 | 0 ~ 65535 |
MEDIUMINT | 3 | -8388608 ~ 8388607 | 0 ~ 16777215 |
INT/INTEGER | 4 | -2147483648 ~2147483647 | 0 ~ 4294967295 |
BIGINT | 8 | -9223372036854775808 ~ 9223372036854775807 | 0 ~ 18446744073709551615 |
字符串
类型 | 字节大小 | 示例 |
---|---|---|
CHAR | 0-255 | 类型:char(3) 输入 'ab', 实际存储为'ab ', 输入'abcd' 实际存储为 'abc' |
VARCHAR | 0-255 | 类型:varchar(3) 输 'ab',实际存储为'ab', 输入'abcd',实际存储为'abc' |
TEXT | 0-65535 | 大文本 |
日期时间类型
类型 | 字节大小 | 示例 |
---|---|---|
DATE | 4 | '2020-01-01' |
TIME | 3 | '12:29:59' |
DATETIME | 8 | '2020-01-01 12:29:59' |
YEAR | 1 | '2017' |
TIMESTAMP | 4 | '1970-01-01 00:00:01' UTC ~ '2038-01-01 00:00:01' UTC |
三. 表操作
1. 查看所有数据表
show tables;
2. 查看表结构
desc tab_name;
3. 创建表
create table tab_name(
字段名 字段数据类型 约束,
字段名 字段数据类型 约束,
字段名 字段数据类型 约束,
字段名 字段数据类型 约束,
...........................
);
3.1. 创建表案例
create table students(
num_id int unsigned primary key auto_increment not null,
name varchar(30) not null,
age tinyint(3) unsigned,
gender enum('男', '女', '中性', '保密') default '男',
addr varchar(255)
);
4. 查看表示如何创建的
show create table tab_name;
show create table students;
5. 修改表名称
alter table old_tab_name rename to/as new_tab_name;
6. 删除表
drop table tab_name;
7. 修改表结构
7.1 添加字段
alter table tab_name add 字段名 类型和约束;
alter table students add birthday datetime default "1990-1-1";
7.2 修改字段名称和类型
alter table tab_name change 原字段名 新字段名 类型和约束;
alter table students change birthday birth date default "1990-01-01";
7.3 删除字段
alter table tab_naem dorp 字段名;
alter table students drop birthday;
四. 表的CRUD(重点)
1. 插入数据
1.1 全字段插入
1.1.1 全字段插入单条数据
insert into tab_name values(字段1, 字段2, 字段....);
查看表结构,再对应插入数据
mysql> desc students; +----------+-------------------------------------+------+-----+------------+----------------+ | Field | Type | Null | Key | Default | Extra | +----------+-------------------------------------+------+-----+------------+----------------+ | num_id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(30) | NO | | NULL | | | age | tinyint(3) unsigned | YES | | NULL | | | gender | enum('男','女','中性','保密') | YES | | 男 | | | addr | varchar(255) | YES | | NULL | | | birthday | date | YES | | 1990-01-01 | | +----------+-------------------------------------+------+-----+------------+----------------+
insert into students values(0, '老李', 99, 1, '地球南美', "2017-01-01");
insert into students values(null, 'Gavin', 22, "男", '贵州贵阳', '1997-09-24');
说明:
- 注意!插入字段的值和类型必须跟定义的一致,并且不能超过最大值,否则会报错
- 定义表时定义了默认值(default)的时候, 在全字段插入时, 可以不用插入定义了默认值的字段,只需要用
default
占个位置,就会在此字段使用默认值 - 枚举类型中的下标从1开始
1.1.2 全字段插入多条
insert into students values (default, '老杨', 22, "男", '贵州六盘水', "1997-09-24"), (default, '老冯', 20, "女", '贵州金沙', "1998-11-20"), (default, '老张', 21, "女", '贵州贵阳', "1997-12-12"), (default, '老朱', 22, "女", '贵州六盘水', "1996-05-23");
1.2 指定字段
1.2.1指定字段插入单条数据
insert into students (字段1, 字段2, 字段...) values(对应字段1, 对应字段2, 对应字段...);
insert into students (name, age, gender) values("老老王", 88, 4);
1.2.2 指定字段插入多条数据
insert into students (name, age, gender) values ('aaa', 21, 1), ('bbb', 22, 2), ('ccc', 20, 3), ('ddd', 23, 4), ('eee', 28, 1), ('xxx', 29, 2);
2. 修改数据
2.1 修改全部
update tab_name set 列1=值1, 列2=值2...where 条件
update students set age=20;
把所有人的age都改成20
2.2 指定条件修改
update students set age=22 where name="Gavin";
把所有name="Gavin"的行的age都修改成22update students set addr="地球", age=99 where addr is NULL;
把所有addr是NULL的行的addr修改成地球,age修改成99
3. 删除数据
3.1 物理删除
3.1.1 删除全部
delete from tab_name where 1;
删除表中的所有数据delete from yyy where 1;
删除yyy表中的所有数据
3.1.2 指定条件删除
delete from students where name="aaa";
删除name为aaa的行
3.2 逻辑删除
- 添加一个字段来表示这条信息是否不能再使用了
# 添加一个is_delete字段,默认为0
1. alter table students add is_delete bit default 0;
2. 把你要删除的数据的is_delete修改为1, 就表示删除了
4. 查询基本使用
4.1 查询所有
select * from tab_name;
4.1.1 查询所有并指定显示的列
select name, age, gender from tab_name;
查询所有数据,显示name, age, gender这些列
4.1.2 查询所有并指定显示的列,同时为显示的列取别名
select name as 姓名, age as 年龄, gender as 性别, addr as 地址 from students;
增强可读性
4.2 指定条件查询
4.2.1 指定条件查询,显示所有信息
select * from students where gender="女";
查询所有gender="女"的行
4.2.2 指定条件查询,显示指定字段
select name, age, gender from students where gender="男"
查询gender="男"的所有行,并显示name, age, gender等字段
五. 高级查询
1. 查询所有
1.1 查询所有字段
select * from students;
1.2 查询指定字段
select name, age, gender from students;
1.3 查询指定字段, 指定别名
select name as 姓名, age as 年龄, gender as 性别 from students;
2. 指定条件查询
2.1 指定条件查询,显示所有信息
select * from students where gender="女";
查询所有gender="女"的行
2.2 指定条件查询,显示指定字段
select name, age, gender from students where gender="男"
查询gender="男"的所有行,并显示name, age, gender等字段select students.name, students.age, students.gender from students;
通过表名.字段查询
2.3 给表取别名
select s.name, s.age, s.gender from students as s;
使用别名.字段名查询
3. 对查询结果消除重复
使用
distinct
字段select distinct gender from students;
对查询的字段gender消重
4. 使用逻辑运算符查询
4.1 and 多个条件都要满足
select * from students where age>23 and age<27;
查询age大于23 并且age小于27的所有满足条件的行select name, age, gender, birthday where age>22 and age<50;
查询所有age大于等于22,并且小于等于50的所有行,并显示指定字段select * from students where age < 27 and gender="女";
查询年龄小于27的所有女生
4.2 or 满足其中一个条件
select * from students where age<22 or age>27;
查询age小于22或者age大于27的数据select * from students where (age<22 or age>27) and gender="女";
查询age小于22或者age大于27的女生 信息
4.3 not
select * from students where (not age<22) and gender="女";
查询所有age不在22以上的女生select * from students where not (age<22 and gender="男");
查询所有age不小于22的男生
5. 模糊查询
5.1 like 替换、
%
: 替换一个或者多个
_
: 替换一个
select * from students where name like "老_";
查询所有以老开头的并且只有两个字名字select * from students where name like "老%";
查询所有以老开头的名字,后面任意多个字符select * from students where name like "老%李";
查询所有以老开头,以李结尾,中间可以是一个或者多个字符的名字
5.2 rlike 正则
支持正则表达式查询
语法:select ... from tab_name where 条件 rlike "正则表达式" ;
select * from students where name rlike "^老";
查询所有以老开头的名字select * from students where name rlike "王$";
查询所有以王结尾的名字select * from students where addr rlike "贵.*";
查询所有以贵开头的地址
6. 范围查询
6.1 in
in(22, 26, 30) 表示在一个非连续的范围内
select * from students where age=21 or age=25 or age=30;
select * from students where age in(21, 25, 30);
查询所有age为21,25,30的所有信息
6.2 not in
not in 不在非连续范围内
select * from students where age not in(21, 25, 30);
查询所有age不是21,25,30的所有信息
6.3 between...and...
between...and...表示在一个连续的范围内
select * from students where age between 20 and 30;
查询所有age在20到30的信息select * from students where age between 20 and 30 and gender=2;
查询所有age在20到30这个范围内的女生
6.4 not between...and...
not between...and... 表示不在一个连续的范围内
select * from students where age not between 22 and 30;
查询所有age不在20到30这个范围内的信息select * from students where age not between 22 and 30 and gender=2;
查询所有age不在22到30这个范围内的信息
7. 判断空
7.1 is null: 判断是空
select * from students where addr is null;
select * from students where addr is NuLl;
查询addr是空的信息说明:null不区分大小写
7.2 is not null 判断不是空
select * from students where addr is not null;
查询addr不是空的信息