mysql基本知识

后端 > java_use myqsql
Created At :
Views 👀 :
  1. mysql基础
    1. 1. 安装
    2. 2.数据库连接
    3. 3.SQL语句分类
    4. 4. SQL 语句
    5. 5. 数据库操作
    6. 6. 表操作
  • mysql 进阶
    1. 1. SQL约束
      1. 1.2 删除主键约束
  • 2. SQL 语句
  • 创建表
    1. 表之间到关系
  • 分类表
    1. 多表关系
      1. 多表查询方式
      2. 子查询
      3. 1. 中文乱码
      4. 2.密码忘记
  • 注意点
    1. 1.面试题

    • mysql基础

    1. 安装

    参考 tools/server-tools/mysql.md 进行安装

    2.数据库连接

    – 格式1，本地连接:cmd> mysql –u用户名 –p密码

    mysql -uroot –proot

    – 格式2，远程连接:cmd> mysql –host=ip地址 –user=用户名

    –password=密码 2

    mysql –host=127.0.0.1 –user=root

    –password=root

    1. 还可以使用 navicat连接
    3.SQL语句分类

    数据库是不认识JAVA语言的，但是我们同样要与数据库交互，这时需要使用到数据库认识的语言【SQL 语句】，它是数据库的代码。
    结构化查询语言(Structured Query Language)简称SQL，是关系型数据库管理系统都需要遵循的规范。 不同的数据库生产厂商都支持SQL语句，但都有特有内容。

    • 数据定义语言:简称DDL(Data Definition Language)，用来定义数据库对象:数据库，表， 列等。关键字:create，alter，drop等

    • 数据控制语言:简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户。

    • 数据操作语言:简称DML(Data Manipulation Language)，用来对数据库中表的记录进行 更新。关键字:insert，delete，update等

    • 数据查询语言:简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字: select，from，where等

    4. SQL 语句
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    |  类型名称   |    说明  |
    |  ----  | ----  |
    |int     | 整数类型|

    |double   | 小数类型|

    |decimal(m,d)| 指定整数位与小数位⻓度的小数类型|

    |date   |  日期类型，格式为yyyy-MM-dd，包含年月日，不包含时分秒| 

    |datetime  | 日期类型，格式为 YYYY-MM-DD HH:MM:SS，包含年月日时分秒|

    |timestamp |  日期类型，时间戳|

    |varchar(M) | 文本类型， M为0~65535之间的整数|

    |char(M)    |文本类型, M为0~65535之间的整数|
    5. 数据库操作

    5.1创建

    /创建数据库/
    – 方式一: 使用指定的字符编码表,创建数据库.

    格式: create database 数据库名 character set 字符编码;

    – 方式二: 使用默认的字符编码表,创建数据库.

    格式: create database 数据库名;

    CREATE DATABASE mydb CHARACTER SET utf8; – 方式一

    CREATE DATABASE mydb2; – 方式二

    5.2查看数据库

    /查看数据库/
    – 查看所有的数据库. 格式: show databases; SHOW DATABASES;

    – 查看指定数据库的字符编码. 格式: show create database 数据库名; SHOW CREATE DATABASE mydb;
    SHOW CREATE DATABASE mydb2;

    5.3删除数据库

    /删除数据库/
    – 删除数据库. 格式: drop database 数据库名; DROP DATABASE mydb2;

    5.4 使用数据库

    /使用数据库/
    – 查看当前使用的数据库. 格式: select database(); SELECT DATABASE();

    – 设置当前使用的数据库. 格式: use 数据库名; USE mydb;

    6. 表操作

    6.1 创建

    /*
    创建表, 格式:

    create table 表名 (
    字段名 数据类型[⻓度] [约束], 字段名 数据类型[⻓度] [约束], …

    );
    注:[]中的内容是可选项
    */
    – 创建表student, 字段包括 编号id\ 姓名name\ 年龄age 

    1
    2
    3
    4
    CREATE TABLE student (
    id INT,
       NAME VARCHAR(100),
       age INT

    );

    – 创建表users, 字段包括 编号id\ 用户名username \ 密码

    1
    2
    3
    4
    create password CREATE TABLE users (
    id INT,
     username VARCHAR(100),
     PASSWORD VARCHAR(100)

    );

    6.2 查看

    – 查看所有表, 格式: show tables
    SHOW TABLES;
    – 查看指定表的建表结构, 格式: show create table 表名; SHOW CREATE TABLE users;

    6.3删除表

    – 删除表, 格式: drop table 表名; 2 DROPTABLEusers;

    6.4 修改表结构

    /*

    对表中的列进行修改
    \1. 添加新的列, 格式: alter table 表名 add 新列名 数据类型(⻓度);
    \2. 修改列的数据类型(⻓度), 格式: alter table 表名 modify 列名 修改后的数据类型(⻓ 度);

    1. 修改列的名称, 格式: alter table 表名 change 列名 新列名 新列名的数据类型(⻓度);
    2. 删除指定列, 格式: alter table 表名 drop 列名;

    */
    ALTER TABLE student ADD desc VARCHAR(100); – 添加新的列
    ALTER TABLE student MODIFY desc VARCHAR(50);– 修改列的数据类型(⻓度) ALTER TABLE student CHANGE desc description VARCHAR(100);– 修改列的名称 ALTER TABLE student DROP description;– 删除指定列

    /*

    对表进行修改

    1. 修改表的名称, 格式: rename table 表名 to 新表名;
    2. 修改表的字符编码, 格式: alter table 表名 character set 字符编码;

    */
    RENAME TABLE student TO stu; – 修改表的名称
    ALTER TABLE stu CHARACTER SET gbk; – 修改表的字符编码

    6.5 插入表

    /*

    插入表记录
    方式一, 对指定的字段插入值, 格式: insert into 表名(字段1, 字段2, …) values (值 1, 值2, …);
    方式二, 对所有字段插入值, 格式: insert into 表名 values(值1, 值2, …);
    */

    INSERT INTO student(id, NAME, age) VALUES(1, ‘1’, 24); INSERT INTO student(NAME, age) VALUES(‘2’, 22);
    INSERT INTO student(id, NAME, age) VALUES(3, ‘3’, NULL);

    INSERT INTO student VALUES(4, ‘4’, 26); INSERT INTO student VALUES(5, ‘zhangsan’, 26),(6,’6’,27);

    6.5 更新表记录

    – 更新表记录, 格式: update 表名 set 字段1=值, 字段2=值… where 条件;

    UPDATE student SET NAME=’lili’, age=21 WHERE id=1;
    UPDATE student SET age=25 WHERE age=27;

    6.6 删除

    – 删除表记录, 格式: delete from 表名 where 条件;

    DELETE FROM student WHERE id=1;
    DELETE FROM student WHERE age IS NULL;

    mysql 进阶

    1. SQL约束

    • 什么是约束
      约束, 其实就是一种限制条件, 让你不能超出这个控制范围.

    而在数据库中的约束, 就是指 表中的数据内容 不能胡乱填写, 必须按照要求填写. 好保证数据的完整性与 安全性.

    – 准备数据
    CREATE TABLE persons (

    1
    2
    3
    4
    5
      pid int,
      lastname varchar(255),
      firstname varchar(255),
      address varchar(255)
    );

    insert into persons values(1, ‘星驰’,’周’,’香港’);

    insert into persons values(1, ‘德华’,’刘’,’香港’);

    insert into persons values(2, ‘德华’,’刘’,null);

    insert into persons values(null, ‘润发’,’周’,’香港’);

    ######1.1 主键约束

    RIMARY KEY 约束:

    主键必须包含唯一的值, 不能重复。
    主键列不能包含 NULL 值。 每个表都应该有一个主键，并且每个表只能有一个主键。

    添加主键约束

    • 方式一:创建表时，在字段描述处，声明指定字段为主键:

      格式: 字段名 数据类型[⻓度] PRIMARY KEY

    CREATETABLEpersons(
    pid int primary key, – 添加了主键约束 lastname varchar(255),
    firstname varchar(255),
    address varchar(255)

    );
    INSERTINTOpersonsVALUES(1,’星驰’,’周’,’香港’);
    INSERT INTO persons VALUES(1, ‘德华’,’刘’,’香港’); – 设置主键后, 插入失败, 值 重复
    INSERTINTOpersonsVALUES(2,’德华’,’刘’,NULL);
    INSERT INTO persons VALUES(NULL, ‘润发’,’周’,’香港’); – 设置主键后, 插入失败, 值不能为 NULL

    // 加个tab键 就变成 空心了

    • 方式二:创建表时，在constraint约束区域，声明指定字段为主键:
      • 格式: [constraint 名称] primary key (字段列表)
      • 关键字constraint可以省略，如果需要为主键命名，constraint不能省略，主键名称一般没 用。
      • 字段列表需要使用小括号括住，如果有多字段需要使用逗号分隔。声明两个以上字段为主 键，我们称为联合主键。
    1
    2
    CREATE TABLE persons (
      pid int,

    lastname varchar(255),
    firstname varchar(255),
    address varchar(255),
    constraint pk_persons primary key (pid) – 添加主键约束, 单一字段

    );

    CREATE TABLE persons (
    pid INT,

    lastname VARCHAR(255),
    firstname VARCHAR(255),
    address VARCHAR(255),
    CONSTRAINT pk_persons PRIMARY KEY (lastname, firstname) – 添加主键约束,

    多个字段, 我们称为联合主键。
    );
    INSERT INTO persons VALUES(1, ‘星驰’,’周’,’香港’);
    INSERT INTO persons VALUES(1, ‘德华’,’刘’,’香港’);
    INSERT INTO persons VALUES(2, ‘德华’,’刘’,NULL); – 插入失败

    • 方式三:创建表之后，通过修改表结构，声明指定字段为主键

      alter table persons add constraint pk_persons primary key (lastname, firstname); – 添加联合主键

    1.2 删除主键约束

    altertablepersonsdropprimarykey;

    2. SQL 语句

    创建表

    #创建商品表:
    create table product(

    pid int primary key, pname varchar(20), price double, category_id varchar(32)

    );
    INSERT INTO product(pid,pname,price,category_id) VALUES(1,’联 想’,5000,’c001’);
    INSERT INTO product(pid,pname,price,category_id) VALUES(2,’海 尔’,3000,’c001’);
    INSERT INTO product(pid,pname,price,category_id) VALUES(3,’雷 神’,5000,’c001’);
    INSERT INTO product(pid,pname,price,category_id) VALUES(4,’JACK JONES’,800,’c002’);

    ####简单查询

    SELECT * from product;
    – 查询商品名和商品价格.
    select pname,price from product;
    – 查询价格,去掉重复值.
    select DISTINCT price from product;
    – 查询结果是表达式(运算查询):将所有商品的价格+10元进行显示. select pname,price+10 from product;
    – 别名查询.使用的关键字是as(as可以省略的).列别名
    select pname,price+10 as ‘价格’ from product;
    select pname,price+10 ‘价格’ from product;
    – 别名查询.使用的关键字是as(as可以省略的).表别名
    select * from product as p;
    select * from product p;

    ####条件查询

    #查询商品名称为“花花公子”的商品所有信息:
    select * from product where pname=’花花公子’; #查询价格为800商品
    select * from product where price=800; #查询价格不是800的所有商品
    select * from product where price<>800;
    select * from product where price!=800; – mysql特有的符号 #查询商品价格大于60元的所有商品信息
    select * from product where price>60;

    #查询商品价格在200到1000之间所有商品
    select * from product where price>=200 and price<=1000; select * from product where price between 200 and 1000; #查询商品价格是200或800的所有商品
    select * from product where price=200 or price=800; select * from product where price in(200,800);
    # LIKe 中的 %代表匹配任意⻓度的任意字符; _代表匹配一个任意字符 #查询商品名称含有’霸’字的所有商品
    select * from product where pname like ‘%霸%’; #查询商品名称以’香’开头的所有商品
    select * from product where pname like ‘香%’; #查询商品名称第二个字为’想’的所有商品
    select * from product where pname like ‘_想%’; #商品没有分类id的商品
    select * from product where category_id is NULL; #查询有分类id的商品
    select * from product where category_id is NOT NULL;

    ####排序查询

    #使用价格排序(降序)
    select * from product order by price desc; #在价格排序(降序)的基础上，以分类排序(降序)
    select * from product order by price desc,category_id desc; #显示商品的价格(去重复)，并排序(降序)
    select DISTINCT price from product order by price desc;

    ####聚合查询

    #查询商品的总条数
    select count(*) from product; – 不推荐 select count(pid) from product;
    select count(category_id) from product;

    #查询价格大于200商品的总条数
    select count(*) from product where price>200;

    #查询分类为’c001’的所有商品的价格总和
    select sum(price) from product where category_id=’c001’;

    #查询分类为’c002’所有商品的平均价格
    select avg(price) from product where category_id=’c002’;

    #查询商品的最大价格和最小价格
    select max(price),min(price) from product;

    ####分组查询

    1. #统计各个分类商品的个数selectcategory_id,count(*)fromproductgroupbycategory_id;

    #统计各个分类商品的个数,且只显示个数大于1的信息

    SELECTcategory_id,count(*)fromproduct GROUPBYcategory_idHAVING

    count(*)>1;

    #统计价格>200元的 各个分类商品的个数,且只显示个数大于1的信息

    select category_id,count() from produc twhere price>200 group by category_id HAVING count()>1;

    ####条数限制查询

    分⻚查询格式:
    SELECT * FROM 表名 LIMIT startRow,pageSize;

    例如:

    select * from products limit 0,5; #第一⻚，每⻚显示5条。
    select * from products limit 5,5; #第二⻚，每⻚显示5条。
    select * from products limit 10,5; #第三⻚，每⻚显示5条。
    select * from products limit startRow,5; #第curPage⻚，每⻚显示5条, startRow 的值如何计算呢?

    – 后台计算出⻚码、⻚数(⻚大小)
    – 分⻚需要的相关数据结果分析如下,
    – 注意:下面是伪代码不用于执行
    int curPage = 2; – 当前⻚数
    int pageSize = 5; – 每⻚显示数量
    int startRow = (curPage - 1) * pageSize; – 当前⻚, 记录开始的位置(行数)计算 int totalSize = select count(*) from products; – 记录总数量
    int totalPage = Math.ceil(totalSize * 1.0 / pageSize); – 总⻚数

    ####多表查询

    表之间到关系

    • 一对一关系:

      • 在实际的开发中应用不多.因为一对一可以创建成一张表.

      • 常⻅实例:身份证表与员工信息表

    • 一对多关系:

      • 常⻅实例:客户和订单，分类和商品，部⻔和员工, 省份和城市

    • 多对多关系:

      • 常⻅实例:学生和课程、用户和⻆色, 演员和电影, 商品和订单

    #####.外键约束

    有两张表“分类表”和“商品表”，为了表明商品属于哪个分类，通常情况下，我们将在商品表上 添加一列，用于存放分类cid的信息，此列称为:外键

    alter table 从表 add [constraint][外键名称] foreign key (从表外键字段名) references 主表 (主表的主键);

    //[外键名称]用于删除外键约束的，一般建议“_fk”结尾

    alter table 从表 drop foreign key 外键名称

    分类表

    CREATE TABLE category (

    cid VARCHAR(32) PRIMARY KEY ,
    cname VARCHAR(50)
    );

    #商品表
    CREATE TABLE products(

    pid VARCHAR(32) PRIMARY KEY ,
    pname VARCHAR(50),
    price INT,
    flag VARCHAR(2), #是否上架标记为:1表示上架、0表示下架 category_id VARCHAR(32)

    );

    #分类
    INSERT INTO category(cid,cname) VALUES(‘c001’,’家电’);
    INSERT INTO category(cid,cname) VALUES(‘c002’,’服饰’);
    INSERT INTO category(cid,cname) VALUES(‘c003’,’化妆品’);
    #商品
    INSERT INTO products(pid, pname,price,flag,category_id) VALUES(‘p001’,’联 想’,5000,’1’,’c001’);

    多表关系

    ######1.省和市

    • 方案1:多张表，一对多

    – 创建省份表
    create table province(

    pid int PRIMARY KEY,
    pname varchar(32), – 省份名称 description varchar(100) – 描述

    );

    – 创建城市表
    create table city (

    cid int PRIMARY KEY,
    cname varchar(32), – 城市名称
    description varchar(100), – 描述
    province_id int,
    CONSTRAINT city_province_fk foreign key(province_id) references province(pid) –创建外键
    );

    • 方案2:一张表，自关联一对多

    create table area (
    id int PRIMARY key AUTO_INCREMENT,
    name varchar(32),
    description varchar(100),
    parent_id int,
    CONSTRAINT area_area_fk FOREIGN KEY(parent_id) REFERENCES area(id)

    );

    INSERT into area values(null, ‘辽宁省’, ‘这是一个省份’, null); INSERT into area values(null, ‘大连市’, ‘这是一个城市’, 1); INSERT into area values(null, ‘沈阳市’, ‘这是一个城市’, 1); INSERT into area values(null, ‘河北省’, ‘这是一个省份’, null); INSERT into area values(null, ‘石家庄市’, ‘这是一个城市’, 4); INSERT into area values(null, ‘保定市’, ‘这是一个城市’, 4);

    ######2. 用户和角色

    – 用户表
    create table user (

    uid varchar(32) PRIMARY KEY, username varchar(32), password varchar(32)

    );

    – ⻆色表
    create table role (

    rid varchar(32) PRIMARY KEY,
    rname varchar(32)

    };

    – 中间表
    create table user_role(

    user_id varchar(32),
    role_id varchar(32),
    CONSTRAINT user_role_pk PRIMARY KEY(user_id,role_id),
    CONSTRAINT user_id_fk FOREIGN KEY(user_id) REFERENCES user(uid), CONSTRAINT role_id_fk FOREIGN KEY(role_id) REFERENCES role(rid)

    );

    #####多表查询

    1. INSERTINTOcategory(cid,cname)VALUES(‘c001’,’家电’);
    2. 3 INSERTINTOcategory(cid,cname)VALUES(‘c002’,’服饰’);
    3. 4 INSERTINTOcategory(cid,cname)VALUES(‘c003’,’化妆品’);
    4. 5 #商品
    5. 6 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p001’,’联 想’,5000,’1’,’c001’);
    6. 7 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p002’,’海 尔’,3000,’1’,’c001’);
    7. 8 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p003’,’雷 神’,5000,’1’,’c001’);

    9

    1. 10 INSERTINTOproducts(pid,pname,price,flag,category_id)

      VALUES(‘p004’,’JACK JONES’,800,’1’,’c002’);

    2. 11 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p005’,’真

      维斯’,200,’1’,’c002’);

    3. 12 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p006’,’花

      花公子’,440,’1’,’c002’);

    4. 13 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p007’,’劲

      霸’,2000,’1’,’c002’);

    14

    1. 15 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p008’,’香 奈儿’,800,’1’,’c003’);
    2. 16 INSERTINTOproducts(pid,pname,price,flag,category_id)VALUES(‘p009’,’相 宜本草’,200,’1’,’c003’);
    多表查询方式

    • 交叉连接查询

      语法: select * from A,B;

    • 内连接查询

      连接查询**(使用的关键字 inner join – inner可以省略)**

      • 隐式内连接: select * from A,B where 条件;
      • 显示内连接: select * from A inner join B on 条件;

      – 查询那些分类的商品已经上架 – 隐式内连接

      SELECT DISTINCT

      *FROM

      category c,

      products p

      WHERE

      c.cid = p.category_id;

      – 显示内连接

      SELECT DISTINCT

      *FROM
      category c INNER JOIN products p

      ON

      c.cid = p.category_id;

    • 外连接查询

      外连接查询**(使用的关键字 outer join – outer可以省略)**

      • 左外连接:left outer join
        • select * from A left outer join B on 条件;
      • 右外连接:right outer join
        • select * from A right outer join B on 条件;

      #2.查询所有分类商品的个数
      #左外连接
      INSERT INTO category(cid,cname) VALUES(‘c004’,’奢侈品’);

      SELECT cname,COUNT(category_id)
      FROM category c LEFT OUTER JOIN products p ON c.cid = p.category_id
      GROUP BY cname;

    子查询

    一条select语句结果作为另一条select语法一部分(查询条件，查询结果，表等)。 语法:

    select ….查询字段 … from … 表.. where … 查询条件

    – 子查询方式 第二种(作为 一张表 使用) select

    p.*

    FROM
    products p, (select * from category where cname=’化妆品’) c

    WHERE

    p.category_id = c.cid;

    ###问题统计

    1. 中文乱码

    因为mysql的客户端设置编码是utf8,而windows系统的cmd窗口编码是gbk

    show variables like ‘character%’; 查看所有mysql的编码

    1. 需要修改client、connection、results的编码一致(改为GBK编码

    set name sgbk;

    2.密码忘记

    1. 停止mysql服务器运行输入services.msc 停止mysql服务

    2. 在cmd下,输入mysqld –console –skip-grant-tables 启动服务器,不要关闭出现的窗口

    3. 新打开cmd,输入mysql -uroot 不需要密码, 然后输入以下SQL语句, 完成密码的修改

    usemysql; update user set password=password(‘root’) WHERE user=’root’;

    注意点

    1.面试题

    1. 针对auto_increment ，删除表中所有记录使用 delete from 表名 或使用 truncate table 表名，二 者有什么区别?

      1删除过程:
      2 delete: 表中记录一条一条删除, auto_increment 计数不会重置为1; 新记录添加时在原有计数基础上+1

      3 truncate: 直接将表删除,重新创建新表, auto_increment 计数重置为1;

    转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达。可以在下面评论区评论，也可以邮件至 337950548@qq.com

    💰

    ©2016-2022 想做个"懒"人

    Built with Hexo and 3-hexo theme

    ×

    Help us with donation