文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

MYSQL基础语法示例

2024-04-02 19:55

关注

小编给大家分享一下MYSQL基础语法示例,相信大部分人都还不怎么了解,因此分享这篇文章给大家参考一下,希望大家阅读完这篇文章后大有收获,下面让我们一起去了解一下吧!

用户管理

新建用户及密码:foo为名,123为密码,locahost为固定地址登录

# 以下为两种创建方式
CREATE USER foo@localhost IDENTIFIED BY ‘123’ 
insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));
#只要更改了用户及密码
flush privileges

设置及更改用户密码:

#以下为三种更改方式
SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword')
#若果是当前登录用户
SET PASSWORD = PASSWORD('newpassword')
update mysql.user set password=password('newpassword') where User='username' and Host='host'
#只要更改了用户及密码
flush privileges

删除用户:

Delete FROM user Where User='test' and Host='localhost';
flush privileges;
# 删除用户的数据库
drop database testDB; 
# 删除账户及权限
drop user 用户名@ localhost;

授权:

GRANT INSERT,DELETE,UPDATE,SELECT ON databasename.tablename TO 'username'
@'host'
flush privileges

说明:

(1)privileges-用户的操作权限,如SELECT , INSERT , UPDATE  
(2)databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用表示, 如.*
(3)新设置用户或更改密码后需用flush privileges刷新MySQL的系统权限相关表,否则会出现拒绝访问,还有一种方法,就是重新启动mysql服务器,来使新设置生效

查看权限:

show grants for root@localhost;
# GRANT的反操作,去除权限
 REVOKE SELECT ON db_name.* TO name;

登录远程MySQL(ubuntu): mysql -h  <远程ip> -P 远程端口 -u 用户- p 密码

# 需远程ip和端口:10.10.101.111:30061  远程mysql的用户及密码
mysql -h 10.10.101.111 -P 30061 -u root -p 123456

MYSQL数据类型

数据库

创建表:
CREATE TABLE table_name(
id TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(60) NOT NULL,
score TINYINT UNSIGNED NOT NULL DEFAULT 0,PRIMARY KEY(id)
)ENGINE=InnoDB;//设置表的存储引擎,一般常用InnoDB和MyISAM;InnoDB可靠,支持事务;MyISAM高效不支持全文检索
DROP TABLE IF EXISTS `db_school`,`Student`;
ALTER TABLE tb_name ADD COLUMN address varchar(80) NOT NULL;
ALTER TABLE tb_name DROP address;
ALTER TABLE tb_name CHANGE score score SMALLINT(4) NOT NULL;
数据

条件控制

where语句:

SELECT * FROM tb_name WHERE id=3;

Group by  
Group by解释
GROUP BY 与WHERE联合查询:
select 列a,聚合函数 from 表名 where 过滤条件 group by 列a having 过滤条件
当结合在一起时,where在前,group by 在后。即先对select xx from xx的记录集合用where进行筛选,然后再使用group by 对筛选后的结果进行分组 使用having字句对分组后的结果进行筛选

HAVING语句:

SELECT * FROM tb_name GROUP BY score HAVING count(*)>2;

Having用法

*以上三者实践

转载: mysql group by 用法解析(详细)

相关条件控制符:

 =、>、<、<>、IN(1,2,3......)、BETWEEN a AND b、NOT
 AND 、OR
 Like()用法中      %  为匹配任意、  _  匹配一个字符(可以是汉字)
 IS NULL 空值检测 IS NOT NULL 
 IN   NOT IN  // 
 limit [offset,] N #如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1) : SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15
SELECT * FROM tb_name 
WHERE id=1003 AND price<=10 
# WHERE id=1003 OR price<=10 
# WHERE id IN(1002,1003)
# WHERE id NOT IN(1002,1003)
# WHERE name like '%huxx'
 SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC;

MySQL常见运算符

正则表达式
SELECT * FROM tb_name WHERE REGEXP ‘^[A-D]’;
解释:使用“^”匹配名字的开始,找出以A-D为开头的name
* 正则表达式实践:
参考博客:MYSQL使用正则表达式过滤数据

子查询

where型子查询:(把内层查询结果当作外层查询的比较条件)

#不用order by 来查询最新的商品
select goods_id,goods_name from goods where goods_id = (select max(goods_id) from goods);
#取出每个栏目下最新的产品(goods_id唯一)
select cat_id,goods_id,goods_name from goods where goods_id in(select max(goods_id) from goods group by cat_id);
#先查出哪些同学挂科两门以上
select name,count(*) as gk from stu where score < 60 having gk >=2;
#以上查询结果,我们只要名字就可以了,所以再取一次名字
select name from (select name,count(*) as gk from stu having gk >=2) as t;
#找出这些同学了,那么再计算他们的平均分
select name,avg(score) from stu where name in (select name from (select name,count(*) as gk from stu having gk >=2) as t) group by name;
#查询哪些栏目下有商品,栏目表category,商品表goods
select cat_id,cat_name from category where exists(select * from goods where goods.cat_id = category.cat_id);

MySQL函数

JOIN详解

join 用于多表中字段之间的联系

... FROM table1 INNER|LEFT|RIGHT JOIN table2 ON conditiona

JOIN 按照功能大致分为如下三类:
* INNER JOIN(内连接,或等值连接):取得两个表中存在连接匹配关系的记录
* LEFT JOIN(左连接):取得左表(table1)完全记录,即是右表(table2)并无对应匹配记录
* RIGHT JOIN(右连接):与 LEFT JOIN 相反,取得右表(table2)完全记录,即是左表(table1)并无匹配对应记录

注意:mysql不支持Full join,不过可以通过UNION 关键字来合并 LEFT JOIN 与 RIGHT JOIN来模拟FULL join.
具体参考博客Mysql Join语法解析与性能分析

UNION规则

UNION 用于把来自多个 SELECT 语句的结果组合到一个结果集合中:在多个 SELECT 语句中,对应的列应该具有相同的字段属性

SELECT column,... FROM table1  UNION [ALL] SELECT column,... FROM table2 ...

视图

视图是从一个或多个表中导出来的表,是一种虚拟存在的表。数据库中只存放了视图的定义,而没有存放视图中的数据,这些数据存放在原来的表中。
背景:安全性、查询性能提高
- 使操作简单化,可以对经常使用的查询定义一个视图,使用户不必为同样的查询操作指定条件
- 增加数据的安全性,通过视图,用户只能查询和修改指定的数据。
- 节省空间,内容是总是一致的话,那么我们不需要维护视图的内容,维护好真实表的内容,就可以保证视图的完整性了
- 提高表的逻辑独立性,视图可以屏蔽原有表结构变化带来的影响
工作机制:当调用视图的时候,才会执行视图中的sql,进行取数据操作。视图的内容没有存储,而是在视图被引用的时候才派生出数据。这样不会占用空间,由于是即时引用,视图的内容总是与真实表的内容是一致的。更新视图可以更新真实表。
视图与数据库:

视图中的数据依赖于原来表中的数据,一旦表中数据发生改变,显示在视图中的数据也会发生改变。
视图的建立和删除只影响视图本身,不影响对应的基本表
某些视图是可更新的。也就是说,可以在诸如UPDATE、DELETE或INSERT等语句中使用它们,以更新基表的内容。对于可更新的视图,在视图中的行和基表中的行之间必须具有一对一的关系。还有一些特定的其他结构,这类结构会使得视图不可更新。更具体地讲,如果视图包含下述结构中的任何一种,那么它就是不可更新的。
视图中虽然可以更新数据,但是有很多的限制。一般情况下,最好将视图作为查询数据的虚拟表,而不要通过视图更新数据。因为,使用视图更新数据时,如果没有全面考虑在视图中更新数据的限制,就可能会造成数据更新失败。

索引

单列索引:
组合索引

一个表中含有多个单列索引不代表是组合索引,通俗一点讲 组合索引是:包含多个字段但是只有索引名称
创建:CREATE INDEX Tno_Tname_index ON Teacher(Tno,Tname);

全文索引

文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.
建立: ALTER TABLE tablename ADD FULLTEXT(column1, column2)

删除索引

DORP INDEX IndexName ON TableName

查看索引

show index from tblname;

这块参考博客细说mysql索引 ,写的很详细。
索引原理:索引原理

存储

一组为了完成特定功能的SQL语句集,经过编译后存储在数据库中。当希望在不同应用程序或者平台上执行相同函数时,或封装特定功能时,存储过程是非常有用的。存储过程类似一个可编程函数。MySQL 中,单个 Store Procedure(SP) 不是原子操作,要使整个存储过程成为一个原子操作的办法是:在存储过程主体开始部分。
注意:非事务导致存储过程没有原子性即过程有的成功有的失败,变为事务增加原子性,即使执行过程出错,出错前的操作将不会真正执行。http://www.cnblogs.com/fnlingnzb-learner/p/6861376.html

优点
1. 执行速度快:存储过程会预编译,查询优化器会对其优化。
2. 可多次调用及修改
3. 功能灵活:可用流程控制语句编写,完成复杂运算
4. 安全:设置存储过程权限,保证数据安全
5. 减少流量:调用存储过程时,网络只传输此调用语句即可

语法
DELIMITER //
  CREATE PROCEDURE myproc(OUT s int)
    BEGIN
      SELECT COUNT(*) INTO s FROM students;
    END
    //DELIMITER ;

解释:  
(1)分隔符:mysql默认“;”,“DELIMITER //”声明分隔符 ,最后“DELIMITER ;”还原分隔符
(2)参数:输入、输出、输入输出参数{IN,OUT,INOUT} 具体参考mysql存储

字符串类:默认第一个字符下标为1,即参数position必须大于等于1

CHARSET(str) //返回字串字符集 
CONCAT (string2 [,... ]) //连接字串 
INSTR (string ,substring ) //返回substring首次在string中出现的位置,不存在返回0 
LCASE (string2 ) //转换成小写 
LEFT (string2 ,length ) //从string2中的左边起取length个字符 
LENGTH (string ) //string长度 
LOAD_FILE (file_name ) //从文件读取内容 
LOCATE (substring , string [,start_position ] ) 同INSTR,但可指定开始位置 
LPAD (string2 ,length ,pad ) //重复用pad加在string开头,直到字串长度为length 
LTRIM (string2 ) //去除前端空格 
REPEAT (string2 ,count ) //重复count次 
REPLACE (str ,search_str ,replace_str ) //在str中用replace_str替换search_str 
RPAD (string2 ,length ,pad) //在str后用pad补充,直到长度为length 
RTRIM (string2 ) //去除后端空格 
STRCMP (string1 ,string2 ) //逐字符比较两字串大小, 
SUBSTRING (str , position [,length ]) //从str的position开始,取length个字符, 
TRIM([[BOTH|LEADING|TRAILING] [padding] FROM]string2) //去除指定位置的指定字符 
UCASE (string2 ) //转换成大写 
RIGHT(string2,length) //取string2最后length个字符 
SPACE(count) //生成count个空格

数学类

ABS (number2 ) //绝对值 
BIN (decimal_number ) //十进制转二进制 
CEILING (number2 ) //向上取整 
CONV(number2,from_base,to_base) //进制转换 
FLOOR (number2 ) //向下取整 
FORMAT (number,decimal_places ) //保留小数位数 
HEX (DecimalNumber ) //转十六进制 
注:HEX()中可传入字符串,则返回其ASC-11码,如HEX('DEF')返回4142143 
也可以传入十进制整数,返回其十六进制编码,如HEX(25)返回19 
LEAST (number , number2 [,..]) //求最小值 
MOD (numerator ,denominator ) //求余 
POWER (number ,power ) //求指数 
RAND([seed]) //随机数 
ROUND (number [,decimals ]) //四舍五入,decimals为小数位数] 
注:返回类型并非均为整数,如下文: 
SIGN (number2 ) //

日期时间类

ADDTIME (date2 ,time_interval ) //将time_interval加到date2 
CONVERT_TZ (datetime2 ,fromTZ ,toTZ ) //转换时区 
CURRENT_DATE ( ) //当前日期 
CURRENT_TIME ( ) //当前时间 
CURRENT_TIMESTAMP ( ) //当前时间戳 
DATE (datetime ) //返回datetime的日期部分 
DATE_ADD (date2 , INTERVAL d_value d_type ) //在date2中加上日期或时间 
DATE_FORMAT (datetime ,FormatCodes ) //使用formatcodes格式显示datetime 
DATE_SUB (date2 , INTERVAL d_value d_type ) //在date2上减去一个时间 
DATEDIFF (date1 ,date2 ) //两个日期差 
DAY (date ) //返回日期的天 
DAYNAME (date ) //英文星期 
DAYOFWEEK (date ) //星期(1-7) ,1为星期天 
DAYOFYEAR (date ) //一年中的第几天 
EXTRACT (interval_name FROM date ) //从date中提取日期的指定部分 
MAKEDATE (year ,day ) //给出年及年中的第几天,生成日期串 
MAKETIME (hour ,minute ,second ) //生成时间串 
MONTHNAME (date ) //英文月份名 
NOW ( ) //当前时间 
SEC_TO_TIME (seconds ) //秒数转成时间 
STR_TO_DATE (string ,format ) //字串转成时间,以format格式显示 
TIMEDIFF (datetime1 ,datetime2 ) //两个时间差 
TIME_TO_SEC (time ) //时间转秒数] 
WEEK (date_time [,start_of_week ]) //第几周 
YEAR (datetime ) //年份 
DAYOFMONTH(datetime) //月的第几天 
HOUR(datetime) //小时 
LAST_DAY(date) //date的月的最后日期 
MICROSECOND(datetime) //微秒 
MONTH(datetime) //月 
MINUTE(datetime) //分返回符号,正负或0 
SQRT(number2) //开平方

游标

定义:游动的标识,相对于普通的一次性查询给出所有结果;游标的作用就是对数据样本中一条一条分析处理,像个指针。
使用:
1. 声明: declare 游标名 cursor for select_statement;
2. 打开: open 游标名
3. 取值: fetch 游标名 into var1,var2[,…]
4. 关闭:close 游标名;

事务

Mysql事务主要用于处理操作量大,复杂度高的数据。例如:当你删除一样东西时,你得把它自身及所依赖的东西都要删除。所有这些操作行为形成一个事务。
注意
- MYSQL中:只有Innodb数据库引擎的数据库或表才支持事务
- 事务处理用来维护数据库完整性即保证批量SQL语句全部执行或者全部不执行
- 事务用来管理insert,update,delete语句
事务满足4个条件:
1. 事务的原子性:要么成功,要么失败
2. 稳定性: 有非法数据,事务撤回
3. 隔离性: 事务独立运行
4. 可靠性:当发生奔溃,InnoDB数据表驱动会利用日志文件重构修改
参考博客事务

导入导出

use 数据库;
source d:/dbname.sql;

性能优化

查询实例

数据库题:学生表,选课表,课程表 设教学数据库中有三个基本表:
学生表 Student(Sno,Sname,Age,Sex),其属性表示学生的学号、姓名、年龄和性别;
选课表 SC(Sno,Cno,score),其属性表示学生的学号、所学课程的课程号和成绩;
课程表 Course(Cno,Cname,Tho),其属性表示课程号、课程名称和任课教师姓名;
教师表 Teacher (Tno,Tname),其属性表示教师号、教师名称;
下面的题目都是针对上述三个基本表操作的。
*导入sql文件:source  course.sql;  sql文件编码格式:无BOM的UTF-8

drop database IF EXISTS db_school;
CREATE database db_school;
use db_school;
DROP TABLE IF EXISTS `db_school`.`Student`; 
create table Student  
(  
    Sno varchar(20),  
    Sname varchar(50), 
    Age smallint,
    Sex varchar(5),
    primary key (Sno)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

DROP TABLE IF EXISTS `db_school`.`Course`;  
create table Course  
(  
    Cno varchar(20),  
    Cname varchar(50),    
    Tno varchar(20),  
    primary key (Cno)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
DROP TABLE IF EXISTS `db_school`.`SC`;  
create table SC  
(  
    Sno varchar(20),  
    Cno varchar(20),      
    score int,  
    primary key (Sno,Cno)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  

DROP TABLE IF EXISTS `db_school`.`Teacher`;  
create table Teacher  
(  
    Tno varchar(20),  
    Tname varchar(50),    
    primary key (Tno)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;  
INSERT INTO `Student`(Sno,Sname,Age,Sex) VALUES ('001','陈一',25,'nan');  
INSERT INTO `Student`(Sno,Sname,Age,Sex) VALUES ('002','郭二',20,'nv');  
INSERT INTO `Student`(Sno,Sname,Age,Sex) VALUES ('003','张三',25,'nv');  
INSERT INTO `Student`(Sno,Sname,Age,Sex) VALUES ('004','李四',22,'nan');  
INSERT INTO `Student`(Sno,Sname,Age,Sex) VALUES ('005','王五',23,'nan');  
INSERT INTO `Teacher`(Tno,Tname) VALUES ('001','张老师');  
INSERT INTO `Teacher`(Tno,Tname) VALUES ('002','王老师');  
INSERT INTO `Teacher`(Tno,Tname) VALUES ('003','钱老师');  
INSERT INTO `Teacher`(Tno,Tname) VALUES ('004','刘老师');  
INSERT INTO `Teacher`(Tno,Tname) VALUES ('005','胡老师');  
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('001','语文','张老师');  
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('002','数学','王老师');  
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('003','英语','钱老师');  
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('004','物理','刘老师');  
INSERT INTO `Course`(Cno,Cname,Tno) VALUES ('005','政治','胡老师');  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','001',50);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','002',60);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','003',70);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','004',80);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('001','005',90);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','001',90);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','002',80);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','003',70);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','004',60);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('002','005',50);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','001',81);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','002',82);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','003',83);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','004',84);  
INSERT INTO `SC`(Sno,Cno,score) VALUES ('003','005',85);
select Cno  from Student,SC where Student.Sno=SC.Sno AND Student.Sex='nv';
# 先创建SC_C表,自行创建insert into SC_C select SC.Cno,Cname,AVG(score) AS Avg_score FROM Course,SC WHERE Course.Cno=SC.Cno GROUP BY SC.Cno;
delete from SC where Sno in (select Sno from Student where Sex='nv') AND Cno in (select Cno from Course where Tno='王老师');
select a.Sno from (select Sno,score FROM SC where Cno='001') AS a ,(select Sno,score FROM SC where Cno='002') AS  b  WHERE a.score > b.score;//当两个表存在相同列名时,用tablename.columnname指定列
select Student.Sno,Student.Sname,count(SC.Cno),sum(SC.score) from Student left OUTER join SC on Student.Sno=SC.Sno group by Student.Sno,Student.Sname;

以上是“MYSQL基础语法示例”这篇文章的所有内容,感谢各位的阅读!相信大家都有了一定的了解,希望分享的内容对大家有所帮助,如果还想学习更多知识,欢迎关注亿速云行业资讯频道!

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯