MySQL相关基础

一.和数据库相关的sql

1.连接数据库 mysql -uroot -p

2.显示所有数据库 show databases;

3.查看字符编码 show variables like "char%";

4.显示当前状态 status

5.创建数据库  create database 数据库名;

6.查看单个数据库信息  show create database 数据库名;

7.删除数据库 drop database 数据库名;

8.创建数据库时指定字符集 create database 数据库名 character set utf8;

二.和表相关的sql

1.创建表 create table 表名 (字段名 字段类型，字段名 字段类型) engine=innodb charset=utf8;

2.查看所有表 show tables;

3.查看表的属性 数据库的引擎和编码 show create table student;

4.查看表结构 desc 表名;

5.给表添加字段 alter table 表名  add 字段名 字段类型;

                        alter table 表名 add 字段名 字段类型 after 字段名；

                        alter table 表名 add 字段名 字段类型 first 字段名；

6.删除表字段 alter table 表名 drop 字段名;

7.修改表的属性 alter table 表名 engine=myisam charset=utf8;

8.修改字段的名称和类型 alter table 表名 change 字段名 新字段名 新字段类型;

9.修改字段的类型和位置 alter table 表名 modify 字段名 字段类型  after 字段名；

10.删除表 drop table 表名;

11.重名名表 alter table 表名 rename to 新表名;

三.数据相关sql

1.插入数据

    全表插入 insert into 表名 values (字段值，字段值，字段值);

    指定字段插入 insert into 表名 (字段名1，字段名2) values (字段1值，字段2值);

    批量插入 insert into 表名 (字段值，字段值，字段值),(字段值，字段值，字段值),(字段值，字段值，字段值);

2.修改数据 update 表名 set 字段名=新值 where 字段名=字段值；后面的判断条件可以有多种

3.删除数据 delete from 表名 where 字段名=字段值;

4.查询数据 select 字段名 from 表名 where 字段名=字段值;

四.事务相关

mysql客户端事务是默认提交的,

查看是否自动提交事务：show variables like "%autocommit%";

0是关闭，1是自动提交：set autocommit=0;

五.SQL分类

1.Data Definition Language   数据定义语言DDL 不支持事务 不能回滚 

常见命令：create alter drop

2.Data Manipulation Laguage 数据操纵语言 DML 支持事务

常见命令:insert update delete

3.Data Query Language 数据查询语言 DQL 

常见命令：select

4.Trainsaction Control Language 事务控制语言TCL

常见命令：begin commit roolback

5.Data Control Language 数据控制语言DCL

六.数据库的数据类型

1.整数：

int (m) 

bigint(m)

m代表显示的位数，意思是当前显示数据不足m时在前面补0，前提是必须在字段的声明后面添加zerofill

create table student (id int(6)  zerofill);

2.浮点数

float(m,d)   m代表总长度，d代表小数的位数

double

decimal  涉及到钱的用decimal

3.字符串

varchar(20) 可变长度 执行效率低 节省空间    65535字节  但是建议不超过255, 超过建议使用text

char(20) 固定长度  长度255

text 长度可变最大 65535字节 没有默认值

4.日期

date只能保存日期

time只能保存时间

datetime  保存日期+时间 如果不赋值 默认值为null，最大值是9999-12-31

timestamp 保存日期+时间 如果不赋值默认为当前时间,最大值 2038-01-19

六.select查询

  select 字段名,字段名 from 表名 where 字段名 is not null;

  去重distinct             select distinct 字段名 from 表;

  别名       select 字段名 as '别名' from 表名;

                select 字段名 '别名' from 表名;

                select 字段名 别名 from 表名;

where   =   <   >   >=   <=  !=  (<>)

运算符        and          or

in 查询某个字段的值为多个时   select * from 表名 where 字段名 in(字段值1，字段值2，字段值3);

between  and 某两个数值之间，包括and两边的数  

like操作符   -_代表单个未知字符;

                   -%代表多个未知字符;

                    select * from 表名 where 字段名 like '%奇迹_';

order by 子句   by后添加排序规则的字段  asc升序，desc降序，默认升序;

                如果有where要写在where的后面；

                多字段排序会按照第一个字段排序，如果第一个字段相同，然后按第二个字段排序；

                order by age,money desc;

limit 子句  分页语句   limit begin,count      begin从0开始

            select *from 表名 order by 字段名  limit 10,5;        显示第三页的数据

concat()函数   select concat(name,'的工资是',money,'元') from 表名;

数值计算  +-*/      7%2 等效于 mod(7,2)

日期相关函数

        now()  获取当前日期+时间

        curdate() 获取当前日期

        curtime()获取当前时间

        date(now()) 从某个时间中获取日期

        time(now()) 从某个时间获取时间

        extract() 从某个时间中获取 年 月 日 时 分 秒

        select extract(year from now());    month,day,hour,minute,second

date_format()函数

        date_format(时间，格式)

        格式：%Y四位年        %y两位年         %m 05月          %c  5月        %d日        %H  24小时        

                    %h12小时    %i 分        %s 秒

        select date_format(now(),'%Y年%m月%c日');

str_to_date 将日期字符串转化为date

        str_to_date(时间字符串,转化格式)

        select str_to_date("1998年5月25日哈哈","%Y年%c月%d日哈哈");

ifnull()函数

        字段名=ifnull（A,B）  如果A值为null，则值为B,若A值不为空，则值为A

聚合函数

        sum()求和        avg()平均值       count()数量        max()最大值        min()最小值

字符串函数select得到查询结果

        char_length(str)    获取字符串长度

        locate(subStr,str)    得到子串在父串的位置，从一开始

        insert(str,subStr)    得到子串在父串的位置，从一开始

        lower(str)  转成小写

        upper(str)  转成大写

        trim(str) 去掉字符串两头的空格

        substring(str,index,length)     截取字符串，index从1开始

        repeat(str,count)    重复

        replace(str,old,new) 替换

        reverse()  反转

数学相关函数

        floor(num)  向下取整

        round(num) 四舍五入

        round(num,m)  四舍五入，m代表小数位数

        truncate(num,m)  不四舍五入

        rand()  随机数  小于1的随机数

七.条件分组统计

    having子句 聚合函数不能写在where后面,因为执行where的时候聚合函数还没有执行

    推荐having和聚合函数结合使用,虽然可以写普通字段条件,但是普通字段条件推荐使用where

    SQL关键字执行顺序 1.from    2.where   3.group by    4.having聚合字段过滤    5.order by  6.select筛除 

连接：：：: ：：：

等值连接   select * from A,B where A.x=B.x and age>18;

内连接  select * from A [inner] join B on A.x=B.x  where age>18;  都在一个表中

        内连接只能查询出有关联关系的数据；

        select   e.ename,e.sal,d.dname,d.loc

        from emp e inner join dept d

        on e.deptno=d.deptno

        where e.sal>3000;

左连接   以join左边的表为基准，查询结果显示左边表的所有数据，没有关联关系的显示null

        select e.name,d.name from

        emp e left join dept d

        on e.deptno=d.deptno;

右连接

完全连接 full

交叉连接 cross  不带on子句，返回的是两个表的乘积，笛卡尔积（避免）

关联：：：：：：

子查询

关联查询 select emp.name,dept.dname from  emp,dept  where  emp.deptno=dept.deptno;

#自关联查询

    一张表中的数据存在层级关系

# 关联查询

    一对一关联     用户表    用户信息表

    一对多关联    在多的表中添加一个关联关系的字段

    多对多关联    使用中间表保存两张表的对应关系

注意：连接方式 和 关联关系的区别

        连接方式：使用sql语句查询存在关联关系的表的数据的时候      

        关联关系：数据表之间存在逻辑关系 

八.视图 （就是一段sql语句，就是一张虚拟表）

    创建：create view 视图名 as 子查询；

            create view v_emp as (select * from emp where deptno=10); 

    使用：select * from 视图名；

            select*from v_emp;

    修改视图： create or replace v_emp as(select * from emp where deptno=10 and sal<3000);

    删除视图：drop view v_emp;

    分类：简单视图，复杂视图 

九.MySQL约束

    unique                not null                 default

    primary key     添加主键 alter table 表名 add primary key(字段名);

                            删除主键 alter table 表名 drop primary key;

    auto_increment

    外键约束 foreign key  myisam不支持     

    check约束

十.事务

    ACID性质：原子性，最小的单元，不可分割

                     一致性，保证sql执行一致，要么都成功，要么都失败

                      隔离性，多个事务并发时，互不影响

                       持久性，commit提交之后，数据保存到数据库中