mysql表的练习

最新推荐文章于 2025-04-11 14:26:02 发布

wada64

最新推荐文章于 2025-04-11 14:26:02 发布

阅读量91

点赞数

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/wada64/article/details/131601615

版权

（1）创建数据库

create database Market

（2）创建数据表customers，在c num字段上添加主键约束和自增约束，在c birth字段上添加非空约束

use Market
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
-> );
-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(50) | YES | | NULL | |
| c_contact | varchar(50) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
+-----------+-------------+------+-----+---------+----------------+

（3）将c contact字段插入到c birth字段后面。

alter table customers modify c_contact varchar(50) after c_birth;

+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(50) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_contact | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+

（4）将c name字段数据类型改为VARCHAR(70)。

alter table customers modify c_name varchar(70);

mysql> desc customers;
+-----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_contact | varchar(50) | YES | | NULL | |
+-----------+-------------+------+-----+---------+----------------+

（5）将c contact字段改名为c_phoneo

alter table customers change c_contact c_phone varchar(50);

+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+

（6）增加c_gender字段，数据类型为CHAR(I)

alter table customers add c_gender char(1);

+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
| c_gender | char(1) | YES | | NULL | |

（7）将表名修改为customers infoa

rename table customers to customers_info;

+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_city | varchar(50) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
| c_gender | char(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+

（8）删除字段c_cityo

alter table customers_info dorp c_city;

+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| c_num | int(11) | NO | PRI | NULL | auto_increment |
| c_name | varchar(70) | YES | | NULL | |
| c_birth | datetime | NO | | NULL | |
| c_phone | varchar(50) | YES | | NULL | |
| c_gender | char(1) | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+

（9）修改数据表的存储引擎为MylSAMo

alter table customers_info engine=MyISAM;

（1）创建数据表orders,在o_num字段上添加主键约束和自增约束，在c id字段上添加外键约束，关联customers表中的主键c numo

alter table customers_info engine=InnoDB

create table orders(
-> o_num int(11) PRIMARY KEY auto_increment,
-> o_data date,
-> c_id int(11),
-> FOREIGN KEY(c_id) REFERENCES customers_info(c_num)
-> );

+--------+---------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+----------------+
| o_num | int(11) | NO | PRI | NULL | auto_increment |
| o_data | date | YES | | NULL | |
| c_id | int(11) | YES | MUL | NULL | |
+--------+---------+------+-----+---------+----------------+

（2）删除orders表的外键约束，然后删除表customerso

show create table orders 查看外键名
alter table orders dorp foreign key 外键名
dorp table customerso_info

1）创建一个新账户，用户名为accountl,该用户通过本地主机连接数据库，密码为 oldpwdl。授权该用户对Team数据库中player表的SELECT和INSERT权限，并且授权该用户对player表的info字段的UPDATE权限。

SET GLOBAL validate_password_special_char_count=0;
SET GLOBAL validate_password_mixed_case_count=0;
SET GLOBAL validate_password_number_count=0;
SET GLOBAL validate_password_length=0;
更改密码策略

create user accountl@'localhost' identified by 'oldepwd1';
grant select,insert，updat（info，on Team.player to accountl@'localhost' identified by 'oldpwd1';

（2）创建SQL语句，更改accountl用户的密码为newpwd20