MySQL语句详情整理


一、用户管理

1、新建用户
mysql> create user name identified by 'root';
2、更改密码
mysql> set password for name=password('ok');
3、设置用户权限
MySQL> show grants for name; //查看name用户的权限 mysql> grant select on db_name.* to name; //给用户name的db_name数据库所有权限 mysql> revoke select on db_name.*to name; //与grant操作相反,去掉权限

二、对数据库操作

1、查看所有的数据库
mysql> show databases;
2、创建数据库
mysql> create database db_name; //创建名称为db_name的数据库
3、删除数据库
> drop database db_name; //删除名称为db_name的数据库
4、使用数据库
>use db_name; //对数据库操作前必须使用

三、对表操作

1、创建表列的格式:列名+数据类型+属性;
create table student( id int auto_increment primary key, //设置主键 name varchar(50), sex varchar(20), date varchar(50), content varchar(100) )default charset=utf8; //编码
2、显示数据库中的所有表
show tables;
3、查看表的结构
describe student; # 可以简写为desc student;
4、对表名重命名
rename table student to stubents; 或者使用: alter table stubent rename students;
5、设置主键与外键:
  • 主键:
方法1:primary key: create table student( sno char(4) primary key, #设置主键(单字段主键) sname char(10), sage int, ssex char(10) );
方法2:primary key (列名) create table course( cno char(4), cname char(8), ccredit int, primary key (cno) #设置主键的第二种方法 );
  • 外键:foreign key(列名) references 父类表(列名)
create table Score ( Sno varchar(20) not null, Cno varchar(20) not null, Degree Decimal(4,1), foreign key(Sno) references student(Sno), #外键 foreign key(Cno) references Course(Cno), primary key(Son,Cno) #设置sno和cno的属性组为主键(即多字段主键,或联合主键) );
  • 自主增删主外键
增删主键:
1、alter table 表名 modify 主键列名 新列类型(不含auto_increment); #删除自增,才能删除主键 2、alter table 表名 drop primary key; #删除主键 3、alter table 表名 add primary key(列名); #添加主键
增删外键:
1、alter table 子表名 add [constraint fk_sno] foreign key(子表的外键名称) references 父表名(父表的主键名称); --添加外键约束。fk_sno为外键ID名,若不添加,系统会自动配一个。 2、alter table 子表名 drop foreign key fk_sno; --删除外键约束。fk_sno为外键ID名, 若不知,可查询建表明细(show create table 表名)。
6、复制表
create table user2 select * from user; #复制表user命名为user2

五、对数据操作

1、插入数据insert into 表名 values(属性);
insert into student values(null,'aa','男','1988-10-2','......'); #给定所有属性数据 insert into student(name, sex, date) values('bb','女','1889-03-6'); #给定部分属性数据 insert into student values (null,'王老师'), (null, '李老师'); #同时插入多条记录
2、删除数据
delete from student where id=5; #删除id为5的数据
3、修改数据update 表名 set 修改内容 where 条件;
update student set age = 20 where id = 2; #修改id为2的学生年龄为20
4、查找数据(方法较多)select 内容 from 表名 (可附属条件);
select name,age ,id from student; #查询数据表student中的所有名字与年龄

六、查询条件关键词用法

1、where用法,where后加条件注:条件符可用 ‘> ‘, ’ =’ , ’ <’ , ’ >=’, <= , != ;
select * from student where age>40; #查询age大于40的学生信息
2、and用法,一般在where后,连接多条件“ 且 ”的关系!两个条件都得满足
select * from student where age>40 and age<60; #小于60且大于40的
3、or用法,一般在where后,连接多条件“ 或 ”的关系!A or B满足A或者B都显示出来
select * from c where age<40 or age>60; #大于60和小于40的都显示
4、between…and…用法,在两者之间
select * from c where age between 40 and 60; 年龄在40到60之间的
5、in用法,在某几个值内的
select * from c where age in (30,48,68,99); #in 查询指定集合内的数据
6、order by排序用法,asc升序(从小到大) des降序(从大到小)
select * from c order by age desc; #将年龄降序排列显示
注:多字段排序 使用,隔开 如下SQL
SELECT * FROM students st ORDER BY st.sAge, st.sGrade DESC;
(1)排序情况预期结果:sAge和sGrade两个字段都按降序排列,但事实并非如此实际结果:sAge按升序排序,sGrade按降序排列MySql中,order by 默认是按升序排列的 所以需改造SQL:
SELECT * FROM students st ORDER BY st.sAge DESC, st.sGrade DESC;
(2) 先后顺序MySql中,order by 多个字段时,按字段先后顺序排优先级。如:
SELECT * FROM students st ORDER BY st.sAge ASC, st.sStuId DESC, st.sGrade DESC;
结果:
(1)在MySql中,使用 ASC 或 DESC 关键字来设置查询结果是按升序或降序排列。 (2)尤其非常特别重要:默认按升序(ASC)排列。 (3)order by 后可加2个(或多个)字段,字段之间用英文逗号隔开。 (4)若A用升序,B用降序,SQL该这样写:order by A ASC, B DESC; 默认同理,也可以这样写:order by A, B DESC; (5)若A、B都用降序,必须用两个DESC,order by A DESC, B DESC; (6)多个字段时,优先级按先后顺序而定。
7、其他:not用法,取反;as 另命名;group by 分组查询;
select name,max(age) from c group by sex; #按性别分组进行,查年龄最大值 select * from c where not age between 40 and 60; #不再40到60之间的 select name as '名字',max(age) as '年龄' from c group by sex; #按性别分组进行,查年龄最大值
like 字符串匹配;is null 要求指定值等于null;
在这里插入代码片
  • 1

七、多表关联查询

https://blog.csdn.net/plg17/article/details/78758593
INNER JOIN(内联):两个表a,b 相连接,取出符合连接条件的字段
LEFT JOIN(左联):先返回左表的所有行,再加上符合连接条件的匹配行
RIGHT JOIN(右联):先返回右表的所有行,再加上符合连接条件的匹配行
1、内连接——最常用定义:仅将两个表中满足连接条件的行组合起来作为结果集。
在内连接中,只有在两个表中匹配的行才能在结果集中出现 :上图中间部分两个都有的数据(第二部分)
关键词:INNER JOIN
格式:SELECT 列名表 FROM 表名1 [INNER] JOIN 表名2 ON或WHERE 条件表达式
说明:(1)列名表中的列名可以出自后面的两个表,但如果两个表中有同名列,应在列名前标明出处,格式为:表名.列名
(2)若连接的两个表名字太长,可以为它们起个别名。 格式为:表名 AS 别名
(3)INNER是默认方式,可以省略
2、左连接定义:在内连接的基础上,还包含左表中所有符合条件的数据行,并在其中的右表列填写NULL,(上图一和二部分)
关键字:LEFT JOIN
注意:
当在内连接查询中加入条件是,无论是将它加入到join子句,还是加入到where子句,其效果是完全一样的,但对于外连接情况
就不同了。
当把条件加入到 join子句时,SQL Server、Informix会返回外连接表的全部行,然后使用指定的条件返回第二个表的行。
3、右连接定义:在内连接的基础上,还包含右表中所有不符合条件的数据行,并在其中的左表列填写NULL,(上图二和三部分)
关键字:RIGHT JOIN

八、批量处理

1、批量插入
insert into USER (SERIAL_NUMBER) select table_tmp.* from ((SELECT ? FROM DUAL union all SELECT ? FROM DUAL union all SELECT ? FROM DUAL . . . )) table_tmp
mybatis写法:
<insert id="insertUserBatch"> insert into USER( SERIAL_NUMBER) select table_tmp.* from( <foreach close=")" collection="userList" index="index" item="item" open="(" separator="union all"> SELECT #{item.SerialNumber,jdbcType=CHAR} FROM DUAL </foreach> ) table_tmp

九、CET的使用(with…as…用法)

WITH AS短语,也叫做子查询部分(subquery factoring),可以定义一个SQL片断,该SQL片断会被整个SQL语句用到。可以使SQL语句的可读性更高,也可以在UNION ALL的不同部分,作为提供数据的部分。
对于UNION ALL,使用WITH AS定义了一个UNION ALL语句,当该片断被调用2次以上,优化器会自动将该WITH AS短语所获取的数据放入一个Temp表中。而提示meterialize则是强制将WITH AS短语的数据放入一个全局临时表中。很多查询通过该方式都可以提高速度。
with cte as( select 1 from dual union all select 2 from dual ) select * from cte;
符攀飞 符攀飞
7 个月前
34

扫描下方二维码手机阅读文章

0