- 创建用户定义的数据库
语法: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;