mysql基础1

1.sql语句不区分大小写,但是字符串常量区分大小写,建议命令大写,表名库名小写

2.sql语句可多行或单行书写,以‘;’结尾,关键词不能跨多行或简写

3.子语句位于独立行,便于编辑,提高可读性

4.注释:单行注释: --
多行注释: /*.....*/

5.DDL:定义语句
DML:操作语句
DCL:控制语句
------------------------------
创建数据库:
create database [if not exists] 库名;(在磁盘上创建一个对应的文件夹)
create database [if not exists] 库名 character set gbk; /*设置文件格式*/

删库跑路:
drop databases 库名;

查看所有库名:
show databates;

查看错误信息:
show warnings;

查看创建信息:
show create database 库名;

更改数据库信息:
alter database 库名;

-----------------------------
进入并使用数据库:
use 库名;

检测进入了哪个数据库:
select database();

-----------------------------
创建表(类似于一个excle表):
create table tab_name;
CREATE TABLE employee(
id TINYINT PRIMARY KEY auto_increment,/*PRIMARY KEY是约束性条件,主键约束,造成的影响是不能为空,是惟一的,
id号就是必须有值,且不能重复,否则报错,还有一个约束性条件是auto_increment,id号自动自增*/
name VARCHAR (25),
gender boolean,
age INT,
department VARCHAR (20),
salary DOUBLE (7,2)
)

---------------------------
查看表结构:
desc tab_name;
show columns from tab_name;

查看当前数据中所有的表:
show tables;

查看当前数据库表建表语句:
show create table tab_name;
---------------------------
修改表结构:
增加列,字段:
alter table employee add is_married tinyint(1)
alter table employee add entry_date date not null;
alter table employee add A INT,
add B VARCHAR(20);

删除字段:
alter table employee DROP A;
alter table employee DROP entry_date,
DROP B;

修改列类型:
alter table employee MODIFY age SMALLINT not null default 18 after name;

修改列名:
alter table employee CHANGE department depart VARCHAR(20) after salary;

修改表名:
rename table employee to emp;

-----------------------------
表记录值增删改
插入数据:
insert into emp (id,age,name,gender,salary,depart,is_marride)
values(1,38,"alex",0,1700,"技术部",1);
插入多条数据:
insert into emp (age,name,salary,depart)
values(20,"bles",30000,"技术部"),
(22,"clex",5000,"销售部");
另一种插入方式:
INSERT INTO emp SET name = "dlex",age = 24;
----------
修改表记录:
update emp set salary = salary + 2000 where name = "clex";
查看表内容:
select * from 表名; *表示所有字段
----------
删除表记录:
DELETE FROM tab_name [where ....]
DELETE FROM tab_name WHERE id = 3 or id = 4;
删除表:
DROP TABLE table_name ;
----------
表记录查询:
SELECT [distinct] name FROM examresult;/*[distinct]表示去重*/
SELECT name,JS,Django,flask FROM ExamResult;
SELECT name,JS+10,Django+10,flask+20 FROM ExamResult;/*加了数值以后,数据库中的值不会改变,只显示改变后的值*/
SELECT name as "姓名",JS+10 as JS成绩,Django+10,flask+20 FROM ExamResult;/*同上,制作显示用,不存储*/
SELECT name,JS FROM ExamResult WHERE JS > 90;
-------
使用正则表达式查询
select * from employee where emp_name REGEXP "^yu"; 匹配开头
select * from employee where emp_name REGEXP "yu$"; 匹配结尾
select * from employee where emp_name REGEXP "m{2}"; 匹配2个m
-----
where字句中可以使用:
比较运算符:
> < >= <= <> !=
between 80 and 100 值在80到100之间
in(10,20,30) 值是10,20,30
like "杨%" 模糊匹配,%可以匹配多个字符,如杨洋,杨阳洋,_只能匹配一个杨洋
SELECT name,JS FROM examresult where JS between 80 and 100;
SELECT name,JS FROM examresult where JS in(80,90,100);
SELECT name,JS FROM examresult where JS like "8%";
SELECT name,JS FROM examresult where JS is NULL;

------*******--------
分组查询:group by 按分组条件分组后,每组只会显示第一条记录
select * from examresult group by name;等同于select * from examresult group by 2;这个2表示字段2,也就是name,只显示重复的名字的第一个
select name, sum(JS) from examresult group by name;按名字分组,把名字重复的JS的值加起来

having也是和where一样是过滤用的,只不过必须得放在group by 后面,where是分组之前的过滤,使用where的地方都可以用having进行替换,
having可以用聚合函数(sum()之类的),where就不行
select name, sum(JS) from examresult group by name having sum(JS) > 150;

------*******---------
聚合函数:
count(列名):统计
select count(*) from examresult;统计总人数
select count(JS) from examresult where JS > 80;统计JS大于80 的人数
select count(name) from examresult where (ifnull(JS,0)+ifnull(django,0)+ifnull(flask,0)) >280;如果JS为null,则JS==0

AVG(列名):求平均值
select AVG(ifnull(JS,0)) from examresult;求JS平均分
select AVG(ifnull(JS,0)+ifnull(django,0)+ifnull(flask,0)) from examresult;求总分平均分

Max(),Min()
select max(js) from examresult;
select min(js) from examresult;
select min(ifnull(js,0)) from examresult;
select max(js+django+flask) from examresult;

------*******-----------
limit:限制
select * from examresult limit 3;取前三条记录
select * from examresult limit 1,4;从第2条开始,取4条
-----------------------------
排序:
select name,JS from examresult order by JS;/*默认从小到大排*/
select name,JS from examresult where JS > 90 order by JS;/*也可以where先过滤*/
select name,JS from examresult where JS > 90 order by JS desc;/*从大到小排*/
select name,JS+Django+flask as 总成绩 from examresult order by 总成绩;
select name,JS+Django+flask as 总成绩 from examresult where name = "zhou"order by 总成绩;

-----------------------------
mysql语句执行顺序:
from > where > select > group by > having > order by > limit
-----------------------------
MySQL中的数据类型:
见图:
float(4,2):最大99.99

char(3):定长字符串,存3个字节的内容

varchar(10):不定长字符串,存最多10个字节的内容

在utf8中一个汉字占3个字节

BLOB:存二进制字符串

TEXT:存长文本字符串

 

 

posted @ 2020-12-10 22:49  一只小羊  阅读(88)  评论(0编辑  收藏  举报