MySQL入门学习的第一节(SQL语句)
SQL语句🤔
以及SQL语句的分类:
DDL (定义)🤔
① 对其数据库的操作
② 对其表的创建
③ 数据类型
④ 对其表的修改
增加字段:
修改数据类型:
表的删除:
DML(操作)🤔
① 添加数据
先创建一个表user:
代码举例增加数据:
a.给指定字段添加数据
insert into user(id, `work-no`, name, gender, age, `id-card`, `entry-date`) values (1,'1','jack','男',18,'123456789123456789','2002-09-27');
运行结果为:
注:
若出现 gender(男) 报错,可以去将gender列的排序规则改为 utf8_bin
b.给全部字段添加数据
insert into user values (2,'2','mary','女',19,'121111119123456789','2004-02-27');
运行结果为:
c.添加多条数据
和上面代码类似,只不过多条数据之间用 , 隔开
② 修改数据
举例:
# 1. 修改id=1的数据,将name修改为 mamad;
update user set name = 'mamad' where id = 1;
# 2.修改id=2的数据,将name修改为小李,gender修改为 男;
update user set name = '小李',gender = '男' where id = 2;
# 3.将所有员工的入职时间日期修改为 2008-09-21;
update user set `entry-date` = '2008-09-21';
修改之前的表:
修改之后的表:
③ 删除数据
举例:
# 4.删除age=19的员工;
delete from user where age = 19;
# 5.删除表中所有的员工;
delete from user;
4.运行结果为:
其中删除表中所有员工delete from 和drop 是完全不一样的;
delete from | drop |
---|---|
删除表中的全部员工数据 | 删除表 |
DQL(查询)🤔
① 基本查询
首先搭建表的环境(多组数据):
以此代码为例,来举例掌握其查询语句
create table emp(
id int comment '编号',
workno varchar(10) comment '工号',
name varchar(10) comment '姓名',
gender char(1) comment '性别',
age tinyint unsigned comment '年龄',
idcard char(18) comment '身份证号',
workaddress varchar(50) comment '工作地址',
entrydate date comment '入职时间'
)comment '员工表';
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (1, '00001', '柳岩666', '女', 20, '123456789012345678', '北京', '2000-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (2, '00002', '张无忌', '男', 18, '123456789012345670', '北京', '2005-09-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (3, '00003', '韦一笑', '男', 38, '123456789712345670', '上海', '2005-08-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (4, '00004', '赵敏', '女', 18, '123456757123845670', '北京', '2009-12-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (5, '00005', '小昭', '女', 16, '123456769012345678', '上海', '2007-07-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (6, '00006', '杨逍', '男', 28, '12345678931234567X', '北京', '2006-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (7, '00007', '范瑶', '男', 40, '123456789212345670', '北京', '2005-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (8, '00008', '黛绮丝', '女', 38, '123456157123645670', '天津', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (9, '00009', '范凉凉', '女', 45, '123156789012345678', '北京', '2010-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (10, '00010', '陈友谅', '男', 53, '123456789012345670', '上海', '2011-01-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (11, '00011', '张士诚', '男', 55, '123567897123465670', '江苏', '2015-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (12, '00012', '常遇春', '男', 32, '123446757152345670', '北京', '2004-02-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (13, '00013', '张三丰', '男', 88, '123656789012345678', '江苏', '2020-11-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (14, '00014', '灭绝', '女', 65, '123456719012345670', '西安', '2019-05-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (15, '00015', '胡青牛', '男', 70, '12345674971234567X', '西安', '2018-04-01');
INSERT INTO emp (id, workno, name, gender, age, idcard, workaddress, entrydate)
VALUES (16, '00016', '周芷若', '女', 18, null, '北京', '2012-06-01');
对其进行操作以及运行结果如下:
A
B
C
其中 as 是可以省略的!!!
D
② 条件查询
以上面数据表为例,举几个例题来熟悉其条件查询的用法,可自行在datagrip上运行:
-- 条件查询
# 1. 查询年龄等于88的员工
select * from emp where age = 88;
# 2.查询年龄小于20的员工
select * from emp where age < 20;
# 3.查询年龄小于等于20的员工
select * from emp where age <= 20;
# 4,查询没有身份证信息的员工
select * from emp where idcard is null;
# 5,查询有身份证信息的员工
select * from emp where idcard is not null;
# 6,查询年龄不等于88的员工
select * from emp where age != 88;
select * from emp where age <> 88;
# 7,查询年龄在15(包含)到20(包含)直接的员工
select * from emp where age>=15 and age<=20;
select * from emp where age>=15 && age<=20;
select * from emp where age between 15 and 20;
# 8.查询性别为女且年龄小于25的员工
select * from emp where gender = '女' and age <25;
# 9.查询年龄为18或者20或者40的员工
select * from emp where age=18 or age=20 or age=40;
select * from emp where age in (18,20,40);
# 10.查询姓名为两个字的员工
select * from emp where name like '__';
# 11.查询身份证最后一位为X的员工
select * from emp where idcard like '%X';
select * from emp where idcard like '_________________X';
③ 聚合函数
以上面数据表为例,举几个例题来熟悉其条件查询的用法,可自行在datagrip上运行:
-- 聚合函数
# 1,统计该企业员工数量;
select count(idcard) from emp;
select count(*) from emp;
# 2.统计该企业员工的平均年龄;
select avg(age) from emp;
# 3.统计该企业员工的最大年龄;
select max(age) from emp;
# 4,统计该企业员工的最小年龄;
select min(age) from emp;
# 5,统计西安地区员工的年龄和
select sum(age) from emp where workaddress = '西安';
④ 分组查询
以上面数据表为例,举几个例题来熟悉其条件查询的用法,可自行在datagrip上运行:
-- 分组查询
# 1.根据性别分组,统计男员工和女员工的数量;
select gender , count(*) from emp group by gender;
# 2,根据性别分组,统计男员工和女员工的平均年龄;
select gender,avg(age) from emp group by gender;
# 3,查询年龄小于45的员工,按工作地址分组,获取员工数量大于3的工作地址
select workaddress,count(*) from emp where age <45 group by workaddress having count(*)>3;
select workaddress,count(*) address_count from emp where age <45 group by workaddress having address_count>3;
⑤ 排序查询
以上面数据表为例,举几个例题来熟悉其条件查询的用法,可自行在datagrip上运行:
-- 排序查询
# 1.根据年龄对公司员工进行排序
select * from emp order by age asc ; # 升序(默认值)
select * from emp order by age desc ; # 降序
# 2.根据入职时间,对员工进行降序排序
select * from emp order by entrydate desc ;
# 3.根据年龄对公司的员工进行升序排序,年龄相同,在按照入职时间进行降序排序
select * from emp order by age asc,entrydate desc;
⑥ 分页查询
以上面数据表为例,举几个例题来熟悉其条件查询的用法,可自行在datagrip上运行:
-- 分页查询
# 1.查询第一页员工数据,每一页展示10条记录
select * from emp limit 0,10;
select * from emp limit 10;
# 2.查询第二页员工数据,每页展示10条记录 -----> (页码-1)*页展示记录数
select * from emp limit 10,10;
DCL(控制)🤔
以上面数据表为例,举几个例题来熟悉其条件查询的用法,可自行在datagrip上运行:
-- 用户管理
# 1.创建用户 itcast 只能在当前主机localhost访问,密码为123456
create user 'itcast'@'localhost' identified by '123456';
# 2.创建用户 jackson 可以在任意主机访问数据库,密码为123123
create user 'jackson'@'%' identified by '123123';
# 3.修改用户jackson的访问密码为1234;
alter user 'jackson'@'%' identified with mysql_native_password by '1234';
# 4.删除itcast@localhost用户
drop user 'itcast'@'localhost';
以上就是对MySQL的SQL语句的大概讲解,感谢观看。🥰
新手小白,有何不妥,请您提出,还需努力!!!
👍+✏️+⭐️ 是对博主最大的鼓励与支持!!!