Mysqlデータ基本操作(追加、削除、変更、検索)
5047 ワード
一、データベース構成
二、接続データベース
三、ユーザー情報の表示
四、ユーザー操作
五、データベースの基本操作
create 、show、use、drop
六、表の基本操作
create 、show、desc、drop
七、記録の基本操作
insert into、delete from、update set、select from
# : (mysqld)、 (client)
1. mysqld( ) utf-8, , utf-8.
:
# 1) mysql , :my.ini
# mac my.cnf
# 2)
[client] # mysql [mysql], [client] [mysql], , Navicat
# mysql
default-character-set=utf8
[mysqld] #
# 3306
port = 3306
# mysql
basedir=F:\mysql-5.6.45-winx64
# mysql
datadir=F:\mysql-5.6.45-winx64\data
#
max_connections=200
# 8 latin1 , utf8
character-set-server=utf8
#
default-storage-engine=INNODB
# 3)
二、接続データベース
1. ( ),
>:mysql
2.
>:mysql -u root -p
, ,
3. mysql
>:mysql -h ip -P -u -p
eg:
>:mysql -hlocalhost -P3306 -uroot -p
4.
>:quit
>:exit
三、ユーザー情報の表示
1.
mysql>: select user();
r
2.root :
mysql>: select * from mysql.user;
mysql>: select * from mysql.user \G
mysql>: select user,password,host from mysql.user;
3.root , ( mysql )
mysql>: delete from mysql.user where user='';
4.root , ( mysql )
mysql>: update mysql.user set password=password('12345678') where user=' ' host='localhost';
5. ,
>: mysqladmin -u -p -h password " "
eg>: mysqladmin -uroot -p12345678 -hlocalhost password "root"
6.root ,
mysql>: create user' '@'%' identified by ' ';
eg>: create user'cxk'@'localhost' identified by '123456';
%: , ‘localhost’, , root ‘localhost’
7.root , :
mysql>: grant all on *.* to tomjoy@localhost identified by '123456';
: all , *.* , , tset , test.*
四、ユーザー操作
1.
mysql>: grant on . to @' ' identified by ' ';
# 1)all:
# 2)oldboy.*:oldboy
# 3)oldboy@'localhost': oldboy
# 4)identified by 'Oldboy123': Oldboy123
eg>: grant all on oldboy.* to oldboy@'localhost' identified by 'Oldboy123';
# 1)select,delete,update,insert,drop,alter:
# 2)oldboy.*:oldboy
# 3)oldboy@'%': oldboy
# 4)identified by 'Oldboy123': Oldboy123
eg>: grant select,delete,update,insert,drop,alter on oldboy.* to oldboy@'%' identified by 'Oldboy123';
2.
mysql>: revoke 1, 2,... on . from @' ';
# 3. oldboy oldboy drop
eg:> revoke drop on oldboy.* from oldboy@'localhost';
4.
drop user @' ';
五、データベースの基本操作
create 、show、use、drop
1.
mysql>: show databases;
2.
mysql>: use
3.
mysql>: select database();
4.
mysql>: create database [charset= ];
eg>: create database cxk;
eg>: create database kobe charset=utf8;
5.
mysql>: show create database ;
6.
mysql>: drop database ;
7.
mysql>: alter database charset= ;
六、表の基本操作
create 、show、desc、drop
# : use ;
1.
mysql>: show tables;
2.
mysql>: create table ( );
eg>: create table student(name varchar(16),age int);
eg>: create table teacher(name varchar(16),age int);
3. sql
mysql>: show create table ;
eg>: show create table student;
4.
mysql>: desc ;
5.
mysql>: drop table
eg>: drop table teacher;
6.
mysql>: alter table rename ;
7.
mysql>: alter table change ( );
8.
mysql>: alter table modify ( );
9. :
mysql>: alter table default charset=gbk;
10.
#
mysql>: create table (
1 ( )
,...
1 ( )
) engine= default charset=utf8;
七、記録の基本操作
insert into、delete from、update set、select from
1. , ,
mysql>: select * from [ .] ;
eg>: select * from student;
: *
2.
mysql>: insert [into] [ ]. values( 1,..., n);
eg>: name age student
1 >: insert into student values('cxk',38);
>: insert into student values(' ',78),(' ',69);
>: insert stu.student values(' ',38),(' ',48);
3.
mysql>: update [ .] set 1= 1, n= n where = ;
eg>: update student set name=' ',age='100' where name = ' ';
: 1) 2) ,
eg>: update student set age='38' where age='100';
4.
mysql>: delete from [ .] where ;
eg>: delete from student where name=' ';
eg>: delete from student where age<38;