MySQL数据库基本操作
库
登录:mysql -u root -p root -P 3306 -h 127.0.0.1,常用(mysql -u root)
退出:exit;
、quit;
、\q
注释:#xxx
、"--xxx"
、/*xxx*/
增删改查
creat database db1;
drop database db1;
alter database db1 charset utf8;
show databases; show creat database db1;
设置默认编码:配置文件character_set_server = utf8
切换数据库:use db2;
重命名:rename database oldname to new name;
显示特定的系统资源:show status;
显示所有表:show tables;
查询mysql数据库中user表下root的密码
select password from mysql.user where user=‘root’;
表
显示表结构(不显示外键):desc database_name.table_name; describe
新建表:
create table table_name (
属性名 数据类型 [完整的约束条件],
属性名 数据类型 [完整的约束条件],
. . .
属性名 数据类型 [完整的约束条件],
);
if not exits 表示当相同表名存在时,不执行创建语句。
create table user(id int (7) auto_increment,
username varchar(100) not null,
password varchar(100) not null,
primary key (id)
)engine = innodb default charset=utf8;
auto_incremen 自动增长
engine 表的引擎和字符类型,常见数据库引擎 innoodb myisam
数据库的数据类型:https://www.runoob.com/mysql/mysql-data-types.html
约束条件 :
primary key(id) 标识属性id为表的主键,可以唯一标识对应的元组。
foreign key 标识为外键,是与之联系的表的主键
not null 属性不为空
unique 标识该属性的值是唯一的
auto_increment 表示属性值是自动增加的
default 为该属性设置默认值
删除表:
drop table table_name;
修改表:
修改表名:alter table old_tablename rename new_tablename;
修改字段数据结构:alert table_name modify 属性名 数据类型;
修改字段名:alert table table_name change old_tablename new_tablename;
增加字段名:alert table table_name add 属性1 数据类型 [完整性约束条件];
删除字段:alter tabel table_name drop 属性名;
更改表存储引擎:alter table table_name engine = 存储引擎名;
数据
增
添加数据
insret into 表名 (字段名1,字段名2…) values (值1,值2,…)
insert into users (id,username,password) values (1,‘rick’,‘123456’);
或者不指定字段名
insert into users values (1,‘rick’,‘123456’);
还可以
insert into users (username) values (‘lcz’);
还能
insert into users set username = ‘xxx’;
同时添加多条数据,与上同理
insert into user[(id,username,password)] values (2,‘asd’,‘1234’),(3,‘qwe’,‘12345’);
删
删除指定数据
delete from user where id=1;
删除全部数据
delete from user;
truncate(截断)删除
truncate table user;
delete与truncate区别:自动增长的id用truncate回刷新,delete继续增长,最好用truncate
改
更改部分数据
uodate user set username = ‘ri’,password=‘asdasd’ where id = 2;
更新全部数据
update user set pasword=‘123456’;
查
select * from user;
select * from user where id=1;
select username from user where id=1;
select username from user where id in (1,2,3);
select username from user where id between 1 and 3;
空值查询
select username from user where username is null;
过滤重复distinct(截然不同的)
select distinct username,password from user;
模糊查询
select id,username from user where username like “%s”; //以s开头
排序
select * from user order by id desc;//降序
高级查询
聚合查询
select count(id) from user;//返回行数
select sum(id) from user;//返回某列的和
select avg(id) from user;//返回某列平均值
select max(id) from user;//返回最大值
select min(id) from user;//返回最小值
分组查询
select * from user group by id;
与聚合查询结合
select count(*) from user group by id;
having应用
select max(length(username)) from user group by id having id > 1;
limit限制结果数量
select count(*) from user group by id limit 1;
取别名
select username from user as uu where uu.id = 1;
select username as uuee from user where id =1;
子查询
where型
select * from user where id > (select id from user where username = ‘rick’);
from型
select * from (select * from user where id >= 1) as uuee where id > 1;
exists型,后面成立前面才执行
select * from user where exists (select usernamefrom user where id > 1);
联合查询
union all 保留重复行
union使用时两次查询列数要一致
select * from test.user
union select * from test.author;
内连接和左右连接
内连接,重合部分
select * from test.user as a inner join test.author as b on a.id=b.author_id;
左连接,左表全部加右表重合部分
select * from test.user as a left join test.author as b on a.id=b.author_id;
右连接,右表全部加左表重合部分
select * from test.user as a right join test.author as b on a.id=b.author_id;