1、连接mysql数据库:
a、连接localhost:
mysql -u root -p (默认端口号:3306)
b、连接远程mysql:(-h指定远程IP,-u指定用户,-P指定端口号)
mysql -h172.17.162.250 -uappstore -p -P3306
iloveapp2013
2、选择数据库,
a、show databases;
b、use cdo_tag(数据库名称);
3、显示该数据库下的表信息:
a、show tables;
4、展示表结构:
a、desc user(表名称)
5、创建新的数据库
create database databaseName
6、删除数据库
drop database databaseName
7、创建表
create table user(表名)(int id notnull primary key auto_imcrement,string name ,int age)
8、删除表
drop table user(表名)
9、创建索引
create index index1(索引名) from user (表名)
1.添加PRIMARY KEY(主键索引):
ALTER TABLE table_name
ADD PRIMARY KEY ( column
)
2.添加UNIQUE(唯一索引) :
ALTER TABLE table_name
ADD UNIQUE ( column
)
3.添加INDEX(普通索引) :
ALTER TABLE table_name
ADD INDEX index_name ( column
)
4.添加FULLTEXT(全文索引) :
ALTER TABLE table_name
ADD FULLTEXT ( column
)
5.添加多列索引:
ALTER TABLE table_name
ADD INDEX index_name ( column1
, column2
, column3
)
10、查看索引
show index from user(表名)
11、删除索引
drop index index1(索引名)
12、修改表
alter table user (表名)
13、给user表增加一个主键
alter table user add primary key (name)
14.给表增加一个字段
mysql启动:
centos 7:
systemctl start mysqld.service
systemctl status mysqld.service
低版本mysql启动:
service mysql start
其他启动方式:
/etc/rc.d/init.d/mysqld start
mysqld_safe --defaults-file=/etc/my.cnf &
降低mysql密码等级:
set global validate_password_policy=LOW;
其他机器连接mysql:(远程连接,授权.grant)
GRANT ALL PRIVILEGES ON . TO 'root'@'%' IDENTIFIED BY 'aspire-ngboss-cxtc' WITH GRANT OPTION;
更新特定ip可以远程登录权限:update user set host = '特定ip' where user = '特定用户'
刷新权限:
FLUSH PRIVILEGES;
centos 7 安装mysql rpm包时,提示:
file /usr/share/mysql/czech/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.60-1.el7_5.x86_64
先删除依赖:
rpm -e --nodeps mariadb-libs-5.5.60-1.el7_5.x86_64
再重新安装
首次登陆mysql,修改mysql密码:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'aspire-ngboss-cxtc';
方式1:use mysql;
update user set authentication_string=PASSWORD('aspire-ngboss-cxtc') where user='root';
update user set authentication_string=PASSWORD('123456') where user='root';
然后刷新权限:
FLUSH PRIVILEGES;
mysql生成的随机密码有特殊符号:
需要在特殊符号前加,
mysql -uroot -plgc)S9HTHrtd
group_by 需要字段唯一
order_by 数据排序 ,只有order_by后面才加desc /asc
SELECT *
FROM biz_monitor_alert
WHERE customerNum = '721312-08' GROUP BY id ORDER BY updateTime desc LIMIT 0,7