MySQL数据库

mysql

SQL：结构化查询语言

一、数据库产品

1.1 关系型数据库

• Oracle（甲骨文） - java
• MySQL - php
• SQL Server（微软） - asp.net（C#）
• DB2
• SQLite
• Access

1.2 非关系型数据库

• MongDB

二、数据库管理系统MySQL

• 数据库（database）
  
  • 表（table）
    
    • 记录

三、安装MySQL数据库

下载后安装，见word文档

四、MySQL命令

查看有哪些数据库：
show databases;
进入/切换数据库：
use 数据库名称;
查看当前数据库下有哪些表：
show tables;
查看表结构：
desc 表名;

五、cmd命令提示符窗口登录（需要配置环境变量）

连接本机：mysql -u 用户名 -p
连接其它服务器（需要开启授权远程登录）：mysql -h ip地址 -u 用户名 -p

六、授权远程登录

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '密码' WITH GRANT OPTION;
FLUSH PRIVILEGES;

七、MySQL客户端工具

Navicat for MySQL

7.1 软件下载安装

7.2 软件使用

八、数据库使用

建库、建表、建约束
增、删、改、查

SQL

• DDL：数据定义语言：CREATE、ALTER
• DCL：数据控制语言
• DML：数据操作语言：INSERT、DELETE、UPDATE
• DQL：数据查询语言：SELECT

8.1 建库

-- db_name：数据库名称
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 

8.2 建表

CREATE TABLE 表名 (
    列名1  数据类型 (大小) 属性1  属性2,
    列名2  数据类型 (大小) 属性1  属性2,
    ...
);

例子：

create table user (
    id int primary key auto_increment,
    name varchar (30) not null,
    password varchar(30) not null
);
-- primary key =>  主键
-- auto_increment => 自动增长
-- not null  =>  非空

8.3 增

INSERT INTO 表名 (列1, 列2, 列3, ...) VALUES (值1, 值2, 值3, ...)；
-- 如果值是字符串类型的，那么要加单引号引起来

例子：

insert into user (id, name, password) values (2, 'root', '1234');
-- 因为id是自动增长列，所有可以写default
insert into user (id, name, password) values (default, 'usr', '123456');
insert into user (name, password) values ('sa', 'sa');
-- 当所有的列都要插入数据时，可以省略列名；注：值的顺序一定要和表中列名顺序一致
insert into user values (default, 'sb', 'asdfg');

8.4 删除

DELETE FROM 表名 WHERE 条件;

例子

delete from user where name='sb';

8.5 修改

UPDATE 表名 SET 列1=值1, 列2=值2,... WHERE 条件;

例子

-- 修改root的密码为duang
UPDATE user SET password='duang' WHERE name='root';
-- 修改id为6的用户名为：admin  密码设置为：hello
update user set name='admin', password='hello' where id=6;

8.6 查询

8.6.1 基本查询

SELECT 列1, 列2, ... FROM 表名 WHERE 条件;

例子：

select id, name, password from user;
select name from user;
select * from user;
select * from user where name='admin';
select * from user where id >= 6;
-- 查询年龄在20-23岁的学生
select * from student where age >= 20 and age <= 23;
select * from student where age between 20 and 23;
-- 查询年龄在20岁以下及23岁以上的学生
select * from student where age < 20 or age > 23;
-- 查询年龄不是21岁的学生
select * from student where age != 21;
select * from student where age <> 21;

8.6.2 模糊查询

select 列名 from 表名 where 列名 like '%关键字%';

8.6.3 排序

select 列名 from 表名 order by 列名 [asc/desc];
-- asc：升序，默认值
-- desc：降序

8.6.4 限制个数

select * from 表名 limit x,y;
-- x : 开始位置，从0开始的
-- y : 个数

8.6.5 聚合函数

• AVG( ) : 平均值
• MAX( ) : 最大值
• MIN( ) : 最小值
• SUM( ) : 总和
• COUNT( ) : 个数

select 函数名(列名) from 表名;

8.6.6 分组查询

select 列名 from 表名  group by 列名 having 条件;

8.6.7 联表查询

九、案例

9.1 案例一

-- 创建数据库
CREATE DATABASE suibian DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 
-- 切换到suibian数据库
use suibian;
-- 建表
create table student(
    id int primary key auto_increment,
    name varchar(30) not null,
    age int,
    gender varchar(10) default '男',
    height decimal(4,2),
    class varchar(20)
); 
-- 向表中插入若干条数据
insert into student (id, name, age, gender, height, class) values (10000, '法海', 23, '男', 1.49, 'BP1701');
insert into student (name, age, height, class) values ('许仙', 18, 1.76, 'BP1702');
insert into student values (default, '拉登', 45, '男', 1.78, 'BT1701');
insert into student values (default, '九天', 65, '女', 1.38, 'BP1701');
insert into student values (default, '王母', 13, '女', 1.54, 'BT1701');
insert into student values (default, '宇笛', 34, '男', 1.67, 'BP1703');
insert into student values (default, '张三', 24, '男', 1.88, 'BP1701');
insert into student values (default, '李四', 61, '女', 1.91, 'BT1701');
insert into student values (default, '王五', 18, '男', 1.24, 'BP1702');
insert into student values (default, '赵六', 27, '女', 1.47, 'BT1701');
insert into student values (default, '佳琪', 33, '男', 1.69, 'BP1701');
insert into student values (default, '孙八', 42, '女', 1.62, 'BT1701');
insert into student values (default, '照旧', 19, '女', 1.73, 'BP1702');
insert into student values (default, '田七', 26, '男', 1.98, 'BP1701');
insert into student values (default, '立刻', 28, '男', 1.58, 'BT1701');
-- 查询所有数据
select * from student;
-- 修改 '佳琪'的性别为女
update student set gender='女' where name = '佳琪';
-- 删除 身高为1.24的学员
delete from student where height=1.24;
-- 查询所有的女生
select * from student where gender = '女';
-- 查询年龄大于20岁的学员
select * from student where age > 20;
-- 查询年龄在22~40之间的学员
select * from student where age >= 22 and age <= 40;
select * from student where age between 22 and 40;
-- 查询年龄大于40岁或身高小于1.7的学员
select * from student where age > 40 or height < 1.7; 
-- 查询非BP1701班的学生姓名和班级
select name, class from student where class != 'BP1701';
select name, class from student where class <> 'BP1701';
-- 查询所有姓李的学生
select * from student where name like '%李%';
-- 按照年龄升序排序
select * from student order by age asc;
-- 查询所有男生，并按照身高降序
select * from student where gender='男' order by height desc;
-- 获取身高最高的三个学生
select * from student order by height desc limit 0, 3;
select * from student order by height desc limit 3;
-- 获取最大的年龄是多少
select max(age) from student;
-- 获取BP1701班学生个数
select count(*) from student where class='BP1701';
-- 查看有几种班级
select class from student group by class;
-- 获取男女学生人数
select count(gender), gender from student group by gender;
-- 每个班级的人数
select class, count(class) from student group by class;
-- 获取班级人数大于2个的班级名称及人数
select class, count(class) from student  group by class having count(class) > 2;
-- --------------------------------------------------------------------------------------
-- 切换到companydb
use companydb;
show tables;
-- ---------------------------------------------
-- 查询员工的姓名和其所在部门名称
select emp.name as "员工姓名", dept.name as "部门名称" from emp, dept where emp.deptid = dept.id;
select emp.name "员工姓名", dept.name "部门名称" from emp, dept where emp.deptid = dept.id;
select emp.name, dept.name from emp, dept where emp.deptid = dept.id;
select emp.name, dept.name from emp join dept on emp.deptid = dept.id;
-- 查询开发部的所有员工姓名和薪资,按照薪资降序
select emp.name, emp.salary from emp, dept where emp.deptid = dept.id and dept.name = '开发部' order by salary desc;
select emp.name, emp.salary from emp join dept on emp.deptid = dept.id where dept.name = '开发部' order by salary desc;
select e.name, e.salary from emp e, dept d where e.deptid = d.id and d.name = '开发部' order by salary desc;
-- 子查询
select name, salary from emp where deptid = (select id from dept where name = '开发部');
-- 查询年龄最大的员工的信息
select * from emp, dept where emp.deptid = dept.id order by age desc limit 1;
select * from emp, dept where emp.deptid = dept.id and emp.age = (select max(age) from emp);
-- 查询开发部工资大于平均薪资的员工姓名和薪资
select emp.name, emp.salary from emp, dept where emp.deptid = dept.id and emp.salary > (select avg(salary) from emp) AND dept.name = '开发部';

9.2 案例二

-- 1. 创建表：
--  员工表（emp）：
--      编号：id 主键 自动增长
--      姓名：name 字符串，长度25
--      薪水：salary 整数
--      部门编号：deptid 整数，引用部门表的id
--      性别：gender 字符串，长度10
--      年龄：age 整数
--      
--  部门表（dept）：
--      编号：id 主键，自动增长
--      名称：name 字符串，长度20
--      描述：info 字符串，长度200
-- 建部门表
-- 建员工表
-- 1. 向员工和部门表中插入若干数据
-- 2. 查询所有员工信息
select * from emp, dept where emp.deptid = dept.id;
-- 3. 查询薪水在3000~5000的员工姓名和年龄
select name, age from emp where salary >= 3000 and salary <= 5000;
select name, age from emp where salary between 3000 and 5000;
-- from form   alter alert   test text  true ture
-- 4. 查询薪资小于3000和薪资大于8000的员工
select * from emp where salary < 3000 or salary > 8000;
-- 4. 查询年龄小于30岁的员工，并按照年龄降序排序
select * from emp where age < 30 order by age desc;
-- 5. 查询所有员工姓名和其所在的部门名称
select emp.name, dept.name from emp, dept where emp.deptid = dept.id;
select emp.name, dept.name from emp join dept on emp.deptid = dept.id;
select emp.name, dept.name from emp inner join dept on emp.deptid = dept.id;
select emp.name, dept.name from emp left join dept on emp.deptid = dept.id;
select emp.name, dept.name from emp right join dept on emp.deptid = dept.id;
-- 6. 查询“开发部”的所有员工姓名和年龄
select emp.name, emp.age from emp, dept where emp.deptid = dept.id and dept.name = '开发部';
select emp.name, emp.age from emp join dept on emp.deptid = dept.id where dept.name = '开发部';
select name, age from emp where deptid = (select id from dept where name = '开发部');
-- 7. 查询薪资最高的三位员工
select * from emp order by salary desc limit 0, 3;
select * from emp order by salary desc limit 3;
-- 8. 查询“人事部”的平均薪资
select avg(salary) from emp where deptid = (select id from dept where name = '人事部');
select avg(salary) from emp, dept where emp.deptid = dept.id and dept.name = '人事部';
-- 9. 查询薪资大于平均薪资的所有员工，并按照年龄升序排序
select * from emp where salary > (select avg(salary) from emp) order by age asc;
-- 10. 查询男女员工的数量
select gender "性别", count(*) "人数" from emp group by gender;