mySQL语句

数据库SQL语句

查看所有数据库

show databases;

创建数据库  

create database 数据库名;

修改数据库字符集

alter database 数据库名 charset = utf8/gbk;

使用数据库  

use 数据库名;

删除数据库  

drop database 数据库名;

创建数据库时指定字符集

Create database 数据库名 character set = utf8/gbk;

创建表单

Create  table 表名 (字段类型 字段值);

创建表单时指定引擎和字符集字符集

        Create table 表名(字段类型,字段名)engine = innodb/myisam charater set = utf8/gbk;

查看表详情

    Show create table 表名;

查看表结构

    Desc 表名;

修改表名

    Rename 原表名 to 新表名;

修改表引擎和字符集

    Alter table 表名 engine = innodb/myisam character set = utf8/gbk;

修改表字段

    Alter table 表名 change 原字段名 新字段名 字段类型 first/after id;

修改字段类型和位置

    Alter table 表名 modify 字段名 新类型 first/after id;

添加表字段

    Alter table 表名 add 字段名 字段类型 first/after name;

删除表

    Drop table 表名;

全表插入

    Insert into 表名 values(1,’tom’),(1,’Jim’);

指定字段插入

    Insert into 表名 (id,name) values(2,’Jerry’);

查询数据

    Select * from 表名;

查看所有表

    Show tables;

删除表字段

    Alter table 表名 drop name;

删除表内某条数据

    Delete from 表名 where id = 1;

删除表内所有数据

    Delete from 表名;

删除表并创建新表

    Truncate table 表名;

修改数据

    Update 表名 set  name = ‘Jim’ where id = 2;

解决windows乱码

    Set names gbk;

主键约束 + 自增

    Create table 表名 (id int primary key auto_increment,name varchar(10));

注释

    Create table 表名 (id int primary key auto_increment comment ‘这是主键’);

事务

    开启事务 begin

    回滚事务 rollback

    标记事务 savepoint s1

    回滚到标记事务    rollback to s1;

    提交事务 commit

查看数据库提交状态

    Show variables like ‘%autocommit%’;

查看表提交状态

    Show variables like ‘%character%’;

修改数据库提交状态

    Set autocommit = 0/1;

SQL分类

    DDL 数据标记语言  create alter truncate(不支持事务)

    DML 数据操作语言  update insert delete select

    DQL 数据查询语言  select

    TCL 事务控制语言  begin savepoint commit rollback

    DCL 数据控制语言  用于分配用户使用权限

Is null 判断是否为空值

Is not null 判断是否不为空值

数据类型

    整型 int bigint(m)  m 代表显示长度,结合zerofill使用

    浮点型 double(m,d) m代表显示整数长度 d 代表小数长度.高精度运算时使用 decimal

    字符串   char 长度为255,char长度超过255时使用text,text为可变字符串,长度为65535,varchar 可变长度字符串,长度为65535

    日期类型 date time timestamp;

导入SQL数据文件

    Windows 系统 scoure D:/文件名.后缀

    Linux 系统       系统/home/用户名/桌面/文件名.后缀

别名 as ‘别名’

    Select name as ‘别名’ from info;

    Select name ‘别名’ from info;

    Select name 别名 from info;

去重 distinct

    Select distinct date from info;

比较运算符

• > < = != <> * % /

模糊查询

    _ 代表单个字符

    % 代表多个未知字符

    Select name from info like ‘%s%’;

And 与 or 的区别

    And 与 Java 中的 && 一样

    Or 与 Java 中的 || 一样

查询某个字段的多个值

    Select name from info where salary in(100,200,300);

查询字段的值在两个值之间

    Select name from info where salary between 100 and 200;

排序

    升序排列 select name from info order by salary;

    降序排列 select name from info order by salary desc;

    多段排序 select name from info order by deptno,salady desc;

分页查询

• 公式     limit((跳过的页数-1)*每页数据的条数),每页显示数据的数量)
• Select name from info where salary>100 limit 1,10;

数值计算

• + * / % 7%2 = mod(7,2)

Select name,comm*5 from info;

日期函数相关

1. 获取当前时间    select now();
2. 提取当前日期    select date(now());
3. 提取当前时间    select time();
4. 从年月日时分秒当中提取时间分量

     Select extract(year from now());

     Select extract(month from now());

     Select extract(day from now());

     Select extract(hour from now());

     Select extract(minute from now());

     Select extract(second from now());

日期格式化

    格式 : %Y 四位年      %y 两位年    %m 两位月    %c 一位月    %d 日   %H 24小时   %h 12小时   %i 分   %s 秒

1. 把默认时间格式转换为默认格式
   1. Select date_format(now().’%年%m月%d日%H时%i分%s秒’);
2. 把非标准时间转换为默认格式
   1. Select str_to_date(’20.01.2019 17:32:10’,’%d.%m.%Y %H”%i:%s’);

If null(x,y)    例: age = ifnull(x,y) 如果x的值为null,那么age的值为y,如果x不为null那么age的值是x

聚合函数

1. 平均值: avg(salary);
2. 统计值: count(salary);
3. 求和值: sum(salary);
4. 最大值: max(salary);
5. 最小值: min(salary);

字符串相关

    - 字符串拼接 concat(字段名,'字符串')

        select ename,concat(salary,'$') from emp;

    - 获取字符串长度 char_length()

        select enamr,char_length(ename) from emp;

    - 获取字符串在另一个字符串出现的位置

        1. instr('字符串','要查询的')

            select instr('ethan','t');

        2. locate(substr,str)

            select locate('t','Ethan');

    - 插入字符串insert(str,start,length,newstr)

            select insert('ethan',1,4,'m');

    - 转大写 upper()

            select upper('abc');

    - 转小写 lower()

            select lower('ABC');

    - 去除两端空白 trim

            select trim('       abc ');

    - 截取字符串

        1.左侧开始截取 left()

            select left('tom',2);

        2.右侧开始截取

            select right('tom',2);

        3.全局截取 substring(str,start)/substring(str,start,length);

            select substring('tom',1);

            select substring('tom',1,2);

    - 重复 repeat()

            select repeat('tom',2);

    - 替换 replace()

            select replace('tmo','t','T');

    - 反转 reverse()

            select reverse('cba');

数学相关的函数

    1.向下取整

        select floor(3.1456);

    2.四舍五入

        select round(3.45);

    3.round(num,m);

        select round(22.879,2);

    4.非四舍五入

        select truncate(22.873,2);

    5.随机数rand()

        select floor(rand()*3+3);

分组查询

    - group by 字段名

        select deptno,avg(salary) from emp group by deptno;

    - 多字段分组查询

        select deptno,mgr,count(*) from emp where mgr is not null group by deptno,mgr;

having

    1.having后面可以写普通字段的条件,也可以写聚合函数的条件,但是不推荐在having后面写普通条件

    2.where后面不可以写聚合函数

    3.having 要结合group by 分组查询使用

子查询/嵌套查询

    select * from emp where salary > (select avg(salary) from emp);

关联查询:一次查询多张表

    select e.name,d.name from emp e,dept d where e.deptno = d.deptno;

等值连接和内连接

    1.等值连接: select * from a,b where a.x = b.x and a.age=18;

    2.内连接: select a.* from a join b on a.id = b.id where a.age=18;

    3.外连接

        - 左外连接: select a.*,b.* from a left join b on a.id = b.id and where age = 18;

        - 右外连接: select a.*,b.* from a right join b on a.id = b.id where a.age = 18;

        - 查询 a,b两张表的数据,如果查询量两张表的交集数据使用内连接或等值连接,如果查询某一张表的全部数据和另一张表的交集数据则用外连接

表设计之关联关系

    - 什么是外键:主键是用于表示数据唯一性的字段,外键是用来建立关系的字段,值通常指向另一张主键的表

    - 一对一关系:有A,B两张表,A表中一条数据对应B表中的一条数据,称为一对一关系.应用场景用户表和用户信息扩展表,商品表信息扩展表

    - 一对多:AB两张表,A表中的一条数据对应B表数据中的多条数据,同时B表中的一条对应A表中的一条数据.应用场景:员工表和部门表,商品表和商品分类表

    - 多对多:AB两张表,A表中的一条数据对应B表中的多条数据,同时B表中的一条数据对应A表中的多条数据.应用场景:老师表和学生表.

自关联

    - 当前表的外键指向当前表的主键,这种关联方式叫自关联

    - 应用场景:需要保存上下级关系时

连接方式和关联方式

    - 连接方式:包括等值连接,内连接,外连接,是指查询多张表数据使用的查询方式

    - 关联关系:包括一对一,一对多,多对多,是指设计表时,两张表之间存在的逻辑关系

视图

    - 什么是视图:数据库中的表和视图都是其内部的对象,视图可以理解成为一张虚拟的表,视图的本质就是取代了一条SQL语句.

    - 为什么使用视图:因为数据查询需要书写大量的SQL语句,每次书写效率太低,使用视图可以起到重用SQL的作用,视图可以隐藏敏感信息.

    - 创建视图

        - create view 视图名 as 子查询

            - 创建一个10号部门员工的视图

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

    - 从视图中查询数据

        select * from v_emp_10;

    - 创建一个没有工资的视图

        create view v_emp_noSalary as(select empno,ename,job from emp);

视图的分类

    - 简单的分类: 创建视图的子查询不能包含:去重,函数分组,关联查询,可以对视图中的数据进行增删改查

    - 复杂视图:和简单视图相反,只能对视图中的数据进行查询操作

        - 创建一个复杂视图

             create view info as(select avg(salary),count(*),max(salary)from emp);

对简单的视图进行增删改查,操作方式和table一样

    - 插入数据

        insert into v_emp_10(empno,ename,deptno)values(100,'tom',10);

    - 如果插入一条视图不可见,但是原表中却可见的数据称为数据污染

        insert into v_emp_10(empno,ename,deptmno)values(1001,'jim',20);

   - 通过 with check option 解决数据污染

        create view v_emp_20 as(select * from emp where deptno=20)with check option;

        insert into v_emp_20 (empno,ename,deptno)values(1001,'tom',20)//成功

        insert into v_emp_20(empno,ename,deptno)values(1002,'tom',30)//失败

    - 修改和删除视图数据(只能修改删除视图中的数据)

        update v_emp_20 set ename='jim' where ename = 'tom';

        update v_emp_20 set ename='Jim' where deptno = 10;

        delete from v_emp_20 where deptno = 10;

    - 创建或替换视图

        create or replace view v_emp_10 as(select * from emp where deptno = 10 and sal>2000);

    - 删除视图

        drop view 视图名

    - 如果创建视图的子查询中使用了别名,则对视图操作时只能使用别名

        create view v_emp_10 as(select ename as 'name' from emp where deptno = 10);

        select name from v_emp_10;//成功

        select ename from v_emp_10;//失败

    - 视图总结:

        1.视图是数据中的对象,代表一段SQL语句,可以理解为一张虚拟的表.

        2.作用:重用SQL,隐藏敏感信息

        3.分类:简单视图(创建简单视图时不使用去重,函数,分组关联查询,可以对数据进行增删改查)和复杂视图(和简单视图相反,只能对数据进行查询操作)

        4.插入数据时可能出现视图污染,通过 with check option 解决

        5.如果创建视图的子查询中使用了别名,则对视图操作时只能使用别名

        6.删除和修改只能操作视图中存在的数据

约束

    - 什么是约束:约束就是给表字段添加限制的条件

    - 主键约束+自增: 作用:唯一且非空

    - 非空约束: not null

        - 作用:该字段的不能为null

            create table t_null(id int,age int not null);

            insert into t_null values(1,23);//成功

            insert into t_null values(2,null);//失败

    - 唯一约束 unique

        - 作用: 该字段的值不能重复

            create table t_unique(id int,age int unique);

            insert into t_unique values(1,21);//成功

            insert into t_unique values(2,21);//失败

    - 默认约束 default

        - 作用:给字段设置默认值

            create table t_default(id int,age int default 20);

            insert into t_default(id)values(1);//默认值生效

            insert into t_default values(2,null);//默认值不生效

            insert into t_defaule values(3,30);//可以赋其他值

    - 外键约束

        - 外键:用来建立关系的字段称为外键

        - 外键约束:添加外键约束的字段,值不能为null,可以重复,但是值不能是关联表中不存在的数据

    - 如何使用外键约束

        1.创建部门表

            create table dept(id int primary key auto_increment,name varchar(10));

        2.创建员工表

            create table emp(id int primary key auto_incement,name varchar(10),dept_id int,constraint fk_dept foreign key(dept_id)references dept(id));

        - 格式: constraint 约束名称; foreign(外字段名)references 依赖的表名(依赖的字段名)

        - 测试插入数据

            insert into dept values(null,'神界'),(null,'魔界');

            insert into emp values(null,'赛利亚',1),(null,'卢克',2);

            insert into emp values(null,'卢克',3);//失败

            delete from dept where id=1;//失败

            drop table dept;//失败

    - 由于添加外键约束会影响测试效果,所以工作中很少使用,一般都是通过Java代码实现逻辑外键

索引

    - 什么是索引: 索引是数据中来提高查询效率的技术,类似于目录.

    - 为什么使用索引:如果不使用索引会零散的保存在磁盘块中,查询数据需要挨个遍历每一个磁盘块,直到找到数据为止,使用索引后会见磁盘块以树状结构保存,查询数据时会大大减低磁盘块的访问量,从而提高查询效率

    - 有索引就一定好吗?:如果表中的数据很少,是用索引反而会减低查询效率

    - 索引是越多越好?:不是,索引会占用储存磁盘空间,只针对查询时常用的字段创建索引

    -  如何创建索引

    -  格式: create index 索引名 on 表名(字段名(字符长度));

          create index syin on info(name);

    - 索引分类

        1.聚集索引:通过主键创建的索引称为聚集索引,聚集索引中保存数据,只要给表添加主键,则会自动创建聚集索引

        2.非聚集索引:通过非主键段创建的索引成为非聚集索引,非聚集索引 中没有数据

    - 如何查看索引

        格式: show index from 表名;

    - 删除索引

        格式: drop index 索引名 on 表名;

    - 复合索引: 通过多字段穿件的索引成为复合索引

        格式:create index 索引名 on 表名(字段1,字段2);

        create index syin on info(id,name);

    - 索引总结

        1.索引是用来提高查询效率的技术,类似目录

        2.因为索引会占用磁盘空间,所以不是越多越好

        3.因为数据量小的时候使用索引会减低查询效率所以不是有索引就一定好

        4.分类:聚集索引和非聚集索引

        5.通过多个字段创建的索引称为复合索引

事务

    - 数据库中执行统一业务多条SQL语句的工作单元,可以保证全部执行成功或者全部执行失败

    - 事务的ACID特性

        - ACID是保证数据库事务正确执行的四大基本要素

            1.Atomicty:原子性,最小不可拆分,保证全部成功或者全部失败

            2.Consistency:一致性,保证事务从一个一致状态到另外一个一致状态

            3.Isolation:隔离性,多个事务间互补影响

            4.Duablity:持久性,事务提交之后数据保存到数据库文件中持久生效

    - 事务相关SQL

        - 开启事务 ：begin;

        - 回滚事务 ：rollback;

        - 提交事务 ： commit;

        - 标记事务：savepoint s1;

        - 回滚到标记事务 ：rollback to s1;

组连接 group_concat()

    - 查询每一个部门所有员工的姓名和工资

        select deptno,group_concat(ename,'-',sal)from emp group by deptno;

如何不使用SQL语句修改字符集和端口号

     在mySQL的安装目录下找到my.ini配置文件,在配置文件中的[client] 下找到 port = 修改端口号, default-charset= 修改默认字符集.如果知道在哪里找到这个配置文件,可以打开mySQL终端输入 show variabels like '%data%'; data文件的路径,复制此路径粘贴到我的电脑地址栏回车,然后退一级目录就可以找到my.ini配置文件;Linux系统的配置文件名称为my.cnf

数据库的存储过程

    1.存储过程就是存储在数据库服务器中的一组SQL语句,通过在查询中调用一个指定的名称来执行这类SQL语句命令

    2.创建存储过程: create procedure 存储过程名(参数);

    3.存储过程体:create function 存储函数名(参数);

    4. 优点:

        a) 存储过程只在创建时进行编译,以后每次执行存储过程 不需要再重新编译,而一般SQL语句每执行一次就编译一次,因此使用存储过程可以大大提高数据执行速度

        b) 存储过程一次创建便可以重复使用,从而可以减少数据开发人员的工作量

        c) 安全性高,存储过程中可屏蔽底层数据库对象直接访问,使用Execute权限调用存储过程,无需拥有访问底层数据库对象的显示权限

     缺点: 可维护性差,可读性也差

mySQL与oracle的区别

    1. mySQL是自动增长数据类型,插入记录时不需要操作此字段,会自动获得数据值.oracle没有自动增长的类型,需要建立一个自动增长的序列号,插入记录时把序列号的值赋予此字段

    2. 单引号处理:MySQL是可以使用双引号包起字符串的,oracle中只可以使用单引号包起字符串,在插入和修改字符串前必须做单引号的替换,把所有出现的一个单引号替换成两个单引号

    3. 分页查询:MySQL使用的是limit; oracle使用的是rownum

    4. 长字符串处理:oracle对于长字符串的处理,update insert 可以最大操作字符串长度为 <=4000的单字节

    5. 日期字段处理:MySQL日期字段名有date和time; oracle只有date包含年月日时分秒信息

    6. MySQL非空字段允许有null值; oracle 非空字段不允许有空内容

    7. 注册驱动不同

        a) MySQL driverClass:com.mysql.jdbc.Driver   url: jdbc:mysql://localhost:3306/用户名

        b) oracle driverClass:oracle.jdbc.driver.OracleDriver    url:jdbc:oracle:thin:@127.0.0.1:1521:用户名

    8. 整数字段类型:MySQL 整数字段类型使用 int bigint; oracle 整数字段类型使用 number

如何提高查询数据的速度

   1.查询时不要过多的使用通配符进行查询,要用到几列就查询几列

   2.避免使用不兼容的数据类型,例如:float和int char和varchar binary和varbinary

   3.尽量避免where句子中对字段进行函数或表达是操作,这样会导致引擎放弃使用索引而进行全表扫描

   4.避免使用 != <> ,is null ,is not null, in , not in,这样的操作符,因为这样会使系统无法使用索引,而直接搜索表中的数据

   5.尽量使用数字型字段,一部分开发人员和数据库管理员喜欢把包含数值信息的字段设计为字符型,这样会降低查询和连接性能,并且会增加存储开销,这是因为引擎在处理连接会逐个比较字符串中每个字符,对于数字型而言只需要比较一次就够了

   6.尽量避免在索引字符数据中使用非开头字母搜索,这样使得引擎无法利用索引

   7.充分利用连接条件,在某种情况下两个表不止一个连接条件,在where子句中将连接条件写完整会大大提升查询速度