mysql

数据库和表的基本操作

查看表结构与修改表明

把数据表tb_emp改名为jd_emp：
ALTER TABLE tb_emp RENAME jd_emp;
查看该数据库下数据表的列表：
SHOW TABLES;
查看数据表jd_emp的基本结构：
DESCRIBE jd_emp;

DESCRIBE可以查看表的字段信息，包括：字段名、字段数据类型、是否为主键、是否有默认值等。

修改表中的字段名，并修改字段的数据类型

把数据表tb_emp的字段Id改名为prod_id，数据类型不变：ALTER TABLE tb_emp CHANGE Id prod_id INT(11);
数据类型查看数据表结构可以知道，不可以空着它哦
把数据表tb_emp字段Name的数据类型改为varchar(30)：ALTER TABLE tb_emp MODIFY Name varchar(30);
语法规则为： ALTER TABLE 表名 MODIFY 字段名 数据类型;

添加与删除字段

在数据表tb_emp的Name字段后添加字段Country，数据格式为varchar(20)：ALTER TABLE tb_emp ADD Country varchar(20) AFTER Name;
ALTER TABLE 表名 ADD 新字段名 数据类型 [约束条件] [FIRST|AFTER] 已存在字段名;若是没说位置那么默认是添加在最后一列。

在 MySQL 中常用的约束：
NOT NULL 约束：确保某列不能有 NULL 值。
DEFAULT 约束：当某列没有指定值时，为该列提供默认值。
UNIQUE 约束：确保某列中的所有值是不同的。
PRIMARY Key 约束：唯一标识数据库表中的各行/记录。
CHECK 约束：CHECK 约束确保某列中的所有值满足一定条件。

删除：语法规则为： ALTER TABLE 表名 DROP 字段名; 。
删除数据表tb_emp中的字段Salary：ALTER TABLE tb_emp DROP Salary;

修改表中某字段的顺序

通过ALTER TABLE来改变表中字段的相对位置。
语法规则为： ALTER TABLE 表名 MODIFY 字段1 数据类型 FIRST|AFTER 字段2; 。

其中，字段1指要修改位置的字段，FIRST与AFTER 字段2为可选参数。
想把字段的位置调整到第一列，只需做FIRST的位置说明

将数据表tb_emp的Name字段移至第一列，数据格式不变：ALTER TABLE tb_emp MODIFY Name varchar(25) FIRST;
将DeptId字段移至Salary字段的后边，数据格式不变：ALTER TABLE tb_emp MODIFY DeptId int(11) AFTER Salary;

删除表中的外键约束

建立了外键我们就建立起了两张表的关联关系，那如果我想删除主表呢？为了确保数据库的正确性，我们必须先解除两个表之间的关联关系，那就是删除外键约束啦。
语法规则为： ALTER TABLE 表名 DROP FOREIGN KEY 外键约束名;

CONSTRAINT prod_country FOREIGN KEY(country_id) REFERENCES country(id)

上述语句成功执行后，在表Mall_products2上添加了名称为prod_country的外键约束，外键名称为prod_country，依赖于表country的主键id，从下图中可以看到，已经成功添加了表的外键
ALTER TABLE Mall_products2 DROP FOREIGN KEY prod_country;成功删除。

插入数据

插入数据最简单的方法就是使用INSERT语句。

语法规则：INSERT INTO 表名 (字段名) VALUES (内容);

insert into MyUser(name,age) values('zhnagsan',18);

可以不填字段名，但是这样的话，就必须老老实实的按字段顺序来填入相应的数据。
可以只为指定好的字段插入数据，其他字段的值为表定义时的默认值。

我们为你新建了一个空数据表tb_emp，请你为它同时添加3条数据内容；
insert into tb_emp(Id,Name,DeptId,Salary) values(1,‘Nancy’,301,2300.00),(2,‘Tod’,303,5600.00),(3,‘Carly’,301,3200.00);
这里用逗号隔开就可以了

更新数据

语法规则为： UPDATE 表名 SET 字段名1 = 内容1, 字段名2 = 内容2, 字段名3 = 内容3 WHERE 过滤条件; 。
现在我们想把Span换成Pakistan，当然，地区代码也要换为92 。

UPDATE Mall_products2
SET country_name = "Pakistan", country_id = 92
WHERE id = 2;//筛选出Span对应的那一列

删除数据

语法规则为： DELETE FROM 表名 WHERE 条件语句; 。
想把包含Span和Italy的这两行数据内容同时删除，删除（DELETE）语句为：

 DELETE FROM Mall_products2 
 WHERE id=2 OR id=3;//想删除的数据对应的id号

表Mall_products2的所有数据内容同时删除，删除（DELETE）语句为：

 DELETE FROM Mall_products2;

删除表tb_emp中Salary大于3000的数据行删除：DELETE FROM tb_emp
WHERE Salary>3000;

安全性控制

用户和权限

用户(User)
MySQL创建用户的语句：
create user 用户名 identified by 用户登录密码；
通常用户名可包含域名，限定用户在该域名内登录再有效。例：
CREATE USER ‘jeffrey’@‘localhost’ IDENTIFIED BY ‘password’;
该语句创建用户jeffrey,密码为’password’，仅限在MySQL服务器本机上登录才有效。用户名与域合起来，被称为账户(account)。
注意不要写成：‘jeffrey@localhost’，它代表账户：
‘jeffrey@localhost’@’%’
意即用户名为jefrrey@localhost，在任何机器上登录都有效。两者的含义完全不同。
省略域名，即默认为’%’,表示用户可远程登录。在本实训中，可以省略域，这时，用户名可以不加引号。
drop user语句可删除用户。用户被删除时，该用户拥有的权限自动被收回。
alter user语句可重置用户密码：
ALTER USER user IDENTIFIED BY ‘new_password’;
MySQL在安装时，初始用户名为root，此为系统管理员用户，其余用户均由root创建，并授权。经授权的用户也可以创建用户。
在生产环境中，除了管理员，其它人应当使用由管理员创建的账户，不得使用root帐户。出于安全考虑，一般root用户也仅限在服务器本地登录，不轻易开放远程登录。

角色(Role)
角色是权限的集合。如果有一组人(承担相同职责的小组,或者説小组成员扮演相同的角色)应该被授予一组相同的权限，不妨创建一个角色，将那组权限授予该角色，然后再将角色授予该组的每个成员。这比一个个地给每个组员授予一批权限要方便得多。

创建角色的语句：
CREATE ROLE [IF NOT EXISTS] role [, role ] …
一次可以创建多个角色。
删除角色：
DROP ROLE [IF EXISTS] role [, role ] …
角色被删除后，拥有该角色的用户立即失去角色定义的权限组合。不过，如果用户同时拥有多个角色，两个角色代表的权限集合如果有交集，则该用户仍拥有交集代表的权限。

GRANT授权语句
以下语句授予权限给用户或角色：
grant 权限[,权限] … on 数据库对象 to user|role,[user|role]… [with grant option]
可以同时将多个权限授予多个用户或角色。
with grant option表示被授权用户可以传播权限，即授权该用户将其拥有的权限（之前获得的权限，通过本语句获得的权限，以及今后获得的权限）再授予其它用户。
权限有：

all: 所有权限(grant option除外)
alter: alter table权限
alter routine: alter 存储过程
create: create database/table
create role: create role
create user： create/alter/rename/drop user
insert: insert语句//插入
select: select语句//查询
update: update语句 //更改

REVOKE收回权限语句

以下语句将对象的权限从用户或角色手中收回：
revoke 权限[,权限]… on 数据库对象 from user|role[,user|role]…
下列语句把role所代表的权限集合从用户或角色中收回：
REVOKE role [, role ] … FROM user_or_role [, user_or_role ] …
如果用户本身拥有多个角色所代表的权限集合，而这些集合存在交集，收回其中部分角色代表的权限集后，用户可能仍拥有那个角色所代表的部分权限(交集代表的那部分权限)。

– (1) 创建用户tom和jerry，初始密码均为’123456’
CREATE USER tom IDENTIFIED BY ‘123456’;
CREATE USER jerry IDENTIFIED BY ‘123456’;
– (2) 授予用户tom查询客户的姓名，邮箱和电话的权限,且tom可转授权限
GRANT SELECT(c_name, c_mail, c_phone) ON client TO tom WITH GRANT OPTION;
– (3) 授予用户jerry修改银行卡余额的权限
GRANT UPDATE(b_balance) ON bank_card TO jerry;
– (4) 收回用户Cindy查询银行卡信息的权限
REVOKE SELECT ON bank_card FROM Cindy;

– (1) 创建角色client_manager和fund_manager
CREATE ROLE client_manager, fund_manager;
– (2) 授予client_manager对client表拥有select,insert,update的权限
GRANT SELECT, INSERT, UPDATE ON client TO client_manager;
– (3) 授予client_manager对bank_card表拥有查询除银行卡余额外的select权限
– 假设bank_card表有b_number, b_type, b_c_id等字段，不包括b_balance
GRANT SELECT(b_number, b_type, b_c_id) ON bank_card TO client_manager;
– (4) 授予fund_manager对fund表的select,insert,update权限
GRANT SELECT, INSERT, UPDATE ON fund TO fund_manager;
– (5) 将client_manager的权限授予用户tom和jerry
GRANT client_manager TO tom, jerry;
– (6) 将fund_manager权限授予用户Cindy
GRANT fund_manager TO Cindy;

数据查询一

按条件查询数据表的所有字段

命令格式：
select * from 数据表 where 查询条件
打开province数据库user province

• 查询街道信息(jdxx)数据表的开福区(qxmc)的所有字段:select *from jdxx where qxmc=“开福区”;
• 查询街道信息(jdxx)数据表的开福区和岳麓区(qxmc)的所有字段:select * from jdxx where qxmc=“开福区”||“岳麓区”;
• 查询街道信息(jdxx)数据表的长沙市(cs)的西湖街道（name)所有字段:select * from jdxx where cs=“长沙市”&&name=“西湖街道”;

查询唯一值：distinct

命令格式：distinct <字段名>去掉重复的查询结果

• 查询湖南省（sf)所有的区县名称(qxmc)，每个区县只出现一次：select distinct qxmc from jdxx where sf=‘湖南省’;

统计查询

查询函数：sum：求数值列和；avg：求数值列的平均值；max/min：计算列的最大/小值；count：计算查询结果的数目。
函数，所以括号内传参，也就是传入列，若是唯一值，记得再传入distinct。在数据中，同一个区县名称可能出现多次（例如不同街道属于同一个区县），使用 DISTINCT 可以消除这些重复。街道名称在一个区县内通常是唯一的，一般不需要去重。

• 查询湖南省的区县个数：select count(distinct qxmc) from jdxx where sf=“湖南省”;

分组查询

select sf,count() from jdxx group by sf;
SELECT cs,count() from jdxx GROUP BY cs HAVING count(*)>200;这条语句的作用是统计jdxx表中每个cs(城市)的记录数量，但只显示记录数超过200的城市。

SELECT cs, count(*): 选择显示两列数据 - cs列和计数结果
from jdxx: 数据来源是名为jdxx的表
GROUP BY cs: 按照cs列的值进行分组，也就是相同的在同一行
HAVING count(*)>200: 只显示分组后计数结果大于200的记录

数据排序

Order by <字段名1> ASC|DESC,
<字段名2> >ASC|DESC……
首先按照<字段名1>的顺序排列记录
若多条记录的<字段名1>值相同，则按<字段名2>的顺序排列
ASC升序 DESC降序 默认升序
limit就是指它的偏移量如：
显示jdtj数据表中街道个数最多的10个的所有字段：那么就是按照降序（让最多的排最前面）然后取前面10个
那么若是最少呢，就是升序的前10个。
即：

select *
from jdtj
order by jdgs desc/asc
limit 10;

注意看这里整整四行才是一个查询语句哦，因为就是分号标志着这个语句的结束。然后呢，大小写select什么的是没有关系的，一般是习惯大写，这样可以清楚区分这是关键字，与列名等不同。

按街道个数从高到底，街道个数相同的按省份升序排序显示jdtj数据表中街道个数大于35的所有字段
jdtj数据表字段为省份(sf)、城市(cs)、区县(qx) 、街道个数（jdgs)字段

select *
from jdtj
where jdgs>35
order by jdgs desc,sf asc;//这里就涉及到二级字段查询

在SQL语句中，SELECT * 中的星号（）是一个通配符，表示"所有列"的意思。
具体含义：
当你在SELECT语句中使用时，表示要查询指定表中的所有字段（列）

例如：SELECT * FROM employees 会返回employees表中的所有列的数据
这个语句等同于明确列出表中所有列名，如：SELECT id, name, age, department, salary… FROM employees

数据库设计

用户信息表

-- 创建用户信息表t_user
CREATE TABLE t_user (
    userId bigint NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',  -- 主键，自增长
    username varchar(32) NOT NULL COMMENT '用户名',                     -- 非空
    password varchar(32) NOT NULL COMMENT '密码',                       -- 非空
    user_sex varchar(6) NOT NULL DEFAULT '0' COMMENT '性别 0代表男 1代表女',     -- 默认值为0
    email varchar(64) COMMENT '邮箱',                                   -- 无约束
    phone varchar(11) NOT NULL COMMENT '手机号码',                       -- 非空
    firstname varchar(6) COMMENT '姓',                                 -- 无约束
    lastname varchar(12) COMMENT '名',                                 -- 无约束
    avatar varchar(255) COMMENT '头像地址',                             -- 无约束
    is_superuser int NOT NULL DEFAULT 0 COMMENT '是否是管理员 0代表不是 1代表是', -- 默认值为0
    last_login datetime COMMENT '上一次登陆时间',                       -- 无约束
    user_register_time datetime COMMENT '用户注册时间'                  -- 无约束
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户信息表';```
主键，自动增长，增量为1,注意：主键也约束了该字段不能为空：NOT NULL
comment:备注。

注意要添加外键约束，外键为userid，外键名称设置为FK_comment_user_id，外键表为用户信息表（t_user）。

```c
INDEX idx_userid (userid),  -- 为userid添加索引，命名为idx_userid
 CONSTRAINT FK_user_id FOREIGN KEY (userid) REFERENCES t_user(userid),

注意创建表的顺序，因为现在这个表要参照表t_user所以，一定是先创建t_user之后再创建这个表并添加外键
添加外键后会自动创建索引，而上面的只是普通创建了个索引并不会检查是不是外键。索引必须基于已存在的列。即使是外键，也是要先按照那个要求去加了列之后再添加外键约束，去命名。

CREATE INDEX idx_product_category ON products(category_id);
//后期添加索引```
key和index都是索引，一般用key，没有create key哈，仅 KEY idx_username (username)。
PRIMARY KEY、UNIQUE KEY、FOREIGN KEY 等约束都会自动创建索引

## MySQL开发技巧 - 事务
什么是事务
可以把一系列要执行的操作称为事务，而事务管理就是管理这些操作要么完全执行，要么完全不执行。
经典的事务举例：A要给B转钱，首先A的钱减少了，但是突然的数据库断电了，导致无法给B加钱，然后由于丢失数据，B不承认收到A的钱；在这里事务就是确保加钱和减钱两个都完全执行或完全不执行，如果加钱失败，那么不会发生减钱。
事务管理的意义：保证数据操作的完整性；
事务的特性:
原子性：事务的整个操作是一个整体，不可以分割，要么全部成功，要么全部失败；
一致性：事务操作的前后，数据表中的数据没有变化（按示例中解释为A转钱给B的前后两个人钱的总金额不会改变）；
隔离性：事务操作是相互隔离不受影响的；
持久性：数据一旦提交，不可改变，永久的改变数据表数据。

```c
drop procedure if exists mydb.proc_insert;//检查是否存在名为proc_insert的存储过程，如果存在则删除它。mydb.指定了数据库名称
delimiter $$ //将语句分隔符从默认的分号;改为$$，因为存储过程中会包含多个SQL语句，用分号分隔，所以需要临时改变分隔符
create procedure proc_insert()//    创建一个名为proc_insert的存储过程， 括号()内可以定义参数，这里没有参数
Begin //表示存储过程体的开始
	//开启事务，保证接下来的多个SQL操作要么全部成功，要么全部失败
	start transaction ;
    insert into t_emp values(1,'Nancy',301,2300);
    insert into t_emp values(2,'Tod',303,5600);
    insert into t_emp values(3,'Carly',301,3200);
	//update t_emp set salary=0 where id=1;
	// update t_emp set salery=8000 where id=2;
	//事务提交，提交事务，使之前的插入操作永久生效。如果在start transaction和commit之间有错误发生，可以使用rollback回滚所有操作
	commit;
END $$//结束存储过程体，因为前面用delimiter $$改变了分隔符，使用$$作为结束符
delimiter ;//将分隔符恢复为默认的分号;

注意在事务全部提交后不能回滚事务，只能在commit前有错误就回滚事务，让所有提交撤销。
结构化编程的需要，存储过程通常需要包含多个语句作为一个整体执行，BEGIN…END块可以创建局部作用域，使得MySQL存储过程能够实现真正的程序化数据库操作，而不仅仅是简单的SQL语句集合。
BEGIN和END关键字创建了一个代码块，它：

界定了存储过程的开始和结束位置
将多个SQL语句组织成一个逻辑单元
类似于其他语言中的函数体或代码块概念事务控制的明确边界

在事务处理中，BEGIN…END帮助明确：

哪些操作属于同一个事务
哪里放置错误处理逻辑

索引的建立与删除

建立聚集索引：clustered

聚集索引用CLUSTERED，为表 TInfo 按教师工号（TID）升序建立一个聚集索引clu_tid

CREATE CLUSTERED INDEX clu_tid ON TInfo(TID ASC)

建立复合索引（多列）

复合索引（也称为组合索引或多列索引）是指基于表中两个或多个列创建的索引

 CREATE INDEX ix_dptnm ON TInfo(Dept ASC, Tname DESC);

这种索引特别时和这种查询：SELECT * FROM TInfo WHERE Dept = '某部门' ORDER BY Tname DESC;

创建唯一索引：unique

为表 TInfo 中的 HomeTel 字段创建一个唯一索引 unq_hometel

create unique index unq_hometel on TInfo(HomeTel);

删除索引：drop

删除已经建立的索引 ix_dptnmdrop index ix_dptnm on TInfo;

多表连接查询

简单的连接查询

求选修了’数学’课的学生学号和成绩，并要求对查询结果按成绩降序排列，如果成绩相同则按学号降序排列

Begin
    select//选中两列
        Sno ,
        Grade
    from
        SC 
    where
        Cno=2//筛选条件是选修了数学课的
    order by//排序先是grade，再是sno
        Grade DESC,
        Sno DESC;
end

查询选修数学课且成绩在80-90之间的学生学号和成绩，并将成绩乘以系数0.8输出。

select Sno,Grade*0.8//要展示的两列的值，那么也*0.8
from SC 
where Cno=2 AND Grade between 80 and 90;//and并列筛选条件

多表连接查询

查询学生的姓名、选修的课程名以及成绩。

/********** Begin **********/
Begin
    select Student.Sname,Course.Cname,SC.Grade
    from Student,Course,SC
    where Student.Sno=
SC.Sno AND Course.Cno=SC.Cno;
End

外连接查询

查询所有课程的名称和选修人数，包括无人选课的课程

select
    Course.Cname,COUNT(SC.Cno)//选择要显示的列
from
    Course
LEFT JOIN
    SC ON Course.Cno=SC.Cno
GROUP BY
    Course.Cname;//按课程名称分组，这样COUNT函数会对每门课程单独计算选课人数

LEFT JOIN是关键部分：

    它表示以左表(Course)为基础，连接右表(SC学生选课表)
    即使右表(SC)中没有匹配的记录，左表的记录也会被保留
    ON Course.Cno=SC.Cno表示连接条件是课程编号相同
    与INNER JOIN不同，LEFT JOIN会保留左表中没有学生选的课程

特别说明LEFT JOIN的含义
LEFT JOIN(左连接)确保：

结果中包含Course表中的所有课程
即使某门课程没有任何学生选修(在SC表中没有记录)，这门课程也会出现在结果中
对于没有学生选的课程，COUNT(SC.Cno)会返回0

如果是INNER JOIN，则只会显示有学生选修的课程，没有学生选的课程不会出现在结果中。
这个查询非常适合用来统计每门课程的选课人数，包括那些无人选修的课程。

自连接查询

查询所有课程的先行课，结果列标题为课程名和先行课程名

select
        C1.Cname AS '课程名',
        C2.Cname AS '先行课程名'
    from
        Course C1//从Course表获取数据，并给表起别名C1（代表主课程表）
    LEFT JOIN
        Course C2 ON C1.Cpno=C2.Cno
    order by
        C1.Cno;//结果按照主课程的课程编号(C1.Cno)排序

使用AS给列起别名，使结果更易读

LEFT JOIN是关键操作：
    再次使用Course表，但这次起别名C2（代表先行课程表）
    连接条件是主课程的先行课号(C1.Cpno)等于先行课程的课号(C2.Cno)
    即使某门课程没有先行课程(Cpno为NULL)，LEFT JOIN也会保留主课程记录

特别说明

这个查询使用了自连接（同一张表连接自己）：
    C1代表主课程
    C2代表先行课程
LEFT JOIN的作用：
    确保即使某门课程没有设置先行课程（Cpno为NULL），这门课程也会出现在结果中
    对于没有先行课程的记录，"先行课程名"列将显示为NULL
如果是INNER JOIN：
    只会显示有先行课程的课程记录
    没有先行课程的课程不会出现在结果中

这个查询非常适合用来查看课程间的先修关系，包括哪些课程没有设置先行课程。