Mysql 增删改查

转载

mb5fe1902d5617a 2018-01-19 22:07:00

文章标签 mysql 表名字段数据库数据 文章分类 MySQL 数据库

数据库操作

增add

# 创建一个数据库
mysql> create database 数据库名;

# 创建数据库并加入符集
mysql> create database xiang charset utf-8;

# 创建表
create table table_name (column_name column_type);

create table student(
   stu_id INT AUTO_INCREMENT,
   name CHAR(32) NOT NULL,
   age  INT NOT NULL,
   register_date DATE,
   PRIMARY KEY ( stu_id )
);

View Code

注：
   create table 表名(
stu_id列名 INT整数字类型 AUTO_INCREMENT自增(每创建一条ID就增加),
name列名 CHAR(32)32字节 NOT NULL不能为空,
age列名 INT征数字类型 NOT NULL不能为空,
register_data DATE日期类型,
PRIMARY KEY（stu_id）把stu_id列设置为唯一的主键,
);

注：PRIMARY KEY（stu_id）主键设置后默认就已经不能为空，所以stu_id不需要重复设置null。

View Code

# 插入表内数据
mysql> insert into student(name) values("Xiangsikai");
注：insert into 表名（插入字段1,2,3） values（插入值1,2,3,）

# 添加一个表内的字段
mysql> alter table student add sex enum("M","F");
注：alter table 表名 add 字段 enum("M","F");选择内容M,F

删del

# 删除一个数据库
mysql> drop database xiang;

# 删除表内的列数据
delete from student where name="xsk";
注：delete删除 from 表名 where匹配动作

# 删除一个表内的字段
mysql> alter table student drop age;
注：alter table student drop 删除字段名。

# 清空表(残留框架)
mysql>truncate table 表名;

# 删除表(完全删除)
mysql>drop table 表名;

改update

# 修改表内列字段下的指定数据
mysql> update student set name="xsk",age=33 where id=4;
注：update 表名 set修改数据字段="xsk" where指定修改条件 id=4

# 修改表内字段下批量数据
update student set name="xsk",age=33 where id>1 and id<6;
注：where后加如条件 id>大于 and与 id<小于

# 修改字段的数据类型modify
mysql> alter table xxx modify name CHAR(50);
注：alter table 表名 modify 字段名修改的数据类型；

# 修改字段的数据类型与字段名
mysql> alter table xxx change name name1 CHAR(60);
注：alter table 表名 change 字段名修改的字段名修改的数据类型；

# 修改表名
mysql> alter table student rename to xxx;
注:alter table 表名 rename to 要修改的表名。

# 临时修改数据库模块数值
mysql>set global 模块 = 数值;

查select

# 查询数据库
mysql> show databases;

# 查看用户数据库权限
mysql> show grants for '用户名'@'地址';

# 查看数据库字符集
mysql> show create database 数据库名;

+----------+----------------------------------------------------------------+
| Database | Create Database                                                |
+----------+----------------------------------------------------------------+
| xiang    | CREATE DATABASE `xiang` /*!40100 DEFAULT CHARACTER SET utf8 */ |
+----------+----------------------------------------------------------------+
1 row in set (0.00 sec)
注：默认字符集是 latin1 只支持英文，utf-8支持中文。

View Code

# 查看数据库版本
mysql> select version();

# 查看当前用户
mysql> select user();

#查看所有用户
mysql> select user,host from mysql.user;

# 查看mysql状态
mysql>show status;

# 查看mysql定义参数
mysql>show variables\G;

# 查看mysql模块
mysql> show variables like '%timeout%';

# 查看mysql工作队列
mysql> show processlist;

# 查看数据库表
mysql> show tables;

+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
....
| user                      |
+---------------------------+
28 rows in set (0.00 sec)
注：Tables_in_mysql  #表名

View Code

# 查看表的结构
mysql> desc 表名;

# 查看表内数据
mysql> select * from user;

# 查看数据库表多少列
mysql>select count(*) from 数据库.表名;

# 将数据竖着打印
mysql> select * from user\G

*************************** 1. row ***************************
                  Host: localhost
                  User: root
              Password: *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
....
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: 
      password_expired: N
7 rows in set (0.00 sec)

ERROR: 
No query specified

View Code

# 查询表中筛选行
mysql> select * from student limit 2 offset 2;
注：limit查看行数，offset重多少行开始查询。

# 查询列表中数据进行模糊查询
mysql> select * from student where register_data like "2018-04%";
注：where 列名 like后面跟字符 “字符”

# 查看表各个字段属性
mysql> show columns from 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                     |       |
....
+------------------------+-----------------------------------+------+-----+-----------------------+-------+

View Code

注：
Field 列名
Host                #列名称
Type 类型
char(规定长度)        #字符串
int(规定长度)        #整数字
enum(N，Y)        #只能选择yes no

Null 是否可以为空
NO                 #不可为空
YES                #可以为空

Key 主键
IPR                #主键，两个字段是复合键

Default 填一个默认值

Extra 额外的参数

View Code

#筛选查看mysql表列字段
mysql> select 列字段 from 表名;
mysql> select User from user;

+-------+
| User  |
+-------+
| root  |
| root  |
|       |
| root  |
| user1 |
|       |
| root  |
+-------+

View Code

# 查看表结构的创建记录
mysql> show create table 表名;

# 查看用户表内字段权限
mysql> select * from user\G

# 查询表中筛选字段数据
mysql> select * from student where id =1 and age <23;
注：where id等于1 and与 age字段 <小于23 的数据。and 可以多次使用。

操作符描述实例=等号，检测两个值是否相等，如果相等返回true(A = B) 返回false。<>, !=不等于，检测两个值是否相等，如果不相等返回true(A != B) 返回 true。>大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true(A > B) 返回false。<小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true(A < B) 返回 true。>=大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true(A >= B) 返回false。<=小于等于号，检测左边的值是否小于于或等于右边的值, 如果左边的值小于或等于右边的值返回true(A <= B) 返回 true。
注：使用主键来作为 WHERE 子句的条件查询是非常快速的。

View Code

# 表降序排列
mysql> select * from student order by id desc;
注：by后根的是指定排序的列名

# 表升序排列
mysql> select * from student order by id;
注：by后根的是指定排序的列名

# 统计分组统计，统计某个列名内重复数据个数，并组合成组
mysql> select name,count(*) as xsk_num from student group by name;

注：
select name,count(*)统计数量的列名
as 指定排序数量名称（可不填默认 count(*)）
from 表名 group by 指定字段的组名。

View Code

+------------+----------+
| name       | xsk_num |
+------------+----------+
| Xiangsikai |        7 |
| xsk        |        2 |
+------------+----------+

View Code

# 统计某列同名值总和，与统计出后的和
mysql> select coalesce(name,"age_num"),sum(age) from student group by name with rollup;

注：
select coalesce(对应名的字段(NULL),"定义名")修改对应总和的对应值
sum(字段)统计对应同名的字段值相加
from 表名 group by 
with rollup 求出所有值的总和；

View Code

+--------------------------+----------+
| coalesce(name,"age_num") | sum(age) |
+--------------------------+----------+
| Xiangsikai               |      168 |
| xsk                      |       44 |
| age_num                  |      212 |
+--------------------------+----------+

View Code

其他操作

# mysql下创建授权用户
mysql> grant all on test.* to 'xsk'@'localhost' identified by '123456';

注:
GRANT          #命令

# 赋值权限
SELECT        #查
INSERT        #插入
UPDATE        #修改
DELETE        #删除
CREATE        #创建
DROP          #删除
ALL           #给所有权限

on            # 赋值给那个数据库
*.*           # 所有数据库.所有表
test.*        # 指定单个数据库下所有的表

to            # 权限赋值给哪个用户
'xsk'         # xsk 用户
@             # 可以在哪个机器上访问
'%'           # 所有机器
localhost     #本地

identified     # 设置用户密码
by             # 后面根密码
‘123456’;      # 设置密码

View Code