SQL语句与权限管理
一.介绍
SQL语言
SQL(Structured Query Language 即结构化查询语言)
作用:主要用于存取数据,查询数据,更新数据和管理关系数据库系统。
二.sql语言类型
SQL语言类型
Data Definition Language(DDL)
DDL语句 数据库定义语言: 数据库、表、视图、索引、存储过程,例如CREATE(创建) DROP(删除) ALTER
(修改) DECLARE(声明)
Data Manipulation Language(DML)
DML语句 数据库操纵语言: 插入数据INSERT、删除数据DELETE、更新数据UPDATE、SELECT查询
Data Query Language(DQL)
DQL语句 数据库查询语言: 查询数据SELECT(GRANT 授权, REVOKE 取消, COMMIT 提交, ROLLBACK 回滚)
Data Control Language(DCL)
DCL语句 数据库控制语言: 例如控制用户的访问权限GRANT、REVOKE(SELECT)
涉及到的名词
数据库服务器
三.数据库管理
- 默认数据库
mysql> show databases;
information_schema 虚拟库,主要存储了系统中的一些数据库对象的信息,例如用户表信息,列信息,权限信
息,字符信息等
mysql 授权库,主要存储系统用户的权限信息
performance_schema 主要存储数据库服务器的性能参数
sys 主要存储数据库的性能参数
- 表的管理单元
表 记录的管理单元
记录 字段的管理单元
字段 字段名,字段类型(长度),字段约束组成
类型 字符,数字,日期
约束 不能为空,自动增长
SQL语句注意事项
-
每条SQL语句结束时要以『;』做为结束符(除了『use』命令);
-
SQL语句的关键字不区分大小写(除了库名和表名);
-
在查询数据库信息和表信息时,可以以『\G』做为结束符,表示以文本模式输出;
-
当你不需要一条语句的输出结果时,可以以『\c』结束;
-
当我们想要在命令行上执行SQL语句时,可以用『mysql -e』的参数将结果显示到shell上,如:
# mysql -e “show databases \G”
- 如需获取SQL语句的帮助可以使用『help』命令,如:
mysql> help create
如需进一步获取帮助可以继续使用『help』命令,如:
mysql> help create database
mysql> ! ls 在mysql中执行shell命令
mysql> \q 退出mysql,等价于exit和quit
exit; \q; quit; 退出数据库
\c 取消本行命令
\G 将查询后的结果竖立起来
\s 显示当前的服务器状态
\h 帮助信息
specification规格
四.mysql数据类型
1.常见数据类型
数值类型:
整数类型 TINYINT SMALLINT MEDIUMINT INT BIGINT
浮点数类型 FLOAT DOUBLE
定点数类型 DEC
位类型
BIT
FLOAT DOUBLE (近似值,类型代表近似数字数据值)
定点数类型 DEC (精确值,在保持精度很重时用这些类型,例如使用货币的数据)
位类型
BIT (该BIT数据类型被用于存储比特值。一种 能够存储位值的类型。 范围从1到64)
字符串类型:
CHAR系列
CHAR VARCHAR (其长度指示要存储的最大字符数)
TEXT系列 TINYTEXT TEXT MEDIUMTEXT LONGTEXT
BLOB 系列
TINYBLOB BLOB MEDIUMBLOB LONGBLOB
BINARY系列 BINARY VARBINARY
枚举类型:
ENUM (多个选项,只能选择一个)
集合类型: SET (多个选项,可以多选)
时间和日期类型:
DATE TIME DATETIME TIMESTAMP YEAR
2.数据类型测试
**===整数类型测试:tinyint,int **
作用:用于存储用户的年龄、游戏的Level、经验值等。
LAB1:
mysql> create table test1(
-> tinyint_test tinyint,
-> int_test int
-> );
mysql> desc test1;
+--------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| tinyint_test | tinyint(4) | YES | | NULL | |
| int_test | int(11) | YES | | NULL | |
+--------------+------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into test1 values (111,111);Query OK, 1 row affected (0.09 sec)
mysql> insert into test1(tinyint_test) values(128);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
mysql> insert into test1(int_test) values(2147483647);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test1(int_test) values(2147483648);
ERROR 1264 (22003): Out of range value for column 'int_test' at row 1
LAB2: 无符号整形测试
mysql> create table test2(
-> tinyint_test tinyint unsigned, //约束条件unsigned限定只能存正值(无符号)
-> int_test int unsigned
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> desc test2;
+--------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| tinyint_test | tinyint(3) unsigned | YES | | NULL | |
| int_test | int(10) unsigned | YES | | NULL | |
+--------------+---------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into test2(tinyint_test) values(255);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test2(int_test) values(2147483648);
Query OK, 1 row affected (1.87 sec)
mysql> insert into test2 values(-20,-20);
ERROR 1264 (22003): Out of range value for column 'tinyint_test' at row 1
测试整数类型的显示宽度
mysql> create table t1 (
-> id1 int,
-> id2 int(6)
-> );
mysql> desc t1;
+-------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| id1 | int(11) | YES | | NULL | |
| id2 | int(6) | YES | | NULL | |
+-------+---------+------+-----+---------+-------+
45/17846/178
mysql> insert into t1 values(1,1);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| id1 | id2 |
+------+------+
| 1 | 1 |
+------+------+
1 row in set (0.00 sec)
mysql> create table t2 (
-> id1 int zerofill,
-> id2 int(6) zerofill
-> );
Query OK, 0 rows affected (0.05 sec)
mysql> desc t2;
+-------+---------------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------------+------+-----+---------+-------+
| id1 | int(10) unsigned zerofill | YES | | NULL | |
| id2 | int(6) unsigned zerofill | YES | | NULL | |
+-------+---------------------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into t2 values(2,2);
Query OK, 1 row affected (0.01 sec)
mysql> select * from t2;
+------------+--------+
| id1 | id2 |
+------------+--------+
| 0000000002 | 000002 |
+------------+--------+
1 row in set (0.00 sec)
mysql> insert into t2 values(3,2222222);
//插入大于宽度限制的值,仍然可以存储
Query OK, 1 row affected (0.03 sec)
mysql> select * from t2;
+------------+---------+
| id1 | id2 |
+------------+---------+
| 0000000002 | 000002 |
| 0000000003 | 2222222 |
+------------+---------+
2 rows in set (0.00 sec)
显示宽度,不会影响,正常储存47/178
**===浮点数类型测试: **
作用:用于存储用户的身高、体重、薪水等
浮点数和定点数都可以用类型名称后加(M,D)的方式来表示,(M,D)表示一共显示M位数字(整数位
+小数位),其中D位于小数点后面,M和D又称为精度和标度。
float和double在不指定精度时,默认会按照实际的精度(由实际的硬件和操作系统决定)来显示,
而decimal在不指定精度时,默认的整数位为10,默认的小数位为0
定点数在MySQL内部以字符串形式存储,比浮点数更精确,适合用来表示货币等精度高的数据。
mysql> create table test4(float_test float(5,2));
//一共5位,小数占2位
Query OK, 0 rows affected (0.00 sec)
mysql> desc test4;
+------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+------------+------+-----+---------+-------+
| float_test | float(5,2) | YES | | NULL | |
+------------+------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql> insert into test4 values (10.2), (70.243), (70.246);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from test4;
+------------+
| float_test |
+------------+
| 10.20 |
| 70.24 |
| 70.25 |
+------------+
3 rows in set (0.00 sec)
mysql> insert into test4 values (1111.2);
ERROR 1264 (22003): Out of range value for column 'float_test' at row 1
定点数decimal测试:(储存方式:字符串形式的储存!)
mysql> create table test5(decimal_test decimal(5,2));
mysql> insert into test5 values (70.245);
Query OK, 1 row affected, 1 warning (0.05 sec)
mysql> show warnings;
+-------+------+---------------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------------+
| Note | 1265 | Data truncated for column 'decimal_test' at row 1 |
+-------+------+---------------------------------------------------+
1 row in set (0.00 sec)
**===位类型测试:BIT **
BIT(M)可以用来存放多位二进制数,M范围从1~64,如果不写默认为1位
对于位字段可以使用函数读取:
bin()显示为二进制
hex()显示为十六进制
mysql> create table test_bit (id bit(4));
Query OK, 0 rows affected (0.35 sec)
mysql> desc test_bit;
+-------+--------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| id | bit(4) | YES | | NULL | |
+-------+--------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql> insert into test_bit values(4);
mysql> select * from test_bit;
+------+
| id |
+------+
| |
+------+
1 row in set (0.00 sec)
mysql> select bin(id),hex(id) from test_bit;
+---------+---------+
| bin(id) | hex(id) |
+---------+---------+
| 100 | 4 |
+---------+---------+
1 row in set (0.00 sec)
插入超过指定宽度的值:
mysql> insert into test_bit values(8);
Query OK, 1 row affected (0.05 sec)
mysql> insert into test_bit values(9);
Query OK, 1 row affected (0.06 sec)
mysql> insert into test_bit values(16);
ERROR 1406 (22001): Data too long for column 'id' at row 1
**===时间和日期类型测试:year、date、time、datetime、timestamp **
作用:用于存储用户的注册时间,文章的发布时间,文章的更新时间,员工的入职时间等
48/17849/178
数据类型
“ 零 **”**值
DATE
‘0000-00-00’
TIME
‘00:00:00’
DATETIME
‘0000-00-00 00:00:00’
TIMESTAMP
‘0000-00-00 00:00:00’
YEAR
0000
mysql> create table test_time(
-> d date, 年月日
-> t time, 24小时(时分秒)
-> dt datetime 完整时间
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> desc test_time;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| d | date | YES | | NULL | |
| t | time | YES | | NULL | |
| dt | datetime | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into test_time values(now(),now(),now());
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> select * from test_time;
+------------+----------+---------------------+
| d | t | dt |
+------------+----------+---------------------+
| 2013-12-18 | 00:06:10 | 2013-12-18 00:06:10 |
+------------+----------+---------------------+
1 row in set (0.00 sec)
mysql> insert into t3 values (20201001,'12:00:01','2022-12-12 20:00:15');
Query OK, 1 row affected (0.02 sec)
mysql> select * from t3;
+------------+----------+---------------------+
| d | t | dt |
+--