MySQL 学习笔记

MySQL 学习笔记

1.数据类型 数值、日期/时间和字符串(字符)

类型            所占字节数            说明
tinyint        1                   小整数值,如状态
smallint      2                   大整数值
mediumint    3                   大整数值
int                4              大整数值
bigint           8                   极大整数值
float            4                   单精度浮点数值
double        8                  双精度浮点数值
decimal     Max(D+, M+)     含小数值,例如金额

date         3                YYYY-MM-DD
time         3                HH:MM:SS
year         1                YYYY
datetime     8                YYYY-MM-DD HH:MM:SS
timestamp    8                YYYYMMDDHHMMSS

char         0~255               定长字段串
varchar     0~65535         变长字符串
text         0~65535         长文本数据
blob                         二进制形式的文本数据

2.DDL Data Definition Language   

    #创建数据库
    CREATE DATABASE 数据库名
    #删除数据库
    DROP DATABASE 数据库名
    
    #查看mysql支持的存储引擎
    mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
   
    CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name [(create_definition,...)][table_options][select_statement]
    
        create table contacts(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
                          name VARCHAR(30),
                          id_number varchar(18) unique comment 'IDCARDNUMBER',
                          phone VARCHAR(11)) ENGINE=InnoDB DEFAULT CHARSET=utf8;
        create table sc(id int not null auto_increment primary key comment '主键id',
                stu_no int not null comment '学号',
                course varchar(30) comment '课程',
                grade int comment '成绩',
                foreign key(stu_no) references stu(stu_no));
                
    alter TABLE contacts ADD sex VARCHAR(1);
    alter TABLE contacts MODIFY sex tinyint;
    alter table contacts DROP COLUMN sex;
    DROP table contacts;
   


3. DML Data Manipulation Language

    INSERT 插入单条数据:
    INSERT INTO table_name(field1,field2,...,fieldN) VALUES (value1,value2,...,valueN);
    INSERT 插入多条数据:
    INSERT INTO table_name(field1,field2,...,fieldN) VALUES (valueA1,valueA2,...,valueAN),(valueB1,valueB2,...,valueBN),...,(valueN1,valueN2,...,valueNN);
    
    注意事项:
    1、如果字段是字符型,值必须使用单引号或者双引号,如”value”;如果值本身带单引号或双引号,需要转义
    2、如果所有列都要添加数据,insert into语句可以不指定列,即
    
    insert into contacts(name,sex,phone) values('tom\'s cat',1,'13312345678');
    insert into contacts(name,sex,phone) values("Lily\'s cat",1,'13312345678');

    UPDATE table_name set field1=newValue1,field2=newValue2 [where clause];
    
    DELETE from table_name [where clause];


mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| contacts       |
+----------------+
1 row in set (0.00 sec)

mysql> desc contacts;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int         | NO   | PRI | NULL    | auto_increment |
| name  | varchar(30) | YES  |     | NULL    |                |
| phone | varchar(11) | YES  |     | NULL    |                |
| sex   | tinyint     | YES  |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)


4. DQL    https://dev.mysql.com/doc/refman/8.0/en/select.html

    SELECT column_name1, column_name2
    FROM table_name
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}, ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position} [ASC | DESC], ... [WITH ROLLUP]]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]
    
    #SQL语句中使用where子句语法
    SELECT column_name FROM table_name WHERE column_name 运算符 value
    运算符           描述
    =                等于
    <> 或 !=          不等于
    >                 大于
    <                 小于
    >=               大于等于
    <=               小于等于
    between and 选取介于两个值之间的数据范围;在MySQL中,相当于>=并且<=
    
    #and、or运算符语法
    运算符        描述
    and         表示左右两边的条件同时成立
    or             表示左右两边只要有一个条件成立
    SELECT column_name FROM table_name WHERE condition1 AND condition2 OR condition3
    
    #where子句使用in语法
    SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)
    #where子句使用like语法
    SELECT column_name FROM table_name WHERE column_name LIKE ‘%value%’
        说明:
        1、LIKE子句中的%类似于正则表达式中的*,匹配任意0个或多个字符
        2、LIKE子句中的_匹配任意单个字符
        3、LIKE子句中如果没有%和_,就相当于运算符=的效果
    
    select * from employee where name like '李_'  #李后面只有一个字符
    select date_format(now(), '%Y%-%M-%D %H:%i:%s');
    select ifnull(1/0,0);
    select ifnull(1,0);
    

            CASE WHEN [expr] THEN [result1]…ELSE [default] END
            CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
            select id,name,
                    case sex when '' then 'F'
                             when '' then 'M'
                    end as sex,
                    salary
            from employee;
            
    SELECT * from contacts;
    select sum(salary) from employee;
    select max(salary) from employee;
    select min(salary) from employee;
    select avg(salary) from employee;
    sum,avg,min,max 忽略空值
    select count(*) from employee;   

    #order by语法
    SELECT column_name1, column_name2 FROM table_name1, table_name2 ORDER BY column_name, column_name [ASC|DESC]
        1. ASC表示按升序排列,DESC表示按降序排列。
        2. 默认情况下,对列按升序排列。
    #limit语法 约束要返回的记录数,通常使用LIMIT实现分页。
    SELECT column_name1, column_name2 FROM table_name1, table_name2 LIMIT [offset,] row_count
        1. offset指定要返回的第一行的偏移量。第一行的偏移量是0,而不是1。
        2. row_count指定要返回的最大行数。
        【经验分享】limit的分页公式:limit (page-1)*row_count, row_count
        SELECT * from contacts limit 0, 5;
        SELECT * from contacts limit 5, 10;
    
    #group by语法    
    SELECT column_name, aggregate_function(column_name) FROM table_name
    GROUP BY column_name
        1. aggregate_function表示聚合函数。
        2. group by可以对一列或多列进行分组。
          
    #having语法    在 SQL 中增加 HAVING 子句原因是,WHERE 关键字无法与聚合函数一起使用。HAVING 子句可以对分组后的各组数据进行筛选。
    SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value
    GROUP BY column_name HAVING aggregate_function(column_name) operator value
    
    #group_concat语法
    group_concat配合group by一起使用,用于将某一列的值按指定的分隔符进行拼接,MySQL默认的分隔符为逗号。
    group_concat([distinct] column_name [order by column_name asc/desc ] [separator '分隔符'])
    select dept,group_concat(name order by name desc separator ';') from employee group by dept;
    
    #distinct语法
    distinct用于在查询中返回列的唯一不同值(去重复),支持单列或多列
    SELECT DISTINCT column_name, column_name FROM table_name;
    
    #表连接语法
    连接类型            定义                                                        图示                        例子
    内连接            只连接匹配的行                                                A and B                    select A.c1, B.c2 from A join B on A.c3 = B.c3
    左连接            包含左表的全部行(不管右表是否存在与之匹配的行),以及右表中全部匹配的行    Only A                    select A.c1, B.c2 from A left join B on A.c3 = B.c3
    右连接    包含右表的全部行(不管左表是否存在与之匹配的行),以及左表中全部匹配的行            ONLY B                     select A.c1, B.c2 from A right join B on A.c3 = B.c3
    全连接    包含左右两个表的全部行(不管在另一个表中是否存在与之匹配的行)                    A OR B                         select A.c1, B.c2 from A full join B on A.c3 = B.c3
    交叉连接(cross join):没有用where子句的交叉连接将产生笛卡尔积,第一个表的行数乘以第二个表的行数等于笛卡尔积和结果集的大小。
    
    select A.stu_no,A.name,B.course,B.score from stu A join score B on (A.stu_no = B.stu_no)
    select A.stu_no,A.name,B.course,B.score from stu A inner join score B on (A.stu_no = B.stu_no)
    select A.stu_no,A.name,B.course,B.score from stu A ,score B where A.stu_no = B.stu_no
    
    select A.stu_no,A.name,B.course,B.score from stu A left join score B on (A.stu_no = B.stu_no)
    select A.stu_no,A.name,B.course,B.score from stu A right join score B on (A.stu_no = B.stu_no)
       
    select A.stu_no,A.name,B.course,B.score from stu A ,score B
    
    #自连接语法
    自连接是一种特殊的表连接,它是指相互连接的表在物理上同为一张表,但是逻辑上是多张表。自连接通常用于表中的数据有层次结构,如区域表、菜单表、商品分类表等。
    SELECT A.column, B.column FROM table A, table B WHERE A.column = B.column;
    
    SELECT A.id,A.name,B.name as province from area A, area B where A.pid = B.id and A.pid<>0;
    
    #where子句使用in语法
    SELECT column_name FROM table_name WHERE column_name IN(value1, value2, …)
    
    SELECT column_name FROM table_name WHERE column_name IN(SELECT column_name FROM table_name [WHERE]);
    
    #where子句使用exists语法
    SELECT column_name1    FROM table_name1    WHERE EXISTS (SELECT * FROM table_name2 WHERE condition);
)


5.MySQL内置函数

    函数                描述
    数学函数如            ABS、SQRT、MOD、SIN、COS、TAN、COT等
    字符串函数            如LENGTH、LOWER、UPPER、TRIM、SUBSTRING等
    日期和时间函数        如NOW、CURDATE、CURTIME、SYSDATE、DATE_FORMAT、YEAR、MONTH、WEEK等
    聚合函数            COUNT、SUM、AVG、MIN、MAX
    条件判断函数        IF、IFNULL、CASE WHEN等
    系统信息函数        VERSION、DATABASE、USER等
    加密函数            MD5、SHA1、SHA2等
    函数now()        用于返回当前的日期和时间。
    date_format()    用于以指定的格式显示日期/时间。         select name, date_format(birthday, '%Y/%m/%d') from user;
    

 聚合函数
    函数            描述
    count         返回符合条件的记录总数
    sum         返回指定列的总和,忽略空值
    avg            返回指定列的平均值,忽略空值
    min            返回指定列的最小值,忽略空值
    max            返回指定列的最大值,忽略空值
   
    
    函数ifnull()用于处理NULL值。    ifnull(v1,v2),如果 v1 的值不为 NULL,则返回 v1,否则返回 v2。
    case when是流程控制语句,可以在SQL语句中使用case when来获取更加准确和直接的结果。
    SQL中的case when类似于编程语言中的if else或者switch。
    
    #case when的语法有2种
    CASE [col_name] WHEN [value1] THEN [result1]…ELSE [default] END
    CASE WHEN [expr] THEN [result1]…ELSE [default] END
   

posted on 2020-06-02 21:57  InnoLeo  阅读(166)  评论(0编辑  收藏  举报