insert 增加
delete 删除
update 修改
#插入数据 insert
1.不规范
mysql> insert into student4 values(1,\'qiandao\',84,\'m\',NOW());
2.规范写法
mysql> insert into student4(name,age,gender) values(\'qiandao\',84,\'m\');
3.插入多条数据
mysql> insert into student4(name,age,gender) values(\'qiandao\',84,\'m\'),(\'qiudao\',73,\'f\');
4.利用表数据插入表数据
mysql> insert into student select * from student4;
#修改数据 update
1.不规范
mysql> update student set gender=\'m\';
2.规范
mysql> update student set gender=\'m\' where name=\'qiandao\';
3.就算修改整列内容，也要加条件
mysql> update student set age=100 where 1=1;
#删除数据 delete
1.不规范
mysql> delete from test.student;
2.规范
mysql> delete from student4 where id=1;
3.规范
mysql> delete from student4 where 1=1;
使用update代替delete做伪删除
# 1.添加状态列
mysql> alter table student4 add state enum(\'0\',\'1\') default \'1\';
# 2.使用update删除数据
mysql> update student4 set state=\'0\' where id=7;
# 3.查询数据
mysql> select * from student4 where state=\'1\';
3.DCL (Data Control Language) 数据控制语言
grant 向用户赋予权限/角色
revoke 撤销用户的权限/角色
#grant
mysql> grant all on *.* to msy@\'%\' identified by \'123\' with grant option;
#其他参数（扩展）
max_queries_per_hour：一个用户每小时可发出的查询数量
max_updates_per_hour：一个用户每小时可发出的更新数量
max_connetions_per_hour：一个用户每小时可连接到服务器的次数
max_user_connetions：允许同时连接数量
#允许同时连接数量为1:
mysql> grant all on *.* to msy@\'%\' identified by \'123\' with max_user_connections 1;
#连接了一个
[root@db01 ~]# mysql -umsy -p123 -h10.0.0.51
mysql>
#再连第二个
[root@db01 ~]# mysql -umsy -p123 -h10.0.0.51
Warning: Using a password on the command line interface can be insecure.
ERROR 1226 (42000): User \'msy\' has exceeded the \'max_user_connections\' resource (current value: 1)
#可以这样连续给参数
mysql> grant select() on ku.biao to dev1@\'%\' identified by \'123\' with max_queries_per_hour 1 max_updates_per_hour 1 max_connections_per_hour 1 max_user_connections 1;
#当你给查询数量为1时 mysql连接时 就已经算一次了 所以啥也干不成
#revoke
#撤销个查权限
mysql> revoke select on *.* from msy@\'%\';
#看看还有啥权限
mysql> show Grants for msy@\'%\'\G;
*************************** 1. row ***************************
Grants for msy@%: GRANT 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 ON *.* TO \'msy\'@\'%\' IDENTIFIED BY PASSWORD \'*23AE809DDACAF96AF0FD78ED04B6A265E05AA257\' WITH MAX_USER_CONNECTIONS 1
4.DQL (Data Query Langauge) 数据查询语言
select 查询
#查看建库语句（字符集）
#使用二进制装的就是拉丁字符集
mysql> show create database zls;
+----------+----------------------------------------------------------------+
| Database | Create Database
|
+----------+----------------------------------------------------------------+
| zls
| CREATE DATABASE `zls` /*!40100 DEFAULT CHARACTER SET latin1 */ |
+----------+----------------------------------------------------------------+
#使用源码安装的是utf8集
mysql> show create database db;
+----------+-------------------------------------------------------------+
| Database | Create Database
|
+----------+-------------------------------------------------------------+
| db
| CREATE DATABASE `db` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+-------------------------------------------------------------+
#查看表结构
mysql> desc student;
+----------+---------------------+------+-----+---------+-------+
| Field | Type
| Null | Key | Default | Extra |
+----------+---------------------+------+-----+---------+-------+
| id
| int(11)
| YES |
| NULL |
|
| name
| varchar(20)
| YES |
| NULL |
|
| age
| tinyint(4)
| YES |
| NULL |
|
| gender | enum(\'f\',\'m\',\'qls\') | YES |
| NULL |
|
| cometime | datetime
| YES |
| NULL |
|
+----------+---------------------+------+-----+---------+-------+
#查看自己当前在哪个库
mysql> select database();
+------------+
| database() |
+------------+
| test
|
+------------+
#查看数据库的默认的引擎类别
mysql> show variables like \'%storage_engine%\';
+----------------------------+--------+
| Variable_name
| Value |
+----------------------------+--------+
| default_storage_engine
| InnoDB |
| default_tmp_storage_engine | InnoDB |
| storage_engine
| InnoDB |
+----------------------------+--------+
导入数据小玩一波
# 导入数据
[root@db01 ~]# mysql < world.sql
mysql> show tables from world;
+-----------------+
| Tables_in_world |
+-----------------+
| city
|
| country
|
| countrylanguage |
+-----------------+
#想看看这个导入的新表里有点啥
mysql > select * from city;[在生产环境中千万不要用 如果大占内存 会oom]
mysql> select count(*) from world.city;[先这样看看]
+----------+
| count(*) |
+----------+
|
4079 |
+----------+
1.函数
max()
min()
sum()
avg()
count()
distinct()
2.查询所有数据
mysql> select * from world.city;
