增删改查

数据库常用操作

---

0.开关数据库

net stop/start mysql
1.登录格式
1.mysql -u 用户名 -p 密码
2.mysql -u用户名 -p
Enter password: root
2.登录方式
mysql –hip地址 –u用户名 –p密码
3.登录方式
mysql -host=ip地址=用户名 -password=密码

SQL语句

---

概念 –> Structured Query Language

对数据库进行操作的语言

作用

方便操作数据库中的数据,表,数据库
标准SQL是数据库管理系统都需要遵循的规范

SQL语句分类

1.DDL(data Definition Language)数据库定义语言
用来定义数据库对象:数据库,表,列 –> create drop alter
2.DML(Data Manipulation Language)数据库操作语言
数据库中表的增删改查 insert delete update
3.DCL(Transaction Control Language)数据库控制语言
定义数据库的访问权限和安全级别 GRANT REVOKE
4.TCL(Data Query Language)数据库事务控制语言
控制数据库的事务操作 COMMIT SAVEPOINT ROLLBACK
5.DQL(Data Query Language)数据查询语言
非官方的分类

SQL通用语法

可以单行或多行书写,以分号结尾
可使用空格和缩进来增强语句的可读性
不区分大小写,建议关键词使用大写
3中注释: 1 .单行–注释内容 2.#注释内容(mysql特有) 3.多行注释/…/

DDL操作数据库

创建数据库(默认编码utf-8)

1.直接创建
create database 数据库名
2.判断是否存在并创建数据库
create database if not exists 数据库名
3.创建数据库并指定字符集
create database 数据库名 character set 字符集

查询数据库

1.查看所有的数据库
show databases
2.查看某个数据库的定义信息
show create database 数据库名

修改数据库

修改数据库字符集格式
alter database 数据库名 default character set 字符集
删除数据库
drop database 数据库

使用数据库

1.查看正在使用的数据库
select database();
2.使用/切换数据库
use 数据库名

用户操作

创建用户
create user ‘用户名’@’IP地址’ identified by ‘密码’;
删除用户
drop user ‘用户名’@’IP地址’;
修改用户
rename user ‘用户名’@’IP地址’; to ‘新用户名’@’IP地址’;;
修改密码
set password for ‘用户名’@’IP地址’ = Password(‘新密码’)

PS：用户权限相关数据保存在mysql数据库的user表中，所以也可以直接对其进行操作（不建议）
用户权限设置:
show grants for ‘用户’@’IP地址’ – 查看权限
grant 权限 on 数据库.表 to ‘用户’@’IP地址’ – 授权
revoke 权限 on 数据库.表 from ‘用户’@’IP地址’ – 取消权限

DDL操作表

创建表

create table 表名(
字段名1 字符类型1[可选的],
字段名2 字段类型2[约束],
…
);
解释
字段: –> 每个列的列名
在常见的数据类型中,如果是varchar,必须指定长度
每个字段都可以加约束(可省略)
有一个约束 –> 主键约束,被主键约束的键,必须唯一且不可为空

数据类型

bit[(M)]

            二进制位（101001），m表示二进制位的长度（1-64），默认m＝1

           tinyint[(m)] [unsigned] [zerofill]

            小整数，数据类型用于保存一些范围的整数数值范围：
        有符号：
            -128 ～ 127.
        无符号：
            0 ～ 255

        特别的： MySQL中无布尔值，使用tinyint(1)构造。

int[(m)][unsigned][zerofill]

        整数，数据类型用于保存一些范围的整数数值范围：
            有符号：
                -2147483648 ～ 2147483647
            无符号：
                0 ～ 4294967295

        特别的：整数类型中的m仅用于显示，对存储范围无限制。例如： int(5),当插入数据2时，select 时数据显示为： 00002

bigint[(m)][unsigned][zerofill]

大整数，数据类型用于保存一些范围的整数数值范围：

有符号：
-9223372036854775808 ～ 9223372036854775807
无符号：
0 ～ 18446744073709551615

        decimal[(m[,d])] [unsigned] [zerofill]
        准确的小数值，m是数字总个数（负号不算），d是小数点后个数。 m最大值为65，d最大值为30。

        特别的：对于精确数值计算时需要用此类型
               decaimal能够存储精确值的原因在于其内部按照字符串存储。

FLOAT[(M,D)] [UNSIGNED] [ZEROFILL]

        单精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。
            无符号：
                -3.402823466E+38 to -1.175494351E-38,
                0
                1.175494351E-38 to 3.402823466E+38
            有符号：
                0
                1.175494351E-38 to 3.402823466E+38

        **** 数值越大，越不准确 ****

DOUBLE[(M,D)] [UNSIGNED] [ZEROFILL]

        双精度浮点数（非准确小数值），m是数字总个数，d是小数点后个数。

            无符号：
                -1.7976931348623157E+308 to -2.2250738585072014E-308
                0
                2.2250738585072014E-308 to 1.7976931348623157E+308
            有符号：
                0
                2.2250738585072014E-308 to 1.7976931348623157E+308
        **** 数值越大，越不准确 ****


        char (m)
          char数据类型用于表示固定长度的字符串，可以包含最多达255个字符。其中m代表字符串的长度。
            PS: 即使数据小于m长度，也会占用m长度

varchar(m)

           varchars数据类型用于变长的字符串，可以包含最多达255个字符。其中m代表该数据类型所允许保存的字符串的最大长度，只要长度小于该最大值的字符串都可以被保存在该数据类型中。

        注：虽然varchar使用起来较为灵活，但是从整个系统的性能角度来说，char数据类型的处理速度更快，有时甚至可以超出varchar处理速度的50%。

因此，用户在设计数据库时应当综合考虑各方面的因素，以求达到最佳的平衡

text

        text数据类型用于保存变长的大字符串，可以组多到65535 (2**16 − 1)个字符。

#### mediumtext
A TEXT column with a maximum length of 16,777,215 (2**24 − 1) characters.

#### longtext
A TEXT column with a maximum length of 4,294,967,295 or 4GB (2**32 − 1) characters.

#### enum
枚举类型，
An ENUM column can have a maximum of 65,535 distinct elements. (The practical limit is less than 3000.)
示例：
CREATE TABLE shirts (
name VARCHAR(40),
size ENUM(‘x-small’, ‘small’, ‘medium’, ‘large’, ‘x-large’)
);
INSERT INTO shirts (name, size) VALUES (‘dress shirt’,’large’), (‘t-shirt’,’medium’),(‘polo shirt’,’small’);

set

        集合类型
        A SET column can have a maximum of 64 distinct members.
        示例：
            CREATE TABLE myset (col SET('a', 'b', 'c', 'd'));
            INSERT INTO myset (col) VALUES ('a,d'), ('d,a'), ('a,d,a'), ('a,d,d'), ('d,a,d');

DATE

        YYYY-MM-DD（1000-01-01/9999-12-31）

#### TIME
HH:MM:SS（’-838:59:59’/’838:59:59’）

YEAR

        YYYY（1901/2155）

DATETIME

        YYYY-MM-DD HH:MM:SS（1000-01-01 00:00:00/9999-12-31 23:59:59    Y）

TIMESTAMP

        YYYYMMDD HHMMSS（1970-01-01 00:00:00/2037 年某时）

查看表

1.查看某个数据库中的所有表
show tables;
2.查看表结构
desc 表名
3.查看创建表的SQL语句
show create table 表名

快速创建一个表结构相同的表

create table 新表名 like 旧表名

删除表

1.直接删除表
drop table 表名
2.判断是否存在并删除表
drop table if exists 表名

修改表结构

1.添加表列
alter table 表名 add 列名 类型
2.修改列类型
alter table 表名 modify 列名 新类型
3.修改列名
alter table 表名 change 旧列名 新列名 类型
4.删除列
alter table 表名 drop 列名
5.修改表名
rename table 表名 to 新表名
6.修改字符集
alter table 表名 character set 字符集

DML语句

插入记录

1.插入全部字段(全列名添加)
所有字段名全都写出来
insert into 表名 (字段名1, 字段名2,字段名3…) values (值1,值2,值3);
不写字段(全列值添加) –> 不能省略任何值
insert into 表名 values (值1,值2,值3…)

插入部分数据

insert into 表名(字段名1,字段名2…) values(值1,值2…) –>没有数据的字段会使用null
intsert into 表名 –> 向哪张表中添加数据
{字段名1,字段名2…} –> 给那些字段设置值
values(值 1, 值2..) –> 设置具体的值

注意

值与字段必须对应,个数相同,类型相同
值得数据大小必须写在字段的长度范围内
除了数值类型外,其它字段类型的值必须使用引号引起
如果插入null,可以不写字段,或插入null

当我们使用DOS命令行进行SQL语句操作如有有中文会出现乱码，导致SQL执行失败
错误原因:因为MySQL的客户端设置编码是utf8,而系统的DOS命令行编码是gbk，编码不一致导致的乱码
查看 MySQL 内部设置的编码 show variables like ‘character%’;
解决方案：修改client、connection、results的编码为GBK，保证和DOS命令行编码保持一致
1. 单独设置
set character_set_client=gbk;
set character_set_connection=gbk;
set character_set_results=gbk;
2. 快捷设置
set names gbk;
注意：以上2种方式为临时方案，退出DOS命令行就失效了，需要每次都配置

1. 修改MySQL安装目录下的my.ini文件，重启服务所有地方生效。
   此方案将所有编码都修改了[不建议]

蠕虫复制

在已有数据的基础上,将原来的数据进行赋值,插入到对应的表中
insert into 表名1 select * from –> 将表2的数据复制到表1中

更新表记录

1.不带条件修改数据(修改所有数据)
update 表名 set 字段名= 值
2.带条件修改数据
update 表名 set 字段名= 值 where 字段名= 值
3.多列修改
update 表名 set 字段名= 值,字段名= 值 where 字段名= 值

关键字

update:修改数据
set:修改哪些字段
where:指定条件 –> 如果省略,则会对表中的所有数据进行修改

删除表记录

1.不带条件删除数据(删除所有)
delete from 表名
2.带条件修改数据(删除指定的)
delete from 表名 where 字段名=值
3.truncate删除表记录(不能加条件)
truncate table 表名

DQL查询

查询表所有数据
1.使用*表示所有列(全列查询)
select * from 表名
2.写出查询每列的名称(指定列查询)
select 字段名1 , 字段名2…from 表名

别名查询 –> 对查询结果重起一个名字

1. 查询时给列、表指定别名需要使用AS关键字
2. 使用别名的好处是方便观看和处理查询到的数据
   SELECT 字段名1 AS 别名, 字段名2 AS 别名… FROM 表名;
   SELECT * FROM 表名 AS 表别名;
   可以省略as
   select 列名 别名 from 表名;
   select * from 表名 表别名
   别名 –> 重命名无限制

清除重复值

1.查询指定列并且结果不出现重复数据
SELECT DISTINCT 字段名 FROM 表名;

1. 查询结果参与运算

2. 某列数据和固定值运算
   SELECT 列名1 + 固定值 FROM 表名;

3. 某列数据和其他列数据参与运算
   SELECT 列名1 + 列名2 FROM 表名;
   注意: 参与运算的必须是数值类型

条件查询

获取到 满足条件 的数据 语法格式： 

SELECT 字段名 FROM 表名 WHERE 条件;
流程：
取出表中的每条数据，满足条件的记录就返回，不满足条件的记录不返回

查询商品价格大于60元的所有商品信息

SELECT * FROM product WHERE price > 60;

比较运算符

大于 < 小于 <= 小于等于 >= 大于等于 = 等于 <> 、 != 不等于

逻辑运算符

and(&&) 多个条件同时满足 or(||) 多个条件其中一个满足 not(!)
–> 用作条件筛选

is null

未分类的信息
select * from 表名 where 字段名 is null;
有分类的信息
select * from 表名 where 字段名 is not null;

in关键字

语法格式：

SELECT 字段名 FROM 表名 WHERE 字段 in (数据1, 数据2…);
in 里面的每个数据都会作为 一次条件，只要满足条件的就会显示

范围

BETWEEN 值1 AND 值2 表示从值1到值2范围，包头又包尾
比如： age BETWEEN 80 AND 100 相当于： age>=80 && age<=100

like

模糊查询
SELECT * FROM 表名 WHERE 字段名 LIKE ‘通配符字符串’;
满足通配符字符串规则的数据就会显示出来/通配符字符串就是含有通配符的字符串
MySQL通配符有两个：
% : 表示0个或多个字符(任意个字符)
_ : 表示一个字符

排序

通过 ORDER BY 子句，可以将查询出的结果进行排序
–> (排序只是显示方式，不会影响数据库中数据的顺序)

整体查询

select * from 表名 order by 要排序的字段 asc | desc;
指定查询
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段名 ASC|DESC;
ASC: 升序, 默认是升序
DESC: 降序

单列排序就是使用一个字段排序
具体操作：
查询所有数据,使用年龄降序排序
SELECT * FROM student3 ORDER BY age DESC;

组合排序

组合排序就是先按第一个字段进行排序，如果第一个字段相同，才按第二个字段进行排序，依次类推。
整体排序
select * from product order by price asc , id desc;
指定排序
SELECT 字段名 FROM 表名 WHERE 字段=值 ORDER BY 字段 名1 [ASC|DESC], 字段名2 [ASC|DESC];

聚合函数

聚合函数查询是纵向查询，它是 对一列的值进行计算，然后返回一个结果值。
聚合函数会忽略空值

五个聚合函数：
count ： 统计指定列记录数，记录为NULL的不统计
count(*):统计指定列记录数,包括null
sum ： 计算指定列的数值和，如果不是数值类型，那么计算结果为0
max ： 计算指定列的最大值
min ： 计算指定列的最小值
avg ： 计算指定列的平均值， 如果不是数值类型,那么计算结果为0 聚合函数的使用：

写在 SQL语句 SELECT 后 字段名 的地方
SELECT 字段名… FROM 表名;
SELECT COUNT(age) FROM 表名;

IFNULL(expr1, expr2)的用法：
假如expr1 不为 NULL，则 IFNULL() 的返回值 为 expr1; 否则其返回值为expr2
SELECT COUNT(IFNULL(english,0)) FROM student3;
SELECT COUNT(*) FROM student3;

分组

结合聚合函数使用
分组查询是指使用 GROUP BY 语句对查询信息进行分组,相同数据作为一组
SELECT 字段1,字段2… FROM 表名 GROUP BY 分组字段 [HAVING 条件];

GROUP BY 将分组字段结果中相同内容作为一组，并且返回每组的第一条数据
SELECT SUM(math), sex FROM student3 GROUP BY sex;
SELECT sex(分组的名子), COUNT(*) FROM student3 GROUP BY sex;
结果的列顺序不同而已,无其他区别
注意事项：当我们使用某个字段分组,在查询的时候也需要将这个字段查询出来,否则看不到数据属于哪组的

具体步骤：

查询年龄大于25岁的人,按性别分组,统计每组的人数
1.先过滤掉年龄小于25岁的人。
2.再分组。
3.最后统计每组的人数
SELECT sex, COUNT(*) FROM student3 WHERE age > 25 GROUP BY sex;

注意: 并只显示性别人数>2的数据属于分组后的条件,对于分组后的条件需要使用 having 子句
SELECT sex, COUNT() FROM student3 WHERE age > 25 GROUP BY sex HAVING COUNT() >2; 只有分组后人数大于2的男这组数据显示出来

having与where的区别

having是在分组后对数据进行过滤. where是在分组前对数据进行过滤
having后面可以使用聚合函数 where后面不可以使用聚合函数

limit语句

LIMIT 是 限制 的意思，所以 LIMIT 的作用就是限制查询记录的条数。
SELECT *|字段列表 [as 别名] FROM 表名 [WHERE子句] [GROUP BY子句][HAVING子句][ORDER BY子句][LIMIT子句];
思考：limit子句为什么排在最后？ 因为前 面所有的限制条件都处理完了，只剩下显示多少条记录的问题了！

LIMIT语法格式：
LIMIT offset,length;
limit length;
offset 是指偏移量，可以认为是跳过的记录数量， 默认为0 length 是指需要显示的总记录数

具体步骤：

查询学生表中数据，从第三条开始显示，显示6条
INSERT INTO student3(id,NAME,age,sex,address,math,english) VALUES (9,’唐僧’,25,’男’,’长安’,87,78), (10,’孙悟空’,18,’男’,’花果山’,100,66), (11,’猪八戒’,22,’男’,’高老庄’,58,78), (12,’沙僧’,50,’男’,’流沙河’,77,88), (13,’白骨精’,22,’女’,’白虎岭’,66,66), (14,’蜘蛛精’,23,’女’,’盘丝洞’,88,88);
我们可以认为跳过前面2条，取6条数据
SELECT * FROM student3 LIMIT 2,6;

LIMIT的使用场景：

分页 比如我们登录京东，淘宝，返回的商品信息可能有几万条，不是一次全部显示出来。是一 页显示固定的条数。 假设我们一每页显示5条记录的方式来分页
SQL语句如下：
‐‐ 每页显示5条
‐‐ 第一页： LIMIT 0,5; 跳过0条，显示5条
‐‐ 第二页： LIMIT 5,5; 跳过5条，显示5条
‐‐ 第三页： LIMIT 10,5; 跳过10条，显示5条
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5,5;
SELECT * FROM student3 LIMIT 10,5;

注意：

如果第一个参数是0可以简写：
SELECT * FROM student3 LIMIT 0,5;
SELECT * FROM student3 LIMIT 5;
LIMIT 10，5; – 不够5条，有多少显示多少