MySQL数据库 - 数据库创建及常用约束定义

小蒋的学习笔记

于 2023-05-20 17:43:46 发布

阅读量214

点赞数 1

文章标签：数据库 mysql linux

桂林电子科技大学计算机与信息安全学院蒋熹煜

本文链接：https://blog.csdn.net/qq_61735602/article/details/130783484

版权

1. 创建数据库

版本库代码路径：/data/workspace/myshixun/
root@evassh-20125843:~# mysql -uroot -p123123 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases
    -> ;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

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

mysql>

2. 创建表

版本库代码路径：/data/workspace/myshixun/
root@evassh-20125843:~# mysql -uroot -p123123 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database TestDb;
ERROR 1007 (HY000): Can't create database 'TestDb'; database exists
mysql> use TestDb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
mysql> create table t_student(
    -> sno varchar(20) not null,
    -> sname varchar(32) not null,
    -> dptno int,
    -> ssex varchar(2)
    -> );
ERROR 1050 (42S01): Table 't_student' already exists
mysql>

3. 使用主键约束

版本库代码路径：/data/workspace/myshixun/
root@evassh-20125843:~# mysql -uroot -p123123 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 49
Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use TestDb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table t_user1;
ERROR 1051 (42S02): Unknown table 't_user1'
mysql> create table t_user1(
    -> userid INT primary key,
    -> name varchar(32),
    -> password varchar(11),
    -> phone varchar(11),
    -> email varchar(32)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_user2(
    -> name varchar(32),
    -> phone varchar(11),
    -> email varchar(32),
    -> primary key (name,phone)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE t_user1 CHANGE userid useid int(11);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t_user1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_user1(
    -> useid int,
    -> name varchar(32),
    -> password varchar(11),
    -> phone varchar(11),
    -> email varchar(32)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql> alter table t_user1 add primary key useid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> alter table t_user1 add primary key useid;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
mysql> alter table t_user1 add primary key(useid);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> drop table t_user1;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_user1(
    -> useid int primary key,
    -> name varchar(32),
    -> password varchar(11),
    -> phone varchar(11),
    -> email varchar(32)
    -> );
Query OK, 0 rows affected (0.00 sec)

mysql>

4. 外键约束

版本库代码路径：/data/workspace/myshixun/
root@evassh-20125843:~# mysql -uroot -p123123 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use TestDb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t_depart(
    -> dptno int primary key,
    -> dptname varchar(22));
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_student(
    -> stid varchar(12) primary key,
    -> name varchar(22),
    -> dptno int,
    -> foreign key (dptno) references t_depart(dptno));
Query OK, 0 rows affected (0.01 sec)

mysql>

5. 添加常用约束

版本库代码路径：/data/workspace/myshixun/
root@evassh-20125843:~# mysql -uroot -p123123 -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 62
Server version: 5.5.59-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use TestDb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> create table t_user(
    -> id int primary key auto_increment,
    -> username varchar(32) not null unique,
    -> sex varchar(4) default 'm');
Query OK, 0 rows affected (0.00 sec)

mysql>