mysql学习大全

数据类型

整型数据：

数据类型(显示宽度)
int(4)  表示int类型的显示宽度为4字符，；
int(3) 显示宽度为3，也就是010；
int 默认显示宽度是11位；
create table student(id int(4) zerofill)   zerofill参数表示数字不足的显示空间由0来填充！

---

用tinyint来举例，tinyint大小为1字节，也就是8bit位；
第一位为符号位，
无符号取值范围：2^8-1，即255
有符号的取值范围：2^7-1,即127

---

字符串类型

数据类型(最大长度)
char()其长度可以是0~255
varchar()长度可以在0~66535
char()的长度不可变，varchar()的长度可变！
char()长度不可变，会导致存储空间的浪费，但是数据规整，读取速度快
vchar()长度可变，节省存储空间，读取速度慢！
char(10)只能存储10个英文字符或数字,或者只能存储5个汉字
一个英文字母占一个字符，一个汉字占两个字符
VARCHAR(5)所占的字节数是实际长度的基础上加1。因为varchar类型的字符串的结束标志符占用了1个字节

---

其他数据类型，可查看
https://blog.csdn.net/weixin_45441862/article/details/121211086

---

msyql数据库安装

yum install mariadb mariadb‐server ‐y
 systemctl start mariadb
 mysql_secure_installation   #初始化操作
 

---

修改数据库密码：知道旧密码
mysqladmin -uroot -p123 password 1234

进入数据库修改：
mysql 刚装完不需要密码直接进入
set password for root@localhost=password('123456');

忘记密码修改配置文件：
/etc/my.cnf
/etc/my.cnf.d/mariadb-server.cnf

[mysqld]
skip-grant-tables

进入数据库修改密码：
update mysql.user set authentication_string=password('123') where user = 'root';
flush privileges;

查看数据库版本：
select version();
查看当前用户：
select user();
查看当前数据库：
select database();
查看全局变量：
show GLOBAL VARIABLES;
查看数据库的存储路径：

1. select @@datadir
2. show variables like '%datadir%'

设置全局变量：
set @@global.wait_timeout = 604800
查看数据库字符集：
show variables like 'character%';
查看数据库字符集
show variables like 'character%';
修改数据库的字符集
ALTER DATABASE test_1 charset utf8;
设置数据库字符集
set character_set_results = utf8;

DCL语句：权限管理 grant、revoke

创建用户：
create user zhangsan identified by 'zhangsan';

create user 'lisi'@'%' indentified by 'lisi';
允许用户在一个网段访问数据库
create user 'wangwu@192.168.1.0/24' identified by 'wangwu'

grant <权限> on 库.表 to ‘user’@‘host’

允许root用户在所有主机登录数据库，并将所有数据库的所有权限下放，密码是‘password’
grant all on *.* to 'root'@'%' identified by 'password'
允许root用户在本地登录，下方db1数据库的所有权限
grant all on db1.* to 'root'@'localhost' identified by 'password'

GRANT ALL ON db1.* TO ‘jeffrey’@‘[具体地址|域名|网段|%(所有网段)]’

删除用户
drop user xxx; 默认删除的是’xxx’@'%'这个用户
drop user 'XXX'@'localhost'

回收权限
撤销用户对某个数据库的全部权限
revoke all privileges on database_name.* from 'user_name'@'host';
收回用户对某个数据库的插入、更新、删除的权限
revoke insert,update,delete on database_name.* from 'user_name'@'host';
撤销with grant option 撤销赋予的所有权限
revoke all privileges,grant option from 'user_name'@'host';

查看用户权限
show grants for jeffrey

DDL：数据定义语言，create、drop、alter、show（对表、库的增删改查）

数据库操作

创建数据库：
create database [IF NOT EXISTS 数据库名] [DEFAULT CHARSET 字符集] [COLLATE 排序规则]
查看数据库：
show databases;
删除数据库：
drop database mysql
修改数据库字符集：
alter database db1 charset lantin1;

数据库重命名：这种方法只在5.1版本可用，其他的可进行数据库备份再导入方式
rename database oldname to newname
数据库备份：
mysqldump -uxxx -pxxx -h xxx db_name > db_name.SQL
mysql -uxxx -pxxx -h xxx -e "create database new_db_name"
mysql -uxxx -pxxx -h xxx new_db_name < db_name.SQL

---

非空约束：NOT NULL    #该字段不能为空值
唯一约束：UNIQUE      # 该字段值不能够重复
主键：PRIMARY KEY  ,唯一性+not null
外键：FOREIGN KEY
自增id：auto_increment

唯一约束不的value值不能重复
create table t4(id int UNIQUE,name char);
当unique中有两个字段时，允许其中一个键重复

create table t5(id int ,name char,UNIQUE(id,name));
insert into t5 values(11234,'lisi');
insert into t5 values(11234,'wangwu');
insert into t5 values(11234,'wangwu');#当和前面的id和name都相同时是不可以的，只能允许其中一个相同

创建表的时候指定约束条件：
create table pulisher(pid int(2) auto_increment,paddress char(4) not null,PRIMARY KEY(pid));

查看建表语句
show create table test_1
查看表结构
desc test_1

增加表字段
alter table test_1 add age int(2); 增加字段，默认增加在表的最后一个字段

alter table test_1 add id int(3),add sex enum('boy','girl'); 向表中增加两个字段

alter table test_1 add sex enum('man','woman') 增加性别这个可选字段

alter table test_1 add age int(2) first; 增加age字段在最前面

alter table test_1 add age int(2) after id; 增加age字段在id字段后面

alter table test_1 change id stu_id int(4) after stu_name; 修改id字段为stu_id，并调至stu_name字段后面
修改表字段的数据类型
alter table new_stu_info modify column age char(4);
修改表名称：
alter table table_name rename new_table_name;
修改表字符集：
alter table test_1 charset utf8;

增加表约束条件
create table book(bid int(4),bname char(8),bpublish char(8));
alter table book change bid bid int(4) primary key auto_increment;

删除表的主键约束：

1. 先删除自增
   alter table test_1 change bid bid int(4);
2. 删除主键
   alter table test_1 drop primary key;

如果创建表的时候忘记了将主键写入字段，可以用一下方法加入：
alter table table_name add primary key(filed);

删除表字段
alter table table_name drop field_name

alter table table_name drop id

alter table table_name drop id,drop name 删除表中的两个字段

删除表：
drop table table_name;

DML：对表中数据增删改查，insert、delete、update、select

表插完整的数据：
insert into t1 values(2,'tom',18,'boy');
插入数据到指定字段：
insert into t1(id,name,age) values(2,'Tony',19);
插入多条数据：
insert into table_name VALUES(v1,v2...),(vv1,vv2....);
表一查询到数据插入表二
insert into stu_info(id,name,age) select stu_id,stu_name,stu_age from new_stu_info where stu_id = 11;

清空表
truncate table <表名>

delete * from table <表名>

删除表中字段：
delete from table_name where age=18;

修改表中数据：
update teble_name set f1=v1,f2=v2....where condition;

数据查询

查看所有表数据：
select * from test_1

查询表中重复数据：

1. 查看重复记录，首先要使用分组函数（group by），再用聚合函数中的计数函数count(name)给姓名列计数，且使用group by 后不可使用*
2. 查关键字：姓名，以姓名进行分组，看潜在条件：重复，也就是count（name）>1，使用having过滤条件，不可使用where（where子句无法与聚合函数count（）一起使用）

select name,count(*) as na from stu_infor group by name having na > 1;

where条件查询
select key from table where key=value

group by** 分组查询**
select [字段],[字段] from table_name where grop by [字段],[字段]

between and
BETWEEN v1 and v2 : 值在v1-v2之间
select * from stu_infor where age between 18 and 25;

in
in (v1,v2,v3…) 值是v1或v2或v3
select name,id,sex from stu_infor where age in(18,19,20)
select name,id,sex from stu_infor where age not in(18,19,20)

like模糊匹配：%表示多个字符，_表示匹配一个字符
select * from test_1 where age like "To%"

is null
select emp_name,salary,age from employee where post_comment is not null;
select emp_name,salary,age from employee where post_comment is null;

多条件查询:
select emp_name,salary,age from employee where salary < 5000 and age > 18;
select emp_name,salary,age from employee where salary < 5000 or age > 18;

group by 和 GROUP_CONCAT(expr) 函数一起使用
该函数返回带有来自一个组的连接的非NULL值的字符串结果。
查询每个角色下的员工有哪些
select post,GROUP_CONCAT(emp_name) as numbers from employee group by post;

示例3：group by和count（）统计函数的结合使用
查看每个办公室下有多少人
select office,count(id) as sum_num from employee group by office;
示例4：group by和having结合使用，having是给分组设置条件
优先级： where > group by > having
想要得知每个部门中：薪水高于10000的人有哪些
错误实例：
select post,group_concat(emp_name) from employee group by post; # 分组之后并没有salary字段
select post,group_concat(emp_name) from employee group by post having salary > 10000;
正确示例：你需要在分组前先把薪水高于10000的人过滤出来，然后再进行分组
select post,group_concat(emp_name) from employee where salary > 10000 group by post;

排序查询：ORDER BY
ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 默认情况下，它是按升序排列。
示例1：单字段正序倒序查询
将老师的薪资按升序进行排列！！
select id,emp_name,salary from employee where post='teacher' order by salary ASC;
将老师的工资按降序排列！！
select id,emp_name,salary from employee where post='teacher' order by salary desc;
示例2：多字段正序倒序查询: 先以第一个字段进行排序，如果字段相同则以下一个字段进行排序
select id,emp_name,age,salary from employee where post='teacher' order by age desc,salary desc;
示例3：查询每个部门中：薪水高于1000的人有哪些并根据部门人数进行排序操作
select post,count(emp_name) as numbers from employee where salary > 1000 group by post order by numbers ASC;
select post,GROUP_CONCAT(emp_name) as person from employee where salary > 1000 group by post order by person ASC;

示例：根据学生总分数进行降序
SELECT stu_id,stu_name,chinese,english,math,science,music,总分,AVG(总分/4) '平均分' from new_stusubj1 GROUP BY stu_name ORDER BY 总分 desc

限制输出：limit
输出前3行内容 起始位置为
select id,emp_name,salary from employee where post='teacher' limit 3
输出的是第6和第7行内容
select id,emp_name,salary from employee where post='teacher' limit 5,2
==去重:distinct ==
优先级： from > where > group by > select > DISTINCT > having > oder by > limit

● 示例一：
select DISTINCT age from employee where emp_name='alex';

查询重复行
SELECT stu_name,count(stu_name)from stu_borrowbook GROUP BY stu_name HAVING count(stu_name)>1;

删除重复行

UNION 链接
描述
MySQL UNION 操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
语法
MySQL UNION 操作符语法格式：
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
参数
● expression1, expression2, … expression_n: 要检索的列。
● tables: 要检索的数据表。
● WHERE conditions: 可选， 检索条件。
● DISTINCT: 可选，删除结果集中重复的数据。默认情况下 UNION 操作符已经删除了重复数据，所以 DISTINCT 修饰符对结果没啥影响。
● ALL: 可选，返回所有结果集，包含重复数据。
实例：
select * from ((SELECT stu_name, chinese 'yuwen' FROM stu_subjectgrand1) UNION ALL (select stu_name, chinese 'yuwen' FROM stu_subjectgrand2) UNION ALL (SELECT stu_name, chinese 'yuwen' FROM stu_subjectgrand3));

多表操作
建表：

create table department(id int,name varchar(20));
create table employee2(id int primary key auto_increment,name varchar(20),sex enum('male','female') not null default 'male',age int,dep_id int);

插入数据：

insert into department values(200,'技术'),(201,'人力资源'),(202,'销售'),(203,'运营');
insert into employee2(name,sex,age,dep_id) values('egon','male',18,200),('alex','female',48,201),('wupeiqi','male',38,201),('yuanhao','female',28,202),('liwenzhou','male',18,200),('jingliyang','female',18,204);

多表查询：inner、left、right
inner：内连接查询
left: 左连接查询
right：右连接查询
重点：外连接语法
语法：
select * from table_name1 [inner|left|right] join table_name2 on comdition;

交叉查询:表,表
作用：生成笛卡尔积 n * m关键是两个表中间用，连接起来
select count(*) from employee2,department;#查看表的总数据个数

select * from employee2,department; #查看详细的表内容

内连接查询(inner join on)
示例：查询每个员工所在的部门（两张表之间的关系：dep_id 和 部门id）
select employee2.id,employee2.name,department.name as department_name from employee2 inner join department on employee2.dep_id = department.id;

示例1：
以内连接的方式查询employee2和department表，并且employee表中的age字段值必须大于25,即找出年龄大于25岁的员工以及员工所在的部门
select employee2.name,department.name from employee2 inner join department on employee2.dep_id=department.id where employee2.age > 25;
示例2：
以内连接的方式查询employee2和department表，并且以age字段的升序方式显示
select * from employee2 inner join department on employee2.dep_id=department.id ORDER BY employee2.age;
总结：
对多表进行查询的时候，先把连接查询写下来（找到多张表之间的对应关系），然后根据查询记录可以当成单表进行操作(关键字：where ORDER BY …)

外连接之左连接查询(left join on)
原理：优先把左表的记录全部显示
select employee2.id,employee2.name,department.name as department_name from employee2 left join department on employee2.dep_id = department.id;

外连接之右连接查询(right join on)
原理：优先把右表的记录全部显示
select employee2.id,employee2.name,department.name as department_name from employee2 right join department on employee2.dep_id = department.id;

全外连接查询(left join on union right join on)
原理：把左表和右表全部显示

select* from employee2 left join department on employee2.dep_id = department.id union select * from employee2 right join department on employee2.dep_id = department.id;

子查询

1. 子查询是将一个查询语句嵌套在另一个查询语句中
2. 内层查询语句的查询结果，可以作为外层查询提供的查询条件
3. 子查询中可以包含：IN ，NOT IN，EXSITS ，NOT EXSIT等等关键字
4. 还可以包含比较运算符： = < > 等等

带IN关键字的子查询

示例1： 查询平均年龄在25岁以上的部门名称
● 内连接语法：
select department.name from employee2 inner join department on employee2.dep_id=department.id group by department.name having avg(employee2.age) > 25;
● 子查询
select name from department where id in (select dep_id from employee2 group by dep_id having avg(age) > 25);
示例2：查看技术部员工姓名
● 子查询
select name from employee2 where dep_id = (select id from department where name="技术");
● 内连接查询:
select employee2.name from employee2 inner join department on employee2.dep_id=department.id where department.name="技术";

问题及解决方案
删除重复数据行
情况一：

解决方法：delete from employee where id>=7;
情况二：id是自增主键，数据有重复而且是混乱的重复
CREATE TABLE contacts (id INT PRIMARY KEY AUTO_INCREMENT,first_val VARCHAR(50) DEFAULT NULL,last_val VARCHAR(50) DEFAULT NULL, email VARCHAR(255) NOT NULL);

解决方法一：克隆表删除重复数据行
step1 ：克隆/复制表结构
CREATE TABLE contacts_temp LIKE contacts;
step2 ：数据不重复插入克隆表
INSERT INTO contacts_temp(first_val,last_val,email) SELECT first_val,last_val,email FROM contacts GROUP BY email;
step3： 修改原始表以作备份，出错可以还原
ALTER TABLE contacts RENAME TO contacts_copy;
step4： 修改原始表为克隆表
ALTER TABLE contacts_temp RENAME TO contacts;
再次查看：

解决方法二：delete join 删除重复行
step1：查看重复数据的字段
SELECT email, COUNT( email ) FROM contacts GROUP BY email HAVING COUNT( email ) > 1;

SELECT last_val, COUNT( last_val ) FROM contacts GROUP BY last_val HAVING COUNT( last_val) > 1;

step2:使用delete join语句删除
delete t1 from contacts t1 inner join contacts t2 where t1.id < t2.id and t1.email=t2.email;

可以看见重复的行已经被删除，但是由于id是自增主键所以id值是不连续的

扩展
count(*)和count(1)都是统计表的所有行数，包括空行
count(列名) 将返回表格中某一列除去null以外的所有行的总数
distinct (列名)得到的结果将是除去值为null和重复数据后的结果
select DISTINCT age from employee where emp_name='alex';

去重：
select distinct [字段] from table_name 在表中查找某一字段，并去重