:
DDL:Data Defination Lanuage:
CREATE, ALTER, DROP
DML:Data Manapulate Language:
INSERT, REPLACE, UPDATE, DELETE
DCL:Data Control Language:
GRANT, REVOKE
:
mysql
DDL
CREATE
ALTER
DROP
DML
INSERT
UPDATE
DELETE
DCL
GRANT
REVOKE
DDL create alter drop
mysql> create database mydb;
Query OK, 1 row affected (0.01 sec)
mysql> use mydb;
Database changed
mysql> show tables;
Empty set (0.00 sec)
mysql> create table students (id char(20),name char(20),age char(10),score char(10)); 4
Query OK, 0 rows affected (0.10 sec)
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| students |
+----------------+
1 row in set (0.00 sec)
mysql> create table teachers as select * from students; ,
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show tables;
+----------------+
| Tables_in_mydb |
+----------------+
| students |
| teachers |
+----------------+
2 rows in set (0.00 sec)
mysql> desc students;
mysql> desc teachers;
+-------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id | char(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| age | char(10) | YES | | NULL | |
| score | char(10) | YES | | NULL | |
+-------+----------+------+-----+---------+-------+
mysql> alter table teachers add subject varchar(100);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc teachers;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | char(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| age | char(10) | YES | | NULL | |
| score | char(10) | YES | | NULL | |
| subject | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
mysql> alter table teachers drop score;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc teachers;
+---------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+--------------+------+-----+---------+-------+
| id | char(20) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| age | char(10) | YES | | NULL | |
| subject | varchar(100) | YES | | NULL | |
+---------+--------------+------+-----+---------+-------+
mysql> alter table teachers change id teacherid char(30); , change
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc teachers;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| teacherid | char(30) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| age | char(10) | YES | | NULL | |
| subject | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
mysql> alter table teachers modify teacherid varchar(5); , modify
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> desc teachers;
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| teacherid | varchar(5) | YES | | NULL | |
| name | char(20) | YES | | NULL | |
| age | char(10) | YES | | NULL | |
| subject | varchar(100) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
mysql> drop table teachers; ,drop table table_name
Query OK, 0 rows affected (0.00 sec)
1:
:SHOW TABLES FROM db_name;
:DESC tb_name;
:DROP TABLE tb_name;
:
ALTER TABLE tb_name
MODIFY
CHANGE
ADD
DROP
help create table help alter table
DML insert update delete ; select
mysql> insert into students (id,name,age,score) value('1','wxtan','18','99'),('2','yull','17','80'); ,insert into
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from students;
+------+-------+------+-------+
| id | name | age | score |
+------+-------+------+-------+
| 1 | wxtan | 18 | 99 |
| 2 | yull | 17 | 80 |
+------+-------+------+-------+
mysql> insert into students value('3','AA','30','60'); ( )
Query OK, 1 row affected (0.00 sec)
mysql> select * from students;
+------+-------+------+-------+
| id | name | age | score |
+------+-------+------+-------+
| 1 | wxtan | 18 | 99 |
| 2 | yull | 17 | 80 |
| 3 | AA | 30 | 60 |
+------+-------+------+-------+
mysql> update students set age=14 where id=1; ,update table_name
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
+------+-------+------+-------+
| id | name | age | score |
+------+-------+------+-------+
| 1 | wxtan | 14 | 99 |
| 2 | yull | 17 | 80 |
| 3 | AA | 30 | 60 |
+------+-------+------+-------+
3 rows in set (0.00 sec)
mysql> update students set age='28' where name='yull'; ,update table_name
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from students;
+------+-------+------+-------+
| id | name | age | score |
+------+-------+------+-------+
| 1 | wxtan | 14 | 99 |
| 2 | yull | 28 | 80 |
| 3 | AA | 30 | 60 |
+------+-------+------+-------+
3 rows in set (0.00 sec)
mysql> select * from students;
+------+-------+------+-------+
| id | name | age | score |
+------+-------+------+-------+
| 1 | wxtan | 14 | 99 |
| 2 | yull | 28 | 80 |
| 5 | bb | 44 | 55 |
| 6 | cc | 44 | 60 |
+------+-------+------+-------+
4 rows in set (0.00 sec)
mysql> delete from students where age='44'; ,delete from
Query OK, 2 rows affected (0.00 sec)
mysql> select * from students;
+------+-------+------+-------+
| id | name | age | score |
+------+-------+------+-------+
| 1 | wxtan | 14 | 99 |
| 2 | yull | 28 | 80 |
+------+-------+------+-------+
mysql> delete from students where age='14' or score='80'; or and
Query OK, 2 rows affected (0.00 sec)
mysql> select * from students;
Empty set (0.00 sec)
mysql> select * from students; ,select from
+------+--------+------+-------+
| id | name | age | score |
+------+--------+------+-------+
| 1 | apple | 20 | 88 |
| 3 | xigua | 10 | 50 |
| 2 | banana | 30 | 68 |
+------+--------+------+-------+
3 rows in set (0.00 sec)
mysql> select id,name from students;
+------+--------+
| id | name |
+------+--------+
| 1 | apple |
| 3 | xigua |
| 2 | banana |
+------+--------+
3 rows in set (0.00 sec)
mysql> select id,name from students where age=20;
+------+-------+
| id | name |
+------+-------+
| 1 | apple |
+------+-------+
1 row in set (0.00 sec)
DCL grant revoke;show grants for
mysql> grant select,insert,update on mydb.students to 'wxtan2'@'192.168.8.%' identified by '123'; , ( )
mysql> flush privileges;
[root@test mysql]# mysql -uwxtan2 -p123 -h192.168.8.104 192.168.8.%
Welcome to the MariaDB monitor.
MySQL [mydb]> select * from students;
+------+--------+------+-------+
| id | name | age | score |
+------+--------+------+-------+
| 1 | apple | 20 | 88 |
| 3 | xigua | 10 | 50 |
| 2 | banana | 30 | 68 |
+------+--------+------+-------+
3 rows in set (0.00 sec)
MySQL [mydb]> insert into students value('4','guapi','40','50'); insert
Query OK, 1 row affected (0.02 sec)
MySQL [mydb]> update students set name='apple222' where id='1'; update
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [mydb]> select * from students;
+------+----------+------+-------+
| id | name | age | score |
+------+----------+------+-------+
| 1 | apple222 | 20 | 88 |
| 3 | xigua | 10 | 50 |
| 2 | banana | 30 | 68 |
| 4 | guapi | 40 | 50 |
+------+----------+------+-------+
4 rows in set (0.00 sec)
MySQL [mydb]> delete from students where name='banana'; delete
ERROR 1142 (42000): DELETE command denied to user 'wxtan2'@'192.168.8.101' for table 'students'
mysql> revoke update on mydb.students from 'wxtan2'@'192.168.8.%'; update
mysql> flush privileges;
MySQL [mydb]> update students set name='apple545' where id='1'; update
ERROR 1142 (42000): UPDATE command denied to user 'wxtan2'@'192.168.8.101' for table 'students'
MySQL [mydb]> insert into students value('6','guapi','40','50'); insert
Query OK, 1 row affected (0.00 sec)
MySQL [mydb]> select * from students; select
+------+----------+------+-------+
| id | name | age | score |
+------+----------+------+-------+
| 1 | apple555 | 20 | 88 |
| 3 | xigua | 10 | 50 |
| 2 | banana | 30 | 68 |
| 4 | guapi | 40 | 50 |
| 6 | guapi | 40 | 50 |
+------+----------+------+-------+
mysql> show grants for 'wxtan2'@'192.168.8.%'; ;usage
+-----------------------------------------------------------------------------------------------------------------+
| Grants for [email protected].% |
+-----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'wxtan2'@'192.168.8.%' IDENTIFIED BY PASSWORD '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257' |
| GRANT SELECT, INSERT ON `mydb`.`students` TO 'wxtan2'@'192.168.8.%' |
+-----------------------------------------------------------------------------------------------------------------+
mysql> grant all privileges on mydb.students to 'wxtan2'@'192.168.8.%' identified by '123456'; ,
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
/etc/init.d/mysql stop (service mysqld stop )
/usr/bin/mysqld_safe --skip-grant-tables
SSH
[root@localhost ~]# mysql
mysql>use mysql;
mysql>update user set password=password("123456") where user="root";
mysql>flush privileges;
mysql>exit
CTRL+Z
[root@apple ~]# jobs
[1]+ Stopped mysqld_safe --skip-grant-tables
[root@apple ~]# kill %1
[root@apple ~]# service mysqld start
Starting MySQL SUCCESS!
1: SET PASSWORD
MySQL。
:mysql> set password for @localhost = password(' ');
:mysql> set password for root@localhost = password('123');
2: mysqladmin
:mysqladmin -u -p password
:mysqladmin -uroot -p123456 password 123
3: UPDATE user
MySQL。
mysql> use mysql;
mysql> update user set password=password('123') where user='root' and host='localhost';
mysql> flush privileges;
4: root ,
windows :
1. MySQL 。
2. DOS , mysql\bin 。
3. mysqld --skip-grant-tables 。--skip-grant-tables MySQL 。
4. DOS ( DOS ), mysql\bin 。
5. mysql , , MySQL >。
6. : use mysql; 。
6. :update user set password=password("123") where user="root";( ) 。
7. ( ):flush privileges; 。
8. quit。
9. , , root 123 。