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

最新推荐文章于 2024-04-22 10:17:13 发布
最新推荐文章于 2024-04-22 10:17:13 发布
阅读量3k 收藏 5
点赞数
分类专栏： 学习笔记
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/qq_41842192/article/details/90753425
版权

目录

显示指令

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>
JobYuanPioneer
关注
  • 0
    点赞
  • 踩
  • 5
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
mysql创建表属性_【学习之Mysql数据库】mysql数据库创建表的属性详解
weixin_34942265的博客
01-26 1531
该楼层疑似违规已被系统折叠隐藏此楼查看此楼MySQL中create table语句的基本语法是：CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement]TEMPORARY：该关键字表示用MySQL create table新建的表为...
用MySQL语句为SC定义主码_珍藏的数据库SQL基础练习题答案
weixin_35538624的博客
02-12 1686
一,基本表的定义与删除.题1:用SQL语句创建如下三张表：学生(Student),课程表(Course),和学生选课表(SC),这三张表的结构如表1-1到表1-3所示。表1-1 Student表结构列名 说明 数据类型 约束Sno 学号 字符串，长度为7 主码Sname 姓名 字符串，长度为10 非空Ssex 性别 字符串，长度为2 取‘男’或‘女’Sage 年龄 整数 取值15~45Sdept ...
MySQL修改表属性的SQL语句
weixin_30732487的博客
08-05 266
ALTER TABLE 句法 ALTER [IGNORE] TABLE tbl_name alter_spec [, alter_spec ...] alter_specification: ADD [COLUMN] create_definition [FIRST | AFTER column_name ] or ADD [COLUMN] (create_defin...
数据库实验三
weixin_62655793的博客
11-02 343
4.把AL2021224828用户的表Student的修改学生学号的权限赋予用户” S学号U4”，然后让S2021224828U4用户修改AL2021224828的student表的SNO数据。2.创建before触发器，当向sc表中插入一行记录时，如果Student表中没有对应的sno值，则自动向student中插入一条记录，sno值取插入的记录中的学号，姓名为空字符串或者“无名”。5．建立表SC，要求SNO参照STUDENT表的学号，且当删除Student表中的一个学生记录时，级联删除学生的选课记录。
数据表基本操作——创建数据表、查看表结构、修改表、删除表
最新发布
2302_80178071的博客
04-22 1199
1.2.8设置表的存储引擎创建数据表，实际上是规定列属性和实现数据完整性约束的过程，基本语法格式如下： 注意：create为创建数据表的关键字，table_name表示数据表的名称，表的结构在括号中定义，col_name表示字段名，data_type表示数据类型，constraints表示约束条件，各个字段之间使用逗号进行分隔，语句的最后要以分号结束。数据表表名应遵循以下原则：如果没有创建数据库，先创建数据库，比如我创建的数据库是zxj，执行以下语句创建数据库： 创建数据表需要选择数据库，比如我上一
SQL Server创建及修改表实验报告
qq_66238381的博客
01-02 4598
因为该对象有一个外键约束，student是一个主表，而sc表是一个从表和它相关联的，互相引用的所以不能删除。创建student（学生信息表），在数据库XSCJ中创建一个表student,它由学号sno,姓名sname,性别ssex,年龄sage,系部sdept五个属性组成。因为学号原本是允许空的，要修改学号这个字段是非空的才可以，课程号也是一样。课程和学生是多对多的联系，这个表的主键要包括学号和课程两个字段。主键所在的表叫主表，外键所在的表叫从表，先要有主表才能有从表。删除student 表。
Access数据库-表的创建与使用.ppt
11-12
本章主要介绍Access数据库中的表的创建和使用，包括表的定义、建立表的方法、字段属性、数据的有效性规则、建立表关系、表关系的高级设置、修改数据表结构和记录等内容。 1. 表的定义 表是整个数据库的基本单位，...
SQL-server数据库和表的创建与管理.doc
05-24
在SSMS中，可以通过右击"数据库"节点，选择"新建数据库"，在"新建数据库"对话框中设置数据库的常规属性（如名称为"Product"），然后点击"确定"，即可完成数据库创建。 2. 使用SQL命令创建数据库： 可以使用以下...
Access数据库应用技术-教案-课题：任务1-创建数据库.docx
12-17
7. 安全性与权限管理：Access还允许设置用户访问权限，控制不同用户对数据库的读、写、修改和删除权限，确保数据安全。 通过学习和实践Access数据库应用技术，可以提升数据管理能力，有效提高工作效率。同时，掌握...
用T-SQL语句完成创建与管理数据库操作练习.doc
11-17
T-SQL是一种专门用于管理和操作关系数据库的语言，它可以用来创建、修改和管理数据库。下面是使用T-SQL语句创建和管理数据库操作的实践。 创建数据库 使用T-SQL语句可以创建新的数据库，语法为： ```sql CREATE ...
实验2-创建与管理数据库-.doc
07-15
实验内容包括数据库存储结构、数据库对象、系统数据库、创建数据库、查看数据库和修改数据库等部分。 一、数据库存储结构 数据库是一种逻辑实体，由一组操作系统文件组成。这些文件包括主数据文件、次数据文件和...
'modify' 附近有语法错误 'change'附近有语法错误
无
11-20 5233
修改列的数据类型： 错误命令版本1： alter table student modify column studentgender char(2); --‘modify’ 附近有语法错误。 错误命令版本2： alter table student modify studentgender char(2); –‘modify’ 附近有语法错误。 修改列名称以及其数据类型(用change) 错误的命...
数据库系统原理——实验三
Capricorn_wow的博客
11-11 1万+
实验三 数据库完整性、安全性实现 一、实验目的 使学生加深对数据库安全性和完整性的理解，并掌握 SQL Server 中有关用户、角 色及操作权限的管理方法，学会创建和使用规则、缺省和触发器以及存储过程。 二、实验要求 通过实验对数据进行完整性控制、安全性维护。 三、实验步骤 1、 开始→程序→Microsoft SQL Server→SQL Server Management Stdio。 2、 在“连接到服务器”对话框中，选择“Windows 身份验证”，点击“连接”，进
[轉]MYSQL的常用命令和增删改查语句和数据类型
weixin_33801856的博客
07-28 269
连接命令：mysql -h[主机地址] -u[用户名] -p[用户密码]   创建数据库：create database [库名]   显示所有数据库: show databases;   打开数据库:use [库名]   当前选择的库状态:SELECT DATABASE();   创建数据表:CREATE TABLE [表名]([字段名] [字段类型]([字段要求]) [字段参数]...
数据库上机课实验题
zzsaixuexi的博客
09-30 1万+
操作数据库 简单查询 操作表 复杂查询 视图与索引 数据完整性实验 存储过程 存储过程 触发器 游标实验
Oracle 练习
℡瀦娃娃ゞɑ
06-23 771
实验内容：一、 用SQL语句建表：Student,sc,course学生表：Student(Sno,Sname,Ssex,Sdept)        Student由学号(Sno)、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，其中Sno为主码，sage取值范围为(18~40),Ssex 默认为男。   create table student(sn...
建立一个数据库
热门推荐
一个小白的求知路
05-16 8万+
建立一个数据库MySQL的基础命令：        打开MySQL：net start mysql      关闭MySQL：net stop mysql        登陆MySQL：mysql -u root -p   然后根据提示输入密码                显示现有数据库：                show databases;                创建数据库： ...
mysql练习-数据定义
RainforCSDN的博客
07-10 3275
首先需要知道数据定义的基本语法（省略建库操作） #定义基本表 CREATE TABLE 表名 ( 列名 数据类型 [列级约束]， 列名 数据类型 [列级约束], …… [,表级约束] ) [CHARSET=字符集]; 例如： CREATE TABLE student ( sno char(7) PRIMARY KEY, sname char(10) NOT NULL , ssex char(2) , sage tinyint UNSI
实验三 基本表的定义、删除与修改
ao_mike的博客
06-02 3531
实验三 基本表的定义、删除与修改 实验目的：熟练掌握基本表的定义、删除与修改，索引的建立与删除的方法，为后继学习作准备。 实验要求： 了解并掌握SQL查询分析器及企业管理器的使用; 掌握基本表的定义、删除与修改。 掌握建立索引的二种方法，即在基本表中建立和用命令方式建立。; 掌握删除索引的方法。 实验设备：装有SQL SERVER 2005的电脑 实验时数：2课时 类 别：验证 实验步骤： 启动Microsoft SQL Server 2005； 进入Mi...
"SQL Server2000数据库管理系统下公司管理数据库的创建与修改教学
该项目涉及到公司管理数据库的创建和修改，所使用的文档共有69张。目标是通过使用企业管理器和Transact-SQL语句来创建数据库，并设置数据库属性。工作任务包括以下几个方面： 1. 创建名为companyinfo的公司管理...

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值