SQL语句

SQL语句

mysql的连接工具

自带连接工具(客户端)

# mysql

# 选项
-u：user指定MySQL的用户
-p：password指定MySQL用户的密码
-S：socket指定socket文件的位置
-h：host指定主机IP地址
-e：exec执行SQL语句
-p：port指定端口

第三方连接工具（客户端）

• Navicat
• SQLmanager
• SQLyog

MySQL启动关闭流程

启动

/etc/init.d/mysql start
systemctl start mysql
mysql_safe  --选项

关闭

/etc/init.d/mysqld stop
systemctl stop mysqld
mysqladmin -uroot -p'123' shutdown

# 不要轻易使用，强制kill会丢数据
kill -9 pid 
kill all mysqld
pkill mysql

MySQL实例初始化配置

• 预编译

• 命令行

• 配置文件（读取顺序）

  /etc/my.cnf 10
  /etc/mysql/my.cnf 20
  $basedir/my.cnf 30
  --defaults-extra-file=/opt/my.cnf
  ~/.my.cnf 40

# 优先级
~/.my.cnf > --defaults-extra-le=/opt/my.cnf > $basedir/my.cnf > /etc/mysql/my.cnf >/etc/my.cnf

注意：如果启动MySQL加了--defaults-extra-file=/etc/my.cnf 选项，其他位置的配置文件不读取

思考

# cmake
socket=/application/mysql/tmp/mysql.sock
# 命令行
--socket=/tmp/mysql.sock
#配置文件：
/etc/my.cnf中[mysqld]标签下：socket=/opt/mysql.sock

#default参数：
--defaults-file=/tmp/a.txt配置文件中[mysqld]标签下：socket=/tmp/test.sock

/application/mysql/tmp/mysql.sock
/tmp/mysql.sock
/opt/mysql.sock
/tmp/test.sock

mysqld --defaults-file=/tmp/a.txt --socket=/tmp/mysql.sock

/tmp/mysql.sock

# 结论
默认配置 优先级
1.命令行
2.配置文件
	- ~/.my.cnf
	- --defaults-extra-file=/opt/my.cnf
	- $basedir/my.cnf
	- /etc/mysql/my.cnf
	- /etc/my.cnf
3.编译参数

初始化配置的作用

1.影响实例的启动

2.影响到客户端的连接

[mysql] [server] # 这两个标签下的配置，都是来影响服务端启动的
[mysql] [mysqladmin] [mysqldump] # 这几个标签，影响对应的客户端命令
[client] # 这个标签，影响所有的客户端命令

注意：修改客户端配置，不需要重启MySQL，修改服务端配置[MySQL]需要重启MySQL

[mysqld]
skip_name_resolve
basedir=/application/mysql
datadir=/application/mysql/data
server_id=10
socket=/opt/mysql.sock

[client]
user=root
password=abc
socket=/opt/mysql.sock

客户端命令

MySQL

# 查看命令帮助
？ \？ help \h

# 查看状态
status \s

# 退出
exit quit \q

# 结束当前SQL语句
 MySQL5.6 \c
 MySQL5.7 Ctrl+c

# 在MySQL中执行系统命令
systemctl \!

# 临时将操作记录到指定文件中
tee \T
tee /var/log/mysql.log
/T /var/log/mysql.log

# 切换数据库
use \u
use MySQL
\u MySQL

# 导出数据
[root@db01 ~]# mysqldump -uroot -p'123' -B wc >/tmp/wc.sql

# 导入数据
source \.
source /tmp/wc.sql
\. /tmp/wc.sql

# 格式化（key：value）方式，显示数据
\G
mysql> select * from mysql.user\G

# 客户端配置，显示当前所在数据库及登录用户
[client]
prompt="\u@\h:\d>"
root@localhost:(none)>

MySQLadmin

# 修改密码或者设置密码
mysqladmin password
[root@db01 ~]# mysqladmin -uroot -p'123' password  '456'

# 关闭mysql
mysqladmin shutdown
[root@db01 ~]# mysqladmin -uroot -p'456' shutdown

# 检测mysql是否存活
mysqladmin ping
[root@db01 ~]# mysqladmin -uroot -p'456' ping

# 查看mysql的状态
mysqladmin status
[root@db01 ~]# mysqladmin -uroot -p'456' status

# 查看mysql的默认配置
mysqladmin variables
[root@db01 ~]# mysqladmin -uroot -p'456' variables

# 在库外创建数据库
mysqladmin create 库名
[root@db01 ~]# mysqladmin -uroot -p'456' create wc

# 在库外删除数据库
mysqladmin drop 库名
[root@db01 ~]# mysqladmin -uroot -p'456' drop wc

# 重新加载数据库
mysqladmin reload
[root@db01 ~]# mysqladmin -uroot -p'456' reload

# 刷新授权表
mysqladmin flush-log
[root@db01 ~]# mysqladmin -uroot -p'456' flush-log

什么是SQL语句

结构化的查询语句标准：SQL-92

SQL语句的分类

DDL

Database Definition Langua
数据		定义		语言

# 开发规范
(01) 表名不能大写,数字开头,16个字符串
(02) 表名和业务有关
(03) drop 语句禁止
(04) 选择合适的数据类型
(05) 必须要有主键
(06) 列尽量非空约束
(07) 减少外键约束
(08) 必须设置存储引擎和字符集
(09) 列必须要有注释
(10) 对于非负数设置数据类型约束--无符号

库

# 增
语法：
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
}

# 增
create database 库名；
create schema	库名；
create database 库名 character set utf8;
create database 库名 charset utf8；
# 判断库是否存在
create database if not exists 库名 charset utf8 collate utf8_bin;

# 删
drop database 库名；

# 改
修改字符集
alter database wc charset utf-8；
# DQL查看数据库的字符集
mysql> show create database wc;
+----------+---------------------------------------------------------------+
| Database | Create Database                                               |
+----------+---------------------------------------------------------------+
| wc       | CREATE DATABASE `wc` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+---------------------------------------------------------------+
1 row in set (0.00 sec)

# 查看库下表
root@localhost:school>show tables;

表

# 增
create table 表名(字段1 数据类型 约束,字段 数据类型 约束....)
建表，至少要给的是 字段名称和数据类型
create table zls.student(id int not null primary key auto_increment,namevarchar(10),age tinyint,gender enum('0','1'));

create table zls.student2(
id int,
name varchar(10),
age tinyint,
gender enum('0','1'));

# 数据类型
int： 整数 -2^31 ~ 2^31 -1
varchar：字符类型 （变长）
char： 字符类型 （定长）
tinyint： 整数 -128 ~ 127
enum： 枚举类型
enum('A','B','C','D')
datetime： 时间类型 年月日时分秒

# 约束
not null： 非空
primary key： 主键（唯一且非空的）一张表只能有一个主键
auto_increment： 自增（此列必须是：primary key或者unique key）
unique key： 唯一键，单独的唯一的 唯一键 + not null
default： 默认值
unsigned： 无符号（非负）
comment： 注释

create table sstu(
id int primary key auto_increment comment '学号',
name varchar(10) not null comment '学生姓名',
age tinyint unsigned not null default 18 comment '学生年龄',
gender enum('0','1') not null default '1' comment '学生性别');

# 删
drop table 表名

# 改
# 修改表名
alter table 表名 rename 新表名;
root@localhost:school>alter table student1 rename stu;

# 添加字段（将字段添加在表的最后一列）
root@localhost:school>alter table stu add cjk varchar(10) not null;

# 带默认空间的字段
root@localhost:school>alter table stu add wcwc varchar(10) default 'dsb';

# 按照指定位置添加字段
root@localhost:school>alter table stu add zh1 char(1) after wc;

# 将字段添加在最前面的一列
alter table stu add cjk char(1) first;

# 删除字段
root@localhost:school>alter table stu drop wcwc;

# 修改字段名 数据类型 属性
root@localhost:school>alter table stu change zh1 zhzh varchar(10);

DML

Data Manipulation Language
数据 		操作 		语言
所有的DML都是操作表内容的

# 增
插入单条数据
root@localhost:school>insert into stu(zh,id,name) value(1,44,'wcdsb');

# 插入多条数据
insert into stu(name,gender,age,date,phone,bir,id)
value('wc1','f',255,'2022-08-10','136',NOW(),4),
('wc2','m',38,'2022-09-10','137',NOW(),5)

# 默认字段不加
insert into stu(name,bir,phone) value('liquanyi',NOW(),'138');

# 不规范写法
insert stu value(7,'liangkang',18,'m',NOW(),'139',NOW());

# 删
delete from stu；（危险加条件）

# 加条件
delete from stu where name='jll'；

# 全部删除
delete from stu where 1=1;

# 改
使用update一定要加条件
update stu set id=1 where name='wc';

# 使用update代替delete删除数据
1.给表加一个状态列
root@localhost:school>alter table stu add status enum('0','1') default '0';

2.使用update修改数据
root@localhost:school>update stu set status='1' where id='1';

3.查询的时候，使用where条件查询
root@localhost:school>select * from stu where status='1';

DCL

Database Control Language
数据 		控制	  语言

# 赋予权限
5.6和5.7区别：5.7老版本，grant赋予权限，如果该用户不存在，则无法创建，5.6和5.7新版本可以直接创建用户
grant all on *.* to test@'%' identified by '123';
grant 权限1,权限 on 库.表 to 用户@'主机域' identified by '密码';

grant all on *.* to test@'%' identified by '123' with grant option;

grant all on *.* to zls6@'%' identified by '123'
without max_queries_per_hour 3 ## 限制该用户一小时内，只能查询3次
max_updates_per_hour 1 ## 限制用户一小时，只能执行一次update
max_connections_per_hour 1 ## 限制用户一小时内，只能连接一次数据库
max_user_connections 1 ## 限制用户，只能同时一个用户连接
grant option;

# 回收权限
revoke
revoke 权限 on 库.表 from 用户@'主机域';
revoke delete on *.* from dev@'%';

DQL

Database Query Language
数据 		查询  语言

mysql> desc wc.dd;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10) | NO   |     | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

# 事务控制语句
Transaction Control Language
Database Transaction Language

作业

# 建库
create database linux50 character set utf8 collate utf8_general_ci; 

# 建表
# student
create table student(
sno int(20) not null primary key comment'学号主键',
sname varchar(10) not null comment'学生姓名',
sage tinyint not null comment'学生年龄',
ssex enum('0','1') not null default '1' comment'学生性别(1是男，0是女)默认是男',
sbirthday datetime comment'学生生日',
class varchar(10) not null  comment'学生班级');

# 插入小组数据
insert into student(sno,sname,sage,ssex,sbirthday,class) 
value(1,'王成','18','1',NOW(),'Linux2期'),
(2,'罗伟','19','1',NOW(),'Linux2期'),
(3,'周恒','18','1',NOW(),'Linux2期'),
(4,'计磊','38','0',NOW(),'Linux2期');

# course
create table course(
cno int(20) not null primary key comment'课程号主键',
cname varchar(10) not null comment'课程名称',
tno varchar(10) not null comment'教师编号');

# 插入教师信息
insert into course(cno,cname,tno)
value('1','Linux','666'),
('2','PHP','777'),
('3','python','888');

# score
create table score(
sno int(20) not null primary key comment'学号主键',
cno int(20) not null comment'课程主键',
mark float(4) not null comment'成绩');

# 插入成绩信息
insert into score(sno,cno,mark)
value(1,'1','90.0'),
(2,'1','10.0'),
(3,'1','60.0');

# teacher
create table teacher(
tno int(20) not null primary key comment'教师编号',
tname varchar(10) not null comment'教师姓名',
tage tinyint unsigned not null comment'教师年龄',
tsex enum('0','1') not null default '1' comment'学生性别(1是男，0是女)默认是男',
prof varchar(10) comment'教师职称',
depart varchar(10) not null comment'教师部门');

# 插入教师信息
insert into teacher(tno,tname,tage,tsex,prof,depart)
value(001,'曾志高翔','18','1','教学总监','语言系'),
(002,'徐亮伟','50','1','讲师','文学系'),
(003,'李永宜','80','1','助教','科学系');