MySQL相关知识（一）命令相关

数据库相关知识

oracle 公司----oracle和mysql数据库

微软------ssqlserver数据库

ibm------DB2数据库

需要学习的都是SQL语句

 

国内数据库：

阿里------HB数据库----ADB数据库------palarDB

 

mySQL安装

1、下载mysql

 2、选择Custom自定义

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 在这里设置用户名和密码 方便记忆 可以选择 账号密码都为root

 

 

 

 

 

 当我们安装完成后 将mysql的bin目录配置到环境变量

 

 

 配置完成之后在cmd控制台输入mysql验证环境是否搭建成功

 

 

 

mysql默认端口是3306

表示本地的IP地址方法

127.0.0.1

localhost

如何链接mysql

mysql -h localhost -u 用户名 -p

代码解析

-h 指定ip地址 其中localhost为本地端口

-u 指定用户名

-p 指定密码

查看mysql版本

select version();

 

 

 

如何启动（windows)?

cmd以管路员的身份运行

net start MySQL57

 

 

 

如何停止（windows)？

先需要找到mysql的服务名称

 

 

 输入

net stop MySQL57

 

 

 

即为停止服务

status;查看信息配置

 

 

 

Current user: 用户名和ip地址
Protocol version： 版本
Server characterset：服务端编码
DB characterset：   数据库编码
Client characterset ：客户端编码
Conn characterset ：查看 链接编码
TCP port：端口号

select now();查询当前时间

select now();    

 

 

 

show variables like '%connections%';查询MySQL连接数

show variables like '%connections%';

 

 

 

其中max_connections为最大连接数

show variables like '%timeout%';查询超时

shouw variables like '%timeout%';

 

 

 

DB（DataBase）数据库

	增加
Database	查询
	删除
表的英文是table

 

 

show databases;查询当前已有数据库

show databases;

 

 

 

其中information_schema为MySQL自带的库

mysql是MySQL的库

create database testDev;创建库

create database 创建的目录名字;    ：创建库

 

 

 

show variables like '%datadir%';查询库的目录

show variables like '%datadir%';

 

 

 

 

 

 

 

通过查询 我们就能知道创建目录的地方

drop database testDev;删除库

drop database 删除库的名字;    将库进行删除

 

 

 

use testDev;进入数据库

use testDev;  进入testDev数据库
select database();   查看当前库

 

 

 

create table info();创建表

 

create table 名字();   创建表

在表的定义中，应该指定列的名称，数据类型和具体默认的值的信息，MySQL目前支持的类型有

数字:tinyint smallint int bigint bit （整形） decimal float double （float类型 带小数点的）

char varchar blob text enum set （str 字符串 图片）

default     :默认值
primary key :唯一固定  主键
not null:不能为空
unique key:唯一性约束  针对所有的
auto_increment: 自增关键字 ，需要与主键 关联使用

 

 

 

另外一种方式来创建

 

 

设置多个字段

 

 

desc info;查询表的字段

desc 表的名称;

 

 

show create table info;查看脚本

show create table info;   查看info脚本

 

 

drop table info;删除表

drop table info;  删除表

 

 

insert into info values(); 插入信息

insert into info values(); 

 

插入多条语句

 

 

把一个表里的数据写入到另外一个表里面？（场景解决）

1、先将原来的表进行复制

2、将表进行批量的写入

 

insert into 新表 select * from 旧表;           :将旧表里的值全部写入到 新表里
insert into userinfo select * from user;

 

3、读取新的表中数据

 

 

 

select * form info;查询表里的数据

select * form info;

 

复制表 （like）

 

create table 新的表名 like 旧的表名

 

 

 

修改表的名称(rename)

 rename table 表名 to 新表名

在表中添加新的字段(add)

alter table 表名 add 字段;
alter table user add age int;

 

 

 

 

 

 

在表中删除字段(drop)

alter table 表名 drop 字段;
alter table user drop name;

 

 

 

 

 

表中在字段后面添加新的字段(after)

alter table 表名 add 字段 after 字段;      在字段后面添加新的字段
alter table user add name varchar(10) after id;

 

 

在表中添加第一位字段(add)

alter table 表名 add 字段 first;
alter table user add id int primary key auto_increment first;

 

 

在表中对字段添加注释(comment)

alter table 表名 add 字段 comment ;
alter table user add test varchar(10) comment 'jiachao zhenshuai'

 

 

修改表中的字段的类型(modify)

alter table 表 modify 字段的类型;
alter table user modify name varchar(100);

 

 

修改表中的字段名称（要写类型）(change)

alter table 表名 changer 原字段名称 新字段名称 类型
alter table user change name username varchar(10)；

 

 INSERT,UPDATE,DELETE可以理解为写的操作，也就是write，SELECT是⼀个读的操作，也就是read。

 

修改表中数据的名称（update）

update 表名 set 变量 where id=变量;
update user set username='taishuaile' where id=1;

 

 

删除表中数据的名称（delete）

 

delete from 表名 where id=?;
delete from user where id=1;

针对大量数据进行删除（truncate）

1、cd进入到文件夹里

2、执行mysql -u root -p <employees.sql 命令

mysql -u root -p <employees.sql

 

3、输入密码

4、导入成功 在mysql里查看

 

 

 

 

表之间的联合查询

select person.name,person.age,work.company,school.schoolName  from person inner join  work on person.code=work.code inner join school on work.code=school.code where person.code=1001;
select 查询的内容 from inner join (表之间用什么关联) where (表达式)

 

 

 

查询表中的几行数据（limit）

select * from employees limit 20;查询 前20条数据
select * from 表名 limit N;

 

 

 

查询表中总共有多少条数据（count）

select count(*) from employees;

 

 

 

 

 

利用and的关系查询表中的内容

select * from employees where first_name='Georgi' and last_name='Facello';

 

 

 

 

利用or的关系查询表中的内容

select * from employees where first_name='Georgi' and last_name='Facello';

 

 

 

 

利用in的关系查询表中的内容

select * from employees where last_name in ('christ','lamba')limit 10;
in前面加not即为反向

 

 

 

 

 

查询区间内表里的数据（between and）

select * from employees where hire_date between '1986-12-01' and '1986-12-31' limit 20;

 

 

利用%占位进行模糊匹配搜索

select * from employees where first_name like 'christ%' limit 5;

 

 

利用下划线进行模糊匹配搜索（下划线）

一个下划线是代表一个字符串

 select * from employees where first_name like '___ka%' limit 5

 

 

利空开头进行模糊搜索（rlike ^）

mysql> select * from employees where first_name rlike '^na' limit 5;

 

 

 

以什么什么结束（rlike $）

mysql> select * from employees where first_name rlike   'mo$' limit 5;

 

 

 

 

别名（更改总数名称 as 名称）

mysql> select count(1) as shuliang from employees;

 

 

 

 

正序排序（场景 薪资进行排序order by ）

select * from salaries order by salary desc limit 10;

 

 

 

倒序排序（场景 薪资进行排序 order by asc）

select * from salaries order by salary asc limit 10;

 

 

聚合函数

查询总数（count）

总和：sum
最大：max
最小：min
平均：avg
select max(字段) from salaries;

 

 

 

 

 

 

按照薪资进行分组查询（group by）

select salary,count(1) as count from salaries group by salary order by count desc limit 10;

代码解析

查询 salary字段 查询总数 salaries库里 进行分组 order by排序 展示前10种

 

 

按照年进行排序查询

select year(from_date) as dateYear,sum(salary) as sumSalary from salaries group by dateYear order by sumSalary desc limit 10;

 

 

按照平均值进行排序

select year(from_date) as dateYear,avg(salary) as avg from salaries group by dateYear order by avg desc limit 10;

 

 

去重进行查询（distinct）

select distinct title from titles limit 10;

 

 

过滤进行排序查询（having）

select emp_no,avg(salary) as avg from salaries group by emp_no having avg>100000 order by avg desc limit 10;
需求：平均工资大于10万 进行排序

 

 

内连接

inner join，⼜叫内连接的部分，主要是获取两个表中字段匹配关系的表。查询关联字段共同拥有的数据

 

 

查询两个表共同拥有的数据（inner join）

select e.first_name,e.last_name,e.gender,s.salary from employees as e inner join salaries as s on e.emp_no=s.emp_no where s.salary <40000 limit 3;

 

左连接

获取左表所有记录，获取左边数据表所有符合要求的字段数据信息

 

 

获取左表所有记录（left join）

select p.name,p.age,p.age,w.company,w.salary from person as p left join work as w on p.code=w.code limit 5;

 

 

三表之间的查询

 

select p.name,p.age,p.sex,w.company,w.salary,info.type,info.isSafe from person as p left join work as w on p.code=w.code left join workinfo as info on w.workID=info.workID limit 5;

 

 

右链接

获取右表所有记录的信息，获取右边数据表所有的数据信息

 

 

 

获取右表所有记录（right join）

select w.company,w.salary,info.type,info.issafe from work as w right join workinfo as info on w.workID=info.workID;

 

 

通过子查询，来进行查询

 

select * from person where code in (select code from work where code=1001);

 

 

 

通过子查询，来进行查询

 

select * from person where code in (select code from work where code=1001);

 

 

 

关于学生成绩 薪资等判断用mysql解决

mysql> select
    -> (
    -> case when salary>=2500 then'niubi'
    -> when salary >1500 then'lihai'
    -> when salary >600 then'jiunayang'
    -> else 'shabi'end
    -> )
    -> company,salary
    -> from work;

 

 

场景（过滤出薪资相同 并且大于1的员工）

select * from work where salary in  ( select salary from work group by salary having count(*) >1 );

 

 

一、

1、表的结构信息；
mysql> desc A;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | varchar(10) | YES | | NULL | |
| b | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from A;
+------+------+
| a | b |
+------+------+
| 1 | b |
+------+------+
1 row in set (0.00 sec)

mysql> desc B;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| a | varchar(10) | YES | | NULL | |
| c | varchar(10) | YES | | NULL | |
| d | varchar(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> select * from B;
+------+------+------+
| a | c | d |
+------+------+------+
| 1 | c | d |
| 1 | cc | dd |
+------+------+------+
2 rows in set (0.01 sec)

解决方案：使用右连接，具体SQL如下：
mysql> select * from A right join B on A.a=B.a where A.a='1';
+------+------+------+------+------+
| a | b | a | c | d |
+------+------+------+------+------+
| 1 | b | 1 | c | d |
| 1 | b | 1 | cc | dd |
+------+------+------+------+------+
2 rows in set (0.00 sec)

 

 

三、

 

mysql> select * from employee;
+------+----------+------+------+-------+
| id | name | sex | age | score |
+------+----------+------+------+-------+
| 001 | lisi | 女 | 23 | 90 |
| 002 | wangwu | 男 | 23 | 98 |
| 003 | wangmazi | 女 | 20 | 56 |
| 004 | zhaosi | 男 | 26 | 88 |
+------+----------+------+------+-------+

聚合函数的应用
mysql> select sex,sum(score) as score from employee group by sex;
+------+-------+
| sex | score |
+------+-------+
| 女 | 146 |
| 男 | 186 |
+------+-------+
2 rows in set (0.01 sec)

根据分数显示不同的判断情况：
mysql> select (case when score>90 then "优秀" when score>80 then "良 好" else "其他" end) score,name from employee;
+---------+----------+
| score | name |
+---------+----------+
| 良 好 | lisi |
| 优秀 | wangwu |
| 其他 | wangmazi |
| 良 好 | zhaosi |
| 优秀 | lisi |
+---------+----------+
5 rows in set (0.01 sec)

3、重复字段

 

select * from employee where name in (select name from employee group by name having count(*)>1);

统计人数：

select sex,count(1) as 总人数 from employee group by sex;

 mysql索引

在MySQL中，创建MySQL的索引主要是为了提⾼MySQL查询的效率。但是添加太多的索引也是会降低更新表的速度的

添加索引

mysql> create table userIndex(
    -> ind int primary key,
    -> name varchar(20),
    -> index index_name(name)
    -> );

 

 

更新表的时候添加索引

show create table userindex \G