MySQL基础语句

数据库语句

1.要求服务器告诉您它的版本号和当前日期。

select version(),current_date;

2.使用该SHOW语句查找服务器上当前存在的数据库： 如果您没有该SHOW DATABASES 权限，则不会显示您没有权限的数据库

show databases;

3.创建数据库

create database 数据库名;

4.选择数据库

use 数据库名

5.查询数据库详情和字符集

show create database 数据库名;

6.创建数据库指定字符集

create database 数据库名 character set gbk/utf8;

7.删除数据库

drop database 数据库名

表相关

1. 创建表

create table表名（字段1名，字段1类型，字段2名，字段2类型……）；

2. 查询所有表

show tables;

3. 查询表详情

show create table 表名;

4. 创建表指定引擎和字符集

create table 表名（字段1名，字段1类型，字段2名，字段2类型…）engine=innobd/myisam charset=gbk/utf8;

5. 查看表结构

desc 表名；

6. 删除表

drop table 表名；

修改表相关

1. 修改表名

Rename table 原名to新名;

2. 修改引擎和字符集

Alter table 表名 engine=myisam/innodb charset=gbk/utf8;

3. 添加表字段
   （1） 最后添加：

alter table 表名 add 字段名 类型;

（2） 最前面添加：

alter table 表名 add 字段名 类型 first;

（3） 某字段后面添加：

alter table 表名 add 字段名 类型 after 字段名;

4. 删除表字段

Akter table 表名drop 字段名；

5. 修改表字段名称和类型

alter table 表名 change 原字段名 新字段名 新类型;

6. 修改字段类型和位置

Alter table 表名 modify 字段名 类型 first/after(字段名);

数据相关

1. 插入数据
   （1） 全表插入格式：

insert into 表名 values(值1，值2…);

（2） 指定字段格式：

insert into 表名（字段1名，字段2名）values（值1，值2…）;

（3） 批量插入数据：

insert into 表名 values(‘’, ),(‘’, )…;

insert into (name) values (‘’),(‘’)…;

2. 查询所有字段

select * from 表名;

3. 删除数据：

Delete from 表名 (where 条件)；不加条件删除所有

4. 修改格式

Update 表名 set 修改条件 where 查询条件；

5. 创建主键

Create table 表名(id int primary key auto_increment);

6. 注释：

comment

格式：

create table 表名(id int primary key auto_increment comment’注释内容’);

查看方式：

showcreate table 表名;

事务

1. 查看数据库自动提交状态

Show variables like ‘%autocommit%’;

2. 关闭和打开自动提交

Set autocommot=0/1;(0关闭，1开启)；

3. 手动提交：

commit

4. 回滚：

rollback

回滚到某个回滚点：

rollback to 标识

5. 设置回滚点：savepoint标识

运算符

比较运算符：“<=”, ”=”, ”!=”/”<>”（不等于）

查询关键字

1. 别名：as

例：select name as ‘姓名’ from emp;

2. and和or:与java中&&和||效果一样；

例：select * from 表名 where id=1 or id=2 and age=20;

3. in:查询某个范围内的数据

select * from where field in (value1,value2,value3,…);

4. between x and y:在x和y之间包含xy
5. 模糊查询：

Like：代表未知字符 %代表0或多个位置字符；
例：倒数第三个字符是a：%a _;

6. 排序：

Order by 如果有条件写在条件的后面，没条件写在表名的后面，默认是升序：desc降序，asc：升序
例：order by price,age desc;

7. 分页查询

limit x,y:第一个参数代表跳过的条数，第二个参数代表每页的数量，通常写在sql语句最后面

8. 数值计算±*/ 5%3等效mod(5,3);

例：查询所有员工姓名，工资以及年终奖（工资5）
Select name,sal,sal5 from emp;

9. ifnull(x,y)函数

例：age=ifnull(x,y) 如果x的值为null则赋值y，如果不为null,则赋值x

10. 聚合函数：对多行数据进行统计

（1） 求和：sum(求和的字段名) （2） 平均值：avg（字段名） （3） 最大值：max（字段名） （4） 最小值：min（字段名）
（5） 统计数量：count（字段名/*）

11. 日期相关函数

（1） 获取当前的年月日时分秒 now(); select now();

（2） 获取当前的年月日 current();

（3） 获取时分秒curtime();

（4） 从年月日时分秒中提取年月日，提取时分秒: select date (now()); Select time
(now());

（5） 提取时间分量年月日时分秒 Select extract(year from now()); Select extract(hour
from now());

（6） 日期格式化函数： 格式：date_format(日期，format)；

Formet：

a. %Y四位年 %y两位年

b. %m两位月 %c一位月

c. %d 号

d. %H 24小时制 %h 12小时

e. %i分

f. %s秒

例：把now()格式换成年月日时分秒 Select date_format(now(),’%Y年%m月%d日%H时%i分%s秒’);

12. 数字相关函数

Floor（num）向下取整

Select rand()随机数0-1

Round（num,m）四舍五入，m小数位数

Round（num）四舍五入

Truncate(num,m)非四舍五入

13. 分组查询：分组查询通常和聚合函数结合使用（group by）

存在位置：select * from emp where … group by … order by … limit…;

14. having: 使用having解决聚合函数的条件过滤问题，having写在group by 后面，where后面写普通字段的过滤条件，having后面写聚合函数的过滤条件；
    having要和group by结合使用
    子查询（嵌套查询）
    将两条合并成一条sql
    例：select customer_id,order_id from table2 where customer_id in (select customer_id from table1 where city=‘hangzhou’);

关联查询

1. 同时查询多张表的数据称为关联查询

例：查询每一个员工的姓名和对应的部门名称： select e.name, d.name from 表名 e join dept d where
on e.deptno=d.deptno;

2. 等值链接和内连接

等值：select * from A,B where A.x=B.x and A.age=18;

内连接：select * from A join B on A.x = B.x where A.age=18;

3. 外连接

左外连接：以join 左边表为主表，左边显示所有数据右边交集数据
select e.ename,d.dname from emp e left join dept d on e.deptno=d.deptno；

右外连接：以join 右边表为主表，右边显示所有数据左边交集数据

select e.ename,d.dname from emp e right join dept d on e.deptno=d.deptno;

4. 字符串

(1) 字符串的拼接：concat(s1,s2); //s1s2

(2) 获取字符串长度：char_length(str);

(3) 获取字符串在另一字符串出现的位置，从1开始

  格式1：instr(ste,substr);		instr(‘abcde’,b);	//2
  格式2：locate(substr,str) 		locate(‘b’,’abcde’);	//2

(4) 插入字符串：insert(str,start,length,newstr);

(5) 转大写小写：upper(str) lower(str)

(6) 去空白：trim(str)

(7) 重复： repeat(str,count);

(8) 截取字符串：left(str,start)/right(str,start);

(9) 替换：replace(str,old,new);

(10) 反转：reverse(str);

5. 一对一
   有AB两张表，A表中的一条数据对应B表中的一条数据同时B表一条对应A表一条，这种关系称为一对一
   应用场景：商品表和商品详情表，
   如何建立关系： 在从表中添加外键，外键的值指向主表的主键
   练习：请设计表保存以下数据
   用户名：wukong 密码：123456 昵称：齐天大圣 电话：13733666633 地址：花果山
   用户名：bajie 密码：abcd 昵称：二师兄 电话：13833446622 地址：高老庄
   用户名：libai 密码：aabbcc 昵称：李白 电话：13355668877 地址：语文书里

create table user(id int primary key auto_increment,username varchar(10),password varchar(10));

create table userinfo(userid int,nick varchar(10),tel varchar(15),address varchar(20));

insert into user values(null,‘wukong’,‘123456’),(null,‘bajie’,‘bacd’),(null,‘libai’,‘aabbcc’);

insert into userinfo values(1,‘齐天大圣’,‘13833446622’,‘花果山’),(2,‘二师兄’,‘13833446622’,‘高老庄’),(3,‘李白’,‘13833446622’,‘语文书里’);

完成以下查询：
查询李白的用户名和密码是什么

select u.username,u.password from user u join userinfo ui on u.id=ui.userid where ui.nick=‘李白’;

查询每一个用户的所有信息

select * from user u join userinfo ui on u.id=ui.userid;

查询用户名bajie 的昵称是什么

select ui.nick from user u join userinfo ui on u.id=ui.userid where u.username=‘bajie’;

5. 一对多
   AB两张表，A表中的一条数据对应B表中的多条数据，同时B表中的一条数据对应A表中的一条数据，称为一对多
   应用场景： 员工-部门， 商品-分类
   如何建立关系： 在多的一端添加外键指向另外一张表的主键
   练习：创建表保存以下数据 temp tdept
   悟空 28岁 3000月薪 神仙部 花果山
   刘备 34岁 8000月薪 三国部 蜀国
   路飞 18岁 1000月薪 海贼部 日本
   八戒 30岁 4000月薪 神仙部 花果山

create table temp(empno int primary key autoincrement,ename var char(10),age int,sal int,deptno int);
create table tdept(deptno int primary key autoincrement,dname varchar(10),loc varchar(10));
insert into tdept values(null,‘神仙部’,‘花果山’),(null,‘三国部’,‘蜀国’),(null,‘海贼部’,‘日本’);
insert into temp values(null,‘悟空’,28,3000,1),(null,‘刘备’,34,8000,2),(null,‘路飞’,18,1000,3),(null,‘八戒’,30,4000,1);

做题：
查询每个员工的姓名和部门名

select e.ename,d.dname from temp e join tdept d on e.deptno=d.deptno;

查询工作在花果山的员工姓名及工资

select e.ename,e.sal from temp e join tdept d on e.deptno=d.deptno where d.loc=‘花果山’;

6. 多对多
   AB两张表，A表中一条数据对应B表中多条数据同时B表中一条数据对应A表中多条，称为多对多
   应用场景： 老师-学生 用户-角色
   如何建立关系：需要创建新的关系表，表中添加两个外键，指向两个主表的主键
   练习：创建表保存以下数据
   唐僧的学生有：悟空，八戒
   苍老师的学生有： 八戒，小泽

create table teacher(id int primary key autoincrement,name varchar(10));
create table student(id int primary key autoincrement,name varchar(10));
create table ts(tid int,sid int);
insert into teacher values(null,‘唐僧’),(null,‘苍老师’);
insert into student values(null,‘悟空’),(null,‘八戒’),(null,‘小泽’); insert into ts values(1,1),(1,2),(2,2),(2,3);

查询苍老师的学生姓名

select s.name from teacher t join t_s ts on t.id=ts.tid join student s on ts.sid=s.id where t.name=‘苍老师’;

查询八戒的老师姓名

select t.name from teacher t join t_s ts on t.id=ts.tid join student s on ts.sid=s.id where s.name=‘八戒’

视图

1. 创建视图格式：

Create view 视图名 as 子查询；创建出来一个虚拟的表
Create table 表名 as 子查询; 创建出来一张新表

2. 分类
   2.1 简单视图：创建视图的子查询中不包含：去重，函数，分组，关联查询的视图；简单视图可以对表中数据进行增删改查操作。
   2.2 复杂视图：和简单视图相反，复杂视图只能进行查询；
3. 对简单视图进行增删改查操作方式和table一样
   插入数据 insert into vemp10 (empno,ename) values(10010,‘Tom’);
   数据污染：往视图中插入一条视图中不可见，但是原表中存在的数据称为数据污染。
   通过 with check option 解决数据污染问题

例：create view vemp as (select * from emp where deptno=20) with check option;

4. 修改视图

create or replace view vemp as (select * from emp where deptno=20 and sal>2000);

5. 视图别名：

如果创建视图的子查询中使用别名，则对视图进行增删改查时只能使用别名
create view v_emp as (select ename name from emp); 此时在视图中只能使用name 使用ename则报错

索引

1. 创建索引：

格式：create index 索引名 on 表名 (字段(字段长度))；

2. 查看索引：

Show index from item2;
只要是给表添加主键约束，则数据库会为此表自动创建主键字段的索引。

3. 删除索引：

Drop index 索引名 on 表名；

4. 复合索引：通过多个字段创建的索引称为复合索引

格式：create index 索引名 on 表名(字段1,字段2);