Mysql约束

最新推荐文章于 2024-10-05 13:49:12 发布
最新推荐文章于 2024-10-05 13:49:12 发布
阅读量34 收藏
点赞数
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_52927369/article/details/131969854
版权 
λ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.30 MySQL Community Server - GPL

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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 company;
Database changed
mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | YES  |     | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int         | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| salary   | double      | YES  |     | NULL    |       |
| COMM     | double      | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select * from emp;
+-------+--------+-----------+------+------------+--------+------+--------+
| EMPNO | ENAME  | JOB       | MGR  | HIREDATE   | salary | COMM | DEPTNO |
+-------+--------+-----------+------+------------+--------+------+--------+
|  7369 | SMITH  | CLERK     | 7902 | 1980-01-17 |   1000 | NULL |     20 |
|  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 |   1800 |  300 |     30 |
|  7521 | WARD   | SALESMAN  | 7698 | 1981-02-22 |   1450 |  500 |     30 |
|  7566 | JONES  | MANAGER   | 7839 | 1981-04-02 |   2975 | NULL |     20 |
|  7654 | MARTIN | SALESMAN  | 7698 | 1981-09-28 |   1450 | 1400 |     30 |
|  7698 | BLAKE  | MANAGER   | 7839 | 1981-05-01 |   2850 | NULL |     30 |
|  7782 | CLARK  | MANAGER   | 7839 | 1981-06-09 |   2450 | NULL |     10 |
|  7788 | SCOTT  | ANALYST   | 7566 | 1987-07-13 |   3000 | NULL |     20 |
|  7839 | KING   | PRESIDENT | NULL | 1981-11-07 |   5000 | NULL |     10 |
|  7844 | TURNER | SALESMAN  | 7698 | 1981-09-08 |   1700 |    0 |     30 |
|  7876 | ADAMS  | CLERK     | 7788 | 1987-07-13 |   1300 | NULL |     20 |
|  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 |   1150 | NULL |     30 |
|  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 |   3000 | NULL |     20 |
|  7934 | MILLER | CLEERK    | 7782 | 1982-02-23 |   1500 | NULL |     10 |
+-------+--------+-----------+------+------------+--------+------+--------+
14 rows in set (0.01 sec)

mysql>  ALTER TABLE emp ADD CONSTRAINT pk_emp_EMPNO PRIMARY KEY (EMPNO);--设置empno为主键
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc emp;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| EMPNO    | int         | NO   | PRI | NULL    |       |
| ENAME    | varchar(10) | YES  |     | NULL    |       |
| JOB      | varchar(9)  | YES  |     | NULL    |       |
| MGR      | int         | YES  |     | NULL    |       |
| HIREDATE | date        | YES  |     | NULL    |       |
| salary   | double      | YES  |     | NULL    |       |
| COMM     | double      | YES  |     | NULL    |       |
| DEPTNO   | int         | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
8 rows in set (0.00 sec)

mysql> select ename,salary from emp where salary = 1000 or ename = 'ALLEN' and deptno = 20;--先执行or，再执行and
+-------+--------+
| ename | salary |
+-------+--------+
| SMITH |   1000 |
+-------+--------+
1 row in set (0.00 sec)

mysql>  select ename, salary FROM emp WHERE salary = 1000 OR ename = 'JONES' AND deptno = 20;
+-------+--------+
| ename | salary |
+-------+--------+
| SMITH |   1000 |
| JONES |   2975 |
+-------+--------+
2 rows in set (0.00 sec)

mysql> select ename, salary FROM emp WHERE salary = 800 AND ename = 'JONES' OR deptno = 20;
+-------+--------+
| ename | salary |
+-------+--------+
| SMITH |   1000 |
| JONES |   2975 |
| SCOTT |   3000 |
| ADAMS |   1300 |
| FORD  |   3000 |
+-------+--------+
5 rows in set (0.00 sec)

select ename, sal, deptno FROM emp WHERE sal = 2975 AND ename = 'JONES' OR deptno = 30;

mysql> select ename, salary, deptno FROM emp WHERE salary = 2975 AND ename = 'JONES' OR deptno = 30;

mysql> select ename, salary, deptno FROM emp WHERE (salary = 2975 AND ename = 'JONES') OR deptno = 30;
+--------+--------+--------+
| ename  | salary | deptno |
+--------+--------+--------+
| ALLEN  |   1800 |     30 |
| WARD   |   1450 |     30 |
| JONES  |   2975 |     20 |
| MARTIN |   1450 |     30 |
| BLAKE  |   2850 |     30 |
| TURNER |   1700 |     30 |
| JAMES  |   1150 |     30 |
+--------+--------+--------+
7 rows in set (0.00 sec)

mysql> show create table emp;
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table




         |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| emp   | CREATE TABLE `emp` (
  `EMPNO` int NOT NULL COMMENT '员工编号',
  `ENAME` varchar(10) DEFAULT NULL COMMENT '员工姓名',
  `JOB` varchar(9) DEFAULT NULL COMMENT '员工职位',
  `MGR` int DEFAULT NULL COMMENT '领导编号',
  `HIREDATE` date DEFAULT NULL COMMENT '入职日期',
  `salary` double DEFAULT NULL,
  `COMM` double DEFAULT NULL COMMENT '奖金',
  `DEPTNO` int DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`EMPNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci                           |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> --创建表时添加主键约束
mysql> create table student(
    -> id int primary key,
    -> name varchar(14),
    -> age int);
Query OK, 0 rows affected (0.06 sec)

mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table

                                   |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(14) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into student values(1,'王雪',20);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student(id) VALUES(1);
ERROR 1062 (23000): Duplicate entry '1' for key 'student.PRIMARY'
mysql> INSERT INTO student(name, age) VALUES('张三', 18);
ERROR 1364 (HY000): Field 'id' doesn't have a default value
mysql> INSERT INTO student(id, name, age) VALUES('张三', 18);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql>  INSERT INTO student(id, name, age) VALUES(2,'张三', 18);
Query OK, 1 row affected (0.00 sec)

mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 王雪   |   20 |
|  2 | 张三   |   18 |
+----+--------+------+
2 rows in set (0.00 sec)

mysql> insert into student(id) values (3);
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | 王雪   |   20 |
|  2 | 张三   |   18 |
|  3 | NULL   | NULL |
+----+--------+------+
3 rows in set (0.00 sec)

mysql>  DROP TABLE student;
Query OK, 0 rows affected (0.01 sec)

mysql> create table student(
    -> id int,
    -> name varchar(20),
    -> CONSTRAINT pk_id PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> insert into student values (1,'李四');
Query OK, 1 row affected (0.00 sec)

mysql> --删除主键
mysql> alter table student drop primary key;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> create table student(
    -> id int primary key AUTO_INCREMENT,
    -> age int UNIQUE AUTO_INCREMENT
    -> );
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

mysql> create table student(
    -> id int primary key auto_increment,
    -> name varchar(20)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> insert into student(name) values ('张三');
Query OK, 1 row affected (0.01 sec)

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
+----+--------+
1 row in set (0.00 sec)

--再次插入名字为张三
mysql> insert into student(name) values ('张三');
Query OK, 1 row affected (0.01 sec)

--随着ID的自增第二个张三也会插入表中
mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
+----+--------+
2 rows in set (0.00 sec)

--查询的几种方式
mysql> select id,name from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 张三   |
|  2 | 张三   |
+----+--------+
2 rows in set (0.00 sec)

mysql> select id as '编号',name as '姓名' from student;
+--------+--------+
| 编号   | 姓名   |
+--------+--------+
|      1 | 张三   |
|      2 | 张三   |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select id '编号', name '姓名' from student;
+--------+--------+
| 编号   | 姓名   |
+--------+--------+
|      1 | 张三   |
|      2 | 张三   |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select id '编号', name '姓名' from student AS s;
+--------+--------+
| 编号   | 姓名   |
+--------+--------+
|      1 | 张三   |
|      2 | 张三   |
+--------+--------+
2 rows in set (0.00 sec)

mysql> select id '编号', name '姓名' from student s;
+--------+--------+
| 编号   | 姓名   |
+--------+--------+
|      1 | 张三   |
|      2 | 张三   |
+--------+--------+
2 rows in set (0.00 sec)

mysql>  select id '编号', name '姓名' from student ;
+--------+--------+
| 编号   | 姓名   |
+--------+--------+
|      1 | 张三   |
|      2 | 张三   |
+--------+--------+
2 rows in set (0.00 sec)

外键

mysql> -- 将 dept 表的 deptno 设置为主键
mysql> ALTER TABLE dept ADD PRIMARY KEY(deptno);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> CREATE TABLE employee(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20),
    -> dept_no INT, -- 外键列
    -> CONSTRAINT FK_DEPT_NO FOREIGN KEY(dept_no) REFERENCES dept(deptno)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> -- 有外键约束后，约束列的值只能是 NULL 或者 主表中被引用的列的值
mysql> SELECT * FROM dept;
+--------+------------+----------+
| DEPTNO | DNAME      | LOC      |
+--------+------------+----------+
|     10 | ACCOUNTING | NEW YORK |
|     20 | RESEARCH   | DALLAS   |
|     30 | SALES      | CHICAGO  |
|     40 | OPERATIONS | BOSTON   |
+--------+------------+----------+
4 rows in set (0.00 sec)

mysql> INSERT INTO employee(name, dept_no) VALUES('JONES', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`scott`.`employee`, CONSTRAINT `FK_DEPT_NO` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`DEPTNO`))
mysql> INSERT INTO employee(name, dept_no) VALUES('JONES', 10);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO employee(name) VALUES('JERRY');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM employee;
+----+-------+---------+
| id | name  | dept_no |
+----+-------+---------+
|  2 | JONES |      10 |
|  3 | JERRY |    NULL |
+----+-------+---------+
2 rows in set (0.00 sec)

mysql> DELETE FROM employee WHERE id = 3;
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM employee;
+----+-------+---------+
| id | name  | dept_no |
+----+-------+---------+
|  2 | JONES |      10 |
+----+-------+---------+
1 row in set (0.00 sec)


mysql> INSERT INTO dept VALUES(50, '研发部', '西安');
Query OK, 1 row affected (0.01 sec)

mysql> -- 删除主表中未被引用的数
mysql> DELETE FROM dept WHERE deptno = 50;
Query OK, 1 row affected (0.01 sec)

mysql> -- 删除主表中被引用的数据
mysql> DELETE FROM dept WHERE deptno = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`scott`.`employee`, CONSTRAINT `FK_DEPT_NO` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`DEPTNO`))
mysql> -- 更新主表数据时也一样
mysql> UPDATE dept SET deptno = 11 WHERE deptno = 10;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`scott`.`employee`, CONSTRAINT `FK_DEPT_NO` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`DEPTNO`))
mysql> DROP TABLE employee;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE employee(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20),
    ->  dept_no INT
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> -- 添加外键约束
mysql> ALTER TABLE employee ADD CONSTRAINT FK_DEPT_NO FOREIGN KEY (dept_no) REFERENCES dept(deptno);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE employee;
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table    | Create Table

                                                                                                                | +----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | employee | CREATE TABLE `employee` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `dept_no` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_DEPT_NO` (`dept_no`),
  CONSTRAINT `FK_DEPT_NO` FOREIGN KEY (`dept_no`) REFERENCES `dept` (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)

mysql> -- 删除外键约束
mysql> ALTER TABLE employee DROP CONSTRAINT FK_DEPT_NO;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

唯一 非空 默认

mysql> CREATE TABLE temp(
    -> id FLOAT AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> -- 修改自增长列的起始值
mysql> ALTER TABLE temp AUTO_INCREMENT = 10;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- 插入值
mysql> INSERT INTO temp(name) VALUES('A');
Query OK, 1 row affected (0.01 sec)

mysql> -- 查询值
mysql> SELECT * FROM temp;
+----+------+
| id | name |
+----+------+
| 10 | A    |
+----+------+
1 row in set (0.00 sec)

mysql> -- 删除表
mysql> DROP TABLE temp;
Query OK, 0 rows affected (0.01 sec)

mysql> -- 创建表时设置自增长初始值
mysql> CREATE TABLE temp(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(2)
    -> ) AUTO_INCREMENT=10;
Query OK, 0 rows affected (0.01 sec)

mysql> -- 测试
mysql> INSERT INTO temp (name) VALUES('B');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM temp;
+----+------+
| id | name |
+----+------+
| 10 | B    |
+----+------+
1 row in set (0.00 sec)

mysql> show tables;
+-----------------+
| Tables_in_scott |
+-----------------+
| bonus           |
| dept            |
| emp             |
| salgrade        |
| student         |
| teacher         |
| temp            |
+-----------------+
7 rows in set (0.00 sec)

mysql> SHOW CREATE TABLE student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table

       |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> -- 唯一约束，表示列的值不同
mysql> -- UNIQUE KEY
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.01 sec)

mysql> -- 创建学生表，id 为主键，name 唯一
mysql> CREATE TABLE student(
    -> id INT PRIMARY KEY,
    -> name VARCHAR(20) UNIQUE, -- 唯一列
    -> stu_no VARCHAR(10) COMMENT '学号'
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> -- student 表中有两个约束，一个 id 的主键约束， 另外一个是 name 的唯一约束
mysql> INSERT INTO student(id, name, stu_no) VALUES(1, 'TOM', '10001');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student(id, name, stu_no) VALUES(2, 'TOM', '10002');
ERROR 1062 (23000): Duplicate entry 'TOM' for key 'student.name'
mysql> INSERT INTO student(id, name, stu_no) VALUES(2, 'TOM1', '10002');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student;
+----+------+--------+
| id | name | stu_no |
+----+------+--------+
|  1 | TOM  | 10001  |
|  2 | TOM1 | 10002  |
+----+------+--------+
2 rows in set (0.00 sec)

mysql> -- 修改数据时，数据也会受约束的影响
mysql> UPDATE student SET name = 'TOM' WHERE id = 2;
ERROR 1062 (23000): Duplicate entry 'TOM' for key 'student.name'
mysql> -- 唯一约束表示值不能重复，那么可以有多个NULL
mysql> INSERT INTO student(id, stu_no) VALUES(3, '10003');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student;
+----+------+--------+
| id | name | stu_no |
+----+------+--------+
|  1 | TOM  | 10001  |
|  2 | TOM1 | 10002  |
|  3 | NULL | 10003  |
+----+------+--------+
3 rows in set (0.00 sec)

mysql> INSERT INTO student(id, stu_no) VALUES(4, '10004');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student;
+----+------+--------+
| id | name | stu_no |
+----+------+--------+
|  1 | TOM  | 10001  |
|  2 | TOM1 | 10002  |
|  3 | NULL | 10003  |
|  4 | NULL | 10004  |
+----+------+--------+
4 rows in set (0.00 sec)

mysql> -- 第二种创建唯一约束
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE student(
    -> id INT PRIMARY KEY AUTO_INCREMENT,
    -> name VARCHAR(20),
    -> stu_no CHAR(10),
    -> CONSTRAINT uq_name UNIQUE(name)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> -- 测试
mysql> INSERT INTO student (name, stu_no) VALUES('Danny', '10001');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO student (name, stu_no) VALUES('Danny', '10002');
ERROR 1062 (23000): Duplicate entry 'Danny' for key 'student.uq_name'
mysql> -- 将 stu_no 也设置为唯一约束的列
mysql> ALTER TABLE student ADD CONSTRAINT uq_no UNIQUE(stu_no);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- 删除唯一约束
mysql> ALTER TABLE student DROP CONSTRAINT uq_no;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE student ADD UNIQUE(stu_no);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> -- 没有约束名的约束如何删
mysql> ALTER TABLE student DROP CONSTRAINT stu_no;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table

                                                    |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `stu_no` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> -- 之前讲过 LIKE 创建同类型的表
mysql> CREATE TABLE student_copy LIKE student;
Query OK, 0 rows affected (0.02 sec)

mysql> SHOW CREATE TABLE student_copy;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table        | Create Table

                                             |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_copy | CREATE TABLE `student_copy` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `stu_no` char(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> -- 那么 AS 会吗？
mysql> CREATE TABLE student_copy1 AS SELECT * FROM student;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE student_copy1;
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table
                                                                                                       |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student_copy1 | CREATE TABLE `student_copy1` (
  `id` int NOT NULL DEFAULT '0',
  `name` varchar(20) DEFAULT NULL,
  `stu_no` char(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> -- 默认约束。默认值
mysql> -- 默认约束能和主键约束同时存在吗？
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE student(
    -> id INT DEFAULT 5 PRIMARY KEY,
    -> name VARCHAR(20)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> -- 测试
mysql> INSERT INTO student(name) VALUES('A');
Query OK, 1 row affected (0.01 sec)

mysql> SELECT * FROM student;
+----+------+
| id | name |
+----+------+
|  5 | A    |
+----+------+
1 row in set (0.00 sec)

mysql> INSERT INTO student(name) VALUES('A');
ERROR 1062 (23000): Duplicate entry '5' for key 'student.PRIMARY'
mysql> DROP TABLE student;
Query OK, 0 rows affected (0.01 sec)

mysql> -- 非空约束和默认约束一起
mysql> CREATE TABLE student(
    -> id INT PRIMARY KEY,
    -> name VARCHAR(20) NOT NULL DEFAULT NULL
    -> );
ERROR 1067 (42000): Invalid default value for 'name'
mysql> CREATE TABLE student(
    -> id INT PRIMARY KEY,
    -> name VARCHAR(20) NOT NULL DEFAULT 'a'
    -> );
Query OK, 0 rows affected (0.01 sec)
Aurora雪
关注
mysql约束日期_MySQL约束
weixin_35663823的博客
01-28 2651
约束* 概念： 对表中的数据进行限定，保证数据的正确性、有效性和完整性。* 分类：1. 主键约束：primary key2. 非空约束：not null3. 唯一约束：unique4. 外键约束：foreign key* 非空约束：not null，值不能为null1. 创建表时添加约束CREATE TABLE stu(id INT,NAME VARCHAR(20) NOT NULL -- nam...
MySQL 约束
weixin_46441425的博客
01-29 1763
主键约束 ：primary key、非空约束 : not null、唯一约束 ：unique、外键约束 ： foreign key
MySQL约束
qq_53060585的博客
01-16 1257
文章目录约束介绍一. 主键约束1.1 添加单列主键1.2添加多列主键(联合主键)1.3通过修改表结构添加主键1.4删除主键约束二． 自增长约束2.1 指定自增字段初始值2.2 delete和truncate在删除后自增列的变化三. 非空约束3.1添加非空约束3.2删除非空约束四.唯一约束五.默认约束5.1 删除约束：六.零填充约束 约束介绍 概念： 约束英文：constraint 约束实际上就是表中数据的限制条件 作用： 表在设计的时候加入约束的目的就是为了保证表中的记录完整性和有效性，比如用户表有些
MySQL约束constraint
weixin_45856470的博客
10-01 4990
约束canstraint，约束实际上就是表中数据的限制条件。表在设置的时候加入约束的目的是为了保证表中的记录完整性和有效性，比如用户的某些数据不能为空。
MySQL 约束语法
weixin_53395092的博客
02-12 2653
约束的概念 约束是作用于表中列上的规则，用于限制加入表的数据。 例如：我们可以给id列加约束，让其值不能重复，不能为null值。 约束的存在保证了数据库中数据的正确性、有效性和完整性。添加约束可以在添加数据的时候就限制不正确的数据，年龄是3000，数学成绩是-5分这样无效的数据，继而保障数据的 完整性。 通俗的说：就是让数据库表中的数据不出现逻辑上的错误 1、非空约束 非空约束用于保证列中所有数据不能有null值 语法： 创建表时添加约束：CREATE TABLE 表名( 字段名 数据类型 N
MySQL 约束详解
GSON的博客
11-07 2992
一、什么是约束 约束实际上就是表中数据的限制条件 二、约束作用 表在设计的时候加入约束的目的就是为了保证表中的记录完整和有效 比如name字段中要让其用户名不重复，这就需要添加约束。或者必须注册的时候需要添加邮箱等 三、约束种类 非空约束(not null) 唯一性约束(unique) 主键约束(primary key) PK 外键约束(foreign key) FK 检查约束(目前MySQL不支持、Oracle支持) 下面将逐一介绍以上约束 四、非空约束 用not null约束
MySQL 约束篇
Java 技术专栏，从入门到精通，熟悉企业级开发
04-23 7205
此为MySQL专栏文章之一，讲解MySQL约束。分类：约束是作用于表中字段上的，可以在创建表/修改表的时候添加约束。
mysql 约束_mysql常见约束有哪些
weixin_35337252的博客
01-18 934
mysql常见约束有：1、非空约束“not null”；2、唯一性约束“unique”；3、主键约束“primary key”；4、外键约束“foreign key”；5、默认值约束“Default”等等。约束是一种限制，它通过对表的行或列的数据做出限制，来确保表的数据的完整性、唯一性。下面本篇文章就来给大家介绍一下6种mysql常见的约束，希望对大家有所帮助。1、非空约束(not null)非空...
mysql约束
TimeFriends
01-13 1717
mysql约束-自动增长约束(auto_increment) 语法:字段名 数据类型 auto_increment 特点: 默认情况下,auto_increment的初始值是1,每新增一条记录,字段值自动加1 一个表只能有一个字段auto_increment约束,且该字段必须有唯一索引,以避免序号重复(即主键或者主键的一部分) auto_increment约束的字段必须具备not null 属性 auto_increment约束的字段只能是整数类型(tinyint,smallint,int,
MySQL：约束
2401_83595513的博客
08-07 1494
非空约束&唯一约束&默认约束&主键约束、自增类型、复合主键&外键约束
25.10 MySQL 约束
04-02
MySQL约束是数据库设计中的关键元素，用于确保数据的完整性和一致性。它们定义了表中数据必须遵循的规则，防止不合法或错误的数据被插入或更新。以下是对MySQL约束的详细阐述： 1. **非空约束 (NOT NULL)**：此约束...
MySQL约束类型及举例介绍
01-19
约束类型包括：NOT NULL （非空约束），PRIMARY KEY（主键约束），UNIQUE KEY（唯一约束），DEFAULT（默认约束），FOREIGN（外键约束） 1.主键约束 PRIMARY KEY 每张数据表只能存在一个主键 主键保证记录的唯一...
MySQL约束(三).pdf
05-01
### MySQL约束(三) #### 内容概要 本篇文档深入探讨了MySQL数据库中的多种约束类型及其实际应用场景，作为约束系列教程的第三部分，它不仅涵盖了基础概念，还涉及了高级用法。 #### 主要内容概述 1. **PRIMARY ...
MySql约束
yangao1213192588的博客
10-05 731
mysql约束知识点
MySQL表的增删改查(基础)
weixin_63965017的博客
10-02 545
语法表名values(值，值......);值与列相匹配。
【MySQL】MySQL 数据库主从复制详解
人生苦短，睡觉要紧，睡醒再说
10-01 1290
MySQL 主从复制是实现高可用性和负载均衡的重要手段。通过合理配置和监控，可以显著提升数据库的可用性和性能，有效地管理数据同步，实现系统的弹性和鲁棒性。在实际应用中，应根据业务需求和系统架构来合理设计部署方案，并定期监控复制状态，以确保数据的一致性和完整性。了解主从复制的工作原理及其配置过程，对数据库管理员至关重要。希望本文能帮助您深入理解 MySQL 主从复制的相关知识。
【mysql相关总结】
Charles D
09-30 387
数据库小的表,全表扫描效率更高，不用建索引。索引的类型1.普通索引：基本的索引，没有任何约束限制2.唯一索引：类似普通索引,有唯一约束性3.主键索引：特殊的唯一索引,不允许有空值4.组合索引：多个列组合创建索引5.外键索引：只有innodb可以使用6.全文索引：只有myisam可以使用，且只能对英文进行全文检索注意事项1.复合索引遵循前缀原则（最左匹配原则）2.like查询,%号不能在前面，否则索引失效3.如果or前面的条件有索引,也会失效。
【MySQL 09】表的内外连接
最新发布
2301_76618602的博客
10-05 920
但是从上表可以看出，并不是每个学生都有成绩，但是我们也是要把没有成绩的学生也显示出来。由于员工名字在emp中，部门名称在dept中，因此我们需要对两表先做笛卡尔积，形成一张大的表，再从大的表中筛选出符合条件的。对stu表和exam表联合查询，把所有的成绩都显示出来，即使这个成绩没有学生与它对应，也要显示出来。我们理解了左外连接，那么右外连接也就能理解了，就是以右表为主，左边表没有符合条件的，那么就填NULL。可见内连接，只显示两表交集部分，因为只有1号和2号学生才有成绩，所以只显示1，2。
Python 3 和 MySQL(PyMySQL) 的完美结合
wjs2024的博客
10-02 652
PyMySQL 是一个纯 Python 编写的 MySQL 客户端库，它提供了与 MySQL 数据库进行交互的接口。PyMySQL 设计轻量、易于安装和使用，且完全兼容 MySQLdb 接口，使得从 MySQLdb 迁移变得非常简单。Python 3 和 PyMySQL 库的结合为开发人员提供了一种高效、简洁的方式来与 MySQL 数据库进行交互。通过本文的介绍，您应该能够理解如何安装 PyMySQL、建立数据库连接、执行 SQL 语句、处理查询结果以及处理可能出现的错误。
评论 1
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值