• MySQL-SQL基础应用01


    MySQL-SQL基础应用

    1.sql 介绍

    1. 什么是SQL?
    关系型数据库当中通用的查询语言。全名:结构化查询语言。
    

    2.sql 标准

    SQL-89
    SQL-92 
    SQL-99 
    SQL-03
    5.7 以后符合SQL92严格模式
    通过sql_mode参数来控制
    mysql> select @@sql_mode;
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | @@sql_mode                                                                                                                                |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
    +-------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    

    3.sql 常用分类

    DDL : 数据定义语言 
    DCL : 数据控制语言
    DML : 数据操作语言
    

    4.数据类型、表属性、字符集

    4.1 数据类型

    4.1.1 作用

    保证数据的准确性和标准性。
    

    4.1.2 种类

    #数值类型
    tinyint  : -128~127
    int       :-2^31~2^31-1
    说明:手机号是无法存储到int的。一般是使用char类型来存储收集号
    #字符类型
    char(11) :
    定长 的字符串类型,在存储字符串时,最大字符长度11个,立即分配11个字符长度的存储空间,如果存不满,空格填充。
    varchar(11):
    变长的字符串类型看,最大字符长度11个。在存储字符串时,自动判断字符长度,按需分配存储空间。
    enum('bj','tj','sh'):
    枚举类型,比较适合于将来此列的值是固定范围内的特点,可以使用enum,可以很大程度的优化我们的索引结构。
    #时间类型
    datetime
    范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。
    timestamp 
    1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。
    timestamp会受到时区的影响
    
    
    

    列值不能为空,也是表设计的规范,尽可能将所有的列设置为非空。可以设置默认值为0
    unique key :唯一键
    列值不能重复
    unsigned :无符号
    针对数字列,非负数。

    其他属性:
    key :索引
    可以在某列上建立索引,来优化查询

    4.2 表属性

    4.2.1列属性

    约束(一般建表时添加):
    **primary key** :主键约束
    设置为主键的列,此列的值必须非空且唯一,主键在一个表中只能有一个,但是可以有多个列一起构成。
    **not null**      :非空约束
    列值不能为空,也是表设计的规范,尽可能将所有的列设置为非空。可以设置默认值为0
    **unique key** :唯一键
    列值不能重复
    **unsigned** :无符号
    针对数字列,非负数。
    
    其他属性:
    **key** :索引
    可以在某列上建立索引,来优化查询,一般是根据需要后添加
    **default**           :默认值
    列中,没有录入值时,会自动使用default的值填充
    **auto_increment**:自增长
    针对数字列,顺序的自动填充数据(默认是从1开始,将来可以设定起始点和偏移量)
    **comment ** : 注释
    

    4.2.2表属性

    存储引擎:
    InnoDB(默认的)
    字符集和排序规则:
    utf8       
    utf8mb4
    

    4.3字符集和校对规则

    4.3.1字符集

    utf8       
    utf8mb4
    

    4.3.2 校对规则

    大小写是否敏感
    

    5、DDL应用

    5.1 数据定义语言

    5.2 库定义语言

    5.2.1创建

    5.2.1 创建数据库

    mysql> create database db charset utf8mb4;
    Query OK, 1 row affected (0.00 sec)
    mysql> create database wordpress;
    Query OK, 1 row affected (0.00 sec)
    
    mysql> show create database db;
    +----------+----------------------------------------------------------------+
    | Database | Create Database                                                |
    +----------+----------------------------------------------------------------+
    | db       | CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
    +----------+----------------------------------------------------------------+
    1 row in set (0.00 sec)
    建库规范:
    1.库名不能有大写字母   
    2.建库要加字符集         
    3.库名不能有数字开头
    4.库名要和业务相关
    

    5.2.2 查询库

    mysql> show databases;
    +--------------------+
    | Database           |
    +--------------------+
    | information_schema |
    | db                 |
    | mysql              |
    | performance_schema |
    | sys                |
    | test               |
    | wordpress          |
    +--------------------+
    7 rows in set (0.00 sec)
    mysql> show create database test;
    +----------+------------------------------------------------------------------+
    | Database | Create Database                                                  |
    +----------+------------------------------------------------------------------+
    | test     | CREATE DATABASE `test` /*!40100 DEFAULT CHARACTER SET utf8mb4 */ |
    +----------+------------------------------------------------------------------+
    1 row in set (0.00 sec)
    mysql> show create database wordpress;
    +-----------+----------------------------------------------------------------------+
    | Database  | Create Database                                                      |
    +-----------+----------------------------------------------------------------------+
    | wordpress | CREATE DATABASE `wordpress` /*!40100 DEFAULT CHARACTER SET latin1 */ |
    +-----------+----------------------------------------------------------------------+
    1 row in set (0.00 sec)
    
    

    5.2.3 修改库

    mysql> alter database wordpress charset utf8mb4;
    Query OK, 1 row affected (0.01 sec)
    

    5.2.4 删除库

    mysql> drop database wordpress;
    Query OK, 0 rows affected (0.01 sec)
    #生产环境误用!!!!
    

    5.3表定义

    5.3.1 创建

    create table stu(
    列1  属性(数据类型、约束、其他属性) ,
    列2  属性,
    列3  属性
    )
    

    5.3.2 建表

    mysql> CREATE TABLE stu (
        -> id     INT NOT NULL PRIMARY KEY AUTO_INCREMENT COMMENT '学号',
        -> sname  VARCHAR(64) NOT NULL COMMENT '姓名',
        -> age    TINYINT  UNSIGNED  NOT NULL DEFAULT 0  COMMENT '年龄',
        -> gender ENUM('m','f','n')  NOT NULL DEFAULT 'n' COMMENT '性别',
        -> intime DATETIME NOT NULL COMMENT '入学时间'
        -> )ENGINE=INNODB CHARSET=utf8mb4 COMMENT '学生表';
    Query OK, 0 rows affected (0.01 sec)
    
    

    建表规范

    1. 表名小写
    2. 不能是数字开头
    3. 注意字符集和存储引擎
    4. 表名和业务有关
    5. 选择合适的数据类型
    6. 每个列都要有注释
    7. 每个列设置为非空,无法保证非空,用0来填充。
    

    5.3.3查表定义

    mysql> show tables;
    +------------------+
    | Tables_in_school |
    +------------------+
    | stu              |
    +------------------+
    1 row in set (0.00 sec)
    
    mysql> show create table stu;
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    | stu   | CREATE TABLE `stu` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学号',
      `sname` varchar(255) NOT NULL COMMENT '姓名',
      `sage` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '年龄',
      `sgender` enum('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
      `sfz` char(18) NOT NULL COMMENT '身份证',
      `intime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入学时间',
      PRIMARY KEY (`id`),
      UNIQUE KEY `sfz` (`sfz`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表'                   |
    +-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    1 row in set (0.00 sec)
    mysql> desc stu;
    +---------+---------------------+------+-----+-------------------+----------------+
    | Field   | Type                | Null | Key | Default           | Extra          |
    +---------+---------------------+------+-----+-------------------+----------------+
    | id      | int(11)             | NO   | PRI | NULL              | auto_increment |
    | sname   | varchar(255)        | NO   |     | NULL              |                |
    | sage    | tinyint(3) unsigned | NO   |     | 0                 |                |
    | sgender | enum('m','f','n')   | NO   |     | n                 |                |
    | sfz     | char(18)            | NO   | UNI | NULL              |                |
    | intime  | timestamp           | NO   |     | CURRENT_TIMESTAMP |                |
    +---------+---------------------+------+-----+-------------------+----------------+
    6 rows in set (0.00 sec)
    
    

    5.3.4 修改表的定义

    -- 添加和删除字段
    -- 1. 在表中添加telnum char(11) not null unique key comment '手机号'
    ALTER TABLE stu ADD COLUMN telnum CHAR(11) NOT NULL UNIQUE KEY COMMENT '手机号';
    
    -- 2. 在sname后添加a列
    ALTER TABLE stu ADD COLUMN  a INT NOT NULL COMMENT '测试列' AFTER sname;
    
    -- 3. 在第一列前添加b列
    ALTER TABLE stu ADD COLUMN  b INT NOT NULL COMMENT '测试列' FIRST ;
    
    -- 4. 删除添加的a,b列
    ALTER TABLE stu DROP COLUMN a;
    ALTER TABLE stu DROP COLUMN b;
    
    -- 5. 修改数据类型 
    ALTER TABLE stu MODIFY telnum VARCHAR(20) NOT NULL UNIQUE KEY COMMENT '手机号';
    ALTER TABLE stu MODIFY telnum VARCHAR(30) NOT NULL UNIQUE KEY COMMENT '手机号';
    
    -- 6. 修改列名及数据类型
    ALTER TABLE stu CHANGE telnum tel VARCHAR(64) NOT NULL UNIQUE KEY COMMENT '手机号';
    

    5.3.5 删除表

    mysql> show tables;
    

    6.DCL应用

    grant 
    revoke
    

    7.DML应用

    7.1 作用

    对表中的数据行进行增、删、改
    

    7.2 insert

    --- 最标准的insert语句
    INSERT INTO stu(id,sname,sage,sg,sfz,intime) 
    VALUES
    (1,'zs',18,'m','123456',NOW());
    SELECT * FROM stu;
    --- 省事的写法
    INSERT INTO stu 
    VALUES
    (2,'ls',18,'m','1234567',NOW());
    --- 针对性的录入数据
    INSERT INTO stu(sname,sfz)
    VALUES ('w5','34445788');
    --- 同时录入多行数据
    INSERT INTO stu(sname,sfz)
    VALUES 
    ('w55','3444578d8'),
    ('m6','1212313'),
    ('aa','123213123123');
    SELECT * FROM stu;
    _____________________________________________________________________________________________________
    
    
    

    7.3 update

    DESC stu;
    SELECT * FROM stu;
    UPDATE stu SET sname='zhao4' WHERE id=2;
    注意:update语句必须要加where。
    mysql> desc stu;
    +--------+---------------------+------+-----+---------+----------------+
    | Field  | Type                | Null | Key | Default | Extra          |
    +--------+---------------------+------+-----+---------+----------------+
    | id     | int(11)             | NO   | PRI | NULL    | auto_increment |
    | sname  | varchar(64)         | NO   |     | NULL    |                |
    | age    | tinyint(3) unsigned | NO   |     | 0       |                |
    | gender | enum('m','f','n')   | NO   |     | n       |                |
    | intime | datetime            | NO   |     | NULL    |                |
    +--------+---------------------+------+-----+---------+----------------+
    5 rows in set (0.00 sec)
    
    mysql> select * from stu;
    +----+------------+-----+--------+---------------------+
    | id | sname      | age | gender | intime              |
    +----+------------+-----+--------+---------------------+
    |  1 | xiaolai    |  18 | m      | 2021-07-02 08:30:00 |
    |  2 | laihecheng |  14 | f      | 2021-07-02 08:30:00 |
    |  3 | xiaowang   |  34 | m      | 2021-07-02 08:30:00 |
    +----+------------+-----+--------+---------------------+
    3 rows in set (0.00 sec)
    
    mysql> update stu set sname='zhao4' where id=2;
    Query OK, 1 row affected (0.14 sec)
    Rows matched: 1  Changed: 1  Warnings: 0
    
    mysql> select * from stu;
    +----+----------+-----+--------+---------------------+
    | id | sname    | age | gender | intime              |
    +----+----------+-----+--------+---------------------+
    |  1 | xiaolai  |  18 | m      | 2021-07-02 08:30:00 |
    |  2 | zhao4    |  14 | f      | 2021-07-02 08:30:00 |
    |  3 | xiaowang |  34 | m      | 2021-07-02 08:30:00 |
    +----+----------+-----+--------+---------------------+
    3 rows in set (0.00 sec)
    
    

    7.4 delete

    mysql> select * from stu;
    +----+----------+-----+--------+---------------------+
    | id | sname    | age | gender | intime              |
    +----+----------+-----+--------+---------------------+
    |  1 | xiaolai  |  18 | m      | 2021-07-02 08:30:00 |
    |  2 | zhao4    |  14 | f      | 2021-07-02 08:30:00 |
    |  3 | xiaowang |  34 | m      | 2021-07-02 08:30:00 |
    +----+----------+-----+--------+---------------------+
    3 rows in set (0.00 sec)
    
    DELETE FROM stu  WHERE id=3;
    
    mysql> select * from stu;
    +----+---------+-----+--------+---------------------+
    | id | sname   | age | gender | intime              |
    +----+---------+-----+--------+---------------------+
    |  1 | xiaolai |  18 | m      | 2021-07-02 08:30:00 |
    |  2 | zhao4   |  14 | f      | 2021-07-02 08:30:00 |
    +----+---------+-----+--------+---------------------+
    2 rows in set (0.00 sec)
    
    

    全表删除

    DELETE FROM stu
    truncate table stu;
    区别:
    delete: DML操作, 是逻辑性质删除,逐行进行删除,速度慢.
    truncate: DDL操作,对与表段中的数据页进行清空,速度快.
    

    为删除:用update来替代delete,最终保证业务中查不到(select)即可

    1.添加状态列
    ALTER TABLE stu ADD state TINYINT NOT NULL DEFAULT 1 ;
    SELECT * FROM stu;
    2. UPDATE 替代 DELETE
    UPDATE stu SET state=0 WHERE id=6;
    3. 业务语句查询
    SELECT * FROM stu WHERE state=1;
    

    删除语句的对比

    drop table t1 ;      ---> 表定义+表数据(物理),全删除,磁盘空间立即删除
    truncate table t1 ;  ---> 清空表数据(物理),立即释放磁盘空间。
    delete from t1;      ---> 逐行删除表数据(逻辑,delete mark)。不会立即释放磁盘空间,会有碎片。
    

    8.DQL应用(select)

    8.1 单独使用

    # 查询系统变量(参数)
    SELECT @@port;
    SELECT @@basedir;
    SELECT @@datadir;
    SELECT @@server_id;
    SELECT @@innodb_flush_log_at_trx_commit;
    #替代方案: 
    SHOW VARIABLES;
    SHOW VARIABLES LIKE '%trx%';
    

    --select 函数()

    SELECT NOW();
    SELECT DATABASE();
    SELECT USER();
    SELECT CONCAT("hello world");
    SELECT CONCAT(USER,"@",HOST) FROM mysql.user;
    SELECT GROUP_CONCAT(USER,"@",HOST) FROM mysql.user;
    https://dev.mysql.com/doc/refman/5.7/en/func-op-summary-ref.html?tdsourcetag=s_pcqq_aiomsg
    

    8.2单表字句-from

    SELECT 列1,列2 FROM 表
    SELECT  *  FROM 表
    

    列子:

    -- 查询stu中所有的数据(不要对大表进行操作)

    SELECT * FROM stu ;
    

    -- -- 查询stu表中,学生姓名和入学时间

    SELECT sname , intime FROM stu;
    

    sql语句练习

    # 导入world练习库
    -- https://dev.mysql.com/doc/index-other.html
    -- [root@db01 ~]# mysql -uroot -p123 < world.sql
    world            ===>世界
    city             ===>城市
    country          ===>国家
    countrylanguage  ===>国家语言
    
    city:城市表
    DESC city;
    ID :         城市ID
    NAME :       城市名
    CountryCode: 国家代码,比如中国CHN 美国USA
    District :   区域
    Population : 人口
    
    SHOW CREATE TABLE city;
    SELECT * FROM city WHERE id<10;
    

    8.2.1 where 配合等值查询(select+from+where应用)

    -- 查询中国(CHN)所有城市信息

    SELECT * FROM city WHERE countrycode='CHN';
    

    -- 查询北京市的信息

    SELECT * FROM city WHERE NAME='peking';
    

    -- 查询甘肃省所有城市信息

    select * from city where district='gansu';
    

    -- 查询美国(USA)所有的城市名和人口数

    select name,Population from city where CountryCode='USA';
    

    8.2.2 where 配合不等值查询 (> < >= <= !=)

    -- 例子: 查询世界上人口数据小于100人的城市信息

    select name,population from city where Population<100;
    

    -- 例子:查询中国,并且人口大于500w的城市信息

    select * from city where CountryCode='CHN' and Population>5000000;
    

    -- 例子: 查询中国或美国的城市信息

    select * from city where CountryCode='CHN' or CountryCode='USA';
    #另一种写法
    select * from city where countrycode in ('CHN','USA');
    

    -- 例子: 查询人口数量在 100w-110w之间

    select * from city where Population>=1000000 and Population<=1100000;
    #另一种写法
    select * from city where Population between 1000000 and 1100000;
    

    8.2.3 where配合like应用

    -- 查询countrycode是 “CH” 开头的城市信息

    select * from city where countrycode like 'CH%';
    

    8.2.4 select + from + where + group by +聚合函数应用

    -- 聚合函数种类

    count() : 统计个数
    sum()   : 求和
    avg()   :平均值
    max()   :最大值
    min()   :最小值
    group_concat():列转行:
    

    -- 统计city表,每个国家的城市个数

    select CountryCode,count(*) from city  group by countrycode;
    

    -- 统计city表,中国 每个省的 城市个数

    select district,count(*) from city where CountryCode='CHN' group by District;
    

    -- 统计city表,每个国家的总人口数

    select CountryCode,sum(Population) from city group by CountryCode;
    

    -- 统计city表,中国 每个省的总人口数

    select District,sum(Population) from city where CountryCode='CHN' group by District;
    

    -- 统计city表,中国 每个省的 城市个数 ,所有城市名

    select District,count(*),group_concat(name) from city where CountryCode='CHN' group by District;
    

    select + from + where + group by + 聚合函数 + having 应用

    -- 统计city表,中国 每个省的总人口数,显示总人口数超过500w的信息

    select District,sum(Population) from city where CountryCode='CHN' group by District having sum(population)>5000000;
    

    select + from + where + group by + 聚合函数 + having +order by

    -- 统计city表,中国 每个省的总人口数,显示总人口数超过500w的信息,并按照总人口排序输出。

     #有小到大排序
     select district,sum(population) from city where countrycode='CHN' group by District having sum(Population)>5000000 order by sum(Population); 
      #有大到小排序
     SELECT district,SUM(population)  FROM city  WHERE countrycode='CHN'  GROUP BY district HAVING  SUM(population)>5000000 ORDER BY SUM(population) desc;
    

    select + from + where + group by + 聚合函数 + having +order by + limit

    -- 统计city表,中国 每个省的总人口数,显示总人口数超过500w的信息,并按照总人口从大倒小排序输出
    -- 只显示前5名。

    select district,sum(population) from city  where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5 offset 0;
    
    

    -- 只显示6-10名。

    select district,sum(population) from city  where countrycode='CHN' group by district having sum(population)>5000000 order by sum(population) desc limit 5 offset 5;
    
  • 相关阅读:
    Linux用户组管理及用户权限3
    MySQL预处理和事务
    MySQL-子查询和多表联查
    Mysql-分组和聚合函数
    LNMP搭建
    apache-虚拟主机配置
    Apache-重写
    apache配置文件详解
    vim使用
    php-curl_init函数
  • 原文地址:https://www.cnblogs.com/lailaoban/p/14765802.html
Copyright © 2020-2023  润新知