Mysql 基础 Mysql study之常用操作
docker>Evan2019年9月6日 (五) 07:37的版本 (→表操作(创建和插入))
目录
mysql study之常用操作
关闭 启动
本质关闭和启动的最直接就是他们了,启动脚本也是启用他们的 关闭mysql 数据库 mysqladmin --defaults-file=/etc/my.cnf -uroot -p'xx' shutdown 2>&1 killall -15 mysqld 启动 mysqld_safe my.cnf
日常基本操作
CREATE DATABASE youdbname CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; #创建库 #create database dbname default charset=utf8;
SQL DELETE 语句
DELETE FROM 表名称 WHERE 列名称 = 值
参考 http://www.w3school.com.cn/sql/sql_delete.asp
首次记录时间:2016年8月14日 有个哥说介绍一些mysql常用操作,于是有了此文 补充 RDBS, Relation data base system(关系型数据库系统),数据以二维表形式存储,每行(row)代表同一行数据,为记录 每列表示记录中的某个属性,为字段也 不能用 kill -9 因为会 自动重启 mysql service root@debian:/# mysqladmin -pevan shutdown root@debian:/h# ps -ef | grep mysqld root 9950 5490 0 19:11 pts/0 00:00:00 grep mysqld root@debian:# mysqladmin -pevan status mysqladmin: connect to server at ‘localhost’ failed error: ‘Can’t connect to local MySQL server through socket ‘/var/run/mysqld/mysqld.sock’ (2)’ Check that mysqld is running and that the socket: ‘/var/run/mysqld/mysqld.sock’ exists! rm -f /var/run/mysqld/mysqld.sock root@debian:# mysqladmin -pevan status Uptime: 19 Threads: 1 Questions: 2 Slow p628 /etc/init.d/mysqld mysql> show variables like ‘%buffer%’; +——————————+———–+ | Variable_name | Value | +——————————+———–+ | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_instances | 1 | | innodb_buffer_pool_size | 134217728 | | innodb_change_buffering | all | | innodb_log_buffer_size | 8388608 | | join_buffer_size | 131072 | | key_buffer_size | 16777216 | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size | 131072 | | read_rnd_buffer_size | 262144 | | sort_buffer_size | 2097152 | | sql_buffer_result | OFF | +——————————+———–+ mysqladmin #change the root password is 123456 mysqladmin -uroot -p password 123456 evan@debian:~/bakkali/software$ mysqladmin -uroot -p processlist Enter password: +—-+——+———–+—-+———+——+——-+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+——+———–+—-+———+——+——-+——————+ | 53 | root | localhost | | Sleep | 118 | | | | 54 | root | localhost | | Query | 1 | | show processlist | +—-+——+———–+—-+———+——+——-+——————+ 26.5 p639 mysql> show tables; +—————————+ | Tables_in_mysql | +—————————+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | mysql> describe user; +————————+———————————–+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +————————+———————————–+——+—–+———+——-+ | Host | char(60) | NO | PRI | | | | User | char(16) | NO | PRI | | | | Password | char(41) | NO | | | | | Select_priv | enum(‘N’,’Y’) | NO | | N | | | Insert_priv | enum(‘N’,’Y’) | NO | | N | | | Update_priv | enum(‘N’,’Y’) | NO | | N | | #创建表 CREATE TABLE IF NOT EXISTS employees ( # EMPLOYEE_ID varchar(10) not null, #// EMPLOYEE_ID 字段的类型为varchar(10),字段值不允许为空 FIRST_NAME varchar(10) not null, LAST_NAME varchar(10) not null, EMALL varchar(50) not null, HIRE_DATE date, #date 类型 JOB_ID int not null, SALARY int default 0, #//SALARY 字段默认值为0 MANAGER_ID int , DEPARTMENT_ID int not null, primary key (EMPLOYEE_ID) ) #// 主键为EMPLOYEE_ID #// 使用数据库引擎为MyISAM ENGINE=MyISAM DEFAULT CHARSET=utf8; mysql> describe employees; +—————+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +—————+————-+——+—–+———+——-+ | EMPLOYEE_ID | varchar(10) | NO | PRI | NULL | | | FIRST_NAME | varchar(10) | NO | | NULL | | | LAST_NAME | varchar(10) | NO | | NULL | | | EMALL | varchar(50) | NO | | NULL | | | HIRE_DATE | date | YES | | NULL | | | JOB_ID | int(11) | NO | | NULL | | | SALARY | int(11) | YES | | 0 | | | MANAGER_ID | int(11) | YES | | NULL | | | DEPARTMENT_ID | int(11) | NO | | NULL | | +—————+————-+——+—–+———+——-+ 9 rows in set (0.00 sec) ####################### 修改新密码 set password for root@localhost = password(''); use mysql; update user set password=PASSWORD('evan') where user='root'; # on mysql 5.7 ##最好要加这一行 flush privileges; shell>mysqladmin -uroot -p password 新密码 #然后在提示符后输入老密码 增加新用户 格式:grant select on 数据库.* to 用户名@登录主机 identified by ‘密码’ 举例: 例 1:增加一个用户 test1 密码为 abc,让他可以在任何主机上登录,并对所有数据库有 查询、插入、修改、删除的权限。首先用以 root 用户连入 MySQL,然后键入以下命令: mysql> grant select,insert,update,delete on *.* to test1@localhost identified by 'abc'; Query OK, 0 rows affected #或者 mysql> grant all privileges on *.* to test1@localhost identified by 'abc'; flush privileges; 删除用户 delete from user where user=’用户名’ and host=’localhost’; delete from user where user='test2' and host='localhost'; flush privileges; 查看当前使用的数据库 select database(); 表操作 备注:操作之前使用“use <数据库名>”应连接某个数据库。 建表 命令:create table <表名> (<字段名 1> <类型 1> [,..<字段名 n> <类型 n>]); create table mm( id int(4) not null primary key auto_increment, name char(20) not null, sex int(4) not null default '0', degree double(16,2)) #ENGINE=InnoDB; ENGINE=MyISAM DEFAULT CHARSET=utf8; 获取表结构 命令: desc 表名,或者show columns from 表名 mysql> describe my; mysql> desc my; show columns from my; 删除表 drop table 插入数据 命令:insert into <表名> [( <字段名 1>[,..<字段名 n > ])] values ( 值 1 )[, ( 值 n )] mysql> insert into my values(1,'tom',0,96); mysql> insert into my(name,sex,degree) values('evan',1,90); 查询表中的数据 mysql> select * from my; +—-+——+—–+——–+ | id | name | sex | degree | +—-+——+—–+——–+ | 1 | tom | 0 | 96.00 | | 2 | evan | 1 | 90.00 | +—-+——+—–+——–+ #前 2 行数据 select * from my limit 0,2; select * from my order by id limit 0,2; 删除表中数据 命令:delete from 表名 where 表达式 delete from my where id=2; 修改表中数据 命令:update 表名 set 字段=新值,… where 条件 update my set name=’eva’ where id=1; mysql> select * from my order by id limit 0,2; +—-+——+—–+——–+ | id | name | sex | degree | +—-+——+—–+——–+ | 1 | eva | 0 | 96.00 | +—-+——+—–+——–+ 在表中增加字段 命令:alter table 表名 add 字段 类型 其他; 例如:在表 MyClass 中添加了一个字段 pass,类型为 int(4),默认值为 0 mysql> alter table my add pass int(4) default '0'; 更改表名 命令:rename table 原表名 to 新表名; rename table my to mm; 更新字段内容 命令:update 表名 set 字段名 = 新内容 update 表名 set 字段名 = replace(字段名, ‘旧内容’, ‘新内容’); update my set name='nick' where name='eva'; update my set name=replace(name,'jkjk','evan'); 数据库导入导出 将数据库 com 中的 my 导出到 文件中 mysqldump -uroot -pevan com my > my.sql 数据库 mydb 的结构导出到 e:\MySQL\mydb_stru.sql 文件中 mysqldump -uroot -pevan com --add-drop-table >com.stru.sql 只导出数据不导出数据结构 mysqldump -u [数据库用户名] -p -t [要备份的数据库名称]>[备份文件的保存路径] mysqldump -uroot -pevan -t com >com0.sql #here 导出数据库中的Events 格式:mysqldump -u [数据库用户名] -p -E [数据库用户名]>[备份文件的保存路径] 导出数据库中的存储过程和函数 格式:mysqldump -u [数据库用户名] -p -R [数据库用户名]>[备份文件的保存路径] 从外部文件导入数据库中 1)使用“source”命令 2)使用 “<” 小于符号 *************************************** ##20160819 26.5.4 改变表结果 ALTER TABLE 数据表名 更改1 [,更改2 …] #添加TELPHONE 字段 ALTER TABLE employees add TELPHONE char(20); #更改字段 change alter table employees change TELPHONE TEL char(20); ##更改字段 类型 modify alter table employees modify TEL varchar(20); ##删除 字段 drop alter table employees drop tel; #表改名 rename alter table employees rename to emp; 26.5.5 复制数据表 CREATE TABLE 新表名 LIKE 源表名 create table employees2 like employees; #只复制了表结构, 要复制结构和数据的 为 create table emp3 select * from emp; 26.5.6 删除数据表 DROP TABLE tablename; 26.6 数据管理 26.6.1查询数据 SELECT * | 字段列表 FROM 数据表 WHERE 条件; 1.所以数据 select * from emp; 2.某些字段的数据 mysql> select EMPLOYEE_ID, last_name,salary from emp; +————-+————+——–+ | EMPLOYEE_ID | last_name | salary | +————-+————+——–+ | kyct | [email protected] | 9 | +————-+————+——–+ 3,满足某些条件的数据 mysql> select EMPLOYEE_ID, last_name,salary from emp where salary < 10000; +————-+————+——–+ | EMPLOYEE_ID | last_name | salary | +————-+————+——–+ | kyct | [email protected] | 9 | +————-+————+——–+ 4.查询数据的总数 mysql> select count(*) from emp; +———-+ | count(*) | +———-+ | 1 | +———-+ 26.6.2 插入 INSERT INTO 表名(字段1, 字段2, …) VALUES (值1,值2,…); INSERT INTO 表名(字段1, 字段2, …) SELECT 字段1, 字段2, …) FROM 源表; mysql> desc emp; +—————+————-+——+—–+———+——-+ | Field | Type | Null | Key | Default | Extra | +—————+————-+——+—–+———+——-+ | EMPLOYEE_ID | varchar(10) | NO | PRI | NULL | | | FIRST_NAME | varchar(10) | NO | | NULL | | | LAST_NAME | varchar(10) | NO | | NULL | | | EMALL | varchar(50) | NO | | NULL | | | HIRE_DATE | date | YES | | NULL | | | JOB_ID | int(11) | NO | | NULL | | | SALARY | int(11) | YES | | 0 | | | MANAGER_ID | int(11) | YES | | NULL | | | DEPARTMENT_ID | int(11) | NO | | NULL | | +—————+————-+——+—–+———+——-+ mysql> insert into emp values (‘kyct’,’kyct’,’[email protected]’,7,’2016-08-20′,3,9,8,9); insert into emp (employee_id,LAST_NAME,job_id) values (10888,’jim’,5); 2. 插入其它有的数据 mysql> create table emp2 like emp; Query OK, 0 rows affected (0.34 sec) mysql> select * from emp2; mysql> insert into emp2 select * from emp where employee_id=10888; Query OK, 1 row affected, 1 warning (0.00 sec) Records: 1 Duplicates: 0 Warnings: 1 mysql> select * from emp2; +————-+————+———–+——-+———–+——–+——–+————+—————+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID | +————-+————+———–+——-+———–+——–+——–+————+—————+ | 10888 | | jim | | NULL | 5 | 0 | NULL | 0 | +————-+————+———–+——-+———–+——–+——–+————+—————+ 26.6.3 更新数据 UPDATE 表名 SET 字段1 = 值1 [,字段2 = 值2 …] WHERE 查询条件; mysql> select * from emp2; +————-+————+———–+——-+———–+——–+——–+————+—————+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID | +————-+————+———–+——-+———–+——–+——–+————+—————+ | 10888 | | jim | | NULL | 5 | 0 | NULL | 0 | +————-+————+———–+——-+———–+——–+——–+————+—————+ mysql> update emp2 set emall=’[email protected]’,salary=2000 where last_name=’jim’; mysql> select * from emp2; +————-+————+———–+—————+———–+——–+——–+————+—————+ | EMPLOYEE_ID | FIRST_NAME | LAST_NAME | EMALL | HIRE_DATE | JOB_ID | SALARY | MANAGER_ID | DEPARTMENT_ID | +————-+————+———–+—————+———–+——–+——–+————+—————+ | 10888 | | jim | [email protected] | NULL | 5 | 2000 | NULL | 0 | +————-+————+———–+—————+———–+——–+——–+————+—————+ 26.6.4 删除数据 DELETE FROM 表名 WHERE 查询条件; delete from emp where last_name=’jim’; 26.7 索引管理 26.7.1 创建索引 mysql> create table depar ( -> depa_id char(10) not null, -> depa_name varchar(50) not null, -> mana_id char(10), -> index ind_depa01 (depa_id) //创建索引ind_depa01 -> ); #上面为创建表depar的同时,在 depa_id 字段创建一个名为 ind_depa01 的索引 如果表已存在 CREATE INDEX CREATE [UNIQUE] INDEX 索引名 ON 表名 (字段1 [,字段2 …]); create unique index ind_depa02 on dep(EMPLOYEE_ID); #表emp 的 EMPLOYEE_ID 字段上,创建一个名为 ind_depa02 的唯一索引 create unique index ind_depa02 on emp(EMPLOYEE_ID); 26.7.2 删除索引 DROP INDEX 索引名 ON 表名 26.8 用户和权限管理 mysql> select host,user,password,select_priv from user; +———–+——————+——————————————-+————-+ | host | user | password | select_priv | +———–+——————+——————————————-+————-+ | localhost | root | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | Y | | debian | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y | | 127.0.0.1 | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y | | ::1 | root | *CB0AB6862555C52CBE5D736D089D8D56A468AE12 | Y | | localhost | debian-sys-maint | *E7BF66CEE2E00177E47E00EDE25DAFADF8AADF17 | Y | +———–+——————+——————————————-+————-+ 26.8.2 用户管理 1.添加 #add user 记得要刷新权限表 insert into user (host,user,password) values(‘%’,’test’,password(‘123456’)); flush privileges; 2.用户查看 #查看所有用户 在这个新授权时非常有用 也就是看用户是不是已存在 mysql> SELECT user,host,password FROM mysql.user; #查看MySQL的当前用户 mysql> SELECT USER(); 3.更新口令 mysql> update user set password=password(‘1234′) where user=’test’; mysql> flush privileges; delete from user where user=’test’; 26.8.3 用户授权 GRANT 权限[(字段)] ON 数据库名.表名 TO 用户名@域名或者ip 地址 [ IDENTIFIED BY ‘口令’ ] [ WITH GRANT OPTION ]; 1.授权数据库中所有的对象的权限 hr库中所有的表的查询权限授给 evan grant select on hr.* to evan; 2.授权数据库中个别对象的权限 #库hr中数据表emp 的查询和更新权限授给 test用户 mysql> grant select on hr.emp to test; mysql> grant update on hr.emp to test; 3.控制访问的主机 grant all on dbname.tablename to user@’ip’; 4. 授test 用户 hr.emp 表的删除权限,并允许test 把该项权限授其它的用户 grant delete on hr.emp to test with grant option; 5 创建用户 grant select on evan.emp to sam identified by ‘123’; mysql> select host,user,password from user; +———–+——————+——————————————-+ | host | user | password | +———–+——————+——————————————-+ | % | test | *A4B6157319038724E3560894F7F932C8886EBFCF | | % | evan | | | % | sam | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | +———–+——————+——————————————-+ CREATE USER evan@localhost IDENTIFIED BY ‘1234’; 26.8.4 回收权限 REVOKE 权限 [( 字段)] ON dbname.tablename from user@ip revoke select on hr.* from test; mysql创建用户以及授权 http://blog.linuxchina.net/?p=1344 mysql授权查看 http://blog.linuxchina.net/?p=950 26.9 mysql 备份恢复 (5种 mysqldump,mysqlhocopy,sql,binlog,直接备份数据文件 ) 26.9.1 mysqldump 1. 导出指定的表,不指定将导出库中的所有表 mysqldump [options] dbname [table] 2.导出一个多个指定数据库中的所有表 mysqldump [options] –database dbname1 [dbname2,…] 3.导出系统中所有数据库 mysqldump [options] –all-databases 常用的选项 –databases,-B 指定要导出的库 -default-character-set=charset_name 默认字符集 –ignore-table=dbname.tablename #忽略的表,不导出 –lock-all-tables, -X 导出前,锁定所以的数据表,保持系统数据的一致 –lock-tables, -l 锁定当前导出的数据表 #这个用处应该比较多 –log-error=filnename 保存错误和警告日志到文件 –no-create-db, -n 只导出数据,不导出CREATE TABLE 语句 –no-data, -d 只导出表结构,不导出数据 –routines, -R 导出存储过程和函数 1.备份表 mysqldump -uroot -p dbname table >table.sql 2.整个库 mysqldump -uroot -p –database dbname > db.sql 3.只导出表结构 mysqldump -uroot -p –no-data dbname table >table.sql 4.恢复数据 mysql -uroot -p dbanne < file.sql 或者 source **************************************************************** 26.9.2 mysqlhotcopy 感觉不好用 其实是使用lock tables cp scp 来完成备份的 只能备份myisam存储引擎 1.备份 2.恢复 直接是 cp -Rf src targer 26.9.3 使用SQL语句备份和恢复 BACKUP TABLE ,SELECT NITO OUTFILE 26.9.4 二进制日志(binlog) 恢复 mysqlbinlog -d games_s25 --start-datetime="2016-07-25 02:05:00" --stop-datetime="2016-07-25 04:40:00" "mysql-bin.000201" | mysql -uroot -ppassword 26.9.5 直接备份数据文件 ,flush tablename with read lock;#会比较 好 MySQL基本操作 #其它 #查看当前写入的日志 mysql> show master status ; #查看某一库的字符集 mysql> show create database test; #查看MySQL支持的字符集 1 mysql> show character set ; #查看默认字符集 mysql> show variables like '%char%'; 查看某一表的字符集 mysql> show create table test.t1 \G #修改字符集 mysql>alter database mydb character set utf-8; #可以直接在配置文件中做添加,然后重启服务 [client] character_set_server = utf8 [mysqld] character_set_server = utf8
表操作(创建和插入)
create table t1(name varchar(20),fa char(1)); insert into t1 values("e","m"); create table t2(name varchar(20),fa char(1)); insert into t2 values("t","m"); DROP TABLE IF EXISTS `t_teacher`; CREATE TABLE `t_teacher` ( `id` int(11) NOT NULL, `t_name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
数据库及表编码查看
查看数据库编码格式 show variables like 'character_set_database'; 查看数据表的编码格式 show create table <表名>;