数据库基本操作。

建库，选择库
create database pygodnet;
use pygodnet;

删库
drop database pygodnet;

建表，删表
create table user(userid int(11),username varchar(32),password char(32));
drop table user;

改表名
alter table user rename users;

表字段改名
alter table user change userid id int(11);

修改字段类型与位置
alter table user modify username char(32) first;

添加删除表字段
alter table user add column password char(64) after username;
alter table user drop column sex;

create创建索引，create 不能创建主键索引，注意与alter的区别
create index in_id on t1(id);

alter创建索引，create 不能创建主键索引，注意与alter的区别
alter table users add unique(字段名 );
#fulltext(字段名 );primary key(字段名 );index(字段名)

删除索引
alter table users drop index username on t1;

删除主键索引，先取消递增
alter table users modify id int(11) unsigned not null;
alter table users drop primary key;

插入数据方法一，所有字段，顺序一致
insert into users values(1,“haha”,“adfdsfadsf12”),(2,“xixi”,“afdsf123321”);

插入数据方法二，设置了递增的字段可不操作
insert into users(id,username,password) values(3,“hehe”,“12333adf123”);

替换列数据
update scores set nu=replace(nu,1,2);

去重
select distinct balance from money;

排序，desc降序，asc升序。
select * from money where age>30 order by balance desc,age asc limit 2 ,5;
asc升序；

分组，group by
select count(province),province from money group by province;

条件筛选group by
select count(province) as result,province from money group by province with rollup;

条件筛选group by
select count(province) as result,province from money group by province having result>1;

表联合查询
select user.username as ‘用户名’,order_goods.oid,order_goods.name as “商品名” from user,order_goods where user.uid=order_goods.uid;

表联合查询2
select user.username as ‘用户’,order_goods.name as “商品” from user inner join order_goods on user.uid=order_goods.uid;

left join 语法
select * from user left join order_goods on user.uid=order_goods.uid;

查询
select * from user where uid in(select uid from order_goods);
select uid from user union all select uid from order_goods;

更新数据update
update user set username=“哈哈”,password=“123456” where uid=8;

同时对两个表更新
update money m,users u set m.balance=m.balance*u.id,u.password=m.balance where m.id=u.id;

delete from，累加id从原来的基础上累加，如1，2，3，将id=2删除了,id=3不变！
delete from money where id>9;
delete from 表名；

truncate清空表以后 id 从1 开始
truncate table users;

数据库远程连接,注意符号的使用
grant 权限 on 库名.表名 to ‘用户名’@‘ip地址’ identified by 密码；
grant all on . to ‘root’@‘localhost’ identified by ‘123456’ with grant option;
grant select,insert on class1824. to ‘jinxingping’@‘localhost’ identified by ‘123321’;
flush privileges;
service mysql restart；

删除指定权限
revoke insert on class1824.* from ‘jinxingping’@‘localhost’;

复制表结构和数据
create table user1 like users；
insert into user1 select * from users；

表视图 view
create view v_t1 as select * from t1 where id>2 and id<8; show create/drop view v_t1;

mysql常用内置函数：
mysql 显示内容 用 select
字符串函数
mysql> select concat(“hello”,“world”) as nihao; #拼接字符串
select lcase(“MYSQL”);#转小写
select ucase(“mysql”); #转大写
mysql> select length(“haha”); 字符串的长度
mysql> select ltrim(" haha"); 去除左侧空格
±------------------+
| ltrim(" haha") |
±------------------+
| haha |
±------------------+
1 row in set (0.00 sec)

mysql> select rtrim(" haha ");去除右侧空格
mysql> select repeat(“重要的事情说三遍”,6);
select replace(“java 是世界上最好的语言”,“java”,“python”);
mysql> select substr(“python is so good”,2,7); 从第二个字符开始 截取7个 mysql 从1开始
mysql> select concat(space(20),‘haha’); #生成20个空格
数学函数
mysql> select ceiling(123.1);
mysql> select floor(123.1);
mysql> select rand(); 求随机数 0-1之间的小数
mysql> select bin(10);

日期函数
mysql> select curdate(); 返回日期 2018-7-25
mysql> select curtime(); 11:21:33
mysql> select now(); 日期+时间 2018-07-25 11:22:14
mysql> select week(now()); 返回29 表示当前第几周
mysql> select year(now()); 返回2018
mysql> select unix_timestamp(now()); 当前时间的时间戳
mysql> select from_unixtime(1532489069);
±--------------------------+
| from_unixtime(1532489069) |
±--------------------------+
| 2018-07-25 11:24:29 |
±--------------------------+ 将当前的时间戳格式化为日期

转载自https://blog.csdn.net/pygodnet/article/details/82810885?tdsourcetag=s_pcqq_aiomsg