MySQL-SQL语句

MySQL客户端命令

• mysql

# 修改mysql命令提示符
prompt  \u@\h [\d] > 

-u 指定用户
-p 指定密码
-h 指定主机域
-S 指定socket
-P 指定端口
-e 指定SQL语句
# 查看MySQL服务端状态
status|\s 
# 切换库
use|\u 
# 格式化输出数据
ego|\G 
# 在库内恢复数据（导入数据）
source|\.
# 临时局部记录日志（只在当前终端生效，退出终端后失效）
tee|\T 
# 退出mysql
exit|quit|\q 
# 在数据库内执行系统命令
system|\!
# 查看帮助信息
help|?|\?|\h 

## mysql5.6
clear|\c 退出当前输入的sql语句
## mysql5.7
clear|\c|ctrl+c 退出当前输入的sql语句

• mysqladmin

想使用mysqladmin的前提，服务端必须开启
# 修改密码
mysqladmin -uroot -p123 password '123' 

# 检查mysql是否存活
[root@db02 ~]# mysqladmin -uroot -p123 ping
mysqld is alive 

# 库外创建数据库
mysqladmin -uroot -p123 create 库名

# 删除数据库
mysqladmin -uroot -p123 drop 库名

# 查看mysql默认配置
mysqladmin -uroot -p123 variables
## 应用场景：可以查看各种信息
mysqladmin -uroot -p123 variables|grep port

# 查看mysql状态信息
mysqladmin -uroot -p123 status

# 重载授权表
mysqladmin -uroot -p123 reload

# 库外刷新日志
mysqladmin -uroot -p123 flush-log

# 库外刷新授权表
mysqladmin -uroot -p123 flush-host

# 关闭mysql进程
mysqladmin -uroot -p123 shutdown

• mysqldump

SQL层 SQL语句

DDL（Database Definition Language）数据定义语言

针对库、表的操作

库

##相当于linux 
mkdir
touch
rm

## 数据库
# 增：建库
create database 库名;

[语法]
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
    [create_option] ...

create_option: [DEFAULT] {
    CHARACTER SET [=] charset_name
  | COLLATE [=] collation_name
}

CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.

URL: https://dev.mysql.com/doc/refman/5.6/en/create-database.html

# 示例：
root@localhost [(none)] >create database ljy;
root@localhost [(none)] >create schema ljy;

## 数据库已存在的情况下
root@localhost [(none)] >create schema if not exists ljy;

## 指定字符集和校验规则
root@localhost [(none)] >create database if not exists ljy3 charset utf8 collate utf8_bin;
root@localhost [(none)] >show create database ljy3;
+----------+--------------------------------------------------------------------------------+
| Database | Create Database                                                                |
+----------+--------------------------------------------------------------------------------+
| ljy3     | CREATE DATABASE `ljy3` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_bin */ |
+----------+--------------------------------------------------------------------------------+
## 修改默认字符集
vim /etc/my.cnf
[mysqld]
character_set_server=utf8

# 删
drop database ljy;
drop database if exists ljy2;

# 改
alter database 库名 修改的内容;

root@localhost [(none)] >alter database ljy charset utf8;
Query OK, 1 row affected (0.00 sec)
root@localhost [(none)] >show create database ljy;
+----------+--------------------------------------------------------------+
| Database | Create Database |
+----------+--------------------------------------------------------------+
| ljy | CREATE DATABASE `ljy` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+--------------------------------------------------------------+
1 row in set (0.00 sec)

root@localhost [(none)] >alter database ljy collate utf8_bin;
Query OK, 1 row affected (0.01 sec)

root@localhost [(none)] >alter database ljy charset latin1 collate latin1_general_ci;

表

## 表操作
# 增：建表

# 数据类型

## 数字类型
int：整形 -2^31 ~ 2^31-1
tinyint：最小整形 -128 ~ 127

## 字符串类型
name
varchar(10) // 可变长类型 张三
char(10) // 定长类型 张三

char(11) // 手机号
char(18) // 身份证号

## 枚举类型
enum('f','m')
male female

enum('A','B','C','D')

## 浮点型
float 单精度
double 双精度

## 时间戳类型
timestamp '1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'
datetime '1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'

# 字段属性（约束）
not null：非空
primary key：主键 唯一且不能为空（一张表中只能创建一个主键）
auto_increment：自增
unique key：唯一键 唯一可以为空
default：默认值
unsigned：无符号（非负）
comment：注释
主键=唯一键+not null

## 在ljy数据库中建stu10的1表并设置数据类型为10个字符的可变长类型字符串 age的存储范围在-128到127之间的小整数
root@localhost [(none)] >create table ljy.stu10(name varchar(10),age tinyint);

## 建表
create table

# 删
drop table 表名;

root@localhost [(none)] >drop table ljy;
root@localhost [(none)] >drop table ljy.yjl;

# 改
alter table
--- 修改表结构 ---
## 修改表名
alter table 旧表名 rename 新表名;
alter table student rename student1;

## 添加字段
###向后追加（将字段放在最后一列）
alter table 表名 add 字段名 数据类型 属性约束;
alter table student1 add ljy varchar(20) not null;
###指定追加
alter table 表名 add 字段名 数据类型 属性约束 after 需要指定的字段名;
alter table student1 add yl int after ssex;
###放至最前端（将字段放在最前面）
alter table 表名 add 字段名 数据类型 属性约束 first;
alter table student1 add hhh int first;
## 删除字段
alter table 表名 drop 字段名;
alter table student1 drop hhh;

## 字段修改
change
modify

alter table 表名 modify 字段名 数据类型 约束属性;
alter table student1 modify yl char(10);

alter table 表名 change 旧字段名 新字段名 数据类型 约束属性;
alter table student1 change yl yuanli varchar(10);
----------------

DML

# 对表中的数据进行操作
## 对数据的增删改

### 增
insert
insert into 表名('字段1','字段2') 值 ('字段1的值','字段2的值');

insert into linux50.student (sno, sname, sage, ssex, sbirthday, class)
values ('1', '徐导', 20, '1', '2001-09-18', '1'),
       ('2', '曾导', 18, '1', '2003-07-10', '1'),
       ('3', '李导', 25, '1', '2000-12-25', '2');

mysql> insert student(sname,sage,sbirthday,class) values('yuanli',3,NOW(),'L5');       
mysql> select * from student;
+-----+--------+------+------+---------------------+-------+
| sno | sname  | sage | ssex | sbirthday           | class |
+-----+--------+------+------+---------------------+-------+
|   1 | yuanli |    3 | 1    | 2023-07-25 10:18:35 | L5    |
+-----+--------+------+------+---------------------+-------+

mysql> insert student values(5,'yuanli',3,'1',NOW(),'L5'),(6,'wyd',2,'0','2021-11-12 00:00:00','L6');
mysql> select * from student;                                                  
+-----+--------+------+------+---------------------+-------+
| sno | sname  | sage | ssex | sbirthday           | class |
+-----+--------+------+------+---------------------+-------+
|   1 | yuanli |    3 | 1    | 2023-07-25 10:18:35 | L5    |
|   5 | yuanli |    3 | 1    | 2023-07-25 10:21:08 | L5    |
|   6 | wyd    |    2 | 0    | 2021-11-12 00:00:00 | L6    |
+-----+--------+------+------+---------------------+-------+
### 删
delete
delete from 库名.表名 where 条件;

delete from student where sname='yuanli';
delete from student where sno='6';
#### 删除全部(只要条件成立就可以)
delete from student where 1=1;

#### 删除user表中的用户
delete from mysql.user where user='test1' and host='%';

### 改
update
update 库名.表名 set 字段='值' where 条件(唯一值);
update student set ssex='1' where sno=8;

#### 修改全部(只要条件成立就可以)
update student set ssex='1' where 1=1;

使用update代替delete做伪删除

1.为表添加状态列
mysql> alter table student add status enum('0','1') default '1';

2.使用update代替delete做伪删除
mysql> update student set status='0' where sno=10

3.使用select查询时，加上条件
mysql> select * from student where status='1';

DCL

# 授权(可以创建用户)
grant
grant 权限 on 库.表 to 用户@'主机域' identified by '密码' with 可加下面的条件;
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connections_per_hour：一个用户每小时可连接到服务器的次数
max_user_connections：允许同时连接数量
grant option：授权权限

例：
grant select,update,insert on *.* to dev@'%' identified by '123' with max_user_connections 1;

# 回收权限
revoke

## 创建所有权限的用户
grant all on *.* to test1@'%' identified by '123';
## 回收其中的select权限
revoke select on *.* from test1@'%';

DQL

select的基础用法

## 查看数据库
show databases;

## 查看表
show tables;
show tables from ljy;

## 查看建库语句相关信息
show create database ljy3;

## 查看表结构
desc ljy.stu10;

# 查看建表语句
show create table linux50.student;

# 查看创建用户语句
show grants for test1@'%'

# 查看当前所在数据库
select database();

# 查看字符集
show charset;

# 查看校验规则
show collation;

# 查看存储引擎
show engines;

# 查看mysql的进程
show processlist;
show full processlist;

# 查看变量
show variables like '%log%';

select

mysql> show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city            |            // 城市表
| country         |            // 国家表
| countrylanguage |            // 国家语言表
+-----------------+

mysql> desc world.city;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | id号           |
| Name        | char(35) | NO   |     |         | 城市名          |
| CountryCode | char(3)  | NO   | MUL |         | 国家代码        |
| District    | char(20) | NO   |     |         | 省             |
| Population  | int(11)  | NO   |     | 0       | 城市人口数量     |
+-------------+----------+------+-----+---------+----------------+
# 查询所有数据
select * from linux50.student;

# 使用where条件查询数据
select * from linux50.student where sno=10;

# 使用where多条件查询数据
select * from linux50.student where sno=10 or sno=20;
select * from world.city where countrycode='CHN' or countrycode='USA';
select * from world.city where countrycode in ('CHN','USA');

# 使用where范围查询数据
select * from linux50.student where sno>10 and sno<30;

# 模糊查询
select * from linux50.student where sname like 'y%';(以y开头)
select * from linux50.student where sname like '%y%';(中间包含包含y)
select * from linux50.student where sname like '%y';(以y结尾)

# 联合查询 (两条select查询使用union all相连)
select * from world.city where countrycode='CHN' union all select * from world.city where countrycode='USA';

# 分页查询
## 60条数据为一页
select * from world.city limit 60;

## 初始数据0,并以60条数据为一页
select * from world.city limit 0,60;

## 初始数据60,并以60条数据为一页
select * from world.city limit 60,60;

## 初始数据120,并以60条数据为一页
select * from world.city limit 120,60;

# 排序查询
## 升序 （小到大）
select * from world.city where countrycode='CHN' or countrycode='USA' order by population;

## 降序（大到小）
select * from world.city where countrycode='CHN' or countrycode='USA' order by population desc;

group by

函数

# 去重函数
distinct() 

# 统计（用于，查询出来的结果，统计出来有多少行）
count()

# 求和
sum()

# 求平均值函数
avg()

# 求最大值函数
max()

# 求最小值函数
min()

#此时此刻，我想吟诗一首
1.遇到统计想函数
2.形容词前groupby
3.函数中央是名词
4.列名select后添加

#统计中国各个省的人口数量(练习)
sum(population)
group by district
select district,sum(Population) from city where countrycode='CHN' group by district;

#统每个国家的城市数量(练习)
count(name)
group by countrycode
select countrycode,count(name) from city group by countrycode;

select countrycode as '国家',count(name) as '城市数量' from city group by countrycode order by count(name) limit 10;

select 高级用法(连表查询)

        2    3    1 
mark:   70   80   90

        1     2    3
name: zhang3 li4 wang5

传统连接

select test1.name,test2.mark from test1,test2 where test1.id=test2.id and name='zhang3';

# 世界上小于100人的人口城市是哪个国家的？
city Population
country
select country.name,city.name,city.population 
from city,country 
where city.countrycode=country.code and city.population<100;
+----------+-----------+------------+
| name     | name      | population |
+----------+-----------+------------+
| Pitcairn | Adamstown |         42 |
+----------+-----------+------------+

# 世界上小于100人的人口城市是哪个国家的,说什么语言？
countrylanguage
city
country
select country.name,city.name,city.population,countrylanguage.language
from city,country,countrylanguage 
where city.countrycode=country.code and country.code=countrylanguage.countrycode and city.population<100;

内连接

join on
## 小表在前，大表在后，字段多就属于大表                                                       

# 世界上小于100人的人口城市是哪个国家的？
select country.name,city.name,city.population
from city join country
on city.countrycode=country.code
where city.population<100;

# 世界上小于100人的人口城市是哪个国家的,说什么语言？
select country.name,city.name,city.population,countrylanguage.language
from countrylanguage join city join country  
on city.countrycode=country.code and country.code=countrylanguage.countrycode 
where city.population<100;

select country.name,city.name,city.population,countrylanguage.language
from city
join country on city.countrycode=country.code
join countrylanguage on country.code=countrylanguage.countrycode
where city.population<100;

自连接

# 自动找到等价条件 natural join
##前提：等价条件的列名和数据值必须一致

# 世界上小于100人的人口城市说什么语言？
select city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;

# 世界上小于100人的人口城市是哪个国家,说什么语言？
select city.CountryCode,city.name,countrylanguage.language
from city natural join countrylanguage
where city.population<100;

外连接 outer join

• 左外连接 left join

  select city.name,city.countrycode,country.name 
  from city left join country
  on city.countrycode=country.code
  and city.population<100;
  

• 右外连接 right join

  select city.name,city.countrycode,country.name 
  from city right join country 
  on city.countrycode=country.code 
  and city.population<100;

额外拓展

库名：linux50 字符集：utf8 校验规则：utf8_general_ci

create database if not exists linux50 charset utf8 collate utf8_general_ci;

表名：student（学生表）

# 创建表结构
create table linux50.student(
sno bigint(20) not null primary key auto_increment comment'学号（主键）',
sname varchar(10) not null comment'学生姓名',
sage tinyint unsigned  not null comment'学生年龄',
ssex enum('0','1') not null default '1' comment'学生性别',
sbirthday datetime null comment'学生生日',
class varchar(5) not null comment'学生班级');

# 写入内容
INSERT INTO linux50.student (sname, sage, sbirthday, class)
VALUES ( '徐导', 20, '2001-09-18', '1'),
       ( '曾导', 18, '2003-07-10', '1'),
       ( '李导', 25, '2000-12-25', '2');

INSERT INTO linux50.student (sname, sage, ssex,sbirthday, class)
VALUES ( 'yl', 20, '0', '2001-09-18', '1'),
       ( 'll', 18, '0', '2003-07-10', '1'),
       ( 'uu', 25, '1', '2000-12-25', '2');

表名：course（课程表）

# 创建表结构
create table linux50.course(
cno bigint(20) not null primary key auto_increment comment'课程号（主键）',
cname varchar(10) not null comment'课程名字',
tno int(3) zerofill not null  comment'教师编号');

# 写入内容
INSERT INTO linux50.course (cname, tno)
VALUES ('英语', '001'),
       ('语文', '002'),
       ('数学', '003');

表名：score（成绩表）

# 创建表结构
create table linux50.score(
sno bigint(20) not null comment'学号（主键）',
cno varchar(20) not null comment'课程号（主键）',
mark float(4,1) not null comment'成绩',
primary key (sno,cno));

# 写入内容
INSERT INTO linux50.score (sno, cno, mark)
VALUES ('1', '1', '90.0'),
       ('2', '1', '10.0'),
       ('3', '1', '60.0'),
       ('1', '2', '90.0'),
       ('2', '2', '99.5'),
       ('3', '2', '80.0'),
       ('1', '3', '80.5'),
       ('2', '3', '60.0'),
       ('3', '3', '88.0');

表名：teacher（教师表）

# 创建表结构
create table linux50.teacher(
`tno` bigint(3)  unsigned zerofill not null auto_increment comment'教师编号（主键）',
`tname` varchar(10) not null comment'教师姓名',
`tage` tinyint unsigned not null comment'教师年龄',
`tsex` enum('0','1') not null default '1' comment'教师性别',
`prof` varchar(10) null comment'教师职称',
`depart` varchar(10) not null comment'教师部门',
primary key (`tno`)); 

# 写入内容
INSERT INTO linux50.teacher (tno, tname, tage, prof, depart)
VALUES ('001', '曾导', 18, '教学总监', '语言系'),
       ('002', '徐导', 50, '讲师', '文学系'),
       ('003', '李导', 80, '助教', '科学系');

1.查询student表中的所有记录的sname、ssex和class列。
select sname,ssex,class from student;

2.查询教师所有的单位即不重复的depart列。
select distinct(depart) from teacher;

3.查询student表的所有记录。
select * from student;

4.查询score表中成绩在60到80之间的所有记录。
select * from score where mark>60 and mark<80;

5.查询score表中成绩为85，86或88的记录。
select * from score where mark=85 or mark=86 or mark=88;
select * from score where mark in (85,86,88);                                             

6.查询student表中1班或性别为“女”的同学记录。
select * from student where ssex=0 or class=1;

7.以class降序查询Student表的所有记录。
select * from student order by class desc;

8.以cno升序、mark降序查询Score表的所有记录
select * from score order by cno , mark desc;

9.查询2班的学生人数。
select count(class) from student where class=2;

10.查询”曾志高翔“教师任课的学生成绩。
select * from score where cno=1;

11.查询score表中成绩在60到80之间的所有记录,和同学的名字和学科
score成绩 sno学号 cno课程号
student学生表 sno学号 sname学生名称
course课程表 cno课程号 cname课程名称

select score.mark,student.sname,course.cname 
from score
join student on score.sno=student.sno
join course on score.cno=course.cno
where score.mark >= 60 and score.mark <= 80;

12.查询score表中成绩为85，86或88的记录。和同学的名字和学科
select score.mark,student.sname,course.cname 
from score
join student on score.sno=student.sno
join course on score.cno=course.cno
where score.mark in (85,86,88);

13.查询”曾老师“教师任课的学生成绩。
teacher（教师表） tname tno
course课程表 cno课程号 cname课程名称 tno
score成绩 sno学号 cno课程号
student学生表 sno学号 sname学生名称
select teacher.tname,course.cname,student.sname,score.mark
from teacher
join course on course.tno=teacher.tno
join score on score.cno=course.cno
join student on student.sno=score.sno
where teacher.tname='曾导';

14.查询语文课程所有男生的成绩并且查出对应课程的教师名，职称，及所在部门。
score成绩 sno学号 cno课程号 mark
course课程表 cno课程号 cname课程名称 tno
student学生表 sno学号 sname学生名称 ssex
teacher（教师表） tname tno prof depart
select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark
from teacher
join course on teacher.tno=course.tno
join score on score.cno=course.cno
join student on student.sno=score.sno
where student.ssex='1' and course.cno='1';

15.把14题查出的成绩按照降序排序。
select teacher.tname,teacher.prof,teacher.depart,student.sname,course.cname,score.mark
from teacher
join course on teacher.tno=course.tno
join score on score.cno=course.cno
join student on student.sno=score.sno
where student.ssex='1' and course.cno='1' order by score.mark desc;

使用shell脚本编写登录框，用户创建的账号密码存储在MySQL中

# 创建脚本
vim login.sh
#!/bin/bash

# 提示用户输入用户名和密码
read -p "请输入用户名: " username
read -s -p "请输入密码: " password
echo

# 连接 MySQL 数据库并插入用户信息
mysql -uroot -p123 -e "insert into users (username, password) VALUES ('$username', '$password');"

# 显示注册成功提示信息
echo "注册成功！"

# 授权执行权限
chmod +x login.sh

# 建库
create database if not exists login charset utf8 collate utf8_general_ci;

# 建表
create table login.users( 
username varchar(10) not null primary key comment'账号',
password varchar(10) not null comment'密码');

# 执行脚本
[root@db02 ~]# sh login.sh 
请输入用户名: root
请输入密码: 1

# 查询结果
mysql> select * from login.users;
+----------+----------+
| username | password |
+----------+----------+
| root     | 1        |
+----------+----------+
1 row in set (0.00 sec)