数据库技术课程复习4---MySQL语言(1)（表的创建，插入，修改，删除）
程序员文章站
2024-03-08 13:15:34
...
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>