根據(jù)兩個實例進行練習(xí)

1）創(chuàng)建庫

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

2）創(chuàng)建表

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)字段插入（調(diào)換位置）

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）創(chuàng)建表、添加外鍵約束

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）創(chuàng)建用戶并授權(quán)

修改密碼策略等級

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)

創(chuàng)建用戶

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

Query OK, 0 rows affected (0.00 sec)

授權(quán)

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）重新加載權(quán)限

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

4)查看權(quán)限

mysql> show grants for account1@localhost;

5）回收權(quán)限

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

Query OK, 0 rows affected (0.00 sec)

文章來源地址http://www.zghlxwxcb.cn/news/detail-528365.html

到了這里，關(guān)于MySQL數(shù)據(jù)庫、表、用戶的基礎(chǔ)操作的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！