Mysql 常用操作记录
1.查看数据库:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 5 rows in set (0.25 sec)
2.创建数据库,create 数据库名字 default charset 编码格式 collate 字符集;
mysql> create database test_db default charset utf8 collate utf8_general_ci; Query OK, 1 row affected, 2 warnings (0.21 sec)
3:使用数据库
mysql> use test_db; Database changed
4.用户管理:
4.1:创建用户:
mysql> create user 'test'@'localhost' identified by '12345678'; Query OK, 0 rows affected (0.03 sec)
出现错误: The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement
解决:
mysql> flush privileges; Query OK, 0 rows affected (0.05 sec)
4.2删除用户:
mysql> drop user 'test'@'localhost'; Query OK, 0 rows affected (0.11 sec)
4.3修改用户名;
mysql> rename user 'test'@'localhost' to 'new_test'@'localhost'; Query OK, 0 rows affected (0.04 sec)
4.4修改密码
set password for 'name' =password('new_password'); 我mac上的mysql 这个会语法错误，版本是Server version: 8.0.13 MySQL Community Server - GPL,查询后判断为新版本的不支持set password的语法
mysql> alter user 'new_test'@'localhost' identified by '123456'; Query OK, 0 rows affected (0.08 sec)
5.权限相关:
5.1显示用户的权限:
mysql> show grants for 'root'@'localhost'; +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT BACKUP_ADMIN,BINLOG_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SET_USER_ID,SYSTEM_VARIABLES_ADMIN,XA_RECOVER_ADMIN ON *.* TO `root`@`localhost` WITH GRANT OPTION | | GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION | +----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.01 sec)
5.2授权,必须写明表名,如果是所有加上*;
权限需要小于自己的权限，注意host的限制，
mysql> grant select on mysql.* to 'new_test'@'localhost'; Query OK, 0 rows affected (0.07 sec)
all privileges 除grant外的所有权限 select 仅查权限 select,insert 查和插入权限 ... usage 无访问权限 alter 使用alter table alter routine 使用alter procedure和drop procedure create 使用create table create routine 使用create procedure create temporary tables 使用create temporary tables create user 使用create user、drop user、rename user和revoke all privileges create view 使用create view delete 使用delete drop 使用drop table execute 使用call和存储过程 file 使用select into outfile 和 load data infile grant option 使用grant 和 revoke index 使用index insert 使用insert lock tables 使用lock table process 使用show full processlist select 使用select show databases 使用show databases show view 使用show view update 使用update reload 使用flush shutdown 使用mysqladmin shutdown(关闭MySQL) super 使用change master、kill、logs、purge、master和set global。还允许mysqladmin调试登陆 replication client 服务器位置的访问 replication slave 由复制从属使用 对于权限
5.3 取消权限 from user:
mysql> revoke select on mysql.* from 'new_test'@'localhost'; Query OK, 0 rows affected (0.10 sec)
5.4对于用户@ip:
用户名@IP地址 用户只能在改IP下才能访问 用户名@192.168.1.% 用户只能在改IP段下才能访问(通配符%表示任意) 用户名@% 用户可以再任意IP下访问(默认IP地址为%)
# 启动免授权服务端 mysqld --skip-grant-tables # 客户端 mysql -u root -p # 修改用户名密码 update mysql.user set authentication_string=password('666') where user='root'; flush privileges; 忘记密码
6.表相关:
6.1增加表:
crate table 表名字(
列名字 类型 相关参数,
列名字 类型 相关参数
)engine=引擎 default 编码格式;
create table student( age int not null, name varchar(20) not null)engine=innodb default charset=utf8; #注意最后一个列没有',' Query OK, 0 rows affected, 1 warning (0.10 sec)
6.2查看引擎:
mysql> show engines; +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ | ARCHIVE | YES | Archive storage engine | NO | NO | NO | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | | InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | | CSV | YES | CSV storage engine | NO | NO | NO | +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 9 rows in set (0.01 sec)
6.3参数条件:
mysql> create table test1(name varchar(20) not null);#是否为空 Query OK, 0 rows affected (0.14 sec)
mysql> create table test2(name varchar(20) default 'sb');#默认值 Query OK, 0 rows affected (0.05 sec)
mysql> create table test3(name varchar(20) comment '姓名'); #添加备注 Query OK, 0 rows affected (0.07 sec) mysql> show full columns from test3; +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | Field | Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ | name | varchar(20) | utf8_general_ci | YES | | NULL | | select,insert,update,references | 姓名 | +-------+-------------+-----------------+------+-----+---------+-------+---------------------------------+---------+ 1 row in set (0.00 sec)
mysql> create table test4(nid int primary key,name varchar(20) comment '姓名'); #设置主键 Query OK, 0 rows affected (0.11 sec)
mysql> create table test5(uid int primary key,num int not null, constraint fk_cc foreign key(num) references test4(nid)); #外键 Query OK, 0 rows affected (0.13 sec)
#constraint 外键名字 foreign key(外键列名字) reference 外键关联表(该表的某列);外键字段先存在,再声明,主表从表column类型需要一致.不然会报:can not add foreign key;错误.
6.4 删除表:
drop table name;
mysql> drop table test5; Query OK, 0 rows affected (0.18 sec)
6.5 清空
delete from 表名，删除行,不删除定义,不释放空间
mysql> delete from test4; Query OK, 0 rows affected (0.04 sec)
truncate 表名,删除行,不删除定义,释放空间,速度相对快
mysql> truncate test3; Query OK, 0 rows affected (0.09 sec)
都不会清空主键列
6.6修改表
#删除列:alter table 表名 drop column 列名字;
mysql> alter table student drop age;
Query OK, 0 rows affected (0.16 sec) Records: 0 Duplicates: 0 Warnings: 0
#添加列:alter table 表名 add 列名 类型 参数;
mysql> alter table student add age int not null default 11 ;
Query OK, 0 rows affected (0.13 sec) Records: 0 Duplicates: 0 Warnings: 0
#添加多列:alter table 表名 add (列名 类型 参数,列名 类型 参数...);
#修改alter table 表名 modify column 列名字 修改后的类型;--用于修改类型
mysql> alter table student modify column age int; Query OK, 0 rows affected (0.08 sec) Records: 0 Duplicates: 0 Warnings: 0
#修改alter table 表名 change 原来的列名字 新名字 新类型;--用于修改名字和类型
mysql> alter table student change column age sex tinyint(1) ; mysql 没有bool类型,change 必须表明新字段的类型，也就是必须写类型 Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0
添加主键:alter table 表名 add primary key(字段名字); 字段名字必须 删除主键:alter table 表明 drop primary key; 添加外键:alter table 表名 add constraint fk_t1_t2 foreign key(列名字) references 主表名字(主表列名字); 删除外键:alter table drop foreign key
6.7显示表的详细信息
desc table_name
mysql> desc test1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | name | varchar(20) | NO | | NULL | | +-------+-------------+------+-----+---------+-------+ 1 row in set (0.00 sec)
7.表内级别操作:
增:
insert into 表名 (列名字,列名字) values (值,值) ,(值,值) 或者值是一个从其他表查询出来的字段 :
mysql> insert into student (name,age) values('ling','21'),('jing','22'),('song','24'); Query OK, 3 rows affected (0.03 sec) Records: 3 Duplicates: 0 Warnings: 0
insert into 表名(列名字,列名字) values select (列名,列名)" from xxx; 只要类型个数匹配就可以:
删:
delete from 表 where 条件:
mysql> delete from student where age=24; Query OK, 1 row affected (0.04 sec)
改:
updata 表名 set 字段名字=值 where 条件:
mysql> update student set age=25 where name='ling'; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0
查询:
select * from 表名字 where 条件;
select 字段名字 from 表名字;
查询出来的结果也是一张表, 可以设置其column 名字 或者表名字,as;
条件: where = != in not in and or between in(select * from ) 通配符: % 通配任意字符 多次 _ 通配任意字符一次 排序: select * from 表 oreder by 字段 asc select * from 表 order by 字段 desc select * from 表 order by 字段 desc/asc, 字段 desc/asc.... 当前一个条件有重复时按照第二个条件排序. 分页取值: select * from 表名 limit 每页的个数 offset 起始的位置; 或者简写 select * from 表名 limit起始位置, 个数. 分组: select * from 表名 group by 字段名字; 需要结果的字段往往需要聚合函数支持, 如果需要聚合结果作为条件 ,将where 改成having , group by 必须在条件前面, 排序后面.
分组实列: 查找最大成绩都大于60分 的学生:
组合: 可以让2个查询结果组合起来 ,个数和类型必须一致, 默认自动去重 加上all 不去重:
select nid from 表 union select cid from 表 默认去重
select nid from 表 union all select cid from 表 显示所有
***链表操作:
select 列名字 from 表1,表2 where 表1.xxx=表2.xxxxx;
select 列名字(前面加表名字, 比如 表1.columnxx) from 表1 left join 表 2 on 表1.xxx=表2.xxxx;
