初学mysql命令及注意事项创建数据库实现三表查询
1.查找mysql里面有多少数据库
show databases;
2.创建自己的数据库
create database student; student 为自定义数据库名
3.查看自己的数据库有没有创建出来
show databases;
4.定位到自己创建的数据库
use student; 建自己的表时必须定位到自己的数据库
5.查看当前的数据库里面有没有表格（数据表）
show tables;
6.开始创建表
create table user( user为自定义表明
列 列的类型 是否为空
)
7. 创建完表 开始给表里面添加数据
1.单行插入数据
insert into user (id,name,sex,age,tel,email,address)
values
(1,"张三",0,18,"13767677867","[email protected]","西安市碑林区")；
2.多行插入
insert into user (id,name,sex,age,tel,email,address)
values
(1,"张三",0,18,"13767677867","[email protected]","西安市碑林区")，逗号隔开
(2,"张三",0,18,"13767677867","[email protected]","西安市碑林区")，
(3,"张三",0,18,"13767677867","[email protected]","西安市碑林区");
8.查当前表里面有没有数据
select * from user; 无条件查询表中全部数据
select * from user where id=2; 对表中所有数据条件查询： 条件
select user.name from user where id=2; 对表中所有数据条件查询user.name为表中某一条数据
9.数据表的增删改查
查： select * from user;
删：delete from user; 指的是删除表里面的所有数据
delete from user where id=2 根据条件删除
改：update user set name="马六"; 没有条件 修改整个数据表里面的整个name列
根据条件来修改：
update user set name="王尼" where id=1;
增：
insert into user (id,name,sex,age,tel,email,address)
values
(1,"张三",0,18,"13767677867","[email protected]","西安市碑林区")，
(2,"张三",0,18,"13767677867","[email protected]","西安市碑林区")，
(3,"张三",0,18,"13767677867","[email protected]","西安市碑林区");
同理还有单行插入
10. 给表添加主键
一种是在创建表的时候添加 id int （primary key）
一种是在表外添加Alter table score add primary key(id); 表外添加；
11.添加外键：
一种是在创建表的时候添加
Id int; //先要有建
foreign key(id) references user(id);
一种是在表外添加 alter table score add foreign key(id) references user(id);
12.添加自增列
auto_increment必须要求该列是主键(或别的键)
13.删除的数据的方法
删除表的语法：drop table name；
删除库的语法：
删除主键 alter table score drop primary key;
删除外键： alter table score drop foreign key(id);
//实例
mysql> create table score(
-> id int primary key, //成绩表的主键添加方式1创建的时候加
-> chinese varchar(20) not null,
-> english varchar(20) not null,
-> math varchar(20) not null,
-> history varchar(20) not null);
Query OK, 0 rows affected (0.09 sec)
结果
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| score |
| user |
+-------------------+
2 rows in set (0.00 sec)
mysql> drop table score; //删除用方式1添加主键的成绩表
Query OK, 0 rows affected (0.02 sec)
结果
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| user |
+-------------------+
1 row in set (0.00 sec)
mysql> create table score(
-> id int, //成绩表主键的添加方式2，
-> chinese varchar(20) not null,
-> english varchar(20) not null,
-> math varchar(20) not null,
-> history varchar(20) not null);
Query OK, 0 rows affected (0.05 sec)
mysql> alter table score add primary key(id); //表外添加
Query OK, 0 rows affected (0.16 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> alter table score drop primary key; //删除主键
Query OK, 0 rows affected (0.15 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create table score(
-> id int,;
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
//三表结合查询步骤
//1创建的用户表
mysql> select * from user;
+----+-----------+-----+-----+-------------+--------------+--------------------+
| id | name | sex | age | tel | email | address |
+----+-----------+-----+-----+-------------+--------------+--------------------+
| 1 | 王麻子 | 0 | 18 | 13732132131 | [email protected] | 西安市莲湖区 |
| 3 | 马六 | 0 | 18 | 13732132131 | [email protected] | 西安市莲湖区 |
+----+-----------+-----+-----+-------------+--------------+--------------------+
2 rows in set (0.01 sec)
2给用户表添加主键
mysql> alter table user add primary key(id);
Query OK, 0 rows affected (0.12 sec)
Records: 0 Duplicates: 0 Warnings: 0
//3创建第一个成绩表
mysql> create table score(
-> id int,
-> foreign key(id) references user(id), //成绩表是用户标的外键
-> chinese varchar(20) not null,
-> english varchar(20) not null,
-> math varchar(20) not null,
-> history varchar(20) not null);
Query OK, 0 rows affected (0.04 sec)
结果
mysql> show tables;
+-------------------+
| Tables_in_student |
+-------------------+
| score |
| user |
+-------------------+
2 rows in set (0.01 sec)
mysql> insert into user(id,name,sex,age,tel,email,address)
-> values
-> (2,"老李",1,18,"13878738728","[email protected]","咸阳武功");
Query OK, 1 row affected (0.01 sec)
mysql> insert into user(id,name,sex,age,tel,email,address)
-> values
-> (2,"老李",1,18,"13878738728","[email protected]","咸阳武功");
ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY' //主键只能是独一无二的，不能存在重复的数据
结果
mysql> select * from user;
+----+-----------+-----+-----+-------------+--------------+--------------------+
| id | name | sex | age | tel | email | address |
+----+-----------+-----+-----+-------------+--------------+--------------------+
| 1 | 王麻子 | 0 | 18 | 13732132131 | [email protected] | 西安市莲湖区 |
| 2 | 老李 | 1 | 18 | 13878738728 | [email protected] | 咸阳武功 |
| 3 | 马六 | 0 | 18 | 13732132131 | [email protected] | 西安市莲湖区 |
+----+-----------+-----+-----+-------------+--------------+--------------------+
3 rows in set (0.00 sec)
mysql> insert into score(id,chinese,english,math,history) //创建的成绩表添加数据
-> values
-> (4,"50","12","147","2"); //外键添加的数据必须是主键中存在的所以错误
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint f
ails (`student`.`score`, CONSTRAINT `score_ibfk_1` FOREIGN KEY (`id`) REFERENCES
`user` (`id`))
mysql> insert into score(id,chinese,english,math,history)
-> values
-> (1,"50","12","147","2"); //外键添加的数据必须是主键中存在的
Query OK, 1 row affected (0.01 sec)
mysql> insert into score(id,chinese,english,math,history)
-> values
-> (2,"99","150","147","40");
Query OK, 1 row affected (0.08 sec)
mysql> insert into score(id,chinese,english,math,history)
-> values
-> (3,"10","120","107","140");
Query OK, 1 row affected (0.00 sec)
mysql> select * from score;
+------+---------+---------+------+---------+
| id | chinese | english | math | history |
+------+---------+---------+------+---------+
| 1 | 50 | 12 | 147 | 2 |
| 2 | 99 | 150 | 147 | 40 |
| 3 | 10 | 120 | 107 | 140 |
+------+---------+---------+------+---------+
3 rows in set (0.01 sec)
//4创建第三个关联表教师表
//错误演示
mysql> create table teacher(
-> id int auto_increment, //自增列要求必须是主键或其他的键即必须是键所以此写法错误
-> userid int,
-> foreign key(userid) references user(id),
-> tname varchar(20));
ERROR 1075 (42000): Incorrect table definition; there can be only one auto colum
n and it must be defined as a key
//正确示范
mysql> create table teacher(
-> id int primary key auto_increment,所以加上一个键（可以是主键）
-> userid int,
-> foreign key(userid) references user(id), //教师表的教师名主键也是用户表的外键
-> tname varchar(20));
Query OK, 0 rows affected (0.04 sec)
//给创建的第三个教师表中创建数据
mysql> insert into teacher(id,userid,tname)
-> values
-> (1,1,"毛豆");
Query OK, 1 row affected (0.02 sec)
mysql> insert into teacher(id,userid,tname)
-> values
-> (1,2,"毛豆");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into teacher(id,userid,tname)
-> values
-> (1,2,"毛豆");
ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'
mysql> insert into teacher(userid,tname)
-> values
-> (2,"毛豆");
Query OK, 1 row affected (0.00 sec)
mysql> select * from teacher;
+----+--------+--------+
| id | userid | tname |
+----+--------+--------+
| 1 | 1 | 毛豆 |
| 2 | 2 | 毛豆 |
+----+--------+--------+
2 rows in set (0.00 sec)
mysql> insert into teacher(userid,tname)
-> values
-> (3,"老吴");
Query OK, 1 row affected (0.01 sec)
mysql> select * from teacher;
+----+--------+--------+
| id | userid | tname |
+----+--------+--------+
| 1 | 1 | 毛豆 |
| 2 | 2 | 毛豆 |
| 3 | 3 | 老吴 |
+----+--------+--------+
3 rows in set (0.00 sec)
//第四步有条件的查找数据
//表中的某条数据查找时用表明.数据查找
mysql> select user.id,user.name,teacher.tname,score.chinese,score.english from u
ser,teacher,score where user.id=score.id;
+----+-----------+--------+---------+---------+
| id | name | tname | chinese | english |
+----+-----------+--------+---------+---------+
| 1 | 王麻子 | 毛豆 | 50 | 12 |
| 2 | 老李 | 毛豆 | 99 | 150 |
| 3 | 马六 | 毛豆 | 10 | 120 |
| 1 | 王麻子 | 毛豆 | 50 | 12 |
| 2 | 老李 | 毛豆 | 99 | 150 |
| 3 | 马六 | 毛豆 | 10 | 120 |
| 1 | 王麻子 | 老吴 | 50 | 12 |
| 2 | 老李 | 老吴 | 99 | 150 |
| 3 | 马六 | 老吴 | 10 | 120 |
+----+-----------+--------+---------+---------+
9 rows in set (0.01 sec)
mysql> select user.id,user.name,teacher.tname,score.chinese,score.english from u
ser,teacher,score where user.id=score.id and score.id=teacher.userid;
+----+-----------+--------+---------+---------+
| id | name | tname | chinese | english |
+----+-----------+--------+---------+---------+
| 1 | 王麻子 | 毛豆 | 50 | 12 |
| 2 | 老李 | 毛豆 | 99 | 150 |
| 3 | 马六 | 老吴 | 10 | 120 |
+----+-----------+--------+---------+---------+
3 rows in set (0.01 sec)
mysql>
//总结注意事项
1：主键只能是独一无二的，不能存在重复的数据，否则会出现下面错误
因为user表里id 是主键所以当存在id=1时的数据时再不能添加了
2：外键添加的数据必须是主键中存在的，否则会出现以下错误
由于主键数据表中不存在id=5的行
3：自增列要求必须是主键或其他的键即必须是键所以此写法错误
由于id不是键所以错误
处理办法：将id变成主键或其他键值类型