mysql---安装、常用命令、建表、查询、主键、外键、表关系、约束、group by、order by、where

@

目录

  1. mysql的安装

    官网地址：https://www.mysql.com

    安装 community server 5.6 64位，一定是这个版本，高版本的可能下边的配置无效。

    防止你们找不到直接点击这个链接：https://dev.mysql.com/downloads/installer/

    下载好后直接解压到D盘根目录，建议D盘根目录，如果不是，下边代码的设置mysql的安装目录和设置mysql数据库的数据的存放目录中的路径需要自己手动改变一下。

    把里边的一个my-default.ini的文件复制到pycharm，重命名为my.ini然后打开，删除所有里边的内容，把下边的这段代码写到里边，内容什么都不要变，除非你没有解压到D盘根目录。

    [mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[mysqld]
#设置3306端口
port = 3306
# 设置mysql的安装目录
basedir=D:\mysql-5.6.50-winx64
# 设置mysql数据库的数据的存放目录
datadir=D:\mysql-5.6.50-winx64\data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

    ctrl+s保存后，把my.ini文件copy到D:\mysql-5.6.50-winx64下边。

    然后添加系统环境变量：此电脑-->属性-->高级系统变量-->环境变量。

    双击系统变量里的path,点击新建，把D:\mysql-5.6.50-winx64\bin添加上，然后一路点确定就好。

    1

    前边都完成后，搜索框找到命令行并以管理员的身份运行。

    执行命令安装MySQL服务 ，MySQL服务就被注册到你的操作系统中：

    mysqld install

    看到安装完成，然后启动MySQL服务：

    net start mysql

    看到服务已启动，证明安装成功，否则一定是你前边什么步骤搞错了。

  2. mysql

    mysqld install   # 安装MySQL服务  MySQL服务就被注册到你的操作系统中
net start mysql  # 启动MySQL服务
net stop mysql   # 关闭MySQL服务
mysql -uroot -p  # 启动客户端连接server,默认无密码.就是管理员身份登录
set password = password('123'); # 给当前账户设置密码
create user 'xiaoli'@'10.14.206.%' identified by '123'; # 创建一个其他用户
# create user '用户名'@'IP地址/域名' identified by '密码';
select user();  # 查看当前登录用户
create database 数据库名; # 创建一个数据库
show databases  # 查看所有的数据库
grant all/select/insert on 数据库名 to 用户名;  # 给一个用户授权
use 数据库名  # 切换到这个库下
create table student(name char(12), age int);  # 创建一张表
desc student;  # 查看表结构
insert into student values ('alex', 78);  # 插入数据
insert into student values ('alex', 78), ('wang', 12);
insert into student (name, age) values ('alex', 78);
select * from student;  # 查询数据
update student set age=85 where name='alex';  # 修改数据
delete from student where name='alex';  # 删除数据
show variables like '%engin%';  # 查看默认存储引擎

  3. 存储引擎

    存储引擎:存储数据的方式
    1.数据存储在硬盘上,数据持久化
    一张表
    数据
    表的结构
    索引(查询的时候用的一个目录结构)
    数据和索引存在一起 2个文件
    Innodb存储引擎: 支持事务 行级锁 表级锁 支持外键 mysql5.6之后默认的
    数据和索引不存在一起 3个文件
    Myisam存储yinq 表级锁 mysql5.5之前默认的
    2.数据存储在内存中,数据断电消失.
    只存储表结构
    Memory存储引擎
    面试题:
    你了解mysql的存储引擎吗?
    了解一些.
    你的项目用了什么存储引擎,为什么?
    Innodb存储引擎
    多个用户操作的过程中对同一张表的数据同时做修改, 行级锁
    innodb支持行级锁,所以我们使用了改存储引擎
    未来适应程序未来的扩展性,扩展新功能的时候可能会用到.
    项目中有两张表 之间的外键关系 怕出问题 做了外键约束

    查看mysql是否支持各种引擎
    show engines;

  4. mysql中的数据类型

    数字
    整数
    TINTYINT 1字节 默认有符号 加unsigned约束 无符号
    SMALLINT 2字节
    MEDIUMINT 3字节
    INT/INTEGER 4字节
    BIGINT 8字节
    浮点型
    FLOAT 4字节
    DOUBLE 8字节
    DECIMAL double(65, 30) 默认10位整数

    create table t1(
    id int,
    age tinyint unsigned
);
insert into t1 values (1, 89);
insert into t1 values (-2, 93);

create table t2(
    f1 float(5,2), # 保留两位小数,并四舍五入
    f2 float,
    f4 double(5,2)
    f3 double
);

    时间
    datetime 20201022165023
    date 20201022
    time 165023
    timestamp
    year 1901/2155

    create table t4 (
    dt datetime not null default current timestamp on update current timestamp, # 非空约束,默认为当前时间并且更新时自动更新为当前时间
    y year,
    t time,
    ts timestamp  # 默认为当前的时间
);
insert into t4 values(now(), now(), now(), now());

    字符串
    默认都是1个字节长度
    char 0-255字节 定长存储 'alex' 存 'alex ' 定长
    varchar 0-65535字节 边长存储 'alex' 存'alex4'
    适合使用cahr
    身份证号 手机号码 qq号 username password 银行卡号
    适合使用varchar
    评论 朋友圈 微博

    enum/set
    enum 单选行为
    set 多选行为

    create table t6(
    c1 char,   # char 可以不写长度
    v1 varchar(1),  # varchar 必须写
    c2 char(5),
    v2 varchar(10)
);

create table t8(
    id int,
    name char(18),
    gender enum('male', 'female')
);
insert int t8 values(1, 'alex', 'male');

create table t9(
    id int,
    name char(18),
    hobby set('抽烟', '喝酒', '烫头')
);
insert into t9 values(1, '太白', '抽烟,喝酒,打游戏'); # 不在范围内的会自动去除

  5. 完整性约束

    ​ 约束一个字段不能为空 not null
    ​ 唯一约束 unique 值不能重复,连续两个null可以
    ​ 无符号的 int unsigned
    ​ 默认值是什么 default
    ​ 自增 auto_increment 只能对数据有效 自带非空约束 至少时unique约束之后才能使用
    ​ 主键 primary key
    ​ 外键 foreign key

    create table t10(
    id int unsigned not mull unique,  # 非空不生效的话,修改配置文件
    name char(18) not null,
    gender enum('male', 'female') not null default 'male'
);

create table t11(
    id int,
    server_name char(12),
    ip char(10),
    port char(10),
    unique(ip, port)  # 联合唯一 unique  两列不能一i重复  单个的可以重复
);
create table t12(
    id int not null unique,  # 第一个被定义为非空+唯一的那一列会成为这张表的primary key主键  主键时唯一的
    username not null unique
)
create table t13(
    id int not null unique,
    username primary key  # 也可以自定义
);

create table t14(
    id int,
    server_name char(12),
    ip char(10),
    port char(10),
    primary key(ip, port)  # 联合主键
);

create table t15(
    id int primary key auto_increment,
    name char(12)
);
insert into t15(name) values('alex');
insert into t15(name) values('小明');

# 外键
# 班级表
create table class(
    cid int primary key auto_increment,
    cname char(12) not null,
    start date
);
# 学生表
create table student(
    id int primary key auto_increment,
    name char(12) not null,
    gender enum('male', 'female') default 'male',
    class_id int,
    foreign key(class_id) references class(cid)
);
insert into class values(1, 'py231','2019-3-19');  # 此时必须先写class
insert into student values(1, '小明', 'male',1);

  6. 修改表

    alter table 表名;
alter table t2 add age int notnull;  # 自动添加到最后
alter table t2 add age int notnull after id; # 添加到id后
alter table t2 modify name char(15) not null;  # 修改类型,不能字段改名
alter table t2 change name sname char(15) not null;  # 修改类型,不能字段改名
# 删除表
drop table 表名;

  7. 表与表之间的关系

    多对一
   学生  班级
      多个学生是一个班级的
      学生表有一个外键 关联班级表
   书籍    作者
    多本书可以都是一个作者写的
    书记表有一个外键  关联作者表
多对多
   出现第三张表 这张表关联两个外键
一对一
    客户   学生
    学生表建立外键  两个字段必须都是unique

    练习：

    根据表结构合理设计表与表之间的主外键关系和约束，并完成表结构的创建。

    img

    注意建表的顺寻和向表中插入数据的顺序，需要建立外键的表在后边建立外键。插入的时候也是。

    create table teacher(
    tid int primary key auto_increment,
    tname char(10) not null
);
create table course(
    cid int unique auto_increment,
    cname char(10) not null,
    teacher_id int,
    foreign key(teacher_id) references teacher(tid)
);
create table class(
    cid int unique auto_increment,
    caption char(10) not null
);
create table student(
    sid int unique auto_increment,
    sname char(10) not null,
    gender enum('男', '女') not null default'男',
    class_id int,
    foreign key(class_id) references class(cid)
);
create table score(
    sid int unique auto_increment,
    student_id int,
    course_id int,
    number int,
    foreign key(student_id) references student(sid),
    foreign key(course_id) references course(cid)
);
insert into teacher(tname) values('波多野结衣');
insert into teacher(tname) values('苍井空');
insert into teacher(tname) values('俞布麻衣');
select * from teacher;
# +-----+-----------------+
# | tid | tname           |
# +-----+-----------------+
# |   1 | 波多野结衣      |
# |   2 | 苍井空          |
# |   3 | 俞布麻衣        |
# +-----+-----------------+
# 3 rows in set (0.00 sec)
insert into course values(1, '生物', 1);
insert into course values(2, '体育', 1);
insert into course values(3, '物理', 2);
select * from course;
# +------+--------+------------+
# | cid  | cname  | teacher_id |
# +------+--------+------------+
# |    1 | 生物   |          1 |
# |    2 | 体育   |          1 |
# |    3 | 物理   |          2 |
# +------+--------+------------+
# 3 rows in set (0.00 sec)
insert into class(caption) values('三年二班');
insert into class(caption) values('一年三班');
insert into class(caption) values('三年一班');
select * from class;
# +-----+--------------+
# | cid | caption      |
# +-----+--------------+
# |   1 | 三年二班     |
# |   2 | 一年三班     |
# |   3 | 三年一班     |
# +-----+--------------+
# 3 rows in set (0.00 sec)
insert into student(sname,gender,class_id) values('钢蛋','女',1);
insert into student(sname,gender,class_id) values('铁锤','女',1);
insert into student(sname,class_id) values('山炮',2);
select * from student;
# +-----+--------+--------+----------+
# | sid | sname  | gender | class_id |
# +-----+--------+--------+----------+
# |   1 | 钢蛋   | 女     |        1 |
# |   2 | 铁锤   | 女     |        1 |
# |   3 | 山炮   | 男     |        2 |
# +-----+--------+--------+----------+
# 3 rows in set (0.00 sec)
insert into score(student_id, course_id, number) values(1,1,60);
insert into score(student_id, course_id, number) values(1,2,59);
insert into score(student_id, course_id, number) values(2,2,100);
select * from score;
# +-----+------------+-----------+--------+
# | sid | student_id | course_id | number |
# +-----+------------+-----------+--------+
# |   1 |          1 |         1 |     60 |
# |   2 |          1 |         2 |     59 |
# |   3 |          2 |         2 |    100 |
# +-----+------------+-----------+--------+
# 3 rows in set (0.00 sec)

  8. 数据库的增删改和select的操作

    create table t1(
    id int primary key auto_increment,
    username char(12) not null,
    sex enum('male','female') default 'male',
    hobby set('抽烟', '喝酒','烫头') not null
);
# 增加:
insert into t1 values(1, '大壮', 'male','抽烟,烫头');
insert into t1 values(2, 'b哥', 'male','烫头'),(3, 'alex', 'male','抽烟');
insert into t1(username, hobby) values('小李', '喝酒');
create table t2(id int, username char(12));
insert into t2 select id, username from t1;  # 注意没有values
# 删除
delete from t2;  # 清空这张表,表结构还在.不会清空自增的偏移量.就是设置了auto_increment 你添加到了3 delete之后再添加会从4开始
truncate table t2;  # 会把偏移量也清空
drop table t2;  # 表结构都没了,这张表就没了
delete from t1 where id = 3;  # 删除某一条数据
# 修改
update t1 set hobby = '喝酒,烫头' where id = 1;
# 查询
# 数据准备
company.employee
    员工id      id                  int
    姓名        emp_name            varchar
    性别        sex                 enum
    年龄        age                 int
    入职日期     hire_date           date
    岗位        post                varchar
    职位描述     post_comment        varchar
    薪水        salary              double
    办公室       office              int
    部门编号     depart_id           int



#创建表
create table employee(
id int not null unique auto_increment,
emp_name varchar(20) not null,
sex enum('male','female') not null default 'male', #大部分是男的
age int(3) unsigned not null default 28,
hire_date date not null,
post varchar(50),
post_comment varchar(100),
salary double(15,2),
office int, #一个部门一个屋子
depart_id int
);

#插入记录
#三个部门：教学，销售，运营
insert into employee(emp_name,sex,age,hire_date,post,salary,office,depart_id) values
('egon','male',18,'20170301','老男孩驻沙河办事处外交大使',7300.33,401,1), #以下是教学部
('alex','male',78,'20150302','teacher',1000000.31,401,1),
('wupeiqi','male',81,'20130305','teacher',8300,401,1),
('yuanhao','male',73,'20140701','teacher',3500,401,1),
('liwenzhou','male',28,'20121101','teacher',2100,401,1),
('jingliyang','female',18,'20110211','teacher',9000,401,1),
('jinxin','male',18,'19000301','teacher',30000,401,1),
('成龙','male',48,'20101111','teacher',10000,401,1),

('歪歪','female',48,'20150311','sale',3000.13,402,2),#以下是销售部门
('丫丫','female',38,'20101101','sale',2000.35,402,2),
('丁丁','female',18,'20110312','sale',1000.37,402,2),
('星星','female',18,'20160513','sale',3000.29,402,2),
('格格','female',28,'20170127','sale',4000.33,402,2),

('张野','male',28,'20160311','operation',10000.13,403,3), #以下是运营部门
('程咬金','male',18,'19970312','operation',20000,403,3),
('程咬银','female',18,'20130311','operation',19000,403,3),
('程咬铜','male',18,'20150411','operation',18000,403,3),
('程咬铁','female',18,'20140512','operation',17000,403,3)
;


# 查询结果对字段重命名
# select 字段 新名字,... from 表;
# select 字段 as 新名字, ... from 表;
select id i,emp_name name from employee;
# +----+------------+
# | i  | name       |
# +----+------------+
# |  1 | egon       |
# |  2 | alex       |
# |  3 | wupeiqi    |
# |  4 | yuanhao    |
# |  5 | liwenzhou  |
# |  6 | jingliyang |
# |  7 | jinxin     |
# |  8 | 成龙       |
# |  9 | 歪歪       |
# | 10 | 丫丫       |
# | 11 | 丁丁       |
# | 12 | 星星       |
# | 13 | 格格       |
# | 14 | 张野       |
# | 15 | 程咬金     |
# | 16 | 程咬银     |
# | 17 | 程咬铜     |
# | 18 | 程咬铁     |
# +----+------------+
# 18 rows in set (0.00 sec)


# 避免重复 distinct
select post from employee;
# +-----------------------------------------+
# | post                                    |
# +-----------------------------------------+
# | 老男孩驻沙河办事处外交大使              |
# | teacher                                 |
# | teacher                                 |
# | teacher                                 |
# | teacher                                 |
# | teacher                                 |
# | teacher                                 |
# | teacher                                 |
# | sale                                    |
# | sale                                    |
# | sale                                    |
# | sale                                    |
# | sale                                    |
# | operation                               |
# | operation                               |
# | operation                               |
# | operation                               |
# | operation                               |
# +-----------------------------------------+
# 18 rows in set (0.00 sec)
select distinct post from employee;
# +-----------------------------------------+
# | post                                    |
# +-----------------------------------------+
# | 老男孩驻沙河办事处外交大使              |
# | teacher                                 |
# | sale                                    |
# | operation                               |
# +-----------------------------------------+
# 4 rows in set (0.01 sec)


# 联合去重,age和sex都不同的留下
select distinct age,sex from employee;
# mysql> select age,sex from employee;
# +-----+--------+
# | age | sex    |
# +-----+--------+
# |  18 | male   |
# |  78 | male   |
# |  81 | male   |
# |  73 | male   |
# |  28 | male   |
# |  18 | female |
# |  18 | male   |
# |  48 | male   |
# |  48 | female |
# |  38 | female |
# |  18 | female |
# |  18 | female |
# |  28 | female |
# |  28 | male   |
# |  18 | male   |
# |  18 | female |
# |  18 | male   |
# |  18 | female |
# +-----+--------+
# 18 rows in set (0.00 sec)
#
# mysql> select distinct age,sex from employee;
# +-----+--------+
# | age | sex    |
# +-----+--------+
# |  18 | male   |
# |  78 | male   |
# |  81 | male   |
# |  73 | male   |
# |  28 | male   |
# |  18 | female |
# |  48 | male   |
# |  48 | female |
# |  38 | female |
# |  28 | female |
# +-----+--------+
# 10 rows in set (0.00 sec)


# 四则运算
select emp_name, salary*12 year_salary from employee;  # 计算年薪
# mysql> select emp_name, salary*12 year_salary from employee;
# +------------+-------------+
# | emp_name   | year_salary |
# +------------+-------------+
# | egon       |    87603.96 |
# | alex       | 12000003.72 |
# | wupeiqi    |    99600.00 |
# | yuanhao    |    42000.00 |
# | liwenzhou  |    25200.00 |
# | jingliyang |   108000.00 |
# | jinxin     |   360000.00 |
# | 成龙       |   120000.00 |
# | 歪歪       |    36001.56 |
# | 丫丫       |    24004.20 |
# | 丁丁       |    12004.44 |
# | 星星       |    36003.48 |
# | 格格       |    48003.96 |
# | 张野       |   120001.56 |
# | 程咬金     |   240000.00 |
# | 程咬银     |   228000.00 |
# | 程咬铜     |   216000.00 |
# | 程咬铁     |   204000.00 |
# +------------+-------------+
# 18 rows in set (0.00 sec)


# concat()  拼接
select concat(emp_name,':',salary) info from employee;
# +-----------------------------+
# | info                        |
# +-----------------------------+
# | egon:7300.33                |
# | alex:1000000.31             |
# | wupeiqi:8300.00             |
# | yuanhao:3500.00             |
# | liwenzhou:2100.00           |
# | jingliyang:9000.00          |
# | jinxin:30000.00             |
# | 成龙:10000.00               |
# | 歪歪:3000.13                |
# | 丫丫:2000.35                |
# | 丁丁:1000.37                |
# | 星星:3000.29                |
# | 格格:4000.33                |
# | 张野:10000.13               |
# | 程咬金:20000.00             |
# | 程咬银:19000.00             |
# | 程咬铜:18000.00             |
# | 程咬铁:17000.00             |
# +-----------------------------+
# 18 rows in set (0.01 sec)

# concat_ws('|', 'alex', '74');  第一个是分隔符
# 练习
# 1 查出所有员工的名字，薪资,格式为
#     <名字:egon>    <薪资:3000>
select concat('<','名字',emp_name,'>'), concat('<','薪资',salary,'>') from employee;
# 2 查出所有的岗位（去掉重复）
select distinct post from employee;
# 3 查出所有员工名字，以及他们的年薪,年薪的字段名为annual_year
select emp_name,salary*12 annual_salary from employee;


# where约束  筛选所有符合条件的行
# where可以使用:
# 1.比较运算符:> < = >= <= !=
# 2.范围:
#     between and   [10000, 20000]  在之间的所有的
#     in  in [10000, 20000]  在里面的选一个
# 3.like 'e%'   通配符
#     %表示任意多个
#     _表示一个
# 4.逻辑运算符:and or not
select * from employee where sex = 'male';
select * from employee where salary >= 10000;
select * from employee where salary between 10000 and 20000;  # [10000, 20000]

# 练习
# . 查看岗位是teacher的员工姓名、年龄
select emp_name, age from employee where post = 'teacher';
# . 查看岗位是teacher且年龄大于30岁的员工姓名、年龄
select emp_name, age from employee where post = 'teacher' and age > 30;
# . 查看岗位是teacher且薪资在9000-10000范围内的员工姓名、年龄、薪资
select emp_name, age, salary from employee where post = 'teacher' and salary between 9000 and 10000;
# . 查看岗位描述不为NULL的员工信息
select * from employee where post_comment is not null; # 判断NUL必须用is
# . 查看岗位是teacher且薪资是10000或9000或30000的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post = 'teacher' and salary in (10000,9000,30000);
# . 查看岗位是teacher且薪资不是10000或9000或30000的员工姓名、年龄、薪资
select emp_name,age,salary from employee where post = 'teacher' and salary not in (10000,9000,30000)
# . 查看岗位是teacher且名字是jin开头的员工姓名、年薪
select emp_name,salary*12 from employee where post = 'teacher' and emp_name like 'jin%';

# 分组聚合 group by  count  max min sum avg  求出的值之和这个组对应
select sex,count(id) from employee group by sex;
# +--------+-----------+
# | sex    | count(id) |
# +--------+-----------+
# | male   |        10 |
# | female |         8 |
# +--------+-----------+
# 2 rows in set (0.02 sec)
# 练习
# . 查询岗位名以及岗位包含的所有员工名字
select post,group_concat(emp_name) from employee group by post;
# +-----------------------------------------+---------------------------------------------------------+
# | post                                    | group_concat(emp_name)                                  |
# +-----------------------------------------+---------------------------------------------------------+
# | operation                               | 程咬铁,程咬铜,程咬银,程咬金,张野                        |
# | sale                                    | 格格,星星,丁丁,丫丫,歪歪                                |
# | teacher                                 | 成龙,jinxin,jingliyang,liwenzhou,yuanhao,wupeiqi,alex   |
# | 老男孩驻沙河办事处外交大使              | egon                                                    |
# +-----------------------------------------+---------------------------------------------------------+
# 4 rows in set (0.00 sec)
# . 查询岗位名以及各岗位内包含的员工个数
select post,count(id) from employee group by post;
# . 查询公司内男员工和女员工的个数
select sex,count(id) from employee group by sex;
# . 查询岗位名以及各岗位的平均薪资
select post,avg(salary) from employee group by post;
# . 查询岗位名以及各岗位的最高薪资
select post,max(salary) from employee group by post;
# . 查询岗位名以及各岗位的最低薪资
select post,min(salary) from employee group by post;
# . 查询男员工与男员工的平均薪资，女员工与女员工的平均薪资
select sex,avg(salary) from employee group by sex;

# having 过滤 在having条件中可以使用聚合函数,在where中不可以
select post,avg(salary) from employee group by post having avg(salary) > 10000;
# 练习
# 1. 查询各岗位内包含的员工个数小于2的岗位名、岗位内包含员工名字、个数
select post,group_concat(emp_name),count(id) from employee group by post having count(id)<2;
# 3. 查询各岗位平均薪资大于10000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary)>10000;
# 4. 查询各岗位平均薪资大于10000且小于20000的岗位名、平均工资
select post,avg(salary) from employee group by post having avg(salary) between 10000 and 20000;

# order by 根据什么排序  默认(asc)从小到大  desc 从大到小
select * from employee order by age desc;
select * from employee order by age,salary desc; 优先根据age从小到大排,在age相同的情况下,再根据薪资从大到小排

# limit m,n  从第m+1开始取n项,等同于 limit m offset n
select * from employee order by salary desc limit 2, 2;
posted @ 2020-10-23 20:13  笨鸟先飞啊  阅读(240)  评论(0)  编辑  收藏  举报