SQL基础应用，基本操作

SQL基础应用，基本操作

insertupdatedeletegrantrevoke

欢迎来到 来到大浪涛天的博客 ！

• 一、SQL基础应用
  • 1. mysql内置的功能
    • 1-1. 连接数据库
    • 1-2. MySQL内置命令
  • 2. SQL基础应用
    • 2-1. SQL介绍
    • 2-2. SQL常用种类
    • 2-3. SQL引入-数据库的逻辑结构
    • 2-4. 字符集 (charset)
    • 2-5. 排序规则: collation
  • 3. 数据类型介绍
    • 3-1. 数字
    • 3-2. 字符串
    • 3-3. enum 枚举数据类型
    • 3-4. 时间
    • 3-5. DDL的应用
    • 3-6. DDL-表定义
    • 3-7. DCL
      • 3-7-1. grant
      • 3-7-2. revoke
    • 3-8. DML
      • 3-8-1. insert

一、SQL基础应用

1. mysql内置的功能

1-1. 连接数据库

-u: 用户
-p：密码
-S：后面接sock文件的路径
-h：接主机名或者ip地址，添加-h的时候一定要在数据库里做好授权远程登陆的用户 
-P：接MySQL的端口号，使用-P前面一定要加-h
-e：免交互式登陆，-e后面可以接MySQL的命令，而不需要登陆数据库里面
<：恢复数据使用，可以在shell下将sql文件导入数据库

例如:

1. mysql -uroot -p -S /tmp/mysql.sock
2. mysql -uroot -p -h10.0.0.51 -P3306
3. -e 免交互执行sql语句
[root@db01 ~]# mysql -uroot -p -e "show databases;"
4. < 恢复数据
[root@db01 ~]# mysql -uroot -p123 	 /root/world.sql

1-2. MySQL内置命令

• help 打印mysql帮助
• \c ctrl+c 结束上个命令运行
• \q quit; exit; ctrl+d 退出mysql
• \G 将数据竖起来显示
• source 恢复备份文件

mysql> source /root/t100w.sql;
Query OK, 0 rows affected (0.02 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)

2. SQL基础应用

2-1. SQL介绍

1. 结构化的查询语言
2. 关系型数据库通用的命令
3. 遵循SQL92的标准(SQL_MODE)

2-2. SQL常用种类

1. DDL 数据定义语言
2. DCL 数据控制语言
3. DML 数据操作语言
4. DQL 数据查询语言

2-3. SQL引入-数据库的逻辑结构

1. 库

• 库名字
• 库属性:字符集,排序规则

2. 表

• 表名
• 表属性:存储引擎类型,字符集,排序规则
• 列名
• 列属性:数据类型,约束,其他属性
• 数据行

2-4. 字符集 (charset)

相当于MySQL的密码本(编码表)

show charset;
utf8		  : 3个字节
utf8mb4 (建议): 4个字节,支持emoji

2-5. 排序规则: collation

mysql> show collation;
对于英文字符串的,大小写的敏感
utf8mb4_general_ci 			大小写不敏感
utf8mb4_bin  				大小写敏感(存拼音,日文)

3. 数据类型介绍

3-1. 数字

整数

1. tinyint (0-255或者-127-128)
2. int (-2^31-231 只能存10位数)
3. 浮点数，略

3-2. 字符串

1. char(100)
   • 定长字符串类型,不管字符串长度多长,都立即分配100个字符长度的存储空间,未占满的空间使用"空格"填充
2. varchar(100)
   • 变长字符串类型,每次存储数据之前,都要先判断一下长度,按需分配此盘空间.
   • 会单独申请一个字符长度的空间存储字符长度(少于255,如果超过255以上,会占用两个存储空间)
3. 如何选择这两个数据类型?
   • 少于255个字符串长度,定长的列值,选择char
   • 多于255字符长度,变长的字符串,可以选择varchar

3-3. enum 枚举数据类型

address enum('sz','sh','bj'.....)
			  1    2    3   
以上数据类型可能会影响到索引的性能

3-4. 时间

1. datetime

• 范围为从 1000-01-01 00:00:00.000000 至 9999-12-31 23:59:59.999999。

2. timestamp

• 范围为从 1970-01-01 00:00:00.000000 至 2038-01-19 03:14:07.999999。

3-5. DDL的应用

库的定义:

1. 创建test01库，要求utf8，区分大小写

create database test01 charset utf8mb4 collate utf8mb4_bin;

2. 查看数据库

show databases;

3. 查看创建数据库的具体情况

show create database test01;

4. 删除数据库

drop database zabbix;

5. 字符集修改，要注意一定要从小往大改，比如只能utf8改为utf8mb4,而不能utf8mb4改为utf8，不然会出现乱码

show create database wordpress;
alter database wordpress charset utf8mb4;
show create database wordpress;

6. 关于库定义规范

• 库名使用小写字符
• 库名不能以数字开头
• 库名不能是MySQL内部的关键字
• 建库必须设置字符集

3-6. DDL-表定义

1. 建表

• 表名,列名,列属性,表属性

2. 列属性

• PRIMARY KEY : 主键约束,表中只能有一个,非空且唯一.
• NOT NULL : 非空约束,不允许空值
• UNIQUE KEY : 唯一键约束,不允许重复值
• DEFAULT : 一般配合 NOT NULL 一起使用.
• UNSIGNED : 无符号,一般是配合数字列,非负数
• COMMENT : 注释
• AUTO_INCREMENT : 自增长的列
  建表事例：

CREATE TABLE stu (
id INT PRIMARY KEY NOT NULL AUTO_INCREMENT COMMENT '学号',
sname VARCHAR(255) NOT NULL  COMMENT '姓名',
age TINYINT UNSIGNED NOT NULL DEFAULT 0 COMMENT '年龄',
gender ENUM('m','f','n') NOT NULL DEFAULT 'n' COMMENT '性别',
intime DATETIME NOT NULL DEFAULT NOW() COMMENT '入学时间'
)ENGINE INNODB CHARSET utf8mb4;

3. 建表规范 *****
   --- 1. 表名小写字母,不能数字开头,
   --- 2. 不能是保留字符,使用和业务有关的表名
   --- 3. 选择合适的数据类型及长度
   --- 4. 每个列设置 NOT NULL + DEFAULT .对于数据0填充,对于字符使用有效字符串填充
   --- 5. 没个列设置注释
   --- 6. 表必须设置存储引擎和字符集
   --- 7. 主键列尽量是无关列数字列,最好是自增长
   --- 8. enum类型不要保存数字,只能是字符串类型

4. 查询建表信息

SHOW TABLES;
SHOW CREATE TABLE stu;
DESC stu;

5. 创建一个表结构一样的表

CREATE TABLE test LIKE stu;

6. 删表(不代表生产操作)

DROP TABLE test;

7. 修改表中的列, 注意在alter语句中，在线操作的话均会进行锁表，会影响业务，如果要影响最小最好是凌晨操作，因为锁表时间是按数据量的多少来确认锁表多久，如果要减缓锁表可以用第三方工具如pt-osc等
   --- 在stu表中添加qq列 *****

DESC stu;
ALTER TABLE stu ADD qq VARCHAR(20) NOT NULL COMMENT 'qq号';

--- 在sname后加微信列 ***

ALTER TABLE stu ADD wechat VARCHAR(64) NOT NULL UNIQUE COMMENT '微信号' AFTER sname;

--- 在id列前加一个新列num ***

ALTER TABLE stu ADD num INT NOT NULL UNIQUE COMMENT '身份证' FIRST ;
DESC  stu;

--- 把刚才添加的列都删掉(危险,不代表生产操作) ***

ALTER TABLE stu DROP num;
DESC stu;
ALTER TABLE stu DROP qq;
ALTER TABLE stu DROP wechat;

--- 修改sname数据类型的属性 ***

DESC stu;
ALTER TABLE stu MODIFY sname VARCHAR(64) NOT NULL COMMENT '姓名';

--- 将gender 改为 sex 数据类型改为 CHAR 类型 ***

ALTER TABLE stu  CHANGE gender sex CHAR(4) NOT NULL COMMENT '性别';

3-7. DCL

3-7-1. grant

1. 创建一个管理员用户root，可以通过10网段，管理数据库.

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

2. 创建一个应用用户wordpress，可以通过10网段，wordpress库下的所有表进行SELECT,INSERT, UPDATE, DELETE.

grant SELECT,INSERT, UPDATE, DELETE on wordpress.* to wordpress@'10.0.0.%' identified by '123';

3-7-2. revoke

show  grants for wordpress@'10.0.0.%';
mysql> revoke delete on wordpress.*  from 'wordpress'@'10.0.0.%';
mysql> show  grants for wordpress@'10.0.0.%';

3-8. DML

3-8-1. insert

插入数据先要查看表结构，DESC stu;

1. 最简单，但是要严格按顺序来插入数值

INSERT stu VALUES(1,'zs',18,'m',NOW());
SELECT * FROM stu;

2. 最规范,可以按表名后面需要的字段顺序来写

INSERT INTO stu(id,sname,age,sex,intime)
VALUES (2,'ls',19,'f',NOW());

3. 针对性的录入数据,可以选定需要录的数值

INSERT INTO stu(sname,age,sex)
VALUES ('w5',11,'m');

4. 一次性录入多行

INSERT INTO stu(sname,age,sex)
VALUES 
('aa',11,'m'),
('bb',12,'f'),
('cc',13,'m');

5. update(一定要加where条件)

UPDATE stu SET sname='aaa';
SELECT * FROM stu;
UPDATE stu SET sname='bb' WHERE id=6;

6. delete (一定要有where条件)

DELETE FROM stu;
DELETE FROM stu WHERE id=9;

7. 生产中屏蔽delete功能

使用update替代delete 
ALTER TABLE stu ADD is_del TINYINT DEFAULT 0 ;
UPDATE stu SET is_del=1 WHERE id=7;
SELECT * FROM stu WHERE is_del=0;