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

编辑: admin 分类: 电脑知识 发布时间: 2023-06-14 来源:互联网
MySQL操作：

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

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

显示数据库列表：show databases；

  1. MySQL [(none)]> show databases;
  2. +--------------------+
  3. | Database           |
  4. +--------------------+
  5. | information_schema |
  6. | laravel            |
  7. | mysql              |
  8. | performance_schema |
  9. | sys                |
  10. +--------------------+

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

  1. MySQL [(none)]> use laravel;
  2. Database changed
  3. MySQL [laravel]>

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

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

2.2 更新(Update): UPDATE

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

2.3 读取(Read) : SELECT

  1. MySQL [laravel]> select `id`,`name` from `test`;
  2. +----+-------+
  3. | id | name  |
  4. +----+-------+
  5. |  1 | hao   |
  6. |  2 | admin |
  7. |  3 | user  |
  8. +----+-------+

2.4 删除(Delete): DELETE

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

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

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

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

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

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

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

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

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

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

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

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

  1. alter user '用户名'@localhost identified by '新密码';
表字段类型timestamp与datatime的区别
  1. 区别一：timestamp 会灵活地根据服务器时区，取出来的时间会做相应调整。datetime则不会。
  2. 区别二： timestamp所能存储的时间范围为：'1970-01-01 00:00:01.000000' 到 '2038-01-19 03:14:07.999999'。
  3. datetime所能存储的时间范围为：'1000-01-01 00:00:00.000000' 到 '9999-12-31 23:59:59.999999'。
认识PDO
  1. $dsn = 'mysql:host=localhost;dbname=laravel';
  2. try{
  3.     $db = new PDO($dsn,'root', 'root');
  4. }catch(\PDOException $e){
  5.     die('连接失败'.$e->getMessage());
  6. }
  7. $res = $db->query('select * from test')->fetchAll(PDO::FETCH_ASSOC);
  8. print_r($res);
  1. Array
  2. (
  3.     [0] => Array
  4.         (
  5.             [id] => 1
  6.             [name] => hao
  7.             [time] => 1681960338
  8.         )
  9.     [1] => Array
  10.         (
  11.             [id] => 2
  12.             [name] => admin
  13.             [time] => 1681960338
  14.         )
  15. )
总结：

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

【文章转自 盐城网页制作公司 http://www.1234xp.com/yancheng.html 处的文章,转载请说明出处】
更多阅读