1.登录MySQL：mysql -u root -p（回车后输入密码，密码以“*”形式输入，增加安全性）

一、DDL

DDL：Data Definition Language，数据定义语言，用来定义数据库对象（数据库，表，字段）。

1.操作数据库

2.操作表

创建表的语法

-- 创建表
create table user(
    id int,
    username varchar(50) ,
    name varchar(10),
    age int,
    gender char(1)
)comment '用户信息表';

约束

非空：not null

唯一：unique

主键：primary key

默认：default

主键自增：auto_increment

create table user(
    id int primary key auto_increment,
    username varchar(50) not null unique ,
    name varchar(10) not null,
    age int,
    gender char(1) default '男'
)comment '用户信息表';

SQL数据类型

主要分为数值类型，字符串类型，日期时间类型。

数值类型常用的有：

  1. tinyint：表示范围（0-255）

  2. int：表示范围 （0，42亿）

  3. bigint：表示范围（0，264-1）

注：加unsigned表示无符号类型，不加默认为有符号类型

字符串类型常用的有：

  1. char：定长字符串，大小为0-255bytes，适用于长度固定的字符串

  2. varchar：变长字符串，大小为0-65535bytes，适用于长度不定的字符串

日期时间类型常用的有：

  1. date：表示日期值，格式为YYYY-MM-DD。

  2. datetime：表示混合日期和时间值，格式为YYYY-MM-DD HH-MM-SS

设计表案例

-- 案例：设计员工表
create table emp(
    id int unsigned primary key auto_increment,
    username varchar(20) unique not null ,
    password varchar(30) default '123456',
    name varchar(10) not null,
    gender tinyint not null comment '1 男 2 女',
    phone int unique not null ,
    position tinyint unsigned,
    money int ,
    image varchar(255),
    start_data date,
    create_time datetime,
    update_time datetime
) comment '员工表';

表操作--查询，修改，删除

语法格式

实例

-- 查询当前数据库所有表
show tables;

-- 查看表结构
desc emp;

-- 查询建表语句
show create table emp;

-- 添加字段QQ
alter table emp add QQ varchar(13) comment 'QQ号码';

-- 修改字段类型为qq varchar(15)
alter table emp modify QQ varchar(15);

-- 修改字段名QQ -> QQ_name
alter table emp change QQ QQ_name varchar(15);

-- 删除字段QQ_name
alter table emp drop column QQ_name;

-- 修改表名
alter table emp rename to employee;

-- 删除表
drop table employee;

DML(数据操作语言，Data Manipulation Language)

用于对数据库中的数据记录进行增删改操作。

DML-insert

格式语法

实例

-- 指定字段添加数据
insert into user(username, name, age, gender) values ('aaa','AAA',18,1);

-- 全部字段添加数据
insert into user values ('2','bbb','BBB',23,2);

-- 批量添加数据（指定字段）
insert into user(username, name, age, gender) values
            ('ccc','CCC',18,1),
            ('ddd','DDD',20,2);

-- 批量添加字段（全部字段）
insert into user values (5,'eee','EEE',25,1),
                        (6,'fff','FFF',27,2);

DML-update

实例

-- DML : 更新数据 update
-- 1.将user表中ID为1的用户名更新为zhangsan，姓名更新为张三
update user set username = 'zhangsan' , name='张三' where id=1;

-- 2.将user表的所有员工的年龄改为30
update user set age = 30;

DML-delete

语法格式

实例

-- DML : 删除数据 delete(delete只能删除一行或整个表的数据，无法删除单个字段，若想删除单个字段可用update将数据修改为null)
-- 删除表中id为1的数据
delete from user where id = 1;

-- 删除user表中的所有user
delete from user;

DQL

Date Query Language--数据查询语言

1.基本查询

语法格式

实例

--  =================== DQL: 基本查询 ======================
-- 1. 查询指定字段 name,entry_date 并返回
select name, entry_date from emp;

-- 2. 查询返回所有字段
select id,username,password,name,gender,phone,job,salary,image,entry_date,create_time,update_time from emp;

-- 3. 查询所有员工的 name,entry_date, 并起别名(姓名、入职日期)
select name 姓名,entry_date 入职日期 from emp;

-- 4. 查询已有的员工关联了哪几种职位(不要重复)
select distinct job from emp;

2.条件查询

语法格式

实例

--  =================== DQL: 条件查询 ======================
-- 1. 查询 姓名 为 柴进 的员工
select * from emp where name = '柴进'

-- 2. 查询 薪资小于等于5000 的员工信息
select * from emp where salary <= 5000;

-- 3. 查询 没有分配职位 的员工信息
select * from emp where job is null;

-- 4. 查询 有职位 的员工信息
select * from emp where job is not null;

-- 5. 查询 密码不等于 '123456' 的员工信息
select * from emp where password != '123456';

-- 6. 查询 入职日期 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间的员工信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01';

-- 7. 查询 入职时间 在 '2000-01-01' (包含) 到 '2010-01-01'(包含) 之间 且 性别为女 的员工信息
select * from emp where entry_date between '2000-01-01' and '2010-01-01' and gender = 2;

-- 8. 查询 职位是 2 (讲师), 3 (学工主管), 4 (教研主管) 的员工信息
select * from emp where job in (2,3,4);

-- 9. 查询 姓名 为两个字的员工信息
select * from emp where name like '__';

-- 10. 查询 姓 '李' 的员工信息
select * from emp where name like '李%';

-- 11. 查询 姓名中包含 '二' 的员工信息
select * from emp where name like '%二%';

注：

3.分组查询

1.聚合函数

将一列数据作为一个整体，进行纵向计算。

--  =================== DQL: 分组查询 ======================
-- 聚合函数

-- 1. 统计该企业员工数量 -count
-- 有三种表示方法：
-- 1.count(字段)  2.count(*)  3.count(常量)
select count(id) from emp;

select count(*) from emp; -- 最推荐

select count(1) from emp;
-- 2. 统计该企业员工的平均薪资
select avg(salary) from emp;

-- 3. 统计该企业员工的最低薪资
select min(salary) from emp;

-- 4. 统计该企业员工的最高薪资
select max(salary) from emp;

-- 5. 统计该企业每月要给员工发放的薪资总额(薪资之和)
select sum(salary) from emp;

-- 分组 -group By
-- 1. 根据性别分组 , 统计男性和女性员工的数量
select gender,count(*) from emp group by gender;

-- 2. 先查询入职时间在 '2015-01-01' (包含) 以前的员工 , 并对结果根据职位分组 , 获取员工数量大于等于2的职位
select job,count(*) from emp where entry_date <= '2015-01-01' group by job having count(*) >= 2;

4.排序查询

order by 默认为升序（asc），若需降序则需单独指定desc

--  =================== 排序查询 ======================
-- 1. 根据入职时间, 对员工进行升序排序     默认为升序asc，若为降序则需指定desc
select * from emp order by entry_date;
select * from emp order by entry_date asc;

-- 2. 根据入职时间, 对员工进行降序排序
select * from emp order by entry_date desc;

-- 3. 根据 入职时间 对公司的员工进行 升序排序 ， 入职时间相同 , 再按照 更新时间 进行降序排序
select * from emp order by entry_date ,update_time desc;

5.分页查询

--  =================== 分页查询 ======================
-- 1. 从起始索引0开始查询员工数据, 每页展示5条记录
select * from emp limit 0,5;
select * from emp limit 5;

-- 2. 查询 第1页 员工数据, 每页展示5条记录
select * from emp limit 5;

-- 3. 查询 第2页 员工数据, 每页展示5条记录
select * from emp limit 5,5;

-- 4. 查询 第3页 员工数据, 每页展示5条记录
select * from emp limit 10,5;

-- 小结：索引 = （页码 - 1） * 每页展示的记录

预编译SQL

优点

1.防止SQL注入

2.性能更高：执行相同功能语句时，只需编译一次。

实战

使用静态SQL

当输入为正确的username和password时，可以正确进入系统。

当输入用户名为ramdon，密码为' or '1' = '1时，也可以正确进入系统，此即为SQL注入。

使用预编译SQL

当username和password不正确时，无法进入系统。其原理为使用预编译的SQL语句，在为其占位符赋值时会将特殊符号转义，从而使上述案例中的密码' or '1' = '1失效，进而达到预防SQL注入的功能。