MySql常用语句

create table tb_student(

     id int(11),

     stu_name varchar(50),

     stu_sex bit,

     stu_hobby varchar(50),

     stu_age smallint

);

-- 注意：最后一列没有逗号

-- 列与列之间以半角逗号相隔

1.2.3 实例创建一个学生表tb_student并指定编码和存储引擎

create table student(

     id int(11),

     stu_name varchar(50),

     stu_sex bit,

     stu_hobby varchar(50),

     stu_age smallint

)default charset=UTF8, ENGINE=INNODB;

default charset 用于指定表中数据的字符集编码方式

engine 用于指定表的存储引擎

1.3 创建约束

1.3.1 设置主键约束

-- auto_increment 自动增长

--primary key 主键约束

create table tb_class(

id int(11) auto_increment, -- id是主键列，不用显示插入值让其自动增长

class_name varchar(30) not null, -- not null 非空约束

class_desc varchar(100), -- 班级描述

primary key (id) -- 为tb_class表的id列设置主键约束

)ENGINE=INNODB,DEFAULT CHARSET UTF8;

注意： MySQL 数据库 auto_increment自动增长和主键primary key 是配套的不能单独使用

1.3.2 设置非空约束

-- not null 非空约束

create table tb_class(

id int(11) auto_increment, -- id是主键列，不用显示插入值让其自动增长

class_name varchar(30) not null, -- not null 非空约束

class_desc varchar(100), -- 班级描述

primary key (id) -- 为tb_class表的id列设置主键约束

)ENGINE=INNODB,DEFAULT CHARSET UTF8;

1.3.3 设置唯一约束

制定一个规则，让表的某一列数据必须唯一

特征：某列数据可以为空，但是必须唯一

例如：学生的手机号码

-- unique

create table tb_1(

phone int unique

)

1.3.4 设置默认约束

为某一列制定一个默认规则

例如：性别默认为 0

--DEFAULT

create table tb_1(

phone int unique,

sex int DEFAULT 0

)

1.3.5 设置外键约束

外部的关键字叫做外键，通常为多张表中建立联系，确保表与表之间的数据安全性，一致性，能够减少数据冗余(重复的数据)。

前面几个约束：在一张表建立约束(规则)

外键约束：多张表之间建立约束(关联，联系)

场景：创建一个tb_student表，为其添加默认约束、唯一约束、非空约束、外键约束

foreign key (class_id) references tb_class(id)

--将表中的班级id（class_id）关联到班级表的id

create table tb_student(

id int(11) auto_increment,

stu_name varchar(50) not null,

stu_mobile varchar(20) unique, -- unique 唯一约束

stu_sex bit default 1, -- 默认约束性别默认为1 0女 1男

class_id int(11) not null,

primary key (id),

-- 学生表的class_id建立外键去关联 tb_class表的主键

foreign key (class_id) references tb_class(id)

)ENGINE=INNODB,DEFAULT CHARSET UTF8;

1.4 表中增加字段

添加列，例如：向student表新增加一列stu_height学生身高

alter table <表名称> add column <列名称> <数据类型>;

alter table student add column stu_height int(11);

1.5 表中添加数据

1.5.1 单个添加

-- insert into 表名称 values (值1,值2,……);

insert into student values(101,'老王',0,'足球',31);

-- 注意：在插入所有的数据时values后面的括号必须填满所有列，表中定义了5列，则必须插入5列数据，否则会报错。

-- 注意：student表后面没有指定列名称，那么在插入数据的时候，数据的类型必须跟定义表结构类型保持一致

1.5.2 批量添加

-- 行与行之间使用半角逗号分离，value关键字只定义一次

insert into student(id,stu_name,stu_sex,stu_hobby,stu_age)

values

(108,'老王',1,'Basketball',39),(1031,'老张',1,'Basketball',39);

2 删除

2.1 删除数据库

2.1.1 方法一

drop database <数据库名称>;

drop database test;

2.1.2 方法二

drop database if exists <数据库名称>;

——删除之前先判断存在才删除

drop database if exists test;

2.2 删除表

2.2.1 直接删除

drop table <表名称>;

drop table student;

2.2.2 删除之前先判断表是否存在

-- drop table if exists <表名称>;

-- 刪除之前先做校验，表在数据库中存在才删除

drop table if exists student;

2.3 删除表中的一个字段

删除某一个列，例如：删除student表中的学生身高这个字段

-- alter table <表名称> drop column <列名称>;

alter table student drop column stu_height;

2.4 删除表中的数据

2.4.1 删除一行数据

-- delete from <表名称> where 条件;

-- 例如：删除id为108的student数据

delete from student where id = 108;

2.4.2 删除表中的所有数据

-- delete from <表名称> ;

-- 例如：删除student表所有数据

delete from student;

3 修改

3.1 修改表中字段名称和数据类型

修改列名称和数据类型：例如修改stu_sex 为 student_sex 数据类型修改为 varchar(30)

-- alter table <表名称> change column <原列名称> <新列名称> <数据类型>;

-- 修改stu_sex 为 student_sex 数据类型修改为 varchar(30)

alter table student change column stu_sex student_sex varchar(30);

3.2 修改某一字段的数据类型，将学生年龄修改为int类型

-- alter table <表名称> modify column <原来的列名称> <新的数据类型>;

-- 例如：

alter table student modify column stu_age int(11);

3.3 修改表中的数据

3.3.1 修改某一字段对应的所有行的值

-- update <表名称> set 列名称 = 值;

-- 例如：student表的年龄全部修改为50

update student set stu_age = 50;

3.3.2 修改某一行数据中某一字段对应的值

-- 例如：我只想修改id为1031的年龄为60
-- where 表示行过滤
-- where id=1031 表示只选择id为1031的行进行修改
-- 下面代码执行步骤：
-- 首先执行 update student 确定修改那张表
-- 然后执行 where 条件确定修改表中的哪一行，不满住条件的行都会被过滤掉

update student set stu_age = 60 where id = 1031;

3.3.3 修改某一行中，多个字段对应的值

--修改id是1031这一行中的，stu_age=60，name的值为张三。

update student set stu_age = 60，name='张三' where id = 1031;

4 查询

4.1 查看表结构

4.1.1 第一种方式查看表结构

desc <表名称>;

desc student;

4.1.2 第二种方式查看表结构

-- show create <表名称>;

show create table tb_student;

4.1.3 查看数据库里的所有表

show tables;

4.2 单表数据简单查询

4.2.1 查询表的所有数据

select * from <表名>;

select * from emp;

4.2.2 查询表中指定字段的数据

select <字段名>, <字段名>,<字段名>,<字段名> from <表名>;

select empno, ename,sal,job from emp;

4.2.3 使用算术表达式查询数据

--查询emp表中，字段 empno，ename，sal的值，并且将sal*12+1000。

select empno, ename,sal, sal*12 +1000 from emp;

4.2.4 使用字段别名查询

重命名查询结果中的字段，以增强可读性

别名如果含有空格或其他特殊字符或大小写敏感，需用双引号引起来。

AS可以省略

select empno as 员工编号, ename 员工姓名, sal*12 年薪 from emp;

select empno, ename "Ename", sal*12 "Anual Salary" from emp;

select sal*12+5000 as "年度工资(加年终奖)" from emp;

4.2.5 去重查询

缺省情况下，查询结果中包含所有符合条件的记录行，包括重复行

select deptno from emp;

使用DISTINCT关键字可从查询结果中清除重复行

select distinct deptno from emp;

DISTINCT的作用范围是后面所有字段的组合

select, distinct deptno job from emp;

4.2.6 排序查询

使用order by 子句对查询结果进行排序

排序方式包括升序（asc，缺省）和降序（desc）两种：

select <字段一>,<字段二>,<字段三> from <表名> order by <字段三>;

select <字段一>,<字段二>,<字段三> from <表名> order by <字段三> desc;

select empno, ename, sal from emp order by sal;

select empno, ename, sal from emp order by sal desc ;

按多字段排序

select deptno, empno, ename, sal from emp order by deptno, sal;

使用字段别名排序

select empno, ename, sal*12 annsal from emp order by annsal;

4.3 分页查询

关键字：limit

4.3.1 查询前3条客户信息

select * from emp limit 3

小结：limit 后面跟1个参数，例如 limit 3 此时表示最多返回3行

4.3.2 查询第3条到第6条的客户信息

limit 参数1 , 参数2

参数1：表示伪列(index)的偏移量(下标从0开始)
参数2：表示返回最大限制的行数

select * from emp limit 3,6

4.4 合并查询

4.4.1 作用

将多个select语句联合(合并)为一个select语句，涉及的关键字union 和union all。

4.4.2 案例

union all 不管是否重复，全部合并

select * from emp where DEPTNO=10

union all

select * from emp where DEPTNO=20

union 如果有重复的，过滤掉重复的

select * from emp where DEPTNO=10

union

select * from emp where DEPTNO=20

4.5 条件查询

4.5.1 查询单个字段满足对应条件的数据

select * from emp where deptno=10; --查询emp表中，deptno=10的数据

select * from emp where ename = 'JACK'; --查询emp表中，ename=JACK的数据

select * from emp where hiredate = '2020-12-12'; --查询emp表中，hiredate=2020-12-12的数据

注意：

字符串和日期值要用单引号扩起来

字符串大小写敏感

日期值格式敏感，缺省的日期格式是'YYYY-MM-DD HH:mm:ss'

4.5.2使用比较运算符查询

MySQL支持如下比较运算符： > >= < <= != <>

注意：MySQL使用=运算符来判断表达式是否相等，它没有==

Java使用!=表示不等于，MySQL也支持。但是我们不要使用，效率太低了

MySQL使用的不等于使用<>

select * from emp where sal > 2900;

select * from emp where deptno <> 30;

select * from emp where sal between 1600 and 2900;

select * from emp where ename in('SMITH','CLARK','KING');

4.5.3 模糊查询

使用like操作符利用通配符查询，like的作用是指示mysql后面的搜索模式是利用通配符，而不是直接相等匹配进行查询；如果like后面没有出现通配符，则在sql执行优化时将like默认为“=”执行。

% 通配符： % 表示零或多个字符

'***%' :查询以***开头的数据

select * from emp where ename like 'S%';

'%***':查询以***结尾的数据

select * from emp where ename like '%S';

'%***%:查询含有***的数据

select * from emp where ename like '%S%';

_ 表示单个字符，

查询含有*A.......的数据

select * from emp where ename like '_A%';

对于特殊符号可使用ESCAPE 标识符来查找

select * from emp where ename like '%\_%' escape '\';

4.5.4 空值判断

使用is null 运算符进行空值判断

select * from emp where comm is null;

select * from emp where comm is not null;

4.5.5 使用逻辑运算符查询

and ，or，not in

select * from emp where deptno = 10 and sal > 1000;

select * from emp where deptno = 10 or job = ‘CLERK’;

select * from emp where sal not in (800, 1500, 2000);

4.6 连接查询

4.6.1 交叉连接

关键字：cross join

语法：select <字段名> from <表名1> cross join <表名2>;

select dept.deptno,dname,ename from dept cross join emp;

交叉连接在返回左侧表中的每个行时，会例出右侧表中的每个行。如果一个表中有1000行，另一个表有500行，则结果集返回1000*500个行，总共500,000个行。

4.6.2 自然连接

自然连接（NATURAL JOIN）是一种特殊的等价连接，它将表中具有相同名称的列自动进行记录匹配。自然连接不必指定任何同等连接条件。

关键字：natural join

语法：select <字段名> from <表名1>natural join <表名2>;

select empno, ename, sal, deptno, dname from emp natural join dept

4.6.3 using 子句

如果不希望参照被连接表的所有同名列进行等值连接，自然连接将无法满足要求，可以在连接时使用USING子句来设置用于等值连接的列（参照列）名。

using子句引用的列在sql任何地方不能使用表名或者别名做前缀

关键字：join <表名2> using (字段名)

语法：select <字段名> from <表名1> join <表名2> using (字段名)

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

       from emp e join dept d

    using(deptno)

       where deptno=101

4.6.4 on子句

自然连接的条件是基于表中所有同名列的等值连接为了设置任意的连接条件或者指定连接的列，需要使用ON子句连接条件与其它的查询条件分开书写使用ON 子句使查询语句更容易理解

关键字：join table2 on (table1.column_name = table2.column_name)

语法：select <字段名> from <表名1> join table2 on (table1.column_name =table2.column_name)

1----select ename,dname

   from emp inner join dept on   emp.deptno=dept.deptno

   where emp.deptno=30;

2---select empno, ename, sal, emp.deptno, dname

     from emp inner join dept

      on (emp.deptno = dept.deptno and sal>5000);