mysql基础知识

mysql基础

数据库操作
stu employee 员工表   Department  部门
cmd位置：C:\Windows\System32  管理员身份运行
1.登陆  mysql -uroot -p
2.退出mysql： exit   quit

3.查询所有的数据库  SHOW DATABASES;
4.创建数据库  CREATE DATABASE 数据库名称;     
创建数据库(判断，如果不存在则创建)       CREATE DATABASE IF NOT EXISTS 数据库名称;

5.删除数据库 DROP DATABASE 数据库名称;
删除数据库(判断，如果存在则删除)   DROP DATABASE IF EXISTS 数据库名称;

6.使用数据库   USE 数据库名称;
7.查看当前使用的数据库  SELECT DATABASE();

表操作
1.查询当前数据库下所有表名称    SHOW TABLES;
2.查询表结构    DESC 表名称;
3.创建表   CREATE TABLE 表名 (
    字段名1  数据类型1,
    字段名2  数据类型2,
    …
    字段名n  数据类型n
);
如：
create table tb_user (
    id int,
    username varchar(20),
    password varchar(32)
);
create table student (
    id int,
    name varchar(10),
    gender char(1),
    birthday date,
    score double(5,2),
    email varchar(15),
    tel varchar(15),
    status tinyint
);
8.删除表  DROP TABLE 表名;
9.修改表名 ALTER TABLE 表名 RENAME TO 新的表名;
10.添加一列    ALTER TABLE 表名 ADD 列名 数据类型;
11.修改数据类型   ALTER TABLE 表名 MODIFY 列名 新数据类型;
12.修改列名和数据类型   ALTER TABLE 表名 CHANGE 列名 新列名 新数据类型;
13.删除列   ALTER TABLE 表名 DROP 列名;
navicat使用
DML
1.给指定列添加数据   INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…);
2.给全部列添加数  INSERT INTO 表名 VALUES(值1,值2,…);
3.批量添加数据    INSERT INTO 表名(列名1,列名2,…) VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
INSERT INTO 表名 VALUES(值1,值2,…),(值1,值2,…),(值1,值2,…)…;
4.修改表数据  UPDATE 表名 SET 列名1=值1,列名2=值2,… [WHERE 条件] ;
5.删除数据  DELETE FROM 表名 [WHERE 条件] ;
6.查询所有表结构 ：select  *  from   表名称  如：SELECT *FROM stu;

DQL
基础查询
1.查询多个字段    SELECT 字段列表 FROM 表名;
如：查询name、age两列     select name,age from stu;
   SELECT * FROM 表名; -- 查询所有数据   如：select * from stu;
2.去除重复记录     SELECT DISTINCT 字段列表 FROM 表名;   如：select distinct address from stu;
3.起别名   AS: AS 也可以省略     
如：查询姓名、数学成绩、英语成绩。并通过as给math和english起别名（as关键字可以省略）
  select name,math as 数学成绩,english as 英文成绩 from stu;
  select name,math 数学成绩,english 英文成绩 from stu;
 条件查询
 SELECT 字段列表 FROM 表名 WHERE 条件列表;
 1.between ... and的应用：
查询年龄大于等于20岁 并且 年龄 小于等于 30岁 的学员信息
select * from stu where age BETWEEN 20 and 30;
2.<>的应用：
查询年龄不等于18岁的学员信息 ： select * from stu where age <> 18;
3.in和or应用：
查询年龄等于18岁 或者 年龄等于20岁 或者 年龄等于22岁的学员信息
select * from stu where age = 18 or age = 20 or age = 22;
select * from stu where age in (18,20 ,22);
 4.is  或者 is not的应用
查询英语成绩为 null的学员信息
select * from stu where english is null;
查询英语成绩不为 null的学员信息
select * from stu where english is not null;
 模糊查询练习
模糊查询使用like关键字，可以使用通配符进行占位:
_ : 代表单个任意字符
% : 代表任意个数字符
如：查询名字中包含 '德' 的学员信息
select * from stu where name like '%德%';
查询姓'马'的学员信息
select * from stu where name like '马%';
查询第二个字是'花'的学员信息  
select * from stu where name like '_花%';
排序查询
SELECT 字段列表 FROM 表名 ORDER BY 排序字段名1 [排序方式1],排序字段名2 [排序方式2] …;
ASC ： 升序排列 **（默认值）
DESC ： 降序排列
如：查询学生信息，按照年龄升序排列（默认升序）
select * from stu order by age ;
查询学生信息，按照数学成绩降序排列
select * from stu order by math desc ;
查询学生信息，按照数学成绩降序排列，如果数学成绩一样，再按照英语成绩升序排列
select * from stu order by math desc , english asc ;
聚合函数  SELECT 聚合函数名(列名) FROM 表;
count(列名)             统计数量（一般选用不为null的列）  
统计班级一共有多少个学生
select count(id) from stu;
select count(english) from stu;
select count(*) from stu;      建议使用 count(*)
max(列名)               最大值
查询数学成绩的最高分
select max(math) from stu;
min(列名)                最小值
查询数学成绩的最低分
select min(math) from stu;
sum(列名)               求和
查询数学成绩的总分
select sum(math) from stu;
avg(列名)               平均值
查询数学成绩的平均分
select avg(math) from stu;
分组查询   难
SELECT 字段列表 FROM 表名 [WHERE 分组前条件限定] GROUP BY 分组字段名 [HAVING 分组后条件过滤];
> 注意：分组之后，查询的字段为聚合函数和分组字段，查询其他字段无任何意义
select name, sex, avg(math) from stu group by sex;  -- 这里查询name字段就没有任何意义

查询男同学和女同学各自的数学平均分
select sex, avg(math) from stu group by sex;
查询男同学和女同学各自的数学平均分，以及各自人数
select sex, avg(math),count(*) from stu group by sex;
查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组
select sex, avg(math),count(*) from stu where math > 70 group by sex;
查询男同学和女同学各自的数学平均分，以及各自人数，要求：分数低于70分的不参与分组，分组之后人数大于2个的
select sex, avg(math),count(*) from stu where math > 70 group by sex having count(*)  > 2;
where 和 having 区别：
执行时机不一样：where 是分组之前进行限定，不满足where条件，则不参与分组，而having是分组之后对结果进行过滤。
可判断的条件不一样：where 不能对聚合函数进行判断，having 可以。
分页查询
SELECT 字段列表 FROM 表名 LIMIT  起始索引 , 查询条目数;-----起始索引是从0开始
起始索引计算公式：
起始索引 = (当前页码 - 1) * 每页显示的条数   
 从0开始查询，从第1页开始查询数据   查询3条数据    （1-1）*3
select * from stu limit 0 , 3;
每页显示3条数据，查询第1页数据       （1-1）*3
select * from stu limit 0 , 3;
每页显示3条数据，查询第2页数据    （2-1）*3
select * from stu limit 3 , 3;
每页显示3条数据，查询第3页数据    （3-1）*3
select * from stu limit 6 , 3;
小结：
SELECT    字段列表
FROM   表名列表 
WHERE    条件列表
GROUP BY      分组字段
HAVING         分组后条件
ORDER BY      排序字段
LIMIT          分页限定

  mysql高级

-- 添加外键 dep_id,关联 dept 表的id主键   外键键在从表上
CONSTRAINT  fk_emp_dept（外键名称） FOREIGN KEY(dep_id)[哪个列是列外键 在这里是dep_id列是外键] REFERENCES dept(id) [关联主表的主键 在这里的主表是dept，主键是id]
-- 建完表后，添加外键
alter table emp add CONSTRAINT fk_emp_dept FOREIGN key(dep_id) REFERENCES dept(id);
一.约束
非空约束： 关键字是 NOT NULL
唯一约束：关键字是  UNIQUE
主键约束： 关键字是  PRIMARY KEY  要求非空且唯一
检查约束： 关键字是  CHECK    MySQL不支持检查约束。
默认约束： 关键字是   DEFAULT 
外键约束： 关键字是  FOREIGN KEY
二.表关系(一对多)
- 如：部门 和 员工
- 一个部门对应多个员工，一个员工对应一个部门。 
 在多的一方建立外键，指向一的一方的主键  如 员工表中添加一列外键（dep_id），指向于部门表的主键（id）
如：
                添加外键 dep_id,关联 dept 表的id主键
    CONSTRAINT fk_emp_dept FOREIGN KEY(dep_id) REFERENCESdept(id)
三.表关系(多对多)
多对多
- 如：商品 和 订单
- 一个商品对应多个订单，一个订单包含多个商品
建立第三张中间表，中间表至少包含两个外键，分别关联两方主键
-- 建完表后，添加外键
alter table tb_order_goods(第三张表) add CONSTRAINT fk_order_id （外键名称）FOREIGN key(order_id)[哪个列是列外键 在这里是order_id列是外键] REFERENCES tb_order(id);[关联主表的主键 在这里的主表是tb_order，主键是id]
alter table tb_order_goods(第三张表) add CONSTRAINT fk_goods_id（外键名称） FOREIGN key(goods_id) [哪个列是列外键 在这里是goods_id列是外键] REFERENCES tb_goods(id);[关联主表的主键 在这里的主表是tb_goods，主键是id]
四.表关系(一对一)
一对一
- 如：用户 和 用户详情
- 一对一关系多用于表拆分，将一个实体中经常使用的字段放一张表，不经常使用的字段放另一张表，用于提升查询性能
在任意一方加入外键，关联另一方主键，并且设置外键为唯一(UNIQUE)