mysql -u root -p
Enter password: ******
一:基础
1.如上,登录mysql,密码:111111或123456或123123
create database if not exists 西游记;-------创建一个名为西游记的数据库
2.如上,新建一个名为“西游记”的数据库。
create table 唐僧(id int primary key auto_increment);------新建一个名为“唐僧”的表,id为自动递增的主键
3.如上,在“西游记”数据库中,新建一个名为“唐僧”的表,其字段id数据类型为int类型,自动递增的主键。
desc 唐僧;--------语句
---结果如下
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| weapons | varchar(64) | YES | | NULL | |
| abibty | varchar(64) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
4.如上,查看表,唐僧的数据结构。
alter table 唐僧 add(weapons varchar(64),abibty varchar(64));
5.如上,在唐僧表中插入两个字段(武器 64字符串,能力64字符串)
insert into 唐僧(id,weapons,abibty)values('1','紧箍咒','headache');
6.如上,在唐僧表中插入行,id为1,weapons武器为紧箍咒,abibty能力为头疼。
mysql> select * from 唐僧;---查看名为唐僧的表
---结果如下:
+----+-----------+----------+
| id | weapons | abibty |
+----+-----------+----------+
| 1 | 紧箍咒 | headache |
+----+-----------+----------+
1 row in set (0.00 sec)
7.如上,这个语句可以打开查看名为唐僧的表
mysql> use 西游记; ------选中西游记数据库
Database changed
mysql> show tables; -------查看西游记数据库中的表
+---------------------+
| Tables_in_西游记 |
+---------------------+
| 唐僧 |
+---------------------+
1 row in set (0.01 sec)
mysql>
8.如上,第二次登录时。
use 西游记;---该语句可以选中西游记数据库,查看该数据库中的表时,使用语句show tables;---可查看数据库中的所有表。
mysql> desc 唐僧; ---------该语句查看唐僧表的数据结构
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int | NO | PRI | NULL | auto_increment |
| weapons | varchar(64) | YES | | NULL | |
| abibty | varchar(64) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> select * from 唐僧; ---------该语句查看唐僧表的内容
+----+-----------+------------+
| id | weapons | abibty |
+----+-----------+------------+
| 1 | 紧箍咒 | headache |
| 2 | 袈裟 | protection |
+----+-----------+------------+
2 rows in set (0.01 sec)
9.如上,desc 唐僧;和select * from 唐僧;的区别就是,前者查看“唐僧”表数据结构,后者查看“唐僧”表内容。
二:查询语句
mysql> select * from 唐僧; ----查看表唐僧里的所有内容
+----+-----------+------------+
| id | weapons | abibty |
+----+-----------+------------+
| 1 | 紧箍咒 | headache |
| 2 | 袈裟 | protection |
+----+-----------+------------+
2 rows in set (0.00 sec)
1.如上,该语句用于查看唐僧个表全部内容
mysql> select * from 唐僧 where id=1; ----查看唐僧表里,id=1的全部内容
+----+-----------+----------+
| id | weapons | abibty |
+----+-----------+----------+
| 1 | 紧箍咒 | headache |
+----+-----------+----------+
1 row in set (0.01 sec)
2.如上,该语句:查看唐僧表里,id=1的全部内容
mysql> select weapons from 唐僧 where id=1; ----查看唐僧表里的id=1的列weapons
+-----------+
| weapons |
+-----------+
| 紧箍咒 |
+-----------+
1 row in set (0.00 sec)
3.如上,该语句用来查看唐僧表里的id=1的列weapons武器。
mysql> insert into 唐僧(id,weapons)values('3','手杖');
Query OK, 1 row affected (0.01 sec)
mysql> select *from 唐僧;
+----+-----------+------------+
| id | weapons | abibty |
+----+-----------+------------+
| 1 | 紧箍咒 | headache |
| 2 | 袈裟 | protection |
| 3 | 手杖 | NULL |
+----+-----------+------------+
3 rows in set (0.00 sec)
mysql> select * from 唐僧 where abibty is null; ----查找唐僧表中abibty为空的行。
+----+---------+--------+
| id | weapons | abibty |
+----+---------+--------+
| 3 | 手杖 | NULL |
+----+---------+--------+
1 row in set (0.00 sec)
4.如上,在表唐僧中插入行,但是abibty为空,要判断值是否为null,要使用is null运算符。
mysql> select * from 唐僧 where id=2 OR abibty is null;
+----+---------+------------+
| id | weapons | abibty |
+----+---------+------------+
| 2 | 袈裟 | protection |
| 3 | 手杖 | NULL |
+----+---------+------------+
2 rows in set (0.00 sec)
5.OR关键词,只要满足两个条件之一就可以被查询到。
三:修改
1.如图,我在创建表student时,将stu_qq数据类型设置为了int,我在填写qq时,报错,怀疑数据类型错误,此时我想将数据类型更改一下改为long,此处需要用到的关键词:modify
修改表某一字段数据类型语句:alter table 表名 modify column 列名或字段名 要更改的数据类型。
mysql> alter table student add(stu_name varchar(64),stu_qq int); ----如此将stu_qq设置为了int
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> insert into student (stu_id,stu_name,stu_qq)values('1','唐僧','3386301528'); -------在我添加qq号时报错
ERROR 1264 (22003): Out of range value for column 'stu_qq' at row 1
mysql> desc student;
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| stu_id | int | NO | PRI | NULL | auto_increment |
| stu_name | varchar(64) | YES | | NULL | |
| stu_qq | int | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)
mysql> alter table student modify column stu_qq long; ---更改表student中列stu_qq为long类型。
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> desc student; ----此时,查看表数据类型时,数据类型更改
+----------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+----------------+
| stu_id | int | NO | PRI | NULL | auto_increment |
| stu_name | varchar(64) | YES | | NULL | |
| stu_qq | mediumtext | YES | | NULL | |
+----------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into student(stu_id,stu_name,stu_qq)values('2','孙悟空','3039525060');
Query OK, 1 row affected (0.01 sec) ----添加成功
mysql> select * from student;
+--------+-----------+------------+
| stu_id | stu_name | stu_qq |
+--------+-----------+------------+
| 1 | 唐僧 | 33333 |
| 2 | 孙悟空 | 3039525060 |
+--------+-----------+------------+
2.上图中,唐僧的qq号写错了,此时我想要修改qq号。
修改某一字段内容语句:update 表名 set 更改字段=‘具体信息’ where 条件
mysql> update student set stu_qq='3386301528' where stu_id=1;
-----修改student表中student_id=1的信息,将stu_qq改为3386301528
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student; -----已成功修改
+--------+-----------+------------+
| stu_id | stu_name | stu_qq |
+--------+-----------+------------+
| 1 | 唐僧 | 3386301528 |
| 2 | 孙悟空 | 3039525060 |
+--------+-----------+------------+
2 rows in set (0.00 sec)