数据集字符编码设置（模糊查找）

最新推荐文章于 2025-04-11 10:54:14 发布

原创最新推荐文章于 2025-04-11 10:54:14 发布 · 357 阅读

0 ·

CC 4.0 BY-SA版权

学习笔记专栏收录该内容

98 篇文章

订阅专栏

本文通过一系列的终端命令展示了在 macOS 上进行 MySQL 数据库操作的过程，包括进入 MySQL 目录、创建与删除数据库、插入与查询数据、处理权限错误以及设置字符集。过程中遇到了权限拒绝、SQL 语法错误和外键约束等问题，并最终成功解决了这些问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Last login: Tue Jan 8 15:34:02 on console
bnz@MacBook-Pro-3 ⮀ ~ ⮀ ⭠ master± ⮀ cd usr
cd: no such file or directory: usr
✘ bnz@MacBook-Pro-3 ⮀ ~ ⮀ ⭠ master± ⮀ cd local
cd: no such file or directory: local
✘ bnz@MacBook-Pro-3 ⮀ ~ ⮀ ⭠ master± ⮀ cd …/
bnz@MacBook-Pro-3 ⮀ /Users ⮀ ls
Shared baonianzhong bnz
bnz@MacBook-Pro-3 ⮀ /Users ⮀ cd …/
bnz@MacBook-Pro-3 ⮀ / ⮀ ls
Applications bin net
Library cores private
Network dev sbin
System etc tmp
Users home usr
Volumes installer.failurerequests var
bnz@MacBook-Pro-3 ⮀ / ⮀ cd sur
cd: no such file or directory: sur
✘ bnz@MacBook-Pro-3 ⮀ / ⮀ cd usr
bnz@MacBook-Pro-3 ⮀ /usr ⮀ ls
bin lib local share
include libexec sbin standalone
bnz@MacBook-Pro-3 ⮀ /usr ⮀ cd local
bnz@MacBook-Pro-3 ⮀ /usr/local ⮀ ls
Cellar mysql
Frameworks mysql-5.7.17-macos10.12-x86_64
Homebrew opt
bin remotedesktop
etc sbin
include share
lib var
bnz@MacBook-Pro-3 ⮀ /usr/local ⮀ cd mysql
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql ⮀ ls
COPYING bin docs lib share
README data include man support-files
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql ⮀ cd bin
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ ls
innochecksum mysqlbinlog
lz4_decompress mysqlcheck
my_print_defaults mysqld
myisam_ftdump mysqld-debug
myisamchk mysqld_multi
myisamlog mysqld_safe
myisampack mysqldump
mysql mysqldumpslow
mysql_client_test_embedded mysqlimport
mysql_config mysqlpump
mysql_config_editor mysqlshow
mysql_embedded mysqlslap
mysql_install_db mysqltest_embedded
mysql_plugin mysqlxtest
mysql_secure_installation perror
mysql_ssl_rsa_setup replace
mysql_tzinfo_to_sql resolve_stack_dump
mysql_upgrade resolveip
mysqladmin zlib_decompress
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql -uroot -p123456
zsh: command not found: mysql
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql -u root -p 123456
zsh: command not found: mysql
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql
zsh: command not found: mysql
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ cd mysql
cd: not a directory: mysql
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ alias mysql=/usr/local/mysql/bin/mysql
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ alias mysqladmin=/usr/local/mysql/bin/mysqladmin
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql -u root -p 123456
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql -u root -p 123456
Enter password:
ERROR 1049 (42000): Unknown database ‘123456’
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql -u root -p 123456
Enter password:
ERROR 1045 (28000): Access denied for user ‘root’@‘localhost’ (using password: YES)
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.7.17 MySQL Community Server (GPL)

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 love;
Query OK, 1 row affected (0.00 sec)

mysql> drop database love;
Query OK, 0 rows affected (0.00 sec)

mysql> use database test;
ERROR 1049 (42000): Unknown database ‘database’
mysql> use test;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> use Exchange;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
±-------------------+
| Tables_in_exchange |
±-------------------+
| exchange |
±-------------------+
1 row in set (0.00 sec)

mysql> select * from exchange;
Empty set (0.00 sec)

mysql> show databases；
-> show databases;
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 ‘databases；
show databases’ at line 1
mysql> exit
Bye
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql --version
/usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.17, for macos10.12 (x86_64) using EditLine wrapper
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql
ERROR 1045 (28000): Access denied for user ‘bnz’@‘localhost’ (using password: NO)
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ alias mysql=/usr/local/mysql/bin/mysql
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ alias mysqladmin=usr/local/mysql/bin/mysqladmin
bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql
ERROR 1045 (28000): Access denied for user ‘bnz’@‘localhost’ (using password: NO)
✘ bnz@MacBook-Pro-3 ⮀ /usr/local/mysql/bin ⮀ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.7.17 MySQL Community Server (GPL)

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> use Exchange;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
±-------------------+
| Tables_in_exchange |
±-------------------+
| exchange |
±-------------------+
1 row in set (0.00 sec)

mysql> select database();
±-----------+
| database() |
±-----------+
| exchange |
±-----------+
1 row in set (0.00 sec)

mysql> show create table exchange;
±---------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±---------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| exchange | CREATE TABLE exchange (
id int(11) NOT NULL AUTO_INCREMENT,
abbrev varchar(32) NOT NULL,
name varchar(255) NOT NULL,
city varchar(255) DEFAULT NULL,
country varchar(255) DEFAULT NULL,
currency varchar(64) DEFAULT NULL,
timezone_offset datetime DEFAULT NULL,
last_updated_data datetime NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
±---------±---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show table;
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 ‘’ at line 1
mysql> select name from exchange;
Empty set (0.00 sec)

mysql> show exchange;
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 ‘exchange’ at line 1
mysql> select
-> name
-> from
-> exchange
->
-> \c
mysql> select round(123.456)
-> select round(123.456,1);
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 ‘select round(123.456,1)’ at line 2
mysql> select rand();
±--------------------+
| rand() |
±--------------------+
| 0.24725514021446476 |
±--------------------+
1 row in set (0.00 sec)

mysql> select round(123.456)
-> select round(123.456);
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 ‘select round(123.456)’ at line 2
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| DailyPrice |
| mysql |
| performance_schema |
| sys |
| test |
±-------------------+
6 rows in set (0.00 sec)

mysql> use DailyPrice
Database changed
mysql> create table ‘exchange’(
-> ‘id’ int NOT NULL AOTO_INCREMENT,
-> ‘abbrev’ varchar(32) NOT NULL ,
-> ‘name’ varchar(255) NOT NULL,
-> ‘city’ varchar(255) NOTNULL,
-> ‘country’ varchar(255) NULL;
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 '‘exchange’(
‘id’ int NOT NULL AOTO_INCREMENT,
‘abbrev’ varchar(32) NOT NULL ,
‘n’ at line 1
mysql> create table ‘exchange’(
-> ‘id’ int NOT NULL AUTO_INCREMENT,
-> ‘abbrev’ varchar(32) NOT NULL,
-> ‘name’ varchar(255) NOT NULL,
-> ‘city’ varchar(255) NULL,
-> ‘country’ varchar(255) NULL,
-> ‘currency’ varchar(64) NULL,
-> ‘timezone_offset’ time NULL,
-> ‘created_date’ datetime NOT NULL,
-> ‘last_updated_date’ datetime NOT NULL,
-> PRIMARY KEY (‘id’)
-> )
-> ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
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 '‘exchange’(
‘id’ int NOT NULL AUTO_INCREMENT,
‘abbrev’ varchar(32) NOT NULL,
‘na’ at line 1
mysql> use liu
Database changed
mysql> create table ‘exchange’(
-> ‘id’ int NOT NILL AUTO_INCREMENT,
-> ‘abbrev’ varchar(32) NOT NULL,
-> ‘name’ varchar(255) NOT NULL,
-> ‘city’ varchar(255) NULL,
-> ‘country’ varchar(255) NULL,
-> ‘currency’ varchar(64) NULL,
-> ‘timezone_offset’ time NULL,
-> ‘created_date’ datetime NOT NULL,
-> ‘last_updated_date’ datetime NOT NULL,
-> PRIMARY KEY (‘id’)
-> )ENGINE=InnoBD AUTO_INCREMENT=1 DEFAULT CHARTSET=utf8;
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 '‘exchange’(
‘id’ int NOT NILL AUTO_INCREMENT,
‘abbrev’ varchar(32) NOT NULL,
‘na’ at line 1
mysql> create table ‘exchange’(
-> -> ‘id’ int NOT NILL AUTO_INCREMENT,
-> -> ‘abbrev’ varchar(32) NOT NULL,
-> -> ‘name’ varchar(255) NOT NULL,
-> -> ‘city’ varchar(255) NULL,
-> -> ‘country’ varchar(255) NULL,
-> -> ‘currency’ varchar(64) NULL,
-> -> ‘timezone_offset’ time NULL,
-> -> ‘created_date’ datetime NOT NULL,
-> -> ‘last_updated_date’ datetime NOT NULL,
-> -> PRIMARY KEY (‘id’)
-> \c
mysql> create table ‘exchange’(
-> -> show variables like ‘character%’;
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 ‘‘exchange’(
show variables lables like ‘character%’’ at line 1
mysql> show variables lke “character%”;
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 ‘lke “character%”’ at line 1
mysql> set charater_set_database=utf8;
ERROR 1193 (HY000): Unknown system variable ‘charater_set_database’
mysql> set character_set_database=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> use liu
Database changed
mysql> show tables
-> show tables;
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 ‘show tables’ at line 2
mysql> use liu;
Database changed
mysql> show tables;
±--------------+
| Tables_in_liu |
±--------------+
| data_vendor |
| employee |
| symbols |
±--------------+
3 rows in set (0.00 sec)

mysql> select * from t_student;
Empty set (0.00 sec)

mysql> insert into t_student(no,name,sex,birthday,email) values(2,‘zhangsin’,‘M’,‘1990-03-12’,‘zhagnsan163.com’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student(no,name,sex,birthday,email) values(3,‘gsin’,‘M’,‘1990-03-12’,‘lnsan163.com’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student(no,name,sex,birthday,email) values(1,‘zhangsan’,‘M’,‘1994-12-11’,‘zhangsan123.com’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
±-----±---------±-----±--------------------±----------------+
| no | name | sex | birthday | email |
±-----±---------±-----±--------------------±----------------+
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 2 | zhangsin | M | 1990-03-12 00:00:00 | zhagnsan163.com |
| 3 | gsin | M | 1990-03-12 00:00:00 | lnsan163.com |
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 4 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 1 | zhangsan | M | 1994-12-11 00:00:00 | zhangsan123.com |
±-----±---------±-----±--------------------±----------------+
7 rows in set (0.00 sec)

mysql> insert into t_student(no,name,sex,birthday,email) values(5,‘lisi’,‘F’,‘1993-10-11’,‘liuqioaju@163.com’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
±-----±---------±-----±--------------------±------------------+
| no | name | sex | birthday | email |
±-----±---------±-----±--------------------±------------------+
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 2 | zhangsin | M | 1990-03-12 00:00:00 | zhagnsan163.com |
| 3 | gsin | M | 1990-03-12 00:00:00 | lnsan163.com |
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 4 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 1 | zhangsan | M | 1994-12-11 00:00:00 | zhangsan123.com |
| 5 | lisi | F | 1993-10-11 00:00:00 | liuqioaju@163.com |
±-----±---------±-----±--------------------±------------------+
8 rows in set (0.00 sec)

mysql> select * from ju
-> \c
mysql> select * from ju;
Empty set (0.00 sec)

mysql> select * from ju;
Empty set (0.00 sec)

mysql> insert into t_student values(6,‘wangwu’,‘F’,‘1980-12-11’,‘qiaoju@qq.com’);
Query OK, 1 row affected (0.01 sec)

mysql> select * from t_student;
±-----±---------±-----±--------------------±------------------+
| no | name | sex | birthday | email |
±-----±---------±-----±--------------------±------------------+
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 2 | zhangsin | M | 1990-03-12 00:00:00 | zhagnsan163.com |
| 3 | gsin | M | 1990-03-12 00:00:00 | lnsan163.com |
| 1 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 4 | zhangsan | M | 1990-12-12 00:00:00 | zhangsan163.com |
| 1 | zhangsan | M | 1994-12-11 00:00:00 | zhangsan123.com |
| 5 | lisi | F | 1993-10-11 00:00:00 | liuqioaju@163.com |
| 6 | wangwu | F | 1980-12-11 00:00:00 | qiaoju@qq.com |
±-----±---------±-----±--------------------±------------------+
9 rows in set (0.00 sec)

mysql> insert into t_student(no,name) values(7,“wangba”);
Query OK, 1 row affected (0.00 sec)

mysql> show create table t_student;
±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_student | CREATE TABLE t_student (
no int(10) DEFAULT NULL,
name varchar(32) DEFAULT NULL,
sex char(1) DEFAULT NULL,
birthday datetime DEFAULT NULL,
email varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
±----------±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set character_set_database=utf8;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_student(
-> no int(10),
-> name varchar(32),
-> sex char(1) default ‘m’);
Query OK, 0 rows affected (0.02 sec)

mysql> desc t_student;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| no | int(10) | YES | | NULL | |
| name | varchar(32) | YES | | NULL | |
| sex | char(1) | YES | | m | |
±------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

mysql> insert t_student(no,name,sex) values(1,‘liuq’,‘f’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
±-----±-----±-----+
| no | name | sex |
±-----±-----±-----+
| 1 | liuq | f |
±-----±-----±-----+
1 row in set (0.00 sec)

mysql> insert into t_student(no,name,sex) values(2,‘ju’,‘f’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student(no,name,sex) values(3,‘bao’,‘m’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
±-----±-----±-----+
| no | name | sex |
±-----±-----±-----+
| 1 | liuq | f |
| 2 | ju | f |
| 3 | bao | m |
±-----±-----±-----+
3 rows in set (0.00 sec)

mysql> source desktop:\test.sql
ERROR:
Failed to open file ‘desktop:\test.sql’, error: 2
mysql> source Desktop:\test.sql
ERROR:
Failed to open file ‘Desktop:\test.sql’, error: 2
mysql> select now(0;
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 ‘’ at line 1
mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2019-01-09 18:38:59 |
±--------------------+
1 row in set (0.00 sec)

mysql> create table t_organization(
-> code char(10),
-> name varchar(32),
-> createTime date);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_organization(code,name createTime) values(‘111’,‘business’,now());
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 ‘createTime) values(‘111’,‘business’,now())’ at line 1
mysql> show tables
-> \c
mysql> show tables;
±---------------+
| Tables_in_liu |
±---------------+
| data_vendor |
| employee |
| exchange |
| ju |
| t_organization |
| t_student |
±---------------+
6 rows in set (0.01 sec)

mysql> insert into d_organization(code,name,createTime) values(‘111’,‘business’,now());
ERROR 1146 (42S02): Table ‘liu.d_organization’ doesn’t exist
mysql> insert into d_organization(code,name,createTime) values(‘111’,‘business’,now());
ERROR 1146 (42S02): Table ‘liu.d_organization’ doesn’t exist
mysql> insert into d_organization(code,name,createTime) values(‘111’,‘business’,now());
ERROR 1146 (42S02): Table ‘liu.d_organization’ doesn’t exist
mysql> insert into t_organization(code,name,createTime) values(‘111’,‘business’,now());
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select now();
±--------------------+
| now() |
±--------------------+
| 2019-01-09 18:46:48 |
±--------------------+
1 row in set (0.00 sec)

mysql> create table orga as select * from t_organization;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> create table org as select node,name from orga;
ERROR 1054 (42S22): Unknown column ‘node’ in ‘field list’
mysql> create table org as select code,name from orga;
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0

mysql> select * from t_student;
±-----±-----±-----+
| no | name | sex |
±-----±-----±-----+
| 1 | liuq | f |
| 2 | ju | f |
| 3 | bao | m |
±-----±-----±-----+
3 rows in set (0.00 sec)

mysql> update t_student set name=‘wanger’,sex=‘m’ where no=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from t_student;
±-----±-------±-----+
| no | name | sex |
±-----±-------±-----+
| 1 | wanger | m |
| 2 | ju | f |
| 3 | bao | m |
±-----±-------±-----+
3 rows in set (0.01 sec)

mysql> delete from t_student where no=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_student;
±-----±-------±-----+
| no | name | sex |
±-----±-------±-----+
| 1 | wanger | m |
| 2 | ju | f |
±-----±-------±-----+
2 rows in set (0.00 sec)

mysql> select count() from t_student;
±---------+
| count() |
±---------+
| 2 |
±---------+
1 row in set (0.00 sec)

mysql> create table t_student(
-> id int not null,
-> name varchar(32) not null,
-> email varchar(64),
-> unique(name,email))
-> ;
ERROR 1050 (42S01): Table ‘t_student’ already exists
mysql> create table t_teacher(
-> id int not null,
-> name varchar(32) not null,
-> email varchar(128),
-> unique(name,email);
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 ‘’ at line 5
mysql> create table t_teacher(
-> id int not null,
-> name varchar(32) not null,
-> email varchar(128));
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists t_teacher
-> \c
mysql> drop table if exists t_teacher;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_teacher(
-> id int not null,
-> name varchar(32) not null,
-> email varchar(128),
-> unique(name,email);
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 ‘’ at line 5
mysql> create table t_teacher(
-> id int not null,
-> name varchar(32) not null,
-> email varchar(128),
-> unique(name,email));
Query OK, 0 rows affected (0.02 sec)

mysql> use information_schema;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> select constraint_name from table_constraints where table_name=“student”;
Empty set (0.00 sec)

mysql> select constraint_name from table_constraints where table_name=“t_student”;
Empty set (0.00 sec)

mysql> use liu;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

mysql> drop table if exists t_user;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t_user(
-> id int(10),
-> name varchar(32) not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_user(id,name) values(1,‘jack’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name) values(2,‘lilei’);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_user(id,name) values(3,‘hanmeimei’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(id,name) values(4,‘xiaohua’);
Query OK, 1 row affected (0.00 sec)

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_user(
-> id int(10) primary key,
-> name varchar(32));
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_user(
-> id int(10),
-> name varchar(32),
-> primary key (id));
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_user(
-> id int(10),
-> name varchar(32),
-> constraint t_user_id_pk primary key(id));
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t_user(
-> id int(10),
-> name varchar(32),
-> primary key(id ,name));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t_user;
±------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±------------±-----±----±--------±------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(32) | NO | PRI | NULL | |
±------±------------±-----±----±--------±------+
2 rows in set (0.00 sec)

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_user(
-> id int(10);
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 ‘’ at line 2
mysql> create table t_user(
-> id int(10),
-> name varchar(128),
-> constraint t_user_id_name_pk primary key(id ,name));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t_user;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| id | int(10) | NO | PRI | NULL | |
| name | varchar(128) | NO | PRI | NULL | |
±------±-------------±-----±----±--------±------+
2 rows in set (0.00 sec)

mysql> drop table if exists t_user;
Query OK, 0 rows affected (0.01 sec)

mysql> create table t_user(
-> id int(10) primary key auto_increment,
-> name varchar(32));
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t_user(name) values(‘jack’);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_user(name) values(‘jack’);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
±—±-----+
| id | name |
±—±-----+
| 1 | jack |
| 2 | jack |
| 3 | jack |
| 4 | jack |
| 5 | jack |
| 6 | jack |
±—±-----+
6 rows in set (0.01 sec)

mysql> delet from t_user where id=3;
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 ‘delet from t_user where id=3’ at line 1
mysql> delete from t_user where id=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from t_user;
±—±-----+
| id | name |
±—±-----+
| 1 | jack |
| 2 | jack |
| 4 | jack |
| 5 | jack |
| 6 | jack |
±—±-----+
5 rows in set (0.00 sec)

mysql> drop table if exists t_student;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table if exits t_class;
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 ‘exits t_class’ at line 1
mysql> drop table if exists t_class;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table t_class(
-> cno int(3) primary key,
-> cname varchar(128) not null unique);
Query OK, 0 rows affected (0.02 sec)

mysql> create table t_student(
-> sno int(3) primary key,
-> sname varchar(32) not null,
-> classno int(3),
-> constraint t_student_classno_fk key(classno) references t_class(cno)?
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 ‘key(classno) references t_class(cno)’ at line 5
-> create table t_student(
-> cno int(3) primary key,
-> \c
mysql> create table t_student(
-> sno int(3) primary key,
-> sname varchar(32) not null,
-> classno int(3),
-> constraint t_student_classno_fk key(classno) references t_class(cno));
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 ‘key(classno) references t_class(cno))’ at line 5
mysql> create table t_student(
-> sno int(3) primary key,
-> sname varchar(32) not null,
-> classno int(3),
-> constraint t_student_classno_fk foreign key(classno) references t_class(cno));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t_class;
±------±-------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±------±-------------±-----±----±--------±------+
| cno | int(3) | NO | PRI | NULL | |
| cname | varchar(128) | NO | UNI | NULL | |
±------±-------------±-----±----±--------±------+
2 rows in set (0.00 sec)

mysql> desc_t_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 ‘desc_t_student’ at line 1
mysql> desc t_student;
±--------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------±------------±-----±----±--------±------+
| sno | int(3) | NO | PRI | NULL | |
| sname | varchar(32) | NO | | NULL | |
| classno | int(3) | YES | MUL | NULL | |
±--------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

mysql> insert into t_class(cno,cname) values(100,“Grade3 class1”);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_class(cno,cname) values(200,“Grade3 class 2”);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_class(cno,cname) values(300,“Grade3 class 3”);
Query OK, 1 row affected (0.00 sec)

mysql> desc t_student;
±--------±------------±-----±----±--------±------+
| Field | Type | Null | Key | Default | Extra |
±--------±------------±-----±----±--------±------+
| sno | int(3) | NO | PRI | NULL | |
| sname | varchar(32) | NO | | NULL | |
| classno | int(3) | YES | MUL | NULL | |
±--------±------------±-----±----±--------±------+
3 rows in set (0.00 sec)

mysql> insert into t_student(
-> \c
mysql> insert into t_student(sno,sname,classno) values(1,‘lucy’,100);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student(sno,sname,classno) values(2,'lilei’100);
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 ‘100)’ at line 1
mysql> insert into t_student(sno,sname,classno) values(3,‘king’,200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student(sno,sname,classno) values(4,‘Hanmeime’,200);
Query OK, 1 row affected (0.00 sec)

mysql> insert into t_student(sno,sname,classno) values(5,‘wangwu’,300);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_student(sno,sname,classno) values(6,‘lijun’,300);
Query OK, 1 row affected (0.01 sec)

mysql> insert into t_student(sno,sname,classno) values(9,‘lucy’,600);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (liu.t_student, CONSTRAINT t_student_classno_fk FOREIGN KEY (classno) REFERENCES t_class (cno))
mysql> select * from t_student;
±----±---------±--------+
| sno | sname | classno |
±----±---------±--------+
| 1 | lucy | 100 |
| 3 | king | 200 |
| 4 | Hanmeime | 200 |
| 5 | wangwu | 300 |
| 6 | lijun | 300 |
±----±---------±--------+
5 rows in set (0.00 sec)