一.数据类型
常用的数据类型如下：
- 整数：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 时推荐使用, 比如博客
二、数据约束
约束本质上是对数据在数据类型限定的基础上添加的额外的要求.
常见的约束如下:
- 主键 primary key: 用于唯一标识一条记录. MySQL 建议所有表的主键字段都叫 id, 类型为 int unsigned.
- 非空 not null: 此字段不允许填写空值.
- 惟一 unique: 此字段的值不允许重复.
- 默认 default: 当不填写字段对应的值会使用默认值，如果填写时以填写为准.
- 外键 foreign key: 对关系字段进行约束, 当为关系字段填写值时, 会到关联的表中查询此值是否存在, 如果存在则填写成功, 如果不存在则填写失败并抛出异常.
说明：虽然外键约束可以保证数据的有效性, 在进行数据的 crud（增删改查）, 会降低数据库的性能, 此种情况下不推荐使用
最后附上常见的数据类型表
1. 整数类型
| 类型 | 字节大小 | 有符号范围(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 |
2. 字符串
| 类型 | 字节大小 | 示例 |
|---|---|---|
| CHAR | 0-255 | 类型:char(3) 输入 'ab', 实际存储为'ab ', 输入'abcd' 实际存储为 'abc' |
| VARCHAR | 0-255 | 类型:varchar(3) 输 'ab',实际存储为'ab', 输入'abcd',实际存储为'abc' |
| TEXT | 0-65535 | 大文本 |
3. 日期时间类型
| 类型 | 字节大小 | 示例 |
|---|---|---|
| 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、登录以及退出MYSQL
- 登录：mysql -uroot -p
- 退出：exit 或者 quit
- 查看数据库版本：select version();
- 显示当前的日期时间：select now();
- 显示当前的年份 select year(now());
- 显示当前的时间 select time(now());
- 查看环境变量 show variables;
- 查看状态 show status;
- 查看用户登录信息 执行语句 show processlist;
- 查看上次报警信息 show warnings;
- 开启查看时间监控 set profiling=1 select语句 show profiles;
2、数据库操作
- 查看当前在哪个数据库：select database();
- 查看所有数据库：show databases;
- 使用数据库：use 数据库名;
- 创建数据库: create database [if not exists] 数据库名；推荐这种方法
- 创建数据库：create database 数据库名 charset=utf8; (charset=utf8一定要加，不然表里用到中文可能会乱码，后续更改很麻烦)
- 删除数据库： drop database 数据库名;
- 删除数据库：drop database [if exists] 数据库名; 推荐这种方法
3、数据表结构的操作
- 查看当前数据库的所有表：show tables;
- 删除表：drop table 表名;
- 查看表结构：desc 表名;
- 创建表结构：create table 表名(字段名称 数据类型 约束条件,*其他的格式和前面的一样)
注意：主键说明可以放在字段中单独说明 也可以放在最后统一说明PRIMARY KEY(字段名称)
表基础操作
-- 1.创建表（类似于一个excel表）
create table tab_name(
field1 type[完整性约束条件],
field2 type,
...
fieldn type
)[character set xxx];
-- 创建一个员工表employee
create table employee(
id int primary key auto_increment ,
name varchar(20),
gender bit default 1, -- gender char(1) default 1 ----- 或者 TINYINT(1)
birthday date,
entry_date date,
job varchar(20),
salary double(4,2) unsigned,
resume text -- 注意，这里作为最后一个字段不加逗号
);
/* 约束:
primary key (非空且唯一) :能够唯一区分出当前记录的字段称为主键！
unique
not null
auto_increment 主键字段必须是数字类型。
外键约束 foreign key */
-- 2.查看表信息
desc tab_name 查看表结构
show columns from tab_name 查看表结构
show tables 查看当前数据库中的所有的表
show create table tab_name 查看当前数据库表建表语句
-- 3.修改表结构
-- (1)增加列(字段)
alter table tab_name add [column] 列名 类型［完整性约束条件］［first｜after 字段名］;
alter table user add addr varchar(20) not null unique first/after username;
#添加多个字段
alter table users2
add addr varchar(20),
add age int first,
add birth varchar(20) after name;
-- (2)修改一列类型
alter table tab_name modify 列名 类型 [完整性约束条件]［first｜after 字段名］;
alter table users2 modify age tinyint default 20;
alter table users2 modify age int after id;
-- (3)修改列名
alter table tab_name change [column] 列名 新列名 类型 [完整性约束条件]［first｜after 字段名］;
alter table users2 change age Age int default 28 first;
-- (4)删除一列
alter table tab_name drop [column] 列名;
-- 思考：删除多列呢？删一个填一个呢？
alter table users2
add salary float(6,2) unsigned not null after name,
drop addr;
-- (5)修改表名
rename table 表名 to 新表名;
-- (6)修该表所用的字符集
alter table student character set utf8;
-- 4.删除表
drop table tab_name;
---5 添加主键，删除主键
alter table tab_name add primary key(字段名称,...)
alter table users drop primary key;
eg:
mysql> create table test5(num int auto_increment);
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
create table test(num int primary key auto_increment);
-- 思考，如何删除主键？
alter table test modify id int; -- auto_increment没了，但这样写主键依然存在，所以还要加上下面这句
alter table test drop primary key;-- 仅仅用这句也无法直接删除主键
-- 唯一索引
alter table tab_name add unique [index|key] [索引名称](字段名称,...)
alter table users add unique(name)-- 索引值默认为字段名show create table users;
alter table users add unique key user_name(name);-- 索引值为user_name
-- 添加联合索引
alter table users add unique index name_age(name,age);#show create table users;
-- 删除唯一索引
alter table tab_name drop {index|key} index_name
三.表记录增删改
-- 1.增加一条记录insert
/*insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);*/
create table employee_new(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday varchar(20),
salary float(7,2)
);
insert into employee_new (id,name,birthday,salary) values
(1,'yuan','1990-09-09',9000);
insert into employee_new values
(2,'alex','1989-08-08',3000);
insert into employee_new (name,salary) values
('xialv',1000);
-- 插入多条数据
insert into employee_new values
(4,'alvin1','1993-04-20',3000),
(5,'alvin2','1995-05-12',5000);
-- set插入: insert ［into］ tab_name set 字段名=值 通过键值对插入数据
insert into employee_new set id=12,name="alvin3";
-- 2.修改表记录 update tab_name set field1=value1,field2=value2,......[where 语句]
/* UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。*/
update employee_new set birthday="1989-10-24" WHERE id=1;
--- 将yuan的薪水在原有基础上增加1000元。
update employee_new set salary=salary+4000 where name='yuan';
-- 3.删除表纪录 注意：一定要带where条件 不带是删表
delete from tab_name [where ....]
/* 如果不跟where语句则删除整张表中的数据
delete只能用来删除一行记录
delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在
事务中恢复。*/
-- 删除表中名称为’alex’的记录。
delete from employee_new where name='alex';
-- 删除表中所有记录。
delete from employee_new;-- 注意auto_increment没有被重置:alter table employee auto_increment=1;
-- 使用truncate删除表中记录。
truncate table emp_new;
四.单表查询
-- 1.增加一条记录insert
/*insert ［into］ tab_name (field1,filed2,.......) values (value1,value2,.......);*/
create table employee_new(
id int primary key auto_increment,
name varchar(20) not null unique,
birthday varchar(20),
salary float(7,2)
);
insert into employee_new (id,name,birthday,salary) values
(1,'yuan','1990-09-09',9000);
insert into employee_new values
(2,'alex','1989-08-08',3000);
insert into employee_new (name,salary) values
('xialv',1000);
-- 插入多条数据
insert into employee_new values
(4,'alvin1','1993-04-20',3000),
(5,'alvin2','1995-05-12',5000);
-- set插入: insert ［into］ tab_name set 字段名=值 通过键值对插入数据
insert into employee_new set id=12,name="alvin3";
-- 2.修改表记录 update tab_name set field1=value1,field2=value2,......[where 语句]
/* UPDATE语法可以用新值更新原有表行中的各列。
SET子句指示要修改哪些列和要给予哪些值。
WHERE子句指定应更新哪些行。如没有WHERE子句，则更新所有的行。*/
update employee_new set birthday="1989-10-24" WHERE id=1;
--- 将yuan的薪水在原有基础上增加1000元。
update employee_new set salary=salary+4000 where name='yuan';
-- 3.删除表纪录 注意：一定要带where条件 不带是删表
delete from tab_name [where ....]
/* 如果不跟where语句则删除整张表中的数据
delete只能用来删除一行记录
delete语句只能删除表中的内容，不能删除表本身，想要删除表，用drop
TRUNCATE TABLE也可以删除表中的所有数据，词语句首先摧毁表，再新建表。此种方式删除的数据不能在
事务中恢复。*/
-- 删除表中名称为’alex’的记录。
delete from employee_new where name='alex';
-- 删除表中所有记录。
delete from employee_new;-- 注意auto_increment没有被重置:alter table employee auto_increment=1;
-- 使用truncate删除表中记录。
truncate table emp_new;
-- 查询表达式
SELECT select_expr [,select_expr,...] [
FROM tb_name
[JOIN 表名]
[ON 连接条件]
[WHERE 条件判断]
[GROUP BY {col_name | postion} [ASC | DESC], ...]
[HAVING WHERE 条件判断]
[ORDER BY {col_name|expr|postion} [ASC | DESC], ...]
[ LIMIT {[offset,]rowcount | row_count OFFSET offset}]
]
# 精简版
select distinct *
from 表名
where ....
group by ... having ...
order by ...
limit start,count
#执行顺序
from 表名
where ....
group by ...
select distinct *
having ...
order by ...
limit start,count
---准备表
CREATE TABLE ExamResult(
id INT PRIMARY KEY auto_increment,
name VARCHAR (20),
JS DOUBLE ,
Django DOUBLE ,
OpenStack DOUBLE
);
INSERT INTO ExamResult(name,JS,Django,OpenStack) VALUES ("yuan",98,98,98),
("xialv",35,98,67),
("alex",59,59,62),
("wusir",88,89,82),
("alvin",88,98,67),
("yuan",86,100,55);
-- （1）select [distinct] *|field1，field2，...... from tab_name
-- 其中from指定从哪张表筛选，*表示查找所有列，也可以指定一个列
-- 表明确指定要查找的列，distinct用来剔除重复行。
-- 查询表中所有学生的信息。
select * from ExamResult;
-- 查询表中所有学生的姓名和对应的英语成绩。
select name,JS from ExamResult;
-- 过滤表中重复数据。
select distinct JS ,name from ExamResult;
+------+-------+
| JS | name |
+------+-------+
| 98 | yuan |
| 35 | xialv |
| 59 | alex |
| 88 | wusir |
| 88 | alvin |
| 86 | yuan |
+------+-------+
-- （2）select 也可以使用表达式，并且可以使用: 字段 as 别名或者:字段 别名
-- 在所有学生分数上加10分特长分显示。
select name,(JS+10)as JS成绩,(Django+10)Django成绩,(OpenStack+10)OpenStack成绩 from ExamResult;
+-------+----------+--------------+-----------------+
| name | JS成绩 | Django成绩 | OpenStack成绩 |
+-------+----------+--------------+-----------------+
| yuan | 108 | 108 | 108 |
| xialv | 45 | 108 | 77 |
| alex | 69 | 69 | 72 |
| wusir | 98 | 99 | 92 |
| alvin | 98 | 108 | 77 |
| yuan | 96 | 110 | 65 |
-- 统计每个学生的总分。
select name,JS+Django+OpenStack from ExamResult;
-- 使用别名表示学生总分。
select name as 姓名,JS+Django+OpenStack as 总成绩 from ExamResult;
select name,JS+Django+OpenStack 总成绩 from ExamResult;
+-------+-----------+
| name | 总成绩 |
+-------+-----------+
| yuan | 294 |
| xialv | 200 |
| alex | 180 |
| wusir | 259 |
| alvin | 253 |
| yuan | 241 |
+-------+-----------+
select name JS from ExamResult; -- what will happen?---->记得加逗号
-- （3）使用where子句，进行过滤查询。
-- 查询姓名为XXX的学生成绩
select * from ExamResult where name='yuan';
-- 查询英语成绩大于90分的同学
select id,name,JS from ExamResult where JS>90;
-- 查询总分大于200分的所有同学
select name,JS+Django+OpenStack as 总成绩 from
ExamResult where JS+Django+OpenStack>200 ;
-- where字句中可以使用：
-- 比较运算符：
> < >= <= <> !=
between 80 and 100 值在80到100之间
in(80,90,100) 值是80或90或100
like 'yuan%'
/*
pattern可以是%或者_，
如果是%则表示任意多字符，此例如唐僧,唐国强
如果是_则表示一个字符唐_，只有唐僧符合。两个_则表示两个字符：__
*/
-- 逻辑运算符
在多个条件直接可以使用逻辑运算符 and or not
-- 练习
-- 查询JS分数在 70－100之间的同学。
select name ,JS from ExamResult where JS between 70 and 100;
-- 查询Django分数为75,76,77的同学。
select name ,Django from ExamResult where Django in (75,76,77);
-- 查询所有姓王的学生成绩。
select * from ExamResult where name like '王%';
-- 查询JS分>90，Django分>90的同学。
select id,name from ExamResult where JS>90 and Django >90;
-- 查找缺考数学的学生的姓名
select name from ExamResult where Database is null;
-- （4）Order by 指定排序的列，排序的列即可是表中的列名，也可以是select 语句后指定的别名。
-- select *|field1,field2... from tab_name order by field [Asc|Desc]
-- Asc 升序、Desc 降序，其中asc为默认值 ORDER BY 子句应位于SELECT语句的结尾。
-- 练习：
-- 对JS成绩排序后输出。
select * from ExamResult order by JS;
-- 对总分排序按从高到低的顺序输出
select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
总成绩 from ExamResult order by 总成绩 desc;
-- 对姓李的学生成绩排序输出
select name ,(ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)) as
总成绩 from ExamResult where name like 'a%'
order by 总成绩 desc;
-- （5）group by 分组查询：
CREATE TABLE order_menu(
id INT PRIMARY KEY auto_increment,
product_name VARCHAR (20),
price FLOAT(6,2),
born_date DATE,
class VARCHAR (20)
);
INSERT INTO order_menu (product_name,price,born_date,class) VALUES
("苹果",20,20170612,"水果"),
("香蕉",80,20170602,"水果"),
("水壶",120,20170612,"电器"),
("被罩",70,20170612,"床上用品"),
("音响",420,20170612,"电器"),
("床单",55,20170612,"床上用品"),
("草莓",34,20170612,"水果");
select * from order_menu;
+----+--------------+--------+------------+--------------+
| id | product_name | price | born_date | class |
+----+--------------+--------+------------+--------------+
| 1 | 苹果 | 20.00 | 2017-06-12 | 水果 |
| 2 | 香蕉 | 80.00 | 2017-06-02 | 水果 |
| 3 | 水壶 | 120.00 | 2017-06-12 | 电器 |
| 4 | 被罩 | 70.00 | 2017-06-12 | 床上用品 |
| 5 | 音响 | 420.00 | 2017-06-12 | 电器 |
| 6 | 床单 | 55.00 | 2017-06-12 | 床上用品 |
| 7 | 草莓 | 34.00 | 2017-06-12 | 水果 |
+----+--------------+--------+------------+--------------+
-- 注意,按分组条件分组后每一组只会显示第一条记录
-- group by字句，其后可以接多个列名，也可以跟having子句,对group by 的结果进行筛选。
-- 按位置字段筛选
select * from order_menu group by 5;
+----+--------------+--------+------------+--------------+
| id | product_name | price | born_date | class |
+----+--------------+--------+------------+--------------+
| 4 | 被罩 | 70.00 | 2017-06-12 | 床上用品 |
| 1 | 苹果 | 20.00 | 2017-06-12 | 水果 |
| 3 | 水壶 | 120.00 | 2017-06-12 | 电器 |
+----+--------------+--------+------------+--------------+
-- 练习：对购物表按类名分组后显示每一组商品的价格总和
select class as 商品,SUM(price) as 总价 from order_menu group by class;
+--------------+--------+
| 商品 | 总价 |
+--------------+--------+
| 床上用品 | 125.00 |
| 水果 | 134.00 |
| 电器 | 540.00 |
+--------------+--------+
-- 练习：对购物表按类名分组后显示每一组商品价格总和超过150的商品
select class,SUM(price)from order_menu group by class
HAVING SUM(price)>150;
+--------+------------+
| class | SUM(price) |
+--------+------------+
| 电器 | 540.00 |
+--------+------------+
/*
having 和 where两者都可以对查询结果进行进一步的过滤，差别有：
<1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；
<2>使用where语句的地方都可以用having进行替换
<3>having中可以用聚合函数，where中就不行。
*/
-- GROUP_CONCAT() 函数
SELECT id,GROUP_CONCAT(name),GROUP_CONCAT(JS) from ExamResult GROUP BY id;
-- （6）聚合函数： 先不要管聚合函数要干嘛，先把要求的内容查出来再包上聚合函数即可。
-- (一般和分组查询配合使用)
--<1> 统计表中所有记录
-- COUNT(列名)：统计行的个数
-- 统计一个班级共有多少学生？先查出所有的学生，再用count包上
select count(*) from ExamResult;
-- 统计JS成绩大于70的学生有多少个？
select count(JS) from ExamResult where JS>70;
-- 统计总分大于280的人数有多少？
select count(name) from ExamResult
where (ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0))>280;
-- 注意:count(*)统计所有行; count(字段)不统计null值.
-- SUM(列名)：统计满足条件的行的内容和
-- 统计一个班级JS总成绩？先查出所有的JS成绩，再用sum包上
select JS as JS总成绩 from ExamResult;
select sum(JS) as JS总成绩 from ExamResult;
-- 统计一个班级各科分别的总成绩
select sum(JS) as JS总成绩,
sum(Django) as Django总成绩,
sum(OpenStack) as OpenStack from ExamResult;
-- 统计一个班级各科的成绩总和
select sum(ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0))
as 总成绩 from ExamResult;
-- 统计一个班级JS成绩平均分
select sum(JS)/count(*) from ExamResult ;
-- 注意：sum仅对数值起作用，否则会报错。
-- AVG(列名)：
-- 求一个班级JS平均分？先查出所有的JS分，然后用avg包上。
select avg(ifnull(JS,0)) from ExamResult;
-- 求一个班级总分平均分
select avg((ifnull(JS,0)+ifnull(Django,0)+ifnull(Database,0)))
from ExamResult ;
-- Max、Min
-- 求班级最高分和最低分（数值范围在统计中特别有用）
select Max((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
最高分 from ExamResult;
select Min((ifnull(JS,0)+ifnull(Django,0)+ifnull(OpenStack,0)))
最低分 from ExamResult;
-- 求购物表中单价最高的商品名称及价格
---SELECT id, MAX(price) FROM order_menu;--id和最高价商品是一个商品吗?
SELECT MAX(price) FROM order_menu;
-- 注意：null 和所有的数计算都是null，所以需要用ifnull将null转换为0！
-- -----ifnull(JS,0)
-- with rollup的使用
--<2> 统计分组后的组记录
-- （7） 重点：Select from where group by having order by
-- Mysql在执行sql语句时的执行顺序：
-- from where select group by having order by
-- 分析:
select JS as JS成绩 from ExamResult where JS成绩 >70; ---- 不成功
select JS as JS成绩 from ExamResult having JS成绩 >90; --- 成功
-- (8) limit
SELECT * from ExamResult limit 1;
SELECT * from ExamResult limit 2,5;--跳过前两条显示接下来的五条纪录
SELECT * from ExamResult limit 2,2;
--- (9) 使用正则表达式查询
SELECT * FROM employee WHERE emp_name REGEXP '^yu';
SELECT * FROM employee WHERE emp_name REGEXP 'yun$';
SELECT * FROM employee WHERE emp_name REGEXP 'm{2}';
参考：https://www.cnblogs.com/yuanchenqi/articles/6357507.html
https://www.cnblogs.com/chichung/p/9571004.html
https://www.cnblogs.com/chichung/p/9585127.html