MySQL基本知识超详解（傻瓜版）持续更新中

• 创建用户定义的数据库

语法：create database (if not exists) <数据库名字>

default charset <字符集名称(utf8)>

default collate <字符序名称>;

• 访问数据库

指定要操作的数据库

语法：use <数据库名字>;

查看MySQL服务器中所有数据库的列表

语法：show databases;

• 修改数据库

更改现有数据库的字符集和字符序

alter database <数据库名字>

default charset <字符集名称>

default collate <字符序名称>;

• 删除数据库

drop database <数据库名字>;

• 了解数据类型

1数值

整数数据类型(tinyint,smallint,mediumint,int,bigint)

小数数据类型(float,double,decimal)

bit数据类型

2字符串

char 固定长度字符数据，快，但不省空间

varchar 可变长度字符数据，慢，但节省空间

tinytext

text

mediumtext

longtext

binary

varbinary

tinyblob

blob

mediumblob

longblob

3.日期

date 日期数据

datetime 日期和时间数据

timestamp 时间戳，使用系统的当前日期和时间值进行更新的日期和时间值，只要更新了记录或将记录插入表中

year

time

• 创建表

语法：create (temporary) table (if not exists) <表名>

(

<列名> <数据类型> (not null/null) (default <默认值>),

<列名> <数据类型> (not null/null] (default <默认值>),

<列名> <数据类型> (not null/null] (default <默认值>)

) ;

查看表中所有列的详细信息

语法：desc <表名>;

查看以字符p开头的列的详细信息

语法：desc <表名> 'p%'

查看当前数据库中的表

语法：show tables;

• 应用约束

1.主键约束：对一个列或一组列定义，这些列的值唯一地标识表中的所有行

（表一定要有主键，且只有一个主键，用主键去唯一标识某一条数据）[唯一且非空]

语法：create table <表名>

(

<列名> <数据类型> primary key (auto_increment),

<列名> <数据类型> (not null/null] (default <默认值>),

<列名> <数据类型> (not null/null] (default <默认值>)

) ;

2.非空约束[不能为空，可以重复] not null

3.唯一约束[不能重复，可以为空] unique

例：如创建用户表user

User_id 主键自增auto_increment

User_name 非空

Nick_name 不能重复

User_age

Birthday

mysql命名规范：项目名_表名 全小写，多个单词间用_下划线连接(不能用MySQL的关键字)

(mall mall_user,mall_product,mall_order此类为正确的表名)

create table usertb(

user_id int primary key auto_increment,

user_name varchar(20) not null,

nick_name varchar(50) unique,

user_age int,

birthday date

);

4.外键约束：当一个表中的数据依赖于另一个表中的数据时，将使用外键约束维护这些表中数据的一致性。 表中的外键总是引用其他表中的主键列。

==商品表==

create table product (

pro_id int primary key auto_increment,

pro_name varchar(50) not null,

pro_num int ,

price double

);

==客户表customer==

create table customer(

cus_id int primary key,

cus_name varchar(20) not null

);

==订单表 ordertb==

create table ordertb(

order_id int primary key,

cus_id int not null,

pro_id int not null,

order_num int,

foreign key (cus_id) references customer(cus_id),

foreign key (pro_id) references product(pro_id)

);

• 更改表（了解内容）

添加约束

语法：alter table <表名>

add constraint <主键名> primary key <列名>;

添加列

语法：alter table <表名>

add column <列名> <数据类型>

修改列

语法：alter table <表名> alter column <列名> set default '默认值';

删除列

语法：alter table <表名>

drop column <列名>;

重命名表

alter table <表名> rename to <新表名>;

• 操作表数据

插入数据

语法：insert into <表名(指定列)> values(数据1),(数据2);

例子：insert into usertb values(null,'张三','zs',18,'2020-09-23'),(null,'ls',null,20,null);

指定往哪些列插入数据

例子：insert into usertb(user_name,user_age) values('Ethan',18);

修改数据

语法：update <表名> set 修改的列1,修改的列2 where过滤

update usertb set user_name='Ethan' where user_id=2;

例子：将姓名为Ethan的数据的生日改为'2002-2-3'

update usertb set birthday='2002-2-3' where user_name='Ethan';

例子：所有数据的年龄信息改为18

update usertb set user_age=18;

例子：将id为1的用户的昵称改为Ethan,年龄改为20.

update usertb set nick_name='Ethan',user_age=20 where user_id=1;

删除数据

delete

语法：delete from <表名> where过滤;

（不加where子句可以删除表中的所有记录）

例子：删除名字为Ethan的数据

delete from usertb where user_name='Ethan';

truncate

删除所有数据

语法：truncate <表名>;

区别如下

truncate语句	delete from语句
它删除表并重新创建表	它通过逐个删除记录来删除表中的所有记录
此语句的执行速度很快	此语句的执行速度较慢
将auto_increment列的值重置为其开始值	只要执行此语句后将记录插入表中，auto_increment列中的值就会按预期递增
不得将此语句包含在事务中，因为一旦发出此语句，将无法恢复被删除的数据	可将此语句包含在事务中，并且可恢复被删除的数据

删除表

语法：drop table <表名>;

• 查询数据

语法：select [distinct] 指定列 from 表名 where子句

用distinct去重

1.查询指定列

查询所有的员工数据，显示员工ID，姓名，薪资

select id,emp_name,sal from emp;

2.查询所有列（通过*或指定所有列实现）

语法：select * from <表名>;

例子：select id,emp_name,job,top_id,hdate,sal,bonus,dept_id from emp;

3.查询出员工表中的所有职位job(用distinct去重)

例子：select distinct job from emp;

4.查询的同时为列起别名（as关键字可省略）

例子：查询所有的员工数据，显示员工号，员工姓名，姓名列要求显示'姓名'不显示emp_name

select id,emp_name as '姓名' from emp;

select id,emp_name '姓名' from emp;

where子句

比较运算符

=

查询id为1的数据

select * from emp where id=1;

<>

查询id不为1的数据

select * from emp where id<>1;

>=

查询薪资大于等于7000的员工信息

select * from emp where sal>=7000;

算数运算符+-*/%

例子：查询在2021-12-02前入职的员工，给他们的薪资涨1000,显示员工ID，姓名，涨后薪资

select id,emp_name,sal+1000 as '涨后薪资' from emp where hdate<'2021-12-

02';

空或非空

is null

查询没有奖金的员工数据

select * from emp where bonus is null;

is not null

查询有所属部门的员工数据

select * from emp where dept_id is not null;

三类逻辑运算符

or

查询职位为销售或薪资大于5000的员工

select * from emp where job='销售' or sal>5000;

and

查询职位为软件工程师并且薪资大于5000的员工

select * from emp where job='软件工程师' and sal>5000;

not

查询职业不是销售的员工

select * from emp where job<>'销售';

select * from emp where not job='销售';

between x and y [x,y]

查询薪资在5000到10000的员工数据

select * from emp where sal between 5000 and 10000;

like 模糊查询

配合通配符一起使用（_匹配任意1个字符，%匹配任意零至多个字符）

查询姓张的员工数据

select * from emp where emp_name like '张%';

查询姓张的员工且名字是两个字的员工数据

select * from emp where emp_name like '张_';

in(后跟一个集合)

查询id号为1,4,5的员工数据

select * from emp where id in(1,4,5);

limit子句，实现分页查询

语法：limit (要显示的页码 - 1)*页面大小,页面大小;

例子：页面大小为3，如何查询出第一页的数据

select * from emp limit 0,3;

例子：页面大小为3，如何查询出第二页的数据

select * from emp limit 3,3;

例子：页面大小为3，如何查询出第三页的数据

select * from emp limit 6,3;

MySQL函数

1、concat连接列

例子：查询所有员工数据，并将员工的姓名和职位连接成一个字符串显示，起列别名为员工标识

select * concat(emp_name,job) 员工标识 from emp;

如果要以 - 作为连接符则

select * concat(emp_name,'-',job) 员工标识 from emp;

2、substring截取字符串

语法：substring(a,b,c)表示截取

解释：其中第一个参数a表示被截取的参数对象，第二个参数b表示从哪个位置开始截取，第三个参数c表示要截取的长度。

例子：抽取员工姓名中的第一个字符

select substring(emp_name,1,1) from emp;

3、curdate

select curdate(); #获取当前日期

select year(curdate()); #抽取当前日期的年份

例子：查询员工入职了几年

select emp_name,hdate,year(curdate())-year(hdate) 入职年限 from emp;

#查询员工的入职年限

补充：

sysdate() 返回实时的系统日期和时间

curdate() 返回语句执行时的日期

curtime() 返回语句执行时的时间

now() 返回语句执行时的日期和时间，一般在sql中使用now()

sleep(5)是让命令在数据库中等待5秒再执行

4.聚合函数

avg(col)求平均数

例子：查询员工的平均薪资

select avg(sal) from emp;

max(col)求最大值

例子：查询员工的最高薪资

select max(sal) from emp;

min(col)求最小值

查询员工的最低薪资

select min(sal) from emp;

count(col)个数，

例子： count(*)

对表行的数目进行计算，包含null;

count(1)

对表中第一列的值的行数进行计算。count(1)会忽略NULL列;

count(列名)

对表中特定的列的值的行数进行计算，不包含null;

count(distinct 列名)

对表中特定的列的不重复值的个数进行计算;（对值去重并计数）----查寻该列不重复的有几个

count(distinct 列名1,列名2)

对多个字段去重并计数;（最好不要用，性能不好，可以使用join代替，或者分组代替）

sum(col)求和

例子：select sum(rental_rate) from file

排序和分组数据

1.数据排序

排序order by col [asc/desc]

asc表示升序，默认就是asc升序排序

desc表示降序。

例一：查询所有员工数据，按薪资升序排序

select * from emp order by sal;

例二：查询所有员工数据，按薪资降序排序

select * from emp order by sal desc;

例三：查询所有员工数据，按奖金升序排序，如果奖金相同，按照薪资降序排序

select * from emp order by bonus,sal desc;

2.分组数据

分组group by col

例一：查询每个部门的最高薪资

聚合函数，如果不分组则计算这一列所有数据，如果分组了，则会为每一组进行汇总计算

select max(sal) from emp;

select dept_id,max(sal) from emp where dept_id is not null group by dept_id;

null值的处理

查询部门ID不为2的员工数据

select * from emp where dept_id <>2;

处理null值，用ifnull(col,data)

select * from emp where ifnull(dept_id,0) <>2;