mysql数据操纵实验总结_MySQL 操作总结

1. 数据库级别操作

1.1 创建数据库

CREATE DATABASE db1 default charset utf8 collate utf8_general_ci;

1.2 删除数据库

DROP DATABASE db1;

2. 用户级别操作

2.1 单独创建用户并授权

CREATE USER 'hukey'@'%';

2.2 删除用户

DROP USER 'hukey'@'%';

2.3 修改用户

RENAME USER 'hukey'@'%' TO 'superman'@'%';

2.4 修改密码

SET PASSWORD FOR 'superman'@'%' = PASSWORD('111111');

3. 授权管理操作

3.1 为已有用户授权

GRANT SELECT, UPDATE, DELETE ON db1.* TO 'superman'@'%';

3.2 查看用户授权

SHOW GRANTS FOR 'superman'@'%';

3.3 删除用户授权

REVOKE DELETE ON db1.* FROM 'superman'@'%';

all privileges除grant外的所有权限select仅查权限select,insert查和插入权限

...

usage                   无访问权限alter                   使用alter table

alter routine           使用alter procedure和drop procedure

create                  使用create table

create routine          使用create procedure

create temporary tables 使用create temporarytablescreate user             使用create user、drop user、rename user和revoke??all privileges

create view             使用create view

delete使用deletedrop                    使用drop table

execute使用call和存储过程file                    使用select into outfile 和 loaddata infilegrant option            使用grant 和 revoke

index使用indexinsert使用insert

lock tables             使用locktableprocess                 使用showfullprocesslistselect使用select

show databases          使用show databases

showview               使用show view

update使用update

reload                  使用flushshutdown                使用mysqladmin shutdown(关闭MySQL)

super                   使用change master、kill、logs、purge、master和set global。还允许mysqladmin????????调试登陆replicationclient      服务器位置的访问replication slave       由复制从属使用

对于权限

对于目标数据库以及内部其他：

数据库名.*数据库中的所有

数据库名.表         指定数据库中的某张表

数据库名.存储过程   指定数据库中的存储过程*.*                 所有数据库

对于数据库

用户名@IP地址用户只能在改IP下才能访问

用户名@192.168.1.%  用户只能在改IP段下才能访问(通配符%表示任意)

用户名@%            用户可以再任意IP下访问(默认IP地址为%)

对于用户和IP地址

PS：修改完权限需要执行：

FLUSH PRIVILEGES;

将数据读取到内存中，从而立即生效.

4. 常用数据类型

MySQL数据类型可以分为 3 大类：

(1)数值类型：int float

(2)字符串类型 char varchar text

(3)时间类型 datetime

重点问题：

char 和 varchar 的区别？

char： 定长，效率高

varchar：不定长，效率偏低

5. 表内容操作

5.1 增

insert into 表 (列名,列名...) values(值,值,值...)insert into 表 (列名,列名...) values(值,值,值...),(值,值,值...)insert into 表 (列名,列名...) select (列名,列名...) from 表

5.2 删

delete from表delete from 表 where id＝1 and name＝'alex'

truncate table tb2; 清空数据并将自增值设置为 1

5.3 改

update 表 set name ＝ 'alex' where id>1

5.4 查

select * from表select * from 表 where id > 1

select nid,name,gender as gg from 表 where id > 1

5.5 其他

5.5.1条件select * from 表 where id > 1 and name != 'alex' and num = 12;select * from 表 where id between 5 and 16;select * from 表 where id in (11,22,33)select * from 表 where id not in (11,22,33)select * from 表 where id in (select nid from表)5.5.2通配符select * from 表 where name like 'ale%' -ale开头的所有(多个字符串)select * from 表 where name like 'ale_' -ale开头的所有(一个字符)5.5.3限制select * from 表 limit 5; -前5行select * from 表 limit 4,5; -从第4行开始的5行select * from 表 limit 5 offset 4 -从第4行开始的5行5.5.4排序select * from 表 order by 列 asc -根据 “列” 从小到大排列select * from 表 order by 列 desc -根据 “列” 从大到小排列select * from 表 order by 列1 desc,列2 asc -根据 “列1” 从大到小排列，如果相同则按列2从小到大排序5.5.5分组select num from 表 group bynumselect num,nid from 表 group bynum,nidselect num,nid from 表 where nid > 10 group by num,nid order nid desc

select num,nid,count(*),sum(score),max(score),min(score) from 表 group bynum,nidselect num from 表 group by num having max(id) > 1021特别的：group by 必须在where之后，orderby之前5.5.6连表

无对应关系则不显示selectA.num, A.name, B.namefromA,BWhere A.nid =B.nid

无对应关系则不显示selectA.num, A.name, B.namefrom A inner joinBon A.nid =B.nid

A表所有显示，如果B中无对应关系，则值为nullselectA.num, A.name, B.namefrom A left joinBon A.nid =B.nid

B表所有显示，如果B中无对应关系，则值为nullselectA.num, A.name, B.namefrom A right joinBon A.nid =B.nid5.5.7组合

组合，自动处理重合selectnicknamefromAunion

selectnamefromB

组合，不处理重合selectnicknamefromAunion all

selectnamefromB5.5.8连表查询left join 表名 on

right join 表名 on

inner join 将出现null时一行隐藏

6. 补充知识

6.1 mysql left join 中 where 和 on 条件的区别？

left join中关于where和on条件的几个知识点：

1. 多表left join是会生成一张临时表，并返回给用户

2. where条件是针对最后生成的这张临时表进行过滤，过滤掉不符合where条件的记录，是真正的不符合就过滤掉。

3. on条件是对left join的右表进行条件过滤，但依然返回左表的所有行，右表中没有的补为NULL

4. on条件中如果有对左表的限制条件，无论条件真假，依然返回左表的所有行,但是会影响右表的匹配值。也就是说on中左表的限制条件只影响右表的匹配内容，不影响返回行数。

测试：

创建两张表：

--t1 表

CREATE TABLE t1(id INT,name VARCHAR(20));insert  into `t1`(`id`,`name`) values (1,'a11');insert  into `t1`(`id`,`name`) values (2,'a22');insert  into `t1`(`id`,`name`) values (3,'a33');insert  into `t1`(`id`,`name`) values (4,'a44');--t2 表

CREATE TABLE t2(id INT,local VARCHAR(20));insert  into `t2`(`id`,`local`) values (1,'beijing');insert  into `t2`(`id`,`local`) values (2,'shanghai');insert  into `t2`(`id`,`local`) values (5,'chongqing');insert  into `t2`(`id`,`local`) values (6,'tianjin');

测试01：返回左表所有行，右表符合 on 条件的原样匹配，不满足条件的补 NULL

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id;+------+------+----------+

| id   | name | local    |

+------+------+----------+

|    1 | a11  | beijing  |

|    2 | a22  | shanghai |

|    3 | a33  | NULL     |

|    4 | a44  | NULL     |

+------+------+----------+

测试02：on后面增加对右表的限制条件：t2.local='beijing'

结论02：左表记录全部返回，右表筛选条件生效

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id and t2.local='beijing';+------+------+---------+

| id   | name | local   |

+------+------+---------+

|    1 | a11  | beijing |

|    2 | a22  | NULL    |

|    3 | a33  | NULL    |

|    4 | a44  | NULL    |

+------+------+---------+

测试03：只在 where 后面增加对右表的限制条件：t2.local = 'beijing'

结论03：针对右表，相同条件，在where后面对最后的临时表进行记录筛选，行数可能会减少；在on后面是作为匹配条件进行筛选，筛选的是右表的内容。

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t2.local='beijing';+------+------+---------+

| id   | name | local   |

+------+------+---------+

|    1 | a11  | beijing |

+------+------+---------+

测试04：t1.name = 'a11' 或者 t1.name = 'a33'

结论04：on中对左表的限制条件，不影响返回的行数，只影响右表的匹配内容

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id and t1.name='a11';+------+------+---------+

| id   | name | local   |

+------+------+---------+

|    1 | a11  | beijing |

|    2 | a22  | NULL    |

|    3 | a33  | NULL    |

|    4 | a44  | NULL    |

+------+------+---------+

MariaDB[db2]> select t1.id, t1.name, t2.local from t1 LEFT JOIN t2 on t1.id = t2.id and t1.name = 'a33';+------+------+-------+

| id   | name | local |

+------+------+-------+

|    1 | a11  | NULL  |

|    2 | a22  | NULL  |

|    3 | a33  | NULL  |

|    4 | a44  | NULL  |

+------+------+-------+

测试05：where t1.name = 'a33' 或者 where t1.name = 'a22'

结论05：where条件是在最后临时表的基础上进行筛选， 显示只符合最后where条件的行

MariaDB [db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t1.name='a33';+------+------+-------+

| id   | name | local |

+------+------+-------+

|    3 | a33  | NULL  |

+------+------+-------+

MariaDB[db2]> SELECT t1.id,t1.name,t2.local FROM t1 LEFT JOIN t2 ON t1.id=t2.id where t1.name='a22';+------+------+----------+

| id   | name | local    |

+------+------+----------+

|    2 | a22  | shanghai |

+------+------+----------+