MySQL基础操作 - 创建 - 《数据分析_学习笔记》

创建和删除数据库
创建表
添加主键和外键约束
- ALTER TABLE 表名 ADD CONSTRAINT PRIMARY KEY (字段名);
- ALTER TABLE 表名 ADD CONSTRAINT FOREIGN KEY (字段名) REFERENCES 表名(字段名);
插入数据
- INSERT INTO 表名(列名1,列名2,……,列名n) VALUES(列值1,列值2,……,列值n);

创建和删除数据库

创建数据库—create database

password: c1663884773

C:\Users\chenh>mysql -u root -p
Enter password: ***********
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.17 MySQL Community Server - GPL
Copyright (c) 2000, 2019, 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> create database school;
Query OK, 1 row affected (0.01 sec)
mysql> create database school;
ERROR 1007 (HY000): Can't create database 'school'; database exists
mysql> create database if not exists school;
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Note  | 1007 | Can't create database 'school'; database exists |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

显示所有数据库-show databases

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

选择要使用的数据库-use database

mysql> show tables form mysql;
ERROR 1046 (3D000): No database selected
mysql> use mysql;
Database changed
mysql> show tables from mysql;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
| component                 |
| db                        |
| default_roles             |
| engine_cost               |
| func                      |
| general_log               |
| global_grants             |
| gtid_executed             |
| help_category             |
| help_keyword              |
| help_relation             |
| help_topic                |
| innodb_index_stats        |
| innodb_table_stats        |
| password_history          |
| plugin                    |
| procs_priv                |
| proxies_priv              |
| role_edges                |
| server_cost               |
| servers                   |
| slave_master_info         |
| slave_relay_log_info      |
| slave_worker_info         |
| slow_log                  |
| tables_priv               |
| time_zone                 |
| time_zone_leap_second     |
| time_zone_name            |
| time_zone_transition      |
| time_zone_transition_type |
| user                      |
+---------------------------+
33 rows in set (0.01 sec)
mysql> show columns from users from mysql;
ERROR 1146 (42S02): Table 'mysql.users' doesn't exist
mysql> show columns from user from mysql;
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field                    | Type                              | Null | Key | Default               | Extra |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host                     | char(255)                         | NO   | PRI |                       |       |
| User                     | char(32)                          | NO   | PRI |                       |       |
| Select_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Insert_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Update_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Delete_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Create_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Reload_priv              | enum('N','Y')                     | NO   |     | N                     |       |
| Shutdown_priv            | enum('N','Y')                     | NO   |     | N                     |       |
| Process_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| File_priv                | enum('N','Y')                     | NO   |     | N                     |       |
| Grant_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| References_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Index_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Show_db_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Super_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tmp_table_priv    | enum('N','Y')                     | NO   |     | N                     |       |
| Lock_tables_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Execute_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_slave_priv          | enum('N','Y')                     | NO   |     | N                     |       |
| Repl_client_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Create_view_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Show_view_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_routine_priv      | enum('N','Y')                     | NO   |     | N                     |       |
| Alter_routine_priv       | enum('N','Y')                     | NO   |     | N                     |       |
| Create_user_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Event_priv               | enum('N','Y')                     | NO   |     | N                     |       |
| Trigger_priv             | enum('N','Y')                     | NO   |     | N                     |       |
| Create_tablespace_priv   | enum('N','Y')                     | NO   |     | N                     |       |
| ssl_type                 | enum('','ANY','X509','SPECIFIED') | NO   |     |                       |       |
| ssl_cipher               | blob                              | NO   |     | NULL                  |       |
| x509_issuer              | blob                              | NO   |     | NULL                  |       |
| x509_subject             | blob                              | NO   |     | NULL                  |       |
| max_questions            | int(11) unsigned                  | NO   |     | 0                     |       |
| max_updates              | int(11) unsigned                  | NO   |     | 0                     |       |
| max_connections          | int(11) unsigned                  | NO   |     | 0                     |       |
| max_user_connections     | int(11) unsigned                  | NO   |     | 0                     |       |
| plugin                   | char(64)                          | NO   |     | caching_sha2_password |       |
| authentication_string    | text                              | YES  |     | NULL                  |       |
| password_expired         | enum('N','Y')                     | NO   |     | N                     |       |
| password_last_changed    | timestamp                         | YES  |     | NULL                  |       |
| password_lifetime        | smallint(5) unsigned              | YES  |     | NULL                  |       |
| account_locked           | enum('N','Y')                     | NO   |     | N                     |       |
| Create_role_priv         | enum('N','Y')                     | NO   |     | N                     |       |
| Drop_role_priv           | enum('N','Y')                     | NO   |     | N                     |       |
| Password_reuse_history   | smallint(5) unsigned              | YES  |     | NULL                  |       |
| Password_reuse_time      | smallint(5) unsigned              | YES  |     | NULL                  |       |
| Password_require_current | enum('N','Y')                     | YES  |     | NULL                  |       |
| User_attributes          | json                              | YES  |     | NULL                  |       |
+--------------------------+-----------------------------------+------+-----+-----------------------+-------+
51 rows in set (0.01 sec)
mysql> show errors;
Empty set (0.00 sec)
mysql> create database school;
ERROR 1007 (HY000): Can't create database 'school'; database exists
mysql> show errors;
+-------+------+-------------------------------------------------+
| Level | Code | Message                                         |
+-------+------+-------------------------------------------------+
| Error | 1007 | Can't create database 'school'; database exists |
+-------+------+-------------------------------------------------+
1 row in set (0.00 sec)

删除数据库-drop database

mysql> creare database abc;
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 'creare database abc' at line 1
mysql> create database abc;
Query OK, 1 row affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| abc                |
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
7 rows in set (0.00 sec)
mysql> drop database abc;
Query OK, 0 rows affected (0.01 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sakila             |
| sys                |
| world              |
+--------------------+
6 rows in set (0.00 sec)

创建表

要求创建一个学生选课数据库，里面包含四张表：
1. 学生表，5个字段分别为学号，姓名，性别，出生年月，所在班级
2. 教师表，6个字段分别为教师编号，姓名，性别，出生年月，职称，部门
3. 课程表，3个字段分别为课程号，课程名，授课教师的教师编号
4. 成绩表，3个字段为学号，课程号，成绩

设计学生表（student）的结构

字段	列名	数据类型	可否为空	约束
学号	sno	varchar(20)	not null	主键
姓名	sname	varchar(20)	not null
性别	ssex	varchar(20)	not null	default’男’
出生年月	sbirthday	datetime
所在班级	sclass	varchar(20)

创建学生表（student）

mysql> create table student(
    -> sno varchar(20) not null,
    -> sname varchar(20) not null,
    -> ssex varchar(20) not null default'男',
    -> sbirthday datetime,
    -> sclass varchar(20));
Query OK, 0 rows affected (0.11 sec)
mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| student          |
+------------------+
1 row in set (0.00 sec)

设计教师表（teacher）的结构

字段	列名	数据类型	可否为空	约束
教师编号	tno	varchar(20)	not null	主键
姓名	tname	varchar(20)	not null
性别	tsex	varchar(20)	not null	default’男’
出生年月	tbirthday	datetime
职称	pro	varchar(20)
部门	depart	varchar(20)	not null

创建教师表（teacher）

mysql> create table teacher(
    -> tno varchar(20) not null,
    -> tname varchar(20) not null,
    -> tsex varchar(20) not null default'男',
    -> tbirthday datetime,
    -> pro varchar(20),
    -> depart varchar(20) not null);
Query OK, 0 rows affected (0.06 sec)
mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| student          |
| teacher          |
+------------------+
2 rows in set (0.00 sec)

设计课程表（course）的结构

字段	列名	数据类型	可否为空	约束
课程号	cno	varchar(20)	not null	主键
课程名	cname	varchar(20)	not null
教师编号	tno	varchar(20)	not null	外键

创建课程表（course）

mysql> use school;
Database changed
mysql> create table course(
    -> cno varchar(20) not null,
    -> cname varchar(20) not null,
    -> tno varchar(20) not null);
Query OK, 0 rows affected (0.07 sec)
mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| student          |
| teacher          |
+------------------+
3 rows in set (0.01 sec)

设计成绩表（score）的结构

字段	列名	数据类型	可否为空	约束
学号	sno	varchar(20)	not null	外键
课程号	cno	varchar(20)	not null	外键
成绩	degree	decimal(4,1)	not null

创建成绩表（score）

mysql> create table score(
    -> sno varchar(20) not null,
    -> cno varchar(20) not null,
    -> degree decimal(4,1) not null);
Query OK, 0 rows affected (0.04 sec)
mysql> show tables from school;
+------------------+
| Tables_in_school |
+------------------+
| course           |
| score            |
| student          |
| teacher          |
+------------------+
4 rows in set (0.00 sec)

添加主键和外键约束

建立基本表间的关联，从而实现连接查询或多表查询

ALTER TABLE 表名 ADD CONSTRAINT PRIMARY KEY (字段名);

对学生表（student）添加主键约束（primary key），即设置sno为学生表的主键
2. 对教师表（teacher）添加主键约束，即设置tno为教师表的主键
3. 对课程表（course）添加主键约束，即设置cno为课程表的主键
4. 对成绩表（score）添加主键约束，即设置sno，cno为成绩表的主键

ALTER TABLE 表名 ADD CONSTRAINT FOREIGN KEY (字段名) REFERENCES 表名(字段名);

对课程表（course）添加外键约束（foreign key），即设置tno为课程表的外键，对应字段是教师表（teacher）的tno
2. 对成绩表（score）添加外键约束，即设置sno为成绩表的外键，对应字段是学生表（student）的sno
3. 对成绩表（score）添加外键约束，即设置cno为成绩表的外键，对应字段是课程表（course）的cno

mysql> alter table student add constraint primary key(sno);
Query OK, 0 rows affected (0.07 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table teacher
    -> add constraint primary key(tno);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table course add constraint primary key(cno);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table course add constraint foreign key(tno) references teacher(tno);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table score add constraint primary key(sno,cno);
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table score add constraint foreign key(sno) references student(sno);
Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> alter table score add constraint foreign key(cno) references course(cno);
Query OK, 0 rows affected (0.12 sec)
Records: 0  Duplicates: 0  Warnings: 0

插入数据

INSERT INTO 表名(列名1,列名2,……,列名n) VALUES(列值1,列值2,……,列值n);

mysql> use school;
Database changed
mysql> insert into student(sno,sname,ssex,sbirthday,sclass)
    -> values(108,'曾华','男','1977-09-01',95033);
Query OK, 1 row affected (0.03 sec)
mysql> insert into student(sno,sname,ssex,sbirthday,sclass)
    -> values(105,'匡明','男','1997-10-02',95031);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(sno,sname,ssex,sbirthday,sclass)
    -> values(107,'王丽','女','1976-01-23',95033);
Query OK, 1 row affected (0.00 sec)
mysql> insert into student(sno,sname,ssex,sbirthday,sclass)
    -> values(101,'李军','男','1976-02-20',95033);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(sno,sname,ssex,sbirthday,sclass)
    -> values(109,'王芳','女','1975-02-10',95031);
Query OK, 1 row affected (0.01 sec)
mysql> insert into student(sno,sname,ssex,sbirthday,sclass)
    -> values(103,'陆君','男','1974-06-03',95031);
Query OK, 1 row affected (0.01 sec)
mysql> select * from student;
+-----+-------+------+---------------------+--------+
| sno | sname | ssex | sbirthday           | sclass |
+-----+-------+------+---------------------+--------+
| 101 | 李军  | 男   | 1976-02-20 00:00:00 | 95033  |
| 103 | 陆君  | 男   | 1974-06-03 00:00:00 | 95031  |
| 105 | 匡明  | 男   | 1997-10-02 00:00:00 | 95031  |
| 107 | 王丽  | 女   | 1976-01-23 00:00:00 | 95033  |
| 108 | 曾华  | 男   | 1977-09-01 00:00:00 | 95033  |
| 109 | 王芳  | 女   | 1975-02-10 00:00:00 | 95031  |
+-----+-------+------+---------------------+--------+
6 rows in set (0.01 sec)
mysql> insert into teacher(tno,tname,tsex,tbirthday,pro,depart)
    -> values(804,'李诚','男','1958-12-02','副教授','计算机系');
Query OK, 1 row affected (0.01 sec)
    -> values(856,'张旭','男','1969-03-12','讲师','电子工程系');
Query OK, 1 row affected (0.01 sec)
mysql> insert into teacher(tno,tname,tsex,tbirthday,pro,depart)
    -> values(825,'王萍','女','1972-05-05','助教','计算机系');
Query OK, 1 row affected (0.01 sec)
mysql> insert into teacher(tno,tname,tsex,tbirthday,pro,depart)
    -> values(831,'刘冰','女','1977-08-14','助教','电子工程系');
Query OK, 1 row affected (0.01 sec)
mysql> select * from teacher;
+-----+-------+------+---------------------+--------+------------+
| tno | tname | tsex | tbirthday           | pro    | depart     |
+-----+-------+------+---------------------+--------+------------+
| 804 | 李诚  | 男   | 1958-12-02 00:00:00 | 副教授 | 计算机系   |
| 825 | 王萍  | 女   | 1972-05-05 00:00:00 | 助教   | 计算机系   |
| 831 | 刘冰  | 女   | 1977-08-14 00:00:00 | 助教   | 电子工程系 |
| 856 | 张旭  | 男   | 1969-03-12 00:00:00 | 讲师   | 电子工程系 |
+-----+-------+------+---------------------+--------+------------+
4 rows in set (0.00 sec)
mysql> insert into course(cno,cname,tno)
    -> values('3-105','计算机导论',825);
Query OK, 1 row affected (0.01 sec)
mysql> insert into course(cno,cname,tno)
    -> values('3-245','操作系统',804);
Query OK, 1 row affected (0.00 sec)
mysql> insert into course(cno,cname,tno)
    -> values('6-166','数据电路',856);
Query OK, 1 row affected (0.00 sec)
mysql> insert into course(cno,cname,tno)
    -> values('9-888','高等数学',831);
Query OK, 1 row affected (0.02 sec)
mysql> select * from course;
+-------+------------+-----+
| cno   | cname      | tno |
+-------+------------+-----+
| 3-105 | 计算机导论 | 825 |
| 3-245 | 操作系统   | 804 |
| 6-166 | 数据电路   | 856 |
| 9-888 | 高等数学   | 831 |
+-------+------------+-----+
4 rows in set (0.00 sec)
mysql> insert into score(sno,cno,degree)
    -> values(103,'3-245',86);
Query OK, 1 row affected (0.01 sec)
mysql> insert into score(sno,cno,degree)
    -> values(105,'3-245',75);
Query OK, 1 row affected (0.01 sec)
mysql> insert into score(sno,cno,degree)
    -> values(109,'3-245',68);
Query OK, 1 row affected (0.01 sec)
mysql> insert into score(sno,cno,degree)
    -> values(103,'3-105',92);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score(sno,cno,degree)
    -> values(105,'3-105',88);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score(sno,cno,degree)
    -> values(109,'3-105',76);
Query OK, 1 row affected (0.01 sec)
mysql> insert into score(sno,cno,degree)
    -> values(101,'3-105',64);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score(sno,cno,degree)
    -> values(107,'3-105',91);
Query OK, 1 row affected (0.01 sec)
mysql> insert into score(sno,cno,degree)
    -> values(108,'3-105',78);
Query OK, 1 row affected (0.01 sec)
mysql> insert into score(sno,cno,degree)
    -> values(101,'6-166',85);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score(sno,cno,degree)
    -> values(107,'6-166',79);
Query OK, 1 row affected (0.00 sec)
mysql> insert into score(sno,cno,degree)
    -> values(108,'6-166',81);
Query OK, 1 row affected (0.00 sec)
mysql> select * from score;
+-----+-------+--------+
| sno | cno   | degree |
+-----+-------+--------+
| 101 | 3-105 |   64.0 |
| 101 | 6-166 |   85.0 |
| 103 | 3-105 |   92.0 |
| 103 | 3-245 |   86.0 |
| 105 | 3-105 |   88.0 |
| 105 | 3-245 |   75.0 |
| 107 | 3-105 |   91.0 |
| 107 | 6-166 |   79.0 |
| 108 | 3-105 |   78.0 |
| 108 | 6-166 |   81.0 |
| 109 | 3-105 |   76.0 |
| 109 | 3-245 |   68.0 |
+-----+-------+--------+
12 rows in set (0.00 sec)