MySQL数据库、表、用户的基础操作

根据两个实例进行练习

1）创建库

mysql> create database Market;
Query OK, 1 row affected (0.00 sec)

2）创建表

mysql> create table customers (
    -> c_num int(11) PRIMARY KEY AUTO_INCREMENT,
    -> c_name varchar(50) ,
    -> c_contact varchar(50),
    -> c_city varchar(50),
    -> c_birth datetime NOT NULL 
    -> );
Query OK, 0 rows affected (0.00 sec)

3)字段插入（调换位置）

mysql> alter table customers MODIFY c_contact VARCHAR(50) after c_birth;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

4)修改字段类型

mysql> alter table customers MODIFY c_name VARCHAR(70);
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

5)修改字段名

mysql> alter table customers CHANGE c_contact c_phone VARCHAR(50);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

6)增加字段

mysql> alter table customers ADD c_gender CHAR(1);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

7）修改表名（这一步做掉了，最后才添加上来。所以后面添加外键主表名依旧为customers。）

mysql> alter table customers RENAME customers_info;
Query OK, 0 rows affected (0.00 sec)

8)删除字段

mysql> alter table customers DROP c_city;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

9)修改存储引擎

mysql> alter table customers ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

2、

（1）创建表、添加外键约束

mysql> create table orders (
    -> o_num INT(11) PRIMARY KEY AUTO_INCREMENT,
    -> o_date DATE,
    -> c_id INT(11),
    -> foreign key (c_id) references customers (c_num)
    -> );

Query OK, 0 rows affected (0.01 sec)

（2）删除外键

先查看外键名

进行删除操作 

mysql> alter table orders drop foreign key orders_ibfk_1;

Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

删除表

mysql> drop table customers;

Query OK, 0 rows affected (0.00 sec)

---

 

1）创建用户并授权

修改密码策略等级

mysql> set GLOBAL validate_password_policy='low';
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=0;
Query OK, 0 rows affected (0.00 sec)

创建用户

mysql> create user account1@localhost IDENTIFIED BY 'oldpwd1';

Query OK, 0 rows affected (0.00 sec)

授权

mysql> grant SELECT,INSERT,UPDATE(info) on Team.player to account1@localhost;

Query OK, 0 rows affected (0.00 sec)

2）修改密码

mysql> alter user account1@localhost IDENTIFIED BY "newpwd2";

Query OK, 0 rows affected (0.00 sec)

3）重新加载权限

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4)查看权限

mysql> show grants for account1@localhost;

5）回收权限

mysql>  revoke SELECT,INSERT,UPDATE(info) on Team.player from  account1@localhost;

Query OK, 0 rows affected (0.00 sec)