6.修改表：
1）修改表名
表名可以在一个数据库中唯一的确定一张表。
格式：ALTER TABLE 旧表名 RENAME 新表名;
示例：
mysql> ALTER TABLE student RENAME student4;
Query OK, 0 rows affected (0.11 sec)
mysql> DESCRIBE student;
ERROR 1146 (42S02): Table 'example.student' doesn't exist
由上面可以看出，改名后的表已经不存在了。
2）修改字段的数据类型
格式：ALTER TABLE 表名 MODIFY 属性名 数据类型;
mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(20) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.08 sec)
mysql> ALTER TABLE student1 MODIFY name varchar(30);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
3）修改字段名：
格式：ALTER TABLE 表名 CHANGE 旧属性名 新属性名 新数据类型;
示例：
mysql> DESCRIBE student1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(30) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE student1 CHANGE name stu_name varchar(40);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| stu_name | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
修改的字段名的同时也修改了数据类型
4）增加字段
格式：ALTER TABLE 表名 ADD 属性名1 数据类型 [完整性约束条件] [FIRST | AFTER 属性名2];
其中，“属性名1”参数指需要增加的字段的名称；“FIRST”参数是可选参数，其作用是将新增字段设置为表的第一个字段；“AFTER”参数也是可选的参数，其作用是将新增字段添加到“属性名2”后面；“属性名2”当然就是指表中已经有的字段
示例：
mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| stu_name | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> ALTER TABLE student1 ADD teacher_name varchar(20) NOT NULL AFTER id;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE student1;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| teacher_name | varchar(20) | NO | | NULL | |
| stu_name | varchar(40) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
5）删除字段
格式：ALTER TABLE 表名 DROP 字段名;
示例：
mysql> DESCRIBE student1;
+--------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| teacher_name | varchar(20) | NO | | NULL | |
| stu_name | varchar(40) | YES | | NULL | |
+--------------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> ALTER TABLE student1 DROP teacher_name;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESCRIBE student1;
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | NULL | |
| stu_name | varchar(40) | YES | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
6)更改表的存储引擎
格式：ALTER TABLE 表名 ENGINE = 存储引擎名;
示例：
mysql> SHOW CREATE TABLE student2;
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| Table | Create Table
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| student2 | CREATE TABLE `student2` (
`id` int(11) NOT NULL DEFAULT '0',
`stu_id` int(11) NOT NULL DEFAULT '0',
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`,`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
1 row in set (0.05 sec)
mysql> ALTER TABLE student2 ENGINE = MYISAM;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE student2;
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| Table | Create Table
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
| student2 | CREATE TABLE `student2` (
`id` int(11) NOT NULL DEFAULT '0',
`stu_id` int(11) NOT NULL DEFAULT '0',
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`,`stu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=gb2312 |
+----------+----------------------------------------
----------------------------------------------------
----------------------------------------------------
1 row in set (0.00 sec)
7)删除表的外键约束
格式：ALTER TABLE 表名 DROP FOREIGN KEY 外键别名;
示例：
mysql> SHOW CREATE TABLE teacher;
+---------+------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------+
| Table | Create Table
|
+---------+------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
`id` int(11) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `STUID` (`stu_id`),
CONSTRAINT `STUID` FOREIGN KEY (`stu_id`) REFERENCES `stu
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------
-----------------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------+
1 row in set (0.08 sec)
mysql> ALTER TABLE teacher DROP FOREIGN KEY STUID;
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> SHOW CREATE TABLE teacher;
+---------+------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------------+
| Table | Create Table
|
+---------+------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------------+
| teacher | CREATE TABLE `teacher` (
`id` int(11) NOT NULL,
`stu_id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `STUID` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312 |
+---------+------------------------------------------------
-----------------------------------------------------------
--------------------------------------------------------+
1 row in set (0.00 sec)
6.删除表
格式：DROP TABLE 表名;
删除没有被关联的普通表：直接上面的SQL语句就行了
删除被其他表关联的父表：
方法一：先删除子表，在删除父表
方法二：删除父表的外键约束（上面有介绍），再删该表
7.表数据的操作
一、增
insert:增加(条件一条新纪录，默认新添加的记录都添加在已有记录的末尾)
1.格式： 1.1添加新纪录时，只给记录中的某几个字段赋值
insert into 表名(字段名1,字段名2...)values(值1,值2....);
*值得类型是字符的话需要用双引号引起来
1.2 添加新纪录时，给所有记录中的所有字段赋值
insert into 表名 values(值1,值2....);
*值得类型是字符的话需要用双引号引起来 *值与字段的类型一定匹配
2.例子 1.1 给表中插入一条记录
insert into usertab values(46,"plj",100,"x",2000,2000,"my teahere","/pljdir","/bin/bash");
1.2 给表中的指定字段赋值
insert into usertab(username,password,uid,gid,shell)values("plj","x",3000,3000,"/bin/bash");
1.3 如何给字段赋空值
update usertab set comment=NULL;
二、删
delete:删除(删除的是一整条记录)
1.格式： 1.1 删除表中的所有记录
delete from 数据库.表名;
1.2 只删除指定的记录
delete from 数据库.表名 where 条件表达式;
*条件表达式：数值比较、字符比较、范围内、空、非空、逻辑比较、模糊、正则表达式
2.例子：
1.1 删除uid字段为null的记录
mysql> select id,uid from usertab where uid is null;
+----+------+
| id | uid |
+----+------+
| 27 | NULL |
| 28 | NULL |
| 29 | NULL |
| 30 | NULL |
| 31 | NULL |
| 32 | NULL |
+----+------+
16 rows in set (0.00 sec)
mysql> delete from usertab where uid is null;
Query OK, 16 rows affected (0.00 sec)
mysql> select id,uid from usertab where uid is null;
Empty set (0.00 sec)
三、改
update:修改字段的值
1.批量修改 1.1 格式：
update 数据库名.表名 set 字段名=值;
update 数据库名.表名 set 字段名=值,字段名=值;
1.2 例子
批量修改age字段的值为20
mysql> update usertab set age=20;
Query OK, 42 rows affected (0.00 sec)
Rows matched: 42 Changed: 42 Warnings: 0
mysql> select age from usertab;
+------+
| age |
+------+
| 20 |
| 20 |
| 20 |
| 20 |
| 20 |
.....
2.只修改符合条件的记录中指定字段的值 1.1格式：
update 数据库名.表名 set 字段名=值,字段名=值 where 条件表达式;
*条件表达式：数值比较 字符比较 范围内 空 非空 逻辑比较 模糊 正则表达式
1.2例子： 修改username是root的age字段的值为30
mysql> update usertab set age=30 where username="root";
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select username,age from usertab;
+----------+------+
| username | age |
+----------+------+
| root | 30 |
| bin | 20 |
| daemon | 20 |
| adm | 20 |
....
四、查
select:查询记录内容(值) 1.格式
select 字段名列表 from 表名;
select 字段名列表 from 数据库名.表名;
select 字段名列表 from 数据库名.表名 where 条件表达式;
2.字段名列表的表示方式
#所有字段
*
#查询某几个字段
字段名1，字段名2，字段名N
select 字段名列表 from 数据库名.表名 where 条件表达式;
例子：查看uid小于10的
select username,uid from 数据库名.表名 where uid < 10;
3.条件表达式 1.1 数值比较 比较符号：> >= < <= = != 格式： 字段名(数值类型且是整型) 比较符号 数字 例子：
select * from usertab where uid = 10;
查询id=10的记录
mysql> select * from usertab where id = 10;
+----+----------+----------+------+------+---------+-----------------+---------------+
| id | username | password | uid | gid | comment | homedir | shell |
+----+----------+----------+------+------+---------+-----------------+---------------+
| 10 | uucp | x | 10 | 14 | uucp | /var/spool/uucp | /sbin/nologin |
+----+----------+----------+------+------+---------+-----------------+---------------+
1 row in set (0.00 sec)
1.2字符比较 比较符号：= !=
格式： 字段名 “字符串” 比如：name=”root”
例子: 查询表中username=root的记录
mysql> select * from usertab where username="root";
+----+----------+----------+------+------+---------+---------+-----------+
| id | username | password | uid | gid | comment | homedir | shell |
+----+----------+----------+------+------+---------+---------+-----------+
| 1 | root | x | 0 | 0 | root | /root | /bin/bash |
+----+----------+----------+------+------+---------+---------+-----------+
1 row in set (0.00 sec)
查询usertab表中usernam=root用户的username，uid，gid的记录
mysql> select username,uid,gid from usertab where username="root";
+----------+------+------+
| username | uid | gid |
+----------+------+------+
| root | 0 | 0 |
+----------+------+------+
1 row in set (0.00 sec)
1.3范围内查找
in 在......里
not in 不在.....里
between....... and 在.....与....之间 (可以做字符比较，但很少用做字符)
例子：
select username ,uid from usertab where uid between 10 and 20; ：大于等于10小于等于20的
select username ,uid from usertab where uid a and c;
1.4 空和非空 匹配空：is null 匹配非空：is not null
例子： 匹配为空的
mysql> select * from usertab where username is NULL;
Empty set (0.00 sec)
匹配非空的
[外链图片转存失败(img-qLKhkbYG-1566789662382)(C:\Users\captain\Desktop\1620)]
*赋值的时候 “” 和 ” ” 是等效的
#匹配空
select id,username from usertab where username is null;
#匹配字符串null
select id,username from usertab where username="null";
#匹配空
select id,username from usertab where username="";
#匹配非空
select id,username from usertab where username is not null;
1.5逻辑比较 （查询时使用2个或2个以上查询条件）
逻辑与: and-多个条件必须同时成立
select username,uid from usertab where username="root" and uid=35 and shell="/bin/shell"
逻辑或:or-多个条件只要有一个条件成立就可以
select username,uid from usertab where username="root" or uid=35 or shell="/bin/shell"
select username,uid from usertab where username in("root","daemon") or uid=10;
逻辑非:!-取反
select username from usertab username!="root"
distinct 不显示重复的值
select shell from usertab;
mysql> select distinct shell from usertab;
1.6模糊查询 查询用户名姓张的学生信息 格式： where 字段名 like ‘表达式’
表达式符号 ： % 匹配0个到多个字符 _ 匹配任意一个字符
匹配任意3个字符 select username from usertab where username like ‘_ _ _’ _
匹配以r开头的任意3个字符 select username from usertab where username like ‘r_ _’
匹配以a开头的 select username from usertab where username like ‘a%’
select username from usertab where username like ‘a’
select username from usertab where username like ‘张%’
使用这则表达式做查询条件
mysql> select username from usertab where username like ‘j%’;
1.7使用正则表达式的格式： where 字段名 regexp ‘正则表达式’
查询名字中包含数字的
mysql> select username from usertab where username regexp ‘[0-9]’;
查询uid是两位数的 mysql> select id,username,uid from usertab where uid regexp ‘^…$’;
4.查询时做四则运算 运算符号： + - * / %
1.1 加法
mysql> select username,uid+gid from usertab;
+----------+---------+
| username | uid+gid |
+----------+---------+
| root | 0 |
| bin | 2 |
| daemon | 4 |
| adm | 7 |
+----------+---------+
42 rows in set (0.00 sec)
1.2减法运算
mysql> select username,2016-age as s_year from usertab where username="root";
+----------+--------+
| username | s_year |
+----------+--------+
| root | 1995 |
+----------+--------+
1 row in set (0.00 sec)
3.乘法运算
mysql> select uid,gid,(uid*gid) as chengji from usertab where username="mail";
+------+------+---------+
| uid | gid | chengji |
+------+------+---------+
| 8 | 12 | 96 |
+------+------+---------+
1 row in set (0.00 sec)
*as chengji:表示给运算结果列取一个名字 4.除法运算
mysql> select username,uid,gid,(uid+gid)/2 as pjcj from usertab where username="www";
+----------+------+------+----------+
| username | uid | gid | pjcj |
+----------+------+------+----------+
| www | 501 | 501 | 501.0000 |
+----------+------+------+----------+
1 row in set (0.00 sec)
5.常用统计函数 avg()：集合的平均值 sum()：对集合中的各参数求和 min()：集合中的最小值 max()：集合中的最大值 count()：记录的个数(空值不算)
1.1 统计所有字段的个数
mysql> select count(*) from usertab;
+----------+
| count(*) |
+----------+
| 42 |
+----------+
1 row in set (0.00 sec)
1.2 统计指定字段的个数
mysql> select count(username),count(uid) from usertab;
+-----------------+------------+
| count(username) | count(uid) |
+-----------------+------------+
| 42 | 26 |
+-----------------+------------+
1 row in set (0.00 sec)
6.排序 1.1 格式：(默认为升序)
order by 字段名 排序方式{ASC(升序),DESC(降序)}
1.2 例子：
*默认(升序)排序
mysql> select uid,username from usertab order by uid;
mysql> select uid,username from usertab order by uid;
+------+----------+
| uid | username |
+------+----------+
| NULL | |
| NULL | jim8 |
| NULL | jim9 |
| NULL | 3jim |
| NULL | |
| 0 | root |
| 0 | NULL |
| 1 | bin |
| 2 | daemon |
......
*降序排列
mysql> select uid,username from usertab order by uid desc;
+------+----------+
| uid | username |
+------+----------+
| 501 | www |
| 500 | mysql |
| 499 | saslauth |
| 99 | nobody |
| 4 | lp |
......
7.分组 1.格式：
select shell from usertab group by 字段名;
2.例子：
mysql> select shell from usertab group by shell;
+----------------+
| shell |
+----------------+
| NULL |
| /bin/bash |
| /bin/sync |
| /sbin/halt |
| /sbin/nologin |
| /sbin/shutdown |
| NULL |
+----------------+
7 rows in set (0.00 sec)
3.处理分组后的结果 1.1格式：
select shell from usertab group by 字段名 having 条件表达式
例子： 查询usertab表中的shell字段并分组，然后查找出uid小于10的
mysql> select shell from usertab group by shell having "uid"<10;
+----------------+
| shell |
+----------------+
| NULL |
| /bin/bash |
| /bin/sync |
| /sbin/halt |
| /sbin/nologin |
| /sbin/shutdown |
| NULL |
+----------------+
7 rows in set, 1 warning (0.00 sec)
8.限制显示记录的条目数
limit限制显示记录的条目数
1.1格式:
limit N;
# N表示从查询结果的第几条记录开始显示,默认从查询结果的第一条记录开始显示,第一条记录的编号是0
# M 表示显示条目的记录数
limit N,M;
例子：
显示查询结果的前10条记录
mysql> select id,username,uid from usertab limit 10;
mysql> select id,username,uid from usertab limit 0,10;
+----+----------+------+
| id | username | uid |
+----+----------+------+
| 1 | root | 0 |
| 2 | bin | 1 |
| 3 | daemon | 2 |
| 4 | adm | 3 |
| 5 | lp | 4 |
| 6 | sync | 5 |
| 7 | shutdown | 6 |
| 8 | halt | 7 |
| 9 | mail | 8 |
| 10 | uucp | 10 |
+----+----------+------+
10 rows in set (0.00 sec)
从第三行显示三行
mysql> select id,username,uid from usertab limit 3,3;
+----+----------+------+
| id | username | uid |
+----+----------+------+
| 4 | adm | 3 |
| 5 | lp | 4 |
| 6 | sync | 5 |
+----+----------+------+
3 rows in set (0.00 sec)
9.嵌套查询 里层查询结果做为外层查询条件
格式/例子： 从usertab表查找uid小于uid列平均值的记录
mysql> select username,uid from usertab where uid < (select avg(uid) from usertab);
+----------+------+
| username | uid |
+----------+------+
| root | 0 |
| bin | 1 |
| daemon | 2 |
| adm | 3 |
| lp | 4 |
| sync | 5 |
| shutdown | 6 |
| halt | 7 |
| mail | 8 |
| uucp | 10 |
| operator | 11 |
| games | 12 |
.....
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
