MySQL Grammar summary and Practice

#增加用户
#格式: Grant permissions on the database. * To username @ Login host identified by ' password '
/*
For example, add a user user1 password to Password1, so that it can log on to the computer, and all databases have query, insert, modify, delete permissions. First, use the root user to connect to MySQL, and then type the following command:
Grant Select,insert,update,delete on * * to [E- Mail protected] identified by "Password1";
If you want the user to be able to log on to MySQL on any machine, change localhost to "%".
If you do not want to User1 have a password, you can make another command to remove the password.
Grant Select,insert,update,delete on mydb.* to [e-mail protected] identified by "";
*/

Grant all privileges the wpj1105.* to [e-mail protected] identified by ' 123 '; #all Privileges All Permissions

Show grants for [email protected] ' localhost '; #查看用户具有哪些权限.

Revoke select on ucyzw.* from [email protected] ' localhost '; #取消用户授权.

#----------------------------
#-----MySQL Database operation base-----

#显示数据库
show databases;

#判断是否存在数据库wpj1105, delete some words first
Drop database if exists wpj1105;

#创建数据库
Create Database wpj1105;

#删除数据库
Drop database wpj1105;

#使用该数据库
Use wpj1105;

#显示数据库中的表
Show tables;

#先判断表是否存在, there is a first delete
drop table if exists student;

#创建表
CREATE TABLE Student (
ID int auto_increment PRIMARY key,
Name varchar (50),
Sex varchar (20),
Date varchar (50),
Content varchar (100)
) default Charset=utf8;

To view the table structure:

Desc

#删除表
drop table student;

#查看表的结构
describe student; #可以简写为desc student;

View index:

Show indexes from student;

#插入数据
INSERT into student values (null, ' AA ', ' Male ', ' 1988-10-2 ', ' ... ');
INSERT into student values (null, ' BB ', ' female ', ' 1889-03-6 ', ' ... ');
INSERT into student values (null, ' cc ', ' Male ', ' 1889-08-8 ', ' ... ');
INSERT into student values (null, ' DD ', ' female ', ' 1889-12-8 ', ' ... ');
INSERT into student values (NULL, ' ee ', ' female ', ' 1889-09-6 ', ' ... ');
INSERT into student values (NULL, ' FF ', ' null ', ' 1889-09-6 ', ' ... ');
#查询表中的数据
SELECT * from student;
Select Id,name from student;

#修改某一条数据
Update student set sex= ' man ' where id=4;

#补充update数据库多表查询的赋值 (statement to master.) Sometimes the online database to be repaired):

UPDATE TRACK,MV

SET Track.is_show=mv.is_show

WHERE Track.trkid=mv.mvid and Trkid<6 #多表查询的时候注意也要找相等关系

#删除数据
Delete from student where id=5;

# and and
SELECT * FROM student where date> ' 1988-1-2 ' and date< ' 1988-12-1 ';

# or OR
SELECT * FROM student where date< ' 1988-11-2 ' or date> ' 1988-12-1 ';

#between
SELECT * from student where date between ' 1988-1-2 ' and ' 1988-12-1 ';

#in query to develop data within a collection
SELECT * from student where ID in (1,3,5);

#排序 ASC Ascending desc Descending
SELECT * FROM student order by ID ASC;

#分组查询 #聚合函数
Select Max (ID), name,sex from student group by sex;

Select min (date) from student;

Select AVG (ID) as ' averaging ' from student;

Select COUNT (*) from student; #统计表中总数

Select count (Sex) from student; #统计表中性别总数 If there is a piece of data in which sex is empty, it is not counted ~

Select SUM (ID) from student;

#查询第i条以后到第j条的数据 (excluding article i)
SELECT * FROM student limit 2, 5; #显示3-5 data

#巩固练习
CREATE TABLE Xiaoluo

(ID int auto_increment PRIMARY key,

Name varchar (NOT NULL),

Sex varchar (not null),

Age int unsigned, #年龄不能为负值

Sno int unique) default Charset=utf8;

drop table Xiaoluo;
Desc Xiaoluo;

Insert into Xiaoluo (ID,NAME,SEX,AGE,SNO) VALUES (null, ' Ko tao ', ' Male ', 68, 1);
Insert into Xiaoluo (ID,NAME,SEX,AGE,SNO) VALUES (null, ' AA ', ' Male ', 68,2);
Insert into Xiaoluo (ID,NAME,SEX,AGE,SNO) VALUES (null, ' flat ', ' male ', 35, 3);
...

SELECT * from Xiaoluo;

#修改数据
Update Xiaoluo set age=66 where id=2;
Update Xiaoluo set name= ' flower ', age=21,sex= ' woman ' where id=2
Delete from Xiaoluo where age=21;

#常用查询语句
select name,age, id from xiaoluo
select * from Xiaoluo W Here age<40 or age<60;   #or
select * from Xiaoluo where age between 60  #between
select * from Xiaoluo order by age desc;       #order by (ASC ascending des Descending)

#分组查询
select Name,max (age) from Xiaoluo Group by sex;  #按性别分组查年龄最大值
#聚合函数
select min from xiaoluo;
select count (*) from xiaoluo;  #统计表中数据总数
select sum (age) from Xiaoluo;

#修改表的名字
#格式: ALTER TABLE tbl_name Rename to New_name
ALTER TABLE Xiaoluo Rename to A;

#表结构修改
CREATE TABLE Test
(
ID int NOT NULL auto_increment primary key, #设定主键
Name varchar () NOT NULL default ' NoName ', #设定默认值
department_id int NOT NULL,
position_id int NOT NULL,
Unique (department_id,position_id) #设定唯一值
);

#修改表的名字
#格式: ALTER TABLE tbl_name Rename to New_name
ALTER TABLE test Rename to Test_rename;

#向表中增加一个字段 (column)
#格式: ALTER TABLE tablename add columnname type;/alter table tablename Add (columnname type);
ALTER TABLE test add columnname varchar (20);

#修改表中某个字段的名字
ALTER TABLE tablename change columnname newcolumnname type; #修改一个表的字段名
ALTER TABLE test change name uname varchar (50);

SELECT * from Test;

#表position Add column test
alter table position Add (test char);
#表position Modify the column test
alter table position Modify Test char () not NULL;
#表position Modify the column test default value
alter table position alter test set default ' system ';
#表position Remove the test default value
alter table position alter test drop DEFAULT;
#表position Remove the column test
alter table position drop column test;
#表depart_pos Delete the primary key
alter table Depart_pos drop primary key;
#表depart_pos increase the primary key
alter table Depart_pos Add primary key PK_DEPART_POS
(department_id,position_id);

#用文本方式将数据装入数据库表中 (e.g. D:/mysql.txt)
Load data local infile "d:/mysql.txt" into table MYTABLE;

#导入. sql file commands (for example, D:/mysql.sql)
SOURCE D:/mysql.sql; #或者 Mysql-uroot Daname<mysql.sql

Usage of binary logs:

Backup binary logs can be used:

Mysqlbinlog mysql-bin.xxx > Xx.sql (starting point restore)

Or, depending on the point in time:

Mysqlbinlog--start-datetime= "2014-12-13 17:00:00"--stop-datetime= "2014-12-13 19:20:00"/home/mysql/data/ mysql-bin.000008 >/home/test/uclog2.sql

When the master-slave synchronization occurs time offset error can be based on the above steps to find out the error point resynchronization:

Master_host= ' 192.168.1.8 ', master_user= ' rsync ', master_password= ' 123456 ', master_port=3306,master_log_file= ' Mysql-bin.000008 ', master_log_pos=272267720 (find the wrong time point cat uclog2.sql)

#

#mysqlbinlog

--start-position Start position

--stop-position End Position

--start-datetim Start time

--stop-datetim End Time

MySQL is set without password login (Find/-name my.cnf):

First: The configuration file or boot file contains:--skip-grant-tables option.

Second: or add a password to the configuration file:

[Client]

Root=xiaoluo

password=xsdasy892

The MySQL client program uses:

Mysqladmin-uroot-p-h192.168.10.1 Ping #测试对方是否在线

Mysqladmin processlist #查看用户并发线程

Mysqladmin status #查看数据库状态, including full query

#shell allow MySQL statements directly mysql-e "xxx (MySQL statement)" can write a script.

Things: MySQL has a transaction isolation level, based on data consistency requirements. The isolation level can be optimized and things are submitted by default:

START TRANSACTION; #启动事物;

ROLLBACK; #出错的时候事物回滚: The things that have been submitted cannot be rolled back.

COMMIT; #事务提交

Variable to see if a transaction is committed by default, 1 is the default commit, and to reduce the consumption of IO performance, it is recommended to turn off autocommit.

Set autocomit=0

Mysql> SELECT @ @autocommit;

+--------------+

| @ @autocommit |

+--------------+

| 1 |

+--------------+

1 row in Set (0.00 sec)

Lock mechanism and view:

Mysql> Show status like ' table% '

+----------------------------+-------+

| variable_name | Value |

+----------------------------+-------+

| Table_locks_immediate | 1147 |

| table_locks_waited | 0 |

| Table_open_cache_hits | 0 |

| table_open_cache_misses | 0 |

| Table_open_cache_overflows | 0 |

+----------------------------+-------+

5 rows in Set (0.00 sec)

If the table_locks_waited value is higher, it indicates that a lock table is already available.

second, through show Processlist view, if there are more locks, the temporary solution is to direct the Idkill off. But not the ultimate way to find out what causes the lock. Whether it is a hardware problem. Either the problem with the program itself or the lock that is caused by the statement full query. And then slowly optimize. Cache can be added.

This article is from the "Little Luo" blog, please be sure to keep this source http://xiaoluoge.blog.51cto.com/9141967/1589793

MySQL Grammar summary and Practice