MySQL操作

MySQL字段类型

在mysql中包含的数据类型很多，这里主要列出来常用的几种

• 数值：int，decimal
• 字符串：varchar，text
• 日期：datetime

数据类型详细

MySQL约束

• 主键：primary key
• 非空：not null
• 唯一：unique
• 默认值：default
• 外键：foreign key

逻辑删除

• 对于重要数据，并不希望物理删除，一旦删除，数据无法找回
• 一般对于重要数据，会设置一个isDelete的列，类型为bit，表示逻辑删除
• 大于大量增长的非重要数据，可以进行物理删除
• 数据的重要性，要根据实际开发决定

数据库操作

-- 创建数据库
create database [if not exists] db_name [character set utf8];

-- 查看数据
show databases;

-- 查看数据库创建方式
show create database db_name;

-- 修改数据库
alter database db_name [character set xxx];

-- 删除数据库
drop database [if exists] db_name;

-- 使用数据库
use db_name;

-- 查看当前使用数据库
select database();

数据库管理

-- 添加用户
-- 通过grant命令，给py3数据库，添加用户xx，密码123456xx
grant select,insert,update,delete,create,drop on py3 to 'xx'@'localhost' identified by '123456xx';

-- 修改用户密码
grant select,insert,update,delete,create,drop on py3 to 'xx'@'localhost' identified by 'xx';

-- 显示用户
select user from mysql.user;

-- 删除用户
drop user 'xx'@'localhost';

数据表操作

建表和查看表信息

-- 显示数据库所有表
show tables;

-- 创建表
create table employee(
    id int primary key auto_increment,
    name varchar(20),
    gender TINYINT(1) default 1,
    birthday date,
    entry_date date,
    job varchar(20),
    salary double(4,2),
    resume text	
);

-- 查看表结构
desc employee;

-- 查看创建表语句
show create table employee;

修改表结构

-- 增加列（字段）
alter table employee add address varchar(30);

-- 增加多个列
alter table employee add address varchar(30),
					 add age int,
					 add height int;
                     
-- 修改一列列名
alter table employee change address addr varchar(20);

-- 修改一列类型
alter table employee modify age tinyint default 20;

-- 删除一列
alter table employee drop height;

-- 修改表名
rename table employee to emp;

-- 修改表所用字符集
alter table employee character set utf8;

-- 删除表
drop table employee;



create table users(
	id int,
	name varchar(10),
	age int,
	height int
);

-- 添加主键
alter table users add primary key (id);
-- 删除主键
alter table users drop primary key;

-- 添加唯一索引
alter table users add unique(name);
-- 添加唯一索引设置索引名
alter table users add unique key user_name(name);

-- 添加联合索引
alter table users add unique index name_age(name,age);

-- 删除索引
alter table users drop index name;

表数据操作（增删改）

-- 创建一个学生表
create table student(
	id int primary key auto_increment,
	name varchar(20),
	birthday varchar(20),
	age int
);

-- 插入一条数据
insert into student(name,birthday,age) values('学生1','2001-1-1',11);

-- 插入多条数据
insert into student(name,birthday,age) values('学生2','2001-1-2',11),
                                        ('学生3','2001-1-3',11),
                                        ('学生4','2001-1-4',11);
                                        
-- 修改数据
update student set birthday='2001-1-10' where id=1;
-- 所有学生年龄+3
update student set age=age+3;

-- 删除数据
delete from student where id=1;
-- 删除表中所有数据
delete from student;

SQL 语法顺序和执行顺序

语法顺序：

1. SELECT[DISTINCT]
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. UNION
7. ORDER BY

执行顺序：

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. UNION
8. ORDER BY

 -- 不成功
 select js as js分数 from grade where js分数 >70; 
 
 -- 成功
 select js as js分数 from grade having js分数 >70;

表数据操作（单表查询）

-- 创建一个成绩表
create table grade(
	id int primary key auto_increment,
	name varchar(20),
	js double,
	java double,
	python double
);

-- 插入多条数据
insert into grade (name,js,java,python)
values ('Tom',68,89,87),('Jim',70,91,92),('Jake',71,73,74),('Mike',80,84,85),('Jame',85,88,83);

select 查询

-- 查询所有数据
select * from grade;
-- 查询姓名和js成绩
select name,js from grade;
-- 过滤表中重复数据
select distinct js from grade;

-- 给所有js成绩+10，并使用as别名
select name,js+10 as 'js成绩' from grade;

where 过滤查询

-- 查询姓名XX的学生信息
select * from grade where name='Tom';
-- 查询总分大于250分的所有学生
select name,js+java+python as '总成绩' from grade where js+java+python>250;
-- 查询js分数和java分数大于90的学生
select * from grade where js>90 and java>90;
-- 查询java分数80~90的学生
select * from grade where java between 80 and 90;
-- 查询java分数在80或90的学生
select * from grade where java in (80,90);

/*
模糊查询，%表示多个字符，_表示一个字符
*/
-- 查询姓名j开头的学生
select * from grade where name like 'j%';

order by 排序

/*
asc:升序，默认值
desc：降序
*/

-- 根据js分数升序排序
select * from grade order by js;

-- 更加总分数从高到低排序
select name,(ifnull(js,0)+ifnull(java,0)+ifnull(python,0)) as '总分数' 
from grade 
order by '总分数' desc;

group by 分组查询

其后可以接多个列名，也可以跟having子句，对group by 的结果进行筛选

-- 定义一个商品表
create table product_tab(
	id int primary key auto_increment,
	product_name varchar(20),
	price float(6,2),
	product_date date,
	class varchar(20)
);
-- 插入多条数据
insert into product_tab (product_name,price,product_date,class) values
('苹果',10,'20180812','水果'),
('香蕉',20,'20180826','水果'),
("水壶",120,'20170612',"电器"),
("被罩",70,'20170612',"家具"),
("音响",420,'20171012',"电器"),
("电视",2000,'20170912',"电器"),
("床单",55,'20171112',"家具"),
("草莓",34,'20170512',"水果");

-- 按位置分组
select * from product_tab group by 5;
-- 按产品类别分类并显示平均价格
select class,avg(price) from product_tab group by class;
-- 按产品分类显示每一种商品价格总和超过200的商品
select class,sum(price) from product_tab group by class having sum(price)>200;

-- 分组显示所有产品：group_concat
select id,group_concat(product_name) from product_tab group by class;

having和where的区别：

• where只能用于分组之前的筛选
• having只能用于分组以后的筛选
• 使用where的地方都可以用having替换
• having中可以使用聚合函数

聚合函数

-- COUNT（列名）：统计行的个数
-- 统计学生个数
select count(id) from grade;

-- SUM（列名）：统计总量
-- 统计所有js总成绩
select sum(js) as 'js总成绩' from grade;

-- AVG（列名）：平均数
-- 统计所有js平均分
select avg(js) as 'js平均分' from grade;

-- MAX,MIN （最高，最低）
select max(js) as 'js最高分' from grade;
select min(js) as 'js最低分' from grade;

limit

-- 查询前3条数据
select * from grade limit 3;
-- 跳过1条，查询3条数据
select * from grade limit 1,3;

-- 数据量大也可以优化为
select * from grade where id>5 limit 5;

正则表达式

-- 查询j开头的学生
select * from grade where name regexp '^j';
-- 查询名字中m出现2次的学生
select * from grade where name regexp 'm{2}';

外键约束

外键用于关联两个表

创建外键

/*
一个班级对应多个学生，一个学生只能对应一个班级
*/
-- 主表：创建班级表
create table class(
	id int primary key auto_increment,
	name varchar(20),
	stu_nums int
);
-- 子表：创建学生表
create table student(
	id int primary key auto_increment,
	name varchar(20),
	class_id int, 
	foreign key(class_id) references class(id) -- 作为外键一定要和关联主键的数据类型保持一致
);

-- 插入数据
insert into class (name,stu_nums)
values ('班级一',10),('班级二',12),('班级三',13),('班级四',14),('班级五',15);
insert into student (name,class_id) 
values ('Tom',1),('Jim',1),('Jake',2),('Mike',3),('Jane',4);



-- 增加外键
alter table student add constraint student_fk_class foreign key(class_id) references class(id);
-- 删除外键
alter table student drop foreign key student_ibfk_1;

INNODB支持的on语句

• 外键约束对子表的含义：如果在父表中找不到候选键，则不允许在子表进行insert/update
• 外键约束对父表的含义：在父表上进行update/delete以更新或删除在子表中的一条或多条对应数据，父表的行为取决于：在定义子表的外键指定的on update/on delete语句

/*
cascade：级联模式
district：严格模式
set null：置空模式
*/

-- ON DELETE CASCADE 级联删除：父表记录被删除，子表对应的记录自动被删除
foreign key(class_id) references class(id) on delete cascade;
-- ON DELETE SET NULL 置空：父表update/delete记录时，子表设为null
foreign key(class_id) references class(id) on set null;
-- RESTRICT：拒绝对父表进行删除操作
-- NO ACTION：在mysql中同RESTRICT,如果子表中有匹配的记录,则不允许对父表对应候选键

多表查询

/*
创建2张表：员工表和部门表
并插入相关数据
*/
create table employee(
	emp_id int primary key auto_increment,
	emp_name varchar(20),
	age int,
	dept_id int
);

create table department(
	dept_id int primary key auto_increment,
	dept_name varchar(100)
);

insert into employee(emp_name,age,dept_id) values
 		('A',19,200),
        ('B',26,201),
        ('C',30,201),
        ('D',24,202),
        ('E',20,200),
        ('F',38,204);

insert into department values
      (200,'人事部'),
      (201,'技术部'),
      (202,'销售部'),
      (203,'财政部');		

笛卡尔积查询

-- 查询结果是m*n

mysql> select * from employee,department;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      1 | A        |   19 |     200 |     201 | 技术部    |
|      1 | A        |   19 |     200 |     202 | 销售部    |
|      1 | A        |   19 |     200 |     203 | 财政部    |
|      2 | B        |   26 |     201 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      2 | B        |   26 |     201 |     202 | 销售部    |
|      2 | B        |   26 |     201 |     203 | 财政部    |
|      3 | C        |   30 |     201 |     200 | 人事部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     202 | 销售部    |
|      3 | C        |   30 |     201 |     203 | 财政部    |
|      4 | D        |   24 |     202 |     200 | 人事部    |
|      4 | D        |   24 |     202 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      4 | D        |   24 |     202 |     203 | 财政部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|      5 | E        |   20 |     200 |     201 | 技术部    |
|      5 | E        |   20 |     200 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     203 | 财政部    |
|      6 | F        |   38 |     204 |     200 | 人事部    |
|      6 | F        |   38 |     204 |     201 | 技术部    |
|      6 | F        |   38 |     204 |     202 | 销售部    |
|      6 | F        |   38 |     204 |     203 | 财政部    |
+--------+----------+------+---------+---------+-----------+
24 rows in set (0.01 sec)

内连接

-- 查询两张表中都有的关联数据

mysql> select * from employee,department where employee.dept_id=department.dept_
id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
+--------+----------+------+---------+---------+-----------+
5 rows in set (0.00 sec)

外连接

-- 左外连接：在内连接的基础上增加左边有右边没有的结果

mysql> select * from employee left join department on employee.dept_id=departmen
t.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      6 | F        |   38 |     204 |    NULL | NULL      |
+--------+----------+------+---------+---------+-----------+
6 rows in set (0.00 sec)

-- 右外连接：在内连接的基础上增加右边有左边没有的结果

mysql> select * from employee right join department on employee.dept_id=departme
nt.dept_id;
+--------+----------+------+---------+---------+-----------+
| emp_id | emp_name | age  | dept_id | dept_id | dept_name |
+--------+----------+------+---------+---------+-----------+
|      1 | A        |   19 |     200 |     200 | 人事部    |
|      2 | B        |   26 |     201 |     201 | 技术部    |
|      3 | C        |   30 |     201 |     201 | 技术部    |
|      4 | D        |   24 |     202 |     202 | 销售部    |
|      5 | E        |   20 |     200 |     200 | 人事部    |
|   NULL | NULL     | NULL |    NULL |     203 | 财政部    |
+--------+----------+------+---------+---------+-----------+
6 rows in set (0.00 sec)

多条件查询

-- 查询员工大于25岁的信息
mysql> select employee.emp_name,department.dept_name
    -> from employee,department
    -> where employee.dept_id=department.dept_id and age>25;
+----------+-----------+
| emp_name | dept_name |
+----------+-----------+
| B        | 技术部    |
| C        | 技术部    |
+----------+-----------+
2 rows in set (0.00 sec)


-- 以内连接方式查询employee和department表，并以age字段升序显示
mysql> select employee.emp_id,employee.emp_name,employee.age,department.dept_name
    -> from employee,department
    -> where employee.dept_id=department.dept_id
    -> order by age asc;
+--------+----------+------+-----------+
| emp_id | emp_name | age  | dept_name |
+--------+----------+------+-----------+
|      1 | A        |   19 | 人事部    |
|      5 | E        |   20 | 人事部    |
|      4 | D        |   24 | 销售部    |
|      2 | B        |   26 | 技术部    |
|      3 | C        |   30 | 技术部    |
+--------+----------+------+-----------+
5 rows in set (0.00 sec)


-- 子查询：查询employee表，dept_id在department表中的所有信息
mysql> select * from employee
    -> where dept_id in (select dept_id from department);
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
+--------+----------+------+---------+
5 rows in set (0.00 sec)


-- 子查询
mysql> select * from employee
    -> where dept_id in (select dept_id from department where age>25);
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
+--------+----------+------+---------+
2 rows in set (0.00 sec)


-- 使用EXISTS关键字
-- EXISTS关字键字表示存在。在使用EXISTS关键字时，内层查询语句不返回查询的记录。
-- 而是返回一个真假值。Ture或False
-- 当返回Ture时，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
mysql> select * from employee
    -> WHERE EXISTS (SELECT dept_name from department where dept_id=205);
Empty set (0.34 sec)


mysql>   select * from employee
    ->   WHERE EXISTS (SELECT dept_name from department where dept_id=203);
+--------+----------+------+---------+
| emp_id | emp_name | age  | dept_id |
+--------+----------+------+---------+
|      1 | A        |   19 |     200 |
|      2 | B        |   26 |     201 |
|      3 | C        |   30 |     201 |
|      4 | D        |   24 |     202 |
|      5 | E        |   20 |     200 |
|      6 | F        |   38 |     204 |
+--------+----------+------+---------+
6 rows in set (0.00 sec)