MySQL 语句基础

 

创建用户

Creat user ‘username@localhost’ IDENTIFIED BY '123456';
输入明文密码提示过短需转密文：select password(String pwd)；

赋权：
GRANT ALL ON databasename.tablename TO username@localhost;

创建用户并给定权限
Grant all on databasename.tablename ‘username’@‘localhost’ IDENTIFIED BY ‘root’;

用以上命令授权的用户不能给其它用户授权，如果想让该用户可以授权，用以下命令:
GRANT privileges ON databasename.tablename TO 'username'@'localhost' WITH GRANT OPTION;

操作数据库

创建数据库

create database 数据库名;

删除数据库

drop database 数据库名；

显示数据库

show databases[数据库名];

打开数据库

use databases[数据库名];

操作表

权限属性

1.not null  不能为空       2.default  '男'  默认值

3.unique key  唯一约束不能重复可以为空       4.primary key 主键

5.foreign key 外键       6.auto_increment 自增

创建表

#创建普通表
create table tableName(id  int(10) not null primary key auto_increment,.... )

#创建临时表
CREATE TEMPORARY TABLE tableName select * from....;

#拆分创建表
CREATE TABLE `phoneList`(SELECT `studentName`,`phone` FROM `student`);

查看表

#直接输入
show tables;

删除表

drop table 表名;

修改表名

ALTER TABLE 旧表名 RENAME [TO]  新表名

操作列

添加列

ALTER TABLE 表名 ADD 列名 数据类型[属性];

修改列

ALTER TABLE 表名 CHANGE 原列名 新列名 数据类型 [属性];

修改列属性

ALTER TABLE 表名 MODIFY COLUMN 列名 VARCHAR(30);

删除列

ALTER TABLE 表名 DROP 列名;

操作表数据

插入数据

INSERT into tb_xuesheng(name,age,sex,riqi) VALUES('张三',20,'女','2018-05-30'),('李四',21,'男','2018-05-29')；

修改数据

update tb_xuesheng  set NAME='王五',age=50 where age=20 and name='张三'

#多表联合修改 grade、teacher、subject为表单
UPDATE   
grade gra LEFT JOIN teacher tea on gra.tId=tea.tId LEFT JOIN subject sub ON sub.suName=gra.gSsfx 
SET gra.gName="JavaBase 1" WHERE gid=1;  

删除数据

delete  FROM tb_student where name='张三';

#清空数据，自增为1
TRUNCATE TABLE tb_student;

一般查询

普通查询

select name,age from 表名;

带有条件
select name,age from 表名 where uid=1;

区间查询

select * from 表名 limit 1,2;

联表查询

tb_student 学生表，tb_class 班级表

WHERE联表查询

场景模拟：查询学生表和班级表中的所有信息，展示学生ID、学生姓名和班级名称；

select s.id,s.name,c.name from tb_student s,tb_class s where s.classid=c.id;

联表倾向

基于信息的权重，连表可分为左联、内联、右联。顾名思义左联是以左边的信息为主，右表缺少数据补空，右联亦是如此。内联则是平分权重，左右表信息符合一致时显示，因此使用场景也较为广泛。

左联查询

场景模拟：查询学生、班级表中所有一班学生的信息，此时以学生为重。

Select * from tb_student s left join tb_clss c on s.classid=c.id where c.id=1;

右联查询

场景模拟：查询学生、班级表中所有一班学生的信息，此时以班级为重

Select * from tb_student s right join tb_class c on s.classid=c.id;

内联查询

场景模拟：查询学生、班级表中所有一班学生的信息

Select * from tb_student s inner join tb_class c on s.classid=c.id;

数据排序

可对多个字段进行排序，排序方式有 升序 ASC 和 降序DESC

SELECT * from tableName ORDER BY [排序字段] ASC,[排序字段] ASC;

逻辑语句

条件语句

where 条件作用于任何表，先数据后选择；

having 作用于分组，先选择后数据。

关系运算符

and 与

or 或

条件运算符

=、>=、<=  等于、大于等于、小于等于

<> 、!=、 IS NOT 都代表不等于

IN 在指定范围查询

NOT IN：范围查询、

LIKE：模糊查询  ...where  name like  '%张_%'  下划线代表字符数量，%代表前后字符匹配

条件运算符举例

范围查询
select * from tb_student s where s.classid in (2,3)

模糊查询：此时查询张姓两字
select * from tb_student where name like  '%张%' ；%通配符无差别匹配前后数字，如果要匹配大小写 可以使用LIKE BINARY

存在判断

exists 和 in 是相似的，但exists的效率比in查询要高，因为IN不走索引，但要看实际情况具体使用

IN适合于外表数据量大而内表数据小的情况；EXISTS适合于外表小而内表大的情况。

EXISTS查询
SELECT * FROM cusinfo c WHERE EXISTS(SELECT * FROM cusinfo WHERE c.`name`='李四');

IN查询
SELECT * FROM cusinfo c WHERE EXISTS(SELECT * FROM cusinfo u WHERE u.name='李四');

常用函数

使用举例

求平均值
select avg(age) from tb_student group by sex;

聚合函数

avg() 平均、max() 最

大值、min() 最小值、count() 字段数据统计、sum() 求和

字符串函数

LOWER(name) 小写、UPPER(name) 大写、

截取字符

#截取前五个字符
SELECT SUBSTRING('MySQL SUBSTRING',1,5) ；

拼接字符

#cusinfo 表中的name 字段增加了前后缀
SELECT CONCAT("*",name,"*") FROM cusinfo;

替换字符

#将name中的李替换成王
SELECT REPLACE(name,'李','王') FROM tb_student;

数学函数

CEIL(X) 向上取整

FLOOR(X) 向下取整

RAND(X) 四舍五入

进阶

事务

开启事务 BEGIN 或 START TRANSACTION

中间执行语句

BEGIN 

update bank set money=money-500 where name='张三';
update bank set money=money+500 where name='李四';

COMMIT / ROLLBACK

提交事务 COMMIT

异常回滚 ROLLBACK 回到之前状态

视图

视图是一个虚拟表，其内容由查询定义。创建视图可以灵活的重构不同表之间字段的显示

创建视图：CREATE VIEW view_student[视图名称] as select * from....;
查询视图：select * from view_student；

索引

创建索引的好处：提高表连接速度，数据查询排序速度

创建索引的坏处：存储索引占用磁盘空间，执行数据修改操作产生索引维护

#创建索引
CREATE INDEX indexName on table_name (column_name);
#修改索引
ALTER table tableName ADD INDEX indexName(columnName)
#删除索引
DROP INDEX [indexName] ON mytable; 
#查询索引
show index from tb_chengji；