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

Mysql database service

# main configuration file

/ etc/my.cnf

# View service status

Systemctl status mysqld

# Database directory

/ var/lib/mysql

# Database process name

[root@mysql ~] # ps-C mysqld PID TTY TIME CMD 5604? 00:00:00 mysqld

# default port number

[root@mysql ~] # netstat-pantu | grep 3306 tcp6 0 0:: 3306: * LISTEN 5604/mysqld

# who the process belongs to

[root@mysql ~] # grep mysql / etc/passwd mysql:x:27:27:MySQL Server:/var/lib/mysql:/bin/false

# Group to which the process belongs

[root@mysql ~] # grep mysql / etc/group mysql:x:27: [root@mysql ~] # ps aux | grep mysqldmysql 5604 0.216.8 1119216 171772? Sl 21:36 0:00 / usr/sbin/mysqld-- daemonize-- pid-file=/var/run/mysqld/mysqld.pidroot 5754 0.0 112660 972 pts/0 R + 21:39 0:00 grep-- color=auto mysqld [root@mysql] # ls-ld/ var/lib/mysqldrwxr-x--x. 5 mysql mysql 4096 December 19 21:36 / var/lib/mysql

# log files automatically generated after mysql is started

[root@mysql ~] # ls / var/log/mysqld.log / var/log/mysqld.log

Manage the database using the sql command

Sql Command Classification DDL data definition language (create alter drop) DML data manipulation language (insert update delete select) DCL data Control language (grant revoke) DTL data transaction language (commit rollback savepoint)

The process of storing data on a database server

1, the connection database server client provides the connection tool 2, creates the library (folder) database naming rule? No pure numbers, no special symbols, no uniqueness of keywords.

Create a library

Create database library name; mysql > create database gamedb;# will generate related files [root@mysql4-1 mysql] # ls / var/lib/mysql/gamedb/db.opt in the corresponding database directory.

View existing libraries

Mysql > show databases

Delete Library

Mysql > drop database gamedb;Query OK, 0 rows affected (0.00 sec) [root@mysql4-1 mysql] # ls / var/lib/mysql/gamedb/ls: unable to access / var/lib/mysql/gamedb/: does not have that file or directory

Switching library

Use library name; mysql > use mysql

View the current library

Mysql > select database (); +-+ | database () | +-+ | NULL | +-+ 1 row in set (0.00 sec)

Check the existing tables in the library

Mysql > show tables

3. Create a table (file)

Create table library name. Table name (field name character type, field name numeric type,...); mysql > create table gamedb.stu (- > name char (10),-> age int->)

Insert table record

Insert into library name. Table name values (values list)

Mysql > insert into gamedb.stu values ("jim", 23), ("tom", 21)

View table records

Select * from library name. Table name

Mysql > select * from stu;+-+-+ | name | age | +-+-+ | jim | 23 | tom | 21 | +-+-+

View table structure

Desc library name. Table name

Mysql > desc gamedb.stu

Delete table record

Delete from library name. Table name

Mysql > delete from gamedb.stu;Query OK, 2 rows affected (0.03 sec) mysql > select * from stu;Empty set (0.00 sec)

Delete tabl

Drop table library name. Table name

Mysql > drop table gamedb.stu;Query OK, 0 rows affected (0.24 sec) mysql > show tables;Empty set (0.00 sec)

Mysql data type

Supported data types

Numeric type: integer, floating point type

Integers: according to the range of stored values, integers are divided into:

Tinyint smallint mediumint int bigint 1 byte 2 bytes 3 bytes 4 bytes 8 bytes create table T3 (age int unsigned); # unsigned does not allow storage of negative numbers

Floating point: according to the range of stored values:

Single precision: float (m) n table is the total number of digits, m represents the number of decimal places float (7) xxxxx.xx 99999.99-99999.99 double precision: the width of the double numeric type is the display width, the size of the field assignment cannot be limited Size depends on the type when the assigned width is not enough to save space: the default space padding zerofill # width is not enough for zero padding mysql > create table T24 (id int (3) zerofill,age int (5) zerofill) Query OK, 0 rows affected (0.40 sec) mysql > insert into t24 values (2 sec 2); Query OK, 1 row affected (0.16 sec) mysql > select * from T24 +-+-+ | id | age | +-+-+ | 00002 | +-+-+ 1 row in set (0.00 sec) mysql > desc T24

Character type: text with fixed length and large length

Char varchar blob/text fixed length fixed space changing time changing length changing time changing space with character size

Date and time type:

Year yesr YYYY 1901-2155 can use two digits to assign a date to yesr date YYYYMMDD time time HHMMSS date time datetime YYYYMMDDHHMMSS if it is not assigned default assignment NULLtimestamp YYYYMMDDHHMMSS if it is not assigned default automatic assignment current time use current time function to assign date now () # current time mysql > select now () +-- + | now () | +-- + | 2017-12-20 03:49:11 | +- -- + 1 row in set (0.00 sec) year () year mysql > select year (now ()) in the specified date Month () month in specified time day () days in specified time date () date in specified time time () time in specified time insert into T10 values ('tom',19,year (now ()), time (now ()), date (now ()), now ()

Enumerated: the value of a field can only be selected within the enumerated range

Field name enum (values list) Radio selection Field name set (values list) multiple selection

Field constraint

Set field constraints: use Null to allow not empty. Default setting NOT NULL does not allow setting default values for empty Key index type Default. Default is NULLmysql > create table T13 (- > name char (10) not null,-> age tinyint default 23,-> sex enum ('man','woman') not null default' man'->); Query OK, 0 rows affected (0.37 sec) mysql > desc t13

Modify table structure

Alter table table name execute action

Add a new field

Add field type (width) constraint; # add end add field type (width) constraint first; # add beginning add field type (width) constraint after field name; # add mysql > alter table T11 add name char (10) first;mysql > alter table T11 add mail varchar (30) default 'stu@tedu.cn';mysql > alter table T11 add class char (10) default' nsd1709' after name after adding to a field

Delete a field

Drop field name mysql > alter table T11 drop party,drop mail; copy the original

Modify field type

Modify field type (width) constraint; mysql > alter table T11 modify name varchar (15); modify field name change source field name new field name type (width) constraint; mysql > alter table T11 change meetting party timestamp not null; modify table name mysql > alter table T11 rename stuinfo;mysql > use information_schema; filter constraint mysql for a database > select * from table_constraints where table_schema=' database name'

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.