MySQL Database (i)

Win installation

#Windows:    #可执行文件        点点点    #压缩包        #放置任意目录        #初始化            服务端：E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --initialize-insecure                    # 用户名 root 密码：空        #启动服务端：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysqld                     #客户端连接：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld\mysql -u root -p                         发送指令：                show databases;                create database db1;                     #环境变量的配置：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin            mysqld                     #windows服务：            E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --install            net start MySQL                         E:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\bin\mysqld --remove                         net start MySQL            net stop MySQL

User Management

• User Management

#创建用户create user '用户名'@'IP地址' identified by '密码';# 删除用户drop user '用户名'@'IP地址';#修改用户rename user '用户名'@'IP地址'; to '新用户名'@'IP地址';#修改密码set password for '用户名'@'IP地址' = Password('新密码');

• Authorization Management

show grants for '用户'@'IP地址'                 # -- 查看权限grant  权限 on 数据库.表 to   '用户'@'IP地址'     # -- 授权revoke 权限 on 数据库.表 from '用户'@'IP地址'     # -- 取消权限

• Permission Description

All privileges except grant all permissions select only Check permissions Select,insert check and insert Permissions ... usage no access permission alter Use alter TABLEALTER routine with ALTER procedure and drop procedurecreate using create Tablecreate rout  Ine use create procedurecreate temporary tables using create temporary tablescreate user using create user, drop                    User, rename user, and revoke all privilegescreate view use the Create Viewdelete using Deletedrop Use the drop Tableexecute to use call and stored procedure file using SELECT INTO outfile and load data infile             Grant option uses Indexinsert with Grant and Revokeindex insertlock tables Use the lock tableprocess with show full processlistselect using Selectshow databases                Show Databasesshow view uses updatereload with show Viewupdate Flushshutdown Using MysqladmIn shutdown (off MySQL) Super uses change master, kill, logs, purge, master, and set global. also allows mysqladmin???????? Debug login Replication Client server location access replication slave used by replication slaves

Database operations

• Create a database

# utf-8CREATE DATABASE 数据库名称 DEFAULT CHARSET utf8 COLLATE utf8_general_ci;create database olddog CHARACTER SET utf8  COLLATE utf8_general_ci; # gbkCREATE DATABASE 数据库名称 DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

• Database common operations

show create database oldboy\G;      #查看创建库的信息 show databases;show databases like "%old%";select database();           #查看进入的数据库

• Common commands

Select version ();          #查看版本select user ();           #查看当前的用户select now ();     #查看当前时间help CREATE database #查看创建数据库帮助show character set;   #查看字符集 CREATE database Oldboy CHARACTER SET UTF8 COLLATE utf8_general_ci;       #创建数据库grant all on oldboy.* to [email protected] identified by ' 123456 ';                                       #授权用户show Grants for [email protected];                                    #查看用户的权限select user,host from Mysql.user; #查看有哪些用户use test;mysql> CREATE TABLE test (ID int (4), name varchar) Engine=innodb Defaul                                       T charset=utf8;show create table test\g;                                                      #查看创建的表desc test;                            #查看表结构insert into test values (1, ' Oldboy ');                       #插入数据update table name Set field = "" where field ...; #修改字段数据delete from table name where condition; #删除字段数据 2, Query (DQL) Select User,host,password from MYSQ L.uSer   #正常查询 Select User,host,password from Mysql.user order by user ASC;  #升序查询 Select User,host,password from Mysql.user order by user desc; #倒序查询 3, Data Manipulation language (DML) INSERT UPDATE Delete delete from mysql.user where user= "Tom"; 4. Object processing Language (DPL) BEGIN transacyion,commit,rollback5, Data Control Language (DCL) GRANT REVOKE6, data definition reason (DDL) CREATE DROP ALTER

Table Operations

#创建表   create table 表名(    列名  类型  是否可以为空，    列名  类型  是否可以为空)ENGINE=InnoDB DEFAULT CHARSET=utf8   #删除表   drop table 表名   #清空表   delete from 表名truncate table 表名   #推荐使用      #修改表   　　#添加列：alter table 表名 add 列名 类型　　#删除列：alter table 表名 drop column 列名　　#修改列：        alter table 表名 modify column 列名 类型;  -- 类型        alter table 表名 change 原列名 新列名 类型; -- 列名，类型     　　#添加主键：        alter table 表名 add primary key(列名);　　#删除主键：        alter table 表名 drop primary key;        alter table 表名  modify  列名 int, drop primary key;    #自增主键修改  alter table db1 AUTO_INCREMENT=10;     　　#添加外键：alter table 从表 add constraint 外键名称（形如：FK_从表_主表） foreign key 从表(外键字段) references 主表(主键字段);　　#删除外键：alter table 表名 drop foreign key 外键名称     　　#修改默认值：ALTER TABLE testalter_tbl ALTER i SET DEFAULT 1000;　　#删除默认值：ALTER TABLE testalter_tbl ALTER i DROP DEFAULT;      #修改表名rename table 表名old to 表名new;alter table 表名old rename to 表名new;　

Table content Operations

• Increase

insert into 表 (列名,列名...) values (值,值,值...)insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...)insert into 表 (列名,列名...) select (列名,列名...) from 表

• By deleting

delete from 表delete from 表 where id＝1 and name＝'alex'

• Change

update 表 set name ＝ 'alex' where id>1

• Check

select * from 表select * from 表 where id > 1select nid,name,gender as gg from 表 where id > 1　　

Database relationships

• Self-increment step

注意：1、对于自增列，必须是索引（含主键）。         2、对于自增可以设置步长和起始值MySQL: 自增步长       基于会话级别：             show session variables like 'auto_inc%';      #查看会话变量             set session auto_increment_increment=2;   #设置会话步长             set session auto_increment_offset=10;        #设置会话起始值      基于全局级别：             show global  variables like 'auto_inc%';    #查看全局变量             set global auto_increment_increment=2;  #设置全局步长             set global auto_increment_offset=10;      #设置全局起始值

• Foreign key binding two primary keys

create table db1(    cid int not null auto_increment,    id1 int not null,    id2 int,    primary key(cid,id1)    )engine=innodb default charset=utf8;create table db2(    sid int not null auto_increment primary key,    ic1 int,    ic2 int,    constraint db2_db1 foreign key(ic1,ic2) references db1(cid,id1)    )engine=innodb default charset=utf8;

• Foreign key unique one-to-one demo

create table user_info(    uid int not null auto_increment primary key,    name varchar(32) not null,    usertype int not null    )engine=innodb default charset=utf8;create table admain_info(    id int not null auto_increment primary key,    user_id int not null,    unique admin_user (user_id),    constraint admin_user foreign key(user_id) references user_info(uid)    )engine=innodb default charset=utf8;insert into user_info(name,usertype) values("alex",1),("egon",2),("tom",3);insert into admain_info(user_id) values(1),(2),(3);

• Foreign key unique many-to-many demo

create table user(    uid int not null auto_increment primary key,    name varchar(32) not null,    gender ENUM("男","女") not null    )engine=innodb default charset=utf8;create table host(    hid int not null auto_increment primary key,    name varchar(32) not null    )engine=innodb default charset=utf8;create table user_host(    id int not null auto_increment primary key,    uid int not null,    hid int not null,    unique uid_hid (uid,hid),    constraint user_host_user foreign key(uid) references user(uid),    constraint user_host_host foreign key(hid) references host(hid)    )engine=innodb default charset=utf8;insert into user(name,gender) values("alex","男"),("egon","男"),("tom","男");insert into host(name) values("host1"),("host2"),("host3");insert into user_host(uid,hid) values(1,1),(1,2),(1,3);insert into user_host(uid,hid) values(2,1),(2,2),(2,3);insert into user_host(uid,hid) values(3,1),(3,2),(3,3);

• Foreign key pair Multi Demo

create table user_info(    uid int not null auto_increment primary key,    name varchar(32) not null,    usertype int not null    )engine=innodb default charset=utf8;create table admin_info(    id int not null auto_increment primary key,    user_id int not null,    constraint admin_user foreign key(user_id) references user_info(uid)    )engine=innodb default charset=utf8;insert into user_info(name,usertype) values("alex",1),("egon",2),("tom",3);insert into admin_info(user_id) values(1),(2),(3);

SQL statement Data

• Common

1、增insert into 表 (列名,列名...) values (值,值,值...);insert into 表 (列名,列名...) values (值,值,值...),(值,值,值...);insert into 表 (列名,列名...) select (列名,列名...) from 表;insert into tb11(name,age) values('alex',12);insert into tb11(name,age) values('alex',12),('root',18);insert into tb12(name,age) select name,age from tb11; 2、删delete from 表;delete from 表 where id＝1 and name＝'alex';delete from tb12 where id >=2 or name='alex'; 3、改update 表 set name＝'alex' where id>1;update tb12 set name='alex' where id>12 and name='xx'update tb12 set name='alex',age=19 where id>12 and name='xx' 4、查select * from tb12;        select id,name from tb12;select id,name as cname from tb12 where id > 10 or name ='xxx';

• Other

A, condition select * from table where ID > 1 and name! = ' Alex ' and num = 12;select * FROM table where ID between 5 and 16;select * fro m table where ID in (11,22,33) a select * from table where ID not in (11,22,33) select * FROM table where ID in (select Nid from table) b, wildcard SE Lect * FROM table where name like ' ale% '-ale begins with all (multiple strings) select * FROM table where name like ' Ale_ '-ale begins with all (one character) c, limit Select            * FROM table limit 5;          -First 5 lines select * FROM table limit 4, 5; -5 rows starting from line 4th select * FROM table Limit 5 offset 4-5 rows from 4th Line D, sort select * from table ORDER BY column ASC-from "column" small to large arrangement s Elect * from table ORDER BY column desc-rank from large to small according to "column" SELECT * from Table order BY column 1 desc, column 2 ASC-According to "column 1" from large to small arrangement, if the same  Sort By column 2 from small to large e, group select Num from table GROUP by Numselect Num,nid from table group by Num,nidselect Num,nid from table where nid > 10  GROUP BY Num,nid Order nid descselect Num,nid,count (*), SUM (score), Max (score), Min (score) from table GROUP by Num,nidselect Num From table GROUP by NUM have max (ID) > 10 Special: Group by must be in where, order byFront F, even table select * FROM USERINFO5,DEPARTMENT5 select * from userinfo5,department5 where userinfo5.part_id = Depa Rtment5.idselect * from Userinfo5 left join department5 on userinfo5.part_id = department5.id# userinfo5 Show All # select * F Rom Userinfo5 right join department5 on userinfo5.part_id = department5.id# department5 All display select * from Userinfo5 Innde R Join department5 on userinfo5.part_id = Department5.id will appear null when a row hides the select * from Department5 the LEFT join Userinfo5 on Useri nfo5.part_id = Department5.idleft Join Userinfo6 on userinfo5.part_id = department5.id

MySQL Database (i)