MySQL操作；timestamp与datatime字段的区别；PDO操作

MySQL操作：

有很多操作数据库窗口化工具，常用的有免费的Navicat等，原生数据库操作有以下几种：
1 DDL: 数据定义语言(Data Definition Language)
数据库登录：mysql -u root -p；再输入密码就进入MySQL界面；

C:\Users\hao_z>mysql -u root -p
Enter password: ****
Welcome to the MariaDB monitor...
MySQL [(none)]>

显示数据库列表：show databases；

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| laravel            |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

选择数据表：use 数据库名;

MySQL [(none)]> use laravel;
Database changed
MySQL [laravel]>

2 DML: 数据操作语言(Data Manipulation Language)
2.1 创建(Create): INSERT

MySQL [laravel]> INSERT test SET `name`='user';
Query OK, 1 row affected (0.005 sec)

2.2 更新(Update): UPDATE

MySQL [laravel]> update test set `time`=1681960338 where `id`=3;
Query OK, 1 row affected (0.005 sec)
Rows matched: 1  Changed: 1  Warnings: 0

2.3 读取(Read) : SELECT

MySQL [laravel]> select `id`,`name` from `test`;
+----+-------+
| id | name  |
+----+-------+
|  1 | hao   |
|  2 | admin |
|  3 | user  |
+----+-------+

2.4 删除(Delete): DELETE

MySQL [laravel]> delete from test where id=3;
Query OK, 1 row affected (0.002 sec)

3 DCL: 数据控制语言(Data Control Language)
3.1 创建用户：
CREATE USER ‘用户名’@地址 IDENTIFIED BY ‘密码’;

 MySQL [laravel]> CREATE USER 'users'@localhost IDENTIFIED BY '123';
Query OK, 0 rows affected (0.025 sec)

3.2 给用户授权：
GRANT 权限1, … , 权限n ON 数据库.* TO ‘用户名’@地址;

 MySQL [laravel]> GRANT ALL ON laravel.test TO users@localhost;
Query OK, 0 rows affected (0.004 sec)

3.3 撤销授权：
REVOKE 权限1, … , 权限n ON 数据库.* FROM ‘用户名’@地址;

 MySQL [laravel]> REVOKE  ALL ON  laravel.test  FROM  users@localhost;
Query OK, 0 rows affected (0.001 sec)

3.4 查看用户权限
SHOW GRANTS FOR ‘用户名’@地址;

 MySQL [laravel]> show grants for 'users'@localhost;
+------------------------------------+
| Grants for users@localhost         |
+------------------------------------+
| GRANT USAGE ON *.* TO 'users'@'localhost'                      |
| GRANT ALL PRIVILEGES ON `laravel`.`test` TO 'users'@'localhost' |
+------------------------------------+
2 rows in set (0.000 sec)

3.5 删除用户
DROP USER ‘用户名’@地址;

 MySQL [laravel]> drop user 'users'@localhost;
Query OK, 0 rows affected (0.001 sec)

3.6 修改用户密码需要以root身份。

alter user '用户名'@localhost identified by '新密码';

表字段类型timestamp与datatime的区别

区别一：timestamp 会灵活地根据服务器时区，取出来的时间会做相应调整。datetime则不会。
区别二： 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'。

认识PDO

$dsn = 'mysql:host=localhost;dbname=laravel';
try{
    $db = new PDO($dsn,'root', 'root');
}catch(\PDOException $e){
    die('连接失败'.$e->getMessage());
}
$res = $db->query('select * from test')->fetchAll(PDO::FETCH_ASSOC);
print_r($res);

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => hao
            [time] => 1681960338
        )
    [1] => Array
        (
            [id] => 2
            [name] => admin
            [time] => 1681960338
        )
)

总结：

学习编程主要还是靠自己琢磨、演练，老师只能是提供一些资料，带大家走一下，节省学习时间，成败在自己。