数据库技术课程复习4---MySQL语言(1)（表的创建，插入，修改，删除）

最新推荐文章于 2022-10-26 00:30:45 发布
最新推荐文章于 2022-10-26 00:30:45 发布
阅读量568 收藏 2
点赞数 1
分类专栏： 数据库技术 文章标签： mysql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41842192/article/details/107289894
版权

MySQL语言（表的创建，插入，修改，删除）

0.学习前言

从这一节开始，要开始写代码实战啦，一起加油鸭！


在这里插入图片描述

1.数据库级别的操作指令

输入mysql -u root -p 后，键入密码，进入后台系统。
在这里插入图片描述
使用show databases;查看当前所有的数据库
使用 create database databaseName; 创建名字为databaseName的数据库
使用use databaseName; 选择进入databaseName数据库
使用drop database databaseName;删除databaseName数据库

2.表格级别的操作指令

2.1创建

CREATE TABLE <表名>（<列名> <数据类型>[ <列级完整性约束条件> ]
[，<列名> <数据类型>[ <列级完整性约束条件>] ] …
[，<表级完整性约束条件> ] ）；
<表名>：所要定义的基本表的名字
<列名>：组成该表的各个属性（列）
<列级完整性约束条件>：涉及相应属性列的完整性约束条件
<表级完整性约束条件>：涉及一个或多个属性列的完整性约束条件

[例] 建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，姓名也不能重复。
CREATE TABLE Student (
Sno varCHAR(5) NOT NULL UNIQUE，
Sname varCHAR(20) UNIQUE，
Ssex varCHAR(1) ，
Sage INT，
Sdept varCHAR(15)
)；

CREATE TABLE Student (Sno varCHAR(5)  NOT NULL  UNIQUE， Sname  varCHAR(20) UNIQUE，Ssex  varCHAR(1) ，Sage  INT，Sdept  varCHAR(15))；

2.2数据类型

MySQL的数据类型有
整数类型：tinyint(1),smallint(2),mediumint(3),int(4),bigint(8)
高精度型：decimal和numeric。可选长度和小数位数，如Numeric(10,2) 指字段是数字型,长度为10 小数为两位的。
char(N):定长字符，N取值为0-255
varchar(N):变长字符，N取值0~2的16次方-1
日期类型：DateTime(8),TimaStamp(4),Date(3),Year(1),Time(1)

上表的数据类型为：
在这里插入图片描述

2.3查看表和表结构

show tables;查看当前数据库中所有的表
describe tableName;查看tableName表的结构
在这里插入图片描述

2.4删除表

drop table tableName;删除表

2.5修改表结构

ALTER TABLE <表名>
[ ADD <新列名> <数据类型> [ 完整性约束 ] ]
[ DROP <完整性约束名> ]
[ MODIFY <列名> <数据类型> ]；

<表名>：要修改的基本表
ADD子句：增加新列和新的完整性约束条件
DROP子句：删除指定的完整性约束条件
MODIFY子句：用于修改列名和数据类型
[例1] 向Student表增加“入学时间”列，其数据类型为日期型。

 ALTER TABLE Student ADD Scome DATE；

不论基本表中原来是否已有数据，新增加的列一律为空值。
[例2]
删除属性列

ALTER TABLE Student  Drop Scome；

同时修改列名和列的数据类型的方法:
指令格式： alter table <表名> change column <旧列名> <新列名> <新的列类型>
[例3]
student表中列sname的类型是char(20),现在要修改为stuname varchar(20),SQL语句如下

alter table student change column sname stuname varchar(20);

3.元组级别的操作指令

3.1插入

往表格中插入一个元组

insert into student(sno,sname,ssex,sdept,sage) values('95010','陈冬','男','IS',18);

如果没有给完整表属性一定要严格按照顺序赋值,所有的都要赋值

insert into student values('95011','张成民','男',18,'CS');

显示表student的所有内容

select * from student

3.2修改

通过主码定位，修改某个属性
update tableName set property=CONST where key’[bool];
把95002的年龄加一,sno必须为主码

update student set sage=22 where sno='95002';

把所有的年龄加一

update student set sage=sage+1;

3.3删除

定位规则同上一个修改。
删除95009这个元组

delete from student where sno='95009';

4.命令行测试记录

Microsoft Windows [版本 10.0.17134.765]
(c) 2018 Microsoft Corporation。保留所有权利。

C:\Users\袁一博>mysql -u root -p
Enter password: *************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 8.0.11 MySQL Community Server - GPL

Copyright (c) 2000, 2018, 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 |
| jobyuan            |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.04 sec)

mysql> use school
Database changed
mysql> create Table Course(Cno varchar(3),Cname varchar(60),Cpno varchar(3),Ccreadit int not null,Primary key(Cno));
Query OK, 0 rows affected (0.14 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jobyuan            |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql> show tables
    -> ;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| student          |
+------------------+
2 rows in set (0.02 sec)

mysql> create table sc(sno varchar(5),cno varchar(3),grade int,primary key(sno,cno),foreign key(sno)references student(sno),foreign key(cno)references course(cno));
Query OK, 0 rows affected (0.10 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
+------------------+
3 rows in set (0.00 sec)

mysql> show create table sc
    -> ;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sc    | CREATE TABLE `sc` (
  `sno` varchar(5) NOT NULL,
  `cno` varchar(3) NOT NULL,
  `grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  KEY `cno` (`cno`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> show create table sc;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                           |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sc    | CREATE TABLE `sc` (
  `sno` varchar(5) NOT NULL,
  `cno` varchar(3) NOT NULL,
  `grade` int(11) DEFAULT NULL,
  PRIMARY KEY (`sno`,`cno`),
  KEY `cno` (`cno`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`sno`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show tables;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
+------------------+
3 rows in set (0.00 sec)

mysql> descrebe sc
    -> ;
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 'descrebe sc' at line 1
mysql> describe sc;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| sno   | varchar(5) | NO   | PRI | NULL    |       |
| cno   | varchar(3) | NO   | PRI | NULL    |       |
| grade | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

mysql> alter table student add scome data;
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 'data' at line 1
mysql> alter table student add scome DATA;
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 'DATA' at line 1
mysql> alter table student ADD scome DATA;
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 'DATA' at line 1
mysql> ALTER TABLE Student ADD Scome DATE;
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | varchar(5)  | NO   | PRI | NULL    |       |
| Sname | varchar(20) | YES  | UNI | NULL    |       |
| Ssex  | varchar(1)  | YES  |     | NULL    |       |
| Sage  | int(11)     | YES  |     | NULL    |       |
| Sdept | varchar(15) | YES  |     | NULL    |       |
| Scome | date        | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)

mysql> alter table student drop scome;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | varchar(5)  | NO   | PRI | NULL    |       |
| Sname | varchar(20) | YES  | UNI | NULL    |       |
| Ssex  | varchar(1)  | YES  |     | NULL    |       |
| Sage  | int(11)     | YES  |     | NULL    |       |
| Sdept | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> describe sc;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| sno   | varchar(5) | NO   | PRI | NULL    |       |
| cno   | varchar(3) | NO   | PRI | NULL    |       |
| grade | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> alter table strdent modify sage smallint
    -> ;
ERROR 1146 (42S02): Table 'school.strdent' doesn't exist
mysql> alter table student modify sage smallint
    -> ;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | varchar(5)  | NO   | PRI | NULL    |       |
| Sname | varchar(20) | YES  | UNI | NULL    |       |
| Ssex  | varchar(1)  | YES  |     | NULL    |       |
| sage  | smallint(6) | YES  |     | NULL    |       |
| Sdept | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> alter table strdent modify Sage smallint
    -> ;
ERROR 1146 (42S02): Table 'school.strdent' doesn't exist
mysql> alter table student modify Sage smallint
    -> ;
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | varchar(5)  | NO   | PRI | NULL    |       |
| Sname | varchar(20) | YES  | UNI | NULL    |       |
| Ssex  | varchar(1)  | YES  |     | NULL    |       |
| Sage  | smallint(6) | YES  |     | NULL    |       |
| Sdept | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> show create able student;
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 'able student' at line 1
mysql> show create table student;
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                     |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `Sno` varchar(5) NOT NULL,
  `Sname` varchar(20) DEFAULT NULL,
  `Ssex` varchar(1) DEFAULT NULL,
  `Sage` smallint(6) DEFAULT NULL,
  `Sdept` varchar(15) DEFAULT NULL,
  UNIQUE KEY `Sno` (`Sno`),
  UNIQUE KEY `Sname` (`Sname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table student drop key sname;
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> describe student;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| Sno   | varchar(5)  | NO   | PRI | NULL    |       |
| Sname | varchar(20) | YES  |     | NULL    |       |
| Ssex  | varchar(1)  | YES  |     | NULL    |       |
| Sage  | smallint(6) | YES  |     | NULL    |       |
| Sdept | varchar(15) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> insert into student(sno,sname,ssex,sdept,sage) values('95010','陈冬','男','IS',18);
Query OK, 1 row affected (0.05 sec)

mysql> select * from student
    -> ;
+-------+-------+------+------+-------+
| Sno   | Sname | Ssex | Sage | Sdept |
+-------+-------+------+------+-------+
| 95010 | 陈冬  | 男   |   18 | IS    |
+-------+-------+------+------+-------+
1 row in set (0.01 sec)

mysql> insert into student values('95011','张成民','男',18,'CS');
Query OK, 1 row affected (0.07 sec)

mysql> select * from student;
+-------+--------+------+------+-------+
| Sno   | Sname  | Ssex | Sage | Sdept |
+-------+--------+------+------+-------+
| 95010 | 陈冬   | 男   |   18 | IS    |
| 95011 | 张成民 | 男   |   18 | CS    |
+-------+--------+------+------+-------+
2 rows in set (0.01 sec)

mysql> insert into sc(sno,cno) values('95011','1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`))
mysql> insert into sc(sno,cno) values('95011','1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`))
mysql> insert into SC(sno,cno) values('95011','1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`))
mysql> show tables
    -> ;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| sc               |
| student          |
+------------------+
3 rows in set (0.00 sec)

mysql> insert into sc(sno,cno) values('95011','1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`))
mysql> insert into school.sc(sno,cno) values('95011','1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`))
mysql> insert into sc(sno,cno) values('95011','1');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`cno`) REFERENCES `course` (`cno`))
mysql> show create table course
    -> ;
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table  | Create Table                                                                                                                                                                                                                                       |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| course | CREATE TABLE `course` (
  `Cno` varchar(3) NOT NULL,
  `Cname` varchar(60) DEFAULT NULL,
  `Cpno` varchar(3) DEFAULT NULL,
  `Ccreadit` int(11) NOT NULL,
  PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into student values('95001','张三','男',20,'CS');
Query OK, 1 row affected (0.06 sec)

mysql> insert into student values('95002','李四','男',21,'IS');
Query OK, 1 row affected (0.10 sec)

mysql> insert into student values('95003','王五','男',18,'MA');
Query OK, 1 row affected (0.07 sec)

mysql> insert into student values('95004','马六','女',19,'CS');
Query OK, 1 row affected (0.03 sec)

mysql> insert into student values('95005','苏三','女',19,'IS');
Query OK, 1 row affected (0.05 sec)

mysql> insert into student values('95006','刘七','女',18,'IS');
Query OK, 1 row affected (0.01 sec)

mysql> insert into student values('95007','刘三姐','女',22,'IS');
Query OK, 1 row affected (0.09 sec)

mysql> insert into student values('95008','欧阳锋','男',23,'MA');
Query OK, 1 row affected (0.09 sec)

mysql> insert into student values('95009','欧阳大侠','男',22,'MA');
Query OK, 1 row affected (0.08 sec)

mysql> select * frome student
    -> ;
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 'frome student' at line 1
mysql> select * from student
    -> ；
    -> ;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   20 | CS    |
| 95002 | 李四     | 男   |   21 | IS    |
| 95003 | 王五     | 男   |   18 | MA    |
| 95004 | 马六     | 女   |   19 | CS    |
| 95005 | 苏三     | 女   |   19 | IS    |
| 95006 | 刘七     | 女   |   18 | IS    |
| 95007 | 刘三姐   | 女   |   22 | IS    |
| 95008 | 欧阳锋   | 男   |   23 | MA    |
| 95009 | 欧阳大侠 | 男   |   22 | MA    |
| 95010 | 陈冬     | 男   |   18 | IS    |
| 95011 | 张成民   | 男   |   18 | CS    |
+-------+----------+------+------+-------+
11 rows in set (0.01 sec)

mysql> show create table student;
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                     |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
  `Sno` varchar(5) NOT NULL,
  `Sname` varchar(20) DEFAULT NULL,
  `Ssex` varchar(1) DEFAULT NULL,
  `Sage` smallint(6) DEFAULT NULL,
  `Sdept` varchar(15) DEFAULT NULL,
  UNIQUE KEY `Sno` (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+---------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into course values(1)
    -> ;
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> describe course;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| Cno      | varchar(3)  | NO   | PRI | NULL    |       |
| Cname    | varchar(60) | YES  |     | NULL    |       |
| Cpno     | varchar(3)  | YES  |     | NULL    |       |
| Ccreadit | int(11)     | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

mysql> insert into course values('1','MATH',NULL,6);
Query OK, 1 row affected (0.03 sec)

mysql> insert into course values('2','DB_DESIGN','3',2);
Query OK, 1 row affected (0.01 sec)

mysql> insert into course values('3','P_DESIGN','4',3);
Query OK, 1 row affected (0.10 sec)

mysql> insert into course values('4','OS',NULL,2);
Query OK, 1 row affected (0.01 sec)

mysql> select * from course;
+-----+-----------+------+----------+
| Cno | Cname     | Cpno | Ccreadit |
+-----+-----------+------+----------+
| 1   | MATH      | NULL |        6 |
| 2   | DB_DESIGN | 3    |        2 |
| 3   | P_DESIGN  | 4    |        3 |
| 4   | OS        | NULL |        2 |
+-----+-----------+------+----------+
4 rows in set (0.00 sec)

mysql> insert into sc(sno,cno) values('95011','1');
Query OK, 1 row affected (0.12 sec)

mysql> select * from sc
    -> ;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95011 | 1   |  NULL |
+-------+-----+-------+
1 row in set (0.00 sec)

mysql> insert into sc(sno,cno) values('95001','1',92);
ERROR 1136 (21S01): Column count doesn't match value count at row 1
mysql> describe sc
    -> ;
+-------+------------+------+-----+---------+-------+
| Field | Type       | Null | Key | Default | Extra |
+-------+------------+------+-----+---------+-------+
| sno   | varchar(5) | NO   | PRI | NULL    |       |
| cno   | varchar(3) | NO   | PRI | NULL    |       |
| grade | int(11)    | YES  |     | NULL    |       |
+-------+------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> insert into sc values('95001','1',92);
Query OK, 1 row affected (0.07 sec)

mysql> insert into sc values('95002','2',94);
Query OK, 1 row affected (0.06 sec)

mysql> insert into sc values('95001','3',90);
Query OK, 1 row affected (0.11 sec)

mysql> insert into sc values('95001','2',94);
Query OK, 1 row affected (0.07 sec)

mysql> select * from sc
    -> ;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95002 | 2   |    94 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
5 rows in set (0.00 sec)

mysql> insert into sc values('95001','4',97);
Query OK, 1 row affected (0.06 sec)

mysql> insert into sc values('95002','2',90);
ERROR 1062 (23000): Duplicate entry '95002-2' for key 'PRIMARY'
mysql> insert into sc values('95002','3',80);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sc values('95003','2',NULL);
Query OK, 1 row affected (0.01 sec)

mysql> insert into sc values('95004','3',NULL);
Query OK, 1 row affected (0.05 sec)

mysql> insert into sc values('95004','4',87);
Query OK, 1 row affected (0.08 sec)

mysql> insert into sc values('95005','1',90);
Query OK, 1 row affected (0.10 sec)

mysql> insert into sc values('95005','2',98);
Query OK, 1 row affected (0.02 sec)

mysql> insert into sc values('95005','3',90);
Query OK, 1 row affected (0.12 sec)

mysql> insert into sc values('95005','4',89);
Query OK, 1 row affected (0.07 sec)

mysql> select * from sc
    -> ;
+-------+-----+-------+
| sno   | cno | grade |
+-------+-----+-------+
| 95001 | 1   |    92 |
| 95001 | 2   |    94 |
| 95001 | 3   |    90 |
| 95001 | 4   |    97 |
| 95002 | 2   |    94 |
| 95002 | 3   |    80 |
| 95003 | 2   |  NULL |
| 95004 | 3   |  NULL |
| 95004 | 4   |    87 |
| 95005 | 1   |    90 |
| 95005 | 2   |    98 |
| 95005 | 3   |    90 |
| 95005 | 4   |    89 |
| 95011 | 1   |  NULL |
+-------+-----+-------+
14 rows in set (0.00 sec)

mysql> select * from student;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   20 | CS    |
| 95002 | 李四     | 男   |   21 | IS    |
| 95003 | 王五     | 男   |   18 | MA    |
| 95004 | 马六     | 女   |   19 | CS    |
| 95005 | 苏三     | 女   |   19 | IS    |
| 95006 | 刘七     | 女   |   18 | IS    |
| 95007 | 刘三姐   | 女   |   22 | IS    |
| 95008 | 欧阳锋   | 男   |   23 | MA    |
| 95009 | 欧阳大侠 | 男   |   22 | MA    |
| 95010 | 陈冬     | 男   |   18 | IS    |
| 95011 | 张成民   | 男   |   18 | CS    |
+-------+----------+------+------+-------+
11 rows in set (0.00 sec)

mysql> update student set sage=22 where sno='95002';
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   20 | CS    |
| 95002 | 李四     | 男   |   22 | IS    |
| 95003 | 王五     | 男   |   18 | MA    |
| 95004 | 马六     | 女   |   19 | CS    |
| 95005 | 苏三     | 女   |   19 | IS    |
| 95006 | 刘七     | 女   |   18 | IS    |
| 95007 | 刘三姐   | 女   |   22 | IS    |
| 95008 | 欧阳锋   | 男   |   23 | MA    |
| 95009 | 欧阳大侠 | 男   |   22 | MA    |
| 95010 | 陈冬     | 男   |   18 | IS    |
| 95011 | 张成民   | 男   |   18 | CS    |
+-------+----------+------+------+-------+
11 rows in set (0.00 sec)

mysql> update student set sage=sage+1;
Query OK, 11 rows affected (0.08 sec)
Rows matched: 11  Changed: 11  Warnings: 0

mysql> select * from student;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   21 | CS    |
| 95002 | 李四     | 男   |   23 | IS    |
| 95003 | 王五     | 男   |   19 | MA    |
| 95004 | 马六     | 女   |   20 | CS    |
| 95005 | 苏三     | 女   |   20 | IS    |
| 95006 | 刘七     | 女   |   19 | IS    |
| 95007 | 刘三姐   | 女   |   23 | IS    |
| 95008 | 欧阳锋   | 男   |   24 | MA    |
| 95009 | 欧阳大侠 | 男   |   23 | MA    |
| 95010 | 陈冬     | 男   |   19 | IS    |
| 95011 | 张成民   | 男   |   19 | CS    |
+-------+----------+------+------+-------+
11 rows in set (0.00 sec)

mysql> update student set sno='95022'where sno='95001';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`Sno`))
mysql> update student set sno='95022' where sno='95001';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`Sno`))
mysql> delete from student where sno='95001';
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`school`.`sc`, CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`sno`) REFERENCES `student` (`Sno`))
mysql> select sno,sname from student;
+-------+----------+
| sno   | sname    |
+-------+----------+
| 95001 | 张三     |
| 95002 | 李四     |
| 95003 | 王五     |
| 95004 | 马六     |
| 95005 | 苏三     |
| 95006 | 刘七     |
| 95007 | 刘三姐   |
| 95008 | 欧阳锋   |
| 95009 | 欧阳大侠 |
| 95010 | 陈冬     |
| 95011 | 张成民   |
+-------+----------+
11 rows in set (0.00 sec)

mysql> select sname,sno,sdept from student;
+----------+-------+-------+
| sname    | sno   | sdept |
+----------+-------+-------+
| 张三     | 95001 | CS    |
| 李四     | 95002 | IS    |
| 王五     | 95003 | MA    |
| 马六     | 95004 | CS    |
| 苏三     | 95005 | IS    |
| 刘七     | 95006 | IS    |
| 刘三姐   | 95007 | IS    |
| 欧阳锋   | 95008 | MA    |
| 欧阳大侠 | 95009 | MA    |
| 陈冬     | 95010 | IS    |
| 张成民   | 95011 | CS    |
+----------+-------+-------+
11 rows in set (0.00 sec)

mysql> select * from student;
+-------+----------+------+------+-------+
| Sno   | Sname    | Ssex | Sage | Sdept |
+-------+----------+------+------+-------+
| 95001 | 张三     | 男   |   21 | CS    |
| 95002 | 李四     | 男   |   23 | IS    |
| 95003 | 王五     | 男   |   19 | MA    |
| 95004 | 马六     | 女   |   20 | CS    |
| 95005 | 苏三     | 女   |   20 | IS    |
| 95006 | 刘七     | 女   |   19 | IS    |
| 95007 | 刘三姐   | 女   |   23 | IS    |
| 95008 | 欧阳锋   | 男   |   24 | MA    |
| 95009 | 欧阳大侠 | 男   |   23 | MA    |
| 95010 | 陈冬     | 男   |   19 | IS    |
| 95011 | 张成民   | 男   |   19 | CS    |
+-------+----------+------+------+-------+
11 rows in set (0.00 sec)

mysql> select sname,2019-sage from student;
+----------+-----------+
| sname    | 2019-sage |
+----------+-----------+
| 张三     |      1998 |
| 李四     |      1996 |
| 王五     |      2000 |
| 马六     |      1999 |
| 苏三     |      1999 |
| 刘七     |      2000 |
| 刘三姐   |      1996 |
| 欧阳锋   |      1995 |
| 欧阳大侠 |      1996 |
| 陈冬     |      2000 |
| 张成民   |      2000 |
+----------+-----------+
11 rows in set (0.00 sec)

mysql> select sname,'Your of Birth:',2019-sage,lcase(sdept) from student;
+----------+----------------+-----------+--------------+
| sname    | Your of Birth: | 2019-sage | lcase(sdept) |
+----------+----------------+-----------+--------------+
| 张三     | Your of Birth: |      1998 | cs           |
| 李四     | Your of Birth: |      1996 | is           |
| 王五     | Your of Birth: |      2000 | ma           |
| 马六     | Your of Birth: |      1999 | cs           |
| 苏三     | Your of Birth: |      1999 | is           |
| 刘七     | Your of Birth: |      2000 | is           |
| 刘三姐   | Your of Birth: |      1996 | is           |
| 欧阳锋   | Your of Birth: |      1995 | ma           |
| 欧阳大侠 | Your of Birth: |      1996 | ma           |
| 陈冬     | Your of Birth: |      2000 | is           |
| 张成民   | Your of Birth: |      2000 | cs           |
+----------+----------------+-----------+--------------+
11 rows in set (0.01 sec)

mysql> select snmae NAME,'Year of Birth:' BIRTH,2019-sage BIRTHDAY,lcase(sdept) DEPARTMENT from student;
ERROR 1054 (42S22): Unknown column 'snmae' in 'field list'
mysql> select sname NAME,'Year of Birth:' BIRTH,2019-sage BIRTHDAY,lcase(sdept) DEPARTMENT from student;
+----------+----------------+----------+------------+
| NAME     | BIRTH          | BIRTHDAY | DEPARTMENT |
+----------+----------------+----------+------------+
| 张三     | Year of Birth: |     1998 | cs         |
| 李四     | Year of Birth: |     1996 | is         |
| 王五     | Year of Birth: |     2000 | ma         |
| 马六     | Year of Birth: |     1999 | cs         |
| 苏三     | Year of Birth: |     1999 | is         |
| 刘七     | Year of Birth: |     2000 | is         |
| 刘三姐   | Year of Birth: |     1996 | is         |
| 欧阳锋   | Year of Birth: |     1995 | ma         |
| 欧阳大侠 | Year of Birth: |     1996 | ma         |
| 陈冬     | Year of Birth: |     2000 | is         |
| 张成民   | Year of Birth: |     2000 | cs         |
+----------+----------------+----------+------------+
11 rows in set (0.00 sec)

mysql> select sno from sc;
+-------+
| sno   |
+-------+
| 95001 |
| 95005 |
| 95011 |
| 95001 |
| 95002 |
| 95003 |
| 95005 |
| 95001 |
| 95002 |
| 95004 |
| 95005 |
| 95001 |
| 95004 |
| 95005 |
+-------+
14 rows in set (0.01 sec)

mysql> select distinct sno from sc;
+-------+
| sno   |
+-------+
| 95001 |
| 95002 |
| 95003 |
| 95004 |
| 95005 |
| 95011 |
+-------+
6 rows in set (0.01 sec)

mysql> select sname,sage from student where sage<20;
+--------+------+
| sname  | sage |
+--------+------+
| 王五   |   19 |
| 刘七   |   19 |
| 陈冬   |   19 |
| 张成民 |   19 |
+--------+------+
4 rows in set (0.00 sec)

mysql> select sname,sage from student where not sage>=20;
+--------+------+
| sname  | sage |
+--------+------+
| 王五   |   19 |
| 刘七   |   19 |
| 陈冬   |   19 |
| 张成民 |   19 |
+--------+------+
4 rows in set (0.00 sec)

mysql> select sname,sdept,sage from student where sage between 20 and 23;
+----------+-------+------+
| sname    | sdept | sage |
+----------+-------+------+
| 张三     | CS    |   21 |
| 李四     | IS    |   23 |
| 马六     | CS    |   20 |
| 苏三     | IS    |   20 |
| 刘三姐   | IS    |   23 |
| 欧阳大侠 | MA    |   23 |
+----------+-------+------+
6 rows in set (0.00 sec)

mysql> select sname,ssex from student where sdept in('IS','MA','CS');
+----------+------+
| sname    | ssex |
+----------+------+
| 张三     | 男   |
| 李四     | 男   |
| 王五     | 男   |
| 马六     | 女   |
| 苏三     | 女   |
| 刘七     | 女   |
| 刘三姐   | 女   |
| 欧阳锋   | 男   |
| 欧阳大侠 | 男   |
| 陈冬     | 男   |
| 张成民   | 男   |
+----------+------+
11 rows in set (0.00 sec)

mysql>
JobYuanPioneer
关注
专栏目录
零基础入门 全角度解读企业主流数据库MySQL8.0 --- 复习笔记
争当做一个有趣的人
03-09 1300
〖课程介绍〗: 本课程遵循“用户体验至上”的原则，不敢说设计精良，尽善尽美，却也竭尽全力去锻造良好的学习体验。包含三方面，（1）设计之初：遵循“有教无类，教学相长”的原则；（2）学习路线：采用循序渐进的教学模式，带你从了解到熟悉，到掌握，到精通，最后到深谙；（3）学习内容：课程遵循前沿性，以当前最新企业主流的MySQL8.0版本为内容。以“实用”为课程最高宗旨，带你搞定工作中SQL优化 痛点问题，最终的目的是：助力个人能力的快速提升，最终服务工作所需，助你占据不可替代的一席之地 第1章 【蓦然回首】开篇..
mysql-数据库第八次作业-复习作业（工厂项目）
luo_ci的博客
03-31 1256
1.什么是数据库？ 答：组织、存储和管理数据的仓库。 2.用户可以对数据库执行什么操作？ 答：增、删、改、查 3.什么是数据库管理系统？ 答：专门用于创建和管理数据的一套软件 4.一个数据库管理系统可以管理多少个数据库？ 答：无数个 5.每张表是否可以有很多条数据？ 答：无数条 6.打开控制台，输入指令登录MySQL 7.查看所有数据库 8.创建新的数据库，名为temp_姓名全拼 9.将temp_姓名全拼数据库的默认字符集改为gbk 10.查看temp_姓名全拼数据库创建信息，告
MySQL经典50道练习题
Melo
04-14 1万+
MySQL基础+JDBC编程教程：https://zhuanlan.zhihu.com/p/32651307 参考答案： https://blog.csdn.net/dehu_zhou/article/details/52881587 https://blog.csdn.net/huaxiawudi/article/details/82288044 其中部分代码进行了改写，技巧：每次只写最简单的语...
javaweb后端第1次作业
lijin786的博客
07-07 1102
1.建表 1.1student表 create table student ( no int(10) unsigned PRIMARY KEY , name varchar(20) NOT NULL, sex varchar(10) DEFAULT NULL, birthday datetime DEFAULT NULL, class int(10) unsigned DEFAULT NULL); 1.2curse表 create table curse ( con .
数据库系统概论--第三章 关系数据库标准语言SQL
Du霄霖
08-12 1840
SQL概述 SQL的特点 SQL集数据查询、数据操纵、数据定义和 数据控制功能于一体，其主要特点包含以下几部分： 1、功能统一 SQL集数据定义语言、数据操纵语言、数据控制语言的功能于一体，语言风格统一，可以独立完成数据库生命周期内的全部活动。 特别是用户在数据库系统投入运行后还可以根据需要随时地、逐步地修改模式，并不影响数据库的运行。 2、高度非工程话 只要提出“做什么”而无需之命“怎么做”，因此无需了解存取路径。存取路径的选择以及SQL的操作过程由系统自动完成。 3、面向集合的操作方式
javaWeb第一次作业
qq_37244548的博客
07-07 715
数据库作业16个题 现在有一教学管理系统，具体的关系模式如下： Student (no, name, sex, birthday, class) Teacher (no, name, sex, birthday, prof, depart) Course (cno, cname, tno) Score (no, cno, degree) 其中表中包含如下数据： Course表： Score表： Student表： Teacher表： 根据上面描述完成下面问题..
SQL基础应用
qq_41116260的博客
10-26 970
SQL基础应用
数据库课程设计项目-背单词软件
最新发布
05-07
4. **SQL语言**: SQL（结构化查询语言）是与数据库交互的语言。学生需要学会编写SQL语句来插入、更新、删除和查询数据。例如，查询某个用户的学习记录或统计已完成任务的数量。 5. **用户界面（UI）设计**: 背单词...
计院学网盘复习资料--数据库.zip
07-01
SQL（Structured Query Language）是用于管理关系型数据库的标准语言，而MySQL则是一种广泛使用的开源关系数据库管理系统。本压缩包“计院学网盘复习资料--数据库.zip”显然是针对计算机学院学生的期末复习资料，...
mysql复习笔记-by燕十八.docx
01-08
4. **创建/删除数据库**：创建数据库的命令是`create database dbname charset utf8;`，删除数据库则是`drop database dbname;`。 5. **表级操作**： - **显示表**：使用`show tables;`列出库中的所有表。 - **...
数据库基础知识
Coder_android
09-28 6898
今天到某公司笔试，数据库考的比较多，但是说老实话，考的也比较基础。现在趁回忆得起来，将数据库知识简单整理如下：   一、建表指令 比如创建一个学生表student，它由学号Sno，姓名Sname，性别Ssex，年龄Sage，所在系Sdept五个属性组成。其中学号不能为空，值是唯一的，并且姓名取值也唯一。 CREATE TABLE Student (Sno    CHAR(10) NOT 
sno什么意思mysql_在MySQL中，设有学生表Student(sno,sname,sage)，各字段的含义分别是学生学号、姓名、年龄。其中数据类型分别为（char(6),char(20),tin...
weixin_28683689的博客
01-19 6086
【单选题】在气相色谱分析中,用于定量分析的参数是【单选题】发育在砾岩中的张节理在遇到砾石时通常会 。【判断题】两组共轭剪节理的交线一般与最小主应力轴平行。【单选题】在MySQL中，设有学生表Student(sno,sname,birthday)，各字段的含义分别是学生学号、姓名、出生日期。其中数据类型分别为(char(4),char(20),date),下列语句会报错的是( )。...
MySQL修改列名称或列的数据类型
YYyyy
05-19 6089
一、只修改列的数据类型的方法: 语法： alter table 表名 modify column 列名 新的列的类型 例如:student表中列stu_name的类型是char(20),现在要修改为varchar(20),SQL语句如下      alter table student modify column stu_name varchar(20);   二、同时修改列名和列的数据
sysname数据类型
学无止境
06-26 1万+
sysname SQL Server 实例包括用户定义的名为 sysname 的数据类型。sysname 用于表列、变量以及用于存储对象名的存储过程参数。sysname 的精确定义与标识符规则相关；因此，SQL Server 的各个实例会有所不同。sysname 与 nvarchar(128) 作用相同。SQL Server 6.5 或早期版本仅支持较小的标识符；因此，在早期版本中，sysn
数据库报duplicate XXX entry for key primary错误的可能原因以及解决方法
热门推荐
“时光清浅，不觉多年”的博客
02-23 9万+
今天在使用数据库的过程中，发现一直报duplicate entry for key primary的错误，如下图所示： 这个问题直译过来就是主键设置重复，或者说是主键冲突，因为第一次碰到这样的问题，有些不明所以，就在网上差了一些资料，发现了这么一篇文章 ======> 这是文章链接 其中说到这种情况的可能原因有两种： 主键没有设置自增 插入线程频率较高，没有处理好事务，造成插入sql执行顺序混乱 该文章中也给予了对应的解决方案，可以参考。 不过这篇文章中所说到的情况并不适应于我今天遇到的问题
mysql 出现错误 Duplicate entry for key PRIMARY 解决办法
digitalkee的博客
06-22 6447
原来是没有主键的，想增加一列为主键。 mysql> desc t111; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(24) | YES | | robert |
mysql上机试题和答案_数据库上机习题和答案.doc
weixin_36253081的博客
01-27 4089
数据库上机习题和答案数据库及应用复习题一、设计题有一个[学生课程]数据库，数据库中包括三个表：学生表Student由学号(Sno)、姓名(Sname)、性别(Ssex)、年龄(Sage)、所在系(Sdept)五个属性组成，记为： Student(Sno,Sname,Ssex,Sage,Sdept) ，Sno 为关键字。课程表Course由课程号(Cno)、课程名(Cname)、先修课号(Cpno)...
数据库第五次作业 查询数据
weixin_61961888的博客
03-23 1542
一、查询教师表中教师号在T5到T10之间的所有字段数据。 select *from zhongzheng_teacher where NO between “T10” and “T5”; 二、查询授课表中 周数为14 的 课程号、教师号、周数、教室号 字段数据 select * from qiufaqing_school_teaching where week=15; 三、查询授课表中去重后的所有教师号 select distinct teacher_no from qiufaq..
向Student表增加“入学时间列，数据类型为日期型
weixin_40222848的博客
04-04 1万+
向Student表增加“入学时间列，数据类型为日期型sql sever : alter table Student ADD S_entrance datetime;oracle:alter table Student ADD S_entrance date;
数据库系统概论：课程8-新增计算机组织课程的插入操作详解
在整个数据库系统概论的学习过程中，课程大纲涵盖了多个关键章节，包括数据库系统概述、数据模型、SQL语言、关系系统查询优化、数据库设计、数据库恢复技术、并发控制、数据库安全性和完整性等多个方面。学生们需要...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值