Shulou(Shulou.com)06/01 Report--

Basic operation of MySQL

# enter database mysql-h localhost-uroot-p # display all databases show databases # enter a database use database_name# show all tables in a database show tables# exit database exit&&quit# allow users to log in remotely to grant all on *. * to user name @'% 'identified by' password'# to run, which means that a user has been created in the user table of MySQL. 1. Basic data type of MySQL: (1) Integer

It should be noted that bit (1) is a bit that represents a bit. (commonly used and denoted by gender)

(2) floating point number

  float: declaration format: float (mfocus n) m: total length, n: number of decimal places; default is (10L2)

  double: declaration format: double (mrecoery n) m: total length, n: number of decimal places; default is (16Jing 4)

  decimal:decimal (m ·n), m: total length, n: number of decimal places, no default value, mainly accurate representation of deposits.

(3) string type:

  char: declaration: char (n) represents a fixed-length string. Fill in the blanks when it is insufficient, which exceeds the error of the Times.

  varchar: declaration: varchar (n), which represents a variable length string, which is the length of the string when it is insufficient, and changes the length when the length is exceeded. (65535)

  text: users store large text

(4) date type:

  date: indicates the date (year-month-day YYYY-MM-DD)

  time: indicates time (hour-minute-second HH:MM:SS)

  datetime: date time (date time: YYYY-MM-DD HH:MM:SS), range: 1000 01-00:00:00-9999-12-31 23:59:59

  timestamp: timestamp (timestamp: YYYYMMDDSSHHMMSS), range: 1970 01-00:00:00-2037-12-31 23:59:59

(5) other types:

  blob: storing binaries

  clob: storing binaries

  enum: enumerated types

  json:json data

2.SQL statement

   sql is a structured query statement (SQL), a database query and programming language used to store data and to query, update, and manage relational databases. Used for database data addition, deletion and modification, database object creation, modification, and deletion, user rights, and transactions.

Classification of    sql statements:

   -DQL: data query statement: select

   -DML: database operation statements: insert, update, delete

   -DDL: database definition statements: create, alter, drop

   -DCL: database control statements: grant (authorization), revoke (de-authorization)

   -TCL: transaction control statements: COMMIT, ROLLBACK

(1) DDL

   function: used to delete, create, view and switch databases and tables

Operate the database

# create database create databse database_name charset=utf-8# delete database drop database database_name # use / switch database use databse_name# to view the currently selected database select database ()

Operation table:

# Show all tables in the database show tables# create table create table t_table (field1 type Field type...) # modified field: add | change | drop | modify# add field alter table t_name add field type# add field position to the first alter table t_name add field type first# modify existing field location alter table t_name modify field1 type after/before field2# modify field name alter table t_name change old_name new_name type# modify field type alter table t_name modify field type# delete field alter table t_name drop field# delete table drop table t_name

Add:

The difference between change and modify?

Alter table table name modify field property constraint

Alter table table name change old field new field property constraint

# display the structure of the table, rename rename table old_name to new_name of the desc t_name# table # View the table creation statement of the table, show create table tsignname; (2) DML

Statements used by    to manipulate data (excluding modifying the structure and properties of the table)

Insert data

# insert a complete record insert into table name values (....) # insert by default (fields correspond to values) insert into table name (fields1,field2,field3...) Values (value1,value2value3...) # insert multiple rows of complete data into insert into table name values (....), (...) # insert all data from one table into another table insert into table1 select * from table2

Modify data

# update update t_name set field1=value1, field2=value2,field3=value3 where... ps: if where is not added, all are modified by default

Delete data

# Delete a record delete from t_name where.... ps: delete all records in the table by default without adding where, but self-increment will not reset # empty the table data and set self-increment to 1 to restart truncate table userinfo (3) backup and recovery of MySQL # data export mysqldump-uroot-p database name > / Desktop/ backup file .sql # data import (if the database already exists and is empty when importing the database) mysql-uroot-p

< /Desktop/备份文件.sql#加载数据文件source /路径/文件名.sql由于sql主要的就是DQL操作，所以后面的博文会详细介绍，这里暂时不写出。3.MySQL中的函数（1）聚合函数#count 统计有多少行记录select count(1) from emp; //1表示常量，每一条记录都有一个1，查看1的个数#max 查看记录中某个字段最大的select max(id) from emp;#min 查看记录中某个字段最小的select min(id) from emp;#sum 查看记录中某个字段的总和select sum(salary) from emp;#avg 查段看记录中某个字的平均值select avg(salary) from emp; ps：在使用这些函数时，如果在加其他字段，可能显示的值不一样，默认是显示表中的第一行的字段值 （2）MySQL的加密函数#md5加密函数（不可逆）MD5(pwd)#密码加密passowrd(pwd)#sha加密sha(str)（3）MySQL的内置函数#数学函数PI() //圆周率Ceil(num) //向上取整floor() //向下取整round(num,保留的小数位数) //四舍五入，如果第二个参数为负数，从小数点前一会四舍五入。Mod(5,2) //取模 相当于 5%2Rand() //随机数，[0,1)从表中随机取数据：select * from emp GROUP BY RAND() limit 2;Pow(2,2) //2的2次方#字符函数replace(str,oldword,newold) //替换lpad(str,len,padstr) //左填充，当字符串不够len时，用字符进行左填充rpad(str,len,padstr) //右填充，当字符串不够len时，用字符进行右填充trim() //删除首位的空格 #日期函数 Now() //当前的时间Sysdate() //获取系统时间Current_date() //获取系统当前日期Current_time() //获取系统当前时间Year(date) //获取日期中的年Month(date) //获取日期中的月Last_day(date) //获取当前月的最后一天（返回的是一个日期）Date_add(date,interval num 单位) //计算日期（返回的是一个日期）#例：select date_add(now(),INTERVAL 2 month) //+2月select date_add(now(),INTERVAL 2 day) //+2天select date_add(now(),INTERVAL 2 year) //+2年select date_add(now(),INTERVAL -2 year) //-2年4.MySQL中运算符   MySQL中的运算符 主要用于select和from子句中：   例：   select 1+1,1*2,2/2,0 ; (除法，保留小数double类型)         整除：select 3 div 2 //返回1    0做除数： select 3 / 0 //返回null 算数运算符 + - * / div（整除） 比较运算符 =（等于） >

=

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.