建库,选择库
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