数据库---表的创建与属性修改

显示指令

show databases;//显示所有数据库
show tables;//显示当前库下所有的表
show create table tableName;//显示tableName这张表的属性
select * from tableName;//显示tableName这张表所有的元组
describe tableName;//显示tableName这张表的属性

创建表

创建

//创建student表
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));

//创建course表:Course(Cno,Cname,Cpno,Ccreadit) 主码为Cno
create Table Course(Cno varchar(3),Cname varchar(60),Cpno varchar(3),Ccreadit int not null,Primary key(Cno));
//创建sc表Sc(Sno,Cno,Grade) 主码为(sno,cno)外码为(sno,cno),注明索引到另一个表
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));

属性修改

ALTER TABLE Student ADD Scome DATE;//增加一个属性(date类型)
alter table student drop scome;//删除scome属性
alter table student modify sage smallint;//更改sage的数据类型
alter table student drop key sname;//去除sname的主码特征
同时修改列名和列的数据类型的方法:
通常可以写成 alter table 表名 change column 旧列名 新列名 新的列类型
例如:
student表中列sname的类型是char(20),现在要修改为stuname varchar(20),SQL语句如下

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

元组(记录)

插入

//往表格中插入一个元组
insert into student(sno,sname,ssex,sdept,sage) values(‘95010’,‘陈冬’,‘男’,‘IS’,18);
//显示表student的所有内容
select * from student
//如果没有给完整表属性一定要严格按照顺序赋值,所有的都要赋值
insert into student values(‘95011’,‘张成民’,‘男’,18,‘CS’);

修改

update tableName set property=CONST where key;*
update student set sage=22 where sno=‘95002’;//把95002的年龄加一,sno必须为主码
update student set sage=sage+1;//把所有的年龄加一

删除

delete from student where sno=‘95009’;//删除95009这个元组

查询

课堂记录///

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>
  • 0
    点赞
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值