数据库基础"/>
msql数据库基础
一、数据库操作
1、显示数据库
SHOW DATABASES;
SHOW CREATE DATABASE 数据库名称; #数据库的创建信息
2、创建数据库
#utf8 CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;#gbk CREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;
数据库命名规则
1、可以由字母、数字、下划线、＠、＃、＄组成 2、区分大小写 3、唯一性 4、不能使用关键字如 create等 5、不能单独使用数字 6、最长128位
3、使用数据库
USE 数据库名
4、修改数据库
ALTER DATABASE 数据库名 CHARSET utf8;
5、删除数据库
DROP DATABASE 数据库名;
6、用户管理
用户相关信息是放在mysql数据库中的user表中，所以对用户的操作，实际上就是对user表的操作。
#创建用户create user '用户名'@'IP地址' identified by '密码';
#删除用户drop user '用户名'@'IP地址';
#修改用户rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';;
#修改密码set password for '用户名'@'IP地址' = Password('新密码') 例如：
mysql> create user 'xiaohua'@'localhost' identified by '12345678hr';
7、授权管理
#查看用户权限 show grants for '用户'@'IP地址'
例如：
mysql> show grants for 'root'@'127.0.0.1';
#赋予权限 grant 权限 on 数据库.表 to '用户'@'IP地址'
例如：
mysql> grant select on test_study.* to 'xiaohu'@'localhost';
#取消权限 revoke 权限 on 数据库.表 from '用户'@'IP地址' #如果权限不存在会报错
例如：
mysql> revoke select on test_study.* from 'xiaohu'@'localhost';
在赋予权限时，也可以同时赋予多个权限，中间以逗号隔开：
mysql> grant select,update,delete,insert on test_study.* to 'xiaohu'@'localhost';
更改后的权限立即生效，使用：
flush privileges ;
设置权限的语句中应包含以下信息：
- 要授予的权限（例如select、update、delete、insert）
- 被授予访问权限的数据库或表
- 用户信息
权限设置的层次：
- 整个服务器，使用 grant all 和revoke all...*.*
grant all privileges on *.* TO '用户名'@'IP'
- 整个数据库，使用on database.*
grant select on db_test.* TO '用户名'@'IP'
- 某张表，使用on database.table
revoke select on db1.tb1 from '用户名'@'IP'
- 某一列或多列
grant select(id, name) on db1.tb1 to 'xiaohu'@'localhost';
- 存储过程
grant execute on procedure dbtest.pro_remo to ’xiaohu’@’localhost’
总结：
all privileges 除grant外的所有权限select 仅查权限select,insert 查和插入权限...usage 无访问权限alter 使用alter tablealter routine 使用alter procedure和drop procedurecreate 使用create tablecreate routine 使用create procedurecreate temporary tables 使用create temporary tablescreate user 使用create user、drop user、rename user和revoke all privilegescreate view 使用create viewdelete 使用deletedrop 使用drop tableexecute 使用call和存储过程file 使用select into outfile 和 load data infilegrant option 使用grant 和 revokeindex 使用indexinsert 使用insertlock tables 使用lock tableprocess 使用show full processlistselect 使用selectshow databases 使用show databasesshow view 使用show viewupdate 使用updatereload 使用flushshutdown 使用mysqladmin shutdown(关闭MySQL)super ??使用change master、kill、logs、purge、master和set global。还允许mysqladmin????调试登陆replication client 服务器位置的访问replication slave 由复制从属使用对于权限权限设置
数据库名.* 数据库中的所有数据库名.表 指定数据库中的某张表数据库名.存储过程 指定数据库中的存储过程*.* 所有数据库数据库设置
参考：.html
二、表操作
1、创建表
CREATE TABLE 表名 ( 字段名1 数据类型 字段选项 , 字段名2 数据类型 字段选项 （无逗号） )ENGINE=InnoDB DEFAULT CHARSET=utf8;
说明：
- 如果表指定了字符集和校对规则，则以表指定的为准，如果表没有指定，则以表所在的数据库的字符集和校对规则。
- engin 就是存储引擎，比如常用的(MyISAM、InnoDB, Memory), 如果创建表的时指定了这个存储引擎，则以这个准，如果没有指定以默认的. 在my.ini中设置的有。
- 加上auto_increment，主键自动增长
- 可以通过constraint 指定外键
CREATE TABLE `userinfo` ( `nid` int unsigned AUTO_INCREMENT PRIMARY KEY COMMENT "主键ID", `name` varchar(64) NOT NULL COMMENT "姓名", `nickname` varchar(64) NOT NULL DEFAULT "" COMMENT "昵称", `password` char(32) NOT NULL COMMENT "密码" )CHARSET=UTF8 COLLATE=utf8_general_ci ENGINE=InnoDB;
2、查看表结构
desc 表名; describe 表名;
3、删除表
drop table 表名
4、清空表
delete from 表名 truncate table 表名
它们都是默认删除表内容，但是truncate 的删除速度更快
5、修改表
修改表名： alter table 表名 rename 新表名;添加列：alter table 表名 add 列名 类型 删除列：alter table 表名 drop column 列名 修改列：alter table 表名 modify column 列名 类型; -- 类型alter table 表名 change 原列名 新列名 类型; -- 列名，类型添加主键：alter table 表名 add primary key(列名); 删除主键：alter table 表名 drop primary key;alter table 表名 modify 列名 int, drop primary key;
添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段); 删除外键：alter table 表名 drop foreign key 外键名称修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000; 删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;
三、表内容操作
#增加记录 insert into 表 (列名,列名...) values (值,值,值...) insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...) insert into 表 (列名,列名...) select (列名,列名...) from 表#修改记录 update 表 set name ＝ 'xxx' update 表 set name ＝ 'xxx' where id>1#删除记录 delete from 表 delete from 表 where id＝1 and name＝'xxx'#查询记录 select * from 表 select * from 表 where id > 1 select nid,name,gender as gg from 表 where id = 1
四、表记录查询
-- 查询表达式SELECT *|field1,filed2 ... FROM tab_nameWHERE 条件GROUP BY fieldHAVING 筛选ORDER BY fieldLIMIT 限制条数
--向userinfo表插入数据
INSERT INTO userinfo VALUES (1,"张明","明",123),
(2,"李二","二",456),
(3,"王五","五",789),
(4,"顺六","六",888),
(5,"小七","七",100);
--使用where子句，进行过滤查询
#查询nid>2的记录
select * from userinfo where nid>2;
#查询nid在[2,4]的记录
select * from userinfo where nid between 2 and 4;
#查询nid=2,3,4的记录
select * from userinfo where nid in (2,3,4);
#查询nid != 2,3
select * from userinfo where nid not in (2,3);
#nid是另一张变的id或者其它关联的数据
select * from userinfo where nid in (select user_id from depart);
--使用通配符
#匹配以bg开头多个字符
select * from userinfo where nickname like 'bg%';
#匹配以bg开头一个字符
select * from userinfo where nickname like 'bg_';
--限制limit
#取出表的前两行
select * from userinfo limit 2;
#从第二行开始的后两行
select * from userinfo limit 2,2;
#从第一行开始的后三行
select * from userinfo limit 3 offset 1;
--排序order by
#根据nid列从小到大排列
select * from userinfo order by nid;
#根据nid列从大到小排列
select * from userinfo order by nid desc;
根据name列从大到小排列，如果相同则按nid列从小到大排序
select * from userinfo order by name desc,nid asc;
--分组group by
#建测试数据库
create table book_info(id int primary key auto_increment,
book_name varchar(20),price float(6,2),description varchar(20),pub_date date);#插入数据
insert into book_info (book_name,price,description,pub_date) values ("python",20,"jfjd",20180304),("python",40,"hhjhj",20190304),("java",80,"lkkj",20180404),("c",20,"lkk",20180409),("python",60,"hhjj",20190404),("java",20,"jfjd",20180304);
#按位置字段进行分组筛选
select * from book_info group by 2;#按第二列进行分组
#按字段名分组后显示内容
select * from book_info group by book_name;
#group by 中使用where以及order by语句
select id,book_name,price from book_info where id>1 group by book_name order by price desc;
#group by 中使用聚合函数
select book_name,sum(price) from book_info group by book_name; #将每一个种类的书籍的总价求出来
select book_name,count(*),max(price),min(price),avg(price) from book_info group by book_name;#求出分组后每一种类书籍的个数、价格的最大值、最小值、平均值
#group by中使用having语句
select * from book_info having id > 2; #此时相当于select * from book_info where id > 2;
select id,book_name,price from book_info group by book_name having min(id) > 3; #对书籍通过名字进行分组，分组后选出id至少大于3的组别
having 和 where两者都可以对查询结果进行进一步的过滤，差别有：<1>where语句只能用在分组之前的筛选，having可以用在分组之后的筛选；<2>使用where语句的地方都可以用having进行替换<3>having中可以用聚合函数，where中不行。
注意：group by必须在where之后order by之前
-- group_concat() 函数
将group by产生的同一个分组中的值连接起来，返回一个字符串结果。
可以看到分组后price的值只有一个，如何将同一种类的所有price娴熟出来呢？
select id,book_name,group_concat(price) from book_info group by book_name;
--使用正则表达式
select * from book_info where book_name regexp '^j';#以‘j’字符开头
select * from book_info where book_name regexp '[th]';#字符集合。匹配所包含的任意一个字符
更多请参考：.html
五、连表查询
1、测试数据准备
create table User(id int primary key auto_increment,name varchar (20),dept_id int
);
insert into User(name,dept_id) values ("abil",100),("lily",101),("cail",102),("veyr",103),("pty",104);create table Dept(dept_id int ,dept_name varchar (100)
)charset=utf8 collate=utf8_general_ci engine=innodb;insert into Dept(dept_id, dept_name) values (100,"品质部"),(101,"生产部"),(102,"管理部"),(103,"品质部"),(104,"生产部");mysql> select * from user;
+----+------+---------+
| id | name | dept_id |
+----+------+---------+
| 1 | abil | 100 |
| 2 | lily | 101 |
| 3 | cail | 102 |
| 4 | veyr | 103 |
| 5 | pty | 104 |
+----+------+---------+
5 rows in set (0.00 sec)mysql> select * from dept;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
| 100 | 品质部 |
| 101 | 生产部 |
| 102 | 管理部 |
| 103 | 品质部 |
| 104 | 生产部 |
+---------+-----------+
5 rows in set (0.00 sec)
2、笛卡尔积查询
如果想一次查出两张表的内容呢？那么应该如何查询
mysql> select * from user,dept; +----+------+---------+---------+-----------+ | id | name | dept_id | dept_id | dept_name | +----+------+---------+---------+-----------+ | 1 | abil | 100 | 100 | 品质部 | | 2 | lily | 101 | 100 | 品质部 | | 3 | cail | 102 | 100 | 品质部 | | 4 | veyr | 103 | 100 | 品质部 | | 5 | pty | 104 | 100 | 品质部 | | 1 | abil | 100 | 101 | 生产部 | | 2 | lily | 101 | 101 | 生产部 | | 3 | cail | 102 | 101 | 生产部 | | 4 | veyr | 103 | 101 | 生产部 | | 5 | pty | 104 | 101 | 生产部 | | 1 | abil | 100 | 102 | 管理部 | | 2 | lily | 101 | 102 | 管理部 | | 3 | cail | 102 | 102 | 管理部 | | 4 | veyr | 103 | 102 | 管理部 | | 5 | pty | 104 | 102 | 管理部 | | 1 | abil | 100 | 103 | 品质部 | | 2 | lily | 101 | 103 | 品质部 | | 3 | cail | 102 | 103 | 品质部 | | 4 | veyr | 103 | 103 | 品质部 | | 5 | pty | 104 | 103 | 品质部 | | 1 | abil | 100 | 104 | 生产部 | | 2 | lily | 101 | 104 | 生产部 | | 3 | cail | 102 | 104 | 生产部 | | 4 | veyr | 103 | 104 | 生产部 | | 5 | pty | 104 | 104 | 生产部 | +----+------+---------+---------+-----------+ 25 rows in set (0.00 sec)
很明显上述这样查询有很多重复，这就是笛卡尔积。
3、内连接
查询两张表都有关联的数据，相当于利用where语句从笛卡尔积中筛选数据
select * from user,dept where user.dept_id=dept.dept_id;
select * from user inner join dept on user.dept_id=dept.dept_id;
4、外连接
- 外左链接
User表（左）有则显示，如果Dept表（右）中无对应关系，则值为null
select * from user left join dept on user.dept_id=dept.dept_id;
- 外右连接
Dept表（有）有则显示，如果User表（左）中无对应关系，则值为null
select * from user right join dept on user.dept_id=dept.dept_id;
六、组合
用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中。多个 SELECT 语句会删除重复的数据。
#语法
SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions] UNION [ALL | DISTINCT] SELECT expression1, expression2, ... expression_n FROM tables [WHERE conditions];
实例：
select name from User union select dept_name from dept; #将user表中的name与dept表中的dept_name进行组合
注意：union与union all的区别是union会去掉相同的纪录，而union all不会
更多查看：.html
七、连表查询之子查询
将一个查询语句嵌套在另一个查询语句中
1、where型子查询
select * from user where dept_id in (select dept_id from dept where dept_id>102);
2、from型子查询：把内层的查询结果当成临时表，查询结果集可以当成表
select distinct id,name,dept_id from (select * from user where id > 2 order by dept_id desc) as u;
3、exists型子查询：把外层查询的结果，拿到内层去测试，如果内层的语句成立，返回True，外层查询语句将进行查询；当返回值为False时，外层查询语句不进行查询
select * from dept where exists (select dept_id from User where dept_id in (101,102));
转载于:.html
更多推荐
msql数据库基础
发布评论