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

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>
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值