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;表数据操作（单表查询）
-- 创建一个成绩表
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;sql语句执行顺序
- from
- where
- select
- group by
- having
- order by
-- 不成功
select js as js分数 from grade where js分数 >70;
-- 成功
select js as js分数 from grade having js分数 >70;limit
-- 查询前3条数据
select * from grade limit 3;
-- 跳过1条，查询3条数据
select * from grade limit 1,3;正则表达式
-- 查询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)
浙公网安备 33010602011771号